2010年07月01日

Excelでできる擬似個人情報生成

 こんにちは、切身魚です。本日はちょっとVOCALOIDから離れて、
プログラミング的なことをお話しましょう。
 タイトルどおり、

・件数が一万から数万程度の件数において
・「厳密に重複を許可しない」わけではない(少々の重複は気にしない)
・実在しない個人情報(氏名、郵便番号、住所、マンション名)を作る

 本日はこの3つを主目的とした、簡易な擬似個人情報生成を行うExcelシートの作り方をご紹介します。
 参考用のエクセルファイルは、この記事の最下行にてリンクご案内しております。ご自由にダウンロードして頂き、この記事にある権利情報をお守りいただいた上でご利用ください。

 VBAの基本的文法を理解しており、コードを読んで流れを追うことならできる、という読者や、他のプログラミング言語を習得している人を対象に書いてあります。
 実在しないけれど、実際にありえそうな人名や住所を生成すること、が目的です。
 用途は主に2つ、自己チェック用のデータを現実的なものにすること。そしてよそで提示する用のデータです。
 極めてまれに『実在データと同一』の住所や人名が出来てしまうこともありえますが、主用途からいって実害を及ぼすことはまずありえません。また、良くある人名から抽出したデータが実在人名にかぶる可能性は常に存在します。これが実在人類に何らかの影響を及ぼすとすれば、それは情報の取扱の問題であって、生成プログラムには無関係です。ゆえにこれは無視します。
 同様の理由で、極めてまれに『重複姓名』『重複住所』『重複電話番号』が出来てしまうこともありえますが、無視します。
 理由は、1万や10万単位のなかの1件や2件の重複が、処理の上で問題となるような案件には用いないため、です。同一姓名で同一住所で同一電話番号でも、各データに固有IDを割り振って別個のものとして扱う、などの処理に用いるためです。実際のお仕事上データ処理の世界でも、コンピューターシステムは同姓同名で誕生日も同じ人間をきちんと区別できるよう、IDを割り振って処理していますからね。(逆に申せば、IDを用いないで運用が上手くいく、大人数を管理するシステムなど設計できません。便利さを享受するためには、それなりに手間が必要です。)
 それでは、細かい要求定義に参りましょう。
 
 まず、氏名は『姓・名の間に空白があったらいいな』と言う時もあれば、『姓・名の間に空白は無いほうが良いな』という時もあります。
 このため、設定ファイルを作って用意する……のは大変です。「ファイルの同梱」「読み込み・書き出し」「設定変更の容易性」などを考えると、外部ファイルで持つことの『秘密保護性』メリットより、『作る手間』と『運用時のヒューマンエラー』というデメリットが上回ります。Excel入力はできるけれど、MacroやVBAの組み方は理解していない人や、外部ファイルとのリンク保持の大事さを知らない人もいるためです。結構な数の事務屋さんが、こうした機能の使いこなしをご存知ないのは残念なことです。
 そこで、Excelの同一ファイル内にシート『設定』を作成し、そこのセルに注意書きとともに記載することにしました。外部に持って行って金の貰える仕事にするわけじゃあ、ないんですからね。
 姓名を生成するのは一から作ると大変ですので、同姓同名辞典(全国の同姓同名検索、全国の苗字や名前のランキング)サイトの、順位20000〜299999からデータをもらうことにします。このサイトの表示データをシートに作り直して(ここは手作業でコピペ&整形します)用いることにします。
 
 つぎに、郵便番号は、今回『ハイフン有り』のみ、としました。別途つけたくなったりはずしたくなったりしたら、機能変更を検討します(上記の氏名で行ったのと同様の処理ですから、楽に作れますしね)。今回はハイフン有り固定にします。 ゆうびんホームページの『郵便番号データダウンロード』から、自県のぶんだけデータを貰うことにします。
 住所は「郵便番号データ」から「導出する部分」と、「プログラムが乱数で生成する部分」の2つに分かれます。「郵便番号データ」から「導出する部分」は、「市町村名」と「街区名」です。これは1つのフィールドにまとめてしまうことにします。
 「プログラムが乱数で生成する部分」は、「区・番地のナンバー」と「マンション名・号室ナンバー」の2つがあります。そこでこの2つは2つのフィールドにします。
 「区・番地のナンバー」は、常に「*-*-*」ではリアルでないので、時折「*組」といった記述が混じるようにしたいです。そこで、同じシード、同じ乱数を元にしても、結構違った値が出るように、「1000で割った商」や「500の剰余」で作ることを検討します(細かい仕様はまた別途書きます)。
 「マンション名・号室ナンバー」は、マンションによくありそうな短い名称を乱数で2つ選んで組み合わせ、これまた乱数で適当な号室ナンバーを割り振ることとします。Excelの同一ファイル内にシート『マンション名』を作成し、よくありそうな短い名称は28*28で784通りの名称がでてくるようにしました。ただ、100件の擬似個人情報を作ったら100件全部がマンション名ありだった、というのは『リアルではない』データになりますので、ある程度の頻度(そんなに高くない)で出現するようにします。
 出現する頻度は特別に設定させず、プログラムコード内部で頻度を持つようにします。凝りだせばキリがありませんので、「ある程度リアルなものが作れさえすれば、それ以上のリアルさは必要ない(リアルすぎても困る)」というわけです。
 
 最後に「電話番号」は、市外局番と地域局番までは総務省の『電気通信番号指定状況−固定電話の電話番号9から始まる市外局番』でデータをもらうことができます。残る4桁部分は、1000-9999までの乱数生成でまかなえます。前述の通り、重複は気にしない方向で。
 これで全ての問題が解決かというと、そうでもありません。
 市外局番を得るための「地域名」は、郵便番号簿の市町村名と、電話番号簿の市町村名では異なる記述が見受けられます。これはどうしましょうか。Accessなどのデータベースなら、変換用のテーブルを用意することでしょう。Excelですので、またも同一ファイル内にシート『市町村名変換』を作成し、そこのセルに注意書きとともに記載することにしました。
 このような場合、最もやってはいけないことが、『プログラム内にifで条件判定して、それに併せて随時コードを書き足したり修正したり』です。他の人が触ったとき、見て分からない部分に理解できない処理をしたらいけません。他県でも使いたくなるかもしれないでしょう?

 全体を俯瞰して、『注意書きがあればやりそうにない間違い』、『注意書きを無視したら起きるヒューマンエラー』の対処を考えます。
 心配するだけ無駄なトラブルを「未然に防ぐ」ような「対応」を考えていたら、軽いシステムは作れません。トラブルが起きにくい「インターフェース設計」を行い、それでも人為的なミスでトラブルがでたとき、「こういう理由でエラーが起きた」という「分かりやすいフィードバック」を返すだけで十分です。
 大規模システム(作るのも大人数、使う人も大人数、扱う件数は十万単位どころか億に達する勢い)でなら、話は違ってきます。また、人の生命や健康といった医療系システム、ライフラインに関わる社会基幹システムでも、フェイルセーフの重要性は事務屋の手仕事の比ではありません。
 ただ、今回のように「よく知っていて、心配する必要がないと分かっていること」は捨ておくだけのことです。
 
 このようにして、欲しいデータの形と、それを作るには何を元データとし、どのような処理をさせようか、と大まかに決めました。ここまで形が見えてきたら、ネットのデータを貰い、Excelシートに転記・読み込みさせて、必要な設定シートや変換用のシートを作る作業に入ります。
 
1.参考情報サイトから、ソースになる情報を集める。

 参考情報サイト
電話番号 総務省ホームページ 電気通信番号指定状況−固定電話の電話番号9から始まる市外局番
郵便番号 ゆうびんホームページ 郵便番号データダウンロード
人名 同姓同名辞典(全国の同姓同名検索、全国の苗字や名前のランキング) 20000〜299999

 上記のうち、郵便番号はそのままExcelで読み込めるCSVで提供されているので、そのまま用います。
 電気通信番号指定状況−固定電話の電話番号9から始まる市外局番も、サイトで表示されたExcel形式のデータを、そのままコピペして用います。
 人名だけは、 同姓同名辞典(全国の同姓同名検索、全国の苗字や名前のランキング) 20000〜299999をブラウザで表示させたものを、マウスドラッグでコピー&ペーストして整形します。
 さらに整形を進めていきます。
 整形のやり方は、『この後、ExcelVBAからテーブルとして読み込む』用途を念頭におきます。
 例えば、郵便番号簿なら、地名に『以下に表示の無い場合』などと出てきては困ります。編集メニューの検索から、『以下に表示のない場合』を検索して、文字を消す/架空の地名を入れる、などして変更します。

2.設定情報の検討
 先ほど、Excelの同一ファイル内にシート『設定』を作成すると書きましたが、設定情報にも2種類の設定があります。
 1つは、いつも使う度に変更したい情報。「抽出条件」などは使うたびに変化するものです。今回の例では、「作りたいデータの件数」がそれに当たります。そこで、この数字記入欄は、図のように生成用のボタンがある画面に配置しました。
 もう一つは、一度設定しておけばそうそう変更の必要がない情報。先に述べました姓と名のあいだに空白を入れるかどうか、といった事柄がこれに当たります。
 また、将来的に列の挿入や追加に対応できるよう、列名(アルファベット)も『設定』に残しておきます。
 図のように設定シートを作成し、注意書きラベルも作りました。
 注意書きラベルは、自分以外の人が触るときの対策です。
 下段には、今回使用した参考データのホームページ名をメモしておきます。URLは変更されやすいのでハイパーリンクはせず、Google検索しやすいよう、タイトルを正確にコピー&ペーストします。
 それでは、コマンドボタンの中の処理を見てまいりましょう。
 
3.コマンドボタンの中の処理
 コマンドボタンの中では、VBAで記述していきます。そこでまず、(General)(Declarations)領域(英語の意味の通り、『Sheet1全般についての宣言』です)にこのように書きました。

' 配列の始まりは常に『1』
Option Explicit(オプション指定はいつも明白に書くこと。スペルミスが無いように。こうしておかないとデバッグしにくいから。)
Option Base 1(配列の最初の数字は、いつも1からスタート。基本設定の0スタートは分かりづらいから。)
Private RndNo As Long (各サブプロシージャで一回一回同じような変数を宣言するのは容量の無駄ですので、ランダム数値用の変数はここで宣言。)

 それでは、CommandButton1_Clickの中身を見てまいりましょう。
 最初は変数の宣言です。そんな大量データは扱わないよ、と言いつつも気になってlongやDoubleを用いてしまう小心者です。
 この中でお気をつけ頂きたいのは、sorWS。sorWSは『ソースのあるWorkSheet』の意でつけた変数名です。処理の必要に応じてソース読み込み元を指定するため、プロシージャ内で変更することがあります。
 最初に書くこと:エラーが発生したら、『Err_Exit』と名前を付けた行に飛べ。
 『Err_Exit』と名前を付けた行は、Exit Subの直前に、書く。「エラー番号とエラー内容をメッセージボックスにだす」ことと、『Set 何かオブジェクト = Nothing』と書いてしまいます。気休めですが、「何らかの処理エラーが発生したとき、必ずオブジェクトを開放してから処理を抜ける」ようにしています。システムによる自動開放を信用してないとも申しますね。この辺はコードを書く人の好みレベルのお話しですので、ご随意にどうぞ。
 それから『Err_Exit』行の前に、Exit Subと書きます。こうすれば、正しく処理が済んだ後は、Err_Exitまで処理を進めないで終了できますね。
 
 では処理に参りましょう。
 まずsorWSとして、"設定"シートを指定します。
 そして先ほど同様、Exit Subの前に『Set sorWS = Nothing』と書いてしまいます。気休めですが、「何らかの処理が終了したとき、必ずオブジェクトを開放してから処理を抜ける」ようにしています。
 次に設定を配列に読み込みます。setCol(7)という配列に、1-7までの枠がある変数に『何のデータが何列にあるか』を、sorWSとして指定した"設定"シートから入れ込んでいきます。
 次はtagWSとして"テストデータ"シートを指定します。tagWS変数は、『ターゲットにするワークシート』という意味で命名しました。ここでも、先のsorWS同様、サブプロシージャの末尾とエラー処理に『Set tagWS = Nothing』とオブジェクト開放を指示しておきます。この辺はコードを書く人の好みレベルのお話しですので、ご随意にどうぞ。
 
 それから、tagWSにある既存のデータクリアです。A列に何か書いてある行は全て、.ClearContentsというメソッドで消してしまいます。
 少々余談ですが、VBでもVBAでも、『プロパティ』と『メソッド』を使っていろいろやります。非常に大雑把な説明ですが、『プロパティ』とは値を読み取ったり、値をセットしたりできるもの。『メソッド』とは、『何々メソッド』という学術用語の通り、何かに対する操作のこと。
 ですので、tagWS.Range("A4:F6").Valueという表現で扱うなら、『.Value』はプロパティです。それも、値を読み取る/値をセットする、の両方が可能なプロパティです。(という風のわざわざ書くということは、プロパティのなかには、値読取専用プロパティもある、という事です)
 tagWS.Range("A4:F6").ClearContentsという表現で扱うとき、『ClearContents』はメソッドです。tagWSで指定したシートの、.Range("A4:F6")、つまり『A4セルからF6セルの範囲(Rangeオブジェクト)』に対して、『ClearContents(内容をクリアしなさい)』という操作(メソッド)を行います。
 さて、メソッドとプロパティの違いに関するお話はここまでにして、続きを見てまいりましょう。
 データのソース(源)となるワークシートを、『大分県郵便番号簿』シートに設定しなおしました。
 パブリック変数RndNoに、 tagWS.Cells(2, 5).Valueに書いてある値を読み込みます。Cellsで指定しましたが、可読性を高くしたいならRangeで指定しても問題はありません。
  RndNo = tagWS.Range("E2").Value
  でも良いのです。この辺はコードを書く人の好みレベルのお話しですので、ご随意にどうぞ。
  次にlimitF変数に、『大分県郵便番号簿』シートの最大行数を入れます。1〜最大行数の間のどこかで、ランダムに抽出してもらいたいからです。
  次の行のRandomizeは、VB/VBA共通のプログラム命令。『ランダム処理の元となる値(シード値とも呼びます)を初期化しなさい』という意味です。この後、Rnd関数を複数使いますが、シード値が同じままだと、Rnd関数で作るランダムな値の列が『同じ偏差』のランダムっぷりになります。
  例えば、「1〜5の間でランダムな数字の並びを作りなさい」とRnd関数を使ったら、1回目に「1,4,5,3,2」という値の並んだ答えが返ってきたとします。Randomizeでシード値を変更しておかないと、2回目にRnd関数を使ったら、また「1,4,5,3,2」という値の並んだ答えが返ってくるのです。
  これでは困りますので、Rnd関数の実行前にはRandomizeを実行しておくのです。
  さて、iの中身は4。4行目スタートですから……将来性を無視しておりますね。
  拡張性を持たせる商用プログラムなら、できるだけこうした『コードの内部に、値を書く』という事は避けたほうが宜しゅうございます。将来『5行から開始したい』となったとき、『設定ファイルにスタート行の項目を作って、5と書けばよい』のと、『プログラマに連絡をとって必要なら来てもらい、コードを変更してもらう』のはどちらが簡単でお安くつくでしょうか、という問題は意識しておくほうがよいでしょう。
  もっとも今回の場合、無関係な人に拡張性を保証する必要などはなからございません。自分が使う上で、変更したくなったら書き換えるだけのことです。

  ここから、loop文の中身を見てまいりましょう。
 1件目から、 パブリック変数RndNoに読み込ませた値の件数まで、jをただのカウンタ、iを行指定用の数として用います。
 一行目で、myRnd変数に不思議な加工を施したランダムな値を格納しておりますね。
 myRnd = Int((limitF - 1 + 1) * Rnd() + 1)
 Rnd関数で返される値を、ある任意の範囲内の数値にしたい場合には、このステートメントのように、「Int(最大値 - 最小値 +1 ) * Rnd + 最小値」という式を用います。
 Intは『これに続く()の中の値を整数にする』という関数です。 Rndが返す値は、VBAでは単精度浮動小数点型、0以上1未満という小数なのです。そこで、Intを用いて整数にしてしまいます。
 こうして得たMyRndの値を『行数』として、ソースとなる『大分県郵便番号簿』から、郵便番号を読み込みます。この値を、対象となるワークシート『テストデータ』の、該当列・行(setCol(3) & iで指定)に値として設定します。
 次に、『大分県郵便番号簿』郵便番号と同じ行の『市町村名』と、『街区名』を合体させて、読み込み、設定します。
  街区名の後に、番地を付記します。地元でよくあるタイプの番地は『X-Y-Z』ですが、『X-Y』『X組』というものもあります。そこで、MyRndで生成した値が100超500未満なら『X-Y-Z』、500以上1000未満なら『X-Y』、1000以上1700未満なら『X組』で、1700以上なら『X-Y』という番地に加えてマンション名と何号室、まで付記するようにしました。
 番地の数や何号室の数は、MyRndで生成した値を適当な整数(コード内で値を入れるという、これまた融通の聞かないことをしています)で割ったときの商を用いています。
 この後、氏名の生成(読み込んだ設定から空白の有無を引き渡し)、電話番号の生成をFunctionで行っています。
 
 Functionの説明:Functionは、Subプロシージャとは別に記述できる『関数』と呼ばれるものです。プログラムが最初から用意している関数と異なり、プログラマが各プログラムごとに作成できる関数ですので、ニーズに合わせたものが作れる反面、他所に持って行ってそのまま使えるわけではないのが難点です。
 Subと似ているのは、呼び出し時に値の引渡しができる点です。
 Subと異なるのは、戻り値を返せる点です。
 例えば、『Text_chk(InStr as String)』というSubプロシージャを考えてみてください。Text_chkを呼び出したとき、Instrという値を文字列で渡しますが、Text_chkが処理を行った後、何か返答や値を返してくれるものではありません。何らかの値や反応が知りたければ、別途Private変数などを設定してやらねばいけません。
 一方、『Text_chk(InStr as String) As Boolean』というFunctionプロシージャを考えてみてください。Text_chkを呼び出したとき、Instrという値を文字列で渡し、Text_chkはデフォルトの値か、処理によって変化した値をBoolean型で返してくれます。
 今回の例でいえば、
  tagWS.Range(setCol(2) & i).Value = NameP(setCol(1))
 と言う風に用いています。
 意味は、「対象ワークシートの、setCol(2) & iで対象としたセルの値には、『NamePというFunctionにsetCol(1)の値を渡して処理させた戻り値』をセットしなさい」です。NamePというFunctionは別の場所に、「Private Function NameP(setStr As String) As String」と、文字列型の処理結果を返す関数として記述してあります。(この中身については別途『Functionの説明2』にて説明予定)
 
 Functionで名前、電話番号の生成などが済んだら、iの値を+1して、またLoopしていきます。 こうして全件の生成が済んだら、「必ずオブジェクトを開放してから処理を抜ける」ようにしています。
 また、ただ単に貼り付け処理が終わって、マウスカーソルの砂時計が消えただけですと
「本当に最後まで済んだのか」
 と疑いたくなりますね。そこでメッセージボックスで『終了しました。』と表示させてから、Subを抜けています。
 何万件もデータ貼り付けをする場合、とっても時間が掛かりますので、『現在X件目を処理中です』などのメッセージを間に挟むこともありますが、今回の例では割愛しました。ご自分で考えてみてください。
 
Functionの説明2:Functionで架空の名前、電話番号、マンション名の生成について
 NamePは『人名』を生成するためのFunctionです。Functionですので、ここでは文字列型の値を返すよう設定しました。
 On Error Resume Nextで、何かあっても次の行を実行するようにしてあります。
 では内部を見てまいりましょう。人名のパーツが載った"姓名"ワークシートをソース用WSに設定します。Randomizeでシード値を初期化し、まずA列から『姓』の値を取得。NameP = で、『Functionの戻り値』に設定します。
 次にもう一度Randomizeでシード値を初期化します。
 そして、Function呼び出し時に得た値setStrで、『姓』と『名』の間に空白は入るのか、入らないのかを確認しています。Ifによる条件分岐で、「" "を入れてから、『名』をB列からもらって」NameP = と設定するか、「空白なしで『名』を追加する形で」NameP = に設定するかしています。
 こうしてNamePというFunctionは架空の人名を返すようになりました。
 NameMというマンション名生成用Functionでもほぼ同様のことをやっておりますので、NameMの解説は省きます。
 擬似電話番号生成用のPhoneNでは、少し手順が複雑になっていますので、解説いたしますね。
 先にも書きましたように、市外局番を得るための「地域名」は、郵便番号簿の市町村名と、電話番号簿の市町村名では異なる記述が見受けられます。
 Accessなどのデータベースなら、変換用のテーブルを用意するのですが、ここはExcelだけで処理しますので、同一ファイル内にシート『市町村名変換』を作成しました。
 処理するのはExcel上でも、この変換用シート『市町村名変換』と、郵便番号簿をデータベースのテーブルのように用いています。やりかたは次の通り。
 VBAのメニュー『ツール』から『参照設定』を開き、参照可能なライブラリファイルのなかから『Microsoft Jet and Replication Object 2.6 Library』にチェックを入れます。このライブラリファイルがあれば、Excelでもデータベースオブジェクトを扱えるようになります。
 データベースオブジェクト、と表現しましたが、目に見えるシートやフォームという『モノ』ではなく、データ上の『概念体』『カタチ』です。
 ここでは『Microsoft Jet and Replication Object 2.6 Library』にて提供される機能のうち、ADOという『データベースの取り扱い手順』を用いて、Excelのシート上データを、あたかもデータベースのテーブル上データのように扱います。その手順を説明してまいりましょう。
 Functionの中には次のように書いてあります。
  Set CN = New ADODB.Connection
CN.Provider = "Microsoft.Jet.OLEDB.4.0"
CN.Properties("Extended Properties") = "Excel 8.0"
CN.Open ThisWorkbook.FullName
 変数CNをオブジェクトとして宣言してありますから、まずはその中身を『新規作成した、ADOのデータベース(略してDB)への、接続部品』と設定します。
 次に、接続方法の設定は、先のライブラリから『Microsoft.Jet.OLEDB.4.0』を用います、と設定します。
 それから、プロパティの中の"Extended Properties"(拡張設定項目)として、"Excel 8.0"を指定します(古いバージョンのExcel97でも動作出来る様にしました)。
 最後に、ファイルを指定して接続を開始します。『Open ThisWorkbook.FullName』とは、『このワークブックの、ファイルパス全て含めたファイル名で、データベース接続開始』という意味です。
 これで接続部としてCNが動作し始めました。
 次は実際にデータのあるテーブルに対して、『郵便番号簿シートの地域名と、市町村名変換シートの地域名が一致するものを抽出せよ』と問い合わせを発行します。
 tableL変数に、市町村名変換シートのデータが記載された領域を、セル範囲で指定します。
 その後のsqlStrという文字列型変数に、先の『郵便番号簿シートの地域名と、市町村名変換シートの地域名が一致するものを抽出せよ』という問い合わせを設定します。sqlStrの右辺にはこのように書いておりますね。

 "SELECT * FROM " & tableL & " WHERE 郵便番号簿 = '" & Area & "'"
 
 いくつかの特殊な記号の用い方を除けば、書いてある内容は普通の英語の命令文です。
 「tableLで指定したセル範囲から、WHERE以降で指定した条件『郵便番号簿にAreaが在る』と合致するデータを、*(全部、という意味)SELECT(抽出)せよ」
 tableLで指定したセル範囲は長い文字列です。これをいちいちSQLに書き込んでいたら、後でチェックするときに読みづらいので、変数で代入できるようにしたのです。
 上記のような文の書き方を『SQL』といい、データベースの世界では頻繁に用いられる文体です。データベースへの命令を、抽出に限らず様々な用途で、事細かな部分まで指定できるため、AccessVBAを使う場合にはクエリと併用したりすることもあります。
 今回はVBA内部で、次のように用いています。
 
  Set myRS = New ADODB.Recordset
 myRS.Open sqlStr, CN, adOpenStatic, adLockReadOnly

 myRSで宣言したオブジェクトを、『新規の、ADOのデータベース(略してDB)の、レコードセット』と設定します。大雑把に説明すると、レコードセットとは『テーブルから抽出したデータ(レコード)の一連のかたまり(セット)』です。
 2行目では抽出したデータ『レコードセット』をOpenし、同時に色々設定しています。
 レコードを抽出する方法が指定されねばなりませんし、データベースの接続部品は何を用いるのか、読み込んだデータは読取専用なのか、書き込みも可能にするのか、といった設定をしてやります。それが
 『sqlStr』(SQL文を用いた、データを抽出する方法)
 『CN』(使用するデータベースの接続部品)
 『adOpenStatic』(カーソルタイプという、レコードセット内の検索設定。adOpenStaticは静的に開くという意味で、レコードセットを1番から最後の方へ、あるいは最後から1番の方へ検索できます。またFindというメソッドも使えます。しかしレコードを変更したり追加、削除のような、『動的なこと』は出来ません)
 『adLockReadOnly』(ロックのかけ方。データベースをある人が処理中に、他の人が更新したりする際にはどうするか?という設定。adLockReadOnlyは読取専用という意味ですので、他の人が自由に更新出来ます)
 といった言葉です。
 こうしてmyRSを作成しましたが、場合によっては問い合わせ結果が一件も無いこともありえます。なぜなら、『市町村名変換』シートに記載してあるのは『変換が必要な地域名と、変換後の地域名』であって、『変換の必要ない地域名』は書いてないためです。
 Excelシートからのレコードセットの読み込みは、特別な指定をしなかったら、Excelで指定された範囲の一行めは、データベース・テーブルでいうところの『フィールド名』として読み込みされます。(http://support.microsoft.com/kb/257819/ja
 レコードセットをVBAで記述するときは、レコードセット名!フィールド名 として下記のように記します。
 
 myRS!電話番号簿
 
 そこでIf文による条件判定です。次のようにやっています。
 
  If myRS.BOF = False And myRS.EOF = False Then
   Area = myRS!電話番号簿
  (もし、myRSに1件でもデータがあれば、BOFプロパティとEOFプロパティは共にFalseを返す。両方がfalseならデータあり。AreaにはmyRSレコードセットの電話番号簿フィールドの値を入れる)
  Else
  Area = Left(Area, Len(Area) - 1)
 End If
 (そうでない場合は、Areaで与えられた引数の文字列から、一番右の一文字を削ったものが地域名となるようにする。『電話番号』シートの『市町村』は、『郵便番号簿』の『市』を削った文字だから。)

 これで 『電話番号』シート検索用の『市町村』文字列ができました。
 myRSを一度Nothingで開放しておきます。
 次はmyWSを『電話番号』シートに設定しなおし、電話番号の市外局番検索に入ります。またtableL でセル範囲を指定します。
 sqlStr = "SELECT * FROM " & tableL & " WHERE 市町村 = '" & Area & "'"
 (tableLで設定した範囲のうち、市町村フィールドの値がAreaで指定する文字列と一致するデータを * 全て抽出せよ)
 これでまた、myRSレコードセットを新たに作ります。
 そして次のランダムな値を得る際の範囲指定、最大値をintMaxとして、 myRSのrecordcountプロパティから得ます。record(記録の)count(カウント)即ち『レコードセットの持っているデータの数』です。最小値は1ですので、intMin = 1と代入します。
 先にもでた式で、iにランダムな値を設定します。
 
 i = Int((intMax - intMin + 1) * Rnd() + intMin)
 
 この次は、レコードセットのデータ数の範囲内で、ランダムな一件『i』番目を選びます。それがmyRS.MoveFirstの後の myRS.Move i - 1 です。一度一番前のデータに確実に移動した後、改めてi-1番目に移動しなおします。
 そして、個別の回線番号は1000から9999の範囲内でランダムに生成します。
 その後、市外局番、市内局番はレコードセットから得て、個別回線番号を付加した文字列をPhoneNの戻り値として設定します。
 最後はオブジェクトをメモリから開放して、Function終了です。
 
最後に
 今回の例は、あくまで自分の作った簡易データベースのテスト用に、数百件程度の擬似個人情報が欲しい、というものでした。
 大規模な商用データベースでは、これが何万件、何十万件という話になってしまいますので、同じ手法でテスト用データを作るというわけには行かないでしょう。また、重複の許可される頻度もより少ないものと思われます。
 ただ、世の大半の『VBAで事足りるシステム』は、簡易データベースに過ぎません。ですが、うっかりUSBメモリを置き忘れたり無くしたりしたときに失う信頼は、簡易には取り戻せないものです。予防的に、テストデータ及び持ち出しデモンストレーション用データには、擬似個人情報を用いるようにすることで、結果として『個人情報取扱において信頼のおける人』とみなされるようになれば、万々歳でございます。
 ご自分の在住する都道府県で応用してみたい方は、どうぞお試しになってみてください。この程度のプログラム、自己責任でコピー&ペーストしてお使いになってくださって結構です。
 ただ著作権は『クリエイティブコモンズ』にのっとり、CC−BY−NCといたします。切身魚の名を記名しておいて下さい。この記事及びコードは非商用利用が可能です。記事及びコードを『出版物』として利益を得ることは許されません。コピペしたコードを、ご自分の商用プログラムに、自己責任で組み込むぶんにはご自由にどうぞ。
 お役に立った折は、おひねり程度にAmazonギフトコード(最低限度額)の1つもを送りつけてくださると大層喜ばしいことでございますが、そこいら辺はご随意にどうぞ。(押し付け先は『さんeよんylqあっとまーくgmailどっとcomあたりでw)

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

擬似個人情報生成.xls
posted by 切身魚(Kirimisakana) at 09:37| Comment(0) | プログラミング | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
コメントを書く
お名前: [必須入力]

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

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


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