常用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 真的來了
比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(從前向後查) 以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~ 部落格相關範例
|