在Excel中常會用到查表, 也就是一些規定或是基本資料的查詢, 這些規定或基本資料是建成一張表格, 然後從你要查詢的關鍵字在表中找到另一個資料. 例如班級的制服訂作, 你可以找每一個人來套量, 也可以利用已經有的身高資料, 配合尺寸表得到.
假設你的團購團一年來讓你賺了不少錢, 為了強化顧客忠誠度, 你決定給他們一個驚喜, 送大家一件T恤, 當然還有一個目的, 在T恤上印上廣告宣傳. 即然是驚喜, 自然不能找大家來量, 這時候, 團員的身高表就要拿出來了
| 姓名(A1) | 身高 |
| 王五 | 159 |
| 吳九 | 156 |
| 趙六 | 166 |
| 梁二 | 170 |
| 鄭十 | 151 |
| 張三 | 169 |
| 朱七 | 162 |
| 孫八 | 162 |
| 丁一 | 161 |
| 李四 | 175 |
然後是身高對照尺寸表, 備註欄是為了說明用, 可以不用
身高(D1)
|
尺寸
|
備註
|
0
|
S
|
0 ~ 159.9
|
160
|
M
|
160 ~ 164.9
|
165
|
L
|
165 ~ 169.9
|
170
|
XL
|
170 ~ 174.9
|
175
|
XXL
|
175 ~ 179.9
|
180
|
XXXL
|
180 ~
|
可是想想, 全部送, 沒有辦法刺激買氣, 那改為訂購金額超過1000元的才列為對象好了. 查到符合的人有丁一, 梁二, 趙六等三人. 所以再建一個贈送對象表
姓名(H1)
|
身高
|
尺寸
|
丁一
| ||
梁二
| ||
趙六
|
接下來要說明如何查表了, 我們要用 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)
姓名(H1)
|
身高
|
尺寸
|
丁一
|
161
| |
梁二
|
170
| |
趙六
|
166
|
同樣的, 要查身高對照尺寸, 應該沒有問題吧? 在J2填入
=VLookUp(I2,$D$2:$E$7,2,0)
可是實作發現, 慘了, 查不到161, 166的對照. 但若把最後一個參數由0改為1, 就可以查到
姓名(H1)
|
身高
|
尺寸
|
丁一
|
161
|
M
|
梁二
|
170
|
XL
|
趙六
|
166
|
L
|
這個1是告訴電腦, 表格D2:D7是由小到大排序的, 找到相近但大不於查詢的數即可. 所以161 會查到160, 傳回M, 166會查到165, 傳回L.
最後一個參數, 0 或 1, 需確實了解才能把這公式用對了, 得到正確的結果, 小心. 另外, 被查詢的表格可以有很多欄, 但被查詢比較的欄一定是第1欄, 傳回的欄可以是範圍內的任何一個.
還有HLookUp, 與VLookUp的差異是方向是橫向的而以, 其他一樣. 還有LookUp...
接下來說明如何查詢二維表格.
假設衣服的尺寸與身高相關之外, 還跟體重有關, 所以建了一個二維的表格如下
尺寸(A1)
|
體重
| |||||
身高
|
0
|
50
|
60
|
70
|
80
|
90
|
0
|
S
|
S
|
M
|
M
|
L
|
L
|
160
|
M
|
M
|
M
|
L
|
L
|
XL
|
165
|
L
|
L
|
L
|
L
|
XL
|
XL
|
170
|
XL
|
XL
|
XL
|
XL
|
XL
|
XXL
|
175
|
XXL
|
XXL
|
XXL
|
XXL
|
XXL
|
XXXL
|
180
|
XXXL
|
XXXL
|
XXXL
|
XXXL
|
XXXL
|
XXXL
|
利用公式 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.....公式, 可以練習用用.
