Question to say "I can!"

INDEX+SMALL函數用法

2014-09-26

先說明這不是我的原創,是別人的貼子,覺得有用也就保存了下來。

=INDEX(結果列,SMALL(IF(條件,ROW滿足條件的行號,較大的空行行號),ROW(1:1)))&""

【原理】1、數組運算;2、空單元格與空文本合并。
【特點】得到的結果是文本。
【實例】如圖:篩選張三的領用記錄(條件是E1單元格的“張三”)。?

1.
=INDEX(B:B,SMALL(IF(A$2:A$8=E$1,ROW(A$2:A$8),4^8),ROW(1:1)))&""

復制公式或代碼

【解析】:對照“必殺技”,多數人已可以瞧出一點端倪來。
1、條件模塊:IF(A$2:A$8=E$1,ROW(A$2:A$8),4^8)
表示如果A2:A8的姓名等于E1的“張三”,則返回A2:A8的行號,否則返回4^8,即65536。
IF(如果,則,否則——這語言不難讀吧
條件模塊經過數組運算,因為A2、A5、A8滿足條件,所以返回的是行號數組{2;65536;65536;5;65536;65536;8}。

2、排序模塊:SMALL(IF,ROW(1:1))
公式的第1行,ROW(1:1)返回{1},在第2行返回{2}……因此,利用SMALL+ROW可以將條件模塊返回的行號數組從小到大依次排序得出。當然,此例中SMALL({2;65536;65536;5;65536;65536;8},1)得到的是2,第2小的是5,第3小的是8,第4小及以后都是65536。

3、引用模塊:INDEX(引用列,SMALL得到的行號)
=INDEX(B:B,2)——引用B2,=INDEX(B:B,65536)——引用B65536

4、容錯模塊:&""
當公式到了第4行,3個滿足條件的記錄都已經找出來,此時公式是=INDEX(B:B,65536)&“”
因為Excel2003的最大行數是65536行,而在這一行中,一般不會有人輸入數據,是空單元格。因此,利用空單元格與空文本合并返回空文本的特性,將超出結果數量的部分不顯示出來。

作者:admin | Categories:經典收藏 | Tags:

發表評論

電子郵件地址不會被公開。 必填項已用*標注

*

澳洲f1赛车b盘开奖套路 平特一肖下载 北京快3真准网 陕西十一选五任三遗漏 股票指数平台 湖北30选5开奖视频 什么叫上证指数和深证成指 湖北十一选五专家推荐号 股市行情分析软件 什么可以才能了解腾讯分分彩 上海股票指数 体彩排列3投注计划 上市公司股票质押查 加拿大快乐8开奖结果怎么查 湖北30选5开奖号 彩票山东群英会玩法 陕西11选5走势图手机版