【求助】excel的參照功能

**iamyew** · 2005-09-30, 10:51 AM

向各位大大求助:
我利用excel的參照功能(函數如下)要求classlist中相關的資料

=IF(ISERROR(VLOOKUP($A$4,classlist,3,0))," ",(VLOOKUP($A$4,classlist,3,0)))

請問:
如何在儲存格中顯示出原來classlist名稱中相關資料的格式(例如儲存格與字元的顏色??或者能有其它的解決方式

)!
謝謝大家的幫忙!!

**cbja** · 2005-09-30, 12:29 PM

http://www.excelhelp.net/cgi-bin/forum/leoboard.cgi

**leonchou** · 2005-09-30, 12:34 PM

函數和公式無法實現這樣的功能。

另外你可參考"格式化條件"，也許你能接受折衷的方式..
http://gb.twbts.com/index.php/topic,471.0.html
http://gb.twbts.com/index.php/topic,325.0.html
http://www.pczone.com.tw/showthread.php?t=120084
http://www.excelhelp.net/cgi-bin/for...m=4&topic=1572

**iamyew** · 2005-09-30, 10:07 PM

順著大大們的指引前去尋寶,但手上的資料似乎有點差異.....(因為並無法給予單一條件,而是儲存格的範圍....

)
將相關的問題寫在附檔中,能否再請指點這樣的情況要如何處理?
感謝您再次費心~

**leonchou** · 2005-10-01, 01:09 AM

>> 2.倘請假日期為星期一,則列出Mon1~Mon8;
>> 請假日期為星期二,則列出Tue1~Tue8;
>> ………餘類推

請假哪天就列出哪天? 其他隱藏?
不太合理呀，請再解釋一下。

**iamyew** · 2005-10-01, 12:15 PM

作者：leonchou

>> 2.倘請假日期為星期一,則列出Mon1~Mon8;
>> 請假日期為星期二,則列出Tue1~Tue8;
>> ………餘類推

請假哪天就列出哪天? 其他隱藏?
不太合理呀，請再解釋一下。

呵....sorry...想的人很清楚,看的人頗迷糊...真的是心急口快哩!
嗯...從需求來講應該會比較清楚些!
這是一份總日課表(其它顏色儲存格標示的表示科任課),希望透過參照或其他公式達到兩個功能(太貪心了...本來只想作課表查詢的.....):
1.當選擇班級時,則列出該班的級任老師與週課表
2.倘在請假的欄位輸入日期時,則依對應的日期(指星期幾)列出當事人當天的課表(

因為想用來讓老師填寫請假單.....)
3.格式與欠缺的欄位則等大大指點後,我再自行修正加上!!

感恩啦 leonchou大大.....再次稽首....

**leonchou** · 2005-10-01, 02:29 PM

我的意思是說，週一到週五都已列在上面了 (當然也已包含請假的那天)，
那麼你所謂 "列出請假當天的課表" 還要列在哪裡?

附上的範例是針對變色的部份...
重點是定義參照到公式的名稱，還有格式化條件。
會說折衷方式是因為雖然公式儲存格達到變色的效果，
但公式儲存格只能判斷來源資料的顏色，設定一種固定色彩；
由於顏色不能動態設定，如果你改變來源資料的顏色，
公式儲存格不會自動變成同一種顏色。

還有需注意：
來源表格必須保持每天 8 堂課的格數，
不要隨意從中插入或刪除欄，因為
相關公式需要判斷每一格與上面週課表的相對位置。

**iamyew** · 2005-10-01, 03:42 PM

作者：leonchou

我的意思是說，週一到週五都已列在上面了 (當然也已包含請假的那天)，
那麼你所謂 "列出請假當天的課表" 還要列在哪裡?

附上的範例是針對變色的部份...
重點是定義參照到公式的名稱，還有格式化條件。
會說折衷方式是因為雖然公式儲存格達到變色的效果，
但公式儲存格只能判斷來源資料的顏色，設定一種固定色彩；
由於顏色不能動態設定，如果你改變來源資料的顏色，
公式儲存格不會自動變成同一種顏色。

還有需注意：
來源表格必須保持每天 8 堂課的格數，
不要隨意從中插入或刪除欄，因為
相關公式需要判斷每一格與上面週課表的相對位置。

先謝謝大大如此迅速地回覆(嘻...感覺您可以當Excel的神耶...念佛號就顯靈...)
其實兩個功能(列出課表與請假的問題)是應該要分別用兩個工作表處理的啦!
(總不能凡事都請大大弄好,自己不動手咩....)
能否請大大解說您附檔中名稱定義裡的這個公式( 讓人偷學一下啦~~

)
=GET.CELL(63,OFFSET(classlist,MOD(new!$A1,100)-1,DataColumn-1,1,1))

**leonchou** · 2005-10-01, 08:03 PM

=GET.CELL(63,OFFSET(classlist,MOD(new!$A1,100)-1,DataColumn-1,1,1))
細部拆解 --
MOD(new!$A1,100)
$A1 是列相對參照 (什麼是相對/絕對參照)，故以 C6 為例就是 MOD(new!$A6,100)。
由於來源資料範圍 classlist 的班級號碼是 101,102,103... 依序排列，MOD(new!$A6,100) 取得 A6 班級號碼除以 100 的餘數，例如 101 -> 1, 102 -> 2, ...以此類推，意思就是該班級在 classlist 是第幾筆資料。

DataColumn
這是一個定義名稱，參照到公式 COLUMN()+8*(ROW()-6)/5。
COLUMN() 和 ROW() 這兩個Excel函數分別代表公式所在儲存格的欄號及列號。
8 --> 每天 8 堂課 ( 8 個儲存格)
6 --> 上面週課表第一天(週一)的公式在第 6 列
5 --> 週課表每一天的公式相差 5 列 (第1天在第6列, 第2天在第11列, ...以此類推)
依上述的規律性，於是 COLUMN()+8*(ROW()-6)/5 這公式就可推算出週課表每一天每一堂課在 classlist 是第幾欄(第幾格)，例如週一第1堂課(C6)是第3格, D6是第4格, 週二第1堂課(C11)是第11格, D11在第12格, ...以此類推。因此 DataColumn 亦可應用在你原來的 VLOOKUP 公式。
也就是為了遵循這個規律，所以說 classlist 不能任意插入/刪除欄位，即使那一格是空的。

OFFSET(classlist,MOD(new!$A1,100)-1,DataColumn-1,1,1)
OFFSET 這個Excel函數可依特定儲存格或範圍，指定位移的欄/列數及總欄/列數，傳回一個新的儲存格或範圍參照。語法：
OFFSET(特定儲存格或範圍,位移列數,位移欄數,總列數,總欄數)
套上我們的數據後，就得到每一天每一堂課在 classlist 中的參照位置。

GET.CELL(63,儲存格參照)
GET.CELL 是屬於Excel 4.0巨集函數，它可以依指定的項目代碼取得特定儲存格的詳細資訊，範例中的項目代碼：
24 --> 傳回儲存格第一個字元色彩號碼, 1 至 56. 如果色彩設定為自動, 傳回 0.
63 --> 傳回儲存格的填滿色彩號碼, 原則同上.
套上我們的數據後，就得知週課表每一天每一堂課在來源範圍 classlist 中的顏色值。
範例中定義的名稱 COLOR 及 COLORB：
COLOR --> GET.CELL(24,OFFSET(...)), 範例中用於格式化條件的公式.
COLORB --> GET.CELL(63,OFFSET(...)) 範例中僅供備用及參考.

總結：
在週課表每一堂課的格式化條件公式 =COLOR=3 中，判斷來源儲存格顏色，如果是紅色 (3是紅色的色彩號碼)，在週課表則就呈現黃底紅字。
當然你可把公式改為 =COLOR>0 ，則不論來源儲存格設成什麼顏色都有效。

不過，再次提醒：格式化條件的效果(如字型色彩、底色)是無法動態設定的。

相關學習：
GET.CELL等Excel4.0巨集函數說明
Excel 4.0巨集函數說明安裝程式下載

**iamyew** · 2005-10-03, 03:56 PM

獲益良多,謝謝大大的指點!!

另,有關於:
MOD(new!$A1,100)
$A1 是列相對參照 (什麼是相對/絕對參照)，故以 C6 為例就是 MOD(new!$A6,100)。
由於來源資料範圍 classlist 的班級號碼是 101,102,103... 依序排列，MOD(new!$A6,100) 取得 A6 班級號碼除以 100 的餘數，例如 101 -> 1, 102 -> 2, ...以此類推，意思就是該班級在 classlist 是第幾筆資料。

那麼,如果我的班級號碼是101.102.103....201.202.203...301.302.303
可以用怎樣的方式來取得資料來源?(有點蠢請不要生氣....)

ps.剛剛試了一下把資料全放進去,用原來的公式似乎可以正確套用(整體班級號碼的資料包含了數字與文字,如附件),但是卻覺得不大合理...(或者是我自己的腦袋不清楚....

)