☰

已達到 Excel 中的巢狀 IF 限制

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007
已達到 Excel 中的巢狀 IF 限制

有誰知道如何縮短此 Excel 公式，因為我已達到嵌套“IF”限制？

=IF(Sheet!$G$2=Sheet1!I2,"Y",IF(Sheet!$H2=Sheet1!I2,"Y",
 IF(Sheet!$I2=Sheet1!I2,"Y",IF(Sheet!$J2=Sheet1!I2,"Y",
 IF(Sheet!$K2=Sheet1!I2,"Y",IF(Sheet!$L2=Sheet1!I2,"Y",
 IF(Sheet!$M2=Sheet1!I2,"Y",IF(Sheet!$N2=Sheet1!I2,"Y",
 IF(Sheet!$O2=Sheet1!I2,"Y",IF(Sheet!$P2=Sheet1!I2,"Y",
 IF(Sheet!$Q2=Sheet1!I2,"Y",IF(Sheet!$R2=Sheet1!I2,"Y",""))))))))))))

答案1

筆記：下面我的答案是採用數位邏輯電路的方式來實現的，其中條件是並行評估的。在這種情況下，您需要明確規定每個條件。這實在是太過分了。比必要的更複雜。

thilina R 的答案中顯示的方法利用了 Excel 按順序計算公式和範圍的事實，因此 Excel 已經完成了限制可能條件的大部分工作，類似於問題中的嵌套 IF。

我們都想出了“或”邏輯，如下所示是我的最後一種方法。我將保留這個答案，因為它可能提供任何教育或新穎的價值。但thilina R的答案包含了實際的解決方案，我的投票是方法1：HLOOKUP。

目前尚不清楚您是否使用了比必要的限制更嚴格的公式。我將在這裡使用一些速記符號來簡化討論。我將省略工作表名稱，並以這種方式引用相等性測試：如果這是真的G2=I2，我將呼叫它G2。如果 G2=I2 為假，我將稱之為Not-G2。使用這種簡寫，您的巢狀 IF 可以表達如下：

    G2 
or: H2 and Not-G2
or: I2 and Not-G2 and Not-H2
or: J2 and Not-G2 and Not-H2 and Not-I2
etc.

如果這是您需要的規則，您可以使用一個 IF 以及所有 AND 和 OR 邏輯來完成。簡寫如下：

=IF(OR(G2,AND(H2,Not-G2),AND(I2,Not-G2,Not-H2),...),"Y","")

填寫實際的公式如下所示：

=IF(OR(Sheet!$G$2=Sheet1!I2,
       AND(Sheet!$H2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2),
       AND(Sheet!$I2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2),
       AND(Sheet!$J2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2),
       AND(Sheet!$K2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2),
       AND(Sheet!$L2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2),
       AND(Sheet!$M2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2),
       AND(Sheet!$N2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2),
       AND(Sheet!$O2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2),
       AND(Sheet!$P2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2),
       AND(Sheet!$Q2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2,Sheet!$P2<>Sheet1!I2),
       AND(Sheet!$R2=Sheet1!I2,Sheet!$G$2<>Sheet1!I2,Sheet!$H2<>Sheet1!I2,Sheet!$I2<>Sheet1!I2,Sheet!$J2<>Sheet1!I2,Sheet!$K2<>Sheet1!I2,Sheet!$L2<>heet1!I2,Sheet!$M2<>Sheet1!I2,Sheet!$N2<>Sheet1!I2,Sheet!$O2<>Sheet1!I2,Sheet!$P2<>Sheet1!I2,Sheet!$Q2<>Sheet1!I2)),"Y","")

（我不能保證那裡沒有拼字錯誤）。太長的公式很難輸入和維護。對公式的各個部分使用輔助單元會很有幫助。

如果這實際上不是您需要的規則，那麼這裡有一些更簡單的情況：

  • 如果您不需要該序列，並且只關心任何一個（且只有一個）單元格等於 I2，則可以使用以下方法使用一個 IF 執行相同的邏輯：

    =IF((Sheet!$G$2=Sheet1!I2)+(Sheet!$H2=Sheet1!I2)+(Sheet!$I2=Sheet1!I2)+ (Sheet!$J2=Sheet1!I2)+(Sheet!$K2=Sheet1!I2)+(Sheet!$L2=Sheet1!I2)+ (Sheet!$M2=Sheet1!I2)+(Sheet!$N2=Sheet1!I2)+(Sheet!$O2=Sheet1!I2)+ (Sheet!$P2=Sheet1!I2)+(Sheet!$Q2=Sheet1!I2)+ (Sheet!$R2=Sheet1!I2)=1,"Y","")

    每組括號包含您的一個測試。如果等式為真，則產生值為1，如果不是為真，則值為0。這就是 Excel 表示 true 和 false 的方式。

    所有這些測試的結果都會相加。如果總和為1，則表示只有一項測試為真。

  • 實際上，如果您只關心其中至少一種情況是否屬實，則可以使用簡單的 OR：

    =IF(OR(Sheet!$G$2=Sheet1!I2,Sheet!$H2=Sheet1!I2,Sheet!$I2=Sheet1!I2, Sheet!$J2=Sheet1!I2,Sheet!$K2=Sheet1!I2,Sheet!$L2=Sheet1!I2, Sheet!$M2=Sheet1!I2,Sheet!$N2=Sheet1!I2,Sheet!$O2=Sheet1!I2, Sheet!$P2=Sheet1!I2,Sheet!$Q2=Sheet1!I2,Sheet!$R2=Sheet1!I2),"Y","")

請注意，我在所有公式中添加了換行符和額外的空格，以便更容易查看邏輯。如果您想複製和貼上，則需要刪除它們。

答案2

基本上，您想知道一張工作表中的任何儲存格是否與另一張工作表上的G2:R2儲存格具有相同的值。I2

有幾種方法可以做到這一點。我想到的最簡單的方法是這樣，因為您正在檢查的值處於連續範圍內：

方法一：HLOOKUP/VLOOKUP

=IF(ISERROR(HLOOKUP(Sheet1!I2,Sheet!G2:R2,1,FALSE)),"","Y")

這使用在一張紙上的單元格中查找值I2並檢查它是否在範圍內的另一張紙中G2:R2。如果HLOOKUP在範圍內找到至少一個與另一張工作表中G2:R2的儲存格相符的值，則它傳回，否則不傳回值。I2Y

我已經使用了，HLOOKUP因為提供的範圍是水平範圍。如果範圍是垂直範圍，VLOOKUP則可以使用。

如果值為不是無論出於何種原因，在連續範圍內，您都可以使用以下的其他 2 種方法。

這是另一種方法：

方法 2：連接

=IF(ISERROR(FIND(Sheet1!I2,CONCATENATE(Sheet!G2,Sheet!H2,Sheet!I2,Sheet!J2,Sheet!K2,Sheet!L2,Sheet!M2,Sheet!N2,Sheet!O2,Sheet!P2,Sheet!Q2,Sheet!R2))),"","Y")

這基本上會產生一個包含儲存格中所有值的大字串，並檢查其他工作表的G2:R2儲存格中的值是否在其中。I2如果是，則傳回，Y否則不傳回值。

請注意，由於此方法從您需要查找的單元格範圍內的所有資料中產生一個大字串，因此根據這些單元格中的資料類型，它可能會Y錯誤地返回。例如：如果Sheet1!I2has the value123且Sheet!G2has the value12且Sheet!H2has the value 34，則此方法仍將顯示，Y因為「大字串」將包含「1234...」並且該值123將在此字串中。

另一種方法是：

方法3：布林運算子-OR

[@fixer1234 首先提到了這一點]

=IF(OR((Sheet!G2=Sheet1!I2), (Sheet!H2=Sheet1!I2),(Sheet!I2=Sheet1!I2), (Sheet!J2=Sheet1!I2), (Sheet!K2=Sheet1!I2), (Sheet!L2=Sheet1!I2), (Sheet!M2=Sheet1!I2), (Sheet!N2=Sheet1!I2), (Sheet!O2=Sheet1!I2), (Sheet!P2=Sheet1!I2), (Sheet!Q2=Sheet!I2), (Sheet!R2=Sheet1!I2) ),"Y","")

這使用布林運算子OR來檢查範圍內的任何值是否G2:R2位於I2另一張工作表的儲存格中。如果布林運算子找到至少一個I2與另一張工作表中的儲存格相符的值，Y則它會傳回，否則不傳回值。

方法四：展開公式

  1. 如果您只想使用IF條件（出於某種我無法想像的原因），您可以將條件分散IF到多個儲存格中，然後使用這些儲存格的結果來確定最終結果。例如：您可以在一個儲存格中包含一些IF條件，然後可以在另一個儲存格中使用此結果以及更多IF條件，依此類推。

A1您可以在工作表的儲存格中包含以下內容：

=IF(Sheet!$G$2=Sheet1!I2,"Y",IF(Sheet!$H2=Sheet1!I2,"Y", IF(Sheet!$I2=Sheet1!I2,"Y",IF(Sheet!$J2=Sheet1!I2,"Y", IF(Sheet!$K2=Sheet1!I2,"Y","")))))

然後，您可以在A2工作表的儲存格中包含以下內容：

=IF(A1="Y", "Y", IF(Sheet!$L2=Sheet1!I2,"Y", IF(Sheet!$M2=Sheet1!I2,"Y",IF(Sheet!$N2=Sheet1!I2,"Y", ""))))

A3最後，您可以在工作表的儲存格中包含以下內容：

=IF(A2="Y","Y",IF(Sheet!$O2=Sheet1!I2,"Y",IF(Sheet!$P2=Sheet1!I2,"Y", IF(Sheet!$Q2=Sheet1!I2,"Y",IF(Sheet!$R2=Sheet1!I2,"Y","")))))

方法5：VBA

如果您願意使用 VBA 建立自訂使用者定義函數，在其中可以有任意數量的巢狀If語句，請使用 VBA。

注意：這兩個公式的輸出與您提供的公式相同。

相關內容