【無料DL】やってみよう!初心者のためのエクセル利用術【シフト表作成】

シフト表をエクセルで作成し管理してみましょう。【無料でエクセルダウンロードできます。】飲食店経営での“事務処理”には、シフト管理だけでなく売上表・仕入表、客数・客層・注文数なども必要です。売れ筋メニューの分析、混雑する時間帯の分析など。パソコンで簡単に集計でき、状況判断が速くできます。初心者の方にわかるように解説します。

はじめまして、ジム・ペレイラと申します。

飲食店経営をしていく上で、“事務処理”もかかせない仕事のひとつです。しかし、シフト表を作るのって結構面倒で時間がかかったりしていませんか?勿論なるべく時間をかけたくないのが本音。


ネットで探すと様々なテンプレートが出てきますが、飲食店で必要なのはシフト表だけじゃないですよね?売上表・仕入表、客数・客層・注文数などなど。


どんなメニューが売れているのか、どの時間帯が混雑するのかなど。これらをパソコンでやると簡単に集計でき、その分状況判断が速くできるということです。


確かにネットで簡単に手に入りますが、自分で作ることで理解もついてきますし、身につくと上記のようにいろいろな表が自分の好きなように作れるようになります。


完成形を見てイメージしよう

それでは、今回は「シフト管理表」を作成していきましょう!と言っても完成形が分からないことには作成できません。まずは画像で完成形をイメージしましょう。

f:id:trn_d_fujikawa:20160610101043j:plain


このシフト表には、名前・担当・勤務時間・などがひと目で分かる表となってます。
 
もちろん全て手動ではなく、計算式で補える部分は自動化していきます。この表では下記通りに「手動」と「自動」を作成していきます。
 

【手動で入力するもの】
・名前
・担当
・開始時間
・終了時間
 
【自動で出力されるもの】
・各メンバーの合計勤務時間
・全員の出勤時間合計
・所属人数
・時間毎の出勤人数
 

はじめに覚えておこう!エクセルの基礎知識

エクセルはいくつもの小さな四角でできています。1つ1つの四角を「セル」と言います。縦に1、2・・・・と番号が振ってあり、横にA、B・・・とアルファベットが振ってあると思います。※以降「セル」と呼びます。
 
下図を見て下さい。これが交わるセル。例えば一番左上のセルは「A1」となります。実際に作成に入る前に、まずはこちらを覚えておきましょう。

f:id:tentsu_media:20160620114546p:plain


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

手動項目を入力しよう


各セルに下記の見本を例にして、タイトル、日付、名前、担当、勤務時間をセルに入力します。


f:id:trn_d_fujikawa:20160610103954j:plain


今回は勤務時間を10時~24時と設定していますので、必要に応じて増やして下さい。
 
 

計算式と関数

簡単な計算式を入力してみよう

いよいよ数値を入力していきます。まずは、それぞれのメンバーの勤務時間を入力してみましょう!開始時間と終了時間に24時間表記で入力します。お休みの人は入力しません。

勤務時間を計算することで、人件費の計算・超過勤務メンバーの管理ができます。シフト表は毎日使うものですので、開始時間と終了時間を入力したら自動で勤務時間を計算できるようにします。
 
 
例として、田中さんの勤務時間欄(T5)を選択します。 終了時間から開始時間を引けば勤務時間になりますので、【=D5-C5】と入力、もしくは「=」を入力、「D5」を選択、「-」を入力、「C5」を選択でもOKです。19-11=8で8と表示されていると思います。


それ以下のセルに対しても、同じ計算式を使う場合に、全員分入力する必要はありません。


【=D5-C5】と入力したセルをクリックすると、右下に■マークがあるのでダブルクリックします。すると、Tの列にある、セルに対し同様の計算式が当てはめられます。念のため、他のセルの計算式に間違いが無いか確認しましょう。



f:id:trn_d_fujikawa:20160610135458j:plain


関数を使用してみよう

ここからは関数を使います。関数とはいろいろ面倒な計算をしてくれるもので、覚えておくといろいろ役立つので、ゆっくり覚えていきましょう。関数を記載しますので、最初のうちは、そのままコピーして貼り付けでOK。
 
(1) 全員の勤務時間の合計
全員の勤務時間を合計します。合計するには「SUM」(サム)という関数を使います。T列の1番最後(T24)をクリックして、下記関数を入力してみましょう。
=SUM(T5:T23)

「SUM(サム)は、指定したセル範囲の数値を合計するときに使用する関数です。

 
(2)所属人数の合計
続いて所属人数を関数で計算します。所属人数は「COUNTA」(カウントエー)という関数を使います。B列の1番最後(B24)をクリックして、下記関数を入力してみましょう。

=COUNTA(A5:A23)

「COUNTA」(カウントエー)は、文字が入力されているセルの数を数える関数です。
 
 
(3)時間毎の出勤人数の合計
次はその時間に何人出勤しているか計算します。出勤人数は「COUNTIFS」(カウントイフス)という関数を使います。E列の1番最後(E24)をクリックして、下記関数を入力してみましょう。
=COUNTIFS($C$5:$C$7,"<=10",$D$5:$D$7,">=10")

「COUNTIFS」(カウントイフス)は、複数の条件に合うセルの個数を求めることができる関数です。
 
さて、ここで「COUNTIFS」(カウントイフス)の条件がややこしくなってきましたので、少し解説します。
「COUNT」+「IF」に分けると分かりやすく、「COUNT」は数字が入力されているセルの数を数える関数です。「IF」は条件を付ける関数です。(もし○○場合ってやつです)
 
例えば、10時~16時の出勤をしている人がいる場合、開始に10、終了に16と入力していますね。

しかし、開始と終了に15という文字がないので、間の時間帯にいる人員をエクセルは認識してくれません。ですので、「IF」を使って条件を付けてあげる必要があります。15時に出勤している人は、開始が15時以下の人を数えてと指示しなければならないのです。
 
それでは、21時に出勤している人数を数える場合はどうでしょうか?開始が21時以下の人を数えてもいいのですが、この場合16時に終了していますので、数に入れられません。21時に出勤している人は、その時間以降に終了を迎えるはずですので、終了が21以上の人を数えないといけません。
 
つまり、21時に出勤している人を数えたい場合は、開始が21以下、終了が21以上という条件を2つ付けてあげなければならないのです。条件が複数だから、「IF」の複数形?「IFS」という仕組みです。
 

関数についている$は何?

さて、ここでなにか気づいた方がいるかもしれません。先ほどの範囲を指定しているC5:C23の前に$マークがあります。
 
ここではE24に設定しましたが、各時間(F24~S24)に設定しなくてはなりません。
 
勤務時間のところで紹介した必殺技「右下■ダブルクリック戦法」は縦のコピーにしか使えないので、今回の横にコピーする場合は使えません。そのために新たな必殺技「右下■スライド戦法」を使用します。下図を見てください。
f:id:tentsu_media:20160622170101p:plain
 
今度はダブルクリックせずに、マウスの左を押しながら右にS24までスライドさせて、マウスから手を離してみてください。どうでしょうか? 全部同じ数字になってますか?
 
全部同じ数字になっていれば、順調です。でもこれではシフト表として合っていませんね。
 
どんな必殺技にも弱点はあるもので、これら2つの戦法は、ご丁寧なことにずらしたらずらした分範囲もずれていくのです。勤務時間のときのように、ずれていい場合もあれば、今回のようにずれると困る場合があります。
 
それを解消するのが、$マークなんです。 これをつけておくと範囲がずれることなく関数をコピーしてくれるのです。

先ほど「右下■スライド戦法」を使用した、E24~S24がすべて同じ式になっているかと思いますので、F24~S24の"<=10",">=10"の10の部分だけ各時間に変更してください。手入力で!


f:id:trn_d_fujikawa:20160610184805j:plain
 
これで、計算式はすべて入力できました!もう一息です!
 
 

条件付き書式

このシフト表の目玉でもある、「勤務時間を入力したら色が付いて一目で分かるようにする方法」。
これをガントチャートと言います。
 
関数と似ているのですが、関数は数字、文字などの結果を返すことはできるのですが、セルの色を変えることはできないので、この条件付き書式を使用します。

まず、E5を選択します。
 
エクセルメニューから
「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」
を選択して、ルールの部分に以下を入力します。
 

=AND($C5<=E$4,$D5>=E$4)

 
その後、書式ボタンを押して、好きな色を選択してください。
 
f:id:trn_d_fujikawa:20160610202310j:plain

出勤人数と同じ考え方で、開始時間(C5)が、10(E4)以下、終了時間(D5)が10以上、両方の条件が揃っている(AND)とき、好きな色にセルを変えるという設定です。
 
今設定したのはE5のセルのみになりますので、これを書式コピーという方法ですべてのセルにコピーします。
E5をコピーして、ガントチャートを作成したい範囲すべてを選択して貼り付けを行ってみましょう。
 
f:id:trn_d_fujikawa:20160610202322j:plain


これで完成です。試しに開始・終了時間を入力してみましょう。開始・終了時間に合わせてセルの色が変更されると思います。それでも、どうしても作れないという方は、シフト表をダウンロードできますので、こちらもご利用ください。

 
エクセルダウンロードはこちらから!
 

ジム・ペレイラ

 

こんな記事も読まれています

この記事をご覧になられた方々に、こんな記事も読まれています。
まだお読みでない方は、是非お読みください。

www.tenpo.biz



f:id:trn_d_fujikawa:20160610202310j:plain