Excelで月ごとの集計を間に挟む方法

質問

• Excelで月ごとの集計を行いたい場合、日付が連続して進んでいるシートで列を変動させる必要があります。具体的な方法としては、各月の最終日の直後に集計の列を入れることがあります。日付が存在しない場合は、31列の固定を考慮することもできます。
• 月ごとの集計を行うためには、ピボットテーブルを使用する方法もあります。ただし、今回の要望ではピボットテーブルを使用することができません。そのため、日付が連続して進んでいるシートで列を変動させる必要があります。
• 集計を間に挟む方法としては、まとめて最後に持ってくる方法や別シートに集計表を作成する方法があります。どのような操作で可能かは具体的な要件によりますが、Excelの関数やVBAなどを使用することで実現することができます。

回答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,"")

お礼

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