bizvba_160

ユーザー定義関数を作る(損益分岐点売上高、目標達成売上高)

Excelには、関数が存在するが、「ユーザー定義関数」として自分で関数を作ることができる
今回は、損益分岐点売上高と、目標達成売上高 を求めるユーザー定義関数を作ってみる。

ユーザー定義関数の作成には、VBAが必要ですが、とても簡単です。
VBE というプログラミング用のエディターを起動(Excel で Alt + F11)して、標準モジュールに次のコードを入力する。(以下をコピペしても大丈夫)


Function 損益分岐点売上高(ByVal 固定費 As Long, ByVal 限界利益率 As Double)

    損益分岐点売上高 = Int(固定費 / 限界利益率)
    
End Function


Function 目標達成売上高(ByVal 目標利益 As Long, ByVal 固定費 As Long, ByVal 限界利益率 As Double)
    
    目標達成売上高 = Int((固定費 + 目標利益) / 限界利益率)
    
End Function

 ↓ こんな感じです。

vbe


あとは、Excelに戻って、入力したいセルを選択してから、数式バーの 「fx」マークを押して「関数の挿入」ダイアログボックスを表示して、関数の分類を「ユーザー定義関数」にする。(下図)

dialog1

すると、関数名に、損益分岐点売上高と、目標達成売上高が表示されます。

まずは、損益分岐点売上高を選択してみます。
「関数の引数」ダイアログボックスが表示されるので、固定費に「13,600」を、限界利益率に「0.12」と入力してみます。結果は、113,333となります。(下図)

dialog2

セルには、「=損益分岐点売上高(13600,0.12)」と入力されます。
ダイアログボックスを使わずに、直接セルに入力しても大丈夫です。



次は、目標達成売上高を選択した場合です。
「関数の引数」ダイアログボックスが表示されるので、目標利益に「10,000」、固定費に「13,600」を、限界利益率に「0.18」と入力してみます。結果は、131,111となります。(下図)

dialog3

セルには、「=目標達成売上高(10000,13600,0.18)」と入力されます。
ダイアログボックスを使わずに、直接セルに入力しても大丈夫です。


ユーザー定義関数を作ることで簡単に計算できるようになり、また、セルに入力する算式も短くなるメリットがあります。VBAで、その他いろいろな関数を作ってみてください。