とある海外の合弁企業の業務改善ができないか、思案している最中なのです。
これまで、エクセルを使って、データ数で二千近い部品の表を管理していました。
これぐらいのデータ件数なら、タタミ一枚みたいな巨大なデータに変貌しますね。
英語ではBOMと呼び、Bill Of Materials の略称ですが、これが生産の出発点。
なぜなら、一台の製品を作るのに、必要な部品情報と所要量を網羅しています。
そして、工場の日々の操業を満たす生産資材の手配をするのに欠かせません。
もし、納入業者の納期が一ヶ月なら、生産に間に合うように注文は必要なのです。
だから、生産部門に加えて調達部門でも、非常に重要な情報に間違いありません。
でも、タタミ一枚のようなデータから、必要な情報を取得するのは、ちょっと厄介。
中には部品の単価すら入力されていない項目もあって、単なる”調査中”の表示。
部品のデータとは関係の無い、仕掛品のユニットを示す見出し行すら存在します。
それで、こういった表のリストをデータベースとして、如何に有効に把握できるのか。
それで、ビジュアルベーシックのプログラミングから、有効な手法を考えて来ました。
検索のコマンドも色々とありますが、他方、連想配列というコンセプトに出くわします。
まあ、数値以外のデータ型、文字列型なども使用できる配列だと、説明されていました。
ですが、話がちんぷんかんぷんなので、ならばとネットでサンプルプログラムを探します。
利用者の間口が広いVBAですから、初心者も仕事上、組込んでみる人が大勢おります。
ただ、使いやすい言語とはいえ、プログラム言語には違いなく、悩み出す人も多いのだ。
これに救いの手を差し伸べて、解決策を提供してくれるサイトも数多くあり、実に便利。
大抵は、プログラミング会社やPCスクールのPRだったりしますが、参考にはなるもの。
そして、紹介された連想配列ですが、VBAが持つ機能と他の言語とは違う気もします。
大抵のプログラミング言語には実装されており、優れものには違いないはずですがね。
結論として、有用なサンプルコードは一つしか見つからなくて、後は教科書レベル。
実践的に、ほぼ使え無いんじゃないかと思ったりして、ならば自分が作ってみよう。
というわけで、サンプルコードを書き出しておきますので、良ければ参考にしてみてね。
実際に試行錯誤で改造BOMに組んで見ましたが、キーのみの構成してデータは不要かな。
わざわざ取得しなくとも、値には、ゼロから始まるインデックスが、付いているようです。
次に、複数セルの値を結合させたものを、この配列に検索データとして、組込めます。
先ず、二つの似たようなデータテーブルがあったとして、それを比較しようとします。
この条件として、複数セルの値で必ず一致したものを、選び出すとする分けです。
例えば、関数のFindなどは、選び出せる条件値が一つだったりしますが不便です。
もし、部品表なら、一般呼称、コード、パーツ型式の三つが合致する検索が必要。
しかも、二千件近いデータから抽出作業を繰り返しますので、時間を短縮したい。
このために活用しようとしましたが、十分に理解できたとは言いがたく道半ばです。
なぜなら、プログラミング過程で、For～Next文によるキーが組込めませんでした。
Debug.Printで、この配列の作成結果を見ましたが、何が悪いのか未だ分からない。
これには、何か理由があるはずなのですが、もし理解している優秀な方がいらしゃったら、ぜひご教示いただけないものかと、思案して来た自分がいるのでした。
＜プログラミングサンプル＞
 |
| 比較される表 |
 |
| 検索に使うリストになる表 |
 |
| 比較結果で、データの存在を判定している |
Private Sub Button1_Click()
'ブック２にあるボタン
Dim D As Object
Dim bk1 As Workbook
Dim bk2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
'==動作条件==
''Book2、Book1 がともにOPEN中と仮定
' 参照元Book名＝"Book1" Sheet名="Sheet1"、配列にないデータチェック
' 参照元Book名＝"Book2" Sheet名="Sheet1"、連想配列の作成シート
'Book1のSheet1 の見出し行除くデータ開始が2行目と仮定
'Book2のSheet2 の見出し行除くデータ開始が2行目と仮定
'
Dim r As Long
Dim s As Variant
Dim L As Long
Set bk1 = Workbooks("Book1.xlsx")
Set sh1 = bk1.Sheets("Sheet1")
Set bk2 = Workbooks("Book2.xlsm")
Set sh2 = bk2.Sheets("Sheet1")
Set D = CreateObject("Scripting.Dictionary")
With sh2
r = .Cells(.Cells.Rows.Count, "B").End(xlUp).Row 'データの最終行取得
For L = 2 To r ’データ開始行は２
s = .Cells(L, 2) & .Cells(L, 3) & .Cells(L, 4)
'B列 C列 D列 でキーを作成
D.Item(s) = s
'行データの繰り返し実行で辞書の作成
Debug.Print s
'VBエディターにあるイミディエートウインドウで作成結果の確認
’このデバッグは削除OK
Next
End With
With sh1
r = .Cells(.Cells.Rows.Count, "A").End(xlUp).Row 'データの最終行取得
For L = 2 To r
s = .Cells(L, 2) & .Cells(L, 3) & .Cells(L, 4)
'A列 B列 C列 でキーを作成
If D.Exists(s) = True Then
'行データの繰り返し実行で辞書に存在するか
.Cells(L, 6).Value = "Yes"
.Cells(L, 1).Interior.Color = RGB(0, 0, 255)
'一般呼称、コード、パーツ型式が存在すれば
'セルに配色（青）・セルにYesの表示をする
ElseIf Not D.Exists(s) Then
'行データの繰り返し実行で辞書に存在しない
.Cells(L, 6).Value = "No"
.Cells(L, 1).Interior.Color = RGB(255, 0, 0)
'一般呼称、コード、パーツ型式が存在しない
'セルに配色(赤）・セルにNoの表示をするEnd If
Next
End With
Set D = Nothing
Set bk1 = Nothing
Set bk2 = Nothing
Set sh1 = Nothing
Set sh2 = Nothing
'セット変数のメモリー領域を開放
End Sub
いいねと思ったら、二つポチっとね！
※一部、キーとデータの記述に誤りがあり、訂正しました。もう少し勉強が必要なのだ。
0 件のコメント:
コメントを投稿