職場新鮮誌

文 黃奕霖

許多人用Excel統整資料時，常以Vlookup及Hlookup兩種函數進行輔助，但你知道更新的Xlookup如何使用嗎？

讀完這篇文章，你可以學到的3件事：

Xlookup與Vlookup怎麼用？

XLOOKUP與VLOOKUP皆為excel的查詢函數，當在多個表格中查詢資料時，讓你不必一一對照，就能讓表格自動化。

什麼情況會用到Xlookup與Vlookup?

舉例來說下圖為業務組銷售資料，要完成填寫F2的「銷售單價」欄位，就需要一一找到對應的產品售價、並乘上銷售數量。這方法當然可行，但若遇到資料龐大時，就會顯得沒效率。

這時候，就可以運用查詢函數，在F2儲存格套入Xlookup或Vlookup的函數公式來自動計算。以下是Xlookup和Vlookup的個別操作方式，讓你看出兩者間的差異。

1. Vlookup公式：
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]）
=VLOOKUP(用來對比的資料, 被對比的資料範圍, 指定的欄位, [比對的方式]）

第一個引數代表用來對比的資料，使用的是D排「銷售產品」，對應於F2的值是D2（產品C）；第二個引數則是被對比的資料範圍，應為畫面右側的售價參考表「H3:I7 」； 第三個引數為指定欄位，指涉需要回填範圍的欄位為2（I欄是指定資料範圍的第2欄）；第四個引數比對的方式的值只接受0（false=選擇完全相符的）跟 1（true=選擇大致相符的）。

※備註：參考的表格必須加上固定符號$，打成$H$3:$I$7
按上述原理填入公式後，就會在F2欄位輸入=VLOOKUP(D2, $H$3:$I$7, 2, 0)，向下拖曳（複製公式）後，所有單價的數值就會自動填上。

2. XLOOKUP公式：
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_model])
=XLOOKUP(用來對比的資料,被對比的列/範圍, 要回傳的資料列/範圍, 沒有查到資料時要回傳的訊息, 查到資料時的比對模式)

第一個引數為用來對比的資料是D2（產品C）；第二個引數則是套用比對範圍，找到H3:H7；第三個引數代表要回傳的資料列範圍是I3:I7。

按上述原理填入公式後，就會在F2欄位輸入=XLOOKUP(D2, $H$3:$H$7, $I$3:$I$7)，向下拖曳（複製公式）後，也可以得到答案。

Xlookup與Vlookup的差別？

Xlookup與Vlookup雖然都Excel函數，但功能上仍有些許差異。

差別1：Xlookup能解決所有查表問題
兩者的公式在第2個引數上擷取範圍不同，Vlookup輸入的是整個參考表範圍 (table_array)；Xlookup則是輸入尋找的列 (lookup_array)，這使Xlookup的公式有更大的彈性可以取代許多查詢函數。

要了解Xlookup有多強大，必須以Vlookup對比。

Vlookup公式有一項限制，被比對的欄位(table_array)一定要在參考表的第1欄。

下圖有3種售價參考表，若要符合Vlookup的限制，則售價參考表2或3的格式，都需要另外整理為參考表1才行，否則就會顯示錯誤訊息「#N/A」。


相較之下， Xlookup公式就沒有此限制，被比對的欄位可以抓取任何一欄，因此無論哪種格式皆可直接使用。

此外，若Vlookup遇到參考表3這種橫向表格時，就需改成另一個函數公式Hlookup才行；但Xlookup仍可用同一個既有公式來解決問題。

差別2：Xlookup可傳回多個專案陣列
Xlookup可以設定多個回傳資訊 (return_array)。如下圖，若想要以名字追蹤多個資料，可以設定回傳資訊範圍，只要輸入：=XLOOKUP(H3,B2:B11,B2:E11)，就可以一次回傳橫向資料「部門」、「銷售產品」和「銷售數量」等資訊。

Xlookup可以設定多個回傳資訊 (return_array)。如下圖，若想要以名字追蹤多個資料，可以設定回傳資訊範圍，只要輸入：=XLOOKUP(H3,B2:B11,B2:E11)，就可以一次回傳橫向資料「部門」、「銷售產品」和「銷售數量」等資訊。

差別3：Xlookup公式可以更精簡化

以前面舉例的查詢單價為例：
XLOOKUP()的公式如下：F2=XLOOKUP(D2, $H$3:$H$7, $I$3:$I$7)
VLOOKUP()的相等公式如下：F2=VLOOKUP(D2, $H$3:$I$7, 2, 0)
XLOOKUP因為已預設，所以不用再輸入「比對方式」 0 或 1，在公式輸入上，比VLOOKUP更精簡好懂。

跟XLOOKUP一樣實用的3個函數

以下介紹3個使用XLOOKUP可以效率加倍的函數公式。

1. UNIQUE：高效率擷取不重複資料
輸入公式：=UNIQUE(範圍)，就能抓取指定範圍當中唯一的不重複值。

2. SORT：排序陣列中的資料
輸入公式： =SORT(範圍,排序欄,遞增,[排序欄_2,遞增_2, ...])
如果原始資料是亂數，希望按照指定順序排列成表，便可運用SORT函數做遞增排序，升冪排序就填「True」或「1」、降冪就填「False」或「0」。

3. TRANSPOSE：水平及垂直旋轉儲存格
原本直式的表格，希望改用橫向表格來呈現時，就可以使用公式「=TRANSPOSE(相同數量儲存格)」，表格方向就會轉向。