2010年07月14日

VBAドキュメント『日付の前後を判定して、○×ほかをつける処理』

 こんにちは、猫耳DMことVOCALOIDな界隈では絵師の切身魚です。
 本日はちょっとプログラマっぽい記事を書いてみました。タイトル通り、『日付の前後を判定して、○×ほかをつける処理』
です。ExcelVBAの基本的な文法を理解しており、マクロでは作りにくいこまやかな条件分岐や判定後の処理を、自動的にプログラムに行わせたい人に向けて書いております。

背景情報
・担当者から送られてくるシステムAマスタの会員情報は、本来はシステムA用の情報である。このシステムAでのマスタは、「会員一人につき1つのデータ」という通年度の管理はせず、転居等の理由で会員証番号が変更された場合、変更後の番号で別個のデータを作って管理する。
・このため、システムAからコンバートして得られるシステムBの会員マスタには、重複扱いの個人データが登録されることが多々ある。
・本来は、これらの重複データは『喪失日』『取得日』できちんとリンクされ、「同一個人の通年履歴」として保持されねばならない。
・このため、システム管理会社にて重複データ抽出してもらったのが「元データ」である。

 私の担当部署では、この「元データ」をシステムBマスタにて扱える「同一個人の通年履歴」として保持するための処理を行いました。抽出した重複データは、本来ならこのままシステムA担当者に渡してチェックしてもらうものです。
 ですが、件数が結構な数ありました。簡単に判断がつかないデータだけをシステムA担当者に渡し、資格取得/資格喪失の相関関係を調べてもらう、という風に負担軽減する必要があります。一方、簡単に判断がつくものは、プログラム処理で自動化したいものです。

1.第1目的はシステムA担当者の負担軽減
2.1.のために抽出データのさらなる絞込みを行う
3.現時点の抽出データは、O列の取得日で「年月日の古いほうが上、新しいほうが下」の行にある。このことが意味するのは「上に古い日付、下が新しい日付なら、下にきちんと日付あればそれが『最新データとしてシステムBマスタに残すべき情報』なのでは?」ということ。
4.3.の場合は上の行のC列『判定』に『×』を、下の行の『判定』に『〇』を入れたい。
4.ただし日付が確認できないものである場合、システムA担当者に判断してもらう必要があるため、判定の欄には『-』または『空白のまま』にしたい。

要求機能
・現時点の抽出データは、同一人のデータの中で見ると、O列の取得日で「年月日の古いほうが上、新しいほうが下」の行にある。このことが意味するのは「上に古い日付、下が新しい日付なら、下にきちんと日付あればそれが『最新データとしてシステムBマスタに残すべき情報』なのでは?」ということ。
・上の行のC列『判定』に『×』を、下の行の『判定』に『〇』を入れたい。
・ただし日付が確認できないものである場合、システムA担当者に判断してもらう必要があるため、判定の欄には『-』または『空白のまま』にしたい。
・特に『旧・喪失年月日』が入っていない場合、「新」のどちらに値が入っていても、システムA担当者に確認が必要。「資格喪失年月日」がいちばん重要度の高いデータであって「取得年月日」は重要度が低いため、「資格喪失年月日」昇順でソートしている。「取得年月日」が複数データ内で前後しても気にしないでよい。

ケース1:旧喪失年月日と新取得年月日が同一日付
 旧 ×
 新 〇

ケース2:旧喪失年月日より、新取得年月日が過去の日付
 この場合、『新喪失年月日』の有無で処理が分岐する。
 ケース2−1:新喪失年月日あり。新取得年月日あり、新喪失年月日は旧喪失年月日より後の日付
        旧 ×
        新 〇
 ケース2−2:新喪失年月日あり。新取得年月日あり、新喪失年月日は旧喪失年月日より過去の日付
        旧 -
        新 -
 ケース2−3:新喪失年月日なし。
        旧 -
        新 -
 
ケース3:旧喪失年月日なし、新取得年月日あり、新喪失年月日なし
        旧 -
        新 -

ケース4:旧喪失年月日なし、新取得年月日あり、新喪失年月日あり
        旧 -
        新 -

ケース5:旧喪失年月日あり、新取得年月日なし、新喪失年月日あり。新喪失年月日は旧喪失年月日より後の日付
        旧 -
        新 -


 上記のように、システムA担当者にて調査や判断を要するケースはC列『判定』に"-"または"(空白)"となるようにしたい。
 
コーディング前の設計
 まず、ExcelデータはA列に「同一人物番号」が割り振られている。同一人物で重複データ件数が4-5行に及ぶこともありえなくは無い。が、12件以上になることはまずありえない。
 同一人物の重複データは可変件数。これをカウントする(件数を数える)必要がある。
 カウントする際はfor〜next文やDo〜Loop文での記述が考えられる。しかし、表全体のデータも、上から順番にfor〜next文やDo〜Loop文で処理したい。
 そこで、まず『同一人物の可変件数をカウントする』用に一次元配列i(2)を使用する。
 別途、『上からの行数をカウントする』用に、変数jを使用する。
 
 次に、データベースとまではいかないが、『取得年月日』と『喪失年月日』を区別をつけて入れておける『仕分け箱』役の変数を宣言する。これは二次元配列difDAT(10,1)を使用する。
 二次元配列の読み方: 変数名(要素インデックス番号,次元インデックス番号)
 特別な指定が無い限り、要素・次元共にインデックス番号は『0』からスタートする。
 今回は特別な指定をしていない。二次元配列difDAT(10,1)は、『要素数は0から10の11個、次元数は0と1の2個』の入れ物を持つ。次元インデックス番号0は『取得年月日』用、次元インデックス番号1は『喪失年月日』用とする。要素インデックス番号は昇順に、読み込み順で日付を入れていく。

DateDiff関数の使いこなし
 Excel VBAに用意されたDateDiff関数にはバグがあるが、今回の『二つの日付の間は何日はなれているか』比較においては関係ない。無視する。
 使い方:x = DateDiff("d",過去の日付,最近の日付)
 xは比較した際の日数差が入るlong型の変数とする。
 x = 0 同一日付
 x > 0 最近の日付が、過去の日付よりx日後である
 x < 0 最近の日付として与えた日付は、過去の日付として与えた日付より『過去』のものである。⇒場合によっては他の判定方法へ。
 今回のDateDiff関数には、引数"d"を与えている。意味は『日付同士の間隔を日数(day)で表せ』である。他にも引数"m"(月単位)"s"(秒単位)などがある。
 
コード解説
 General Declarations領域にOption Explicit(オプション指定はいつも明白に書くこと。スペルミスが無いように。こうしておかないとデバッグしにくいから。)と記入します。
 Microsoftの解説によると、こうしてOption Explicitと指定しておくと、この行以降から
(ここから引用)
変数は、Dim、Private、Public、ReDim などの各ステートメントで明示的に(Explicit)宣言する必要があります。宣言されていない変数名を使うと、エラーが発生します
  Option Explicit ステートメントを利用することにより、既存の変数名の入力ミスや、変数の適用範囲 (スコープ) がわかりにくいコード内で変数名の競合を避けることができます。
 (引用ここまで)
 という事になりますので、大変便利です。また、うっかりスペルミスした変数名が、『自動的に新たな変数』と認識されなくなりますので、適切なコーディングや効率よいデバッグに役立ちます。
 では、コマンドボタンの中身を見ていきましょう。
 まず使用する変数を宣言します。
 myWSはCommandBottun1が置かれているシートですので、ActiveSheetをそのまま用いることにします。
 i(2)は『同一人物の可変件数をカウントする』用に宣言する一次元配列。j,kもカウント用です。二次元配列difDAT(10,1)については既に書いた通りです。
 まず、シート上のデータは3行目からスタートしていますので、j=3とします。汎用性のあるシステムを目指すなら、まず設定ファイルから目次となる文言を読込みし、その文言でファイル内検索をかけて『何行目か』をきちんと取得するべきですが、ここでは省略します。情報管理システムの作ったデータですので、いつも同じ行順番だからです。
 また、このプロシージャ内ではmyWS以外のシートを参照したりしないため、最初にWith ステートメントで『この行以降からEnd Withステートメント行までの、シート関連指定は、省略した場合全てmyWSについてのものである』と書きます。
 次にプロシージャ最下行に、『End With』を書きます。書き忘れエラーを気にするストレス回避のため、常に「セットになるステートメントを記述しておく」癖をつけましょう。
 また、End Withの下にはSet myWS Nothingで、myWSオブジェクトの中身をメモリから消して、メモリを開放しておきます。この辺はプログラマの個人的好みです。
 ではDo〜Loopの中身を見ていきましょう。
 
 まず『同一人物の可変件数の重複データ』は何件あるか?をカウントします。このために一次元配列i(2)を設定しました。i(2)の中身は次の通りです。
 i(0) 件数
 i(1) チェックしている行
 i(2) 同一人物かどうかを判定するための値(シート上ではA列の値)格納場所
 
 最初はErase ステートメントで、i(2)の要素を全て初期化します。
 少し余談ですが、制御や宣言などを行なうために言語仕様にあらかじめ組み込まれている命令語をステートメントと呼びます。
 英語で言うところの『命令文の動詞』に当たるものです。英語文法では、命令文は『動詞+目的語』で表現しますね。そこで、ステートメントでも「Erase」の後に、対象となる変数iを指定しているのです。
 Do〜Loopで、一行ずつ見ていきます。当然ながら、Doステートメントを書いたとき、次にLoopを書いてきちんと完成させておき、それから中身を書くようにしましょう。
 まず、jは動かさずに、i(1)で行数をとり、内容をチェックして、同一なら件数カウンタi(0)を+1し、異なる内容ならDoを抜ける。それだけの簡単なチェックです。
 この後のIf文では、二次元配列difDAT(10,1)が処理できない件数、11件より多い場合のエラー回避をします。
 実運用データでは、5件以上になることなどありえないといわれていますが、年度内に引越し回数が10回まではありえない話ではないと想定。引越し10回目で11件の書き換えデータが作られますので、さすがにそれを上回る場合だけは『システムA担当者に人力チェックしてもらう』ことにします。
 そこで、件数カウンタのi(1)が11より多かった場合、メッセージボックスでフィードバックを返します。何か例外処理が発生した場合、なぜその処理をしたのか、理由を明らかにせねばなりません。特に人間に何らかの処理をしてもらいたい場合は、必ず看過できない形でフィードバックを返しておきます。
 その後、プログラム処理をスキップします。これがGoToステートメントです。ステートメントは命令文ですから、目的語をとります。GoToの行き先はSkipというラベルをつけた行です。
 Skip先は、本来のDoループ処理の終わった後の行に設定してあります。ここでは読み込み行jにi(0)、つまり『同一人物の件数』値を足します。
 では、スキップしない場合の通常処理を見ていきましょう。
 
 最初はErase ステートメントで、二次元配列difDAT(10,1)の要素を全て初期化します。
 次に、カウンタ用変数kをFor〜Nextの文でまわします。Forを書いたら、すぐに『Next k』を書いて、対になるステートメントを完成させます。この習慣は絶対に身につけましょう。
 kの上限は、0スタートしているため件数i(0)の値-1をとります。
「1からスタートすれば、上限をi(0)で指定できるのに」
 とお思いになるかもしれませんが、そのようにしない理由があります。
 kはただの件数としてだけでなく、配列difDATの要素インデックス番号を指定するのにも用いているためです。
 また、行カウントjにkを加算して、値を取得したいセルの『行番号』を指定するのにも用いています。このため、kは0からスタートしたほうが都合がよい。しかも2ヶ所において、です。カウンタ上限指定は一箇所だけですから、2対1の多数決原理により、「kは0からスタート」となりました。
 データの処理される流れに沿っていない、無理やり当てはめたような変数の利用は、コードの可読性を低下させ、ミス発見を妨げるので避けるべきです。
 一方で、「違う物事を指すから」といってやたらと変数宣言を増やしては、プロシージャの変数宣言が冗長になり、これまたデバッグがやりにくくなります。同じ処理の中で転用できる、あるいは値を援用できるカウンタは、できるだけ共有しながらコーディングすることで、スッキリしたプログラムを書けます。
 どの程度の利用を『処理の流れに沿っている』『無理やり当てはめすぎている』と判断するのかは、個々のシステム設計者の好みによるところも大きいものです。一般論として、大規模システムになればなるほど、開発に関わる作業人数も増えますので、システム設計時の機能要件定義はよくよく吟味されねばなりません。機能要件定義が変数宣言の中身を左右し、作業に関わる人の効率を左右するのですから。
 今回のkのような場合は、1プロシージャ内での援用とみなして再利用しています。
 再利用の際の最小値の決め方、最大値の決め方は上記の通り。「より多く使用される機会に迎合する」です。「使用される機会」があまりに多様で、整合性のある使い方が思いつかないときは、変数の宣言を見直し、異なる使い方のために他の変数を宣言したり、多次元配列にしたりといった可能性を検討することにしています。
 
 三番目に、二次元配列difDAT(10,1)の要素インデックスk番目に、『取得年月日』O列と『喪失年月日』P列のセルの値を取得します。先に述べたとおり、カウンタkを用いてインデックス番号を指定しています。
 そして、『kが1以上になったら』つまり読み込み2件目になったら、年月日の比較を開始します。『k=0』は読み込み一件目ですから、年月日の比較はできませんので、「kが1以上になった時」かどうかをIf文で条件判定して、処理を開始するのです。
 ここから要求機能の項で想定した複数ケースを、実際の値に当てはめていきます。
 まず、一番最初に判定するのは、『新・取得年月日が記載されているかどうか』と、『旧・喪失年月日が記載されているかどうか』です。両方がそろって初めて、日付の比較処理に入れます。
 『DateDiff関数の使いこなし』で述べた方法で、i(2)を再利用しています。
 余談ながら、このi(2)のような再利用は、他の人が読んで、共同作業するプログラムでは可能な限り避けた方がよいでしょう。「処理の流れを誤解しやすい」ためです。
「あれっ、さっきの処理で使っていたi(2)がまた出てきたぞ?これは何の意味だったかな」
と誤解しそうになりますからね。リソースは十分にあるのでほかの変数でも代替可能なのですから、よいプログラムを組もうとする人はこういうことをしないよう心がけてください。
 それはさておき、i(2)での判定内容を見ていきましょう。
 まずi(2)が0だったら、同一日に旧番号を喪失し、新番号を取得した扱いになっているので、上の行にある「日付の古いデータ」はC列『判定』欄に「×」を入れて、『これは履歴用データ扱いにしてください』と示します。そして下の行にある「日付の新しいデータ」は、C列『判定』欄に「○」を入れて、『これは最新の現状データ扱いにしてください』と示します。
 同様に、i(2)が0より大きくても問題ありません。『新・取得年月日が旧・喪失年月日より後の日付である』ためです。同一日のときと同様に処理しています。
 次が、i(2)が0より小さく、『新・取得年月日が旧・喪失年月日より過去の日付である』場合です。もし新・取得年月日が旧・喪失年月日で記載されていても、ケース2−1のとおり。
 新喪失年月日が旧喪失年月日より後の日付なら、上の行にある「日付の古いデータ」はC列『判定』欄に「×」を入れて、『これは履歴用データ扱いにしてください』と示します。そして下の行にある「日付の新しいデータ」は、C列『判定』欄に「○」を入れて、『これは最新の現状データ扱いにしてください』と示します。喪失年月日がある場合、取得年月日の前後より喪失年月日の前後関係から判定するためです。
 一方、新喪失年月日が旧喪失年月日より「過去の日付」なら、これはプログラムが判断してはいけないケース2−2です。上の行、下の行ともC列『判定』欄に「-」を入れて、システムA担当者に確認&判断してもらいます。
 さらに、新喪失日が記載されていない場合はどうでしょうか。この場合は『ケース2−3:新喪失年月日なし』に該当し、判定ができません。上の行、下の行ともC列『判定』欄に「-」を入れて、システムA担当者に確認&判断してもらいます。
 最後に、『旧・喪失年月日』だけあり、『新・取得年月日』が無い場合、ケース5:としてこれも判定ができないものとします。上の行、下の行ともC列『判定』欄に「-」を入れて、システムA担当者に確認&判断してもらいます。
 同様に、『旧・喪失年月日』が無い場合、『新・喪失年月日』の有無にかかわらず、ケース3及び4として判定ができないものとします。上の行、下の行ともC列『判定』欄に「-」を入れて、システムA担当者に確認&判断してもらいます。
 この2つは要求機能の最後の項目「特に『旧・喪失年月日』が入っていない場合、「新」のどちらに値が入っていても、システムA担当者に確認が必要」によるものです。

 これらのケースに当てはまらないケースは、システムA担当者に確認が必要なケース(プログラムで判定してはいけないケース)ですので、何も処理せず、判定用のC列に『記載しない(空白)』としています。

 末尾のコメントは、コーディングテスト中に値の取得を確認していた時のものです。将来のデバッグや改修時に必要になるかもしれませんので、コメントアウトで残してあります。

 ここで「なぜ、一度に二行ずつ判定しているのか?」について少し説明いたします。
 今回の元データは、喪失日の時系列で昇順に並んでいます。
 二行ずつ判定していってなにも問題ありません。比較エラーは適切に返されます。
 もし、個人番号順元データの中身が、何の規則もなくランダムな順番であれば、これはまた別の処理が必要になります。たとえば、Excelシートをデータベーステーブルのように読み込み、SQLを発行して何らかの規則順にソートし直したデータを、日付で判定する、といった形になるでしょう。
 元になるデータの分析結果によって、機能要件/非機能要件の定義は違ってくることもあるのです。最適なシステムはどこまでをシステム化すれば良いか?を常に念頭において設計し、コードを書き、テストしましょう。

 Skip:ラベルを付けた次の行で、行番号を示すjに、同一人物のデータ件数を加算します。
 次の行で、空白行が来たら処理を抜け、そうでなければA列j行めのセルをアクティブにして『処理中のデータが画面に表示中』にするようにしてあります。
 これで今回のプログラムは終わりです。

 特別に難しいステートメントや技法は用いておりません。中小企業や市町村単位の「小さなデータ取り扱い」の世界ですので、基礎的なプログラム設計の考え方とコーディングの実践が重要になってきます。
 大規模システムでは取りこぼしてしまうデータ、たとえば今回のような「AシステムからBシステムに流用するデータが、Bシステムでは通年度管理のため重複データになってしまう」場合のフォローなどがそうです。私の担当した件数は201件だけでしたが、これを担当者一人で、一件ずつ日付を調べていくと大変な人的・時間的コストの無駄になります。
 ドキュメンテーションは一日仕事でしたが、設計、コーディング、テストは半日で終わりました。
 今後同様のデータを処理する場合、担当者はこのドキュメントを参考に、自分でコーディングを工夫することもできます。もっとも簡単な処理方法は、シートのマクロ/VBAを有効にして、コードをコピー&ペーストすることです。毎回毎回「担当者一人で、一件ずつ日付を調べていく」ことに比べれば、人的・時間的コストは大きく軽減されるでしょう。
 「パソコンをさわれる」のではなく、「システムを設計して、業務の効率化に貢献できる」ことで評価されるようになりましょう。この記事をお読みいただいた皆様のご発展を心からお祈り申し上げます。
 ご自分の関わるプロジェクトで応用してみたい方は、どうぞお試しになってみてください。この程度のプログラム、自己責任でコピー&ペーストしてお使いになってくださって結構です。
 ただ著作権は『クリエイティブコモンズ』にのっとり、CC−BY−NCといたします。切身魚の名を記名しておいて下さい。この記事及びコードは非商用利用が可能です。記事及びコードを『出版物』として利益を得ることは許されません。コピペしたコードを、ご自分の商用プログラムに、自己責任で組み込むぶんにはご自由にどうぞ。
 お役に立った折は、おひねり程度にAmazonギフトコード(最低限度額)の1つもを送りつけてくださると大層喜ばしいことでございますが、そこいら辺はご随意にどうぞ。(押し付け先は『さんeよんylqあっとまーくgmailどっとcom』を半角英数変換です)

元になったEXCELファイル(必ずウイルススキャンを行い、自己責任にてご利用ください)
[権利情報]
このExcelファイルに含まれるコード及び文章は、著作権を主張しません。自己責任においてお使いください。DateDiff_Test.xlsm
このblog記事における文章は著作権を主張します。ただし、日本版クリエイティブ・コモンズにのっとり、記名-改変不可-非営利有償可能・営利不可といたします。
・記名 著作権者の切身魚(英語使用前提の場ではKirimisakana)を記載してください。省略しないで下さい。元記事であるこの記事へのリンクを貼る、URLを印字してください。
・改変不可 誤字脱字を、誰が修正したのか分かるように訂正する、程度の改変はOKです。事前許可不要です。それ以外の改変には事前許可を得てください。
・非営利有償可能・営利不可 非営利活動、個人blogでの紹介や教育現場での複製、同人レベルの出版物での出版、月収の1/20以下程度の対価での頒布は事前許可不要でOKとします。一方商用メディアでの紹介、営利出版での利用は事前許可を確実に得てから行ってください。
上記3条件をお守りいただき、お互い気持ちよく活動を続けられますように。
posted by 切身魚(Kirimisakana) at 19:42| Comment(0) | プログラミング | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
コメントを書く
お名前: [必須入力]

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。