- VLOOKUP関数を使って値参照する場合、
列番号をベタ打ちすると列を挿入などすると、
ずれて困ることがある - それを解決するためには
VLOOKUP関数とMATCH関数を組み合わせて処理すると良い - VLOOKUP関数やMATCH関数の基本的な使い方も紹介
こんにちは、hokkyokunです。
仕事で他部署からマクロ作成の相談をいただいたのですが、
「これってエクセル関数で十分じゃない？」と思ったので、調べてみました。
エクセル関数はあいまいな部分も多かったので、
chat-GPTに聞きながら作ってみたら思いのほかいいできだったので
共有したいと思います。
VLOOKUP関数とは
VLOOKUP関数とは何かchat-GPTに聞いてみた
VLOOKUP関数は、
指定した値をデータテーブルの最初の列から検索し、
その値が見つかった行から指定した列の値を返すExcelの関数です。
具体的に見ていきましょう。
chat-GPTに「VLOOKUP関数とはどんなものか」聞いてみました。
エクセルの関数について考えてください
先ずvlookup関数の引数を教えてください
VLOOKUP関数はExcelの検索関数で、
一つの表から特定の値を検索し、それに対応する値を返すために使用されます
VLOOKUP関数の一般的な書式は以下の通りです：
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP関数の引数は以下の4つです
- lookup_value (検索値)：
これは、あなたが検索する値です。
この値は、最初の列で探します。 - table_array (範囲):
これは、検索値が含まれるテーブルまたは範囲です。
VLOOKUPは最初の列からあなたの検索値を見つけ、
その行の指定された列インデックス番号の値を返します。 - col_index_num (列インデックス数)：
これは、テーブルまたは範囲の中で、
検索値に基づいて返すべき値が含まれている列の番号です。
つまり、この番号の列から値が返されます。 - range_lookup (範囲検索)：
これは、VLOOKUPが完全一致または近似一致を返すべきかを指定するオプションです。
値がTRUEまたは省略されると、VLOOKUPは近似一致を返します。FALSEが指定されると、VLOOKUPは完全一致を返します。
見やすくするように少し改行や加工はしましたが、
基本的にはchat-GPTの内容そのままです。
すごくわかりやすいですね。
VLOOKUP関数はわりと使うこと多い関数だと思いますが、
少し時間が空くと引数の使い方忘れちゃうので助かります。
具体的な使用方法
ちょっと具体的に使ってみましょう
エクセルで書いてみました。
少し、仕事で使えるっぽく変えてみました。
F3セルにVLOOKUP関数を仕込み、
F2セルに文字列を入力して、表から「Cost」を参照するようにしています。
VLOOKUP関数の実務上の問題点
しかし、この関数は列インデックスが固定的であるため、
データテーブルの構造が変わると問題が発生します。
例えば、Item列（A列）とCost列（B列）の間に
「Date」列を追加する必要が出てきたとします。
列の参照番号がベタ打ちのため、
参照する位置がずれてしまいました。
このくらいなら後で気づけると思いますが、
仕事で大きなデータテーブルを扱っていると知らず知らずのうちに
変なデータを拾っている可能性も…
列が変わっても自動で変換できる仕組みを作る必要がある
その方法として
MATCH関数を使う方法がある
MATCH関数を使った列調整方法
MATCH関数がいいというのは知識でちょっと知っていたのですが、
具体的にどうすればいいかわからなかったので、
chat-GPTに聞いてみました。
MATCH関数を使うのはいいアイデアだと褒めてくれました（笑）
MATCH関数の具体的な使用方法についても解説してくれました。
ちょっとこのままだと見づらいので、
少し調整してみます。
MATCH関数とは
MATCH関数は、
配列内で特定の項目を見つけてその位置を返します。
MATCH関数の一般的な書式は以下の通りです：
MATCH(lookup_value, lookup_array, [match_type])
引数について
- lookup_valueは
検索したい値 - lookup_arrayは
検索する範囲や配列 - match_typeは
一致タイプ（-1は一番近い小さい値、0は完全一致、1は一番近い大きい値）
MATCH関数の具体的な使用方法
エクセルで使用してみます。
MATCH関数を使ってデータテーブルの列名から列位置を取得してみます。
関数は
“=MATCH(G2,A1:D1,0)”
と作りました。
G２セルに列名を入力すると
G3セルに位置を返してくれています。
VLOOKUP関数×MATCH関数の列調整方法
上記のようにMATCH関数単独で列の位置を取って
それをVLOOKUP関数で参照してもいいのですが、
エクセルシートに空きスペースがない場合もあると思われますので、
関数の組み合わせ方法を見ていきます。
“=VLOOKUP(I7,A:F,MATCH(“Cost”,1:1,0),FALSE)”
VLOOKUP関数の中の赤字部分がMATCH関数です。
列を増やしましたが、
変わらず対応できています。
ところで関数の参照範囲を変えたのわかりましたか？
最初はデータテーブルの範囲（上記ではA1:E4）としていました。
具体的には
=VLOOKUP(I7,A1:E4,MATCH(“Cost”,A1:E1,0),FALSE)のような感じです。
一方、下記では
=VLOOKUP(I7,A:F,MATCH(“Cost”,1:1,0),FALSE)
のように列あるいは行全体を指しています。
次の章で説明しますが、
エクセル関数の限界値の一つである、参照範囲の対応問題です。
エクセル関数関数の問題点
さて、エクセル関数でも結構いろいろできることがわかりましたが、
エクセル関数にも問題点は複数あります。
一つ一つ整理していきましょう。
処理が重たい
関数を常に作動させながらなので、
当然処理は重くなります。
数行くらいなら大丈夫だと思いますが、
数百～数千行のデータテーブルを抱えつつ、
何個もシートを作っていると開くだけで結構な時間がかかってしまいます。
なんの処理をしているのかパッと見でわからない
今回、関数を二つ重ねただけですが、
慣れていないとちょっと見ただけでは何を処理したいのかわからないと思われます。
後述しますが、データテーブルの範囲変更にも対応したり、
さらに関数を重なると作った人じゃないとメンテナンスができなくなり、
悪名高い(笑)マクロVBAと変わらない仕様となってしまいます。
個人的には関数は２個重ねまでに止めておいた方がいいのではないかと思います。
範囲の変化対応には限界がある。解決策は？
VLOOKUP関数とMATCH関数を組み合わせて列の変更処理を可能にしました。
ただし、それだけでは不十分でデータテーブルの範囲変更にも本来は対応する必要があります。
対応する方法は大きく３つあり
- テーブルを使う
- 関数を重ねる
- 予め範囲を広めにとっておく
このあたりかなと思います。
それぞれメリットデメリットがあり、
①テーブルを使う
範囲変更をエクセル側で管理してくれるので
使用者側はあまり意識しなくていいメリットがあります。
ただし、仕様には癖があり、
きちんと使いこなさないと思わぬ事故につながります。
例えば行削除は右クリックを押して行削除を選ぶ必要があります。
「Delete」キーで値を消すだけじゃダメです
５行目は全て空欄ですが、テーブル上は範囲に入っています。
きちんと右クリックで削除を選ぶ必要があります。
不特定多数の人が触るとテーブルは荒れやすいので注意が必要です。
②関数を重ねる
OFFSET関数とCOUNTA関数を組み合わせることで、データの量に応じて範囲を動的に調整することも可能です
長くなるので詳細は別記事になりますが、
具体的な式は以下の通りです。
=VLOOKUP(“Cherry”, OFFSET($A$1, 0, 0, COUNTA($A:$A), 3), MATCH(“Cost”,1:1,0), FALSE)
個人的にはおすすめしません、
見て頂ければわかる通り関数がたくさん重なって複雑です。
構造を解読するのも大変だし、
重くなります。
当然壊れやすくもなります。
③予め範囲を広めにとっておく
これが一番楽で壊れにくいと思います。
つまり、おすすめ
ただし、デメリットもあります。
エクセルの使い方としてはすごく正しい使い方かと思いますが、
そうはいかない事情もあると思います。
シートが複雑な構造になって返って面倒なことになることもあるかもしれません。
このあたりは適宜判断が必要ですね。
関数もいいけどVBAもいいよ。VBAが関数よりも優れているところ３点
関数も使い方によっては変化に強く、便利なシートを作ることができると思います。
が、上記で上げたようにデメリットもそれなりにあり、
完璧ではありません。
もし今以上にエクセルを便利に使いたいならぜひVBAを学習してみてください
VBAは属人化するとか難しいとかネガティブな声も聞きますが、
エクセルのパワーを最大限発揮するにはVBAが最良の方法だと確信をもって言うことができます。
- 関数よりファイルが軽い
- 慣れれば処理内容が関数よりも理解しやすい
- 関数とは比較にならないほど複雑な処理や適応範囲も広い
関数よりファイルが軽い
関数はデータ数が多くなると立ち上がりが不安定になるほど重たくなりますが、
VBAは基本的にいくら書いても重たくなりません。
ファイルもサクサク立ち上がります。
慣れれば処理内容が関数よりも理解しやすい
なぜなら関数は一行で複雑な処理をてんこ盛りにしていますが、
VBAは一行で一個の処理、それを踏まえて二個目の処理と
段階を踏んで処理内容をかくことができるからです。
関数でif関数の中にvlookup関数を組む人いますが、
式が長くて何かいているかさっぱりわからないみたいな経験ないですか？
VBAなら
if 〇〇 then
処理１
else if 〇〇
処理２
みたいに、処理を分けて書くことができるので、
正直、関数よりもVBAの方が処理内容わかりやすいです。
関数とは比較にならないほど複雑な処理や適応範囲も広い
関数は基本的には特定のファイルの特定の位置の数値を参照することしかできません
一方VBAは変化をつけることができます。
例えば、
- セルに新たに値を入力する
⇒毎月変わる顧客ごとに請求書を発行可能 - エクセルブックを新たに作成し、フォルダに保存する
⇒月次ごとに売り上げを自動でまとめ上げる - メールを送信
⇒毎日、社内に売り上げメールを自動送信 - 受信メール分析
⇒添付ファイルを開いて注文内容を在庫管理表に転記
つまり、今ないものを新たに作ることができることが
関数との大きな違いです。
また、VBAは
新規エクセルファイル作成、
フォルダ作成、削除、修正
メール送信、受信メール取得
等
さまざまなシーンで生産性のある行動をとることが可能です。
VBAはガチの自動化が可能。
VBAはガチです。
本当にワンクリックで一日かかっていた仕事が３０秒で終わる
みたいな世界が本当に広がっています。
例えば、
その日の売り上げを担当者ごとに集計して、
月次変化を添えながらまとめたファイルを部署の担当者や重役に
メールで送る
このような仕事を本当に何もせずワンクリックで行うことが可能です。
または営業から発注メールをもらったとして
何もせずに倉庫係に発送依頼書の作成と在庫管理表の転記を行うことも可能です。
関数だけではできない世界が広がっています。
VBAはガチの自動化がまっています。
もし少しでも興味がわいたならVBAの学習を検討してみてください。
私もVBAを覚える前は出荷のオペレーションで四苦八苦していましたが、
VBAを覚えることで年間100時間以上時間節約できるようになりました。
VBAを高コスパで、短期間で学ぶにはUdemyがおすすめです。
Udemyの公式サイトはこちらです。
Udemyは良質の学習プラットフォームですが、
動画数が多すぎてどれを見ればよいか迷います。
おすすめの講師をまとめました。
正直、０円で学習するのは難しいし、時間もかかります。
かけた費用分以上に正しく評価されるかは会社によると思います。
ただ、きちんと習得できれば
生産性は魔法レベルで変わることは保証します。
