関数だけでいろいろ作成しているうちに、ちょっと面白くなってきて、以前、何度か作成を諦めた、24時間（6時〜翌6時）のシフト表を関数縛りで作ってみようと思い立ちまして、早速作ってみました！
仕事用ではなく、勝手に作っているものなので、手間とか時間とか、、、本当に何も考えずに没頭できて、、、ちょっと楽しかったです（笑）
ただですね、、、効率を完全に無視しましたので、おそろしく手数が多く、面倒くさいの極みになっております。
そのため、1週間で表示させるものを１度作成したのですが、ファイル容量が1.8MBと巨大化してしまい、試しに動かしてみると、動作がものすごい遅い！！！私のPC環境が悪いこと（10年前のFWVでExcel2013。。。）も多々ありますが、、、さすが安定の重さExcel！！記事にしておいてなんですが、もし、業務転用を考えていらっしゃる方は、別の手を使ったほうがよいかなぁと。。。
↑↑反応があまりにも遅く、表の書式をそろえることすら諦めました。
なので、細かく作成方法を書かずに、考え方だけに絞って書いていきます！
もし、PCスペックに自信がある場合は、週間リストにチャレンジしてもいいかもですね。でも、Macの方はやめたほうがいいかもしれません。PCが悪いわけではなく、MacとExcelアプリは相性が悪いから、多分フリーズするかもしれません。。。
まず、設定です。
- 登録スタッフの上限 20人
- １時間あたりのシフト本数の上限 5本
- シフトの色はスタッフごとに固定
- シフトに名前を表記
- シフト区切りの６時をまたぐシフトは翌日に反映させる
- 表示は１日分で曜日はドロップダウンリストで変更可能
１日分だけの表示にして曜日を変更できるようにしたのは、先程ふれたとおり、動作に影響が出たことによる追加回避策（苦肉の策）です。
次に、基本の動きを説明付きでGifアニメで作ってみました。動きを確認してください。
イメージだけでも取れましたでしょうか？逆に見にくくなっていたら申し訳ないです。
このように、縦軸にスタッフ一覧、横軸に週間シフト一覧で構成されている表にシフト時間を入力すると、下のシフト表が連動して変化するしくみです。out時間が空白でもin時間が入力されると、空白を0時としてシフトが表示され、out 時間に入力がされると、データのとおりに変更されます。そのため、シフトが伸び縮みしているように見えますが、これは空白ガードをかければ止まります。まあ、、、使うには支障がなさそうなので放置してあります。
どうでしょうか？よく紹介されている「個人別ガントチャートのシフト表」ではありません。
関数だけでも、入力されたシフトに合わせて上に詰めて表示され、後で入力されたシフトも含めて対応して変化できます。スタッフごとの色分けも固定でき、名前もシフトに表示できています。
ただ、残念ながら色の変更は簡単にできないですが、、、
このシフト表は、スタッフ位置が固定された縦長のガントチャートに比べて、縦に短くできる利点があります。
365日24時間で運営しているような業態ですと、どうしてもスタッフ人数が増えますし、週１シフトの人なども入ってくると、ガントチャートでは縦長になりすぎて、人時の把握もしにくくなります。シフト表はつくるのが目的ではなく、活用するのが目的ですから、パッとみてわかるようにしたいですよね。
また、縦に短くできる分、掲示するスペースも取らないので、１ヶ月、２ヶ月分と多くのシフト表を掲示できるようになります。
シフト表はなるべく先まで掲示したほうがメリットが多いものですよ。私も長い間、店長やってましたが、シフト表は仮の予定でも、内容が適当でも、とにかく先々まで貼っておくと、勝手にシフトが埋まったり、早めに休みの相談してくれたりと、、、いい事ずくめでした！特に、間違ったシフト貼りだすと、速攻スタッフが教えてくれます（笑）オススメです。
ちょっと話がズレました。。。戻します。
このサンプルはマクロを一切使っていないので、フリーサーバーの自作ページにファイルをダウンロードできるように準備していましたが、ストレージの整理のため、やめることにしました。１年アップしてましたが利用はほぼ無かったので、もったいないなと思いまして。。。実際にファイルがほしい！というかたはコメントください。ファイル便か何かでお送りいたします。趣味で作っただけのファイルですので、もちろん無料配布です！ただ、、、不具合あっても責任はとれません（笑）もし、ご要望があれば個別に微調整、修正はします！こちらも締め切りなしなら、バイト休まなくてもできるので無料でやっちゃいます！
では、どんな仕組みで動いているのか、さらっと説明します。
1．シフトによっての表示行を可変させる
多分これが一番関数でどうやってんの？？と感じるところかもしれません。でもこれ、実は難しいことはあまりやってなくて、過去記事の「関数だけで可変リスト」と「関数だけでランキング」を合体しているだけなんです。
表示するシフトのリストを作るために、ワークシートを別に１枚使っています。
このようにデータを横にズラしながら、表示シフトを１行ずつ作っています。
作成手順としては、全スタッフデータから表示するシフトを抽出して並べます。このとき、前日から当日の６時を超えて勤務するシフトも考慮しなければならないので、全スタッフデータには、前日シフトも準備しておきます。
そして、その中で一番小さい開始時間のシフトを基準にして、数珠つなぎにシフトを繋げていき、表示行を作っているわけです。
このシフトを数珠つなぎにする処理に、MATCH関数【以上】を使っています。途中にデータを降順ソートする列が入っているのは、こういうわけです。
このように、データ一覧から該当データを抜き出して上詰めしたり、降順にデータを並び替えたりする方法は、過去記事で紹介したやり方を使えば関数だけで可能なのです。
そして、表示シフトの上限が５本ですので、これを５回横方向に繰り返しているわけです。プログラミングのようにループ処理が使えませんので、横方向にコピーして繰り返しを表現しています。そのため、自動でシフト上限を増やすことはできませんが、手作業で横方向にコピーすれば、データだけは上限を簡単に増やすことができる仕組みになっています。そして、一応シフト上限を超えた時のガードとして、表示されないシフトがある場合、アラートを出すようにしました。（動きは上のGifアニメの中盤から確認してください。）このガードの判定は、５行のリストを作った後に、表示シフトが残っているかどうかを判断する条件分岐式で行っています。
このように、表示シフトの上限を決めないとできないというあたりが、関数だけでやる限界なのかな？と思いますが、フォーマットさえ決まっていれば、関数だけでもなんとかなるとも言えるので、Excelは関数だけでも結構優秀ですよね。
２．積み上げ横棒グラフを準備する
さて、実際に可変するシフトを表現している大事なポイントです。
見出しでネタバレしてますが、シフト表は積み上げ横棒グラフを使っています。
スタッフごとに色分けしたり、シフトに名前を表示させたりするため、一人に付き１グラフ使って表現します。シフト表は１つに見えますが、本当は２０人分のグラフが重なって動いているのです。ほらね、面倒くさいでしょ？？
それでは、作成のポイントを下の図で説明していきます。
作成するグラフの形
このように作ります。
ここのポイントは、６時から翌６時のシフト表なのですが、表示させない４時からのグラフにしていることと、積み上げ棒グラフを透明と塗りつぶしを交互に２セット作り、１行で２つシフトを準備しておくことです。
それでは、このポイントがなぜ必要なのか、どのように使っているのかを説明していきます。
まず、時間をどこにどうやって入れて使うのかを、失敗例も入れ、わかりやすいように行を１つにして説明していきます。
１，6時〜9時、22時〜6時の２つのシフトを表示
これは今回のグラフの使い方の基本の形です。シフトの時間をグラフにする表にそのまま入れるわけではありません。今回のグラフは積み上げ式ですので、6時から9時ならまず6を積んで塗りつぶしなしのダミー透過グラフを作って、次は塗りつぶしたグラフで９の位置で終わりたいので、６に３を積んで9にするのです。なので、考え方としては、０から考えて、何時間経過したか？という数字にしてあげればいいわけです。調整という「透明なグラフ」と、系列名に名前を持った「塗りつぶし」のグラフが交互になっていたのは、このように開始位置を表現するためです。
２．9時〜15時シフトを表の上のシフトを使って表示する
【失敗例】
このように、0でも系列名が表示されてしまうみたいで、シフト表の表示には邪魔です。これではシフト表になりません。
なので、シフトを１つだけ使うときは、次のようにします。
【改善例】
このようにグラフ表示設定で４〜３０にしているので、それ以下のものは表示されないわけです。
では、６時からの表示にすればいいのに、なぜ、わざわざ４時としているのかといいますと、これは作成途中に表れた状態なのですが、予期せぬ数字が左側シフトに入ったり、なにか条件が揃ってしまうと、目盛りの左端で系列名だけ表示されてしまうことがあったのです。これはグラフエリアを広げてもプロットエリアで起きていることなので回避できません。なので、系列名が表示されても隠れるぐらいの余裕をシフト６時より前に設定しておき、あとでオートシェイプで隠して見えなくして回避しました。なので、、、もし、系列名にチャイコフスキーとかドヴォルザークとかがいらっしゃるようならば、４時では足りませんよ。
ただ、完成させてからは、系列名が出る状態は再現できていないので、グラフ出力する数値加工をしっかり制御できれば、おそらく、目盛りは６時からでも大丈夫なのかもしれません。でも、まあ、念の為の作っておいてほうがよいかと思います。
また、１行で表示シフトを２つ準備している理由ですが、基本１日１シフトなので、殆どの場合、１行で同じ人が重なることはありません。ですが、この方法で作ると、登録順が後半のスタッフで、深夜勤務のロングシフトがメインになると、翌日早朝シフトと深夜シフトが１行に重複してしまうことがあります。こうなると、重なったシフトを別の行に振り替えるか、１行で２つ表示させるかのどちらかの対策が必要になります。
まず、別の行に振り替えるほうが簡単に思えたのでとりかかってみると、、、甘かった。もう一度シフト行の表示を見直すことが必要になり、見直し用に１枚シートを作るぐらいの勢いになります。手間もデータ容量もかかります。なので、グラフを操作して２つ表示できる手を選びました。シフトを２つにしたほうが手間的にはかなり楽です。
あとは、グラフを人数分コピーし、すべて重ねて作成するのですが、２枚めからは、目盛線も背景もなしにして透明にします。グラフィックツールのレイヤーの考え方ですね。私が作ってみたものは下の図にまとめます。
作成時のポイントですが、グラフをコピーする前に、グラフデータに設定する表を人数分先に準備しておきます。そうしますと、先にグラフ用データを加工する作業を先にしたほうが良さそうにみえますが、グラフの色を変えたり、系列名を入れるためには、一度ダミーデータでグラフを全部表示させないと設定できません。ここがとても不便です！
なので、先に表を一度人数分全部作ったら、１行２シフトを５行分、全部５と入力でもしておいたあと、ベースのグラフをコピーしたら、データの選択先を変更し、すべての積み上げデータを表示させたあとに、色を変更するなどの設定をします。上の図でも書きましたが、うっかりサイズが変わることが多々あります。慎重に作業が必要です。
また、グラフを人数分作成して重ねる時、自分で操作して重ねると大変です。作成したグラフを一度全部選択したあと、図の書式に「配置」という項目があります。そこで、「上詰め」「左寄せ」のように一気に揃えると簡単です。そのあとに、時間表示、曜日表示で整えて完成です。
オートシェイプのテキストをセルにリンクさせる方法ですが、オートシェイプを選択して、数式バーで式を操作してみてください。簡単にリンクできます。このとき、オートシェイプ自体の中に式を書いてもテキストとしてしか扱われませんのでご注意ください。また、参照はしっかりシートまで指定しておいたほうがよいです。シートをまたいでグラフを移動させる場合は、リンク先が変わってしまいます。
この方法を使えば、日付をリンクさせたりもできますよね。
あと、一番最後にグラフ、オートシェイプ、時間表示など、シフト表を構成しているオブジェクトすべてをグループ化します。これやっておかないとグラフが１枚だけズレたり、消えたりと面倒です。グループ化しても起こりますので、イライラポイント２ですね（笑）
３．グラフ用データに整理して加工する
５本シフトを作って、グラフまで作ったら、別シートにデータをグラフ用に加工します。INDEX＋MATCH関数とIFERROR関数のオンパレードで対応しています。
このように、また別シートを準備して、先程作った５行分のシフトを前日繰越シフトと当日シフトと分けて集めます。そのあと、行で重複したかどうかを判断し、重複していたら２シフトを表示、してなければ１シフトを表示させます。特に注意する点はありませんが、グラフの数値は日付変更線をまたいでも０に戻りませんので、そのへんを注意して時間をあつかうことぐらいですね。
また、グラフがこのシートにもありますが、もし週間で作ろうと考えている方は、曜日ごとにシートとグラフを準備することになります。シートにグラフ貼っておけば、コピーする時楽なのでくっつけたままです。今回のサンプルのように１日でよければ、不要ですな。。。
いかがでしたでしょうか？作ってみよう！って思えましたか？！結構大変ですよ（笑）もし、作成についての質問などありましたらコメントください。。
それではまた、なにか作ろうと思い立ったら記事書きます！！（万年カレンダー書くとか言ってたんですが。。。気が向いたら（笑））
追記 2022年5月6日
サンプルEXCELファイルを私のページに公開
修正 2022年5月11日
サンプルファイルの案内を取りやめ
