photo credit: Window Advent Calendar via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
Googleスプレッドシートを使った勤怠管理集計システムを作るシリーズ、第２回目です。
前回の記事はこちらで、勤怠管理集計システムの概要と構成についてお伝えしています。
いよいよ本格的に作っていきます。
まずは日付と曜日を手入力せずに関数と表示形式を使って簡単にそしてクールに出力していきたいと思います。
スプレッドシートの定番関数、日付や曜日などの取り扱いなど初心者には絶好のテクニックを散りばめました。
では行ってみましょう！
新規スプレッドシートに見出しを作る
ではまず新規のスプレッドシートで見出しの部分を作っていきましょう。
見出しを作っていくとなんとなく全体が見えるようになってきますからね。
こんな形でしょうか。
良いですね。
では、D列「日」とE列「曜日」のところを作っていきましょう。
スプレッドシートに日付を作る
DATE関数で特定の年、月の1日を求める
まずD3セルですが、なんとなく単純に「1」と入れてしまいたくなりますが、そこはグッとこらえて下さい。
B4の「年」とB5の「月」を参照する形で
として下さい。
DATE関数は以下のように書いて
年、月、日を組み合わせて日付を出力する関数です。
B4,B5にそれぞれダミーデータを入れてあげて、D3セルに上記計算式を入れます。
すると、D3には「2016/06/01」と出力されます。
カスタムの日付と時刻の形式で表示形式を「日」だけにする
このままではちょっと桁数が多くて見づらいので、表示形式を変更して「日」だけの表示にしてしまいます。
まずメニュー「表示形式」から「数字」→「表示形式の詳細設定」→「その他の日付や時刻の形式」と選択していきます。
「カスタムの日付と時刻の形式」というウィンドウが開きます。
入力窓に「年(1930)/月(8)/日(5)」などとデフォルトで入っているかと思いますが、入力窓のカーソルを十字キーで操作しつつ「日(5)」
だけを残して他の要素は Delete で削除してしまって下さい。
そして適用をします。
これで表示形式を「日」だけにすることができました。
表示は「日」だけなのですが、裏側のデータとしてはちゃんと年、月も含めた「日付データ」として保持されています。
この工夫が後程の自動集計に役立ってくれるようになるというわけです。この成果については連載を続けていけばおのずとわかります。
2日から28日までを出力する
次にD4には
という計算式を入れて、それをD5からD30までコピーします。
これで28日まではOKですね。
29日から31日まではどうするんだ？って話なのですが、月によって日数が違いますので、その月に存在しない日数の場合は「–」と表示するようなクールな計算式にしてあげたいと思います。
29日はうるう年ではない2月を判定する
まずD31セル、29日は
とします。
それぞれの関数を説明しておきます。
割り算の余りを算出する関数MOD
まずMODは割り算の余りを算出する関数で
と書きます。数値を除数で割った余りが出ます。
今回の例では、B4セルつまり「年」を4で割った余りを出しているわけです。4で割れる年って言えば…そうです、うるう年ですね。
AND関数で論理積を求める
次にAND関数です。
難しく言うと論理積を求める関数なのですが
と書いて、全ての論理式がTRUEだった場合にTRUEを返します。一つでもFALSEがあればFALSEになります。
ですから今回の場合は
- B5セルつまり「月」が「2」
- B4セルつまり「年」を4で割った余りの数が0でない
が両方も成立する場合にTRUEが返ります。うるう年でない2月の場合にTRUEになるというわけですね。
IF関数で論理式によって表示内容を分岐する
最後はIF関数ですね。
と書き、論理式がTRUEでれば「TRUEの場合」を出力、さもなくば「FALSEの場合」を出力します。
したがって今回の場合は論理式に応じて
- うるう年ではない2月：「–」
- それ以外：D30+1つまり「29」
が出力されるというわけです。
30日は2月かどうかを判定する
次にD32セルですが、実は簡単です。
これでOKです。
2月以外であれば全ての月で30日は存在していますからね。
31日は月で判定をする
最後の31日です。31日は月によってあったりなかったりしますから、B5セルを見て判定をします。
OR関数で論理和を求める
OR関数は論理和を求める関数です。
と書いて、論理式のうちどれか一つでも成立していればTRUEを返します。全てがFALSEだった場合にFALSEが返ります。
したがって、今回の例では2,4,6,9,11月のいずれかであればTRUEが返りつまり、計算式としては「–」を返すというわけです。
OR関数はAND関数とセットで覚えちゃうといいですね。
曜日をクールに出力する方法
続いて隣りのE列に曜日を入れていきたいと思います。
まさか、月、火、水、…と手打ちなんかしようとしてないでしょうね？ダメですよ。
もっと簡単にできます。
まずE3セルに
として、それをE4からE33までコピーをしちゃってください。
そしてE3からE33を選択した状態で
メニューの「表示形式」からたどって先ほども活躍した「カスタムの日付と時刻の形式」ウィンドウを出します。
「日(5)」がいると思いますが、その上下の三角マークをクリックするとプルダウンしますので、その中から「曜日の省略形(日)」を選択して「適用」します。
すると…
はい、バッチリ「曜日」に表示が変更されましたね。
曜日の手打ち入力とはもうオサラバですね。
エクセルでも似たようなことができますので、ぜひご活用下さい。
まとめ
今回は勤怠管理集計システムを作る第一歩目ということで、スプレッドシートの定番関数
- DATE
- MOD
- AND,OR
- IF
と日付や曜日の表示形式を使って、日付と曜日を簡単にかつクールに数式で出力する方法についてお伝えしました。
ちょっと面倒のような気がするかも知れませんが、これからシステムを作る上でものすごく活躍をしてくれます。
日付や曜日を扱うようなツールやシステムを作るときは必須のテクニックともいえると思うので、ぜひ活用して頂きたいです。
次回は、VLOOKUP関数を色々と使って区分、打刻の箇所をデータから引っ張ってくる箇所を進めていければと思います。
どうぞお楽しみに！
コメント
突然失礼致します。
ブログを拝見させて頂き、勤怠管理集計システムを作ってみようと思っているのですがつまづいております。
質問させて頂きたいと思います。
日付の末日について、
29日はうるう年ではない2月を判定する
まずD31セル、29日は
=IF(AND(B5=2,MOD(B4,4)0),”–“,D30+1)
とします。
と記載されている数式を入力するとエラーが起こってしまいます。
何か原因はわかりますでしょうか？
恐れ入りますが返答頂けたらと思います。
田嶋様
=IF(AND(B5=2,MOD(B4,4)=0),”?”,D30+1)
が正しいのかなと思いますが、いかがでしょうか？
29〜31日の処理は
D31に
=IF(MONTH(D30+1)=B$5,D30+1,”–“)
これをD33までコピーでどうでしょうか。
「上のセルの翌日が今月なら、翌日を表示。」という式です。
3日とも同じ式で大丈夫です。
難しい計算をしなくていいので便利です。