【Excel】SUMIFSのコピーが連番になる

質問

条件が複数の場合の関数のSUMIFSについて、作り方は一応わかったのですが、コピーするとどうしてもセルが連番になってしまいます。

条件が一つの場合はSUMIFを使います。
たとえば添付画像の「種目」別の合計をだしたい場合、
=SUMIF(B:B,G6,D:D)
という式になりますが、これを複数の条件とする場合にはSUMIFSを使うようですが、各「年」ごと、各「種目」の合計をさらに「店舗」ごと個別計算し、その結果を表２に示す場合、
=SUMIFS(D2:D596,A2:A596,H2,B2:B596,G5,E2:E596,H3)
という式でよいかと思いますが、これを「種目」すべてにコピーすると、上の式のセルがすべて連番になってしまうのです。
たとえば
=SUMIFS(D3:D597,A3:A597,H3,B3:B597,G6,E3:E597,H4)
このようにです。

冒頭に述べた
=SUMIF(B:B,G6,D:D)
の場合、たとえば下のセルにコピーすると
=SUMIF(B:B,G7,D:D)
というようにG列だけ変化します。

連番になってしまうと、参照元がまったく別のものになってしまいます。

「合計対象範囲」「条件範囲」はセルの範囲を指定するという説明です。
https://www.becoolusers.com/excel/sumifs.html
しかしこれは合計結果がひとつづつの場合ですが、この添付画像のような複数の項目の、複数の条件の場合、同式の内容のみコピーさせるにはどうしたらいいのか困っております。

回答No.2

miso_kasu · ベストアンサー

>同式の内容のみコピーさせるにはどうしたらいいのか困っております。
条件が単独か複数かの問題でなく数式を他のセルへコピーするときの問題です。
セルの番地は列記号（A、B、C等）と行番号（１、２、３等）の組み合わせになっています。
セルの番地を絶対番地で指定する場合は記号の前に$記号を付けます。
提示された数式ではセルの番地が相対番地なので数式を他のセルへコピーするとコピー先のセルの番地に合せた番地に置き換えられます。
H5の数式は下記に示す数式にすれば良いでしょう。
=SUMIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3)

A列の比較値（$H$2）は数式をどこのセルへコピーしても変化しないようにします。
B列の比較値（$G5）は下へコピーすると行番号が相対的に変化し、右へコピーしたときは列記号（$G）は絶対番地なので変化しません。
E列の比較値（H$3）は下へコピーしたとき行番号（$3）は絶対番地なので変化しませんが右へコピーしたときは列記号（H）は相対的に変化します。

H5をH6へコピーしたとき
=SUMIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G6,$E$2:$E$596,H$3)

H5をJ5へコピーしたとき
=SUMIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,J$3)

補足

詳しいご回答ありがとうございます。

（$G）は列の絶対値で、（$G5）のような場合には、行（5）は相対値
（$3）は行の絶対値で、（H$3）のような場合には、列（H）は相対値
（$H$2）は行と列（ひとつのセル）が絶対値（相対値なし）

ということで理解しました。

まだ不明なのが、
=SUMIF(B:B,G6,D:D)
の場合です。この式が入っているのがA1セルとしたら、A2セルに式をコピーした場合
=SUMIF(B:B,G7,D:D)
となるわけですが、「B:B」と「D:D」には絶対値の記号「$」がつけられてません。
「B:B」はB列全部という意味だと思いますが、列全部という意味の式にすればそれが絶対値の場合でも「$」マークは不要なのでしょうか？

回答No.5

miso_kasu

>すみません。H1はH5の誤りでした。
「H5の数式　=SUMIF(B:B,G5,D:D)　を
H6へコピーすると　=SUMIF(B:B,G6,D:D)　になります。」
再確認してください。
「H5の数式　=SUMIF(B:B,G5,D:D)　を
J5へコピーすると　=SUMIF(D:D,I5,F:F)　に変化します。」
これも再確認してください。
結果が「=SUMIF(E:E,J5,G:G)
となりました。」と言うことですがそれは変ですね。
相対番地がどのようなものかを基本的に覚えないと数式のコピーで失敗します。
H5セルの数式「 =SUMIF(B:B,G5,D:D) 」はB列がG5と同じ行のD列の値を合計することです。
H5セルから見ればB列は「左へ6列離れた位置」でG5セルは「左隣りのセル」です。また、D列は「左へ4列離れた位置」です。
H5セルの数式をJ5セルへコピーすると「G5」がJ5の左隣りのI5に、「B:B」がJ5の左へ6列離れたD:Dに、「D:D」がJ5の左へ4列離れたF:Fに変化するはずです。
これらのことを再確認できればセルの相対番地と絶対番地の使い分けが分かるようになるでしょう。

お礼

再度試してみたところ、ご回答のとおりになりました。
まとめますと

H5を起点➡=SUMIF(B:B,G5,D:D)
・H5から見れば➡B列は「左へ6列離れた位置」
・H5から見れば➡D列は「左へ4列離れた位置」
・H5から見れば➡G5は「左隣りのセル」
H5にある数式「=SUMIF(B:B,G5,D:D)」をJ5セルへコピーすると変わる式「=SUMIF(D:D,I5,F:F)」について
・J5から見れば➡「B:B」がJ5の左へ6列離れた「D:D」に変化
・J5から見れば➡「D:D」がJ5の左へ4列離れた「F:F」に変化
・G5から見れば➡「J5」の左隣りの「I5」に変化

H5の式をJ列にコピーした場合、コピー先ではH5と相対する各列の距離の分の列が変化する（相対参照）ということですね。

補足

この関数が入ったシートをコピーして使ったところ、コピー先では数式が機能しません。
考えられる原因がありましたら教えてください。

回答No.4

miso_kasu

>「H1セルの数式をH2セルへコピーしたときH2セルから見ると隣のB列全体なのでH1セルから見たときと同じのため変化しません。」
H1セルをH5セルに置き替えないと質問に添付の画像と合いません。
また、B列はH列から見ると隣ではないので用語が誤りになります。

>全体を表す「B:B」という表記は相対的になるということでよいでしょうか？
B:BはB列全体を指しますが相対番地の列記号です。
$B:$Bとすれば絶対番地の列記号になります。

>「H1セルをC1セルへコピー」してみましたが、「=SUMIF(#REF!,B1,#REF!)」となりました。
H5の数式　=SUMIF(B:B,G5,D:D)　を
H6へコピーすると　=SUMIF(B:B,G6,D:D)　になります。
H5の数式　=SUMIF(B:B,G5,D:D)　を
J5へコピーすると　=SUMIF(D:D,I5,F:F)　に変化します。（目的に合いません）
H5の数式をC5へコピーするとB:BがA:Aより左側へ変化させようとしてもセル範囲が指定できなくなりエラーになります。
C列には商品名が入力されていますので数式のコピー先としては不適当のようです。

補足

すみません。H1はH5の誤りでした。

回答３の

＞A1セルの数式をA2セルへコピーしたときA2セルから見ると隣のB列全体なのでA1セルから見たときと同じのため変化しません。

という回答のA列をH列に替えるとき、「隣のB列」はI列、「A1」は「H5」となりますが、確かにH5の数式　
=SUMIF(B:B,G5,D:D)　をH6へコピーすると　
=SUMIF(B:B,G6,D:D)　となり、「B:B」は相対番地となる。
つまりH5セルの数式をH6セルへコピーしたときH6セルから見ると隣のB列全体なのでH1セルから見たときと同じのため変化しないということがわかりました。

回答３

＞D列に対してもA列から見ていますので相対番地でも列記号は変化しません。
（セル替えしてA列をH列に替えD列の位置に該当する）K列にコピペしたところ

=SUMIF(E:E,J5,G:G)

となりました。

（コピペだからK5のはずが）J5になるのはK列が隣接するのがJ列だから。
つまりこれが「相対」という意味なのだと思いますが、

回答３の
＞D列に対してもA列から見ていますので相対番地でも列記号は変化しません。
（セル替えしたとき「K列に対してもH列から見ていますので相対番地でも列記号は変化しない」）

に該当しません。

=SUMIF(B:B,G5,D:D)　
のB:BがなぜE:Eに、D:DがG:Gに、K5列にコピーしたはずがJ5に変化（目的に会わない）しているのでしょう？

＞C5へコピーするとB:BがA:Aより左側へ変化させようとしても

についても関連すると思いますが、なぜ変化となるのでしょうか？

回答No.3

miso_kasu

>「B:B」はB列全部という意味だと思いますが、列全部という意味の式にすればそれが絶対値の場合でも「$」マークは不要なのでしょうか？
「B:B」は相対番地ですがA1セルの数式をA2セルへコピーしたときA2セルから見ると隣のB列全体なのでA1セルから見たときと同じのため変化しません。
D列に対してもA列から見ていますので相対番地でも列記号は変化しません。
A1セルをC1セルへコピーしてみると変化する様子が分かるでしょう。

補足

質問の仕方が間違ってましたので回答がわかりにくくなってしまいました。

＞この式が入っているのがA1セル

この表の場合、A1セルは、参照される対象を指定するところ（B列内の項目）ですので、ここに式を入れることはできません。
よって式が入るところはH1とし、参照対象（たとえばこの質問の表のアボカド）をG1に改めます。

＞A1セルの数式…のくだりについては

「H1セルの数式をH2セルへコピーしたときH2セルから見ると隣のB列全体なのでH1セルから見たときと同じのため変化しません。」

となりますが、これでもやはり全体を表す「B:B」という表記は相対的になるということでよいでしょうか？

＞ D列に対してもA列から見ていますので相対番地でも列記号は変化しません。

これも同様

「 D列に対してもH列から見ていますので相対番地でも列記号は変化しません。」
ということに代わりますが、これもH全体をみるので相対的となる、という理解で宜しいでしょうか？

ただ

＞ A1セルをC1セルへコピーしてみると変化する様子が分かるでしょう。

について「H1セルをC1セルへコピー」してみましたが、「=SUMIF(#REF!,B1,#REF!)」となりました。「変化」というのはエラーのことでしょうか？（#REFの意味もわかりかねますが）

そもそもExcel関数において絶対と相対がわかりません。
ご回答の「相対番地」というのは、参照される側ということで、「絶対番地」というのは指令された結果がだされる場所という意味でよいでしょうか？

回答No.1

kkkkkm

参照元を一定にしたい場合は
D2:D596
を
$D$2:$D$596
のようにします。
D2やD569など一定にしたい参照元を選択した状態で
F4キーを押すと自動で$が付きます。

お礼

それぞれの費目に対する算出場所が違い、それによって
A列の比較値➡（$H$2）
B列の比較値➡（$G5）
というように、$のつけ方も違ってくるわけですので、
＞$D$2:$D$596のようにします。
という回答のみではできませんでした。