[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日付を6〜8日ごとに分ける』（クロネコ）
煮詰まりました。お助けください。 下記のような一覧表があります。 A B 11 2015/1/21 12 2015/1/22 13 2015/1/23 14 2015/1/24 15 2015/1/25 ： ：
一年分の日付が入っています。
B列には数値が入ります。
これを以下の条件である程度の範囲に分けて集計することになりました。
○21日はじめ20日締め
○1範囲は6日から8日間。つまり21日から20日の間を6日から8日間を一塊として分ける。
○一応基本は月曜初めの日曜終わりだが、6日に満たない場合は何曜から始まっても問題ない。
N列を作業列としてためしに以下の式を入れてみました
N11に4
N12に
=IF(COUNTIF($N$10:N11,N11)<6,N11,WEEKNUM(A12,2))
こうすると最初はいいのですが、20日締めの部分が余ってしまいます。
以下の理想の列のようにしたいのです。
A N 理想 11 1/21 4 4 12 1/22 4 4 13 1/23 4 4 14 1/24 4 4 15 1/25 4 4 16 1/26 4 4 17 1/27 5 5 18 1/28 5 5 19 1/29 5 5 20 1/30 5 5 21 1/31 5 5 22 2/1 5 5 23 2/2 6 6 24 2/3 6 6 25 2/4 6 6 26 2/5 6 6 27 2/6 6 6 28 2/7 6 6 29 2/8 6 6 30 2/9 7 7 31 2/10 7 7 32 2/11 7 7 33 2/12 7 7 34 2/13 7 7 35 2/14 7 7 36 2/15 7 8 37 2/16 8 8 38 2/17 8 8 39 2/18 8 8 40 2/19 8 8 41 2/20 8 8
こうするにはどうしたらいいのでしょうか？
このあとこの番号を目印に集計しようと思っております。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
02/21(土) 02/22(日) 02/23(月) 02/24(火) 02/25(水) 02/26(木) 02/27(金) 02/28(土) 03/01(日) 03/02(月) 03/03(火) 03/04(水) 03/05(木) 03/06(金) 03/07(土) 03/08(日) 03/09(月) 03/10(火) 03/11(水) 03/12(木) 03/13(金) 03/14(土) 03/15(日) 03/16(月) 03/17(火) 03/18(水) 03/19(木) 03/20(金)
の場合はどうなればいいのか？
（ねむねむ） 2015/03/25(水) 13:25
ねむねむさん、ありがとうございます。 以下のようになります。
02/21(土) 9 02/22(日) 9 02/23(月) 9 02/24(火) 9 02/25(水) 9 02/26(木) 9 02/27(金) 9 02/28(土) 9 03/01(日) 10 03/02(月) 10 03/03(火) 10 03/04(水) 10 03/05(木) 10 03/06(金) 10 03/07(土) 10 03/08(日) 10 03/09(月) 11 03/10(火) 11 03/11(水) 11 03/12(木) 11 03/13(金) 11 03/14(土) 11 03/15(日) 12 03/16(月) 12 03/17(火) 12 03/18(水) 12 03/19(木) 12 03/20(金) 12
自分の式は8にする分が抜けていますね。ここも考えないとだめですね。ご指摘ありがとうございます。 ここの部分も今から考えますが、もしもよいアイディアがあればご指導ください。 （クロネコ） 2015/03/25(水) 13:37
3つの条件のうち２つは良いと思いますが、 >○一応基本は月曜初めの日曜終わりだが、6日に満たない場合は何曜から始まっても問題ない。
が話を複雑にしていると思います。 どうせほとんどの週は月曜始まりにならないのですから、 単純に7日区切りにして、最後の 1週(29日の月)〜3週(31日の月) を8日区切りにする、 などシンプルなルールにしてはどうでしょうか。 （Mook） 2015/03/25(水) 14:35
Mookさんありがとうございます。
理解が悪くてすみません、以下の場合はMookさんのおっしゃるルールですとどうなるのでしょうか？ 1/21 4 1/22 4 1/23 4 1/24 4 1/25 4 1/26 4 1/27 4 1/28 5 1/29 5 1/30 5 1/31 5 2/1 5 2/2 5 2/3 5 2/4 6 2/5 6 2/6 6 2/7 6 2/8 6 2/9 6 2/10 6 2/11 7 2/12 7 2/13 7 2/14 7 2/15 7 2/16 7 2/17 7 2/18 8 2/19 8 2/20 8 これですと最後が3日あまってしまいます。 7の週にたすと10日になってしまい、要件が満たされません。 ここがクリアできればおっしゃるルールでも構わないのですが… （クロネコ） 2015/03/25(水) 15:16
21日から翌月20日までの日数によって割り振る日数を固定してはどうか？
1、3、5、7、8、10、12の月始まりの場合は31日 4、6、9、11の月始まりの場合は30日 2月始まりの場合は28日か29日。
それぞれで割り振り方を決めてしまってはどうだろうか？ （ねむねむ） 2015/03/25(水) 15:37
ねむねむさんのかかれたコメントと同じことですが、 1月からの場合（31日の月）は、下記のようになるということです（曜日は見ない）。 1/21〜1/27 7日 1/28〜2/4 8日 2/5 〜2/12 8日 2/13〜2/20 8日 （Mook） 2015/03/25(水) 15:44
31:7+8+8+8 30:7+7+8+8 29:7+7+7+8 28:7+7+7+7
これだと6日がないか。 （ねむねむ） 2015/03/25(水) 15:48
ねむねむさん、Mookさん、ありがとうございます。 なるほど、そういうことなんですね。 6日はなくても問題はありません（6〜8の間でなければならない、なので7と8だけでもいいです）ので、その方法がいいかもしれません。 その方法だとどうやったらいいか、ご指導願えませんでしょうか？ （クロネコ） 2015/03/25(水) 16:05
数式でやろうとしたのですが（出来るはずですが）、頭がパンクしたので マクロ併用案です。
標準モジュールに下のコードを置き、 B1 に 1 B2 に =IF(myWeekday(A2)=1,B1+1,B1) を置いて、B3以降にコピー。でどうでしょうか。
Function myWeekday(d As Range)
myWeekday = -1
If d.CountLarge <> 1 Then Exit Function
If d.Value = "" Then Exit Function
If IsDate(d.Value) = False Then Exit Function
Dim m As Long
m = IIf(Day(d.Value) >= 21, Month(d.Value), Month(d.Value) - 1)
Dim md As Long
md = d.Value - DateSerial(Year(d.Value), m, 20)
Select Case m
Case 0, 1, 3, 5, 7, 8, 10, 12
'// 31日
Select Case True
Case md <= 7
myWeekday = md
Case md <= 15
myWeekday = md - 7
Case md <= 23
myWeekday = md - 15
Case Else
myWeekday = md - 23
End Select
Case 4, 6, 9, 11
'// 30日
Select Case True
Case md <= 7
myWeekday = md
Case md <= 14
myWeekday = md - 7
Case md <= 22
myWeekday = md - 14
Case Else
myWeekday = md - 22
End Select
Case 2
'// 28日・29日
Select Case True
Case md <= 7
myWeekday = md
Case md <= 14
myWeekday = md - 7
Case md <= 21
myWeekday = md - 14
Case Else
myWeekday = md - 21
End Select
End Select
End Function
マクロの使用が不可である場合は、関数の回答をお待ちください。 （Mook） 2015/03/25(水) 21:00
Mookさん、ありがとうございます。 頭がパンクするほど考えていただいて本当にありがたいです。 私もすでにパンク状態です。 頂いたマクロを実行させてみました。 最初自分の表がA11から始まっていることを忘れていましたが、無事に数値が出てきました！ ありがとうございます！ きれいに数値が並んでうれしいです！ 本当はマクロはダメ（なんだか会社の規定で禁止されてるのです）なんですが、 このままいい数式が思い浮かばなかったときは、上司には「手で入力しました」と言って自分のところで実行させてしまおうかなと思います。 そして来年までにはこのマクロの意味を覚えたいと思います。 これでだいぶ気が楽になりました。 本当にありがとうございました。 一応締切間近まで関数頑張ってみます。 もしも関数にてご指導いただける方がいらっしゃいましたら、引き続きよろしくお願いいたします。
（クロネコ） 2015/03/26(木) 09:31
かなり力技。 C列を作業列に使う。
C11セルに =MONTH(A11-20) と入力して下へフィルコピー。
B11セルに
=(C11-1)*4+MATCH(COUNTIF(C$11:C11,C11),CHOOSE(COUNTIF(C$11:C$376,C11)-27,{1,8,15,22},{1,8,15,22},{1,8,15,23},{1,8,16,24}),1)
と入力して下へフィルコピーでどうだろうか？
追記
B11セルの式は
=(C11-1)*4+MATCH(DAY(A11-20),CHOOSE(COUNTIF(C$11:C$376,C11)-27,{1,8,15,22},{1,8,15,22},{1,8,15,23},{1,8,16,24}),1)
でも。
（ねむねむ） 2015/03/26(木) 11:17
書いている間に回答付きましたけれど、せっかく考えたので記念参加で。
B1 =1
B2 =IF(ISERROR(MATCH(DAY(A2),CHOOSE(DAY(EOMONTH(A2-20,0))-27,{7,14,21,28},{6,13,21,28},{5,13,21,28},{5,13,21,28}),0)),B1,B1+1)
でB2をB3以降にコピーで。
（Mook） 2015/03/26(木) 11:53
ねむねむさん、ありがとうございます。 すごいです、できました！ どっちの式もできました。 MONTH(A11-20) とか DAY(A11-20) とか、これでこうなるのかとびっくりしました。 MONTH(A11-20)と同じ物を出そうとして =IF(IF(DAY(A11)<21,MONTH(A11)-1,MONTH(A11))=0,12,IF(DAY(A11)<21,MONTH(A11)-1,MONTH(A11))) とかこねくり回しているレベルでした… 本当にありがとうございました！ Mookさん、再びありがとうございます。 こちらもできました！ 最初EOMONTHがわからず、何が行われているのが4度くらい見直してしまいました。 最終日を示す関数が存在するんですね…すごいです。 私では知っていても思いつかない気がします。 重ね重ねありがとうございました！ 関数でなければならないというわがままをかなえてくださいまして、本当にありがとうございます。 これで書類が出来上がりそうです。 マクロも保存しておいて、毎年３つ同じ数字が並ぶか確認しようと思います。 ありがとうございました！ （クロネコ） 2015/03/26(木) 15:04
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.