在做多條件匹配作業(yè)的時候發(fā)現(xiàn)了很多坑。多虧了幾位圈友的指點(diǎn)，加上參考了小蚊子老師的博客，總結(jié)成了這篇文章和大家分享6種方法和其中的思路。文中一些概念是我自己的話總結(jié)的，可能并不嚴(yán)謹(jǐn)，如果看不懂，詳細(xì)原理還是百度吧-。=
先從VLOOKUP講起。用字段合并的方法，把多條件變?yōu)閱我粭l件，如下圖，把公司、部門合在一起。查找A:E區(qū)域，參考到第4行即可。注：輔助列加在哪都行，道理一樣。
前面的方法需要輔助列，如果不用輔助列，那么需要在函數(shù)中將A列B列進(jìn)行合并，簡單&的合并實(shí)際操作中批量會出現(xiàn)#N/A的結(jié)果，所以需要數(shù)組計算。函數(shù)的編寫見上圖。函數(shù)寫完，最后不是按Enter，而是Shift Ctrl Enter，這樣就是數(shù)組計算了。最后可以看到，函數(shù)最外面有個大括號“{}”。這個VLOOKUP函數(shù)中嵌套了一個IF函數(shù)。
【什么是IF({1，0},...)？】IF函數(shù)，1就是true，0就是false。與后面的真值和假值相對應(yīng)。而{1，0}是數(shù)組，就是把真值、假值分別計算，1返回的值是A2：A25。0返回的值是D2：D25。兩個結(jié)果結(jié)合成一個數(shù)組，也就是這里面的VLOOKUP的數(shù)據(jù)表區(qū)域。
LOOKUP法的邏輯就是找到唯一對的那個值，在此借用“挫人”的解釋會更清晰：
LOOKUP(lookup_value,lookup_vector,[result_vector])
當(dāng)Lookup的Lookup Value永遠(yuǎn)大于lookup vector時，返回最后一個小于lookup vector對應(yīng)的result
=LOOKUP(1,0/((A2:A3=G2)*(B2:B3=H2)),C2:C3)
lookup_value為1lookup_vector,為0/邏輯值
邏輯值(A2:A3=G2)*(B2:B3=H2)結(jié)果有兩種，TRUE和FALSE。在公式計算中：TRUE看作是1，F(xiàn)ALSE看做0
上面公式就變成了0/({TRUE;TRUE}*{TRUE;FALSE})-->0/{1;0}-->{0/1;0/0}-->{0;#DIV/0!}
整個公式就變成了=LOOKUP(1,{0;#DIV/0!},{100;200})因?yàn)?/0-->#DIV/0!為錯誤值，而LOOKUP要找的，是非錯誤值。所以，第二參數(shù)只有0，0<1,0對應(yīng)的result是100，所以最后的值是100
P.S.除了1,0/……，還可以寫2,1或者3,2或者100,0只要第一參數(shù)永遠(yuǎn)大于第二參數(shù)中的值就可以^_^
SUM是求和，SUMIFS是對條件指定的單元格求和，利用了條件篩選功能。求和區(qū)域就是要利用里面的值求和，當(dāng)只有一個加數(shù)時，這個值就是我們想要的值。我們想知道員工數(shù)，就選C:C。然后是條件篩選，先選第一條件區(qū)域比如公司A:A，在選條件也就是對應(yīng)的G2。后面的部門也是如此。這樣就通過條件篩選出了唯一值。
SUMPRODUCT是數(shù)組或各區(qū)域的乘積之和，這里用的不是乘積和而是多條件屬性=SUMPRODUCT((條件1)*(條件2)*...(條件n))。同時滿足多條件，返值。
利用高級篩選復(fù)制到別的區(qū)域，缺點(diǎn)是需要手動更新。
后面3種方法是在小蚊子老師博客里找到的，這里給出簡單的原理，具體操作請看博客里面有動圖。同時，感謝“挫人”、“沒臉的小白臉”、“Starnight”、“米果”的指點(diǎn)和建議。
以上就是總結(jié)的各種方法，有些地方表達(dá)不清，如果看不懂請百度。希望能幫到大家。
聯(lián)系客服
