常用Excel的人都听说过VLookup这个神奇的公式,但今天向大家介绍另一款更好用更安全的公式,那就是INDEX加MATCH组合。
1. 为什么比VLookup好用?
VLookup只能匹配查找范围的第一列,而返回的列号需要提前计算好。如果指定“列“的位置发生变化呢?比如添加或删除了一列;
或者如果你有十几个列需要用到VLookup呢?
这时用VLookup就会变得麻烦,而且返回列号容易出错。所以推荐使用INDEX加MATCH组合,它不仅解决了上述问题,而且可以匹配“行列”两个维度的数据。
2. VLookup为什么容易出错?
举个例子,上图是微博抽奖名单,我们随机选取中奖人,并希望找到中奖人对应的信息以发奖。我们用VLookup,其公式如下,注意col_index_num这个参数通常是我们手动输入的值,这个值并不会因为“列”的变化而变化,比如第一种情况,“性别”在查找区域的第2列,但是如果我插入了一列数,性别就不是第2列了,但是VLookup还是会按照第2列查找,所以就得不到我们想要的结果了。第二种情况也是同样的道理,因为col_index_num不会因为我们的拖拽而改变,所以VLookup无法找到我们想要的数值。
3. 如何用Match改善VLookup?
我们先来看一下MATCH函数。MATCH函数其实是找到数值在指定区域中的行号或者列号。我们首先利用MATCH函数找到“性别”在总表格的对应列号。输入
=MATCH(lookup_value, lookup_array, [match_type]) 得到2,即表示“性别”在第2列。这时我们再将这个MATCH函数带入VLookup就可以得到同样的结果。唯一的区别是,这个2不是我们手工输入的,而是MATCH函数帮我们找到的。此时对表格做添加或删除列的操作,或者拖拽公式覆盖,都不会影响VLookup的结果,因为MATCH函数会帮我们重新定位列号。
4. Index+Match什么时候更好用?
如果需要同时匹配两个维度的数据,就要使用INDEX加MATCH组合了,而VLookup无法实现。
继续上面的例子,如果我需要对中奖人的会员级别和会员时长进行划分,给予不同金额的优惠,那就要先用MATCH函数分别定位会员级别和时长所在的列和行,再用INDEX函数找到对应的返回值。INDEX的英文意思是“索引”,就像在坐标系中一样,两个MATCH函数分别告诉我们横坐标和纵坐标的位置,INDEX函数就可以定位到我们想找的数值。
5. 有没有动图演示呢?
当然有。学会了这个公式后,我们将它应用到实际操作中。
是不是很容易,并且更加自动化,更加安全了呢?希望这个公式教程对你有帮助。
2020-05-12
2631比VLOOKUP好用10倍,新函數 XLOOKUP 真的來了
- 43453
- 0
- EXCEL
比VLOOKUP好用10倍,新函數 XLOOKUP 真的來了
2631 | 比VLOOKUP好用10倍,新函數 XLOOKUP 真的來了 |
近期Excel世界爆發出一個超重磅新聞,微軟發佈了最新的XLOOKUP函數。
看它能實現的功能,估計你口水都要流出來了:
※從右向左查
※多條件查找
※從上向下查
※查找最後一個
.......
它不但是VLOOKUP函數接班人,就連LOOKUP、HLOOKUP函數恐將會退出Excel舞臺。
只是這個函數目前只有微軟Office 365版本才支援使用,正式發佈後在Excel的最新版本中將會增加這個函數。
【語法】
XLOOKUP(查找值,查找值範圍,傳回值範圍,如果找不到要顯示內容,匹配模式,查找模式)
參數共有6個,其實後面兩個參數皆可以省略,省略後默認為精確查找,這和VLOOKUP正好相反。
XLOOKUP函數省略這二個參數感覺這不就是LOOKUP函數的語法嗎。看上去是一樣,但其實是VLOOKUP、HLOOKUP和LOOKUP三個函數的合體。
語法愈看愈頭暈,我們通過幾個範例,來認識一下強大的XLOOKUP函數
【範例1】查找第1個符合條件的值
如果要在表格中依姓名(A11)查找其國語成績(B11),要如何做呢?
點取B11儲存格輸入公式:
=Xlookup(A11,A2:A8,C2:C8)
如果不省略第5,6個參數:(0表示精確查找,1表示從前向後查)
=Xlookup(A11,A2:A8,C2:C8,0,1)
VLOOKUP函數精確查找總要帶上最後一個參數Flase或0,用XLOOKUP就省事了。
【範例2】從右向左查
如果要在表格中依姓名(A11)查找其學號(B11),要如何做呢?
點取B11儲存格輸入公式:
=XLOOKUP(A11,B2:B8,A2:A8)
Index+Match組合要說886。
【範例3】按欄上下列查找
如果要在表格中依科目名稱(B5)查找其成績(B6),要如何做呢?
點取B6存格輸入公式:
=Xlookup(B5,A1:E1,A2:E2)
Hlookup,你這次要徹底被打入冷宮了!
【範例4】多條件查找
如果要在表格中依學號(A11)與姓名(B11)查找其國語(C11)成績,要如何做呢?
點取C11儲存格輸入公式:
=Xlookup(A11&B11,A2:A8&B2:B8,D2:D8)
多條件查找只要用&連接號即可,這也未免太簡單了吧!
【範例5】查找最後一個
如果要在表格中依產品(E2)查找其最後銷售量(F2),要如何做呢?
點取B6存格輸入公式:
=xlookup(E2,B2:B11,C2:C11,,0,-1)
當最後一個參數是-1時,XLOOKUP從後向前查符合條件的值,默認為1(從前向後查)
以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~
部落格相關範例
2021.10.15 | 2621Excel秘密功能-新活頁簿設定 |
2021.10.14 | 2620Excel秘密功能-最近使用的檔案清單 |
2021.10.13 | 2619Excel秘密功能-你的名字 |
2021.10.12 | 2618存檔時自動備份 |
2021.10.11 | 2617設定自動存檔時間 |
2021.10.10 | 2616更改預設的儲存位置 |
2021.10.09 | 2611日期運算-TEXT |
2021.10.08 | 2610日期計算-月、季、半年 |
2021.10.07 | 2609一次開啟多個檔案 |
2021.10.06 | 2608Excel以唯讀方式開啟檔案 |
2021.10.05 | 2607Excel開啟檔案為副本型式 |
2021.10.04 | 2604如何將公式簡化 |
2021.10.03 | 2603Excel不規則字串擷取 |
2021.10.02 | 2602Excel VBA選取範圍複製 |
2021.10.01 | 2601Excel函數問題?-SUMPRODUCT |
2021.09.30 | 2598Office 2019中在快速存取工具列中快速增加刪除按鈕的方法 |
2021.09.29 | 2589Excel如何將重複數值資料剔除-UNIQUE函數 |
2021.09.28 | 2577Excel 數字出現統計問題-OFFICE 365版 |