前の記事では、「クエリのマージ」でローデータに商品マスタの列を足しました。
この記事ではその応用として、構成マスタをマージして、部材の使用数を出す方法を紹介します。
実務では、製品を販売した数の連絡が来るので、そこからどの部材をいくつ使ったかを出しています。たとえば、商品Aに部材Bを3つ、部材Cを4つ使う場合、商品Aを5個販売したら、部材Bは15個、部材Cは20個使ったことになります。部材Bを単品で1つだけ販売することもあるので、その場合は部材Bを1個と数えます。
なお、この記事の表や手順は説明のために作ったもので、実際に使っているExcelとは作りが違います。商品コードや商品名も架空です。
例の表
販売数の表です。「クッキー詰め合わせ」を5個、「プレーンクッキー」を単品で1つ販売しました。
| 商品コード | 商品名 | 数量 |
|---|---|---|
| 2001 | クッキー詰め合わせ | 5 |
| 3001 | プレーンクッキー | 1 |
構成マスタには、商品ごとに、使う部材とその数量を登録しています。部材コードといっても、商品コードと同じコードです。
| 商品コード | 部材コード | 数量 |
|---|---|---|
| 2001 | 3001 | 3 |
| 2001 | 3002 | 4 |
クッキー詰め合わせ1つに、プレーンクッキー（3001）を3つ、チョコクッキー（3002）を4つ使う、という意味です。プレーンクッキーは部材を使わないので、構成マスタには登録していません。
構成マスタも、前の記事の商品マスタと同じようにクエリにして、「接続の作成のみ」にしておきます。
列名を変えておく
このあと、構成マスタから「数量」の列を展開し、条件列で新しい「商品コード」の列を作ります。同じ名前の列ができないように、販売数のクエリで、元の「商品コード」を「商品コード.0」、「数量」を「数量.0」に変えておきます。列名はダブルクリックで変えられます。
構成マスタをマージする
［ホーム］タブ→［クエリのマージ］を押して、次のように指定します。
- 上の表：販売数。「商品コード.0」の列を選ぶ
- 下の表：「構成マスタ」を選び、「商品コード」の列を選ぶ
- 結合の種類：「左外部」
左外部にするのは、構成マスタにない商品を「null」（空）にするためです。左外部は、上の表の行をすべて残して、構成マスタにあるものだけ列を足します。そのため、構成マスタにないプレーンクッキーの行も消えずに残り、部材コードと数量がnullになります。このnullを、あとの条件列とカスタム列で使います。
部材コードと数量を展開する
「構成マスタ」の列の見出しにある左右の矢印のボタンを押し、「部材コード」と「数量」にチェックを入れて［OK］を押します。「元の列名をプレフィックスとして使用します」のチェックは外しておきます。
展開したら、構成マスタから来た「数量」の列名を「数量.1」に変えます。
| 商品コード.0 | 商品名 | 数量.0 | 部材コード | 数量.1 |
|---|---|---|---|---|
| 2001 | クッキー詰め合わせ | 5 | 3001 | 3 |
| 2001 | クッキー詰め合わせ | 5 | 3002 | 4 |
| 3001 | プレーンクッキー | 1 | null | null |
クッキー詰め合わせは、構成マスタに部材が2つあるので、2行に増えます。商品マスタのときと違い、構成マスタは1つの商品に複数の部材があるので、展開すると部材の数だけ行が増えます。
条件列で使った商品のコードを出す
部材コードがnullの行は、構成マスタにない商品です。この行は元の商品コードをそのまま使い、それ以外の行は部材コードを使います。
［列の追加］タブ→［条件列］を押して、次のように指定します。
- 新しい列名：商品コード
- 列名：部材コード
- 演算子：次の値に等しい
- 値：null
- 出力：「列の選択」に切り替えて、商品コード.0
- それ以外の場合：「列の選択」に切り替えて、部材コード
部材コードは商品コードと同じコードなので、詰め合わせに使った部材も、単品で販売した商品も、新しい「商品コード」の列にそろいます。
使用数を出す
使用数は、「数量.1」がnullなら「数量.0」のまま、それ以外は「数量.0」×「数量.1」です。
［列の追加］タブ→［カスタム列］を押して、新しい列名を「使用数」、式を次のようにします。
if [数量.1] = null then [数量.0] else [数量.0] * [数量.1]| 商品コード.0 | 数量.0 | 部材コード | 数量.1 | 商品コード | 使用数 |
|---|---|---|---|---|---|
| 2001 | 5 | 3001 | 3 | 3001 | 15 |
| 2001 | 5 | 3002 | 4 | 3002 | 20 |
| 3001 | 1 | null | null | 3001 | 1 |
クッキー詰め合わせ5個分で、プレーンクッキーを15個、チョコクッキーを20個使ったことになります。単品で販売したプレーンクッキーは、1個のままです。表では商品名の列を省いています。
部材ごとに合計する
最後に、新しい「商品コード」でグループ化して、使用数を合計します。グループ化のやり方はこちらの記事で紹介しています。
| 商品コード | 使用数 |
|---|---|
| 3001 | 16 |
| 3002 | 20 |
詰め合わせに使った分と単品で販売した分を合わせて、プレーンクッキーは16個使ったことが分かります。
まとめ
- 元の「商品コード」「数量」は「.0」、構成マスタの「数量」は「.1」を付けて、列名が重ならないようにする
- 構成マスタを「左外部」でマージすると、構成マスタにない商品はnullになる
- 展開すると、部材の数だけ行が増える
- 条件列で、部材コードがnullなら元の商品コード、それ以外は部材コードを使う
- 使用数はカスタム列で、数量.1がnullなら数量.0、それ以外は数量.0×数量.1
- 最後にグループ化すれば、部材ごとの使用数が分かる
