エクセル関数のコピーについて

質問

購入品関係の年間集計金額表を作っています。
初心者ですので専門的言葉がわからないので説明下手になると思います。
すみません・・・

毎月ごとにシートが別になっていてそのシート４月～３月までのデータが会社の専用ソフトで
勝手に品名、金額、購入者などを織り込んだ表が自動作成されるのですが、
これを一括りにまとめたシートを作成しています。
やっと関数が出来たのですが隣のセルにコピーすると月も(シート名)次の月に変えることは出来ないでしょうか？
文章だと説明出来ないので実際使ってる関数を出します。

例：
=SUMIF('4月'!$D:$D,"*電池*",'4月'!$E:$E))

この関数がB1にあってC1、D1・・・にコピーさせると５月、６月・・・にしたいというものです。
「電池」となってる条件はB2、B3・・・と長々30種目ほどあって、月を1つづつ変えるのは
気が遠くなってしまいます。

あと、
=SUMIF('4月'!$D:$D,"*電池*",'4月'!$E:$E))

この関数を↓方向にコピーさせると合計範囲の「E:E」がF:F、G:G・・となるように出来る方法も
知りたいです。

どなたか詳しい方よろしくお願い致します。

回答No.1

MackyNo1 · ベストアンサー

＞例：
=SUMIF('4月'!$D:$D,"*電池*",'4月'!$E:$E))
この関数がB1にあってC1、D1・・・にコピーさせると５月、６月・・・にしたいというものです。

以下の式を入力して右方向にオートフィルコピーしてみて下さい。

=SUMIF(INDIRECT(MOD(COLUMN(C1),12)+1&"月!$D:$D"),"*電池*",INDIRECT(MOD(COLUMN(C1),12)+1&"月!$E:$E"))

＞「電池」となってる条件はB2、B3・・・と長々30種目ほどあって、月を1つづつ変えるのは
気が遠くなってしまいます。

この意味がよくわからないのですが、集計したい項目が一覧にされているなら、以下のように検索条件を単純にセル参照した数式にすればよいのではないでしょうか。

"*電池*"　→　"*"&セル番地&"*

＞あと、
=SUMIF('4月'!$D:$D,"*電池*",'4月'!$E:$E))
この関数を↓方向にコピーさせると合計範囲の「E:E」がF:F、G:G・・となるように出来る方法も知りたいです。

集計範囲を以下のように変更してください。

'4月'!$E:$E　→　INDEX('4月'!$E:$Z,,ROW(A1))

お礼

ご回答ありがとうございます。
全部の難題に回答くださって完全に解決です！！

「電池」の条件ですが・・・すごい・・・・
こんな事ができたのですね～！！
「～を含む」を昨日覚えたので早速使ってみたのですが
＆で挟むとセル番地でも対応できる！！！
しっかり覚えました！ありがとうございます。

回答No.4

imogasi

回答は既に出ていると思うが、回答の式をコピペして「うまく行きました」ではなく、下記（基礎的なこと・例）をやってみて納得し、応用してください。
関数・数式のシート名の部分は式のコピーで都合の良いように変化しませんから、別途手を打たないとならない。
ーー
テスト
３枚のシート名を各々４月、５月、６月に変える。
それぞれのA1セルに４月、５月、６月と人間が入れる（A1セルでなくても、どのセルでも良いが、シートの同じ位置のセルに入れる。関数式でシート名を取るのは難しいので、人間がセルに入れる
テスト目的は、各シートのA2：A9の合計をA10セルに、式を一々要れないで、出すものとする。
各シートの、A2:A9に適当な数を入力する:。
ーー
４月シートのA10セルに　=SUM(INDIRECT(A1&"!A2:A9"))と入れる。４月シートのA2:A9が正しく合計されていることを確認。
（=SUM(Sheet2!A2:A9)などは経験があるのだろう。）
４月シートのA10をコピーし、５月シートのA10にコピー貼り付けする。同じく６月シートのA10に貼り付ける。
これで５月、６月シートともに、A2:A9の合計が正しく出ることを納得。
ーー
コピーして、式が変化したというよりも、INDIRECT関数を使って変化するように仕組むものである。
複数ブックで、ブック名の変化にも応用できる。

お礼

ご回答ありがとうございます。
度々ご親切に教えていただき感謝しております。
そうなんです・・・関数は教えてもらったものを使ってるので上司から言われてる宿題は
完成してきてるのですが、意味がわからないうちに使ってるので、
なかなか応用に結びつかないんです・・・
実際にテストやってみました！
こういった応用のヒントはとても助かります。
ありがとうございました。

回答No.3

mu2011

一例です。
以下の数式を縦横にコピーで如何でしょうか。
=SUMIF(OFFSET(INDIRECT(MONTH(DATE(1,COLUMN(D4),1))&"月!D1"),,,65535),"*電池*",OFFSET(INDIRECT(MONTH(DATE(1,COLUMN(D4),1))&"月!D1"),,ROW(A1),65535))

お礼

ご回答ありがとうございます。
度々助けていただき感謝しております。
同じ答えでも色々な関数があるんですね！
参考になります。ありがとうございました。

回答No.2

kagakusuki

INDIRECT関数を使用されると良いと思います。
　$D:$Dは固定だとしますと、

=SUMIF(INDIRECT(MOD(COLUMNS($B:B)+2,12)+1&"月!D:D"),"*電池*",INDIRECT(MOD(COLUMNS($B:B)+2,12)+1&"月!C"&ROWS($1:1)+4,FALSE))

では如何でしょうか。

お礼

ご回答ありがとうございます。
また助けていただき感謝しております！
D:Dは固定でも$←コレ付けなくて大丈夫なんですね~！
ちょっとまだ解読は出来ていませんが早速使わせていただきます。
ありがとうございました！