星期三, 10月 17, 2007

用函數達到(兩條件)自動查表

Major John @ 星期三, 10月 17, 2007
在Excel中最常用的查表功能是Vlookup、Hlookup,這是最直覺拿來套用就可以的了!不過他們有個限制,資料需要經過排序,如果沒排序的話,函數搜尋出來的資料會出現錯誤。在這裡就不探討這兩個函數了!
其實我最喜歡用INDEX和MATCH函數來達到相似的功能,好處是不需要排序,比Vlookup、Hlookup好這麼一個功能。不過MATCH函數也有缺點的啦,就是他找MATCH資料的話,資料要是唯一的!!這裡不懂沒關係,我先講起來放而已!(不然我真不知道怎麼起頭這篇文章)

假如有一筆資料如下圖:



需求的功能

先借《超級星光大道》的資料來舉個例,假設我想要查10/12號比賽當天,唱真命天子的演唱者是誰;或是10/6號唱心動的人是誰。這樣的查表模式。
這樣我們就需要有兩個選項嘛!?先選擇日期,在選擇歌曲,然後得到結果 - 演唱者。
這兩個選項,我打算用下拉式選單來做,這樣就不用key得要死啦!
這裡很妙的地方在於,當你選擇完日期之後,選擇歌曲的該儲存格會跑出相對應的歌曲,也就是選擇了10/12日(C1),下面的歌曲就只會讓你選擇C2到C13(C2:C13);選擇10/6日(B1)後,歌曲的選擇就只能選擇B2到B13(B2:B13)。

會用到的函數
要做到這樣的功能,需要用到以下幾個函數:
  1. 下拉式選單
  2. COUNTA()
  3. MATCH()
  4. OFFSET()
  5. INDEX()

Example File

強烈建議將檔案下載回去看,邊看檔案邊看說明,比較好懂。(因為我對自己的表達能力沒太大的信心)

函數解析的步驟 - 從裡到外
假設有兩個函數分別為A()與B(),而B函數被A函數包起來,A(B()),Excel會先把B函數算出來後的結果再帶入A中。這跟數學的算法是一樣的,有括號先算,然後先乘除後加減。


下拉式選單
Excel中有兩種下拉式選單,在這裡先只用一種。
移到你要作成下拉式選單的儲存格,接著在最上面的選單「資料」→「驗證」
跳出對話框
在「儲存格內允許(A)」的項目選擇「清單」。在「來源(S)」可以填寫選項範圍,這裡我先填B1到C1!選單就會出現B1到C1的內容讓你選了!
「來源(S)」除了可以填儲存格的位置之外,還可以填入公式!下一格F18就是要填公式囉!


COUNTA(範圍)
功用是:計算出範圍內有多少的非空白儲存格的數目。
COUNTA(1:1)
就是計算第一列有幾個非空白儲存格。在這裡會得到COUNTA(1:1)=3,第一列只有三欄資料。

為甚麼要這樣做呢?
因為這個table可能會一直增加下去,《超級星光大道》每個禮拜都會比賽嘛,這個table每過一個禮拜資料就會往右邊延伸下去。所以用COUNTA來算總共已經有幾欄資料了。


MATCH(要搜尋的值,要搜尋的範圍,type) 的用法,
功用是:在指定範圍內找到指定的值,標出該值在範圍內的位置
type=0 是 精確相等 在「搜尋範圍」內找到完全符合「要搜尋的值」的位置;
type=-1 or 1 是 相似值 只要搜尋範圍內有相似於「要搜尋的值」就指出他的位置。
我通常都是type用0。

第一個下拉式選單選日期嘛!我們用MATCH去找到這個日期是在表格的第幾欄,MATCH(F17,1:1,0)
在1:1的範圍(即第一列)找到F17格的值,。F17那格就是你選擇的比賽日期。
如果是
「10月6日比賽」則MATCH函數得到的結果是2 - 「10月6日比賽」這個值在第一列裡是第二欄;
「10月12日比賽」,MATCH得到結果是3 - 「10月12日比賽」這個值在第一列裡是第三欄。


OFFSET(範圍,起始點列數,起始點欄數,資料高度,資料寬度)
OFFSET()的結果,會是一個「資料範圍」。
範圍Reference:我們圈選A1:C13,就是歌曲的列表,
起始列數Rows:1,從第二列開始,第二列才是歌曲的曲目。
起始欄數Cols:MATCH(F17,1:1,0)-1。MATCH來幫我們定位比賽日期是第幾欄嘛!
資料高度Height:COUNTA(A:A)-1,A欄的資料高度COUNTA(A:A)=13,可是要扣掉第一列是標題列,所以是13-1=12列。其實不一定要用A欄定義資料的高度啦,你也可以用B欄、C欄,我取A欄是因為,A欄的資料一定都在!
資料寬度Width:1。只要一欄就好。

=OFFSET(A1:C13,1,MATCH(F17,1:1,0)-1,COUNTA(A:A)-1,1)
如果
日期是「10月12日比賽」
→MATCH(F17,1:1,0)-1=2,;
→COUNTA(A:A)-1=12,即
→A1:C13這個範圍內的第2欄(C欄)資料,資料高度12則表示C2到C13這個範圍。這就是我們要的。在下拉式選單上就給我出現C2~C13的資料讓我選。

F18這格就填寫清單成這樣。


會用了OFFSET以後,你自己就可以把F17格的「選日期」的清單改成這樣
=OFFSET(1:1,0,1,1,COUNTA(1:1)-1)
如此一來,當每個禮拜都比賽的《超級星光大道》每個禮拜都會新增加資料進去,「10月6日比賽」、「10月12日比賽」、「10月18日比賽」………一直下去。你的F17格就增加比賽日期的選項!!!


INDEX(範圍,第幾列,第幾欄)
在指定的範圍裡面的某一欄某一列的儲存格值。
在F19格,我們要得出結果,F17、F18兩個選項選完之後,我要讓這格自動出現演唱者是誰!
範圍:A2:C13
第幾列:MATCH(F18,OFFSET(A1:C13,1,MATCH(F17,1:1,0)-1,COUNTA(A:A)-1,1),0)。別被這麼長的函數嚇到了,先把灰色的不看,其實他是個很單純的MATCH函數。灰色的部份是MATCH的範圍,而這個〝範圍〞跟F18中定義的範圍是一樣的,所以我直接copy&paste貼到這裡來。比如說,你選擇的「10月12日比賽」+「火」,應該要得到演唱者是「林佩瑤」嘛!「火」是「10月12日比賽」欄中的第3列,所以MATCH得到的值是3。
第幾欄:1。
這一格最重要的是第幾列這個參數。


大功告成。

No Response to "用函數達到(兩條件)自動查表"

張貼留言