Power Query：先入先出法で在庫表を作る

他人が作った在庫管理表を見ると、大体の場合「在庫」という列が右端についている。

ただこれだけでは「在庫が減っているな／増えたな」は把握できても、「いつ仕入れたものがどれだけ残っているのか」は分からない。単価が変動するものの場合は「いつ仕入れた在庫がどれだけ残っているのか」を把握しないといけないので、在庫状況を確認するための表が別途必要になる。つまり入力表の右横に在庫列なんて付けてもあんまり役には立たないのである。これは現金出納帳の「残高」列にもいえる。こういうのは全部、印刷物だった時の名残でしかない。

ちなみに、上の表の「在庫」は下記の数式で計算できる。

=SUM(D2,B3)-SUM(C3)

構造化参照にするなら

=SUM(OFFSET([@在庫],-1,0),[@入庫])-SUM([@出庫])

閑話休題。

先入先出法で在庫状況を把握する場合、必要になるのは「（今回の場合は日ごとの）入庫の累計」で「行ごとの差引累計」なんていらない。

    行の抽出 = Table.SelectRows(在庫管理表, each [入庫]<>null),
    テーブル再編 = [
        入庫リスト = 行の抽出[入庫],
        累計 = List.Accumulate(入庫リスト, {0},
            (x, y)=> {y + x{0}} & x
        ),
        リスト調整 = List.Skip(List.Reverse(累計)),
        テーブル変換 = Table.FromColumns(
            {行の抽出[日付], 入庫リスト,リスト調整},
            type table [入庫日=date, 入庫数=Int64.Type, 累計=Int64.Type]
        )
    ][テーブル変換],

まずはここまで。これで累計列を追加したテーブルができたので、行ごとの累計から現在の出庫合計を引いて「0」より大きくなるものだけ残してやればいい。

    出庫計 = List.Sum(在庫管理表[出庫]),
    在庫数列の追加 = Table.AddColumn(
        テーブル再編, "在庫数", each
        List.Min({[累計]-出庫計,[入庫数]})
    ),

在庫状況表は日付降順のほうが見やすいので、並べ替えしてしまおう。とはいえ「在庫」列があるくらいだから、元表は日付昇順に並んでいるに決まっている。こういう時は Table.Sortではなく、Table.ReverseRowsを使った方がいい。

    行の反転 = Table.ReverseRows(在庫数列の追加),

あとは在庫数が正の値のデータだけを残す。この時も Table.SelectRowsではなく、Table.FirstNを使おう。

    上位行の保持 = Table.FirstN(行の反転, each [在庫数]>0),
    列の選択 = Table.SelectColumns(上位行の保持, {"入庫日", "在庫数"})

最後に列を選択すればおしまい。

こうすれば元の入出庫表に在庫列なんて付けなくても、在庫状況表は作れる。