問題発覚
何ヶ月か前の話です。
毎月末に提出する勤務表（エクセルファイル）に、「特定条件下で作業時間が正確に算出されない」という問題が見つかりました。
具体的には、作業開始時刻の記入欄に09:00より前の時刻を書いても、09:00から作業を開始したものとして作業時間が計算されてしまっていました。
作業時間のセルに書かれていた計算式
作業時間を表示するセルを確認したところ、下記の計算式が書かれていました。
IF(AND(F16="",G16=""),"",IF(AND(F16<>"",G16=""),7.5-N16,IF(AND(F16="",G16<>""),"開始時刻入力無し",O16-N16)))
F列が作業開始時刻でG列が作業終了時刻なので、
①両方とも無記入なら何も表示しない。
②作業開始時刻だけ書かれているなら、7.5（標準勤務時間が7.5時間ということになっているようです）からN列の値を引き算する。
③作業終了時刻だけ書かれているなら「開始時刻入力無し」と表示する。
④両方とも記入済みならO列の値からN列の値を引き算する。
という仕組みのようです。
単純に作業終了時刻から作業開始時刻を引き算するだけで良いだろうに…と思いつつ、隠されていたN列～R列を表示させてみました（上記の計算式を見るまで、隠されている列があることに気づきませんでした）。
隠されていた列に用意されていた表
| N列 | O列 | P列 | Q列 | R列 |
|---|---|---|---|---|
| 09:00～開始 | 09:00～終了 | タイムテーブル | ||
| 0.0 | 7.5 | 09:00 | 0.0 | |
| 0.0 | 7.5 | 09:30 | 0.5 | |
| 1.0 | 8.5 | 10:00 | 1.0 | |
| … | … | … | … | … |
Q列とR列には「タイムテーブル」と名付けられた表が作られていました（範囲選択して右クリックメニューから「名前の定義」を選択することで、セル範囲に名前を付けられるそうです）。
09:00との時間差を0.5時間単位で表示しており、09:00から48:00まで用意されていました。
P列には何も書かれていませんでした。
N列は作業開始時刻と09:00との時間差を、O列は作業終了時刻と09:00との時間差を表示する列のようです。
下記の計算式が書かれていました。
N列：IF(ISERROR(VLOOKUP(F16,タイムテーブル,2)),0,VLOOKUP(F16,タイムテーブル,2))
O列：IF(ISERROR(VLOOKUP(G16,タイムテーブル,2)),0,VLOOKUP(G16,タイムテーブル,2))
F列やG列に記入された時刻がQ列に存在するか確認し、無ければ0を、あればR列の値を表示する仕組みのようです（ISERRORは指定したデータがエラー値だった場合にTRUEを、そうでない場合にFALSEを返す関数です）。
問題解決
つまり、タイムテーブルに09:00以降しか対応表が用意されていないために、09:00より前の時刻を記入すると不具合が起こるようです。
下記の表をタイムテーブルに追加してタイムテーブルのセル範囲を修正したところ、問題なく動くようになりました。
| Q列 | R列 |
|---|---|
| タイムテーブル | |
| 08:00 | -1.0 |
| 08:30 | -0.5 |
さほど難しくない問題だったので助かりましたが、そもそもこのような複雑な処理をわざわざしなくても良いのではないか、と思ってしまいました。
この勤務表を作った人なりに意図があってのことかもしれないので、もし機会があったら教えていただこうかな、と思います。
以上、ご覧いただきありがとうございました。