エクセル 施設時間割表 関数

Question

やりたいこと

Hall E、Lobby1、Lobby2と会場があります。
a1:d4に入力される情報を元にb8:d55に値（1時間延長、全日、午前・午後）を出す。
関数が仕上がり次第文字は白塗りし、背景に色を付け見栄えは良くするつもりです。

利用時間の説明
Hall E、Lobby1、Lobby2の全日は13時間（２６セル分）
Hall Eの午前・午後は9時間（18セル分）
Lobby1、Lobby2の午前・午後は８時間（16セル分）
１時間延長は１時間ごと（２セル分ずつ）

入力方法
1行目　　延長時間の数字入力
2、3行目　利用開始時間の入力。全日と午前、午後がどちらも入力されることはない。
例）b2に9:00と入力するとb3は空白。下表にはb26から"全日"と２６セル分表示
4行目　　延長時間の数字入力

表示方法（条件）
1.　２，３行目に時間が入力された場合、入力された時間から利用時間分のセルに利用時間内容を表示
2.　１行目の延長時間が入力された場合
2-1 2,3行目が空白ならば8行目から延長時間を延長時間分（１時間なら２セル分）表示
2-2 ２，３行目が入力されていればその時間から延長時間分遡る
3. 　４行目の延長時間が入力された場合
3-1 2,3行目が空白ならば55行目から延長時間分遡る
3-2 2,3行目が入力されていればその時間からの延長分表示（Hall Eで全日9:00なら26セル分したの52行目から１時間延長と表示）

現状
1.の表示はできました。（b26関数表示）
=IF($A26=MAX(B$2:B$3),INDEX($A$2:$A$3,MATCH($A26,B$2:B$3,0)),IF(AND(COUNTIF(B$7:B25,$A$2)>=1,COUNTIF(B$7:B25,$A$2)<=25),$A$2,IF(AND(COUNTIF(B$7:B25,$A$3)>=1,COUNTIF(B$7:B25,$A$3)<=IF(B$7="Hall E",17,15)),$A$3,"")))

2以降で躓いております。
一気に行うのではなく2-1のみ、2-2のみなどで検討中
2-1(b8関数表示)
=IF(B$1="","",IF(MAX(B$2:B$3)=0,IF(ROW($A$8)=ROW(),$A$1,IF(AND(COUNTIF(B$7:B7,$A$1)>=1,COUNTIF(B$7:B7,$A$1)<=2B$1-1),$A$1,"")),IF($A9=MAX(B$2:B$3),$A$1,IF(AND(COUNTIF(B9:B$30,$A$1)>=1,COUNTIF(B9:B$30,$A$1)<=2B$1-1),$A$1,""))))

2-2(b25関数表示)
=IF(B$1="","",IF(MAX(B$2:B$3)=0,"",IF($A26=MAX(B$2:B$3),$A$1,IF(AND(COUNTIF(B26:B$30,$A$1)>=1,COUNTIF(B26:B$30,$A$1)<=2*B$1-1),$A$1,""))))

c,d列は2,3行目のみの関数を入れているためきれいに表示されています。

最終的には全ての表示条件を１つの関数で行いたいと思ってます。
1の関数と２の関数がぶつかり合う部分で循環関数となってしまい、うまくいきません。

もっと簡単な関数があったり、表の入力、出力の方法が良いものがあれば教えて頂きたいです！

わかりにくいと思いますのでご質問等ご連絡ください。
よろしくお願い致します。

Answer 1

こんばんは。

9時（26行目）の行には必ず何かの値が入ってるんですかねぇ？

もしそうであれば、26行目を基準に 　
「IF関数」と「ROWS関数」で値を入れていけばいいと思います

（A1とA4の）"1時間延長" の文字列を変更可能なら 区別した方が
「INDIRECT関数」が使えるので異なる文字列に変更した方が良いかと思います。

仮に、早出（A1）延長（A4）とした場合は ROWS関数の引数に
「OFFSET関数」で足したり 引いたりすれば値を入れる事が出来そうです。

もし、お使いのPCで 「IFS関数」 が使用可能であれば使用した方が可読性良くなると思います。
開始時間が9時（26行目）と決まっていないのなら
「SWITCH関数」を使うのも面白いかも知れません。

後々、背景色を変更予定なら 条件付き書式は必須だと思われます。