【Excel】SUMIFSの式を絶対値にするには

質問

添付した画像のセルB2には以下のとおりのSUMIFSの式が入っているとします。

=SUMIFS(E:E,B:B,Q2,C:C,Q3,G:G,R2)

セルC2にこの式をコピーしたいのですが、

=SUMIFS(F:F,C:C,R2,D:D,R3,H:H,S2)

このようになり、合計対象範囲から条件まで、すべてズレてしまいます。

逆にB2の下のセルB3にB2の式をコピーすると条件それぞれの位置がこのように

=SUMIFS(E:E,B:B,Q3,C:C,Q4,G:G,R3)

ズレます。

表マトリクスの数値の合計をだすために、どの位置にセルB2の式をコピーしても正確な結果がでるようにセルB2を絶対値としたいのです。セルB2に入れる正確な式を教えてください。

また式だけではなく絶対値となる理由もご説明していただけると幸いです。

回答No.4

miso_kasu

>また式だけではなく絶対値となる理由もご説明していただけると幸いです。
Excelの数式で参照するセルを絶対番地にするには列記号（A、B等）の前に$マークを付ける（$A、$B等）と数式を他のセルへコピーしたときに変化しなくなります。
また、行番号（1、2等）も列記号と同様に$マークを付けることで絶対番地の指定になります。
提示の数式で集計範囲の列がE列全体のとき他のセルでもE列全体が集計範囲であれば$E:$Eのように指定するとB2セルの数式の集計範囲はC2セルへコピーしても$E:$Eになります。
比較範囲1のB:Bと比較値のQ2については例の数表が提示されていないので質問文だけではB:Bを絶対番地にしたいのかQ2セルを絶対番地にしたいのかがわかりません。
列記号が可変で行番号が絶対番地の場合はQ$2とし、列記号が絶体番地で行番号が可変の場合は$Q2とすれば良いでしょう。（範囲のB:Bには行番号が無いので1～最大行番号であり省略されています）
B2の数式をC2へコピーしたときどのように変化してほしいかを提示して頂ければ具体的に数式を提示できます。
また、同様にB2の数式をB3へコピーしたときの数式を提示頂ければあなたの希望通りの数式を提示できると思います。
絶対番地の指定は他のセルへコピーしたときに指定した番地が変化しないことです。
可変（相対番地）の指定では数式を設定したセルと対象のセルが隔たった位置をコピー先へ引き継ぎますので数式のセルと対象のセルの位置関係を観察してコピーの可否を判断してください。

回答No.3

kkkkkm

> B2とC2セル（店舗）に対して共通項である商品を絶対値とし、店舗間だけ相対値とすることはできないのか？というのが質問の主旨でした。

ですから、エクセル絶対参照を検索して絶対的に変化させたくないセル指定をご自身で指定してください。ということです。とにかく絶対参照を理解しなければ「絶対値となる理由」もわからないのです。
それとも、ネットに情報が山ほどあるのにそれをいちいち私が説明しろとでもいうのでしょうか？

お礼

前のお礼で成功したと言ってますよね？
ネットで調べてみたところ、複合参照というものだとわかったからです。

＞エクセル絶対参照を検索して絶対的に変化させたくないセル指定をご自身で指定してください

そう、最初に言ってもらえばわかりやすかったです。補足しても質問の意味がわかってないようですね。

補足

＞いちいち私が説明しろとでもいうのでしょうか？

というのなら最初から回答しないでください。

質問の意味がわかってないようなのに憶測での回答されて逆に混乱しました。

回答No.2

kkkkkm

> 数式がそのままコピーされただけ（つまり答えも同じ）でしたが…。

はい、そのようにしてます。
E:E,
Q2,
Q3
G:G
R2
何がどうなっているのか不明ですから。全てB2絶対値にしてます。
エクセル絶対参照で調べて自身で変更したくないセルの指定先を設定してください。だから検索してくださいと回答しています。

お礼

回答いただいた式を編集し、絶対値として固定したくないセル（つまりコピーしたとき相対値となるセル）については、絶対値の記号＄をとりましたところ、成功しました。

回答No.1

kkkkkm

=SUMIFS($E:$E,$B:$B,Q$2,$C:$C,$Q$3,$G:$G,$R$2)
でいいと思います。エクセル絶対参照で検索してください。

お礼

ご回答者様に質問の意味が伝わってないようですので改めて。
B2を絶対値にという言い方が間違っていたと思います。

質問で述べたとおり、B2に入れた式をC2にコピーしたとき、B1を相対値にしたままの状態のときにすべてのセル位置がひとつづつズレてコピーされてしまうが、B2とC2セル（店舗）に対して共通項である商品を絶対値とし、店舗間だけ相対値とすることはできないのか？というのが質問の主旨でした。

それとも、相対値をコピーしたら、合計対象範囲から条件まで、すべてズレてしまうのは仕方なく、条件が変わるのなら、ひとつづつ手動で関数式を設定しなければならないのでしょうか？

補足

ご回答の式で試してみましたが、数式がそのままコピーされただけ（つまり答えも同じ）でしたが…。