奮闘記（4）VLOOKUP関数
VLOOKUP関数について
今回は、業務用のピボットテーブルで使用する関数について紹介します。ピボットテーブルのレポートを充実させるためにはデータソースの元データに、関数などを使って情報列を増やすことが必要です。
VLOOKUP関数・・マスタなどから情報を検索したい場合
ＩＦ関数・・元データの条件から新たなる情報を作りたい場合
文字列操作関数・・データの編集など（RIGHT、LEFT・・）
他にも、数学関数、日付/時刻・・沢山あります。
今回は、関数の中でも一番人気の「VLOOKUP関数」を紹介します。
Excelの本なら必ず説明されますし、単独の解説書も出ています。
先ず、Excelのヘルプの抜粋を見てください。
Excel /数式と関数/参照/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関数で自動設定にします。
「売上データ」 顧客ＩＤ、商品ＣＯＤＥ、売上数量・・各マスタから情報を検索、付与します。
完全一致・・マスタに無い場合は、「＃N/A」の表示になります。
近似一致・・マスタに無くても、近い検索のものが表示されます。・・使えません。
一般的な説明は、完全一致と近似一致の違いだけで、実務にどう使うかまでは、ほとんど説明されていません。ピボットおじさんは、実務に使うことを前提にしています。
IF（VLOOKUP(1,true）＝検索key、VLOOKUP(項目番号,true)、エラー処理）で、
近似一致でも、完全一致と同等の処理を行うことができます。
[注意：マスタはkeyの昇順でＳＯＲＴしておく必要があります]
次に、IFERROR関数で、VLOOKUP関数のERROR「#Ｎ/Ａ」表示の対応を追加します。「マスタ無し」等の表示でも構いませんが、ここでは「－」としています。
＄A2など列を固定すれば、データ行が増えても、COPYで対応できます。
検索するkeyは変わらないので、列番号は１で固定ですが、検索列番号を変更するだけで、新たな列検索が可能になります。
おじさんの実例では、２→3に変更すれば、住所が検索できます。
見かけは難しそうですが、このパターンだけで関数の応用が簡単にできます。
Excel のパフォーマンス: パフォーマンスの向上と制限の改善
パフォーマンスについての理解が、業務用に活用するときに必要です。
VLOOKUP、INDEX、または MATCH を使って値を検索する、という方法が紹介されています。近似一致でVLOOKUP関数を置換えたケースと比較してみましたが、両者の計算時間にはほとんど差がありませんでした。
それならば、VLOOKUP関数で統一しておいた方が、ユーザーには理解しやすいと思います。
※特殊なケースですが、VLOOKUP関数は検索ｋｅｙの右側の項目に検索範囲が限定されます。 INDEX－MATCH関数を使用すると、 左側の項目も検索可能になるというメリットがあります。
VLOOKUP関数のスピードアップが紹介され、内部のキャッシュインデックスの効果が説明されています。完全一致でも実用にふさわしい処理時間になったのでしょうか。
ピボットおじさんの実例は、データ件数が１０万件を超え、マスタからの検索項目が２０以上もある場合、完全一致で全列を再計算させると、Ｅｘｃｅｌ２０１０ではハングアップしました。データテーブルの構造化参照でも、同様な状況でした。 業務用には、近似一致での対応が確実と思います。
ピボットおじさんのVLOOKUP関数の秘訣
ピボットおじさんの秘訣は、以下の４つです。
①VLOOKUP関数は、近似一致
②IFERROR関数でエラー処理
③全行・全列同時再計算せず、列毎に分割して再計算を行う
④マクロで自動処理を行う
１０万件で２０項目の検索をする場合、 この手法で行うと Ｅｘｃｅｌ２０１０でも２分間程度、Ｅｘｃｅｌ２０１６では１分間程度という処理時間で計算できています。
PowerQueryで対応できないならVLOOKUP関数です！
Ｅｘｃｅｌ２０１３からピボットテーブルでは「リレーション機能」での検索が可能になりましたが、おじさんお薦めの「集計フィールド」が使えないという制約があります。
Ｅｘｃｅｌ２０１６からは、「PowerQuery」でピボットテーブル外で検索できるので、ピボットテーブルでは「集計フィールド」が使えるようになりました。皆さん、新しい機能を覚えて使うメリットはあると思います。
「PowerQuery」は、 検索に使う「マスタ」があることを前提としています。しかし、実際の業務は、どんどん変化しています。今ある「マスタ」が唯一ではなく、 もっといろいろな管理をしたいというユーザーの要望には応えていく必要があります。ピボットおじさんは、「PowerQuery」 でＶＬＯＯＫＵＰ関数を使った処理は減るとは思いますが、無くならない大事な関数だと思っています。
