![根据条件组合多个列](https://linux22.com/image/1539628/%E6%A0%B9%E6%8D%AE%E6%9D%A1%E4%BB%B6%E7%BB%84%E5%90%88%E5%A4%9A%E4%B8%AA%E5%88%97.png)
我有一系列列 (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)
我不得不发表第二个答案,因为这些公式在评论中看起来不太好。