Excel。オートフィルターを使わずに関数で該当データを抽出するには？
＜IFERROR+INDEX+SMALL+ROW関数、IF+AND+YEAR+MONTH関数＞
売上データや納品データなど大きなデータがあって、そこから、請求書などのデータを抽出したい時に、オートフィルターなどを使わずに、数式（関数）で直接データを抽出することができれば、オートフィルターなどを使わない分、作業効率が改善されます。
では、どのように数式を作成したらいいのでしょうか？
次のような表（購入リストという名前のシート）と
転記したいシート（店舗別という名前のシート）を用意しています。
転記したいシートのB1には、店舗名。D1には、年。
そして、F1には、月を入力して、そのデータに合致したものを、表から抽出するということをします。
表にオートフィルターを設定して、抽出条件を加味して、そのデータをコピー&ペーストするという作業で問題はないのですが、例えば、店舗が10店舗とかあると、いちいち抽出していると面倒なので、店舗ごとのシートを作れば、年とか月とかを変更するだけで済むようになるわけです。
【合致するものがあるのかを確認する】
転記したいシートのD列を使って、まずは、表と合致するデータがあるのかどうかを算出させます。
D4をクリックして、次の数式を設定します。
=IF(AND(購入リスト!B2=店舗別!$B$1,YEAR(購入リスト!A2)=$D$1,MONTH(購入リスト!A2)=$F$1),ROW(A1),"")
少し長い数式なので、説明をしていきます。
購入リストシートのB2が、抽出したい店舗名と同じで、年月も同じなのかをAND関数を使って確認しています。
IF関数を使って、真の場合は、この後の使う関数のために、昇順の連番がほしいので、ROW(A1)としています。合致しなければ、空白という数式です。
あとは、オートフィルで適度に数式をコピーしておきます。
【合致したデータを詰めて表示したい】
A4に次の数式を設定します。
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(A1)),1),"")
D列のデータがないとエラーが表示されるので、IFERROR関数でエラーを表示させないようにしています。
そして、ポイントになる関数。それが、『
INDEX関数』です。
INDEX関数は、指定された列と行が交差するデータを表示することができる関数です。
今回は、交差するセル情報を表示させたいので、INDEX関数の引数は、【配列・行番号・列番号】を使います。
配列は、購入リストシートの$A$2:$D$10。
行番号は、先程算出してあるD列を使うのですが、D4に1と算出しているので、最初は1を使うのですが、2件の場合は、D6の3を使うことで、引数の行番号には3を使いたいわけです。
となると、順々に数値を変更させたいのですが、連番とは限りませんので、SMALL関数を使い、さらにROW関数を使うことで、1番目に小さい数値、2番目に小さい数値と引数に設定することができます。
列番号には、1列目のデータなので、1。
先程の行番号は、SMALL関数とROW関数を使うことで、自動的に引数の数値を変えることができたのですが、この列番号だけは、何列目のデータを抽出していいのか、判断が難しいので、それぞれの列で、修正する必要があります。
つまり、
日付のA4の数式は、
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(A1)),1),"")
商品名のB4の数式は、
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(B1)),3),"")
購入金額のC4の数式は、
=IFERROR(INDEX(購入リスト!$A$2:$D$10,SMALL($D$4:$D$12,ROW(C1)),4),"")
となっています。
それでは、オートフィルで数式をコピーしてみましょう。
該当するデータを詰めて表示することができましたね。
あと作業用で使ったD列の数値が見えているとカッコ悪いですね。
当然、数式を削除するわけにはいきませんね。
そこで、D4以降には、次のような表示形式を設定します。
セルの書式設定ダイアログボックスを表示して、分類の「ユーザー定義」に、;;;（セミコロン×3）と設定して、OKボタンをクリックしましょう。
:::（セミコロン×3）を設定すると、文字を
非表示にできます。
このような数式を使うことで、抽出業務も改善できるかもしれませんね。
