エクセル関数で、行を挿入した時にADDRESS関数の行番号を変える方法を教えてください

質問

エクセルで収入と支出と利益を計算する単純なシートで、数年に渡る月次のデータ（sheet2)から、sheet1で指定した特定の期間の3ヶ月の収入や支出を検索として合計をする関数を作成しました（sheet2の右の方の罫線の箇所）。
SUM関数とINDIRECT関数、ADDRESS関数、MATCH関数を組み合わせているのですが、この状態で例えば収入(2)の後ろに収入(3)を挿入しようとすると、ADDRESS関数の行番号（ADDRESS（7,MATCH～)の数字の7のところ）
が自動的に8に変わらないので、都度関数を修正しなければなりません。行を挿入したり削除したりしたら、ADDRESS関数の行番号も増えたり減ったりするようにできる方法はないでしょうか。
もしくは、そもそももっと簡単で使いやすい関数はないでしょうか。
教えていただけるととても助かります。

回答No.4

don9don9 · ベストアンサー

No.3ですが画像がちょっと見づらいですね。

表の作りはB列からJ列の9列に2009年1月～2009年9月のデータ
E～G列（4～6月）は非表示
L列、M列、N列でそれぞれ1～3月、4～6月、7～9月の集計

L3の式は
=SUMPRODUCT(($B$1:$J$1>=L$1)*($B$2:$J$2<=L$2)*($B3:$J3))
です（以下説明）

自分自身のセルと同じ行のB列からJ列のうち
B1:J1が自分自身のセルの1行目以上、かつ
B2:J2が自分自身のセルの2行目以下、であるセルの値を合計

絶対参照と相対参照が混ざっていますので$をつける場所を
間違えないように注意してください。
その代わり縦でも横でもコピー＆ペースト可能です。
（図中黄色の範囲は全てL3の式を複写したものです）

お礼

本当に丁寧にありがとうございます！
さっそく試してみますが、上手くいきそうな気がします。
結果はまたご報告させて頂きます。

補足

すべて上手くいきました。本当にありがとうございました！！

回答No.3

don9don9

縦軸に科目があって、横軸に月毎のデータがあって
ヘッダ行は1行目が月初日、2行目が月末日
実際のデータは3行目以降に入っている
集計列のヘッダは1行目が期首日付、2行目が期末日付
同じ行のデータのみを集計する
ということですか。

であればSUMPRODUCTを試してみてください。
画像をつけます。

回答No.2

don9don9

すみません。再度補足要求です。

>2009/1/1から3/31の3ヶ月間の、5行目の収入(1)の集計の場合

「2009/1/1から3/31の3ヶ月間の集計」であれば、
Sheet2から該当する期間のレコードを探して
それを集計、ということになるわけですから
行自体は指定した条件によって変動するはずです。
「5行目」と行を固定する意味がわかりません。

それと収入(1)の(1)とは何でしょうか？
収入の列が複数あるということでしょうか？

折角補足いただいたのですが、収支表の構成がイメージできません。
もう少し具体的に収支表の構成を

A列が何の項目でどんなデータが入っている（具体例：○○）
B列が何の項目でどんなデータが入っている（具体例：○○）
C列は…
で、集計したい列はどの列

のように記述していただけないでしょうか。

補足

説明不足でお手数をおかけします…
5行目としたのはあくまで例であって、私の作成している表では、収入と支出で全部で100項目（100行）はあります。しかも、案件によって、項目（行）が追加されたり削除されたりします。
もう少し具体的に申し上げますと、
A列には収入と支出の科目名（例：賃料、水光熱費等）があり、B列以降は、各月の各科目の金額が入力されています。B列の1行目には、Sheet1から引用した2008/1/1が入力されており、C列1行目には、次で説明するB列2行目の日付+1として、2008/2/1となり、D列は2008/3/1～と5年間続きます。2行目には1行目の日付にEOMONTH関数を使って、B列には2008/1/31、C列には2008/2/28～と、1行目の日付の月末の日付が5年間入力されています。
そして、5年間の収支表の右側には、Sheet1で指定した年度の第1期、2期、3期、4期の3ヶ月毎のデータを集計する表があります。
例えば、BA列の1行目にはsheet1から引用した2009/1/1、2行目には同じくsheet1から引用した2009/3/31とあり、3行目以下には、左側の各月の収支表から集計した2009年1月から3月の第1期の3ヶ月（3つのセルの）合計の数字が反映されます。BB列の1行目は4/1、2行目は6/30で、3行目以下には同じく第2期の3ヶ月合計の各収入・支出の数字が反映されます。毎月の収支表と集計する表は、行が平行になっています（2009年1月の賃料の数字が5行目なら、2009年1月～3月の賃料の合計を集計するセルも、5行目です）。これでわかりますでしょうか。
EXCELのシートが貼り付けられれば一目瞭然なのですが…
科目を増やしたり減らしたりするために、行を挿入したり削除したりもするのですが、そうするとADDRESS関数の行数が変わらないので、集計表で集計する数字が変わってしまうのは質問に記載している通りです。
よくありそうな集計シートですので、もっと単純な方法があるのではないかと思っています。よろしくお願いいたします。

回答No.1

don9don9

・Sheet1での期間指定方法
年、月は別々のセルなのか？
○○年□月～○○年×月のように「期間」で指定するのか？
○○年□月のように単月で指定してそこから３ヶ月（前？後？）を計算するのか？
など。

・Sheet2のデータの項目とできれば具体的なデータの例
例えば2009年9月が「200909」という数値で入っているのか？
「2009年9月」という文字列で入っているのか？
「2009」と「9」という数値で別々のセルに入っているのか？
など。

・今、SUM関数、INDIRECT関数、ADDRESS関数、MATCH関数をどう組み合わせているのか？

・使っているExcelのバージョン（2007かそれ以前か）

を補足して下さい。

何となく質問を読んだ限りでは、SUMIFやSUMPRODUCTを
使ったほうがよさそうな気がしたのですが。

補足

早速のご回答ありがとうございます。エクセルファイルを添付しようとしたのですが、できないようで、時間切れになってしまいました。中途半端な質問になってしまい申し訳ございません。
Excelのバージョンは2003です。
sheet１には、入力日の始期（例2008/1/1）と、集計したい年度の期（例　第1期2009/1/1、第1期終期2009/3/31、第2期始期2009/4/1～第4期終期2009/12/31）の日付をセルに手入力しています。
sheet2には、sheet1を受けて、EOMONTH関数を使って、始期から5年分の毎月の収支表が作成されます。収入と支出は手入力します。そして、収支表と行を揃える形で、収支表の右側に、集計したい期（3ヶ月毎）のデータを合計して、第1期から第4期までのデータが表示されるといったものです。
そこで使っている関数は以下のようなものです(2009/1/1から3/31の3ヶ月間の、5行目の収入(1)の集計の場合）。
SUM(INDIRECT(ADDRESS(5,MATCH(検査値:2009/1/1,検査範囲：収支表の日付))):INDIRECT(ADDRESS(5,MATCH(検査値:2009/3/31,検査範囲：収支表の日付)))
要は、sheet1で集計したい日付を指定すれば、5年間のデータから該当の期日のデータの合計を自動的に集計するようにするものです。
SUM関数だけでは、集計したい期が変わる度に、都度合計するセル範囲を変更しなければならないので。
よろしくお願いいたします。