Excel の関数だけで勤務管理表を作成してみよう
こんにちは。今回はこんな感じのものを Excel で作成する流れを書いていきます。
作成した Excel 資料もダウンロード可能にしていますので、参考にしてみてください。
作成していく過程で、どういった関数をどのように作成していっているのかを見てもらえると嬉しいです！
※私の開発環境が Mac の Office365 を使用しているので Windows 版とレイアウトが異なる場合があります。
この記事の目的
成果物から関数をどのように組み合わせて使用しているかを体験してみる
INDEX 関数、MATCH 関数、IF 関数の使い方を体験してみる
まず最初に確認してほしいこと
Excel を起動したら初期設定を確認
基本的な設定をまずは確認していきましょう。確認してほしいことをまとめた記事をこちらで書いています。
チームごとの名簿を作成
まずはこの表形式で名簿を作成していきます。名簿は列ごと、チームで分けていきます。これはあとでチーム毎にデータを抽出していきたいのでこうしています。
ポイント
ちょっとここで１つこだわりが…
A列に少し隙間が空いていますね。
資料を作成するとき、このA列の幅を”１”に設定しています。
この隙間がない状態で表を作成すると見た目が窮屈に感じるのと、罫線を引きていた場合、このA列の左側に罫線が惹かれているかがひと目でわからないですね。
印刷して初めて気がつく…これは避けたいのでページの左端、上部にも１行余白をもたせておくことをおすすめします。
余談ですが、この氏名は全てダミーです。このサイトで簡単にダミー情報が作成することができます。
シフト入力シートを作成
何曜日に何時から何時まで稼働可能なのかを記入していきます。
今回は指定した範囲内でランダムな数字を作成する関数を使用しています。
今回は名前と時間帯を入力するセルだけ、操作可能にしたいです。”月”や”火”の曜日が誤って”月曜日”などに変わると、他の数式で正しく参照できなくなってしまうため、シートの保護をしていきます。
ポイント
シートの保護は簡単です。「校閲 → シートの保護」を押してOKを押すだけです。
※配布データもシートの保護をしていますが、パスワードは未設定なのでご安心を。
これでこのシート全体が操作不可になりました。めでたしめでたし…ではだめですね。
このシートでは名前と稼働開始時間、稼働終了時間は操作が可能にしておかなければいけません。
一部のセルだけをシートの保護を無効にしたい場合は、セルの書式設定から簡単に設定できます。
セルの書式設定を開いたら、一番右側の保護タブをクリックします。
そうすると”ロック”にレ点チェックが最初は入っていますので、これをレ点を外します。
こうするとで、このセルはシートの保護によって”ロック”されないようになります。
計画表の基本レイアウトを作成
こんな感じの稼働表を作成していきます。
最初にここで使用してい関数を紹介していきます。
MATCH 関数、INDEX 関数、IF 関数、この３つだけで作成しています。
関数以外では比較演算子、条件付き書式を使っています。
機能としてはチームと曜日を選択し、その情報が返ってくるようにしています。
これで基本となる機能、レイアウトは完成です。これからは実際に使用した関数や、書式の設定などに関して解説していきたいと思います。
書式設定をうまくつかっていこう
書式設定がうまく活用していくことで見やすい、使いやすい資料になりますので是非参考にしてください。
どのセルに入力してほしいかわかるようにする
シフト入力シートですが、データ入力をした上で進めていましたが最初はもちろん空のシートが前提です。
全てのデータが入力されていない場合、どこにデータ入力が必要なのかがひと目ではわかりません。
こんなとき、入力してほしいセルを塗りつぶしておくといいでしょう。ただしここは組織でルールがあるかもしれないので確認が必要です。
書式設定を使わずにやろうとした場合、どうしたらいいでしょう。１つ１つセルを塗り潰して行く方法が考えられるでしょう。
数が少なければ塗りつぶしでもいいですが正直めんどくさいですね。また入力したら塗りつぶされないようしたいです。印刷したとき不要な色が入るのは避けたいです。
ではどのようにするか。今回使用するのが、条件付き書式です。
のちほど勤務稼働時間の塗りつぶしにも使用していきます。
条件付き書式には数多くの機能が備わっているので、興味がある方は調べてみてください。
設定する前に今回の”条件”を定義しましょう。
「選択したセルが空白の場合、セルを塗りつぶしたい」
これが条件になるので、これを設定していきます。
※設定画面が Windows と異なると思います。仕事では Windows、プライベートは Mac を使用しているので、 Mac のレイアウトで戸惑いました…
新しいルールを選択
指定の値を含むセルだけを書式設定を選択
特定の文字列ではなく空白を選択
書式を設定
私はいつも入力してほしいセルには薄いブルーで統一。
これで設定完了なので、全てOKを選択して条件書式付きの設定画面を閉じましょう。
ちょんと設定できていますね。では実際に名前を入力してみます。
名前が入力されたセルには塗りつぶしの条件書式が解除されています。
今回の条件は”空白の場合、塗りつぶす”を実行するという条件だったためです。
これで資料を作成していく上でどのセルに入力していけばいいのか、どのセルに入力しないと資料が完成しないのかがひと目でわかるようになります。
ほかのセルにも同様の条件書式を設定したい場合、セルをコピーしてそのまま貼り付けするだけで条件書式が反映します。
ある数字に等しいときに書式を変更する
条件付き書式は、稼働表の時間帯の塗りつぶし活用していますのでこちらも解説していきます。
ではわかりやすくするために、一度設定した条件付き書式を解除してみましょう。解除のやり方はこちらです。
今回は選択したセルのみ解除しています。下にあるシート全体を選択すればそのシート全体の条件書式が解除されますので注意してください。
解除されたセルをみると０と１の数字がいきなり現れましたね。後ほど解説しますが、If 関数である条件の場合は０と１の値を返す数式を組んでいます。
この０と１をうまく条件付き書式の設定で活用していくことで、今回ように塗りつぶす範囲を可変することが可能になります。
ここでもどのような条件を設定したいかを確認します。
「値が０に等しい場合、フォントを白色にする」
「値が１に等しい場合、フォント・背景色を青色にする」
この２つの条件を１つのセルに設定していくことが必要です。
では設定していきましょう。今回は新しいルールの設定以外からのやり方を紹介します。
セルの強調表示ルール → 指定の値に等しいを選択
すでにいくつかの条件が設定されているのでここをメインに使用していきましょう。
指定する値を入力
ユーザー設定の書式からフォント色で白を選択
これで値が０のセルが画面に表示されなくなりました。値が削除になったわけではなく、背景色と同色になっているから見えていないだけです。
では値が１の場合も書式を設定してきましょう。
ここでポイントとなるのがフォント色と背景色を同色にすることです。
色がずれると値が見える表になってしまうので注意してください。
入力規制で指定した値を選択させよう
今回ポイントになるのが、チーム名と曜日を選択したら自動で切り替わるようにしているところです。
この値を利用者側が手入力し誤った値が入ると、情報が返ってこなくなくなります。指定した値を強制的に入力してもらう必要があります。
この場合、入力規制を使用すると一発で解決します。
入力規制を選択したら、許可： のプルダウンからリストを選択します。
元の値： で指定したいセルを選択します。
今回はチーム名のプルダウンを作成したいのでチーム名が入力された範囲を選択します。
※シートを跨いで選択できますが、個人的に同シート内にあるとその後のメンテナンスがしやすいので同シート内に事前に準備しておきます。
リストは縦横どちらでも選択可能になっています。
INDEX・MATCH 関数で指定した行列の値を取得する
さて関数の使い方を細かくみていきましょう。抽象度を下げて関数を分解していきましょう。
簡単な例題として、B3 セルに入力された値を C5 セルで取得する流れを見ていきましょう。
まず関数の使い方を使い方を見ていく場合、Google やネットで検索していくのも手段の１つですが、もともと Excel 内に情報があるのでそこを確認するのも１つの手段です。
"fx" を押すと数式パレットが表示されます。
今回使用する INDEX を検査します。ここに関数の説明文が記載されているので、この内容でも十分確認することができます。
使用する関数を選択すると、その関数が持っている引数のパターンを選択する場合があります。今回は ”配列、行番号、列番号” を使用します。
では実際に入力している数式 "=INDEX(1:1048576, 3, 2)" を分解していきます。
配列：1:1048576、行番号：3、列番号：2
まず行、列から見ていきましょう。
行が横、列が縦になっています。
なので今回は3行目、2列目がぶつかり合う値を指定しています。
配列はどの範囲を選択するかを指定しています。今回はシート全体を選択したいので、左上のクリックするとシート全体が選択されます。
1:1048576 が選択している範囲になっています。シート全体を選択しておく範囲の設定を見直さなくていいので、範囲はシート全体で統一しておきましょう。
名前を取得してみよう
では実際に名前を取得する流れを見ていきます。チーム_BのC3セルに入力されている「木島 聡」を取得する流れで見ていきます。
何行目の何列にあるのかを取得できればいいので、C3 は３行目、３列目に位置していることを押さえておきましょう。
Excel の関数で VLOOKUP というものがあります。今回の名前を取得する場合、恐らく多くの方が VLOOKUP を使うのを想像されたかもしれません。
INDEX と VLOOKUP の違いは INDEX は欲しい値をピンポイントで取得できる、処理も速いというメリットがあります。
Excel を学び始めたときは VLOOKUP を多様していましたが、資料を本格的に作成するときは INDEX をメインで使うようにしています。
MATCH 関数を使用して行・列を取得
INDEX 関数の使い方、名前をどのような流れで取得できているか理解できたかと思います。
INDEX 関数でピンポイントに値を取得するには行、列を指定する必要があるので、その取得方法を説明していきます。
まず行の値を取得方法を見ていきましょう。名簿の表をみると、３行目から下に名前が入力されていることがわかります。
名前は３行目から始まることは、各チームでも同じなので、「３，４，５，６，７，８…」と変動しない値になります。
次の稼働表を見てみましょう。チーム名簿と書かれているところに”３”から始まる数字が入力されています。
名簿と同じ行の値になっていることがわかります。最初の３は手入力していますが、４以降は上のセルに対して＋１していく手入力しなくていいのでミスもなく設定できるので、手入力は使わないようにしていきましょう。
次に列を取得していきます。ここで使用するのが MATCH 関数です。MATCH 関数がどのようなものか見ていきましょう。
検索したい値が、指定した範囲の中で何番目に位置しているかを探し、その位置している数値を返してくれます。
構文は ”MATCH(検索値、検索範囲、照合の種類)” と定義されています。
今回はチーム名が A列からみて何番目に位置しているかを取得していきます。
ここでポイントになるのが検索範囲の指定です。ここからは実際に数式を見ながら解説していきます。
MATCH 関数の構文に当てはめてみていきます。
”MATCH(検索値(E5)、検索範囲(名簿シート2行目)、照合の種類(0))” です。
範囲の指定は選択したい行、列を選択すると楽です。A2:E2 としてもいいですが、チーム数が増えて F列が追加になった場合は範囲の指定をしなければならないので漏れる可能性が高く、エラーが発生する要因になります。
照合の種類は０を入力しましょう。ここは完全一致した値を返したいからです。
これでチーム名を変更したら、チーム名がどこに位置しているかが可変的に取得できるようになります。
曜日ごとの出勤時間も取得していく必要があります。取得する方法、考え方はチーム名を取得した流れと一緒です。
ポイントになる検索範囲の指定だけ注意してください。今回検索したい範囲はシフト入力シートから検索しているので注意してください。
非表示にしたい範囲はグループ化しよう
曜日、チーム名のリストや、INDEX 関数で必要になる行、列を取得するための情報が複数入っています。
この情報は見せる必要はないので非表示にしましょう。
恐らくですが大体の方は表示させたくない行、列を選択し右クリックで表示しないを選択するのではないでしょうか。
ですがこれは使用しないようにしましょう。理由は非表示にした箇所がどこかがひと目でわからなくなるからです。
資料を作成したあと、数式の見直しなどメンテナンスをする可能性は高いです。そのとき、どこに何が入力されているかを探す手間が発生します。
探せたとしても表示させたい範囲を選択し、右クリック → 再表示という手間が発生します。
この探す手間と再表示する手間を解決してくれるのがデータ → グループ化です。
グループ化（表示させない）したい行、列を選択しグループ化 → グループ化… を選択します。
B列 〜 D列の上に [ の記号が出てきます。このマイナスボタンを押すと非表示にすることができます。
マイナスボタンを押すと非表示になり、プラスボタンに切り替わります。
マイナス、プラスボタンを切り替えることで、表示・非表示を簡単に設定することができます。
また、ボタンがある = 隠せたデータがある という目印代わりにもなるので、非表示にした箇所を探すという手間が削減されます。
IF 関数を使って勤務時間内かを判定しよう
出勤時間ないであればセルが塗りつぶされるようにしてきましょう。
実際に塗りつぶすのは、条件付き書式設定で、０と１の場合に対して書式を設定しました。
これからやりたいことは IF 関数を使って０か１かを判定していくことをやっていきます。
では実際に数式を見ていきましょう。
IF 関数を数式パレットで見ていきましょう。
論理式に対して、正しい場合の処理、偽の場合の処理 を書いていきます。
簡単な例題で使用方法をみていきましょう。
B2、B3に入力されている値が○であるかどうかを判定しています。○であれば（正しい場合）○が入力されている、○以外であれば（偽の場合）○以外が入力されている、と判定しています。
では今回は論理式を組んでいけばいいのか考えてみます。
9時〜11時勤務可能な場合、9時より大きく（9時以降という日本語がただしいのか）かつ11時以下 である場合、正しい処理１を返し、そうでない場合は０を返す処理をしたい。
複数の条件を指定する必要があります。詳しい使い方は調べるとたくさんでてくるので、是非調べてみてください。
複数条件の設定は AND 関数を使用すばいいことはわかりました。
中身の条件を設定するのに必要になってくる知識として以上、以下、より大きい、より小さい（未満）を設定する方法です。
これも調べるとたくさん情報がでてくるので調べてみてください。
これで０か１かの値が返ってくるので、この数式を全てのセルに貼り付けすれば完了です。
最後に
いかがでしたでしょうか。最初に作成するものを見た時、いくつもの関数を組み合わせて、難しい設定をしなければいけないのではないか…そう思ったかもしれません。でも実際やってみると意外といけるかも！？と感じたのではないでしょうか？
今回、Excel に対しての抵抗感、ハードルが少しでも下がるととても嬉しいです！
また今回作成した勤務管理表はまだまだカスタマイズすることができます。
その時間帯に何人勤務しているのか。勤務予定時間に対して休憩時間は必要か。週間の累計勤務時間予定は何時間か。シフト時間だけでなく作業項目を計画できるようにする…
是非カスタマイズするアイディアがあればコメントで教えて下さい！