こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルの「IF+VLOOKUP」に関してのよくある問題で、意味のわからない相対参照があります

「1.セル【C18】にセル【B18】の「商品コード」に対応する「商品名」を返す数式を入力して下さい。「商品一覧」の表を参照すること。
次に、「商品コード」が入力されていない場合は、「商品名」にエラーが表示されないように数式を修正して下さい。(シートに表があり、IF+VLOOKUPを使う問題です)

2.セル【C18】の数式をもとに請求書の表を完成させて下さい」(←「【C18】の下の行も同じように、商品コードから商品名を表示させれるように完成させなさい」っていう問題です)

上記のような問題があったのですが、
数式を入力して下にオートフィルして
完成させたのですが、【問題の解答】を見た限りでは
「セル【C18】を「=IF($B18="","",VLOOKUP($B18,$H$18:$J$29,2,FALSE))」と修正する」
と書いてありました。

ですが、数式の「$B18」の部分で「列を固定にする」
意味はあるのでしょうか?
「$」はなくてもちゃんと機能してますし、
表も完成してるように思うのですが・・・

あと、これは商品コードが空白の場合にはエラーが
出ないのでいいのですが、商品コードとは関係ない
文字列も入れた場合(間違った文字列を入れた場合)に
エラーがでないようにする事もできますでしょうか?

投稿日時 - 2004-05-28 11:47:18

QNo.873169

困ってます

質問者が選んだベストアンサー

こんにちは。maruru01です。

表にないデータを入力した場合の対処方法の別解です。

=IF(LEN($B18),IF(COUNTIF($H18:$H29,$B18),VLOOKUP($B18,$H$18:$J$29,2,FALSE),""),"")

ちなみに、個人的には、巷にあるExcel関数の問題集の解答は、鵜呑みにしてはいけないと思っています。
(解答作成者のレベルによりますから。)
また、解答は1とおりしかないわけでもない場合が多いですし。
(私の上式も、ちょっとアレンジしてあります。)

投稿日時 - 2004-05-28 12:30:34

お礼

maruru01さん、お世話になっておりますm(_ _)m

=IF(LEN($B18),IF(COUNTIF($H18:$H29,$B18),VLOOKUP($B18,$H$18:$J$29,2,FALSE),""),"")

ずばり、これ完成系でした!

>個人的には、巷にあるExcel関数の問題集の解答は、鵜呑みにしてはいけないと思っています。

私もこれはよく思ってました!自分が正解してると思ってた問題では、解答(手順)が違ってたりして・・・。
そういう時には、本当に合ってるのか、もしくは減点になってはいないものか心配になります^^;
逆に複数の解答例が書いてある問題集は、とても親切に思ったりしています!

改めまして、お礼申し上げますm(_ _)m

投稿日時 - 2004-05-28 13:50:33

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

表に見つからないときのエラーは#N/Aです。
エラーを聞く関数は
(A)ISERR
(B)ISERROR
とあり、上記(A)は#N/A以外のエラー値のときTRUEになります。
従って(B)を使わないとなりません。
検索列が空白の場合も#N/Aになりますから、両ケース
あわせて
=IF(ISERROR(VLOOKUP(・・)),"",VLOOKUP(・・))
で良いでしょう。
このVLOOKUP(・・)は2度書きになりますが、どうしようもないようです。
(・・部は略)

投稿日時 - 2004-05-28 12:21:23

お礼

imogasiさん、再度御回答ありがとうございます<(_ _)>

一度、

> =IF(ISERROR(VLOOKUP(・・)),"",VLOOKUP(・・))

にVLOOKUPの引数(?)を入れてみまして、試してみました!

「空白」と「商品コード以外の文字」を入力した場合は、エラーが出ずによかったです。ですが、商品コードを入れた場合にはD列にまで商品名が出てきてしまいました。

ISERRORについて私も調べてみようと思います!

投稿日時 - 2004-05-28 13:40:34

ANo.3

 この設問だけなら$Bは必要ないですね。

 でも、実際にご質問のような表を作るときには、商品名のほかに、その商品の規格や単価をD,E列に入れることになります。
 その場合、$Bと固定しておけば、C列の式をコピー、編集すれば簡単に望んだデータを表示できるので、$Bとしたほうがより実践的でしょう。

 商品コードエラーを非表示にするには、ISERR関数を使いLookup関数がエラーを返した場合に、Nullを表示させます。
 簡単に処理するには、D列に
=IF(ISERR(C18),"",C18)
などと入れて(式は検証してません)、C列を非表示にするのがよいと思います。
 IFがネストするとバグり安いので、作業セルを作る方が確実と思います。

投稿日時 - 2004-05-28 12:02:58

お礼

>この設問だけなら$Bは必要ないですね。

ですよね!
解答としては間違ってないのでは?っとは思いましたが、でもこれをしないと実際の試験では×(か減点)になりそうなので、今後は一応つけてこうと思います^^;

あと、横の列にも同じようにコピーしてやってみましたが、やはり編集しやすかったです!!!
これからはこの点に関しても注意して相対参照(←タイトルにも書いちゃいましたが私が聞きたかったのは「複合参照」の間違いだったみたいです。すみません)を使ってこうと思います。

>=IF(ISERR(C18),"",C18)

これは失敗してしまいました。どこか修正すれば出来たのかもしれませんが、「ISERR」の関数は、私は初めて使う関数だったので、私ではとても手におえませんでした(T_T)解明できなくてすみません。

>IFがネストするとバグり安いので、作業セルを作る方が確実と思います。

これも初耳です!
とても勉強になりました!!!ありがとうございました<(_ _)>

投稿日時 - 2004-05-28 13:24:29

ANo.2

「列を固定にする」意味は、商品名から単価部分へのコピーを考慮しているからです。
商品名に作成した式を、単価にコピーすれば、引数の列番号だけを書き換えるだけで、参照できます。

「商品コードとは関係ない文字列も入れた場合」も空欄にしたいのであれば、
ISERROR関数を使う方法があります。
IFとISERRORとVLOOKUPを組み合わせます。

式は長くなりますが。
=IF(ISERROR(VLOOKUP($B18,$H$18:$J$29,2,FALSE)),"",VLOOKUP($B18,$H$18:$J$29,2,FALSE))

で、どうでしょう?

投稿日時 - 2004-05-28 11:58:12

ANo.1

>数式の「$B18」の部分で「列を固定にする」意味はあるのでしょうか?
確かに行方向へのコピーなら支障はありません。
でも列方向にコピーする可能性を考えればあった方がいいかもしれません。

>間違った文字列を入れた場合
方法1
B18のセルで「データ」「入力規則」で「リスト」を選択
「元の値」に「=$H$18:$H$29」として
▼でリストから選択するようにする。
方法2
=IF($B18="","",IF(ISNA(VLOOKUP($B18,$H$18:$J$29,2,FALSE)),"存在しません",VLOOKUP($B18,$H$18:$J$29,2,FALSE))
のようにしてください。

投稿日時 - 2004-05-28 11:56:15

お礼

mshr1962さん、先程はお世話になりました<(_ _)>

二つの方法試してみました!
方法1は、他の場面でも役に立ちそうでいい発見でした!!

方法2の関数は、初めてみたのですが、一度使ってみましたら、「存在しません」と表示され、これもいい方法ですね^^この関数もどこかで役に立つかもしれません!

再び御回答下さいましてありがとうございましたm(_ _)m

投稿日時 - 2004-05-28 13:07:47

あなたにオススメの質問