スプレッドシートのVLOOKUPの使い方｜受注CSVに商品台帳の原価・仕入先を引く

VLOOKUPは、検索キー（商品管理番号など）で別の表を探し、同じ行の値を持ってくる関数です。 商品番号から原価や仕入先を取得するときは、完全一致にするため最後の引数をFALSEにします。 #N/Aの多くは、数値と文字列の違い・余分な空白・台帳にない番号が原因なので、順に確認します。

VLOOKUPの書き方

受注CSVに原価を引くには、商品番号を検索キーにして商品台帳を探し、原価の列を指定します。

基本形は、=VLOOKUP(検索キー,範囲,番号,並べ替え済み)です。

引数	指定する内容	今回の例
検索キー	探したい商品番号が入ったセル	A2
範囲	検索列と取得する列を含む表	'商品台帳'!$A:$C
番号	範囲の左端を1とした取得列の位置	原価は2
並べ替え済み	完全一致か近似一致か	FALSE

商品台帳のA列が商品番号、B列が単位原価、C列が仕入先なら、原価を引く式は次のとおりです。

=VLOOKUP(A2,'商品台帳'!$A:$C,2,FALSE)

仕入先を引く場合は、取得列の番号を変えて、=VLOOKUP(A2,'商品台帳'!$A:$C,3,FALSE)とします。

番号はシート全体の列番号ではなく、指定した範囲の中で数える位置です。

最後の引数を省略すると近似一致になるため、商品番号の照合では必ずFALSEを明示します。

また、VLOOKUPが探すのは指定範囲の左端列なので、商品番号を含む列から範囲を指定します。

受注CSVと商品台帳の検索キーをそろえる

検索キーには、受注明細と商品台帳に共通して存在し、原価や仕入先を特定できる番号を使います。

以下の列配置は説明用であり、楽天市場の実際のCSV項目名や列順を示すものではありません。

データ	列配置
商品台帳	A列：検索キー、B列：単位原価、C列：仕入先
受注の計算シート	A列：検索キー、B列：数量、C列：明細売上
追加する計算列	D列：単位原価、E列：仕入先、F列：原価合計、G列：商品粗利、H列：商品粗利率

キーを決めるときは、列名が似ているかではなく、同じ商品単位を指しているか確認します。

キーの候補	採用する前の確認
商品管理番号などの商品単位の番号	色やサイズが違っても原価・仕入先が共通か
SKU単位の番号	受注側と台帳側で同じ番号体系になっているか
JANや店舗独自の商品番号	空欄や重複がなく、先頭の0も保持されているか

同じ商品でもサイズによって原価が違う場合は、その違いを識別できるキーで照合します。

複数条件で検索する補助列

Googleのヘルプでは、VLOOKUPを複数条件に直接使うことはできず、&で値をつないだ補助列を作る方法が案内されています。

たとえば商品番号とサイズの組み合わせで原価を引くなら、台帳の左端に補助列を入れ、=B2&"|"&C2のように2つの値をつなぎます。受注側も同じ順番と区切り文字でつないだキーを作り、そのキーでVLOOKUPを使います。

区切り文字を入れると、「AB」と「C」、「A」と「BC」のような別の組み合わせが同じキーになるのを防げます。台帳に列を足すと、取得列の番号も1つずれるので式を見直します。

商品台帳の重複を先に消す

台帳に同じキーが複数あると最初の一致が返るため、検索前に採用する原価と仕入先を確定します。

VLOOKUPは重複をエラーにしないので、原価が表示されたことだけでは正しさを判断できません。

台帳の補助列に、=IF(A2="","",COUNTIF($A$2:$A,A2))を入力すると、同じキーの件数を確認できます。

結果・状態	確認して行う処理
件数が1	そのキーは範囲内で重複していない
件数が2以上で内容も同じ	元台帳を保存して重複行を整理する
同じキーで原価が違う	適用する原価を決めて照合用台帳を作る
同じキーで仕入先が違う	採用する仕入先や管理単位を見直す
過去と現在の原価が混在	履歴用と今回の照合用の台帳を分ける

1. 元台帳のコピーを保存します。
2. 重複候補の原価・仕入先・適用時点を確認します。
3. 照合用台帳を、検索キーごとに採用情報が決まった状態へ整理します。

［データ］→［データ クリーンアップ］→［重複を削除］でも、判定対象の列を選んで整理できます。

ただし、キー列だけで機械的に削除すると、原価や仕入先の食い違いを見落とすおそれがあります。

COUNTIFは大文字と小文字を区別せず、*や?をワイルドカードとして扱う点にも注意します。

別シートの商品台帳を参照する方法

同じファイルにある別シートの台帳は、シート名と感嘆符を範囲の前に付けて参照します。

たとえば、計算シートのD2には、=VLOOKUP(A2,'商品台帳'!$A:$C,2,FALSE)と入力します。

'商品台帳'!は参照先シート、$A:$Cは固定した検索範囲を表しています。

検索キーのA2は固定しないため、下へコピーするとA3、A4へ順に変わります。

一方、台帳範囲は絶対参照で固定することで、コピーによる検索範囲の移動を防げます。

別ファイルの台帳はIMPORTRANGEで取り込む

別ファイルの商品台帳は、IMPORTRANGEで専用シートに取り込み、接続を許可してから検索します。

1. 「設定」シートのB1に、参照元ファイルのURLを入力します。
2. 「台帳取込」シートのA1に、=IMPORTRANGE('設定'!$B$1,"'商品台帳'!A:C")を入力します。
3. 接続を求める#REF!が表示されたら、内容を確認して［アクセスを許可］を選びます。
4. 取り込み後、=VLOOKUP(A2,'台帳取込'!$A:$C,2,FALSE)で原価を取得します。

元ファイルを閲覧できない場合は、接続許可の前に、所有者からアクセス権を得る必要があります。

確認項目	Google公式の案内と運用上の注意
受信データ量	リクエストごとに10MBが上限なので、必要な列・行に絞る
取り込み先の編集者	接続後は、参照元の別範囲もIMPORTRANGEで取得できる
ファイル間の参照の連鎖	何層にもつなぐと更新反映が遅くなることがある
更新間隔	計算設定ヘルプは30分ごと、個別ヘルプは開いている間に1時間ごとと記載

更新間隔は公式資料間に記載差があるため、一定時間内の反映を前提にせず、集計前に元台帳と確認します。

また、共有範囲を限定したい場合は、連携に必要な情報だけを置いたファイルを用意します。

#N/Aになる原因と直し方

#N/Aは、検索キーに一致する値が見つからないという意味です。完全一致で#N/Aが出た場合は、検索キーの登録有無・データ型・空白・検索範囲を順に確認します。

原因	確かめ方	直し方
台帳に番号がない	元データと台帳で同じ番号を探す	登録漏れや番号変更を確認する
数値と文字列が違う	両側で=ISNUMBER(A2)を試す	両側を同じ型へ統一する
前後に余分な空白がある	元の値と整形後の値を比較する	両側のキーを同じ規則で整形する
検索列が違う	指定範囲の左端列を確認する	キー列が左端になる範囲へ直す
コピーで範囲が動いた	エラー行の式を確認する	台帳範囲を絶対参照にする
先頭の0が消えている	元CSVの識別子と比較する	正しい文字列を元データから取り直す

ISNUMBERは数値ならTRUE、数字に見える文字列など、数値以外ならFALSEを返します。

文字列へそろえる補助列には、=TO_TEXT(A2)を使えますが、既存の表示形式も引き継ぎます。

先頭・末尾の空白を整える例は、=TRIM(TO_TEXT(A2))で、検索側と台帳側の両方へ適用します。

ただし、TRIMですべての空白文字を除去できるわけではなく、残っている文字の確認が必要です。

全角スペースが原因なら、=TRIM(SUBSTITUTE(TO_TEXT(A2),"　"," "))で置換してから整形できます。

コード内部の空白に意味がある場合は、整形によって別の商品が同じキーにならないか確認します。

先頭の0の消失や指数表記があれば元CSVと照合し、表示形式と実際の値を分けて確かめます。

すでに失われた桁はTO_TEXTだけでは復元できないため、推測でゼロを補わないようにします。

IFNAで未一致の明細を残す

未登録の商品を見落とさないため、#N/Aは空欄やゼロではなく「要確認」と表示して残します。

計算シートのD2には、=IF(A2="","",IFNA(VLOOKUP(A2,'商品台帳'!$A:$C,2,FALSE),"要確認"))と入力します。

E2も同じ形にして、VLOOKUPの番号を3に変えると、仕入先を取得できます。

関数	置き換える対象	確認作業での使い分け
IFNA	#N/Aのみ	未一致を表示し、ほかのエラーを残す
IFERROR	エラー全般	エラーをまとめて指定表示へ置き換える

原価未登録をゼロにすると粗利を過大に計算するため、「要確認」の明細を解消してから集計します。

台帳側の原価空欄は未一致とは別の問題なので、検索前に数値が登録されているか確認します。

XLOOKUP・INDEX/MATCHとVLOOKUPの違い

検索列の左側を取得したい場合や、列番号を数えず指定したい場合は、ほかの検索関数も選択肢です。

比較項目	VLOOKUP	XLOOKUP	INDEX/MATCH
検索方法	範囲の左端列を検索	検索範囲と結果範囲を指定	MATCHで位置を探しINDEXで取得
左側の列の取得	通常の範囲指定では不可	可能	可能
完全一致の指定	FALSE	0、省略時も完全一致	MATCHに0
未一致の表示	IFNAなどを併用	引数で指定できる	IFNAなどを併用
取得列の指定	範囲内の列番号	結果範囲を直接指定	結果範囲を指定

原価を取得するXLOOKUPの例は、=XLOOKUP(A2,'商品台帳'!$A:$A,'商品台帳'!$B:$B,"要確認",0)です。

INDEX/MATCHでは、=INDEX('商品台帳'!$B:$B,MATCH(A2,'商品台帳'!$A:$A,0))と書けます。

MATCHは検索の種類を省略すると近似検索になるので、商品番号の照合では0を明示します。

どの関数を使う場合も、検索範囲と結果範囲は、同じ商品の行に対応するようにそろえます。

原価から粗利の列を作る方法

商品粗利は明細売上から数量分の原価を引き、売上と原価の税区分や適用時点をそろえて計算します。

B列の数量とC列の明細売上が数値であることを確認したうえで、次の計算列を追加します。

セル	項目	式
F2	原価合計	=IF(A2="","",IF(ISNUMBER(D2),B2*D2,"要確認"))
G2	商品粗利	=IF(A2="","",IF(ISNUMBER(F2),C2-F2,"要確認"))
H2	商品粗利率	=IF(A2="","",IF(ISNUMBER(G2),IF(C2=0,"対象外",G2/C2),"要確認"))

H列はパーセント表示にし、売上ゼロの明細では除算せず、「対象外」として確認できる状態にします。

計算前に決めること	確認内容
明細売上	単価ではなく、数量分の売上になっているか
税区分	売上と原価で税込・税抜が混在していないか
値引き	どの金額を差し引き、明細へどう配分するか
原価の時点	現在の原価か、受注時点の原価か
返品・取消	集計対象と数量の扱い
送料・手数料	別の費用列で管理するか

ここでの商品粗利は店舗内の管理用の計算例なので、会計帳簿と照合する際は費用の範囲も確認します。

台帳を更新すると過去分の原価も変わり得るため、月次確定時には適用原価と計算結果を保存します。

CSVの貼り直しで範囲がずれない工夫

CSV更新時は原本と計算シートを分け、検索範囲を列全体で固定すると、式の上書きや追加行の漏れを減らせます。

'商品台帳'!$A:$Cなら検索対象の終端行を固定しないため、台帳の末尾に追加した商品も対象になります。

ただし、列全体の指定で防げるのは範囲外の問題であり、計算式のコピー漏れは別に確認します。

起きやすい問題	対策
CSVを貼って式が消える	「受注原本」と「計算」を分ける
新商品が検索対象に入らない	台帳を列全体で参照する
新しい受注明細に式がない	数式の適用行を確認する
前回の注文が末尾に残る	旧データの内容を消してから更新する
台帳の列追加で違う値が返る	列構成と取得列の番号を再確認する

コピー漏れを減らすには、D2へ=ARRAYFORMULA(IF(A2:A="","",IFNA(VLOOKUP(A2:A,'商品台帳'!$A:$C,2,FALSE),"要確認")))と入力する方法があります。

この場合は結果が展開するD列へ別の値を入れず、仕入先や粗利の列も新しい行まで計算させます。

1. 同じ受注原本シートを維持し、旧データの内容を消してCSVを更新します。
2. 列名・列順と、先頭の0を含む検索キーを元CSVと比較します。
3. 台帳の重複・原価空欄・「要確認」を解消してから集計を確定します。

よくある質問（FAQ）

VLOOKUPで迷ったら、検索条件・台帳の構造・元データを順に確認すると、原因を切り分けやすくなります。

FALSEでも違う原価が返るのはなぜですか

重複キーがあると最初の一致が返るため、台帳の重複、取得列の番号、検索キーの管理単位を確認します。

なお、検索キーの*や?は、FALSEでもワイルドカードとして働くため、文字を含むコードは注意が必要です。

台帳を昇順に並べ替える必要はありますか

完全一致のFALSEでは昇順への並べ替えは不要なので、商品番号の照合ではFALSEを明示します。

#REF!も未登録の商品という意味ですか

列番号が範囲の列数を超えた場合や、IMPORTRANGEの接続許可が必要な場合もあるため、表示内容を確認します。

先頭の0はTO_TEXTで戻せますか

TO_TEXTは文字列へ変換する関数なので、すでに消えた0は元CSVや正式な台帳から取り直します。

値が見つからないのに台帳には番号があるのはなぜですか

見た目が同じでも、片方が数値でもう片方が文字列だったり、前後に空白があったりすると一致しません。ISNUMBERとTRIMで両側の型と空白をそろえてから、もう一度検索します。

原価が見つからない明細をゼロにしてよいですか

原価未確定の明細をゼロにすると粗利を過大に計算するため、「要確認」で残して登録内容を確認します。