Googleスプレッドシート 自動でタイムスタンプを入力する3つの方法 -3 【GASなし 関数で出来る！】
Googleスプレッドシートで使える 自動タイムスタンプ機能の紹介です。通常の GASを使ったものに加え セルが保護されている場合の 少しアレンジしたコードやシート関数での実現方法なども紹介します。
今回は全3回の最後、その3 GASを使わない関数での 実現方法 です。
前回の記事はこちら。
Q. 指定したシートの B列にコメントを入れたら、A列にタイムスタンプ（日時）を自動で入れたい
・シート1 という名前のシートが対象
・B列が入力、編集されたら同じ行の A列に タイムスタンプを自動入力
・2行目がタイトル行なので、3行目以降が対象
・B列は手入力のみとし、コピペや複数セルの一括編集への考慮は不要
・B列のコメントを削除したら、A列のタイムスタンプも消したい
今回のタイムスタンプお題の要件です。
このお題に対して 3つのアプローチを考えてみます。
GAS でオーソドックスなタイムスタンプを作成する (onEdit）
タイムスタンプ(A列)をユーザーに編集させたくない (トリガー設定）
GASを使わない タイムスタンプ。（関数でも出来る？）
ここまで GASを使う方法 1,2 を回答・解説しました。今回は
3. GASを使わない タイムスタンプ。（関数でも出来る？）
を考えてみましょう。
A3.GASを使わない タイムスタンプ。（関数でも出来る？）
ネット上の情報では、今回のようなタイムスタンプ機能を使いたい場合、ほぼ Excel なら VBA、Googleスプレッドシートなら GAS を使った方法の紹介となります。
「再計算されてしまう関数では タイムスタンプが更新されてしまうので、GASじゃないと不可能。」 と書いているサイトもあります。
本当にそうでしょうか？
NOW() 関数は、更新時に常に再計算
タイムスタンプを関数で実装するにあたり、一番使えそうなのは NOW() という関数です。これは、その名の通り 今（現在の日時）を返す関数で、Excelにも昔からあった関数です。
試しにこれを入れてみましょう。
関数を入力した直後は、今の日時がセルに入るものの、確かに 他のセルを編集するたびに 再計算 されて 日時が更新されてしまいます。
ちなみに、セルをまったく編集しなかったとしても、スプレッドシートを開きなおす際も再計算されてしまうので、開くたびに日時が更新されます。
これは仕様なので、残念ながら 自動再計算を止めることはできません。
Excelの場合は 自動計算を 手動に切り替えることが出来ますが、特定の関数だけ 自動計算をオフにすることが出来ないため、ブック内のすべての数式が 計算されない状態になります。そもそも 手動にしちゃったら、最初のNOW関数すら機能しません。
このままタイムスタンプとして実装しようとした場合、たとえば A3に以下の式を入れて、
=IF(B3="",,NOW())A3を 下にオートフィルした状態で B列を更新してみると
全てのタイムスタンプが、最後にセルを更新した 日時になってしまいます。
これだとタイムスタンプとしては使えないですね。
タブー（禁術）？ 循環参照・反復計算 を使って計算をロック
じゃあどうすれば良いか？ 無理なのか？
正攻法では無理なので、禁術？の 循環参照・反復計算 を使ってみましょう。
循環参照とは、数式を入れたセル自身を参照したり、そのセル自身を参照する式が入ったセルを参照することです。
簡単に言うと、上の画像みたいに A2セルに =A2 と入れた状態です。
実際に機能として存在するので、 循環参照 は 禁術でもタブーでもないんです。使ったからと言って、等価交換で体の一部をもってかれたりもしません。
でも、ほとんどの人が 使わない機能でしょうし、Excel先生にお叱りを受けたトラウマ？から、関数が得意な人でも使うのに抵抗感のある人が多いんじゃないでしょうか？
勇気をもって、一歩踏み出して新しい世界に突入してみましょう。
循環参照のエラーを解消するには、設定で反復計算を ON にする必要があります。
ファイル ＞ 設定 ＞ 計算 で、上記画面が開くので、
タイムスタンプを実現するために
反復計算を オン
最大反復関数を 1
と設定します。
最大反復回数は、今回の場合はデフォルト値でもいいんですが、
値がループループする～♪
ですっごい無駄なので必要最低限が理想的です。
今回は1回再計算すればOK。
反復計算をオンにして、他の関数には影響ない？
まったく影響ありません。安心して使ってください。
もちろん、別の循環参照の式を使っている場合は影響ありますが、そんなケースはほぼ無いでしょう。
シート関数で タイムスタンプを実現する数式は これ！
設定を保存したうえで、A3に以下の式を入れて 下にオートフィル。
これで準備完了です。
意外と簡単な式で 拍子抜けですかね？
=IF(B3="",,IF(A3>0,A3,NOW()))
このやり方は、実は Excel でも可能です。
ただし、式の中の IF(A3>0 の箇所は、 Excelだと IF(A3<>"" とします。
=IF(C3="","",IF(A3<>"",A3,NOW()))
この部分、Googleスプレッドシートで IF(A3<>"" と書くと 1899/12/30 0:00:00 が書き込まれちゃうんですよね。空に見えて 0が入ってる扱いみたいです。仕様の違い？？
だから、A3（自分自身のセル）にすでに タイムスタンプが入ってるかどうかの判別をGoogleスプレッドシートの場合は IF(A3>0 としてあげるのがポイントでしょうか。
本当にこれで タイムスタンプが 機能し、勝手に更新はされないかを テストしてみましょう。
きちんと タイムスタンプが 入力され、かつ他のセルが編集されても再計算されず、入力した日時が 維持されました。
コピペによる複数セルの編集でもさくっと動作しますし、コメントを消すと タイムスタンプもリセットされます。
もちろん スプレッドシートを一度閉じて開いたり、リロードしても タイムスタンプ は更新されません。
関数なので Ａ列が保護されていても問題ありませんし、スマホのアプリ版でもサクサク動作します。
シート関数でタイムスタンプの弱点
GASより こっちの方がよくない？と思うかもしれませんが、
唯一、満たせない要件が以下の太字部分です。
・B列が入力、編集されたら同じ行の A列に タイムスタンプを自動入力
B列の入力（空白 → 空白以外）には対応できますが、元々文字が入力されているセルが「編集」された際のタイムスタンプの更新は対応できません。
数式では B列のセルが 空白 か 空白以外かの切換えのみを判別しているので、 セルが更新されたかどうか は見分けることができないのです。
コメントの追加でタイムスタンプを更新する為には、一度 コメントを削除して タイムスタンプをリセットする必要があります。
この部分は運用（ユーザーへのルール周知）でカバーしなければなりません。
Arrayformula でも関数タイムスタンプは使える？
シート関数で タイムスタンプが実現可能なら、 Arrayformula との組み合わせはどうなのか？ 気になるところだと思います。
なんとこれ、Arrayformula と組み合わせても 使える のです。
A3セルに以下の式で OK。
=Arrayformula(IF(B3:B="",,IF(A3:A>0,A3:A,NOW())))最後の編集に引っ張られて、全部再計算されるかと思いきや、既にタイプスタンプが入ったところは固定化されて動きませんでした。問題なしです。
セルの編集時にタイムスタンプが更新されない、という制限はありますが、それでも メリットも多いので、関数で実現するタイムスタンプって、もっと広まってもいいと思うんですけどね。
今回の検証では関数を使ったタイムスタンプの動作は問題なさそうでしたが、やはり書き込まれた 値 ではなく、揮発性の 関数による表示は、いつふっとぶかわからない怖さがあります。変更履歴も残らないし。
ご利用は自己判断・自己責任でお願いします。
全3回にわたって、タイムスタンプの実装について 回答・解説してみました。参考になりましたでしょうか？
今回使った 循環参照・反復計算 を応用した 別の活用事例もあるので、そのうち取り上げたいと思います。
■このシリーズの次の記事
いいなと思ったら応援しよう！
