| |
2つの表（リスト）を突き合わせる方法について有識者、
経験者の方のご意見をきかせてください。
１．業務要件
(1)建設プロジェク(＝案件と呼ぶ。)の案件名、担当社、売上、利益
などを１行にまとめて月次でメンテしています
(2)大元のマスタリストがあり、それを月に一回、10人の担当者に配布。
担当者は自分の担当案件の追加や、案件の売上や利益の変化を、配布
された表に記載して返信。
(3)各担当から返信されたリストを大元のマスタに反映させています
２．担当者が案件情報をリストに反映する際の記載ルール
▼(更新)：
リストの各行に対し変更ががある場合、行の1列目に▼印をつける。
削除は行削除ではなく、案件の進捗を示すセルを用意して、
失注として表す。リストの行の削除は考慮不要。
★(挿入)：
リストに対し、案件＝行を追加する場合1列目に★印をつけて、行
ごと追加する
３．現状のマクロの仕様
1)マクロ本体のボタンを押す。
2)ダイアログが開いて、担当者の作成したリストのブックを指定する。
3)ダイアログが開いて、マスタのリストのブックを指定する
4)担当者のリストのシートを読み込み Find 文で１列目のセルを順に
読込む
5)1列目に▼を見つけたら、プロジェクトの区分、案件名、担当者を複合キー
として、マスタのリストからキーが合致する行を探す
6)1列目が★印の場合、その行の前の行を見る。
前行の1列目のセルが空白か▼だったら、前行のキーで、マスタリストを検索
し、挿入先の行を特定しその後ろに該当行をマスタリストのに挿入する。
7)キーがマッチする行や挿入先の行が風名などの場合は、マスタリストの一番後
ろにその行を挿入し、1列目にエラー理由を書き込む。
４．問題
上の方法は、各担当者が作成する修正元リストのキーの一部の、
プロジェクト区分、案件名、担当者名 が変わるとマスタリストと突合わせがで
きません。
そこで次善の策として、キー項目を修正する場合は、修正元リストの１列目に▽
印を書き
同じ行の使われていない備考欄に、変更前のキーの項目を残しておき、マスター
のリスト
と突き合わで使っています。
この方法は、
・各担当者にとって ▼、★、▽の場合は、備考欄に元のキーを残す必要があるな
ど記載
ルールが面倒らしく▼や★の取り違えや記載漏れ、▽の場合の備考欄の記載漏れ
が発生します。
・またキー項目を文字列としているため、変更元とマスタ側で文字の全角、半角
の違い等の場合も不一致が発せしやすいルールとなっています。
５．改善案
1.マスタ側で大本のマスタリスト作成時、従来のリストの外の右端余白に作業列
を追加
１列目 そのリストを作成した日時のミリ秒単位でタイムスタンプを格納
２列目 記号１文字 挿入Ｉ(最初はすべてＩ) or 変更Ｃ
＋リストの各行を識別するための項番（シーケンシャル）を記載
３列目 行の修正箇所
例 mm/dd/mm/ss△I,1
2.各担当者は、リストの行に変更を加えた場合のwork_sheet_Changeイベントプロシジャ内
で以下を記載
Private Sub WorkShhet_Change(ByVal Target As Range)
If Not Application.Intersect(Target,Range(担当者リストの全領域）
’ここでTargetでを見てどこが参照されたか判定し
’タイムスタンプを作業列１、修正範囲を作業列３に書く
End Sub
a) 変更
・1列目に ▼
・作業列１ タイムスタンプ
・作業列２ Ｃ＋項番、
・作業列３ 修正箇所のセル範囲
例
１列目 作業列１ 作業列２ 作業列３
▼ mm/dd/mm/ss C,10 $B$10,$C$10:$E$10,$F$10, $G$10 ...サイズ可変
b) 挿入 自作のイベントを定義し、タイマーで監視し、イベントプロシージャで処理をする
・リストの１列目 ★格納
・作業列１ I＋項番
2行追加の例
１列目 作業列１ 作業列２
★・・・ mm/dd/mm/ss I,10 ・・・元の行、挿入時のターゲット
★・・・ mm/dd/mm/ss I,10-1 ・・・挿入行１
★・・・ mm/dd/mm/ss I,10-2 ・・・挿入行２
マスタ側から見て、担当者１、担当者２の挿入行が重なった時は以下。
１列目 作業列１ 作業列２
★・・・ 03/22/10/60 I,10
★・・・ 03/23/12/12 I,10-1 ・・・ 担当者１による挿入
★・・・ 03/23/50/13 I,10-1-1 ・・・ 担当者２による挿入
★・・・ 03/23/50/13 I,10-1-2 ・・・ 担当者２による挿入
★・・・ 03/23/12/12 I,10-2 ・・・ 担当者１による挿入
行挿入時ののイベントプロシジャの例は以下。
「井川はるき VBA裏ワザ技大辞典」Sample31_1」のコード参照
Private Sub myRowsInsertEventClass_RowsInsert(Cancel As Boolean)
Cancel = MsgBox("行が挿入されました。" & vbCrLf _
& "キャンセルしますか？", vbInformation Or vbYesNo) = vbYes
’★
End Sub
★の部分に 担当者リストに行挿入がされた場合に
1列目へ★記載や、作業列１、作業列２ 作成の処理を書く
3.更新行処理 (1列目▼）
作業列１、２を見る
作業列１のタイムスタンプがマスタのタイムスタンプより新しい かつ
作業列２の先頭文字がＣ
作業列２の後半の、修正行の項番をキーとしてマスタのリストを検索し
作業列３のセル範囲を修正する
4.挿入行処理 (1列目★)
作業列１のタイムスタンプがマスタのタイムスタンプより新しい かつ
作業列２の先頭文字が Iの時 作業列２の, から枝番までの数字をキー
にして、マスター側のリストを検索し、見つかったらそのうしろに枝番の
ついた行を枝番の順序で挿入する
注 井川はるき Excel VBA裏ワザ大辞典の TIPS 31 を参考にする
添付 Sample031.xls では 行を挿入した時 キャンセルしますかという
ウィンドウを表示させている
５．質問
Q１ 担当者リストには、イベントハンドラ処理を入れることによって悪影響
を少なくする方法について教えてください
例えばESCキーなどが押されマクロの実行＝イベントの監視が行われなくなる
だけでなく、担当者にとって意味不明のメッセージが出るなどを想定。
こういった場合でもエラーが発生しました、ブックを閉じて再度開きなおして
ください等のメッセージを出すか、どこかで イベント監視の機能を動かないように
するApplication.EnableEvents = False を発行するよにしておき
従来の処理を正常に実行できるようにしておきたいです
どのような方法があるでしょうか
Q２ Worksheet_Changeイベントは行の挿入時にも発生するようで
自作するRowsInsertイベントと処理が重複して不具合がおきないか
どのような方法があるでしょうか？
よろしくお願い致します。
以上
|
|
かず
- 16/3/26(土) 11:09 -
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
≪
