-
-
Save Ogurana17/5cf6d3a9258f6c9e909f258e8ad17e4c to your computer and use it in GitHub Desktop.
Excel:ランダムな値を作成する関数辞書
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// ---------------------------------------- | |
// ■RANDDIC | |
// 乱数を簡単に扱うためのモジュール。 | |
// | |
// ■使い方 | |
// https://note.com/akayas_77/n/n397a7566f056 | |
// | |
// ■利用 | |
// Microsoft アカウント | |
// ・Office 365 | |
// ・Excel Labs(https://appsource.microsoft.com/ja-jp/product/office/WA200003696?tab=Overview&exp=ubp8) | |
// ---------------------------------------- | |
// 指定された桁数の整数を生成 | |
NUMINT = LAMBDA(digit, | |
INT(RAND() * 10 ^ digit) | |
); | |
// YYYYMMDD形式の日付から32進数のシリアル値を生成 | |
DATETIMESER = LAMBDA(val, | |
MAKEARRAY( | |
val, | |
1, | |
LAMBDA(row, col, | |
BASE( | |
TEXT( | |
NOW(), | |
"yyyymmdd" | |
) * 10 ^ 4 + | |
TEXT( | |
NOW(), | |
"hhmm" | |
) + row, | |
32 | |
) | |
) | |
) | |
); | |
// クレジットカードのサンプル値を生成 | |
CCNUM = LAMBDA(val, | |
MAKEARRAY( | |
val, | |
1, | |
LAMBDA(row, col, | |
REDUCE( | |
"", | |
SEQUENCE(4), | |
LAMBDA(text, i, | |
IF( | |
i = 1, | |
TEXT( | |
INT( | |
RAND( | |
) * | |
10000 | |
), | |
"0000" | |
), | |
text & "-" & | |
TEXT( | |
INT( | |
RAND( | |
) * | |
10000 | |
), | |
"0000" | |
) | |
) | |
) | |
) | |
) | |
) | |
); | |
// 郵便番号のサンプル値を生成 | |
POSTNUM = LAMBDA(val, | |
MAKEARRAY( | |
val, | |
1, | |
LAMBDA(row, col, | |
TEXT( | |
INT(RAND() * 1000) + | |
row, | |
"000" | |
) & "-" & | |
TEXT( | |
INT( | |
RAND() * | |
100000 | |
) + col, | |
"00000" | |
) | |
) | |
) | |
); | |
// 電話番号のサンプル値を生成 | |
PHONENUM = LAMBDA(val, | |
MAKEARRAY( | |
val, | |
1, | |
LAMBDA(row, col, | |
REDUCE( | |
"", | |
SEQUENCE(3), | |
LAMBDA(text, i, | |
IF( | |
i = 1, | |
TEXT( | |
INT( | |
RAND( | |
) * | |
1000 | |
), | |
"000" | |
), | |
text & "-" & | |
TEXT( | |
INT( | |
RAND( | |
) * | |
10000 | |
), | |
"0000" | |
) | |
) | |
) | |
) | |
) | |
) | |
); | |
// UUIDを生成 | |
UUID = LAMBDA(val, | |
MAKEARRAY( | |
val, | |
1, | |
LAMBDA(row, col, | |
UPPER( | |
CONCATENATE( | |
DEC2HEX( | |
RANDBETWEEN( | |
0, | |
POWER( | |
16, | |
8 | |
) | |
), | |
8 | |
), | |
"-", | |
DEC2HEX( | |
RANDBETWEEN( | |
0, | |
POWER( | |
16, | |
4 | |
) | |
), | |
4 | |
), | |
"-", | |
"4", | |
DEC2HEX( | |
RANDBETWEEN( | |
0, | |
POWER( | |
16, | |
3 | |
) | |
), | |
3 | |
), | |
"-", | |
DEC2HEX( | |
RANDBETWEEN( | |
8, | |
11 | |
) | |
), | |
DEC2HEX( | |
RANDBETWEEN( | |
0, | |
POWER( | |
16, | |
3 | |
) | |
), | |
3 | |
), | |
"-", | |
DEC2HEX( | |
RANDBETWEEN( | |
0, | |
POWER( | |
16, | |
8 | |
) | |
), | |
8 | |
), | |
DEC2HEX( | |
RANDBETWEEN( | |
0, | |
POWER( | |
16, | |
4 | |
) | |
), | |
4 | |
) | |
) | |
) | |
) | |
) | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment