RAND関数ですが、時間経過やセルを更新するたびに乱数が更新されるの鬱陶しいと感じたことありませんか？
・・・
・・・かんやさん！
あ、私ですか？！
特に感じたことはないというか・・・RAND関数ってなんですか？？
・・・うん。
じゃあ今回は
① 都度更新ではなく、1日で乱数が更新される「RAND」関数風の数式の作り方
② 作成した数式を名前付き関数に登録する
③ 名前付き関数を動かしてみる
④ 数式に使用した関数の説明
について話していきたいと思います！
・・・え、はい！
よろしくお願いします！
1日更新の「RAND」関数風数式
今から長い前置きが始まります！
数式を見たい方はこちらにジャンプしてください！
前置き・「RAND」関数の変えたい仕様
あのー、まずRAND関数っていうのは・・・ー？
こちらでも解説しますが、RAND関数は「0 以上 1 未満の乱数」を出力する関数ですね。
この「RAND」関数について、ちょっと気になる仕様がありまして。
=RAND()例えばB2セルに上の数式を入力すると「乱数」、すなわちランダムな数が出力されます。
ただこれ、他のセルの値を変えると乱数も変更されます。
この仕様、良くも悪くもだと思うのですが、次のようなケースの場合あまり美味しくないわけです。
例えば、買い物のダミーデータを作ることを考えていたとします。
金額はなんでも良いので、RAND関数でランダムな数値を出力したいと思います。
B7セルに、INT関数とRAND関数で三桁までの整数を出力する数式を作成しました。
B8セルに、これの数式をコピペすると・・・
B7セルの値も変わってしまいました。
B10セルまでコピペしてみます。
B7セル、B8セルの値が更新されました。
これ、嫌じゃないですか。
・・・？？
何がですか？
これはこれで使えることはあると思いますが、
「乱数の作成は一回でいいよ」って思うわけです。
「OneDayRand」で良いわけですよね。
この関数自体、いつ使うのかが分からないので、よく分からないです。
最初にできた乱数で固定（1日）しておく数式
乱数は作りたい。
でも最初にできた乱数で固定しておきたい。
そんな時のために作ったのがこの数式ですね！
=LET(x,VALUE(TODAY()),y,COLUMN(),RIGHT(INT(RIGHT((x+ROW())*
(x+y)/(1+ROW()),5)*(x&y)),8)/10^8)前提条件は3つ
・RAND関数は使わない
・出力は小数点代8位まで
・1日で別の乱数を生成し直す
この数式をセルに入れると、その小数点第8位「0.xxxxxxxx」の乱数（もどき）を出力します。
数式の内容を簡単に説明すると、
「今日の日付」を取得して、それとセルの「行数」、「列数」をかけたり足したりすることで、擬似的な乱数を作成しています。
数式の詳しい説明はこちらでおこないますね。
「名前付き関数」への登録
こちらの数式もよく使うと思うので、「名前付き関数」に登録しておきましょう。
いつ使うのか、
私はイメージが全くわいていません。
「名前付き関数」の設定
「名前付き関数」の詳しい説明については以下の記事でも紹介していますので興味があれば見てみてください。
名前付き関数は、スプレッドシートの
上部メニュー ＞ 「データ」 ＞ 「名前付き関数」 と進むと画面の右にメニューが表示されます。
| 関数名 | RANDATE |
| 関数の説明 | 1日の間は固定される0以上1未満の乱数を出力します。 |
| 引数 | |
| 数式の定義 | =LET(x,VALUE(TODAY()),y,COLUMN(),RIGHT(INT(RIGHT((x+ROW())*(x+y)/ (1+ROW()),5)*(x&y)),8)/10^8) |
「次へ」を押します。
今回は引数を設定していないので、このまま「作成」で進んで問題ありません。
作成した「名前付き関数」を動かしてみる
先ほど作成した関数名をセルに入力してみてください。
=RANDATE()
=RANDATE()*1000出力される値は1未満ですので、「1000」をかけてみます。
（擬似）乱数の値が出力されました。
数式をコピペしても・・・
作成された乱数は変わりません。
数式に使用した関数の説明
名前付き関数に登録した数式の解説
=LET(x,VALUE(TODAY()),y,COLUMN(),RIGHT(INT(RIGHT((x+ROW())*
(x+y)/(1+ROW()),5)*(x&y)),8)/10^8)「LET」って見たことない関数・・・
なんだか難しそうですね〜
そんなに難しいことはやっていませんが、
ブロックにバラしてみていきましょう。
//ブロックA
=LET(x, ’ブロックB’ ,y,COLUMN(), ’ブロックC’ )//ブロックB
VALUE(TODAY())//ブロックC
RIGHT(INT(RIGHT((x+ROW())*(x+y)/(1+ROW()),5)*(x&y)),8)/10^8ブロックBについて
//ブロックB
VALUE(TODAY())「TODAY」関数は”今日”を取得する関数です。
「2024年3月28日」に「=TODAY()」をセルに入力すると、
「2024/03/28」といった日付が出力されます。
「VALUE」関数は、「TODAY」で取得した「年月日」を”数値データ”に変換する関数です。
日付を入力したのに、こんな数値になったことありません？
「45379」
あります！！
なんとか苦戦しながらも日時に変えた覚えが！！
これが日付の”数値データ”ですね。
「2024/3/28」では扱いづらいので、「VALUE」関数で「45379」に変換しているわけです。
ブロックAについて
//ブロックA
=LET(x, ’ブロックB’ ,y,COLUMN(), ’ブロックC’ )ブロックBでは「日付データ」を「数値データ」に変換しました。
これをブロックAに代入してみます。
//ブロックA
=LET(x, "45379" ,y,COLUMN(), ’ブロックC’ )この「LET」関数は、定義した名前に値を代入できる関数です。
一番最初の「x」は定義した名前で、この「x」に「45379」を代入しています。
「y」と「COLUMN()」の関係も同様で、「y」には「COLUMN()」が代入されています。
そこで最後の、今「’ブロックC’」が入っている箇所ですが、
この「x」と「y」を使って数式を組めるエリアになります。
ブロックCについて
//ブロックC
RIGHT(INT(RIGHT((x+ROW())*(x+y)/(1+ROW()),5)*(x&y)),8)/10^8
「’ブロックC’」の数式には「x」と「y」が散りばめられていますね。
ここには、
「x」=「45379」
「y」=「COLUMN()」
が入ってくるわけです。
ああー・・・
「LET」は、複数回同じ数式を使う場合に便利な関数ってことですね！！
そういうことです。
「LET」関数の考え方はやや難しいですが、
使い方によって大幅に数式を短くすることも可能です。
今回のがいい例ですね。
RIGHT(INT(RIGHT((VALUE(TODAY())+ROW())*(VALUE(TODAY())+COLUMN())/
(1+ROW()),5)*(VALUE(TODAY())&COLUMN())),8)/10^8本来ならこんな風に数式を入れる必要がありますが、
これを「LET」関数を使うことで・・・
=LET(x, VALUE(TODAY()), y ,COLUMN(),
RIGHT(INT(RIGHT((x+ROW())*(x+y)/(1+ROW()),5)*(x&y)),8)/10^8
）ここまで短くできます。
RIGHT(
INT(
RIGHT((x+ROW())*(x+y)/(1+ROW()),5)*
(x&y)
),8
)/10^8この「’ブロックC’」の内容ですが、今日の「日付」と「行数」、「列数」を使って適当な値を作っています。
この赤字部分の計算式では、結構な桁数の値が出されますので、それを「RIGHT」で”右から5つ”抜粋しています。
なんで抜粋する必要があるんですか？
それと、なぜ”左から”ではなく、”右から”とるんですか？？
日付の”数値データ”って、「45379」、次の日になっても「45380」、
ここに「行数」や「列数」を足し引きしたところで、ほとんど似たような数値になってしまう。
例えば、同じセル（COLUMN() = 「1」、ROW() = 「1」）と掛け合わせた「45379」と「45380」の出力結果を比較してみます。
=("45379"+COLUMN())*("45379"+ROW())/(1+COLUMN())
=1029626820.5=("45380"+COLUMN())*("45380"+ROW())/(1+COLUMN())
=1029672200数値が「1」違うだけでは、大幅な出力の揺らぎは作り出せません。
「1029626820.5」、「1029672200」では数値の差は大してありません。
書き方を変えて、10億2962万6820と10億2967万2200だったら、もうほとんど一緒とも言えますね？
え！？ん？？
10億2962万と10億2967万はほとんど一緒です！！
これを”右から五桁”で抜粋すると・・・
「26821」と「72200」。
この2つの数字を比べると結構の違いが出てきます。
はい・・・っ！！
「26821」と「72200」だと全然違ってきます。
それに更に数値を掛けて数を大きくし、「INT」関数で端数を切っています。
この掛け合わせによって、数値が「1」違うだけでも桁が大きく変わったりするので、出力結果を均一化するために、最後に「RIGHT」関数で8桁に整えています。
それを「10^8」＝「10の8乗」で割ってできあがりですね。
一応、数値がちゃんとバラけているのか確認のためにこんな表を作ってみました。
使用した関数について（Ai解説）
LET
LET関数は、計算内で使用するための名前を割り当て、その名前に値を代入することができます。これにより、複雑な式をより読みやすく、効率的に記述できます。計算の重複を避け、パフォーマンスを向上させることができます。
LET(名前1, 値1, 名前2, 値2, ..., 式)
VALUE
VALUE関数は、テキスト形式の数値を数値データに変換します。テキストとして入力された数値を計算に使用したい場合に便利です。
VALUE(テキスト)
TODAY
TODAY関数は、現在の日付を返します。この関数は引数を取らず、スプレッドシートを開いた時点での日付が自動的に計算されます。時間は含まれず、日付のみが表示されます。日付関連の計算や条件付き書式設定に便利で、特に日報、週報、月報などの作成時に、常に最新の日付を表示させたい場合に使用されます。
TODAY()
例えば、今日の日付を基準にして何日後や何日前の日付を計算したい場合、TODAY関数を使用してその基準日を設定し、そこから所定の数を加算または減算することで目的の日付を求めることができます。
COLUMN
COLUMN関数は、参照されたセルや範囲の列番号を返します。特定のセルの位置情報が必要な場合に使います。
COLUMN([セル参照])
RIGHT
RIGHT関数は、テキスト文字列の右側から指定した数の文字を返します。テキストの一部を抽出したい時に使用します。
RIGHT(テキスト, [文字数])
INT
INT関数は、数値を下に丸めて最も近い整数にします。小数点以下を切り捨てる場合に使用します。
INT(数値)
ROW
ROW関数は、参照されたセルや範囲の行番号を返します。セルの位置情報を知りたい時に使用します。
ROW([セル参照])
まとめ
今回は
① 都度更新ではなく、1日で乱数が更新される「RAND」関数風の数式の作り方
② 作成した数式を名前付き関数に登録する
③ 名前付き関数を動かしてみる
④ 数式に使用した関数の説明
について話しました。
これ、いつ使うんですか？
取得している数値は「1」しか違いがなくても、
掛け合わせて桁数を増やし、大きくなった数値の小さい桁を「RIGHT」関数でトリミングすることが、”良い乱数”を生むミソだったと思います！
コメント