伝票明細をスプシを使って違うフォーマットに落とし込む - ゼロからはじめるアパレルDX

こんにちは。テックブログの萱場です。今回は、基幹システムから出力した伝票明細を、スプシを使って別のフォーマットに落とし込むことをやってみたいと思います。まだ、試行錯誤中なのですが、皆様の考えるヒントになれば幸いです。

前提条件

先に画像を使ってやることのイメージをお伝えしたいと思います。

シートA

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

シートB

こんな感じで、1行毎の売上明細を、日付単位の数量のフォーマットに落とし込む感じになります。

※シートAの明細数よりもシートBの明細数が少ないのは、売上パターン(再利用可能フォーマット)の登録で数量0のデータでもそのままデータ登録しているためです。

やること

上記を行うためにやることを箇条書きにしてみました。

品番の相対表をSKU単位で作る
売上伝票(シートA)の明細を、相対表を使ってSKU単位の読み替え可能な様にする
シートBの内容から、SKU、日付などから、クロス集計できる様にする。

基本的には、以上で実現可能です。

やること1 品番相対表を作成する

これは、そのままですね。

以下のようにシートを作ります。

特段の工夫は無いのですが、シートBのSKUに関しては、項目含めてだいぶゆらぎがある場合があります。品名のところにサイズ込の記入になっていたり、各列に対して、入力される項目が一定ではありません。

なので、SKUを読み替えるためのB列とH列は、それぞれ、連結文字列はなしで、繋いでいます。

この様につなぐことで、文字列の可読性は下がりますが、項目の順番さえ一致していれば、別の列に内容が入力されていても、問題なく読み替えることができます。

また、シートAのSKUとシートBのSKUは、必ずしも一対一の関係ではありません。シートBでは、単価が変わらない場合、まとめて入力する場合がありますが、後にでてくるSUMIFS関数でクロス集計する場合、シートAのSKU 対シートBのSKUが多対一になっていても問題ありません。(多対多だと崩壊しますが、、、)

やること2 シートAに読み替え用の列を作成する

明細のSKUをシートBのSKUに読み替えるために、A列、B列を新たに挿入して、B列には、明細のSKU(シートAのDH列、DI列、DJ列、DL列を入力しています。

A列には、B列の値を品番相対表で、検索してシートBのSKUを持ってきています。

やること3 シートBに必要な列を追加

今度は、シートBの方です。

以下の画像の様に赤い行をシートBに追加します。

A列

A列は、品番相対表で読み替えができるように、各行のB、C、D、E列の値をTEXTJOINしています。

TEXTJOIN()

B2セル

この、シートBのフォーマットは、複数ある先述の売上パターン毎にプリントできるようなレイアウトになっています。また別の売上パターンで同じSKUがでてくることもあります。

なので、B2セルには、SUMIFSの条件で使えるように、売上パターンの文字列を入れています。

F2:S2

F2からS2は、日付が入っています。単純な数字だど日付として認識されないので、T1セルに年、U1セルに月を入れて、DATE()関数を使っています。

DATE()

やること4 クロス集計

準備はできましたので、あとは、SUMIFS()を使って条件に合う数量を、各セルに出力するだけです。

SUMIFSとLET関数

=LET(value,SUMIFS('シートA'!$DR$2:$DR,'シートA'!$A$2:$A,$A4,'シートA'!$E$2:$E,F$2,'シートA'!$N$2:$N,$B$2),IF(value <>0,value,""))

長いですが、上記のような数式が、F4からS4までそれぞれ入っています。

SUMIFS関数

文字にして解説すると、

SUMIFSの引数項目名	参照している範囲または値
集計範囲	シートAの数量部分
条件範囲1	シートAのA列
条件1	シートBの各行のA列の値
条件範囲2	シートAの日付列
条件2	シートBの2行目の各列の値(日付)
条件範囲3	シートAの売上パターン列
条件3	シートBの売上パターンが入力されたセル