例えば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：2015年の全国ピアノ調律師住所録
Sheet2：2016年の全国ピアノ調律師住所録
減少：2015年より2016年住所録で減少したデータ513件
増加：2015年より2016年住所録で増加したデータ38件
3つのスタイルでの事業者住所録データの販売を行っています。
※住所録データをご購入の場合は「Ｗｅｂマニュアル」をご用意しておりますので、ご一読下さい。
※住所録データのサブスクリプションをご希望の方は下記バナーをクリックして下さい。
※住所録データの業種と地域を指定してのデータをご希望の方は下記バナーをクリックして下さい。
※皆様のご要望から生まれた都道府県別全業種事業者住所録データです。
