一切總是適時完成: Excel密技-查表

在Excel中常會用到查表, 也就是一些規定或是基本資料的查詢, 這些規定或基本資料是建成一張表格, 然後從你要查詢的關鍵字在表中找到另一個資料. 例如班級的制服訂作, 你可以找每一個人來套量, 也可以利用已經有的身高資料, 配合尺寸表得到.

假設你的團購團一年來讓你賺了不少錢, 為了強化顧客忠誠度, 你決定給他們一個驚喜, 送大家一件T恤, 當然還有一個目的, 在T恤上印上廣告宣傳. 即然是驚喜, 自然不能找大家來量, 這時候, 團員的身高表就要拿出來了

表1

然後是身高對照尺寸表, 備註欄是為了說明用, 可以不用

表2

可是想想, 全部送, 沒有辦法刺激買氣, 那改為訂購金額超過1000元的才列為對象好了. 查到符合的人有丁一, 梁二, 趙六等三人. 所以再建一個贈送對象表

接下來要說明如何查表了, 我們要用 VLookUp 這個公式, 在丁一的身高格I2填上

=VLookUp(H2,A2:B11,2,0)

意思是在表格 A2:B11中的第一欄A2:A11中找H2:丁一, 找到了, 傳回第2欄(B)的值. 最後一個參數0, 是告訴電腦, A2:A11是沒有排序, 要找到完全一樣的才可以.

所以會得到下表. 注意, 用自動填滿時, 公式要寫成 =VLookUp(H2,$A$2:$B$11,2,0)

同樣的, 要查身高對照尺寸, 應該沒有問題吧? 在J2填入

=VLookUp(I2,$D$2:$E$7,2,0)

可是實作發現, 慘了, 查不到161, 166的對照. 但若把最後一個參數由0改為1, 就可以查到

這個1是告訴電腦, 表格D2:D7是由小到大排序的, 找到相近但大不於查詢的數即可. 所以161 會查到160, 傳回M, 166會查到165, 傳回L.

最後一個參數, 0 或 1, 需確實了解才能把這公式用對了, 得到正確的結果, 小心. 另外, 被查詢的表格可以有很多欄, 但被查詢比較的欄一定是第1欄, 傳回的欄可以是範圍內的任何一個.

還有HLookUp, 與VLookUp的差異是方向是橫向的而以, 其他一樣. 還有LookUp...

接下來說明如何查詢二維表格.

假設衣服的尺寸與身高相關之外, 還跟體重有關, 所以建了一個二維的表格如下

利用公式 Index(B3:G8,1,1)會得到S, Index(B3:G8,6,1)會得到XXXL, 類推. 所以, 現在我們要找出的是在行, 列中的第幾個, 再利用Index公式找到所要的值.

要找出所查詢的值在被查詢的欄或列的第幾個, 就要用Match公式. 以身高 171, 體重 81 來說明

Match(171,A3:A8)傳回來4, Match(81,B2:G2)會傳回來5, Index(B3:G8,4,5)會傳回來XL

所以公式為 =Index(B3:G8,Match(171,A3:A8),Match(81,B2:G2))

Match 公式也有像VLookUp, HLookUp的查詢方式的參數, 用法相同, 還可以從大到小排列.

此外還有 Address, Indirect.....公式, 可以練習用用.

一切總是適時完成