☰

Excel でネストされた IF の制限に達しました

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007
Excel でネストされた IF の制限に達しました

ネストされた「IF」の制限に達したので、この Excel 数式を短縮する方法を知っている人はいますか?

=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 と同様に、可能な条件を制限する作業のほとんどをすでに実行しています。

私たちは 2 人とも、以下に示すような私の最後のアプローチである OR ロジックを思いつきました。この回答は、教育的価値や目新しい価値を提供するかもしれないので、そのまま残しておきます。しかし、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.

それが必要なルールであれば、1 つの 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","")

(タイプミスがないとは保証できません)。数式が長すぎると、入力や管理が非常に難しくなります。数式の一部にヘルパー セルを使用すると便利です。

それが実際に必要なルールではない場合は、次のようなより単純なケースがいくつかあります。

  • そのシーケンスは必要なく、1 つのセルだけが I2 に等しいことだけを知りたい場合は、1 つの 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 つ含まれています。等式が true の場合、値は1、true でない場合、値は になります0。Excel では、これが true と false を表す方法です。

    これらすべてのテストの結果を合計します。合計が の場合1、テストのうち 1 つだけが真であったことを意味します。

  • 実際には、これらのケースの少なくとも 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

これを行うにはいくつかの方法があります。チェックする値が連続した範囲内にあるため、最も簡単な方法は次のようになります。

方法 1: HLOOKUP/VLOOKUP

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

これは、1 つのシートのセル内の値を検索しI2、その値が範囲内の他のシートにあるかどうかを確認します。範囲内に他のシートのセルと一致する値が少なくとも 1 つ見つかったG2:R2場合はを返し、そうでない場合は値を返しません。HLOOKUPG2:R2I2Y

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!I2に値があり123、Sheet!G2に値があり12、Sheet!H2に値がある場合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。ブール演算子は、他のシートのセルに一致する値が少なくとも 1 つ見つかった場合は値I2を返しますYが、そうでない場合は値を返しません。

方法4: 式を広げる

  1. 条件だけを使用したい場合IF(理由はわかりませんが)、条件をIF複数のセルに分散し、それらのセルの結果を使用して最終結果を決定できます。たとえば、1 つの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。

注: これら両方の数式の出力は、指定した数式と同じです。

関連情報