【エクセルでのデータ抽出】FILTER関数が使えないバージョンでの対処法

実績表や売上表をエクセルで作ったり、基幹ソフトからCSVに書き出した際に、月ごとや部署ごとでシートに分けたいもしくは、そのような依頼があるということよくありますね。

その際、エクセルのフィルタ機能を利用して手動で、コピペを繰り返して、データを抽出するという方法を取る方も多いのではないでしょうか。

しかし、Office365を契約している方の場合、FILTER関数というものを使用して、とても簡単に抽出できるのですが、Excel2016、Excel2019を使用しているという方も多いと思います。

その方に向けてデータの抽出のやり方を調べました。

今回のやり方で出来る事

今回の【エクセルでのデータ抽出】FILTER関数が使えないバージョンでの対処法で紹介する方法で出来ることは以下の事です

下記の売上表のような行に項目が並び、それに対応するデータが並んでいる資料があるとします。
その中から、項目に合致する内容のデータのみを抽出し、別のシートに書き出すということを行います。

そこで、今回は、商品ごとに抽出をいきます。

COUNTIF関数を設定

今回の方法で抽出する際に「COUNTIF」という関数を使います。

COUNTIF関数で出来ることは、指定した範囲の中で選んだデータは何番目かということを返す関数です。
実際に使って説明と設定を行いましょう。

F3のセルに

=COUNTIF($D$3:D3,D3)

と入力します。

カッコ内の意味ですが、コンマの前に$D$3:D3はD3~D3の範囲を意味します。
D3~D3なので、D3のみです。

=COUNTIF($D$3:D3,D3)の関数の意味は、D3~D3までの間でD3の値（今回は”a”です）が、何個あるかということを意味します。
当然最初のなのでF3には”1”が表示されます。
今回の数式のポイントは、最初のD3に＄マークがついている点です。

F3の数式をF4にコピーしてみます。

数式は、=COUNTIF($D$3:D4,D4)

となりますね。

この意味は、D3~D4の範囲でD4（今回は”b”）が何個あるかを意味です。

この要領でコピーしていくと、12行目の2回目の”a”の時はF12は”2”と表示されます。

抽出したいデータの列とCOUNTIFを結合させる

COUNTIF関数によって、同じ商品であっても違う数字が割り当てられている状態ができました。
そこで、商品とCOUNTIF関数で割り振られた数字を結合させていきます。

結合させる行は表の一番左に配置してください。
その理由は、後でVLOOKUP関数で必要なデータを抽出するためです。

数式は

=D3&F3

です。

そのまま下のセルまでコピーすればOKです。

これで抽出までの準備が整いました。

あとは、抽出後の結果を表示させる表を作っていきましょう。

別シートに抽出先を作る

抽出先にお表は以下の通りでいいでしょう。

商品ごとの抽出なので、表上部に”商品”をしてするセルを配置し、それに対する表示させたいデータの項目を並べます。
今回ですと、”売上日””担当部署””金額”と入力します。

A列は関数を設定しますのでA列は空けておいてください。

ROW関数を設定る

先ほど空けておいたA列に関数を設定していきます。

A4に

=$E$1&ROW()-3

を入力します。

ROW関数で出来ることは、関数を入力した行が何行目かということを返します。
つまり、A1のセルに=ROW()を入力すると、「1」が表示されます。
これは1行目だから「1」が表示されます。
そのため、Z1のセルに=ROW()を入力しても、「1」が表示されます。

今回の=ROW()-3は=ROW（）で表示された値から3を引いた値が表示されるということです。

=$E$1&ROW()-3を下にコピーをし、E1に仮にaを入力すると、a1から順番に並ぶことがわかります。

VLOOKUP関数を設定する

a1から順番に並べることができなたので、あとは、VLOOKUP関数を用いてデータを抽出していきます。

例えば、B4のセルは

=VLOOKUP($A4,Sheet1!$A$4:$E$20,2,FALSE)

となります。

これにより、4/1という値が表示されます。

同様に、C4、D4を設定します。

それを下の行にもコピーすれば完成です。

データが無いところは、「#N/A」とエラー表示になりますので、それが気になる場合は、

=IFERROR(VLOOKUP($A4,Sheet1!$A$4:$E$20,2,FALSE),””)

上記のようにIFERROR関数を使いすれば、「#N/A」の表示は消えます

今回使用した関数

今回の抽出の作業で使用した関数は以下のものです。

COUNTIF
ROW
VLOOKUP
IFERROR

の4つを使いました。

今回のポイントは、COUNTIF関数を用いて、同じ商品だけど、個々を分類し、それを結合させることにより、VLOOKUPで抽出するということを行いました。
結合させる際に、一番左の列に配置することがポイントです。
今回の方法は、担当部署単位でも同様に行うことができます。
また、金額でも同様の事はできますが、数字の羅列で行うときの注意点として、COUNTIF関数で表示された数字と、抽出もとになる数字を結合させる時に、アンダーバーなどで仕切りを作ったほうが良いです。
その理由は、例えば、「111」に「1」を結合させた場合と、「11」に「11」を結合させた場合の値が一緒になるからです。アンダーバーを使用すれば、「111_1」と「11_11」のように、完全に別の値となり、VLOOKUPの使用が可能となります。