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

[`evernote` not found]
[`livedoor` not found]
[`yahoo` not found]

よく忘れるのでメモ。

知ってる人も多いと思いますが、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で漢字の名前にふりがなを振る方法

[`evernote` not found]
[`livedoor` not found]
[`yahoo` not found]

最近、仕事で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で『未』という文字を書いたセルをオートフィルした結果

[`evernote` not found]
[`livedoor` not found]
[`yahoo` not found]

今回は小ネタ。知っている人も多いかもしれませんが、会社で偶然発見してビックリしたので。

Excelで、ある予定表のようなものを作成し、まだやっていないものは右側にまだやってないという意味の『未』と書く表をつくろうと思いました。例えば下記のような感じ。
autofill1

ところで、Excelでは特定のセルを選択し、その右下にポイントをあわせてドラッグするとそのセルの内容や、特殊な値の場合は連続したデータがコピーされます。これをオートフィルというよう。
例えば、”hoge”と入力されているセルを下にオートフィルすると”hoge”という文字が並んだセルの列ができます。また、”1月”というセルを下にオートフィルすると”2月”,”3月”といった具合に連続した意味のある内容が入力されます。”=A1″というセルを下にオートフィルすると”=A2″,”=A3″というようになります。”1″の下に”2″と書かれたセルの両方を選択して下にオートフィルすると、”3″,”4″,”5″となります。”1″,”3″だった場合は、”5″,”7″,”9″と等差数列になります。じゃあ、”1″,”2″,”4″の3つを選択してオートフィルしたら、等比数列を判定して”8″,”16″,”32″となるのでは! と期待しましたが、そうはなりませんでした・・・。
autofill2
いまいちよく分からない・・・。4つ目以降は等差数列になってるようなのだけれども・・・(多分、1つ目と2つ目の差の数と、2つ目と3つ目の差の数の二つの数の平均の等差数列になってる)。
参考:Excel2010(エクセル2010)基本講座:オートフィル

で、本題。予定表の右側に『未』と書いた列を作ろうとして『未』と書かれたセルをオートフィルするとどうなったか。
こうなりました。
autofill3
一瞬、何が起こったのか分かりませんでした。しばらくたってようやく理解。干支のオートフィル機能なんてものがあるんですね。ところで、未ってなんだっけ? 『ミ』だからへび年? いや、それは巳年か(正解は、ひつじ年だそう)。

仕方がないので、上二つのセルに『未』と書いて2つとも選択し、オートフィルすることに。
autofill4
調べてみると、こんなことせずにコントロールキーを押しながらオートフィルすると『未』というセルだけの列ができるようです。
まだまだExcelには知ってるようで知らないことがありそうな気がします。

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

[`evernote` not found]
[`livedoor` not found]
[`yahoo` not found]

ちょっと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を利用する。 | 初心者備忘録)
何これ。すごい便利そう。

単語の暗記に最適!? Excelを用いた勉強術

[`evernote` not found]
[`livedoor` not found]
[`yahoo` not found]

特に自慢できるわけじゃないのですが、僕は基本情報技術者という資格を持っています。取得したのは確か、2008年秋の試験。ちょうど就活を始めることもあって、資格欄に書くものが何もないからと受験したような気がします。
 その時に何か効率的に勉強できないかと考えて編み出したExcelを用いた勉強方法を紹介します。就活中に話して何人かに「いいアイデア」といってもらえたことがあるので。

1.項目名を書きます。以下のようにA1に『答え』、B1に『問題』、C1に『解答欄』、D1に『正誤判定』、E1に『ランダム値』と入力。
項目名行

2.A列に問題の答え(例では英単語)、B列に問題(例ではA列に書いた単語の日本語訳)、E列はE2に『=IF(D2=”○”,1,RAND())』と入力。最後の問題の行までオートフィルします(オートフィルについてはこちら:オートフィルって?)。
答え、問題、ランダム値入力

3.A列を文字が見えなくなるまで狭めます。幅0でも構わないのですが、0だと矢印キーですらA列のセルを選択できないそうなので、0より大きい幅がいいと思います。
A列幅狭く

4.E列を選択して、昇順で並べ替えます。すると、問題の順序がバラバラになります。
E列並べ替え

5.C列の解答欄にB列の解答を入力します。
回答入力

6.D2に『=IF(A2=C2,”○”,”×”)』と入力、問題がなくなる行までオートフィルします。すると、解答欄に入力した解答が正解ならば”○”、違うならば”×”と表示されます。
正誤判定

7.再度、E列を選択して昇順で並べ替えます。すると、先ほど間違えた問題のみ上に集まります。そのため、再度C列に解答を入力していくときに、先ほど間違えた問題のみ効率よく解答できるようになります。
再解答

面接でこのやり方を話したら、「それはいいアイデア」とは言ってもられたのですが、結局次の選考に進めませんでした・・・。

Excelは使いようによって、便利に使えると思います。自分の場合、マクロには詳しくないので、マクロを使えばもっといい方法があるかもしれません。

多分いないと思いますが、一応パクリと思われないように言っておくと、ほとんど同じことを昔、id:yuu665のはてなダイアリーで書いています。