Excelの関数を教えてください。

条件は以下の通りです。
sheet1とsheet2があります。
sheet1のZ100セルにTRUEかFALSEで表記

sheet1のB100セルにNo.の値
sheet1のE100セルにユーザ値
sheet1のF100セルにグループ値
sheet1のG100セルにpermission
sheet2のA:Aにランダムで数字が記載
sheet2のB:Bにpermission
sheet2のC:Cにグループ値
sheet2のD:Dにユーザー値

例題
sheet1のB100セルに299
sheet1のE100セルにroot
sheet1のF100セルにsys
sheet1のG100セルに644

sheet2のA3.A4.A5セルに299
sheet2のB3.B4.B5セルに644
sheet2のC3.C4.C5セルにsys
sheet2のD3.D4.D5セルにroot

このように記載されてた時、
sheet1のB100セルとsheet2のA3.A4.A5セルが一致かつ、sheet1のE100セルとsheet2のD3.D4.D5セルご一致かつ、sheet1のF100セルとsheet2のC3.C4.C5セルが一致してた時、sheet1のZ100セルにTRUEと記載

ユーザー、グループ、permissionが一つで異なっていた場合はFALSEと記載

また、sheet1のB100セルがsheet2のA:Aに存在しなかった場合は’-‘と記載


僕の知能では難しく、どなたかご教授をお願い致します。あくまでも関数です。

質問者からの補足コメント

• 返信ありがとうございます。
  僕の頭の理解が追いつかないのですが、全ての式を繋げた場合、どうなるのでしょうか？
  
  この後がわからないです。
  =IF(COUNTIF(Sheet2!A:A,B100)=0,"-",(COUNTIFS(Sheet2!A3:A5,B100,Sheet2!D3:D5,E100,Sheet2!C3:C5,F100,Sheet2!B3:B5,G100)=3))？？？ら

  No.2の回答に寄せられた補足コメントです。 補足日時：2023/12/08 10:21

• goomania様
  ありがとうございます。
  早速、試したのですが、全ての値が一致してもFALSEになってしまいます。
  また、範囲が下記のようにしたいのですが、式はあっておりますか？
  =IF(COUNTIF(Sheet2!A:A,B100)=0,"-",(COUNTIFS(Sheet2!A:A,B100,Sheet2!D:D,E100,Sheet2!C:C,F100,Sheet2!B:B,G100)=3))
  
  ご教授お願い致します。

    補足日時：2023/12/08 10:38

No.2 ベストアンサー 回答者： goomania 回答日時： 2023/12/08 09:33

まず、ご質問者の説明の辻褄があっていません。

>sheet1のB100セルとsheet2のA3.A4.A5セルが一致かつ、sheet1の
>E100セル>とsheet2のD3.D4.D5セルご一致かつ、sheet1のF100セル
>とsheet2のC3.C4.C5セルが>一致してた時、sheet1のZ100セルに
>TRUEと記載
>ユーザー、グループ、permissionが一つで異なっていた場合はFALSE
>と記載

との説明ですので、permissionの一致も確認しなければならないのに、permissionの一致条件である【sheet1のG100セルとsheet2のB3.B4.B5セルが一致】という条件が漏れているのではないかと思います。

そこで、それを補完した上で、ご質問者の説明に従って数式を組立てると以下のようになります。

=IF(COUNTIF(Sheet2!A:A,B100)=0,"-",(COUNTIFS(Sheet2!A3:A5,B100,Sheet2!D3:D5,E100,Sheet2!C3:C5,F100,Sheet2!B3:B5,G100)=3))･･･①

①の数式を解説すると、
>sheet1のB100セルがsheet2のA:Aに存在しなかった場合は’-‘
とのことなので、数式①の冒頭
=IF(COUNTIF(Sheet2!A:A,B100)=0,"-"･･･②
でこれを実現します。そうでなかったら、
>ユーザー、グループ、permissionが一つで異なっていた場合はFALSEと記載
とのことなので、【一つでも異なっていたら】ということは完全一致が3セットあることを意味します。ですから、sheet1のB100,E100,F100,G100がsheet1のA列、D列、C列、B列の3行目～5行目にセットで3回登場するかチェックして、登場したらTRUE、登場しなかったらFALSEを返せばよいことになります。そこで、②の数式の後に
(COUNTIFS(Sheet2!A3:A5,B100,Sheet2!D3:D5,E100,Sheet2!C3:C5,F100,Sheet2!B3:B5,G100)=3)･･･③
を付けて、括弧でくくれば③の数式がTRUE、FALSEを返します。

この回答へのお礼

この度は本当にありがとうございました！
あなた様のような知能が持てるように私も日々勉強していきます！
本当に助かりました！！

お礼日時：2023/12/08 11:08

No.3 回答者： goomania 回答日時： 2023/12/08 15:35

No.2です。

>sheet1のB100セルにNo.の値
>sheet1のE100セルにユーザ値
>sheet1のF100セルにグループ値
>sheet1のG100セルにpermission
>sheet2のA:Aにランダムで数字が記載
>sheet2のB:Bにpermission
>sheet2のC:Cにグループ値
>sheet2のD:Dにユーザー値
というご説明のように、sheet1の項目の並び順とsheet2の項目の並び順が異なるので注意が必要です。

前回回答の数式
=IF(COUNTIF(Sheet2!A:A,B100)=0,"-",(COUNTIFS(Sheet2!A3:A5,B100,Sheet2!D3:D5,E100,Sheet2!C3:C5,F100,Sheet2!B3:B5,G100)=3))･･･①
を実際のデータに適用した状態の画像を添付します。
ご覧いただいておわかりのように、
>全ての値が一致してもFALSEになってしまいます。
という症状にはなりません。

>範囲が下記のようにしたいのですが、式はあっておりますか？
=IF(COUNTIF(Sheet2!A:A,B100)=0,"-",(COUNTIFS(Sheet2!A:A,B100,Sheet2!D:D,E100,Sheet2!C:C,F100,Sheet2!B:B,G100)=3))･･･②

提示された数式②は数式としては成立していますが、ご質問の説明である
>sheet1のB100セルとsheet2のA3.A4.A5セルが一致かつ、sheet1の
>E100セル>とsheet2のD3.D4.D5セルご一致かつ、sheet1のF100セル
>とsheet2のC3.C4.C5セルが>一致してた時、sheet1のZ100セルに
>TRUEと記載
>ユーザー、グループ、permissionが一つで異なっていた場合はFALSE
>と記載
とは全く異なるものになっています。最終的に何をしたいのか判りません。

仮に、ご提示の数式②を用いて、sheet1のNo.、ユーザー値、グループ値、permissionの4項目のセットが、sheet2のA列、D列、C列、B列に3セットあるかチェックしたいというのが最終目的だとすると、当初の説明の「A3～A5のように【連続した行に、続けて】3セットあるか」という条件のうち【連続した行に、続けて】という条件は無視されることになります。
つまり、No.、ユーザー値、グループ値、permissionの4項目のセットが、離れた位置にあっても3セットあればOKという数式になっているということです。

No.1 回答者： 河内のおやじ 回答日時： 2023/12/07 22:01

以下は、条件に基づいてExcelの式を表現したものです。

sheet1のZ100セルに次の式を入力してみてください：

```excel
=IF(AND(ISNUMBER(MATCH(B100, Sheet2!A:A, 0)), B100=INDEX(Sheet2!A:A,MATCH(B100, Sheet2!A:A, 0)), E100=INDEX(Sheet2!D:D,MATCH(B100, Sheet2!A:A, 0)), F100=INDEX(Sheet2!C:C,MATCH(B100, Sheet2!A:A, 0))), TRUE, IF(ISNUMBER(MATCH(B100, Sheet2!A:A, 0)), FALSE, "-"))
```

この式は、各条件を確認して、一致する場合はTRUE、一致しない場合はFALSE、B100がSheet2のA:Aに存在しない場合は'-'を表示します。