ExcelでのVLOOKUPの代用(MATCH,INDEXとMATCH,OFFSET)と速度について


よく忘れるのでメモ。

知ってる人も多いと思いますが、ExcelにはVLOOKUPという関数があります(参考:VLOOKUP 関数 – Office のサポート)。これは、表の中にある、特定の値(検索値)に対応する値を取得する関数なのですが、この関数は検索値よりも右側の値しか取得できません。最初、第三引数にマイナスの値を指定する右側の値を取得できると思って試したのですが、無理でした。このため、昔は表の右側に、検索値より左側の値を参照する式を追加していていました(検索値がB列でほしい値がA列の場合、C列に”=A列の値”といった式を入力する)。

ただ、最近になって代用方法があるということを知りました。どうやら、MATCH関数とINDEX関数の組み合わせ、もしくはMATCH関数とOFFSET関数の組み合わせで代用可能とのこと(参考:徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技)。

簡単に説明すると、MATCH関数は指定の範囲から検索値が何番目にあるかを返す関す、INDEX関数は指定の範囲内にある指定の行数、列数から値を取得する関数、OFFSET関数は指定のセルと行数、列数から指定のセルの行数、列数分移動したところにある値を取得する関数です。

言葉だけでは分かりにくいと思うので、実際に試した結果の画像を下記に載せておきます。
エクセルで試した結果

F列がG列の式をセットした結果です。検索範囲はA列からC列、検索値はF1にある『近畿』です。見てもらえればなんとなく何をしているか分かるかと思います。F2,F5,F7では『近畿』という値をB列から検索し、その右側の値を取得して『7』という結果を得ています。F4,F6では『近畿』という値をB列から検索し、その左側の値を取得して『5』という結果を得ています。

直感的にはINDEX関数よりOFFSET関数のほうが意味合いが分かりやるいと思っています(INDEX関数って言われても、どういう関数なのかピンと来ない)。ただ、OFFSET関数は第二引数に-1を付けなきゃいけない(1引く必要がある)ので、自分は何度も間違えました。なので、慣れたらINDEX関数とOFFSET関数の組み合わせで対応するのがいいかと思います。

というより、そもそもこの組み合わせを分かっていたらVLOOKUP関数を使う必要はないかもしれません。たとえ探したい値が検索値より右側にあったとしてもINDEX関数とOFFSET関数さえ使っておけばいいんじゃないかと(もちろん、こっちのほうが少し冗長ですが)。

ただ、そこで気になるのが速度の問題です。関数が一つですむVLOOKUP関数にたいして、INDEX関数とOFFSET関数の組み合わせだと関数を二つ使うことになります。ということは、その分、速度も遅くなってしまうのではないかと。

というわけで、検証してみることにしました。用意したのはアルファベットをランダムに3文字並べた文字列50万個をSheet1とSheet2の二つのシートのA列に記載し、Sheet2のほうにはB列に行番号を追記するというもの。その後、式のままだと問題があるので、式を結果で上書きしました(全体をコピーした後、右クリックで値貼り付け)。
参考:アルファベットをランダムに表示したい−CHAR関数・RANDBETWEEN関数:Excel(エクセル)の関数・数式の使い方-文字列

作成後は下記のようになっています。
Sheet1
Sheet1
Sheet2
Sheet2

その後、VBAにて計測処理とSheet1のB列において検索式の追加処理、C1の値に処理を開始してから完了するまでの時間(秒)を記載する処理を追加。具体的には下記のような感じ。

上記はINDEXとMATCH関数の組み合わせのみ記載しましたが、VLOOKUP関数やOFFSETとMATCH関数の組み合わせでもほとんど同じです。5行目がVLOOKUP関数になるかOFFSETとMATCH関数になるかが違うぐらいです。

結果は下記のような感じになります(環境Excel2010です)。
結果

以下、それぞれ5回ずつ試した結果。

VLOOKUP実行 INDEX_MATCH実行 OFFSET_MATCH実行
1回目 78.453125 77.2578125 77.234375
2回目 77.3046875 77.5 77.2734375
3回目 77.359375 77.6171875 77.421875
4回目 77.5390625 77.59375 77.4140625
5回目 77.4296875 77.75 77.3671875
平均 77.6171875 77.54375 77.3421875

ほぼ全く関係なさそうです。微妙に違いますが、誤差レベルと思われます。INDEX,MATCH関数の組み合わせよりOFFSET,MATCH関数の組み合わせのほうがちょっとだけ速いような気もしなくはないですが、気にするほどの差はないかと思われます(VLOOKUP関数の1回目が遅いのは、最初の実行だからかと思われます)。

ExcelのVBAで漢字の名前にふりがなを振る方法


最近、仕事でVBAをいじってみて、その便利さと面白さに気づきました。ひらがなとカタカナの変換とか、半角と全角の変換とか……。

それはともかく、今回はエクセルのVBAを用いて、自動的に指定のふりがなを振る方法について。

例えば下記のように、A列に苗字と名前を全角スペースで分けた漢字で書いた名前があり、B列にA列の名前のふりがながあるとします(こちらも苗字と下の名前は全角スペースで分けてある)

名前参考サイト:
同姓同名探しと名前ランキング | 姓名、名前、名字、苗字の分布と由来
赤ちゃん名付け実績No.1/無料 赤ちゃん名づけ|キラキラネームランキング

この時、A列の漢字での名前のほうに、B列のふりがなを振っていくにはどうすればいいか。
その前にエクセルには自動でふりがなを振ってくれる機能があるのでやってみました。

最初のほうは一致してますが、最後のほうはダメでした。

というわけで、ふりがなを自動的に振っていく方法を調べてみると、PhoneticCharactersというプロパティに指定のふりがなを代入すると、ふりがながセットされることが分かりました。

以下、実装したコード(プロシージャ名が日本語になってます。初めて見た時は慣れませんでした)。

以下、実行結果。

ふりがなを振ることができました。

以下、おまけ。

VBAで特定のセルにセットされているふりがなを取得したいことがあると思います。調べてみると、Application.GetPhoneticというメソッドを使うと取得できると分かり、A10セルをアクティブにした後、下記のスクリプトを実行してみました(参考:VBAでふりがなを取得する方法)。

以下、実行結果

なんと、セットしたふりがなではなく、最初に自動で割り振られたふりがなの値を取得しました。VBAでセルにセットしたふりがなを取得したい場合は、Application.WorksheetFunction.Phoneticを利用しなければいけないようです。

以下、実行結果
furigana3

ところで、ふりがなって日本以外の国でもあるのだろうか? 英語版のWikipedia(Furigana – Wikipedia, the free encyclopedia)を見ると、日本独特の文字っぽいけど。HTML5にrubyタグ追加されたけど、日本語以外のサイトでは使われてないのだろうか?

ExcelのVBAでニコニコ動画APIを読み込んでみる


ちょっとExcelでニコニコ動画のAPIを読み込みたいと思ったので、試してみました。

まずは、下記のようなシートを用意。
VBA実行前
作りたいのは、A列の2行目以降に動画IDを書いて、その左側にその動画の情報を書き込んでいくというもの。

VBAはほとんど触ったことがないので、Rangeプロパティだとか、Offsetプロパティだとかの概念がよく分かってなかったのですが、なんとか作ることができました。

XPathにはあまりなれてないのですが、属性で判断するカテゴリータグを取得したいということもあり、XPathを利用することに。
19行目で動画情報が取得できたかどうかの可否、25行目でユーザーIDがあるかどうかの可否、28行目でカテゴリータグがあるかどうかの可否を調べて条件分けしてます(動画情報が取得できたどうかは、If文じゃなくて、For Each文を使ってるので、間違ったつかいかたかもしれません)。

というわけで、実行してみました。
VBA実行後
相変わらず、カテゴリーがうまく取得できていないところがありますが、他は問題なくいけたようです。
ありえない動画ID(この例では、sm1とaiueo)は動画情報を取得していませんし、ユーザーIDやカテゴリーも見つからなかったものは飛ばしているよう。数値のみの動画IDの動画情報もうまく取得できました。
今気づいたんですが、カテゴリータグってタグの一番目とは限らないんですね。てっきり、一番目なのかと・・・。ということは、カテゴリーが取得できない場合、一番目のタグを見て判断するという方法は使えないのか・・・。

ところで、自分が使ってるExcelはExcel2010なのですが、Excel2013だとWEBSERVICE関数なんていう便利なものがあるよう(参考:Excel 2013で追加された「WEBSERVICE」関数を使って、マクロを使わずWeb APIを利用する。 | 初心者備忘録)
何これ。すごい便利そう。