はじめに

この動画ではエクセルでアルバイトスタッフの給料計算表を作成する方法を紹介します。

給料計算を自動化することで、給料の計算ミスを減らすことができます。

また、アルバイトスタッフの方であれば、自分に支給された給料が間違っていないかを確認するのにも使えるので、是非活用してみてください。

完成形の確認

それでは、まずは完成形を確認しましょう。

ファイル(ブック)は月ごとに分けて使います。

計算表のシートは、1人のアルバイトスタッフにつき、1シートずつ割り当てられています。

設定のシートに年月を入れると、自動で、全ての従業員のシートに、その月の日数と曜日が表示されます。

計算表はここに通常時給を入力します。するとその下に深夜・残業手当である25%の給料が表示されます。設定のシートで、最低賃金を設定してその金額以下は入力できないようにしています。

下の表のタイムカードの欄に、このようにタイムカードの時刻を入れていくと、

• 実働時間と
• そのうちの
  • 深夜の勤務時間(22時～5時)と
  • 残業時間(時間外労働)(8時間以上の勤務時間)

を計算して、

• その合計の勤務時間と
• 給料

を計算することができます。

また、動画の後半では、表のデザインをこのように縞々にして見やすさを整えたり、意図しない文字や数字は入力できないように制限をかける方法についても紹介しますので、是非最後までご覧ください。

途中で解説が分からなくなっても、同じように入力すれば、正しく機能しますし、動画説明欄には、完成ファイルの配布URLを貼っていますので、そちらも活用してみてください。

それでは早速作成していきます。

型

まずは、項目名などの型を入力していきます。

右へ情報を入力していく際は、Tabキーを押すと、入力を確定して選択セルを右に移動することができるので、素早く入力できます。

下へ情報を入力していく際は、Enterを押すと、入力を確定して選択セルを下に移動することができるので、素早く入力できます。

セルをまたいで情報を表示させるには、そのセルをドラッグで複数選択した状態で、上のホームタブから、配置グループのセルを結合して中央揃えをクリックします。中央揃えにしたくない場合は、この右のアイコンをクリックして、セルの結合をクリックします。

Ctrlキーを押しながらYまたは、F4キーで、直前のアクションを繰り返すことができるので、2回目以降はこのキーで素早くセルを結合することができます。

次に給与計算表の文字と年月の文字サイズを変えます。まず、文字サイズを変えたいセルを選択します。給料計算表をクリックして、Ctrlキーを押しながら、年月をドラッグで選択します。

文字のサイズを変えるには、文字を変えたいセルを選択した状態で、ホームタブの中からフォントグループの、この数字を変えることで、文字のサイズが変わります。今回は28サイズに変えておきます。

年月の表示形式

次に、ここの年月に応じて自動で、日付と曜日が表示されるように設定していきます。

曜日を自動で表示するには、日の欄に日だけでなく年月の情報も入力する必要があります。よって日の欄には年月日の情報が入力されているけども、日だけ表示するという設定をしていきます。

年月の数字を参照して、日付を表示するには、参照先のデータが数値データである必要があります。現在、年月の欄には年と月が入ってしまっているので、エクセルでは文字とみなす文字列データと認識されています。ただ年月が無いとなんの数字か分からなくなってしまうので、数字のみ入力されているけども、表示上のみ年と月がついているという状態にする必要があります。

そのためには、まず、年と月を削除して、数字のみ入力された状態にします。F2キーかダブルクリックでセルを編集モードにして、年と月を削除します。

削除し終わったら見た目上のみ年をつけていきます。年のセルをクリックして選択したら、Ctrlキーを押しながら1を押します。するとセルの書式設定画面が表示されます。書式というのは、文字やセルの見た目のことなので、この画面では、文字やセルの見た目を変更することができます。

セルの書式設定画面が表示されたら、表示形式のタブを開きます。今回のように、実際の値と表示を変える機能のことを、表示形式機能と言います。

表示形式にはいくつかテンプレートが用意されていますが、設定したい表示方法がテンプレートにない場合は、ユーザー定義を選択して自分でカスタマイズする必要があります。

ユーザー定義を選択したら、種類の欄に0”年”と入力します。これで数字の後ろに年を付けるという意味になります。OKをクリックすると、入力されている情報は数字のみですが、表示上は年を付けることができました。

同じように月にも適用していきます。月のセルをクリックして選択したら、Ctrlキーを押しながら1を押してセルの書式設定画面を開きます。表示形式のタブからユーザー定義を選択して、0”月”と入力します。OKをクリックすると、表示上のみ月をつけることができました。

日付

次に、今入力した年月を参照して、日付を入力していきます。1日目の欄に『=DATE(H1,J1,1)』と入力します。

すると、その月の一日の日付が表示されました。

DATE関数では

• 1つ目の引数に年
• 2つ目の引数に月
• 3つ目の引数に日

を入れることで、日付データを作成する関数です。

2日目は1日目の日付に＋1することで作成します。

2日目の欄に

=A11+1と入力します。

すると2日目の日付を入力できました。

次にこのセルをクリックして選択した状態で、そのセルの右下の四角をクリックしながらドラッグすることで、他の日付を作成することができます。これで、31日分作成しましょう。

このようにシャープが表示されるのは、列の幅が足りなくて情報を表示し切れない場合に起こります。列名のAとBの間をダブルクリックすると列を自動調整することができます。幅は今後も適宜、調整してください。

31日分作成すると、31日までない月の場合、次の月が表示されますが、これの非表示方法については後半で紹介します。

罫線

次に、このような枠線を付けていきます。エクセルではセルの境目のことを罫線と言います。

枠線をつけたい範囲をドラッグで選択して、ホームタブのこの罫線メニューの横の下矢印から任意の物を選びます。

広い範囲のセルを複数選択するには、まず1つ目の範囲の端のセルをクリックして、範囲の対角側のセルをShiftキーを押しながらクリックします。すると1つ目の範囲が選択できるので、次に2つ目の範囲の端のセルをCtrlキーを押しながらクリックします。すると2つ目の範囲の端も同時に選択された状態になるので、2つ目の範囲の対角の端をShiftキーを押しながらクリックします。

これを繰り返して複数の範囲のセルを選択した状態で、書式設定を行うと、同時に複数のセルに書式設定を適用することができます。

同じ操作を繰り返す場合は、Ctrlキーを押しながらYキーを押すことで、以前使用した罫線メニューを繰り返し利用することができるので、効率があがります。

曜日

次に、日付に応じて自動で曜日が表示されるように設定していきます。

日付に応じて自動で曜日が表示されるようにするには、TEXT関数を使ってこのように入力します。

=TEXT(A11,”aaa”)

Enterで確定すると日付に応じて曜日を表示することができました。

TEXT関数は、数値に表示形式を適用して、文字列データに変換することができます。1つ目の引数に変換したい数値を入力し、2つ目の引数ではどのような表示形式を適用するかを指定することができます。

今回は曜日を表示したいので“aaa”と入力しています。

もし、曜日まで表示したい場合はaを4つにすると、曜日まで表示することができます。

他にも、

• ddddと入力すれば、曜日を英語で表示することができますし、
• dddと入力すれば、短縮形で表示することができます。

TEXT関数を入力できたら、そのセルを選択して、右下の小さい四角をダブルクリックすると、他の列の1番下まで伸びてる行、つまり31日目の行まで自動でコピーされます。日付に応じて曜日が自動で表示されていることが確認できると思います。

表示形式(日)

次にこの日付の日の部分だけ表示するという設定をしていきます。

こちらもセルの書式設定の、表示形式機能を使っていきます。

日付の端をクリックして選択した状態で、もう片方の端をShiftキーを押しながらクリックします。すると、その範囲を選択することができます。この状態で、Ctrlキーを押しながら1を押して、セルの書式設定を開きます。表示形式のタブから、ユーザー定義を選択して種類の項目のd以外を削除します。これで、日だけを表示するという意味になります。dはdayのdです。

ちなみにyは年、mは月を意味します。

入力が終わったら、OKをクリックすると、日だけの表示になりました。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

今回は

ファイル名を『給料計算表(原本)』として、

保存場所はデスクトップを指定します。

実働時間の算出方法(概念)

次に実働時間を自動で算出できるようにします。正しく計算されているかを確認するために、タイムカードの欄に仮で時刻データを入れておきます。

時刻データは半角数字と半角コロンを使って入力してください。

実働時間を自動で算出する計算式を作るには別の列にこのような表を作成する必要があります。

• 退社時刻と
• 休憩終了時刻は

日付をまたいで休憩を取ったり、勤務した時のために必要です。

例えば、22時から8時まで勤務したとすると、8:00-22:00という計算になるため、-14:00となってしまい、正しく計算されません。

同じく、休憩時間も23時～0時に休憩を取ったとして、単純に休憩時間を算出しようとすると、0:00-23:00なので、計算結果が-23:00となってしまい正しく計算されません。

よって、

• 退社時刻が出社時刻より小さい場合は、退社時刻に24時間を足して、32:00-22:00という計算をすることで、正しく計算できるようにします。
• 同じく休憩終了時刻も、休憩終了時刻が休憩開始時刻より小さい場合は、休憩終了時刻に24時間を足して、24:00-23:00という計算をすることで、正しく計算できるようにします。

※48時間以上勤務した場合、計算できないことになります。

この計算用の退社時刻と休憩終了時刻を使って、右側に時間帯ごとの拘束時間と休憩時間を算出しています。

日本の法律では、22:00~早朝5:00までは深夜手当をつける必要があるので、このように時間を分けています。

この時間ごとの拘束時間と休憩時間を元に、実働時間と深夜の勤務時間を算出し、実働時間が8時間を超える場合は、残業時間を表示するという仕組みになっています。

計算用 退社時刻

まずは項目名を作っていきます。

同じ情報を繰り返し入力する場合は、その範囲をドラッグで選択して、右下の小さい四角をドラッグすることで、入力できます。

セルを結合して、見た目を整えます。

罫線も引いておきましょう。

次に計算用の退社時刻を作成していきます。このように入力します。

=IF(F11=””,0,IF(F11>C11,F11,F11+VALUE(“24:00”)))

※0は無くても同じ動作をしますが、分かりやすくするために0を入れて作成しています。

※F11=””　は　ISBLANK(F11)　でも同じ機能を果たします。

数式について解説します。IF関数は、条件によって表示を変えることができます。1つ目の引数には、条件を。2つ目の引数にはその条件を満たした場合に表示する値。3つ目の引数にはその条件を満たさなかった場合に表示する値を入力します。

ここでは、IF関数の中にIF関数が入っていてややこしいですが、順番に解説していきます。

まず1つ目のIF関数の1つ目の引数では、F11、つまり退社時刻が空白かどうかを確認しています。ダブルクォーテーションは数式や数値以外の文字列データを入力したい際に挟んで使います。今回はダブルクォーテーションの間には何も挟まれていないので、F11に何も入力されていない場合、という意味になります

2つ目の引数では、F11が空白だった場合に表示する値を入力しています。ここでは退社時刻が空白だったら0を返すという意味になります。

※0は無くても同じ動作をしますが、分かりやすくするために0を入れて作成しています。

3つ目の引数では、F11が空白で無かった場合、つまり退社時刻が入力されてる場合の値です。ここで、さらにIF文で分岐します。

1つ目の引数は、F11セルがC11セルより大きければ、つまり退社時刻が出社時刻より大きければという意味です。

一般的には退社時刻は出社時刻の後にくるので、F11セルの方が数字が大きくなり、2つ目の引数が適用されて、F11セル、つまり退社時刻をそのまま表示します。

退社時刻が出社時刻より小さい場合、つまり、深夜0時をまたいで、休憩した場合は、出社時刻の方が大きくなるので、F11セルの退社時刻に24時間を足しています。

VALUE関数は文字列を数値に変換する関数です。

以上の数式で、計算用の退社時刻を表示することができるんですが、ここで数字が正しく表示されていない問題が発生します。

これは、エクセルにおいて、時間の処理は24時間を数字の1として処理していることが原因です。

一例

• 3:00 → 0.1250
• 6:00 → 0.2500
• 9:00 → 0.3750
• 12:00 → 0.5000
• 15:00 → 0.6250
• 18:00 → 0.7500
• 21:00 → 0.8750
• 24:00 → 1.0000

これをシリアル値と言います。

先ほど入力したタイムカードの時刻も表示上はコロンがついて時刻表示になっていますが、実態はこのようなシリアル値です。

つまり、シリアル値で計算して、そのままシリアル値で表示されているということです。表示方法を変えるには表示形式設定していきます。

表示形式を設定するセルをクリックして、選択した状態で、Ctrlキーを押しながら1を押して、セルの書式設定メニューを開きます。表示形式のタブからユーザー定義を選択して、このように入力します。[h]:mm;;

OKをクリックすると、このように時刻表示をすることができました。

今回の表示形式はセミコロンで区切られていて、1つ目はセルに入力されている情報が数字かつ正の数字の場合の表示形式、2つ目はセルに入力されている情報が数字かつ負の数字の場合の表示形式、3つ目にはセルに入力されている情報が数字の0である場合の表示形式を設定します。

今回1つ目には、時刻形式で表示するように設定しています。hは時間、mは分を表すので、コロンで区切ることで、シリアル値を時刻形式に変換して表示するという意味になります。

エクセルの仕様上、時間を計算して、24時以上になった場合、0時にリセットして表示されます。

例

• 24:00 → 0:00
• 26:00 → 2:00

そこで時間を表すhを角括弧(かくかっこ)で挟むと24時間以上を表示することができます。

2つ目の項目は、退社時刻は負の数字になることは無いので何も入力していません。

3つ目の項目は、0の場合は何も表示したくないので、何も入力していません。

以上が、シリアル値を時刻形式に変換して表示する方法です。

次にこの数式を他の行に適用していきます。今、入力したセルをクリックして選択して、Ctrl + Cでコピーします。そのまま31日目の行でShiftキーを押しながらクリックします。すると範囲を複数選択することができます。この状態でCtrl + Vを押すと、他の行にも数式を適用することができます。

計算用 休憩終了時刻

次に計算用の休憩終了時刻を作成していきます。

こちらは、退社時刻と同じような公式で算出できるので、今作成した数式をコピーして、一部を編集する形で作成していきます。

F2キーかダブルクリックでセルを編集モードにしたら、Ctrlキーを押しながらAですべてを選択して、Ctrlキーを押しながらCでコピーして、Tabキーで右のセルに移動します。その状態でCtrlキーを押しながらVで貼り付けることができます。

そして、F11をE11に、C11をD11に置き換えます。

=IF(E11=””,0,IF(E11>D11,E11,E11+VALUE(“24:00”)))

※0は無くても同じ動作をしますが、分かりやすくするために0を入れて作成しています。

※また、E11=””　は　ISBLANK(E11)　でも同じ機能を果たします。

入力したセルを選択して、Ctrlキー押しながら1を押して、表示形式を変更します。表示形式タブからユーザー定義を選択して、[h]:mm;;と入力して、OKで確定します。

先ほどとは違い、同じ表内にデータ入っているので、右下の小さい四角をダブルクリックすると、オートフィル機能で31日分の数式が作成できます。

これで、計算用の退社時刻と休憩終了時刻を作成することができました。

次に時間ごとの拘束時間を算出していきます。

時間ごとの拘束時間

時間ごとの拘束時間を算出するには、

=MAX(0,MIN(計算用の退社時刻,算出したい時間帯の終了時刻)-MAX(出社時刻,算出したい時間帯の開始時刻))

で算出することができます。

実際に、N11に、0時～5時の拘束時間を算出するにはこのように入力します。

=MAX(0,MIN(L11,”5:00″)-MAX(C11,”0:00″))

数式を簡単に説明します。

MIN関数では引数の中から1番小さい数字が返されます。ここでは、計算用の退社時刻と5:00を比べて小さい方の数字が返ります。

MAX関数では逆に引数の中から一番大きい数字が返されます。ここでは、計算用の出社時刻と0:00を比べて大きい方の数字が返ります。

最後に、0とその2つの数字を引いて算出された数字の大きい方が表示されます。

これで、0時～5時の拘束時間を算出することができます。

入力したセルを選択して右下の小さい四角をダブルクリックして計算式を他の行にも適用します。

この公式を使って、5時～22時、22時～29時、29時～48時の拘束時間も算出していきます。

先ほど入力した式をコピーして書き換えると素早く入力できます。先ほど入力したセルをダブルクリックか、F2で編集モードにして、Ctrl + Aでセル内の全ての情報を選択します。この状態でCtrl+Cでコピーして、他の時間帯にCtrlキーを押しながらVキーで貼り付けます。

そして、算出したい時間帯の

• 開始時刻と
• 終了時刻

を書き換えます。

左が終了時刻で、右が開始時刻なので、間違えないように、気を付けてください。

※ここでは48時間としています。「業種」「業界」「職種」に合わせて、調整してください。

書き換え終わったら、書き換えたセルをドラッグで選択した、右下の小さい■をダブルクリックして、計算式を他の行にも適用します。

時間ごとの休憩時間

休憩時間も同じような公式になります。

=MAX(0,MIN(計算用の休憩終了時刻,算出したい時間帯の休憩終了時刻)-MAX(休憩開始時刻,算出したい時間帯の休憩開始時刻))

それぞれの時間帯の拘束時間をコピーして、参照するセルを書き換えると素早く作成できます。

F2で編集モードにして、Ctrl + Aでセル内の全ての情報を選択して、Ctrl+Cでコピーして、Ctrlキーを押しながらVキーで貼り付けます。

次に参照セルである

• L11をM11に
• C11をD11

に置き換えていくんですが

1つ1つ変えていくのは手間なので、一瞬で置き換える方法を紹介します。

対象となるセルの1つ目をクリックして、2つ目以降はCtrlキーを押しながらクリックして選択します。

この状態でCtrlキーを押しながらHを押すと、置き換えのメニューが表示されます。

検索する文字列の欄に置き換え前の文字、置き換え後の文字列の欄に置き換え後の文字を入力します。

1つ目はL11をM11に置き換えたいので、このように入力します。この状態で、すべて置き換えをクリックすると、置き換えることができます。

次に、C11をD11に置き換えたいので、このように入力します。この状態で、すべて置き換えをクリックします。

閉じるをクリックすると、数式の参照セルが置き換えられてることが分かります。

右下の小さい四角をダブルクリックして、他の行にも計算式を適用しておきます。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを上書き保存しておきましょう。

実働時間を算出

次に、実働時間を算出します。拘束時間-休憩時間で算出できるので、このようになります。

=N11+P11+R11+T11-O11-Q11-S11-U11

入力できたらそのセルをクリックして、選択した状態で、右下の小さい四角形をダブルクリックして、他の行にも適用します。

深夜時間を算出

次に深夜の勤務時間を算出します。深夜時間は22時から5時なのでこのようになります。

=N11+R11-O11-S11

同じく右下の小さい四角形をダブルクリックして、他の行にも適用します。

残業時間を算出

次に残業時間を算出します。ここでは8時間を超えた時間のことを残業時間として扱います。

実働時間から8時間を超えた時間を算出するにはこのように入力します。

=IF(G11<=TIME(8,0,0),0,G11-TIME(8,0,0))

※0は無くても同じ動作をしますが、分かりやすくするために0を入れて作成しています。

ここでもIF文を使います。1つ目の引数では、実働時間が8時間以上あるかを確認しています。<=は小なりイコール(≦)という意味で、TIME関数は、時間と分と秒を入れて、時刻を返すことができる関数です。これは8時という意味になります。

2つ目の引数では、実働時間が8時間以下だった際に、表示する値を入力します。実働時間が8時間以下の場合は残業無しなので、0を表示するように設定しています。

3つ目の引数では、実働時間が8時間以上だった際に、実働時間から8時間を引いた時間を返しています。これで、残業時間を算出することができます。

c右下の小さい四角形をダブルクリックして、他の行にも適用します。

表示がシリアル値になっているので、表示形式を変えたいセルの端を選択した状態で、もう片方の端をCtrlキーとShiftキーを押しながらもう片側の方向の方向キー、つまりここでは下キーを押すと連続するデータの範囲を入力することができます。

そのままCtrlキーを押しながら1キーを押して、セルの書式設定を開きます。表示形式タブからユーザー定義を選択して、[h]:mm;;と入力して、OKで確定します。

するとシリアル値を時刻表示に切り替えることができました。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを上書き保存しておきましょう。

給料計算

次にこの実働時間を元に給料計算を行っていきます。

時給

まず、通常時給の欄には手動で時給をいれます。ここでは分かりやすいように仮で1000と入れておきましょう。

深夜手当と残業手当は25%なのでこのように算出できます。

=G5*0.25

エクセルでは掛け算はアスタリスクで行います。

入力したセルをF2キーかダブルクリックで編集モードにして、Ctrlキーを押しながらAキーでセル内の全ての情報を選択します。この状態で、Ctrlキーを押しながらCキーでコピーして、残業のセルにCtrlキーを押しながらVキーで貼り付けます。

勤務時間

次に勤務時間の合計を算出していきます。

勤務時間の合計の算出にはSUM関数を使います。SUM関数は引数で指定した数字を全て足す関数です。

=SUM(と入力し、合計する範囲の上の端をクリックして、CtrlキーとShiftキーを押しながら下キーを押して連続するデータの端まで入力します。そのままEnterを押すと勤務時間の合計が算出されました。

同じように深夜の勤務時間と残業時間を算出しましょう。

給料

次に給料を算出します。

給料は時給×勤務時間なので、このように算出します。

=G5*H5*24

時間はシリアル値で計算されているので、24をかけることで、正しく計算をすることができます。

ここで、表示形式が時刻データになってしまっているので、通常の数字に戻します。表示形式を変えたいセルを選択して、ホームタブから数値グループで、標準を選択します。

※小数点以下の表示については後ほど調整します。

表示形式を変更できたら、そのセルを選択して、右下の小さい四角形をダブルクリックして、深夜・残業時間の給料も算出します。

総支給

最後にこの3つ給料をSUM関数で足して総支給を算出します。

=SUM(と入力して、合計するセルをドラッグで選択します。Enterで確定すると総支給が算出されます。

ドラッグでセルを選択して、ホームタブからセルの結合で、下のセルと結合しておきましょう。

総支給の小数点以下に注目してください。日本では小数点以下の賃金を払うことができないので、小数点以下の処理方法を考えます。

労働基準法上、原則賃金は切り捨てすることはできません。四捨五入できる賃金もありますが、条件等が複雑なので、ここでは全て切り上げするように設定しておきます。

総支給の小数点以下を切り上げて表示するには、ROUNDUP関数を使います。

=以降の数式Ctrlキーを押しながらXで切り取って、ROUNDUP(と入力し、Ctrlキーを押しながらVキーで貼り付けます。次にカンマ0)と入力します。

ROUNDUP関数では1つ目の引数に切り上げる数値データを入力して、2つ目の引数にいくつめの位を表示するかを設定します。ここでは0を入力することで、一の位を表示して、それ以下の数字は切り上げるように設定しています。

これで、小数点以下を全て切り上げて表示することができました。

以上で基本的な計算は終了です。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを上書き保存しておきましょう。

誤差修正

ここで、エクセルの時間計算における誤差について理解しておく必要があります。

エクセルでの時間の計算は、先ほど紹介したシリアル値という数字で計算しています。

一例

• 3:00 → 0.1250
• 6:00 → 0.2500
• 9:00 → 0.3750
• 12:00 → 0.5000
• 15:00 → 0.6250
• 18:00 → 0.7500
• 21:00 → 0.8750
• 24:00 → 1.0000

シリアル値とは先ほど紹介した、24時間を数字の1として処理する考え方です。

シリアル値は、時間/24で算出することができますが、時間によっては割り切れない場合があります。その場合、小数点15位を四捨五入して計算します。つまり、時刻をシリアル値で計算すると、小数点15位を四捨五入した数字で計算するので、多少の誤差が発生します。

この誤差があると、時間を比較する際に不具合が生じたり、場合によっては給料の計算ミスが発生します。

給料に計算にミスがあると、法に触れる可能性もあるので、この誤差は極力減らす必要があります。

小数点15位という小さい数字を四捨五入しているので、1回の計算であれば問題ないですが、何度も計算していると、誤差は開いていきます。

今回であれば、5回、時間を計算しています。

この誤差を実務上問題ないレベルに抑えるには、時間を計算する度に、一度文字列の時刻データに変換してから、数値の時刻データに戻すという操作をする必要があります。この変換を行うことで、時刻データを入力時の状態、つまりに一度も計算していない時刻データに変換することになるので、誤差を最小限に抑えることができます。

この変換を行うには、このように入力します。

=VALUE(TEXT(変換したい時刻データ,”[h]:mm”))

TEXT関数は、数値に表示形式を適用して、文字列データに変換することができます。1つ目の引数に変換したい数値データを入力し、2つ目の引数ではどのような表示形式を適用するかを指定することができます。2つ目の引数は、今回は時刻データなので、”[h]:mm”(“角括弧h角括弧閉じコロンmm”)と入力して、時間コロン分という文字列データに変換することができます。24時間以上を表示する項目もあるので、hは角括弧で囲っておきます。

VALUE関数は数値データに変換する関数なので、最後にその文字列データをVALUE関数で数値データに変換します。

それでは、計算結果を時間で表示しているセル全てに、この変換を設定していきます。

まずは、実働時間に適用していきます。

イコール以降の数式をドラッグで選択して、Ctrl＋Xで切り取ります。そしてVALUE(TEXT(と入力したら、Ctrl+Vで貼り付けて,”[h]:mm”))と入力します。

これを繰り返して他の列にもVALUE関数とTEXT関数を入力していきましょう。

・・・

右下の小さい四角をクリックして他の行にも適用します。

これで、時間計算の誤差を実務上問題ないレベルまで無くすことができました。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを上書き保存しておきましょう。

デザイン

次に見た目を整えていきましょう。

テーブル

まずは、各表の見出しに濃い背景色を付けて、データを入れる行を縞々にする方法を紹介します。

各表の見出しに濃い背景色を付けて、データを入れる行を縞々にするには、テーブルという機能を設定していきます。

テーブルというのは表という意味で、表にテーブルの設定をすることで、エクセルに表であることを伝えて、表のデザインを簡単に変更したり、データを並び替えたり・絞り込んだりできるようになります。

集計

テーブルを設定するには、設定したい表のどこかを選択して、Ctrlキーを押しながらTキーで設定することができます。

Ctrlキーを押しながらTキーを押すと、このように、テーブルを設定する範囲を選択するメニューが表示されます。基本的にはこのように自動で選択されますが、希望通り選択されていない場合は、表の端をクリックしながら、対角側の端までドラッグして、選択しなおしてください。

先頭の行は見出しなので「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認したら、OKをクリックします。

すると、テーブルが設定されて、自動で縞々のデザインになりました。

もし細かくデザインを変更したい場合は、テーブルデザインタブのテーブルスタイルのオプションとテーブルスタイルのグループから変更することができます。

テーブルを設定すると、セルの結合が解除されるので、デザインが適用できたら、テーブルを解除します。テーブルを解除しても、デザインは維持されたままになります。

テーブルを解除するには、解除したいテーブルのどこかを選択した状態で、テーブルデザインタブから、範囲に変換をクリックして、確認画面が表示されるので、はい、をクリックするとテーブルを解除することができます。

総支給のセルを結合し直しておきましょう。

タイムカードと実働

他の表にも同じように適用していきます。

設定したい表のどこかを選択して、Ctrlキーを押しながらTキーを押します。

見出しは1行しか設定できないので、テーブルを設定する範囲の一番上が10行目になるように設定していきます。

テーブルを設定する範囲の端をクリックして、選択して、対角側の端をShiftキーを押しながらクリックします。

「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認したら、OKをクリックします。

タイムカードと実働時間の表は土曜日を青色に、日曜日を赤色に変えたいので、表の背景色を黄色に変えておきます。表のどこかをクリックして選択した状態で、テーブルデザインから黄色のデザインを選択しておきます。

計算用の表にも同じようにテーブルを設定します。

テーブルに設定する範囲は先に選択することもできます。テーブルを設定したい範囲の端をクリックして、対角側の端をShiftキーを押しながらクリックして、範囲を選択した状態で、Ctrlキーを押しながらTキーを押します。

同じように黄色にしておきます。

罫線

一部罫線が消えてるので、罫線を付け加えておきます。

￥

次に、給料に、円マークと3桁ごとのカンマを付けていきます。

実際に円マークとカンマを付けるのではなく表示上のみ付けるので、表示形式機能を使います。

表示形式を変えたいセルをドラッグで選択します。離れたセルを選択する場合はCtrlキーをしながら選択します。選択できたらホームタブの数値グループからこの通貨のアイコンをクリックします。

すると、円マークと3桁ごとのカンマをつけることができました。

ついでに小数点以下の数字を非表示にすることができました。小数点以下は非表示になっているだけで、実際に計算はされているので、問題はないです。小数点以下を表示したい場合は、表示したいセルを選択した状態で、ホームタブの数値グループのこのアイコンをクリックすることで小数点以下を増やしたり減らしたりすることができます。

曜日　土日

次に、条件付き書式機能を使って、土曜日の行を青色、日曜日の行を赤色に変えていきます。

条件付き書式というのは、指定した条件に応じて、書式を変更することができる機能でうｓ。

これを利用することで、年月が変わって、土日のセルが移動しても、自動で色を塗り替えることができます。

色を変えたいデータの端をクリックして選択したら、対角側の端をShiftキーを押しながらクリックして色を変えたい範囲を選択した状態にします。計算用の表も色を変えたいので、Ctrlキーを押しながら、計算用の表のデータ入力欄の端をクリックして、対角側の端をShiftキーを押しながらクリックします。

選択できたらホームタブから条件付き書式をクリックして、新しいルールをクリックします。

ここで、どのような条件で、どのような書式を適用するかを設定していきます。

今回は『数式を使用して、書式設定するセルを決定』を選択して、まずは書式を適用する条件を設定していきます。

今回はこのように入力します。

=$B11=”土”

※解説は後ほど

次に書式をクリックして、条件を満たしたときの書式を設定していきます。まず、文字の色を変えていきます。フォントタブから色の欄を青に変えます。

次にセルの背景色を水色に変えます。塗りつぶしタブから水色をクリックします。

設定が終わったら、OKをクリックすると土曜日の行を青に変えることができました。

ここで、条件で設定した数式について解説します。

=$B11=”土”

でB11セルが”土”だったら、という意味になります。

一番左のイコールは、数式入力のため必要です。

土は文字列のため、ダブルクォーテーションで挟むというルールにより、ダブルクォーテーションで挟んでいます。

Bの手前にあるドル記号は、Bを絶対参照に設定するという意味になります。

ここで絶対参照について解説します。

条件付き書式では、

条件(〇〇が✕✕の場合)、適用先(△△を)、書式(☐☐する)

3つの設定で成り立っています。

適用先が複数ある状態で、条件の数式でドル記号を付けずにセルを指定した場合、適用先の1番上のセル、複数ある場合は1番左のセルを起点として、相対的な位置が他の適用先のセルからも参照されます。

ここから—————————————————————————————————————————————

今回の場合であれば、適用先の一番左上のセルはA11なので、このセルが起点になります。

このA11セルから、条件で指定したセルであるB11は『同じ行、1つ右の列』のセルなので、相対的な位置は『同じ行、1つ右の列』となります。

この相対的な位置が他の適用先のセルからも参照されます。

つまり今回の場合であれば

• A11セルは(起点)「『B11』が『土』だったら青くなります(参照先：『同じ行、1つ右の列』)
• B11セルから『同じ行、1つ右の列』のセルは『C11』なので、
  • B11セルは『C11』セルが『土』だったら青くなります」
• C11セルから『同じ行、1つ右の列』のセルは『D11』なので、
  • C11セルは『D11』セルが『土』だったら青くなります」
• ・・・

12行目を例に出すと

• A12セルから『同じ行、1つ右の列』のセルは『B12』なので、
  • A12セルは『B12』セルが『土』だったら青くなります」
• B12セルから『同じ行、1つ右の列』のセルは『C12』なので、
  • B12セルは『C12』セルが『土』だったら青くなります」
• C12セルから『同じ行、1つ右の列』のセルは『D12』なので、
  • C12セルは『D12』セルが『土』だったら青くなります」
• ・・・

となります。

今回は各行のセルには、各行のB列が『土』かを条件として判定してほしいですが、相対参照では参照先がずれてしまいます。

相対的な参照とは逆に、指定したセルを絶対的に参照することを絶対参照と言います。

絶対参照を設定するには、絶対参照を設定したい列名または行名の前にドル記号をつけます。

=$B11=”土”

今回の場合であれば、Bの前にドル記号がついているので、B列のみ絶対参照で、11は相対参照となります。

つまりこのような、条件で参照されます。

• A11セルは(起点)「『B11』が『土』だったら青くなります。(参照：『同じ行、B列』)」
• B11セルから『同じ行、B列』のセルも『B11』なので、
  • B11セルは『B11』セルが『土』だったら青くなります」
• C11セルから『同じ行、B列』のセルも『B11』なので、
  • C11セルは『B11』セルが『土』だったら青くなります」
• ・・・

12行目を例に出すと

• A12セルから『同じ行、B列』のセルは『B12』なので、
  • A12セルは『B12』が『土』だったら青くなります」
• B12セルから『同じ行、B列』のセルも『B12』なので、
  • B12セルは『B12』が『土』だったら青くなります」
• C12セルから『同じ行、B列』のセルも『B12』なので、
  • C12セルは『B12』が『土』だったら青くなります」
• ・・・

となります。

これで各行のセルが、各行のセルのB列を参照して、色を変えることができます。

ここまで—————————————————————————————————————————————

日曜日も同じように設定していきます。

色を変えたいデータの端をクリックして選択したら、対角側の端をShiftキーを押しながらクリックして色を変えたい範囲を選択した状態にします。Ctrlキーを押しながら、計算用の表のデータ入力欄の端をクリックして、対角側の端をShiftキーを押しながらクリックします。

選択できたらホームタブから条件付き書式をクリックして、新しいルールをクリックします。

『数式を使用して、書式設定するセルを決定』を選択して、

=$B11=”日”

と入力します。

次に書式をクリックして、フォントタブから色の欄を赤に変えます。

次にセルの背景色を薄い赤色に変えます。塗りつぶしタブから薄い赤色をクリックします。

※オレンジっぽいですが

設定が終わったら、OKをクリックすると日曜日の行を赤色に変えることができました。

もし条件付き書式を編集したり削除したい場合はホームタブの条件付き書式メニューからルールの管理をクリックすると、設定した条件付き書式が一覧で表示され、編集したり削除することができます。

月の最後を切る

次に30日以下の月の場合に、次の月の日付が表示されている問題を解消していきます。

29日目のセルにこのように入力します。

=IF(A38=EOMONTH(A11,0),””,A38+1)

EOMONTH関数では月末の日付を取得することができます。

1つ目の引数に月末の日付を取得したい月の年月日を入れて、

2つ目の引数に何か月後の月末の日付を取得するかを入力します。

今回はその月の月末の日付を取得するので、

EOMONTH(A11,0)となります。

次にIF文で、1つ上の日付が、その月の最後の日かを判定しています。

1つ上の日付が、その月の最後の日であれば、それ以上は日付を表示する必要が無いので、空白を指定しています。最後の日でなければ、新たに日付を1日足して表示します。

同じ数式を31日まで作成したいので、数式を入れたセルをF2キーかダブルクリックで編集モードにして、A11の11の前にドル記号を入れて、11を絶対参照に設定します。Enterで確定したら、右下の小さい四角をダブルクリックして、31日目まで入力します。

すると、次の月の日にちは非表示になりました。

配置

次に全てのセルの内容を中央寄せにします。

セルの一番左上をクリックしてセル全体を選択したら、ホームタブの配置グループのこの中央寄せのアイコンをクリックします。

元々、中央寄せの設定になっていたので、解除されて、左寄せになりました。もう1度中央寄せのアイコンをクリックすると全てのセルの内容を中央寄せにすることができました。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを上書き保存しておきましょう。

ファイルの管理方法

最後に、使いやすくて分かりやすいファイルの管理方法について考えます。

シートは1人のアルバイトスタッフにつき、1シートずつ使い、分かりやすいようにシート名を従業員名にして使っていきます。

今後、従業員が増える度に、1つのシートをコピーして使っていきたいので、時給やタイムカードのデータが入っていないシートを原本として使います。

なので今作ったシートのシート名をダブルクリックして名前を原本と変えておきます。ついでに時給とタイムカードのデータを削除しておきます。

削除したいセルを選択して、Deleteキーで削除します。

シート名はダブルクリックすると編集することができます。

ファイルは1ヵ月、1ファイルずつ使いますので、全アルバイトスタッフ分のシートを作成して、時給の欄のみデータが入ったファイルを原本として、毎月コピーして使っていきます。

そのように運営する中で、新しい月のシートを作成した際に、従業員の数が多いと、全てのシートの年月を変更するのは大変手間がかかります。

1か所、年月を変えるだけで、全てのシートの年月が変わるように設定する

そこで、このように1か所、年月を変えるだけで、全てのシートの年月が変わるように設定していきます。年月が変わることで、曜日やその色も勝手に自動で調整されるので、大変便利になります。

まず、下のプラスボタンをクリックして新しいシートを作成します。分かりやすいようにシート名を設定に変えておきましょう。シート名をダブルクリックして、設定と変えておきます。

右には従業員のシートが並ぶので、ドラッグで設定のシートを左に移動しておきます。

設定のシートを開いたら、項目名と仮の年月を入れておきます。

次に給料計算表のシートを開いて、この年月を参照します。

年のセルに=を入力して、設定のシートをクリックして、年の数字をセルをクリックします。Enterで確定します。すると、設定のシートのA2セルを参照されます。

月も同じように設定していきます。=を入力して、設定のシートをクリックして、月の数字をセルをクリックし、Enterで確定します。

ここで、給料計算表のシートをCtrlキーを押しながら、ドラッグするとシートをコピーします。

設定のシートの年月を変えると全てのシートの年月が変更されてることが分かります。

ここで設定シートの見た目を整えておきます。

ここでは、

Ctrlキーを押しながらTキーでテーブルの設定をして見出しのデザインを整えておきます。

データの並び替えや絞り込みは使用しないので、テーブルデザインタブから範囲に変換で、テーブルを解除します。

次に、ホームタブの配置グループから中央寄せにしておきます。

従業員名参照

次に、シート名を参照して、氏名の欄に表示できるように設定していきます。

従業員が増えた際は、原本のシートをコピーして、シート名と氏名の欄を従業員の名前に変えるんですが、両方変えるのは手間なので、シート名を変えると、それを参照して、氏名の欄も変わるように設定していきます。

先にコピーしたシートは一旦削除しておきます。シート名を右クリックして、削除をクリックすると削除することができます。

エクセルにはシート名を取得する関数が無いので、ファイルのフルパス名を取得できるCELL関数を使います。

フルパスのフルは全てという意味で、パスというのはファイルの場所のことです。簡単に言うとパソコン内の住所のことです。。

CELL関数で、シート名を含むファイルのフルパス名を取得して、そこからシート名のみを残すという手順で、シート名を取得していきます。

シート名を参照して、セルに表示するには、このように入力します。

=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,31)

これで、シート名を変えると、氏名も同じ名前に変わります。

CELL関数では、セルの情報を取得することができます。1つ目の引数にはセルの何の情報を取得したいのかを設定します。今回はシート名を取得したいので、シート名を含むファイルのフルパス名を取得することができる”filename”を設定します。

2つ目の引数には、どのセルの情報を取得するかを選択します。今回、取得したいのはシート名なので、このシート内であれば、どのセルを選んでも同じシート名を取得できます。分かりやすいように一番左上のA1セルを指定しています。

今回はパス名がこのように取得されています。

最後はファイル名が角括弧で挟まれて、その後にシート名が表示されていることが分かります。

※ファイルの保存場所によって異なります。

FIND関数では、指定した文字を検索して、その文字が何文字目にあるかを取得します。1つ目の引数に検索したい文字、2つ目の引数に検索先を入力します。

今回は、セルのフルパス名から、角括弧閉じが何文字目かを取得しています。セルのフルパス名において、角括弧はファイル名を挟んでいるので、ファイル名の終わり、つまり、シート名の前の文字が何文字目かを取得することができます。

最後にMID関数は、文字列から一部を抜き出すことができる関数です。1つ目の引数に抜き出し先の文字列を入力し、2つ目の引数には何文字目から抜き出すかを入力し、3つ目の引数には抜き出す文字数を入力します。

よって、セルのパス名の、]の次の文字目から、31文字を抜き出す。という意味になります。

2つ目の引数のFIND関数に、+1することで、角括弧の文字数に1を足すことで、角括弧の次の文字目から抜き出すことができます。

3つ目の引数では、シート名に設定できる文字数が最大31文字なので、31を設定することで、シート名の全ての文字を取得することができます。

最後に注意点として、FIND関数では、セルのフルパス名から角括弧を検索しているので、フォルダ名やファイル名に角括弧閉じを使っている場合は、正しく機能しないので気を付けてください。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを上書き保存しておきましょう。

データの入力規則(概要)

次にデータの入力規則という機能を利用して、データの入力に制限をかけていきます。

例えば時給の欄に数字以外は入力できないように設定することができます。

データの入力に制限をかけることで、例えば、このように数字以外のデータが入ってしまい、計算ミスやエラーが起こる可能性を減らすことができます。

整数

年月

まずは、設定シートの年月の欄に年から数字以外は入力できないように設定していきます。

設定タブを開いて、入力制限をかけたいセルを選択します。まずは年から設定していきます。

年の数字の欄を選択して、データタブからデータ入力規則の上半分のアイコンの部分をクリックします。

データの入力規則メニューを開いたら、設定タブから入力制限のルールを設定していきます。

今回は数字以外の入力を制限したいので、入力値の種類を整数に設定します。次に、数字の範囲を設定することができます。

年の数字が減ることは無いので、データを次の値以上、最小値を現在の年に設定し、現在の年以上の数字のみ入力できるように設定します。

ここでは2020と入力します。

この状態でOKをクリックします。設定した数字未満の数字以外を入力しようとすると、このようにエラーを表示して、入力できないように制限をかけることができました。

同じように月にも設定していきます。

月の入力セルを選択して、データタブからデータの入力規則を開いて、設定タブから設定していきます。

月も数字なので、入力値の種類は整数を選択します。

月は1月～12月まであるので、データの項目は次の値の間で、最小値を1、最大値を12に設定します。

これでOKをクリックすると、1～12以外は入力できないようになります。

時給

次に、時給の欄に最低賃金以上の数字以外は入力できないように設定していきます。

最低賃金の法律が変わった際に、従業員が多いと、全てのシートの設定を1つずつ変えないといけなくなってしまうので、年月と同じように設定シートに、最低賃金を入力してその数字を、給料計算表のシートから参照して制限をかけるように作っていきます。

まず、設定のシートに、最低賃金の項目を作ります。

2020年7月現在、東京都の最低賃金は1013円なので、ここでは1013と入力します。勤務地の各都道府県に合わせて設定してください。

次に、参照元となるセルを選択して、上のデータタブからデータの入力規則を開きます。

設定タブで、入力値の種類は整数を選択して、データは次の値以上を選択します。

次に最小値の中のボックスをクリックして、カーソルを入れた状態で、設定シートを開いて、先ほど入力した、最低賃金の数字をクリックします。すると、最低賃金の数字が参照されます。

設定できたらOKをクリックします。

これで、最低賃金以上の数字以外は入力できないように設定することができました。

次にこの最低賃金も数字以外は入力できないように設定しておきます。

入力欄を選択して、データタブからデータの入力規則をクリックします。入力値の種類は整数を選択して、データは次の値以上、最小値は1に設定しておきます。

最後に設定シートの見た目を整えておきます。

ここでは、

Ctrlキーを押しながらTキーでテーブルの設定をして見出しのデザインを整えておきます。

データの並び替えや絞り込みは使用しないので、テーブルデザインタブから範囲に変換で、テーブルを解除します。

次に、ホームタブの配置グループから中央寄せにしておきます。

時刻

次にタイムカードの項目に時刻データ以外は入力できないように設定していきます。

時刻データも数字コロン数字というデータ以外を入力できないように設定することで、計算ミスが出ないようにすることができます。

入力制限をかけるデータの端をクリックして、対角側の端をShiftキーを押しながらクリックします。

するとその範囲を選択できるので、そのままデータタブのデータ入力規則を開きます。

設定タブから、入力値の種類は時刻を選択します。

入力を許可したい時刻は23:59までなので、データは次の値の間に設定して、開始時刻は0:00、終了時刻は23:59に設定してOKをクリックします。

これで、時刻データ以外は入力できないように設定することができました。

保護

次に、編集する必要が無いセルを編集できないように設定していきます。

エクセルでは、セルを編集できないように制限をかける機能を保護と言います。

編集する必要のないセルを保護しておくことで、知らず知らずのうちに数式を編集してしまって、計算ミスが起こったり、削除してしまったりする可能性を無くすことができます。

特定のセルを保護するには、大きく2つの手順を踏みます。

まず保護したいセルにロックという設定をします。その後、保護を設定をすると、ロックをかけたセルが保護されます。逆にロックをかけていないセルは保護されていないので、編集できる状態のままになります。

なのでまずは編集できないように設定したいセルにロックをかけていきます。初期状態では全てのセルにロックがかかってるので、特定のセルのみロックする場合は、一度全てのセルのロックを解除してから、保護するセルのみロックの設定を行います。

全てのセルのロックを解除するには、セルの一番左上をクリックし、全てのセルを選択した状態にします。次にCtrlキーを押しながら1を押して、セルの書式設定を開きます。保護のタブから、ロックのチェックを外し、OKをクリックします。

これで、全てのセルのロックが解除されました。

次に保護したいセルのみロックをかけていきます。

まずはロックをかけたいセルを選択していきます。

ロックかけたい1つ目のセルを選択したら、2つ目以降のセルをCtrlキーを押しながら選択して、複数セルを選択していきます。

広い範囲を選択したい場合は、その範囲の端をクリックして、対角側の端をShiftキーを押しながらクリックします。

ロックしたいセルが選択されたら、Ctrlキーを押しながら1を押して、セルの書式設定を開きます。保護のタブを開き、ロックの項目にチェックを入れてOKをクリックします。これで、保護したいセルがロック状態になりました。

ロックをかけただけではまだ編集できる状態なので、次にロックをかけたセルに保護をかけます。

上の校閲タブのシートの保護をクリックします。すると、保護に関する設定ウィンドウが表示されます。

1つ目の項目では保護を解除する際のパスワードを設定することができます。パスワードを設定しなくても、保護の解除ボタンを押さない限り編集はできないので、基本的にはパスワードを設定する必要は無いかと思います。もし、会社のファイルにパスワードを設定する場合は、万が一の際に会社に迷惑をかけることのないように、上司やシステム管理者などにパスワードを共有しておくようにしましょう。今回はパスワードは設定しないので空白のまま進めます。

次に「シートとロックされたセルの内容を保護する」にチェックが入ってることを確認します。これにチェックが入ってることでセルを保護することができます。

次に、保護状態でも、許可する操作を選択します。基本的に全て外れていても大丈夫なんですが、選択ができないと使いにくくなってしまうので、選択を可能にするこの2つの項目のみチェックを入れた状態で、OKをクリックします。

これで、セルを編集できないように設定することができました。

これで、セルを保護することができました。保護したセルを編集するとこのようにエラーが表示されます。

非表示(グループ)

次に、計算用の表を非表示にします。

計算用の表は、計算するための一時的なデータの保存場所なので、普段は見る必要が無いですし、印刷時のレイアウトを考えると非表示にしておいた方が良いです。

保護がかかった状態だと非表示にできないので、校閲タブからシート保護の解除で一旦保護を解除します。

エクセルの非表示機能は列や行ごとに行います。

非表示にしたい列や行をドラッグで選択して、その上で右クリックします。その中から非表示をクリックすると、非表示にすることができます。

再表示したい場合は、非表示になっているセルをドラッグで選択して、その上で右クリックして、再表示をクリックすると、再表示することができます。

最後に列幅を調整しておきます。最後にセルを一番左上をクリックして、セル全体を選択したら、どこでも良いので、列の境目をダブルクリックすると自動で列幅が調整されます。

ついでに年を右寄せにしておきます。

保存

ここでCtrlキーを押しながらSキーを押して、ファイルを上書き保存しておきましょう。

最後に

以上が、エクセルでアルバイトスタッフの給料計算表を作成する方法でした。