【原】34個公式可以查詢萬千條數(shù)據(jù)，但這1個函數(shù)就能滿足你工作所需

Excel不加班 2019-12-26

展開全文

圖為我的老家風景

想當年我也牛逼過，數(shù)理化的那些東西對我來說就是小菜一碟。

做數(shù)學三角函數(shù)

知道物理牛頓的三大定律

背誦化學元素周期表

都說學好數(shù)理化，走遍天下也不怕。

然而

現(xiàn)在除了會玩Excel

我什么都不會

……

這是我6年前的論壇精（裝）華（逼）帖子，花了1個晚上整理出來。

問題：根據(jù)左邊的數(shù)據(jù)源，查找每個人的隨機人緣。

查找文本首選VLOOKUP函數(shù)為首的查找引用家族，查找數(shù)值首選SUM函數(shù)為首的求和家族。

你沒看錯，SUM函數(shù)為首?。?！因為查找不到數(shù)據(jù)顯示0，而用VLOOKUP函數(shù)查找不到顯示錯誤值。

1、SUM函數(shù)家族

常規(guī)公式。

=SUMIF($A$2:$A$7,D2,$B$2)
=SUMPRODUCT(($A$2:$A$7=D2)*$B$2:$B$7)
=SUMPRODUCT(--($A$2:$A$7=D2),$B$2:$B$7)

當年還沒接觸到SUMIFS函數(shù)，要不然就又多了一種。

數(shù)組公式，需要按Ctrl+Shift+Enter三鍵結束。

=SUM(($A$2:$A$7=D2)*$B$2:$B$7)
=MMULT(--(TRANSPOSE($A$2:$A$7)=D2),$B$2:$B$7)
=MAXA(($A$2:$A$7=D2)*$B$2:$B$7)
=MAX(($A$2:$A$7=D2)*$B$2:$B$7)
=MINA(IF($A$2:$A$7=D2,$B$2:$B$7))
=MIN(IF($A$2:$A$7=D2,$B$2:$B$7))
=AVERAGEA(IF($A$2:$A$7=D2,$B$2:$B$7))
=AVERAGE(IF($A$2:$A$7=D2,$B$2:$B$7))
=PRODUCT(IF($A$2:$A$7=D2,$B$2:$B$7))
=MEDIAN(IF($A$2:$A$7=D2,$B$2:$B$7))
=LARGE(IF($A$2:$A$7=D2,$B$2:$B$7),1)
=SMALL(IF($A$2:$A$7=D2,$B$2:$B$7),1)

說句心理話，里面除了第一個，都是在裝逼。

2、VLOOKUP函數(shù)家族

常規(guī)公式。

=VLOOKUP(D2,$A$2:$B$7,2,)
=LOOKUP(1,0/($A$2:$A$7=D2),$B$2:$B$7)
=INDEX($B$2:$B$7,MATCH(D2,$A$2:$A$7,))
=CHOOSE(MATCH(D2,$A$2:$A$7,),$B$2,$B$3,$B$4,$B$5,$B$6,$B$7)
=INDIRECT("B"&MATCH(D2,$A$1:$A$7,))
=INDIRECT("R"&MATCH(D2,$A$1:$A$7,)&"C2",)
=OFFSET($B$1,MATCH(D2,$A$2:$A$7,),)
=VLOOKUP(D2,IF({0,1},$B$2:$B$7,$A$2:$A$7),2,)
=VLOOKUP(D2,CHOOSE({2,1},$B$2:$B$7,$A$2:$A$7),2,)
=INDIRECT(ADDRESS(MATCH(D2,$A$1:$A$7,),2,4))

數(shù)組公式，需要按Ctrl+Shift+Enter三鍵結束。

=HLOOKUP(D2,TRANSPOSE($A$2:$B$7),2,)
=INDEX($B$2:$B$7,MATCH(1,0/($A$2:$A$7=D2)))
=OFFSET($B$1,MATCH(1,0/($A$2:$A$7=D2)),)
=INDIRECT("R"&MATCH(1,0/($A$1:$A$7=D2))&"C2",)
=INDIRECT("B"&MATCH(1,0/($A$1:$A$7=D2)))
=INDEX(B:B,SMALL(IF($A$2:$A$7=D2,ROW($A$2:$A$7),4^8),1))
=INDEX($B$1:$B$7,MAX(IF($A$2:$A$7=D2,ROW($A$2:$A$7),0)))

IF函數(shù)也來湊熱鬧。