Excelで月ごとの集計を間に入れたい

質問

1行目に日付があり、別シートの入力項目で入れられた年月の1日を開始日として
横に連続して日付が進んでいるシートがあります
やりたいことは、月ごとの集計なのですが
集計の列を各月の最終日の直後に入れないといけない事です
まとめて最後に持ってくるとか、別シートに集計表とするなら
ピボットテーブルも使えそうですが、今回の要望では使えません
しかも
何月を入れられ羽化わからないため、列が可変長となります
つまり
2月は28日か29日、その他の月は30日か31日で、
一番目指したいスタイルとしては・・・
2月28日　2月29日　集計　3月1日　3月2日　～～　3月31日　集計　4月1日　～～
ですが、日付が存在しなければスグに集計を持ってくると言うのは
不可能と考えますので、31列固定で考えて
2月28日　2月29日　空欄　空欄　集計　3月1日　3月2日　～～　3月31日　集計　4月1日　～
でも構いません
それにしても、計算の結果、月が変われば空欄にして、集計の直後から改めて翌月から
はじめるということは、どのような操作で可能となるでしょうか？
どだい、集計を間に挟む事自体無理がありますでしょうか？

回答No.6

dogs_cats · ベストアンサー

D1が1/1からで良いのでしょう・
D1に2016/1/1を手動入力又はセル参照による関数等で実うるう年である今年を1/1として下さい。

D2式
=IFERROR(IF(MONTH(D1)=MONTH(D1+1),D1+1,"集計"),C1+1)
右方向にNE1までオートフィル
集計が12作成されてる事を確認
月合計数式
１月
AI2式
=SUM(OFFSET(AH2,,,,-DAY(AH$1)))
下方へオートフィル
AI2~数式までをコピー
各月の集計の２行目にペーストする。

２月、３月修正
閏年の月末及び、3/1数式修正
2/29日のBL1式を下記に修正（コピペ）
=IFERROR(IF(MONTH(BK1)=MONTH(BK1+1),BK1+1,""),"")
BM1に集計を入力
3/1のBN1式
=IF(BL1="",BK1+1,BL1+1)
2月集計数式BM2式
=SUM(OFFSET(BL2,,,,-29))
下方へオートフィル

D1の年を変更で２月の29日表示も自動で行います。

添付画像は１日と月末間は非表示で非表示セルは空白で、１日と月末だけ数値を入力し集計欄で月集計させています。数式は上記の通りです。

お礼

いろいろな方法があるものですね
わざわざ画像まで添付くださり、ありがとうございました
すごく詳細に書いて頂きありがとうございました
しょうしょう難しく、ついて行けていない部分がありますが
今後の材料にさせて頂きたいと思います
今回は、日付の間に挟み込むはなくてもいいと言う事になったので
最後に集計を持ってきます
ありがとうございました

回答No.7

msMike

[No.2お礼]へのコメント、
》 これは「悪い例」としてあげたものです」
そんなこと書いてありましたっけ、質問文に?

「列が可変長」はマクロの鉄人にお願いするとして、マクロ音痴の私なら次のようにします。
１．m/dd に書式設定したセル D1 に 2015/2/1 を入力して、此れを右方に(セル
＿＿AH1 までの合計 31列)ズズーッとオートフィル。
＿＿m/dd に書式設定したセル AJ1 に 2015/3/1 を入力して、此れを右方に(セル
＿＿BN1 までの合計 31列)ズズーッとオートフィル。
＿＿以降の月も右側に延々と同様にネ。此処では列ＣＵ までとしておく。
＿＿つまり、３２日目毎に集計列を置く次第。
２．セル AI2 に次式を入力して、此れを下方にズズーッとオートフィル。
＿＿ =SUM(OFFSET(D2,,,,DAY(EOMONTH(D$1,0))))
３．範囲 AI2:AI6 を[コピー]して、セル BO2、CU2 に[貼り付け]
４．範囲 D1:CU6 を選択して、次の[条件付き書式]を設定
＿＿数式が→ =MOD(COLUMN(A1),32)<>DAY(D$1)
＿＿フォント色→ 白

回答No.5

kagakusuki

単に、

>2月28日　2月29日　集計　3月1日　3月2日　～～　3月31日　集計　4月1日　～～

とするだけでしたら可能なのですが、おそらく2月28日や4月1日といった日付が表示されている列の2行目以下のセルをデータの入力欄として使用した上で、「集計」と表示されている列の2行目以下のセルには関数が常に入力済みとなっている様にしたいという事なのではないかと思われます。(もし違っていた場合には御補足願います)
　2行目以下のセルにデータを手入力してしまいますと、そのセルに入力されていた関数は上書きされて消えてしまいますので、2行目以下のセルをデータの入力欄として使用する場合には、

>2月28日　2月29日　集計　3月1日　3月2日　～～　3月31日　集計　4月1日　～～

というパターンでは、マクロを使わない限り無理という事になります。
　或いは、通常の関数を使って行うのであれば、「仕入」や「外注」と言った全てのデータをも別シートに入力する様にした上で、添付画像に写っているシートは月毎の集計結果を表示する事だけに使用して、入力には使用しないと言った方法にするなどと言った方法にするのでもなければ無理という事になります。
　只、御質問文に書かれている情報だけでは、「仕入」や「外注」といった元データを別シートに入力して行く際に、別シートをどの様なレイアウトの表にした方が良いのかという事が今一つ不明である事や、「集計」と表示されている列の2行目以下のセルに表示させる集計値とは、単に「同じ行内のその月の数値の合計値」とするだけで良いのかどうかという事なども判りませんので、ここですぐに「全てのデータをも別シートに入力する様にした上で、添付画像に写っているシートは月毎の集計結果を表示するのみとする」という事を実現するために必要となる2行目以下のセルに入力する関数を提示する事は困難です。

　ですから、取り敢えず

>31列固定で考えて
>2月28日　2月29日　空欄　空欄　集計　3月1日　3月2日　～～　3月31日　集計　4月1日　～

とする場合の方法に関してのみ回答する事に致します。
　尚、

>別シートの入力項目で入れられた年月の1日を開始日として

としか説明が無く、日付けを表示させるのはどのセルから始めれば良いのかという事も不明なら、別シートとは何というシート名のシートなのかも不明、別シートのどこのセルに、どの様な形式で年月が入力されているのかという事も不明ですので、取り敢えず仮の話として、Sheet2のA1セルにのみ例えば2016/1/30などといった基準となる日付データが入力されていて、添付画像に写っているシートのAJ1セルから右に向かって日付を表示させるものとした場合に関して回答させて頂きます。

　まず、添付画像に写っているシートのAJ1セルに次の関数を入力して下さい。

=IF(AND(Sheet2!$A$1>=1,Sheet2!$A$1<="9999/12/31"+0),TEXT(Sheet2!$A$1,"yyyy/m/""1""")+0,"")

　次に、添付画像に写っているシートのAK1セルに次の関数を入力して下さい。

=IF(MOD(COLUMNS($AJ1:AK1),32),IF(ISNUMBER($AJ$1),IFERROR((TEXT(EDATE($AJ1,INT(COLUMNS($AJ1:AK1)/32)),"yyyy/m/")&MOD(COLUMNS($AJ1:AK1),32))+0,"-"),""),"集計")

　次に、添付画像に写っているシートのAJ1セルとAK1セルの書式設定の表示形式を[日付]に設定して下さい。
　そして、添付画像に写っているシートのAK1セルをコピーして、同じ行のAK1セルよりも右手にあるセル範囲に貼り付けて下さい。

　尚、もし別シートに日付が入力されているセルが1個だけではなく、例えば別シートのA列に日付データが入力されているセルが複数あり、それらの日付の中で最も古い日付けの月の1日の日付とそれに続く日付を、添付画像に写っているシートのAJ1セルから表示し始める、という様な場合には、添付画像に写っているシートのAJ1セルを次の様なものにして下さい。(AK1セルに入力する関数は同じもののままで構いません)

=IF(COUNTIFS(Sheet2!$A:$A,">=1",Sheet2!$A:$A,"<=9999/12/31"),TEXT(SMALL(Sheet2!$A:$A,COUNTIF(Sheet2!$A:$A,"<1")+1),"yyyy/m/""1""")+0,"")

お礼

すごく詳細に書いて頂きありがとうございました
しょうしょう難しく、ついて行けていない部分がありますが
今後の材料にさせて頂きたいと思います
今回は、日付の間に挟み込むはなくてもいいと言う事になったので
最後に集計を持ってきます
ありがとうございました

回答No.4

keithin

直接のご相談としての集計と書かれたセル（＝先頭行が「集計」になっている）を月替わり位置に挟んで可変にすることは簡単ですが、後の仕事を考えると集計列の集計式（２行目以下）をイチイチ動かさなきゃならないってことで、手間が増えるだけです。
それよりは集計列を３２列ごとの固定位置として、その列に当月分の集計関数を最初から配置しておいた方が実際的です。

＞計算の結果、月が変われば空欄にして、集計の直後から改めて翌月からはじめる

全ての日付セルにイチイチ計算させたいご相談が実際とっても多いのですが、集計の続きの１日２日３日…セルは、どう考えても１日２日３日であり空白になるハズがありません。空白になる可能性のある２９日以降のセルについてだけ、空白の計算をすれば十分ということです。

たとえばＡＤ１：仮にＢ列が１日として29日
=IF(MONTH(B1+28)=MONTH(B1),B1+28,"")

回答No.3

bunjii

>別シートの入力項目で入れられた年月の1日を開始日として
とは例えばSheet2のA1へ2016年1月のように入力され、実質の値が2016/1/1のシリアル値（42370）を開始年月日と考えて良いのでしょうか？

>どだい、集計を間に挟む事自体無理がありますでしょうか？
1行目の日付と集計と言う文字列を配置するだけでしたら次のような数式を使えば可能です。
D1=IF(NOT(ISNUMBER(C1)),MAX(B1+1,Sheet2!$A$1,-1),IF(DAY(C1+1)=1,"集計",C1+1))
しかし、集計列の2行目以降には月の合計を求める数式を設定する必要があるように思います。
従って、実際に使えるテーブルはVBA等で処理しなければ目的に合わないでしょう。
VBA等のマクロについては不得手なので他の回答者のご意見を参考にしてください。

回答No.2

msMike

「集計の列を各月の最終日の直後に入れないといけない」と宣わっているにも拘わらず、添付図では「12月1日」と「12月2日」の間に「集計の列」があるの？
「アレは単なるサンプルでぇ～」なんて仰らないでぇ～！

お礼

参考の画像が紛らわしくて、申しわけないです
これは「悪い例」としてあげたものです
３１列固定でやると、こんな風になってしまうし
うまいこと月替わり（最終日の次の列）に週系列を持ってくる方法
がどうしてもわからない状況です
３１列固定でも構いません
それでも、30,29,28日の月では、この悪い例のようになるので
回避する方法があればご教授願いたいです

回答No.1

aokii

ピボットテーブルを使って、ピボットテーブルの結果を引用してみてください。

お礼

集計はできても、連続する日付の間にうまく挿入する方法が
わからず、ピボットは集計には役立ちますが
月替わりの境目に集計を持ってくる操作は無理でしょう？