【スプレッドシート】カレンダー日付や曜日を自動表示する方法

こんにちは、たま(@closuppo)です。

「作りながら学ぼう」シリーズ第3回!

シリーズ連載を通して「勤怠管理システム」を作りながら、Googleスプレッドシートの関数や基礎知識について解説してます。

今回は「設定シート」と「勤怠入力シート」を作りながら、以下❶〜❸についてお伝えします。

  1. データ入力規則を活用してセルをクリックするとカレンダーが表示されそこから任意の日を選択する方法
  2. DATE関数でカレンダーの日付を作成する方法
  3. 表示形成で日付の表記を変更する方法
  4. TEXT関数で日時を曜日に変更する方法

それでは、シート作成しながら一つずつ進めていきましょう!


タマ

前回の記事では「スプレッドシートを使う現場でよく使われる専門用語 」についてお伝えしました!


シリーズ全編で学ぶ項目

  • 表示形式の変更
  • 文字・背景色の変更
  • 枠線の変更
  • 条件付き書式設定
  • データの入力規則
  • 演算子
  • 絶対参照
  • TEXT関数
  • DATE関数
  • OR関数
  • IF関数
  • SUM関数
  • COUNTA関数
  • COUNTIF関数
  • ArrayFormula関数
  • VLOOKUP関数


「作りながら学ぼう」シリーズ第一弾では、スプレッドシートの関数や書式設定などの 即戦力に必要なスキルが学べます。 業務効率化・スキルアップにぜひお役立てください!


※目次をクリックすると
タイトルの先頭まで移動します(↓)

目次

設定シートを作成する

データ入力規則を活用して セルをクリックするとカレンダーが表示されそこから任意の日を選択する方法 についてお伝えします。

こちらは設定シートを作成してながら進めていきましょう。

新規スプレットシートのワークシート名(シートタブの名前)をダブルクリックし「設定」に変更してください。

シート名の変更

次に、以下のように設定項目を記入してください。

シートはこうなります(↓)

spreadsheet-date-day_3
設定画面
稼働する年を記入してください。
稼働する月を記入してください。
日付(休み) 休日の日付を記入してください。※このセルで設定した日付の背景が自動変更されるようにします)
曜日(休み) 休日の曜日を記入してください。曜日は短縮形で表示します。例)月 ※このセルで設定した曜日の背景が自動変更されるようにします)


タマ

休日を増やしたい場合はD列、E列と右横に日付や曜日を追加しておきます!


データの入力規則を適応する

「データの入力規則」でカレンダーが表示されるように設定します。

spreadsheet-date-day2
データ入力規則

それでは、以下1〜3の手順で設定していきましょう。

  1. セル範囲「B3:G3」を選択
  2. データからデータ入力規則を選択
  3. 「条件」項目で日付▼ 有効な日付▼を選択し保存。

セル範囲「B3:G2」を選択

ドラッグしてセル範囲「B3:G2」を選択してください。

spreadsheet-date-day3

「データの入力規則」を選択

メニューバーのデータからデータ入力規則を選択してください。

spreadsheet-date-day4

「条件」項目を設定し保存

「条件」項目で日付▼ 有効な日付▼を選択し保存してください。

spreadsheet-date-day5

タマ

セル「B3」や「C3」をクリックしカレンダー表示になっているか試してみてください!


勤怠入力シートを作成する

次に、DATA関数・TEXT関数を活用して日付や曜日を自動表示する方法についてお伝えします。

こちらは、勤怠を入力するユーザーシートを作成してながら進めていきましょう。

日付、曜日、出退勤時間、休憩時間を入力し勤務時間が自動計算されるシートにしていきます。


見出しを作る

左下部の「シートを追加」より新しくシートを追加し、セル範囲「A1:J1」

に以下項目を入力して見出しを作ってください。

A1:日付
B1:曜日
C1:出勤
D1:退勤
E1:休憩
F1:稼働時間
G1:残業時間
H1:深夜
I1 :区分
J1 :備考

赤枠の「+」をクリックするとシートが追加されます。

spreadsheet-date-day6

ワークシート名(シートタブの名前)をダブルクリックし「ユーザー」に変更してください。

項目入力したシートはこうなります(↓)

シート名変更

日付を作る

別シートから値を参照し「DATE関数」でカレンダーの日付を作成する方法 についてお伝えします。

データ関数は「年」「月」「日」の3つの数値から日付データを作成します。構文は以下の通りです。

=DATE(❶年,❷月,❸日)

「❶年」「❷月」設定シートから値を参照し、「❸日」は「1」とします。

セル「A1」に入力する完成した数式はこうなります(↓)

=DATE(‘設定’!B1,’設定’!B2,1)
spreadsheet-date-day7

ユーザーシートのA2に半角で「 = 」を入力するとセルが青枠になります。その状態で以下❶〜❺の手順をしましょう。

  • 半角で「 =DATE( 」と記入
  • 「設定」シートに移動し参照するセルB1をクリック
  • , 」(カンマ)で区切る
  • 「設定」シートB2をクリック
  • , 」(カンマ)で区切る
  • 数字の1を指定
  • 半角「 ) 」で閉じる


タマ

うまく日付は参照されましたか?

書式入力でも参照することもできます。

今回の例でいうとユーザーシートのセルA2に「=DATE(‘設定’!B1,’設定’!B2,1)」と直接入力することで値が参照されます。


チェック

シート名に英数字以外の文字や記号、スペースが含まれている場合は、シート名を引用符で囲んでください。

  • =Sheet1!A1
  • =シート番号 2!B4

表示形式を変更する

「2020/02/01」のままでは桁数が多くて見づらいので、表示形式を変更して「日」だけの表示にします。

表示形式01

メニューバーの表示形式から数字>表示形式の詳細設定>その他の日付や時刻の形式と選択していくと「カスタムの日付と時刻の形式」というウィンドウが開きます。


表示形成02

上部の枠内で「日(5)」だけを残して他の要素は Delete で削除してください。


表示形成設定手順2

タマ

「2020/02/01」の表示が「01」に変更されました!

02日から28日までを作成する

しっかりと「01」と表示することができたら、次に02から28までを作成します。

A3に「=A1+1」という計算式を入れて、それをA4からA29までコピーしてください。

表示形成4

タマ

2020/02/01に1日ずつプラスされ、01日から28日までが反映されましたね。


※29日から31日までの日付作成については、シリーズ第3回「うるう年か判定し自動入力する方法」で解説します。


曜日を作る

続いて、 TEXT関数を活用して日付を曜日に変更する方法 についてお伝えします。

spreadsheet-date-day_4

TEXT関数は「数値に指定した書式を設定し、文字列に変換した結果を返す」関数です。構文は以下の通りです。

=TEXT(❶数値, ❷表示形式)

❶数値 にセル「A2」を指定し ❷表示形式「”ddd”」と指定することで、日付データが曜日に変更されます。

セル「B2」に入力する完成した数式はこうなります(↓)

=TEXT(A2,”ddd”)

次に、セル「B2」の数式をセル「B3」から「B29」までコピーしてください。

今回はオートフィル機能を使ってコピーします。

セルの右下に表示されている「■」にカーソルを合わせると「+」に変わるので、これをドラッグしてください。


spreadsheet-date-day_5

たま

TEXT関数に使用できる ❷表示形式 には次のものがあります。

日付 使用例 結果
2月1日 "yyyy/mm/dd" 2020/02/01
2月1日 "yyyy" 2020
2月1日 "mmm" 2月
2月1日 "mm" 02
2月1日 "m" 2
2月1日 "dddd" 土曜日
2月1日 "ddd"
2月1日 "dd" 01
2月1日 "d" 1


まとめ

今回はGoogleスプレッドシートのDATA関数・TEXT関数・データ入力規則・表示形式の使い方についてお伝えしました。

こにらは、日付や曜日を扱うシートを作るときに役立つスキルとなっています。

どんどん活用していきましょう!

次回は、IF関数・DAY関数・OR関数を使って閏年(うるうどし)を自動判定しカレンダー月末の29日・30日・31日を自動表示&自動非表示する方法 についてお伝えします。