大人の発達障害者の挑戦日記

30代後半で成人発達障害者となった私の日記

Excelに挑戦(応用)5:VLOOKUP関数を使う

スポンサーリンク

こんにちは。エイキチです。

最近、Office 365のExcelを使っています。今回はVLOOKUP関数を使って見ました。

2つの表

VLOOKUP関数使うときは、大抵は2つの表が登場します。一つは日々の業務で更新する表で、もう一つは更新する表が参照する基本情報をまとめた表です。例えば、以下のような表です。

商品コード 商品名 単価
A001 りんご 100
A002 みかん 110
A003 いちご 120

もし売上表を作るなら、例えば以下のよう表になるでしょう。

日付 商品コード 商品名 売上個数 単価 売上価格
2018年4月1日 A001 1
2018年4月2日 A001 2
2018年4月2日 A002 3

上記の表では、商品名と単価は商品コードから決まるものです。なので、商品コードを入力すれば、自動的に商品名と単価のセルが埋まる仕組みを整えておくと便利です。こういう時はVLOOKUP関数を使います。

表を入力

商品コード、商品名、単価の表を入力します。今回は「商品マスタ」というシートを作り、そこにA1セルから入力したとします。

f:id:otona-hattatsushougai-challenge:20181127192604p:plain

上の画像のように表を作ってみました。「商品コード」だけオレンジ色にして見ました。これは商品コードが決まると行が決まるという表の作りだからです。商品名や単価は重複する可能性があります。以下の表のようにより高価なりんごが表にあってもおかしくはないということです。

商品コード 商品名 単価
A001 りんご 100
A002 みかん 110
A003 いちご 120
A004 りんご 200

上記の表はデータベースと呼ばれる形式になっています。1行目は列の見出しになっていて、2行目からは1行1行がデータになっています。これをレコードと呼ぶこともあります。VLOOKUP関数は行を特定する列を指定するという使い方をします。今回の場合、商品コードがレコードを特定するということです。

売上を入力するシートに売上表を入力します。

f:id:otona-hattatsushougai-challenge:20181127192601p:plain

日付と商品コードを日々入力する表です。商品名と単価をVLOOKUP関数で商品マスタの表から取得する想定です。売上価格は売上個数×単価という数式を入力すれば求まります。

VLOOKUP関数を使う

C2セルに数式を入力していきます。「fx」ボタンをクリックすると、「関数の挿入」ダイアログが表示されるので、「関数の分類」を「すべて表示」にして、関数名のリストからVLOOKUPを選びます。「OK」ボタンをクリックすると、「関数の引数」ダイアログが表示されます。

f:id:otona-hattatsushougai-challenge:20181127192558p:plain

引数の入力ボックスはそれぞれ以下の値になります。

引数
検索値 B2
範囲 商品マスタ!$A$2:$C$4
列番号 2
検索方法 FALSE

引数については、詳細は省きますが、次のような感じで決めています。

検索値はキーとなる値です。商品マスタから商品コードをキーにして商品名を検索するので、B2セルになります。

範囲は「商品マスタ」シートの表の範囲です。関数のコピーアンドペーストをするため、表の範囲の部分は絶対参照にしています。[シート名]!セル範囲という形式で、シートを指定することが出来るので、上記のような値にしています。

列番号は、「商品マスタ」の何列目を検索結果にしたいかという数値です。商品名は第2列目なので、「2」という数値にしています。

最後の検索方法は通常は「FALSE」を入力します。この指定でキーを完全一致で検索してくれます。

同じようにして、単価も求められます。E2セルには、B2セルをキーにして、商品マスタの第3列目を検索したいので、以下の式になります。

  • =VLOOKUP(B2,商品マスタ!$A$2:$C$4,3,FALSE)

要するに第3引数が変わっただけです。

関数の入力した結果、セルにはVLOOKUP関数による検索結果が表示されます。

f:id:otona-hattatsushougai-challenge:20181127192555p:plain

あとは下のセルにコピーアンドペーストして、売上価格を求める数式を入力すれば完成です。

最後に一言

VLOOKUP関数を使うと、今まで手作業でやってきたことが省略できることがあると思います。

参加中 にほんブログ村 成人発達障害