☰

根据条件组合多个列

tag-icon • tag-icon microsoft-excel
根据条件组合多个列

我有一系列列 (A1、B1、C1、D1)。这些列的值从 1 到 20。我在 F1 中有一个如下公式：

代码：

=if($A1>7,"U",if($B1>7,"X",If($C1>7,"Y",if($D1>7,"Z",""))))

如果其中任何一个单元格（A1、B1、C1 或 D1）大于 7，则此公式将输入 U、X、Y 和 Z 的值。

但如果有两个单元格大于 >7，它就无法处理。

我该怎么办，如果有两列大于 7，最终结果将是一对 U、X、Y 或 Z（以逗号分隔）。

谢谢。

答案1

这可以用公式来完成，但它相当长，VBA 可能是更好的解决方案。此外，这无法处理您可能遇到的情况三值 > 7。但无论如何我都会向您展示公式方法。

让我们从更简单的情况开始，其中只有一个单元格 > 7：

在此处输入图片描述

F1中的公式为：

=IF(COUNTIF($A$1:D$1,">7")>1,"Double",IF($A1>7,"U",IF($B1>7,"X",IF($C1>7,"Y",IF($D1>7,"Z","")))))

COUNTIF() 部分计算 A1:D1 范围内大于 7 的单元格数。如果总数大于 1，则 IF() 返回“Double”，这只是后面更大公式的占位符。如果总数不大于 1，则公式使用嵌套的 IF() 语句显示大于 7 的单元格的代码。

对于两个单元格大于 7 的情况，嵌套 IF() 将不起作用。我们需要知道哪些列大于 7。如果我们有这些数据，

在此处输入图片描述

首先，我们建立一个列出大于 7 的列的数组。此表达式 (A1:D1>7)*COLUMN(A1:D1)可实现这一点。第一部分(A1:D1>7)询问哪些单元格大于 7，并计算为数组 {TRUE,FALSE,TRUE,FALSE}。第二部分是 A1:D1 的列号或 {1,2,3,4}。将这两个数组相乘会导致逻辑值 TRUE 和 FALSE 转换为 1 和 0，因此结果是数组 {1,0,3,0}，即具有大于 7 的值的列号。

现在我们可以使用这些数字通过 INDEX() 查找列代码：

INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))

这里 LARGE() 返回列号 > 7 的数组中的第二大值（即 1），而 INDEX() 使用该值返回字母数组中的第一个元素 - U。

类似地，此表达式获取用逗号分隔的第二大列号和第一大列号对应的代码：

INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))&","&INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),1)

现在我们将所有这些代入上面的第一个公式，替换“Double”。F1 中的这个公式是一个数组公式，必须使用CtrlShiftEnter

=IF(COUNTIF($A$1:D$1,">7")>1,INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))&","&INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),1)),IF($A1>7,"U",IF($B1>7,"X",IF($C1>7,"Y",IF($D1>7,"Z","")))))

我告诉过你这个公式会“相当长”。希望这会有所帮助。

答案2

这里我发现了上述公式的一些其他替代选项：

=IFERROR(LEFT(IF($A1>7,"U, ","")&IF($B1>7,"X, ","")&IF($C1>7,"Y, ","")&IF($D1>7,"Z, ",""),(COUNTIF($A1:$D1,">7")-1)*3+1),"")


{=TEXTJOIN(",",TRUE,IF(A1:D1>7,MID("UXYZ",COLUMN(A1:D1)-COLUMN(A1)+1,1),""))}


=MID(IF($A1>7,", U","")&IF($B1>7,", X","")&IF($C1>7,", Y","")&IF($D1>7,", Z",""),3,99)

如果有公式返回“”：

=MID(IF(N($A1)>7,", U","")&IF(N($B1)>7,", X","")&IF(N($C1)>7,", Y","")&IF(N($D1)>7,", Z",""),3,99)

我不得不发表第二个答案，因为这些公式在评论中看起来不太好。

相关内容