【Excel中級者編】広告運用で使える！便利な関数の使い方

Excelを扱う上で最初に学ぶものの一つとしてVLOOKUP関数が挙げられます。
採用要件でのExcel技術では必須といっても過言ではないほど頻出する関数ですね。

業務でも使用頻度の高い関数ですので、新入社員研修だったり、Excel本などでも覚えておく関数として取り上げられる場面が度々あります。
そんな基本関数であるVLOOKUP関数ですがExcelの仕様上、使いにくい場面もあります。

今回は、そんな時に代わりに使用できる関数を紹介していきます。

VLOOKUP関数のおさらい

VLOOKUP関数の使い方に関しては、基本中の基本なので、この場では割愛させていただきますが、関数の書式と難点だけ記載しておきます。

関数の書式
VLOOKUP( 検索値, 検索範囲, 戻り値を含む列番号, 検索方法 )
　検査値：検索する値
　検査範囲：検査値を検索する範囲
　戻り値を含む列番号：検索範囲から取り出したいデータの列番号を指定
　検索方法：完全一致（0/FALSE）または近似一致（1/TRUE）を指定

VLOOKUP関数の難点としては、

・左端の列からしか検索ができない
・列を毎回番号付与しないと行けない
・1つの検査値しか扱えない

などのルールがあり、毎回指定する列が何番目にあるかの確認が必要となります。
そのため、VLOOKUP関数で抽出した値を左セルへコピーして貼り付ける場合、
指定する列番号は変わらず、再度列番号を打ちなおす手間が生じてしまいます。

INDEX関数とMATCH関数の概要

【INDEX関数】

INDEX関数は指定した行と列が交わるセルのデータを抽出する関数です。

INDEX(配列,行番号,列番号)
　配列：検索する範囲
　行番号：配列の中で何行目を指定するか
　列番号：配列の中で何列目を指定するか

つまり、範囲内で、上から何行目、左から何列目を持ってくるかを指定する関数ですね。

例：A2:D9の範囲から広告見出し「デジタリフト／採用情報」を持ってくる場合

配列は検査範囲である「A1:E9」を記載
広告見出し「ベンチャー企業／採用情報」は6行目の4列目なので
行番号に「6」、列番号に「4」を記載
※あくまで指定範囲内で何行目・何列目にあるかの話なので、範囲の起点から算出します。

関数：INDEX(A1:E9,6,4)

これでA1:E9の範囲内で上から6行目、左から4列目にあるデータを抽出することに成功しました。

これでINDEX関数の概要は以上になりますが、
このままではVLOOKUP関数と変わらない、むしろ列番号だけでよかったVLOOKUP関数と比べて、行番号を探す必要があるので、余計に手間がかかるのでは？という課題が残っていますね。

そこで必要になるのがMATCH関数です。

【MATCH関数】

MATCH関数は指定した検査値が指定した検査範囲内で何番目にあるかを求める関数になります

MATCH(検査値,検査範囲,照合の種類)
　検査値：検索する値
　検査範囲：検査値を検索する範囲
　照合の種類：検索するときの条件を指定
　・１→検査値以下で最大の値
　・０→検査値に一致する値
　・-1→検査値以上で最小の値

例１：広告見出し「デジタリフト／採用情報」が範囲内で何行目にあるかを求める
検査値としてセルD11を指定
検査範囲はD1:D9を指定します。

関数：MATCH(D11,D1:D9,0)
これでセルD11と同じ値がD1:D9の中で何行目にあるかが分かりました。

例２：「URL」が範囲内で何列目にあるかを求める
検査値としてセルD11を指定
検査範囲はA1:E1を指定します。

関数：MATCH(D11,A1:E1,0)
これでセルD11と同じ値がA1:E1の中で何列目にあるかが分かりました。

MATCH関数を使うことで、
広告見出し「デジタリフト／採用情報」が何行目にあって、
URLが何列目にあるかが分かりましたね。

では次にINDEX関数に入れてみましょう。

まず、INDEX関数の行番号にはMATCH関数で求めた
広告見出し「デジタリフト／採用情報」の番号を入れます。
次に列番号には「URL」の列番号を入れます。

これで範囲内で広告Aのある行と、URLのある列が交差する地点にある値が抽出されます。
INDEX関数は行番号と列番号の順番を間違えやすいので、注意してくださいね。

INDEX関数とMATCH関数の複合関数の使い方

INDEX関数とMATCH関数の複合関数を使って短縮する
上記対応を1つの関数で行っていきましょう。

関数：INDEX(A1:E9,MATCH(D13,D1:D9,0),MATCH(E12,A1:E1,0))

次に他のセルにもコピーしたいって場合は、
・INDEX関数の配列を$A$1:$E$9に変更
・行番号用のMATCH関数を($D13,$D$1:$D$9,0)に変更
・列番号用のMATCH関数を(E$12,$A$1:$E$1,0)に変更
この対応をすることで変更したくない検索値のセルはコピーした後も変更されることが無くなります。

これで関数を入れたセルE13をE14へコピーしたら
行番号はセルA2を参照にしつつ列番号はセルD1のパラメータを引っ張ってこれますね。

ちなみに、範囲内で行番号と列番号を指定して持ってくるだけなので、
MATCHでの検査範囲の列が最後の列でも、行が最終行でも
列番号と行番号が交差するセルの値を抽出する作業へは影響ないです。

ですが、このままだと、
1つの検査値しか扱えない
この問題は解消されていないので、
次は複数の条件を指定する対応をしていきましょう。

最終応用：MATCH関数の複数条件

先ほどまでは特定した行の中で対象となる行を選択して抽出する対応でしたが、
これが「キャンペーンAの中にある広告Aで使用するURLを抽出」したいという場合はどうでしょう。

関数をキャンペーンAで絞ってやると一番上のキャンペーンAのURLが抽出されますが、これだと広告「企業名訴求」のURLとなりますね。

このような場合はMATCH関数を複数条件指定してあげると解決します。

上述しているINDEX関数とMATCH関数との違いは大きく3つ
・MATCH関数で使用する検査値と検査範囲を「＆」で繋いで条件を重ねる
・「＆」を使って重ねる条件は順番を合わせる
・数式の最初と最後は「｛ ｝」で括って配列数式にする

最終的な関数は下記のようになります。
=INDEX(配列,MATCH(検査値1&検査値2,検査範囲1&検査範囲2,照合の種類),列番号)

ではやっていきましょう。

実際はINDEX関数自体の構成は変わらないので、割愛しますね。

MATCH関数に関して、今回は列番号は「URL」だけなので変わらないです。

行番号に関して、キャンペーンAを指定したいので検査値Aは指定するキャンペーンAであるセルA15を指定。
次の条件として広告Aを指定するので、セルB15を＆で繋ぎます。

検査範囲については、
まずキャンペーンAの行番号を調べるので検査範囲「A3:A10」を記載。
次に広告Aの行番号を調べるので検査範囲「B3:B10」を＆で繋げます。

関数：MATCH(B13&C13,A1:A9&C1:C9,0)

Excelのバージョンによっては、最後に関数を「{}」で括る必要があるものもあります。
これは「Ctrl+Shift+Enter」でショートカットできるので、Enterだけで押すと「#VALUE!」となってしまいます。

※「MATCH(A15&B15:A3:A10&B3:B10,0)」という関数が配列数式だからExcelに配列数式を扱うことを伝えるために「{}」で括る必要があります。

補足：配列数式とは
・配列数式は数(or値)同士の計算ではなく、数(or値)の集まり同士の計算を扱える
・配列数式であることを伝えるために「{}」で括る必要がある

これだけでキャンペーンAと広告「WEBマーケ訴求」がある行番号が抽出されます。

あとは先ほどのINDEX関数に入れれば
キャンペーンAと広告Aがある行と、URLが記載ある列が交わるデータを抽出することができます。

行番号を複数条件指定しましたが、列でも同様にできるので、月別の売上が記載されている表や、テレアポのアタックリストの確認にも使えそうですね。

まとめ

今回紹介しました、INDEX関数とMATCH関数を組み合わせだと、一つのセルに入れてしまえばコピー＆ペーストで他のセルにも反映させることができます。

大量のデータをまとめる時は、業務効率の改善になりますので、是非お試しください。

広告運用に関して詳しくお話を聞きたいという企業様がいらっしゃいましたら、こちらからお問い合わせをお願い致します。

お問い合わせ | 株式会社デジタリフト

もし、私たちの会社で働く事に少しでも興味を持っていただけたら、ぜひ応募フォームよりご連絡ください。

株式会社デジタリフト の全ての求人一覧