Office Hack

今回は、価格テーブルの表を別のシートに移行し、受注シートの「種類」「商品名」「単価」の項目を別シートにある価格テーブルから参照する方法をお伝えします。

「価格テーブル」の別シートを作成します。赤枠の【＋】を押します。

シート名を『価格テーブル』に変更しましょう。

「受注シート」のシートへ戻って、価格テーブルを切り取りしましょう。【範囲を選択】して、Ctrl+Xで切り取れます。

「価格テーブル」のシートへ行き、貼り付けたいセル上で、Ctrl+Vで貼り付けれます。これで準備が完了しました。

「受注シート」入力説明をします。項目の「受注日」「商品コード」「個数」は手入力をし、「金額」は単価と個数を掛け算して自動算出しております。

「受注シート」の「商品コード」を手入力した時点で、、

別シートの「価格テーブル」の「種類」「商品名」「単価」をVLOOKUP関数で引っ張ってきます。

それではVLOOKUP関数で別シートを参照するため、一度赤枠内をクリアします。

『=VLOOKUP(』ではじめ、検索値のセルを参照します。すぐ左のセルの「商品コード」を検索値とするので「C4セル」を指定してカンマで区切ります。

「価格テーブル」のシートへ移動し、検索の範囲を指定します。今回は、B列からE列の全てのデータを対象としたいため、赤枠の箇所を【BからEまでドラッグ】し、カンマで区切ります。。こうすることにより、新しく商品コードが1007以降追加された場合、自動で検索範囲としてくれますので便利です。

「種類」の項目を取得したいので、左から数えて2番目に「種類」の列が存在しているので、『2』と入力し、カンマで区切ります。

完全一致検索にするので『FALSE』と入力します。

【Enter】を押すと、受注シートのD4セルに商品コード1001の「種類」項目が「果物」であると正しく参照されました。

それでは他のセルへ反映していきます。反映するとセルの参照がずれてしまうので、適切な箇所を「$」で固定していきます。

商品コードと範囲を赤枠の箇所で固定します。

赤枠の【フィルハンドルをクリック】したまま、「単価」のセルまで【ドラッグ】します。

正しい値が反映されたかと思ったら、全て同じ値が抽出されました。これは列番号がすべて「2」のため、すべて「種類」の列を参照しているためです。

こちらの解消方法は以下のページにて書かれております。

「商品名」と「単価」の列番号を変更します。すると正しい値が抽出されました。

【D4～F4のセルを範囲指定】し、フィルハンドルを9行目まで【ドラッグ】します。

全ての値が反映されました。これで完成です。

n/aエラーが出る理由としては、別シートの範囲の参照を赤枠のように固定にしてしまっているケースです。

別シートの価格表を見ると、赤枠の範囲を固定で参照しております。

今回新たに「価格テーブル」に「トマト」を追加しました。

そして「トマト」の受注が入ったので「受注シート」にトマトを追加しましたが、#N/Aエラーが出てしまいました。

「価格テーブル」の検索範囲は固定にしていたので、検索範囲から外れているため#N/Aエラーになります。

これを解消するためには上部で説明したとおり、検索範囲を列ごと指定しましょう！