【スプレッドシート】データ集計の自動化(COUNTA、SUM、COUNTIFS)

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

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

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

今回は出勤回数や稼働時間を自動集計する方法についてお伝えします。

COUNTA関数、SUM関数、COUNTIFS関数を使って 稼働時間や出勤回数を自動集計 していきましょう。

たま

前回の記事では「プルダウンリストを作る方法」についてお伝えしました!


シリーズ全編で学ぶ項目

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



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

目次

前回記事のおさらい

前回の記事ではデータ入力規則を使ってプルダウンリストを作る方法についてお伝えしました。

このシートの続きをから作っていきます。

spreadsheet-aggregate20

前回の記事はこちら


見出しを作成する

セル「A34~A39」に以下項目を入力して見出しを作ってください。

A34:出勤時間
A35:稼働時間
A36:残業時間
A37:遅刻
A38:当日欠勤

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

spreadsheet-aggregate22

たま

それでは各項目のデータを自動集計するようにしていきましょう!



出勤回数を自動計算する

COUNTA(カウントエー)関数を使って出勤回数の合計値を算出します。

COUNTA関数は、指定した範囲の「空白でないセルの個数」を求めることができます。 数字はもちろん、文字や記号などが入力されているセルの合計値を自動で計算してくれます。

今回は、出勤時間を記入するセル範囲「C2〜C32」を集計します。休みの日は出勤時間のC列は空白となるため、時間が入力されている日数が出勤回数ということになります。

COUNTA関数の構文はこちら。

=COUNTA(値)

(値)に対象となるセル範囲を指定することで合計値を自動で計算してくれます。

完成した数式はこうなります。

=COUNTA(C2:C32)

この数式をセル「B34」に記入することで出勤回数の合計値が返されます。

セル範囲セル「C2」を選択した状態で、Shift (⇧ シフト)を押しながらセル「C32」をクリックすると「C2:C32」を選択できます。

spreadsheet-aggregate23

稼働時間の合計を自動計算する

SUM(サム)関数を使って稼働時間の合計値を算出します。

SUM関数は、指定した範囲の「合計値」を求めることができます。 稼働時間のセル範囲「F2〜F32」を集計していきましょう。

COUNTA関数の構文はこちら。

=SUM(値)

(値)に対象となるセル範囲を指定することで合計値を自動で計算してくれます。

合計値を求めたい対象範囲を指定するとこうなります。

=SUM(F2:F32)
spreadsheet-aggregate24

この数式では稼働時間のセルF2からF32までを加算した合計値を自動計算してくれます。

しかし、このままでは時間の合計がうまく表示されません。時刻の加算となっているため24時間以上の結果はおかしな合計値になってしまいます。

この問題を解決するためには、時刻に「24」を掛けて「表示形式」を変更します。

完成した数式はこうなります。

=SUM(F2:F32)*24
spreadsheet-aggregate25

「*」は掛けるを意味します。

対象のセル「B35」を選択した状態で、メニューバーの表示形式から数字>数値を選択することで時刻から時給計算できる数値に変更することができます。

この変更により、給与計算できる合計値を表示することができます。


たま

深く考えずに「時間を数値に変更するには24を掛ける」と覚えておきましょう!


残業時間の合計を自動計算する

稼働時間の計算と同様に、SUM関数を使って残業時間の合計値を算出します。

求めたい対象範囲のセルを指定し「24」を掛けて「表示形式」を変更しましょう。

完成した数式はこうなります。

=SUM(G2:G32)*24
spreadsheet-aggregate28

この数式で残業時間のセル「G2」から「G32」までを加算した合計値を自動計算してくれます。

対象のセル「B36」を選択した状態で、メニューバーの表示形式から数字>数値に変更すれば完成です。


遅刻・当日欠勤の回数を自動計算する

COUNTIFS(カウントイフス)関数を使って、「遅刻」「当日欠勤」の合計回数を算出します。

COUNTIFSは、指定した条件に合うデータを検索し、検索したデータに一致するセルの個数を求めることができます。

COUNTIFS関数の構文はこちら。

=COUNTIF(❶範囲,❷検索条件)

それでは、各項目の一致する個数を算出していきましょう。

区分の対象範囲から「遅刻」に一致する個数を求める数式はこうなります。

=COUNTIFS(H2:H32,"遅刻")
spreadsheet-aggregate26

数式を日本語にすると、

  • 区分のセル範囲「H2:H32」を検索。
  • 「遅刻」に一致するデータの個数を求める。

という意味になります。

この数式をセル「B37」に記入することで遅刻回数の合計値が算出されます。

残りの「当日欠席」も同様に、COUNTIFS関数を使って一致する個数を求めましょう。

セルB38に記入する「当日欠勤」に一致する個数を求める数式はこうなります。

=COUNTIFS(H2:H32,"当日欠席")
spreadsheet-aggregate27

検索範囲は同じなので ❷検索条件 の文言を変更するだけです。


たま

セル「B37」の数式をコピペして文言を変更してください!


まとめ

今回はGoogleスプレッドシートでのCOUNTA関数、SUM関数、COUNTIFS関数の使い方についてお伝えしました。

これら関数は、範囲の合計金額や条件に合ったデータ個数を手計算することなく自動集計してくれます。

使えると使えないとでは作業効率に大きな差が出ます。積極的に関数を活用して面倒な事は自動化できるようにしていきましょう。

次回は、文字・背景色や枠線の変更などの表をデザインする方法についてお伝えします。