目次
Excelで2つの列を比較し、一致または相違を取得し、それらを強調表示し、あるいはデータを引き出すための完全なガイドです。
Excelでデータを扱っていると、データ間の類似点や相違点を見つけるために列を比較する必要がある場合があります。列の比較は、データの整理や分析に非常に有効です。2つの列のデータを手動で比較するのは時間がかかり、疲れる作業なので、Excelのさまざまな数式を使用して列を一致させることができます。
Excel には、列を比較し、一致するデータと一致しないデータを見つけるための方法と関数がいくつかあります。VLOOKUP、MATCH、AND、INDEX、IF、COUNTIF、ISERROR、IFERRORといった論理演算子や、条件付き書式のルールを使って、データを比較したり一致させたりすることが可能です。この記事では、Excelで列を比較して一致と相違を求めるさまざまな方法について説明します。
2つの列を行ごとに比較し、一致または相違を確認する
Excelで2つの列を比較する最も簡単な方法は、行と行の単純な比較です。この方法では、ある列の値が、同じ行の別の列の値と一致するかどうかをチェックします。この方法は、データセット全体ではなく、同じ行の値のみを比較します。2 つの列を行ごとに比較するために使用できる数式には、単純な比較演算子、IF 関数、EXACT 関数を使用したさまざまなタイプがあります。
等比演算子による列の比較
2 つの列のデータを 1 行ずつ比較して一致するものを見つける最も簡単な方法は、比較演算子を使用することです。Equals to’ (=) を使用すると、2 つの列のセルを比較して一致するかどうかを調べ、その結果を true または false として得ることができます。
例1．
例えば、2つの列（下のスクリーンショットではBill DueとBill Paid）を比較して、それらが一致するかどうかを確認することにします。そのためには、以下のような簡単な数式を使用します。
=B2=C2
B2 の値は C2 の値と一致するので、数式は TRUE を返します。まず、セル D2 に数式を入力し、B 列と C 列を 1 行ずつ比較するために、塗りつぶしハンドルをドラッグして他のセルにコピーします。塗りつぶしハンドルは、選択したセルの右下隅にある小さな緑色の四角形です。
セルD2からD12まで塗りつぶしハンドルをドラッグすると、カーソルが黒いプラス記号に変化します。
セル D2 から D12 に数式を適用すると、行ごとに値が比較され、一致する行と一致しない行があることがわかります。例えば、セル B4 の値は隣接するセル C4 と一致しないので、セル D4 の値は「偽」である。
例2．
上記の数式がどのように数字を扱うかを見てきましたが、日付、時間、テキスト文字列も同様に比較することができます。この式がテキスト値を持つ列をどのように比較するかを見てみましょう。
=A2=B2
この式は、2つの列が正しく一致するかどうかを調べ、スペース1文字さえも見逃さない。そして、条件を満たした場合は TRUE を、そうでない場合は FALSE を返します。セル A2 の請求先住所はセル B2 の配送先住所と一致するので、結果として TRUE が得られます。また、セル A5 の住所はセル B5 の住所と一致しない – セル B5 では最後の文字が異なっている。従って、FALSEを返します。
IF関数による列の比較
2つの列を一行ずつ比較するもう一つの方法は、IF関数を使うことです。IF関数は、条件や基準を満たすかどうかをチェックし、条件がTRUEの場合は指定した1つの値を返し、条件がFALSEの場合は別の値を返します。この方法は上記の方法と似ていますが、TRUEやFALSEだけでなく、より説明的な結果を得るために使用することができます。
例えば、以下の式で2つの列を比較し、一致するものがあれば「Paid」、一致しないものがあれば「Not Paid」という結果を得ることができるのです。
=IF(B2=C2,\"Paid\",\"Not Paid\")
上記の式では、IF関数がB2の値とC2の値が等しいかどうかをチェックし、その条件がTrueであれば、テキスト「Paid」を返します。Falseの場合は、Not Paidと表示される。B2のBill dueとC2のBill Paidは同じなので、D2には "Paid "が返されます。しかし、B5の金額とC5の金額は一致しないので、D5には「未払い」が返されます。
試合専用。
2列で一致するものだけを探したい場合は、以下の式を使用することができます。
=IF(B2=C2,\"Paid\",\"\")
上記の式は、B列の値がC列の値と等しいかどうかを、1行ずつチェックする。条件が真であれば「Paid」という文字列が得られ、条件が偽であれば何も得られない（空文字列）。
差分のみ。
同じ行で異なる値を持つセルを見つけるには、次の式を試してみてください。
=IF(B4C4,\"Not Paid\",\"\")
上記の式は、B列の値がC列の値と等しくないかどうかを、1行ずつチェックします。条件が真であれば「未払い」の文字列が得られ、条件が偽であれば何も得られない（空文字列）ことになります。
注：equals to式とIF関数式は、大文字と小文字を区別しませんので、テキスト値を比較するときに大文字と小文字を無視します。
EXACT関数で同じ行の大文字小文字を区別して2列を比較する
上記の数式では、テキスト値を比較する際に大文字と小文字を区別しています。大文字と小文字を区別して比較したい場合は、EXACT関数を使用する必要があります。ExcelのEXACT関数は、2つのテキスト文字列を比較し、両方の値が同じであれば「真」、そうでなければ「偽」を返します。EXACTは単独で使用することもできますし、IF関数と一緒に使用することもできます（単に「真」や「偽」ではなく、説明的な結果を得たい場合など。
例えば、異なるデータベースの会社名リストを比較し、それらが完全に一致するかどうかを単純なEXACT関数を使って調べてみましょう。
=EXACT(A2,B2)
上記の式は、A2とB2のテキスト文字列が大文字と小文字を完全に一致するかどうかをチェックします。すると、A2では "St "が小文字で、B2では大文字なので、FALSEが返される。
記述的な結果を得たい場合は、IF関数とEXACT関数を併用する必要があります。
=IF(EXACT(A3,B3),\"Match Filter", \"Check Database Filter")
上記の数式では、EXACT 関数はセル A3 と B3 の値が大文字と小文字の完全一致かどうかをチェックします。しかし、B2 の最初の単語 ‘ANGELO’ は大文字で、A2 の会社名とは異なるため、EXACT 関数は FALSE を返します。したがって、IF関数はFALSEの出力に対して「データベースの確認」テキスト文字列を返します。
5行目では、セル値A5とB5が大文字小文字を区別して一致するので、IF関数はEXACT関数からTRUE結果を得て、その代わりに「MATCH」を返します。
大なり小なり2つの列を比較する
列を比較して、ある列の値が他の列より大きいか小さいかを調べたいことがあります。たとえば、日付の列が2つあり、同じ行でどちらの日付が後か比較したい場合（商品の賞味期限を比較する場合など）、簡単な論理演算で調べることができます。
賞味期限が切れているかどうかは、C列がB列より大きい場合に2つの列を比較します。
=IF(C2>B2,\"Yes\",\"No\")
上記の数式は、セルC2の値がセルB2より大きいかどうかをチェックします。TRUEであればIF関数で「Yes」、そうでなければ「No」を返します。
複数の列を行ごとに比較し、一致を確認する
2 つの列を行ごとに比較する方法を見てきましたが、同じ行の複数の列を比較して一致させることもできます。複数の列を比較する方法には、同じ行のすべてのセルで一致するものを探す方法と、同じ行の任意の2つのセルで一致するものを探す方法の2つがあります。
同じ行内のすべてのセルで一致するものを検索する
方法1：2列以上（マルチカラム）のデータセットで、すべての列で同じ値を持つ行を探したい場合。IF関数とAND関数を使えば可能です。
=IF(AND(A3=B3,A3=C3),\"All Match\",\"\")
AND関数は、複数の条件を同時にテストし（A3=B3とA3=C3）、その引数がすべてTRUEと評価された場合のみ、TRUEを返します。AND関数は、引数の1つがFALSEと評価された場合でも、FALSEを返します。AND関数では、各条件の間にカンマを入れることで、複数の条件を追加することができます。
下図のように、AND関数は、同じ行のすべてのセルが同じ値であれば真を返します。そして、IF関数は、AND関数がTRUEを返した場合、「すべて一致」というテキストを返します。
方法2：データセットに多くの列がある場合、COUNTIF関数を使用して数式をコンパクトにすることができます。
=IF(COUNTIF($A3:$D3, $A3)=4, \"All match", \"All match")
ここで、4は数式で比較する列の数を表します。特定の1つの基準を満たす数を数えるには、COUNTIF関数を使用します。
COUNTIF式は、行内のすべてのセル（A3:D3）に同じ値があるかどうかを調べ、一致した数の合計を返します。そして、同じ行内で一致した列の数（COUNTIF関数の結果）と一致した列の数が同じなら、「すべて一致」という文字列が得られます。
同じ行の任意の2つのセルでマッチを検索する
方法1：複数（3）の列があり、同じ行の2つの列のいずれかに一致するものを見つけたい場合、IF関数とOR関数の助けを借りてそれを行うことができます。これを行うには、以下の式を使用します。
=IF(OR(A3=B3, B3=C3, A3=C3), \"Match", \"Match")
上の式では、OR関数は各列と他の列を比較し、同じ行内で同じ値を持つ2つ以上の列のいずれかが一致すれば、TRUEを返します。IF関数は、OR関数からTRUEを得ると、「一致」というテキストを返します。
方法2：比較する列が多すぎる場合、上記のOR式が大きくなりすぎて複雑になることがあります。これを避けるために、いくつかのCOUNTIF関数を追加することができます。
=IF(COUNTIF(B3:D3,A3) COUNTIF(C3:D3,B3) (C3=D3)=0,\"Unique\",\"Match\")
ここでは、最初のCOUNTIF関数で1列目（A3）と同じ値を持つセル（列）がいくつあるかを調べ、数え、2番目のCOUNTIF関数で2列目と同じ値を持つ列がいくつあるかを調べ、といった具合にします。そして、すべてのCOUNTIF関数の結果が合計されます。つまり、最終的なカウントが0に等しい場合、この数式は「Unique」というテキスト文字列を返します。カウントが0以外の場合は、結果として「Match」が得られます。
一致する/不一致する列を比較し、強調表示する
2つの列を比較して、結果を別の列に表示するのではなく、一致するデータまたは不一致のデータがある行を強調表示したい場合、Excelでは条件付き書式を使用できます。条件付き書式は、一連のルールに基づいてデータを強調表示できるExcelの機能です。条件付き書式を使用すると、2つの列で一致する値や異なる値を視覚的に識別することができます。
2 つの列を比較し、同じ行の一致するデータを強調表示する（Side by Side）
2つの列を比較し、同じ行の同じデータをハイライトしたい場合は、以下の手順で行います。
まず、比較・強調したいセルを選択します。1つの列を選択するか、行全体をハイライトしたい場合は複数の列を選択することができます。
ホーム」タブで、「スタイル」グループの「条件付き書式」メニューをクリックし、メニューから「新規ルール…」を選択します。
これにより、「新しい書式設定ルール」ダイアログボックスが表示されます。そのダイアログウィンドウで、ルールの種類「数式を使用して書式設定するセルを決定する」を選択します。
その後、「Format values where this formula is true:」フィールドに次の数式を入力します。
=$A1=$B1
見ての通り、これはセルA1の値がB1と等しいかどうかをチェックする単純な「equals to」式です。しかし、列ラベル A と B の前に「$」記号を追加して、列を絶対参照に固定しています。そのため、数式を適用すると、各行ごとに行番号だけが自動的に変更されます。
次に、「書式」ボタンをクリックして、ハイライトされた行の外観をカスタマイズします。
セルの書式設定」ダイアログウィンドウでは、フォントサイズ、フォントカラー、セルの枠、数値の書式などを変更することができます。異なる背景色で一致する行をハイライトするには、「塗りつぶし」タブに切り替えて、「背景色」のセクションから色を選択します。また、ハイライトされたセルのパターンスタイルとパターンカラーを変更することができます。書式の選択が終わったら、「OK」ボタンをクリックします。
再度、「新しい書式ルール」ダイアログボックスで「OK」をクリックすると、書式が適用されます
A列とB列の両方で値が一致するセルは、下図のようにハイライト表示されます。
表中の一致するデータが不一致のデータより少ない場合、条件を反転させて2列のデータ差を強調することができます。
例えば、A列とB列の違いを強調するために、以下の条件付き書式規則のいずれかを使用することができます。
=$A1$B1
または
=$A1=$B1=FALSEとする。
まず、データセットを選択し、上でご紹介したように「新しい書式ルール」ウィンドウを開き、「書式設定するセルを数式で決定する」ルールタイプを選択します。そして、上記のルールのいずれかを入力し、「書式設定」ボタンをクリックします。
次に、適用したい書式を選択し、「OK」をクリックします。そして、もう一度’OK’をクリックして、フォーマットを適用してください。
2つの列を比較し、重複する値を強調表示する
2つの列を比較し、同じ行にない場合でも、両方の列に存在する値を強調したい場合は、プリセットの条件付き書式ルールまたはカスタム書式ルールを使用することができます。
例えば、異なるお店の果物のリストが2つあり、両方のお店で購入できる果物を強調したい場合です。その方法は次のとおりです。
まず、比較したい列を選択し、「スタイル」グループから「条件付き書式」メニューをクリックします。
次に、ドロップダウンメニューから「セル規則の強調表示」オプションにカーソルを合わせ、「値の重複」オプションを選択します。
値の複製]ダイアログボックスで、左側のドロップダウンから[複製]を選択します。
次に、右側のドロップダウンメニューから形式を選択し、「OK」をクリックします。
両列に存在する項目がハイライト表示されます。
また、カスタムフォーマットルールを使用して、2つの列で重複する値を強調表示することもできます。
そのためには、まず、A列を選択し、リボンから「条件付き書式」オプションをクリックします。そして、メニューから「新規ルール」を選択します。
その後、「数式を使用して書式設定するセルを決定する」ルールタイプを選択し、A列のマッチをハイライトするために以下のルールを入力します。
=COUNTIF($B$2:$B$12, $A2)>0
次に、「書式」ボタンをクリックして、適用したい書式を選択し、それらを適用します。
Ok」をクリックして、A列に書式を適用します。
次に、B列を選択し、リボンから「条件付き書式設定」オプションをクリックします。次に、メニューから「新規ルール」オプションを選択します。
新しい書式設定ルール」ウィンドウで、「数式を使用して書式設定するセルを決定する」ルールタイプを選択し、B列の重複を強調するために以下のように入力します。
=COUNTIF($A$2:$A$12, $B2)>0
数式を入力したら、「書式」ボタンをクリックして、セルを強調表示するための書式を指定します。
フォーマットを選択したら、「OK」をクリックして適用します。
これで、両方の列で重複する値が強調表示されました。
2つの列を比較し、ユニークな値を強調表示する
この方法は、上記の方法と正反対です。2つの列を比較して、一致しない両方の列のユニークな値だけをハイライトしたい場合、これにも条件付き書式を使うことができます。
まず、比較したい列を選択し、「ホーム」タブを開き、「スタイル」グループの「条件付き書式」メニューをクリックします。
次に、「セルの規則を強調表示」オプションにカーソルを合わせ、「値の複製」を選択します。
重複」というドロップメニューで「一意」を選択し、不一致のデータに対して定義済みの書式を選択します。その後、「OK」をクリックします。
これで、両方の列からユニークな値または不一致の値が強調表示されます。
また、カスタムフォーマットルールを使用して、2つの列で一意の値を強調することもできます。
そのためには、まず、A列を選択し、リボンから「条件付き書式」オプションをクリックします。そして、メニューから「新規ルール」を選択します。
その後、「数式を使用して書式設定するセルを決定する」ルールタイプを選択し、A列のマッチをハイライトするために以下のルールを入力します。
=countif($b$2:$b$12, $a2)=0
次に、「書式」ボタンをクリックして、書式を選択します。
Ok」をクリックして、A列に書式を適用します。
次に、B列を選択し、リボンから「条件付き書式設定」オプションをクリックします。次に、メニューから「新規ルール」オプションを選択します。
新しい書式設定ルール」ウィンドウで、「数式を使用して書式設定するセルを決定する」ルールタイプを選択し、B列の重複を強調するために以下のように入力します。
=countif($a$2:$a$12, $b2)=0
数式を入力したら、「書式」ボタンをクリックし、セルをハイライトするための書式を指定します。その後、「OK」をクリックして適用します。
これで、両方の列の一意な値が強調表示されました。
複数の列を比較し、一致する行をハイライト表示する
ここまで、2つの列を比較し、一致する行をハイライトする方法を見てきましたが、比較する必要がある複数の列がある場合、条件付き書式を使用してそれを行うこともできます。条件付き書式を使用すると、複数の列を行ごとに比較し、一致する行を強調表示することができます。
例えば、3つのお店の果物のリストがあり、3つの列すべてに同じものがある行をハイライトしたいとします。そのためには、次のような手順を踏みます。
まず、比較する列を選択します（A2:D12）。次に、「条件付き書式設定」メニューをクリックし、「新しいルール…」オプションを選択します。
VLOOKUP関数を使って2つの列を比較し、一致するデータを取得することができます。
=vlookup(d2,$a$2:$b$13,2,false)です。
まず、セルE2に数式を入力し、フィルハンドルをドラッグして数式を列の下にコピーします。
ここで、D2はルックアップテーブルの最初の列で検索する必要がある値である。A$2:$B$13 は、値が検索され、対応する値が引き出されるルックアップテーブルを表します。ここでは、数式がコピーダウンされるときにセル参照が変更されないように、範囲を絶対参照にロックしています。
式中の「2」は、抽出したい値を持つルックアップテーブルの列番号である。FALSE パラメータは、D2 の完全一致を探すために使用されます。
上記の数式は、範囲 A2:B13 の最初の列 (A列) を検索して、D2 の値を取得します。D2 の完全一致が A 列の 5 行目で見つかったので、対応する値が B 列 (2 列目) から抽出され、E2 に返されます。この数式をE列の下にコピーすると、lookup_valueの値だけがD3、D4などに自動的に調整されて、A2:B13の範囲でD列の各値が検索される。
IFERROR または IFNA 関数を使用して列を比較し、一致するデータを取得する
ルックアップテーブルにルックアップ値がない場合、またはルックアップ値がルックアップテーブルの値の完全なコピーでない場合、#N/Aエラーが発生します。
以下の例では、lookup_value（D3およびD5）がA列で見つからず、マッチタイプがFALSE（正確）であるため、数式は#N/Aエラーを返します。
これは、look_upの値に余分なスペースや欠落、タイプミスがある場合にも起こりえます。このような場合、match_typeをTRUEに変更すると、数式が小さな誤差を無視して、値の近似的な一致を探すことができるようになります。
lookup_valueがテーブルで見つからない場合、IFNAまたはIFERROR関数を使用して#N/Aエラーを回避することができます。
=ifna(vlookup(d2,$a$2:$b$13,2,false),\"㊙")
この数式は、IFNAがエラーメッセージを空白に置き換えることを除いて、前のVLOOKUP数式と同じように動作します。また、この数式は、空白セルの代わりにテキストを返すようにすることもできます。
また、IFERROR関数を使用して、エラーメッセージを削除し、指定されたテキスト文字列を返すこともできます。そのためには、以下の数式を入力します。
=IFERROR(VLOOKUP(D2,$A$2:$B$13,2,FALSE),\"Not Available")
セルE2に上記の数式を入力し、列の下にコピーします。VLOOKUP関数が#N/Aエラーを返した場合、IFERROR関数で以下のように「利用できません」メッセージに置き換えます。
2つの列を比較し、ワイルドカードを使用して部分一致を検索する
2つの列の名前に細かな違いがある場合、VLOOKUP関数のTRUEパラメータではカバーしきれません。たとえば、一方の列に「Google」という値があり、もう一方に「Google LLC」という値がある場合、上記のVLOOKUP式では列を一致させることができません。しかし、数式にワイルドカードを追加することで、VLOOKUPを使用して列を部分的に一致させることは可能です。
VLOOKUP関数は、ワイルドカード文字を使用して指定された値の部分一致を検索することができます。任意の位置に検索値を含む値を検索したい場合は、アンパサンド記号（&）を追加して検索値をワイルドカード文字（*）で結合します。セルの絶対参照には「$」記号を使用し、ルックアップ値の前または後にワイルドカード「*」記号を追加します。
以下の例では、セル D3 にルックアップ値の一部（Fan）しかありません。そこで、与えられた文字に対して部分一致を行うには、セル参照の前後にワイルドカード「*」を連結する。
=VLOOKUP(\"*\"&D3&\"*\",$A$2:$B$13,2,FALSE)
上記の式では、D2 は ‘&’ 演算子で囲まれ、アスタリスク "*" がルックアップ値の前後にある欠落した文字を補うために使用されています。List 2に項目名全体がない場合、アスタリスク文字が足りない文字を補い、部分的に一致する列から値を引き出します。
例えば、セルD3には「Fan」という項目しかないが、列Aには「Table Fan」がある。しかし、D3の前にあるアスタリスク「*」が、ルックアップ値の前にある「Table」の欠落を補ってくれたのです。そこで、VLOOKUP関数は、B列から対応する値「31.68」を返します。
MATCH関数による2つの列の比較
値そのものではなく、一致した値の列内での位置を返したい場合は、MATCH 関数を使用します。
MATCH関数は、Excelの組み込み関数で、主に列や行の中のルックアップ値の相対位置を特定するために使用されます。
MATCH関数のシンタックス。
=MATCH(lookup_value,lookup_array,[match_type})とする。
ここで
lookup_value – 指定したセル範囲または配列の中から検索したい値。数値、テキスト値、論理値、あるいは値を持つセル参照を使用できます。
lookup_array – 値を検索するセルの配列．1つの列あるいは1つの行である必要があります。
match_type – オプションのパラメータで，0,1,-1のいずれかを指定します．
- 0 は完全一致を探し、見つからなければエラーを返します。
- -1 は，配列が昇順の場合に，lookup_value と等しいかそれ以上の値を持つ最小の値を探します。
- 1は、ルックアップ配列が降順のときに、ルックアップ値以下の最大の値を探します。
2つの列を比較し、完全に一致する位置を見つける
次のような表があり、D列の各値がA列のどの位置にあるかを調べるとする。
=match(d2,$a$2:$a$13,0)
この式は、リスト1の中からリスト2の各値を検索し、各値の位置を返します。
MATCH関数で重複や一致するデータを表示する
MATCH、ISERROR、IF関数の組み合わせで、列の重複を比較表示することができます。
例えば、以下のような数式で2列を比較し、1列目に重複したものを表示させることができます。
=if(iserror(match(a2,$b$2:$b$10,0)),\"une",a2)
ここでは、ISERROR関数とIF関数を組み合わせて、エラーを発見し、文字列や空白を表示しています。
MATCH関数はA2の位置（範囲B2:B10内）を検索して5と返します。 エラーではないのでISERROR関数はFALSEを返し、IF関数はA2の値を返します。別の例では、C6 の MATCH 関数は、A6 の値が範囲 B2:B10 で見つからなかったため、#N/A エラーを返します。したがって、ISERROR関数はTRUEを返し、その後、IF関数は空白を返します。
MATCH関数を使って一意なデータを表示する
2つの列を比較して、それぞれの列でユニークな値を表示したい場合、IF関数の最後の2つの引数を入れ替えるだけで、同じ上記の数式で行うことができます。
1列目にユニークな値を表示するには、以下の数式を入力します。
=if(iserror(match(a2,$b$2:$b$10,0)),a2,\"♪")
MATCH関数でA2の位置（B2:B10の範囲）を検索して5と返す。 結果はエラーではないので、ISERROR関数はFALSEを返し、IF関数は空白を返す。
C4 の MATCH 関数は、A4 の値が範囲 B2:B10 で見つからないため、#N/A エラーを返します。したがって、ISERROR関数はTRUEを返し、その後、IF関数はA4の値を返します。
2列目に一意な値を表示するには、以下の数式を入力します。
=if(iserror(match(b2,$a$2:$a$10,0)),b2,\")
MATCH関数はB2の位置（A2:A10の範囲）を見て5と返します。 結果はエラーではないので、ISERROR関数はFALSEを返し、IF関数は空白を返します。
C4 の MATCH 関数は、B4 の値が範囲 B2:B10 で見つからなかったため、#N/A エラーを返します。したがって、ISERROR関数はTRUEを返し、その後、IF関数はB4の値を返します。
INDEX関数とMATCH関数を使って2つの列を比較する
MATCH関数は、INDEX関数と組み合わせて、2つの列を比較したり、一致させたりすることができます。VLOOKUPと比較して、INDEX MATCHは2つの列を比較し、また一致するデータを引き出すことができる強力で多目的な数式です。
INDEX関数は、表や範囲内の特定の位置にある値を取得するために使用します。MATCH関数は、ある列または行における値の相対位置を返します。MATCH関数を組み合わせると、特定の値の行番号または列番号（位置）を見つけ、INDEX関数はその行番号と列番号に基づいて値を取得します。
INDEX関数の構文です。
=INDEX(array,row_num,[col_num],)
- array – 値を検索するセルが格納された配列．
- row_num – 値を返す配列の行を表します。row_num が省略された場合は， column_num が必要です．
- column_num – 値を返す配列の列を表します。column_num が省略された場合は、row_num が必要となる。
例
A列とD列の2つを比較し、INDEXとMATCHを使ってD列の価格（一致した値）を取得する。
=index($b$2:$b$13,match(d2,$a$2:$a$13,0))
セル E2 に数式を入力し、範囲 E3:E7 にコピーします。では、この数式がどのように機能するか見てみましょう。
INDEX関数は、値を取得するために行番号と列番号を必要とします。上記の式では、ネストされたMATCH関数が値D2の行番号（位置）を見つけます。そして、その行番号（5）を範囲B2:B13でINDEX関数に与えています。最後の引数に ‘0’ を指定して列番号を無視しているのは、配列の中で B 列 ($B$2:$B$13) のみを考慮しているためです。
最後に、INDEX関数は配列B2:B13の5番目の値である24.14を返します。
このようなエラーを回避するには、数式を IFERROR 関数で囲むとよいでしょう。
=ferror(index($b$2:$b$13,match(d2,$a$2:$a$13,0)),\"une")
ワイルドカードの使用
比較する2つの列の名前にほとんど差がない場合、数式にワイルドカードを追加することで列を部分的に一致させることができます。
MATCH関数でワイルドカードを使用できるのは、match_typeが’0’に設定され、検索値がテキスト文字列である場合のみである。MATCH関数で使用できるワイルドカードは、アスタリスク(*)とクエスチョンマーク(?)である。
- クエスチョンマーク(?)は、任意の1文字または1文字と文字列をマッチさせるために使用されます。
- アスタリスク (*) は、任意の数の文字と文字列をマッチさせるために使用されます。
以下のように、リスト 2 の名前はリスト 1 ほど完全ではないので、ワイルドカードを使用して不足している文字を補うことができます。
=INDEX($B$2:$B$13,MATCH(\"*\"&D2&\"*\",$A$2:$A$13,0))
上記の式では、D2は「&」演算子とアスタリスク「*」で囲まれ、ルックアップ値の前後にある欠落した文字を補っている。リスト2が項目名全体を持たない場合、アスタリスク文字が欠落した文字を補い、部分的に一致した列から値を抽出することになる。
VBAマクロを使用して2つの列を比較し、一致と相違を検索する
頻繁に、または繰り返し列を比較したり一致させたりする必要がある場合、VBAマクロを作成してこれらのタスクを自動化することができます。VBAコードを使用して、タスクや計算を実行するためにユーザーが作成したカスタム関数を作成することができます。ここでは、その方法を説明します。
VBAコードを使って2つの列を行ごとに比較し、違いを強調表示する
VBAマクロは、Excelで2つの列を比較する最も迅速かつ効果的な方法です。2つの列を比較し、それらの間の相違点を強調表示したい場合は、指示に従ってください。
まず、比較したい2つの列が含まれるワークブックを開きます。
次に、「開発者」タブを開き、リボンから「Visual Basic」オプションをクリックするか、Alt F11キーボードショートカットを押して、Microsoft Visual Basic for Applicationsを開いてください。
これにより、Microsoft Visual Basic for Applicationsが別ウィンドウで表示されます。VBAウィンドウで、「挿入」メニューをクリックし、「モジュール」オプションを選択します。または、左側のナビゲーションバーにある「Microsoft Excel Objects」を右クリックして、「挿入」をクリックし、サブメニューから「モジュール」を選択するだけでもOKです。
ここで、以下のVBAスクリプトをコピーして、新しいモジュールウィンドウに貼り付けてください。
Sub HighlightColumnDifferences()Dim Rg As RangeDim Ws As WorksheetDim FI As Integer On Error Resume NextSRC: Set Rg = Application.InputBox(\"Select Two Columns:\", \"Excel", , , , 8) If Rg Is Nothing Then Exit Sub If Rg.Columns.Count 2 Then MsgBox \"Please Select Two Columns\" GoTo SRC End IfSet Ws = Rg.RowSet.Count + $ + + + + $ $ + $ $ $ + $ + + + + + [ ] {d ] {d ] [d ] {l ] {d ] {d ] * *d} *D ?WorksheetFor FI = 1 To Rg.Rows.Count If Not StrComp(Rg.Cells(FI, 1), Rg.Cells(FI, 2), vbBinaryCompare) = 0 Then Ws.Range(Rg.Cells(FI, 1), Rg.Cells(FI, 2)).Interior.ColorIndex = 6 \’You can change the color index as you like.エンドイフネクスト FI エンドサブ
上記のコードでは、2つの列を行ごとに比較し、その差分をハイライトすることができます。
スクリプトを貼り付けた後、「ファイル」をクリックし、「XXXX（ファイル名）を保存」を選択すると、このモジュールがマクロとして保存されます。
VBスクリプトは、マクロが有効なファイルタイプで保存する必要があります。保存」をクリックすると、このファイルをマクロなしのファイルに保存するか、マクロを有効にしたファイルタイプに保存するかを尋ねるプロンプトボックスが表示されます。
マクロが有効なファイル形式を選択する場合は、「いいえ」をクリックしてください。
名前を付けて保存」ウィンドウで、「保存の種類」ドロップダウンから「Excel マクロ有効ワークブック (*.xlsm) 」形式を選択します。
次に、「保存」ボタンをクリックして、VBAマクロをワークブックと一緒に保存します。
これで、マクロを実行して列を比較することができます。
Excelのワークシートに戻り、「リボン」の「開発者」タブで「マクロ」を選択するか、ALT F8キーを押してください。
Macroというダイアログボックスが開きます。マクロの名前の下に、作成したマクロが表示されます。HighlightColumnDifference」マクロを選択し、「実行」をクリックします。
ここで、2つの列を指定するためのダイアログボックスが表示されます。比較したい列を選択し、「OK」をクリックするだけです。
2つの列の違いは、コード内で指定した背景色でハイライトされます。このVBAコードは、大文字と小文字を区別して列を比較し、その差分をハイライト表示します。
VBAコードを使用して2つの列を比較し、一致するデータ（または重複）を強調表示する
2つの列を比較して、2番目の列の一致または重複をハイライトしたい場合は、次のコードを使用することができます。
スプレッドシートを開き、Alt F11キーを押して、Microsoft Visual Basic for Applicationsウィンドウを開きます。次に、Microsoft Visual Basic for Applications ウィンドウの ‘Insert’ > ‘Module’ を開きます。
次に、以下のマクロコードを新しい空白のモジュールスクリプトにコピー＆ペーストします。
Sub CompareTwoRanges()Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As RangeSRg:Set xRgC1 = Application.InputBox(\"Select the column you want compare according to", \"Excel", , , , 8)If xRgC1 Is Nothing Then Exit SubIf xRgC1.Columns.Count <<, XRgC1.RangeSRg.Count<, XRgC1.RangeSRg<, XRgC1.RangeSRg<, XRgC1.RangeSRg<,{double1 Then MsgBox ㊞"列を1つ選択してください" GoTo SRgEnd IfSsRg:Set xRgC2 = Application.InputBox(\"Select the column you want to highlight duplicates in:\", \"Excel", , , 8)If xRgC2 Is Nothing Then Exit SubIf xRgC2.Columns.Count 1 Then MsgBox \"Please select a single column" GoTo SsRgEnd If For Each xRgF1 In xRgC1 For Each xRgF2 In xRgC2 If xRgF1.Value = xRgC1.Value + 1 Then Insight SubIf XSRGC2(1つの列にハイライトする場合は)If(1つの列を選択してください)Instance Set xRgC2.Value = xRgF2.Value Then xRgF2.Interior.ColorIndex = 38 \(as you can change the color index as you need) End If Next NextEnd Sub
コードを貼り付けたら、上で紹介したように、ファイルを「*.xlsm」形式のマクロ有効ワークブックとして保存してください。そして、モジュールとMicrosoft Visual Basic for Applicationsウィンドウを閉じます。
VBAマクロを実行するには、「開発者」タブに切り替え、「コード」グループから「マクロ」をクリックします。
マクロのダイアログウィンドウで、「CompareTwoRanges」を選択し、「実行」をクリックします。
最初のポップアップダイアログボックスが表示されたら、重複した値を比較する列を選択して「OK」をクリックします。
2つ目のダイアログボックスで、重複する値を強調表示したい列を選択し、「OK」をクリックします。
下図のように、2番目の列と1番目の列が比較され、2番目の列で重複が背景色で強調表示されます。このVBAコードは、大文字と小文字を区別して列を比較します。
VBAコードで2列を比較し一致するデータを抽出する
2つの列を行ごとに比較して、一致する値（重複）を別の列に抜き出したい場合には、以下のようなマクロコードを使用します。
Microsoft Visual Basic for Applicationsのウィンドウで、ご紹介したように、空白のモジュールを開きます。以下のスクリプトをコピーして、新しい空白のモジュールに貼り付けます。
Sub PullMatches()Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As RangeDim xIntSR, xIntER, xIntSC, xIntEC As IntegerOn Error Resume NextSRg:Set xRgC1 = Application.InputBox(\"Select first column:\", \"Excel", , , , 8)If xRgC1 Is Nothing Then Exit SubIf xRgC1.Columns.Count 1 Then MsgBox \"Please select single column" GoTo SRgEnd IfSsRg:Set xRgC2 = Application.InputBox(Single Columns.Count) (NextRgC1,2,3)。InputBox(\"Select the second column:\", \"Excel", , , 8)If xRgC2 Is Nothing Then Exit SubIf xRgC2.Columns.Count 1 Then MsgBox \"Please select single column" GoTo SsRgEnd IfSet xWs = xRg.Worksheet For FI = 1 To xRg.Count If Not Str.Rows.Count If Not StrComp(xRg.Cells(FI, 1), xRg.Cells(FI, 2), vbBinaryCompare) = 0 Then Ws.Range(xRg.Cells(FI, 1), Rg.Cells(FI, 2)).Interior.ColorIndex = 8 \’You can change the color index as you like.エンドイフネクスト FIEndサブセット
コードを貼り付けたら、ファイルを保存し、Microsoft Visual Basic for Applicationsのウィンドウを閉じます。次に、Marcoダイアログウィンドウを開き、’PullMatches’マクロを選択し、’Run’をクリックします。
まず、比較したい1列目（左）を選択し、「OK」をクリックします。
2つ目のダイアログで、比較したい2つ目の列を選択し、「OK」をクリックします。
2つの列の間の一致は、選択した2つの列の右側の列に自動的に引き出されて表示されます。
VBAコードで2つの列を比較し、ユニークなデータを抽出する
2つの列を比較し、ユニークな値を引き出したい場合、以下のVBAコードがお役に立ちます。
Microsoft Visual Basic for Applicationsウィンドウで空白のモジュールを開き、以下のスクリプトを新しい空白のモジュールにコピー・ペーストしてください。
Sub PullUniques()Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As RangeDim xIntR, xIntSR, xIntER, xIntSC, xIntEC As IntegerDim xWs As WorksheetOn Error Resume NextSRg:Set xRg = Application.入力ボックス( \"Select two columns:\", \"Excel", , , , 8)If xRg Is Nothing Then Exit SubIf xRg.Columns.Count 2 Then MsgBox ###"Please select two columns as a range ###" GoTo SRgEnd IfSet xWs = xRg.Worksheet xIntSC = xRg.ColumnxIntEC = xRg.Columns.Count xIntSC – 1xIntSR = xRg.RowxIntER = xRg.Rows.Count xIntSR – 1 Set xRg = xRg.Worksheet xIntEC – 1 xRg.Columns.Count xIntSR = xRg.Columns.Count ' ' ' – 2 Then MsgBox列セット xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Columns(xRgC3, xIntSR, xIntSC))Set xWs.Range(xWs.Cells(xIntSR), xWs.Columns(xIntSR))Cells(xIntER, xIntEC))xIntR = 1For Each xFRg In xRgC1 If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then xWs.Cells(xIntER, xIntEC)) xWs.Range(xWs.Cells(xIntSR), xIntEC)Cells(xIntER, xIntEC).Offset(xIntR) = xFRg xIntR = xIntR 1 End IfNextxIntR = 1For Each xFRg In xRgC2 If WorksheetFunction.Count If(xIntR) = 1For Each xFRg In xRgC2 If WorksheetFunction.Offset(xIntR) = 2For Each xFRg In xRgC2CountIf(xRgC1, xFRg) = 0 Then xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg xIntR = xIntR 1 End IfNextEnd Sub
その後、ファイルを保存し、Microsoft Visual Basic for Applications ウィンドウを閉じます。
その後、Marcoダイアログウィンドウを開き、’PullUniques’マクロを選択し、’Run’をクリックします。
ポップアップウィンドウで、比較する2つの列を選択し、「OK」をクリックします。
このマクロは、大文字と小文字を区別せずに列を比較し、2つの列からユニークな値をリストアップします。
以上です。さて、あなたはExcelで列を比較することについてのすべてを知っています。自分に合った方法を選ぶことができます。
