店舗ごとの数字を見る集計表は、店舗ごとにシートを分けると、店舗の数だけシートが増えます。メンテナンスも大変です。
そこで実務では、集計表のシートは1枚だけにして、プルダウンで店舗を選ぶと表が切り替わるようにしています。職場全体で見る資料ですが、見る人は店舗を選ぶだけです。
この記事では、パワークエリで書き出したテーブルから、プルダウンで選んだ店舗の数字をXLOOKUPで出す方法を紹介します。なお、この記事の表や手順は説明のために作ったもので、実際に使っているExcelとは作りが違います。例の数字も架空です。
例として使うテーブル
前の記事で、パワークエリを使って店舗ごとの集計の下に全店の合計を足し、「集計」という名前のテーブルで書き出しました。この記事では、このテーブルを使います。
| 検索キー | 店舗 | 月 | カテゴリ | 金額 |
|---|---|---|---|---|
| 本店_10_アイス | 本店 | 10 | アイス | 2,500 |
| 本店_10_菓子 | 本店 | 10 | 菓子 | 1,400 |
| 駅前店_10_アイス | 駅前店 | 10 | アイス | 1,700 |
| 本店_11_アイス | 本店 | 11 | アイス | 900 |
| 合計_10_アイス | 合計 | 10 | アイス | 4,200 |
| 合計_10_菓子 | 合計 | 10 | 菓子 | 1,400 |
| 合計_11_アイス | 合計 | 11 | アイス | 900 |
A列の検索キーは、店舗・月・カテゴリを「_」でつないだものです。
このテーブルの作り方は、こちらの記事で紹介しています。
作る集計表
「集計表」という名前のシートに、次のような表を作ります。縦にカテゴリ、横に月を並べ、値は金額です。
| 店舗 | 本店 | |
|---|---|---|
| カテゴリ | 10 | 11 |
| アイス | 2,500 | 900 |
| 菓子 | 1,400 | 0 |
店舗のプルダウンはB1、月の見出しはB3:C3、カテゴリはA4:A5に入っています。金額はB4:C5に式で出します。
月の見出しは、検索キーに合わせて数値の10・11にしています。「10月」と表示したい場合は、セルの書式設定の［表示形式］→［ユーザー定義］で 0"月" にすると、値は10のまま「10月」と表示されます。
店舗のプルダウンを作る
- B1を選びます。
- ［データ］タブ→［データの入力規則］を開きます。
- ［設定］タブで、［入力値の種類］を「リスト」にします。
- ［元の値］に「本店,駅前店,合計」と入力します。
- ［OK］を押します。
候補に「合計」を入れておくと、全店の数字も同じ表で出せます。
候補を表から読み込む方法や、1つ目の選択で2つ目の候補を変える方法は、こちらの記事で紹介しています。
XLOOKUPで金額を出す
B4に次の式を入れます。
=XLOOKUP($B$1&"_"&B$3&"_"&$A4,集計[検索キー],集計[金額],0)
式の中身は次のとおりです。
| 部分 | やっていること |
|---|---|
$B$1&"_"&B$3&"_"&$A4 | 店舗・月・カテゴリを「_」でつないで、「本店_10_アイス」のような検索値を作る |
集計[検索キー] | 集計のテーブルのA列（検索キー）から探す |
集計[金額] | 見つかった行の金額を返す |
0 | 見つからないときは0を表示する |
B4の式を、C5までコピーします。式の中の「$」は、コピーしても参照がずれないようにするためのものです。
| 参照 | 固定しているもの | 理由 |
|---|---|---|
$B$1 | 行と列 | 店舗は、どのセルでもB1を見る |
B$3 | 行だけ | 月は、どの行でも3行目の見出しを見る |
$A4 | 列だけ | カテゴリは、どの列でもA列を見る |
検索キーの列を作って探す理由は、こちらの記事で紹介しています。
店舗を切り替えてみる
B1のプルダウンで店舗を選ぶと、表の金額がその店舗の数字に切り替わります。
駅前店を選んだとき
| カテゴリ | 10 | 11 |
|---|---|---|
| アイス | 1,700 | 0 |
| 菓子 | 0 | 0 |
合計を選んだとき
| カテゴリ | 10 | 11 |
|---|---|---|
| アイス | 4,200 | 900 |
| 菓子 | 1,400 | 0 |
駅前店の菓子や11月のアイスのように、データがない組み合わせは、XLOOKUPの4つ目の引数で0になります。
「合計」を選んでも、店舗のときと同じ式のままで全店の数字が出ます。全店用のシートや式を別に作る必要はありません。
実務では「101:本店」の形で選んでいる
実務では、プルダウンの候補を「101:本店」のように、店舗コードと店舗名をつなげた形にしています。全店の合計も、店舗コードを決めて同じ形で候補に入れています。
この場合は、LEFTで先頭3桁の店舗コードを取り出し、検索値に使います。パワークエリ側の検索キーも、店舗名ではなく店舗コードでつないでいます。
=XLOOKUP(LEFT($B$1,3)&"_"&$A4&"_"&B$3,集計[検索キー],集計[金額],0)
実務の検索キーは「店舗コード_カテゴリ_月」の順なので、式の中のつなぐ順番もそれに合わせています。検索値と検索キーは、つなぐ順番をそろえる必要があります。
また、実務の集計表では、月ごとに予算・実績・予実率を並べています。予実率は、予算が0や空欄のときにエラーにならないように、IFERRORで対策しています。
「101:本店」の形のプルダウンで、オートフィルにより店舗コードが変わってしまった話は、こちらの記事で紹介しています。
まとめ
- 店舗ごとにシートを分けず、プルダウンで店舗を選ぶと表が切り替わるようにする
- 店舗・月・カテゴリを「_」でつないだ検索値で、パワークエリで作った検索キーからXLOOKUPで探す
- 「$」の付け方を変えておけば、1つの式を表全体にコピーできる
- プルダウンに「合計」を入れておけば、同じ式で全店の数字も出せる
パワークエリで、ローデータを店舗・月・カテゴリごとにまとめる方法は、こちらの記事で紹介しています。
