Spreadsheetsの日付列をARRAYFORMULAを使っていい感じにやる

ようやく異世界転生小説じゃなくて技術トピックを書くことができて感無量です。異世界転生小説は後で書くのでカクヨムでフォローしてください。

昨日はid:papixさんの｢SFC修行計画シート｣のご紹介でした。Spreadsheetsは趣味ユースが充実していていいですね。

最近Spreadsheetsコミュニティの雑談で出てきた相談からのネタです。

f:id:minemuracoffee:20181213222217p:plain

よくやりますよね、日付列を上の行+1でつくるやつ。別にこれでもさほど困らないのですが、ARRAYFORMULA()使ってかっこよく一発でやりたいのが人の性。それではやっていきましょう。

素直に上のセル+1を書こうとするとなんだか循環参照みたいな気持ちになって不安になりますね。そもそもどうやって記述するのかいまいちイメージも付きません。

+1は行数の積み重ねなので、ここはROW関数を使うのが良さそうです。つまり基本的には以下のような発想で書けばいい。

=ARRAYFORMULA("2018/12/1"+ROW(A:A))

ちなみにこれを1行目以外に書くと最大行数が無限に足りない現象に陥る*1ので2行目から日付を展開したい場合はROW(A2:A)のように書きます。この注意事項はARRAYFORMULAあるあるです。

=ARRAYFORMULA("2018/12/1"+ROW(A2:A))

ただ、これをそのまま書くと行頭は12/3になってるはず。もやもやするので先頭行の行番号を引きます。

=ARRAYFORMULA("2018/12/1"+ROW(A2:A)- ROW(A2))

これで完成。任意の行からはじめてもいい感じになります。あとはこれだと行があるかぎりひたすら日付を入れてしまう。そのあたりを考慮したかったらお好みでIF使っての空白削除とか月内に絞るとか適当にカスタマイズしてやりましょう。

課題としてあげるなら、この方法だと集計行を下に作りにくいことでしょうか？提案するとしたら、日付を降順、集計行を上にもってくるという手法。これはわりとデータを直近にフォーカスして見る上でも良いです。そのサンプルと関数を最後に提示します。

=ARRAYFORMULA(TODAY()-ROW(A3:A)-ROW(A3))

f:id:minemuracoffee:20181201193726p:plain

こういう感じだと、メンテナブルな表になるのではないでしょうか。

もしくはINDIRECT関数を使って、月の日数を自動的に生成したりするのもいいかもしれません。

以上です。ARRAYFORMULAはSpreadsheetsのキラー関数の一つですが、内包する関数によっては使えなかったり*2、書き方に注意しないといけないシーンがあったり、ユーザー的には未研究分野の多い関数です。

たとえば以前書いた記事でいえば以下のようなものもあります。

せっかくの便利関数ですが、WEB上にまだまだこういったトピックが少ないことによって痒いところに手が届かない状態になってしまっています。こういった情報を小さなことであってもブログ等で書いていけると、世の中から困りごとが減ってよいのかなと思っています。やっていきましょう。

明日はみくた (id:hogehoge0919)さんの「IF関数をいっぱい使ってTwitter依存度を可視化してみた」です。2日めの「関数初心者がとあるニコ生主の配信履歴表を作ってみた」も情熱を感じるいい記事でした。楽しみです。

*1:やってみたらわかるのでぜひやってみてください

ミネムラ珈琲ブログ