役立つ! 総務マガジン

Excelの3-D集計で毎月の休出と代休を管理

毎月の勤怠管理表から休日出勤日数を集計し、代休の残数も計算する

月ごとにワークシートを分けてExcelで勤怠を管理していると、年度を通した休日出勤や代休の日数が分かりにくくなります。そこで、ワークシートをまたいでデータを集計する「3-D集計」を使い、休日出勤と代休取得の日数、そして代休の残数を計算しましょう。

[2019年11月25日公開]

同じレイアウトのワークシートを串刺しにして集計できる

勤怠の管理は、総務の大事な仕事の一つです。休日出勤した社員には総務から代休取得を促すなどのルールを設けている企業も多いでしょう。

勤怠管理をExcelで行う場合は、月ごとにワークシートを分けているのが一般的だと思います。しかし、このようなファイルでは月ごとの休日出勤や代休取得は確認しやすくても、月をまたいでの確認を行うのは面倒になります。

そこで、Excelの「3-D集計」を利用して、ワークシートをまたいで休日出勤の日数などを集計しましょう。3-D集計を使うと、同じレイアウトの複数のワークシートから、指定したセルのデータを簡単に集計できます。月ごとの勤怠管理表の集計のような用途には、まさに最適な機能です。

今回は、勤怠管理表でまず月(ワークシート)ごとに休日出勤と代休取得の日数を集計します。その後、年度通算の休日出勤と代休取得の日数を集計したうえで、代休の残日数も確認できるようにしましょう。

勤怠管理表の完成イメージ

今回作成する勤怠管理表では「休日出勤日数」(セルE6とF6が合体したセル)で、その月の休日出勤日数を表示します。「代休消化日数」のセルG6ではその月の代休取得日数、セルH6では年度を通じた代休の残日数を表示します。代休の残日数は、年度内の休日出勤日数から代休取得日数を引いたものです。

毎月の休日出勤と代休消化の日数を集計する

まず、月ごとのワークシートで休日出勤の日数を集計します。「勤怠区分」列の毎月1日から31日までにあたるセル範囲C12~C42の中で、「休日出勤」がいくつあるかをCOUNTIF関数で数えます。

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

「勤怠区分」列は、リストで「休日出勤」「代休」などから選んで入力できるようにしておきます。リストについては第33回(Excel申請書の部署名欄を「リスト」で省力化)を参考にしてください。

第33回:Excel申請書の部署名欄を「リスト」で省力化

(1)月の休日出勤日数を集計する

[3月度]から[4月度]までのワークシートを選択した状態([3月度]ワークシートを選択しておき、[Shift]キーを押しながら[4月度]ワークシートをクリック)で[10月度]枠シートをクリックし、休日出勤日数が入るセルE6を選択して「=COUNTIF(C12:C42,"休日出勤")」と入力します。

[3月度]から[4月度]までのワークシートを選択しておくことで、1回の操作で全ての月のワークシートに同じ数式を入力できます。

複数のワークシートを選択した状態で、選択中のいずれのかのワークシートをクリックすると、選択状態のままワークシートの表示を切り替えられます。しかし、ブック内の全てのワークシートを選択している場合([3月度]~[4月度]以外のワークシートがない場合)では、選択状態が解除されてしまいます。この場合はワークシートを右クリックしていったんメニューを表示させ、メニューを選択しない(セルなどをクリックする)ことで、選択状態のままワークシートの表示を切り替えられます。

COUNTIF関数では、指定したセル範囲に、検索条件である「休日出勤」がいくつあるかを求めます。月により日数は30日、31日とまちまちですが、ここでは31日分のセル範囲を指定し、空欄であれば集計しないだけなので、全ての月で同じ数式を適用して問題ありません。

(2)月の代休取得日数を計算する

続けて代休取得日数が入るセルG6を選択し、「=COUNTIF(C12:C42,"代休")」と入力します。

(3)休日出勤日数と代休取得日数が表示された

「休日出勤日数」「代休取得日数」がそれぞれ集計されるようになりました。各月のワークシートに切り替えると、その月の休日出勤日数と代休取得日数を確認できます。

3-D集計で代休の残り日数を計算する

続けて、3-D集計で代休の残り日数を計算します。

3月度から4月度までの全てのシートの休日出勤日数(セルE6)の合計から、代休日数(セルG6)の合計を引きます。全ての月のワークシートに最新の残日数が表示されるようにして、月ごとに集計範囲を変えた「5月時点の残日数」のような計算はしないことにします。

休日出勤と代休の日数の3-D集計にはSUM関数を使います。引数としてのセル範囲を指定するときにワークシートを選択していくのが、3-D集計での特徴的な操作です。

SUM関数の構文
SUM(セル範囲)

(1)SUM関数の入力を開始する

[3月度]から[4月度]までのワークシートを選択して[10月度]ワークシートを表示した状態で、代休の残日数が入るセルH6を選択し、「=SUM(」と入力します。

(2)3-D集計の始点となるワークシートを選択する

3-D集計の始点となるワークシート[3月度]ワークシートをクリックします。すると「=SUM('3月度'!」と引数が入力されます。

(3)3-D集計の終点となるワークシートを選択する

続けて、[Shift]キーを押しながら終点となる[4月度]ワークシートをクリックします。すると、「=SUM('3月度:4月度'!」と表示されます。

(4)集計するセルを選択する

次に、集計対象であるセルE6をクリックします。「=SUM('3月度:4月度'!E6:F6」と引数が入力されたら末尾に「)」を入力して、休日出勤日数を集計するSUM関数を完成させます。

セルE6はセルF6と結合しているため、数式上は「E6:F6」となります。「'3月度:4月度'!」は、[3月度]ワークシートから[4月度]ワークシートまでの範囲が3-D集計の対象であることを表します。

(5)代休の取得日数の合計を引く数式を入力する

先に入力した数式に続けて「-SUM('3月度:4月度'!G6)」と入力します。この部分は、代休の取得日数の合計をマイナスする(休日出勤日数の合計から引く)ことを表します。先に入力したSUM関数をコピーして引数を書き換えても、「-SUM(」と入力してから休日出勤日数の場合と同様にワークシートを選択して入力しても構いません。完成した数式は「=SUM('3月度:4月度'!E6:F6)-SUM('3月度:4月度'!G6)」になります。

(6)代休の残日数が表示された

全てのワークシートのセルH6に、代休の残日数が表示されました。ただし、このままでは数字の意味が伝わりません。残日数だと分かるように表示形式を変更します。

(7)表示形式を変更する

[ホーム]タブ上の[セル]グループから[書式]をクリックし、[セルの書式設定]を選択します。

(8)ユーザー定義の表示形式を設定する

[セルの書式設定]ダイアログボックスが表示されました。[表示形式]タブ内の[分類]にある[ユーザー定義]を選択し、[種類]のテキストボックスに「"/残"0」と入力して[OK]をクリックします。

(9)表示形式が変更された

「/残7」と、「/残」に続いて日数が表示されるようになり、残日数のことだと分かりやすくなりました。

複数のシートを確認する手間を省いて集計をラクに

複数のワークシートにまたがるデータの集計は、シートを切り替えながら数式を入力する操作が独特で、初めは難しく感じられるかもしれません。しかし、一度入力してしまえば、1シートずつデータをチェックしなくてもよくなり、大幅な時短を実現できます。

3-D集計は、多数のワークシートのデータを比較的簡単な操作で計算できます。勤怠管理表のように月ごとに同じレイアウトの表でデータを管理しているケースでは、積極的に活用するといいでしょう。

協力メディア

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

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

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

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

Excel研修の詳細情報を見る

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

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

Office 365 について見る