条件付き書式を使って2つの表を比較する
ある表と表のセルの値を比較したいとき、みなさんはどのような方法を使ってますか？
今回ご紹介する方法は、条件付き書式を使った比較方法です。
手順は慣れるまでは、やや面倒に感じるかもしれませんが、一回作ってしまうと見やすさでは抜群だと思います。
使える！と思った方はぜひ試してみてくださいませ。
比較するための「第3の表」が要らないメリット
最初にここでお話する表についてですが、ごく普通の表を想定しています。
※ここの説明では、わかりやすいように表の大きさを小さくして、比較する2つの表を一つのシートにまとめていますが、実際には「1つのシートに1つの表」という形で、シート間(の表)同士で比較することの方が多いかもしれません。この場合でも条件付き書式の考え方は同様です。
このような比較では、簡単な表、あるいは難しい表でも、IF関数を使ったやり方というのもよくあると思います。
上記のようにIF関数を使う方法もポピュラーで簡単にできますので、そこそこ需要があるのではないかと思います。
しかし、この場合は、ご覧の通り「比較をするための表をもう一つ追加」しなければならないわけですね。
一方の条件付き書式を使った比較では、比較したい2つの表だけでOKという点がメリットになると思います。
また、この後説明しますが、条件付き書式は、そもそもセルやセルの値を色々と装飾できるための機能で、値の異なる(あるいは一致する)セルをよりはっきりと表示させることもできるわけです。
これは一画面どころか、何回も縦横スクロールしなければならないような大きな表同士の比較では、一層役に立つ機能だと思います。
条件付き書式の設定について
条件付き書式の設定は、「少し分かりづらい」ことが難点ですね。しかしルールや仕組みさえ覚えれば、そこまで難しくないと思います。
- 比較したい一方の表のセル全てを選択(ここでは表Bに条件付き書式を設定)
- 比較する条件を設定する →「数式を使用して、書式設定するセルを決定」
- 値が不一致したときのセルや文字の装飾の設定をする(ここでは「不一致」したセルを装飾)
この3つの過程で表同士の比較ができます。ただ、ここでの難点が2つ目の設定ですね。
この部分の設定では、関数や数式などで登場する相対参照の考え方が必要になるからですね。
「1」を選択後、「比較をするための式」を入れます。ここではEXACT関数を入れています。
しかし、この設定画面の入力で比較するセルは表Aのセル1個、表Bのセル1個の「一対のセル：計2個だけ」なんですね。
この設定で進めると、「左上の最初のセルの値比較のみの設定」になってしまいます。しかし、比較したいセルの範囲は、見出し行を除いた「表A、表Bのセル全て」です。
ではどうするのか？、どうやって全てのセルを比較対象とするのか？
ここが分かりにくい点だと思います。
それは、最初に「1」で選択した範囲と、EXACT関数の引数で設定するセルの参照がポイントです。
EXACT関数の入力では、2つのセル分しか入力できませんでしたね。しかし、このとき「1」で選択したセルの範囲全部を比較対象とするには、EXACT関数で指定するセルを「相対参照」にします。
マウスなどで入力すると、初期値では「$」がついてしまい絶対参照となりますが、手動でこの「$」を削除します。
この設定をすることで、目で見ただけではわかりませんが、「1」で選択した範囲を元にEXACT関数で設定したセルの範囲が相対的に動いて比較してくれるという仕組みになっています。
これでキモの部分ができるわけですね。
条件付き書式で一致しないセルを強調する
小さい表の場合でも、大きい表の場合でも、どのセルの値が不一致(あるいは一致)しているのかがより明らかであれば、要らぬミスや漏れを防ぐことに繋がりますね。
上記で言うところの「3」の部分で相違しているセル、または一致しているセルを際立たせ、見ただけで分かるようにします。
私の場合ですが、これは、「ほとんどが一致しているはず(不一致はあったとしても数個)」という前提の時に、そして「相違が有るのか、無いのか知りたい(値がどのように違うのかはその次)」という時によく使います。
※条件付き書式で、目立つ色にすると、上記よりももっとわかりやすくできますね。
条件付き書式で気をつける点
条件付き書式は便利ですが、用途によっては注意しなければならないケースもあります。
ここでは、値の一致検証の作業を「条件付き書式で行うのがベターかどうか」などの判断材料として見てもらえればと思います。
動きが重くなる
比較する表の範囲が大きくなるほど、重くなります。裏では関数を入れているわけですし、その関数が範囲内のすべてのセルに対して実行されます。
さらに条件付き書式を使わない他のシートにも関数がたくさん使用されていると、それも加えた処理負荷になることも起こりえます。
「セルの位置だけ」が分かる
これは、IF関数などの比較でも同様ですが、条件付き書式で分かるのは、「不一致(あるいは一致している)セル」です。つまり、セルの値の「どこが違うのか」まではわかりません。
目で見ただけで違いが分かる場合は大きな問題にならないですが、「一見すると同じ」場合には注意です。
実際に値のどこが違うのかを知るためには、セルの値そのものを見て確認する作業が必要になります。
この意味では、Excelの外で作業することになりますが、Excelのお供に・・相性の良い、連携しやすい(フリー)ソフトについて(WinMerge)などが便利です。
表の行列数が異なると比較が漏れる
比較する「表の大きさが異なる」場合も要注意です。
例えば、行数の違う表を比較するとき、「表の小さい方に」条件付き書式を設定すると、長い方の表の一部が漏れてしまいます。
これは列数も同様ですね。
私の場合、範囲漏れがないように、事前に列数と行数を確認してやりますが、時には、2つの表それぞれに条件付き書式を入れて「仮に表の一方で漏れても、もう一方の表で拾える」ようにしたり、1つの表だけでも表の範囲以上(空白セルを含めて)に選択して設定したりすることもあります。
知らない人が見ると意味不明
条件付き書式という機能は便利ですが、多くの企業では使われないケースを目にしています。
条件付き書式で変えたセルの色があり、その「セルの色を変えたい」と、リボンのカラーパレットをクリックしても何も起きず、「？」となっているスタッフなども見かけたこともあります。
これは、知る人ぞ知る機能ということになり、知らない人から見ると文字通り意味がわかりません。これにより作業が遅延したり、無駄な時間が発生するケースもあります。
このようなことがないように、作業者間で「共有できる仕組み」も必要ですね。
まとめ
いかがでしょうか？状況により注意すべき点もありますが、値の比較で使用するとき、色で見分けできるため便利な機能だと思います。
まずは自分自身の中だけの表同士で使用するなど使い方を確認してみるのもよいと思います。
上記でも挙げましたが、内容によってはIF関数でもう一つの表を作り対応したほうが手っ取り早いケースもあります。作業内容や状況により使い分けるとよいと思います。