エクセル 参照範囲の固定方法について

質問

エクセル　参照範囲の固定方法について

エクセルで、SUMIF関数を使用して、ある範囲のセルを参照しています。
参照先のセルを行ごと削除しても関数の参照範囲を変化させたくないのですが、
実際には、削除した列の分だけ参照範囲少なくなってしまいます。
列を削除しても、参照範囲を固定する方法はないでしょうか？　どうか宜しくお願いします。

例）(1)参照先：A1:A100
　　(2)A1～A30を削除
　　(3)（問題）関数の参照範囲がA1:A70となってしまう。⇒（希望）A1:A100のままにしたい。

回答No.4

web2525 · ベストアンサー

offset関数で範囲指定することも可能ですが
基準となるセル（今回の場合はA1セル)を削除されてしまうとエラーになります
A列を見出しに設定し固定されている場合で選択範囲がA2:A101の場合

=SUMIF(OFFSET(A1,1,,100),"条件",OFFSET(A1,1,,100))

こういった形式で固定されます

お礼

ありがとうございます。
エクセルが苦手な私でも、簡単にできました。
A1セルを削除しない範囲に設定することで懸念して頂いた問題も
解決できました。
本当にありがとうございます。（お礼が遅れて、大変申し訳ございません）

回答No.5

KURUMITO

セルの番地をINDEX関数で表しても良いですね。
例えばシート１のＡ列とＢ列にデータベースがありシート２でそのデータベースからVLOOKUP関数でシート１のＢ列の値を引き出したい場合には次のような関数が利用できますね。

=VLOOKUP(A1,Sheet1!A1:B10,2,FALSE)

=VLOOKUP(A1,Sheet1!A$1:B$10,2,FALSE)

=VLOOKUP(A1,INDEX(Sheet1!A:A,1):INDEX(Sheet1!B:B,10),2,FALSE)

いづれもシート１のＡ１セルからＢ１０セルまでにデータがあるケースです。
ここでシート１の１行から２行目を削除する操作をした場合には上の２つの式はA1:B8やA$1:B$8に変わってしまいますが最後に式は何ら変わりません。
最後の式のような使い方をすればお望みのような結果が得られますね。

お礼

回答が遅れて申し訳ございません。
回答頂いたは、私にとって高度な技ですが、この機会に試してみたいと思います。
本当にどうも有難うございました。

回答No.3

chuchuo

削除は諦めて
そのセルの数式と値のクリアではだめですか？
そうか、ほかの空白セルをコピーしてペーストするとか。
削除して詰める方向を聞かれなくてすむので
案外こちらのほうが便利だと思いますが。

お礼

遅れて申し訳ありませんが、回答頂きましてありがとうございます。
今回は、毎回削除するしかない用途なので、他の方の案を採用させて頂きました。
どうも有難うございました。

回答No.2

truffles23

絶対参照にしてはどうでしょうか。
例の場合は、A$1:A$100 とします。

お礼

回答ありがとうございます。
私の説明不足もあったと思いますが、
絶対参照にしても、参照範囲を削除すると、式に変化が生じてしまいます。
今回は他の方の案を採用させて頂きましたが、わざわざご回答頂きまして
ありがとうございます。

回答No.1

keithin

=SUMIF(INDIRECT("A1:A100"),X1,INDIRECT("B1:B100"))
などのようにしてみます。

普通にセル参照が生きている式を書いたときに，そのセル範囲を追加・移動・削ったら数式が変わるのを止めさせる方法はありません。（セル参照が「生きて」いない，閉じた外部ブックのセル範囲の式の場合を除いて，です）

＃余談ですが
誤記：
(1)参照先：A1:A100

正解：
(1)参照元：A1:A100　と言います。
　「先」はA1:A100を参照している式が入っているセルの事です。