【Googleスプレッドシート】商品マスタとトランザクションをVLOOKUPで結合!正規化したデータ設計

商品一覧や売上データを管理するとき、商品名や価格を毎回手入力していませんか。データが重複してしまうと、修正時にあちこちを直す手間が生まれます。この記事では、商品マスタとトランザクションを分離する「正規化」の考え方と、VLOOKUP関数を使ってそれらを自動結合する方法を解説します。これにより、データの一貫性を保ちながら効率よく管理できるようになります。

VLOOKUPとデータ正規化の関係

データベースの設計では、「正規化」という手法がよく使われます。これは、データを重複なく管理するために、複数の表に分割する考え方です。例えば、商品情報(商品ID、商品名、単価)を「商品マスタ」として独立させ、売上データ(日付、商品ID、数量)には商品IDだけを記録します。こうすることで、商品名や単価が変わった場合でも、マスタを1か所更新するだけで済みます。VLOOKUP関数は、この商品IDをキーにして、商品マスタから必要な情報を引き出すのに最適な関数です。VLOOKUPを使えば、手作業で商品名や単価をコピーする必要がなくなり、ミスも減ります。

商品マスタとトランザクションをVLOOKUPで結合する手順

1. 商品マスタシートを準備する

1. 新しいシートを作成して「商品マスタ」と名前を付ける
   スプレッドシートの左下にある「+」ボタンをクリックしてシートを追加し、シート名を「商品マスタ」に変更します。
2. 商品ID、商品名、単価の列を入力する
   A列に商品ID、B列に商品名、C列に単価を入力します。1行目は見出し行として「商品ID」「商品名」「単価」と記入してください。2行目以降に実際のデータを入れます。
3. サンプルデータを用意する
   例として、商品ID「P001」に商品名「ボールペン」、単価「150」、商品ID「P002」に商品名「ノート」、単価「300」というように数件のデータを入力します。

2. トランザクションシートを作成する

1. 新しいシートを追加し「トランザクション」と名前を付ける
   同様にシートを追加し、シート名を「トランザクション」に変更します。
2. 日付、商品ID、数量の列を入力する
   A列に日付、B列に商品ID、C列に数量を入力します。1行目は見出し行です。
3. サンプルのトランザクションを入力する
   例えば、2024/1/10に商品ID「P001」を5個販売した場合、「2024/1/10」「P001」「5」と入力します。

3. VLOOKUPで商品名と単価を自動取得する

1. トランザクションシートのD列に商品名を表示する
   D2セルに次の数式を入力します:=VLOOKUP(B2, 商品マスタ!A:C, 2, FALSE)。この数式は、B2セルの商品IDをキーに、商品マスタシートのA列からC列の範囲を検索し、2列目(商品名)を返します。第4引数にFALSEを指定することで、完全一致検索になります。
2. E列に単価を表示する
   E2セルに次の数式を入力します:=VLOOKUP(B2, 商品マスタ!A:C, 3, FALSE)。これで、同じ商品IDに対応する単価を取得できます。
3. 数式を下方向にコピーする
   D2とE2のセルを選択し、右下のフィルハンドルをダブルクリックするか、必要な行までドラッグして数式をコピーします。これで、すべてのトランザクション行に商品名と単価が表示されます。
4. 金額を計算する
   F列に数量×単価の計算式を入れます。F2セルに=C2*E2と入力し、下方向にコピーしてください。

VLOOKUP使用時の注意点とよくある失敗

商品IDの書式が一致していない

商品マスタの商品IDが数値で、トランザクション側が文字列になっているとVLOOKUPがエラーになります。両方のシートで商品IDのデータ型を統一してください。文字列の場合は先頭に「’」を付けるか、セルの書式設定を「テキスト」にするとよいです。

範囲指定に絶対参照を使っていない

VLOOKUPの第2引数(範囲)は、数式をコピーするときにずれてしまわないように絶対参照($A:$Cまたは$A$1:$C$100)で指定します。先ほどの例では「商品マスタ!$A:$C」と書くと安全です。

商品マスタにない商品IDがある

トランザクションに商品マスタに存在しない商品IDが入力されていると、VLOOKUPは#N/Aエラーを返します。エラーを目立たなくしたい場合は、IFERROR関数で囲みます。例:=IFERROR(VLOOKUP(B2, 商品マスタ!$A:$C, 2, FALSE), "未登録")。

近似一致で意図しない値が表示される

VLOOKUPの第4引数を省略するとTRUE(近似一致)と見なされます。商品IDが数値のソート済みリストでない場合、間違った値が返される原因になります。必ずFALSEを指定してください。

正規化データとフラットデータの比較

まとめ

商品マスタとトランザクションを分離し、VLOOKUPで結合することで、データの重複を防ぎ保守性を高められます。正規化されたデータ設計は、後々の修正や集計をスムーズにします。次は、このデータをもとにピボットテーブルで売上集計を行ってみてください。また、より多くのデータを扱う場合は、INDEX/MATCH関数やXLOOKUP関数の利用も検討するとよいでしょう。