excel 表格lookup 的操作

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

这个实现语句如下，（写在2行然后向下拖动）（2007或以上版本）：

 =

 IF(OR($D2<>,ISBLANK($H2),$G2=""),

  "",

  IF(OR($G2="",$G2=""),

   IF($H2>,,-)*SUMIFS(INDIRECT("E"&IFERROR(LOOKUP(,/(($H$:INDIRECT("H"&ROW()-)<>"")*($G$:INDIRECT("G"&ROW()-)=$G2)),ROW($:))+,ROW($:$))):$E2,INDIRECT("A"&IFERROR(LOOKUP(,/(($H$:INDIRECT("H"&ROW()-)<>"")*($G$:INDIRECT("G"&ROW()-)=$G2)),ROW($:))+,ROW($:$))):$A2,$A2,INDIRECT("B"&IFERROR(LOOKUP(,/(($H$:INDIRECT("H"&ROW()-)<>"")*($G$:INDIRECT("G"&ROW()-)=$G2)),ROW($:))+,ROW($:$))):$B2,$B2,INDIRECT("D"&IFERROR(LOOKUP(,/(($H$:INDIRECT("H"&ROW()-)<>"")*($G$:INDIRECT("G"&ROW()-)=$G2)),ROW($:))+,ROW($:$))):$D2,,INDIRECT("G"&IFERROR(LOOKUP(,/(($H$:INDIRECT("H"&ROW()-)<>"")*($G$:INDIRECT("G"&ROW()-)=$G2)),ROW($:))+,ROW($:$))):$G2,$G2) - $H2,

   IF(OR($G2="",$G2=""),

    IF($H2>,,-)*SUMIFS($E:$E,$B:$B,$B2,$G:$G,$G2,$A:$A,$A2,$D:$D,) - $H2,

    "ERROR"

   )

  )

 )

主要复杂的是：

INDIRECT("E"&IFERROR(LOOKUP(1,0/(($H$1:INDIRECT("H"&ROW()-1)<>"")*($G$1:INDIRECT("G"&ROW()-1)=$G2)),ROW($1:2))+1,ROW($1:$1)))

其作用就是返回：H列中存在空值，假设第i行有数据，从i行向上查找直到遇到另一个数据位置，返回查找到的空值的最小行号，如下面表格，i=2返回1，i=9返回5，当然还有符合一定筛选条件。

1
2	num
3
4	num
5
6
7
8
9	num


语句逐步才开看indirect，作用是将字符串“” 转换成有效代码，INDIRECT（“E”&1）= E1

IFERROR（）

为了解决向上查找时候没有遇到空值的情况，例如上表中的第一行若为标签，i =2 时候向上查找并未遇到空，所以用这个语句返回ROW($1:$1)，E和1 前面带 $ 意义是excel 中拖动时候不变。

ROW（）返回选中单元格的行号。

下面是需要慢慢讲的查找语句，先通过简单版来讲原理。

LOOKUP(1,0/($H$1:INDIRECT("H"&ROW()-1)<>""）,ROW($1:2)）

一个数据的上面最靠近的 非 空值可以通过上面语句来返回行号。原理：

INDIRECT($H$1:INDIRECT("H"&ROW()-1)<>""）

表示该列 从第一行到数据上一行的 是数值是否为空的判断，结果是 一列的true 、false

0/(...)

用0 除以后变成一列 0 和 div/0，需要括起全部数据，后者是excel 的一个错误标记{0;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0}


然后在该列 中查找1 ，必须大于0（利用了excel 的机制，baidu会有更清楚的解释：lookup 会默认第二个参数是已经sort（从小到大），忽略error，然后用二分查找），结果就会选中列中最后一个0，最后返回第三个参数 的对应数值，这里是行号。如果 查找的指匹配到了，lookup 的机制是返回匹配中最后一个，返回第一个好像是match。

剩下的加减 1是自己调整的。