今回の記事では、Excelで「自動計算機能付きの給与計算表」 を作っていきたいと思います。
これは筆者が作成したもので、実際にあるホテルで清掃スタッフの給与計算に使われているものです。
時給計算であれば四則演算だけで済むので誰でも作れます。
しかしこのホテルの場合は
- 清掃した部屋の種類と数によって給与が支払われる
- 一定の部屋数以上を１日で清掃するとボーナスが支払われる
- 共有部分の清掃に関しては時給計算される
このように若干複雑な給与形態のため、ピボットテーブルやVLOOKUPを駆使して作られています。
この記事を読みながら一度この表を作っていただけると、給与計算だけでなくかなり広範囲に応用が効きますので、是非取り組んでみてください。
Excelでこんな表を作ります
ブルーの部分は清掃スタッフもしくは管理スタッフが毎日入力する部分。
橙色の部分が自動で計算される部分です。
項目を見てみよう
各項目を見ていきましょう。
それぞれの計算方法と、使っている Excel の機能を記載していきます。
- 共有部分：時給1,000円での計算：=セル番号/60*1000
- 部屋番号：部屋によって違う金額が支払われる：=VLOOKUP
- 追加ベッド：追加ベッド一つにつき170円支払われる：セル番号*170
- 部屋数ボーナス：部屋数によって異なるボーナスが支払われる：=VLOOKUP
- 追加作業：時給1,000円での計算：=セル番号/60*1000
これらの項目が自動計算され、それピボットテーブルでまとめたものが右側の表です。
その表の横にある項目は
- 出勤日数：交通費の計算のために算出：=COUNTA()
- 交通費：出勤日数 × 交通費：=出勤日数セル*交通費/日セル
- 支払総額：総計 + 交通費：=総計セル + 交通費セル
各項目で使っているExcelの機能
それぞれの機能と使い方について解説していきます。
ピボットテーブルやVLOOKUPそのものの機能については別途詳しく解説している記事がありますので、そちらも是非ご覧ください。
共有部分
これは簡単な計算です。時給1,000円で計算している式が入っているだけ。
入力された分単位での数字を60で割ると、時間に換算された数字がでますので、それに1000をかけているだけのシンプルな式です。
部屋番号
ここが今回の表の一つのキモになります。
部屋によって違う金額が支払われるわけですから、入力された部屋番号に対しての金額が入力されるような関数を入れなければいけません。
正解は「別の表を用意して、VLOOKUPで金額を拾ってくる」です。実はこの表は隠れた部分に別の表があります。
101～501までそれぞれの清掃料金が入力されている表を用意して、VLOOKUPで拾ってくることで、部屋番号に対して正しい金額を入力することが可能になります。
ここでの式は =VLOOKUP(部屋番号セル,$T$4:$U$11,2,FALSE) 「$T$4:$U$11」は右側の別表の範囲を指定しており、$マークは絶対参照といって、下にオートフィルをしていっても同じ範囲を参照するための機能です。
「2」は「2列めの数字を拾ってくる」という意味。
「FALSE」は「部屋番号が完全に一致しない限りは数字を拾ってこない」という意味の命令です。
今回は清掃料金ですが、この使い方は商品名から単価を拾ってきたり、氏名から身長を拾ってきたりと、かなりいろんな局面で使う機能です。
余談ですが、Excel の関数は実は Excel 特有のものでなく、プログラミングの基礎と大きく共通します。
実際にプログラミングをするのは開発環境の整備が必要であったり、言語を覚えたりとハードルが高いのですが、Excel は「簡単にプログラミングの恩恵が受けられるプラットフォーム」と捉えることもできます。
追加ベッド
これはごく簡単ですね。追加ベッドを用意した場合は一台につき170円支払われるので、=追加ベッドセル*170になります。
部屋数ボーナス
これは部屋番号と同じ要領で、それぞれの部屋数に対してのボーナスを入力した表を別途用意し、VLOOKUPで拾ってきます。
一番右の0～4の表で、関数は =VLOOKUP(部屋数セル,$V$4:$W$8,2,FALSE) になります。
部屋数と同じなので詳述は避けますが、よくわからない最後のFALSEは何なのでしょうか？
ここは「FALSE=完全一致の値を検索」か、「TRUE=近い値も検索」を選ぶ部分です。
今回の表でいうと、 FALSEであれば0，2.5，3，3.5，4以外の数値の場合はエラーを返すが、TRUEだとそれぞれの間の数値でも結果を返してしまう。
ということになります。
たとえば入力ミスで「2.6」と入れてしまった場合でも「500」という数字が返されてしまいます。 VLOOKUPを使う場合はFALSEを使うことがほとんどですが、たとえば「体重が50~60キロの場合は○○」といった数字を返すときは、その間の体重の人のためにTRUEを使ったりします。
追加作業
ここは共有部分と同じ時給計算です。=セル番号/60*1000 ですね。
総計を算出している表
ここはピボットテーブルの手番です。
単純に金額の合計を出すならばL列の合計を求めればよいのですが、それだと出勤日数がわからず交通費の計算ができないためにピボットテーブルでまとめています。
「日付」を行ボックスに、「小計」を値ボックスに入れればこういった表になります。 項目を入れ替えれば給与計算だけでなく、「どの部屋が何回清掃されているか」や、「一回あたりの清掃室数はいくつか」といった分析をすることもできますね。
出勤日数
ここはピボットテーブルから作成された表の「日付」データの数を数えています。
=COUNTA(N4:N13) 数を数えるCOUNT系の関数もよく使いますので、一緒に覚えてしまいましょう。
交通費
ここは簡単に見えますが、実は重要な考え方が入っています。
数式的には 交通費セル × 交通費なので、この場合は=P14*B2ですが、あまり Excel に慣れてない方だとこういった表を作りがちです。
当然全く同じ合計金額がでてくるわけですが、たとえば「交通費が間違えていた」とか「変更になった」という場合に全部のセルのデータを変更する必要がありますし、そもそも同じ数字が並んでいると表としてのみやすさを損ないます。
そこで、交通費として、B2セルに金額を入れておいて、その金額×出勤日数にするわけです。
交通費が違う他の人への流用も楽ですし、変更にもすぐ対応でき、見た目もスッキリします。
この「一つのセルに数値を入れておいて、他から参照する」数値をプログラミング用語で「変数」と言います。 上記の理由から Excel で表を作るときはできるだけこの「変数」を使い、同じ数字を複数のセルに入力することを避けるのが重要になります。
まとめ
いかがでしたでしょうか。 今回は実際に業務の現場で使われている Excel の表の作り方をご案内しました。
一つの目的を達成するための Excel の作り方は一つではありませんので、他の方法もあるはずですが、一つの例として、そして練習用として一度作ってみて頂ければ幸いです。
たとえば部屋番号の入力は手入力よりもプルダウンの方が良いかもしれませんし、同じ日付を複数入力しているところも工夫の余地がありそうです。
一度作ることができたら、更に良くする工夫をしてみるのも良い練習になるでしょう。
たかが Excel、されど Excel です。この辺りの機能をスムーズに使いこなせるようになると確実に業務効率が上がりますので、是非読者の方のお仕事にお役立て頂ければ幸いです。