excelperfect
有時候，用戶組織的數(shù)據(jù)不便于Excel的操作，讓我們不得不費一番周折才能得出想要的結(jié)果。
這里是chandoo.org上的一個示例，日期不是放在同一行或同一列中，而是間隔放在不同行中，并且要根據(jù)指定的日期查找對應(yīng)的顧客數(shù)，如下圖1所示。
圖1
數(shù)據(jù)區(qū)域位于單元格區(qū)域B3:H14，奇數(shù)行包含日期，偶數(shù)行包含顧客數(shù)，要查找的日期位于單元格L4中。
可以在單元格L5中輸入公式：
=SUMIFS(B4:H14,B3:H13,L4)
這個公式有幾點注意：
1.它只適用于查找數(shù)字。
2.如果任何一個數(shù)字與我們正在查找的日期相同，那么它就不起作用。例如，2014年10月15日是41927，如果有一個數(shù)據(jù)值也是41927，那么這個SUMIFS公式的結(jié)果將是錯誤的。
下面是其他一些公式。
=SUMPRODUCT(((NOT(ISERROR(SEARCH(L4,B3:H13))))*1),(B4:H14))
使用SEARCH函數(shù)查找值，得到包含TRUE/FALSE的數(shù)組，乘以1轉(zhuǎn)換成包含1/0的數(shù)組，然后與相應(yīng)單元格區(qū)域相乘，得到一人包含找到的值與0的數(shù)組，求和即得結(jié)果。
一個數(shù)組公式：
=SMALL(IF(MMULT((L4=B3:H13)*IF(ISNUMBER(B4:H14),B4:H14),{1;1;1;1;1;1;1}),MMULT((L4=B3:H13)*IF(ISNUMBER(B4:H14),B4:H14),{1;1;1;1;1;1;1})),1)
另一個數(shù)組公式：
=SUM(IF(MOD(ROW(B3:H13),2)=MOD(ROW(B3),2),IF(B3:H13=L4,B4:H14)))
下面的公式也可以得到正確結(jié)果：
=INDEX($B$3:$H$14,ROUNDDOWN((L4-B3)/7,0)*2+2,MOD((L4-B3),7)+1)
或者：
=OFFSET(B4,QUOTIENT(L4-B3,7)*2,MOD(L4-B3,7))
也可以使用下面的公式：
=SUMPRODUCT((ISEVEN(ROW(B3:H13)-MIN(ROW(B3:H13)))*(B3:H13=L4)*B4:H14))
或者：
=SUMPRODUCT((ISEVEN(ROW(B3:H13)-MIN(ROW(B3:H13)))*(B3:H13=L4)*OFFSET(B3:H13,1,,,)))
下面是一個相對復(fù)雜的公式：
=INDEX(B3:H14,SUMPRODUCT((B3:H14=L4)*(ROW(B3:H14)-ROW(B3)+1)*ISODD(ROW(B3:H14)-ROW(B3)+1))+1,SUMPRODUCT((B3:H14=L4)*(COLUMN(B3:H14)-COLUMN(B3)+1)*ISODD(ROW(B3:H14)-ROW(B3)+1)))
還可以使用下面的數(shù)組公式：
=OFFSET(A1,SUM((B3:H13=L4)*ROW((B3:H13))),SUM((B3:H13=L4)*COLUMN((B3:H13)))-1)
或者更復(fù)雜一點的數(shù)組公式：
=INDEX(B3:H14,MATCH(1,MMULT(--(B3:H14=L4),TRANSPOSE(COLUMN(B3:H14)^0)),0)+1,MATCH(1,MMULT(TRANSPOSE(--(B3:H14=L4)),ROW(B3:H14)^0),0))
最后，來一個簡單的：
=OFFSET(B3,ROUNDUP((L4-41911+1)/7,0)*2-1,MOD(L4-41911,7))
小結(jié)：
解決同樣的問題，會有不同的方法，對于Excel公式來說更是如此。我們可以使用不同的函數(shù)，或者不同的思路，來編寫公式。
平時學(xué)習(xí)過程中，不僅僅只滿足于解決問題，還要從他人解決問題的公式中學(xué)習(xí)思路和函數(shù)的運用，這樣可以接觸到多種多樣的解決方法，熟悉很多函數(shù)，對于Excel公式技術(shù)的提升會有很大的好處。
注：可以在知識星球完美Excel社群中下載本文配套示例工作簿。
歡迎到知識星球：完美Excel社群，進行技術(shù)交流和提問，獲取更多電子資料，并通過社群加入專門的微信討論群，更方便交流。
聯(lián)系客服
