例えば1月に購入した住所録データと6月に購入した住所録データを比較して、1月の住所録データにな無かった新しく追加された情報を知りたい、その反対に6月の住所録データから削除された情報を知りたいと言う事があった場合、皆さんはどう対処していますか？
これから説明する内容は2つのEXCELシートで作成された住所録データがあり、その比較をして増えているデータ、削除されているデータを知る方法を説明します。
EXCEL関数には便利な関数が用意されていて、指定した行または列の中で、指定した数値や文字列などを探し、発見したデータが何番目にあるのかを返す関数があります。
それがMATCH関数です。
このMATCH関数を使うことで2つのメリットがあります。
2つのシート間のデータを照合する事で、互いのシートの何行目又は何列めにデータあるのかを知ることができます。
たとえば「日本商事」という会社はSheet1の何番目にあるのか、Sheet2の何番目にあるのかを知ることができます。
無ければエラーを返してきます。
このエラーを利用する事で、新規に追加されたデータ、削除されてたデータを知ることができます。
よって、一つ目は互いのデータがどこにある方知ることができ、無いデータを知ることができます。
実例を見る前に、「MACH関数」のしくみを見ておきましょう。
MACH関数の書式
MATCH（検査値,検査範囲,[照合の型]）
という指定を行います。
検査値とは、照合する値となりますが、住所録データの場合は重複しないデータとしては電話番号になるので、電話番号を指定すると良いですね。
検査範囲とは、検査するセルの範囲となります。Sheet1の場合はSheet2の電話番号のセル範囲となります。
照合の型には3つ用意されています。
1または省略で最大値を検索
0は検査値と等しい最初の値を検索
-1は検査値以上の最小の値を検索
となりますので、住所録データの場合は0の同一の電話番号を照合すると言うことになります。
照合され同じデータが見つかるとそのデータが存在する行番号が表示されます。
同じデータが無いとエラー「#N/A」が表示されます。
このエラーが表示されるデータが、関数を指定したシートの場所によって、追加されたデータ、削除されたデータとなります。
では、実際の住所録データをひとつのブックに用意します。
Sheet1に1月の住所録データ、Sheet2に6月の住所録データをコピー＆ペーストで準備します。
Sheet1及びSheet2いずれも
A列は会社名、B列は郵便番号、C列は住所、D列は電話番号となっています。
照合は、先に説明した重複のないD列の「電話番号」で行います。
Sheet1及びSheet2のE列にMATCHIH関数を指定しますので、E列の項目に照合結果という名前を付けておきましょう。
Sheet1のE2セルには
=MATCH(D2,Sheet2!D:D,0)
と言う計算式を指定します。
Sheet1のD2に指定する電話番号と、Sheet2のD列にある電話番号と同じ電話番号の行番号を求めるという式になります。見つかるとSheet2の行番号を表示、見つからなければエラー「#N/A」が表示されます。
E2セルを住所録の存在する最終行までコピーします。
この「#N/A」が6月の住所録データから削除されたデータとなります。
同様に
Sheet2のE2セルには
=MATCH(D2,Sheet1!D:D,0)
と言う計算式を指定します。
Sheet2のD2に指定する電話番号と、Sheet1のD列にある電話番号と同じ電話番号の行番号を求めるという式になります。見つかるとSheet1の行番号を表示、見つからなければエラー「#N/A」が表示されます。
E2セルを住所録データの存在する最終行までコピーします。
この「#N/A」が1月の住所録から追加されたデータとなります。
いずれも、EXCELの「フィルター」機能を使い「#N/A」を取り出すことで、それぞれ削除されたデータと、新規に追加されたデータが分かります。
これは住所録データだけではなくいろいろなシート間の照合に役立つと思いますので、是非記憶にとどめておくと便利ですよ。
※二つのファイルに分かれている場合は、一つのブックにコピー&ペーストで行って下さい。
今回は2015年のピアノ調律師と2016年のピアノ調律師の住所録を比較してみました。
2015年のピアノ調律師（Sheet1）の住所録件数は2,133件でした。
2016年のピアノ調律師（Sheet2）の住所録件数は1,658件でした。
単純に見ると475件の減少となります。
減少シートには513件のデータがあります。
増加シートには38件のデータがあります。
減少513件から増加38件をひくと475件となります。
さらには「アーアーアーアンシンピアノの調律・修理サービス生活救急車ＪＢＲ」が2016年の住所録からは404件削除されている事も分かります。実際には71件の減少と言う事も結果のデータから見て取れます。
【サンプルデータ】
ピアノ調律師比較ファイル：pianohikaku.xlsx （ファイル名をクリックするとエクセルファイルをダウンロードできます。）
このファイルに今回説明したデータが貼り付けてありますので、比較したいデータをそれぞれ「Sheet1」、「Sheet2」と名前を付けて増減シートに表示される結果をご利用下さい。
【ファイルのシート構成及びデータ】
Sheet1：2015年の全国ピアノ調律師住所録
Sheet2：2016年の全国ピアノ調律師住所録
減少：2015年より2016年住所録で減少したデータ513件
増加：2015年より2016年住所録で増加したデータ38件