夏休みの自由研究で比較的お手軽に絞り込みを行う方法を作ってみました。
スライサーを使用すれば簡単にデータを絞り込みで来ますが、データと出力機能を分けたいときには便利かと思います。
やったこと
検索条件を指定して該当するデータを一覧で表示しました
↓実装した結果
サンプルデータ
こちらのデータの一部を使用しました。
- JANCD
- メーカー名
- 商品名称
- 保管場所
実装のポイント
- 検索条件に該当するデータに印をつけます
- その後、印を探し出して表示します
前提
今回の方法はデータ件数が少ない場合に有用です。データ件数が多くなると少しずつ動作が遅くなります。
1万件で約2秒程度の計算が走りました。
※データ件数が多い場合の実装方法は別記事にします。
実装方法
1. スライサーによる絞り込み
1.1 スライサーを表示
サンプルデータをテーブルに変換しておくことでスライサーが使えるようになります。
テーブル名は「商品データ」にしておきます。
スライサーでメーカー名を１つ選択するとデータがフィルタリングされます。
ここで表示されているデータを出力用の表に転記すればよいわけです。
1.2 フィルタリングされたデータに印をつける
SUBTOTALを使うと表示された行に印をつけられます。
商品データに「メーカー」の列を追加して下記式を入力します。
=SUBTOTAL(103,[@JANCD])>0
表示行がTRUE、非表示行がFALSEになります。
2. オプションボタンによる絞り込み
2.1 オプションボタンを表示
開発タブのフォームコントロールからオプションボタンを複数個追加しておきます。
今回のサンプルでは9個の保管場所と「全て」がありますので合計10追加します。
また、オプションボタンの書式設定を開きリンクするセルをどこかに設定しておきます。
リンクするセルを指定することでオプションボタンを選択したときにそのボタンの番号が指定したセルに書き込まれます。
この値をもとに保管場所を見つけます。
このとき、番号と保管場所を紐づけるテーブルが必要になりますので「見取り図データ」を作っておきます。
あとはVLOOKUP等で番号から保管場所を拾ってきます。
=VLOOKUP($AF$2,見取り図データ,2,FALSE)
これで選択中の保管場所が明らかになりました。
2.2 印をつける
商品データに「見取り図」の列を追加し下記式を入力します。
=OR([@保管場所]=検索!$AG$2,検索!$AG$2="全て")
3. キーワードによる絞り込み
3.1 入力欄の作成
ここは普通のセルで十分です。今回はExcel方眼紙にしたので結合セルにしていますが単一セルでも十分です。
3.2 印をつける
商品データに「キーワード」の列を追加し下記式を入力します。
FINDでキーワードを見つけます。商品名に含まれていたらTRUE、含まれていなかったらFALSEを返すようにします。
=NOT(ISERROR(FIND(検索!$C$12,[@商品名称])))
4. 検索条件全てを満たすデータの抽出
検索条件全てを満たすデータに1を付与します。それ以外は0とします。
商品データに「出力対象」の列を追加し下記式を入力します。
==[@メーカー]*[@見取り図]*[@キーワード]
5.出力対象を上から採番
出力対象を1から連番で採番します。
採番するための式はSUMでもMAXでもVLOOKUPでも何でも構いません。
この連番をキーとして検索結果を表示することになります。
=IF([@出力対象]=1,SUM($H$2:H2),"")
補足：今回、タイトルにあるように「データ数が1万件以下の場合」と制限をかけた理由がここにあります。SUMやMAXはデータ量が多くなると計算対象が増えるため指数的に計算量が増えてしまいます。1万行程度なら許容範囲ですが10万行を超えると1分以上待たされたり最悪Excelがフリーズします。
データ数が増えても計算量が変わらないようにするためには結構な工夫が必要になります。それについては後日、別の記事でまとめます。
6. 該当データを検索結果に表示
出力対象キーが付いたデータを表示します。
出力対象キーが1からの連番になっているのでVLOOKUPやINDEX+MATCHの関数で抽出することは容易です。
今回は出力対象キーが商品データの中で一番最後にあるためVLOOKUPが使えませんのでINDEXとMATCHで抽出します。
検索結果には作業用に隠してある数値もあるのですべて出すとこのような形です。
各セルの式は以下のようになっています。
- 総ページ：一度に表示する数を10にしたので、データ数を10で割ってページ数を求めておきます。
- 現在ページ数：検索結果の右上に配置したスクロールバーとリンクしているセルを総ページ数で割ったときの余りを使って現在ページ数を表現しています。これにより最終ページまで行くと最初のページに戻ることができます。
- #：1からの連番です。ページが切り替わるごとに+10していきます。
- 対象行：商品データの出力対象キーから#のデータの位置を探しています。
- JANCD,名称,保管場所：対象行のデータを表示しています。
実際にページを切り替えたときの動作はこのようになっています。
データの絞り込みから表示までは以上になります。
VBAを使わなくても関数の機能でなんとかなるものですね。
その他
過去に似たような記事を書いてますので興味のある方はご参考ください。