![見出し画像](https://assets.st-note.com/production/uploads/images/119113386/rectangle_large_type_2_33563148d4b4601572a14c83552081e1.png?width=1200)
【OFFSET関数を使いこなせ!】シフト表作り方②
前回は、IF関数とDAY関数を組合わせた翌月の反映方法や
COUNTIF関数を使った土日祝日の色付けを行いました。
今回はマスタシートでのシフト・氏名管理と
データの入力規則を使ったシフト・氏名の反映を実装します。
【前回記事はこちら】
この記事で学べること!
・セルの名前付けと管理の仕方がわかる
・セルの名前をデータの入力規則で参照する方法がわかる
それでは、始めていきましょう!
1.<完成物>のプレビュー
![](https://assets.st-note.com/img/1697422897273-BmyNsySjce.png?width=1200)
それでは、前回の続きからシフト表を作っていきましょう。
2.マスタシート作成とセルの名前付け
2-1.マスタシートに氏名一覧・シフト一覧を作る
シート下の「+」をクリック→「シート名:マスタ」
→下の写真のように氏名一覧・シフト一覧を作成する
![](https://assets.st-note.com/img/1697423353534-xeb6WOHKqm.png?width=1200)
2-2.<ステップアップ>氏名一覧とシフト一覧に名前を付ける
次にデータの入力規則を使う際に名前で参照をするため
名前の列とシフトの列に名前を付けましょう。
まず、B3セルをクリック→「Ctrl」+F3(キーボード上部の)
→下の写真のようにOFFSET関数とCOUNTA関数を使って
名前列に名前を付ける。
![](https://assets.st-note.com/img/1697424114056-iZfZkipsWE.png?width=1200)
続いてD3セルをクリック→「Ctrl」+F3(キーボード上部の)
→下の写真のようにOFFSET関数とCOUNTA関数を使って
シフト列に名前を付ける。
![](https://assets.st-note.com/img/1697424564602-CADn1fG3Yk.png?width=1200)
3.セルの名前と入力規則を使った参照
3-1.データの入力規則を使ってシフト一覧を参照する
E5セルをクリック→ホームタブの「データ」→「データの入力規則」
→「入力値の種類:リスト」、「元の値:=シフト一覧」とする
(下の写真参照)
![](https://assets.st-note.com/img/1697429361928-Q15XpMHm7l.png?width=1200)
「OK」を押すと
↓
![](https://assets.st-note.com/img/1697429597479-UByIqr1GWZ.png?width=1200)
更にオートフィルで月末まで入力規則を反映させる。
↓
![](https://assets.st-note.com/img/1697430013435-riguapImKY.png?width=1200)
3-2.データの入力規則を使って氏名一覧を参照する
シフト一覧と同じように、データの入力規則を使って
氏名一覧から個人名を選択できるようにする。
B5セルをクリック→ホームタブの「データ」→「データの入力規則」
→「入力値の種類:リスト」、「元の値:=氏名一覧」とする
(下の写真参照)
![](https://assets.st-note.com/img/1697430501038-8lTrinKfI3.png?width=1200)
「OK」を押すと
↓
![](https://assets.st-note.com/img/1697430624112-YX0bKPqqC4.png?width=1200)
B14セルまでオートフィルで入力規則を反映し
氏名を選択できるようにする。
![](https://assets.st-note.com/img/1697430943580-AbUtQxJPP2.png?width=1200)
4.条件付き書式を使った「休」の塗りつぶし
4-1.「休」の場合、ピンク色に塗りつぶす
ホームタブ→「条件付き書式」
→「数式を使用して、書式設定するセルを決定」
→下の写真のように式を設定する
![](https://assets.st-note.com/img/1697431671384-umZsltaMb3.png?width=1200)
続いて、「書式」→「塗りつぶし:その他の色→ピンク」
→「OK」→「適用先」月末までオートフィルで条件付き書式を反映
![](https://assets.st-note.com/img/1697432103337-ZOqihES6ou.png?width=1200)
次回は最終章、出勤日数のカウントと労務アラートの実装まで
行っていきます。
この記事がいいと思った方、読者登録とTwiitterでの拡散
宜しくお願いします。また、関数の使い方などご不明な点や
「こんな記事を書いて欲しい」などご要望がありましたら、
今後の励みになりますので、お気軽にtwitterのリプまたはDM、
noteのコメント欄までお問合せください。
一歩上の自分を目指して頑張っていきましょう!!
【こちらの記事もいかが?】
5.補足
【補足-その①】OFFSET関数の使い方
上記(2-2)でセルの名前の範囲を指定する際に
OFFSET関数を使いましたが、ここで本来の使い方を
確認しておきましょう。
![](https://assets.st-note.com/img/1697447764207-cDcenHSiw6.png?width=1200)
写真のように、OFFSET関数は第一引数で「表全体」を指定し
第二引数で「行」、第三引数で「列」、オプションで「高さ」を
指定するのが一般的です。
※上記(2-2)で「=OFFSET(マスタ!$D$3, 0, 0, COUNTA(マスタ!$D:$D) - 1)」としているのは、第一引数でD3セルを指定し、第二引数で0、第三引数で0、
オプションのCOUNTA関数で高さを指定しD3セル以降の列全体を参照している。
【補足-その②】COUNTA関数の使い方
上記(2-2)でセルの名前の範囲を指定する際に
COUNTA関数を使いましたが、ここで本来の使い方を
確認しておきましょう。
![](https://assets.st-note.com/img/1697505033904-JbZC2G4JZL.png?width=1200)
写真のように、COUNTA関数は数字・文字列をカウントするのが
一般的です。
※上記(2-2)で高さを指定する際に「COUNTA(マスタ!$D:$D) - 1」と
しているのは、COUNTA(マスタ!$D:$D)だけだと「シフト一覧」の
文字列もカウントされているので「ー1」をしてD3 セルの「早」以降
を参照する形にしています。
この記事が気に入ったらサポートをしてみませんか?