Excel「シートの内容比較」方法を教えて下さい！

質問

Excelのシート１とシート３の内容を比較したいです。

シート１は現状、シート３は昨年度の内容。
Ａの列に「no.」があり、そのナンバー毎の行の内容を比較するのですが、
ナンバーが多数です・・・。

過去から現状に変わったところを知りたいのですが、
私のチェックでは「漏れ」が有りそうなので、ぜひ、Excelの能力を借りたいです。

方法をご存知の方、教えてください。どうぞよろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

ナンバーの中で、前年までは使われていたが、今年からは使われなくなったものがある場合などには、Sheet1とSheet3でナンバーが入力されている行がずれてしまう事が考えられます。
　以下の方法は、ナンバーの並んでいる順番がSheet1とSheet3で全く異なっている場合でも有効な方法です。

【方法その1】
　条件付き書式を使用して、Sheet1とSheet3の間で、「同じナンバーでありながらデータが異なっている箇所」と「片方のシートにしか存在していないナンバーの行」のセルの色を変える方法です。

　今仮に、Sheet1やSheet3のA列～D列にデータが入力されていて、2行目には「No.」等の項目名が入力されていて、実際のデータは3行目以下に入力されているものとします。
　その場合、以下の様な操作を行って、Sheet1及びSheet3のA列～D列の3行目以下に条件付き書式を設定して下さい。

Sheet1のA3セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(A3<>"",ISERROR(1/(A3=VLOOKUP(INDEX($A:$A,ROW()),Sheet3!$A:A,COLUMNS($A:A),FALSE))))

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄をクリック
　　↓
Sheet1のA列～D列の3行目以下のセル範囲(表のデータ入力欄の全て)をまとめて範囲選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック
　　↓
Sheet3のA3セルを選択
　　↓
「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(A3<>"",ISERROR(1/(A3=VLOOKUP(INDEX($A:$A,ROW()),Sheet1!$A:A,COLUMNS($A:A),FALSE))))

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄をクリック
　　↓
Sheet3のA列～D列の3行目以下のセル範囲(表のデータ入力欄の全て)をまとめて範囲選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　方法その1は以上です。

回答No.4

celtis

下記のツールを使えば差分を取ることができますよ。
http://www.aiper.info/AiperDiffex/
http://replication.hatenablog.com/entry/2014/05/05/220400
http://jp.suntrap-systems.com/ExcelDiff/

回答No.3

kagakusuki

【方法その2】
　Sheet1とSheet3の間で、「同じナンバーでありながらデータが異なっている箇所」と「片方のシートにしか存在していないナンバーの行」のデータのみを抽出して、Sheet4上に表示させる方法です。

　今仮に、Sheet1やSheet3のA列～D列にデータが入力されていて、2行目には「No.」等の項目名が入力されていて、実際のデータは3行目以下に入力されているものとします。
　又、Sheet4のB1セルには「Sheet1」、B2セルには「Sheet3」という具合に比較する2枚のシートのシート名をそれぞれ入力するものとします。
　又、Sheet4のH列とI列を作業列として使用するものとします。

　まず、Sheet4のH3セルに次の関数を入力して下さい。

=IF(ISERROR(ROW(INDIRECT("'"&$B$2&"'!A1"))/(INDIRECT("'"&$B$1&"'!A"&ROW())<>"")),"",IF(COUNTIF(INDIRECT("'"&$B$1&"'!A1:A"&ROW()),INDIRECT("'"&$B$1&"'!A"&ROW()))-1,"",IF(SUMPRODUCT(ISERROR(1/(VLOOKUP(INDIRECT("'"&$B$1&"'!A"&ROW()),INDIRECT("'"&$B$2&"'!A:D"),{1,2,3,4},FALSE)=VLOOKUP(INDIRECT("'"&$B$1&"'!A"&ROW()),INDIRECT("'"&$B$1&"'!A:D"),{1,2,3,4},FALSE)))*1),COUNTIF(INDIRECT("'"&$B$2&"'!A:A"),"<"&INDIRECT("'"&$B$1&"'!A"&ROW()))+COUNTIF(INDIRECT("'"&$B$1&"'!A:A"),"<"&INDIRECT("'"&$B$1&"'!A"&ROW())),"")))

　次に、Sheet4のI3セルに次の関数を入力して下さい。

=IF(ISERROR(ROW(INDIRECT("'"&$B$1&"'!A1"))/(INDIRECT("'"&$B$2&"'!A"&ROW())<>"")),"",IF(COUNTIF(INDIRECT("'"&$B$1&"'!A:A"),INDIRECT("'"&$B$2&"'!A"&ROW()))+COUNTIF(INDIRECT("'"&$B$2&"'!A1:A"&ROW()),INDIRECT("'"&$B$2&"'!A"&ROW()))-1,"",IF(SUMPRODUCT(ISERROR(1/(VLOOKUP(INDIRECT("'"&$B$2&"'!A"&ROW()),INDIRECT("'"&$B$1&"'!A:D"),{1,2,3,4},FALSE)=VLOOKUP(INDIRECT("'"&$B$2&"'!A"&ROW()),INDIRECT("'"&$B$2&"'!A:D"),{1,2,3,4},FALSE)))*1),COUNTIF(INDIRECT("'"&$B$1&"'!A:A"),"<"&INDIRECT("'"&$B$2&"'!A"&ROW()))+COUNTIF(INDIRECT("'"&$B$2&"'!A:A"),"<"&INDIRECT("'"&$B$2&"'!A"&ROW())),"")))

　次に、Sheet4のH3～I3の範囲をまとめてコピーして、同じ列の4行目以下に貼り付けて下さい。
　次に、Sheet4のA5セルに次の関数を入力して下さい。

=IF(INT(ROWS($4:5)/2)>COUNT($H:$I),"",IFERROR(INDEX(INDIRECT("'"&$B$1&"'!A:A"),MATCH(SMALL($H:$I,INT(ROWS($4:5)/2)),$H:$H,0)),"")&IFERROR(INDEX(INDIRECT("'"&$B$2&"'!A:A"),MATCH(SMALL($H:$I,INT(ROWS($4:5)/2)),$I:$I,0)),""))

　次に、Sheet4のB5セルに次の関数を入力して下さい。

=IF(OFFSET($A5,-MOD(ROWS($5:5)-1,2),,)="","",INDEX($B$1:$B$2,MOD(ROWS($5:5)-1,2)+1)&"")

　次に、Sheet4のC5セルに次の関数を入力して下さい。

=IF($B5="","",IF(OR(ISERROR(1/(VLOOKUP(OFFSET($A5,-MOD(ROWS($5:5)-1,2),,),INDIRECT("'"&$B5&"'!A:D"),COLUMNS($C:C),FALSE)<>"")),IFERROR(VLOOKUP(OFFSET($A5,-MOD(ROWS($5:5)-1,2),,),INDIRECT("'"&$B5&"'!A:D"),COLUMNS($C:C),FALSE),"")=IFERROR(VLOOKUP(OFFSET($A5,-MOD(ROWS($5:5)-1,2),,),INDIRECT("'"&INDEX($B$1:$B$2,MOD(ROWS($5:5),2)+1)&"'!A:D"),COLUMNS($C:C),FALSE),"")),"",VLOOKUP(OFFSET($A5,-MOD(ROWS($5:5)-1,2),,),INDIRECT("'"&$B5&"'!A:D"),COLUMNS($C:C),FALSE)))

　次に、Sheet4のA5セルとA6セルを結合して下さい。
　次に、Sheet4のB5セルをコピーして、Sheet4のB6セルに貼り付けて下さい。
　次に、Sheet4のC5セルをコピーして、Sheet4のC5～F6の範囲に貼り付けて下さい。
　次に、Sheet4のA5～F6の範囲をまとめてコピーして、同じ列の7行目以下に貼り付けて下さい。

　以上です。

回答No.1

noname#252332

私だったら、ですが、簡単に

それぞれシート番号を入れる列を追加し、１、３と入れておく。
シート１とシート３を別のシートに混ぜてコピーする。
No.をキーにしてソートする。
未使用の列に、直上のセルと相違がある場合の表示を出す関数を入れる。たとえば、a列がNo、b列が比較したい項目,c列が上記で追加したシート番号のとき、d列すべてに

=if(a2=a1,if(b2=b1),"","※"))

などと入れておけば、シート1とシート3の同じNo.のBに相違がある行に※が表示されます。