January 22, 2015

Excelで文字列を最後から検索し、最後にヒットする位置を返すには

テキストを処理しているときに、文字列中である文字が最後に現れる位置を検索したいと思ったのですが、ExcelにはJavascriptでいうところのlastIndexOfにあたる関数が無いようです。そこで、web上を調べると、メジャーな方法としては、以下のようにワークシート関数であるFIND、LEN、SUBSTITUTEを組み合わせた方法が紹介されています。
=FIND(sub_text,SUBSTITUTE(within_text,find_text,sub_text,LEN(within_text)-LEN(SUBSTITUTE(within_text,find_text,""))))
find_text:検索する文字
within_text:検索対象の文字列
sub_text:最後に現れるfind_textを一時的に置き換えるための文字(within_textに含まれない文字で、かつ、find_textと異なる文字)
この処理は中々トリッキーで、SUBSTITUTE関数が、対象文字をすべて置き換える、あるいは、n個目のヒットのみ置き換える、という動作をすることを利用しています。まず、SUBSTITUTEですべてのfind_textを消去し、文字数をLEN関数で確認することで、find_textの個数を確認します。そして、最後にあるfind_textをsub_textに置き換え、最後にFIND関数でsub_textの位置を探しているわけです。
ワークシート関数のみで処理を実現しているという点は素晴らしいかもしれませんが、こうして日本語で書き下してみても、あまりすっきりとした処理方法とは到底言えません。そこで別の方法を考えてみます。
  1. 名前を定義して左に隣接するセルから、特定の文字が最後に現れる位置を返すようにする
  2. 上記のワークシート関数をVBAでユーザー定義関数とする
  3. 初めからVBAの機能を活かしたシンプルなユーザー定義関数を考える
いずれの方法でも、セルに直接数式を書くよりはシンプルになるのですが、
1の方法では任意のセルで実行できず、使い勝手が悪い、
2の方法ではVBAを使うのに結局、処理の中身は分かりにくいままです。
そこで、3の方法として以下のように関数を定義することにしました(セキュリティの問題もあるしマクロは現実的じゃない、場合の選択肢→名前を定義する)。VBAエディタを開き、標準モジュールを追加して以下のように記載します。
Function FINDFROMLAST(find_text As String, within_text As String) As Integer
    within_text = StrReverse(within_text)
    FINDFROMLAST = Len(within_text) - WorksheetFunction.Find(find_text, within_text) + 1
End Function
処理としては非常に単純で、まず、受け取ったwithin_text文字列をStrReverse関数で反転させます。反転した文字列からワークシート関数のFIND関数でfind_textを検索すると、FIND関数の仕様上、最初にヒットした位置(=元の文字列で後ろから数えた位置)が返ります。そこで、LEN関数で元の文字列の長さを求め、先ほどヒットした位置を引くと、元の文字列within_text中で最後にfind_textがヒットする位置を求めることができます。

使い方は、ただ単にセルに=FINDFROMLAST(find_text, within_text)と入力するだけで、FIND関数と同等の使い勝手で最後から文字列を検索することができます。

なお、ユーザー定義関数の弱点としては、マクロ有効ワークブックとして保存する必要があること、built-in関数のようにツールチップを表示できないため、関数の使い方を事前に説明を用意するか、コードを見て理解してもらう必要があるところです。ちなみに、Application.MacroOptionsオブジェクトを使うと関数ダイアログには説明文を追加できますが、残念ながらセル入力時のツールチップを設定することはできないようです。

No comments :

Post a Comment