関数で行の挿入、削除で範囲がズレてしまう

質問

HLOOKUP関数を使い検索値を日付にして、ある項目の時間を表示させているのですが、項目を挿入、削除によって時間がズレてしまいます。対応する関数はありますか？
＝HLOOKUP（AS$223,$D$3:$AH$110,108,FALSE)の110の範囲が変わってしまいます。分りづらいですが宜しくお願いします。

回答No.7

keithin · ベストアンサー

行挿入・行削除しても「正しく追従させる」には

方法１：
=INDEX($110:$110,MATCH(AS$223,$3:$3,0))
とします

方法２：
=HLOOKUP(AS$223,$D$3:$AH$110,ROWS($D$3:$AH$110),FALSE)
とします。

お礼

お返事遅れてm(__)mご回答有難う御座いました。方法1、2共にOKでした！もっと勉強します！

回答No.6

noname#204879

=HLOOKUP（AS$223,$D$3:$AH$110,108,FALSE)

１．3～108行目全体を選択
２．[名前ボックス]に(例えば) raterate と入力して、Enterキーを「エイヤッ！」と叩き付け
３．次のように式を変更すれば如何？
　　=HLOOKUP（AS$223,$D$3:$AH$110,108,FALSE)
　　　　　　　　　　　　　　　　　 ↓
　　=HLOOKUP（AS$223,$D$3:$AH$110,ROWS(raterate),FALSE)

お礼

お返事遅れて済みませんでしたm(__)m兎に角無知で…でも参考になりました。もっと勉強が必要ですね。

回答No.5

FEX2053

あの・・・みなさんOffsetを使う方法を書いてますが、これ
を使うと確かに参照関係は「ずれません」けど、102行目
を消した時でも、D3:AH110を参照してしまいます。

要は、103行目で参照された結果を、行削除することで
102行目参照に変更する（=参照範囲をD3:AH109にする）
必要があるのかどうか・・・って話です。

変更する必要がないなら、Offsetを使うというのは良い手
ですけど・・・。

お礼

お返事遅れて済みません。変更する必要ありますね。色々試したがダメでした。無知なもので…有難う御座いました。

回答No.4

chie65535

例えば、HLOOKUP関数が書いてある、自分自身のセルが「C2」だとしましょう。

=HLOOKUP（AS$223,$D$3:$AH$110,108,FALSE)
を
=HLOOKUP（OFFSET($C$2,221,42,1,1),OFFSET($C$2,1,1,108,31),108,FALSE)
にしましょう。

C2自身が移動しない限り、必ず同じ範囲、同じ場所を参照します。行や列の追加削除には影響されません。

但し、行や列の追加や削除で、C2自身が移動した場合、範囲と場所もC2に連動して移動します。

OFFSET関数の基準点を「絶対に動かないセル」に、例えば「$A$1」とかにすれば、C2セルが移動しても連動せず、必ず同じ範囲を見るようになります。

お礼

お返事遅れてm(__)m兎に角無知なもので…中々巧くいきませんでしたが勉強になりました。

回答No.3

tom04

こんにちは！
＞110の範囲が変わってしまいます
とありますので、行・列の挿入及び削除を行っても
常に範囲は D3:AH110 に固定したい！という解釈で・・・

=HLOOKUP(AS$223,INDIRECT("$D$3:$AH$110"),108,FALSE)
としてみてはどうでしょうか？

※　検証していませんので、お望み通りにならなかったらごめんなさいね。m（_ _）m

お礼

お返事遅くなって済みませんm(__)m良いヒントになりました。有難う御座いました。

回答No.2

chie65535

OFFSET関数を使うと、行や列の追加や削除をしても、参照範囲は変わりません。

お礼

お返事遅れてm(__)m有難う御座います。なかなか巧く行きません。

回答No.1

FEX2053

挿入・削除するとき、「３行目、110行目」以外の行で、
行単位で挿入・削除すれば、参照関係はずれません。
逆にいえば、それ以外の方法で挿入削除をすると、
参照関係は必ず崩れます。

関数などで逃げる方法はありませんので、それ以外の
方法でうまく削除したいなら、削除できる手順をマクロに
登録するとか・・・という方法になります。

お礼

早速のご回答有難う御座います。やはり関数では無理でしたか！？m(_ _)m