エクセルで学内の備品予約表を作っています。
1つの部につき、貸出期間は同期間で3つまでOK,という条件になっており、
同じ部が同期間で3つまでは〇、4つ目を入力したらエラー(×)がでるような
列を作成したのですが、入力する期間によって正しい〇×になりません。
そこで是非お知恵をお借りしたいと思っております。
貸し出しルールは下記のとおりです。
・同じ部につき、同期間で3つまでOK
・同期間でも別の部ならOK
・期間について、1日でも他行と重なっていたら、同期間とみなす
※入力は都度予約者が行うので、表の部名、日付はソートされていません。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
現状、添付の表のE2セルに下記の式を入れ、
=COUNTIFS(A$2:A2,A2,C$2:C2,">="&B2,B$2:B2,"<="&C2)
D2セルでE2を元に判定式を入れております。
=IF(E2>3,"×","○")
ですが、5行目、9行目のような期間のパターンで
本来は「×」なのに「〇」となってしまいます。
この5,9行目のエラーを修正したいのですが、どのようにすればよいか
ご教示いただけますと大変幸いです。
(〇×が正しく入る方法でしたら、これ以外の新規の式でも問題ございません!)
No.2
- 回答日時:
No1です
>入力者が9行目を入力した時点で8行目の2/27と1日だけ重複するため、
>B&4回目となり×としたい認識でした。
おやそうですか・・・
では、私がご質問の趣旨を理解できていないということになるので、No1の式では求められないと思われます。
なさりたいことの意味を把握できないので、残念ながら関数式も考えられません。
>入れてみたところ「NAME?」エラーがでてしまった
一応、No1で使用している関数の使える環境を調べてみると
IFS:2019以降
SUMPRODUCT:2007以降
ですので、引っかかっているとするとIFS関数でしょうか?
(お使いのバージョンが不明なので、わかりかねますが・・)
IFSだけであれば、IF関数のネストで対応できるでしょう。
とはいえ、内容的に違っているようですので、修正したところで意味はありませんが・・
度々のご回答、誠にありがとうございました。
はじめの説明の仕方に不足があり、貴重なお時間を頂戴してしまい申し訳ありませんでした。
IFS関数につきましてもネストで対応できるとのことで、重ね重ねありがとうございます。使用したことのない関数でしたので、こういったケースで使用できる式を教えていただいたことは勉強になりました!ご教示いただいた式をもとにやりたいことに合わせて自分でも今一度見直してみたいと思います。
この度はありがとうございました!
No.1ベストアンサー
- 回答日時:
こんばんは
ご説明文にある条件と、例示の図が整合していないように見受けられます。
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以上なら×、それ以外なら〇を表示するようにしています。
上記の「省略した条件チェック」を満たしていないデータが混在している場合は、正しい結果を計算できません。
もちろん、式にチェックを組み込むことは可能ですが、その際には、無効なデータをどのように扱うことにするのかを決めておく必要があります。
実際には、条件のチェックに関しては、条件付き書式や入力規則を利用して、入力時に受け付けないなどの処置をしておくのが宜しそうに思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- その他(Microsoft Office) エクセル 条件付き書式 日をまたぐ塗りつぶし 1 2023/01/13 18:00
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- Visual Basic(VBA) vba 等間隔の列に対しての計算 6 2022/05/17 20:15
- Excel(エクセル) エクセルについて教えてください。 2 2023/06/14 11:11
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- Excel(エクセル) アウトラインの小計のやり方 1 2023/03/20 11:51
- Excel(エクセル) フォルダ内のエクセルファイルを開かずにデータ採取する関数式 2 2022/12/22 22:15
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報
ご回答誠にありがとうございます!とても分かりやすく理解できました。
また判定式もありがとうございます。申し訳ございません、こちらは
入れてみたところ「NAME?」エラーがでてしまったため確認できなかったのですが、
pcのverのためだと思うので別のpcで試させていただきます!
>9行目が正しくは×とありますが、図中、部署Bで期間が重複しているのは8行目だけだと思いますので、〇のままで良いのではないでしょうか?
→説明不足で申し訳ありません!重複は先に入力されているものと1日でもかぶったら重複とみなすため、入力者が9行目を入力した時点で8行目の2/27と1日だけ重複するため、B&4回目となり×と
したい認識でした。
こういったケースでも対応できる判定式はございますでしょうか?(ご提示いただいた式で対応可能でしょうか?)
たびたび申し訳ございません。