列の総当たりチェックの方法

列の総当たりチェックをする構文と条件付き書式を教えてください。
条件と求める結果は以下です。

（条件）
・文字列です
・Ａ列とB列を総当たりでチェックする
・Ｂ列の文字はＡ列の文字の頭に"Ｘ"がついている
・したがって、"111"と"X111"は重複とし、その重複は正しい結果とする
・Ｂ列にはＡ列に含まれない文字列があり、それは不正とする（B5とB6）
・Ａ列Ｂ列ともセルの順序に決まりはなくバラバラ

（求める結果）
・Ａ列に存在しないＢ列のセルを検出する（セルを赤くするなど）
・Ｂ列に存在しないＡ列のセルを検出する（セルを赤くするなど）
⇒上記を条件付き書式とセルに記入する関数で作成する

画像の例ですと、Ａ３、Ｂ５、Ｂ６が総当たりチェックで漏れるセルになると思います。
よろしくお願いいたします。

No.3 回答者： goomania 回答日時： 2023/10/21 14:08

No.1さんの回答は、ChatGPTの回答とのことですが、明らかに間違いがあり、ChatGPTへの質問の仕方にもよるとは思いますが、残念ながらChatGPTの未熟さを感じさせる結果です。

No.2さんの回答で既に課題は解決しているかも知れません。条件付き書式の数式としては簡潔であり好ましいと思います。

ただ、A列とB列の数式で使用する関数が異なる点がちょっと気になります。
No.2さんの回答より若干長い数式になりますが、A列、B列とも同じ関数を使って、形を揃え、数式中の参照セル、対象範囲をA列、B列を入れ替える形にしてみました。

条件付き書式の「数式を使用して～」で下部の数式欄に
Ａ列の場合は、
=(SUMPRODUCT(("Ｘ"&A1=B:B)*1)=0)*(A1<>"")
B列の場合は、
=(SUMPRODUCT(("Ｘ"&A:A=B1)*1)=0)*(B1<>"")
を入れて書式設定すればよいと思います。
このような数式にすると、数式として見やすいと思います。

なお、上記数式中の"Ｘ"は、ご質問者が投稿された説明文に登場する「全角大文字英字」を用いていますが、実際の表中の文字に合わせてください。

No.2 回答者： fujillin 回答日時： 2023/10/15 16:10

こんにちは

Ａ列、Ｂ列にそれぞれ条件付き書式を設定します。
「数式を使用して～」で下部の数式欄に
Ａ列の場合は
　=(A1<>"")*(COUNTIF(B:B,"Ｘ"&A1)=0)
Ｂ列の場合は
　=COUNTIF(A:A,MID(B1,2,LEN(B1)))=0
として、書式を設定。

※「Ｘ」の字体は似た字体が存在するので、実際に使っているものにしてください。

No.1 回答者： ぷらぐろまあ 回答日時： 2023/10/15 15:36

そういう時はChatGPTに質問してみると解決できますよ。

質問文をそのまま入れてみた回答が以下です。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
条件付き書式を使用して、ExcelでＡ列とＢ列の総当たりチェックを行い、指定された条件に基づいてセルを書式設定する方法は以下の通りです。

Excelを開き、シートにデータを入力します。Ａ列とＢ列にデータがあると仮定します。

条件付き書式を設定するために、データを選択します。たとえば、Ａ列のセルを選択します。

条件付き書式の設定に進みます。これは通常、Excelの「ホーム」タブ内にある「条件付き書式」のドロップダウンメニューから選択できます。

条件付き書式のルールを追加します。Ａ列に存在しないＢ列のセルを検出するために、新しいルールを追加します。

"新しいルール" ダイアログボックスが表示されたら、次の条件を設定します：
"書式設定"：セルの書式を設定する条件を選択します。
"数式"：以下の数式を入力します。

=ISNUMBER(MATCH("*" & A1, B:B, 0))

この数式は、Ａ列のセルがＢ列内のセルと部分一致している場合に真（True）を返します。これは、Ｂ列の文字がＡ列の文字の頭に"X"がついている場合に一致します。

条件付き書式を設定する方法を選択します。たとえば、セルの背景色を変更する場合、"フォント"や"塗りつぶし"の設定を使用できます。

"OK" ボタンをクリックして、条件付き書式のルールを保存します。

これでＡ列のセルがＢ列と一致する場合に書式設定されます。

同様の手順を使用して、Ｂ列に存在しないＡ列のセルを検出し、条件付き書式を設定できます。ただし、条件式は次のようになります：

=ISNUMBER(MATCH("*" & B1, A:A, 0))

これにより、Ａ列に存在しないＢ列のセルと、Ｂ列に存在しないＡ列のセルが適切に書式設定されます。セルが条件に一致しない場合、指定した書式が適用されるでしょう。