エクセル数式で部品表展開を行なう方法
概要
エクセルで親子関係が登録されたマスターから多段階部品表を生成するには、これまでVBA等を用いる必要がありましたが、最近追加された関数を用い
て、数式だけで多段階部品表を生成できるようになりました。（Excel365が必要です。）
ここでは、親子とQTYが登録された部品表から部品展開を行ない、製品ごとの数量と掛け合わせて、展開した部品の数量を集計するエクセル数式を紹介します。
初稿 2024年7月24日
部品表テーブルを用意
左のような部品構成を例題とします。
エクセルで、左のテーブルを作成します。
テーブル名は「部品表テーブル」として下さい。
項目名は、［親部品］［子部品］［QTY］として下さい。
（テーブル名や項目名を変更したい場合は、紹介する数式を変更すれば可能です。）
（テーブルの作成方法の説明は割愛いたしますので、ご不明な場合は、お問い合わせ下さい。）
「部品表展開」を【名前の定義】に登録
以下の数式を【名前の定義】に登録します。
=LAMBDA(製品, LET( _部品No抽出,LAMBDA(部品,FILTER(SEQUENCE(ROWS(部品表テーブル[#データ])),部品表テーブル[親部品]=部品,"")), _次展開,LAMBDA(前展開, LET( 展開列数,COLUMNS(前展開), 展開済列,DROP(前展開,,-1), 右端列,TAKE(前展開,,-1), 展開行数,ROWS(前展開), 次展開数,LAMBDA(i, LET( 右端No,VALUE(INDEX(右端列, i)), 子部品,INDEX(部品表テーブル[子部品],右端No), com,"// 次展開が、製品または展開途中の部品に戻ったら展開中止 //", IFS(右端No = 0,0, 子部品 = "",0, 子部品 = 製品,0, AND(展開列数 > 1, ISNA(MATCH(右端No,CHOOSEROWS(展開済列,i),0)) = FALSE ),0, TRUE,COUNT(_部品No抽出(子部品)) ) ) ), loop1,LAMBDA(self,i, LET( 右端No,VALUE(INDEX(右端列, i)), 子部品,INDEX(部品表テーブル[子部品],右端No), dt,IF(次展開数(i) = 0,0,ARRAYTOTEXT(_部品No抽出(子部品),0)), IF(i <= 展開行数, CONCAT(dt,",",self(self, i + 1)), "" ) ) ), 次展開列,VALUE(TEXTSPLIT(loop1(loop1,1),,",",TRUE)), loop2,LAMBDA(self,i, LET( dt,SEQUENCE(,MAX(次展開数(i),1),i,0), IF(i <= 展開行数, HSTACK(dt,self(self, i + 1)), "" ) ) ), 展開行,DROP(loop2(loop2,1),,-1), HSTACK(CHOOSEROWS(前展開,展開行),次展開列) ) ), loop,LAMBDA(self,前展開, LET(次展開,_次展開(前展開), 右計,SUM(TAKE(次展開,,-1)), IF(右計 = 0, 前展開, self(self, 次展開) ) ) ), 製品展開,_部品No抽出(製品), 展開全体,loop(loop, 製品展開), IFS(製品 = "","", COUNT(製品展開) = 0,"", TRUE, 展開全体 ) ) )
具体的な手順
１．上記数式をクリップボードにコピー
２．エクセル画面 ［数式］タブ → ［名前の定義］クリック
（左の状態になります）
３．〈名前〉に「部品表展開」と入力
４．Tabキーで〈参照範囲〉に移動して、クリップボードを貼付け（マウスの右クリック）
５．〈OK〉クリック
（名前に「部品表展開」という数式が登録されます）
６．〈閉じる〉クリック
なお、見やすい状態にした数式は、こちらです。
=LAMBDA(製品,
LET(
_部品No抽出,LAMBDA(部品,FILTER(SEQUENCE(ROWS(部品表テーブル[#データ])),部品表テーブル[親部品]=部品,"")),
_次展開,
LET(
_部品No抽出,LAMBDA(部品,FILTER(SEQUENCE(ROWS(部品表テーブル[#データ])),部品表テーブル[親部品]=部品,"")),
_次展開,
LAMBDA(前展開,
LET(
展開列数,COLUMNS(前展開),
展開済列,DROP(前展開,,-1),
右端列,TAKE(前展開,,-1),
展開行数,ROWS(前展開),
次展開数,LAMBDA(i,
LET(
右端No,VALUE(INDEX(右端列, i)),
子部品,INDEX(部品表テーブル[子部品],右端No),
com,"// 次展開が、製品または展開途中の部品に戻ったら展開中止 //",
IFS(右端No = 0,0,
子部品 = "",0,
子部品 = 製品,0,
AND(展開列数 > 1,
ISNA(MATCH(右端No,CHOOSEROWS(展開済列,i),0)) = FALSE
),0,
TRUE,COUNT(_部品No抽出(子部品))
)
)
),
loop1,LAMBDA(self,i,
LET(
右端No,VALUE(INDEX(右端列, i)),
子部品,INDEX(部品表テーブル[子部品],右端No),
dt,IF(次展開数(i) = 0,0,ARRAYTOTEXT(_部品No抽出(子部品),0)),
IF(i <= 展開行数,
CONCAT(dt,",",self(self, i + 1)),
""
)
)
),
次展開列,VALUE(TEXTSPLIT(loop1(loop1,1),,",",TRUE)),
loop2,LAMBDA(self,i,
LET(
dt,SEQUENCE(,MAX(次展開数(i),1),i,0),
IF(i <= 展開行数,
HSTACK(dt,self(self, i + 1)),
""
)
)
),
展開行,DROP(loop2(loop2,1),,-1),
HSTACK(CHOOSEROWS(前展開,展開行),次展開列)
)
),
loop,LAMBDA(self,前展開,
LET(次展開,_次展開(前展開),
右計,SUM(TAKE(次展開,,-1)),
IF(右計 = 0,
前展開,
self(self, 次展開)
)
)
),
製品展開,_部品No抽出(製品),
展開全体,loop(loop, 製品展開),
IFS(製品 = "","",
COUNT(製品展開) = 0,"",
TRUE,
展開全体
)
)
)
LET(
展開列数,COLUMNS(前展開),
展開済列,DROP(前展開,,-1),
右端列,TAKE(前展開,,-1),
展開行数,ROWS(前展開),
次展開数,LAMBDA(i,
LET(
右端No,VALUE(INDEX(右端列, i)),
子部品,INDEX(部品表テーブル[子部品],右端No),
com,"// 次展開が、製品または展開途中の部品に戻ったら展開中止 //",
IFS(右端No = 0,0,
子部品 = "",0,
子部品 = 製品,0,
AND(展開列数 > 1,
ISNA(MATCH(右端No,CHOOSEROWS(展開済列,i),0)) = FALSE
),0,
TRUE,COUNT(_部品No抽出(子部品))
)
)
),
loop1,LAMBDA(self,i,
LET(
右端No,VALUE(INDEX(右端列, i)),
子部品,INDEX(部品表テーブル[子部品],右端No),
dt,IF(次展開数(i) = 0,0,ARRAYTOTEXT(_部品No抽出(子部品),0)),
IF(i <= 展開行数,
CONCAT(dt,",",self(self, i + 1)),
""
)
)
),
次展開列,VALUE(TEXTSPLIT(loop1(loop1,1),,",",TRUE)),
loop2,LAMBDA(self,i,
LET(
dt,SEQUENCE(,MAX(次展開数(i),1),i,0),
IF(i <= 展開行数,
HSTACK(dt,self(self, i + 1)),
""
)
)
),
展開行,DROP(loop2(loop2,1),,-1),
HSTACK(CHOOSEROWS(前展開,展開行),次展開列)
)
),
loop,LAMBDA(self,前展開,
LET(次展開,_次展開(前展開),
右計,SUM(TAKE(次展開,,-1)),
IF(右計 = 0,
前展開,
self(self, 次展開)
)
)
),
製品展開,_部品No抽出(製品),
展開全体,loop(loop, 製品展開),
IFS(製品 = "","",
COUNT(製品展開) = 0,"",
TRUE,
展開全体
)
)
)
文字数制限のためか、このままでは名前の定義の〈参照範囲〉に登録できなかったので、余分な空白を除去した数式を登録するようにしました。
「部品表展開」を確認
数式が正しく登録されたかテストします。
どこかのセルに “ =部品表展開("X") ”と入力して下さい。
このように表示されれば正しく登録されています。
「部品表展開」は、引数（ここでは “X” ）の多段階部品表を、「部品表テーブル」の行番号で表現した配列を返します。
（例ではエクセルの４行目が行番号１になります）
“X”→“A” （行番号 １ ）
“X”→“B”→“C” （行番号 ２→５）
“X”→“B”→“D” （行番号 ２→６）
ツリー展開の状態を確認
以下の数式で、製品“X”のツリー展開の状態を確認できます。
登録した部品表の確認用として、必要によりお使い下さい。
=MAP(部品表展開("X"),LAMBDA(d,IF(d = 0,"",INDEX(部品表テーブル[子部品],d) & " (" & INDEX(部品表テーブル[QTY],d) & ")")))
どこかのセルに、上記数式を入力（コピー＆ペースト）します。
この数式は、「部品表展開」の行番号を、部品表テーブルの部品名とQTYで置き換えた結果を返します。
例では、製品"X"を展開した親子関係とQTYを表示します。
数式中の"X"を"Y"にすれば、製品"Y"の情報を表示します。
製品と数量の一覧を用意
次に、製品と数量のテーブルを作成します。
テーブル名は「製品数量」として下さい。
項目名は［製品］［数量］として下さい。
「部品数量集計」を【名前の定義】に登録
以下の数式を【名前の定義】に登録します。
=LET(
部品表親部品,部品表テーブル[親部品],
部品表子部品,部品表テーブル[子部品],
部品表QTY,部品表テーブル[QTY],
製品数量選別,FILTER(HSTACK(製品数量[製品],製品数量[数量]),ISNA(MATCH(製品数量[製品],部品表親部品,0))=FALSE,""),
集約前部品表,
LAMBDA(行,LET(
製品,INDEX(製品数量選別,行,1),
数量,INDEX(製品数量選別,行,2),
_部品表展開,部品表展開(製品),
末端No,BYROW(_部品表展開,LAMBDA(row,INDEX(row,IFNA(MATCH(0,row,0)-1,COLUMNS(_部品表展開))))),
末端部品,MAP(末端No,LAMBDA(n,IF(INDEX(部品表子部品,n)="",
INDEX(部品表親部品,n) & " 子部品未登録!",INDEX(部品表子部品,n)))),
qtymap,MAP(_部品表展開,LAMBDA(d,IF(d = 0,1,INDEX(部品表QTY,d)))),
QTY累積,BYROW(qtymap,LAMBDA(row,REDUCE(1,row,LAMBDA(qtytotal,qty,qtytotal * qty)))) * 数量,
HSTACK(末端部品,QTY累積)
)
),
loop,LAMBDA(self,i,
IF(i <= ROWS(製品数量選別),
VSTACK(集約前部品表(i),self(self, i + 1)),
""
)
),
集約前,DROP(loop(loop,1),-1),
部品集約,UNIQUE(CHOOSECOLS(集約前,1)),
数量集計,BYROW(部品集約,LAMBDA(p,SUM((CHOOSECOLS(集約前,1) = p) * CHOOSECOLS(集約前,2)))),
IF(COUNT(製品数量選別) = 0,"",
HSTACK(部品集約,数量集計)
)
)
部品表親部品,部品表テーブル[親部品],
部品表子部品,部品表テーブル[子部品],
部品表QTY,部品表テーブル[QTY],
製品数量選別,FILTER(HSTACK(製品数量[製品],製品数量[数量]),ISNA(MATCH(製品数量[製品],部品表親部品,0))=FALSE,""),
集約前部品表,
LAMBDA(行,LET(
製品,INDEX(製品数量選別,行,1),
数量,INDEX(製品数量選別,行,2),
_部品表展開,部品表展開(製品),
末端No,BYROW(_部品表展開,LAMBDA(row,INDEX(row,IFNA(MATCH(0,row,0)-1,COLUMNS(_部品表展開))))),
末端部品,MAP(末端No,LAMBDA(n,IF(INDEX(部品表子部品,n)="",
INDEX(部品表親部品,n) & " 子部品未登録!",INDEX(部品表子部品,n)))),
qtymap,MAP(_部品表展開,LAMBDA(d,IF(d = 0,1,INDEX(部品表QTY,d)))),
QTY累積,BYROW(qtymap,LAMBDA(row,REDUCE(1,row,LAMBDA(qtytotal,qty,qtytotal * qty)))) * 数量,
HSTACK(末端部品,QTY累積)
)
),
loop,LAMBDA(self,i,
IF(i <= ROWS(製品数量選別),
VSTACK(集約前部品表(i),self(self, i + 1)),
""
)
),
集約前,DROP(loop(loop,1),-1),
部品集約,UNIQUE(CHOOSECOLS(集約前,1)),
数量集計,BYROW(部品集約,LAMBDA(p,SUM((CHOOSECOLS(集約前,1) = p) * CHOOSECOLS(集約前,2)))),
IF(COUNT(製品数量選別) = 0,"",
HSTACK(部品集約,数量集計)
)
)
定義の〈名前〉は「部品数量集計」として下さい。
登録の手順は、前述の「部品表展開」を参照下さい。
ここまでで、定義の登録作業は終了です。
「部品数量集計」を確認
どこかのセルに “ =部品数量確認 ”と入力して下さい。
このように表示されれば正しく登録されています。
製品“X”が1個、製品“Y”が2個のとき、各部品の合計は、部品“A”が3個、部品“C”が4個、部品“D”が6個となります。
「部品表テーブル」の親部品に載っていない製品は、「製品数量」にあっても集計しない仕様としています。
部品表展開しない製品も集計の対象にしたい場合は、「部品表テーブル」の親部品と子部品に載せれば集計されます。
例では、展開のない製品"W"を「部品表テーブル」に登録することにより、部品数量集計が行なわれます。
（「部品表テーブル」に"W"を登録しないと、部品数量集計は行われません。）
「部品数量集計」の応用
「製品数量」テーブルにひとつの製品だけを載せると（ QTY=1 ）、その製品のサマリー部品表を確認できます。
数式での使い方の例として、ある部品の数量を、VLOOKUP関数等で取り出すことができます。
「部品数量集計」を、拡張した関数としてシステム中に組み込めば、数式だけで処理をつなぐことが可能です。
異常値の場合の仕様
「部品表テーブル」の部品表を展開する過程で、循環が発生した場合は、エラーとせず、
循環があった親子で展開を打ち切る仕様としています。
例では、「部品表テーブル」に、親－子が A－Ｂ、Ｂ－Ｃ、Ｃ－Ａ というデータがあったとすると、
Ａ－Ｂ－Ｃ－Ａ－B まで展開したところで、Ａ－Ｂ が再び登場したので展開を打ち切り、
集計対象の部品としてＢを返します。（Ａではありません）
「部品表テーブル」の子部品がブランクの場合は、部品数量集計で警告の表示を出すようにしています。
例では、"B"の子部品のひとつがブランクなので、部品数量集計で”B 子部品未登録！”という表示になっています。
（数量の集計は行います。）
Excelファイル（紹介用）をダウンロード
 | 部品表展開紹介 (2024-07-24 ・ 15KB) これまで紹介したテーブル、定義、数式を含んだExcelファイルをご用意いたしました。 とりあえず動作を試してみたい場合は、こちらをダウンロードして下さい。 |
仕様の変更、問い合わせ、ご相談
今回ご紹介した例で、仕様を変えてお使いになりたい場合は、ご相談いただければ検討いたします。
また、部品表展開のシステム作成に関してお問い合わせやご相談がありましたら、ご連絡ください。
なお、本稿で紹介した数式等をお使いの結果生じた損害について、当社は責任を負いません。
（株）ソアラ・サプライ 担当：入沢 電話 (03)3763-4331
