我有以下疑问:
我的输入表是
基本上我想查找每个 ID 的列。值 3>值 2>值 1
如果 value3 和 value 列有数据,则输出应显示具有 value3 的 ID
如果 value3 有数据,则将输出填充为 Value3 ,如果 value2 和 value3 都有数据,则也应该填充 value3
输出:
是否有我可以编写的公式来检索该值?
答案1
如果只有三个值列,那么一个简单的方法是 IF 测试。假设您显示的表格从 A1 开始(A1="ID" 标题)。第一个输出值的公式将是:
=IF(ISBLANK(D2),IF(ISBLANK(C2),$B$1,$C$1),$D$1)
将其复制到输出列中。(这假设数据表中的每一行都由输出表中的一行表示,并且两者都具有相同顺序的 ID 列。)
答案2
这是一个更通用的解决方案。我假设您有五个“值”列(B
到F
),并且您希望在列中得到结果G
。在单元格中输入此公式G2
:
=OFFSET($B$1, 0, MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2)))-2)
然后输入Ctrl+ Shift+ Enter。这样它就变成了一个“数组公式”;它将显示在公式栏中,周围有括号 ( {
... }
)。现在将它向下拖动到任意行:
ISBLANK()
检查单元格是否为空白(TRUE
如果是,FALSE
则为空白;如果不是)。NOT(…)
反转逻辑;现在我们有TRUE
单元格是否包含(非空白)数据和FALSE
是否不包含数据。
事实证明TRUE
实际上是值1
,而FALSE
实际上是0
。COLUMN(B2:F2)*(the above)
取区域中每个单元格的列号,如果单元格有数据,则将其乘以 1,如果没有数据,则将其乘以 0。如果单元格有数据,则结果为列号 (×1),如果没有数据,则结果为 0(列号 × 0)。例如,对于第 3 行,结果为 {2,0,4,0,0},因为第二列 (B
) 和第四列 (D
) 有数据。MAX(…)
选择其中最大的一个;即最右边有数据的那一列的数字。=OFFSET($B$1, 0, (the above)-2)
MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2)))-2
查找位于单元格 下方 0 行、右侧 0 列的单元格B1
。我们从列号中减去 2,因为B1
位于第 2 列,因此,当MAX(COLUMN(B2:F2)*NOT(ISBLANK(B2:F2)))
计算结果为 2(如第 2 行所示)时,我们希望移至单元格 右侧 0 列B1
。