【便利！】Googleフォームとスプレッドシートで毎日の体温を記録

体温の記録といえばいろんなアプリがありますね。
私も少し前まではスマホアプリで記録していました。

しかし、アプリは便利なのですが基本的にローカルで記録されるようになってるんですよね。個人的にはそこだけが残念だなとずっと感じていたので、ここは思い切ってスプレッドシートでの管理に変更してみることにしました！

まずはGoogleフォームを作ります。
こんな感じ↓

体温は記述式です。「回答の検証」の正規表現に「\d{2}.\d{2}」を入力しておくと、小数点2桁の数字縛りにできます。
（小数点1桁の場合は「\d{2}.\d{1}」）

女性は「生理」の項目もあるといいと思います。これはチェックボックスで。選択肢の名前は「1」にしておいてください。理由は後ほど説明します。

あとはお好みでメモ欄とか作ってもいいですね。

できあがったフォームのURLをコピーして、スマホにショートカットを作っておけば、スマホから気軽に体温の送信をすることができるようになります。とても便利です。

次はGoogleフォームとスプレッドシートの連携です。

回答タブのスプレッドシートマークをクリックして、「新しいスプレッドシートを作成」を選択すればできます。

これで、Googleフォームで送信した回答がスプレッドシートに反映されるようになりました。

やったね！　終わり！

……はい。これだけなら大した情報じゃないですね。せっかくなのでフォームのデータをいじっていきましょう。

新しくシートを作成して、1年ごとに記録をわけてみようと思います。
Googleフォームの回答が反映されるシートとは別に、とりあえず2022年という名前のシートを作ってください。

まずは日付を入れます。最初のセルに2022/01/01と入力。
見た目を変えたい場合は表示形式のカスタム日時を使って、お好みの表示にしてください。

あとはオートフィルで下まで引っ張って……でもいいのですが、関数を使った方が後々メンテナンスが楽になります。
2022/01/01の下のセルに関数を入力。

=ARRAYFORMULA(IF(B3+ROW(A1:A365)<=EOMONTH(B3,11),B3+ROW(A1:A365),""))

ROW(A1:A365)は単純に1~365の連番です。
B3（2022年1月1日）に1ずつ足していくことで自動で日付を表示します。365まで足す、つまり合計366日分ってことです。

EOMONTH(B3,11)はB3の11ヶ月後の月末（2022年12月31日）を返します。
閏年じゃないと366日目は翌年の1月1日になりますが、B3+ROW(A1:A365)<=EOMONTH(B3,11)じゃない場合を""にすることで空白になってくれます。

ARRAYFORMULA関数を使っているので、数式を入れるのはB4セルのみでOK。
これだけで1年分の日付を入力できました。

次に、Googleフォームで送信して反映されている別シートの体温を抽出します。

=Arrayformula(IFERROR(VLOOKUP(B3:B,VALUE(LEFT('回答'!A2:B,10)),2,false),""))

※このとき、Googleフォームの回答が反映されるシートの名前は「回答」にしています。

Googleフォームを送信した時間にタイムスタンプが押されますが、形式は
◯◯◯◯/◯◯/◯◯　◯◯:◯◯:◯◯
で固定されています。
正直、時間が邪魔です。なのでLEFT関数で左から10文字をまず切り出します。で、切り出しによって文字列になってしまった日付をVALUE関数でまた数値に戻します。
そして、VLOOKUP関数で日付が一致したところの体温を抽出します。

これで日付の隣のセルに体温が表示され、測り忘れた日は空白になります。

VLOOKUP(B3:B,VALUE(LEFT('回答'!A2:B,10)),2,false)の部分を変更
→ VLOOKUP(B3:B,VALUE(LEFT('回答'!A2:C,10)),3,false)
で、生理の列も抽出できます。

私は測り忘れた日がわかりやすい方がいいと思ったのでこういう形式にしましたが、空白とかいらないという人はVLOOKUPで抽出とかこんな面倒なことをせずに、単純に全部回答シートから引っ張ってくればいいです。

ただし、左から10文字切り出して数値に戻すという工程は、日付以外の列にもかかってしまいます。体温も生理も数値であり10文字を超えることはないため影響はありませんが、残念ながらテキストに同じ手は使えません。つまり、フォームにメモ欄を設けていたとしても、これは抽出できません。VLOOKUPとか使わずに単純に全部引っ張ってくる方を選べばまあ、こんな問題は起こりません。

なにか他にいい方法があったら教えてください。

ちなみに、同じ理由で生理の列は空白のときに0が出てしまいますが、表示形式のカスタム数値形式で「#」にすれば0は表示されなくなります。

さて、これで1年分の記録が抽出されるようになりました。
Googleフォームで送信すれば、2022年シートにも勝手に反映されます。

それでは次に、これをグラフにしてみたいと思います。
なんとなく想像がつくと思いますが、体温は折れ線グラフです。

で、どうせなら生理期間もグラフに組み込みたいですよね。
そうです。ここで「1」にしたのが生きてくるわけです。
生理期間は棒グラフで表現しますよ！

まずは挿入 > グラフで「複合グラフ」を選びます。

X軸に日付の列、系列に体温の列と生理の列の範囲を入力します。
ただ、空白だと範囲選択ができないので、まっさらな状態の場合はとりあえず、1月1日のところにでも何か数字を入れといてください。

カスタマイズタブの「系列」で、1を折れ線（左軸）、2を縦棒（右軸）にします。

カスタマイズタブで「縦軸」と「右の縦軸」が選択できるので、最大値と最小値を入力。
体温は自分の平熱を考えていい感じに。生理は0から1です。

「グリッドラインと目盛」を選択して目盛の間隔とかも決める。

細かい部分はお好みで設定してください。

データが入るとこんな感じになります↓

どうでしょうか。棒グラフで生理期間を入れたの、なかなかよくないですか？

そして、次の年になったら2022年シートをコピーして2023年のシートを作るわけですけど、新たなシートのメンテナンスは、一番最初にB3セルに入力した「2022/01/01」を「2023/01/01」に変更するだけでOKです。

関数の変更もデータ削除も必要ありません。多分。おそらく。
Arrayformula関数、便利すぎる。

ちなみに、条件付き書式でセルに色を塗ることによってこんな生理期間グラフを作ることもできます↓

とりあえず2021年でやってみようと思います。まずは日付を横に、月を縦に入力するんですけど、日はただの数字で大丈夫です。
1と2を入れて2つとも選択してオートフィルで引っ張れば連番になります。

で、縦は一見すると月だけを入れているように見えますが、こっちは表示形式をそうしているだけで中身は2021/01/01～2021/12/01になってます。
こっちも2021/01と2021/02を入れて2つとも選択してオートフィルで引っ張れば、月ごとに連なって表示されます。

グラフを作りたい部分（横は必ず1～31まで、縦は好きなだけ）の範囲を選択して、表示形式 > 条件付き書式をクリックするとルールを設定できるので、書式ルールをカスタム書式にして関数を入れます。

=AND(DATE(YEAR($C3),MONTH($C3),D$2)<=EOMONTH($C3,0),VLOOKUP(DATE(YEAR($C3),MONTH($C3),D$2),Indirect("'"&YEAR($C3)&"年'!B3:D"),3,false)=1)

※このグラフを作っているシート名は何でもいいですが、持ってきたいデータを入力しているシート名は必ず「○○○○年」としておいてください。

DATE(YEAR($C3),MONTH($C3),D$2)
縦に入力した日付から「年」と「月」を、横に入力した数字から「日」を持ってきて日付にしています。

DATE(YEAR($C3),MONTH($C3),D$2)<=EOMONTH($C3,0)
存在しない日付を排除してます。

Indirect("'"&YEAR($C3)&"年'!B3:D")
Indirect関数でシート名を「"'"&YEAR($C3)&"年'!」とすることで、翌年になって新しいシートを作ったとしても自動的にその年のシート名が挿入されます。つまり、1年ごとに条件付き書式を変更する必要はありません。

「$」の位置が重要なので間違えないように。

そしてVLOOKUP関数で「○○○○年」シートの「生理」列を参照し、「1」が入力されていればその日のセルに色がつくという動きです。

これで視覚的にわかりやすいグラフになりました！

あとはどうせなら生理周期も出したいところなんですけど、これを自動で出るようにするのは正直ちょっと難しいですね。

例えば
=INDEX(Indirect("'"&YEAR($C3)&"年'!B3:D368"),MATCH(1,Indirect("'"&YEAR($C3)&"年'!D3:D368"),0),1)
を2021/01の行に入力すれば、その年の最初の生理開始日を抽出できます。

2回目以降はだいぶ面倒で
=IFERROR(INDEX(Indirect("'"&YEAR($C4)&"年'!B"&MATCH($AK3+15,Indirect("'"&YEAR($C4)&"年'!$B$3:$B$368"),0)&":$D$368"),MATCH(1,Indirect("'"&YEAR($C4)&"年'!D"&MATCH($AK3+15,Indirect("'"&YEAR($C4)&"年'!$B$3:$B$368"),0)&":$D$368"),0),1),"")

開始日に15を足して無理やり選択範囲をずらして2回目以降を見つけるという力技。自分で書いた式ですけど、あとから見るともうわけわかんないです。

一応、これをオートフィルで下に引っ張れば、2021年シートから生理開始日を全部抽出することができます。開始日を引き算すれば周期が出ますが、でもこれだと年をまたぐときに対応できないしなによりマジで意味がわからん……。

例えば、さっき条件付き書式で入れた式をちょっと加工して
=IFS(DATE(YEAR($C3),MONTH($C3),D$2)>EOMONTH($C3,0),"",VLOOKUP(DATE(YEAR($C3),MONTH($C3),D$2),Indirect("'"&YEAR($C3)&"年'!B3:D"),3,false)=1,"◯",TRUE,"")

こいつを1月1日のセルの位置に入力してオートフィルで左右に引っ張って

生理日に◯がつくようにしてみます。
ARRAYFORMULA関数使いたかったけど、年をまたぐのがうまくいかなかったのでオートフィルで全セルに関数を入れるしかないみたいです。

まあとにかくこうやって生理日がわかるようにして、2021/01の行に
=IFERROR(IFS(AND($F2="◯",INDIRECT(ADDRESS(ROW()-1,MATCH(DAY(EOMONTH($C3,-1)),$D$2:$AH$2,0)+3,4))="◯"),DATE(YEAR($C2),MONTH($C2),MATCH("◯",$M2:$AH2,0)+9),AND($D3="◯",INDIRECT(ADDRESS(ROW()-1,MATCH(DAY(EOMONTH($C3,-1)),$D$2:$AH$2,0)+3,4))="◯"),DATE(YEAR($C3),MONTH($C3),MATCH("◯",$M3:$AH3,0)+9),TRUE,DATE(YEAR($C3),MONTH($C3),MATCH("◯",$D3:$AH3,0))),"")
を入れてオートフィルで下に引っ張れば

月ごとの生理開始日が出て、この式ならとりあえず年をまたぐのにも対応できます。

IF条件の
AND($D3="◯",INDIRECT(ADDRESS(ROW()-1,MATCH(DAY(EOMONTH($C3,-1)),$D$2:$AH$2,0)+3,4))="◯")
で、1日が◯で前の月の月末も◯の場合を判定してます。そうだったらこれは開始日ではないので、MATCHの範囲を10日ずらして対応という結局は力技なんですけどこれ以上他に思いつかなかったorz

1ヶ月に2回生理が来る場合は
AND($F2="◯",INDIRECT(ADDRESS(ROW()-1,MATCH(DAY(EOMONTH($C3,-1)),$D$2:$AH$2,0)+3,4))="◯")
前の月の3日目と月末が◯のときで引っ掛けてみました。
これもMATCHの範囲を10日ずらして対応してます。

とりあえずいろんな可能性を考慮してみたけど、でもこれでもタイミングによってはもしかしたらうまくいかないときもあるかもしれない……。

まあ、とにかく一応はこれで開始日を引き算すれば周期が出せますね。

見栄えとしては、まず条件付き書式で生理日のセルに色をつけて、かつその文字色をセルの色と同じにする。で、生理日に◯がつく式を入れれば背景と文字が同色になるので見た目的には◯は見えなくなる。そして生理開始日を抽出する式を入れて引き算する。って感じがいいですかね。
なんか変になったら手動修正で。

もっといい方法を誰か教えてください。

はい。長くなってしまいましたがとにかく、Googleフォームとスプレッドシートで体温を管理するのはとても便利だよ！

というお話でした！