おはよう、後輩A君。
前回、VLOOKUPで#N/Aが出る6つの原因を書いたよね。あの最後に、こう書いたんだ。
「#N/Aが出ていないからといって、合っているとは限らない」
今日はその話だよ。
⚠️ 思えば、#N/Aはずいぶん親切なんだよね。赤い字で「見つからなかったよ」と教えてくれるんだ。気づけるんだよ。
今日の話は、何も言わずに、違う答えを返してくるほうなんだ。
実際にエクセルで測ったら、8件のうち5件で違う単価が返ってきたんだよ。エラーは1つも出なかったんだ。原因は、式のいちばん最後に付けるたった1語だったんだよね。
1. 5件さがしたら、1件だけ静かに間違ったんだよ
前回と同じ表を使うね。
商品コード 商品名 単価
2行目 103 マウス 1200
3行目 101 キーボード 3500
4行目 105 モニター 18000
5行目 102 USBメモリ 980
6行目 104 ケーブル 450
⚠️ コードが順番に並んでいないところを見ておいてね。今日はこれが主役なんだ。
この表に、4番目を書かない式を入れて、5つのコードを順番に探させたよ。
=VLOOKUP(101,$A$2:$C$6,2) ← 最後の FALSE が無い
測った結果はこうだったんだ。右が正解だよ。
101 → ⚠️ #N/A （正解：キーボード）
102 → ⚠️ #N/A （正解：USBメモリ）
103 → マウス 合っている
104 → ⚠️ キーボード （正解：ケーブル）
105 → モニター 合っている
⚠️ 104を探したのに、キーボードが返ってきたんだよ。
#N/Aの2件は、まだいいんだ。おかしいと分かるからね。
でも104のところは、ちゃんと商品名が入っているんだよ。赤くもならないし、記号も出ないんだ。見ただけでは絶対に気づけないんだよね。
5件のうち1件だけが静かに間違っているんだ。これがいちばん怖いところだよ。
2. なぜこうなるのか。エクセルは「並んでいる前提」で探しているんだよ
4番目を省略すると、エクセルは「近いものでいいよ」モードで動くんだ。
そしてこのモードのとき、エクセルは表が小さい順に並んでいるものとして探すんだよ。
辞書を引くときの動きだと思ってほしいんだ。
辞書の真ん中を開く → 「探している語より後ろだな」 → 前半だけ見ればいい
半分ずつ捨てていくから速いんだよね。⚠️ でもこれが成り立つのは、辞書が順番に並んでいるからなんだ。
⚠️ 順番に並んでいない紙の束で同じことをやると、こうなるんだよ。
捨てた側に、探し物が入っていた → 見つからない（#N/A）
絞り込んだ行き止まりに、別のものがあった → それを答えにしてしまう
104を探したとき、エクセルは101の行で行き止まりになったんだ。そしてそこにあったキーボードを答えとして返したんだよ。
⚠️ ここで大事なのは、「104ならキーボードが返る」と覚えても意味がないということなんだ。どこで行き止まりになるかは表の並び方で変わるからね。表に1行足しただけで、返る答えが変わるんだよ。
「どう間違うか」は予想できないんだ。だから「間違いに気づく」という方法では守れないんだよね。
3. ⚠️ 並べ替えた表だと、今度は全部正解するんだよ
ここが厄介なところだよ。
同じデータをコードの小さい順に並べ替えて、同じ式で測ってみたんだ。
101 → キーボード 合っている
102 → USBメモリ 合っている
103 → マウス 合っている
104 → ケーブル 合っている
105 → モニター 合っている
⚠️ 全部合うんだよ。4番目を書いていないのに、だよ。
⚠️ だからこういうことが起きるんだ。
「前にこの書き方で作ったけど、問題なかったよ」
→ その表が、たまたま並んでいただけ
そして表は並び替えられるんだよね。誰かが並べ替えボタンを押した日から、答えが変わりはじめるんだ。式は何も変わっていないのにね。
⚠️ 「うちでは大丈夫だった」が、いちばん危ない証言なんだよ。今日たまたま合っているだけだからね。
4. ⚠️ 表に無い数字を探しても、答えが返ってくるんだよ
もう一つ測ってきたよ。表に存在しないコードを探させたんだ。
106 を省略で探す → ⚠️ モニター（106なんて商品は無いのに）
999 を省略で探す → ⚠️ モニター
106 を FALSE で探す → #N/A（こっちが正しい反応だね）
⚠️ 無いものを聞いたのに、答えが返ってきたんだよ。
「近いものでいいよ」モードは、その値以下でいちばん大きいものを返す仕組みなんだ。106は表の最大105より大きいから、105のモニターを返したんだよね。
ちなみに逆向きも測ったよ。
100 を省略で探す（表の最小は101） → #N/A
⚠️ 小さすぎるときだけ#N/Aになるんだ。大きすぎるときは黙って最後の行を返すんだよ。片側だけ教えてくれるんだよね。
⚠️ 入力ミスで桁を1つ多く打ったとき、これが起きるんだ。「1050」と打ち間違えても、エラーは出ないんだよ。
5. 4番目の書き方は4通りあるけど、中身は2つだけだよ
ここで整理しておこうか。実際に測った結果だよ。同じ表で104を探しているんだ。
FALSE と書く → ケーブル 正しい
0 と書く → ケーブル 正しい
TRUE と書く → ⚠️ キーボード
1 と書く → ⚠️ キーボード
何も書かない → ⚠️ キーボード
つまりこうなんだ。
FALSE ＝ 0 ぴったり一致だけ
TRUE ＝ 1 ＝ 省略 近いものでいい
⚠️ 何も書かないのは「TRUEと書いた」のと同じなんだよ。「指定していない」のではなくて、危ないほうを選んだことになるんだ。ここが分かりにくいところだよね。
他の人が書いた式を読むときは、末尾に ,0) と書いてあったら「FALSEのことだな」と思ってね。,1) なら近似だよ。
6. じゃあ「近いものでいい」は何のためにあるんだろうね
ここまで読むと「TRUEなんて要らないじゃないか」と思うよね。でもね、これが無いと困る仕事があるんだよ。
段階で値段が変わる表だよ。
1個以上 → 100円
10個以上 → 90円
50個以上 → 80円
100個以上 → 70円
「30個注文が来たら、いくら？」を引きたいんだ。⚠️ でもこの表に「30」という行は無いよね。ぴったり一致では引けないんだよ。
測った結果がこれだよ。
注文9個 → 省略：100円 ／ FALSE：⚠️ #N/A
注文30個 → 省略：90円 ／ FALSE：⚠️ #N/A
注文99個 → 省略：80円 ／ FALSE：⚠️ #N/A
注文250個 → 省略：70円 ／ FALSE：⚠️ #N/A
⚠️ こっちはFALSEを書くと壊れるんだよ。全部#N/Aになるんだ。
だから前回、私が「FALSEは毎回書いて」と言ったのは、言い足りていなかったんだ。正しくはこうだね。
コードから名前を引く → FALSE（ぴったり）
数量から段階を引く → 省略またはTRUE（近いもの）
⚠️ 用途で選ぶものなんだよ。「いつもFALSE」でもないんだ。そして「何も書かない」は選んだことにならないんだよね。
7. ⚠️ その段階表の並びを崩したら、5件で違う単価が返ったんだよ
ここが今日いちばん書きたかったところだよ。
6章の段階表は、小さい順に並んでいたから正しく動いたんだ。じゃあ並び順だけを崩したらどうなるか、測ってきたよ。表の中身は1文字も変えていないんだ。並べ替えただけだよ。
注文10個 → ⚠️ 100円 （正解：90円）
注文30個 → ⚠️ 100円 （正解：90円）
注文50個 → ⚠️ 100円 （正解：80円）
注文99個 → ⚠️ 100円 （正解：80円）
注文250個 → ⚠️ 90円 （正解：70円）
8件のうち5件が違う単価だったんだ。そしてエラーは1つも出ていないんだよ。
⚠️ よく見てほしいんだ。外れ方が全部「高いほう」なんだよね。
本当は90円なのに → 100円で計算される
本当は70円なのに → 90円で計算される
これが見積書だったら、お客さんに高く請求することになるんだ。250個まとめて買ってくれた人に、割引が効いていないんだよ。
⚠️ そして金額はきれいに計算されるから、ファイルの中に間違いは見えないんだ。気づくのは、先方から「割引はどうなっていますか」と言われたときかもしれないね。
私はこの結果を見て、少し背筋が寒くなったんだ。並べ替えボタンを1回押しただけで、こうなるんだよ。
8. 自分の式を、確かめてみようか
難しいことはしなくていいよ。⚠️ いま持っているファイルを開いて、末尾を見るだけなんだ。
手順はこうだね。
① command + F（Windowsは Ctrl + F）で検索を開く
② 「オプション」を出して、検索場所を「数式」にする
③ VLOOKUP で検索 → 「すべて検索」で件数を見る
④ 続けて ,FALSE) で検索 → 件数を見る
⑤ 続けて ,0) で検索 → 件数を見る
③の数と、④＋⑤の数を比べるんだよ。
数が合っている → 全部に4番目が書いてある
⚠️ ③のほうが多い → 書いていない式がその数だけある
差が出た式を1つずつ見て、こう決めるんだ。
コードや名前を引いている → FALSE を足す
数量や点数の段階を引いている → そのままでいい。⚠️ ただし表が小さい順に並んでいるか確かめる
⚠️ 段階表のほうは、式ではなく表を守るのが仕事だよ。並べ替えられないように、別のシートに置いておくのがいちばん確実なんだ。別のシートから持ってくる書き方は前に書いたよ。
もう一つ、表示だけで確かめる手もあるよ。「数式」タブの「数式の表示」を押すと、セルの中身が式のまま一覧で見えるんだ。数が少ないファイルなら、これで目で追えるよ。
9. エクセルの記事、これで16本目だよ
① 指数表示を解除する
② 先頭の0が消える
③ 「1-2」が日付になる
④ 並べ替えで行がバラバラ
⑤ 数字なのに計算されない
⑥ エラー表示の意味
⑦ コピーしたらズレる
⑧ 見えない空白
⑨ 日付を引いたら1900年
⑩ 重複を消す前に
⑪ フィルタが途中で切れる
⑫ 別のシートから持ってくる
⑬ セルの結合でデータが消える
⑭ 条件付き書式
⑮ VLOOKUPで#N/Aが出る
⑯ VLOOKUPの4番目 ← 今日
16本書いてきて、はっきりしたことがあるんだ。
エクセルの困りごとには、2種類ある
① 教えてくれるもの → #N/A、#REF!、行がバラバラ、合計が0
② ⚠️ 黙っているもの → 結合で消えたデータ、途中で切れたフィルタ、今日の違う単価
⚠️ ①は困るけれど、その日に片づくんだ。②は間違ったまま人に渡ってしまうんだよね。
そして②のほうは、検索しても出てこないんだ。だって困っていることに気づいていないからね。私が測って書こうと思ったのは、そこなんだよ。
結論：4番目は、省略しないで「選ぶ」んだよ
今日測った結果をまとめるとこうだよ。
① 並んでいない表で4番目を省略したら、5件のうち1件が違う商品名になった
② ⚠️ エラーは出ない。見ただけでは気づけない
③ 並べ替えてある表なら全部合う。⚠️ だから「前は大丈夫だった」は証明にならない
④ ⚠️ 表に無い数字でも答えが返る（106を探してモニターが返った）
⑤ FALSE ＝ 0、TRUE ＝ 1 ＝ 省略。書かないのは「TRUEを選んだ」のと同じ
⑥ 段階表（数量割引など）では、逆にFALSEだと#N/Aになる
⑦ ⚠️ その段階表の並びを崩したら、8件のうち5件が違う単価。全部「高いほう」に外れた
私は前回、「FALSEを毎回書いて」と書いたんだ。でも測ってみたら、それでは足りなかったんだよね。用途で選ぶものだったんだ。
後輩A君に伝えたいのは、覚え方じゃなくて順番なんだ。
「これはぴったり一致？ それとも段階？」を先に決める
→ 決めたほうを式に書く
⚠️ 迷ったまま書かないのが、いちばん危ないんだよ。書かないと、勝手に「段階」のほうが選ばれてしまうからね。
エクセルは、こちらが黙っていると黙ったまま進めてしまうんだ。だから決めたことは、面倒でも書いておこうか。1語足すだけで、静かな間違いが1つ消えるんだよ。
よくある質問
Q1. XLOOKUPなら、この問題は起きませんか
起きにくいよ。XLOOKUPは何も書かないと「ぴったり一致」になるんだ。⚠️ VLOOKUPと逆なんだよね。だから「省略したら危ない」の向きが違うんだ。⚠️ ただし古いエクセルでは開けないから、人に渡すファイルでは前回書いたとおり気をつけてね。
Q2. 今あるファイルを全部直すのは大変です
全部やらなくていいと思うよ。⚠️ 人に渡すファイルと、お金を計算するファイルから見ようか。今日の7章のように、単価や金額を引いている式がいちばん実害が出るんだ。自分だけが見る集計表は後回しでいいよ。
Q3. 段階表は、どうやって守ればいいですか
3つあるよ。①別のシートに置く（いちばん確実）②シートを保護する（校閲タブ→シートの保護）③段階表の上に「小さい順に並べたまま使ってください」と1行書いておく。⚠️ ③を笑わないでほしいんだ。理由が書いてある表は、並べ替えられにくいんだよ。書いていないと「見やすくしよう」で触られるんだよね。
Q4. 4番目を省略している式を見つけました。FALSEを足すだけでいいですか
⚠️ 足したあとに答えが変わるか見てほしいんだ。変わらなければ、これまでも正しかったということだよ。⚠️ 変わったら、これまでの結果が間違っていたということなんだ。そこは足して終わりにしないで、その式を使った書類がどこに行ったかを思い出してほしいんだよね。
Q5. なぜエクセルは、危ないほうを「省略時」にしているんですか
⚠️ これは私にも分からないんだ。調べても、はっきりした説明は見つけられなかったよ。VLOOKUPがとても古い関数で、作られた当時は段階表を引くのが主な用途だったから、という説明を見かけるくらいだね。⚠️ 確かめられていないので、そう思っている程度に受け取ってほしいんだ。理由が分からなくても、動きは測れるからね。今日の記事はそこだけで書いてあるよ。
【本日のミッション：4番目を、選んでみようか】
- ☐ command + F で検索場所を「数式」にして、VLOOKUP の件数を数えてみようか
- ☐ 続けて ,FALSE) と ,0) の件数を数えて、差があるか見てみようか
- ☐ 差が出た式を開いて、「ぴったり一致か、段階か」を自分で決めて書き足してみようか
- ☐ （後輩A君へ）もし書き足して答えが変わったら、それは今日いちばん大事な発見だよ。⚠️ 黙っていた間違いが1つ見つかったということなんだ。落ち込まなくていいよ。見つけられる人になったということだからね
📚 AI活用シリーズ（おすすめ）
コメント