エクセルで列を消したら、VLOOKUPが在庫を単価として返した。エラーは出ないんだよ

おはよう、後輩A君。

商品の表から単価を引いてくる VLOOKUP（ブイルックアップ）を使っているとき、その表に列を足したり、消したりしたことはないかな。

先に答えを言うね。⭐ 単価の列を消したら、VLOOKUP は在庫の数（48）を、単価として返したんだ。⚠️ エラーは出なかったんだよ。同じ表を使うほかの式は、ちゃんとエラーで知らせてくれたのに、なんだ。

11月3日に、Mac版のエクセルで試したよ。表は命令（プログラム）で作って、列を入れたり消したりするのも命令（プログラム）でしたんだ。商品も数字も、試し用に作ったものだよ。

1. こんな表と、6つの式で試したんだよ

まず、「商品」のシートに、こんな表を作ったんだ。

	A　コード	B　商品名	C　単価	D　在庫
2行目	A001	ボールペン	120	35
3行目	A002	ノート	180	12
4行目	A003	クリップ	250	48
5行目	A004	ふせん	300	7
6行目	A005	ファイル	450	20

別の「式」のシートの B1 に、探すコード A003（クリップ）を入れたよ。そして、クリップの単価を引く式を、6とおり並べたんだ。

① =VLOOKUP($B$1,商品!$A$2:$D$6,3,FALSE)　→　ふつうの VLOOKUP
② =XLOOKUP($B$1,商品!$A$2:$A$6,商品!$C$2:$C$6)　→　XLOOKUP（エックスルックアップ）
③ =INDEX(商品!$C$2:$C$6,MATCH($B$1,商品!$A$2:$A$6,0))　→　INDEX と MATCH の組み合わせ
④ =VLOOKUP($B$1,商品!$A$2:$D$6,MATCH(“単価”,商品!$A$1:$D$1,0),FALSE)　→　VLOOKUP の「3」を MATCH にしたもの
⑤ =VLOOKUP($B$1,商品!$A$2:$D$6,4,FALSE)　→　こちらは在庫を引く式
⑥ =VLOOKUP($B$1,商品!A:C,3,FALSE)　→　列まるごとで書く形

VLOOKUP の中身を、言葉で言い換えておくね。

範囲　＝ 探す表の場所。①なら $A$2:$D$6（A列からD列まで）
列番号　＝ 範囲の左から何列目を返すか。①の「3」は、A・B・Cと数えてC列の単価
FALSE　＝ ぴったり同じコードだけを探す、という指定

FALSE のことは、VLOOKUP の4番目の記事に書いたよ。⑥の「A:C,3」は、関数を Claude に作ってもらう記事で出てきた =VLOOKUP(C2,商品マスタ!A:C,3,FALSE) と同じ形なんだ。

変える前は、①〜④と⑥が250（クリップの単価）、⑤が48（クリップの在庫）だったよ。このエクセルでは XLOOKUP も使えたんだ。

試し方は、こうしたよ。

・変えることを1つ決めて、「商品」と「式」のシートを1組ずつ用意した
・1組で変えるのは1つだけ（列を1つ消す、列を1つ入れる、など）

⚠️ 画像には「商品_S6」のようなシート名が写っているけれど、これは試しのために分けた名前だよ。記事では「商品」「式」と書くね。

2. ⚠️ 単価の列を消したら、VLOOKUP だけが在庫の48を返したんだよ

最初に、単価の列（C列）を消したんだ。表はこうなったよ。

単価の列が消えて、在庫が C列に来たんだね。式のシートは、こうなったよ。

① VLOOKUP（3）　→　⚠️ 48（在庫。エラーなし）
② XLOOKUP　→　#REF!
③ INDEX と MATCH　→　#REF!
④ VLOOKUP＋MATCH　→　#N/A
⑤ VLOOKUP（4・在庫）　→　#REF!
⑥ VLOOKUP（A:C,3）　→　#REF!（範囲は A:B になっていた）

エラーの記号も、短く言い換えておくね。

#REF!　＝ 行き先がない。指していた場所が消えた
#N/A　＝ 探したものが見つからない

⭐ 同じ「単価の列を消す」なのに、②〜⑥はエラーで知らせてくれたんだ。①だけが、在庫の48を、単価のような顔をして返したんだよ。

エラーのセルには、左上に緑の小さな三角が出ていたよ。⚠️ でも①の48には、出ていなかったんだ。見た目では、ふつうの数と変わらないんだよね。

前に、#REF! と #VALUE! の記事で、式が使っていた列を消すと #REF! になる、と書いたね。今日の①は、その例外なんだ。列を消したのに、#REF! にならなかったんだよ。

3. 範囲は縮むのに、列番号の「3」は3のままなんだよ

数式バー（上の式の欄）を見ると、理由が分かるよ。

変える前　=VLOOKUP($B$1,商品!$A$2:$D$6,3,FALSE)
単価の列を消した後　=VLOOKUP($B$1,商品!$A$2:$C$6,3,FALSE)

⭐ 範囲は、列が1つ減ったぶん、$D$6 から $C$6 に縮んだんだ。でも、列番号の 3 は 3 のままなんだよ。

縮んだ範囲の3列目は、コード・商品名・在庫と数えて、在庫なんだ。だから在庫の48が返ってきたんだね。

ほかの式がエラーになったのも、同じように読めるよ。

⑤ 範囲が3列になったのに、4列目を返そうとした　→　#REF!
⑥ A:C が A:B（2列）になったのに、3列目を返そうとした　→　#REF!
②③ 指していた単価の列そのものが消えた　→　#REF!
④ 見出しの行に「単価」が見つからない　→　#N/A

⑤と⑥のように、列番号が範囲の外を指すと #REF! になる話は、VLOOKUP で #N/A が出る記事の7章（列番号を4にした）と同じ形だよ。

行を足したのに合計が変わらない記事では、範囲が伸びなかったのが原因だったね。今日はその反対で、範囲はちゃんと伸びたり縮んだりするのに、列番号の数字は動かないんだ。

⇒ だから、返す列より左で列を足したり消したりすると、VLOOKUP はエラーを出さずに、別の列の値を返してしまうことがあるんだよ（⑤⑥のように範囲の外を指すと #REF!）。

4. ⚠️ 列を足したときも、仕入値の150を単価として返したんだよ

次は、商品名と単価のあいだ（C列）に列を1つ入れて、「仕入値」を入れたんだ。

仕入値は、上から 80／110／150／190／260 にしたよ。クリップの仕入値は150だね。式のシートは、こうなったんだ。

① VLOOKUP（3）　→　⚠️ 150（仕入値。エラーなし）。範囲は $A$2:$E$6 に伸びていた
② XLOOKUP　→　250
③ INDEX と MATCH　→　250
④ VLOOKUP＋MATCH　→　250
⑤ VLOOKUP（4・在庫）　→　⚠️ 250（在庫ではなく単価）
⑥ VLOOKUP（A:C,3）　→　⚠️ 150（範囲は A:D になっていた）

画像の左の名前（「⑥VLOOKUP A:C,3」など）は、変える前の式につけた名前だよ。

⭐ ここでも、範囲は $A$2:$E$6 に伸びたのに、列番号は 3 のままだったんだ。伸びた範囲の3列目は仕入値だね。

⚠️ もしこの単価で見積もりを作っていたら、見積もりが仕入値で計算されることになるんだよ。クリップなら、250 のはずが 150 で、100 少ない（250−150＝100）んだ。

⑤も、在庫を引くつもりが、4列目にずれてきた単価の250を返したよ。これもエラーは出ていないんだ。

もう1つ、同じC列に空のまま列を入れたときも試したよ。

① VLOOKUP（3）　→　⚠️ 0（空の列を指した）
⑥ VLOOKUP（A:C,3）　→　⚠️ 0
②③④⑤　→　250

0 も、エラーではないんだよね。

5. 単価より右に足すと①は無事。でも在庫を引く式が0になったんだよ

じゃあ、単価の右（D列）に列を入れたらどうなるか。これも試したよ。

① VLOOKUP（3）　→　⭐ 250（無事）
②③④　→　250
⑤ VLOOKUP（4・在庫）　→　⚠️ 0（在庫のはずが、入れた空の列を指した）
⑥ VLOOKUP（A:C,3）　→　250（範囲は A:C のまま）

①は単価（3列目）を返すから、3列目より右に足しても困らないんだ。でも⑤は在庫（4列目）を返すから、単価と在庫のあいだに足すと、ずれたんだよ。

次は、商品名の列（B列）を消したときだよ。

① VLOOKUP（3）　→　⚠️ 48（在庫）
②③④　→　250
⑤ VLOOKUP（4・在庫）　→　#REF!
⑥ VLOOKUP（A:C,3）　→　#REF!（範囲は A:B になっていた）

⚠️ 単価の列には何もしていないのに、①は在庫の48を返したんだ。単価より左の列を消したからだね。

⑤のように列番号4の式がある表で商品名の列を消すと、#REF! になったよ。これは、#N/A の記事の7章と同じ形なんだ。

⇒ ずれるのは、範囲の左はしから返す列までのあいだで、列を足したり消したりしたときなんだよ。返す列より右に足したときは、その式は無事だったんだ。

6. 6つの式をくらべると、こうなったよ

最後に、見出しの「単価」を「売価」に変えたときも試したんだ。全部まとめると、こうなるよ。

変えたこと	① VLOOKUP 3	② XLOOKUP	③ INDEX/MATCH	④ VLOOKUP＋MATCH	⑤ VLOOKUP 4（在庫）	⑥ A:C,3
変える前	250	250	250	250	48	250
単価の列（C列）を消す	⚠️ 48	#REF!	#REF!	#N/A	#REF!	#REF!
C列に列を入れて仕入値	⚠️ 150	250	250	250	⚠️ 250	⚠️ 150
C列に列を入れて空のまま	⚠️ 0	250	250	250	250	⚠️ 0
単価の右（D列）に列を入れる	250	250	250	250	⚠️ 0	250
商品名（B列）を消す	⚠️ 48	250	250	250	#REF!	#REF!
見出しの「単価」を「売価」に	250	250	250	⚠️ #N/A	48	250

⚠️ の太字が、エラーを出さずに違う値を返したところ（と、④の弱いところ）だよ。

⭐ 列番号を数字で書いた①⑤⑥は、列を足しても消しても、数字が動かなかったんだ。
⭐ ②XLOOKUP と ③INDEX と MATCH は、違う値を返したことは一度もなかったよ。単価の列そのものを消したときだけ、#REF! で知らせてくれたんだ。
⭐ ④（列番号を MATCH で見出しから数える形）も、列を足し引きしたときは違う値を返さなかったよ。⚠️ でも、見出しの名前を変えると #N/A になったんだ。ここが弱いところだね。

「VLOOKUP」はもう覚えるな、の記事で、XLOOKUP は列番号を指定しなくていい（ずれるリスクがない）と1行だけ書いたね。今日の試し（Mac版・1列ずつ）では、たしかに違う値を一度も返さなかったよ。

7. 私なら、3つの段で守るよ

じゃあ、A君はどうしたらいいか。私なら、こうするよ。

① 表を直したら、答えを1つ、表の数と見くらべる
② 列を足す・消すときは、返す列までのあいだかどうかを見る
③ これからも使い続ける式だけ、「3」を MATCH(“単価”,見出しの行,0) に差し替える

① 今日の①は、エラーを出さなかったよね。緑の三角も出なかったんだ。だから、表に列を足したり消したりしたら、式の答えを1つだけでも表と見くらべようか。クリップなら、表の単価は250、式の答えも250か。それだけで、48や150に気づけるんだよ。

② 5章のとおり、ずれるのは返す列までのあいだで足し引きしたときだったよ。単価の右に足したら①は無事、でも在庫を引く⑤は0になったんだ。列を入れる前に、その表を使っている式が何列目までを返しているかを見ておこうか。

③ これからも使い続ける表なら、④の形にしておく手があるよ。

=VLOOKUP($B$1,商品!$A$2:$D$6,MATCH(“単価”,商品!$A$1:$D$1,0),FALSE)

MATCH は、見出しの行から「単価」を探して、左から何列目かを数えてくれるんだ。だから列を足しても消しても、数え直してくれるんだよ。⚠️ ただし、見出しの名前を変えると #N/A になるよ。見出しを変えるなら、式の「単価」も一緒に直そうか。MATCH は古いエクセルにもある関数だよ。

XLOOKUP や INDEX と MATCH も、今日の試しでは違う値を一度も返さなかったよ。ただ、#N/A の記事のQ1に書いたとおり、XLOOKUP は古いエクセルでは使えないんだ。私は、渡すファイルは VLOOKUP、自分用は XLOOKUP で分けているよ。

8. ⚠️ 今日測れなかったこと

・列を入れたり消したりしたのは、命令（プログラム）だけだよ。手で列を入れたり消したりしたときは、試していないんだ
・測ったのはMac版のエクセルだよ。Windows版は試していないんだ
・テーブルにした表（列を名前で指す書き方）は、試していないよ
・足したり消したりしたのは、1列ずつだよ。2列以上をまとめて足す・消すときは試していないんだ
・表も式も、命令（プログラム）で作ったよ。商品と数字は試し用に作ったものなんだ

9. エクセルの記事、これで31本目だよ

前の30本目は、人日の合計と人月が合わない話だったね。あれは、計算は合っていて、丸める場所が違うだけだったんだ。

今日の話は、29本目の行を足したのに合計が変わらない話と、対になっているよ。

29本目　→　行を足したのに、合計の範囲が伸びなかった
31本目（今日）　→　列を足したら範囲は伸びたのに、列番号は伸びなかった

どちらも、表の形を変えたあとに、式がついてこなかった話なんだよね。

結論：VLOOKUP の列番号は、列を足しても消しても動かない。表を直したら、答えを1つ見くらべようか

今日試した結果をまとめるとこうだよ。

① 単価の列を消したら、VLOOKUP（3）は在庫の48を返した。エラーも緑の三角も出なかった
② 同じとき、XLOOKUP・INDEX と MATCH・列番号4・A:C の形は #REF!、MATCH の形は #N/A で知らせてくれた
③ 列を入れて仕入値を入れたら、VLOOKUP（3）は仕入値の150を単価として返した
④ 理由は、範囲は伸び縮みするのに、列番号は数字のままだから
⑤ ずれるのは、返す列までのあいだで足し引きしたとき
⑥ 列番号を MATCH にすると数え直してくれる。でも見出しの名前を変えると #N/A

単価の列を消したとき、式の②〜⑥はエラーの記号と緑の三角で知らせてくれたけれど、①の48には、どちらも出なかったんだよ。

表を直したら、答えを1つだけ見くらべる。それだけで、48や150を見逃さずにすむんだよ。

よくある質問

Q1. XLOOKUP に替えれば、もう気にしなくていいですか

今日の試しでは、XLOOKUP は列を足しても消しても、違う値を一度も返さなかったよ。単価の列そのものを消したときは #REF! で知らせてくれたんだ。⚠️ ただ、XLOOKUP は古いエクセルでは使えないんだよね。#N/A の記事のQ1のとおり、私は渡すファイルは VLOOKUP、自分用は XLOOKUP で分けているよ。⇒ 渡すファイルなら、VLOOKUP のまま「3」を MATCH にする手もあるよ。

Q2. 列番号を MATCH にすれば、絶対にずれませんか

今日の試しでは、列を足しても消しても、違う値は返さなかったよ。⚠️ でも、見出しの「単価」を「売価」に変えたら #N/A になったんだ。単価の列を消したときも #N/A だったよ。違う値を返すのではなく、エラーで知らせてくれる、ということなんだ。⇒ 見出しの名前を変えるときは、式の中の「単価」も一緒に直そうか。

Q3. 列まるごと（A:C）で書けば、安全ですか

⚠️ 安全ではなかったよ。商品名と単価のあいだに仕入値の列を入れたら、A:C が A:D に伸びて、列番号は3のまま。答えは仕入値の150だったんだ。空の列を入れたときは0だったよ。列まるごとでも、列番号の数字が動かないのは同じなんだよね。

【本日のミッション：表を直したら、答えを1つ見くらべてみようか】

• ☐ 自分の VLOOKUP の式を開いて、列番号（3や4の数字）が、範囲の何列目を指しているか数えてみようか
• ☐ 表に列を足したり消したりしたら、式の答えを1つ、表の数と見くらべてみようか
• ☐ これからも使い続ける式は、列番号を MATCH(“単価”,見出しの行,0) に差し替えられるか見てみようか
• ☐ （後輩A君へ）エラーが出ないのは、合っているしるしではないんだよ。表を直したら、1つだけ確かめてみようか