Excel関数で限界を感じたら？ 生成AIを使って作る「ユーザー定義関数」で複雑な計算を一発解決：Tech TIPS
「Microsoft Excel（エクセル）」には、「SUM」や「XLOOKUP」など、便利な関数が最初から数多く用意されている。しかし、実際の業務では「自社独自の複雑な見積もりルール」や「特定の条件分岐を繰り返す計算」など、これら標準関数だけでは対応が難しいことも多い。こうした問題を解決する強力な機能が「ユーザー定義関数」である。そこで、ユーザー定義関数を作成する方法を解説していく。
対象：Excel 2021／2024／365
生成AIを使って作るExcelの「ユーザー定義関数」で複雑な計算を一発解決Excelには、便利な関数が最初から数多く用意されている。しかし、実際の業務では「自社独自の複雑な見積もりルール」や「特定の条件分岐を繰り返す計算」など、これら標準関数だけでは対応が難しいことも多い。こうした問題を解決する強力な機能が「ユーザー定義関数」である。そこで、ユーザー定義関数を作成する方法を解説していく。
「Microsoft Excel（エクセル）」には、「SUM」や「XLOOKUP」など、便利な関数が最初から数多く用意されている。しかし、実際の業務では「自社独自の複雑な見積もりルール」や「特定の条件分岐を繰り返す計算」など、これら標準関数だけでは対応が難しいことも多い。
IF関数を何重にも組み合わせた結果、数式バーが呪文のようになってしまい、後で自分でも解読できなくなった経験はないだろうか。また、かっこの対応が合わず、関数がエラーになってイライラした経験は、誰にでもあるだろう。
こうした問題を解決する強力な機能が「ユーザー定義関数」である。ユーザー定義関数は、Excelに内蔵されている「VBA (Visual Basic for Applications)」を使うため、少しハードルが高く思えるが、コツが分かってしまえばそれほど難しいものではない。また、CopilotなどのAI（人工知能）機能を活用すれば、簡単にコードの作成が可能だ。
そこで本Tech TIPSでは、ユーザー定義関数を作成する方法をステップ・バイ・ステップで解説していく。
ユーザー定義関数とは？
ユーザー定義関数とは、Excelに内蔵されている「VBA」というプログラミング言語を使って、オリジナル関数を作成する機能のことだ。一度作ってしまえば、「=SUM(A1:A10)」と同じように、「=userfunc(A1, B1)」といった形で、ワークシート上のセルに直接入力して使えるようになる。
ユーザー定義関数のメリット
ユーザー定義関数を作成するメリットは、「数式が劇的にスッキリする」ことだ。複雑なIF文のネストや、複数の関数を組み合わせた長い数式が、「=userfunc(A1, B1)」のようなシンプルな形に置き換えられる。
同じ長い数式を何度も入力しなくて済み計算ミスや数式の入力ミスを大幅に減らすことができる。複雑な計算ルールをユーザー定義関数の中に一度だけ正しく記述すれば、あとはその関数を呼び出すだけだ。手作業による数式の入力ミスや参照ミスを根本から防ぐことができる。
また、数式のロジックなどが変更になった場合でも、ユーザー定義関数ならば1カ所の変更で済む。
さらに再利用が簡単で、そのExcelブック内であれば何度でも簡単に呼び出して使用できる。「Excelアドイン」として保存すれば、どのExcelファイルを開いているときでも利用できるようになるメリットもある。
作ってみよう！ 数量に応じた割引計算
では、実際にユーザー定義関数を作成していこう。ここでは例として、卸売業などで使われる「数量に応じた割引」を計算する関数を作成してみる。他にも「送料計算」「営業成績の評価」などにも、同様の手順でユーザー定義関数が作成できるはずだ。
関数の条件は以下の通りだ。個数に応じてディスカウント率を変更するというものである。
- ルール1：個数が0個から100個までは割引なし（単価×個数）
- ルール2：個数が101個から200個までの場合、100個までは割引なし。101個目から200個目までの分について3％引き
- ルール3：個数が201個以上の場合、100個までは割引なし。101個目から200個目までの分は3％引き。201個目以上の分について5％引き
なお、作成するユーザー関数名は「CalculateDiscountedPrice」とする。
ちなみにこのルールをExcelの関数を使って数式にすると、以下のようになる（「A2」セルに単価、「B2」セルに個数が入力されているとする）。かなり面倒な数式になることが分かるだろう。
=A2*MIN(B2,100) + A2*MIN(MAX(B2-100,0),100)*0.97 + A2*MAX(B2-200,0)*0.95
ステップ1：［開発］タブを準備する
VBAを使うには、Excelに［開発］タブを表示させる必要がある。
表示されていない場合は、以下の手順で［開発］タブを追加する。
■操作手順
- ［ファイル］メニューをクリックする
- 左メニューで［オプション］を選択する
- ［Excelのオプション］ダイアログが開くので、左メニューの［リボンのユーザー設定］を選択する
- 右ペインの「リボンのユーザー設定」欄にある「開発」にチェックを入れて［OK］ボタンをクリックする
これでExcelに［開発］タブが追加されるはずだ。
［開発］タブを準備する（2）［Excelのオプション］ダイアログが開くので、左メニューの［リボンのユーザー設定］を選択、右ペインの「リボンのユーザー設定」欄で［メインタブ］を選択し、下側のリストにある「開発」にチェックを入れて、［OK］ボタンをクリックする。
ステップ2：VBE（Visual Basic Editor）を開く
追加した［開発］タブを開き、一番左にある［Visual Basic］アイコンをクリックする（［Alt］＋［F11］キーを押してもよい）。
一般に「VBE（Visual Basic Editor）」と呼ばれる［Microsoft Visual Basic for Applications］ウィンドウが開くはずだ。
ステップ3：標準モジュールを挿入する
VBEが開いたら、［挿入］メニューをクリックし、［標準モジュール］を選択する。画面右側に、コードを入力するための真っ白なウィンドウが表示される。
ステップ4：VBAコードを入力する
白いウィンドウが表示されたら以下のコードを入力する。
VBAのコードを作成するハードルが少し高いが、そこはWindows 11で標準機能となった「Copilot」などの生成AIをうまく活用するとよい。Copilotなどの画面を開き、プロンプトとして「以下のルールでExcelのユーザー定義関数を作成するためのVBAのコードを出力して」と入力後、［Shift］＋［Enter］キーで改行して、作成したいユーザー定義関数のルール（今回の場合は、上述のルール1からルール3まで）を書いていけば、ほぼ正確なVBAコードを出力してくれるはずだ。
Function CalculateDiscountedPrice(price As Double, quantity As Long) As Double
Dim totalAmount As Double
If quantity <= 100 Then
'ルール1：100個までは割引なし
totalAmount = price * quantity
ElseIf quantity <= 200 Then
'ルール2：101〜200個
'(100個までの金額) + (101個目からの個数 * 単価 * 0.97)
totalAmount = (price * 100) + (price * (quantity - 100) * 0.97)
Else
'ルール3：201個以上
'(100個までの金額) + (次の100個分の3%割引金額) + (201個目からの5%割引金額)
totalAmount = (price * 100) + (price * 100 * 0.97) + (price * (quantity - 200) * 0.95)
End If
'計算結果を関数の戻り値として設定
CalculateDiscountedPrice = totalAmount
End Function
簡単にこのコードの説明をしておこう。
「Function ... End Function」は、ユーザー定義関数を定義する際の決まり文句だ。「Function」で始めて、「End Function」で終わるようにする。
「Function」に続く、「CalculateDiscountedPrice」が関数の名前になる。
「(price As Double, quantity As Long)」のかっこの中は「引数」で、関数が計算に使うために受け取る値だ。今回は「price（単価）」と「quantity（個数）」の2つを受け取るように定義している。
「If ... ElseIf ... Else ... End If」は、条件分岐（もし〜なら）だ。quantity（個数）に応じて、ルール通りに計算方法を分けている。割引率が変更になった場合など、この部分を修正すればよい。
ユーザー定義関数では、複雑な計算でもこのようにルールに分けて記述できるので、Excel標準関数では表現が難しい複雑な条件分岐や計算処理でも比較的簡単に作成可能だ。
ステップ5：Excelシートで作成したユーザー定義関数を使ってみる
VBEを閉じてワークシートに戻り、作った関数を試してみよう。
「A2」セルに「単価」（例：1000）、「B2」セルに「個数」（例：150）をそれぞれ入力しておく。「C2」セルに、作成したユーザー定義関数を使う数式として「=CalculateDiscountedPrice(A2, B2)」と入力する。Enterキーを押すと、セルに計算結果が表示されるはずだ。
「B2」セルの個数をルール1に該当する「90」、ルール2に該当する「150」、ルール3に該当する「220」などと変えて、結果が正しく変わることを確認してみよう。また、上述のExcelの標準関数で作成した数式でも結果が同じになるか試せば、ユーザー定義関数が正しく動作しているかどうかが確認できるだろう。
Excelシートで作成したユーザー定義関数を使ってみる（1）単価と個数を入力した表を作成、作成したユーザー定義関数を入力してみよう。なお、ユーザー定義関数を作成すると、自動保存がオフになり、画面のように警告が表示される。保存する場合は、［名前を付けて保存］ボタンをクリックし、「.xlsm」ファイルとして保存する。
関数を保存していつでも使えるようにする
作成したユーザー定義関数は、通常そのブックでしか使えない（自動保存も停止する）。しかし、「Excelアドイン」として保存することで、どのExcelファイルを開いているときでも利用できるようになる。
関数を記述したブックを保存する際に、ファイルの種類で「Excelアドイン（*.xlam）」を選んで保存する。［%APPDATA%\Microsoft\AddIns］フォルダに保存すると、アドインの管理が楽になる。
保存したアドインファイルを、Excelファイルの［開発］タブにある［Excelアドイン］で有効にすれば、作成したユーザー定義関数を新たに開いたExcelファイルでも利用できるようになる。
関数を保存していつでも使えるようにする（3）［アドイン］ダイアログが開くので、［参照］ボタンをクリックし、保存した「.xlam」ファイルを指定する。アドインを［%APPDATA%\Microsoft\AddIns］フォルダに保存した場合は、自動的に「有効なアドイン」に読み込まれるので、ここで［参照］ボタンをクリックする必要はない。
ユーザー定義関数を利用する際の注意
ユーザー定義関数の利用には、セキュリティ面への配慮が必要となる。特に、マクロ（VBA）を含むを含むExcelファイルを開くときには、意図しないVBAコードの実行を防ぐための設定が重要である。
作成したユーザー定義関数を社内で広く配布して使うような場合は、作成したVBAコードの安全性にも注意が必要だ。例えば、データを書き換えるようなユーザー定義関数では、引数によってはデータを壊してしまう可能性もある。汎用的に利用するためには、引数のチェックやエラー処理などに配慮したVBAコードの記述が必要になる点に注意してほしい。
また、Excelではマクロが含まれるファイルを開くと「マクロを有効にするかどうか」の警告が表示される。信頼できるソースから入手したファイルであれば有効化して問題ないが、不明な送信元やインターネット経由で取得したファイルの場合は、安易に有効化すべきではない。メールなどでユーザー定義関数を含むExcelファイルを送付するのは避けた方がよいだろう。
さらにユーザー定義関数を含むVBAコードは、意図せず他のブックに影響を与える可能性がある。例えば、同じ関数名が別のブックに存在する場合、予期せぬ挙動を引き起こすことがある。こうしたリスクを避けるためには、関数名を一意に設計する、信頼できる場所に保存するなどの対策をした方がよい。
ユーザー定義関数を利用する際は、これらの注意に配慮してほしい。
ユーザー定義関数は、VBAの知識が少し必要だが、一度覚えてしまえば非常に強力な機能となる。いつもの複雑な計算や、面倒な定型作業を関数化することで、Excel業務のミスを減らし、劇的に効率化することが可能だ。まずは簡単な計算から関数化に挑戦し、Excelを使いやすくカスタマイズするとよい。
Copyright© Digital Advantage Corp. All Rights Reserved.
アイティメディアからのお知らせ
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。