エクセルの「ＩＦ＋ＶＬＯＯＫＵＰ」に関してのよくある問題で、意味のわからない相対参照があります

質問

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

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

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

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

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

回答No.5

maruru01 · ベストアンサー

こんにちは。maruru01です。

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

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

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

お礼

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

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

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

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

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

改めまして、お礼申し上げますｍ（＿　＿）ｍ

回答No.4

imogasi

表に見つからないときのエラーは＃Ｎ／Ａです。
エラーを聞く関数は
（Ａ）ＩＳＥＲＲ
（Ｂ）ＩＳＥＲＲＯＲ
とあり、上記（Ａ）は＃Ｎ／Ａ以外のエラー値のときＴＲＵＥになります。
従って（Ｂ）を使わないとなりません。
検索列が空白の場合も＃Ｎ／Ａになりますから、両ケース
あわせて
=IF(ISERROR(VLOOKUP(・・)),"",VLOOKUP(・・))
で良いでしょう。
このVLOOKUP(・・）は２度書きになりますが、どうしようもないようです。
（・・部は略）

お礼

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

一度、

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

にVLOOKUPの引数（？）を入れてみまして、試してみました！

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

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

回答No.3

Mozisan

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

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

　商品コードエラーを非表示にするには、ＩＳＥＲＲ関数を使いＬｏｏｋｕｐ関数がエラーを返した場合に、Ｎｕｌｌを表示させます。
　簡単に処理するには、Ｄ列に
=IF(ISERR(C18),"",C18)
などと入れて（式は検証してません）、Ｃ列を非表示にするのがよいと思います。
　ＩＦがネストするとバグり安いので、作業セルを作る方が確実と思います。

お礼

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

ですよね！
解答としては間違ってないのでは？っとは思いましたが、でもこれをしないと実際の試験では×（か減点）になりそうなので、今後は一応つけてこうと思います＾＾；

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

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

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

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

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

回答No.2

kuronekozizi

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

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

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

で、どうでしょう？

回答No.1

mshr1962

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

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

お礼

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

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

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

再び御回答下さいましてありがとうございましたｍ（＿　＿）ｍ