エクセルの複数条件作成方法について

エクセルで学内の備品予約表を作っています。

１つの部につき、貸出期間は同期間で３つまでOK,という条件になっており、
同じ部が同期間で3つまでは〇、4つ目を入力したらエラー(×）がでるような
列を作成したのですが、入力する期間によって正しい〇×になりません。
そこで是非お知恵をお借りしたいと思っております。

貸し出しルールは下記のとおりです。
・同じ部につき、同期間で３つまでOK
・同期間でも別の部ならＯＫ
・期間について、1日でも他行と重なっていたら、同期間とみなす
※入力は都度予約者が行うので、表の部名、日付はソートされていません。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
現状、添付の表のE２セルに下記の式を入れ、
=COUNTIFS(A$2:A2,A2,C$2:C2,">="&B2,B$2:B2,"<="&C2)

D２セルでE2を元に判定式を入れております。
=IF(E2>3,"×","○")

ですが、5行目、9行目のような期間のパターンで
本来は「×」なのに「〇」となってしまいます。
この５，９行目のエラーを修正したいのですが、どのようにすればよいか
ご教示いただけますと大変幸いです。
（〇×が正しく入る方法でしたら、これ以外の新規の式でも問題ございません！）

質問者からの補足コメント

• ご回答誠にありがとうございます！とても分かりやすく理解できました。
  また判定式もありがとうございます。申し訳ございません、こちらは
  入れてみたところ「NAME？」エラーがでてしまったため確認できなかったのですが、
  ｐｃのverのためだと思うので別のｐｃで試させていただきます！
  
  >9行目が正しくは×とありますが、図中、部署Bで期間が重複しているのは8行目だけだと思いますので、〇のままで良いのではないでしょうか？
  →説明不足で申し訳ありません！重複は先に入力されているものと1日でもかぶったら重複とみなすため、入力者が9行目を入力した時点で8行目の2/27と1日だけ重複するため、B＆4回目となり×と
  したい認識でした。
  こういったケースでも対応できる判定式はございますでしょうか？（ご提示いただいた式で対応可能でしょうか？）
  たびたび申し訳ございません。

  No.1の回答に寄せられた補足コメントです。 補足日時：2023/01/24 20:11

No.1 ベストアンサー 回答者： fujillin 回答日時： 2023/01/24 00:27

こんばんは

ご説明文にある条件と、例示の図が整合していないように見受けられます。
9行目が正しくは×とありますが、図中、部署Bで期間が重複しているのは8行目だけだと思いますので、〇のままで良いのではないでしょうか？


以下、要領のみの説明なので、「部署名が入力されている行には、貸出開始日、貸出終了日とも必ず入力されており、かつ、貸出開始日＜＝貸出終了日となっている」という仮定をしています。

「人が、手入力で入力する」という説明ですので、実際には上記の仮定のチェックを省くことはできないと思いますが、まとめてチェックも組み込むと式が長くなってわかりにくくなってしまうと思いますので、要領のみなので省略してあります。
（ご提示の図の3行目のような、開始日＞終了日のようなデータは無いとするという意味です）

上記の条件で、D2セルに
=IFS(A2="","",SUMPRODUCT((A$2:A2=A2)*((B$2:B2>=C2)+(C$2:C2<=B2)=0))>3,"×",1,"〇")
の式を入力し、下方にフィルコピーではいかがでしょうか？

簡単に説明すると、自分の行より上の行で
「A列が同じ部署名かつ、期間に重複がある行の数」を数えて4以上なら×、それ以外なら〇を表示するようにしています。

上記の「省略した条件チェック」を満たしていないデータが混在している場合は、正しい結果を計算できません。
もちろん、式にチェックを組み込むことは可能ですが、その際には、無効なデータをどのように扱うことにするのかを決めておく必要があります。
実際には、条件のチェックに関しては、条件付き書式や入力規則を利用して、入力時に受け付けないなどの処置をしておくのが宜しそうに思います。

No.2 回答者： fujillin 回答日時： 2023/01/24 21:12

No1です

＞入力者が9行目を入力した時点で8行目の2/27と1日だけ重複するため、
＞B＆4回目となり×としたい認識でした。
おやそうですか・・・
では、私がご質問の趣旨を理解できていないということになるので、No1の式では求められないと思われます。
なさりたいことの意味を把握できないので、残念ながら関数式も考えられません。


＞入れてみたところ「NAME？」エラーがでてしまった
一応、No1で使用している関数の使える環境を調べてみると
　IFS：2019以降
　SUMPRODUCT：2007以降
ですので、引っかかっているとするとIFS関数でしょうか？
（お使いのバージョンが不明なので、わかりかねますが・・）
IFSだけであれば、IF関数のネストで対応できるでしょう。
とはいえ、内容的に違っているようですので、修正したところで意味はありませんが・・

この回答へのお礼

度々のご回答、誠にありがとうございました。
はじめの説明の仕方に不足があり、貴重なお時間を頂戴してしまい申し訳ありませんでした。
IFS関数につきましてもネストで対応できるとのことで、重ね重ねありがとうございます。使用したことのない関数でしたので、こういったケースで使用できる式を教えていただいたことは勉強になりました！ご教示いただいた式をもとにやりたいことに合わせて自分でも今一度見直してみたいと思います。
この度はありがとうございました！

お礼日時：2023/01/24 22:19