0.発端と主旨
昨年末の大晦日、1年のおさらいとして「毎月のイベント」をひとつずつ選出しようと思ったのですが、情報源が
「自身のSNSの投稿を遡る」
しかなく、選出から投稿までに結構な手間がかかりました。
今年はもっと楽にしたい! でも日記だとFacebookを遡るのと変わらない!
そうだ、毎日のイベントと簡単な感想を1行ずつ記録していこう!
ということで、Google スプレッドシートに「一行日記」を作ろうと思い立ちました。
当エントリは、その「一行日記」のベースを作る際に使った小技をまとめたものになります。
1セルの日付を変えるだけで1年分の日付と曜日が自動的に反映され、色付けされるように手を加えました。
行単位で条件付き書式を設定出来るようになると、リスト管理が一段と楽しくなるのでオススメです。
環境
- Googleアカウントを取得しており、Google Driveにアクセス出来ること
- Google Chromeブラウザ
1.ファイル作成
なんのことはないです。まずはGoogleスプレッドシートを作成します。
Web上の「Google ドライブ」で「新規 > Google スプレッドシート(> 空白のスプレッドシート)」で作成してもいいですし、
「パソコン版ドライブ」であれば、(Windowsの場合)フォルダ上で右クリックから「新規作成 > Google Sheets」でも良いです。
どちらにせよファイル自体はWebブラウザ上で編集することになります。
2.項目名と行の固定
項目名
項目名を設定します。表記や種類、数は完全に好みの問題です。私は
「年月日」「曜」「祝祭日」「出来事」「日記」
としました。
私は項目名は太字でセンタリングするのが好みです。
項目行の固定
1シート上に1年365日分の日記を作成するため、スクロールすることになります。
項目行を固定して常に表示されるようにしておくと確認しやすくなります。
「表示」から「固定」「1行」と選択し、項目行を固定します。
1行目と2行目の間に分割線が出来ました。
これで、シートを下にスクロールさせても、1行目だけ表示されたままになります。
3.年月日と交互の背景色
年月日
このスプレッドシートが今年だけであれば、A2セルに「2023/1/1」と入力し、A2セル選択状態からカーソル右下の「+」を下にドラッグすれば自動的に2023/1/2, 2023/1/3, ... と入力されます。
が、毎年それやるのは面倒くさい! 1月1日だけ変更すればあとの364日は自動的に入力されてほしい!
ので、ここは簡単な数式で解決します。
A3セルに=A2+1と入力するだけです。予測算出結果に2023/1/2と出ているので、そのまま確定するだけ。
あとはA2セルの数式を、A366までコピペすればOKです。
これで、A2セルの「2023/1/1」を「2024/1/1」に変更するだけで、以降の日付が自動的に2024年版に変わる事になります。
(閏年の対応はしてませんが、1日増える分を367行目に1行追加するだけです)
交互の背景色
個人的なトレンドとして「交互の背景色」を設定すると見た目がリッチに感じるので、そのように設定します。
「塗りつぶしの色」から、最下段の「交互の背景色」を選択します。
項目を設定した列(A~E)と、日付を設定した行(1～366)を対象として、A1～E366の範囲を自動的に着色してくれます。
項目行だけ色味が強くなっていますが、「スタイル」の「ヘッダー」のチェックを外すと他と変わらなくなります。
今回は休日時にも色を変える予定なので、ヘッダ有効で、モノトーンの落ち着いたものに変えました。
4.曜日と条件付き書式
毎日の日記なので、営業日と休みが入り乱れる事になります。
当然、曜日も記した上で、休みである土日の色を変えておきたい。
ので、曜日の記入と、週末の自動配色を設定します。
曜日
B2セルに対し、A2セルの日付から自動的に曜日を設定したい。
そこで、TEXT関数 を用いて曜日を自動設定します。
=TEXT(年月日のセル,"ddd")
B2に対し、上記のTEXT関数を=TEXT(A2, "ddd") と入力することで、自動的に「月」が入ります。
あとはB2セルをB3～B366にコピペするだけで、自動的に曜日が算出されます。
"ddd"について
"ddd"及び"dddd"は、スプレッドシートの「言語設定」に沿った曜日の表記を代入してくれる表示形式です。月曜日を例にとると、
"ddd"では「月」
"dddd"では「月曜日」
となります。
yyyy年mm月dd日dddd # 2023年1月6日金曜日
yyyy/mm/dd(ddd) # 2023/1/6(金)
MonやMondayにしたい場合は、スプレッドシートの言語設定を変更する必要があります。
共存させたい場合は、ユーザー定義のリストを用意して、次項の補足で扱うWEEKDAY関数で呼び出す事になるかと思われます。
曜日の条件付き書式
週末でお休みである土曜日と日曜日に対し、自動的に背景色を変えるように条件付き書式を追加します。
土曜日
B2セルに対し右クリックから「セルでの他の操作項目を表示」→「条件付き書式」を選択します。
「範囲に適用」をA2:E366に設定し、項目業以外の全体を対象とします。
書式ルールを「カスタム書式」と選択し、数式を=$B2="土"とします。
入力した時点で、土曜日の行の背景色が変わるはずです。
「書式設定のスタイル」の「カスタム」を土曜日っぽくして完了します。
日曜日
土曜日と同様、条件付き書式を行います。
条件付き書式が既に設定されているセルの場合、右クリックの「条件付き書式」選択後に「条件付き書式設定ルール」の一覧が表示されるようになります。
「+条件の追加」を選択し、条件を追加します。
A2:E366の範囲に対し、=$B2="日"と指定します。
日曜日なので薄い赤にしました。
数式における$の役割について
条件付き書式で、範囲をA2:E366とすると、セルひとつひとつに対して条件に沿っているかを確認します。
このとき、数式中で$B2とすることで、条件対象とする列をB列に限定することが出来ます。
当項目の土曜日だと、B列が”土”になっている行はすべて条件を満たす判定を持つため、$B2が土である場合はA2～E2がすべて書式変更される形です。
日付から直接条件付き書式を実施するには
今回は曜日を別セルに出力して、その曜日の表記から条件付き書式を設定しておりますが、日付から直接曜日を算出する方法もあります。
WEEKDAY関数を用いると、曜日が数値に変換されるので、その数値を条件として用いる事で条件付き書式を行う事が出来ます。
=WEEKDAY(日付, [種類])
種類を指定しない場合は日曜日が1となり、以降月:2、火:3、...、土:7 と出力されます。
種類を指定した場合、日曜日の数値が種類の数値となり、全体がズレることになります。
種類に3を指定した場合は日曜が3となり、土曜日が2となります。
5.祝祭日の条件付き書式
前項で週末の休みは自動的に色がつくようになりました。
ただお休みは週末だけではなく、祝祭日もあります。
また、業務上お休みとなる日も、結果的に休みとなった日も出てくるでしょう。
その場合も、文字列の入力だけでその行の背景色が変わるように手を加えます。
祝祭日
「祝祭日」にあたるC列に、祝祭日の名称か、お休みである事を記入します。
祝祭日の条件付き書式
前項を踏まえて条件付き書式を設定します。
C2セルを右クリックして「条件付き書式」を設定します。
範囲は前項と同様A2:E366、書式ルールを「カスタム数式」として、
数式に=$C2<>""と入力します。
「C列が空欄でない場合は条件を満たす」という式になります。
土曜日や日曜日ではないけれど、お休みっぽい色を設定して設定を完了します。
6.「今日」の条件付き書式
日記なので基本的には当日記入することになります。
なので「今日がどの行なのか」が ひと目でわかったほうが便利ですね。
今日にあたる行も自動的に変わるように、TODAY関数を用いて条件付き書式を設定します。
今日にあたる日のA列にカーソルを合わせ、右クリックから「条件付き書式」を追加します。
範囲はA2:E366、書式ルールを「カスタム書式」として、
数列に=$A2=TODAY()と入力します。
TODAY関数は、今日の日時を返却する関数であり、A列とイコールで判定が可能です。
前項までの休日よりは目立つ色を設定しました。
7.条件付き書式の優先度変更
これまで条件付き書式で自動的に色をつけるように設定してきましたが、ここでひとつ問題が浮上します。
「条件付き書式の優先度が望ましくない」
今日を示す条件付き書式(例では黄色)が、土日や祝祭日だと反映されません。
また、祝祭日も土日と被っていると土日が優先されてしまいます。(例では元旦がそうなっています)
条件付き書式の優先度を変更します。
「条件付き書式設定ルール」の各ルールにカーソルを合わせると、左端に縦4点のマークが現れます。
これをドラッグして並び替える事で、優先度を変える事が出来ます。
今回は、優先度を「今日」>「祝祭日」>「土」>「日」と変更しました。
これで元旦も日曜日の赤ではなく祝祭日のオレンジに変わりました。
8.整形
あとは自分の好きなように、見やすいように幅や書式を変えればおしまいです。
個人的には日付の表記がガタついているのが気になるので、日付を0埋め表記に変更して幅を整えました。
曜日列は1文字分しか要らないはずなので狭く。
日記の欄は逆に小さいので大きくしておきました。
これで、作りたかった「一行日記」のベースが出来ました。
今年記入し終わっても、シートを複製してA2列をその年の1月に合わせ、祝祭日を再設定、出来事と日記をクリアすれば良いので簡単ですね!
9.終わりに
Google スプレッドシートは、Excelとは違う部分が結構多いので、安易に代替にはならないところがあります。
ただ、ネットにつながってさえいれば、OS問わず同じ環境で編集が可能なので、Googleのサービスに乗っかった上でChromeにアカウントを紐づけておくと結構便利だなーと感じます。
今回のような思いつきのリストも割と簡単に作れるので、業務にドンドン推進していけるかはともかく、色々試し甲斐があるのではないでしょうか。