こんにちは。nuieです。今日は少し真面目にEXCELの話をします。
実は5年くらい前にEXCELの使い方を教える仕事をしていました。
最近業務でのEXCEL頻度が減っていてあまり新機能の研究ができていなかったのですが、「XLOOKUP関数」なるものが今度新しく使えるようになると聞き、思わずEXCELのテスト版を入れて試してみました。
XLOOKUP関数とは
XLOOKUP関数とは、MicrosoftのJoe McDaid氏によると、VLOOKUP関数の後継版となる関数らしいです。
We are excited to announce XLOOKUP, successor to the iconic VLOOKUP function.
VLOOKUP関数の後継と言われたものの、垂直にも水平にも検索をするため、HLOOKUP関数の後継も兼ねていますね。
一番シンプルな使い方をする場合、関数の使い方は以下のようになっています。
XLOOKUP(検索値,検索範囲,戻り配列)
- 検索値とは：探す対象(今までのVLOOKUP/HLOOKUPと同じ)
- 検索範囲とは：検索値が含まれると思われる場所(今までのVLOOKUPで指定した範囲の左端列、HLOOKUPの上端列だけを選ぶ感じです)
- 戻り配列とは：関数の答えとして表示させたい場所
VLOOKUP関数やHLOOKUP関数と比較すると、表示させたいものの探し方が大きく異なりますね。「列番号」がなくなり、シンプルに表示させたい範囲を選択する形式になっています。
もう一つ大きな違いがあります。XLOOKUPは、たいていのケースで第四引数「一致モード」を省略してもよいのです。なぜなら、省略すると「完全一致」で検索をするから。
VLOOKUP関数/HLOOKUP関数は、第四引数「検索方法」は、
TRUE：近似値一致/引数省略した場合こちら
FALSE：完全一致
となっておりました。
業務内容にもよりますが、VLOOKUP関数やHLOOKUP関数は、完全一致で検索される場合が大半。
通常は、第四引数に「FALSE」か、FALSEを意味する「0」を入れています。
XLOOKUP関数は、省略が完全一致なので、通常利用であれば省略でき、少し業務効率が上がります。
なお、XLOOKUP関数を省略なしで書こうとすると、以下の5引数となります。
XLOOKUP(検索値,検索範囲,戻り配列,一致モード,検索モード)
- 一致モードとは：検索の方法。0/1/-1/2が指定可能。デフォルトは0の「完全一致」。
- 検索モードとは：検索範囲を左/上から検索する(1)か、右/下から検索する(-1)かの違い。検索範囲のセルの中身に重複があった場合、表示される答えに違いが出てくる。また、2/-2を指定するとバイナリ検索となる。普通の事務職にはあまり縁がないモード。デフォルトは1。
個人的に業務で一番迷いやすそうなのは一致モードの「2」。
VLOOKUP関数/HLOOKUP関数ではデフォルトで使えた、ワイルドカード機能がXLOOKUP関数では一致モードに「2」を指定しないと使えません。私の業務ではワイルドカードでVLOOKUP関数を使う機会が多いので、気をつけねば。
ただし、おおむねVLOOKUP関数よりも使いやすくなっている印象です。これが34年の進化か。
おまけ：VLOOKUP関数について
VLOOKUP関数は1985年にリリースされた初代EXCELから実装されており、34年間ずっと使い続けられているのだとか。
私も現在業務で1日10～1000回(コピペで)くらい使っている関数なのですが、上記の記事によると、EXCELの全関数のうち、3番目に使用頻度が高いらしいです。(1位と2位はSUM関数とAVERAGE関数)
EXCELで事務職をやるのなら、使えて当たり前。そんな有名な関数なのですが、引数が4種類でコピペが前提となるため相対参照・絶対参照の知識が必須と、初心者には少々ハードルの高い関数です。
実際にXLOOKUPを使ってレポート用のデータを作ってみる。
私は今のお仕事がいわゆる企業内WEB屋さんなので、EXCELで出す数字はホームページの実績系が多くなります。
その中で、アクセス解析データのレポートを作ることを想定して、実際にXLOOKUPを使ってみることにします。
こちら、数字はダミーデータですが、Google Analyticsで計測する、「トラフィック」のデータのうち、「メディア(ga:medium)」の計測です。セカンダリディメンションとして「地域(ga:region)」を放り込んで、エクスポートした、という流れ。
ただ、これでそのままピボットに放り込んでも上司や同僚からは「読みづらい」と言われてしまいます。
都道府県、ローマ字ですからね……
このローマ字をちゃんと漢字に変えてやる、というところでXLOOKUPを使ってみましょう。
github gistに日本の都道府県名のヘボン式ローマ字リストCSVがあるので、これを使わせていただきます。
また、「organic」「display」と言われてもわからない人もいるので、Waculさんの記事『Googleアナリティクスの「参照元」とは? 【5分でわかる!】』から説明表を拝借。
最後に、営業地域別になるように都道府県をもう少し大きな地域分類します。
いわゆる一般的な八地方区分でもよかったのですが、今回は天気予報に使われる気象庁の一時細分区域で行きましょう。
そしてできた変換シート「list」がこちら。
左の三列分が都道府県のローマ字→漢字変換。F~H列がメディアの返還。右に見えるのが都道府県→地域の変換表ですね。
使ったEXCELファイルは以下からダウンロードできるようにしておきます。
さて、まずはローマ字の都道府県を漢字に変換していきます。
これだけです。
実務上Google Analyticsは海外からのアクセスも計測するので、実際はその分エラーが出ます。エラーが出たらまとめて「その他」に放り込むとか、IFERROR関数を噛ませるとか、地域のデータを海外対応にするなどの手法がありますが、今回は省略。
VLOOKUP関数と比べると、作業自体は1手短縮です。
その箇所は、最初。VLOOKUP関数は「list」の都道府県名漢字変換テーブルとは非常に相性が悪いのです。なぜかというと、変換元のローマ字よりも変換先の漢字が左にあるからです。
VLOOKUP関数の弱点の一つ。検索値が入っている列は、必ず左になければならないのです。
ではVLOOKUP関数の場合、どのようにすればよいか。一番簡単なのは計算列を使うことですね。
D列に計算列を作り、CD列でVLOOKUP関数をすれば早いです。
INDEX関数+MATCH関数を手癖で書けるなら、もちろん使った方がよいでしょう。
XLOOKUP関数の場合、この計算列を作る作業が省略できるわけですね。
続いてメディアの日本語説明をつけます。
ここはVLOOKUP関数を使う場合とあまり変わらないですね。
最後。地域もくっつけちゃいましょう。
ここだけ、あえてHLOOKUP関数向けの表を参照してみました。
ただ、やることは一緒ですね。
ちなみに、「list」の表は検索値が下、戻り値が上になるため、HLOOKUP関数で表示させるには計算行が必要です。
さて、ここまでくればピボットでレポートが作れます。
地域ごとで流入比較ができるわけですね。
あとは定番のレポートフィルターページを使って地域ごとに分析したりもできるでしょう。
結論としては、データの加工用としては非常に優秀で、VLOOKUP関数の後継として十分に使っていける性能があるように思います。ただし、「計算速度が少し遅く、INDEX関数+MATCH関数の方が使いやすい」場面があったり、「ワイルドカードを使う場合、VLOOKUP関数と全く同じ感覚では使えない」点があるのは若干気になります。これは慣れでなんとかなるレベルですが。
このXLOOKUP関数、まだ一部のベータテストユーザーしか使えませんが、早く広まってほしいですね。この機能が10年前にあったら、多分私の授業内容もガラリと変わったと思います。
コメント