奮闘記(4)VLOOKUP関数

VLOOKUP関数について

今回は、業務用のピボットテーブルで使用する関数について紹介します。ピボットテーブルのレポートを充実させるためにはデータソースの元データに、関数などを使って情報列を増やすことが必要です。
 VLOOKUP関数・・マスタなどから情報を検索したい場合
 IF関数・・元データの条件から新たなる情報を作りたい場合
 文字列操作関数・・データの編集など(RIGHT、LEFT・・)
 他にも、数学関数、日付/時刻・・沢山あります。
今回は、関数の中でも一番人気の「VLOOKUP関数」を紹介します。
Excelの本なら必ず説明されますし、単独の解説書も出ています。
先ず、Excelのヘルプの抜粋を見てください。
   Excel /数式と関数/参照/VLOOKUP 関数

ヘルプ・・VLOOKUP関数


ヘルプや、解説書の説明では、基本な機能と使い方を分かりやすく解説しています。

テーブル内の項目や行ごとの範囲を検索する必要がある場合は、VLOOKUP関数を使用します。 たとえば、自動車部品の価格を部品番号で検索するか、従業員 ID に基づいて従業員名を検索します。
たとえば、自動車部品の価格を部品番号で検索するか、従業員 ID に基づいて従業員名を検索します。
その最も簡単な形式で、VLOOKUP 関数は次のようになります。
= VLOOKUP (検索する内容、検索する場所、検索対象の値を含む範囲内の列番号、近似または完全一致-1/TRUE、または 0/FALSE) を返します。

VLOOKUP関数-業務用に使うための対応とは

 
業務用のレポートで留意すべきことは、元データの項目(列)数が多くなり、データ件数が大幅に増える、再計算に時間がかかることです。専門書でも、データ件数が多い場合の対応や、INDEX-MATCH関数に置換えなどが説明されています。
 どの方法が、一番使いやすく、効果的な方法でしょうか。
 ピボットおじさんは、業務用の検索を最も早く・簡単にできる方法を追究し、VLOOKUP関数の使い方を極めてきました。
 業務用に検索をする手段をまとめると、次のようになります。

検索関数と検索機能の比較

業務用・・①計算を早く、② 取扱いやすい数式 、③ 検索する場所 、
     ④ 検索できない場合の処理
①計算を早く・・VLOOKUP近似一致(事前sort)
②取扱いやすい数式・・INDEX+MATCHよりもVLOOKUPに統一
③検索する場所・・範囲の自動設定・・OFFSET関数は使用しない
          ※前回の「範囲の設定」を参考にしてください。
④検索できない場合の処理・・IFERROR 関数

◎おすすめ数式
=IFERROR(IF(VLOOKUP(検索key,検索する場所,1,TRUE)=検索key,VLOOKUP(検索key,検索する場所,列番号,TRUE),”-“),”-“)
列番号を変えれば、項目の検索が可能になります

VLOOKUP関数、近似一致(事前sort)がおすすめ

VLOOKUP関数の一般的な説明 】 
= VLOOKUP (検索する内容、検索する場所、検索対象の値を含む範囲内の列番号、近似または完全一致-1/TRUE、または 0/FALSE

【 VLOOKUP近似一致の処理 】
IFで検索keyがヒットする場合[true]近似一致を行い、[false] “-” をセット

IF( (VLOOKUP(検索key,検索する場所,1,TRUE)=検索key,
     VLOOKUP(検索key,検索する場所,列番号,TRUE), ”-“)

IFERROR関数で、検索無しのエラー対応

【全体の構成】
=IFERROR ( [VLOOKUP近似計算の処理]), エラー対応[”-”表示] )

IFERROR関数を使用して、数式のエラーをトラップし、処理することができます。 IFERROR は、数式がエラーと評価された場合に指定した値を返します。それ以外の場合は、数式の結果が返されます。
この関数が使えるようになり、検索エラー時の対応は便利になりました。

検索する場所の設定・・名前で登録

ピボットテーブルの 元データと同様に、範囲を手作業で変更するのは面倒なので、 数式/名前で検索する場所を登録して使います。
=$A$1:INDEX($A:$A,COUNTA($A:$A))  $A:$Aは最右列を指定
※OFFSET関数は、揮発性関数のため、使用しないようにしています

【ピボットおじさんの実用例】


「顧客マスタ」顧客はID(CODE)で管理、顧客名、住所・・項目の台帳   
「商品マスタ」商品はCODEで管理、商品名、売価・・項目の台帳

顧客マスタ・商品マスタ

各マスタの名前を登録。参照範囲は、INDEX関数で自動設定にします。

「売上データ」 顧客ID、商品CODE、売上数量・・各マスタから情報を検索、付与します。
 完全一致・・マスタに無い場合は、「#N/A」の表示になります。
 近似一致・・マスタに無くても、近い検索のものが表示されます。・・使えません。

一般的なVLOOKUP関数の悦明

 一般的な説明は、完全一致と近似一致の違いだけで、実務にどう使うかまでは、ほとんど説明されていません。ピボットおじさんは、実務に使うことを前提にしています。
IF(VLOOKUP(1,true)=検索key、VLOOKUP(項目番号,true)、エラー処理)で、
近似一致でも、完全一致と同等の処理を行うことができます。
[注意:マスタはkeyの昇順でSORTしておく必要があります]
次に、IFERROR関数で、VLOOKUP関数のERROR「#N/A」表示の対応を追加します。「マスタ無し」等の表示でも構いませんが、ここでは「-」としています。

おじさんのVLOOKUP関数

$A2など列を固定すれば、データ行が増えても、COPYで対応できます。
検索するkeyは変わらないので、列番号は1で固定ですが、検索列番号を変更するだけで、新たな列検索が可能になります。
おじさんの実例では、2→3に変更すれば、住所が検索できます。
見かけは難しそうですが、このパターンだけで関数の応用が簡単にできます。

Excel のパフォーマンス: パフォーマンスの向上と制限の改善

パフォーマンスについての理解が、業務用に活用するときに必要です。

VLOOKUP、INDEX、または MATCH を使って値を検索する、という方法が紹介されています。近似一致でVLOOKUP関数を置換えたケースと比較してみましたが、両者の計算時間にはほとんど差がありませんでした。
それならば、VLOOKUP関数で統一しておいた方が、ユーザーには理解しやすいと思います。
※特殊なケースですが、VLOOKUP関数は検索keyの右側の項目に検索範囲が限定されます。 INDEX-MATCH関数を使用すると、 左側の項目も検索可能になるというメリットがあります。

VLOOKUP関数のスピードアップが紹介され、内部のキャッシュインデックスの効果が説明されています。完全一致でも実用にふさわしい処理時間になったのでしょうか。
 ピボットおじさんの実例は、データ件数が10万件を超え、マスタからの検索項目が20以上もある場合、完全一致で全列を再計算させると、Excel2010ではハングアップしました。データテーブルの構造化参照でも、同様な状況でした。 業務用には、近似一致での対応が確実と思います。

ピボットおじさんのVLOOKUP関数の秘訣

 ピボットおじさんの秘訣は、以下の4つです。
  ①VLOOKUP関数は、近似一致
  ②IFERROR関数でエラー処理
  ③全行・全列同時再計算せず、列毎に分割して再計算を行う
  ④マクロで自動処理を行う
 10万件で20項目の検索をする場合、 この手法で行うと Excel2010でも2分間程度、Excel2016では1分間程度という処理時間で計算できています。

PowerQueryで対応できないならVLOOKUP関数です!

 Excel2013からピボットテーブルでは「リレーション機能」での検索が可能になりましたが、おじさんお薦めの「集計フィールド」が使えないという制約があります。
Excel2016からは、「PowerQuery」でピボットテーブル外で検索できるので、ピボットテーブルでは「集計フィールド」が使えるようになりました。皆さん、新しい機能を覚えて使うメリットはあると思います。
「PowerQuery」は、 検索に使う「マスタ」があることを前提としています。しかし、実際の業務は、どんどん変化しています。今ある「マスタ」が唯一ではなく、 もっといろいろな管理をしたいというユーザーの要望には応えていく必要があります。ピボットおじさんは、「PowerQuery」 でVLOOKUP関数を使った処理は減るとは思いますが、無くならない大事な関数だと思っています。