結論を先に書きますと…
Google スプレッドシートでも『INDIRECT関数』を使えば『R1C1形式』で数式を書くことができます。
IFTTT で Google スプレッドシートに行を追加するアプレットでも使えるので、『|||』をセルの区切りとして使用して記述するテキストボックス欄に
(省略) ||| =COUNTIF(INDIRECT("R1C[-1]:R[-1]C[-1]",FALSE),INDIRECT("RC[-1]",FALSE))と書くと、1列前の1行目から1行上までに、1列前の文字列が何個あったかをカウントすることができます。
先日、IFTTT と Google スプレッドシートを組み合わせてタイムスタンプを取る仕組みのようなものができると書きました。
私は、IFTTT を使って Google スプレッドシートに、『タイムスタンプ』『品目』を記録しています。
その品目は複数あるんですが、前回に同じ品目を記録したときからどれくらいの時間が経ったかを知りたいなーと思っていました。
タイムスタンプが記録されるシートに数式を書けばそれがわかりますが、GAS でも使わない限り手作業で数式を下に伸ばしてあげなきゃいけないなーって。
ちょっと面倒だな、と思っていたんです。
前回書いたときに、IFTTT から Google スプレッドシートに数式を書くことができる、というのはわかっていましたが、Google スプレッドシートの特性上、うまく数式が書けないな…と思っていたんです。
Google スプレッドシートは、セルの指定の方法がいわゆる『A1形式』です。
Excel では、セルの指定の方法が『A1形式』の他に『R1C1形式』があります。
オプションとかでうっかり『R1C1形式』にしちゃうと、指定の仕方も戻し方もわからなくて焦ってしまうアレです。
VBA を書いたりするには『R1C1形式』の方が楽なんですけどね。
私は自作のアドインツールで『A1形式』と『R1C1形式』を切り替えられるボタンを作っているのでカンタンなんですが、会社では「表示が変になって戻せなくなった」なんて言われて直してあげることもよくありました。
で、Google スプレッドシートでは『A1形式』しか使えない(と思っていた)ので、『前回に記録されたときからどれくらいの期間が経ったのか』の数式を書くのが面倒だな…と思っていました。
IFTTT のアプレットは『Google スプレッドシートの最初のシートの最後の行に1行追加する』という内容なので、じゃぁ今が何行目なのかを取得して…ROW 関数とか使って…あとは INDIRECT 関数かな…なんていろいろ考えていました。
ふと検索したら、INDIRECT 関数を使えば『R1C1形式』で書ける、というのを発見!
だったらROW関数を使わなくても大丈夫だ!
=COUNTIF(INDIRECT("R1C[-1]:R[-1]C[-1]",FALSE),INDIRECT("RC[-1]",FALSE))と書けば、Excel でうところの
=COUNTIF(R1C[-1]:R[-1]C[-1],RC[-1])の意味になります。
この入力したセル(自分)がどこだったとしても、自分の1列前の1行目(R1C[-1])から自分の1列前の1行前(R[-1]C[-1])までの範囲の中に、自分の1列前の値が何個入っているかをカウントしてくれる数式の完成です。
| A | B | C | |
| 1 | 2024/08/17 13:09 | 品目1 | |
| 2 | 2024/08/19 13:12 | 品目2 | 0 |
| 3 | 2024/08/21 6:31 | 品目2 | 1 |
| 4 | 2024/08/24 6:43 | 品目1 | 1 |
| 5 | 2024/08/27 19:00 | 品目1 | 2 |
上がGoogle スプレッドシートだったとして、C2・C3・C4・C5に
=COUNTIF(INDIRECT("R1C[-1]:R[-1]C[-1]",FALSE),INDIRECT("RC[-1]",FALSE))を入れると、上記のような数字が返ってきます。
C1は1行目なのでなし。
C2は、B1:B1の間に『品目2』は0個なので、『0』。
C3は、B1:B2の間に『品目2』は1個あるので、『1』。
C4は、B1:B3の間に『品目1』は1個あるので、『1』。
C5は、B1:B4の間に『品目1』は2個あるので、『2』。
…別に『A1形式』でも ROW 関数何かを使えば書けますが、こちらの方が短く書けます。
これができるのであれば、あとはこっちのもんです。
| A | B | C | |
| 1 | 2024/08/17 13:09 | 品目1 | |
| 2 | 2024/08/19 13:12 | 品目2 | |
| 3 | 2024/08/21 6:31 | 品目2 | 1.7 |
| 4 | 2024/08/24 6:43 | 品目1 | 6.7 |
| 5 | 2024/08/27 19:00 | 品目1 | 3.5 |
先程のスプレッドシートと A・B 列は同じですが、C2・C3・C4・C5に
=IF(COUNTIF(INDIRECT("R1C[-1]:R[-1]C[-1]",FALSE),INDIRECT("RC[-1]",FALSE)),INDIRECT("RC[-2]",FALSE)-MAXIFS(INDIRECT("R1C[-2]:R[-1]C[-2]",FALSE),INDIRECT("R1C[-1]:R[-1]C[-1]",FALSE),INDIRECT("RC[-1]",FALSE)),"")という数式をいれるとこうなります。
この数式は INDIRECT 関数をなくすと
=IF(COUNTIF(R1C[-1]:R[-1]C[-1],RC[-1]),RC[-2]-MAXIFS(R1C[-2]:R[-1]C[-2],R1C[-1]:R[-1]C[-1],RC[-1]),"")となります。
超訳(?)すると、「もし、今追加したものより過去に同じ品目があったら、今の日時と過去の同じ品目で一番新しい日時との差分を表示、なければ何も表示させない」です。
C2は、B1:B1の間に『品目2』は0個なので、なにもなし。
C3は、B1:B2の間に『品目2』は1個あるので、『2024/08/21 6:31』から『2024/08/19 13:12』を引いて約『1.7』。
C4は、B1:B3の間に『品目1』は1個あるので、『2024/08/24 6:43』から『2024/08/17 13:09』を引いて約『6.7』。
C5は、B1:B4の間に『品目1』は2個あるので、『2024/08/27 19:00』からより新しい『2024/08/24 6:43』を引いて約『3.5』。
これで、前に同じ品目を記録した日からの経過日時がわかりました。
あとはこの数式(INDIRECT がある方)を、IFTTT の『|||』をセルの区切りとして使用して記述するテキストボックス欄に書けば、いちいち手作業で下にコピーしなくても毎回計算式が付いてきてくれます。
わーい。
…まぁ、もっとスマートに書けるかもしれませんが、これで動いているからいいです。
IFTTT で Google スプレッドシートに行を追加する場合、タイムスタンプ等だけでなく計算式も書けることがわかったので、なんかこれでいろいろ夢が広がりそうですね。
嬉しいなぁ。
コメント