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回目が遅いのは、最初の実行だからかと思われます)。

paizaの恋愛SLGの水着とサンタ服がゲットできない……


先日、paizaからプログラミングで彼女を作るゲームが発表されました。
恋愛SLG: プログラミングで彼女をつくる|paizaオンラインハッカソン7
参考:プログラミングさえできれば誰でも彼女がつくれる恋愛シミュレーションゲーム「プログラミングで彼女をつくる」 – GIGAZINE

内容は出題されたプログラミングの問題を解くと、その問題にあわせて彼女用のパーツや衣装をゲットできるというもの。

とりあえずコツコツやって、全部ゲットできたらブログに「彼女ができました」というエントリーでも書こうかと思ったのですが、何度やっても「水着」と「サンタ服」をゲットできないでいます。どちらも、提出前動作確認では合格するのですが、テストケースで失敗します(サンタ服のほうなんて、テストケース3までは合格しますが、4で失敗します)。テストケースはデバッグできないのでほとほと困っています。

試したコードは下記です。

これを実際にブラウザ上で動作するようにしてみたのが下記のページ
paizaのサンタ服と水着ゲットチャレンジ

水着ゲットチャレンジで38を入力すると742912となりますし、サンタ服ゲットチャレンジで問題ページの入力例2を入力すると240になり、何が間違っているのか全く分からないでいます。環境がNode.jsとブラウザ(Chrome)で違うというのがあるのかもしれませんが、paiza.IOでもうまくいっているように見受けられるので、多分環境が原因ではないと思います。

誰か失敗する原因が分かる方はヒントを教えて下さい(失敗するテストケース等)。

amazonの「持っています」に登録するブックマークレット作った


どうもいまだに、『amazon 持っています』と検索すると、このブログの過去記事が上位に表示されるようです(amzon 持っています – Google 検索)。
過去記事:amazonの「持っています」にチェックを入れる3つの方法 | while(isプログラマ)

ただし、上記記事を書いた時と今では登録方法が異なっています。いや、異なっているというより正確には減っています。上記ページで3種類の登録方法を書きましたが、うち2種類が現在使えなくなっています。1ヶ月ぐらい前まではウィッシュリストに入れて削除すると持っていますのチェックがでるので、それにチェックを入れると登録することができたのですが、それも無くなりました(参考:アマゾンの「持っています」登録方法: アマゾンの「持っています」登録が変更された!)。これにより、おすすめ商品に表示されている商品しか「持っています」に登録できなくなってしまいました。

これは正直困ります。困ったすえ、試行錯誤して、「持っています」に登録するブックマークレットを作ってみました。
ブックマークレット:持っています登録

使い方は簡単。上記リンクをブックマークバーにドラッグ&ドロップするか右クリックしてブラウザのお気に入りに登録します。その後、持っていますに登録したいamazonの商品ページに移動して、このブックマークをクリックすると、「持っています」に登録します。成功すれば、『登録しました』というアラートが表示されます(といっても、ログインしてなくても『登録しました』と表示されるので、後で「持っています」にチェックを入れた商品のページで確認したほうがいいです)。なお、削除機能はありません。削除は「持っています」にチェックを入れた商品ページで行ってください。

具体的なコードは下記のとおりです。

見て分かるとおりjQuery依存となっています。もし、amazonがjQueryを利用しなくなったらこのブックマークレットは利用できなくなります。後、amazonの仕様が変更されたら利用できなくなる可能性は十分あります。

それにしても何で、おすすめ商品に表示されてる商品しか持っていますに登録できなくなったんだ……。まあ、おすすめ機能だけ利用されて、商品を買うのは別の場所というのはamazonに利益がありませんしね(自分のことです……)。そもそも、これまでの流れからして、『持っています』の機能が無くなるんじゃないかと思わなくもないです。

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タグ追加されたけど、日本語以外のサイトでは使われてないのだろうか?

JavaScriptでlocationオブジェクトのようなオブジェクトを作成する関数を作ってみた


locationオブジェクトは現在表示しているページのURLの情報を含んだオブジェクトです(参考:window.location – Web API インターフェイス | MDN)。

ただ、リファラ(document.referrer)のように、自身のページ以外のURLもlocationオブジェクトのような情報を取得したいと思ったので、試しに作ってみました。正規表現使ったらもっと簡素にできるかもしれませんが、地道に検索して抜き出して……という感じで作っていきました。

引数にURLやパスを入れるとlocationオブジェクトのようなオブジェクトを返す関数です。ついでに、GETパラメータもオブジェクトとして取得するようにしています(参考:JavaScriptでGETパラメーターを取得する – Qiita)。

試しに、node.jsで”http://amyu.localhost:9000/hogehoge/index.html?a=a&b&c=c#zzz”というURLにアクセスできるようにし、locationオブジェクトと今回作った関数を比較してみました。開発者ツールのコンソールでcopyコマンドを利用し、それをペーストした結果です。

順番が異なっているのでわかりづらいですが、あってるようです。

ところで、今回この関数を作るにあたって、相対URLでも絶対URLに変換したうえで取得するようにしよう。そうすればaタグに記述している相対URLの情報も取得できるし。と思って調べたらaタグのhrefプロパティを取得すれば相対URLを入れていても絶対URLで取得できるということが分かりました(参考:JavaScript – 相対URLを絶対URL(URI)に変換する方法について – Qiita)。

なんだ、そんな簡単に相対URLを絶対URLに変換できるんだー! と驚きました。ただ、ここで試しておくべきだったんです……。a要素のプロパティに『hostname』や『pathname』があるのかどうかということを……。そうです。上記みたいな関数を作らなくても、a要素のプロパティにあるようなんです……。さっき知りました……。

参考:Location – Web API インターフェイス | MDN

まだまだJavaScriptは知らないことがあると実感しました。