Excel。シート間のデータの違いを見つけて、マークをつけたい
＜条件付き書式とIF+SUMPRODUCT関数＞
訂正前というシートに次のようなデータがあります。
そして、訂正後というシートに次のようなデータがあります。
訂正前と訂正後で、どのレコードが修正されたのかが、
わかるように、H列に「修正あり」と表示したいとします。
その場合はどのようにしたらいいのでしょうか？
当然、目視で確認するというのでは、大変なことになります。
数値ならば、別シートなどに、
例えば、=訂正前!C2－訂正後!C2 という数式を作り、
その結果が、0（ゼロ）だったならば、
修正していないということがわかりますが、
文字の場合だと、IF関数を使わないといけないといけませんし、
データ量が膨大になると、数式のコピーも大変な作業となってしまいます。
そこで、次のようにしていくには、どのようにしたらいいのでしょうか？
H列に「修正あり」と表示することができました。
こうすることで、オートフィルターなどで、
「修正あり」という文字だけを抽出してあげれば、
どのデータが変わったのかが分かりやすくなります。
では、H2の数式はどのようになっているのかを先にご紹介します。
=IF(SUMPRODUCT((訂正前!C2:G2=訂正後!C2:G2)*1)=5,"","修正あり")
IF+SUMPRODUCT関数のネストになっています。
SUMPRODUCT関数は、
掛け算をして、その合計を算出する関数ですが、
この数式がどのような動きをしているのかを確認したいので、
4行目に空白行を用意して、
合計しないで掛け算だけを算出するPRODUCT関数を使って、算出してみます。
C4の数式は、
=PRODUCT(訂正前!C3=訂正後!C3)
合致していると、TRUEなので、1を返します。
合致しないとFALSEなので0を返します。
この算出された数値を合算すると、4になりますよね。
全部合致していれば、5なので、4と算出されていれば、
合致していない箇所。
すなわち、「修正あり」ということがわかるという仕組みです。
なので、掛け算と合計を求める、SUMPRODUCT関数を使っています。
そして、条件式が一つで、掛け算の値を合計しないで、
セルの数を数える場合は、×1とすると、
0と1の数値に変換した数式を作成する必要がありますので、×1しています。
=IF(SUMPRODUCT((訂正前!C2:G2=訂正後!C2:G2)*1)=5,"","修正あり")
という数式を使うことで、比較することができます。
なお、視覚的にわかるようにするだけならば、
条件付き書式を使うと、簡単に比較することが出来ますよ。
C2:G5を範囲選択して、条件付き書式の新しいルールを選択します。
数式を使用して、書式設定をするセルを決定をクリックして、
数式には、
=訂正前!C2<>訂正後!C2
として、書式を設定してOKボタンをクリックしましょう。
このように、変更したセルを見つけたい場合には、
条件付き書式を使うといいですね。
