奇怪的查找
excelperfect
有时候，用户组织的数据不便于Excel的操作，让我们不得不费一番周折才能得出想要的结果。
这里是chandoo.org上的一个示例，日期不是放在同一行或同一列中，而是间隔放在不同行中，并且要根据指定的日期查找对应的顾客数，如下图1所示。
图1
数据区域位于单元格区域B3:H14，奇数行包含日期，偶数行包含顾客数，要查找的日期位于单元格L4中。
可以在单元格L5中输入公式：
=SUMIFS(B4:H14,B3:H13,L4)
这个公式有几点注意：
1.它只适用于查找数字。
2.如果任何一个数字与我们正在查找的日期相同，那么它就不起作用。例如，2014年10月15日是41927，如果有一个数据值也是41927，那么这个SUMIFS公式的结果将是错误的。
下面是其他一些公式。
=SUMPRODUCT(((NOT(ISERROR(SEARCH(L4,B3:H13))))*1),(B4:H14))
使用SEARCH函数查找值，得到包含TRUE/FALSE的数组，乘以1转换成包含1/0的数组，然后与相应单元格区域相乘，得到一人包含找到的值与0的数组，求和即得结果。
一个数组公式：
=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)
另一个数组公式：
=SUM(IF(MOD(ROW(B3:H13),2)=MOD(ROW(B3),2),IF(B3:H13=L4,B4:H14)))
下面的公式也可以得到正确结果：
=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,,,)))
下面是一个相对复杂的公式：
=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)))
还可以使用下面的数组公式：
=OFFSET(A1,SUM((B3:H13=L4)*ROW((B3:H13))),SUM((B3:H13=L4)*COLUMN((B3:H13)))-1)
或者更复杂一点的数组公式：
=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))
小结：
解决同样的问题，会有不同的方法，对于Excel公式来说更是如此。我们可以使用不同的函数，或者不同的思路，来编写公式。
平时学习过程中，不仅仅只满足于解决问题，还要从他人解决问题的公式中学习思路和函数的运用，这样可以接触到多种多样的解决方法，熟悉很多函数，对于Excel公式技术的提升会有很大的好处。
注：可以在知识星球完美Excel社群中下载本文配套示例工作簿。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。