Excel2010 SUM関数の参照列を指定
- Excel2010で年度(4月~3月)の集計表があり、最右列へSUM関数が入っています。
- 毎月、前月までの合計を出すため、SUM関数が入っている列は、毎月月初に参照列が修正されます。
- 参照列のアルファベットを同じファイル内のどこかのセルで指定して参照させることは可能でしょうか?
- ベストアンサー
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」に一括で変更できるようにしたいです。 分かりにくくすみません。質問あれば補足します。
- du-sama
- お礼率52% (57/108)
- その他(Windows)
- 回答数5
- ありがとう数3
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
ご質問の内容をよく理解していませんがシート2のA列とB列にお示しのような表が入力されているとします。 そこで例えばシート1で9月対応のSUM関数が=SUM(E20:J20)となっているとして、その関数を自動的に10月対応にしたいとのことですね。 例えば9月対応の時にシート1のA1セルに9月としてSUM関数の式を=SUM(E20:J20)のように働かせ、10月の時はA1セルに10月としてSUM関数を自動的に対応する式に変換できるようにするには例えば次のような式にします。 =SUM(E20:INDIRECT(VLOOKUP(A1,Sheet2!A:B,2,FALSE)&20))
その他の回答 (4)
- kagakusuki
- ベストアンサー率51% (2610/5101)
>この各月の数字は日々変化していき、 との事ですが、その数字を変化して行くのは、どの様に行われているのかが不明であるため、下手に元データが入力されている表の右端に合計を表示させようとしますと、「数字を毎月変化させるシステム」と干渉してしまい、数字の書き換えが行われなかったり、合計が表示されなかったりする恐れもないとは言えません。 ですから、元データが毎月入力されて行く表に関しては直接いじり回す事は避けまして、別のシート上に元の表と同じ内容の表を自動的に表示させた上で、「その自動表示させた表の中で最終月の列」の右隣の列に、合計を表示させる様にされては如何でしょうか? まずは下の添付画像を御覧下さい。 もし、質問者様が御利用になられているサイトでは画像が表示されていない場合には、以下の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の表のデータが同じ場合でも、表示される列が異なる事もあり得ます。
- KURUMITO
- ベストアンサー率42% (1835/4283)
回答No3ですが、もっとエクセルの使い方を工夫することが必要に思いますね。 例えばA3セルから下方には項目が並んでいるとしてB2セルには4月、C2セルには5月、D2セルには6月・・・とM2セルの3月まで並んでいるとします。 それぞれの項目で該当する月のデータをそれぞれのセルに入力することにします。 そこで各項目の9月までの集計を求めるとしたら例えばN1セルに9月と入力します。9月のデータを10月に変更するのでしたらN1セルに10月と入力すればよいでしょう。 N2セルには までの集計 とでも文字を入力します。 その上でN3セルには次の式を入力して下方にドラッグコピーすることで4月からN1セルに入力した月までの集計が項目ごとに表示されますね。 =IF(A3="","",IF(COUNTIF(B$2:M$2,N$1)=0,"",SUM(B3:INDEX(B3:M3,MATCH(N$1,B$2:M$2,0))))) 10月までの集計についてもN1セルに10月と入力すればよいのですがO列を利用して表示をするようにしてもよいでしょう。 N列の総計は項目の行が終わった後のN列の行を利用してSUMを使えばよいでしょう。 以上参考になりましたら利用してみてください。 この場合には対照表などは必要ありませんね。
お礼
No.4のやり方でも実現できました! これで参照表も必要ないので、よりスマートにできるようになりました。 大変助かりました。ありがとうございます!!
- K Kazz(@JazzCorp)
- ベストアンサー率31% (549/1751)
イツデモドコデモ4月~3月をSUMする。それがシワァ~セの法則、、、
- shintaro-2
- ベストアンサー率36% (2266/6244)
どこでも良いのですが、 行の上の方とか、データを追加しても 影響されないところにsum用のエリアを用意して sumifで毎月の分を予め用意されては如何でしょう?
お礼
ご意見ありがとうございます。 他のやり方で解決しましたが、発想の転換ということろでは大変勉強になりました。 ありがとうございます。
関連するQ&A
- エクセルSUM関数について
エクセルをやり始めた程度の初心者です。 今原価に対して利益を示す表を作成しています。 Mの列に利益額を出す下記の関数を作成したのですが =SUM(H2:I2:J2)-SUM(K2:L2)-SUM(D2:E2:F2:G2) M3,M4、M5・・の各セルに上記の関数に数値を変えて行くの 大変なので簡単にできる方法があれば教えてください。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- sum関数で行ごとの指定ができない。
Excelの関数式です。 列ごとを指定する時は例えば=sum(a:h)などと指定しますが(A~H列までの列全体を選択) 行ごとの指定をしても、計算式が反応しません =sum(26:43) (26行目~43行目までの行すべてなど)。 これは何がいけないのでしょうか。 それとも行全体指定の場合、何か特別な操作があるのでしょうか。 よろしくお願い致します。 ※何もsum関数に限った事ではないのかもしれませんが。。。
- ベストアンサー
- Excel(エクセル)
- 関数の引数の列番号のアルファベットを参照してくれる関数はありませんか?
関数の引数の列番号のアルファベットを参照してくれる関数はありませんか? 月次で入力している数字がありその右に前年比{=D4:J4/SUM('2009年度'!D4:J4)}を出すセルを設けてあります。今は10月まで入力されてますので11月から3月までは空欄でその右に前年比です。 現状、月末に入力するたびに前年比セル内をJ→k→l→mとアップデイトさせているのですが、これが自動でされるようにするにはどうすればよいですか?
- ベストアンサー
- オフィス系ソフト
- fortran90/95のSUM関数について
fortran90/95のSUM関数について fortran90/95のSUM関数について、SUM関数を使うときは範囲を指定できるのでしょうか? 例えば、 integer A(3,2)/1,2,3,4,5,6/ A=1,4 2,5 3,6 の配列があるとします。 列の和を求めたいのですが、最後の行(3行目)を足したくありません。 結果としては 1+2=3 4+5=9 にしたいのです。 このようなときは、やはりdo文にするしかないのでしょうか。 実際のプログラムではdo文があまりにも多く、見づらいのでSUM関数を使いたいのですが... どなたかご存知の方、お教えいただけないでしょうか。 よろしくお願いします。
- ベストアンサー
- その他(プログラミング・開発)
- 参照するセルに関数が入っていると#VALUE!に
いつもお世話になります。 WIN7 EXCELL2010 です。 年度を変更し時にO列の最終行(参照図ではO24)が変わりその時、最終行以降のセルに関数が入っているとI/J列に#VALUE!が出ます。 年度を変更した時のエラーを解決したいのですがご教示いただけませんでしょうか。 O列は B列から参照した値をN列に取り込みそれを詰めたものがO列です。 この時N列の最終の値をとったO列の最終行以降(O24)のセルに関数を入力していると I /J 列にエラーが発生し、 その解決策として、この場合で言うと O25 からは関数を入れずに対応するとエラーがでません。 I /J 列にそれぞれの日数を求めるのですが参照するのはO列です。 参考 B6 =DATE($B$4,1,1) B8 =DATE(B$4,1,14-WEEKDAY(DATE(B$4,1,0),3)) G2 =DATE($B$4,H2,1) I2 =NETWORKDAYS(DATE($B$4,H2,1),DATE($B$4,H2+1,),$Q$1:$Q$49) J2 =K2-I2 K2 =DAY(DATE(YEAR($B$3),MONTH(G2)+1,0)) M1 =IF(B6="","",B6) N1 =IF(M1="","",COUNT($M$1:M1)) O1 =IF(COUNT($M$1:$M$42)<ROW(M1),"",SUMIF($N$1:$N$42,ROW(M1),$M$1:$M$42)
- ベストアンサー
- その他MS Office製品
- Excel関数について
「VLOOKUP」で参照した文字を関数式に代入する方法を教えてください。 イメージとしては、「=SUM(A1:VLOOKUP(-,-,-,-)25)」というように、 VLOOKUPで参照した文字を"E"とすると、結果的には、=SUM(A1:E25) となるような関数式を作りたいと考えております。 どのようにすればよろしいでしょうか? よろしく教えていただきたくお願いいたします。
- ベストアンサー
- Excel(エクセル)
- INDEX関数で2列からの参照は?
INDEX関数で2列からの参照は? 表1 料金表 A列 B列 C列 D列 着地 北海道 関東 関西 発地 サイズ名 発/着地番号 1 2 3 北海道 60S 1 500 1,200 1,500 北海道 80S 2 700 1,500 1,750 北海道 100S 3 1,000 1,750 1,900 関東 60S 4 750 500 800 関東 80S 5 950 700 1,000 関東 100S 6 1,100 800 1,300 関西 60S 7 1,500 800 500 関西 80S 8 1,750 1,000 750 関西 100S 9 1,900 1,300 900 上の表を使って、下の表の料金を求めます。 表2 A列 B列 C列 D列 E列 氏名 発地 着地 サイズ 料金 あ 北海道 関西 60S い 関東 北海道 80S う 関西 関東 80S え 関東 関西 100S このような場合、料金を求めるには、INDEX関数でいいと思うのですが、2列を参照しないといけないので、どのように式を作ればいいでしょうか? SUMPRODUCT関数を使えばいいですか? どなたか教えて下さい。よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 文字列の順番を指定して条件づける関数 エクセル
添付ファイルを参照いただきたいのですが、 アルファベットのS,M,J,Nで構成される3つの文字列があります。 (別シートにあるデータからif関数で条件に合致したものにそれぞれの3つの文字列が入っています。) 現状として1つの行に2つ以上文字列があったら★をつけているのですが、ここから、かつ、文字列の順番がバラバラのもののみ抽出したいです。 例えば、黄色ハイライトが該当します。1つ目の文字列がSとM,2つ目がMとJ、3つめがJとSなので、3つとも順番が違うので該当。 例えば、緑が該当しません。1つ目と3つめがMとSで同じ。 関数で抽出するにはどうすればいいでしょうか?
- 締切済み
- Excel(エクセル)
- excel関数で条件によって参照元をかえる
A B C D E F G H i J K l M N 1(月) 4 5 6 7 8 9 10 11 12 1 2 3 2(実績) 10 20 30 40 2 3(見通) 4 5 50 7 15 5 今月 3 (← B3には4月を1とし1~12の数字が入っていく) というのがあって、 N2に B3=3の時は、実績行のB~D と 見通し行のE~Gの合計 B3=4の時は、実績行のB~E と 見通し行のF~Hの合計 B3=5の時は ・・・・ というような計算式をつくりたいです 意味的には、 月毎にB3の数字が変わっていって、 6月の場合、B2が3となり、これを参照して、4~6月の実績と7~9月見通しの合計結果 「122」が入る 7月の場合、B2が4となり、これを参照して、4~7月の実績と8~10月見通しの合計結果 「162」が入る という感じです。 素人なんで、上の記述のままIFに置き換えて書くことは思いつくのですが長ったらしい式になるので、 シンプルな計算式で表現できないかと思ってますが、どういう関数がいいのか分かりません。 どなたかご教授頂けますか?
- 締切済み
- その他MS Office製品
お礼
この方法でできました。 No.4の方も理解したかったのですが、取り急ぎ参照表を使うNo.3の方法で間に合いました。 大変助かりました。ありがとうございました!