数式だけで勤務者カレンダーを作るには【Excel】

最近Yahoo!知恵袋で、スピルを使った数式が増えてきたなーと感じます。今回はそのスピルをふんだんに使った質問があったので、紹介します。

質問内容

Excelにて作成している出勤者リスト表です。
少し表記がおかしいところがありますが、Excelで作成しているためと思われます。放念ください。
https://docs.google.com/spreadsheets/d/1-P0TFFgIvu3MNexJlpgth-siFQz-Pj3J/edit?usp=sharing&ouid=102642916343464536112&rtpof=true&sd=true

シート【ワークスケジュール】表に休み(記号:x,c,y)を入れています。空白は出勤、”33″は早番で出勤、”東”は東京に出張
の意。

シート【出勤者カレンダー】に当該日にその出勤者の個人名を表示させている。

質問は、以下の条件を数式で実行したい。その数式を知りたい、です。

①シート【ワークスケジュール】表において、”33″と入力の者を
シート【出勤カレンダー】において”33”を接頭に付け、当該日の先頭へその者を表示させたい。表記例 “33B”

②シート【ワークスケジュール】表において、”東”と入力の者を
シート【出勤カレンダー】において、”:東”を接尾に付け”()”で閉じ、当該日の末尾へ表示させたい。表記例”(B:東)”

③シート【ワークスケジュール】表において、”33”や”東”の入力がない場合もシート【出勤カレンダー】上で正しく表示させたい（#N/Aと表示されてはいけない）。

④数式をできるだけ短くしたい。

シート【出勤カレンダー】上の最終週(赤枠内)8/28〜8/31は、このように表記させたい表示例です。ご参考まで。

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13266009428

まずは解読

元ネタの出勤者カレンダーシートのセルA6に入っている数式は、こんな感じです。

長いですね。一見しただけで、読む気が失せます。

数式の検証を使ってワンステップずつ見てみましたが、やっぱり分かりづらいです。

こういうときは、分解して、一数式ごとにセルに当てはめていくのがよいということが経験から分かっています。

という訳で、別のシートを作りました。

中間ファイル(別シート)を追加

値や位置はワークスケジュールシートに合わせます。

日付部分だけ同じであればよいので、あとは省略しました。

セルJ6以下に、元ネタにあった数式の一部を移植し、セル参照を整えます。

数式を細かく分けて、入力していく

元ネタ(出勤者カレンダーシートのセルA6)にあった、

=OFFSET(ワークスケジュール!E$8:E$23,0,A5-ワークスケジュール!E$4)<“x”

という部分は、新しいシートでは

=OFFSET(ワークスケジュール!E$8:E$23,0,J4-ワークスケジュール!E$4)<“x”

となります。

OFFSETをよく見てみると、結局ワークスケジュールシートのJ8:E23を指し示していることが分かったので、新しいシートではこんなめんどくさい記述をしなくても、

=ワークスケジュール!J$8:J$23<“x”

でよいことが分かりました。

<“x”となっているのは、ｘでもなくｙでもなくというのを一度に把握したかったからでしょうか。

しかし、これだと可読性が悪いので(普通の人は、<“x”がExcel内部では文字コード変換されて数値で認識されるということを知りません)、もっと普通に

=ワークスケジュール!J$8:J$23<>”x”

と表現することにしました。

スピルを意識して、行は多めに用意しておく

気をつけないといけないのは、スピルなので、毎回行をふんだんに用意しておかないとSPILL!エラーになってしまうことです。

計算結果を戻すためのセル範囲が必要十分でない場合にこのエラーが出ます。

必要な行を用意してあげると、このエラーは消えます。

その他の式も順番に入れていく

<“x”でなく<>”x”としましたので、yが入力されたときにもちゃんと休みだと認識してもらうために、もう一個数式が必要です。

=ワークスケジュール!J$8:J$23<>”y”

というのも追加しました。

cも必要ですね。

=ワークスケジュール!J$8:J$23<>”c”

以上、3つの数式をAND条件でつないだら、出勤扱いの人が把握できるという訳です。

AND条件を実装

元ネタではこれを「*」記号でつなぐことで実装していました。

=J6#*J23#*J40#

私もここは元ネタを真似しました。AND(スピル参照式、スピル参照式、スピル参照式)でいけるかな？と試したのですが、ダメでした。ここは、元ネタにあるよう掛け算でAND条件を実装するしかないようです。

ここまで解読が進むと、あとはだいたい分かりますね。REPT関数でAやBやCといった文字列を1回だけ(あるいは、0回)繰り返し、TEXTJOIN関数で文字列を結合し一つの文字列にしている訳です。

INDEX関数は必要ないように思います。実際INDEX部分を削っても同じ結果が得られました。

回答

回答としては、以上のような手順で解読した後、質問者の要望どおりに順番に数式を埋めていって、最終的にアウトプット専用シート(今回は出勤者カレンダー(2)シート)で参照する形をとりました。

途中SORTをする必要があり、最終的な表示の際、質問者が指定する順番になるよう、何度か迂遠な文字列操作をする必要がありました。

この辺は高度なテクニックではなく、どちらかというと泥臭くてかっこ悪い文字列操作でして、「こういうのこそVBAだよなあ！」と思いながら、ちまちま数式を作って埋めていきました。

最終的に完成したファイルがこちらです。

https://drive.google.com/file/d/1rb9NPts1TgZHT2kPudJph6iiaxj3Mezt/view?usp=sharing

まとめ(感想)

今回の例では、質問者の要望に④「数式を短く」とありましたので、どうやったら短く、可読性のよい数式を作っていけるかを意識して作りました。

果たして可読性はよくなったのかどうか。

やっていてつくづく思ったのは、これくらいの仕事(数式)なら、VBAの方が早いということです。

しかし、VBAだと属人化するという批判が多いので、数式だけで完成させた方が美しいのでしょう。

と言いつつ、質問者が提供した元ネタのファイルの中には、ちゃっかりVBAが入っていました。

どういうこと？

マクロが使える人がいるなら、その人に作ってもらえばよいのに・・・と思いました。

質問者の③の質問については、おなじみIF(ISERROR(参照式),””,参照式)を使って実装しました。この辺は定番なので解説はいらないと思います(分からなければ当然ググるはずだと思いますし、このブログでは省略します)。