Excel 公式分别从具有引用的两列中获取最后 5 个非零值和名称

Excel 公式分别从具有引用的两列中获取最后 5 个非零值和名称

我在 excel 中有四列数据,如下所示。A 列、B 列为文本格式,C 列、D 列使用引用链接生成。C(F) 和 D(F) 显示 C 和 D 的公式栏值。由于单元格 A10、A11、B10、B11 为空,因此 C10、C11、D10、D11 显示“0”。

我尝试按照 E 列和 F 列所示的顺序获取最后 5 个非零值,但仅基于 C 列和 D 列,而不是直接基于 A 列或 B 列。

     A       B      C(F)    D(F)      C       D      E      F    
 1 Name     Val     Name    Val     Name     Val   Name     Val
 2 abc01    101     =A2     =B2     abc01    101   abc04    104
 3 abc02    102     =A3     =B3     abc02    102   abc05    105
 4 abc03    103     =A4     =B4     abc03    103   abc06    106
 5 abc04    104     =A5     =B5     abc04    104   abc07    107
 6 abc05    105     =A6     =B6     abc05    105   abc08    108
 7 abc06    106     =A7     =B7     abc06    106
 8 abc07    107     =A8     =B8     abc07    107
 9 abc08    108     =A9     =B9     abc08    108
10                  =A10    =B10      0       0
11                  =A11    =B11      0       0

如果在 A 列和 B 列中添加一行数据,则 E 列和 F 列应反映如下内容。

     A       B      C(F)    D(F)      C       D      E      F    
 1 Name     Val     Name    Val     Name     Val   Name     Val
 2 abc01    101     =A2     =B2     abc01    101   abc05    105
 3 abc02    102     =A3     =B3     abc02    102   abc06    106
 4 abc03    103     =A4     =B4     abc03    103   abc07    107
 5 abc04    104     =A5     =B5     abc04    104   abc08    108
 6 abc05    105     =A6     =B6     abc05    105   abc09    109
 7 abc06    106     =A7     =B7     abc06    106
 8 abc07    107     =A8     =B8     abc07    107
 9 abc08    108     =A9     =B9     abc08    108
10 abc09    109     =A10    =B10    abc09    109
11                  =A11    =B11      0       0

答案1

如果所有非零值都出现在第一个零之前,则可以搜索零,然后向后索引。输入

  • =INDEX(C:C, MATCH(0,C:C,0)-5, 1)进入E2
  • =INDEX(C:C, MATCH(0,C:C,0)-4, 1)进入E3
  • =INDEX(C:C, MATCH(0,C:C,0)-3, 1)进入E4
  • =INDEX(C:C, MATCH(0,C:C,0)-2, 1)进入E5
  • =INDEX(C:C, MATCH(0,C:C,0)-1, 1)进入E6

然后选择它们并拖动/填充到右侧F2:F6。正如我在开头所说,这会 C在列中搜索 0,然后从前五行中获取值。

相关内容