エクセルで 比較して差を求める

質問

よろしくお願いします。

エクセル2010で三つの表があり、シリアルナンバー、英語名、　日本語名が書かれている票があります。

それぞれの表を比較して、同じ部分と違う部分をわかるようにしたいのですがどうすれば一番簡単でしょうか？

３つの表を比較して、シリアル番号は共通しているが、英語名、日本語名が違うもの

（ある表では「1234- apple-りんご」となっているが「1234-apple-なし」となっているなど）

など、シリアルナンバー、英語名、日本語名すべてが一致しているもの以外を抽出したいのです。

ある表にはあるけれど、別の表にはないシリアルナンバーの抽出などどのようにしていけばよろしいでしょうか？　if関数で一致するかどうかなどやっていくという愚直な方法もあると思うのですが
効率的な方法があれば教えてください。

マクロやＶＢＡは使えないので、関数式レベルでできる方法を教えてください。

回答No.4

MackyNo1 · ベストアンサー

>連結シートというのはグループ化したシートのことでしょうか。

単純に3つの表をコピーして、1枚のシートに1つの表として貼り付けることです。

＞やりたいことは３つの表を比較した際に、下記が判明することです。
＞・同じ英語名に対して複数の和訳がないこと（１つの英語名に対して一つの和訳名）
＞・もしも同じ英語名に対して複数の和約がある場合は色塗りがされるなど判明させる

単純にシリアル番号と日本語訳を統一したいだけなら、特に色塗りして作業するよりも表を1つにして（必要ならどの表のデータか補助列に記入しておく）重複したデータには色を付けるなどの処理の方が合理的だと思うのですが、どうして表別に重複データに色を付けたいのでしょうか？
もちろん、このような処理も可能ですが、いくぶん複雑な関数処理が必要になりますので、最終目的をお知らせください（その場合は実際の表のレイアウトも提示してください）。

＞同じ英語名に対して複数の和訳がある場合は統一したいのです。

この部分だけなら1つのシートにまとめたデータで「重複の削除」を行い、英語名の重複を調べればよいと思います。
その場合、重複を削除したデータで英語の列で並べ替えて重複して和訳の異なる英単語を処理しやすくするのが効率的です。
関数で表示したい場合は以下のような関数で重複が表示できます(もちろん色付けもできます）。
=IF(COUNTIF(B:B,B2)>1,"重複あり","")

＞また、もしも同じシリアルナンバーで複数の英語名が設定されている場合も判明させ、
１つのシリアルナンバーに1つの英語名というのを徹底させていきたいです。

この場合もシリアルナンバーで並べ替え、上記と同じ処理を行ってみてください。

お礼

回答ありがとうございます。

教えていただいた方法が一番容易にできるみたいです。

大変助かりました。

回答No.3

MarcoRossiItaly

このような感じではいかがでしょう。

1
「番号　英　日　番号　英　日」というふうに6列などになっている場合は、「番号　英　日」の3列にする。

2
3表を縦にくっつけて、1つの表にする。

3-1-1
2で作った表の中にカーソルを置いて、リボンあるいはメニューバーの「データ」で、「フィルタ」あるいは「オートフィルタ」を取り付ける。

3-1-2
表示されたプルダウンのボタンから、昇順での並べ替えを行う。「日本語、英語、Serial #の順で3回」並べ替える。

3-1-3
残したいレコードを一つひとつ見て確認し、該当する行について、表の右隣の列に○印などの記号を入れる。（色情報は、特に古いバージョンでは何も集計に使えないので、テキストで記録しましょう）

3-1-4
一旦フィルタを解除し、再び取り付ける。すると、○印がある列にもフィルタのボタンが表示されるので、○印を付けたレコードのみ抽出・表示することができる。絞り込んだ状態で表をコピーして他のシートに貼り付けるなどし、活用。

3-2
2で作った表の中にカーソルを置いて、リボンあるいはメニューバーの「挿入」で、「ピボットテーブル」を実行。2で作った表中の同一レコード（「番号　英　日」のデータが3列とも同一であるレコード）の件数が掲載された表を作れる。できた表中を右クリックして表示される「フィールドの設定」から、件数が「最大値」であるレコードのみを表示させることもできる。

お礼

回答ありがとうございます。

回答No.2

MackyNo1

>シリアルナンバー、英語名、日本語名すべてが一致しているもの以外を抽出したいのです。

単純に3つの表の重複にないデータを抽出したいということなら、別シートに3つの表をコピー貼り付けした連結シートを作成し、そのシートで「データ」「重複の削除」を行うのが簡単です。

シートごとに重複のあるデータにマークを付けたいなど、それ以外の目的があるならご希望の操作をもう少し詳しく説明してください。

お礼

回答ありがとうございます。

連結シートというのはグループ化したシートのことでしょうか。

やりたいことは３つの表を比較した際に、下記が判明することです。

・同じ英語名に対して複数の和訳がないこと（１つの英語名に対して一つの和訳名）
・もしも同じ英語名に対して複数の和約がある場合は色塗りがされるなど判明させる

同じ英語名に対して複数の和訳がある場合は統一したいのです。

また、もしも同じシリアルナンバーで複数の英語名が設定されている場合も判明させ、
１つのシリアルナンバーに1つの英語名というのを徹底させていきたいです。

回答No.1

kagakusuki

何をやりたいのかがはっきりとは判りませんが、

>シリアルナンバー、英語名、日本語名すべてが一致しているもの以外を抽出したいのです。

と仰っておられるという事は、若しかしますと、３つの表のデータを全て合わせた中で、たった１回しか現れないデータだけを、抽出したいという事でしょうか？
　それならば、以下の様な方法は如何でしょうか？

　今仮に、抽出したデータをJ列～L列に表示させるものとします。
　又、N列～P列をさ魚列として使用するものとします。

　まず、N2セルに次の数式を入力して下さい。

=IF(OR(COUNTIF($B2:$D2,"*?")=0,COUNTIFS($B$9:$B$12,$B2,$C$9:$C$12,$C2,$D$9:$D$12,$D2)+COUNTIFS($F$2:$F$5,$B2,$G$2:$G$5,$C2,$H$2:$H$5,$D2)),"",ROWS($1:2))

　次に、O2セルに次の数式を入力して下さい。

=IF(OR(COUNTIF($B9:$D9,"*?")=0,COUNTIFS($B$2:$B$5,$B9,$C$2:$C$5,$C9,$D$2:$D$5,$D9)+COUNTIFS($F$2:$F$5,$B9,$G$2:$G$5,$C9,$H$2:$H$5,$D9)),"",ROWS($8:9))

　次に、P2セルに次の数式を入力して下さい。

=IF(OR(COUNTIF($F2:$H2,"*?")=0,COUNTIFS($B$2:$B$5,$F2,$C$2:$C$5,$G2,$D$2:$D$5,$H2)+COUNTIFS($B$9:$B$12,$F2,$C$9:$C$12,$G2,$D$9:$D$12,$H2)),"",ROWS($1:2))　

　次に、N2～P2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、J2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT($N:$P),"",IF(ROWS($2:2)>COUNT($N:$N),IF(ROWS($2:2)>COUNT($N:$O),INDEX($F$1:$H$5,SMALL($P:$P,ROWS($2:2)-COUNT($N:$O)),COLUMNS($J:J)),INDEX($B$8:$D$12,SMALL($O:$O,ROWS($2:2)-COUNT($N:$N)),COLUMNS($J:J))),INDEX($B$1:$D$5,SMALL($N:$N,ROWS($2:2)),COLUMNS($J:J))))

　次に、J2セルをコピーして、K2～L2の範囲に貼り付けて下さい。
　次に、J2～L2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　これで、J列～L列の2行目以下に抽出結果が表示されます。

お礼

回答ありがとうございます。

やりたいことは３つの表を比較した際に、下記が判明することです。

・同じ英語名に対して複数の和訳がないこと（１つの英語名に対して一つの和訳名）
・もしも同じ英語名に対して複数の和約がある場合は色塗りがされるなど判明させる

同じ英語名に対して複数の和訳がある場合は統一したいのです。

また、もしも同じシリアルナンバーで複数の英語名が設定されている場合も判明させ、
１つのシリアルナンバーに1つの英語名というのを徹底させていきたいです。

教えていただいた方法でやってみたいと思います。
ありがとうございました。