[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『カレンダー形式の当番表から祝日等の当番回数をカウントしたい』（omi）
先程、投稿の仕方を間違えてしまいました。
もう一度宜しくお願い致します。
下記のようなカレンダー形式の当番表から以下の事をしたいです。
1:平日・土曜・日曜及び祝日の回数をカウントしたい
（土曜が祝日なら祝日としてカウント）
2:当番担当者をランダムに入れたい
3:関数でもマクロでも構いません
【現在の設定】
1:毎月1日は変動するよう設定しています。
※「2019年4月」を変更すると自動で変更
(今年の5月ならE2に「1」が来ます)
2:祝日は別シートで作成してあります。(土・日・祝は日付の色を変更)
2019年4月 当番表
A B C D E F G H 1 日 月 火 水 木 金 土 2 1 2 3 4 5 6 3当番 田中 鈴木 山田 田中 山田 鈴木 4 7 8 9 10 11 12 13 5当番 鈴木 田中 鈴木 山田 田中 山田 鈴木
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
回答ではありません。
前回の投稿は [[20190411141348]] 『平日、土曜、日曜祝日の当番の回数を数える(カレメx（omi） ですよね？
回答が付かないのは、情報が足りないのと、面倒だからかと思います。
1.について 祝日はどのような形の表ですか？ また祝日が日曜日の場合は、月曜日の振替休日になりますか？ その場合、祝日の表は、どちらの日付で登録されていますか？ どこにカウントした結果を出せばいいですか？ 人別ではなく、月内で何日がわかればよいだけですか？ 2.について ランダム とありますが、田中さんが10回連続で当番になる場合も「ランダム」ではあり得ます。 現実的に何名いて、どのようなローテーションが望ましいとかあると思います。 それこそ、3人くらいなら手でやったほうが早いので「自分でやってくれ」になりますが。
（稲葉） 2019/04/11(木) 17:38
ご質問の件、回答致します。
1.について
祝日はどのような形の表ですか？
また祝日が日曜日の場合は、月曜日の振替休日になりますか？
その場合、祝日の表は、どちらの日付で登録されていますか？
→下記のように別シートで作りました
「祝日リスト」（祝日の多い5月を例で･･）
2019/5/1 天皇即位
2019/5/2 振替休日
2019/5/3 憲法記念日
2019/5/4 みどりの日
2019/5/5 こどもの日
2019/5/6 振替休日
どこにカウントした結果を出せばいいですか？
人別ではなく、月内で何日がわかればよいだけですか？
→下記のように結果を表記したいです
山田 田中 鈴木
平日 2 3 3
土曜 1 2 1
祝・日曜 3 1 2
2.について ランダム とありますが、田中さんが10回連続で当番になる場合も「ランダム」ではあり得ます。 現実的に何名いて、どのようなローテーションが望ましいとかあると思います。 それこそ、3人くらいなら手でやったほうが早いので「自分でやってくれ」になりますが。
→正確に申し上げますと、当番担当者は5人おり、1日につき2人でペアを組むようになります。
作業の内容・担当者により、組み合わせを替えるようになります。（ここはもちろん手作業です）
エクセルでランダムに一人目の担当者を決めて、ペアの人を後から手作業で入れこむように考えておりました。
でも、この２.のところは、面倒なようなので質問を止めておきます。。
申し訳ありません。
（omi） 2019/04/11(木) 18:13
カレンダーの配置の全貌が不明ですが、
１．下図のレイアウトだと仮定 ２．日付はシリアル値と仮定 ３．祝日一覧は「祝日リスト」と名前定義されている。
４．数式
(1) C15セル =COUNT(INDEX(0/((WORKDAY($B$2:$H$12-1,1,祝日リスト)=$B$2:$H$12)*($B$3:$H$13=C$14)),0)) (2) C16セル =COUNT(INDEX(0/((WORKDAY.INTL($H$2:$H$12-1,1,"0000000",祝日リスト) =$H$2:$H$12)*($H$3:$H$13=C$14)),0)) (3) C17セル =COUNT(INDEX(0/((WORKDAY.INTL($B$2:$H$12-1,1,"0000001",祝日リスト)<>$B$2:$H$12)*($B$3:$H$13=C$14)),0))
<結果図>
行 __A__ ____B____ ___C___ ___D___ ____E____ ____F____ ____G____ ____H____ 1 日 月 火 水 木 金 土 2 2019/5/1 5月2日 5月3日 5月4日 3 当番 田中 鈴木 山田 田中 4 5月5日 5月6日 5月7日 2019/5/8 2019/5/9 2019/5/10 2019/5/11 5 当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 6 5月12日 5月13日 5月14日 5月15日 5月16日 5月17日 5月18日 7 当番 山田 鈴木 8 5月19日 5月20日 5月21日 5月22日 5月23日 5月24日 5月25日 9 当番 10 5月26日 5月27日 5月28日 5月29日 5月30日 5月31日 11 当番 12 13 当番 14 山田 田中 鈴木 15 平日 1 1 3 16 土曜 0 1 0 17 祝・日曜 2 2 3
（半平太） 2019/04/11(木) 23:07
平日のチェック範囲は全体(B：H）である必要はなく、C:G でよかった。m(__)m
> (1) C15セル =COUNT(INDEX(0/((WORKDAY($B$2:$H$12-1,1,祝日リスト)=$B$2:$H$12)*($B$3:$H$13=C$14)),0))
↓ ↓ ↓
(1) C15セル =COUNT(INDEX(0/((WORKDAY($C$2:$G$12-1,1,祝日リスト)=$C$2:$G$12)*($C$3:$G$13=C$14)),0))
（半平太） 2019/04/12(金) 08:13
(半平太)様 出来ました！ありがとうございます！! カレンダーの配置の説明が足りず申し訳ありません。。 作成して頂いたイメージの通りです！
恥を忍んでお尋ねしたいのですが、当番者が1日あたり3名になる場合は数式のどこを変えれば 良いのでしょうか･･･？ 2時間調べたのですが、わかりませんでした･･･
行 __A__ ____B____ ___C___ ___D___ ____E____ ____F____ ____G____ ____H____ 1 日 月 火 水 木 金 土 2 2019/5/1 5月2日 5月3日 5月4日 3 当番A 田中 鈴木 鈴木 田中 4 当番B 山田 佐藤 佐藤 山田 5 当番C 鈴木 田中 山田 鈴木 （omi） 2019/04/12(金) 09:56
＞ 恥を忍んでお尋ねしたいのですが、当番者が1日あたり3名になる場合は数式のどこを変えれば ＞ 良いのでしょうか･･･？
恥を忍んでお答えします。分かりません。
他の回答者のレスをお待ちください。 m(__)m
（半平太） 2019/04/12(金) 11:15
食指の動く人は居なかったですか・・
数式だけでやるのは厄介なので、部分的にマクロを利用することにします。
それにしても、表の全貌が分かりにくいです。 全体の範囲はどうなっていますか？ 結果をどこに表示するんですか？
※これまで、全部で５名、ペアリングは２名、と言った説明があったと思うのですが、 今回の表は当番が３種類ですね。上の５名および２名とどんな関係にあるんでしょうか？
（半平太） 2019/04/13(土) 19:16
半平太様
返信遅くなり申し訳ありません。
全体図は半平太様が以前作成してくださった図とほぼ同じです。 つたない説明なのに、理解してくださって、感謝です。。
修理の当番をする職人が5名おり、その月の担当エリアが広ければ、修理当番が2名 結果の報告を受ける社員が1名つくようになります。 １日あたり、3名となります。 (エリアが狭い場合は修理当番1名、社員1名)
当番担当者は職人のベテランと新米の組み合わせになるように考えてペアを組みます。 社員は順番に担当します。
こんな説明で大丈夫でしょうか・・
組み合わせは手作業で入れて、個人の平日・土曜・祝祭日の回数をカウントできたら… と思ったのですが、面倒な作業ですよね・・
行 __A__ ____B____ ___C___ ___D___ ____E____ ____F____ ____G____ ____H____
1 日 月 火 水 木 金 土 2 2019/5/1 5月2日 5月3日 5月4日 3 当番 田中 鈴木 山田 田中 4 当番 河野 山田 佐藤 田中 5 社員 一郎 次郎 三郎 四郎 6 5月5日 5月6日 5月7日 2019/5/8 2019/5/9 2019/5/10 2019/5/11 7 当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 8 当番 河野 山田 佐藤 田中 鈴木 山田 田中 9 社員 五郎 六郎 一郎 次郎 三郎 四郎 五郎 10 5月12日 5月13日 5月14日 5月15日 5月16日 5月17日 5月18日 11当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 12当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 13社員 五郎 六郎 一郎 次郎 三郎 四郎 五郎 14 5月19日 5月20日 5月21日 5月22日 5月23日 5月24日 5月25日 15当番 田中 鈴木 山田 田中 鈴木 山田 田中 16当番 山田 佐藤 田中 田中 鈴木 山田 田中 17社員 五郎 六郎 一郎 次郎 三郎 四郎 五郎 18 5月26日 5月27日 5月28日 5月29日 5月30日 5月31日 19当番 田中 鈴木 山田 田中 鈴木 山田 20当番 田中 鈴木 山田 田中 鈴木 山田 21社員 五郎 六郎 一郎 次郎 三郎 四郎 22 23当番 24当番 25社員 26 (当番) 計：5名 (社員) 計：6名 27 山田 田中 鈴木 一郎 次郎 三郎 28 平日 1 1 3 平日 1 1 3 29 土曜 0 1 0 土曜 0 1 0 30 祝・日曜 2 2 3 祝・日曜 2 2 3
（ omi） 2019/04/15(月) 13:36
もう一点、教えてください。
日付は、該当月の１日〜末日が動的に表示されていると思うのですが、 いま、何月にするか、どのセルで指定しているのですか？
※多分、どこかのセルに「5」と入れているんですよね？ (例として、5月ならの話ですが）
（半平太） 2019/04/15(月) 15:41
半平太様
お忙しいところありがとうございます。 今作ってるシートを再現してみました。
C2に該当月を入力すると、日付が自動で変わります。
行 __A__ ____B____ ___C___ ___D___ ____E____ ____F____ ____G____ ____H____ ____I____ ____J____ 1 当番予定表 2 2019 年 5 月 1 日 月 火 水 木 金 土 2 2019/5/1 5月2日 5月3日 5月4日 3 当番 田中 鈴木 山田 田中 4 当番 河野 山田 佐藤 田中 5 社員 一郎 次郎 三郎 四郎 6 5月5日 5月6日 5月7日 2019/5/8 2019/5/9 2019/5/10 2019/5/11 7 当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 8 当番 河野 山田 佐藤 田中 鈴木 山田 田中 9 社員 五郎 六郎 一郎 次郎 三郎 四郎 五郎 10 5月12日 5月13日 5月14日 5月15日 5月16日 5月17日 5月18日 11当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 12当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 13社員 五郎 六郎 一郎 次郎 三郎 四郎 五郎 14 5月19日 5月20日 5月21日 5月22日 5月23日 5月24日 5月25日 15当番 田中 鈴木 山田 田中 鈴木 山田 田中 16当番 山田 佐藤 田中 田中 鈴木 山田 田中 17社員 五郎 六郎 一郎 次郎 三郎 四郎 五郎 18 5月26日 5月27日 5月28日 5月29日 5月30日 5月31日 19当番 田中 鈴木 山田 田中 鈴木 山田 20当番 田中 鈴木 山田 田中 鈴木 山田 21社員 五郎 六郎 一郎 次郎 三郎 四郎 22 23当番 24当番 25社員 26 (当番) 計：5名 (社員) 計：6名 27 山田 田中 鈴木 一郎 次郎 三郎 28 平日 1 1 3 平日 1 1 3 29 土曜 0 1 0 土曜 0 1 0 30 祝・日曜 2 2 3 祝・日曜 2 2 3 （ omi） 2019/04/15(月) 17:05
標準モジュールに下記コードを貼り付けたら 以下の数式を入力して、右にコピーする
(1) B30セル =WKDYSbyName(B$29,$C$4:$G$27,$C$2) ← 月〜金の範囲 (2) B31セル =WKDYSbyName(B$29,$H$4:$H$27,$C$2) ← 土のみの範囲 (3) B32セル =COUNTIF($B$4:$H$27,B$29)-SUM(B30:B31) ← 日〜土の範囲（全部）
(4) H30セル =WKDYSbyName(H$29,$C$4:$G$27,$C$2) (5) H31セル =WKDYSbyName(H$29,$H$4:$H$27,$C$2) (6) H32セル =COUNTIF($B$4:$H$27,H$29)-SUM(H30:H31)
’標準モジュールに貼り付けるコード
Rem 配列で返させる。1番目は祝日以外の日数、2番目は全日数
Function WKDYSbyName(NM As String, rng As Range, Mon As Range) 'Monは無駄な再計算をさせないため
Dim cel As Range, Workdy(1 To 2) As Long
Dim TheDay, HoList As Range
Set HoList = Application.Range("祝日リスト")
Workdy(2) = Application.CountIf(rng, NM)
If Workdy(2) > 0 Then
For Each cel In rng
If cel.Value = NM And cel.Value <> "" Then
TheDay = Application.Lookup(99999, cel.EntireColumn.Range("A1").Resize(cel.Row, 1))
If Application.CountIf(HoList, TheDay) = 0 Then
Workdy(1) = Workdy(1) + 1
End If
End If
Next
End If
WKDYSbyName = Workdy
End Function
<結果図> 行 _____A_____ ___B___ ___C___ ___D___ ____E____ ____F____ ____G____ ____H____ ___I___ __J__ __K__ __L__ 1 当番予定表 2 2019 年 5 月 3 日 月 火 水 木 金 土 4 5月1日 5月2日 5月3日 5月4日 5 当番 田中 鈴木 山田 田中 6 当番 河野 山田 佐藤 田中 7 社員 一郎 次郎 三郎 四郎 8 5月5日 5月6日 5月7日 2019/5/8 2019/5/9 2019/5/10 2019/5/11 9 当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 10 当番 河野 山田 佐藤 田中 鈴木 山田 田中 11 社員 五郎 六郎 一郎 次郎 三郎 四郎 五郎 12 5月12日 5月13日 5月14日 5月15日 5月16日 5月17日 5月18日 13 当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 14 当番 鈴木 鈴木 鈴木 田中 鈴木 山田 田中 15 社員 五郎 六郎 一郎 次郎 三郎 四郎 五郎 16 a 5月19日 5月20日 5月21日 5月22日 5月23日 5月24日 5月25日 17 当番 田中 鈴木 山田 田中 鈴木 山田 田中 18 当番 山田 佐藤 田中 田中 鈴木 山田 田中 19 社員 五郎 六郎 一郎 次郎 三郎 四郎 五郎 20 5月26日 5月27日 5月28日 5月29日 5月30日 5月31日 21 当番 田中 鈴木 山田 田中 鈴木 山田 22 当番 田中 鈴木 山田 田中 鈴木 山田 23 社員 五郎 六郎 一郎 次郎 三郎 四郎 24 25 当番 26 当番 27 社員 28 (当番) 計：5名 (社員) 計：6名 29 山田 田中 鈴木 一郎 次郎 三郎 四郎 五郎 30 平日 11 9 16 平日 4 4 4 4 0 31 土曜 0 6 0 土曜 0 0 0 0 3 32 祝・日曜 4 6 5 祝・日曜 1 1 1 1 4
（半平太） 2019/04/15(月) 17:59
半平太様
すごいです！！できました！！ 本当にありがとうございます！！！ （ omi） 2019/04/16(火) 09:53
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.