役立つ! 総務マガジン

面倒な日数カウントをExcelで手早く完了

「欠勤」や「休日出勤」などの条件を指定して自動計算する

月末から月初にかけて特に忙しくなる総務の仕事。中でも給与に影響する出勤日数や勤務時間の集計は、忙しさの要因でもあり、とても重要でもある業務の一つでしょう。今回は三つのExcel関数を活用し、イレギュラーな条件でも計算を自動化する方法を見ていきます。

[2018年 7月9日公開]

手作業では限界がある勤怠表の管理業務

社員数が増えれば増えるほど複雑で時間がかかってしまうのが、締め日直後の勤怠管理業務です。出勤・欠勤日数を数えたり、休日出勤の勤務時間を合計したりする作業は、ミスが許されず気を使うものですよね。

しかし、出勤表のデータがきちんと記録されていれば、それらの集計はExcel関数で解決できます。実際の計算方法を見ていきましょう!

出勤日数はデータのあるセルを「COUNT」関数で数える

ここでは、社員ごとに一カ月分のデータがまとまっている勤務表を例にします。出勤日数を計算するには、出勤したことが分かる項目の一つ、例えば勤務時間の「合計」にデータがあるセルを数えていけばOKです。それには「COUNT」(カウント)関数を使います。

COUNT関数の構文
COUNT(値1, 値2, …, 値255)

(1)COUNT関数で出勤日数を数える

COUNT関数を挿入します。上図は[fx](関数の挿入)ボタンから、関数の引数ウィザードを表示した入力例です。引数を入力するテキストボックスにカーソルを置き、勤務時間の「合計」に当たるセル範囲を指定して[OK]をクリックします。セルや数式バーに直接、関数を含む数式(ここでは「=COUNT(J12:J42)」)を入力してもかまいません。

(2)出勤日数が求められた

全ての出勤日数がカウントされました。

欠勤や休日出勤は「COUNTIF」関数で条件を設定

欠勤や休日出勤の日数をカウントするには、勤務表にあらかじめ「勤怠区分」といった列を用意しておき、そこに「欠勤」や「休日出勤」を記録しておくようにします。それらをカウントする条件にすれば、欠勤日数や休日出勤日数を簡単に求められます。

そこで使用するのが、検索条件を満たすセルの個数だけを数える「COUNTIF」(カウントイフ)関数です。検索条件とは「もし〇〇が△△なら……」の△△に当たる部分。欠勤日数や休日出勤日数では、「もし勤怠区分が欠勤なら……」または「もし勤怠区分が休日出勤なら……」といった具合に設定していきます。

COUNTIF関数の構文
COUNTIF(範囲, 検索条件)

(1)COUNTIF関数で欠勤日数を数える

COUNTIF関数を挿入します。上図は[fx](関数の挿入)ボタンから、関数の引数ウィザードを表示した入力例です。ここでは[範囲]に「勤怠区分」のセル範囲、[検索条件]に「欠勤」と入力しました。[OK]をクリックすると、自動的に「欠勤」の文字列にダブルクォーテーションが追加されます。セルや数式バーに直接、関数を含む数式(ここでは「=COUNTIF(C12:C42,"欠勤")」)を入力してもかまいませんが、その場合は「欠勤」をダブルクォーテーション(")で囲むのを忘れないようにしましょう。

「勤怠区分」が「欠勤」のものだけをカウントし、欠勤日数が表示されました。

(2)休日出勤した日数を数える

続けて、休日出勤も同様に関数を設定していきます。ここでの[検索条件]には「休日出勤」を設定します。

「勤怠区分」に「休日出勤」と入力されているものだけをカウントし、休日出勤日数が表示されました。

休日出勤のみの勤務時間を合計するには「SUMIF」関数

休日出勤では給与計算を別にすることがありますが、休日出勤の勤務時間だけを合計するには、どうすればいいでしょうか? それには、条件に該当するデータだけを合計する「SUMIF」(サムイフ)関数を使います。

このとき、合計した勤務時間が24時間を超えると、正しく表示されません。第2回の「Excel時間集計あるある 表示形式を極める」で解説したように、表示形式を変更しておきましょう。

SUMIF関数の構文
SUMIF(範囲, 検索条件, 合計範囲)

(1)判定の根拠となる範囲と合計を求める範囲を確認する

SUMIF関数で必要な引数[範囲]は、二つめの引数[検索条件]に合致するかを判定する根拠となる範囲を設定します。三つめの引数[合計範囲]は、合計を求めたいデータが入力されている範囲です。それぞれがどの列に当たるかを確認しておきましょう。この例では「勤怠区分」を引数[範囲]に、勤務時間の「合計」を引数[合計範囲]に設定します。

(2)SUMIF関数を入力する

SUMIF関数を挿入します。上図は[fx](関数の挿入)ボタンを使った場合です。手順1で確認したとおり、[範囲]に「勤怠区分」のセル範囲、[検索条件]に「休日出勤」、[合計範囲]に勤務時間の「合計」のセル範囲を設定します。セルや数式バーに直接、関数を含む数式(ここでは「=SUMIF(C12:C42,"休日出勤",J12:J42)」)を入力してもかまいません。

休日出勤した分だけの合計時間が表示されました。

(3)セルの書式設定を開く

セルの書式設定をします。この例では休日出勤の合計が24時間を超えていませんが、超えている社員では誤った時間が表示されるため、ミスを予防しておきます。セルを選択して[書式]から[セルの書式設定]を選択するか、ショートカットキーの[Ctrl]+[1]を押します。

(4)ユーザー定義の表示形式を選択する

[セルの書式設定]ダイアログボックスの[表示形式]タブをクリックします。[分類]の中から[ユーザー定義]をクリックし、「[h]:mm」を選択しましょう。見当たらない場合は、[種類]のテキストボックス内に直接入力します。

この例では変化がありませんが、勤務時間が24時間を超えても正しい結果が表示されます。

正確な条件設定には、日ごろからのデータ入力も大事

条件に合ったデータだけを数えたり合計したりする「COUNTIF」関数と「SUMIF」関数は、使い慣れれば総務の業務で非常に便利に使うことができます。

ただ、「勤怠区分」の休日出勤を表すデータにおいて、「休日出勤」と「休出」のような表記揺れがあると、正確に探すことができなくなってしまいます。あらかじめデータの入力ルールを決め、日ごろから総務のスタッフ全員が徹底するようにしてください。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る

おすすめの記事