マクロを使わないブック閲覧回数カウンタ
１．背景
WEBサイト上には「閲覧回数カウンタ」を良く見掛けます。そのサイトを何人の人が閲覧したかを表す数値です。一方、Excelにはそのような機能はありませんが「Workbook_Openイベント」などを使えば、累積カウンタのみで無く、閲覧した日時・閲覧者（＝PCのユーザー名）なども記録することが出来ます。その手法は様々なサイトでも紹介されています。
しかし、その閲覧履歴を作るためには「マクロ」が必要です。そしてファイル拡張子が「.xlsm」となり、ファイルを開く際にユーザーに対し「マクロを有効にするか否か」が求められます。つまりユーザーの意思（＝マクロを無効にしてファイルを開く）により、閲覧の足跡を残さないことが可能なのです。
そこで今回「マクロを使わない閲覧履歴」を紹介します。と言っても、閲覧ユーザー名や日付を記録する所までは辿り着くことが出来ず、「閲覧回数の表示」のみになってしまいました。中途半端で、何の役に立つのか分かりません。良い使い道を考えて頂けたら嬉しく思います。
２．システム概要
Excel側としてはマクロは使わず、図２－１のように、カウンタを表示するセルに「数式」が記されているのみです。数式の内容については「カウンタの数式」で説明しますが、大雑把に言うと数式内の「it-070count.php」が数値を表示するカウンタファイルで、且つそのファイルを呼び出すたびに表示値が＋１ずつ増える仕組みになっています。
図２－１
一般的にセルに記述された数式は、その数式が関連するセル等の値が変化すると自動的に再計算が実行され「常に整合性が取れた状態」を保持します。これはExcelなど表計算ソフトの最重要機能です。
今回の数式も「ブックを開く時には必ず再計算が実行される」ようにしてあり、再計算により既存の値に対し＋１をした数値（閲覧回数）が表示されることになります。
なお御存知の通り、再計算が行われるのはブックを開いた時だけでは無く、強制的に再計算（F9キーなど）が実行できたり、セルへの入力・更新・削除をしても基本的に再計算が行われます。そのたびにカウンタが増えることに注意願います。
一方、Excel側から呼び出されるカウンタファイル「it-070count.php」は、Webサーバー内に置きます。社内専用でもグローバルでもOKですが、URLが固定している必要があります。また、カウンタファイルはPHP言語で作られるため、WebサーバーがPHPを解釈できる環境になっている必要があります。
 実際に動かしてみると、不思議な現象に気が付きました。Excelのセルに表示されている閲覧回数よりも、PHPファイルの数値の方が「１多い」のです。 確認の仕方としては、サーバー上のPHPファイルをエディタで開いたままとします。その状態でExcel側で再計算をさせ、例えば「5」が表示された時には、エディタ上のPHPファイルの値が「6」となっているのです。 図３－１でも説明するように、PHP処理されて変更された値がExcelに返ってくるはずなのに、変更する前の値をExcelが受け取っていることになるのです。 このカラクリは分かりません。もしかしたら自分で自分を書き換えている事により、送信後でないと書き換えが完了しないのかもしれません。とりあえずExcel側としては、再計算を実行するたびに＋１ずつ増えていくのは確かです。 |
「サンプルファイル」では、カウンタファイルは「このサイト」に置いています。ですので、サンプルファイル上のカウンタは「アクセスした全ての人が、サンプルファイルを開いた・または再計算を実行した総回数」を表すことになります。
なお再計算を連打しても、連打の数だけPHPファイルを呼び出す訳では無さそうで、適当な間隔は必要そうです。そうは言ってもサンプルファイルで連打されると、このサイトの負荷が高まり、ダウンする可能性があるので御勘弁願います。
３．プログラムの流れ
Excelのセル（サンプルファイルではB3セル）には、WEBサーバー上のカウンタファイルを呼出し、その値を取得する数式が書かれています。この数式が再計算されると、カウンタファイル「it-070count.php」をWEBサーバーに要求します。
図３－１
要求されたWEBサーバー側では、カウンタファイルの拡張子が「.php」となっているので、そのファイルに書かれているコードをPHP処理します。処理内容は「現在表示されている値を、＋１増やした値に変更」することです。
PHP処理後、要求元であるExcel側に送付します。受け取ったExcel側は「＋１増やされた値」をセルに表示します。
|
PHPはサーバーサイドスクリプトの１つです。サーバーサイドスクリプトとは「ユーザー（クライアント）がサーバー上にあるファイルを要求してきた場合、ファイルそのものを送信するのでは無く、サーバー上で処理を行い、その結果をクライアント側に送信」するものです。 今回の場合Excel側からサーバー上の「it-070count.php」というファイル（内容は図５－１）を要求していますが、サーバーから戻ってくるのは、処理を行った後の値（既存の数値に＋１をした数値）だけとなります。 なおサーバーサイドスクリプトとしては他にPerlやRubyなどもありますが、私が唯一かじったのがPHPだったため今回はPHPを使いました。 |
４．カウンタの数式（サンプルファイルではSheet1）
図２－１のB3セルの数式は「=VALUEまず、数式の本体は「WEBSERVICE("https://atsumitm.iobb.net/it/it-070count.php")」です。WEBSERVICE(URL)関数は「引数URLで指定されたWebサービスにアクセスし、データを取得する」ものです。
ここではURLは「https://atsumitm.iobb.net/it/it-070count.php」ですので、当サイトにある「it-070count.php」というカウンタファイル（図５－１）を指します。「it-070count.php」は数値を表示するページで、且つ呼び出されるたびにその数値を＋１ずつ増やすようにできています。
WEBSERVICE関数の後ろの「& IF(TODAY()>0,"","")」は、ファイルを開く時に必ず再計算される（＝it-070count.phpを呼出す）ようにするためのものです。
実は今回使用しているWEBSERVICE関数は、一度実行された結果を計算結果としてキャッシュしてしまうため、自動再計算は発生してくれません。これではカウンタになりませんので、少なくともファイルを開く時には再計算してくれる「日時に関する関数」を使用することにしました。
今回TODAY関数を使っていますが、NOW関数でもOkです。再計算が発生してくれれば充分ですので、その得られる結果は「""（長さゼロの文字列）」という「WEBSERVICE関数で得られるカウンタ値を邪魔しない」ものに加工しています。
it-070count.phpから得られる数値には、図４－１の左側のように「改行」が含まれています。
図４－１
改行が含まれてしまう原因は、it-070count.phpのファイルのコード内に空行があるためですが、分かり易さを重視するとどうしても空行が入ってしまいます。そのため「CLEAN関数」で「不要な改行を削除」し、図４－１の右側のようにします。
また、it-070count.phpから得られる数値は「文字列型」ですので、「VALUE関数」で、文字列→数値に変換をしています。
但しこの数値変換処理は、この閲覧回数を数値として活用するためには必要ですが、単純に回数を眺めるだけでしたら、文字列のままでもOKと思います。
|
なおWEBSERVICE関数を使ったブックを開く場合、「インターネットから入手したファイルは、ウィルスに感染している可能性があります。編集する必要がなければ保護ビューのままにしておくことをお勧めします」との警告が出ることがあります。また「Webサービス機能は無効になっています」という警告が表示されることもあります。 どちらもセキュリティを守るための警告ですが、もし警告メッセージが発生した場合は「編集を有効にする」や「コンテンツの有効化」をクリックするOKです。 但しより用心深い方は、ご自分でExcelの数式とPHPファイルを作って試されるのが良いかと思います。 |
５．カウンタ用PHP（サンプルの it-070count.php）
PHPファイルは、基本的にテキストファイルです。メモ帳などのエディターを使って作成するものなので、ExcelのVBEに下記コードを貼り付けても動きません。また、ここではPHP言語について詳しく説明することはしませんし、私の知識量からみても正しく説明できるとも思えません。他のサイトや書籍類で確認・知識修得をして下さい。
５－１．コード内容
Excelの数式から呼び出される「it-070count.php」のコードは、下記の図５－１です。- 1
- <?php
- $counter_file = 'it-070count.php';
- $counter_lenght = 8;
- $fp = fopen($counter_file, 'r+');
- if ($fp) {
- if (flock($fp, LOCK_EX)) {
- $counter = fgets($fp, $counter_lenght);
- $counter=1+$counter;
- rewind($fp);
- if (fwrite($fp, $counter) === FALSE) {
- echo ('書き込み失敗');
- }
- flock ($fp, LOCK_UN);
- }
- }
- fclose ($fp);
- ?>
１行目の「1」が、表示されるカウンタ値であり、３～２３行目のPHP言語によって＋１増加されるカウンタ値です。Excelから呼び出された時には、この「1」がExcelに戻されます。
３行目の「<?php」と２３行目の「?>」で囲まれた部分がPHP文となります。
まず４行目「$counter_file = 'it-070count.php';」で、カウンタ値が存在するファイル名（＝自分）を設定します。左辺の「$counter_file」は変数です。先頭の「$」が変数であることを表しています。
また、右辺のファイル名を「'（シングルクォーテーション）」で囲っていますが、これはExcelで文字列を「"（ダブルクォーテーション）」で囲むのと同じ意味です。また、１つの実行コードの後尾には「;（セミコロン）」を付けます。
自分のWEBサーバーにPHPファイルを置く際にファイル名を変更する場合には、この４行目のファイル名も同時に変更する必要があります。
５行目「$counter_lenght = 8;」は、取得する文字数を設定します。この場合は８文字ですので「99,999,999」カウントまでが対象のように思いますが、実際にやってみると１桁少ない「9,999,999」までが正しいカウント対象となるようです。
「9,999,999」の後は「10,000,000」→「10,000,010」→「10,000,020」と、何故か下２桁目に対して増加をしていってしまいます。もちろん５行目の文字数（＝桁）を増やすと「正しくカウントする桁数は増える」ので、変数の有効桁数の問題では無く、文字の取得時に１桁少なく取得するようです。
６行目「$fp = fopen($counter_file, 'r+');」は、４行目で設定したファイルを「読み書き用（r+）」として開きます。開いたファイルは変数fp（$fp）に代入しておきます。
８行目「if ($fp) { 」は、ファイルが正常に開けていた場合、９～２０行目の { } 内を実行するという意味になります。
９行目「if (flock($fp, LOCK_EX)) {」は、ファイルを排他ロック（LOCK_EX）し、他から操作されないようにします。このロックは１９行目「flock ($fp, LOCK_UN);」でロック解除（LOCK_UN）するまで続きます。
１０行目「$counter = fgets($fp, $counter_lenght);」は、ファイルから設定文字数（$counter_lenght）分を取得し、変数counterに代入します。ファイルを開く時（６行目）に「r+」を使用して開いていますので、読み込むファイルポインタはファイルの先頭にありますので「ファイル先頭から、設定文字数分」を読み取ります。
１１行目「$counter=1+$counter;」で、読み取った文字列（＝数値）に１を足し、元の変数$counterに代入します。
１３行目「rewind($fp);」では、ファイルポインタを先頭に移動します。
１５行目「if (fwrite($fp, $counter) === FALSE) { 」の中の「fwrite($fp, $counter)」で、＋１増やした数値をファイルに書き込みます。もし書込みが失敗（=FALSE）した場合には、１６行目「echo ('書き込み失敗');」で、失敗の文字列を書き込みます。（今回の場合、自分自身を書き換えていますので、どういう場合に１６行目が実行されるのか分かりません。もしかしたら、不要なコードなのかもしれません。）
１９行目「flock ($fp, LOCK_UN);」でロック解除（LOCK_UN）します。
２２行目「fclose ($fp);」で、ファイルを閉じます。
５－２．出力として、改行を出さない工夫
Excel側の数式でCLEAR関数を使って「改行を削除」していますが、PHP側のコードを工夫すればCLEAR関数は不要になります。PHP文の範囲は「<?php」から「?>」までで、今回はPHP文の内部で文字列の生成をしていませんので、PHP文の範囲はExcelで言えば「""（長さゼロの文字列）」に置換されます。
ですので、図５－２のように「数値の後には、改行を１つも入れない」「PHP文の終わりの ?> の後にも改行を入れない」ようにすれば、Excel側へ送られてくる文字列（閲覧回数の数値）には、改行が入らないことになります。
- 1<?php
- $counter_file = 'it-070count.php';
- ：
- ：
- ?>
しかし、コードとしては非常に読み難くなりますので、Excel側でCLEAR関数を使って改行を消した方が賢明と思います。
５－３．数値を先頭行に記載する必要性
また「数値はファイルの先頭行（１行目）に記述」する必要があります。例えば、図５－３のように数値の上に空行を設けてしまうと、図５－１の１０行目の読み取り（fgets文）で１行目の空白（＝ゼロ相当）を$counterとして読み込み、１５行目の書込み（fwrite文）で先頭の１行目にゼロに１を足した「１」を書き込むことになってしまいます。
- 1
- <?php
- $counter_file = 'it-070count.php';
- ：
- ：
そうなるとCLEAR関数を使って改行を削除したとしても、図５－３ならば「1」の次は、１行目の１と３行目の１で「11」、その次は１行目の２と３行目の１で「21」という数値になってしまいます。このため、ファイル先頭行を数値行にする必要があります。
６．最後に
マクロ付きExcelファイルの拡張子が「.xlsm」になったExcel2007以降、確かに安全性は高まったと思いますが、反面ユーザーがマクロを無効にして起動した時のことを考えなければならなくなりました。今回もその１つです。マクロが起動さえすれば何ということもないのに、PHPまで使ってもこれだけ？という気持ちです。それだけマクロを使えば自由に色々なことが出来るという意味でしょうが、しかしマクロでは出来ないことも一杯あります。その不自由さと自由さの境界を見たような今回でした。
マクロを使わないブック閲覧回数カウンタ（it-070.xlsx）
カウンタ（it-070count.txt）・カウンタファイルはダウンロードした後、拡張子を.txt → .php に変更して下さい。
・PHPが動作するWEBサーバ上にPHPファイルを置き、Excelの数式をそのアドレスに設定して下さい。
・ファイルOpen直後は式のエラーが出ますが、再計算実行(F9キー)により正しくカウントします。