[[20080117224014]]　『vlookupの範囲について』（るし）　ページの最後に飛ぶ

『vlookupの範囲について』（るし）

 A列にコード、B列以下に品名、価格などが入力してあるリストファイルから 
 vlookupを用いてコードを入力することにより、情報を抽出しております。
 リストのブックと、抽出用のブックは別にしてあります。

 ここで、今まではvlookupの範囲を$A:$Hなどとしていたのですが、 
 列を追加したときなどに、自動で範囲が広がるように 
 リストファイルのデータが入力してある範囲を指定しようと思って 
 以下のようにリストファイルで名前の定義を行いました。

 =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA(1:1)) ←これを「リスト」と定義します。

 その後、vlookupの範囲を「リスト」に変更したところ、 
 リストファイルを開いているときは正しく機能するのですが、 
 リストファイルを開いていない状態で、抽出用ファイルを開くと 
 #REF!というエラーが出てしまいます。この状態でリストファイルを開くと正しく表示されます。 
 今まではリストファイルを開いていなくても正しく表示されておりました。
 このエラーを解消する方法はありますでしょうか？

 また、名前の定義を行わずに、vlookupの範囲に直接offsetを用いてリスト範囲を 
 取得する方法でも同じようなエラーが出てしまいました。

 よろしくお願いいたします。windows xp, excel2003です

 >ここで、今まではvlookupの範囲を$A:$Hなどとしていたのですが、 
 >今まではリストファイルを開いていなくても正しく表示されておりました。
 この２つの”今まで”とは同じ意味ですか？

 単にリストファイルが閉じているとリスト範囲が取得できず（COUNTA関数が機能しないので）、
 「リスト」と定義できないのでは？

 ・・・なんて思ってみたのですけど。
 違ってたらごめんなさい。
 (じゅんじゅん)

 書き方がわかりにくくてすみません。 
 その２つの”今まで”は同じ意味です。 
 つまり、COUNTA関数の仕様でファイルを開いていないと範囲を保持できないということですかね・・・。
（るし）

 >つまり、COUNTA関数の仕様でファイルを開いていないと範囲を保持できないということですかね・・・。
 試してはいないですが、固定された範囲なら開かずともわかりますが、可変するならば開かなければ
 わからないと思ってみたのです。
 よくＶＢＡで”ファイルを開かないで”と聞きますが、実際には開いていたりするようですから。
 (じゅんじゅん)

 なるほど、回答ありがとうございました。 
 リストの有効範囲を自動で取得する方法を模索していたのですが、 
 ファイルを開いている状態なのは前提条件のようですね。
 じゅんじゅん様、ありがとうございました。（るし）

 おっと、質問者さんと衝突して仕舞いましたが・・・。

 COUNTA関数は参照先のブックを閉じていても参照出来ますが
 OFFSET関数は参照先のブックを開いておく必要があります。

 何れにせよ、OFFSET関数を使う限りは
「ファイルを開いている状態なのは前提条件」
 ・・・と言う事になると思いますが。

 (HANA)

 HANA様、コメントありがとうございます。 
 COUNTA関数とOFFSET関数の仕様を教えていただき、助かります。 
 ところで、それぞれの関数が参照先のブックを閉じていても 
 参照出来るのか出来ないのかという情報はどこでわかるのでしょうか？ 
 ヘルプには記載されていないようですし・・・。（るし）

 HANAさん
 >COUNTA関数は参照先のブックを閉じていても参照出来ますが
 >OFFSET関数は参照先のブックを開いておく必要があります。
 OFFSETの方だったのですね。
 勉強になりました。
 (じゅんじゅん)

 そうですね・・・書いてないですよね。
 私はこの学校に来るようになって仕入れた知識ですが・・・。

 あとは、「その様な関数が存在する」と言う事を知っていれば
 ブック間参照する場合は気をつけて確認をしてみれば良いと思います。
 参照先ブックを閉じて再計算しただけで、今まで正しく計算出来ていたのが
 出来なくなったら、参照先を開いておく必要のある関数が使われている
 と考え、その関数を突き止め、使わない様にするのが良いと思います。

 過去に何度か有ったのは、検索関数を使用する際のエラー処理として
 COUNTIF関数を使用した場合　この関数は参照先ブックを開いて於く必要があるので
 実際の検索結果は求める値を返すのに、全体としてエラーとなって仕舞ったりします。
 エラー処理にCOUNTIF関数を使わなければ良いので、何が問題でエラーになるのか
 見極める必要が出てくると思います。
 　中のVLOOKUP関数が参照先を開かなくても結果を返すとき
 　IF(COUNTIF(・・・),VLOOKUP(・・・),"")では参照先を開く必要がありますが
 　IF(ISNA(VLOOKUP(・・・)),"",VLOOKUP(・・・))は、開く必要が有りません。

 今回の場合で、私がやるとするなら
 ＞vlookupの範囲を$A:$H
 としていた場合、8列有りますよね。
 見出し行等が有る場合は、その行をCOUNTA関数でしらべ、
 結果が8以上だった場合は、H列より多く入力されている事になります。
 どこかのセルをチェック用のセルとして、IF関数でも埋め込んでおくと
 範囲が増えているかどうか、参照先のブックを開かなくても分かると思います。

 自動で範囲が増えるわけではないですし
 列が挿入されていて数式をA:Iに変更した場合は
 チェック用のセルに埋め込んだIF関数の数字も変更する必要が有りますが。

 (HANA)

 HANA様、今回はoffsetが原因ということもわかり、大変勉強になりました。 
 色々教えていただきありがとうございました。（るし）

コメント返信：

[ 一覧(最新更新順) ]

[[20080117224014]] 『vlookupの範囲について』（るし） ページの最後に飛ぶ

[[20080117224014]]　『vlookupの範囲について』（るし）　ページの最後に飛ぶ