コピー2016.02.13よく忘れるのでメモ。これは、表の中にある、特定の値(検索値)に対応する値を取得する関数なのですが、この関数は検索値よりも右側の値しか取得できません。最初、第三引数にマイナスの値を指定する右側の値を取得できると思って試したのですが、無理でした。このため、昔は表の右側に、検索値より左側の値を参照する式を追加していていました(検索値が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列に行番号を追記するというもの。その後、式のままだと問題があるので、式を結果で上書きしました(全体をコピーした後、右クリックで値貼り付け)。作成後は下記のようになっています。Sheet1Sheet2その後、VBAにて計測処理とSheet1のB列において検索式の追加処理、C1の値に処理を開始してから完了するまでの時間(秒)を記載する処理を追加。具体的には下記のような感じ。Sub上記はINDEXとMATCH関数の組み合わせのみ記載しましたが、VLOOKUP関数やOFFSETとMATCH関数の組み合わせでもほとんど同じです。5行目がVLOOKUP関数になるかOFFSETとMATCH関数になるかが違うぐらいです。結果は下記のような感じになります(環境Excel2010です)。以下、それぞれ5回ずつ試した結果。77.3421875ほぼ全く関係なさそうです。微妙に違いますが、誤差レベルと思われます。INDEX,MATCH関数の組み合わせよりOFFSET,MATCH関数の組み合わせのほうがちょっとだけ速いような気もしなくはないですが、気にするほどの差はないかと思われます(VLOOKUP関数の1回目が遅いのは、最初の実行だからかと思われます)。最近、仕事でVBAをいじってみて、その便利さと面白さに気づきました。ひらがなとカタカナの変換とか、半角と全角の変換とか......。それはともかく、今回はエクセルのVBAを用いて、自動的に指定のふりがなを振る方法について。下記...VBAExcelのVBAでニコニコ動画APIを読み込んでみるちょっとExcelでニコニコ動画のAPIを読み込みたいと思ったので、試してみました。下記のようなシートを用意。作りたいのは、A列の2行目以降に動画IDを書いて、その左側にその動画の情報を書き込んでいくというもの。コメントをキャンセルメールアドレスが公開されることはありません。(幻冬舎文庫)(ブクログレビュー)カテゴリーPR2Web制作76CSS16HTML8HTML512jQuery17Vue.js14WebAPI2WordPress6まとめ13フリーソフト7プログラミング101JavaScript61PHP5R10SQL10UiPath7VBA3クラシックASP1日記11イベント5書評1未分類2豆知識19雑記25アーカイブ2023年4月12023年3月12023年1月12022年12月12022年11月32022年10月22022年9月12022年8月12022年6月22022年5月12022年4月32022年3月22022年2月52022年1月82019年9月22019年6月32019年5月12019年2月32019年1月32018年12月42018年11月12018年10月22018年9月22018年8月22018年7月32018年6月12018年5月22018年4月22018年3月52018年2月12016年11月32016年10月62016年2月12016年1月32015年12月22015年11月32015年10月12015年9月12015年7月12015年5月22015年4月22015年3月22015年2月22015年1月22014年12月42014年11月92014年10月62014年9月52014年8月72014年7月62014年6月52014年5月52014年4月62014年3月62014年2月92014年1月132013年12月82013年11月92013年10月82012年8月12012年6月32012年5月42012年4月42012年3月62012年2月82012年1月17タイトルとURLをコピーしました。