Excelにはランダムな数値を出す、RAND関数があります。
実務の業務ではあまり利用価値がないかもしれませんね。
利用する例を考えると、抽選を行うときに当選者をランダムで選んだり、統計的にアンケートを取る時に誰にアンケートを取るか選んだりするときに使えるかなと思います。
もう一つ、私のようなパソコンのインストラクターをしていると練習問題を作る時にデータをランダムで作りますが、そのような時に使えます。
単純なランダムデータを作るのはとても簡単です。今回はその方法と、もう少し高度に、ある特性を持ったランダムデータの作り方を紹介します。
ランダムデータを作る関数
まず、ランダムデータを作るための関数が２つあるので紹介します。
RAND関数
0以上１より小さい小数点以下までのランダムな数字を求めます。書式は次の通り。
=RAND()
これだけです。()の中に何もいりません。
0.297515302677358のような細かい数字を出します。だからもうランダムに出せる数の種類は無限と言っていいと思います。
これ、０以上で１より小さいそうですが、過去０ぴったりの数が出たことが私はありません。そりゃほぼ無限の組み合わせがあるものですから、そのうちのたった一つの０が出るのを見るのは、本当にラッキーではないと見れないですね。
余談ですが、昔からプログラムでランダムな数値を求める方法はあって、基本的には０以上１未満の数値を出してくれることが多かったのですね。
昔、ゲームプログラムを作る時、例えばサイコロの1から６の数値を出すには、次のような計算式を作っていました。
サイコロの目=整数（０から１のランダムな数値×６）＋１
これをExcelの計算式とすると、
=INT(RAND()*6)+1
という計算式になります。
RANDBETWEEN関数
もうひとつ、ランダムな数を求めるRANDBETWEEN関数があります。
その名の通り、ランダムな数、その間で（BETWEEN）ですから、指定した2つの値の間のランダムな数値を出します。
=RANDBETWEEN(0,100)
で、１から１００までのランダムな数値を出します。
求めるのは整数で小数点以下ではありません。
なので、1.0から10.0の小数点以下の数値を作るには、
=RANDBETWEEN(10,100)/10
と10をかけた値でランダムな数値を作り、10で割るという形をとります。
また、ランダムな日付もこれで作れます。2019年1月1日から2020年12月31日までのどれかの日付にするには、
=RANDBETWEEN(DATE(2019,1,1),DATE(2020,12,31))
となります。
また、Excelは内部で1日を1とし計算しているので、
=RANDBETWEEN(DATE(2019,1,1),DATE(2020,12,31))+RAND()
これで、2019年1月1日から2020年12月31日までのどこかの時間というランダムな値が求まります。
営業時間が午前10時から午後6時までで、それのどこかの時間という場合は、10時を24時間で割ったもの、10時から18時を引いた値を24時間で割ってRAND関数に足しあわせます。
=RANDBETWEEN(DATE(2019,1,1),DATE(2020,12,31))+RAND()+(10/24)+((18-10)/24)
火曜日がデータを入れたくない時は、NETWORKDAYS関数などを使って、うまく計算すればできるのですが、正直面倒なので、一回すべての日付で多めにデータを作って、WEEKDAY関数やTEXT関数で日付を曜日に変換した値を使って並べ替えて削除します。
このとき、決まったデータの数が欲しい時は、かなり多めに上記の方法で作成して、不要な日付のデータを削除した後、=RAND()だけの計算式を入れた列を使って並べ替えて、必要な分の行数から下のデータを削除すれば、不公平なくデータを選ぶことができます。
規則性のあるデータ
ランダムなデータは、ランダムに求まります。当然です。
でも本当にランダムなデータはデータ数が増えれば増えるほど、集計後には差がない面白くないデータになります。
集計後にある程度の特性をもったデータだと、練習問題として面白くなります。
でも、例えば、１日にだいたい5件のデータとしたいとか、決まった1商品だけ多くしたいという場合もありますし、午後のデータだけ多くしたいという場合もあります。
そのような場合は、他の関数を組み合わせていくことになります。
割合でどちらかが多いデータにする
全体の2019年から2020年までのデータで50%を2019のデータにしたい場合は、
=IF(RAND()>0.5, RANDBETWEEN(DATE(2018,1,1),DATE(2018,12,31)), RANDBETWEEN(DATE(2019,1,1),DATE(2019,12,31)))+RAND()+(10/24)+((18-10)/24)
にようにIF関数にします。
一日あたりのデータ数
1日のデータ数を5くらいにする場合は、もしもランダム値が0.8より大きければ前の日時+1、そうでなければ前の日付とします。この時、時間は別セルに求めて後で足し合わせることになるでしょう。
項目ごとに頻度を変える
商品ごとにどのくらい売れるかを分散させてデータを作る場合は、意外と近似値型のVLOOKUP関数や、MATCH関数が使えます。
まず、商品一覧を作り、それぞれ想定している商品個数を一覧にします。
累計個数の割合を求めます。累計個数割合を求めた一つ上のセルに0を入力します。
計算式は下図を参照してください。
これで商品の累計発生割合一覧表ができます。
累計発生割合一覧表の中から、RAND関数で発生させた値を検索し、それを繰り返せば設定した割合通りの個数が求まるはずです。
計算式は下図の通りで、INDEX関数で参照している範囲とMATCH関数で参照している行番号が１ずれているところがポイントです。
コメント