「知っていると得するかもしれないExcelの癖」シリーズ第二十二回、テーマは「確定値を保持する」になります。
Excel 2016バージョン1903を使用して確認しています。
本テーマで取り上げているのは、「XLSXファイルで一度入力を確定させた値を変更されることなく保持したい」というマクロを使わない場合には少し無理なように思える内容になります。
具体的に「どのようなケースでそのような無理な事をやろうとしているか?」といいますと、例えばExcel(XLSX)シートに質問に対する解答を入力してもらった後に、その解答を一度確定させた後は、修正されることなく保持したいといったケースです。
しかし「100%の完全性があるか?」と言われるとそうではなくて、「シートの保護」や「ブックの保護」は使用する必要があるという事の他に、XLSXファイルではどうしても防げない事が一つありまして、それは事前にファイルをコピーして元ファイルを残されてしまう事です。
「なぜコピーされることを気にするのか?」といいますと、コピーを残されてしまうと、解答に間違いがあったかもしれない最初のファイルを破棄して、残されたファイルを使い、最初からやり直しをする事ができるためです。
これは如何ともし難い事でありまして、例えばファイルを事前に配るようなケースではコピーを残されることは不可避になります。がしかし、パソコンにファイルをセットした状態で解答してもらうようなケースであれば、このようなやり方でも有効になる可能性があります。
サンプルファイル(XLSX)の説明
今回使用するシートはひとつではなく、「解答」シートと「ピボット」シートという2つのシートを使っています。
「ピボット」シートのなかには、少し数が多いのですが3種類のピボットテーブルを設定しています。
2つのシートに分けているのには理由がありまして、前々回の本シリーズ第二十回「Excelピボットテーブルの自動更新でセルとは別にデータを保存する」に記載していますが、ピボットテーブルが含まれるシートを保護するとピボットテーブルの「更新」をすることができなくなります。
ネットを調べると、シートを保護した状態でもマクロ(VBA)を使用して対応するやり方が散見されますが、本シリーズではマクロ(VBA)は使用しない事にしています。
従いまして、ピボットテーブルを保護するためにはシートを分けて、ピボットテーブルを含むシートを非表示にしたうえで、ブックの保護をすることでピボットテーブルを守るしか方法がありません。
なお今回は画面ショットだけでは「どのように動くのか?」をお伝えすることは難しく、そのため実際のサンプルファイルをダウンロードできるようにいたしております。
是非一度サンプルファイルをダウンロードして動きをご確認いただければ幸いです。
- ページレイアウトのテーマはoffice、フォントは游ゴシックを使用
- マクロ(VBA)は使用していません。
- 「解答」シートは一部の列を非表示にしてシートの保護を設定していますが、パスワードは未入力です。
- 「ピボット」シートは非表示にして、ブックの保護を設定していますが、パスワードは未入力です。
今回使用している「5つの設問に対して解答を入力していただく」シートは、前回のシリーズ第二十一回「Excelでシート内の文字列を見ても簡単には解らなくしておく方法」で使用したものを流用しています。
前回と同様にI列「答え」の文字列は暗号化をしていますが、仕組みは前回と同じになりますので説明は省略いたします。
今回A1:K6のセル範囲で数式が前回と異なるのはG列「正答数式」だけになります。この数式の説明は後段の「まとめ」で行います。
なお前回にはなかった表や列がM1:P6セルに追加されていますが、特にこの表に設定されている数式が今回のテーマとなります。具体的な数式は後段でご説明いたします。
ピボットシートに設定されているピボットテーブル
「1から3」まで3種類のピボットテーブルを使用しています。
「ピボット1」は解答シートの「M1:O6セルの表」を参照しています。
「ピボット2」はピボットシートの「E1:F2セルのテーブル3」を参照しています。
「ピボット3」はピボットシートの「H1:I2セルのテーブル4」を参照しています。
①「ピボット1」が参照している表
「ピポット1」が参照する先がテーブルではなく表になっているのには理由があります。
テーブルは見出し行の存在が必須なのですが、表であればそのような縛りがありません。そのために表を使用しています。
その具体的な内容は後段でご説明いたします。
表の「M列はテーブル1のA列設問」「N列はテーブル1のB列解答」をそれぞれ参照しています。
「O列の更新日時」ですが、本シリーズの前々回第二十回「Excelピボットテーブルの自動更新でセルとは別にデータを保存する」でご説明しているタイムスタンプのような機能を流用しています。
A列テーブル1の解答を入力すると、更新日時は今の日時に変わるのですが、「データ」タブから「すべての更新」をクリックしないとピボット1に保存されない、つまりデータとして保持されないので実用性は乏しいのですが、是非お試しいただければ幸いです。
M列の数式は下記のようになります。
=テーブル1[@設問]
テーブルの構造化参照につきましては、シリーズ第九回「Excelでテーブルに数式をセットする際の注意点」をご参照いただければ幸いです。
N列の数式は下記のようになります。
=IF(テーブル1[@解答]="","",テーブル1[@解答])
つぎに、表の右にあるP列「Cd」ですが、実はピボット1に保持された「解答」を参照する数式がセットされています。
P列の用途としては、B列の「解答」とピボット1に保持された「解答」とを比べて変更されているか?を確認するために使用しています。
P列の数式は下記のようになります。
なお「Pテーブル1」はピボット1を参照する時の絶対参照セルになりますが、そのセルを「名前の定義」をして使用しています。
今回定義している名前の一覧につきましては後段の「まとめ」でご説明いたします。
=GETPIVOTDATA("解答",Pテーブル1,"設問",M2)つぎにO列ですが、解答が未入力であれば「2000/1/1 12:00:00」をセットします。
B列の「解答」とピボット1に保持された「解答」とが異なる時は、NOW関数の値をセットし、等しい時はピボット1に保持された「更新日時」をセットします。
=IF(N2="",DATE(2000,1,1)+TIME(12,0,0),IF(N2<>P2,NOW(),GETPIVOTDATA("更新日時",Pテーブル1,"設問",M2)))②「ピボット2」が参照しているテーブル
このピボットテーブルは開いているファイルのファイル名を保持するために使用しています。
E1セルの値は実は何でも良いのですが、今回は連番想定で「1」にしています。
ピボットテーブルですが1つの項目だけでは設定することができません。そのために「No」項目を付け加えてピボットテーブルとして成り立つようにしています。
つぎにF2セルの数式ですか、これは「シート名」を取得する数式の応用で、CELL関数とFIND関数を使い「開いているファイルのファイル名」を取得しています。
実際の数式は下記のようになりますが、この数式はこのままコピペして汎用的に使うことができます。
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-(FIND("[",CELL("filename"))+1))なぜ「ファイル名を保持するか?」といいますと、一連の流れの中で一旦ファイルを閉じてファイル名を変更する操作をしてもらうのですが、その操作(落とし上げ)が確実にされたことを確認するために保持しています。
- ファイル名を変更するためにはファイルを閉じる必要がある。
この当たり前のことが今回のテーマを実装するうえで大きな意味をもつことになります。
③「ピボット3」が参照するセル
「No」項目があるのは「ピボット2」と同じ理由になりますので説明は省略いたします。
では、テーブル4のヘッダー項目は「どこを参照しているか?」と言いますと、「表」の見出し行のO1セル「更新日時」の文字列になります。
ピボット3には「更新日時」の項目だけを保持するようにしていますが、これはM1セルでもN1セルでも、とれか一つどれでも良かったのですが、このなかで一番長い文字列を選択しました。
この「表」の見出し行は、ピボット1の「フィールド名」として使用しているために、この見出し行がスペースに置き換わってしまうと、ピボット1を「更新」した時に下記のようなエラーとなります。
そのピボットテーブルのフィールド名は正しくありません。ピボットテーブル レポートを作成するには、ラベルの付いた列でリストとして編成されたデータを使用する必要があります。ピボットテーブルのフィールド名を変更する場合は、フィールドの新しい名前を入力する必要があります。
このメッセージが表示されるために、ピボット1は「更新」することができず、見出し行がスペースに置き換わった後は「解答」にセットされたデータを変更することはできません。
ちなみに、もしも「ブックの保護」が解除されてしまった場合は、ピボットシート自体を削除することはできます。ただし、削除したファイルを提出されてきた時は、「なぜ削除したのか?」を確認し、再度やり直してもらう事になる認識です。
「表」の見出し行の「更新日時」にセットしている数式は下記のようになります。
=IF(OR(C更新判定2,P更新日時3=""),"","更新日時")
- B列の解答とピボット1の解答がすべて等しい
- ピボット3の見出し項目がスペース
上記のどちらか一方でも成立する時は見出し行はスペースになります。
ピボット3はピボットテーブルのオプションで「ファイルを開くときにデータを更新する」にチェックをする
すべての「解答」を入力した後で、「データ」タブの「すべての更新」をクリックする事が必要です。
「すべての更新」をクリックすると、ピボット1に解答は保持されるとともに、「表」見出し項目はスペースに置き換わります。
これにより、ピボット1に保持されたデータは更新できなくなります。
ファイル名を書き換えるためにファイルを閉じる必要がありますが、ファイルを開くときにピボット3のデータはスペースで更新されます。
そして、一旦ピボット3がスペースに更新されると、どのような操作をしても「表」の見出し行は元に戻すことができなくなります。
この動きが、今回のテーマの「一番の肝」となります。
サンプルプログラムで実装している機能
実装している機能は下記になります。
- 解答欄(B2:B6セル)のすべての入力が終わったら、「データ」タブから接続リボングループのなかの「すべての更新」をクリックするようにナビゲートするメッセージを表示する。
- 「すべての更新」をクリックすることで、「ピボット」シートに設定されている3種類のピボットテーブルすべてが「更新」されます。
- 「すべての更新」がクリックされると、ピボット1の解答とB列の解答の一致することになります。
- ピボット1の解答とB列の解答の一致するとピボット1が参照している「表」の見出し項目がスペースに置き換わります。
- ファイル名が変更されたか?の確認ができるように、現在開いているファイル名を「すべて更新」をクリックした時に、ピボット2に保持します。
- ファイル名が変更されていない場合は、正答を見せることはできません、
- ファイルを開く時に、ピボット3だけは更新するようにピボットテーブルのオプションを設定します。
- これにより、「すべての更新」をクリックした事でスペースになった見出し項目がピボット3に保持されます。
- 一度ピボット3かスペースで保持されると、他にどのような操作をしても通常の方法ではピボット1を更新することはできなくなります。
まとめ
まだご説明できていない事がございますが、かなり長くなりましたのでまとめに入らせていただきます。
G列の「正答数式」にセットしている数式は下記になります。
=IF(AND(C更新判定2,COUNTBLANK(C消去範囲1)=3,テーブル3[ファイル名]<>Pファイル名2,P更新日時3=""),UNICHAR(UNICODE([@答え])-[@加算]),"")
かなりガチガチに確認していますが、
- B列解答がすべてピボット1の解答と一致する、
- 「表」の見出し行がスペースになっている、
- ファイル名が変更されている、
- ピボット3の見出し項目がスペースになっている
以上4つがすべて成立した時に、正答を表示させています。
つぎにO7のセルですが、これはB列の解答がすべて入力されていて、かつ、ピボット1に保持されている解答と等しい場合に「TRUE」になる数式で、「C更新判定2」と名前の定義がされています。
=AND(COUNT(N2:N6)=5,N2=P2,N3=P3,N4=P4,N5=P5,N6=P6)
なお、本サンプルで使用している名前の一覧は下記になります。
- 「C消去範囲1」は「表」の見出し項目のセル範囲になります。
- 「Pファイル名2」はピポット2の「行」項目で、これはGETPIVOTDATA関数では取得できないので、名前を定義して参照しています。
- 「P更新日時3」も同様にピボット3の「行」項目につれた名前になります。
最後に、A8セルとA9からA12セルに、操作ナビゲーションメッセージを表示させています。
=IF(AND(COUNT(テーブル1[[#データ],[解答]])=5,NOT(C更新判定2),P更新日時3<>""),"※「データ」タブの「すべての更新」をクリックして下さい。","")
- 解答がすべて入力されている
- B列解答とピボット1の解答がすべて一致している
- ピボット3の見出し項目がスペースではない
- まだ、ファイルの落とし上げがされていないので、ピボット3には正しい項目名がセットされています。
上記が成り立つときに「データ」タブのすべての更新をクリックするようにナビゲーションします。
その後で、「表」の見出し項目がスペースになっている時は、ファイルの落とし上げをナビゲーションするメッセージを表示させます。
=IF(COUNTBLANK(C消去範囲1)=3,"お疲れ様です。","") 以下省略
最後は駆け足になりましたが、最後までご一読いただきありがとうございました。
