January 25, 2015

名前を定義してExcelで疑似関数を作成するには

エクセルのワークシート関数は様々な処理を比較的高速に実行でき、組み合わせ次第で複雑な処理も可能なのですが、一方で、変数が使えずタブによるインデントやスペースを使った整形もできないため、長い式になると見た目が分かりにくくなる、という欠点もあります。

これを解決する手段としては、王道はVBAエディタを使ってマクロを定義してしまうことなのですが、名前を定義して疑似関数を作成するという方法もあります。名前定義の優れている点は、マクロとはちがって通常のワークブックとして保存されるため、エンドユーザーにセキュリティ上の懸念が生じないということです。一方で、関数機能がセルから名前マネージャに追い出されるため、情報が分散するため、式の検証が若干面倒になるという点でしょう(それでもトレース機能は名前定義で参照されているセルについても追いかけてくれる…これは予想外に優秀。)。

ここでは一例として、A1セルに含まれる文字列の中から、最後の半角空白文字を探すし、空白文字以降を切り出す例を挙げます。
=RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

同じことを名前定義を使うと以下のように書くことができます(一例です)。
=RIGHT(A1, FINDFROMLAST)

ここでは、疑似関数FINDFROMLASTを名前として定義します。具体的な方法としては、B1セルを選択した状態で数式リボンの名前を定義をクリックし、名前定義ウィンドウを表示します。 定義する名前をFINDFROMLASTとし、参照先に以下のように入力します。
=LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1

ここで、注意すべき点としては、上記の式は今回のケースではB1セルを選択して、名前を定義する場合です。さらに、式中のすべてのセル参照がA1といった形で相対参照になっていることに注意してください。名前の定義として、相対参照で記述すると定義したセルとは異なるセルで名前を使った場合に、相対参照が維持される点が、この方法のキモです。

さて、定義した疑似関数FINDFROMLASTの使い方ですが、適当なセルに半角空白を含む文字を入力します。そして、その右隣のセルに
=FINDFROMLAST
と入力してみると…最後に見つかった空白の位置が返ってきますね。

なお、同じことをVBAで関数定義したい場合は→Excelで文字列を最後から検索し、最後にヒットする位置を返すには


No comments :

Post a Comment