Excelでダミーデータを作る

昨日、Twitterにこんなツイートと、

こんなツイートを

しました。
どちらもRAND関数を使っています。
答えは、初めの方は一日に１０個程度のデータを作る、でした。
もう一つの方はちょっと複雑で、度数分布にあわせたデータを作る、でした。
どちらもパソコンインストラクターがExcel演習問題のためのダミーデータを作るのに使います。
では、解説していきましょう。

RAND関数

RAND関数とは、０以上から１未満の何かの数字を出します。
０から１なら０と１しかないじゃないか。
いいえ、０と１の間にも0.5、0.25、0,125など、中途半端な数字はいくらでもというか無限にあります。
要は小数点以下のランダムな数字を出してくれるのですね。
これって、本来はExcelっぽくない関数だと僕は思っていて、何が出るかわからないじゃないですか。
でもExcelってきっちり計算したデータを出すもので、こんなあやふや要素は何に使うのかよくわからないですよね。
例えば、くじとか、あとはダミーデータとか、あと私はExcelの問題を作るのに一回一回数値を自分で決めるのが面倒なので使っています。

計算式の使い方

今回、Twitterで投稿した数式の使い方を簡単に説明します。

一日何個かのデータを作る

先頭に日付を一つ入れます。
そしてその下の行に「=IF(RAND()>0.9,A1+1,A1)」と入力、そのセルの書式が標準になってしまうので、日付の書式に変更し、下に必要な文だけコピーします。

確率として、0.9より大きくすれば約10アイテムで次の日付にいくかなと思うのですが、そこは確率のマジックでそれ以前で変わることが多いみたいで、0.92くらいだとちょうど10アイテムで変わるように感じます。
この数値は適宜調整して下さい。

このままでは、何か操作するたびにデータがわかるので、コピーしたものは、そのままの範囲をコピーして、そのままの範囲に値として貼り付けて下さい。

度数分布にあわせたデータを作る

ただ、ランダムなデータにすると、そのデータの分布は平均的となります。それでは問題データとしては面白くないので特徴をつけます。
ランダムなデータに偏りをつけるのがこの数式の目的です。
まず、一日何個かのデータを作成し、商品一覧に、どのような偏りがあるか１０段階でも１００段階でもかまわないので数値を設定します。

その数値に対して、縦の比率と、累計を求めます。

累計を切り取って商品の前に列の２つめの商品以下に貼り付けます。

1つ目の商品の累計に0を入力します。

商品に数式「=VLOOKUP(RAND(),$D$3:$G$7,2,TRUE)」を入れ、コピーします。

この値もコピーし、値の貼り付けをします。
このくらいの個数だと、指定通りになかなかいかないようですが、100個くらいこの計算式を作ると、だいたい設定どおりの分布になるようです。

簡単な解説

ここからは、簡単にこの計算式の動作を解説します。
難しいかもしれないのですが、応用する時には動作がどうなっているか把握する必要があるので、この数式便利だな、何かに使いたいなと具体的に思った方は繰り返し読んで理解しましょう。

一日何個かのデータを作る

RAND関数は０以上、１以下の数値なので、0.9以上になる確率は10%くらいと踏んで、10アイテムで一つ前の日付に１を足す=翌日になる、そうでなければ一つ上の日付のままになるという数式です。
ちなみに時刻は、Excelの場合、１日が１なので、RAND関数のままで、24時間のどこかになります。
なので、今回の日付の計算式に単純に+RAND()だけで、ランダムな日時とすることができます。
でも、０時から24時までの営業時間ではないという場合もあるでしょう。
例えば、朝6時からの12時間が営業時間だとしたら、12時間は24時間の半分なので0.5です。なので、まず、RAND関数に0.5をかけます。
=RAND()*0.5
この時点で12時間のうちの時刻になります。
朝6時は一日24時間のうち、始まってから4分の１なので、0.25、これが開始になるので、0.25を足せばいいのです。
=RAND()*0.5+0.25
かける値は、開店している時間/24、足す値は開店時間/24で求まります。

度数分布にあわせたデータを作る

作成した確率の表ですが、商品Aから商品Eまでの合計数は、全体の100％の割合ですよね。
それは、各商品の個数やパーセンテージが積みあがった累計からなるものです。
そしてRAND関数は０以上、１以下の数値、つまり0%以上100%以下の数値になります。
RAND関数で出てきた数値を、この表からVLOOKUP関数の一致ではない使い方で2列目の商品名を検索するとその商品を求めることができます。
VLOOKUP関数の一致しない方では、一覧表で参照する数値にお約束があります。
一つは、小さい順に並んでいること。これは累計はすでに小さい順に並んでいるはずなのでクリアしています。
もうひとつは、検索は、その数値が超えないところで一番大きなところを検索します。
例えば１番目が25%だとしたら、RAND関数の答えが0.25未満なら、検索されません。
なので、VLOOKUPの一致しない方では、１個目の選択として、考えられるだけの最小値を設定する必要があって、今回は0未満にはならないので、0を1つ目の商品のインデックスとしています。
RAND関数の答えが0にめちゃくちゃ近い時は１つ目の商品を求めますよね。

コメント

タイトルとURLをコピーしました