題目：如下圖所示的表中，要求根據(jù)“客戶+型號”查找左側(cè)的數(shù)量。結(jié)果如黃色單元格所示。
練習(xí)數(shù)據(jù)可以自行模擬后驗(yàn)證下列公式。
公式：（個(gè)別公式只適用于查找結(jié)果為數(shù)字的情況）
=VLOOKUP(H3,IF({1,0},D$3:D$14,C$3:C$14),2,)
=INDEX(C:C,MATCH(H3,D:D,))
=OFFSET(C$1,MATCH(H3,D:D,)-1,)
=INDIRECT('c'&MATCH(H3,D:D,))
=LOOKUP(,0/(D:D=H3),C:C)
=DSUM(C$2:D$14,1,H$2:H3)-SUM(N$2:N2)
=INDEX(C:C,MAX(IFERROR(IF(FIND(H3,$D$3:$D$14),ROW($D$3:$D$14)),)))
=SUM((D$3:D$14=H3)*C$3:C$14)
=SUMPRODUCT((D$3:D$14=H3)*C$3:C$14)
=SUMIF(D:D,H3,C$1)
=SUM(IF(H3=D$3:D$14,C$3:C$14))
=HLOOKUP(C$2,$2:$14,MATCH(H3,D:D,)-1,)
=VLOOKUP(H3,CHOOSE({1,2},D$3:D$14,C$3:C$14),2,)
=MMULT(TRANSPOSE(--(D$3:D$14=H3)),C$3:C$14)
=SUMIFS(C$3:C$14,D$3:D$14,H3)
=C:C OFFSET($1:$1,MATCH(H3,D:D,)-1,)
=SUBTOTAL(9,OFFSET(C$1,MATCH(H3,D:D,)-1,))
=INDEX(C:C,MATCH(1=1,H3=D:D,))
=VLOOKUP(H3,TEXT({1,-1},LEFT(D$3:D$14,FIND('-',D$3:D$14))&'!'&MID(D$3:D$14,FIND('-',D$3:D$14)+1,1)&SUBSTITUTE(MID(D$3:D$14,FIND('-',D$3:D$14)+2,9),0,'\0')&';'&SUBSTITUTE(C$3:C$14,0,'\0')),2,)
=INDIRECT('r'&MATCH(H3,D:D,)&'c3',)
=INDIRECT(ADDRESS(MATCH(H3,D:D,),3))
=OFFSET(C$2,VLOOKUP(H3,IF({1,0},D$3:D$14,ROW($1:$12)),2,),)
=MIN(IF(D$3:D$14=H3,C$3:C$14))
=SMALL(IF(D$3:D$14=H3,C$3:C$14,9^9),1)
=MAX(IF(D$3:D$14=H3,C$3:C$14,))
=C:C INDEX($1:$14,MATCH(H3,D:D,),)
=C:C INDIRECT(MATCH(H3,D:D,)&':'&MATCH(H3,D:D,))
=MAX((D$3:D$14=H3)*C$3:C$14)
=LARGE((D$3:D$14=H3)*C$3:C$14,1)
=SMALL((D$3:D$14=H3)*C$3:C$14,ROWS(3:14))
=SMALL((D$3:D$14=H3)*C$3:C$14,COUNT(C:C))
=MAXA((D$3:D$14=H3)*C$3:C$14)
=PRODUCT(IF(D$3:D$14=H3,C$3:C$14))
=OFFSET(INDIRECT('d'&MATCH(H3,D:D,)),,-1)
=HLOOKUP(H3,IF({1;0},TRANSPOSE(D$3:D$14),TRANSPOSE(C$3:C$14)),2,0)
=SUBTOTAL(5,OFFSET(C$1,MATCH(H3,D:D,)-1,))
=MAX(IFERROR(FIND(H3,D$3:D$14),)*C$3:C$14)
=SUM(IF(COUNTIF(H3,D$3:D$14),C$3:C$14))
=MAX(COUNTIF(H3,D$3:D$14)*C$3:C$14)
=MAXA(COUNTIF(H3,D$3:D$14)*C$3:C$14)
=SUM(COUNTIF(H3,D$3:D$14)*C$3:C$14)
=SUMPRODUCT(COUNTIF(H3,D$3:D$14)*C$3:C$14)
=INDEX(C:C,MAX(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDEX(C:C,SUM(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDEX(C:C,MAXA(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=OFFSET(C$1,SUM(COUNTIF(H3,D$3:D$14)*ROW($3:$14))-1,)
=OFFSET(C$1,SUMPRODUCT(COUNTIF(H3,D$3:D$14)*ROW($3:$14))-1,)
=OFFSET(C$1,MAX(COUNTIF(H3,D$3:D$14)*ROW($3:$14))-1,)
=OFFSET(C$1,MAXA(COUNTIF(H3,D$3:D$14)*ROW($3:$14))-1,)
=INDIRECT('C'&SUM(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDIRECT('C'&MAX(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDIRECT('C'&SUMPRODUCT(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDIRECT('C'&MAXA(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDEX(C:C,MATCH(1,COUNTIF(H3,D$1:D$14),))
=INDEX(C:C,LOOKUP(99,1/COUNTIF(H3,D$1:D$14)*ROW($1:$14)))
=OFFSET(C$1,LOOKUP(99,1/COUNTIF(H3,D$1:D$14)*ROW($1:$14))-1,)
=INDIRECT('c'&LOOKUP(99,1/COUNTIF(H3,D$1:D$14)*ROW($1:$14)))
=PRODUCT(IF(COUNTIF(H3,D$3:D$14),C$3:C$14))
=PRODUCT(IF(IFERROR(SEARCH(H3,D$3:D$14),),C$3:C$14))
=PRODUCT(IF(ISNUMBER(SEARCH(H3,D$3:D$14)),C$3:C$14))
=AVERAGEIF(D:D,H3,C:C)
=CHOOSE(MATCH(H3,D$3:D$14,),C$3,C$4,C$5,C$6,C$7,C$8,C$9,C$10,C$11,C$12,C$13,C$14)
=MAX(--TEXT((D$3:D$14=H3)*C$3:C$14,'0;!0'))
=MAX(IFERROR(FREQUENCY(IF(D$3:D$14=H3,ROW($1:$12),13),ROW($1:$12))*C$3:C$14,))
=MMULT(COLUMN(A:L)^0,C$3:C$14*(D$3:D$14=H3))
=HLOOKUP(H3,TRANSPOSE(IF({1,0},D$3:D$14,C$3:C$14)),2,)
=MEDIAN(IF(D$3:D$14=H3,C$3:C$14))
=PRODUCT(IF(COUNTIFS(H3,D$3:D$14),C$3:C$14))
=MINA(IF(D$3:D$14=H3,C$3:C$14))
=AVERAGE(IF(D$3:D$14=H3,C$3:C$14))
=AVERAGEA(IF(D$3:D$14=H3,C$3:C$14))
=DAVERAGE(C$2:D$14,1,H$2:H3)*ROW(A1)-SUM(CB$2:CB2)
對于大多數(shù)人來說，能有一種方法解決問題就足夠了，但是對于那些公式函數(shù)玩的很溜的人，總是希望能夠用盡可能多的的公式來解決同一個(gè)問題，對別人來說是燒腦，對他們來說是干題，并且樂此不疲。
如果真的要把Excel用好，尤其是公式函數(shù)這部分，多燒燒腦肯定沒壞處。即便自己能夠想出來很有限的方法，哪怕只有兩種，那也是一大進(jìn)步，多看看其他人的思路，看多了自己的思路也會(huì)開拓很多。
如果你也想試試干題的樂趣，推薦一個(gè)地方：
