広告

【新常識】ArrayFormula関数 × VLOOKUP関数の使い方

2019年5月28日

ArrayFormula関数とVLOOKUP関数を組み合わせることで、

arrayfomulaとvlookupを組み合わせた動き
arrayfomulaとvlookupを組み合わせた動き

一瞬でVLOOKUP関数を下まで繰り返し表示させることが可能です。

これ、実際にやってみると分かるのですが、下まで繰り返すよりも関数の表示速度がスーパー速くなる!んですよね。

今回は「ArrayFormulaとVLOOKUPを組み合わせた使い方」について、詳しく説明します!

ArrayFormula × VLOOKUPで作業速度が加速する...!

高速のイメージ
高速のイメージ

実は前にもArrayFomula×VLOOKUP関数の使い方の記事を書いているのですが...

なぜもう一度書いてるのかというと、ArrayFormulaが本当にベンリだから

色んな関数との掛け合わせで業務速度が加速するし、やっぱこの感動は共有したい!

と前置きはさておき、肝心のArrayFormulaとVLOOKUPとの組み合わせで一番のポイントは、VLOOKUPの「検索値を範囲で指定すること」です。

ArrayFormula × VLOOKUPを設定するコツ

そもそもVLOOKUP関数の構文は「VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み]) 」となっています。

VLOOKUP関数の構文
参考:VLOOKUP

構文では「検索キー」となっているため、つい検索キーに該当するセルを選択しがちです。

VLOOKUPの検索キーを1セルで指定
VLOOKUPの検索キーを1セルで指定

しかし、検索キーとなる箇所を「範囲で指定」することで、ArrayFormula関数の特性を活かすことができます

VLOOKUPの検索キーを範囲で指定
VLOOKUPの検索キーを範囲で指定

ArrayFormulaとVLOOKUPを組み合わせて使うコツは、基本的にこれだけ!

実際にサンプルシートを使ってやってみます。

ArrayFormula × VLOOKUPの設定例

商品名と価格を50個用意して、VLOOKUP関数で任意の商品の価格を取得していきます。

ArrayFormula × Vlookup のサンプルシート
ArrayFormula × Vlookup のサンプルシート

サンプルシートの定義↓

  • A列に商品㊿まで入力
  • B列に価格を入力
  • D列に検索する商品を入力
  • E列はVLOOKUPでD列該当の価格を取得

1. 【重要】VLOOKUPの検索キーに範囲を指定

ArrayFomulaとVLOOKUP関数を組み合わせて使うための、超重要ポイント。

VLOOKUPの検索キーに範囲を指定します。

VLOOKUPの検索キーに範囲を指定
VLOOKUPの検索キーに範囲を指定

検索キーを範囲で指定しないと、ArrayFomula関数と組み合わせても下まで繰り返し表示されません

OYAKUN
OYAKUN
繰り返し表示したい範囲を検索キーの範囲で指定する、と覚えておくと良いでしょう。

2. VLOOKUPの範囲を指定

次にVLOOKUP構文の「範囲」を指定します。

今回はA列の商品名に対し、B列の価格を抽出するので「A:B」を範囲に指定しました。

VLOOKUPの範囲を指定
VLOOKUPの範囲を指定

3. VLOOKUPで検索する番号を指定

VLOOKUP構文の「検索する番号」を指定します。

範囲A:Bの「2列目」を検索する対象としました。

VLOOKUPで検索する番号を指定
VLOOKUPで検索する番号を指定

4. VLOOKUPの並べ替え済みにFALSEを指定

VLOOKUP構文の「並び替え済み」について指定します。今回は「false」に設定しました。

VLOOKUPの並び替え済みにFALSEを指定
VLOOKUPの並び替え済みにFALSEを指定

5. ArrayFormulaをVLOOKUPの前に設置する

最後にVLOOKUP関数の前に、「ArrayFomula」関数を設置します。

ArrayFormulaをVLOOKUPの前に設置する
ArrayFormulaをVLOOKUPの前に設置する

実際に設置したあとの動作です↓

ArrayFormulaをVLOOKUPの前に設置したgif
ArrayFormulaをVLOOKUPの前に設置したgif

VLOOKUP関数を下までコピーせずとも、ArrayFomula関数と組み合わせることで一気に繰り返し表示されていることが確認できますね。

参照:サンプルシート

補足:他の関数でも同じことができます

今回はArrayFomula × VLOOKUPとして記事作成しましたが、もちろんVLOOKUP関数でなくてもArrayFomulaと組み合わせることで同じような処理が可能です。

たとえば2つの文字列が同一であるか検証ができる「EXACT関数」と、ArrayFomula関数を組み合わせてみます。

EXACT関数の構文
EXACT関数の構文

ArrayFomula × EXACT関数の組み合わせ手順

EXACT関数をArrayFomula関数と組み合わせるのも、VLOOKUPで組み合わせるのとポイントは同じ。

文字列に、繰り返し処理させたい範囲を指定します

STEP1. EXACTに文字列1の範囲を設定

EXACTの文字列1を範囲で指定します。

繰り返し処理したい箇所を、選択範囲で指定するイメージです。

EXACTの文字列1の範囲を指定する
EXACTの文字列1の範囲を指定する

STEP2. EXACTに文字列2の範囲を設定

EXACTの文字列2を範囲で指定します。(文字列1と同じ範囲を指定)

EXACTの文字列2の範囲を指定する
EXACTの文字列2の範囲を指定する

STEP3. EXACT関数の前にArrayFomula関数を設置

最後にEXACT関数の前にArrayFomula関数を設置して、Enterを押せばOK!

EXACTの前にArrayFomula関数を設置する
EXACTの前にArrayFomula関数を設置する

文字列を指定した範囲まで、ArrayFomula関数が繰り返し処理してくれているのが分かりますね。

ArrayFomula × EXACT
ArrayFomula × EXACT
OYAKUN
OYAKUN
文字列が違っているセルに対しては、きちんと「FALSE」が反映されていますね。

繰り返し処理が多い関数とArrayFomula関数を組み合わせることで、かなり業務の効率が上がることでしょう。

まとめ

ArrayFomula × VLOOKUPの組み合わせ、うまくできましたか?

基本的に、ArrayFomula関数で繰り返し表示させたい範囲の指定を忘れなければ、そこまで難しい組み合わせではありません(^^)

Excelにはない、Google Spreadsheetだけの神関数ArrayFomula。

めっちゃ便利な関数なので、ぜひVLOOKUP関数と組み合わせて利用してみてください!

OYAKUN
OYAKUN
データが多いほど、表示速度の恩恵が受けられる組み合わせですよ♪