☰

使用條件組合多列

tag-icon • tag-icon microsoft-excel
使用條件組合多列

我有一系列列（A1、B1、C1、D1）。這些列的值從 1 到 20。

代碼：

=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() 語句來顯示 > 7 的儲存格的程式碼。

對於兩個儲存格 > 7 的情況，巢狀 IF() 將無法運作。我們需要知道哪些欄位> 7。

在此輸入影像描述

(A1:D1>7)*COLUMN(A1:D1)首先，我們建立一個列出> 7 列的陣列。 第一部分(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)

我不得不發布第二個答案，因為這些公式在評論中看起來不太好。

相關內容