Excel関数のユーザー定義関数についてお探しですね。
Excelで見積書や集計表を作っていると、標準の関数だけでは数式がどうしても長くなってしまうことがあります。
IF関数を何重にも重ねたり、同じような計算式を何度もコピーしたりすると、後から見返したときに「これ、何の計算だっけ？」と分からなくなってしまいがちです。
そんなとき便利なのが、VBAで自分だけのオリジナル関数を作る「ユーザー定義関数」という機能です。
この記事では、複雑な計算をスッキリさせるための考え方から、実際の作り方、保存するときの注意点まで、分かりやすく説明していきます。
広告
ユーザー定義関数って何？なぜ計算がシンプルになるの?
ユーザー定義関数というのは、ExcelのSUM関数やIF関数みたいに、セルの中で使える自分だけの関数のことです。
VBA(Visual Basic for Applications)というExcelに最初から入っているプログラミング機能を使って作ります。
一度作ってしまえば、シート上で「=関数名(引数)」という形で呼び出せるので、長くてややこしい数式を短くまとめられるんです。
例えば、数量によって割引率が変わる計算とか、会社独自の評価ルール、住所や郵便番号を整える処理など、標準の関数だけだと読みづらくなりがちな作業をまとめるのにぴったりです。
ユーザー定義関数の一番のメリットは、計算のルールを一箇所にまとめられることです。
普通のExcel数式だと、同じような計算を何個ものセルにコピーして使うことが多いですよね。
そうすると、ルールが変わったときに全部のセルを修正しなくちゃいけなくて大変です。
でも、ユーザー定義関数なら、VBAのコードを一箇所直すだけで、その関数を使っている全部のセルに変更が反映されます。
作業時間が短くなるだけじゃなくて、参照ミスやカッコの閉じ忘れみたいなうっかりミスも減らせるんです。
特に仕事でExcelを使っていると、「計算そのもの」より「計算式のメンテナンス」が負担になることってありますよね。
例えば、見積金額の計算方法が部署ごとに違ったり、一定の数量を超えた分だけ割引率が変わったり、文字列を決まった形に整えたりする処理は、標準の関数でもできないことはありません。
でも、数式が長くなればなるほど、引き継ぎや確認が難しくなってしまいます。
ユーザー定義関数は、そういう複雑なルールに名前をつけて何度も使えるようにする仕組みだと思うと分かりやすいです。
VBAでオリジナル関数を作る基本的な手順
ユーザー定義関数を作るには、まずExcelでVBAエディターを開きます。
キーボードの「Alt」キーと「F11」キーを同時に押すと、VBE(Visual Basic Editor)という編集画面が立ち上がります。
画面の左側に表示されるプロジェクト一覧から、今使っているブックを選んで、「挿入」メニューから「標準モジュール」を追加します。
ユーザー定義関数は、この標準モジュールに書くのが基本です。
他の場所にもコードは書けるんですが、シート上で使う関数として作るなら標準モジュールにまとめておくほうが後で管理しやすくなります。
一番シンプルな例として、2つの数値の平均を計算する関数を作ってみましょう。
次のコードを標準モジュールに入力すると、Excelのセルで「=AverageTwoNumbers(10,20)」みたいに呼び出せるようになります。
Functionで始まって、End Functionで終わる部分が一つの関数です。
カッコの中にあるxとyは「引数」と呼ばれるもので、関数に渡す値を表しています。
最後に関数名と同じ名前に計算結果を代入することで、セルに表示される値が決まります。
“`vb
Function AverageTwoNumbers(x As Double, y As Double) As Double
AverageTwoNumbers = (x + y) / 2
End Function
“`
このコードを入力したら、VBAエディターを閉じてExcelのシートに戻ります。
好きなセルに「=AverageTwoNumbers(A1,B1)」と入力すれば、A1とB1の平均値が表示されます。
標準の関数と同じ感覚で使えるので、使う人はVBAの中身を気にする必要がありません。
関数名は自由に決められますが、スペースや一部の記号は使えないし、最初を数字にすることもできません。
後から見た人が意味を分かりやすいように、「CalcTaxIncludedPrice(税込価格を計算)」とか「FormatZipCode(郵便番号を整形)」みたいに、処理の内容が分かる名前にするのがおすすめです。
ちなみに、Excelのリボンに「開発」タブが表示されていない場合は、Excelのオプションから表示設定を有効にできます。
ただ、ユーザー定義関数を作るだけなら「Alt」+「F11」でVBEを開けるので、最初から開発タブを表示しておく必要は特にありません。
VBAに慣れていない場合は、最初に小さな関数を作って動かしてみることが大事です。
いきなり複雑な仕事のロジックを組み込もうとするより、足し算とか平均とか税込価格の計算とかから始めると、Functionの基本的な構造を無理なく理解できます。
実際の仕事で使いやすいユーザー定義関数の例
ユーザー定義関数が特に力を発揮するのは、条件分岐がたくさんある計算です。
例えば、数量によって割引率が変わる見積計算を考えてみましょう。
100個までは割引なし、101個から200個まではその超えた分を3%引き、201個以上はさらに超えた分を5%引きにする場合、標準の関数だけでも計算できますが、数式がすごく長くなりがちです。
VBAで関数にしてしまえば、シート上では「=CalculateDiscountedPrice(A2,B2)」みたいに単価と数量を指定するだけで済みます。
“`vb
Function CalculateDiscountedPrice(price As Double, quantity As Long) As Double
Dim totalAmount As Double
If quantity <= 100 Then
totalAmount = price * quantity
ElseIf quantity <= 200 Then
totalAmount = (price * 100) + (price * (quantity – 100) * 0.97)
Else
totalAmount = (price * 100) + (price * 100 * 0.97) + (price * (quantity – 200) * 0.95)
End If
CalculateDiscountedPrice = totalAmount
End Function
“`
この関数では、priceが単価、quantityが数量です。
If、ElseIf、Elseを使って数量ごとに計算方法を分けています。
Excelのセルでは、A2に単価、B2に数量を入力して、C2に「=CalculateDiscountedPrice(A2,B2)」と入力すれば結果が返ってきます。
計算式をセルに直接書く場合と比べて、関数名から何をしているのかが分かりやすいですし、後から割引率を変更する場合もVBAコードの該当部分を直すだけで済みます。
文字列の処理でもユーザー定義関数は便利です。
例えば、7桁の郵便番号を「123-4567」の形式に整える処理は、LEFT関数やRIGHT関数を組み合わせれば実現できます。
でも、何回も使うなら関数にしておいたほうが分かりやすくなります。
次の例では、入力された値が7文字なら3文字目の後ろにハイフンを入れて、それ以外ならそのまま返します。
想定外の値が入ったときに無理に加工しないようにしているので、データの確認もしやすくなります。
“`vb
Function FormatZipCode(zipcode As String) As String
If Len(zipcode) = 7 Then
FormatZipCode = Left(zipcode, 3) & “-” & Right(zipcode, 4)
Else
FormatZipCode = zipcode
End If
End Function
“`
さらに、消費税の計算みたいなよくある処理も関数にできます。
例えば、税率を引数として受け取る形にすれば、税率が変わったり軽減税率に対応したりするときも楽になります。
標準の数式で「=A1*1.1」と直接書く方法は簡単ですが、何のための1.1なのかが伝わりにくいことがあります。
「=AddTax(A1,0.1)」みたいに関数名で意味を示せば、表を共有された人も処理内容を理解しやすくなります。
ユーザー定義関数は単に数式を短くするだけじゃなくて、仕事のルールを読みやすい形で表現する手段でもあるんです。
“`vb
Function AddTax(price As Double, rate As Double) As Double
AddTax = price * (1 + rate)
End Function
“`
保存・共有・エラー対策で失敗しないための注意点
ユーザー定義関数を入れたExcelブックは、普通の「.xlsx」形式ではVBAのコードを保存できません。
作った関数を残しておきたい場合は、「Excelマクロ有効ブック(.xlsm)」として保存する必要があります。
保存形式を間違えると、せっかく入力したVBAコードが消えてしまう可能性があるので注意してください。
また、他のブックでも同じ関数を使いたい場合は、そのブックにも同じコードを追加するか、Excelアドイン(.xlam)として保存して読み込む方法があります。
社内で共通で使う関数なら、アドインにして管理したほうが配布や更新がしやすくなります。
セキュリティ面にも気をつける必要があります。
VBAは便利な反面、ファイルの操作やデータの変更もできてしまうので、信頼できないマクロ付きファイルを安易に有効化するのは避けたほうがいいです。
自分で作った関数や、社内で管理されているファイルなら問題になりにくいですが、メールやインターネットから手に入れた知らないファイルは慎重に扱いましょう。
ユーザー定義関数を配る場合は、関数の目的、使い方、引数の意味を簡単に説明したシートを付けておくと、使う人の間違いを防ぎやすくなります。
実際の仕事で使う関数では、エラー処理も大切です。
例えば、数量にマイナスの値が入った場合、空白のセルが渡された場合、数値じゃなくて文字列が入った場合など、想定外の入力があると結果がおかしくなることがあります。
必要に応じて、入力値をチェックしてエラーメッセージを返すとか、空白を返すような設計にしておくと安心です。
特に複数の人で使う関数では、作った人が想定していない使われ方をされることもあるので、正常な値だけじゃなくて異常な値を入れたテストもやっておきましょう。
あと、ユーザー定義関数には向き不向きがあります。
セルの値を書き換えるとか、シートを追加するとか、ファイルを保存するみたいな操作は、シート上で使う関数としては向いていません。
基本的には「受け取った値をもとに計算して、結果を返す」処理に使うのが安全です。
複雑な自動処理をやりたい場合は、ユーザー定義関数じゃなくて普通のVBAマクロとして作ったほうが適しています。
関数にすべき処理とマクロにすべき処理を分けて考えることで、Excelファイル全体が管理しやすくなります。
VBAでオリジナルのExcel関数を自分で作れるようになると、複雑な計算をシンプルに表現できて、作業の効率化とミスの削減につながります。
最初は難しく感じるかもしれませんが、Functionの基本的な形、引数、戻り値の仕組みを理解すれば、実際の仕事で使える関数は少しずつ作れるようになります。
まずは平均の計算とか税込価格、郵便番号の整形みたいな小さな処理から始めて、慣れてきたら割引計算とか会社独自のルールの関数化に挑戦してみるといいでしょう。
Excelを「与えられた関数を使う道具」から「自分の仕事に合わせて拡張できる道具」に変えられることが、ユーザー定義関数の最大の魅力です。
広告
