【ソフトウェア】Excelの表データから、指定した条件の行を抽出し別のワークシートへ出力する

はじめに

Excelに蓄積されたデータから条件によって別シートへ抽出したい事案が発生しました。
前回はGoogleスプレッドシートを使用して実現したのですが、Excelでもできないかと試行錯誤を繰り返していました。
Excelにはquery関数は存在しないので、関数を組み合わせる必要がありそうです。

やりたい事

前回とほぼ同じ事を実現してみます。
しかし、あいまい検索や並び替えは複雑になるので今回は省きます。
元データを並び替え済とし、クラスが完全一致の行を抽出します。

A2セルで抽出クラスを入力すると、該当のデータを別ワークシートへ出力するようにします。

実現方法

出力したいセルに、関数を組み合わせて埋め込みます。
使用する関数は、IF,COUNTIF,OFFSET,MATCHの4種類です。

1:IF
該当データが無ければ空白、あれば該当データを抽出

2:COUNTIF
該当データが何件あるか

3:OFFSET
抽出データの場所指定

4:MATCH
検索したい内容が、何行目で出現したか

上記を踏まえて作成した関数が以下のようになります。

B2セルの場合

=IF(COUNTIF(クラス,$A$3)<ROW(A1),"",OFFSET(基礎データ!A1,MATCH($A$3,クラス,0),1))

C2セルの場合

=IF(COUNTIF(クラス,$A$3)<ROW(B1),"",OFFSET(基礎データ!B1,MATCH($A$3,クラス,0),1))

B3セルの場合

=IF(COUNTIF(クラス,$A$3)<ROW(A2),"",OFFSET(基礎データ!A2,MATCH($A$3,クラス,0),1))

以上のように、出力したいセルに関数を順番に設置する事で出力可能です。

まとめ

ExcelでGoogleスプレッドシートと同じ結果を得ようとすると関数の組み合わせをしないといけないので、かなり大変です。
更に今回は「完全位置」で「並び替えなし」という不完全な形です。

このように表データをデータライクで使用する場合は、圧倒的にGoogleスプレッドシートが楽です。
Microsoft365ではそのあたりは改善されているようですが・・・

また機会があれば、実現できていないあいまい検索と並び替えを埋め込んでみたいと思います。

2021/9/15追記

1点、記述漏れがありました。
元データのクラス列について、「クラス」という別名を付与します。
excelメニューから、[数式]-[名前の管理]で登録する事ができます。