エクセルでシフトなどを管理する際には時間帯ごとの人数を抽出したい場合もあるかもしれません。関数などで人数がカウントできるとデータとして集計する際にも手間がかからずに便利ですよね。エクセルでは時間範囲を決めてそれぞれの値を判定してから振り分けするのがポイントです。今回は、Excelで時間帯によって出勤人数などをカウントし、集計したものをグラフにする方法をご紹介します。
◎Excel/エクセルで時間範囲を判定、時間帯ごとに抽出して表示するには？
Excelで時間帯ごとに集計してグラフにまとめる場合には、1時間ごとなどと大枠で表示することも多いかもしれません。そのためには、エクセルに書かれている時刻を時間帯で抽出する必要があります。エクセルで時間範囲で判定できる関数は2つあり、1つ目はHOUR関数です。時刻のデータの中から「●●時」に該当するもののみを取り出す関数で、基本的な式は『=HOUR (シリアル値)』で表されます。シリアル値には調べたいセル番地を参照しましょう。
エクセルで時間帯ごとに表示できる機能の2つ目は、LEFT関数です。Excelのデータの左側から指定した文字数のみを取り出すことができ、特に時間帯を抽出する場合には同じ形式で書かれていることが多いため利用しやすいでしょう。基本的な式は『=LEFT(調べるセル番地を文字列にしたもの,取り出す文字数)』で表されます。たとえば、エクセルのシフトで出勤した時間帯で人数を調べるのであれば『=LEFT(TEXT(C3,”HH:MM”),2) 』などです。ここでは、時刻を文字列にする必要があるため、TEXT関数を使用しました。
書式の設定によっては最初の表記が「9: ●●」や「8時」のように、10時より前の時間が1桁で表示されていることもあるかもしれません。もし、『=LEFT (セル番地,2) 』で取り出すと数字ではない文字列や記号まで含まれてしまいます。その場合には、IF関数と組み合わせて正確な時間を抽出できるようにしましょう。たとえば「9: ●●」のような表記なら『=IF(MID(調べるセル番地,2,1) =”:”, LEFT (セル番地,1), LEFT (セル番地,2) 』とすると桁数に応じて「●●時」の部分のみを取り出せます。
◎エクセルで時間帯ごとの人数をカウントできる関数は？
Excelに書かれたものを時間範囲で判定できるようになったら、次は実際に時間帯ごとの人数をカウントしてみましょう。エクセルで使いやすい関数は、COUNIF関数です。Excelで特定の文字が入っているセルをカウントすることで、その個数を結果として出します。基本的な式は、『=COUNTIF(調べるセル範囲,検索条件)』です。どのようになるのかを例を挙げてみていきましょう。
●関数を使って人数をカウントする［エクセル］
エクセルのシフトなどで時間帯ごとの人数をカウントするときには、「調べるセル範囲」のところを先ほどExcelで時間帯ごとに抽出した結果が書かれているセルを参照し、検索条件では求めたい時間範囲を指定しましょう。たとえば、10時台の人数を求めたいのであれば『=COUNTIF($F$3:$F$11,7)』などと入力します。数式をコピーするのであれば範囲は絶対参照にするのが良いでしょう。
●エクセルで出勤人数をカウント
Excelで出勤人数をカウントするといったように、その時点ではなく期間がある場合には少し工夫が必要です。開始時間と退勤時間がわかっているのであれば関数COUNTIFを組み合わせることで人数をカウントします。この方法はエクセルで滞在時間を振り分ける際などにも利用できるでしょう。具体的な式は『=COUNTIFS (セル範囲1,条件1,セル範囲2,条件2) 』です。
たとえば、正午に出勤している人数を数えるのであれば、12時までに出勤しかつ12時より前には退勤していない人数がカウントできれば求められます。『=COUNTIFS (出勤時間のセル範囲,”<=12″,退勤時間のセル範囲,”>12″) 』とすることで、Excelの各値が時間範囲に含まれているかを判定しましょう。
また、時間を気にすることなくエクセルでの打刻を基にその日の出勤人数をカウントしたい場合には、総数から休みの日数から逆算する方法も考えられます。『=DAY (EOMONTH (日数の範囲))-COUNTIF日数の範囲,”休”)) 』などと入力しましょう。
◎Excelで時間帯ごとに集計したものをグラフにする
エクセルで時間帯ごとに集計したものはグラフにすることでより見やすくなります。抽出した時間帯と数値の範囲を選択し、メニューにある『挿入』⇒『おすすめグラフ』を順番にクリックしましょう。好きなグラフの形式を選び、デザインや要素を編集することでお好みの形で集計結果を表示できます。
SUMPRODUCT関数を使って時間帯ごとに集計することも可能です。
『=SUMPRODUCT (*(HOURHOUR ())) 』
◎まとめ
今回は、Excelで時間帯によって出勤人数などをカウントする方法をご紹介しました。時間帯の表示を「●●時」のようにするのであれば、HOUR関数などを使って一度値から必要な要素を取り出すのがポイントです。エクセルで時間帯を集計してグラフにするのであれば時間を一方向に並べて抽出すると作成しやすいでしょう。最後までお読みいただきありがとうございました。
コメント