見出し画像

会計パーソンのためのExcel～第３回～

HINAKI

皆さん、こんにちは。

お読みいただき、ありがとうございます。

第３回目は、第２回に続きマスタデータを作成していきます。
今回は商品マスタです。


商品マスタ（M_商品）

完成イメージ

この条件から作っていきますが、加工のポイントは以下２つ。

  • 各商品は、有価証券報告書等におけるセグメントに紐づく。セグメントA、B、Cはそれぞれ、２つ、４つ、３つの商品から構成される。
    例：Aであれば、A00001，A00002。

  • A、B、Cはそれぞれ低価格帯、中価格帯、高価格帯と価格レンジが異なる。
    例：Aであれば、単価が1,000円～9,999円のいずれかの数字。

セルに含まれる数字だけ行を増幅させる

前回も使用したリストを使用します。
Excelと違って、セルの中に複数行持たせることができるのがポイントです！
※今回はリストですが、テーブルも持たせられます。こちらは追々。

カスタム列で、リスト使用時の構文{開始数字..終了数字}とします。
今回の終了数字は、「所属商品数」に入っている数字ですので、その値を入れます。

Power Query エディター起動
↓
カスタム列
↓
カスタム式 = {1..[所属商品数]}
↓
新しい列名：仮に「枝番」とする

すると、各セルに「List」と表示され、各セルの「List」という文字以外の箇所をクリックすると、その中身を確認できます。たしかに１～４の連番ができていそうです。

右上の左右に広がった⇔をクリックし、「新しい行に展開する」を選択

すると、１つのセルにまとまっていたListの各数字がそれぞれの行に分散します。展開した列以外のデータについては、そのまま増殖します。

商品コードを作る

以下のような規則性を仮定します。
先頭：Ｐ（ProductのP）
集計先セグメント：列の文字（A、B、C）
末尾：５桁の連番（枝番の数字、ただし５桁に満たないものは０で穴埋め）

こちらも前回使用した、「例からの列」を試してみます。

  • Ctrlを押しながら「集計先セグメント」と「枝番」を選択

  • 「例からの列」を選択

  • ためしに１行目にPA00001と入力

結果は問題なさそうです。
が、数式を見ると、"0000"とゼロ４つ＋枝番の数字になっています。
もし枝番が１０個以上あると、前回同様、桁ズレが起きます。
例）枝番が10＞000010

= Table.AddColumn(#"展開された 枝番", "結合済み", each Text.Combine({"P", [集計先セグメント], "0000", Text.From([枝番], "ja-JP")}), type text)

したがって、前回は参考程度に示した数式を利用して、直接関数をいじってみます。
"0000", Text.From([枝番], "ja-JP"　→　Text.PadStart(Text.From([枝番]),5,"0")
Text.PadStart（X, Y, Z）は、文字列XをY桁で表示し、桁数が不足する場合は先頭をZで埋める、という意味です。
今回は、枝番を５桁で使用するが、足らない分は０で埋める、ということですので要件を満たします。ただし、数字を文字列結合する際、枝番の状態だとそのまま結合できないので、明示的に文字に変換する必要があります。この操作をText.From([枝番])で実行しています。
なお、末尾のtype textは最後に列のデータ型をまとめて変換するのでここでは除外でOKです。

= Table.AddColumn(#"展開された 枝番", "結合済み", each Text.Combine({"P", [集計先セグメント], Text.PadStart(Text.From([枝番]),5,"0")}))
商品コードに名称を変更して完成

集計セグメントに応じた単価をランダムに設定する

行を増幅できましたので、各行の単価-最小、単価-最大の数字を使用し、ランダムな値を生成します。単価は100円単位で丸まった数字にします。
列の追加＞カスタム列で列を追加し、カスタム列の式を入力します。
先頭には、数字を使うよ、という意味で"Number."が必要ですが、意味するところはExcelと同じなのでわかりやすいです。
Number.RandomBetween(X, Y)
→Excel関数の"RANDBETWEEN"と同様です。
Number.Round(X,Y)
→Excel関数の"Round"と同様です。

= Number.Round(
    Number.RandomBetween([#"単価-最小"],[#"単価-最大"]),
    -2
)

ランダムな商品名を生成する

以下と同様の方法を使用します。

今回は、ひとつの数式でまとめてみましょう。

= Character.FromNumber(
    Number.Round(
        Number.RandomBetween(65,90),0
    )
)

３列を結合し、列名を"商品名"とします。
結合したい３列を選択＞変換＞列のマージ

列の順番を整える

列名のところをクリックしたまま、好きな場所へドラッグできますので、順序を整えます。
最後に、不要な列を削除します。
※以下では、必要な列だけ選択し、右クリック＞他の列の削除としました。

テーブルを全選択し、変換＞データ型の検出から、データ型を整えます。
これで完成です！


いいなと思ったら応援しよう！