[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『会社でのお茶当番表を作成したい』（わお）
windowsXP.Excel2003です。
女性15人程で毎月お茶当番表を作成したいのですが、その中に月曜日が忙しいAさんBさんCさん、金曜日が忙しいDさんEさんFさんは、その曜日にあたらない様にしながら残りの人も併せて振り分けたいのですが、関数の組み合わせでこのような当番表を作る事は可能でしょうか？
会社は土日祝はお休みです。
ご教授頂きます様宜しくお願い致します。
＞関数の組み合わせでこのような当番表を作る事は可能でしょうか？
私に出来るかどうかは別として、恐らく可能です。 ですが、どう作るのか不明瞭な部分があり、確かなことは言えません。
疑問点 １．何日分作るのですか？
２．お茶当番は1日当り何人なのですか？
３．要員は15人程との事ですが、 最大は何人までを想定していますか？
４．当番の日に、急病か何かで休んだ場合、 復帰後、優先的に当番になるのですか？ それとも、後の人が順に1日繰り上がるだけで済むのですか？
休暇(短期/長期間)があらかじめ分かっている場合は どう処理するのですか？
５．具体的な当番表のレイアウトを示して頂けませんか？ ※セルのアドレスが分かる様にお願いします。 レイアウトの違いでやり直しになるのは、時間の無駄になります。
(半平太) 2010/10/19 08:52
１．１カ月分ずつ、1シートに作成したいです。
２．お茶当番は１日あたり１人です。
３．最大15人までを想定しています。
現在は10名です。
４．当番の日に休んだ場合、代わりにやってくれた方の当番日に休んだ方が当番をします。
長期休暇の場合や、短期でもあらかじめ分かっている場合は飛ばします。
５．具体的にレイアウトは15日区切りで２段で作成したいです。
10月
１ ２ ３ ４ ５ ６ ７ ８ ９ 10 11 12 13 14 15
金 土 日 月 火 水 木 金 土 日 月 火 水 木 金
Aｻﾝ
Bｻﾝ
Cｻﾝ
Dｻﾝ
Eｻﾝ
Fｻﾝ
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
土 日 月 火 水 木 金 土 日 月 火 水 木 金 土 日
Aｻﾝ
Bｻﾝ
Cｻﾝ
Dｻﾝ
Eｻﾝ
Fｻﾝ
このようなレイアウトで作成したいのですが、ご教授頂けませんでしょうか？
よろしくお願い致します。
わお
私の手には負えないものでした。 m(__)m
たたき台だけアップしておきます。
１．レイアウトは下記にさせて頂きます。 連番などを手入力してください。
２．C3セルに月を入力(下図では10)
３．AN列とAO列に、多忙な「曜日」を１文字で入力。
４．AP列～AY列に休暇予定を入れます。(日を単純数値で入力）
５．名前定義をします。 E5セルを選択したら、Ctrl+F3 でダイアログを出して、以下の名前定義をしてください。
名前 『NEXT』とします。
参照範囲 =MIN(MMULT(ROUND($D$6:D$20,0),SIGN(ROW($B$1:INDEX($B:$B,COLUMN(A1)))))*10^4+($C$6:$C$20="")*10^9+$B$6:$B$20
+MMULT(COUNTIF(E$4,$AN$6:$AO$20),{1;1})*10^9+MMULT(COUNTIF(E$3,$AP$6:$AY$20),ROW($A$1:$A$10))*10^9)
６．セルに数式を入力します。 (1) D6セル =(B6<=MATCH(E$2,AM$6:AM$20,0))*1 D20までフィルコピー
(2) E4セル =TEXT(DATE(YEAR(TODAY())+(MONTH(TODAY())>$C$2),$C$2,E3),"aaa") (3) E5セル =MOD(MATCH($E$2,$C$6:$C$20,0)+COLUMN(A1)-1,COUNTA($C$6:$C$20))+1
E4:E5を一括選択して、AI列迄フィルコピー
(4) E6セル =IF($C6="",0,IF(COUNTIF($AP6:$AT6,E$3),IF(COUNTIF($AP6:$AY6,E$3)*(E$5+COUNTIF($D$5:D$20,0.8)=$B6),0.8,0.2), IF(COUNTIF($AN6:$AO6,E$4),0.1,IF(MOD(Next,100)=$B6,1,0)))) 右へ、下へフィルコピー
７．以下の書式設定をして下さい。 D6:AI20を選択して、セルの書式(表示形式)をユーザー定義で [=1]"当";;;@ と設定してください。
<結果図> 行 _B_ __C__ __D__ _E_ _F_ _G_ _H_ _AE_ _AF_ _AG_ _AH_ _AI_ _AJ_ _AK_ _AL_ __AM__ __AN__ __AO__ ____AP____ _AQ_ _AR_ 1 2 10 3 1 2 3 4 27 28 29 30 31 基本順 多忙1 多忙2 休暇予定→ 4 調整 金 土 日 月 水 木 金 土 日 氏名 5 9 10 1 2 5 6 7 8 9 6 1 Aサン 当 当 Aサン 月 3 10 7 2 Bサン 当 当 Bサン 月 金 8 3 Cサン 当 当 Cサン 9 4 Dサン 当 Dサン 1 2 25 10 5 Eサン 当 Eサン 金 11 6 Fサン 当 Fサン 10 12 7 Gサン 当 当 Gサン 13 8 Hサン 当 当 Hサン 6 14 9 Iサン 当 当 Iサン 金 15 10 Jサン 当 当 Jサン 16 11 17 12 18 13 19 14 20 15
※不要な列や行を非表示にして見やすくしてください。 ※16日以降の下半分は、上半分の後半を単純参照して作ってください。 (半平太) 2010/10/21 20:07
書き忘れました。 m(__)m
E2セルに前月末の当番名を入力してください。
※上のサンプルでは「Hさん」を入れて、テストしています。 ※ この情報がないと、今月は誰から始まるベキなのか分かりませんので。
(半平太) 2010/10/21 20:27
半平太様、私が未熟な為初歩的な入力ミスがあったりしまして何回もやり直したりしている内に随分とお礼が遅くなりまして申し訳ありません。<m(__)m> ただ、1点だけどうしても入力間違いが無いか確認を何度しても見当たらないのですが関数がエラーを起こして動かない所があります。
(4) E6セル =IF($C6="",0,IF(COUNTIF($AP6:$AT6,E$3),IF(COUNTIF($AP6:$AY6,E$3)*(E$5+COUNTIF($D$5:D$20,0.8)=$B6),0.8,0.2),IF(COUNTIF($AN6:$AO6,E$4),0.1,IF(MOD(Next,100)=$B6,1,0)))) のところなのですが、最後のIF(MOD(Next,100)=$B6,1,0))))のNextが違うといってきます。 何故なのでしょうか？
大変お手数をお掛け致しますが、どうかご教授よろしくお願い致します。
(わお)
ありがとうございました<m(__)m>
(わお)
半平太さま 色々とご教授頂き深謝なのですが、もう1点ご教授頂けませんでしょうか？
４．AP列～AY列に休暇予定を入れます。(日を単純数値で入力）
この列をAP列～BI列まで延ばしたいのですが、どのような変更をすれば宜しいのでしょうか？
自分なりに色々と変更してみたのですが、全然動かなくなりました。(T_T)
大変お手数をお掛け致しますが、よろしくお願い致します。
（わお）
この案件は、私の能力範囲外です。複雑な休暇には対応できないです。(^^ゞ
本来当番になる日に休暇を取ると飛ばしてもらえる、と云うことが実現しにくいのです。
休暇当日に当番にならない様にすることはできても、 後日、当番を飛ばして貰えたかどうかの判定が難しい。 しかし、その判定が出来ないと、休暇明けにすぐ当番になるか、 ずーっと先で当番になるか決まるので、大きな違いになります。
「本来当番になる日」とは何か？ これを考えると頭が痛くなります。
誰も休暇を取らない理想的な順をベースにするのか、 月、金は多忙な人の分は調整をした後で決まる順番なのか？ それとも、他人の休暇予定を考慮に入れた順番なのか(しかし、これは一定ではなく相互に影響する）
休暇の翌日に当番するつもりの人がいた。 しかし、他人がそれ以前に休暇予定を入れたため、 当番順は1日繰り上がって休暇予定と重なった。 「それなら休暇翌日の当番はやらない、飛ばして貰えるハズだから。」 と主張する人が出る・・かも。
金曜日が多忙な人が、金曜日に休暇を取ったら、 休暇だから飛ばすのか、予定の金曜だから翌週すぐに当番すべきかなのか？
そんなことを考え出したら、これは私にはできないと悟りました。
私案は、たたき台でしかないです。 どなたかが回答してくれないかな、と期待している状態です。
それにしても、この部分を考慮し忘れておりましたので、現状は全然使えないと思うのですが、 ↓ (土日祝の日は入れない様にすることで対処されたのですか？) >会社は土日祝はお休みです。
取りあえずです。
↓
名前定義の参照範囲
=MIN(MMULT(ROUND($D$6:D$20,0),SIGN(ROW($B$1:INDEX($B:$B,COLUMN(A1)))))*10^4+($C$6:$C$20="")*10^9+$B$6:$B$20
+MMULT(COUNTIF(E$4,$AN$6:$AO$20),{1;1})*10^9+MMULT(COUNTIF(E$3,$AP$6:$BI$20),ROW($A$1:$A$20))*10^9)
E6セル =IF($C6="",0,IF(COUNTIF($AP6:$BI6,E$3),IF(COUNTIF($AP6:$BI6,E$3)*(E$5+COUNTIF($D$5:D$20,0.8)=$B6),0.8,0.2), IF(COUNTIF($AN6:$AO6,E$4),0.1,IF(MOD(Next,100)=$B6,1,0))))
行 _B_ __C__ __D__ __E__ _F_ _G_ _H_ __AM__ __AN__ __AO__ ____AP____ _AQ_ _BH_ _BI_ 1 2 10 Hサン 3 1 4 5 6 基本順 多忙1 多忙2 休暇予定→ 4 調整 金 月 火 水 氏名 5 9 10 1 2 6 1 Aサン 当 Aサン 5 7 2 Bサン 当 当 Bサン 金 8 3 Cサン 当 当 Cサン 9 4 Dサン 当 Dサン 10 5 Eサン 当 Eサン 金 11 6 Fサン 当 Fサン 12 7 Gサン 当 Gサン 13 8 Hサン 当 Hサン 14 9 Iサン 当 Iサン 金 15 10 Jサン 当 Jサン
(半平太) 2010/10/29 16:40
半平太様、週末よりバタバタとして気付かずにお礼を申し上げるのが遅れました。大変申し訳ありません<m(__)m> 休暇予定の範囲を延ばしたかったのは、そこへ月間の土日祝と予め判っている休暇日を 手入力して対応できるのでは？と思ったからなんです。 休暇翌日の当番日に当たっていて拒否は出来ない様になっています。嫌なら誰かに代わって貰う交渉は本人が行います。
これから金曜日にご回答頂いた式を当てはめてみたいと思います。
(わお)
休暇は、その日に当番にならない、と云うだけの意味でしたら以下で。
１．レイアウトは下記に変更させて頂きます。 前回の5行目は無くなりました。
名前、連番、日付(単純数値)を手入力してください。
２．C2セルに月を入力(単純数値で入力) 下図では11 → 年度の判定をしていますので古い月は入れないで下さい。10と入れると2011年10月とみなされます。
３．E2セルに先月末の当番名を入力します（下図では「Hさん」）
４．AN列とAO列に、各人の多忙な「曜日」を１文字で入力。
５．AP列～BI列に休暇予定を入れます。(日を単純数値で入力）
６．名前定義をします。 前回の名前定義は削除して下さい。
E5セルを選択したら、Ctrl+F3 でダイアログを出して、以下の名前定義をしてください。
名前 『当番行』とします。
参照範囲 =MOD(MIN(MMULT($D$5:D$19,SIGN(ROW($B$1:INDEX($B:$B,COLUMN(A1)))))*10^4+($C$5:$C$19="")*10^9+MMULT(COUNTIF(E$4,$AN$5:$AO$19),{1;1})*10^9+MMULT(COUNTIF(E$3,$AP$5:$BI$19),SIGN(ROW($A$1:$A$20)))*10^9+$B$5:$B$19),100)
７．セルに数式を入力します。 (1) D5セル =(B5<=MATCH(E$2,AM$5:AM$19,0))*1 D19までフィルコピー
(2) E4セル =TEXT(DATE(YEAR(TODAY())+(MONTH(TODAY())>$C$2),$C$2,E3),"aaa")
右へフィルコピー (AI列まで）
(3) E5セル =IF(OR($C5="",OR(E$4={"土","日","祝"}),COUNTIF($AP5:$BI5,E$3)),0,IF(当番行=$B5,1,0))
右へ、下へフィルコピー
８．以下の書式設定をして下さい。 D5:AI19を選択して、セルの書式(表示形式)をユーザー定義で "当";;;@ と設定してください。
行 _A_ _B_ __C__ __D__ __E__ _F_ _G_ _H_ _I_ _J_ _K_ _L_ _M_ _N_ __AM__ __AN__ __AO__ ____AP____ _AQ_ 1 2 11 Hサン 3 1 2 3 4 5 6 7 8 9 10 基本順 多忙1 多忙2 休暇予定→ 4 調整 月 火 水 木 金 土 日 月 火 水 氏名 5 1 Aサン 当 当 Aサン 6 2 Bサン 当 当 Bサン 4 7 3 Cサン 当 当 Cサン 金 8 4 Dサン 当 当 Dサン 月 9 5 Eサン 当 当 Eサン 金 10 6 Fサン 当 当 Fサン 11 7 Gサン 当 Gサン 12 8 Hサン 当 Hサン 13 9 Iサン 当 Iサン 金 14 10 Jサン 当 Jサン 15 11
※祝日の日は、曜日の代わりに『祝』と手入力してください。(4行目) 別途、祝日リストが作られていれば、数式で対応できます。 ※土日祝は、全員同じ立場ですので、休暇予定で調整する様なことはしないでください。(入れるとトラぶります)
(半平太) 2010/11/04 18:19
半平太様 あれから体調を崩してしまい、御礼が遅れまして申し訳ありません。 11/4に更新頂いた関数を入力致しまして作成したのですが、 土日祝以外の月～金の所が関数エラーになってしまいます(T_T) 私の入力が間違っているのかもしれないので、再度作成したいと思います。
(わお)
１．E2セルの前月末当番の名前(Hさん)を入れましたか？ 「Hさん」は、C列に完全に同じ名前で存在していますね？
２．名前定義は正しく設定されていますか？
試しに、E5セルに =当番行 と入れてみて、 どうなるか診てください。
(半平太) 2010/11/12 17:16
半平太さま
あれから、何回も見直しをして式入力の間違いがないか確認しましたが見当たらず、依然土日祝以外のところには#VALUEが表示されます。。。。 休暇予定日を入れた所だけ0になりました。
E5セルに=当番行と入れると全てのセルに#VALUEと表示されます。
どこが間違っているのでしょうか？ お忙しいところ、お手数ばかりお掛け致しますがご教授よろしくお願い致します。
わお 2010/11/19 14:41
＞E5セルに=当番行と入れると全てのセルに#VALUEと表示されます。
そうですか？ 私の環境ではE5セルに「9」と出ます。 9番が当番だから。。
恐らく、名前定義がうまく行っていないのでしょう。 この手順で設定しましたか？（E5を選択してから設定する必要がありますよ） ↓ ※ E5セルを選択したら、Ctrl+F3 でダイアログを出して、以下の名前定義をしてください。 ※確認の為、E5セルを選択してから、Ctrl+F3 でダイアログを出し、 どんな数式になっているか、コピーして私の数式と比較してみてください。 余分な""マークなんかが無いですか？ この掲示板にありのまま貼付けて頂ければ、私がチェックしますけど。。
それでもダメなら、新規シートで、以下のデータだけでトライしてみてください。 (それ以外は何のデータも入れない)
セルの書式を標準にすると、以下の状態になれば正常です。
行 _A_ _B_ __C__ __D__ __E__ _F_ __AM__ 1 2 11 Hサン 3 1 2 4 月 火 5 1 Aサン 1 0 0 Aサン 6 2 Bサン 1 0 0 Bサン 7 3 Cサン 1 0 0 Cサン 8 4 Dサン 1 0 0 Dサン 9 5 Eサン 1 0 0 Eサン 10 6 Fサン 1 0 0 Fサン 11 7 Gサン 1 0 0 Gサン 12 8 Hサン 1 0 0 Hサン 13 9 Iサン 0 1 0 Iサン 14 10 Jサン 0 0 1 Jサン 15 11 0 0 0 16 12 0 0 0 17 13 0 0 0 18 14 0 0 0 19 15 0 0 0
＞式入力の間違いがないか確認しましたが見当たらず、 できるだけ、私の書いたものを「コピー / 貼り付け」でやってください。 手入力で正しく入れるのは至難です。 (半平太) 2010/11/19 16:12
半平太様、名前定義のところですが、コピーをして貼り付けた後にOKを押してもエラーが改善されない為、再度Ctrl+F3で確認しようとすると貼り付けた式が、下記のように変わっていました。 この式もコピーして貼り付けました。
=MMULT(Sheet2!$D$5:D$19,SIGN(ROW(Sheet2!$B$1:INDEX(Sheet2!$B:$B,COLUMN(Sheet2!A1)))))*10^4+(Sheet2!$C$5:$C$19="")*10^9+MMULT(COUNTIF(Sheet2!E$4,Sheet2!$AN$5:$AO$19),{1;1})*10^9 COUNTIF(Sheet2!E$3,Sheet2!$AP$5:$BI$19)ฮSIGN(ROW(Sheet2!$A$1:$A$20))
何故コピーをして貼り付けた式がこの様に変わってしまうのでしょうか？
わお11/19 17:06
＞何故コピーをして貼り付けた式がこの様に変わってしまうのでしょうか？ 「Sheet2!」←これが増えたのは問題ありません。 エクセルが自動的に調整してくれた結果ですので気にしないでください。
でもMMULTから始まっているのは納得いきません。
私の上のレス(2010/11/04 18:19 のもの)に従ってやってください。
※ 見易くする為に、名前定義の数式に改行を入れたのですが、 これが上手くいかない原因になっているかも知れません。
改行を取るとだらだらと長くなりますが、
それには目をつぶり、数式を修正して置きますので、
式をそのままコピペしてください。
(半平太) 2010/11/19 17:36
半平太様
修正して頂いた数式をコピペしてやり直したら、出来上がりました!!!!! 来年の1月までシートをコピーして作成したのですが、何の問題も無くスムーズに 当番表が出来ています(^^♪
お忙しいところ、本当にお手数をお掛けいたしました<m(__)m> 感謝感激です<m(__)m>
ありがとうございました<m(__)m>
わお 11/22 14:16
もうすでに解決しているようなので、見ていないとは思いますが、 マクロで作ってみました。
ほとんど趣味の内容ですが、下記の一番下にファイルをリンクしています。 http://d.hatena.ne.jp/Mook/20101123
最近ファイルを扱うことがなかったのですが、ramrun さんとこは使えなくなったのかな。 (Mook)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.