[已解决]请教：INDEX(Sheet1!D:D,SMALL(IF((Sheet1!$G$2:$G$65536=1)*(Sheet1!$H$2:$H$6553...

andyliang

请教一下各位高手：这个函数INDEX(Sheet1!D:D,SMALL(IF((Sheet1!$G$2:$G$65536=1)*(Sheet1!$H$2:$H$65536=1),ROW($H$2:$H$65536),8^8),ROW(D2)))  
这个函数中各部分的意思是啥

最佳答案

月排行榜 / 总排行榜

心正意诚身修

2013-7-27 10:45

首先說這個公式是一個數組公式，在我看來它是為了彌補VLOOKUP的不足，因為VLOOKUP只能返回符合條件的第一個值，這個公式能返回所有附和條件的值。
请教一下各位高手：这个函数INDEX(Sheet1!D:D,SMALL(IF((Sheet1!$G$2:$G$65536=1)*(Sheet1!$H$2:$H$65536=1),ROW($H$2:$H$65536),8^8),ROW(D2)))  
这个函数中各部分的意思是啥
先說主體。INDEX(D:D,。。。）這是函數的主體。意思是說返回D列的值，但究竟是哪些值呢，就是後面這一部分計算的結果。如果G列的值等於1,同時H2列的值也等於1,只要滿足這兩個條件，那麼就返回這個行號，如果不能同時滿足這兩個條件。那麼就返回行號8的8次方，也就是最後一行的行號。一般來說都是空值了。然後呢，把這些條件符合條件的行號放在一起，用SAMLL來依次從小到大來顯示。最終的結果就是我前面說的，把如果H2和G2都等於1的話，那麼就依次顯示對應D列的值。我說得挺詳細的了。給最佳答案哈。有哪兒不明白。我再繼續說。

跳转到最佳答案楼层

huangcaiguang

你将函数拆分为几个函数、看各自的解释F1

andyliang

huangcaiguang 发表于 2013-7-27 10:38
你将函数拆分为几个函数、看各自的解释F1

各部分都能看懂，组合起来的逻辑看不懂，比如那个8^8是什么意思？

huangcaiguang

andyliang 发表于 2013-7-27 10:41
各部分都能看懂，组合起来的逻辑看不懂，比如那个8^8是什么意思？

8的8次方

心正意诚身修

首先說這個公式是一個數組公式，在我看來它是為了彌補VLOOKUP的不足，因為VLOOKUP只能返回符合條件的第一個值，這個公式能返回所有附和條件的值。
请教一下各位高手：这个函数INDEX(Sheet1!D:D,SMALL(IF((Sheet1!$G$2:$G$65536=1)*(Sheet1!$H$2:$H$65536=1),ROW($H$2:$H$65536),8^8),ROW(D2)))  
这个函数中各部分的意思是啥
先說主體。INDEX(D:D,。。。）這是函數的主體。意思是說返回D列的值，但究竟是哪些值呢，就是後面這一部分計算的結果。如果G列的值等於1,同時H2列的值也等於1,只要滿足這兩個條件，那麼就返回這個行號，如果不能同時滿足這兩個條件。那麼就返回行號8的8次方，也就是最後一行的行號。一般來說都是空值了。然後呢，把這些條件符合條件的行號放在一起，用SAMLL來依次從小到大來顯示。最終的結果就是我前面說的，把如果H2和G2都等於1的話，那麼就依次顯示對應D列的值。我說得挺詳細的了。給最佳答案哈。有哪兒不明白。我再繼續說。

youfang

INDEX(Sheet1!D:D,SMALL(IF((Sheet1!$G$2:$G$65536=1)*(Sheet1!$H$2:$H$65536=1),ROW($H$2:$H$65536),8^8),ROW(D2)))  
IF((Sheet1!$G$2:$G$65536=1)*(Sheet1!$H$2:$H$65536=1),ROW($H$2:$H$65536),8^8)，如果G列的某个单元格=1，并且它对应的H列那个单元格也=1，就输出它所在行号，否则输出8^8（一个很大的值）
SMALL(……,ROW(D2))，提取上一步公式得到的结果第2小值（是一个行号）
INDEX(Sheet1!D:D,……) 引用D列的第几行
希望你明白了

andyliang

心正意诚身修 发表于 2013-7-27 10:45
首先說這個公式是一個數組公式，在我看來它是為了彌補VLOOKUP的不足，因為VLOOKUP只能返回符合條件的第一個 ...

首先說這個公式是一個數組公式，在我看來它是為了彌補VLOOKUP的不足，因為VLOOKUP只能返回符合條件的第一個值，這個公式能返回所有附和條件的值。
请教一下各位高手：这个函数INDEX(Sheet1!D:D,SMALL(IF((Sheet1!$G$2:$G$65536=1)*(Sheet1!$H$2:$H$65536=1),ROW($H$2:$H$65536),8^8),ROW(D2)))  
这个函数中各部分的意思是啥
先說主體。INDEX(D:D,。。。）這是函數的主體。意思是說返回D列的值，但究竟是哪些值呢，就是後面這一部分計算的結果。如果G列的值等於1,同時H2列的值也等於1,只要滿足這兩個條件，那麼就返回這個行號，如果不能同時滿足這兩個條件。那麼就返回行號8的8次方，也就是最後一行的行號。一般來說都是空值了。然後呢，把這些條件符合條件的行號放在一起，用SAMLL來依次從小到大來顯示。最終的結果就是我前面說的，把如果H2和G2都等於1的話，那麼就依次顯示對應D列的值。我說得挺詳細的了。給最佳答案哈。有哪兒不明白。我再繼續說。

谢谢你，说的比较详细，有个问题还是没有明白：
sheet1中G列，H列同时为1时，返回的是ROW($H$2:$H$65536)，这是绝对引用，我试了一下返回的是2，也就是当前sheet的第二行，按照这个逻辑，这个函数的功能是当符合条件是返回sheet1中D2的值到当前sheet中的D2，这个视乎和函数体现出来的结果不太一样。请帮忙解答一下

心正意诚身修

他返回的是一組數。如果如果滿足條件，那麼返回的可能是從2到65536之間所有的數。有多少滿足條件，它就可以返回多少個數。ROW()是返回單元格的行號。h2:h65536之間有多少行，它就可以返回多少行。

心正意诚身修

要在編輯狀態下同時按CTRL+SHIFT+ENTER才能進行數組運算。另外如果你只選中一個單元格，那麼只有一個格子放結果，結果當然也只能是2了，這和你設置公式的區域也有關係。如果你要運行 {＝ROW($H$2:$h$65536)}  你要一次性選擇一個區域之後再設公式。再按CTRL+SHIFT+ENTER。

andyliang

心正意诚身修 发表于 2013-7-27 15:21
他返回的是一組數。如果如果滿足條件，那麼返回的可能是從2到65536之間所有的數。有多少滿足條件，它就可以 ...

即使是数组，那也应该是ROW(sheet1$H$2:sheet1$H$65536),而不是ROW($H$2:$H$65536)