行を削除しても関数の参照範囲がずれないようにしたい

質問

エクセル2010を使っている者です。

関数式の参照範囲のことで伺います。

たとえば、

IF(SUMPRODUCT((A$5:A$1000=I5)*(J$5:J$1000<=K5+1))>1,"○",""))

という式を組み、５００行以降を削除した場合、行数に絶対参照をつけていても、
参照範囲が５行目から４９９行目に自動的に修正されてしまいます。

行を削除しても、範囲の参照が修正されないようにする方法はありますか？

よろしくお願いいたします。

回答No.2

kagakusuki · ベストアンサー

回答No.1様が回答されたもの以外の方法として、

=IF(SUMPRODUCT((A$5:INDEX(A:A,1000)=I5)*(J$5:INDEX(J:J,1000)<=K5+1))>1,"○","")

とする方法もあります。

　因みに、回答No.1様が仰っておられる

>OFFSET関数なども利用

という方法は次の様なものです。

=IF(SUMPRODUCT((OFFSET(A$5,,,996)=I5)*(OFFSET(J$5,,,996)<=K5+1))>1,"○","")

　尚、Excelbookの中にINDIRECT関数やOFFSET関数が使用されているセルが存在していた場合、Excelのウィンドウを閉じる際に、例え入力値を何も変更していなくとも、一々、「変更を保存しますか？」と尋ねて来るという現象が発生します。

【参考URL】
　インストラクターのネタ帳 > Excel(エクセル)の関数・数式の使い方 > 変更してないのに保存確認メッセージが表示される
　　http://www.relief.jp/itnote/archives/001519.php

　別にそのまま[保存]ボタンを押して保存すれば良いだけの話なので、大して問題視する様な話でもないのですが、人によっては、その事を煩わしく思うのか、これらの関数を使用する事を嫌う人も一部には居るようです。

お礼

遅くなりましたが、ご回答ありがとうございます。
すぐに拝見したのですが、大変助かりました。

＞=IF(SUMPRODUCT((A$5:INDEX(A:A,1000)=I5)*(J$5:INDEX(J:J,1000)<=K5+1))>1,"○","")

こういった書き方があるのですね。
しかし、INDEX関数での範囲指定の仕方がよくわからなかったので、教えていただけると幸いです。

INDEXの書き方を調べてみると、「=INDEX(範囲,行位置,列位置 [,領域番号]」と書いてありました。
「A$5:INDEX(A:A,1000)=I5」とは、A列の５行目から１０００行目までの間にI5があるかを調べていると思うのですが、なぜ「INDEX(A:A,1000)」でA列の１０００行目までを意味することができるのでしょうか。「A:A」とは何を意味するのでしょうか。

よろしくお願いいたします。

回答No.5

keithin

＞私の最初に組んでいた質問文にある関数と同じようには動きません

具体的な各セルの内容も，計算結果がどうちがうのかの具体的な状況もナイショのままなので，あくまで一例としてですが。計算してるセルに「空っぽのセル」が混じっていると，結果が異なるかもしれません。他の原因なのかも？しれません。例えばご相談で掲示されてる数式からして，実際のエクセルと実は違ってるのかもしれません。

何が「正しい」のかは，今のエクセルの実際のデータを精査して「どうあるべきか」ご自分で判断なさって下さい。

ご相談の内容は「行削除しても参照が修正されない」ためにはで，その回答としてINDIRECTを使いますと回答しています。
検討がめんどくさいなら，今のあなたの計算方法そのまま
IF(SUMPRODUCT((INDIRECT("A$5:A$1000")=I5)*(INDIRECT("J$5:J$1000")<=K5+1))>1,"○",""))
としてください。
余計なことを回答して混乱させたようで，ごめんなさい。

お礼

おっしゃるとおり空白セルがあったのでそれを削除して最初に書いていただいた式を入れたのですが、＃VALUE!のエラーになってしまいます。

また、今回の回答で書いていただいたとおりに
=IF(SUMPRODUCT(INDIRECT("I$5:I$1000")=I5)*(INDIRECT("J$5:J$1000")<=K5+1)*(INDIRECT("K$5:K$1000")>=J5-1))>1,"○",""))
と式を入れたのですが、エラーになってしまいます。

いずれにしろ、丁寧に答えていただきありがとうございました。

回答No.4

kagakusuki

>ANo2さんのご回答のように「セル：INDEX関数」方式の数式も

>＞一々、「変更を保存しますか？」と尋ねて来るという現象が発生します。

>という点では同じなので、誤解無いようお願いします。

　失礼致しました。
　「尚、Excelbookの中にINDEX関数やOFFSET関数が使用されているセルが存在していた場合、Excelのウィンドウを閉じる際に、例え入力値を何も変更していなくとも、一々、『変更を保存しますか？』と尋ねて来るという現象が発生します。」
と書こうとした際に、「INDEX関数」と書くところを間違えて、「INDIRECT関数」と書いてしまっておりました。
　尤も、INDIRECT関数の場合も同じ現象が発生する様です。

お礼

ご親切にありがとうございます。

回答No.3

keithin

ん？
一応ご参考までに

ANo2さんのご回答のように「セル：INDEX関数」方式の数式も

＞一々、「変更を保存しますか？」と尋ねて来るという現象が発生します。

という点では同じなので、誤解無いようお願いします。

＃当方、他の方の回答とかやり取りに、横から口を挟むようなマネは通常はしないんですが。

お礼

ご親切に、ありがとうございます。

しかし、私のファイルではindex関数をすでに使っているのですが、そういったメッセージがでません。
出るときもあるということなのか、条件によっては出なくなるということなんでしょうか。

いずれにしろ、ありがとうございます。

回答No.1

keithin

＞行を削除しても、範囲の参照が修正されないようにする方法はありますか？

＃ご相談の作文は５００とか１０００とかだいぶごっちゃになってますが、そこは触れない事にして

たとえば
=IF(COUNTIFS(INDIRECT("A5:A1000"),I5,INDIRECT("J5:J1000"),"<="&(K5+1))>1,"○","")
などのように、INDIRECT関数を使って出来ます。
状況に依ってはOFFSET関数なども利用できます。

もっとも、
=IF(COUNTIFS(A:A,I5,J:J,"<="&K5+1)>1,"○","")
のようでは何故いけないのか、そもそも５００行までとホントに範囲限定する必要があるのか（SUMPRODUCT関数じゃ確かに問題ありですけどね）検討してみるのも、一つの解決策です。

お礼

ありがとうございます。

＞=IF(COUNTIFS(INDIRECT("A5:A1000"),I5,INDIRECT("J5:J1000"),"<="&(K5+1))>1,"○","")
などのように、INDIRECT関数を使って出来ます。

とのことですが、私の最初に組んでいた質問文にある関数と同じようには動きませんでした。
なぜでしょうか。。