Excel に Filter関数って、あるよね・・・ＢＯＭ展開で使ってみる。

2022年10月15日 18:33

にわかに、思いついた。

2022年10月15日（土曜日）お隣さんの家の工事の音で目が覚めて。「今日は天気いいなぁ・・・」って思って、顔を洗って、よっぱおじさんの頭に、アイディアが降りてきた。
今のExcelならBOM展開も簡単に実装できそうだッ！
昔のExcelのバージョンでBOM展開は無理ゲーでして・・・
出来たとしても、Excelの機能だけでは不可能で、ADOなどのSQLなどを使わないとできない代物でした。
でも、「スピル関数って範囲を返すだよなぁ、ん？　VBAでFilter関数が使えれば、BOM展開できそう！Excelだけで、できそうだ。」と思ったのです。
思い立ったら行動です。ＰＣ起動＋Ｅｘｃｅｌ起動。

そもそも VBA で使えるのか？

WorksheetFunction で、Filter って、出るの？
先ずは、そこからでした（笑）
あっ、出た出た！使えそう。

軽いテストを試してみる。

うまいこと、配列を返してくれるか、テスト。
サンプルデータを自作アドインでデータをサクッて作って、やってみました。
しかし・・・、そんなに、Excel VBA はあまくない・・・

初めのエラーは、コンパイルエラー💦
文法が間違っていますとのこと。
最初から、容赦ないです。Excel VBA さん・・・
おそらく、第二引数だなぁって思いました。
第二引数も配列になるので、この指定では、エラーになりそうです。
ま、ちょっとした工夫で、コンパイルエラーは、回避できました。
[ ] を使うだけでしたね💦

Excel VBA の [ ～ ] ？

初めて、[ ] を見た人もいるかと思いまして。
これは、Excel VBA の関数である Evaluate 関数の簡易バージョンの記号です。 [ ～ ] の～の数式を評価して結果を返してくれる便利な記号です。

今回の場合、Filter関数の第二引数もTrueとFalseの配列を指定しなきゃだから、カンでこれを使ったわけです。

いざ実行

おぉぉぉぉぉ！　ちゃんと、配列を返してきましたね！これは使えると確信しました！
次は、抽出条件を変数にしてみて、実行してみます。

さて、実行してみます。

やりました！
抽出条件に変数を使う場合、[ ] の記号は使えなく、正規の Evaluate 関数を使って、思うような結果を返してくれました。

次は、BOMのマスターを作る。

サンプルデータは手作り

一番シンプルなBOMのデータ構造は、こんな感じです。

ま、これは構成内容がフラットで単純ですので、人間でも読みやすいです。
しかし、こんなＢＯＭはどうですか？

これが、一般的なＢＯＭ構成です。（実際はもっと複雑です）
親品番と構成品番が入れ子になって、ＢＯＭの構成を表現しています。
機械は、このデータ構造の方が処理しやすいですが、人間様は、読み取るに苦労を強いられます。
便利な世の中になったもんです。設計リストから自動でアップデートできるシステムもあるようです。
しかし、現場はそんなに簡単ではなく、色々な例外が発生するものです。
大概のＢＯＭは、設計リストをゴニョゴニョして、完成するものです。

最終的に登録したＢＯＭが設計リストと合っているかのチェックしたり、視覚的にＢＯＭ構造を見たくなるものです。

でもね、見やすいＢＯＭリストを作るの事が一苦労なんです。

で、今回は、ＢＯＭリストを Excel だけで 作ってみようという試みです。Filter関数が使えたので、おそらく簡単に実装できます。
で、準備したサンプルデータは、こんな感じです。けっこう、データ作るの苦痛でして、この件数で勘弁して下さい。

御託はここまで。いざ実装。

BOMマスターの取得
１次レベルのBOM展開
２分探索木のトラバーサルのアルゴリズムを応用してBOM展開
出力用のワークシートに結果を出力

こんな感じのフローで、BOM展開します。
イメージは下図の様な感じです。
さほど難しいテクニックを使っているわけではありません。

汚い絵で、すいません💦
言葉で表現すると、親品番から構成内容を抽出させます。
抽出された構成品番を、親品番として、再度、構成内容を抽出します。
これを再帰で繰り返す。これだけです。

しかし、難関が・・・（苦労した所）

１次元配列と２次元配列を返す FILTER関数
構成内容が１個と２個以上で、FILTER関数は、次元の違う配列を返すことがわかり、対処に苦労しました。
で、苦肉の策がこちら・・・（恥ずかしい）

Function Let_array_size(array_value As Variant) As Integer
    Dim x As Long
    On Error GoTo arr_Err
    x = UBound(array_value, 2)
    Let_array_size = 2
    On Error GoTo 0
    Exit Function
arr_Err:
    Let_array_size = 1
    On Error GoTo 0
End Function

テーブルの並べ替え
BOMマスターを配列に取り込む前に並べ替えした方がいいかなぁって思い、並べ替えをするコードも苦労しました。
テーブルのソートってオブジェクトなんですかね。
Sortオブジェクトに、ゴニョゴニョする感じですよね。

Sub GetData()
    bomSheet.Range("A2").ListObject.Sort.SortFields.Clear
    bomSheet.Range("A2").ListObject.Sort.SortFields.Add bomSheet.Range("A2").ListObject.HeaderRowRange(1)
    bomSheet.Range("A2").ListObject.Sort.SortFields.Add bomSheet.Range("A2").ListObject.HeaderRowRange(2)
    bomSheet.Range("A2").ListObject.Sort.Header = xlYes
    bomSheet.Range("A2").ListObject.Sort.Apply
    BOM = bomSheet.Range("A2").ListObject.DataBodyRange
End Sub

あとは、特に苦労するポイントありませんでした。
（最も苦労した所は、BOMデータを作る所、２時間くらいかかった💦）

こんな感じな仕上がりです。ご検収下さい。

元データのデータ構造より、人間様が理解できるレベルに展開しました。
デザインセンスがいい人は、もっときれいに仕上げてくれるでしょうね。
おっと、肝心な、ＢＯＭを展開するところのコードを見せていませんでした。

'+-------------------------------------------------------------------------------------------------
'+    BOMを正展開する関数（再帰サブルーチン）
'+      root_item  親品番
'+      root_qty   親品の構成数
'+      ret_value  構成情報
'+-------------------------------------------------------------------------------------------------
Sub bom_expansion(root_item As String, root_qty As Long, ret_value)
    
    Dim i As Long, x As Long
    Dim node_item As String
    Dim node_qty As Long
    Dim array_size As Integer
    
    array_size = Let_array_size(ret_value)  '配列の次元数を調査
    If array_size = 1 Then
        x = 1
    Else
        x = UBound(ret_value, 1)
    End If
    
    For i = 1 To x
        
        resultSheet.Cells(RecordCount + 1, 1) = RecordCount
        resultSheet.Cells(RecordCount + 1, 2) = root_item
        resultSheet.Cells(RecordCount + 1, 3) = Level
        If array_size = 1 Then
            resultSheet.Cells(RecordCount + 1, 4) = ret_value(2): node_item = ret_value(2)
            If P = vbYes Then
                resultSheet.Cells(RecordCount + 1, 5) = ret_value(3) * root_qty: node_qty = ret_value(3) * root_qty
            Else
                resultSheet.Cells(RecordCount + 1, 5) = ret_value(3): node_qty = ret_value(3)
            End If
        Else
            resultSheet.Cells(RecordCount + 1, 4) = ret_value(i, 2): node_item = ret_value(i, 2)
            If P = vbYes Then
                resultSheet.Cells(RecordCount + 1, 5) = ret_value(i, 3) * root_qty: node_qty = ret_value(i, 3) * root_qty
            Else
                resultSheet.Cells(RecordCount + 1, 5) = ret_value(i, 3): node_qty = ret_value(i, 3)
            End If
        End If
        RecordCount = RecordCount + 1   '行数を＋１
        
        Dim node_ret_value As Variant
        Dim Fx As String
        
        'FILTER関数で、構成部品情報を取得する
        Fx = "_BOM[Root]=""" & node_item & """"
        node_ret_value = Application.WorksheetFunction.Filter(bomSheet.Range("_BOM"), Evaluate(Fx), False)
        
        '結果がFalseでなければ、再帰で、さらにBOM展開する
        If Not VarType(node_ret_value) = vbBoolean Then
            Level = Level + 1
            Call bom_expansion(node_item, node_qty, node_ret_value)
            If Level > MaxLevel Then MaxLevel = Level
            Level = Level - 1   '階層レベルの変数をー１
        End If
        
    Next i
    
End Sub

Excelには、ワークシートという素晴らしい出力場所があります。
結果は都度、ワークシートに書き出しています。これで、難しい配列の処理を一つ減らせます。

感想

単純な表展開なら Access より優秀
ADO で取り扱う SQL や Recordset の面倒が無くて良い
もっと膨大なデータでは検証できていないので実用性は未知数
スピル関数は超偉大
配列はプログラミングの基礎である

こんな感じ。
兎に角、Recordset や SQL をまったく使わないで実現できる手軽さは中毒性があります。やりますよ！Filter関数さん！
感覚ではありますが、Access より Excel の方がとても良い感じです！

折角、苦労して作ったサンプルのデータ（BOMのみ）を、ここで公開します。自力で作りたい人向けです。超暇な人は、チャレンジしてみてください。

マクロブックも、TwitterのDMを下されば、提供いたします。
でもね、検証不十分です。
仕事で使って「不具合で大損害だぁー！！！」なんて苦情は一切受けませんので、お仕事では使うことはご遠慮下さい。
よっぱおじさんからのお願いです。

この記事が気に入ったらサポートをしてみませんか？