今回は、質問箱に届いたテーマについて書いてみたいと思います。
用意するデータは在庫データと仕入データ、この２つですね。
この２つのデータから在庫がいつ仕入れたもので構成されるのか、その内訳を算定した表をパワークエリで作っていきます。
ちなみに、大前提として在庫は先に仕入れたもの（つまり古いもの）から使っていく先入先出法を前提として作成します。
その前提が無いといつ仕入れたものか計算できませんからね。
では、さっそく見ていきましょう。
この記事でやりたいこと
まず、今回やりたいことのイメージがこちらです。
ここで仕入データは１月から３月の仕入れを、在庫データは３月末の在庫数を示しています。
この２つのデータから、在庫数をこの内訳に分解します。
- １か月以内に仕入れたもの（３月の仕入）
- ２か月以内に仕入れたもの（２月の仕入）
- ３か月以内に仕入れたもの（１月の仕入）
- ３か月より前に仕入れたもの（１月よりも前の仕入＝滞留在庫）
年齢表の作り方も見ておきます。
この図の通りなのですが、例えば直近３月の仕入れを年齢表の１か月以内に転記し、同じことを２か月以内、３か月以内と進めていきます。
ここで、それぞれ仕入の累計（１か月以内の仕入、１～２か月以内の仕入、１～３か月以内の仕入）が在庫数を超えれば、越えた月は在庫数までの分しか残っていないことになります。（上図のばなな）
逆に、１か月以内の仕入～３か月以内の仕入の累計が在庫数に届かないようであれば、それよりも前の仕入がまだ残っていることを示しています。（上図のりんご）
これをパワークエリでやっていくわけです。
事前準備
カレンダーテーブルの作成
仕入データから○か月以内の仕入、という風に置き換えるためには、日付から年齢表の内訳項目に読み替えるための読替表が必要です。
それがこちらのカレンダーテーブルです。
ここでは３月を保有期間１か月、２月を保有期間２か月、１月を保有期間３か月と読み替えていることが分かります。
ちなみに開始日、終了日は基準日を引数にした関数が入ってます。
指定した月の月末日を返すEOMOMTH関数（EndOfMonth関数）を使えば簡単ですね。
基準日が月末のためこれでOKですが、基準日を月中にする場合はここを書き換えて任意の期間を設定します。
いずれにしても、仕入日から年齢表の内訳に読み替えるための表を作っておく必要があります。
仕入データ・在庫データ・カレンダーテーブルの読込
そしてデータタブ「データの取得・変換」から、仕入データ・在庫データ・カレンダーテーブルをそれぞれパワークエリに取り込みます。
その際、日付列のデータ型が日付・時刻になってる場合は日付型に直しておきます。
データの読込はこれで完了です。
取引日パラメーターの作成
さらにさらに、あとで使うのですが、ホームタブ「パラメーターの管理」から「新しいパラメーター」を選択し、下図のように「取引日」というパラメーターを作成しておきます。
パラメータの作成が完了するとこのようになります。
パワークエリの作業ステップ（概要）
まず、パワークエリでどんなことをやっていくのか、簡単に概要を見ておきましょう。
カレンダーテーブルを使って、仕入データの仕入日を年齢表の内訳項目に読み替え、仕入数量を項目別に集計します。（仕入データ改）
次に仕入データ改を在庫データの商品別に展開し、在庫数・各月の仕入数から項目別の在庫数を算定（年齢表用データ）、最後にピボット展開して在庫年齢表を完成させます。
パワークエリでの作業ステップ（操作方法）
在庫データの編集
フィルターされた行
まずは在庫データからです。
在庫データを選択し、開始・終了のどちらでもいいので、項目ヨコのフィルターボタンを押し、「指定の値の間‥」を選択します。
行のフィルターの設定画面では「詳細設定」を選択し、列をそれぞれ下記のように選択し、値には作成した取引日パラメーターを選択します。
- 列：終了 演算子：次の値以降
- 列：開始 演算子：次の値以前
「および/または」は「および」を選択
これでOKします。
カレンダーテーブルの関数化
次に、カレンダーテーブルを関数化します。
画面左のクエリフィールドにて「カレンダーテーブル」を右クリックし、「関数の作成」を選択します。
そして関数名（ここでは「fn保有期間」としています）を入力してOKします。
そしてこうなります。
ちなみに、パラメーターに適当に日付を入れると、カレンダーテーブルをその日付でフィルターした結果が返ってきます。
これでカレンダーテーブルの編集は完了です。
仕入データの編集
ここからは、仕入データから概要に書いた「仕入データ改」を作っていきます。
カスタム関数列の追加
列の追加タブ「カスタム関数の呼び出し」を選択します。
関数クエリには先ほど作った「fn保有期間」を選択し、取引日には日付列を選択します。
展開されたfn保有期間
fn保有期間列の展開ボタンを押し、展開する列を選択します。
ここでは「保有期間」のみでOKで、「・・・プレフィックスとして使用」のチェックは外します。
これで保有期間列が追加されました。
グループ化された行
そして仕入データ最後の操作は保有期間別に数量を集計します。
変換タブ「グループ化」を選択し、詳細設定を開きます。
そして、グループ化列（＝残したい列）に「商品名」と「保有期間」を選択、新しい列名（＝集計列）は「月別仕入量」、操作を合計、列を「数量」としてOKします。
これで仕入日を保有期間に置き換えて集計した「仕入データ改」ができました。
在庫データの編集
さぁここからが本番ですよ～
ここからは在庫データを選択してやっていきましょう。
クエリのマージ
ホームタブ「クエリのマージ」を選択し、テーブルに「仕入データ」照合列はどちらも「商品名」を選択してOKします。
展開された仕入データ
仕入データ列の展開ボタンを押し、展開する列「保有期間」「月別仕入量」をチェックしてOKします。
ここでも「・・・プレフィックス・・・」のチェックは外しましょう。
並び替えられた行
次に、「商品名」列、「仕入データ列」の順に、どちらも昇順に並び替えます。
ちなみにここで重要なのが年齢表の項目である保有期間の並び替えでして、年齢表を作るときの近い方から遠い方の順番になるように並び替えます。
例えば、今回は項目名を１か月、２か月、３か月、としているため昇順ですがここを３月仕入、２月仕入、１月仕入としている場合は降順を選択します。
いずれにしても、年齢表の並びに合わせて並び替えるということがキモです。
仕入累計列の追加
次に在庫数量と比較するための仕入累計列の追加ステップを見ていきます。
ここでは商品別に仕入累計を算定していくのですが、詳細はこちらの記事もあわせてご覧ください。
仕入累計列の追加：グループ化された行
変換タブ「グループ化」を選択し、列を「商品名」新しい列の操作に「すべての行」を選択します。
仕入累計列の追加：インデックス列の追加
列の追加タブ「カスタム列」を選択し、以下のカスタム式を入力します。
カスタム列の式がこちらです。
= Table.AddIndexColumn([カウント],"インデックス",1)
仕入累計列の追加：展開されたカスタム
そして追加されたカスタム列の展開ボタンから必要な項目を展開します。
ここでもプレフィックスのチェックは外しましょう。
仕入累計列の追加：カスタム列（仕入累計列）追加の式
列の追加タブ「カスタム列」より、仕入累計のための数式を入力します。
ここでの注意はインデックス列「展開」前のテーブルを参照していることですね。
= List.Sum(
List.FirstN(
インデックス列の追加{[商品名=[商品名]]}
[カウント][月別仕入量],
[インデックス]))
これで仕入累計列の追加が完了です。
条件列の追加
仕入累計が出たら、仕入累計と在庫数量を比較し、小さい方を在庫累計の累計値とする列を追加します。
列の追加タブ「条件列」を選択します。
仕入累計の値が在庫数いかのときは仕入累計を、仕入累計の値が在庫数を超えるときは在庫数を返すように設定します。
つまり在庫累計列の値は必ず在庫数で頭打ちになるように設定します。
挿入された引き算
次に、「仕入累計ー在庫数」の列を追加します。
これにより、在庫数量を超過する月の仕入れの超過数が分かります。
仕入累計列を選択した状態で、列の追加タブ「標準」の「減算」を選択し、値に在庫累計列を選択します。
ここで作成した列名を「仕入超過」としておくと分かりやすいと思います。
仕入月別在庫列の追加
さて、いよいよ各月別在庫数列を作ります。
まず、基本的な考え方を確認しておきましょう。
- 仕入超過列が０の時：仕入月別在庫＝月別仕入量
→仕入れた分が全部残った - 仕入超過列が０より大きく、月別仕入量列以下の時：仕入月別在庫＝月別仕入ー仕入超過
→仕入れた分が一部残った - 仕入超過列が月別仕入量列より大きいとき：仕入月別在庫＝０
→仕入れた分すべて残ってない
仕入月別在庫はこのように整理できます。これを落とし込めばOKです。
カスタム列の追加より対応します。
そして、入力する数式はこちらです。
= if [仕入超過]=0 then [月別仕入量]
else if [仕入超過]<=[月別仕入量] then [月別仕入量]-[仕入超過]
else 0
さて、ここまでで仕入月別在庫数が計算できました。
最後にひとつ足りないものがあります。
それが設定した期間より前に仕入れたものが残っていた場合です。
この場合今のままではどこにも反映されません。
ですので、年齢表の大きな目的の一つである滞留状況をはっきりさせるため、「滞留在庫」列の追加の方法も見ておきましょう。
滞留在庫列の追加
方法は仕入累計列を追加した方法に似ています。仕入累計では各月の累計値を出すためList.Sum関数の入れ子にList.FirstN関数を使いましたが今回は使いません。各商品の仕入月別在庫数の合計だけが必要なので。
滞留在庫列の追加：グループ化
また商品名でグループ化します。
滞留在庫列の追加：仕入月別在庫合計列の追加
列の追加タブ「カスタム列の追加」より、このように入力します。
ここで注意点をひとつ。
参照するステップに数字やスペースを含む時（今回は数字）は「#”･･･1″」とするルールになっています。
ステップ名を参照してエラーになるときはこういったこともあることに注意が必要です。
そして必要項目を展開するとこうなります。
滞留在庫列の追加：引き算の挿入
そして滞留在庫列追加ステップ最後の処理が、在庫数から月別在庫合計列を減算した列の挿入です。
方法は先述とおなじで、在庫数を選択した状態で列の追加「引き算」に進み、「月別在庫合計」列を選択します。
あとは月別在庫合計列は役目を終えましたので削除します。
これで、年齢表を作るために必要なデータがそろいました。
列のピボット
さあいよいよ佳境です。
保有期間列を選択した状態で、変換タブ「列のピボット」を選択します。
そして値列には仕入月別在庫を選択してOKします。
最後に表の形式を整えて、ここでは滞留在庫列を最終列に移動して、完成です。
最後に
ここまで読んでいただいた方は純粋にすごいと思います。笑
初めは簡単にできるだろうとタカをくくっていたのですが、思いのほか手がかかりました。
とはいえ、これを作るステップで様々な技術を使いますので、根気はいるけどやり切った時にはパワークエリをだいぶ使いこなせるようになってるのではないかと思います。
この記事に限らず、ご意見ご質問はたまたこんな記事を書いてほしい等々ございましたら、お気軽にお問い合わせくださいませ！
最後まで読んでもらって、ありがとうございました！
