（1）IDとパスワード
IDの意味とは？
IDとパスワードでなぜ混乱するの？
IDで使われる文字の種類は？
IDは誰が決める？
IDとメールアドレスは違うの？
（2）パスワードを作る（準備）
よいパスワードとは？
パスワード生成システムの大まかな仕様
Excel の RAND関数とRANDBETWEEN関数
（3）パスワードを作る（基本）
数字のみの場合
数字+英小文字の場合
数字+英字の場合
数字+英字+記号の場合
（4）パスワードを作る（文字種エラーを回避）
文字種エラーが起きない確率は？
文字種エラーを回避するプログラム
（5）パスワードを作る（文字種エラー及び制限文字列を回避）
password30.xlsmの作成
password30.xlsmの利用上の注意
（6）終わりにあたって
『パスワードの作成』について、手っ取り早く、お知りになりたい方は、（5）にお進み下さい。
「桜が各地で咲き出したわね」
「おお、ともちゃんかい。ようやく、本格的な春になった。
暖冬だったとは言え、寒暖の差が年寄りの身には、応えたのう。
さて、本題の『パスワードの作り方』に入る前に、『ID』について、触れておこう。
IDは、英語の Identification （身分証明） や Identity （身元、正体）等の略で身分証明書等を指すのじゃな。
転じて、コンピューターの世界では、対象を区別するための固有番号の意味で使われる」
「その IDは、『ユーザーID』、『ユーザー名』、『会員番号』、『お客様番号』、『アカウント』などとも書かれる。
上の図は、Windows 7 のログオン時に表示されるものね。
IDが『ユーザー名』と書かれている。
これに対し、『パスワード』は、『暗証番号』と書かれる程度で、他の呼び名がないのにね」
「そうなんじゃよ。
IDを意味する用語が多すぎるのは、問題だと思う。
最近、わしも、お二人の方に ID とパスワードの違いは、何なのかと聞かれてしもうた。
ID とパスワード、2つ欄があるだけで、混乱する方がいらっしゃることに配慮する必要があるじゃろう。
IDは、対象を区別するための固有番号であるとご理解いただければよいだけなのじゃがの。
たとえば、銀行の口座（アカウントの元の意）番号、電話番号、学生番号など従来から使われる。
最低限、サイト内でユニーク（唯一無二）で、コンピューターで扱える文字で書き表せないといけない。
※サイトとは、インターネット上の同一組織のホームページのあつまり全体を表す用語。
一方、『氏名』は、漢字でも、コンピューターで扱えない漢字、異体字（渡辺と渡邊等）、同姓同名などの欠点がある。
また、非漢字でも、英文字以外の文字、長すぎる氏名や区切り文字、クリスチャンネーム等の扱いも問題じゃ。
従って、一般に、氏名を ID にすることは、できない」
「IDとパスワードがコンピューター（サイト）の登録データと一致して、初めて、コンピューター（サイト）にアクセスできる。
これを『ログイン』（または『ログオン』、『サインイン』、『サインオン』）と呼ぶことも覚えておきましょう。
逆に、コンピューター（サイト）へのアクセス終了時は、
『ログアウト』（または『ログオフ』、『サインアウト』、『サインオフ』）してくださいな」
「またまた、用語がばらけてしまっているのう。
Windows もWindows 7までは、『ログオン』だったが、Windows 8 以降、『サインイン』となり利用者を戸惑わせた。
ところで、ログインに対しては、ログアウト、すなわち、IN （内）と OUT（外）が対になる。
※個人的には、IN と OUT の対を使うのが、もっとも、説明しやすく、分かりやすいと思う。
ログオンに対しては、ログオフ、すなわち、ON と OFF が対になっているのだな。
ちなみに、英語のログイン（login）のログとは、コンピューターとの通信記録を意味する。
なので、以前は、ログインは、『通信/接続開始』、ログオフは、『通信/接続終了』とも呼ばれていた。
いまは、そのまま、英語をカタカナで書くケースが大半じゃな」
目次に戻る
「なるほど。
さて、日常を振り返ると、銀行のATMで、お金を下ろすとき、自分の口座番号を画面から入れないでしょ。
暗証番号と金額を入力するだけで、お金が出てくるわね。
そのため、インターネットショッピングなどで、IDとパスワードの2つを聞かれると困っちゃうわけよ。
『パスワードは、暗証番号だから、わかるけど、ID って何？、ATMでは、入れなかったよね』と。
でも、よ～く考えてみて欲しいの。ATMにキャッシュカードは、入れるでしょ。
キャッシュカードに口座番号などは、書いてあるので、画面から、利用者が入力しなくていいだけなの」
「もちろん、キャッシュカードの主な目的は、暗証番号と併用して、安全性を高めることじゃがの。
ともちゃんが言うように、利用者の口座番号の入力を省くというメリットもある。
一方、払い戻し請求書に手書きで記載する際は、口座番号や金額などを書き入れるじゃろう。
だが、印鑑を使えば、今度は、暗証番号がいらないのだな。
このように、コンピューターを使うまでは、ID とパスワードという一対のデータを入力する必要が、なかったのじゃ。
インターネットなどの初心者が、日常で意識してこなかった操作を求められて、戸惑うのも無理はないと思う。
あらためて、考えると、わしが話してきた例え話は、こういう点への配慮に少し欠けていたことに思い当たるのう」
目次に戻る
「では、あたし、ともちゃんからも、ご注意を。
みなさん、ID（固有番号）や暗証番号の『番号』は、『番号』と言っても、数字だけとは、限りませんよ。
英字、英数字や場合によっては、記号も含まれることもありますからね。
つまり、日本語の『番号』の意味を、こっそりと、拡張して使っているので、そのおつもりでいてくださいな。
また、IDやパスワードは、半角文字なので、日本語入力をOff にしてください。
IDの例は、こんな感じ。
例1（数字の場合）： 30281234
例2（英数字の場合）：2ccdckz8
例3（英数字と記号の場合）：J1-123456
例4（メールアドレスの場合）：sample@○○.ne.jp」
「たしかにな。
例4のメールアドレスがIDとして使われるケースが増えているようだが、それについては、後述しよう。
また、メールアドレス以外のIDは、サイト側から発行（指定）されることが多いが、自分で決める場合もある。
自分で決める場合は、画面に書かれている注意に従って、文字の種類と長さ（文字数）に注意して欲しい」
目次に戻る
「サイトで新規登録する際、メールアドレス以外のIDは、サイト側から発行（指定）されることが多い。
この場合は、当然ながら、サイトの既存の登録者のIDと重複することはない。
一方、IDを利用者が決めるサイトも少ないながらもあるのじゃな。
この場合、利用者の入力文字列が登録済みの他者のものと重複する可能性があり、変更を促すメッセージが出ることがある。
そのときは、利用者は、文字列の一部または全部を変えて再入力する必要があるので、落ち着いて対応して欲しいのう」
「そうね。
IDは、固有番号なので、重複してしまうと、サイト側で受け入れられないからね。
このように、IDは、誰が決めるのか？、という質問は、素朴だけど、根本的な点だわ。
答えは、IDがサイトから発行される場合と自分で決める場合がある。
どちらなのかは、サイトが決めていることなので、利用者は、画面等の説明に従う必要があるわ」
目次に戻る
「これは、前述のように、IDとメールアドレスが同一のサイトと異なるサイトがある、ということだな。
ただ、近年は、IDをメールアドレスとするサイトが増えている気がするのう。
IDがメールアドレスの場合は、『IDは誰が決める？』という前節の答えは、『利用者が決める』ことになる」
「確かにね。
でも、どうして、IDをメールアドレスにするサイトが増えているのかしら？」
「サイト側から見ると、メールアドレスをIDとするメリットは、いくつかあるじゃろう。
まずは、新規登録の手続きをスムーズに行うことができる。
利用者にIDを決めてもらう場合、文字種や桁数等の制約により、利用者に余分な手間をかけてしまう。
しかし、メールアドレスであれば、自ずと文字種や長さが限定されているので、サイト側も対処しやすい。
さらに、インターネット上で、唯一無二のものなので、サイト内での重複も避けることができる。
一方、IDをサイト側が発行する場合、利用者が書き留めたり、忘れないようにしないといけない。
この点、IDがメールアドレスであれば、利用者が忘れることも少ないだろう。
だが、こういうメリットは、昔からあった。
なぜ、近年、IDをメールアドレスにするサイトが増えているかじゃな。
昔は、一家にパソコン1台があって、それを家族で共用する場合が少なくなかった。
追加のメールアドレスが有料のことが多かったため、個人ごとのメールアドレスがないケースが大半じゃった。
また、追加で、フリーのメールアドレスを取得しても、インターネットショッピング等で使えないこともあった。
そこで、サイト側もメールアドレスを個人のIDとしては、使いにくかったのじゃろう」
「なるほどね。
で、最近は、スマホなどを個人で持つ方も増えているので、少なくとも、1つのメールアドレスがあるって訳ね」
「そうじゃな。
スマホなどを持っていなくても、プロバイダーに無料で追加のメールアドレスを申し込めるケースが多い。
こういった理由から、IDをメールアドレスにするサイトが増えていると考えられるのではないかな」
「IDをメールアドレスとした場合のデメリットは、ないのかしら？」
「やはり、セキュリティ面を考えると、IDがメールアドレスとは、異なっていた方が勝っているじゃろう。
それは、同一のメールアドレスが多くのサイトのIDとして、共通に利用されるからじゃ。
メールアドレスは、普段に利用しているので、サイト以外の他者に知られているか、知られやすい。
従って、セキュリティが低下するおそれがある。
また、利用者がIDを決める場合も、利用者が登録済みの他のサイトと同一または似た文字列をIDとして登録しがちじゃ。
利用者の心がけ次第とはなるものの、メールアドレスをIDにしたのと同様にセキュリティ低下があり得るじゃろう。
一方、サイト側がIDを発行するケースでは、IDがそのサイトのみで通用する文字列となるため、他者に知られにくい。
なので、インターネットバンキングなどのIDは、銀行等のサイト側から発行され、利用者のメールアドレスとは異なる。
もう一つのデメリットは、利用者のメールアドレスが変更された場合のサイト側の手間かのう。
IDがメールアドレスでなければ、それは、登録情報の変更であり、住所や電話番号などの変更と同一のプロセスじゃ。
けれども、IDがメールアドレスの場合は、固有番号が変わることなので、コンピューター上の手続きが、やや複雑になる。
新しいメールアドレスを新規IDとして登録後、従来のIDのデータをコピーした上で、古いデータを削除する必要がある。
従って、IDがメールアドレスのサイトでは、『メールアドレスの変更手続き』ができなかったりすることも、まれにある。
その場合は、やむを得ないので、退会して、新規に登録するしか方法がない」
「メールアドレスの変更時、IDがメールアドレスであろうと、なかろうと、登録サイト毎に変更手続きを行う必要がある。
そういうとき、古いメールアドレスの送受信ができないと退会や変更ができないケースがあり得るので、注意してね。
いずれにしても、メールアドレスの変更は、利用するサイトが多いと結構な手間になるわ」
目次に戻る
「ここから、本題じゃが、ともちゃんは、よいパスワードに要求される条件は、何じゃと思うかの？」
「そうねぇ。
まず、パスワードに対して、サイトが求める条件を満たす文字列であることが必須だわね。
その条件とは、文字の種類と桁数（文字列の長さの最低数と最大数）でしょ」
「そうじゃな。
最初に、文字の種類を説明しよう。
以下、文字と書いた時は、1文字を指し、すべて半角の数字、英字、記号とする。
つまり、日本語入力を解除した状態でキーボードから打てる文字のことじゃな。
文字列は、これらの文字を一列に並べたものだ。
パスワードの文字列の文字種は、以下の4タイプで、たいてい、間に合うと思う。
Ⅰ 数字のみ、
Ⅱ 数字と英小文字の混在、
Ⅲ 数字と英字の混在、
Ⅳ 数字と英字と記号の混在、
なお、上の英字は、英小文字（a，b，c ・・）と英大文字（A，B，C ・・）の両方を指す。
パスワードの英小文字と英大文字は、別の文字として扱われるので、特に注意して欲しい。
サイトによっては、英小文字・大文字の違いによるトラブル防止のため、英字を英小文字のみにしているところもある」
「念のためだけど、英字の小文字と大文字は、英字の半角、全角のことじゃないので、気をつけてくださいな。
次は、桁数（＝文字数）ね。
実用上は、8桁から16桁ぐらいかしら」
「ま、そうじゃな。
キャッシュカードの暗証番号のように4桁とか短い場合は、8桁または16桁の文字列の一部を使えば良いじゃろう。
6桁や12桁なども同様じゃ。
従って、今回は、16桁の文字列を生成し、前半分の8桁を並記することにしよう。
次に考えるべきは、何じゃろうな？」
「たとえば、IDと同じ文字列は、だめね。
また、単純な、1111 や 1234 、password のような推測されやすい文字列を含まないことだわ」
「たしかにな。
もっとも、パスワードがIDと同じ場合は、入力時、サイト側で、たいてい、チェックされるじゃろう。
生年月日、20100401、0401 やニックネーム（例：tomochan）なども好ましくないが利用者毎に変わることに注意する。
さらに、このような制限する文字列に比較的よく使われる英語やローマ字の言葉も含めたい。
そこで、あらかじめ、制限文字列を一括登録しておき、生成される文字列をチェックする仕様にしたいのう」
「なるほど。
最後になるけど、そのシステムで生成される文字列がランダムにする必要があるわね」
「そのランダムさをもう少し、詳しく書けば、
・ システムを起動する度に、できるだけ、異なる文字列を生成すること、
・ 文字列の一部を知られても、残りの文字列を推測されにくいこと、
・ 生成される文字列中の文字は、できるだけ、一様にランダムとする。
すなわち、特定の文字の出現頻度だけが高くなるのは、よくないじゃろう」
目次に戻る
「まとめると、今回作るパスワード生成システムは、次のような仕様になる。
1. 文字の種類から見て、生成するパスワードは、次の4種類、
Ⅰ 数字のみ、
Ⅱ 数字と英小文字の混在、
Ⅲ 数字と英字の混在、
Ⅳ 数字と英字と記号の混在、
2. 文字列に使用する文字（数字、英字、記号）は、利用者が加除訂正できるようにする、
3. 文字列の桁数は、16桁として、前半8桁を並記する、
4. 16桁の文字列には、制限文字列を含まない工夫をする、
5. 制限文字列を利用者が加除訂正できるようにする、
6. 文字列の一部を知られても、残りの文字列を推定されにくいこと
7. 文字列に現れる文字の出現頻度は、できるだけ、一様にランダムであること、
8. システムの起動時に生成される文字列は、できるだけ、同じにはならないこと」
「で、システムは、Windows の独立したプログラムとするの？
それとも、Excelのブックを使うのかしら？」
「みなさんが利用しやすい Excel のマクロ有効ブックとしよう。
ご自分でも、編集できると面白いじゃろう。
そのため、Excelの標準的な式と関数を利用して、一部、プログラム（VBA）を使用する」
目次に戻る
「ともちゃん、Excelの（ワークシート）関数である、RAND関数、について、簡単な実験をして欲しい。
セルに＝RAND()、とした場合にセルに表示される値の範囲、
また、一定の範囲内（整数1と整数2の間）のランダムな整数を返すためには、どうしたらよいか？」
「RAND関数は、0～1の範囲内のランダムな実数値を返す関数だったわね。
よって、整数1と整数2の間の整数を返すためには、
整数1+INT（（整数2－整数1）×RAND()）、とすればいいと思う」
「INT関数は、小数点以下を切り捨てる関数じゃな。
では、試しに、整数1を1，整数2を10として、実験してごらん」
「次のようなExcel表を作ってみたわ。
100回、乱数を生成（下図では、その一部を表示）して、右側の表で出現回数を数えたよ。
あ、ご隠居様、こいつは、いけませんや。
整数2で指定している10の出現が一回もありませんです」
「これで、分かったと思うが、RAND関数は、0以上、1未満なのじゃ。
INT関数の中の （整数2－整数1）×RAND()は、0以上、9未満となり、小数部を切り捨てると、0～8 じゃ。
よって、整数1に加えて、1～9 となり、最大が9までなのだな。（緑字の2行を2019/4/17 修正）
整数1+INT（（整数2－整数1 + 1）×RAND()）、とする必要がある。
すると、以下のように整数2の10の値も出現するのじゃ。
なお、INT関数で小数点以下を切り捨てないで済む実数の場合は、ともちゃんの式が正しい。
マイクロソフトの関数のヘルプでもそうなっている」
「そうか、整数で求めたい場合は、式が変わってくるのね。
うっかりしそうね」
「そこで、Excel 2007 から、RANDBETWEEN関数というものを標準で使うことができる。
※ Excel 2003までは、アドイン関数だった。
RANDBETWEEN（整数1，整数2）、と書けば良い」
「なるほど。
では、同じ問題をRANDBETWEEN関数を使ってやった見たわ。
たまたまかも知れないけど、6が15回も出ている。
平均的には、10回ずつとなるはずだけど」
「Excelのワークシートの式や関数は、他のセルの値が変更されたりすると一斉に再計算される。
下図の数式タブの『計算方法の設定』で『自動』になっていれば、そうなる。
また、数式タブの『再計算実行』をクリックしたり、F9 キーを押したり、ブックを開いたりした時も、再計算される。
RAND関数や日付と時刻を取得するNOW関数なども、上のようなタイミングで再計算され、セルの値が変化する。
さて、いま、1～10までの数字が等しい確率で出るサイコロがあるとする。
そのサイコロをn回振ったとき、ある特定の目がm回出る確率は、どうなるかな」
「特定の目が出る確率を p と書けば、
Comb(n，m)×p^m×（1－p）^(n－m)となるわね。
ここで、COMB（n，m）は、n個のうちからm個を取り出す組み合わせ数を表すDERIVEの関数。
Excelの関数としては、COMBIN（n，m）、ですね。（2項係数）
ちなみに、p＝1/10、として、Excelで計算してみると、下図のようになったわ。（2項分布）
こうしてみると、10回が13%ぐらいの確率で現れる。
15回は、と言うと、3%程度の確率となるので、めったに出ないと言うほどでもなかった」
「2項分布のグラフは、きれいな釣り鐘型となったな。
ともちゃんも知っているように、2項分布は、nが大きい時は、
平均＝n×p、
標準偏差＝σ＝√（n×p×(1－p)）、
の正規分布関数で近似できる。
具体的には、1/√（（2π）σ）×Exp（－（m－平均）^2/（2σ^2））、
だな。
ともちゃんの表に追加して比較すると、
両者の一致度合いは、かなり良いじゃろう。
今回の場合は、σが3なので、3σの範囲は、約99.9%、となるはずだが、
1回から19回までで、正規分布の確率の和は、99.85%だった。
2項分布では、1回から19回までで、99.799%だったので、ほぼ、ドンピシャリと一致しているじゃろう」
「なるほど。
ところで、度数分布や正規分布関数などのExcelの関数を下にご紹介しておきますね。
まず、上の図の FREQUENCY関数は、度数分布（ヒストグラム）を求めます。
ただし、出現確率の欄を先に指定しておき、Shift+Ctrl+Enter、で配列数式として、設定します。
データ配列は、出現回数、区間は、乱数の値欄です。
次に2項分布関数です。
BINOM.DIST（m，n，p，false）、
第4引数のfalseは、確率分布関数を、trueにすると、累積密度関数の値が返ります。
最後に正規分布関数です。
NORM.DIST（m，平均値，標準偏差，false）、
やはり、第4引数のfalseは、確率分布関数を、trueにすると、累積密度関数の値が戻ります。
関数名にピリオドが入っているので注意してください。
関数パレットから引用した方が確実ですよ」
「ともちゃん、ご苦労様。
恥ずかしながら、度数分布を求めるExcelの関数は、使った記憶がなかったな。
あると便利だなと思っておったがの・・。
機会があれば、Excelの統計関数について、あらためて、調べて見たいのう」
※ RAND関数の詳細について、ご興味がある方は、
マイクロソフトのヘルプ及び引用されているウィキペディアの記事をご参照ください。
RAND関数：https://support.office.com/ja-jp/article/rand-関数-4cbfa695-8869-4788-8d90-021ea9f5be73、
メルセンヌ・ツイスタ：https://ja.wikipedia.org/wiki/メルセンヌ・ツイスタ、
目次に戻る
「まず、文字列の制限は、当面、考えずに、数字のみのパスワードを作ってみよう。
Excelブックの名前：『パスワードの生成1』
パスワード文字列を表示するシート：『パスワード』
文字を格納しておくシート：『記号表』
シート『パスワード』の数字部分は、下図のような感じじゃ。
シート『記号表』の数字については、次の通り。
記号表のB3のセルの数字は、数字の個数、10を表している。
実際は、たとえば、数字のゼロを削除した時は、個数が9個となるように、
=COUNTA(範囲)、をセルに入れている」
「問題は、
記号表の0～9までを、パスワードの9行目のD列～S列までの16個のセルにどのようにランダムに表示するかよね。
擬似的に書くと、
セルの値＝記号表の配列（0の位置+RANDBETWEEN（0，9））
というようにしたいんだけどね。
VBAのようなセル番地を簡単に参照する関数がワークシート関数の中にないんだよね」
「わしが、むかし、作った時は、VLOOKUP関数を使ったな。
下図は、その際のシート 記号表。
A列に検索用の番号を振ってある。
一方、シート パスワードのD9のセルには、
=VLOOKUP(INT(RAND()*記号表!$B$3),記号表!$A$6:$B$15,2,FALSE)、を入れてある。
VLOOKUP関数による呼び出しに時間がかるかもしれんが、他の文字種の場合にも分かりやすいと思った。
今回は、OFFSET関数を使ってみたらどうかな？」
「OFFSET関数か。
基準セルから、任意の距離だけ離れたセルを相対的に参照する関数ね。
では、OFFSET関数を使って、『パスワードの生成1』を作ってみたわ。
シート 記号表は、こんな感じ。
シート パスワードは、次のようになった。
D9のセルには、次の式が入っている。
=OFFSET(記号表!$B$5,RANDBETWEEN(1,記号表!$B$3),0,1,1)
赤字は、記号表のB5のセル番地（基準セル）を示す第1引数、
青字の部分が肝心の部分で、（2）節に出てきた RANDBETWEEN（1，個数）を使っている第2引数、
第3引数の0は、基準セルから列方向にいくつずらすかという列数を指定、今回は、ずらさないのでゼロとした。
第4引数の1は、高さといって、参照対象のセルが広がりを持たない場合は、1、
第5引数は、幅と行って、参照対象のセルが広がりを持たない場合は、1、
各引数の意味は、こういうことかしら」
「なるほど。
OFFSET関数の基準位置は、0 の一つ上のセルである B5 としたんじゃな。
それで、RANDBETWEEN関数のかっこ部分は、（1，個数）と書けた。
もし、基準位置をB6の 0 のセルとする場合は、（0，個数－1）とする必要があったからな。
T列には、
=D8&E8&F8&G8&H8&I8&J8&K8&L8&M8&N8&O8&P8&Q8&R8&S8、
のように、D列からS列までの文字を & 演算子によって、1つの文字列にまとめたものが格納される。
では、次節で、数字+英小文字を作ってみよう」
目次に戻る
「ともちゃんの数字のみの例にならって、続きを作ってみた。
ただし、記号表のシートを以下のように、少し、拡大した。
※ 英小文字の、l エルと o オー は、数字の1、0 と紛らわしいため、英小文字に含めなかった。
さて、文字の横に1列を設け、文字種を表す下記の数字を追加した。
数字は、1、
英字は、2、
記号は、3、
これは、後述するように、異なる文字種が混在するとき、文字種が不足している場合を検出するための便宜上のものだ。
これに応じて、パスワードシートを下図のように変更した。
前節の場合は、記号表から文字列を呼び出すOffset関数の第2引数にRANDBETWEENを入れていた。
これに対し、上のシートでは、C列の『乱』と書いてある行にRANDBETWEEN関数の値を入れている。
そして、Offset関数の引数に『乱』のセルを参照して、各文字と文字種を呼び出している。
例えば、数字+英小文字では、
『乱』の行 D15には、=RANDBETWEEN(1,記号表!$D$3)、
（1～記号表シートのD3の個数までの乱数を発生）
16桁の『種』の行 D14には、 =OFFSET(記号表!$E$5,D15,0,1,1)、
（記号表シートのE5からD15分下のセルの文字種を取得）
16桁の『16』の行 D13には、=OFFSET(記号表!$D$5,D15,0,1,1)、
（記号表シートのD5からD15分下のセルの記号を取得）
8桁の『種』の行 D12には、=D14、
（8桁では、D列～K列まで、16桁のものを参照）
8桁の『8』の行 D11には、=D13、
（8桁では、D列～K列まで、16桁のものを参照）
としている」
「文字種エラーのところには、何を入れてあるの？」
「たとえば、U12であれば、
=IF(OR(ISNA(MATCH(1,D12:S12,0)),ISNA(MATCH(2,D12:S12,0))),"エラー","")、
としている。
D12からS12までの間に、1があれば、その最初の位置を返すのがMATCH関数（1，D12：S12，0）、
同様に、2があれば、その最初の位置を返すのが、MATCH関数（2，D12：S12，0）、
MATCH関数では、見つからない場合、#NA というエラーが返る。
ISNA関数は、#NAエラーの場合、TRUE を返し、そうでない場合は、FALSEを返す関数だ。
OR関数は、複数の引数のいずれかがTRUEであれば、TRUEを返し、すべてFALSEの時だけ、FALSEを返す。
最外部のIF関数で、OR関数の値がTRUEの時は、"エラー"、を、FALSEの時は、"" 空文字を返すようにしている」
「文字種エラーとなる確率は、理論上、次のようになるわね。
文字種が数字である確率を p＝10/34、と書けば、
文字列が、k 桁の場合、すべての文字が英小文字になる確率＝（1－p）^k、
同様に、すべての文字が数字である確率＝p^k、
従って、文字種エラーとなる確率は、この2つの和となり、
（1－p）^k+p^k、と表せる。
具体的には、p＝10/34 を使って計算すると、
文字種エラーとなる確率は、8桁の場合、6.17 %、
16桁の場合、0.38 %、となった」
「VBAを使って、繰り返し計算をさせて、文字種エラーが発生する回数を勘定してみた。
試行回数は、3万回じゃ。
8桁で、文字種エラーの発生は、約 6 %、
16桁では、約 0.4 % という結果となった。
命令の実行は、Test1 ボタンに割り当ててある。
命令の詳細は、下図のようじゃ。
試行回数は、n （X3のセルで指定）
プロシージャの途中の Randomize 文は、乱数の種（シード）を新規に発生させる命令。
Calculate は、F9 キーを押すのと同様に、ブックを再計算させる命令（メソッド）じゃ。
計算結果の上記例では、ともちゃんの理論値との一致度合いが良いが、ある程度のばらつきは、ある。
ま、しかし、大体のところは、合っているようだ」
「今度は、数字+英字の場合ね」
目次に戻る
「おじぃさんの作ったものに追加したみたわ。
まずは、記号表のシートね。
英小文字の、l エル、o オー、に加えて、英大文字の、I アイ、O オー、も、数字と紛らわしいので、外しましたよ。
なので、数字10個と英字（26－2）×2＝48個の計58個。
次に、パスワード シートの方は、下図のようになった。
20行目の『乱』の行内のシート 記号表のセルを表す、D3をF3に変更。
19行目の『種』の行内のシート 記号表のセルを表す、EをGに変更。
18行目の『16』行内のシート 記号表のセルを表す、DをFに変更。
なお、17行目の『種』と16行目の『8』は、下の行のセルを参照するだけなので、何も手直しなしね」
「ようできたな。
数字+英文字だと、文字の総数は、58文字となるか。
ともちゃんが注意してくれたように、小文字の l エル、o オー、大文字の I アイ、O オーの4文字を省いている。
数字の割合が、より減ったので、英字だけ、または、数字だけとなる 『文字種エラー』が多くなりそうじゃ。
計算すると、数字の出現確率 p＝10/58、により、
文字種エラーとなる確率は、8桁の場合、22.0 %、16桁の場合、4.8 %、となった。
16桁の場合は、ともかくとして、8桁では、約5回の試行について、1回、文字種エラーが発生する計算となるな。
確かめるために、前節のプログラムを少し、手直しして、3万回、計算させてみた。
予想どおり、8桁で、21.9 %、16桁でも、4.7 % と数字+英小文字の場合より、文字種エラーの発生する確率が高くなる」
「なるほどね。
ここに記号も加えると、より一層、文字種エラーとなる確率が高まるわ。
どうしたら良いかしら。
0～9の数字の組をもう一つ追加するという方法もあるかな」
「それも、一つの方法じゃな。
その場合、数字が10+10＝20個、文字総数が58+10＝68個となり、
数字の出現確率は、p＝20/68、と変わるため、前述と同様の計算により、文字種エラーは、
8桁の場合で、約 6.17 %、16桁で、0.38 % と数字+英小文字と同じぐらいに小さくなる。
実際に1万回試行してみると、上図の右側の『数字+英字』欄の程度となる。
文字種エラーは、8桁で、約 6 %、16桁で、約0.4 %、という結果じゃ。
上述の理論値とほぼ等しくなり、文字種エラーの確率は、大きく低下し、『数字+英小文字』と同程度になる。
ただ、文字種エラーは、この方法では、原理的にゼロには、できない。
また、今後、制限文字列とのチェックを行うと、不適合と判断されるものも出てくるはずじゃ。
回避する簡便な対応法は、エラーメッセージを表示し、利用者にF9キーを押して再計算を実行してもらう方法じゃな。
より実際的な方法は、文字種エラー等の場合に（プログラムにより）エラーがなくなるまで再計算を行わせる方法だ。
後者が簡明な方法じゃろう」
「そうね。
えーと、次のようなこともVBAを使わないとできそうにないけど、
たとえば、8桁の文字列であれば、
1. a＝1～3個程度の個数をランダムに決める。
2. 数字をランダムに、a個選び、記号表シートの新しい列1、その右隣の列2に文字種＝1を縦に並べる。
3. 英字をランダムに、b＝8－a 個選んで、列1の後尾に追加するとともに該当する文字種＝2を列2に追加する。
4. 文字種の列2の右にもう一つの列3があり、0～1までの乱数が入れてある。
5. 最後に列3の値の昇順に列1、列2、列3を並べ替えて、列1を8桁の文字列に、列2を該当する文字種とする。
16桁の場合も同様ね。
こうすれば、文字種エラーは、起こらないわ」
「たしかにな。
ワークシート上の関数だけでは、作れそうにない手順であるのが残念じゃ。
当面、数字+英字+記号の場合に進めよう」
目次に戻る
「では、数字+英字の場合に続けて、数字+英字+記号の文字列を『パスワードの生成1』に追加していこう。
下図は、記号表シートの記号部分の拡大図だ。
記号として、18個の文字を追加した。
これにより、文字数は、76個（数字10個、英字48個、記号18個）となる。
なお、/ スラッシュ、' アポストロフィ 、_ アンダーバー、空白、@ アットマーク、, カンマ、. ピリオド、
\ バックスラッシュ（半角の円記号）、~ チルダ、^ ハットマーク、| 縦線は、除いている。
※ サイトにより、使える記号がまちまちなので、必要に応じて、加除訂正してください。
次にパスワードシートは、下図のようになった。
パスワードシートでは、3つの文字種があるため、文字種エラーのOR関数内に、ISNA関数とMATCH関数を追加した。
その他のセルは、基本的に、数字+英字の場合と同様にセル番地等の変更を加えてある」
「今回は、一層、文字種エラーが増えるわね。
数字の出現確率を、p＝10/76、英字は、q＝48/76、記号は、r＝18/76、として、計算してみると、
文字種エラーとなる確率は、次のようになる。
以下の数式の図では、数字の出現数を a、英字を b、記号を c、として、桁数を、k と置いた。
これは、（p+q+r）^k＝1、の左辺の展開項の一部ね。
左辺は、Σ（a+b+c＝k）多項係数×p^a×q^b×r^c、と書き表せる。
ここで、多項係数＝k！/（a！b！c！）、のこと。
さて、文字種エラーとなるのは、次の2つの場合、
・ a、b、c のいずれか1つがゼロの場合、これが上図の数式の最初の3つの大括弧の和ね。
・ a、b、c のいずれか2つがゼロ、すなわち、すべて1種類となる場合は、上図の数式の後ろの3項となる。
数値を入れて計算すると、
文字種エラーの起きる確率は、8桁では、41.35 %、16桁では、11.72 %、となりましたよ」
「なるほど。
ちょっと、多すぎるようにも思う。
実際に計算してみると、
試行回数を3万回として、
文字種エラーは、8桁で、約41 %、16桁では、約11 %、という結果となった。
ともちゃんの理論値とほぼ同様となった。
これは、数字や記号の出現確率が英字に比べて、小さいからじゃな。
次節で、これらを増やした結果を確認し、また、自動的に文字種エラーを回避する方法を考えてみよう」
目次に戻る
「数字+英小文字、数字+英字、数字+英字+記号、の3つの場合の8桁と16桁の2組ずつについて、
文字種エラーが1個所以上で起きる可能性がある。
では、文字種エラーが起きない可能性は、どの程度かを考えてみよう」
「その6つの文字列のどれにも文字種エラーが起きない確率の事ね。
それは、（1－数字+英小文字の8桁の場合の文字種エラーの確率）などの同様の6つの確率をかけ合わせると、
一つも文字種エラーが起きない確率となるわ。
その数値は、35.92 % となった」
「約 36% ぐらいと言うことじゃな。
では、ともちゃんが言っていたように、
数字+英字では、数字をもう1組増やし、
数字+英字+記号では、数字を2組、記号を1組増やした場合については、どうかな」
「文字種エラーが6つの文字列で、一つも起きない確率は、73.84 %と大きくなった」
「なるほど。
かなりの効果は、あるということじゃな。
文字種エラーのことだけ考えると、英字に比して、少ない数字や記号の比率を高めることは、有利じゃ。
ただ、数字の出現確率を高めると、数字が多くなり、むしろ、脆弱なパスワードとなりかねない。
今回は、文字種エラーが出た場合は、再計算を行って、文字種エラーがない文字列を探ることにしよう」
「それもそうね。
しかし、そもそも、サイト側は、なぜ、文字種がすべて含まれることを要求するのかしら？」
「誤解のないように言っておくと、すべてのサイトがそうだという訳ではない。
いずれかの文字種が欠けた場合に、変更を要求するサイトがある理由を推測してみると、
文字種が足りない文字列をパスワードとして入力する利用者が、抜けた文字種を検討したかどうかが不明じゃ。
そのため、セキュリティを重視すれば、『文字種エラー』の文字列を排除しようと考えているのだと思うがのう」
目次に戻る
「前節の『パスワードの生成10』に文字種エラーを目にしないようなプログラムを追加した。
そうしてできたのが、『パスワードの生成2』じゃ。
パスワードシートや記号表シートは、『パスワードの生成10』と。ほぼ、同じだな。
ただ、違う点は、パスワードシートに『最大試行回数』を入れるセルを設けたことじゃ。
※ 最大試行回数は、無限ループに陥らないようにするためのもの。
文字種エラーが発生した場合、最大試行回数を超えない範囲で、自動的に再計算させている。
これにより、文字種エラーを目にすることがなくなった」
「VBAのコードは、下図のように、『開発』タブを選択して、
次に、開発のリボン内の『Visual Basic』ボタンをクリックする。
あるいは、
Alt+F11 を押すことでも、Visual Basic Editor が起動するわ。
すると、左端のプロジェクトの中の標準モジュール内に一つのSubプロシージャがある。
それが、上の図ね」
「既述のプロシージャ「Test1」とよく似ている。
違う点について、説明しておこう。
y1 = Trim(Str(Year(Now()))): m1 = Trim(Str(Month(Now()))): d1 = Trim(Str(Day(Now())))
h1 = Trim(Str(Hour(Now()))): f1 = Trim(Str(Minute(Now()))): s1 = Trim(Str(Second(Now())))
strw1 = s1 & f1 & h1: strw2 = d1 & m1 & y1
dw = 10 * Val(strw1) + Val(strw2)
m = (dw Mod 317) + 1
の部分は、このプロシージャが呼び出された日付・時刻から、擬似的にランダムな数を作り出す過程を表している。
※ MOD演算子は、余りを求める演算子、割る数 317 は、317である必要は無い。
本来は、Randomize 文だけでも、よいはずだが、念のため、
For j = 1 To m
Calculate
Next j
で、m回、再計算させている。
本当の試行は、その後に、最大試行回数以内で、文字種エラーがないまで、繰り返しを行う。
パスワードシートの『パスワードの生成』ボタンに『Mパスワード生成()』を割り当ててある」
「テスト的に、何回ぐらい、試行回数を行っているかを調べてみたわ。
1回～7回程度だった。
先に計算したように、1回も文字種エラーが出ない確率は、p＝約0.36 なので、
1～（n－1）回は、エラーが起き、n回目に、エラーでなくなる確率は、
P＝（1－p）^(n－1)×p、と書き表せる。
その確率の累積（ΣP）を下図に掲げた。
細かい数値は、下の図の通りね」
「なるほど。
こうしてみると、最大試行回数を、100 と試しに置いてみたが、十分のようだな」
目次に戻る
「（4）節で文字種エラーを回避して、パスワードの文字列を生成することができた。
ここからは、制限する文字列との照合を行い、制限文字列を含まないようにしていこう。
作成する Excelブックの名前は、『password30.xlsm』じゃ。
※ password30.xlsmは、自作ものコーナーに置いてあります。
まず、シートを追加して、『制限文字』というシート名にしておこう。
A列～D列までに、#VALUE エラーが表示されている。
これは、生成された文字列に、E列の制限文字列が含まれていないときに表示される。
含まれる場合は、制限文字列が16桁の文字列に含まれている最初の桁位置（1～16）を表す数字が入る。
このために、後述するFIND関数を使っている。
次に、パスワードシートの方じゃ。
文字種エラーの列までは、前節までに解説したものと、デザイン以外は、同様じゃ。
V列7行目～25行目までに『英字→英小文字』という列が追加されている。
このV列には、T列に生成された文字列のうち、16桁の文字列の英大文字を英小文字に変換する、LOWER() 関数が設定されている。
LOWER関数は、数字や記号には、影響を与えない。
このV列は、制限文字列が含まれるか否かのチェックに利用される。
※制限文字列を含むか、否かのチェックは、16桁の文字列のみに対して行うことにしてある。
文字種エラー、制限文字列のチェックで、エラーがない文字列が、T2～W3の8個のセルに表示されるのじゃ」
「制限文字シートのセルのFIND関数は、具体的にどうなっているのかしら？」
「たとえば、A2のセルには、=FIND($E2,パスワード!$V$9,1)、と入れている。
FIND関数の第1引数、$E$2 は、上の図の制限文字列である、0000 を参照している。
第2引数のパスワード!$V$9は、パスワードシートのV9に表示される『数字のみの16桁の文字列』を参照する。
第3引数は、第2引数の文字列における検索開始位置の指定を行い、ここでは、先頭から検索のため、1としている。
第3引数を省略した場合は、1 と見なされる。
※ 第3引数の説明が抜けていましたので、追記しました。（2019/4/4 追記）
FIND関数は、第2引数内に第1引数が含まれている場合、第2引数の最初の桁位置を返す。
たとえば、=FIND("0000","1234560000789123",1)、は、7を返す。
これは、第2引数の1234560000789123、の7桁目から、第1引数の0000、が含まれていることを意味する。
また、含まれない場合は、#VALUE が返る。
なお、FIND関数では、第2引数に第1引数の文字列が完全に含まれる場合のみ、桁位置が返る。
以下同様に、
数字+英小文字では、=FIND($E2,パスワード!$V$13,1)
数字+英字では、=FIND($E2,パスワード!$V$18,1)
数字+英字+記号では、=FIND($E2,パスワード!$V$23,1)
が、それぞれのセルに入っている」
「なるほど、制限文字列が含まれないときは、すべて、#VALUEエラーが現れるのね」
「そうじゃ。
プログラムは、前節の文字種エラーを回避するプログラムを変更した。
前半は、ほぼ、同じじゃ。
制限文字シートの制限文字列の最大個数がパスワードシートのC4のセルの値から取得されている。
※ 変数 mを整数型（－32768～32767）から長整数型（－2147483648～2147483647）に変更し、未使用の変数kを削除しました。
また、併せて、エクセルブックも変更しました。2019/4/18 修正。
プログラムの中頃は、こうじゃ。
文字種チェックと制限文字列のチェックでは、
With 親オブジェクト名
ピリオド+子オブジェクト+ピリオド+プロパティ＝・・
End With
という構文を用いている。
これは、2つの異なるシートを行き来するときに、
Sheets("シート名").Select
のように書くと、画面がちらつくため、シートを選択しないために書いたのじゃな。
なので、Cells（行数，列数）の前に、. （ピリオド）を書く必要がある。
もちろん、Sheets（”シート名").Cells(m,1)、と書いてもよいのじゃが、煩雑だからな。
IsError関数は、先に紹介した、#VALUEエラーなどの場合、Trueを、エラーがない場合は、Falseを返す関数だ。
注意するのは、IsError関数で、エラーがない場合が、制限文字列が含まれるエラーの場合だ。
その場合は、再度、乱数を発生させて、繰り返し、再計算を要する」
「えーと、Calculate だと、ブック全体の再計算となり、シート名.Calculate だと、そのシート内での再計算となるのね」
「そのとおり。前述のように、
通常は、Excelのオプションの自動再計算がOnになっていると思うので、手動で再計算する必要は、ない。
ただ、今回は、RANDBETWEEN関数（RAND関数でも同様）をパスワードシート内の「乱」の行で利用している。
その乱数の値を再度、求める場合に手動であれば、F9キーを押すことになる。
それをプログラムで行う場合、単に、Calculate となる。
プログラムの最後の部分が次のようだ。
これは、T列8行目以降で求められている生成文字列をT2～W3の8つのセルに代入する命令じゃ。
その際は、一度、手動計算としている。
なぜならば、自動計算のままだと、代入により、乱数が再計算されるおそれがあるからじゃな」
「この制限文字シートの制限文字列に入っている文字列は、自由に編集できるのね？」
「いくつかの注意点がある。
0. シートの保護を有効にしてあるので、校閲タブから、シートの保護を解除する、
1. 英字は、すべて（半角）小文字で書く、（制限文字列に重複があっても大きな問題はない）
2. E列の既存の文字列の修正は、特に問題なし。
3. E列の既存の文字列の削除は、その行毎、削除する、
4. E列に文字列を追加する場合は、E列の既存の文字列の最後の行の次行から追加する、
5. 数字は、先頭にアポストロフィーを付けて入力する、
6. 4の追加後、A列～D列も前行の内容をオートフィルまたは単純にコピーすることにより、追加する、
7. E列の行番号が30万を超える場合は、パスワードシートのC3のCOUNTA関数内の行番号を増やす。
なお、長い文字列を追加しても差し支えないが、16桁を超えると制限文字エラーとはならないので無駄じゃ。
また、1～2文字の短い文字列を複数個追加すると、最大試行回数を超えてもエラーがなくならない場合がある。
その際は、最大試行回数を超えました、というメッセージが表示されるので、制限文字列を見直す」
「そんなに、制限文字列数が多くても大丈夫かしら？」
「実際、15万語程度でテストしてみたが、だいたい、数分で、文字列を生成できた。
今回の制限文字シートには、制限する数字と英字を約23,000語程度、載せている。
内訳は、
・ 4桁のよく使われやすい数字、
・ 中学で習う主な英語（3桁～13桁）、
・ ローマ字書きの日本語（一部を除き、3桁～6桁）、
としている」
「ブックを保存すると、生成されたパスワードは、どうなるのかな？」
「そのままだと、T2～W3の内容を手動でクリアしない限り保存されてしまうので、
・ ブックを閉じる前、
・ 開く前、
・ 保存する前、
の時点で、T2～W3 の範囲をクリアするように、下図のようなイベントプロシージャを書いた。
また、それぞれのタイミングで、各シートを保護している。
イベントプロシージャの記法については、マイクロソフトのヘルプが参考になる。
クラス（この場合は、Workbook）に、どのようなメソッドやプロパティがあるかは、
『オブジェクトブラウザ』を表示して調べることができる。
上の図は、WorkbookのSaveメソッドを参照しているところ。
※ アイコンは、次のような意味です。（https://www.relief.jp/docs/vbe-definition-icon-object-browser.html）による。
オブジェクトブラウザのヘルプボタンを押すと、ヘルプがインターネットのブラウザソフトで表示される。
上の場合は、サンプルコードを含んでいたので、役だった。
まだ、いろいろと、説明すべき点がありそうじゃが、今月は、これまでとさせてもらおうかの」
目次に戻る
最後に、利用上の注意点を挙げておきます。
・ ファイルは、ZIP形式で圧縮してあります。
・ Excelで開く際に、セキュリティの上の警告が表示される場合があります。
・ パスワードは、総当たりで攻撃されると、いつかは、必ず、あたります。
サイトの求める条件内で、文字種及び桁数が、なるべく多い文字列をパスワードとして使うようにしてください。
・ パスワードとして使えない記号が含まれている可能性があります。
よく利用するサイトに合わせて、適宜、記号表の記号は、追加、削除していただくことをお勧めします。
・ 面倒でも、サイト毎に、異なったパスワードをご利用ください。
・ 生成されたパスワードは、別途、ノートなどに記録してから、サイトで、登録・変更してください。
・ 制限文字シートに生年月日、ニックネームなどの個人情報を追加した際は、ファイルが第3者に渡らないように注意してください。
・ 本ファイルは、Excel の学習用に乱数を利用する方法を紹介したサンプルファイルです。
実際のサイトのパスワード作成のために利用することを含めて、ご利用は、自己責任にて、お願いいたします。
※ password30.xlsmは、自作ものコーナーに置いてあります。
目次に戻る
今回もご覧いただきありがとうございました。
次回も、本欄で元気にお会いできますことを願っています。
※旧ドメインは、2017/6/1で閉鎖いたしました。お気に入り、スタートページ等の変更をお願い申し上げます。
目次に戻る
作成日 2019/4/1、一部修正 2019/4/2、FIND関数の説明を追記 2019/4/4、冗長な表現を一部修正 2019/4/7
（2）節内の『Excel の RAND関数とRANDBETWEEN関数』のRAND関数に関する説明を一部修正 2019/4/17、
Mパスワード生成の一部のデータ型を変更 2019/4/18
本稿のタイトルを分かりやすく補足 2020/2/14
一部画像を縮小 2020/7/7
自作ものコーナーへのリンクを追加：2020/10/7
