今回のまとめ
・VLOOKUP関数は、「①検索値、②範囲、③列番号、④検索の型」という4つの「引数」を設定する必要がある。・VLOOKUP関数では、「見つけ出したい項目」は必ず引数①「検索値」と「突合される列を含む右側」に引数②「範囲」を設定する必要がある。
・今後はVLOOKUP関数とHLOOKUP関数の両方の機能を併せ持つXLOOKUP関数が主流となるだろう。
【第2回】監査や経理担当者が知っておくべき!『モダンExcel』データ分析超入門(全10回)
監査技術の一つに「突合」があります。「突合」とは、AとBを突き合わせ、正確な記録が行われているか等を確かめる、基本的な監査技術です。
たとえば、実地棚卸の結果と在庫在高帳を「突合」して、不一致が判明すれば、商品や製品などの棚卸資産が横領されている可能性があることなどを把握できます。言い換えれば、あるべき「入・出・残」から異常点を把握する必要があるわけです。
この「突合」が、データ分析の基本動作の一つです。データ分析の現場では「データ突合」として活用されます。
なお、数字は「P×Q」、つまり「Price(単価)」と「Quantity(数量)」に分けて考えると良いでしょう。詳細は、拙著『CAATで粉飾・横領はこう見抜く』(中央経済社)などに委ねます。
経営管理業務で役立つレガシーエクセルの機能のひとつが、VLOOKUP(ブイルックアップ)関数というデータ検索の関数です。
VLOOKUP関数は、「縦方向/垂直」(Vertical)に、検索(Look Up)する「関数」で、条件に合致したデータを抽出できます。これは、CAAT(Computer Assisted Audit Techniques、コンピュータ利用監査技法)により異常点をつかむ基本の一つでもあります。
図2のように、B列に「得意先コード」はあっても「得意先名」がない場合、それぞれのデータ項目を持つ「得意先マスタ」のような別のデータから該当データを探し出し、D列に「得意先名」を表示できるようにするのがVLOOKUP関数です。
VLOOKUP関数では、「検索値、範囲、列番号、検索の型」という4つの「引数(ひきすう)」を設定する必要があります。
図2をご覧ください。「得意先コード」からVLOOKUP関数で紐づけ、「得意先名」が表示できるようにするには、セルD2「=VLOOKUP(①B2,②得意先マスタ!A:B,③2,④FALSE)」のように、①から④の4項目を「引数」として指定する必要があります。
VLOOKUP関数を適用するには、対象となる項目(ここでは「得意先コード」があるB列)を昇順に「並べ替え(ソートともいいます)」、連続データにする方が良いでしょう。
並べ替えをせずともVLOOKUP関数の引数④を「FALSE(完全一致)」とすれば結果は得られます。
ただし、並べ替えをしない場合、検索に時間がかかることがあります。たかだか数千行程度の「ごく小さなデータ」であっても、パソコンのスペック(性能)次第では、相当難儀することもあります。したがって、VLOOKUP関数では、並べ替えも考慮して欲しいと思います。
データの並べ替え(ソート)をすると元データがバラバラになってしまうので、工夫も必要になります。元のシートをコピーするか、任意の列(作業列と呼ぶことがあります)にあらかじめ「連番(インデックス)」を付すと元データを再現でき便利です。
エクセルにはHLOOKUP関数という「横方向/水平」(Horizontal)に検索(Look Up)する関数もあります。
ただし、財務会計データは「1行1列1項目のきれいなデータ」を対象にすることが多いので、このHLOOKUP関数を使う場面は限られます。したがって、今回ご紹介したVLOOKUP関数を含め、エクセル初学者は、第1回でご紹介した程度の関数や機能を理解されると良いでしょう。
ちなみに、サブスクリプション「Office365」とパッケージ版「Excel2021」以降には、「XLOOKUP」という新しい関数が追加されています。
XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数の両方の機能を併せ持つ大変便利な関数で、操作も比較的簡単なので、今後このXLOOKUP関数が主流になってくるでしょう。
VLOOKUP関数、HLOOKUP関数、XLOOKUP関数、これらを「三位一体」で理解することを促す、幣ブログ後掲のリンク先「XLOOKUP 関数(VLOOKUP関数、HLOOKUP関数、合わせて理解!)」も参考にしてください。
上述のとおり、VLOOKUP関数では、引数①「検索値」が突合するデータを必ず引数②「範囲」の一番左の列(事例では「得意先マスタ」のA列)に設定するのがポイントでした。
これが、VLOOKUP関数「最大の弱点」でもあります。
仮に元データで、検索値(引数①)の左側に「見つけ出したい項目」「跳ね返したいデータ」(引数②の「範囲」並びに引数③の「列番号」の組み合わせ)がある場合、「作業列」というVLOOKUP関数を動作させるため「だけ」の列をわざわざ作成する必要が生じます。
これにより、「1列」以上の「作業のためだけの列」をワークシート上に設けなければならず、その分だけデータが増え、結果としてデータ処理に負荷がかかり、フリーズ(パソコンが固まってしまう現象)などの原因にもなってしまいます。
こうしたVLOOKUP関数の弱点を克服するため、INDEX関数とMATCH関数を組み合わせVLOOKUP関数の代替とする方法なども、エクセルに詳しい人によって編み出されています。次回ご紹介します。
(参考資料等)
拙著『CAATで粉飾・横領はこう見抜く』(中央経済社)
https://amzn.to/3ATjyx2
幣ブログ「モダンExcel研究所」
「XLOOKUP 関数(VLOOKUP関数、HLOOKUP関数、合わせて理解!)」
https://excel.value.or.jp/?p=475
日本公認会計士協会東京会コンピュータ委員会委員長、経営・税務・業務各委員会委員、第三者委員会委員などを歴任。研究テーマ『CAAT(コンピュータ利用監査技法)で不正会計に対処する、エクセルを用いた異常点監査技法』で日本公認会計士協会研究大会に選抜。
『3000社の決算を分析してきた会計士が教える 経営を強くする 会計7つのルール』(ダイヤモンド社)
『Excelによる不正発見法 CAATで粉飾・横領はこう見抜く』(中央経済社)
『会計ドレッシング10episodes』(東洋経済新報社)
『モダンExcel入門 データ分析&可視化の新しい教科書』(日経BP)など
10問の質問に回答するだけで、
性格タイプやキャリア志向が明らかに!
ジャスネットキャリア編集部
公認会計士 齊藤 健太郎
公認会計士 江黒 崇史
公認会計士 ジャスネットキャリア編集部
公認会計士 ジャスネットキャリア編集部
ジャスネットキャリア編集部
公認会計士 江黒 崇史
ジャスネットキャリア編集部
ジャスネットキャリア編集部
公認会計士 福留 聡
公認会計士 福留 聡
公認会計士 野村 宜弘
公認会計士 三宅 博人
公認会計士 三宅 博人
公認会計士 三宅 博人
公認会計士 三宅 博人
公認会計士 三宅 博人
ジャスネットキャリア編集部
公認会計士 三宅 博人
公認会計士 三宅 博人
公認会計士 三宅 博人
公認会計士 三宅 博人
公認会計士 三宅 博人
公認会計士 荻野 光
公認会計士 三宅 博人
ジャスネットキャリア編集部
公認会計士 新開 智之
公認会計士 江黒 崇史
不動産鑑定士 冨田 建
公認会計士 新開 智之
公認会計士 新開 智之
公認会計士 新開 智之
公認会計士 新開 智之
公認会計士 村井 直志
公認会計士 村井 直志
公認会計士 新開 智之
公認会計士 新開 智之
公認会計士 新開 智之
公認会計士 新開 智之
公認会計士 新開 智之
公認会計士 村井 直志
公認会計士 新開 智之
公認会計士 冨田 建
公認会計士 新開 智之
公認会計士 冨田 建
公認会計士 江黒 崇史
公認会計士 江黒 崇史
公認会計士 Rody
公認会計士 重見 亘彦
公認会計士 重見 亘彦
公認会計士 重見 亘彦
公認会計士 江黒 崇史
公認会計士 小林 正和
公認会計士 福留 聡
公認会計士 小林 正和
公認会計士 福留 聡
公認会計士 西濱 絢
公認会計士 西濱 絢
公認会計士 福留 聡
公認会計士 福留 聡
公認会計士 小林 正和
公認会計士 福留 聡
公認会計士 石動 龍
公認会計士 江黒 崇史
税理士 小島 孝子
公認会計士 松本 佑哉
公認会計士 江黒 崇史
公認会計士 江黒 崇史
公認会計士 福留 聡
公認会計士 伊藤 英佑
公認会計士 福留 聡
公認会計士 山本 真美子
公認会計士 岩波 竜太郎
公認会計士 M.N
公認会計士 冨田 建
公認会計士 都外川 雅門
公認会計士 横山 敬子
公認会計士 福留 聡
公認会計士 安田 憲生
公認会計士 GTR
公認会計士 江黒 崇史
公認会計士 江黒 崇史
公認会計士 江黒 崇史
公認会計士 福留 聡
公認会計士 中島 英明
公認会計士 R.H
公認会計士 高橋 善也
公認会計士 白土 英成
公認会計士 三宅博人
公認会計士
公認会計士の資格に関するカテゴリーごとにまとめたページです。
カリスマ公認会計士が語る
5人の会計士が伝授!
試験の乗り越え方と合格後について
自ら転職を経験した会計士たちが、
タイプ別に自分を活かす働き方を解説
プラスαで身につけるべきスキルは?
ダブルライセンスを持つ会計士が指南
転職や独立したら年収はどうなる?
開業会計士が教える成功のノウハウ
辞めた後のキャリア、デザインできていますか?
今だから語れる「私が監査法人を辞めた理由」
4,000社を超える取引実績から分析!
公認会計士を募集する求人動向
公認会計士4,000人の相談実績から分析!
あなたの次のステップは?
ここが違う、公認会計士のための
応募書類と面接対策
公認会計士が〇〇してみた。
実体験を交えたトレンドを解説
「Accountant's Magazine」は、著名な会計プロフェッションにスポットをあて、その人生観・仕事観を紹介。会計・経理分野に従事する人と仕事の将来像を提示する、読者と共に考えるヒューマンドキュメント誌です。今なら新規登録していただくと、「Accountant's Magazine」(WEB版)の全記事を無料で閲覧することができます。
10問の質問に回答するだけで、
性格タイプやキャリア志向が明らかに!
10問の質問に回答するだけで、
性格タイプやキャリア志向が明らかに!