ユーザ定義関数（Ｍｙ関数）の作成
初級演習問題集
Excel には、Sum,Average,Max などワークシート上で利用できるの関数が 330 種類ほど準備さ
れているという。しかし、たとえば、エンゲル係数や
BMI を求める関数、偏差値や優良可不可を
判定する関数などは
Excel には存在しない。もちろん、既存のワークシート関数を組み合わせれ
ば、目的とするところは達成できるが、日常、頻繁に利用するのであれば、それは煩わしいこと
である。
このような場合、ユーザ自身が自分の目的にあったユーザ独自の関数（My 関数）を自作すれ
ば良いのである。このために用意された機能が
Excel では「ユーザ定義関数」と呼ばれるもので
あり、ユーザが
VBA によりプログラミングすれば、ユーザ独自の関数として利用できる関数を作
成できるのである。
以下に、自分独自のワークシート関数を作成できることを体験する。
§０ My 関数の基本と作成手順
ここでは、
「長方形の縦と横の長さを入力すると、その面積を出力するユーザ定義関数を作成す
る」という例題を通して、ユーザ定義関数の基本と、その作成手順について学ぶことにする。
《手順》
（１）Visual BASIC Editor を起動する
Alt＋F11 で起動するのが簡単。
または、メニューバー→ツール→マクロ→Visual BASIC Editor→…………→
（２）マクロプログラムを収めるための「標準モジュール」を挿入する
メニューバー→挿入→標準モジュール→…………→
下図のようなコーディングウィンドウが開くこれから作成しようとするMy 関数（ユーザ定義関数）の実体は、後ですぐ分かるように、ＶＢＡで書かれ たマクロプログラムであり、しかも、Function プロシージャとして書き込まれたプログラムのことである。 ユーザが作成したこのFunction プロシージャをたくさん収納しておくための入れ物が「標準モジュール」と 呼ばれるものである。 次の（３）でキーインする
（３）プログラミング（コーディング）
標準モジュールを挿入すると、上図のようにプログラムを書くためのコードウィンドウが開
くので、そこに、下記のような手順でプログラムを書き込んでいく（すなわち、コーディング
していく）。
①Function 長方形面積(縦の長さ As Single, 横の長さ As Single) As Double とキーイン
コードウィンドウを見ると、Function プロシージャの最後尾を示す「End Function」が自動的に入力さ れていることが分かる。 ここで、「長方形面積」はFunciton プロシージャの名前（関数名）であり、その名前はユーザが勝手に決 めてよい。もちろん、半角のローマ字でも良い。 次の「縦の長さ」「横の長さ」も同様である。なお、これらは、このFunciton プロシージャのパラメータ 変数（引数）と呼ばれるものである。 また、「As Single」は、これらの変数の「データの型」として、ここでは単精度浮動小数点型として定義 している。さらにAs Double は
「長方形面積」は倍精度浮動小数点型として定義している。②Function∼End Function の行間にプログラムを書いていく
本問では、つぎのようなプログラムを１行キーインする。長方形面積=縦の長さ*横の長さ
これで、マクロプログラム作成は終了した。すなわち、ユーザ定義関数（My 関数）が完
成できたことになる。
以上をまとめると
Function プロシージャの書式は次のとおり。
Ｆｕｎｃｔｉｏｎ 関数名（引数1, 引数 1, 引数 1,…………） ←←←ここでデータの型も宣言する ……… ……… ←←←←←ここに目的の処理を行わせるためのプログラムを書く ……… 関数名 ＝ 結果として返す値 ←←←最後に必ず同じ関数名で、この変数に出力したい値を代入する Ｅｎｄ Ｆｕｎｃｔｉｏｎ（４）Excel に戻って、自作したばかりの My 関数を試用してみる
使い方は、通常のワークシート関数の場合とほぼ同様である。
ただ、答えを出力したいセルを先ずアクティブにしたのち、Σボタンの右横にある▽をクリ
ックして、その他の機能(F)を選択するところまでは同じであるが、次の関数の分類を選択すると
ころでユーザ定義を指定する点が異なるのみである。さらに、関数名(N)として、ここでは、「長
方形面積」を選択クリックし
OK ボタンを押すと次の画面が表示される
ここで、長方形の縦横の長さの数値をそれぞれ直接キーインしても良いし、あるいは、そのデ
ータが収められたセルを指定しても良い。これで長方形の面積が、アクティブセルに出力される
はずである。
§１ My 関数の作成方法演習
《問１》半径の値がワークシートのセルに入力されているとする。この値
を半径とする円の面積を求める
My 関数(ユーザ関数)を作成せよ。
半径 円の面積10 314.1592 Function en_menseki(半径 As Single) As Double
en_menseki = 3.141592 * 半径 ^ 2 End Function
《問２》ワークシート上に、台形の上辺、下辺、
高さの値が入力されているとする。これらの値
を持つ台形の面積を求めるユーザ関数を作成せよ
上辺 下辺 高さ 台形の面積 10 18 6 84Function daikei_menseki(上辺 As Single, 下辺 As Single, 高さ As Single) As Double daikei_menseki = (上辺 + 下辺) * 高さ / 2 End Function
《問３》優良可不可を決定するユーザ関数を作成せよ。
なお、得点が負または
100 以上の場合は、Msgbox 関数で「0~100 までの値を入力して下さい」
と注意を促したうえ「判定不能」と表示せよ。
得点 成績判定（優良可不可の判定） 40 不可 50 不可 60 可 70 良 80 優 90 優 100 優 110 判定不能 -50 判定不能 65 可Function yuryoka_hantei(tokuten As Single) As String Dim hantei As String
Select Case tokuten Case Is < 0, Is > 100 MsgBox ("得点は０∼100 までの値を入力してください") hantei = "判定不能" Case Is >= 80 hantei = "優" Case Is >= 70 hantei = "良" Case Is >= 60 hantei = "可" Case Else hantei = "不可" End Select yuryoka_hantei = hantei End Function
《問４》BMI の値を算定し、その値をもとに体型判定（肥りすぎ、痩せすぎ、標準体型の判定）
をおこなえるようにせよ。
ヒント：ＢＭＩ＝体重(kg)／身長(m)
２ＢＭＩの数値が
18.5 未満の場合は痩せ気味、18.5∼25 未満が標準体型、25∼30 未
満が肥満、30 以上が高度肥満とされている。
体重（ｋｇ） 身長（ｍ） 体型判定 55 1.81 痩せ型 60 1.81 痩せ型 65 1.81 標準体型 70 1.81 標準体型 75 1.81 標準体型 80 1.81 標準体型 85 1.81 肥満体型 90 1.81 肥満体型 95 1.81 肥満体型 100 1.81 重度肥満 105 1.81 重度肥満
Function bmi_taikei_hantei(taiju As Single, sincho As Single) As String '体重 taiju の単位は kg、身長 sincho の単位はｍとする
Dim bmi As Single Dim hantei As String bmi = taiju / sincho ^ 2 If bmi < 18.5 Then hantei = "痩せ型" ElseIf bmi < 25 Then hantei = "標準体型" ElseIf bmi < 30 Then hantei = "肥満体型" Else hantei = "重度肥満" End If bmi_taikei_hantei = hantei End Function
《問５》２次方程式ａｘ
2＋ｂｘ＋ｃ=0 の解ｘ
１、ｘ
２を求めるユーザ関数を作成せよ。その際、
実根、虚根、重根であることも明らかにせよ。
a b c ２次方程式の解 1 -8 15 実根....ｘ１＝5....ｘ２＝3 1 -6 9 重根....ｘ１＝3....ｘ２＝3 1 2 17 虚根....ｘ１＝-1+i4....ｘ２＝-1-i4Function nijihouteisiki_kai(a As Single, b As Single, c As Single) As String Dim d As Single
Dim ans0, ans1, ans2 As String d = b ^ 2 - 4 * a * c '判別式 Select Case d Case Is > 0 ans0 = "実根" ans1 = (-b + Sqr(d)) / (2 * a) ans2 = (-b - Sqr(d)) / (2 * a) Case Is = 0 ans0 = "重根" ans1 = (-b + Sqr(d)) / (2 * a) ans2 = ans1 Case Else ans0 = "虚根"
ans1 = -b / (2 * a) & "+i" & Sqr(-d) / (2 * a) ans2 = -b / (2 * a) & "-i" & Sqr(-d) / (2 * a) End Select
nijihouteisiki_kai = ans0 & "...Ｘ１＝" & ans1 & "....Ｘ２＝" & ans2 End Function
《問６》円の値段は時々刻々と変化している。
「ドル」から「円」を換算する関数を求めよ。ただ
し、つぎに示す２種類の異なる使い方の出来る
My 関数を作成せよ。すなわち、次の異なる操作
手順でもこの一つの関数だけで「ドルの値段」を円に換算できるものとする。
1) この関数を利用する途中で「１＄が何円であるかが入力されているセル」を指定して求める。
2) セル指定するのではなく、１＄が何円であるかを直接キーイン入力して求める。
ヒント：ドルの値段のセルは、Ｆ４キーで絶対番地指定すること
ドル 円 本日のドル値段 5 500 １＄＝ 100 円 10 1000 15 1500 20 2000Function dollar_en_kanzan(dollar As Long, doru_no_nedan As Integer) As Long dollar_en_kanzan = dollar * doru_no_nedan
End Function
Function dollar_en_kanzan2(dollar As Long, doru_no_nedan As Integer) As Long dollar_en_kanzan = dollar * doru_no_nedan
End Function
§２ 出力を選択できる My 関数
《問１》
BMI を算定するユーザ関数を作成せよ。ただし、その関数は、オプション（真偽）が「真」
の場合はＢＭＩ値、
「偽」の場合は体型判定（肥りすぎ、痩せすぎ、標準の判定）のいずれかを選
択して出力できるようにせよ。
体重（ｋｇ） 身長（ｍ） 体型判定(真） 体型判定（偽） 55 1.81 16.78826 痩せ型 60 1.81 18.31446 痩せ型 65 1.81 19.84067 標準体型 70 1.81 21.36687 標準体型 75 1.81 22.89308 標準体型 80 1.81 24.41928 標準体型 85 1.81 25.94549 肥満体型 90 1.81 27.47169 肥満体型 95 1.81 28.9979 肥満体型 100 1.81 30.5241 重度肥満 105 1.81 32.0503 重度肥満 Function bmi_taikei_hantei_bmi _(taiju As Single, sincho As Single, BMI 値又は体型判定 As Boolean) As Variant '体重 taiju の単位は kg、身長 sincho の単位はｍとする
Dim bmi As Single Dim hantei As String
bmi = taiju / sincho ^ 2
If BMI 値又は体型判定 = True Then hantei = bmi
Else
If bmi < 18.5 Then hantei = "痩せ型" ElseIf bmi < 25 Then hantei = "標準体型" ElseIf bmi < 30 Then hantei = "肥満体型" Else
hantei = "重度肥満" End If End If bmi_taikei_hantei_bmi = hantei End Function
《問２》ワークシート上のセルに半径の値が入力されている。この半径を持つ円の面積、円周長、
球の体積を求めるユーザ関数を作成せよ。なおオプションによって出力を選択できるようにせよ。
半径 計算選択(123) 円面積（１） 球体積（２） 円周長（３） 10 1 314.1592 10 2 4188.7893 10 3 62.83184Function en_menseki_taiseki_enshucho(r As Single, sentaku As Integer) As Double Dim dumy As Double
'sentaku = InputBox("1:面積 2:体積 3:円周長をキーインせよ", "出力選択", 1) 'この方法による選択ではうまくいかないことを確認せよ。
Select Case sentaku Case 1 dumy = 3.141592 * r ^ 2 Case 2 dumy = (4 / 3) * 3.141592 * r ^ 3 Case Else dumy = 2 * 3.141592 * r End Select en_menseki_taiseki_enshucho = dumy End Function
§３ 「セル範囲の指定」と「ワークシート関数の利用」
《問》ワークシート上のあるセル範囲に数値が入力されているとする。
1) そのセル範囲を指定して、その範囲内に存在するデータ個数を求めよ。
2) そのセル範囲を指定して、その合計値を求めよ。
2) そのセル範囲を指定して、その平均値を求めよ。
4) そのセル範囲を指定して、その範囲内にある３の倍数値のみの合計を求めよ。
ヒント：
0) セル範囲は、回答例にあるように「範囲 as Range」として定義するとよい。ここで「範囲」
は「hanni」などローマ字でもよいが、このMy関数をダイアログウィンドウ関数ｆ
ｘで対話
しながら利用するユーザのことを考えると前者の方が適切であろう。
1) オブジェクト.count を利用する
2)3) VBA 上でワークシート関数（例えば Sum、Average 関数）を利用する場合は、回答例に
あるように、WoorksheetFunction.sum(範囲)、WoorksheetFunction.Average(範囲)のよう
に使うと良い。
4) ここでは、３の倍数かどうかを判定するには、mod 関数を利用して、その値がゼロになれば
３の倍数と判断する。３の倍数の合計を求めるには回答例にあるように、シート関数の
Sumif
関数を利用する。その書式は次のとおりである。
Sumif（「条件」で検索する範囲， 条件， 合計したい範囲）
このため、
MOD 関数で計算した結果の表を作成し、これを「条件を検索する範囲」とする。
Function data_kosu(範囲 As Range) As Integer 1 6 11 16 21 26 2 7 12 17 22 27 データ個数 30 3 8 13 18 23 28 合計 465 4 9 14 19 24 29 平均値 15.5 5 10 15 20 25 30 165 165 1 0 2 1 0 2 ３の倍数合計 165 2 1 0 2 1 0 0 2 1 0 2 1 1 0 2 1 0 2 2 1 0 2 1 0 ①シート関数Sumif を利用 ②sum 関数を利用 ③My 関数を利用 合計したい範囲 条件検索範囲 =mod（セル、3） data_kosu = 範囲.Count End Function
Function data_goukei(範囲 As Range) As Long data_goukei = WorksheetFunction.Sum(範囲)
'data_goukei = 範囲.Sum 'count はＯＫだが sum の場合ダメ End Function
Function data_heikin(範囲 As Range) As Single data_heikin = WorksheetFunction.Average(範囲) End Function
Function data_sumif(条件検索範囲 As Range, 条件 As String, 合計したい範囲 As Range) As Single data_sumif = WorksheetFunction.SumIf(条件検索範囲, 条件, 合計したい範囲) End Function
§４ 今まで学習したことの成果をチェックする問題
《問》偏差値を求めるユーザ関数を作成せよ。別法としてMyユーザ関数を使わずにワークシート
関数のみで求めて検算してみよ。
ヒント
1：偏差値＝10×(得点−平均値)／標準偏差＋５０
ヒント
2：ワークシート関数（Average,StDevP）関数を利用すると良い。
ヒント
3：
ワークシート関数のみ=10*(C77-AVERAGE($D$77:$D$86))/STDEVP($D$77:$D$86)+50 a=60,d=10 整数化 値化（得点） 偏差値 偏差値２ 56.99768 56 56 46.768902 46.8 47.22317 47 47 39.312531 39.3 62.44257 62 62 51.739819 51.7 72.76474 72 72 60.024677 60.0 71.9835 71 71 59.196194 59.2 77.33133 77 77 64.167107 64.2 38.16412 38 38 31.856157 31.9 57.65819 57 57 47.597389 47.6 70.95023 70 70 58.367706 58.4 49.13299 49 49 40.969501 41.0Function hensati(範囲 As Range, 得点 As Single) As Single Dim heikin As Single
Dim sigma As Single
heikin = WorksheetFunction.Average(範囲) sigma = WorksheetFunction.StDevP(範囲)
hensati = 10 * (得点 - heikin) / sigma + 50 End Function
《問》エンゲル係数を求めるユーザ関数を作成せよ。
ヒント：食費のみの合計は
Sumif 関数を利用すると良い。
ヒント：エンゲル係数＝飲食費÷家計総支出（％）
http://www.gohan.ne.jp/okome-data/05/511.html 所得の上昇につれて家計費中にしめる食料費の割合が低下する傾向がある。この統計的法則を「エ ンゲルの法則」という。一方、収入がある額以下になると、固定的な支出に圧迫されて食料費が削ら れるために、収入が低いにもかかわらずエンゲル係数は小さくなるという「エンゲル法則の逆転現象」 も生じる。近年、我が国では、１人あたりの国民所得の伸びを反映して、それに伴ってエンゲル係数 は低下しており、2001 年度我国は 23.2%だったといわれている。 項目 分類 価格 パン 食費 200 食費合計 2350 牛乳 食費 150 総支出費 9750 町内会費 雑費 500 エンゲル係数１ 24.1% ＰＴＡ費 教育費 400 エンゲル係数２ 24.1% セーター 衣料費 5000 米 食費 2000 教科書 教育費 1500Function engeru_keisu(条件範囲 As Range, 条件 As Variant, 合計したい範囲 As Range) As Single engeru_keisu = WorksheetFunction.SumIf(条件範囲, 条件, 合計したい範囲) / _ WorksheetFunction.Sum(合計したい範囲) End Function
