エクセルでデータを照合する方法

マイクロソフト・エクセルの数ある機能のひとつに、2つのデータ・リストを比較し、リスト間の一致を確認したり、片方のリストにしかない項目を特定したりする機能がある。これは、財務記録を比較したり、特定の名前がデータベースにあるかどうかを確認したりする場合に便利です。MATCH 関数を使用して、一致するレコードまたは一致しないレコードを識別してマークすることも、COUNTIF 関数を使用して条件付き書式を使用することもできます。次の手順では、それぞれを使用してデータを照合する方法を説明します。

方法1

MATCH関数でレコードを特定する

1. データ リストを 1 つのワークシートにコピーします。Excelでは、1つのワークブック内の複数のワークシート、または複数のワークブックで作業することができますが、1つのワークシートに情報をコピーすると、リストの比較が簡単になります。
2. 各リスト項目に一意な識別子を付けます。2つのリストがそれらを識別する共通の方法を共有していない場合、与えられたリストの項目がもう1つのリストの項目に関連しているかどうかをExcelが確認できるように、その項目を識別する追加の列を各データリストに追加する必要があるかもしれません。この識別子の性質は、マッチさせようとしているデータの種類によって異なります。列リストごとに識別子が必要になります。

   • 納税記録など、ある期間に関連する財務データの場合、これは資産の説明、資産の取得日、またはその両方となります。場合によっては、項目をコード番号で識別することもできます。しかし、両方のリストで同じシステムを使用していない場合、この識別子によって、一致する項目がないのに一致したり、一致するはずの項目が無視されたりすることがあります。
   • 場合によっては、1つのリストから項目を取り出し、別のリストの項目と組み合わせて、物理的な資産の説明や購入年などの識別子を作成することができます。このような識別子を作成するには、アンパサンド（&）を使用して2つ以上のセルのデータを連結（追加、結合）します。セルF3の品目の説明とセルG3の日付をスペースで区切って結合するには、その行の別のセル、たとえばE3に「=F3&」「&G3」という数式を入力します。年だけを識別子に含めたい場合（一方のリストは完全な日付を使用し、もう一方は年だけを使用するため）、代わりにセルE3に'=F3&" "&YEAR(G3)'を入力してYEAR関数を含める。(一重引用符は入れないでください。）
   • 数式を作成したら、数式のあるセルを選択して、数式をコピーしたい列の他のセル上にフィル・ハンドルをドラッグすることで、識別子列の他のすべてのセルに数式をコピーできます。マウスボタンを放すと、ドラッグした各セルに数式が入力され、セル参照は同じ行の適切なセルに調整されます。
3. 可能な限りデータを標準化する。頭では「Inc.」と「Incorporated」が同じ意味であると認識していても、エクセルではどちらかの単語を再フォーマットさせない限り認識されません。同様に、あなたは$11,950と$11,999.95のような値を十分に近いものと考えるかもしれませんが、エクセルではあなたが指示しない限り一致しません。

   • Co "は "Company"、"Inc "は "Incorporated "といった省略形は、LEFT文字列関数を使用して追加文字を切り捨てることで対処できます。その他の略語、例えば "Association "の "Assn "などは、データ入力のスタイル・ガイドを確立し、不適切な書式を検索して修正するプログラムを書くことで対処するのが最善でしょう。
   • ZIP+4サフィックスを含むものと含まないものがある郵便番号のような数値の文字列の場合、LEFT文字列関数を使用して、主要な郵便番号のみを認識し、マッチさせることができます。近いけれども同じではない数値をExcelに認識させるには、ROUND関数を使用して、近い数値を同じ数値に丸め、一致させることができます。
   • 単語と単語の間に1つではなく2つのスペースを入力するなど、余分なスペースはTRIM関数を使用して削除することができます。
4. 比較式の列を作成する。リスト識別子用の列を作成しなければならなかったのと同様に、比較を行う数式用の列を作成する必要があります。各リストに1つのカラムが必要です。

   • これらの列には、"Missing?" のようなラベルを付けたい。
5. 各セルに比較式を入力します。比較式には、Excelの別の関数ISNAの中にネストされたMATCH関数を使用します。

   • この数式は、"=ISNA(MATCH(G3,$L$3:$L$14,FALSE)) "という形式をとり、最初のリストの識別子列のセルが、2番目のリストの各識別子と比較され、そのうちの1つと一致するかどうかを確認します。一致しない場合、レコードが見つからず、そのセルに "TRUE "という文字が表示される。もし一致すれば、レコードは存在し、"FALSE "と表示される。(数式を入力するときは、引用符で囲まないでください)。
   • セル識別子の数式をコピーしたのと同じ方法で、列の残りのセルに数式をコピーできます。この場合、2 番目のセル識別子のリストで最初と最後のセルの行参照と列参照の前にドル記号を置くと絶対参照になるので、識別子セルのセル参照だけが変わります。
   • 最初のリストの比較式を2番目のリストの列の最初のセルにコピーすることができます。そして、セル参照を編集して、"G3 "を2番目のリストの最初の識別子セルの参照に置き換え、"$L$3:$L$14 "を2番目のリストの最初と最後の識別子セルに置き換える必要がある。(ドル記号とコロンはそのままにしておきます。) 次に、この編集した数式を2番目のリストの比較行の残りのセルにコピーします。
6. 必要に応じて、一致しない値を見やすくするためにリストを並べ替えます。リストが大きい場合は、ソートして一致しない値をまとめておく必要があるかもしれません。以下のサブステップの指示は、再計算エラーを避けるために数式を値に変換し、リストが大きい場合は長い再計算時間を避けます。

   • リストのすべてのセル上にマウスをドラッグして選択します。
   • Excel 2003の場合は「編集」メニューから、Excel 2007または2010の場合は「ホーム」リボンの「クリップボード」グループから「コピー」を選択します。
   • Excel2003の「編集」メニューから「特殊貼り付け」を選択するか、Excel2007または2010の「ホーム」リボンの「クリップボード」グループにある「貼り付け」ドロップダウン・ボタンから「特殊貼り付け」を選択する。
   • 特殊貼り付け」ダイアログボックスの「名前を付けて貼り付け」リストから「値」を選択します。OKをクリックしてダイアログを閉じます。
   • Excel 2003の場合は「データ」メニューから、Excel 2007または2010の場合は「データ」リボンの「並べ替えとフィルタ」グループから、「並べ替え」を選択します。
   • 並べ替え］ダイアログの［自分のデータ範囲にあるもの］リストから［ヘッダー行］を選択し、［欠落しているか］（または比較列ヘッダーに実際に付けた名前）を選択して［OK］をクリックする。
   • もう一方のリストについてもこの手順を繰り返す。
7. 一致しない項目を視覚的に比較し、一致しない理由を確認する。前述したように、Excelは、おおよその一致を探すように設定しない限り、完全に一致するデータを探すように設計されています。一致しない項目は、文字や数字の入れ違いのような単純なものかもしれません。また、リストアップされた資産がそもそも報告される必要があったかどうかをチェックするなど、独自の検証が必要な場合もあります。

方法2

COUNTIFによる条件付き書式設定

1. データ・リストを1つのワークシートにコピーする。
2. どのリストで、一致するレコードまたは一致しないレコードを強調表示するかを決めます。一方のリストのレコードだけを強調表示したい場合は、そのリストに固有のレコード、つまり、もう一方のリストのレコードと一致しないレコードを強調表示することになるでしょう。両方のリストのレコードを強調表示したい場合は、互いに一致するレコードを強調表示することになります。この例では、最初のリストがセル G3 から G14 を占め、2 番目のリストがセル L3 から L14 を占めると仮定します。
3. 一意または一致する項目を強調表示したいリストの項目を選択します。両方のリストで一致する項目を強調表示したい場合は、リストを1つずつ選択し、それぞれのリストに比較式（次のステップで説明）を適用する必要があります。
4. 適切な比較式を適用する。これを行うには、お使いのExcelのバージョンで条件付き書式設定ダイアログにアクセスする必要があります。Excel2003では、「書式」メニューから「条件付き書式」を選択し、Excel2007と2010では、「ホーム」リボンの「スタイル」グループにある「条件付き書式」ボタンをクリックします。ルールの種類を「数式」に選択し、「ルールの説明を編集」フィールドに数式を入力します。

   • 最初のリストに固有のレコードを強調表示する場合、数式は「=COUNTIF($L$3:$L$14,G3=0)」となり、2 番目のリストのセル範囲は絶対値として、最初のリストの最初のセルへの参照は相対値として表示されます。(クローズ引用符は入力しないでください）。
   • 2 番目のリストに固有のレコードを強調表示したい場合は、数式は "=COUNTIF($G$3:$G$14,L3=0) "となり、1 番目のリストのセル範囲は絶対値として、2 番目のリストの最初のセルへの参照は相対値として表示されます (クローズ引用符は入力しないでください)。(クローズ引用符は入力しないでください）。
   • 各リストのレコードのうち、もう一方のリストにあるレコードをハイライトしたい場合は、最初のリスト用と2番目のリスト用の2つの数式が必要になる。最初のリストの式は「=COUNTIF($L$3:$L$14,G3>0)」で、2番目のリストの式は「COUNTIF($G$3:$G$14,L3>0)」です。前述のように、最初のリストを選択してその数式を適用し、次に2番目のリストを選択してその数式を適用します。
   • フラグを付けるレコードを強調表示するために、任意の書式を適用します。OKをクリックしてダイアログを閉じる。

この記事は、CC BY-NC-SAの下で公開されている「 How to Match Data in Excel 」を改変して作成しました。特に断りのない限り、CC BY-NC-SAの下で利用可能です。