エクセル 週ごとの数値抽出

質問

月曜日から日曜日までの出荷数の合計を出すにはどうすればよいのでしょうか？

今のところ、セルR2C3の月の数値を変えると日付と曜日が出てくる所まで出来ました。

セルR6C2の部分の関数は「=R2C3+1」、セルR6C3の関数は「=TEXT(RC[-1],"aaa")」となっています。

来月からも使いたいのでセルR2C3の数値を変えるだけで反映されるよう表示させたいです。

よろしくお願いします。

回答No.6

tom04 · ベストアンサー

こんばんは！
横からお邪魔します。

第1週　→　第1日曜が出現するまでの合計
という解釈です。

画像の配置通りだとして、R5C7に
=SUMIF(C[-5],"<="&R2C[-4]-1-WEEKDAY(R2C[-4]-7,3)+7*ROW(R[-4]C[-6]),C[-2])-SUMIF(C[-5],"<="&R2C[-4]-1-WEEKDAY(R2C[-4],3)+7*(ROW(R[-4]C[-6])-1),C[-2])
という数式を入れオートフィルで下へコピーしてみてください。

※　考え方　※
数式を入れた行（1行目）は
表内の第1日曜までの合計　から　その先週（日曜）までの合計をマイナスした数値。
これを下へコピーしますので、
2行目は
第2日曜までの合計　から　第1日曜までの合計をマイナスした数値。
3行目は
第3日曜までの合計　から第2日曜までの合計をマイナスした数値。
という繰り返しになります。

※　数式がR1C1形式なので数式だけをみると理解するのに難しいかもしれません。m(_ _)m

お礼

ありがとうございます！！！
非常に助かりました。

回答No.7

bunjii

>月曜日から日曜日までの出荷数の合計を出すにはどうすればよいのでしょうか？
第1週目は7日間にならないことを考えて特別な扱いをしなければなりません。

第1週目=SUM(OFFSET(R4C[-5],1,3):OFFSET(R4C[-5],MATCH("日",R5C[-4]:R35C[-4],0),3))

第2週目=SUM(OFFSET(R4C[-5],MATCH("月",R6C[-4]:R35C[-4],0)+(ROWS(R6C[-6]:RC[-6])-1)*7+1,3):OFFSET(R4C[-5],MATCH("月",R6C[-4]:R35C[-4],0)+ROWS(R6C[-6]:RC[-6])*7,3))

第3週目以降は第2週目をオートフィルで下へコピーすれば集計できます。

MATCH関数で最初の月曜日の行を検出して、そこから7行毎に区切ってSUM関数で集計しています。
但し、40行まで計算範囲に入りますので別の用途に使わないでください。

回答No.5

kagakusuki

>月曜日から日曜日までの出荷数の合計を出す

との事ですが、1日目が月曜日ではない月や、月末が日曜日で終わっている訳ではない月の場合には、「月曜日から始まって日曜日で終わっている」という訳ではない週も出て来きます。
　例えば、2014年の1月の場合、1日(水)～5日(日)は月曜日から始まっている週ではありませんし、27日(月)～31日(金)は日曜日で終わってはおりません。
　その様な端数の様な日の出荷数に関しては集計しなくとも良いという条件なのでしょうか？
　それとも、R5C7セルの第1週目の所には1日(水)～7日(火)の期間における出荷数の合計を表示し、
R6C7セルの第2週目の所には8日(水)～14日(火)の期間における出荷数の合計を表示し、
R7C7セルの第3週目の所には15日(水)～21日(火)の期間における出荷数の合計を表示し、
R8C7セルの第4週目の所には22日(水)～28日(火)の期間における出荷数の合計を表示し、
R9C7セルの第5週目の所には29日(水)～31日(火)の期間における出荷数の合計を表示し、
R10C7セルの第6週目の所には何も表示しない
という事なのでしょうか？
　或いは、また別の条件で集計したいという事なのでしょうか？

　取り敢えずの話として、端数の様な日の出荷数は集計しない場合の方法を回答致しますので、もし、この条件とは異なる条件で集計を行いたいという事でしたら、補足欄等を使用して、どの様な集計のやり方をしたいのかという事を御教え願います。

　まず、R5C7セルに次の関数を入力して下さい。

=IF(OR(ISERROR(1/DAY(R2C3)),ROWS(R5:R)>COUNTIF(R5C3:R35C3,"日")-(R5C3<>"月")),"",SUM(OFFSET(INDEX(R5C5:R35C5,MATCH("月",R5C3:R35C3,0)),(ROWS(R5:R)-1)*7,,7)))

　次に、R5C7セルをコピーして、R6C7～R8C7のセル範囲に貼り付けて下さい。（この条件の下では、第5週目は存在しません）

　以上です。

回答No.4

NukoTarou

すみません、回答２です。

第○週の右横に合計を出すには、ということですね。
質問本文をよく読まずに申し訳ありません。

８列目に「第○週」という作業列をつくらせて下さい。

(1)　R5C8に次の数式を入力する。
　　　=INT((WEEKDAY($R5$C2,3)+DAY(R5C2)-1)/7)+1

(2)　R5C8の数式をドラッグして、R35C8までコピー。

これで、８列目に、それぞれの日にちが、月の中の第○週であるか表示されます。
あとは、これをSUMIF関数で集計します。

回答No.3

mshr1962

第1週=SUM(OFFSET(R5C2,0,3,MAX(7-WEEKDAY(R5C2,2),0)+1,1))
第2週=SUM(OFFSET(R5C2,8-WEEKDAY(R5C2,2),3,7,1))
第3週=SUM(OFFSET(R5C2,15-WEEKDAY(R5C2,2),3,7,1))
第4週=SUM(OFFSET(R5C2,22-WEEKDAY(R5C2,2),3,7,1))
第5週=IF(DAY(DATE(YEAR(R5C2),MONTH(R5C2)+1,0))-MAX(7-WEEKDAY(R5C2,2),0)>22,SUM(OFFSET(R5C2,29-WEEKDAY(R5C2,2),3,MIN(DAY(DATE(YEAR(R5C2),MONTH(R5C2)+1,0))-29+WEEKDAY(R5C2,2),7),1)),"-")
第6週=IF(DAY(DATE(YEAR(R5C2),MONTH(R5C2)+1,0))-MAX(7-WEEKDAY(R5C2,2),0)>29,SUM(OFFSET(R5C2,36-WEEKDAY(R5C2,2),3,MIN(DAY(DATE(YEAR(R5C2),MONTH(R5C2)+1,0))-29+WEEKDAY(R5C2,2),7),1)),"-")

回答No.2

NukoTarou

タイトルが「週ごとの数値の抽出」となっているのですが、質問本文では、単に「年月を指定するとその下に各日付とその曜日を表示したい」という内容に読めるのですが、そう言うことでいいでしょうか。

RCタイプでのセル番地表示になれていないのでミスがあった申し訳ないです。

(1)R2C3には「2013/12/1」の様に、作成したい月報の月の初日（日付型データ）を入力することにします。
　（表示形式を「yyyy"年"mm"月"」とするなどで、「2013年12月」と表示されます。）

(2)R5C2に「=R2C3」、R5C3には「=R5C2」の数式を入力します。

(3)セルの表示形式で、
　　　R5C2は、「ユーザー定義」を選択して d とすると、
　　　　　　　その日付型データが持っている日付けのみが表示されます。
　　　　　　　（二桁表示にしたければ dd とします。）

　　　R5C3は、「ユーザー定義」を選択して aaa とすると、
　　　　　　　その日付型データが持っている曜日のみが表示されます。

(4)R6C2のセルに、次の数式を入力します。
　　=IF(MONTH(R5C2+1)=MONTH($R2$C3),R5C2+1,"翌月分へ")

　R6C3のセルに、次の数式を入力します。
　　=R6C2

　この時点では、どちらのセルも「数値」が表示されていて「？」となると思いますが、次の(5)へ。

(5)　R6C2とR6C3の表示形式を、それぞれ、R5C2とR5C3と同じにするため、
　　「R5C2とR5C3」をドラッグして選択し、「コピー」
　　　　　　　↓
　　「R6C2」をクリックし、「形式を選択して貼り付け」の中の「書式」を選択して貼り付け。

　　これで「２」と「月」が表示されたと思います。

(6)　R6C2とR6C3をドラッグして選択。
　　オートフィルを使って、下に31日分まで（R35の列まで）コピーします。

以上のような感じでしょうか。

２月の月末とか、若干変な表示になりますが、勘弁して下さい。

回答No.1

keithin

Ｒ５Ｃ６以下には数字の１から６を記入し、セルの書式設定の表示形式のユーザー定義で　第0週　と設定しておく
Ｒ５Ｃ７には
=SUMIF(C2,"<="&R2C3-1-WEEKDAY(R2C3-7,3)+RC[-1]*7,C5)-SUM(R4C7:R[-1]C)
として第6週までコピー貼り付けておく。