きったんの、その一足が道となる

kintoneを使った業務改善の記録

LAMBDA関数を使ってExcelで在庫表を一発で作成する

Excel

Excelで在庫表を作成しようとするとき、何を使用しますか？
VBA、ピボットテーブル、Power Query、SUMIFなどの関数、いろいろな手段があります。

私がまだVBAもPower Queryも使えなかった頃、
ピボットテーブルがあまり好きではない私は、色々な関数を駆使して在庫表を作成していました。

それが一つの関数で在庫表が作れるようになるとは便利な時代になったものだと思いました。

LAMBDA関数とは

「LAMBDA」関数を利用すると、「Excel」の関数で新しい関数を作り出すことが可能。ユーザーが独自のカスタム関数を定義し（「LET」関数を組み合わせれば名前も付けられる）、ワークブックで再利用することができるので、冗長な繰り返し表現が排除されて数式の見通しがよくなるほか、無用な入力ミスを防止できる。

素人の私には難しい説明はできませんが、とにかく「名前の定義」でLAMBDA関数に名前を付けて、自作の関数としてブック内で使用できるようになるということです！
しかもVBAを使わずに！

自作の関数が作れるのならひとまず在庫表を作ってみようと思い、チャレンジしてみた次第です。

INVENTORY関数を作成してみる

「在庫」を意味するINVENTORY関数をLAMBDA関数を使って作ってみました。

完成品はこんな感じです。
引数に出納履歴テーブルの商品名、入庫、出庫を指定するだけ。
これだけで在庫表ができるようになるんですね。すごい。


これの中身はというと、

1列目　商品名のユニークリストを作る
2列目　履歴テーブルの商品名、1列目のリスト、テーブルの入庫でSUMIF
3列目　履歴テーブルの商品名、1列目のリスト、テーブルの出庫でSUMIF
4列目　2列目 - 3列目で在庫数を出す

このように4つの別々に関数で作った配列を、最後に一つの配列にしてセルに出力しているということになります。

SEQUENCE関数のすごさ

4つの配列を一つの配列にするのはSEQUENCE関数を使います。
SEQUENCE関数は簡単に説明すると連番を作成する関数なわけですが、例えば「=SEQUENCE(10)」にすると1から10までの列になります。

また、「=SEQUENCE(1,10)」にすると1から10までの行になります。

この関数はいろいろな使い方をするととても便利になるポテンシャルを持っています。（と個人的に思っています。）
その一つの方法として、SEQUENCE関数の中にSEQUENCE関数を入れることができます。

具体的には、左側に作った①②③の縦の配列を、右側の横の配列の1を①、２を②、３を③に置き換えることで3つの配列が１つの配列となります。

「1なら○○、2なら○○、3なら○○、・・・」という処理はCHOOSE関数を使うことで可能です。
CHOOSE(置き換えたい配列,１を①,２を②,３を③）というイメージです。

「CHOOSE(SEQUENCE(1,3),SEQUENCE(10),SEQUENCE(10,1,101),SEQUENCE(10,1,501))」
これで先ほどの配列が一つの配列になりました。
こんなことができると知ったとき、SEQUENCE関数ってなんてすごいだ…と感動したのを覚えています。

この要領で在庫出納から作った4つの配列をCHOOSE関数で繋げて、在庫表の完成です！！！

INVENTORY = LAMBDA(商品名, 入庫数量, 出庫数量,
    LET(
        product, UNIQUE(商品名),
        reciept, SUMIF(商品名, product, 入庫数量),
        delivary, SUMIF(商品名, product, 出庫数量),
        CHOOSE(SEQUENCE(1, 4), product, reciept, delivary, reciept - delivary)
    )
);

まとめ

正直、LAMBDA関数の使い方が正しいかどうかもわかりませんし、こんなやり方じゃないもっと簡単な方法があるかもしれません。

ただ比較的メジャーな関数だけを使って作成することができたってことで大目に見ていただければと思います。



これはLAMBDA関数どうこうよりもSEQUENCE関数のおかげですね・・・