ランダムパスワード生成 - エクセル関数の使い方
使われる関数：IF、ROUNDDOWN、LEN、MID、SUM、RAND、RANK、OFFSET
作成方法はいくつかある
・混在を無視する方法（使用文字数が少ないと「数字だけ」や「小文字と数字だけ」などになる）
・ランダムで選び出す（同じ文字が連続したり複数個出る場合がある）
・順番に選び出す（この方法だと一見ランダムだが昇順にならんでいる）
上記はパスとして使いにくいので
ここでは、VBA（マクロ）を使わず同じ文字を複数使わない、
必ず3種類「数字」「小文字」「大文字」を混在させる方法で作ります。
まずパスワードに使う文字を用意する
例 全部）
123456789
0（「0」を使う場合先頭にもっていくと消えるので最後に入れる）
例 全部）abcdefgh
ijk
lmn
opqrstuvwxyz
例 全部）ABCDEFGH
IJKLMN
OPQRSTUVWXYZ
例の全部だと間違えやすい文字があるので、間違えやすい赤い文字は使わない
| |
A |
B |
C |
| 6 |
数字 |
23456789 |
8 |
| 7 |
小文字 |
abcdefghjkmnprstuvxyz |
23 |
| 8 |
大文字 |
ABCDEFGHJKLMNPRSTUVXYZ |
24 |
「B」列に使用する文字を入れます。
「C」列に使用した文字数を表示。「C2」の数式は =IF(B2="","",LEN(B2))
※入力しないときエラーにならないよう
IF() を使う。
LEN()は文字数を数えています。
| |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
| 9 |
数字 |
小文字 |
大文字 |
使う桁数計 |
8 |
|
| 10 |
使う桁数 |
2 |
|
使う桁数 |
3 |
|
使う桁数 |
3 |
|
使う文字計 |
8 |
|
| 11 |
使う数字 |
** |
|
使う文字 |
*** |
|
使う文字 |
*** |
|
使う文字計 |
******** |
|
データ部分
列 D～F 数字
列 G～I 小文字
列 J～L 大文字
桁数の計算は「数字が1個だけ」や「大文字が1個だけ」というのを避けるため
使用文字数の割合で使う数を決めます。
「K10」の数式は
=ROUNDDOWN(C2*(C8/SUM(C6:C8)),0)
「H10」の数式は
=ROUNDDOWN(C2*(C7/SUM(C6:C8)),0)
「E10」の数式は
=C2-K10-H10
割合を出すにあたって
ROUNDDOWNを使用してますが3つとも使うと1足りなくなる場合があるので
「E10」の数式は「=C2-K10-H10」全体から引いています。
11行目は下にランダムの数字を用意して呼び出します。
| |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
| 12 |
7 |
0.153270 |
6 |
13 |
0.459293 |
p |
10 |
0.584856 |
K |
4 |
0.742777 |
7 |
↓ ↓ ↓
| 37 |
|
0.153505 |
|
|
0.459215 |
|
10 |
0.584806 |
|
|
0.742117 |
|
数字で10行、アルファベットで26行使います
E、H、K、Nの列でランダムを発生させます。
「E12」～「E21」の数式 =RAND()
同じように「H12」～「H37」、「K12」～「K37」、「N12」～「N37」にも =RAND()
数字の部分で説明
E列に10個のランダムを発生させてD列で
RANK()関数を使い順位をつけます
「D12」の数式は
=IF($C$6<1,"",RANK(E12,$E$12:OFFSET($D$12,$C$6-1,1)))
通常のRANK関数は数値と範囲指定ですが、文字数が決まっていないのでOFFSET関数でセルの個数で計算します。
「D13」=IF($C$6<2,"",RANK(E13,$E$12:OFFSET($D$12,$C$6-1,1)))
「D14」=IF($C$6<3,"",RANK(E14,$E$12:OFFSET($D$12,$C$6-1,1)))
「$C$6<1」はエラーを出さないように使用している文字数に応じて消しています
上記数字の「D12」で使用文字がバラバラになるのでその数値を「F12」に表示します
「F12」の数式は
=IF(1>$C$6,"",MID($B$6,D12,1))
この方法で「N11」にランダムな文字列が出来上がります
「N11」の数式は
=O12&O13&O14&O15&O16&O17&O18&O19&O20&O21&O22&O23&O24&O25&O26&O27&O28&O29&O30&O31&O32&O33&O34&O35&O36&O37
「CONCATENATE」関数でも良いのですが
SUM関数の sum(O12:O37)のように範囲指定はできないので「&」でも同じ
複数取得したい場合は「M」～「O」列を複数作れば取得できます
またこの方法だと最長26文字ですが「M」～「O」列を増やせば最長62文字までできます。
最後に・・
上記で作成されたパスワードは開くたびに再計算されます。
再計算したくない場合は 数式＞計算方法＞手動 に設定するか
「N11」をコピーして他のセルに 右クリック＞貼り付けオプション（値）で貼り付けてください。