[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『予約時間の重複を避ける』（さくら）
初めて質問します。
予約時間表を作成し、重複予約にならないようにしたい
会議室使用の予約時間を各自で入れてもらいたいのですが、既に予約が入っている場合、その時間帯は入力できない様にしたいのです。
ある日の最初に「10:00−12:00」の予約が入ったら、使用時間なので
次の人は「9：00−10：00」もしくは「12：00−17：00」の間しか入力できないようにしたいのです。
開始時間の部分については、入力規則でなんとなくできそうなのですが、
（次の予約開始時間を入れるセル）
入力値の種類 → 時刻
データ → 次の値の間以外
次の時刻から → 10：00のセル
次の時刻まで → 12：00のセル
エラーメッセージ → 予約あり
終了時間の方がうまくいきません。
入力規則または関数などで、よい方法はないでしょうか。
また、空き時間を示す表？のようなものができれば最高です。
よろしくお願いします。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
＞予約時間表
１．どんなレイアウトなんですか？
２．時間の単位は何ですか？(時間? 30分？ 1分？)
３．最も早い開始時刻 と 最も遅い終了時刻は？
（半平太） 2018/07/22(日) 09:55
１．レイアウトはこのような感じです
担当者名 開始時間 終了時間
8月1日 ○○ 10:00 12:00
（△△） （ 9:00 ） （12:00 ）← この場合終了時間が入らない様にしたい（エラー表示等）
（ ） （ ） （ ）
8月2日 ×× 13：30 17：00
（ ） （ ） （ ）
（ ） （ ） （ ）
２．単位は特にありませんが、大体30分くらいを想定しています。
３．開始時間は8：30、終了時間は17：30です。
夜にしかパソコンを見ることができないので、
ご回答をいただいてから、すぐにはご返信ができませんがよろしくお願いします。
（さくら） 2018/07/22(日) 22:39
各日付の行数は一定ですか？ それとも、始めは数行用意されていて、 担当者が増えていっぱいになると、都度1行挿入して使うのですか？
上の方に書いた人が、あとで時刻を変更することがありますか？ ある場合、下の方に書いた人と重複が生じたりしますが、 そんな時は、その変更を受付ない様にするんでしょうか？
受付拒否された人は、その後どうするんでしょうか？ 変更ができなければしょうがないですから、 当初OKだった行を削除してくれるんでしょうか？
予定表は何日分あるんですか？ 全体で何行くらいを想定していますか？
（半平太） 2018/07/23(月) 08:07
１日分の行数は、４行にする予定です。
（現在、紙管理で４行あれば収まっています）
予定が入らない休日は、１行（日付行）のみにしたいのですが、行数を変えると難しくなりますか？
時間変更で重複する場合は直接交渉になるので、
エクセル上は先に入力した人の変更を受け付けないようにした方がよいのでしょうか？
予定表は１シートに１ヶ月です。
いかがでしょうか。
（さくら） 2018/07/23(月) 23:09
＞１日分の行数は、４行にする予定です。 ＞（現在、紙管理で４行あれば収まっています） ＞予定が入らない休日は、１行（日付行）のみにしたいのですが、行数を変えると難しくなりますか？
1日4行(日曜日は1行)と決まっていれば、 チェックの為に見に行く範囲が限定されるので、より簡便に対応できます。
＞時間変更で重複する場合は直接交渉になるので、 ＞エクセル上は先に入力した人の変更を受け付けないようにした方がよいのでしょうか？
「エクセル上は」と言うより、使う人にとって、そう作られていないと 変更したが為に、範囲が重複することになったのに気づかない・・なんて恐れが生じますよね。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
データ、数式、入力規則をいちいちセットするのは面倒なので、 後記の<省エネ手順>に従って、マクロで自動セットしてください。
<初期画面> 行 _A_ ______B______ ____C____ ____D____ ____E____ _F_ ____G____ ___H___ ___I___ 1 予定時間表 2 日付 担当者名 開始時刻 終了時刻 作業列1 作業列2 作業列3 3 2018/8/1(水) 3 TRUE TRUE 4 3 TRUE TRUE 5 3 TRUE TRUE 6 3 TRUE TRUE 7 2018/8/2(木) 7 TRUE TRUE
<使い方> １．B3セルに月初日を手入力する。(例は8/1。9月なら9/1と入力する）
すると、同月の予定日がB列に表示されます → 1日4行(日曜日は1行) ※この行の構成を手で変更しないようにしてください。
２．いつも通り、担当者名、開始時刻、終了時刻を入れていく・・・だけです。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
<省エネ手順> 新規シートの「シート見出し」を右クリックして、「コードの表示(V)」を選ぶと 画面中央に白いエリアが表れます。(VBE画面です）
その白いエリアに後記マクロをコピぺし、F5キーを押下してください
(すると、マクロ「onlyOnce」が実行され、自動的に数式と所要データ及び入力規則が設定されます）
※なお、実行は１回だけですので、F5を押したら直ぐ「Ctrl+Z」でコードを消去し、Alt+F11でエクセルに戻って下さい
Private Sub onlyOnce()
With Me
Rem 標準外書式セルをまとめて処理
.Range("B3:B120").NumberFormatLocal = "yyyy/m/d(aaa);@"
.Range("D3:E8").NumberFormatLocal = "h:mm"
Rem 生データのセルをまとめて処理
.Range("B1").Value = "予定時間表"
.Range("B2").Value = "日付"
.Range("C2").Value = "担当者名"
.Range("D2").Value = "開始時刻"
.Range("E2").Value = "終了時刻"
.Range("G2").Value = "作業列1 "
.Range("H2").Value = "作業列2"
.Range("I2").Value = "作業列3"
.Range("B3").Value = 43313
.Range("G3").Value = 3
Rem 数式セルをまとめて処理
.Range("H3:H120").FormulaR1C1Local = "=COUNTIFS(OFFSET(INDEX(C[-4],RC7),0,0,4),""<=""&RC[-4],OFFSET(INDEX(C[-3],RC7),0,0,4),"">""&RC[-4])<=IF(ISBLANK(RC[-3]),0,1)"
.Range("I3:I120").FormulaR1C1Local = "=COUNTIFS(OFFSET(INDEX(C[-5],RC7),0,0,4),""<""&RC[-4],OFFSET(INDEX(C[-4],RC7),0,0,4),"">=""&RC[-4])<=IF(ISBLANK(RC[-5]),0,1)"
.Range("B4:B120").FormulaR1C1Local = "=IF(COUNT(R[-1]C),IF(WEEKDAY(R[-1]C)=1,R[-1]C+1,""""),IF(ROW()-MATCH(99999,R1C:R[-1]C)=4,MAX(R3C:R[-1]C)+1,""""))"
.Range("G4:G120").FormulaR1C1Local = "=IF(RC[-5]="""",R[-1]C,ROW())"
'入力規則設定
With .Range("D3:E120").Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=H3"
.IgnoreBlank = True
End With
End With
End Sub
（半平太） 2018/07/24(火) 17:22
ここまでお時間を割いて回答をしていただき、大変恐縮しています。
こんなに複雑になるとは思わず、びっくりしています。
今日すぐにはできませんが、挑戦してみます。
結果はまた報告させていただきます。
ありがとうございます。
（さくら） 2018/07/24(火) 23:16
お世話になります。
遅くなりましたが、教えていただいた手順で試してみました。
１人目 9：00-15：00
２人目 8：30-14：00
と入力しようとすると、２人目の終了時刻でエラー表示（入力制限）が出るので よいのですが、
１人目 9：00-15：00
２人目 8：30-16：00
と入力すると、２人目の終了時刻が通ってしまいました。
これでは使用時間が重なってしまうので、この場合もエラー表示（入力制限）が出るようにしたいのです。
これを解消する方法はあるでしょうか。
よろしくお願いします。
（自分でも考えてみます）
（さくら） 2018/07/29(日) 01:44
本当ですねぇ・・済みません。
なんか根本的におかしいので、改善案が直ぐ思い付かないです。
今日から旅行に出ますので、今日・明日の対応はちょっと難しいです m(__)m
それ迄にどなたかの回答が付くといいのですが・・
（半平太） 2018/07/29(日) 07:42
１．作業列の数式 (1) G3セル =IF(B3="",G2,ROW()) (2) H3セル =COUNTIFS($G$3:$G$120,$G3,$D$3:$D$120,"<="&$D3,$E$3:$E$120,">"&$D3)-(COUNT($D3:$E3)=2)=0 (3) I3セル =COUNTIFS($G$3:$G$120,$G3,$D$3:$D$120,"<"&$E3,$E$3:$E$120,">="&$E3)-(COUNT($D3:$E3)=2)=0
G3:I3 を 120行目までコピー
２．入力規則の設定 D3:E200 の範囲に「ユーザー設定」
判定用数式 → =SUM(($G$3:$G$120=$G3)*$H$3:$I$120)=8
（半平太） 2018/08/01(水) 20:03
お世話になります。
返答が遅くなり申し訳ございません。
8/1に回答をいただいた部分を追加して試してみたところ、
D列・E列共に、どの時刻を入れようとしても入力制限がかかってしまいました。
（さくら） 2018/08/05(日) 00:52
＞8/1に回答をいただいた部分を追加して試してみたところ、 ＞D列・E列共に、どの時刻を入れようとしても入力制限がかかってしまいました。
こちらでは、特に不具合なく動いています。
念の為、修正後の「onlyOnce」を再送します。
Private Sub onlyOnce()
With Me
Rem 標準外書式セルをまとめて処理
.Range("B3:B120").NumberFormatLocal = "m""月""d""日"""
.Range("D3:E4,D5").NumberFormatLocal = "h:mm"
Rem 生データのセルをまとめて処理
.Range("B1").Value = "予定時間表"
.Range("B2").Value = "日付"
.Range("C2").Value = "担当者名"
.Range("D2").Value = "開始時刻"
.Range("E2").Value = "終了時刻"
.Range("G2").Value = "作業列1 "
.Range("H2").Value = "作業列2"
.Range("I2").Value = "作業列3"
.Range("B3").Value = 43313
.Range("D3").Value = 0.375
.Range("E3").Value = 0.416666666666667
.Range("D4").Value = 0.5
.Range("E4").Value = 0.541666666666667
Rem 数式セルをまとめて処理
.Range("G3:G120").FormulaR1C1Local = "=IF(RC[-5]="""",R[-1]C,ROW())"
.Range("H3:H120").FormulaR1C1Local = "=COUNTIFS(R3C7:R120C7,RC7,R3C4:R120C4,""<=""&RC4,R3C5:R120C5,"">""&RC4)-(COUNT(RC4:RC5)=2)=0"
.Range("I3:I120").FormulaR1C1Local = "=COUNTIFS(R3C7:R120C7,RC7,R3C4:R120C4,""<""&RC5,R3C5:R120C5,"">=""&RC5)-(COUNT(RC4:RC5)=2)=0"
.Range("B4:B120").FormulaR1C1Local = "=IF(COUNT(R[-1]C),IF(WEEKDAY(R[-1]C)=1,R[-1]C+1,""""),IF(ROW()-MATCH(99999,R1C:R[-1]C)=4,MAX(R3C:R[-1]C)+1,""""))"
'入力規則設定
With .Range("D3:E120").Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=SUM(($G$3:$G$120=$G3)*$H$3:$I$120)=8"
.IgnoreBlank = True
End With
End With
End Sub
（半平太） 2018/08/05(日) 16:13
諦めかけていたところでしたが、根気よくお付き合いくださりありがとうございます。
思っていた通りの作業ができ、感謝します。
（さくら） 2018/08/07(火) 01:05
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.