Excel2010 SUM関数の参照列を指定

質問

いつもお世話になっております。

文字で説明しにくく、分かりにくい表現になっておりすみません。。

Excel2010　で年度（4月～3月）の集計表があり、最右列へSUM関数が入っています。
様々な項目が行方向に複数行あり、全て最右列へはSUM関数が入っています。

この各月の数字は日々変化していき、今月中は今月中の数字だけが変化していくとは限らず、
1年間のうちどの月の数値が日々変化するかは分かりません。

毎月、前月までの合計を出しておきたい為、SUM関数が入っている列は、毎月月初に
参照列が4月～前月までになるように修正をしています。

毎月置換で数式内のアルファベットを変更したりしていますが、複数Sheetに渡り表が続いていたり、
M列の時はSUM関数の"M"が"SUN"になってしまったり。。うまくいなかいことがあります。

この為、参照列のアルファベットを同じファイル内のどこかのセルで指定して参照させることはできないかと思っています。

例えば、こんな感じの参照表を作成し、、
A　　B
　 A列　B列
1行 4月　E
2行 5月　F
3行 　6月　G
4行 　7月　H
5行　 8月　I
6行 9月　J
7行 10月　M
8行　11月　N

20行　S20へSUM関数

9月選択→10月選択に変更すると、SUM関数内の＜=SUM(E20:J20)＞→＜=SUM(E20:M20)＞
に変更できるような。
SUM関数が入っているセルは全て「J」→「M」に一括で変更できるようにしたいです。

分かりにくくすみません。質問あれば補足します。

回答No.3

KURUMITO · ベストアンサー

ご質問の内容をよく理解していませんがシート２のA列とB列にお示しのような表が入力されているとします。
そこで例えばシート１で９月対応のSUM関数が=SUM(E20:J20)となっているとして、その関数を自動的に１０月対応にしたいとのことですね。
例えば９月対応の時にシート１のA１セルに９月としてSUM関数の式を=SUM(E２０:J２０)のように働かせ、１０月の時はA１セルに１０月としてSUM関数を自動的に対応する式に変換できるようにするには例えば次のような式にします。

=SUM(E20:INDIRECT(VLOOKUP(A1,Sheet2!A:B,2,FALSE)&20))

お礼

この方法でできました。
No.4の方も理解したかったのですが、取り急ぎ参照表を使うNo.3の方法で間に合いました。
大変助かりました。ありがとうございました！

回答No.5

kagakusuki

>この各月の数字は日々変化していき、

との事ですが、その数字を変化して行くのは、どの様に行われているのかが不明であるため、下手に元データが入力されている表の右端に合計を表示させようとしますと、「数字を毎月変化させるシステム」と干渉してしまい、数字の書き換えが行われなかったり、合計が表示されなかったりする恐れもないとは言えません。
　ですから、元データが毎月入力されて行く表に関しては直接いじり回す事は避けまして、別のシート上に元の表と同じ内容の表を自動的に表示させた上で、「その自動表示させた表の中で最終月の列」の右隣の列に、合計を表示させる様にされては如何でしょうか？

　まずは下の添付画像を御覧下さい。
　もし、質問者様が御利用になられているサイトでは画像が表示されていない場合には、以下のURLのページを御覧下さい。

【参考URL】
　QNo.7827789 Excel2010　SUM関数の参照列を指定 【OKWave】
　　http://okwave.jp/qa/q7827789.html

　今仮に、元データの表がSheet1に存在していて、そのA列には各種の項目名が縦に並んでいて、2行目には各種の項目名が横に並んでいて、E2～P2のセル範囲には4月～3月等の月の名が並べられているものとします。
　そして、「最終月の列よりも右側にある列を削除した上で最終月の列の右隣に合計欄を追加した表」をSheet2に表示させるものとします。

【方法その1】
　まず、Sheet2のA2セルに次の数式を入力して下さい。

=IF(INDEX($A:$B,ROE()-ROW($A$1)+ROW(Sheet1!$A$1),COLUMN()-COLUMN($A$1)+1)="","",INDEX($A:$B,ROE()-ROW($A$1)+ROW(Sheet1!$A$1),COLUMN()-COLUMN($A$1)+1))

　次に、Sheet2のA2セルをコピーして、Sheet2のA2～D3の範囲に貼り付けて下さい。
　次に、Sheet2のD1セルに「年」、Sheet2のF1セルに「月度」と入力して下さい。
　次に、Sheet2のE2セルに次の数式を入力して下さい。

=IF(ISNUMBER((1901&"/"&$E$1&"/1")+0),IF(COUNTIF($A$2:A$2,$E$1&"月")=0,TEXT(Sheet1!E$2,"m月"),IF(A$2=$E$1&"月","合計","")),"")

　次に、Sheet2のE3セルに次の数式を入力して下さい。

=IF(E$2="","",IF(E$2="合計",IF(COUNT($A3:A3),SUM($A3:A3),""),IF(ISERROR(1/(INDEX(Sheet1!$E:$P,MATCH($A3,Sheet1!$A:$A,0),COLUMNS($E:E))<>"")),"",INDEX(Sheet1!$E:$P,MATCH($A3,Sheet1!$A:$A,0),COLUMNS($E:E)))))

　次に、Sheet2のE2～E3の範囲をコピーして、Sheet2のF3～Q3の範囲に貼り付けて下さい。
　次に、Sheet2の3行目全体（A3～Q3のセル範囲でも可）をコピーして、4行目以下に貼り付けて下さい。

　最後に、Sheet2のE1セルに、合計値を求める最終月を示す整数値（添付画像の例では11）を入力しますと、Sheet2に、余分な月（添付画像の例では12月～3月）の列は除外された、該当する月（添付画像の例では11月）までのデータが表示され、最終月の列の右隣の列には4月～最終月の合計値が表示されます。

※【方法その1】では、Sheet1のE2～P2の範囲やSheet2のE1セルに入力する月の、数字の部分を半角英数字で入力する必要があります。
　尚、年を示す数字は、計算に関わっていませんので、省略しても構いません。

【方法その2】
　大半は【方法その1】と同じ設定方法となりますが、Sheet2のE2セルに入力する数式が異なっています。
　又、Sheet2の1行目に月を指定する数字を入力する必要はありません。

　まず、Sheet2のA2セルに次の数式を入力して下さい。

=IF(INDEX($A:$B,ROE()-ROW($A$1)+ROW(Sheet1!$A$1),COLUMN()-COLUMN($A$1)+1)="","",INDEX($A:$B,ROE()-ROW($A$1)+ROW(Sheet1!$A$1),COLUMN()-COLUMN($A$1)+1))

　次に、Sheet2のA2セルをコピーして、Sheet2のA2～D3の範囲に貼り付けて下さい。
　次に、Sheet2のE2セルに次の数式を入力して下さい。

=IF(COUNTIF($A2:D2,"合計"),"",IF(AND(COUNT(INDEX(Sheet1!$3:$3,COLUMN(Sheet1!E$2)):INDEX(Sheet1!$8:$8,COLUMN(Sheet1!$P$2))),COLUMNS($E:E)<13),Sheet1!E$2,"合計"))

　次に、Sheet2のE3セルに次の数式を入力して下さい。

=IF(E$2="","",IF(E$2="合計",IF(COUNT($A3:A3),SUM($A3:A3),""),IF(ISERROR(1/(INDEX(Sheet1!$E:$P,MATCH($A3,Sheet1!$A:$A,0),COLUMNS($E:E))<>"")),"",INDEX(Sheet1!$E:$P,MATCH($A3,Sheet1!$A:$A,0),COLUMNS($E:E)))))

　次に、Sheet2のE2～E3の範囲をコピーして、Sheet2のF3～Q3の範囲に貼り付けて下さい。
　次に、Sheet2の3行目全体（A3～Q3のセル範囲でも可）をコピーして、4行目以下に貼り付けて下さい。

　これで、Sheet1のE列～P列の中で、数値が入力されている最終月（添付画像の例では12月）の列までが表示され、最終月の列の右隣の列には4月～最終月の合計値が表示されます。

　添付画像の例を御覧頂ければ御解りになるかと思いますが、
【方法その1】ではSheet2のE1セルに入力された月までの列を表示し、
【方法その2】では数値が入力されている中で、最も右側にある列までを表示する様になっていて、
どの列までを表示させるのかの判定基準が異なりますので、Sheet1の表のデータが同じ場合でも、表示される列が異なる事もあり得ます。

回答No.4

KURUMITO

回答No３ですが、もっとエクセルの使い方を工夫することが必要に思いますね。
例えばA３セルから下方には項目が並んでいるとしてB２セルには４月、C２セルには５月、D２セルには６月・・・とM２セルの３月まで並んでいるとします。
それぞれの項目で該当する月のデータをそれぞれのセルに入力することにします。
そこで各項目の９月までの集計を求めるとしたら例えばN１セルに９月と入力します。９月のデータを１０月に変更するのでしたらN１セルに１０月と入力すればよいでしょう。
N２セルには　までの集計　とでも文字を入力します。
その上でN３セルには次の式を入力して下方にドラッグコピーすることで４月からN１セルに入力した月までの集計が項目ごとに表示されますね。

=IF(A3="","",IF(COUNTIF(B$2:M$2,N$1)=0,"",SUM(B3:INDEX(B3:M3,MATCH(N$1,B$2:M$2,0)))))

１０月までの集計についてもN１セルに１０月と入力すればよいのですがO列を利用して表示をするようにしてもよいでしょう。

N列の総計は項目の行が終わった後のN列の行を利用してSUMを使えばよいでしょう。
以上参考になりましたら利用してみてください。
この場合には対照表などは必要ありませんね。

お礼

No.4のやり方でも実現できました！
これで参照表も必要ないので、よりスマートにできるようになりました。
大変助かりました。ありがとうございます！！

回答No.2

K Kazz（@JazzCorp）

イツデモドコデモ4月～3月をSUMする。それがシワァ～セの法則、、、

回答No.1

shintaro-2

どこでも良いのですが、

行の上の方とか、データを追加しても
影響されないところにsum用のエリアを用意して

sumifで毎月の分を予め用意されては如何でしょう？

参考URL：

http://kokoro.kir.jp/excel/sumif.html

お礼

ご意見ありがとうございます。
他のやり方で解決しましたが、発想の転換ということろでは大変勉強になりました。
ありがとうございます。