コロナ禍によって多数の企業がテレワークやリモートワークに移行し、自宅で働くことが当たり前になって久しい昨今、業務効率化は重要な課題です。
Excelを使って作業する中で、手短に条件に一致したデータを抽出しリストにしたいけど、これはVBAを組まないとできないのでは・・
そんな時ちょっと使える技を紹介したいと思います。
2つのデータの比較をし、差分や一致したデータだけを抽出してリストにしたい事ありますよね。
データ間比較や差分抽出が一発で実現するように特化した関数や機能はなく、VLOOKUPやCOUNTIFを駆使したり、条件付き書式を使って該当するセルを目立たせるなど都度工夫している方が多いかと思いますが、数式を使ってデータ間比較するシートを作ってしまえば、都度の作業から解放されます。
数式を組む
では実践してみましょう。
下図のように、データ同士比較するためにA列B列を作成します。A列B列はそれぞれ重複のないデータです。
A列とB列を比較して「A列にありB列にないもの」を「A○B×」として、D:E列に作業列と抽出リスト出力列を作成します。
同様に、「A列になくB列にあるもの」を「A×B○」として、F:G列に作業列と抽出リスト出力列を作成します。
「A列とB列両方にあるもの」を「A○B○」として、H:I列に作業列と抽出リスト出力列を作成します。
セルL2にA列の個数が表示されるよう =COUNTA(A:A) を入力します。
同様にセルL3にB列の個数が表示されるよう =COUNTA(B:B) を入力します。
セルL4には「A○B×」の個数が表示されるよう =COUNT(D:D) を入力します。
同様にセルL5には「A×B○」の個数が表示されるよう =COUNT(F:F)、
セルL5には「A○B○」の個数が表示されるよう =COUNT(H:H) を入力します。
A列B列それぞれの最終行と抽出データの個数を取得しておくことで無駄な範囲の計算を省くことができます。
A列にありB列にないもの
「A○B×」の作業列D列は「A列にありB列にないもの」を判定し成立したセルの行番号を出力します。
セルD2に下記の数式を貼り付け、比較データA:B列の最終行16行までオートフィルコピーします。
=IF(A2=””,””,IF(COUNTIF(B$2:INDEX(B:B,$L$2),A2),””,ROW()))
「A○B×」の抽出列E列は、D列に出力された行番号を元にデータを出力します。
セルE2に下記の数式を貼り付け、比較データA:B列の最終行16行までオートフィルコピーします。
=IF(L$4<ROW(A1),””,INDEX(A:A,SMALL(D$2:INDEX(D:D,L$2),ROW(A1))))
A列になくB列にあるもの
「A×B○」の作業列F列は「A列になくB列にあるもの」を判定し成立したセルの行番号を出力します。
セルF2に下記の数式を貼り付け、比較データA:B列の最終行16行までオートフィルコピーします。
=IF(A2=””,””,IF(COUNTIF(B$2:INDEX(B:B,$L$2),A2),””,ROW()))
「A×B○」の抽出列G列は、F列に出力された行番号を元にデータを出力します。
セルG2に下記の数式を貼り付け、比較データA:B列の最終行16行までオートフィルコピーします。
=IF(L$4<ROW(A1),””,INDEX(A:A,SMALL(D$2:INDEX(D:D,L$2),ROW(A1))))
A列とB列両方にあるもの
「A○B○」の作業列H列は「A列とB列両方にあるもの」を判定し成立したセルの行番号を出力します。
セルF2に下記の数式を貼り付け、比較データA:B列の最終行16行までオートフィルコピーします。
=IF(B2=””,””,IF(AND(F2=””,B1<>B2),ROW(),””))
「A○B○」の抽出列I列は、F列に出力された行番号を元にデータを出力します。
セルI2に下記の数式を貼り付け、比較データA:B列の最終行16行までオートフィルコピーします。
=IF(L$6<ROW(A1),””,INDEX(B:B,SMALL(H$2:INDEX(H:H,L$3),ROW(A1))))
今回紹介したデータ同士を比較して差分/一致を抽出の数式を使う方法は、一見手間ですが処理速度も速く手軽に実装できるので是非とも参考にしてみてください。
