アルパカログ

カスタマーサポート (CS) とエンジニアリングを掛け算したい CRE (Customer Reliability Engineer) が気になる技術や思ったことなど。

Google スプレッドシートで別シートのセルを参照する(垂直検索)

Google スプレッドシートで別のシートの値を参照したいということがあります。下図のイメージです。

f:id:otoyo0122:20180723130441p:plain

どうすればできるでしょうか?

今回は、シートを検索して値を参照する VLOOKUP と、VLOOKUP の結果を一気に反映する ARRAYFORMULA という2つのスプレッドシートの関数の使い方を説明します。

VLOOKUP 関数の使い方

手元でスプレッドシートを簡単に開ける方は、実際に下記のシートを用意して手を動かしながら覚えていきましょう。

f:id:otoyo0122:20180723131052p:plain

ある範囲を縦に検索して値を参照するには VLOOKUP 関数を使います (Vは垂直を表す vertical の意です)。

今、注文一覧シートの価格(C列)には、商品ID(B列)で商品一覧シートを検索して見つかった行の価格(B列)を入れようとしています。

VLOOKUP は下記4つの引数を取ります。

VLOOKUP(検索キー, 検索範囲, 範囲の何番目を取り出すか, FALSE)

検索キー

ある範囲を検索するためのキーです。ここでは商品ID(注文一覧B列)にあたります。

検索範囲

検索キーで検索したとき、取得したい値が含まれている範囲です。ここでは商品一覧シート($A2:$B)になります。

範囲の何番目を取り出すか

検索範囲の左から何番目の値を取り出すか指定します。ここでは価格を取り出したいので2番目になります。

FALSE(第4引数)

FALSE を指定しなければ、一致していない近い値を返すようになってしまいます。ほとんどのケースでは完全一致で検索すると思いますので FALSE で良いでしょう。

VLOOKUP 関数の詳細は下記をご覧ください。

それでは下図のようにC2セルに VLOOKUP 関数を入力してみましょう。=を忘れず入力してください。

f:id:otoyo0122:20180723133053p:plain

=VLOOKUP(B2, '商品一覧'!$A2:$B, 2, FALSE)

下図のように product-3 の価格 36,000が表示されます。

f:id:otoyo0122:20180723133412p:plain

VLOOKUP を使って別シートのセルを参照して値を持ってくることができました。

しかし、もし注文が数百、数千件とあったらどうでしょうか?一気に反映させたいですよね?

f:id:otoyo0122:20180723133622p:plain

そこで便利なのが ARRAYFORMULA 関数です。

ARRAYFORMULA 関数の使い方

ARRAYFORMULA 関数を使うと、複数の値を複数行に展開することができます。

と、言われてもイメージしにくいかもしれませんが、使い方は簡単です。習うより慣れろでやってみましょう。

先ほど VLOOKUP を入力したセル(C2)を下記のように ARRAYFORMULA を使って書き換えます。

単に ARRAYFORMULA を付けただけではなく VLOOKUP の検索キーが範囲($B2:$B)になっていることに注意してください。

=ARRAYFORMULA(VLOOKUP($B2:$B, '商品一覧'!$A2:$B, 2, FALSE))

下図のように全ての行に正しく価格が表示されます。

f:id:otoyo0122:20180723134238p:plain

ARRAYFORMULA 関数の詳細は下記をご覧ください。

おわりに

Google スプレッドシートはリストの受け渡し等、様々な業務でよく使われています。

今回ご紹介したテクニックを知っておくと業務効率が劇的にアップするので、知らなかった方は必ず使えるようになっておきましょう。