はじめに
Yahoo!知恵袋に次のような質問がありました。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14316173369
追加削除履歴があり、それを日別の在庫リストにするイメージです。
在庫リストは品種と数のクロス表ではなく、存在する品種の在庫をリストに列挙する形になっています。
ピボットテーブルではこの形の表を作成することができず、複雑なワークシート式やPower Query Mコードが必要になったので、備忘録として式やコードの仕組みをメモしておきます。
質問では追加削除履歴の削除が翌日の在庫に反映するようになっていますが、本例では一般的な当日の在庫に反映させています。（削除を翌日に反映するには、+1日した日付列をPower Queryで追加します。）
本例の集計表は一般的ではないです。一般的なPSI管理表をご希望の方は、次の記事を見てください。
1. 入力表
入力表は追加削除した種類を記入した入出簿です。数の情報はありません。
2. 出力表
1) 在庫を数字で表した表です。3.に集計方法を示します。
2) 在庫の種類を在庫数量分繰り返し列挙したものです。(Yahoo!知恵袋の質問の要求事項です。) 4~6.に集計方法を示します。
3. 在庫を数で集計する手順
これは簡単です。前記1)の出力であれば、式はメジャーの1つのみです。その他は殆どクリック操作で作成することができます。
1) 入力ブックの取得：集計ブックを開き[データ]-[データの取得v]-[ファイルから>]-[エクセルブックから]で入力表のブックを選択し[インポート]で入力表のシートを選択し[データの変換]でPower Queryに取得します。右ペインのクエルの設定のプロパティの名前を"入出簿"に変更します。
2) 自動挿入された不要なステップを削除：適用したステップの"ナビゲーション"より下のステップの左の×をクリックして、削除します。
3) ヘッダーの設定：[変換]-[1行目をヘッダとして設定v]-[1行目をヘッダとして設定]。
4) 省略されている日付を埋める："日付"列を選択し[変換]-[フィルv]-[下へ]。
5) 型の設定：Ctrl+aで全列を選択し[変換]-[データ型の検出]。データ型が正しくなければ、個々に修正。
6) 追加クエリの作成：左の"入出簿"クエリを右クリックし[参照]。右の名前を"追加"に変更。
7) 不要な列を消す：Ctrlを押しながら"日付"と"追加"列を選択し[ホーム]-[列の削除v]-[他の列の削除]。
8) データが無い不要な行を削除："追加"列の右の▼をクリックし[空の削除]。
9) 増減数の列を追加：[列の追加]-[カスタム列]で次のように入力
新しい列名：数
カスタム列の式：= 1
10) 列名を変更："追加"列をWクリックし、名前を"種類"に変更。
11) 削除クエリの作成：左の"入出簿"クエリを右クリックし[参照]。右の名前を"削除"に変更。
12) 不要な列を消す：Ctrlを押しながら"日付"と"削除"列を選択し[ホーム]-[列の削除v]-[他の列の削除]。
13) データが無い不要な行を削除："削除"列の右の▼をクリックし[空の削除]。
14) 増減数の列を追加：[列の追加]-[カスタム列]で次のように入力
新しい列名：数
カスタム列の式：= -1
15) 列名を変更："削除"列をWクリックし、名前を"種類"に変更。
16) 集計クエリの作成：[ホーム]-[クエリの追加v]-[クエリを新規クエリとして追加]で、最初のテーブルに[追加]、2つ目のテーブルに[削除]を選択し[OK]。右の名前を"集計"に変更する。
17) 型の変更："日付"列を選択し[変換]-[データ型]-[日付]。"数"列を選択し[変換]-[データ型]-[整数]。
18) カレンダテーブルの作成：左の"集計"クエリを右クリックし[参照]。右の名前を"カレンダ"に変更。
19) 日付列のみにする："日付"列を選択し[ホーム]-[列の削除v]-[列の削除]。
20) 日付の重複を削除："日付"列を選択し[ホーム]-[行の削除v]-[重複の削除]。
21) Excelに戻る：[ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"接続の作成のみ"を選択し[OK]でExcelに戻る。
22) カレンダをデータモデルに追加する：右のクエリと接続ペインの"カレンダ"クエリを右クリックし[接続先]で"データモデルに追加する"をチェックし[OK]。（クエリを接続が表示されていない時は、[データ]-[クエリと接続]をクリックする）
23) 集計をデータモデルに追加しピボットテーブルに読み出す：右のクエリと接続ペインの"集計"クエリを右クリックし[接続先]で"ピボットテーブル"と"データモデルに追加する"をチェックし[OK]。
24) リレーション：[データ]-[データモデル(緑アイコン)v]-[リレーションシップ]-[追加]で次のように設定。
テーブル：集計。関連テーブル：カレンダ。列と関連列：日付。
[Power Pivot]-[管理]-[ダイヤグラムビュー]で'集計'[日付]を'カレンダ'[日付]にドラッグしても良い。
25) メジャーの作成：ピボットテーブルをクリックし、右のピボットテーブルのフィールドで'集計'を右クリックし[メジャーの追加]で次のように入力し[OK]。
メジャーの名前：M在庫
数式：
=VAR n = CALCULATE( // 累計を求めるメジャー
SUM('集計'[数]), // 追加削除数を合計する
FILTER(ALL('カレンダ'), 'カレンダ'[日付] <= MAX('カレンダ'[日付])) // 累計範囲のフィルタ
)
RETURN // 値がある時のみ値を返す。値が無ければnullとなる。
IF(n<>0, n)
カテゴリ：数値。書式：10進数。小数点以下の桁数：0。桁区切り(,)を使う：チェック。
26) ピボットテーブルの設定：ピボットテーブルをクリックし、右のピボットテーブルのフィールドで[すべて]をクリックし、次のように設定。
列：'カレンダ'[日付]。行：'集計'[種類]。Σ値：[M在庫]。
4. '集計'クエリから在庫の種類を日別に列挙する手順
例えば在庫の種類が緑で在庫数が2の1行を、緑、緑にすると行が2行になります。しかし、メジャーで項目は生成できないので、メジャーではこの処理ができません。そこで、ワークシート関数でこの処理を行います。
27) 26)で作成したピボットテーブルのシートをコピーし、ピボットテーブルのフィールドの展開を次のように変更する。
列：無し。行：'カレンダ'[日付]、'集計'[種類]。Σ値：[M在庫]。
28) セルレイアウトの設定：ピボットテーブルを左上の"日付"がA2セルになる位置に移動。セルE3から横に抽出する日付を日付形式で入力。
29) 集計式の入力：セルE4に次の式を入力
=LET(
日付, E3,
種類, FILTER($B:$B, $A:$A=日付),
累計, FILTER($C:$C, $A:$A=日付),
n, SUM(累計),
seq, SEQUENCE(n),
idx, XMATCH(seq, SCAN(0,累計,LAMBDA(a,b,a+b)),1),
INDEX(種類, idx)
)
【式の解説】9/4を例にする
=LET(
日付, E3, // 抽出する日付 = 9/4
種類, FILTER($B:$B, $A:$A=日付), // 抽出する日付の種類の配列 = {黄,緑}
在庫, FILTER($C:$C, $A:$A=日付), // 抽出する日付の在庫数の配列 = {1, 2}
n, SUM(在庫), // 在庫の合計=抽出する行数 = 1+2 = 3
seq, SEQUENCE(n), // 抽出先の行の行番号 = {1, 2, 3}
idx, XMATCH(seq, SCAN(0, 在庫, LAMBDA(a, b, a+b)), 1),
// SCAN(初期値, 配列, LAMBDA(累積値, 現在値, 計算式))。累積値=アキュムレータ
// SCAN(0, 在庫, LAMBDA(a, b, a+b))：初期値=0 ⇒ 最初に累積値a=0になる。在庫の行毎に在庫の値がbに入り、a+bで累積値aが加算される。
// 初期値 a=0、最初の値 b=1 ⇒ a+b=1 ⇒ a=1
// 次：a=1, b=2 ⇒ a+b=3 ⇒ a=3
// SCAN = {1, 3}
// XMATCH(検索値, 検索範囲, 一致モード1=指定した値を越えない)⇒検索範囲で条件を満たす行番号を返す
// XMATCH({1, 2, 3}, {1, 3}, 1) ⇒ {1, 2, 2}
// 検索値1 は 検索範囲の値1以下 ⇒ 検索範囲の1行目 ⇒ 1
// 検索値2 は 検索範囲の値3以下 ⇒ 検索範囲の2行目 ⇒ 2
// 検索値3 は 検索範囲の値3以下 ⇒ 検索範囲の2行目 ⇒ 2
// idx：各抽出先の行が対応する、元の抽出種類の行番号 = {1, 2, 2}
INDEX(種類, idx) // INDEX({黄, 緑}, {1, 2, 2}) = {黄, 緑, 緑}
)
※ 解説を//で記載してますが、Excelのワークシート式に//でコメントを書くことはできません。
5. Power Queryで種類を在庫数分縦に並べる
メジャーでは項目の生成ができないので、PowerQueryで累計し、複数行に展開します。
このクエリはクリックでは生成できないので、"追加"と"削除"クエリを引き継いで、手動でコーディングします。
ポイントは次の2点です。
a) Power Queryで抜けの無い表を作成し累計列を追加する。
b) List.Repeat()関数を使用して、2個以上の種類の行を増やす。
16) 履歴クエリの作成：[データ]-[クエリと接続]でクエリをWクリックしてPower Queryエディタを開き、[ホーム]-[クエリの追加v]-[クエリを新規クエリとして追加]で、最初のテーブルに[追加]、2つ目のテーブルに[削除]を選択し[OK]。右の名前を"履歴"に変更する。
17) 日付でソート："日付"列の▼をクリックし、[昇順で並び替え]。
18) M言語のコードを記述：[ホーム]-[詳細エディタ]で以下の処理コードを記述する。（最初のソースと並び替えられた行がクリックで生成したコード）
let
ソース = Table.Combine({追加, 削除}),
並べ替えられた行 = Table.Sort(ソース,{{"日付", Order.Ascending}}),
// ここから下が直接記述するMのコード
// 重複を除いた全種類と全日付を抽出
種類一覧 = Table.Distinct(Table.SelectColumns(並べ替えられた行, {"種類"})),
日付一覧 = Table.Distinct(Table.SelectColumns(並べ替えられた行, {"日付"})),
// 日付×種類の全組合せを作成：種類一覧の各行に日付一覧を追加し、日付一覧を展開することで生成する
種類に日付追加 = Table.AddColumn(種類一覧, "日付", each 日付一覧),
展開 = Table.ExpandTableColumn(種類に日付追加, "日付", {"日付"}),
// 追加削除の無い日の累計も計算するため、全組合せのテーブルを作成し、そのテーブルに累積列を追加する。
// 種類ごとの日付順テーブルに対して、追加削除の数を累計して"累計数量"列に代入する。(r) => ... は行ごとの関数（ラムダ関数）
累積追加 = Table.AddColumn(展開, "累積数量", (r) =>
List.Sum( // [数]を合計(累計)
// 「入出履歴（AddedQty）」テーブルから、条件に一致する行だけを抽出。[数]列のみ抽出。
Table.SelectRows(並べ替えられた行, each [種類] = r[種類] and [日付] <= r[日付])[数]
)
),
// 0 より大きい場合のみ残す。累計を格納したので、在庫が無いデータは不要になり、削除する。
抽出 = Table.SelectRows(累積追加, each [累積数量] > 0),
// List.Repeat を使って、その在庫数分だけ「種類」を繰り返すリストを追加し、追加したリストを展開することで行を追加する。
// 文法：List.Repeat(list as list, count as number) as list：
// 元のリスト list を count の回数繰り返した結果のリストを返します。例：List.Repeat({1, 2}, 3)
繰り返し列 = Table.AddColumn(抽出, "リスト", each List.Repeat({[種類]}, [累積数量])),
展開済み = Table.ExpandListColumn(繰り返し列, "リスト"),
// 最終出力整形
結果 = Table.SelectColumns(展開済み, {"日付", "リスト"}), // [日付]と[リスト]列のみにする
変更された型 = Table.TransformColumnTypes(結果,{{"日付", type date}, {"リスト", type text}}),
並べ替えられた行1 = Table.Sort(変更された型,{{"日付", Order.Ascending}, {"リスト", Order.Ascending}})
in
並べ替えられた行1
19) テーブルへの読み出し：[ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"テーブル"を選択し[OK]。
20) セルレイアウトの設定：テーブルを左上の"日付"がA2セルになる位置に移動。セルD2から横に抽出する日付を日付形式で入力。
21) 集計式の入力：セルD3に次の式を入力
=FILTER($B:$B, $A:$A = D$2)
6. ワークシート関数のみで種類を在庫数分縦に並べる
Yahoo!知恵袋に他の人が回答した式です。
1) セルレイアウトの設定：追加削除表の左上の"日付"がA1セル、作業用の追加削除表の左上の日付がI1セル、セルL2から横に抽出する日付を日付形式で入力、L2から横に中間式を入力、L3から横に検索結果の式を入力。
2) 作業用の追加削除表：セルI2に次の式を入力（この式はYahoo!知恵袋の回答にはありません。）
=LET(a,
VSTACK(
HSTACK($A2:$B8, TOCOL(
TEXTSPLIT(
REPT("追加,", ROWS($A2:$A8)),
",")
)
),
HSTACK($A2:$A8, $C2:$C8, TOCOL(
TEXTSPLIT(
REPT("削除,", ROWS($A2:$A8)),
",")
)
)
),
FILTER(a, NOT(ISBLANK(CHOOSECOLS(a, 2))) * NOT(ISERROR(CHOOSECOLS(a, 2))))
)
【式の解説】
=LET(a,
VSTACK( // "追加行の表"と"削除行の表"を縦に連結
HSTACK($A2:$B8, // "日付と追加"列の表と"操作"列を横に結合する
TOCOL( // 縦にする⇒操作列になる
TEXTSPLIT( // ","で配列に分割する
REPT("追加,", ROWS($A2:$A8)), // "追加,"を行数分結合する
",")
)
),
HSTACK($A2:$A8, $C2:$C8, // "日付"と"削除"と"操作"列を横に結合する
TOCOL( // 縦にする⇒操作列になる
TEXTSPLIT( // ","で配列に分割する
REPT("削除,", ROWS($A2:$A8)), // "削除,"を行数分結合する
",")
)
)
),
// 2列目("種類"列)が空欄(データが無い)ではない行と
// エラー(REPTで生成する操作列の最後に","が付くので、操作列が1行多くエラーになる)ではない行を取得する
FILTER(a, NOT(ISBLANK(CHOOSECOLS(a, 2))) * NOT(ISERROR(CHOOSECOLS(a, 2))))
)
3) 作業用の種類文字列：セルL2に次の式を入力
=LET(
fx_1, LAMBDA(項目, FILTER($J:$J, ($I:$I<=L1) * ($K:$K=項目), "")),
追加, TEXTJOIN(",", , fx_1("追加")),
削除, fx_1("削除"),
fx_2, LAMBDA(fx_2,リスト,i,
IF(i <= COUNTA(削除),
fx_2(fx_2, SUBSTITUTE(リスト, INDEX(削除, i), "", 1), i+1),
リスト)
),
fx_2(fx_2, 追加, 1)
)
【式の解説】
L1=9/3の時のデータフィルタリング結果
追加データ
日付,種類,操作
9/1,赤,追加
9/1,緑,追加
9/2,黄,追加
9/2,白,追加
9/3,黒,追加
削除データ
日付,種類,操作
9/3,白,削除
9/3,赤,削除
=LET(
// 種類列($J:$J)を、日付列($I:$I)<=検索日付(L1)と操作列($K:$K)=項目(追加/削除)で、フィルタする
fx_1, LAMBDA(項目, FILTER($J:$J, ($I:$I<=L1) * ($K:$K=項目), "")),
追加, TEXTJOIN(",", , fx_1("追加")), // 操作が追加の種類を","区切りで結合する⇒"赤,緑,黄,白,黒"
削除, fx_1("削除"), // 操作が削除の種類を配列にする⇒{白, 赤}
// 削除の各要素を追加文字列から1つずつ消していく再帰関数
fx_2, LAMBDA(fx_2,リスト,i,
IF(i <= COUNTA(削除), // 削除の行数を越えたら再帰終了=削除の行分繰り返す
fx_2(fx_2, SUBSTITUTE(リスト, INDEX(削除, i), "", 1), i+1),
リスト)
),
fx_2(fx_2, 追加, 1) // 最初に呼び出す際の引数（自身(再帰の為), 追加文字列, インデックス1）
// 初期値：
// リスト = "赤,緑,黄,白,黒"
// i = 1
// 削除 = {"白", "赤"}
// ステップ 1: i = 1
// INDEX(削除,1) = "白"
// SUBSTITUTE("赤,緑,黄,白,黒", "白", "", 1) で削除の1行目の白を""に置換 → "赤,緑,黄,,黒"
// 再帰呼出し → fx_2(fx_2, "赤,緑,黄,,黒", 2)
// ステップ 2: i = 2
// INDEX(削除,2) = "赤"
// SUBSTITUTE("赤,緑,黄,,黒", "赤", "", 1) で削除の2行目の赤を""に置換 → ",緑,黄,,黒"
// 再帰呼出し → fx_2(fx_2, ",緑,黄,,黒", 3)
// ステップ 3: i = 3
// i > COUNTA(削除)なので再帰終了、リストを返す
// → 結果：,緑,黄,,黒
)
補足：なぜ fx_2 を自分自身に渡す必要があるのか？
Excel の LETで変数に定義したLAMBDAでは、関数定義内から「自分自身」を直接参照できません。
再帰を実現するには、第1引数として「自分自身」を渡す必要があります。
なお、名前の定義でLAMBDAを使用した場合は、グローバル名空間の名前になるので、自身の引数が無くても再帰呼出しができます。
4) 種類の抽出結果：セルL3に次の式を入力
=TEXTSPLIT(L2, , ",", TRUE)
【式の解説】
TEXTSPLITでカンマ区切りし、空文字を無視（第4引数がTRUE）
L2 = ",緑,黄,,黒"
結果：{緑, 黄, 黒}
7. サンプルファイル
ダウンロード - 250617iridebo.xlsx
C:\Users\Public\Documents に格納してください。
8. 参考
長いワークシート関数式、配列数式を作る方法
最近のコメント