首页 > 代码库 > excel 表格lookup 的操作

excel 表格lookup 的操作

2024-08-04 05:43:55   223人阅读

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

 1 = 2 IF(OR($D2<>0,ISBLANK($H2),$G2="111"), 3  "", 4  IF(OR($G2="222",$G2="333"), 5   IF($H2>0,1,-1)*SUMIFS(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))):$E2,INDIRECT("A"&IFERROR(LOOKUP(1,0/(($H$1:INDIRECT("H"&ROW()-1)<>"")*($G$1:INDIRECT("G"&ROW()-1)=$G2)),ROW($1:2))+1,ROW($1:$1))):$A2,$A2,INDIRECT("B"&IFERROR(LOOKUP(1,0/(($H$1:INDIRECT("H"&ROW()-1)<>"")*($G$1:INDIRECT("G"&ROW()-1)=$G2)),ROW($1:2))+1,ROW($1:$1))):$B2,$B2,INDIRECT("D"&IFERROR(LOOKUP(1,0/(($H$1:INDIRECT("H"&ROW()-1)<>"")*($G$1:INDIRECT("G"&ROW()-1)=$G2)),ROW($1:2))+1,ROW($1:$1))):$D2,0,INDIRECT("G"&IFERROR(LOOKUP(1,0/(($H$1:INDIRECT("H"&ROW()-1)<>"")*($G$1:INDIRECT("G"&ROW()-1)=$G2)),ROW($1:2))+1,ROW($1:$1))):$G2,$G2) - $H2, 6   IF(OR($G2="444",$G2="555"), 7    IF($H2>0,1,-1)*SUMIFS($E:$E,$B:$B,$B2,$G:$G,$G2,$A:$A,$A2,$D:$D,0) - $H2, 8    "ERROR" 9   )10  )11 )

 

主要复杂的是：

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 
2num
3 
4num
5 
6 
7 
8 
9num


语句逐步才开看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是自己调整的。


  • ($H$1:INDIRECT("H"&ROW()-1)<>"")*($G$1:INDIRECT("G"&ROW()-1)=$G2)

通过上面0/(...) 的解释，可以加入条件选择，避免了数组公式所以使用*号，这是一个且运算，并没有找到或运算的，1*n 数列 且 1*n 数列 = 1*n 数列。






把使用方法也附上吧


1.对原始数据添加首行，第一行，需要注意的是列A，B，D，E，G，H 必须正确对应
次码 主码 有效号 计算值 选择类型 处理选择

2.对添加首行后的数据排序，方法:
ctrl+A 后选择 开始->排序和筛选->自定义排序，
勾选 数据包含标题，
列:主码，排序依据：数值，次序：升序
点击添加条件，
列:次码，排序依据：数值，次序：升序

3.在某一列的第二行，必须第二行，复制上面的公式

4.最后是一拖到底（其中有空行不影响，向下拖，另向右拖不影响结果）









excel 表格lookup 的操作






























        

            
                
                  
                
                

                    联系

                    我们