[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『当番表のカウント』（モモ）
お世話になります。
Excelで毎月当番表を作っています。
平日当番は1点、土日祝の当番は2点として自動計算ができるようにするには、どのような関数を使えばいいのでしょうか(>_<)
（平日の当番は月に4回で4点、土日祝の当番は2回で4点、となれば平等になるという確認が必要）
名前は、ドロップダウンリストで選択できるようにしています。
ざっくりで申し訳ありませんが、よろしくお願いいたします(>_<)
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
どんな表で、どんなデータがあるのか分からないと始まらないので、
その範囲を選択 → コピー ↓ この掲示板に貼り付け
としてみてください。
（半平太） 2016/06/23(木) 16:29
ありがとうございます。
見えにくいかとは思いますが、7月だと、こんな感じです。
月 火 水 木 金 土 日 1 2 3 AB CD GH
4 5 6 7 8 9 10
JK LM CO PQ RS EF HI
たとえば、Cさんは土曜日に1回、水曜日に1回当番に入っていますので、
点数は3点になります。
Hさんは、日曜日に2回当番に入っているので、点数は4点。
毎月、一人4点がノルマなので、Hさんは11日以降は当番にはいれる必要ありません。
でも、Cさんは3点なので、平日にあと1回当番に入れないといけません。
当番を入れていく中で、シートのどこかに「Aさん：○点」という感じで
わかるような関数があれば・・と思っています。
こんな説明でわかりますでしょうか・・・
よろしくお願いいたしますm(__)m
（モモ） 2016/06/23(木) 16:57
こう言うのは、本物に近いレイアウトとデータを提示して頂かないと ２度手間になる恐れが強いです。
※祭日をどう判定すべきなのか分かりませんし、 氏名が、本当にA,B,C,・・・なのかも怪しい。
以下、とりあえず叩き台です。
J2セル =SUMPRODUCT(ISNUMBER(FIND(I2,$A$3:$G$12))*{1,1,1,1,1,2,2})
下にコピー
＜点数早見表 結果図＞
行 _A_ _B_ _C_ _D_ _E_ _F_ _G_ _H_ __I__ __J__ 1 月 火 水 木 金 土 日 氏名 点数 2 1 2 3 A 1 3 AB CD GH B 1 4 4 5 6 7 8 9 10 C 3 5 JK LM CO PQ RS EF HI D 2 6 11 12 13 14 15 16 17 E 2 7 F 2 8 18 19 20 21 22 23 24 G 2 9 H 4 10 25 26 27 28 29 30 31 I 2 11 J 1 12 K 1 13 L 1 14 M 1 15 N 0 16 O 1 17 P 1 18 Q 1 19 R 1 20 S 1
（半平太） 2016/06/23(木) 19:27
お礼が遅くなり申し訳ありません。
そして、コピペがうまくいかない上に、実名を出すのを躊躇して、Aさん、Bさんとしてしましました。
再度、コピペしてみました。
1列ずつ、名前を入れています。
平成 ２８ 年度 7月 当番表
月 火 水 木 金 土 日 1 2 3 練習試合 練習試合
田中 小田 佐藤 森 川野 堀 西田 松本 石井 伊藤
4 5 6 7 8 9 10 休み 練習試合 練習試合
村上 林 藤原 古賀 村田 岡田 佐藤 二宮 吉村 田中 古賀 原 岡 赤井 村上
11 12 13 14 15 16 17 休み 練習試合
中川 川野 太田 下田 手島 森 田中 尾上 新田 二宮 堀 川口 鈴木 伊藤
お手数おかけして申し訳ありません(>_<)
祭日の判定が一番難しいですよね。。祭日に関しては、1点として、手動で1点加算しての2点にするしかないでしょうか。
どうぞよろしくお願いいたします(>_<)
（モモ） 2016/06/24(金) 14:03
レイアウトを確認したいのですが、これでいいですか？ ※氏名が２列だとこんなのかと思いますが、行番号が正しいか自信ないです。(最下行は33行目？） ※日付は、ただの数値ですね？
行 __A__ __B__ __C__ __D__ ___E___ __F__ __G__ __H__ __I__ __J__ ____K____ __L__ ____M____ __N__ 1 平成 28 年度 7月 当番表 2 3 月 火 水 木 金 土 日 4 1 2 3 5 練習試合 練習試合 6 7 田中 小田 佐藤 森 川野 堀 8 西田 松本 石井 伊藤 9 10 4 5 6 7 8 9 10 11 休み 練習試合 練習試合 12 13 村上 林 藤原 古賀 村田 岡田 佐藤 二宮 吉村 田中 古賀 原 14 岡 赤井 村上 村上 15 16 11 12 13 14 15 16 17 17 休み 練習試合 18 19 中川 川野 太田 下田 手島 森 田中 尾上 新田 二宮 堀 川口 鈴木 伊藤 20 21 22 18 19 20 21 22 23 24 23 24 25 26 赤井 27 28 25 26 27 28 29 30 31 29 30 31 32 33 岡
（半平太） 2016/06/24(金) 20:54
そうです、↑のような表で間違いありません。
18日以降は省略しましたが、2〜4人、当番に入っています。
最終行は33行目です。
引き続きよろしくお願いいたします(>_<)
（モモ） 2016/06/24(金) 21:26
以下の作業が面倒くさい場合は、後述のマクロを使って、数式やサンプルデータを自動入力しちゃってください。
１．P2セル以下に全員の名前を書き出す。
２．S2セルから右に７月の祭日を入力する。(最多、Y2セルまで） サンプルでは、テスト用に7/29も入れています。
３．数式を入力する。
(1) S9セル =(A1&B1&LEFT(C1)&D1&"1日")-WEEKDAY((A1&B1&LEFT(C1)&D1&"1日"),2)+8
(2) S10セル =IF(S$2="","",S2-$S$9) (3) S11セル =IF(S$2="","",IF(S10<0,-1,FLOOR(S10/7,1))) (4) S12セル =IF(S$2="","",S11*6) (5) S13セル =IF(S$2="","",MOD(S10,7)*2)
S10:S13を範囲選択して、右へフィルコピーする
(6) S3セル =IF(S$2="","",IF(WEEKDAY(S$2-1)<6,OFFSET($A$10,S$12+ROW(A1),S$13)&"",""))
これを右へ、下へフィルコピー（Y7セルまで）
(7) Q2セル =TEXT(IF(P2="","",SUMPRODUCT((A$5:N$39=P2)*{1,1,1,1,1,1,1,1,1,1,2,2,2,2}))+COUNTIF($S$3:$Y$7,P2),"[>4}]""Over4"";[=4]""Just4"";0")
これを下にコピー(名前の最終行まで）
<結果図> 行 __A__ __B__ __C__ __D__ ___E___ __F__ __G__ __H__ __I__ __J__ ____K____ __L__ ____M____ __N__ _O_ __P__ __Q__ _____R_____ ______S______ ___T___ _U_ 1 平成 28 年度 7月 当番表 氏名 2 田中 Just4 祭日→ 7月18日 7月29日 3 月 火 水 木 金 土 日 小田 1 4 1 2 3 0 佐藤 3 5 練習試合 練習試合 森 3 6 川野 3 赤井 7 田中 小田 佐藤 森 川野 堀 堀 Just4 赤井 8 西田 松本 石井 伊藤 村上 Over4 9 林 1 第二週月曜 2016/7/4(月) 10 4 5 6 7 8 9 10 1 藤原 1 日数差 14 25 11 休み 練習試合 練習試合 古賀 3 週差 2 3 12 村田 1 オフ(行) 12 18 13 村上 林 藤原 古賀 村田 岡田 佐藤 二宮 吉村 田中 古賀 原 岡田 1 オフ(列) 0 8 14 岡 赤井 村上 村上 二宮 2 15 吉村 2 16 11 12 13 14 15 16 17 2 原 2 17 休み 練習試合 中川 1 18 太田 1 19 中川 川野 太田 下田 手島 森 田中 尾上 新田 二宮 堀 川口 鈴木 伊藤 下田 1 20 手島 1 21 尾上 1 22 18 19 20 21 22 23 24 3 新田 1 23 川口 2 24 鈴木 2 25 伊藤 Just4 26 赤井 西田 2 27 松本 2 28 25 26 27 28 29 30 31 石井 2 29 岡 Just4 30 赤井 Over4 31 32 33 赤井 岡
ーーーーーーーーー 数式とサンプルデータを自動で入れたい場合 ーーーーーーーーーーーーーーー
新規シートの「シート見出し」を右クリックして、「コードの表示」を選ぶと 画面中央に白いエリアが表れますので、そこにマクロコードをコピぺしてF5キーを押下(つまり実行）してください
すると自動的にデータと数式が入力されます。 実行は１回だけですので、終わったらマクロコードは削除して下さい
Private Sub onlyOnce()
Rem 生データのセルをまとめて処理
Range("A1").Value = "平成"
Range("B1,G28").Value = 28
Range("C1").Value = "年度"
Range("D1").Value = "7月"
Range("E1").Value = "当番表"
Range("P1").Value = "氏名"
Range("P2,I7,L13,G19").Value = "田中"
Range("R2").Value = "祭日→"
Range("S2").Value = 42569
Range("T2").Value = 42580
Range("A3").Value = "月"
Range("C3").Value = "火"
Range("E3").Value = "水"
Range("G3").Value = "木"
Range("I3").Value = "金"
Range("K3").Value = "土"
Range("M3").Value = "日"
Range("P3,J7").Value = "小田"
Range("I4,O10").Value = 1
Range("K4,O16").Value = 2
Range("M4,O22").Value = 3
Range("O4").Value = 0
Range("P4,K7,I13").Value = "佐藤"
Range("K5,M5,K11,M11,K17").Value = "練習試合"
Range("P5,L7,F19").Value = "森"
Range("P6,M7,B19").Value = "川野"
Range("N7,P7,K19").Value = "堀"
Range("K8,P26").Value = "西田"
Range("L8,P27").Value = "松本"
Range("M8,P28").Value = "石井"
Range("N8,N19,P25").Value = "伊藤"
Range("P8,A13,M14:N14").Value = "村上"
Range("P9,B13").Value = "林"
Range("R9").Value = "第二週月曜"
Range("A10").Value = 4
Range("C10").Value = 5
Range("E10").Value = 6
Range("G10").Value = 7
Range("I10").Value = 8
Range("K10").Value = 9
Range("M10").Value = 10
Range("P10,E13").Value = "藤原"
Range("R10").Value = "日数差"
Range("C11,C17").Value = "休み"
Range("P11,F13,M13").Value = "古賀"
Range("R11").Value = "週差"
Range("P12,G13").Value = "村田"
Range("R12").Value = "オフ(行)"
Range("H13,P13").Value = "岡田"
Range("J13,P14,J19").Value = "二宮"
Range("K13,P15").Value = "吉村"
Range("N13,P16").Value = "原"
Range("R13").Value = "オフ(列)"
Range("K14,P29,M33").Value = "岡"
Range("L14,A26,P30,I33").Value = "赤井"
Range("A16").Value = 11
Range("C16").Value = 12
Range("E16").Value = 13
Range("G16").Value = 14
Range("I16").Value = 15
Range("K16").Value = 16
Range("M16").Value = 17
Range("P17,A19").Value = "中川"
Range("P18,C19").Value = "太田"
Range("D19,P19").Value = "下田"
Range("E19,P20").Value = "手島"
Range("H19,P21").Value = "尾上"
Range("I19,P22").Value = "新田"
Range("L19,P23").Value = "川口"
Range("M19,P24").Value = "鈴木"
Range("A22").Value = 18
Range("C22").Value = 19
Range("E22").Value = 20
Range("G22").Value = 21
Range("I22").Value = 22
Range("K22").Value = 23
Range("M22").Value = 24
Range("A28").Value = 25
Range("C28").Value = 26
Range("E28").Value = 27
Range("I28").Value = 29
Range("K28").Value = 30
Range("M28").Value = 31
Rem 数式セルをまとめて処理
Range("Q2:Q30").FormulaR1C1Local = "=TEXT(IF(RC[-1]="""","""",SUMPRODUCT((R5C[-16]:R39C[-3]=RC[-1])*{1,1,1,1,1,1,1,1,1,1,2,2,2,2}))+COUNTIF(R3C19:R7C25,RC[-1]),""[>4}]""""Over4"""";[=4]""""Just4"""";0"")"
Range("S3:Y7").FormulaR1C1Local = "=IF(R2C="""","""",IF(WEEKDAY(R2C-1)<6,OFFSET(R10C1,R12C+ROW(R[-2]C[-18]),R13C)&"""",""""))"
Range("S9").FormulaR1C1Local = "=(R[-8]C[-18]&R[-8]C[-17]&LEFT(R[-8]C[-16])&R[-8]C[-15]&""1日"")-WEEKDAY((R[-8]C[-18]&R[-8]C[-17]&LEFT(R[-8]C[-16])&R[-8]C[-15]&""1日""),2)+8"
Range("S10:Y10").FormulaR1C1Local = "=IF(R2C="""","""",R[-8]C-R9C19)"
Range("S11:Y11").FormulaR1C1Local = "=IF(R2C="""","""",IF(R[-1]C<0,-1,FLOOR(R[-1]C/7,1)))"
Range("S12:Y12").FormulaR1C1Local = "=IF(R2C="""","""",R[-1]C*6)"
Range("S13:Y13").FormulaR1C1Local = "=IF(R2C="""","""",MOD(R[-3]C,7)*2)"
Rem 標準外書式セルをまとめて処理
Range("S2:Y2,U19").NumberFormatLocal = "m""月""d""日"""
Range("S9").NumberFormatLocal = "yyyy/m/d(aaa);@"
End Sub
（半平太） 2016/06/24(金) 22:49
すごい！！すごすぎます〜！
できました！理想以上の表が！！
本当に本当にありがとうございましたm(__)m
感謝です(ToT)
（モモ） 2016/06/25(土) 11:16
すみません。 2列目にある名前の分を失念しました。
関係する部分を以下のマクロで訂正してください。 ※マクロの使い方は、前レスと同じです。
Private Sub onlyOnce()
Range("$Q$2:$Y$30").Clear
Rem 生データのセルをまとめて処理
Range("R2").Value = "祭日→"
Range("S2").Value = 42569
Range("T2").Value = 42580
Range("R15").Value = "第二週月曜"
Range("R16").Value = "日数差"
Range("R17").Value = "週差"
Range("R18").Value = "オフ(行)"
Range("R19").Value = "オフ(列)"
Rem 数式セルをまとめて処理
Range("Q2:Q30").FormulaR1C1Local = "=TEXT(IF(RC[-1]="""","""",SUMPRODUCT((R5C[-16]:R39C[-3]=RC[-1])*{1,1,1,1,1,1,1,1,1,1,2,2,2,2}))+COUNTIF(R3C19:R13C25,RC[-1]),""[>4}]""""Over4"""";[=4]""""Just4"""";0"")"
Range("S3:Y7").FormulaR1C1Local = "=IF(R2C="""","""",IF(WEEKDAY(R2C-1)<6,OFFSET(R10C1,R18C+ROW(R[-2]C[-18]),R19C)&"""",""""))"
Range("S9:Y13").FormulaR1C1Local = "=IF(R2C="""","""",IF(WEEKDAY(R2C-1)<6,OFFSET(R10C1,R18C+ROW(R[-8]C[-18]),R19C+1)&"""",""""))"
Range("S15").FormulaR1C1Local = "=(R[-14]C[-18]&R[-14]C[-17]&LEFT(R[-14]C[-16])&R[-14]C[-15]&""1日"")-WEEKDAY((R[-14]C[-18]&R[-14]C[-17]&LEFT(R[-14]C[-16])&R[-14]C[-15]&""1日""),2)+8"
Range("S16:Y16").FormulaR1C1Local = "=IF(R2C="""","""",R[-14]C-R15C19)"
Range("S17:Y17").FormulaR1C1Local = "=IF(R2C="""","""",IF(R[-1]C<0,-1,FLOOR(R[-1]C/7,1)))"
Range("S18:Y18").FormulaR1C1Local = "=IF(R2C="""","""",R[-1]C*6)"
Range("S19:Y19").FormulaR1C1Local = "=IF(R2C="""","""",MOD(R[-3]C,7)*2)"
Rem 標準外書式セルをまとめて処理
Range("S2:Y2").NumberFormatLocal = "m""月""d""日"""
Range("S15").NumberFormatLocal = "yyyy/m/d(aaa);@"
End Sub
（半平太） 2016/06/25(土) 14:22
訂正の方法ですが、最初に教えていただいたコードのどこかを置き換えるのでしょうか？？
「関係する部分」というのが、自分でも分かっておりません( ;∀;)
（モモ） 2016/06/25(土) 14:51
＞訂正の方法ですが、最初に教えていただいたコードのどこかを置き換えるのでしょうか？？
いえ、コードをいじる必要はありません。
訂正用のマクロを実行すれば、自動的に変わります。
（半平太） 2016/06/25(土) 16:05
訂正できました！
何から何まで、ありがとうございましたm(__)m
感謝♪♪
（モモ） 2016/06/25(土) 17:05
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.