今回は、2つのデータ表を比較して、完全に一致するかどうかを手軽に確認する方法がないか調べます。
確認しました！👀
Excel で2つの表やデータ範囲を比較したいことはよくあると思います。
ところが Excel を探してもそのための機能が見当たりません。
ネットで調べると、「スプレッドシート比較」とか「Inquire」などという便利なツールがあるようなのですが、どうも法人向けの特別なエディションの Excel でしか使えないようです。
個人ユーザは、肉眼を使って全集中でデータを見比べるしかないのでしょうか。
２つのセル範囲が一致するかを確認するには
意地でも Excel の標準機能だけでデータ比較したいと思います。
一言で「比較」といっても、いろいろな目的や要件があるのですが、今回のこの記事では特に
- ２つのセル範囲を比較して、セルの値が「全て一致するかどうか」の確認をする
ことに焦点をあてます。
データの比較といえば、「条件付き書式」を使ったセルの色付けが有名ですが、 大量のデータに対しては相違のあったのかどうかを確認するのが意外と面倒です。 色で検索できればいいのですが、条件付き書式はなぜか検索できません。
そのほかいろいろな方法を検証してみたのですが、どれも一長一短です。 比較的お手軽そうな以下の４つの手順について紹介します。 状況に合わせて使い勝手が良さそうな方法を試してみてください。
「アクティブ行との相違」を使う
２つの列データ（リスト）が一致するかどうかをとりあえずパッと確認したいときには、「アクティブ行との相違」が一番お手軽な方法でしょう。
「アクティブ行との相違」とは（名前からして意味が分かりませんが）、選択範囲の各セルで行方向に比較して、内容に差異のあるセルを見つけ出すというコマンドです。 「ジャンプ」の一機能で、「選択オプション」ダイアログにあります。
ショートカットはCtrl+¥（円マーク）です。
もし一カ所でもセル内容に差異（相違）があれば、そのセルが選択状態になります。
一方、次のようなダイアログが表示されたら、リスト内容が「全て一致している」ことを確認できたことになります。
離れたところにある 2 列を選択するには、Ctrlキーを押しながらドラッグで範囲選択します。
また、ドラッグが面倒なほど大量のセルを比較したいときには列の全選択でもかまいません。
テキスト値の比較には注意があります。 まず、英字の大文字と小文字は区別されません。 また、１セル内で比較できるテキストは最長256文字までです。 もしそれ以降に差異があっても一致するものと見なされます。
さてこの方法では、データ範囲を横並びにしたうえで、一度に１組の列しか確認できません。 お手軽な反面、大きな表の全体を確かめたいときにはちょっと手間です。
「減算して貼り付け」を使う
比較対象のデータが数値のみの場合、「減算して貼り付け」でデータ範囲の全体一致を確認することができます。 「アクティブ列との相違」の合わせ技になります。
「形式を選択して貼り付け」ダイアログを見ると、「演算」に「減算」というのがあります。 これを選択すると、コピー元と貼り付け先を引き算した結果（差）が張り付けられることになります。
比較元のデータのセル範囲全体をコピーし、比較先のデータ範囲に「減算」で貼り付けをしたとき、一致する値は全て 0 になるはずです。
これを利用します。
【手順例】
- 比較元のデータのセル範囲を全選択し、コピーします
- 比較先の左上隅のセルを選択し、「形式を選択して貼り付け」の「減算」で貼り付けをします
- アクセスキー：
Ctrl+Alt+V⇒S⇒Enter
- アクセスキー：
- そのまま、「アクティブ列との相違」を実行します
- 「ホーム」⇒「編集」⇒「検索と選択」⇒「条件を選択してジャンプ」⇒「選択オプション」⇒「アクティブ列との相違」
- ショートカット：
Ctrl+Shift+¥（円マーク）
- 結果を確認します
- 「該当するセルが見つかりません」 ダイアログが表示されるかどうかを確認
- 「元に戻す（
Ctrl+Z）」で貼り付けを取り消し、元のデータに戻します
「アクティブ列との相違」のショートカットは Ctrl + Shift + ¥ で、「アクティブ行との相違」とは違って Shift をつけます。
「アクティブ列との相違」では、先頭行の結果が全て 0 だったとして、それに相違するセル、つまり 0 以外の値を列方向に検索して選択状態にします。
「該当するセルが見つかりません」というダイアログが表示されたら、全ての減算が 0 だったということで、データ全体が完全一致したと判断できます。
逆にもし、ダイアログが出なかったとしたら、それはどこかで差が 0 にならないセルがあったということで、不一致であることを示します。
確認したら、大切なデータを壊さないうちに、「元に戻す」をしておきましょう。
とはいえ、もし差のあるセルが見つかったのなら、選択状態のまま色付けとかしておきたいですよね。 「元に戻す」とそれができません。
もしデータが整数のみならやりようもあります。
まずは、上記手順で選択状態になっている差異のあるセルにそのまま色などを付けます。 次に全データ範囲を再度選択して、続けて今度は「加算して貼り付け」を実行します。 理屈ではこれで全データが元に戻せているはずです。 ただし小数点以下の精度ある数値データはでは、計算精度の関係で厳密に元の値になるとは限りませんのでこの方法は使えません。
さて、この「減算して貼り付け」を使った手順は、差異があるかどうかだけをサクッとチェックしたい時にはとても便利な方法です。 別々のシートにあるデータでも比較できるのもうれしいです。 ただしデータが数値のみという限られた状況のみしか使えないというところが残念です。
「配列数式」を使う
「配列数式」を使うと、2つのセル範囲の全体が一致するかどうかを一つの数式で判定できます。
たとえば、表Aが A1 から J100 , 表Bが L1 から U100 のセル範囲だったとしたら、以下のような数式で比較できます。
=AND(A1:J100=L1:U100)
この数式の結果は、2つのセル範囲間で全てのセル値が一致しているときにのみ TRUE となります。
ただし重要な注意があります。 配列数式の入力方法が、Excel のバージョンによって違うのです。
最近の Microsoft 365（旧称 Office 365）版の Excel なら、数式を入力した後にそのまま普通にEnterキーを押すだけでOK です。
一方、買い切り版の Excel 2019 、あるいは 2016 や 2013 といった古い Excel の場合、代わりに「Ctrl+Shift+Enter」という3つのキーの組み合わせを同時に押す必要があります。
もう一つ注意すると、テキスト値の比較では、= 演算子は英字の大文字と小文字を区別しません。
それも厳密に区別したい場合、EXACT 関数を使って以下のようにします。
=AND(EXACT(A1:J100, L1:U100))
Excel テーブルを使っている場合、テーブル名で指定できるのでもっと単純に
=AND(EXACT(テーブル1, テーブル2))
ですみます。
２つのデータは行と列のサイズが同じでさえあれば、別々のシートにあっても問題ありません。
さて、上記数式のポイントは、通常ならセル同士の値を比較をするところで、セル範囲同士をまるごと比較しているところです。
その結果は、各セルの組に対する個別の比較結果（TRUE/FALSE）を集めた「配列」となります。
AND 関数は配列を受け取ると、中身が全て TRUE の時にのみ TRUE の結果を返し、逆に1個でも FALSE が含まれると FALSE と判定します。
このような配列を使った数式は「配列数式」と呼ばれ、複数セルに対する計算を一括で行うことが可能なのです。
まあ確かに今回のように使いようによっては大変に便利ですが、今まで Excel で配列数式なんて使ったことはないし、そこまでする必要性を感じたこともありません。
ところが今後はそうもいかないようです。
例えば最近話題の「スピル（Spill）」は配列数式の応用です。
Microsoft 365 版 Excel では配列数式が「動的配列数式」としてリニューアルされました。 従来の配列数式より使い勝手がかなり改善され、スピルや便利な新配列関数がいくつかサポートされています。
Microsoft 365 版 Excel をお使いの方は、先ほどの数式で AND 関数を外してみてください（広い空領域で）。 各セル同士の比較結果が溢れ出す（スピル）のが確認できるでしょう。
動的配列数式は今後、Excel の標準スキルになりそうなのでチェックしておきたいところです。
フィルターオプションを使う
続きは以下の後編記事で。
関連記事
スピルを使ってみた動的配列数式の例が以下の記事にあります。
