【スプレッドシート初心者向け】定番関数と表示形式で手入力しないで日付と曜日を出力


みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

Googleスプレッドシートを使った勤怠管理集計システムを作るシリーズ、第2回目です。

前回の記事はこちらで、勤怠管理集計システムの概要と構成についてお伝えしています。

Googleスプレッドシートでクラウド上の勤怠管理集計システムを作る
皆さんの会社の勤怠管理はどのように行われていますか?手入力やファイルのやり取りで、毎月面倒な作業かもしれません。今回はGoogleスプレッドシートを使った簡易的な勤怠管理集計システムの作り方です。

いよいよ本格的に作っていきます。

まずは日付と曜日を手入力せずに関数と表示形式を使って簡単にそしてクールに出力していきたいと思います。

スプレッドシートの定番関数日付や曜日などの取り扱いなど初心者には絶好のテクニックを散りばめました。

では行ってみましょう!

スポンサーリンク

新規スプレッドシートに見出しを作る

ではまず新規のスプレッドシートで見出しの部分を作っていきましょう。

見出しを作っていくとなんとなく全体が見えるようになってきますからね。

こんな形でしょうか。

新規のスプレッドシートに見出しを入力

良いですね。

では、D列「日」とE列「曜日」のところを作っていきましょう。

スプレッドシートに日付を作る

DATE関数で特定の年、月の1日を求める

まずD3セルですが、なんとなく単純に「1」と入れてしまいたくなりますが、そこはグッとこらえて下さい。

B4の「年」とB5の「月」を参照する形で

=DATE(B4,B5,1)

として下さい。

DATE関数は以下のように書いて

=DATE(年,月,日)

年、月、日を組み合わせて日付を出力する関数です。

B4,B5にそれぞれダミーデータを入れてあげて、D3セルに上記計算式を入れます。

スプレッドシートでDATE関数で日付

すると、D3には「2016/06/01」と出力されます。

カスタムの日付と時刻の形式で表示形式を「日」だけにする

このままではちょっと桁数が多くて見づらいので、表示形式を変更して「日」だけの表示にしてしまいます。

まずメニュー「表示形式」から「数字」→「表示形式の詳細設定」→「その他の日付や時刻の形式」と選択していきます。

スプレッドシートで日付の表示形式

「カスタムの日付と時刻の形式」というウィンドウが開きます。

入力窓に「年(1930)/月(8)/日(5)」などとデフォルトで入っているかと思いますが、入力窓のカーソルを十字キーで操作しつつ「日(5)」
だけを残して他の要素は Delete で削除してしまって下さい。

カスタムの日付と曜日の表示形式

そして適用をします。

これで表示形式を「日」だけにすることができました。

表示は「日」だけなのですが、裏側のデータとしてはちゃんと年、月も含めた「日付データ」として保持されています。

この工夫が後程の自動集計に役立ってくれるようになるというわけです。この成果については連載を続けていけばおのずとわかります。

2日から28日までを出力する

次にD4には

=D3+1

という計算式を入れて、それをD5からD30までコピーします。

これで28日まではOKですね。

スプレッドシートで日付を28日までコピー

29日から31日まではどうするんだ?って話なのですが、月によって日数が違いますので、その月に存在しない日数の場合は「–」と表示するようなクールな計算式にしてあげたいと思います。

29日はうるう年ではない2月を判定する

まずD31セル、29日は

=IF(AND(B5=2,MOD(B4,4)<>0),”–“,D30+1)

とします。

それぞれの関数を説明しておきます。

割り算の余りを算出する関数MOD

まずMODは割り算の余りを算出する関数

=MOD(数値,除数)

と書きます。数値を除数で割った余りが出ます。

今回の例では、B4セルつまり「年」を4で割った余りを出しているわけです。4で割れる年って言えば…そうです、うるう年ですね。

AND関数で論理積を求める

次にAND関数です。

難しく言うと論理積を求める関数なのですが

=AND(論理式1,論理式2,…)

と書いて、全ての論理式がTRUEだった場合にTRUEを返します。一つでもFALSEがあればFALSEになります。

ですから今回の場合は

  • B5セルつまり「月」が「2」
  • B4セルつまり「年」を4で割った余りの数が0でない

が両方も成立する場合にTRUEが返ります。うるう年でない2月の場合にTRUEになるというわけですね。

IF関数で論理式によって表示内容を分岐する

最後はIF関数ですね。

=IF(論理式,TRUEの場合,FALSEの場合)

と書き、論理式がTRUEでれば「TRUEの場合」を出力、さもなくば「FALSEの場合」を出力します。

したがって今回の場合は論理式に応じて

  • うるう年ではない2月:「–」
  • それ以外:D30+1つまり「29」

が出力されるというわけです。

30日は2月かどうかを判定する

次にD32セルですが、実は簡単です。

=IF(B5=2,”–“,D31+1)

これでOKです。

2月以外であれば全ての月で30日は存在していますからね。

31日は月で判定をする

最後の31日です。31日は月によってあったりなかったりしますから、B5セルを見て判定をします。

=IF(OR(B5=2,B5=4,B5=6,B5=9,B5=11),”–“,D32+1)

OR関数で論理和を求める

OR関数は論理和を求める関数です。

=OR(論理式1,論理式2,…)

と書いて、論理式のうちどれか一つでも成立していればTRUEを返します。全てがFALSEだった場合にFALSEが返ります。

したがって、今回の例では2,4,6,9,11月のいずれかであればTRUEが返りつまり、計算式としては「–」を返すというわけです。

OR関数はAND関数とセットで覚えちゃうといいですね。

曜日をクールに出力する方法

続いて隣りのE列に曜日を入れていきたいと思います。

まさか、月、火、水、…と手打ちなんかしようとしてないでしょうね?ダメですよ。

もっと簡単にできます。

まずE3セルに

=D3

として、それをE4からE33までコピーをしちゃってください。

そしてE3からE33を選択した状態で

スプレッドシートで曜日を出力する

メニューの「表示形式」からたどって先ほども活躍した「カスタムの日付と時刻の形式」ウィンドウを出します。

「日(5)」がいると思いますが、その上下の三角マークをクリックするとプルダウンしますので、その中から「曜日の省略形(日)」を選択して「適用」します。
カスタムの日付と曜日の書式設定で曜日を表示

すると…

スプレッドシートで曜日を簡単に出力

はい、バッチリ「曜日」に表示が変更されましたね。

曜日の手打ち入力とはもうオサラバですね。

エクセルでも似たようなことができますので、ぜひご活用下さい。

まとめ

今回は勤怠管理集計システムを作る第一歩目ということで、スプレッドシートの定番関数

  • DATE
  • MOD
  • AND,OR
  • IF

日付や曜日の表示形式を使って、日付と曜日を簡単にかつクールに数式で出力する方法についてお伝えしました。

ちょっと面倒のような気がするかも知れませんが、これからシステムを作る上でものすごく活躍をしてくれます。

日付や曜日を扱うようなツールやシステムを作るときは必須のテクニックともいえると思うので、ぜひ活用して頂きたいです。

次回は、VLOOKUP関数を色々と使って区分、打刻の箇所をデータから引っ張ってくる箇所を進めていければと思います。

GoogleスプレッドシートのVLOOKUP関数で複数条件による検索を行う方法
GoogleスプレッドシートでVLOOKUP関数を使う基本の方法とVLOOKUP関数で複数条件による検索をする方法についてです。またエラー表示を非表示にするIFERROR関数の使い方にも触れます。

どうぞお楽しみに!

連載目次:Googleスプレッドシートで勤怠管理集計システムを作る

勤怠管理業務はたくさんのスタッフとのやり取りもありますし、集計もなかなか複雑です。本シリーズでは複数人や離れた場所での共同作業も得意なスプレッドシートを用いてクラウド上で勤怠管理システムを作る方法をお伝えしていきます。
  1. Googleスプレッドシートでクラウド上の勤怠管理集計システムを作る
  2. 【スプレッドシート初心者向け】定番関数と表示形式で手入力しないで日付と曜日を出力
  3. GoogleスプレッドシートのVLOOKUP関数で複数条件による検索を行う方法
  4. 【スプレッドシートで給与計算】打刻時間を指定の分単位で切り上げ、切り捨てをする方法
  5. GoogleスプレッドシートでIF文を超絶駆使して通常・残業・深夜の就業時間を求める
  6. Googleスプレッドシートで時間内・時間外(残業)・休日・深夜の労働時間を計算する
  7. Googleスプレッドシートによる勤怠管理シートをCOUNTIFS関数と書式設定で仕上げる

コメント

  1. 田嶋 祐樹 より:

    突然失礼致します。
    ブログを拝見させて頂き、勤怠管理集計システムを作ってみようと思っているのですがつまづいております。
    質問させて頂きたいと思います。

    日付の末日について、

    29日はうるう年ではない2月を判定する
    まずD31セル、29日は

    =IF(AND(B5=2,MOD(B4,4)0),”–“,D30+1)
    とします。

    と記載されている数式を入力するとエラーが起こってしまいます。
    何か原因はわかりますでしょうか?

    恐れ入りますが返答頂けたらと思います。

  2. iPhone利用者 より:

    29〜31日の処理は
    D31に
    =IF(MONTH(D30+1)=B$5,D30+1,”–“)
    これをD33までコピーでどうでしょうか。
    「上のセルの翌日が今月なら、翌日を表示。」という式です。
    3日とも同じ式で大丈夫です。
    難しい計算をしなくていいので便利です。

タイトルとURLをコピーしました