Google スプレッドシートで体調管理しよう【可能性は無限大∞】

最終更新日

コロナ禍以前から、わたしは体調には気を使っているほうだと思います。
その一環で、日々の体重・睡眠時間なんかを記録しています。

世の中にはいろんな便利なアプリがありますが、
自分ならではの項目とか追加したいですよね。
そんなわがままな要望にも、
Google スプレッドシートの抜群のカスタマイズ性ならこたえてくれます。

今回は、わたしが実際に使用している
Google スプレッドシートでの記録方法をお伝えします。

体調管理の記事なので、一部女性特有の話題にもなりますが、
嫌でなければお付き合いくださいませ。

はじめにまとめ

Google フォームからデータを入力し、
Google スプレッドシートに記録を集めて、関数などで加工していきます。

Google フォームや Google スプレッドシートを使うには
Google のアカウントにログインして Google ドライブを使う必要があります。
そのへんの準備は終わった段階からを想定しています。

Google スプレッドシートやExcel の絶対参照・相対参照あたりは
さらっと流したいので、
そのあたりはわかっている前提だと嬉しいです。
GAS ( Google Apps Script )とかマクロの話題にはならないので安心してください。

GAS は使えないんだもんね!

いつかは使えるようになりたい…。
そのうち…多分…?

Google Fit とかとの連携はしていないので、データは自分で入力します。
入力の手間はありますが、慣れればそんなにかかりません。
自分のほしいデータを見たいように表示できますので、
愛着が湧くんじゃないでしょうか。

わたしが記録しているデータは以下のものです。

  • 月経(生理)・帯下(おりもの)・出血の有無
  • 散髪顔剃りに行ってから何日経ったか
  • その日点鼻薬を何回使ったか
  • その日のお通じの回数と、お通じがあった最初の時間と最後の時間
  • 起床時間就寝時間から睡眠時間、昼寝の時間を足して合計時間
  • 生理周期
  • 体重、28日平均値、前日との差、28日平均との差
  • 夜食を食べたかどうか
  • スマートウォッチに表示される消費カロリー
    それが 250kcal を超えた連続日数、
    計測してからの連続回数(250kcalを下回ったら失敗 → カウントがアップする)

体脂肪とかも加えたほうがいいんでしょうね…。
そのうちやるかもしれません。
以前は基礎体温なんかもつけていました。

実際に自分で入力しているものは太字になっている項目です。

で、下の Google スプレッドシートは2021年の実際のものです。

ちょっと重いかもしれません。
見えてなかったらリロードしてください。

なぜか1月1日のグラフが表示されてないんですが、なんでなんでしょう?
実際のスプレッドシートには
1月1日のスパークラインでのグラフも表示されています。

BBA の体重なんか見せられても…なんて思わず。。。

さらっとやり方だけ見てくださいー。

入力フォームを作成

Google フォームとは、Web のアンケートみたいに
あらかじめ設置してある項目を選択したり、自由回答を書いたりした内容を
Google スプレッドシートに保存しておくことができるものです。
お問合せフォームみたいにいろいろ記入して『送信』すると、
決めておいたGoogle スプレッドシートにその内容が保存されます。

コレ、すごい技術ですよね!
昔なら CGI ガー、Perl ガーなんてヒーヒー言いながら
設置してたフォームが、数クリックでサクッと作れちゃう…。

ちなみに知ったきっかけは、2010年くらいに
会社の後輩が結婚式の二次会の出欠取りに利用していたのを見て、です。
「なんぞこれ!!!」と興奮してたら、ドン引きされました…。

Google フォームでデータを入力すると、
デフォルトで入力した日付・時間が記録されます。
そのことも加味すると、

  1. 値を入力したいもの → 体重・消費カロリー・昼寝時間
  2. やったかどうかの記録をつけるもの → 上記1.以外のもの

の2つに大きく分けられます。

実際には、わたしは体重・消費カロリー・昼寝時間を
それぞれ別のフォームで入力しているので、フォームは4つ用意しました。

1. 値を入力したいもの

例として、体重を入力するフォームを作成します。

Google ドライブの『新規』から Google フォームを選択します。
ファイル名は『体重』などと付けてください。
『フォームの説明』や『質問』などは特に入れなくても大丈夫です。
変更が必要なのは、右側の質問形式の部分。
『記述式』としてください。

今回入力するのは体重なので、多くの場合『○○.○kg』となると思いますが、
小数点の入力が面倒なので、わたしの場合
50.0kgを『500』と入力する方式にしています。
表示するときに10で割れば戻せます。
入力の簡単さを優先させました。

体重の場合、測るときの服装も気になりますね。
あらかじめパジャマの重さを測っておいて、
その重さを入力させるとか選択させるとかすると、
自分で計算する手間が省けるかもしれません。

前は、春秋用・夏用・冬用のパジャマの重さをそれぞれ測って、
選択できるようにしていたんだけど、
今はマッパで体重計に乗ってるよ!

趣味?

そのまま午前中に運動する用のカッコに着替えるんだよ!

デフォルトの入力フォームはこの画像の通り紫色ですが、
この色を変更することもできます。
いくつか作るときは、それぞれ色を変えるとわかりやすいかもしれませんね。

この先がちょっと重要です。

このフォームが1つ目に作ったものの場合、
自動的に作成される Google スプレッドシートに記録がたまります。
このフォームが2つ目以上の場合、
1つ目のフォームと同じ Google スプレッドシートに記録をためたいので、
ためる先を指定してあげます。

上のタブ状のところで『回答』を選択し、右の縦の三点リーダーをクリック。
『回答先を選択』を選びます。
出てきた画面で、『既存のスプレッドシートを選択』をクリックして『選択』。
次の画面でデータをためたいスプレッドシートを選びます。
すると、選んだスプレッドシートでシートが1枚追加され、
そこに体重のデータがたまっていきます。

今回は体重の例にしましたが、
同様に消費カロリーや昼寝時間等も作成します。
昼寝時間なんかは、質問形式のところを『記述式』ではなく
『時刻』にすると、時間の形式でデータがたまります。

また、入力された値がちゃんと数字かどうかなんかの
チェックができる仕組みもあるので、いじってみると楽しいです。

2. やったかどうかの記録をつけるもの

コレはいろんな項目を1つのフォームでまとめられます。
わたしの場合は、

  • 点鼻薬 → かつて一番使用する項目だったので一番上
  • 通 (お通じのこと)
  • 夜食
  • 月経
  • 帯下
  • 出血
  • 散髪
  • 顔剃り
  • 起床
  • 就寝

なんかの項目を1つのフォームで『ラジオボタン』で並べています。

こんな感じで、入力された時間(タイムスタンプ)と項目だけが
2列でたまっていきます。

ところで豆知識!
『行』と『列』のどっちが縦か横か、知っていますか!?

』という漢字は作りの部分に『=』のような部分があるので !
』という漢字は作りの部分が『‖』のようになっているので !

わたしは高校の数III の行列の授業で、数学の先生に教えてもらいました!

さっき検索したら、
行の『ぎ』は横棒から書き始めるから横、
列の『れ』は縦棒から書き始めるから縦、というのもありました~。

このふきだしコメントで、
役立つ情報が乗ったのははじめてなんじゃないかな…!?

作ったフォームが2つ目以降のフォームの場合、
データをためる先のスプレッドシートを選択することを忘れないでくださいね。

入力したデータの見せ方

フォームの準備が終わったものとします。
それぞれのデータは1つのスプレッドシートにまとめてためる設定にします。
シートの名前は変更できますので、
作ったフォームの名前はそのままシートの名前にしたとして、

  • 体重を記録する『体重』
  • カロリーを記録する『カロリー』
  • 昼寝の時間を記録する『昼寝』
  • その他のお通じ・月経などの有無を記録する『体調』

の4つのシートに名前を変更します。

では、データを表示させるシートを作ります。
一番左のA列には(なんとなく)『No.』を。

B列には1月1日からの日付を入れます。
『B3』セルに『1/1』と入れ、『B4』セルには

=B3+1

と入れて、下のNo. 366の行までコピペします。
年明けして新しい年になった場合、
はじめのセルだけ変更すればそれ以降のセルの年も変わります。

それぞれのデータをどんなふうに表示するか考えます。
見せたい方法で項目をまとめると、

  1. 有無がわかればいいもの → 月経・帯下・出血・夜食
  2. 回数を数えたいもの → 点鼻薬・お通じの回数
  3. 入力した時間を表示させたいもの → お通じの時間・起床時間・就寝時間・昼寝時間・体重を測った時間
  4. 経過した日数を表示させたいもの → 散髪・顔剃り・生理周期・連続日数

という感じでしょうか。
後は追加で

  1. 横棒グラフ
  2. 土日祝日でセルの色を変える

ですかね。
では、項目ごとに解説します。

1. 有無がわかればいいもの

月経・帯下・出血・夜食の有無なんかがコレにあたります。

ちなみに、夜食以外の項目は、
以前付けていた基礎体温表にあった項目をそのままパクっています。
表示が『×』なのも、その基礎体温表の凡例が『×』だったから
何となくそうしただけです。

『パクって』だなんて、お言葉が悪いですわ。
おほほほほ~。

先ほどと同じ画像です。
シートの名前は『体調』。

月経の有無を『COUNTIFS』関数で表示させます。
ちょうど表示されている『C50』セルの計算式はコレです。

=COUNTIFS('体調'!$B:$B,C$2,'体調'!$A:$A,">="&$B50,'体調'!$A:$A,"<"&$B50+1)

『COUNTIFS』はカンマ( , )で区切られた条件を2個で1セットと考えます。
条件を整理するとこんな感じです。

  1. 『’体調’!$B:$B』『C$2』
      → 『体調』シートのB列にC2セル『月経』が書かれている
  2. 『’体調’!$A:$A』『”>=”&$B50』
     → 『体調』シートのA列の日付がB50の日付(2/17)と一緒かそれより大きい
  3. 『’体調’!$A:$A』『”<“&$B50+1』
     → 『体調』シートのA列の日付がB50の日付(2/17)に1日足したものよりも小さい

この3つの条件をすべて満たしているデータの個数を数えます。

この『COUNTIFS』や『SUMIFS』のような『~IFS』の関数、
本当に便利ですよね!
コレがなかったときは『SUMPRODUCT』関数でゴリゴリに組んで、
シートが重くて再計算の嵐でした…。

Excel や Google スプレッドシートの日付は、『1』で1日24時間です。
『0.5』だと12時間。
今の日付に1足すと次の日になります。

1日の終わりが夜中の0時だとちょっと困る…

わたしも前までは5時から1日が始まるようにしていました。
規則正しく2時ころに寝ていたし、
仕事が長引いたとき「5時前には寝よう…」と思っていたので…。

1日の開始を0時以外にしたい場合は、『$B50』に必要な数を足します。
24時間で『1』なので、0時から5時間分ずらしたいときは『1÷24×5≒0.21』。
そのまま『1/24*5』だと5時きっかり、大体の数『0.21』だと5時2分24秒です。
コレを『COUNTIF』に戻すと、

=COUNTIFS('体調'!$B:$B,C$2,'体調'!$A:$A,">="&$B50+0.21,'体調'!$A:$A,"<"&$B50+1.21)

となります。

コレで関数は終わりですが、
このままでは数えた数が表示されるだけです。
今ある関数に『IF』をかませて表示を変更してもいいんですが、
今回の『×』の表示には『表示形式』を使ってみます。

『表示形式』から『数字』 → 『表示形式の詳細設定』 → 『カスタム数値形式』を選択。
出てきたテキストボックスに

[>=1]"×";""

と入力します。
コレは、

このセルに入力された数が1以上の場合は『×』を表示、
それ以外の場合は何も表示しない

という意味です。
これで、『COUNTIFS』の結果1以上になったセルに『×』が表示されます。

どうして1『以上』?

月経とかは、トイレに入るたび
うっかり2・3回入力しちゃうこともあるかなーと。

『×』じゃないのがいい場合は?

『[>=1]”×”;””』の『×』のところを好きなものに変えて!
『○』でも『☆』でも『Hey!』でも!

スプレッドシートに『Hey!』が並んでるの、
ガチャガチャしてそうでやだな…。

2. 回数を数えたいもの

点鼻薬の回数やお通じの回数がコレにあたります。
1日何回あったか知りたいものです。

表示のさせ方は、『有無がわかればいいもの』と同じ『COUNTIF』です。
最後の表示形式の部分をやらなければ、回数が表示されます。

手抜き!

ちょっと長くなってきたし…

3. 入力した時間を表示させたいもの

お通じの時間や起床・就寝時間、体重を測った時間などがコレにあたります。

なんでお通じは『先時間』『後時間』で2個も表示させるの?

お通じの時間、大事でしょ!
体重を測る前にお通じがあったどうかは重要!
1日に複数回あった場合、最後の時間によって次の日なくても許されるし!

何かの管理下におかれているの…?
まぁ、昔 Wii Fit の体重測定で、体重増えた理由に
『お通じがなかったから』みたいな項目あったもんね~。

時間の表示は、基本的に『MAXIFS』を使いました。
複数回入力した場合に、一番新しいデータが表示されたほうがいいかなと思ったので。

うっかり二度寝しちゃったときとかねー。

例として『起床時間』を表示させます。
『M50』のセルにはこの計算式が入っています。

=MAXIFS('体調'!$A:$A,'体調'!$B:$B,M$2,'体調'!$A:$A,">"&$B50,'体調'!$A:$A,"<"&$B50+1)

『MAXIFS』は、基本的には『COUNTIFS』と似た構成なんですが、
一番はじめの条件で表示させるデータを選びます。
2番目の条件から2個で1セットになっていて、

  1. 『’体調’!$B:$B』『M$2』
     → 『体調』シートのB列にM2セル『起床』が書かれている
  2. 『’体調’!$A:$A』『”>=”&$B50』
     → 『体調』シートのA列の日付がB50の日付(2/17)と一緒かそれより大きい
  3. 『’体調’!$A:$A』『”<“&$B50+1』
     → 『体調』シートのA列の日付がB50の日付(2/17)に1日足したものよりも小さい

下の2つは『COUNTIFS』と同じ条件です。
これら3つの条件を満たしたデータの中で、

  • 『’体調’!$A:$A』

『体調』シートのA列が一番大きな値を表示します。
『体調』シートのA列は入力した日付・時間が自動で入るので、
『起床』と入力した一番遅い時間が表示されるというわけです。

表示されたデータが時間の形式ではなく小数点のある数字の場合は、
『表示形式』から『時間』を選べば時間の形式になります。

昼寝時間のようにない場合があるものなんかで
ゼロが表示されるのが嫌な場合は、表示形式に

[=0]"";h:mm

と入力します。コレは、

値がゼロの場合は何も表示しない、
それ以外の場合は『時:分』の形式で表示する

という意味です。

『MAXIFS』だと一番最後の時間が表示されるので、
お通じの『先時間』の場合は逆の関数『MINIFS』を使います。
構成は『MAXIFS』と同じです。

お通じの『後時間』は、お通じが2回以上あったときにのみ
表示されてほしいので、『IF』をかませてあります。

=IF(J50<=1,"",MAXIFS('体調'!$A:$A,'体調'!$B:$B,"通",'体調'!$A:$A,">"&$B50,'体調'!$A:$A,"<"&$B50+1))

『MAXIFS』の条件を『IF』で囲んであります。
『J50』セルの値が1以下の場合は何も表示しないけど…
という条件が1つ追加になっています。
『MAXIFS』の条件群の中に入れようかと思ったんですが、
『MAXIFS』の条件群は『列全体』での条件なので、
ある特定のセルの値が…という条件だとうまく噛み合ってくれませんでした。
なので、『IF』でくっつけてしまっています。

つぎはぎ~!

4. 経過した日数を表示させたいもの

散髪、生理周期なんかがコレにあたります。

『散髪』って言い方…
『美容院』とか『サロン』とかさぁ…

昔から言い方おかしいって、よく言われるんだよね…。
旅館とかホテルのことも『宿屋』って言って笑われたし。

それはゲーマーだからでしょ!

散髪を例にします。
『G50』のセルには以下の計算式が入っています。

=IF(COUNTIFS('体調'!$B:$B,G$2,'体調'!$A:$A,">"&$B50,'体調'!$A:$A,"<"&$B50+1),1,G49+1)

構成としては、

  • 『COUNTIFS』で『体調』シートに今日の日付で『散髪』があるかどうか調べる
  • あった場合、『1』と表示する
  • なかった場合、前日の数字に1を足した値を表示する

という感じです。
コレで、散髪に行った日に『散髪』を入力すれば、
経過日数が表示されるようになります。

ショートだから、2ヶ月位に1回は散髪行きたい…。
白髪もひどいから染めたい…。

20代半ばから白髪ひどくて、
「お母さんになる前におばあさんになっちゃいそう」って
言ってたもんね…。

5. 横棒グラフ

睡眠時間・体重・カロリーに横棒グラフを付けています。
グラフで見えていたほうがわかりやすいからです。

何故ならその方がカッコイイから! byキャプテン・ブラボー!

真っ赤な誓い~!

睡眠時間の横棒グラフを例にします。

『R50』のセルは、『R50』から『AC50』まで結合してあります。
『R50』のセルを横に長くしても良かったんですが、
上の見出しの睡眠時間7時間のところに赤線を入れたかったので結合にしました。
『R50』のセルには以下の数式が入っています。

=if(Q50=0,"", SPARKLINE(O50:P50,{"charttype","bar";"max",0.5;"empty","zero";"color1","#009933";"color2","#00ff99"}))

この横棒グラフは、
Excel や Google スプレッドシートに昔からあるいわゆる『グラフ』ではなく、
『スパークライン』という機能で作っています。

Excel のスパークラインは、選択すると専用のダイアログが表示されますが、
Google スプレッドシートのスパークラインは自分で数式を書かなくてはいけません。

今回のスパークラインの関数をざっくり解説すると、

  • 『O50:P50』の『睡眠時間』と『昼寝』の時間をグラフ表示
  • グラフの種類『charttype』 → 積み重ね棒グラフ『bar』
  • 横棒の最大値『max』 → 0.5 (12時間のこと)
  • データがない場合『empty』 → ゼロにする『zero』
  • 1個目のグラフの色 → 濃い緑『#009933』
  • 2個目のグラフの色 → 黄緑『#00ff99』

という感じです。
グラフの種類は他にもあって、
『体重』では『横棒グラフ』(column)を使っています。
セルの中にきれいに入るし、パッと見てわかりやすいので
ご活用ください!

6. 土日祝日でセルの色を変える

最後に、セルの背景を土曜は薄い青、日曜と祝日を薄い赤にする方法です。
コレは『条件付き書式』の機能を使います。

前準備として、スプレッドシートにもう1シート用意して、
『祝日』という名前に変更し、
年間の祝日の一覧を作成します。

厳密に言えば、B列はいらなくてA列の日付だけでいいんですが、
名称もあったほうがなんとなくいいかなと思って付けています。

前は数式組んで、
ハッピーマンデーとか春分の日とかも自動で表示させてたんですが、
今はネットで調べて日付だけ貼っています。

元のシートに戻ります。
条件は、青く塗る用と赤く塗る用の2つ作成します。
このシート全体を選択し、『B3』セルにカーソルがある状態
『表示形式』 → 『条件付き書式』を選択します。

『セルの書式の条件』で『カスタム数式』を選択し、
下のテキストボックスに

=WEEKDAY($B3)=7

と入力します。
『書式設定のスタイル』は、土曜の背景にしたい色、
わたしの場合は薄い青を選択します。
『WEEKDAY』関数はデフォルトで7が土曜日なので、
『B3』セルの日付が土曜日だった場合に薄い青になります。

もう1つ条件を作成します。
同じく『セルの書式の条件』で『カスタム数式』を選択し、
下のテキストボックスに

=OR(WEEKDAY($B3)=1,COUNTIF("祝日!A:A",$B3))

と入力します。
『書式設定のスタイル』は、日曜と祝日の背景にしたい色、
わたしの場合は薄い赤を選択します。
『WEEKDAY』関数はデフォルトで1が日曜日なので、
『B3』セルの日付が日曜日だった場合、
それか、『B3』セルが『祝日』シートのA列にある日付だった場合に
薄い赤になります。

『”祝日!A:A”』の部分は、なぜかこの書き方で通るんですよね…。
よくわからん。

不明点、放置かい!

運用方法

スマホで Google フォームのページにアクセスし、
そのページのショートカットを作っておきます。

朝起きたとき『起床』、
夜寝るとき『就寝』、
髪の毛を切りに行ったら『散髪』、
生理が来たら『月経』、
毎朝体重を測ったら結果を入力…。

測定したい行動をしたらフォームで入力します。
はじめは入力するのが面倒かもしれませんが、
入力にかかる時間は一瞬ですし、
慣れてしまえば無意識でできます。
結果が見えるので、きっとモチベーションにもつながります!

最後にまとめ

ざっくりですが、体調管理のスプレッドシートの作り方の解説でした。
Google フォームと Google スプレッドシートの組み合わせでいろんな記録が取れます。
ためたデータを関数やグラフでいじれば、わかりやすく表示することもできます。

まさに可能性は無限大!
お試しください!

自分の健康管理以外にも、
勉強時間の記録とか、継続したいことの記録とか、
いろんなことに使えると思います~。

さちこ

シェアする

コメントを残す

メールアドレスが公開されることはありません。

CAPTCHA


コメントする