Excel差分がどれか割り出す方法

同じ内容のリストでAのリストには474人,Bのリストには468人いるとしたら
この差分の6人が誰か割り出す方法を教えてください。
Excelです

No.6 回答者： goomania 回答日時： 2023/01/25 16:43

No.5です。

前回回答で「Aのリスト、Bのリストのそれぞれには重複がない」という前提での解決方法を投稿いたしました。

しかし、「Aのリスト、Bのリストのそれぞれに重複がある」というケースも当然考えられます。この場合の差分を抽出する数式を投稿します。

この場合、fujillinさんのご指摘のとおり「一方のリストから他方のリストに一致する名がない」ものを抜き出すことになります。
従って、A⇒B、B⇒Aの両方向からチェックする必要があり、fujillinさんの回答にあるように、2列に分けて表示するのが正統派の考えだと思います。
ただ、既に、fujillinさんが回答を示されているので、ここでは差分を1列にまとめて表示する数式にしてみました。

添付画像をご覧ください。

添付画像のように、A列にAのリスト、B列にBのリストがあったとして、E2から下方向へ差分を表示するため、E2セルに

=IFERROR(SUBSTITUTE(LEFT(","&TEXTJOIN(",",1,IFERROR(INDEX($B$2:$B$30,ROW($A$1:$A$29)/(ISERROR(INDEX($A$2:$A$30,MATCH($B$2:$B$30,$A$2:$A$30,0)))*($B$2:$B$30<>"")*(MATCH($A$2:$A$30,$A$2:$A$30,0)=ROW($A$1:$A$29))))&"",""))&","&TEXTJOIN(",",1,IFERROR(INDEX($A$2:$A$30,ROW($B$1:$B$29)/(ISERROR(INDEX($B$2:$B$30,MATCH($A$2:$A$30,$B$2:$B$30,0)))*($A$2:$A$30<>"")*(MATCH($B$2:$B$30,$B$2:$B$30,0)=ROW($B$1:$B$29))))&"","")),FIND("♪",SUBSTITUTE($M$2,",","♪",ROW(A2)))-1),IF(ROW(A1)<2,",",","&TEXTJOIN(",",1,$H$1:H1)&","),""),"")

という数式を記述しています。
この数式はUNIQUE関数、TEXTSPLIT関数が使用できない前提で作成しているため、とても長いものになっています。
また、上記関数の使用できない環境ではスピル機能も使えないと思いますので、数式を数式バーに入力後、ENTERキーではなく、CTRL+SHIFT+ENTERで確定する必要があると思います。
これを下方向へコピーすることになります。

逆に、UNIQUE関数、TEXTSPLIT関数が使用できる場合の数式として、F2セルに

=UNIQUE(TEXTSPLIT(TEXTJOIN(",",1,IFERROR(INDEX(B2:B30,ROW(A1:A29)/(ISERROR(INDEX($A$2:$A$30,MATCH(B2:B30,$A$2:$A$30,0)))*(B2:B30<>"")))&"",""))&","&TEXTJOIN(",",1,IFERROR(INDEX(A2:A30,ROW(B1:B29)/(ISERROR(INDEX($B$2:$B$30,MATCH(A2:A30,$B$2:$B$30,0)))*(A2:A30<>"")))&"","")),,","))

という数式を記述しています。
こちらは、F2セルに記述すれば、スピル機能により、F2より下のセルも自動的に表示されるはずです。
※スピル機能が有効である前提のため、セル参照の相対参照、絶対参照がまちまちになっています。また、UNIQUE関数、TEXTSPLIT関数が使用できない環境でのチェックは行っておりませんのでご容赦ください。

画像のとおり、どちらも同じ結果が表示されます。

No.5 回答者： goomania 回答日時： 2023/01/20 22:56

このご質問は、ご質問の前提が不明確なので、回答もまちまちになっていると思います。

>Aのリストには474人,Bのリストには468人

とのことですが、Aのリストの474人の中に重複があることはないのでしょうか?　また、Bの468人の中には重複はないのでしょうか?
仮に「各々のリストには重複はない」というのであれば、「全体での重複即ち他リストにあり」という意味になるので、A列にAリストの468人を並べ、B列に474人を並べて、データのある範囲を選択したうえで条件付き書式を設定するだけです。

ホームメニュー⇒条件付き書式⇒新しい書式ルール⇒一意の値または重複する値だけを書式設定⇒「すべての値を書式設定」の下のドロップダウンリストの「重複」を確認⇒書式ボタンを押す⇒塗りつぶしタブから好みの色(添付画像ではグレー)を選択⇒OKを押す⇒OKを押す

これで、添付画像①のような状態になります。
これは、Belltreeriverさんのお示しになった例に、そのまま条件付き書式を設定したものです。
Belltreeriverさんのお示しになった画像と同じになっていることが判ります。つまり、Belltreeriverさんの数式と同じ結果になります。

この状態でオートフィルター⇒色フィルター⇒「塗りつぶしなし」を選択を用いてA列、B列を別々に絞り込みすれば添付画像②③のように差分が明らかになります。
また、Aのリスト、Bのリストに追加があって、現在の行の下に新たなデータを記入した場合でも、自動的に条件付き書式が機能して塗り分けられるので便利です。数式は必要ないということになります。

但し、Aのリストそのもの、またはBのリストそのものに重複がある場合、「【全体での重複即ち他リストにあり】とは言えない」ので、この方法は(Belltreeriverさんの数式も含めて)使用できないということになります。

No.4 回答者： googoo900 回答日時： 2023/01/18 21:19

AのリストがA列に、BのリストがB列にあるとして回答します。

C1セルに『=IFERROR(MATCH(A1,B:B,0),A1)』を入力して、データ最下行までコピペします。C列に表示されている名前がB列に存在しない名前です。
500個近くデータがありますので、C列をコピーして使用していない他の列(G列など)に値で貼り付けて降順で並べ替えを行えば名前が上のほうになります。

No.3 回答者： Belltreeriver 回答日時： 2023/01/18 18:34

TEXTSPLIT関数が

使用可能なら
D1=filter(unique(transpose(textsplit(textjoin("/",1,A1:B15),"/"))),countif(transpose(textsplit(textjoin("/",1,A1:B15),"/")),unique(transpose(textsplit(textjoin("/",1,A1:B15),"/"))))=1)

使用不可なら
D1=iferror(indirect(address(int(1/large(index((countif($A$1:$B$15,$A$1:$B$15)=1)/(row($A$1:$B$15)+column($A$1:$B$15)/10),0),row(A2))),mod(1/large(index((countif($A$1:$B$15,$A$1:$B$15)=1)/(row($A$1:$B$15)+column($A$1:$B$15)/10),0),row(A2)),1)*10)),"")
下へフィルコピー

No.2 回答者： 銀鱗 回答日時： 2023/01/17 20:20

自分なら、Aのリストのほうに

条件付き書式に対して「数式を使用して、初期設定するセルを決定」に
　=NOT(COUNTIF(Bリストの範囲,Aリストの一人))
　=NOT(COUNTIF($B$1:$B$468,A1))
のように設定するかな。
こうやってBリストにない場合に色を付ける。

同様に、Bリスト側にもAリストとの比較を入れれば、Bリスト側にも足りない人がいないかも確認する。

No.1 回答者： fujillin 回答日時： 2023/01/17 18:25

こんばんは

両方のリストに出ていない人を特定したいということだと解釈しました。
リストの人数差が6でも、上記に該当する人数が6人とは限りませんね。

方法としては、「一方のリストから他方のリストに一致する名がない」ものを抜き出せば良いと考えられます。
（それぞれのリストに対して行う必要がありますが）

添付図は簡略化してありますが、A列、B列がそれぞれのリストと仮定しています。
D、E列にそれぞれ他方のリストに載っていない人をピックアップしています。
（リストの人数差は1人ですが、結果的に5人が該当しています）

FILTER関数が使える環境だとして、
D2セルに
=FILTER(A2:A500,(A2:A500<>"")*(COUNTIF(B:B,A2:A500)=0))
E2セルに（列を入れ替えて）
=FILTER(B2:B500,(B2:B500<>"")*(COUNTIF(A:A,B2:B500)=0))
を入力してあります。

※ 上式はA2:B500の範囲を想定してあります。
※ FILTER関数が利用できない場合は、ローテク関数でも可能ですが、式が長く、面倒になります。