ソフトウェア

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

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る

はじめに

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

やりたい事

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

ABC
1クラスNoコード
21-11A
31-12B
41-21C
51-22D
61-23E
71-24F
81-25G
91-31H
101-32I
シート名「元データ」

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

ABC
1クラスNoコード
21-21C
32D
43E
54F
65G
シート名「抽出データ」

実現方法

出力したいセルに、関数を組み合わせて埋め込みます。
使用する関数は、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メニューから、[数式]-[名前の管理]で登録する事ができます。

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る

ブログ記事について

ブログ記事は基本、毎週月曜日午前中に発信!(私感/私考カテゴリについては随時)
今回参照して頂いた記事以外、メニューや検索機能を使用して頂けると目的の情報に辿り着ける事もあります。
多少でも記事に価値があると感じましたら、コメントまたは以下の支援のご協力を頂けましたらありがたいです!

公式ホームページについて

当ブログではITエンジニア関連のテクニカルな事、そして様々な商品のレビュー記事を公開しています。
一方ホームページでは、それ以外の少しビジネス寄り(企業や個人事業主向け)のサービスや情報を発信します。

該当される方、ご興味のある方はホームページの方もご参照ください。

コメントを残す

*