Excel公式练习97:返回列表中第一个和最后一个出现的值之差

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社群,进行技术交流和提问,获取更多电子资料,并通过社群加入专门的微信讨论群,更方便交流。

(0)

相关推荐