実務では、出所の違うデータベースやデータを、1つのExcelの別々のシートに入れています。それをパワークエリで1つにまとめて、1つのシートに書き出しています。A列に「販売_店舗コード_商品コード」のような検索値を入れておくと、販売も仕入も在庫も、1つのシートからXLOOKUPで取り出せます。
この記事では、そのやり方と、取り出した値で現在庫数を出すところまでを紹介します。グループ化や「クエリの追加」は、これまでの記事でも紹介しています。
なお、この記事の表や手順は説明のために作ったもので、実際に使っているExcelとは作りが違います。コードや数字も架空です。
例の表
販売・仕入・在庫の3つのシートがあり、それぞれテーブルにしています。在庫のシートは、先月末（9/30）時点の在庫数です。出所が違うので、列の並びや列名がそろっていません。
販売
| 日付 | 店舗コード | 商品コード | 販売数 |
|---|---|---|---|
| 10/1 | 101 | 1001 | 3 |
| 10/2 | 101 | 1001 | 2 |
| 10/1 | 101 | 1002 | 4 |
| 10/1 | 102 | 1001 | 5 |
仕入
| 日付 | 店舗コード | 商品コード | 仕入先 | 仕入数 |
|---|---|---|---|---|
| 10/1 | 101 | 1001 | A商事 | 10 |
| 10/2 | 101 | 1002 | A商事 | 6 |
| 10/1 | 102 | 1001 | B物産 | 8 |
在庫
| 日付 | 店舗コード | 商品コード | 在庫数 |
|---|---|---|---|
| 9/30 | 101 | 1001 | 7 |
| 9/30 | 101 | 1002 | 2 |
| 9/30 | 102 | 1001 | 3 |
この3つを、「区分・店舗コード・商品コード・数量」の4列にそろえて、1つの表にまとめます。
シートごとにクエリを作る
まず、販売のテーブルを選んで、［データ］タブ→［テーブルまたは範囲から］でPower Queryエディターを開きます。
必要な列だけ残す
「店舗コード」「商品コード」「販売数」の列をCtrlを押しながら選び、右クリック→［他の列の削除］を押します。日付の列が消えます。
列名を「数量」にそろえる
「販売数」の列名をダブルクリックして、「数量」に変えます。
このあとの「クエリの追加」は、列名で列を合わせます。販売数・仕入数・在庫数のままだと別の列として扱われるので、どのクエリも「数量」にそろえます。
区分の列を足す
［列の追加］タブ→［カスタム列］を押して、新しい列名を「区分」、式を次のようにします。
"販売"すべての行の区分が「販売」になります。まとめたあとも、どのシートの行かが分かるようにするためです。
| 店舗コード | 商品コード | 数量 | 区分 |
|---|---|---|---|
| 101 | 1001 | 3 | 販売 |
| 101 | 1001 | 2 | 販売 |
| 101 | 1002 | 4 | 販売 |
| 102 | 1001 | 5 | 販売 |
クエリの名前を「販売」にして、［ホーム］タブ→［閉じて読み込む］の▼→［閉じて次に読み込む］で、「接続の作成のみ」を選びます。
仕入と在庫も同じように作る
仕入と在庫も、同じ手順でクエリを作ります。
- 仕入：日付と仕入先の列を削除し、「仕入数」を「数量」に変えて、区分は
"仕入" - 在庫：クエリの名前を「先月末在庫」にして、日付の列を削除し、「在庫数」を「数量」に変えて、区分は
"先月末在庫"
どちらも「接続の作成のみ」にしておきます。
クエリの追加で3つをまとめる
Excelの画面で、［データ］タブ→［データの取得］→［クエリの結合］→［追加］を選びます。
「3つ以上のテーブル」を選び、「販売」「仕入」「先月末在庫」を「追加するテーブル」に入れて［OK］を押します。クエリの名前は「まとめ」に変えます。
3つのクエリの行が縦につながり、販売4行・仕入3行・先月末在庫3行の合わせて10行になります。列名をそろえているので、どの行も「区分・店舗コード・商品コード・数量」の4列に収まります。
まとめてからグループ化する
「まとめ」のクエリで、［ホーム］タブ→［グループ化］→「詳細設定」を選び、「区分」「店舗コード」「商品コード」でグループ化して、数量を合計します。新しい列名は「数量」にします。
シートごとにグループ化してからまとめることもできますが、その場合は同じ手順を3回書くことになります。区分の列があるので、まとめてから1回グループ化すれば、同じ結果になります。
| 区分 | 店舗コード | 商品コード | 数量 |
|---|---|---|---|
| 販売 | 101 | 1001 | 5 |
| 販売 | 101 | 1002 | 4 |
| 販売 | 102 | 1001 | 5 |
| 仕入 | 101 | 1001 | 10 |
| 仕入 | 101 | 1002 | 6 |
| 仕入 | 102 | 1001 | 8 |
| 先月末在庫 | 101 | 1001 | 7 |
| 先月末在庫 | 101 | 1002 | 2 |
| 先月末在庫 | 102 | 1001 | 3 |
販売の店舗コード101・商品コード1001は、2行が合計されて5になっています。
A列に検索キーを作る
［列の追加］タブ→［カスタム列］で、新しい列名を「検索キー」、式を次のようにします。
[区分] & "_" & Text.From([店舗コード]) & "_" & Text.From([商品コード])店舗コードと商品コードが数値の場合は、そのままだと文字とつなげられないので、Text.Fromで文字に変えています。
できた列を先頭に移動して、［ホーム］タブ→［閉じて読み込む］を押すと、「まとめ」がテーブルでシートに書き出されます。
| 検索キー | 区分 | 店舗コード | 商品コード | 数量 |
|---|---|---|---|---|
| 販売_101_1001 | 販売 | 101 | 1001 | 5 |
| 販売_101_1002 | 販売 | 101 | 1002 | 4 |
| … | … | … | … | … |
| 先月末在庫_102_1001 | 先月末在庫 | 102 | 1001 | 3 |
1つのシートから販売・仕入・在庫を取り出す
別のシートに、取り出すための表を作ります。A列に店舗コード、B列に商品コードを入れ、1行目のC1〜E1には「先月末在庫」「仕入」「販売」と、区分と同じ文字を入れます。
C2セルに次の式を入れます。
=XLOOKUP(C$1&"_"&$A2&"_"&$B2,まとめ[検索キー],まとめ[数量],0)C$1 で1行目の区分を、$A2 と $B2 で店舗コードと商品コードを読んで、「先月末在庫_101_1001」のような検索キーを作っています。C2をE2までコピーし、さらに下の行にもコピーすれば、1つの式で先月末在庫・仕入・販売がそろいます。$ を付けているので、右にコピーしても店舗コードと商品コードの列はずれず、下にコピーしても1行目の区分を読み続けます。
| 店舗コード | 商品コード | 先月末在庫 | 仕入 | 販売 |
|---|---|---|---|---|
| 101 | 1001 | 7 | 10 | 5 |
| 101 | 1002 | 2 | 6 | 4 |
| 102 | 1001 | 3 | 8 | 5 |
1行目の文字は、区分とまったく同じにします。たとえばC1を「在庫」だけにすると検索キーが一致せず、0になります。
4つ目の引数の「0」は、見つからないときに表示する値です。その店舗で売れていない商品などは、エラーではなく0になります。
検索キーを使ったXLOOKUPについては、こちらの記事でも紹介しています。
現在庫数を出す
先月末の在庫数に仕入数を足して、販売数を引けば、現在庫数になります。F1に「現在庫数」と入れ、F2に次の式を入れて下の行にコピーします。
=C2+D2-E2| 店舗コード | 商品コード | 先月末在庫 | 仕入 | 販売 | 現在庫数 |
|---|---|---|---|---|---|
| 101 | 1001 | 7 | 10 | 5 | 12 |
| 101 | 1002 | 2 | 6 | 4 | 4 |
| 102 | 1001 | 3 | 8 | 5 | 6 |
店舗コード101、商品コード1001なら、7＋10－5で12になります。
パワークエリの中で現在庫数を出す
現在庫数は、パワークエリの中で出すこともできます。販売の数量をマイナスにしてからグループ化すると、合計がそのまま現在庫数になります。
ただ、「まとめ」のクエリそのものを変えると、まとめの販売数もマイナスで表示されて、XLOOKUPで販売数を取り出すときに分かりにくくなります。そこで、「まとめ」を参照した別のクエリで計算します。
クエリと接続の一覧で「まとめ」を右クリック→［参照］を選び、新しいクエリの名前を「現在庫」にします。
［列の追加］タブ→［カスタム列］で、新しい列名を「増減」、式を次のようにします。
if [区分] = "販売" then -[数量] else [数量]販売の行だけ、数量がマイナスになります。
続けて、［ホーム］タブ→［グループ化］→「詳細設定」を選び、「店舗コード」「商品コード」でグループ化して、増減を合計します。新しい列名は「現在庫数」にします。区分はグループ化に入れないので、先月末在庫・仕入・販売が1行にまとまります。
| 店舗コード | 商品コード | 現在庫数 |
|---|---|---|
| 101 | 1001 | 12 |
| 101 | 1002 | 4 |
| 102 | 1001 | 6 |
［ホーム］タブ→［閉じて読み込む］を押すと、店舗ごと・商品ごとの現在庫数の表がシートに書き出されます。
まとめ
- 出所の違うシートも、パワークエリで1つの表にまとめられる
- シートごとにクエリを作り、必要な列だけ残して、列名を「数量」にそろえる
- カスタム列で「区分」を足しておくと、まとめたあともどのシートの行か分かる
- 「クエリの追加」で3つをまとめ、グループ化はまとめてから1回
- A列に「区分_店舗コード_商品コード」の検索キーを作れば、1つのシートからXLOOKUPで取り出せる
- 現在庫数は、取り出した値を足し引きするか、パワークエリで販売をマイナスにしてグループ化すれば出せる
