excelperfect
引言:本文的练习整理自chandoo.org。多一些练习,想想自己怎么解决问题,看看别人又是怎么解决的,能够快速提高Excel公式编写水平。
本次的练习是:示例数据如下图1所示,列A包含以随机顺序输入的客户名称,列B包含其对应的数量。单元格D1中是要查找的客户名,单元格E1中是一个公式,其返回值是在列A中查找D1中的客户名,将找到的第一个对应的数量与最后一个对应的数量之差值。例如,示例中最后一个Oracle对应的数量是100,第一个对应的数量是88,因此其差值=100-88=12。
图1
不应该使用任何辅助单元格、中间公式或者VBA。
写下你的公式。
解决方案
下面列出一系列公式,有兴趣的朋友可以逐个研究。
公式1:
=ABS(VLOOKUP(D1,A:B,2,)-LOOKUP(9^9,1/(A:A=D1),B:B))
公式2:
数组公式:
=ABS(VLOOKUP(D1,A:B,2,)-LOOKUP(9.9E+307,IF(A:A=D1,B:B)))
公式3:
=ABS(VLOOKUP(D1,A:B,2,)-LOOKUP(1,N(A2:A30=D1),B2:B30))
公式4:
数组公式:
=INDEX(B:B,MAX((A:A=D1)*ROW(A:A)))-VLOOKUP(D1,A:B,2,0)
公式5:
数组公式:
=SUM(N(OFFSET(B1,MATCH({1,2},1/(A2:A30=D1),{-1,1}),))*{-1,1})
公式6:
数组公式:
=ABS(SUM(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,))*{1,-1}))
公式7:
数组公式:
=ABS(SUM(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,0))*{1,-1}))
公式8:
数组公式:
=ABS(SUM(N(INDIRECT('B'&MATCH({1,2},1/(A:A=D1),{0,1})))*{1,-1}))
公式9:
数组公式:
=ABS(SUM(N(OFFSET(B1,MATCH({1,1},N(A2:A30=D1),{0,1}),))*{1,-1}))
公式10:
数组公式:
=ABS(MMULT(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,0)),{1;-1}))
公式11:
数组公式:
=ABS(SUM(LOOKUP(MATCH({1,2},1/(A:A=D1),{0,1}),ROW(A:A),B:B)*{1,-1}))
公式12:
数组公式:
=ABS(SUM(N(INDIRECT(ADDRESS(MATCH({1,2},1/(A:A=D1),{0,1}),2)))*{1,-1}))
公式13:
数组公式:
=SUM(SMALL(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,)),{2,1})*{1,-1})
=SUM(SMALL(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,0)),{2,1})*{1,-1})
公式14:
数组公式:
=ABS(INDEX(B:B,MATCH(1,IF(A:A=D1,1),))-INDEX(B:B,MATCH(2,1/IF(A:A=D1,1))))
公式15:
数组公式:
=ABS(SUM(N(INDIRECT('R'&MATCH({1,2},1/(A:A=D1),{0,1})&'C2',FALSE))*{1,-1}
公式16:
数组公式:
=ABS(INDEX(B2:B30,MATCH(1,IF(A2:A30=D1,1),))-INDEX(B2:B30,MATCH(2,1/IF(A2:A30=D1,1))))
公式17:
数组公式:
=ABS(SUM(N(OFFSET(B1,CHOOSE({1,2},MATCH(1,N(A:A=D1),),MATCH(2,1/(A:A=D1)))-1,0))*{1,-1}))
公式18:
数组公式:
=ABS(SUM(N(OFFSET(B1,SMALL(IF(A2:A30=D1,ROW(A2:A30)),CHOOSE({2,1},1,COUNTIF(A:A,D1)))-1,0))*{1,-1}))
公式19:
数组公式:
=ABS(SUM(ROUND(MOD(SMALL(IF(A2:A30=D1,ROW(A2:A30)+B2:B30%%),CHOOSE({1,2},1,COUNTIF(A:A,D1))),1)*10^4,0)*{1,-1}))
公式20:
=ABS(LOOKUP(9^9,SEARCH(D1,Client),Amt)-VLOOKUP(D1,Data,2,0))
其中,单元格区域A2:A30命名为“Client”,单元格区域B2:B30命名为“Amt”,单元格区域A2:B30命名为“Data”。
可以通过F9键或者公式求值功能,加深对上述公式的理解。
一个问题,20个解决公式,好好理解,一定会有所进步。
欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。
欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料,并通过社群加入专门的微信讨论群,更方便交流。
联系客服