数式を文字列に、文字列を数式に変換するExcel処理

 FORMULATEXT関数と数式評価のユーザー定義関数で「数式⇔文字列」の相互変換！

今回は、数式を文字列に変換する「FORMULATEXT関数」と、文字列型の数式を評価するユーザー定義関数の作成方法について深掘りしていきます。

FORMULATEXT関数の基本操作と活用方法

FORMULATEXT関数は、指定したセルに入力された数式をテキスト・文字列型データとして取得することができる関数です。これにより、セルにどのような計算が行われているかを簡単に確認できるようになります。

例えば、A1セルに「=SUM(B1:B10)」という数式が入力されている場合、C1セルに「=FORMULATEXT(A1)」と入力すると、「SUM(B1:B10)」というテキストがC1セルに表示されます。

なお、この関数には注意点もあります。指定したセルに数式が入力されていない場合、#N/Aエラーが返されます。

数式を評価するユーザー定義関数の作成方法

次に、テキスト形式の数式を評価し、その計算結果を返すユーザー定義関数の作成方法について見ていきましょう。Excelにはこのような関数は用意されていないため、VBAを使用して自身で関数を作成する必要があります。

まず、VBAエディタを開き(Alt+F11で開けます)、新しい標準モジュールを追加します(挿入→標準モジュール)。次に、以下のVBAコードを標準モジュールに貼り付けます。

Function EVALUATE_FORMULA(formula As String)
    On Error Resume Next
    EVALUATE_FORMULA = Evaluate(formula)
End Function

これで、Excelのセルで「=EVALUATE_FORMULA(数式テキスト)」というユーザー定義関数を使って、テキスト形式の数式を評価できるようになります(関数リストの「ユーザー定義」グループにセットされる)。

例えばFORMULATEXT関数で作成した文字列がセルC1にあるケースにて、

=EVALUATE_FORMULA(C1)

を生成すれば、セルC1の文字列が数式として評価されて、計算結果が返ります。サンプルではセルE1へ上記の式を作成しました。

ただし、無効な式を評価しようとすると、#VALUE! エラーが返される点に注意が必要です。

また、様々な活用も可能です。以下のサンプルはC1の文字列内にある「SUM」をSUBSTITUTE関数を用いて「AVERAGE」に置き換えて数式として評価しています。

=EVALUATE_FORMULA(SUBSTITUTE(C1,"SUM","AVERAGE"))

この流れは

  1. FORMULATEXT関数で数式を文字列にした
  2. その文字列の一部をSUBSTITUTE関数で別の文字へ置き換えた
  3. ユーザー定義関数で、作成した数式型文字列を数式として再評価した

となっています。

まとめ: FORMULATEXT関数とユーザー定義関数でExcel作業を効率化

ExcelのFORMULATEXT関数とユーザー定義関数を利用することで、数式の確認やテキスト形式の数式の評価が簡単に行えるようになります。これにより、Excel作業の効率が大幅にアップすることでしょう。

この記事がExcelの高度な使い方を学ぶ一助となれば幸いです。ぜひ日常のExcel作業に取り入れてみてください。

コメント