検索で見つからない値を、0円にしない

LWP | 検索で見つからない値を、0円にしない

検索で見つからない値を、0円にしない

図解1|0円の商品と、見つからない商品は別

P102は登録された単価0の商品、P999は商品表に見つからないコード。ココさんとナマズさんが二つの結果を比べる

図1|数値の0と検索の失敗を分けると、確認が必要な商品を残せます。

解説1|見積書に必要なのは、そろった数字だけではありません

ストーリー

営業のナマズさんが、商品コードから単価を調べる表を作っています。ところが、数件だけ「#N/A」が出ました。「見積書に出す前に、ここを0にしておこうか」。隣で表を見ていたココさんが、ナマズさんに尋ねます。「その商品、本当に0円でしょうか?」

今回の表には、単価が0円の商品も登録されています。登録された0円は、商品表から取得できた数値です。一方、商品表に存在しないコードでは、単価そのものを取得できていません。この二つをどちらも0にすると、無料の商品と、確認が終わっていない商品を見分けられなくなります。

結果を三つに分ける

検索したコード

商品表の状態

検索結果

今回の読み方

P101

登録あり

1200

単価1200円

P102

登録あり

0

単価0円

P999

登録なし

#N/A

コードや商品表を確認

一行が一つの商品に対応する商品表を使い、コードの重複はないものとします。単価列には数値を入れます。ここでは検索に成功した値と、コードが見つからなかった場合の違いを確かめます。価格が空欄の商品をどう扱うかは、別に決める必要があります。

読むポイント

検索式が返すエラーは、確認を始める手掛かりです。コードの入力違い、商品表の追加漏れ、参照範囲の不足などを調べる前に、結果だけを数字へ置き換えると原因を追いにくくなります。まず置き換える前の式で何が返るかを見て、どの場合に何を表示したいのかを決めましょう。

ココさんが提案するのは、見つからない商品を「要確認」と表示する方法です。ナマズさんも「0円の商品はそのまま、分からないものは確認だね」とうなずきます。見積金額を決める前に、単価を取得できたかどうかを確かめる表を作ります。

図解2|まずVLOOKUPの結果を確かめる

商品表A1:B4と依頼コードD2:D4を並べ、VLOOKUPの検索キー、範囲、戻り列、完全一致を対応させる。ココさんは上部左側で座り、横顔で表を指す

図2|検索範囲の左端でコードを探し、同じ行の2列目から単価を返します。

解説2|小さな商品表で、検索の成功と失敗を比べる

入力例

空のシートを「単価確認」とし、A1:B4へ商品表を入力します。D1は「依頼コード」、E1は「単価」とします。列記号や行番号は位置の案内です。セルの内容として入力しません。

行

A列

B列

C列

D列

E列

1

商品コード

単価

依頼コード

単価

2

P101

1200

P101

3

P102

0

P102

4

P103

800

P999

E2へ次の式を入力し、E4まで下へコピーします。

=VLOOKUP(D2,$A$2:$B$4,2,FALSE)

関数の役割と参照

D2が検索するコード、$A$2:$B$4が商品表、2が取得する列の位置です。「2」はシートのB列という意味ではなく、指定した検索範囲の左から2列目を表します。最後のFALSEで完全一致を指定しています。商品コードのように同じ値を探す場合は、この引数を省略しないようにします。

コピーするとD2はD3、D4へ変わります。商品表の範囲はドル記号で固定しているため変わりません。E2は1200、E3は0、E4は#N/Aになります。商品表を目で探して、同じ結果になるか照合してください。

読むポイント

VLOOKUPは範囲の左端列で検索します。この例ではA列が商品コードです。また、同じコードが複数ある表では最初に見つかった行を使うため、商品表のコードを一意に保つことも必要です。

ここまででは、エラーを置き換えていません。P999が見つからず#N/Aになることと、P102の0が正常な取得値であることを確認してから、表示を整えます。Google公式のVLOOKUPの説明も、検索範囲と完全一致の確認に使えます。

図解3|IFNAで#N/Aだけを「要確認」へ

VLOOKUPの結果1200、0、#N/Aのうち、#N/Aだけが要確認へ変わる。上部右側に座ったココさんが、式と対応図へ目を向けて0はそのまま返ると説明する

図3|IFNAは#N/Aの場合に別の値を返し、正常な数値はそのまま返します。

解説3|取得できた0は残し、見つからない場合に案内を出す

数式を置き換える

E2の式を次のように変更し、E4までコピーします。先ほどのVLOOKUPをIFNAで囲み、#N/Aの場合に返す文字列を指定します。

=IFNA(VLOOKUP(D2,$A$2:$B$4,2,FALSE),"要確認")

計算の内側にあるVLOOKUPが先に単価を検索し、その結果を外側のIFNAが受け取ります。検索に成功した1200や0は、そのままE列へ返ります。#N/Aの場合だけ、文字列の「要確認」に置き換わります。

結果

行

D列

E列

1

依頼コード

単価

2

P101

1200

3

P102

0

4

P999

要確認

P102は、単価0の商品として見積りに使えます。P999はコードや商品表を確認する対象です。「要確認」は数字ではないため、単価として掛け算する前に解決します。表の合計だけを見ると文字列が集計対象から外れることもあるので、確認表示が残ったまま合計金額を確定しないでください。

読むポイント

IFNAが見ているのは#N/Aというエラーの種類です。商品が未登録かどうかを業務上の意味まで調べる関数ではありません。検索対象の単価セル自体に#N/Aが入っている場合にも「要確認」になります。そこで、表示を「未登録」と断定せず、確認が必要だと伝える言葉にしています。

原因を直すときは、元の商品表と検索範囲へ戻ります。P999の入力違いなのか、商品表に追加する商品なのかを担当者に確認します。商品をA5:B5へ追加する場合は、式の検索範囲も新しい行まで広げてください。IFNAを付けても検索範囲は自動で伸びません。Google公式のIFNAの説明では、#N/Aに対する戻り値の指定を確認できます。

図解4|IFERRORは、ほかのエラーも置き換える

IFNAとIFERRORへ#N/Aと#DIV/0!を渡した結果を比較する。ココさんは軽く眉を寄せ、何でも未登録と表示すると原因を見落とすことを示す

図4|IFNAとIFERRORは、置き換えるエラーの範囲が違います。

解説4|表示を整える前に、何を置き換えるかを決める

二つの関数の違い

IFERRORは、式の結果がエラーなら指定した値を返します。#N/Aに加え、ゼロで割った場合の#DIV/0!や、参照に問題がある#REF!なども対象です。IFNAは#N/Aだけを対象にします。正常な値は、どちらもそのまま返します。

内側の式の結果

IFNAで「要確認」へ置換

IFERRORで「要確認」へ置換

1200

1200

1200

0

0

0

#N/A

要確認

要確認

#DIV/0!

#DIV/0!

要確認

小さな式で比べる

商品表とは別の空いたセルで、次の二つの式を一つずつ試せます。1を0で割るため、内側の計算はゼロ除算のエラーになります。

=IFNA(1/0,"要確認")
=IFERROR(1/0,"要確認")

前者は#DIV/0!、後者は「要確認」です。IFERRORがエラー表示を置き換えても、割り算の分母は0のままです。表示が変わったことと、元の計算が正常になったことを分けて考えます。

読むポイント

ナマズさんが「どのエラーでも、未登録と出せば分かりやすいかな」と尋ねると、ココさんは少し真剣な顔になります。「参照の間違いまで、未登録に見えてしまいます」。単にエラーを見せないことより、何を確認してほしいのかが伝わる表示にしましょう。

入力者へ一つの案内を出したい表ではIFERRORも役立ちます。その場合も、元の式でエラーの種類を確認できるようにしておきます。今回の単価検索では、検索失敗の案内を出しつつ別のエラーを見落としにくくするため、IFNAを使います。Google公式のIFERRORの説明で、置換の対象と省略時の動作を確認できます。

図解5|未入力・0円・要確認を分けて仕上げる

未入力の依頼コードは空表示、P102は0、P999は要確認という三つの状態を比較する。ココさんがナマズさんに商品の登録状況を尋ねる

図5|入力前の行、取得できた0、確認が必要な行を区別します。

解説5|式で表示を分け、単価の確認は担当者と進める

未入力の行を空表示にする

依頼コードをこれから入力する行まで「要確認」と出すと、作業中に紛らわしくなります。E2を次の式へ変更し、E5までコピーします。D5は何も入力していないセルとして試してください。

=IF(D2="","",IFNA(VLOOKUP(D2,$A$2:$B$4,2,FALSE),"要確認"))

最初のIFがD2を確かめます。空なら空文字列を返し、コードがあればIFNAを使った検索へ進みます。E5には式がありますが、結果が空文字列なので空に見えます。値も式もない空白セルとは異なります。また、D列に空白文字を入力した場合は、未入力と同じ判定にはなりません。

結果

行

D列

E列

1

依頼コード

単価

2

P101

1200

3

P102

0

4

P999

要確認

5

D2をP103へ変えるとE2は800になります。P102なら0、P999なら「要確認」、D2を消すと空表示です。この四つを順に試すと、検索範囲、表示の切替、正常な0の扱いを確認できます。商品表の値を変えたときにも、対応する結果が変わるか見てください。

まとめ

ココさんは、P999の行を残してナマズさんに尋ねます。「この商品、商品表に追加するものでしょうか?」。「営業の方で確認するよ。式を整えるところは、ココさんにお願いしたい」。関数で表示を整える仕事と、単価を確定する仕事がつながりました。

今回の仕掛けは、IFで未入力を分け、IFNAで#N/Aだけを案内へ置き換えることです。取得できた0円は数値のまま残します。自分の表では、検索するコードの列、商品表の範囲、取得する列の位置を置き換え、コードの重複と追加行も確認してください。

出典

『Googleスプレッドシートの教科書』V048、8.5.2「エラー値を非表示にするには」、8.3.7「検索と参照を扱うグループ」を基に、単価確認の独立例へ再構成しました。図は学習用の模式図です。

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。