mitc - 日記

OS	Windows Vista
Excel	Excel 2007
ダミーデータ作成	なんちゃって個人情報

• やり方の列挙
  • 元となるダミーデータを取得
  • Excelで編集
  • CSV形式で保存
  • 最後に
• 編集に使えそうな関数とか
  • ふりがなはカタカナがいい！(ひらがな→カタカナ変換)
  • このデータは全角(半角)である必要が……(全角→半角，半角→全角変換)
  • 日付形式が違う
  • 誕生日の列を流用ではなく独立したランダムな日付が欲しい
  • 日付だけではなく時刻までランダムに取得したい
  • 文字列フィールドはダブルクォーテーションで囲みたい(文字列結合)
  • こんどのシステムは男，女じゃなくてM，Fで区別するんだけど(IF関数)
  • True/Falseのフィールドを作りたいけどそんな項目無い！(乱数，IF関数内での関数使用)
  • ランダムなn桁の整数が欲しい！(乱数)
  • ランダムな整数が欲しい！(乱数)
  • ランダムに桁数が異なる整数が欲しい！(IFのネスト)
• 使った関数一覧など

上の項目から順番に書いたので，項目単体を見ただけではわからない場合があります*1．
注意！： 住所は都道府県までしか無理です

■やり方の列挙

元となるダミーデータを取得

元データ作成

• なんちゃって個人情報
  • http://kazina.com/dummy/
  • 元々はここのみを見て書いてます．この記事もこれに準拠．
• テストデータ作成
  • http://landhere.info/tools/testdata.php
  • 他にもあったので並べました．郵便番号が本当に妥当な郵便番号かは検証していないので不明．
  • ブラウザ上にテキストで出力されるのでExcelに貼り付けるだけでなんちゃって個人情報のデータと連係できます．
  • この郵便番号をハイフン無しデータ(000-0000→0000000)に変換するならセルの書式設定をユーザ定義で「0000000」に指定してハイフンを空白に置換すればOK

CSV形式にチェックを入れてダウンロード．
デフォルトでは拡張子がcgiになっているのでcsvに変えたりしておくとわかりやすいかも．

Excelで編集

このままでは柔軟性に欠けてあんまり使えないのでExcelの関数でお手軽に色々付け加えるとGOOD．
検証はExcel2007でしかしてませんが，他のバージョンでも問題無いと思います．

Excelで関数を使ったことが無い方は，他のサイトに良さそうな説明があると思います*2
また，ここでは手軽さをメインに書いているのでExcelの関数しか書いていませんが，より高度なことをするならVBAを使った方がいい場合も多々あるでしょう．
VBAは大抵の処理は簡単に書けてExcelと組み合わせると色々なことができるので一時間ほど割いて学習うしてみるのも一つの手です．

CSV形式で保存

普通に保存しちゃいます．
これで完成．
後は適当にコンバータを作ってDBに格納するなりそのままシステムに食べさせるなり自由に．

最後に

なんかExcelの初歩的な関数の使い方リストみたいになってますが，いざやろうと思ったらほとんど忘れていたので書き残しときます．
後はこれらの組み合わせで一般的に使われるほとんどのパターンのフィールドを作れるのでは*3．
(元データ生成のツールも，一番面倒な和名を書き出してくれるのが嬉しいところですしね．都道府県なんかも嬉しいですが)

やっぱ面倒，Excelなんて持ってない，OOo(OpenOffice.org)のCalcにはその関数無いぞ*4！とか和名なんてどうでもいいし面倒だという場合は，次のサイトを利用してみてはいかがでしょう．
出力するデータをもっと詳細に指定できます．

• generatedata.com
  • http://www.generatedata.com/

■編集に使えそうな関数とか

ふりがなはカタカナがいい！(ひらがな→カタカナ変換)

ふりがなフィールドにPHONETIC関数をかけましょう．

例)次の式をどこかのセルに入れてオートフィルで下まで伸ばす
(ふりがなの列がCだった場合)

 =PHONETIC(C2)

参考:ひらがなをカタカナに変換する関数－PHONETIC関数：Excel エクセルの使い方-関数/計算式-文字列操作

このデータは全角(半角)である必要が……(全角→半角，半角→全角変換)

半角→全角
例)次の式をどこかのセルに入れてオートフィルで下まで伸ばす

 =JIS(C2)

全角→半角
例)次の式をどこかのセルに入れてオートフィルで下まで伸ばす

 =ASC(C2)

このASCは「ascending order(昇順)」ではなくASCII(アスキーコード)のことでしょう多分．

参考:半角を全角に、全角を半角に変換－ASC関数・JIS関数：Excel エクセルの使い方-関数/計算式-文字列操作

日付形式が違う

なんちゃって個人情報の誕生日フィールドをExcelの機能で設定するのが一番楽です．
誕生日の列もしくは変換範囲を選択→右クリック→セルの書式設定→ユーザー定義に「yyyymmdd(20000101)」とか「yyyy/mm/dd(2000/01/01)」とか書けばOK．

誕生日の列を流用ではなく独立したランダムな日付が欲しい

ランダムな日付を使いたいなら，このページに書いたランダムな整数を生成する方法の項目を応用して次のようにすれば良いです．
RANDBETWEEN関数については「ランダムなn桁の整数が欲しい！」の項目にも書きましたが，
簡単に書くと第一引数以上第二引数以下の整数を返す関数です．

例)2000/1/1～2008/12/31の範囲でランダムな日付

 =RANDBETWEEN(DATE(2000,1,1),DATE(2008,12,31))

このままでは，38799などの整数が表示されるだけなので，
セルの書式設定で日付に設定するか，ユーザー定義で「yyyy/m/d」とか書きましょう．

仕組みとしては，DATE関数は日付をシリアル値*5という整数で返すので，指定した日付の間でランダムな整数を取得すればそれがランダムな日付のシリアル値になります．
後はそれをExcelに日付として解釈させればOK．

日付だけではなく時刻までランダムに取得したい

時刻も日付と同じようにシリアル値で管理されているのですが，日付より扱いがちょっと面倒です．
拘りが無ければ次のように出してしまうのが一番楽そうです．

例)0:0:0～23:59:59までのランダムな時刻

 =RANDBETWEEN(0,23) & ":" & RANDBETWEEN(0,59) & ":" & RANDBETWEEN(0,59)

日付と時刻を統合するなら，多分別別の列で日付と時刻をそれぞれ生成し，第三の列で統合する方法が簡単でしょう．
例えば時刻については18:32～23:42までにしたいとかあるなら，時刻を複数列で作ってIFを多用すればできますが，
そこまでするならVBAに行ってしまった方が楽だと思います．

文字列フィールドはダブルクォーテーションで囲みたい(文字列結合)

素直に文字列結合を使うのが簡単

例)次の式をどこかのセルに入れてオートフィルで下まで伸ばす
(対象の列がCだった場合)

 ="""" & C2 & """"

ダブルクォーテーションが4つ並んでいるのは，「文字列」を表す「""」と「"」をエスケープする「"」です．
他の項目での結果も，同様にダブルクォーテーションで囲めます．
例えば，次のような形です．

 ="""" & IF(EXACT(C2,"男"), "男", "女") & """"

こんどのシステムは男，女じゃなくてM，Fで区別するんだけど(IF関数)

EXCELの置換機能を使ってもいいですが，関数でやるならこんな感じ

例)次の式をどこかのセルに入れてオートフィルで下まで伸ばす
(対象の列がCだった場合)

 =IF(C2="男", "M", "F")

文字列の比較にEXACT関数を使うパターンも

 =IF(EXACT(C2,"男"), "男", "女")

普通の「=」を使った比較では大文字小文字を区別しないですが，
EXACT関数を使うとそれを区別するそうです．
まあ，今回の処理には関係無いですね．

True/Falseのフィールドを作りたいけどそんな項目無い！(乱数，IF関数内での関数使用)

上の項目と同じやり方で「男=True」とかやればいいんじゃないかと思いましたが，よく考えたら乱数で作っちゃえばいいですね*6

例)

 =IF(RANDBETWEEN(0,1)=0, "True", "False")

RANDBETWEENは，第一引数以上第二引数以下の整数を返す関数です．
乱数発生関数としては，他にRAND関数がありますが，
RAND関数は，0から1の実数を返すので使い分け．

ランダムなn桁の整数が欲しい！(乱数)

「○○番号」系では桁数が固定の整数が必要だったりします．
今回はジェネレータがはき出せるのでいりませんが，郵便番号もランダムな7桁の整数ですね．

例)11桁

 =RANDBETWEEN(10000000000,99999999999)

上の項目で説明したRANDBETWEEN関数でやればOK．
桁数が大きくなりすぎてエラーが出るよ！という場合は次のように分割して求めましょう

例)20桁

 =RANDBETWEEN(10000000000,99999999999) & RANDBETWEEN(100000000,999999999)

ランダムな整数が欲しい！(乱数)

説明する順番を間違えた気がしますが，上の項目で説明したのを流用して次のような感じ．

例)1000～10000

 =RANDBETWEEN(1000,10000)

ランダムに桁数が異なる整数が欲しい！(IFのネスト)

タイトルがあんまり適切じゃないですが，例えば「10桁」もしくは「8桁」もしくは「6桁」の整数がランダムに混ぜたい！みたいな場合です．
IFの中に関数をネストして作ります．

簡易版

例)

 =IF(RANDBETWEEN(0,1)=0, RANDBETWEEN(1000000000,9999999999),
    IF(RANDBETWEEN(0,1)=0, RANDBETWEEN(10000000,99999999), RANDBETWEEN(100000,999999))
  )

(注: 長くなりすぎたので改行を入れています．改行を入れている場合，セルに直接貼り付けるのではなく，数式入力欄に貼り付けないと動作しません)

この場合は，３種類あるのでIFもネストすればOK．
ただし，こうすると当然偏った結果が出ます．
この場合だと10桁が50%，残り25%,25%ですね．

なので面倒ですが次のように複数列を併用した方が無難でしょう．

複数列併用版

例)次の式をどこかのセルに入れてオートフィルで下まで伸ばす

# C列
 =RANDBETWEEN(0,2)
# D列
 =IF(C2=0, RANDBETWEEN(1000000000,9999999999),
    C2=1, RANDBETWEEN(10000000,99999999), RANDBETWEEN(100000,999999))
  )

当然ながらC列とD列はAでもBでもEでもどこでもいいですし，
別に2行目から始める意味もありません．

■使った関数一覧など

関数	使用例	機能
IF	IF(C2="男", "M", "F")	第一引数の条件がTrueなら第二引数を，Falseなら第三引数をセルに書き出す
EXACT	EXACT(C2,"男")	第一引数と第二引数の文字列を比較する
RANDBETWEEN	RANDBETWEEN(0,1)	第一引数以上第二引数以下のランダムな整数を生成する
RAND	RAND()	0以上1以下のランダムな実数を生成する

• 文字列の結合は「&」
• 文字列の明示は「"」
• 文字列中の「"」のエスケープは「"」*7

Excelの関数一覧が比較的わかりやすかったサイト

• Excel(エクセル)基本講座：Excel(エクセル)関数一覧表
  • http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/itiran.html

他に何か
・この方法じゃ詳細な住所は作れないですねえ
・それに連動して郵便番号もランダムな値しか無理ですねえ
→この辺まで求めると自分でコード書くしかないかあ