Excelでシート内の文字列を見ても簡単には解らなくしておく方法

知っ得かもしれないExcelの癖－その2

2019.12.25 2019.04.26

この記事は約5分で読めます。

「知っていると得するかもしれないExcelの癖」シリーズ第二十一回、テーマは「文字列暗号化」になります。
Excel 2016バージョン1903を使用して確認しています。

シート上に秘匿性のある情報を載せる場合、基本的には非表示にしたうえで「シートの保護」をすることになる認識です。
ただし「シートの保護」につきましては、強制解除の方法がネット上に流布されていることを考えると、例え「シートの保護」が解除されたとしても簡単には解らなくする工夫についてまとめて置きたいと思います。

文字列暗号化の方法
数式の説明
まとめ

文字列暗号化の方法

見出しの内容につきましては、下記のForguncy（フォーガンシー）Excel業務カイゼンブログの記事に詳しい方法が掲載されていたのですが、現在は見れ無くなってしまいました。
取り合えず掲載されていたロジックはそのまま使わせていただいております。

https://www.forguncy.com/blog/20180216_encrypt#

話を少し脱線するのですが、Forguncyを開発されているグレープシティ株式会社さんの「Developer Tools」は20年強前のWebアプリケーション開発で、当時出始めのActiveXを使用して印刷フォームを作ったり、グラフを作ったりするための、ActiveReportsやActiveChartなどのツールを使わせていただきました。その節は大変お世話になりました。

という昔の話はこのぐらいにして話を元にもどします。

暗号化の方法は、ほぼほぼ上記の記事で説明されていたUNICODE関数とUNCHAR関数を使用する方法を使わせていただいております。

今回この暗号化方式を使用した下記のような書式を作成して見ました。

5つの解答欄すべてに入力が終わると、正答、解説と〇×による正誤判定が表示されるようにする。
- 正答と解説は、別セルの正答数式列と解説を参照する形にする。
正答数式では、解答欄がすべて入力された時は、暗号化されている「答え」を復号化して表示する。
暗号化は「答え」をUNICODE関数で文字コードにした後、設定された加算値を加えた上でUNICHAR関数で文字に変換する。

なお下記ExcelシートはMicrosoftサポート「OneDrive から Web ページやブログに Excel ブックを埋め込む」を元に設定をしています。
Excelブックを埋め込み方式で表示させるにあたり、下記の点にご注意ください。

このシートはExcel OnlineでOneDrive「https://onedrive.live.com」にアクセスをして表示します。
iframeを使用していますので、iframeがご利用になれない環境の場合は、別途ファイルをダウンロードしてご確認ください。

上記埋め込みExcelシートの説明

解答列B2:B6セルは入力規則が設定されていますが、1から5の数字を入力することができます。なおシートは保護されています。
右と左の2つのテーブルは、本来であれば右のG列からK列は非表示にすべきですが、説明のために表示しています。
右テーブルの答え列が暗号化された正答になります。
5つの解答すべてに1から5のいづれかの数字を入力すると、正答(C列)、解説(D列)、判定(E列)、および正答数式(G列)すべてが表示されます。
2つのテーブルはそれぞれ「テーブル1」「テーブル2」という名前になります。

数式の説明

埋め込みExcelシートで使用している数式は、正答(C列)、解説(D列)、判定(E列)、正答数式(G列)、暗号化(J列)の5つになります。

①正答(C列)の数式

C列は右のテーブルの正答数式(F列)を参照する数式です。
正答数式(F列)で解答列がすべて入力されたか?を確認するようにしていますので、正答数式がスペースの時は正答もスペースになります。

当然、数式はセル参照でも記述することもできますが、シートの保護が解除されたとしても、構造化参照について理解していないと数式の意味が分からないようにしていますのでご容赦ください。なお使用する数式は全行同じになります。

=テーブル2[@正答数式]

②解説(D列)の数式

D列の解説は、正答欄が表示されたら、H列の解説記入を表示するようにします。なお使用する数式は全行同じになります。

=IF([@正答]="","",テーブル2[@解説記入])

③判定(E列)の数式

E列の判定も、正答欄が表示されたら、B列の解答とI列の答えとを比較して〇×判定を表示するようにします。なお使用する数式は全行同じになります。

=IF(テーブル2[@正答数式]="","",IF(TEXT([@解答],"#")=[@正答],"〇","×"))

比較する際に、解答欄は数値での入力となるため、暗号化文字列を復号化した答え欄のデータ形式とは異なります。
そのためB列解答欄をTEXT関数で文字列に変換してから比較しています。

④正答数式(G列)の数式

解答がすべて入力されたか?をCOUNT関数を使って確認します。
その後「答え」を復号化するために文字コートにした後、加算値をマイナスしてからUNICHAR関数で文字に戻します。なお使用する数式は全行同じになります。

=IF(COUNT(テーブル1[[#データ],[解答]])=5,UNICHAR(UNICODE([@答え])-[@加算]),"")

⑤暗号化(J列)の数式

正答を暗号化するために、ます最初に「答え」欄に正答を1から5の数字で入力します。
つぎに、それに対して、下記数式を使って文字コードに変換してから加算値を加え、その計算値をUNICHAR関数で暗号化文字に変換します。
加算値は行ごとに変えられるように設定しています。なお使用する数式は全行同じになります。