第５回　関数の作成と利用

概要

　「関数」の考え方と記述の仕方を学びます。最初に関数の考え方を、具体例を通して学習します。ここでは関数の一般的意味をふまえた上で、Excelのワークシート上の作業で用いられる「ワークシート関数」の成り立ちを理解します。つぎに、VBAマクロにおける関数の作成の仕方を学びます。ここではまず、ワークシート関数を自分で作成する方法を学習します。さらにマクロのソースコードの記述において、自作したワークシート関数をサブルーチンとして呼び出して利用する方法を学習します。

セクション

1. 関数とは何か
 2. 関数の作成
 3. 補足・グラフの表示

1. 関数とは何か

　ここでは「関数」の基本的な考え方を学習します。

1.1 関数の考え方

　関数は、入力される値に対して何らかの計算を行い結果を出力するブラックボックスとして捉えることができます。

　ブラックボックスの内部には、入力された値を利用して計算を行う計算式が入っています。出力される値は、入力される値に応じて変化します。たとえば入力される値をx、計算式をx+1、出力される値をyとします。このとき、xの値が１であればyの値は2、xの値が2であればyの値は3になります（この入力値xと出力値yの関係はy=x+1と表すことができます。これは数学で「一次関数」と呼ばれます。）

　関数とはこのように、二つの変数x、yの間に何らかの対応関係があって、xの値が定まるとそれに対応してyの値が従属的に定まる時の対応関係をさします（また、xに対して従属的に変化するyのことを「xに対する関数y」と呼ぶこともあります）。

1.2 Excelにおける関数

　Excelにおいて関数といえば、すぐに思い出すのは「ワークシート関数」です。すべてのワークシート関数は、上述の一次関数と同様に、入力される値に応じて何らかの値を出力します。たとえばSUM関数は、セル範囲を「入力」すると、その範囲の中にある値の合計値を「出力」します。平均値を算出するAVERAGE関数や標準偏差を算出するSTDEV関数も同様の構造を持っています。

=SUM(A1:A5)		→この式が入力されたセルにA1からA5の合計値を出力する
=AVERAGE(A1:A5)	→この式が入力されたセルにA1からA5の平均値を出力する
=STDEV(A1:A5)	→この式が入力されたセルにA1からA5の標準偏差を出力する

　このとき関数に与える入力のことを「引数（ひきすう）」、関数が出力する値のことを「返値（かえりち）」と呼びます。たとえばSUM関数は、セル範囲を引数にとり、セル範囲内の値の合計値を返値として返す関数です。この引数、返値という呼び方は、Excelのワークシート関数に限らず、プログラミング用語として一般的に用いられます。

2. 関数の作成

　ここではVBAで関数を作成する方法を学習します。

2.1 ワークシート関数の作成

　ワークシート関数は、VBAによってプログラムを書くことで、自分でも作成することができます。ここでは身長を与えるとBMI値に基づく「標準体重」（第２回、3.3を参照）を算出するワークシート関数の作成を例に、ワークシート関数作成の手順を学習します(practice05.xls, Sheet1)。

2.1.1 標準体重を算出するワークシート関数の作成

　「標準体重算出」関数の仕様を確定しましょう。

　まずは外見（動作の目に見える部分）から決めていくことにします。この関数は特定のセルに関数名「標準体重算出」を入力して利用します。その際に、引数として身長をcm単位で与えることにします。

　リターンを押して関数を実行すると、以下のように、身長に応じた標準体重を返値として出力することにします。

　ここまでに決めたことを箇条書きにまとめると以下のようになります。

関数の名称は「標準体重算出」
引数は身長（cm単位、小数値も可）
返値は標準体重（kg単位、小数値も可）

　仕様のアウトラインが決まったら、処理手順を構想します。まだ関数の作成の仕方を学んではいませんが、細かいことは気にせずに、現時点で考えられる最もおおまかな処理の手順をまとめることにします。すると、おそらくは以下のようになるでしょう。

引数として入力された身長を取得する
身長を手がかりに標準体重を算出する
標準体重を返値としてセルに出力する

　ここで標準体重の算出の仕方を確認します。標準体重は次の式で求められます。

標準体重　＝　身長×身長×２２

　ただし上記の式の場合、身長はm単位でなければなりません。これをふまえて上記の処理手順の構想を若干修正して以下のようにします。

引数として入力された身長を取得する
身長をメートル単位に換算する
身長を手がかりに標準体重を算出する
標準体重を返値としてセルに出力する

　上記の処理手順の構想を手がかりに、実際にVBAで関数を記述すると以下のようになります。ソースコードを見ながら関数の記述の仕方を解説します。

Function 標準体重算出(身長 As Double)
' 身長(cm)から標準体重(kg)を算出する
' 引数:身長(Double型)
' 返値:標準体重(Double型)
	Dim 標準体重 As Double

	'　身長をメートル単位に換算する
	身長 = 身長 * 0.01

	'　標準体重を算出する
	標準体重 =  身長 * 身長 * 22

	'　標準体重を出力する
	標準体重算出 = 標準体重
End Function

A. 関数名の宣言

　VBAでワークシート関数を記述する際には、「Sub～End Sub」ではなく「Function～End Function」で囲みます（ちなみに関数はfunctionの和訳です）。プロシージャ名と同様の位置に関数名を宣言します。

B. 引数の宣言

　今までプロシージャ名の直後の（）は空欄でした。この（）の内部に引数（を受け取るための変数）を宣言します。標準体重算出関数の引数は身長でした。通常の変数宣言と同様の書式で変数名とデータ型を記述します。今回の場合、身長は小数値も受け取ることができるようにDouble型で宣言しておきます。

　このように引数を宣言することで、ユーザーが入力した値を自動的に取得することができます。したがって、処理手順の1はこれで完了したことになります。

C. 引数を利用した処理の記述

　引数の値を処理に利用したい場合、引数として宣言した変数名をそのまま記述します。処理の１行目「身長をメートル単位に換算する」および２行目「標準体重を算出する」に記述された変数「身長」は、引数の宣言のところで宣言済みの変数です。

D. 返値の出力

　返値をセルに対して出力するためには、代入文を用います。代入文の左辺に「関数名」を、右辺に返値として出力する値（または値の入った変数名）を記述します。今回の場合、返値は変数「標準体重」に入っているので、「標準体重算出＝標準体重」と記述します。

2.1.2 BMI値を算出する関数の作成

　今度は身長と体重からBMI値を算出するワークシート関数、「BMI値算出」を作成しましょう。

　標準体重算出関数と異なる点は、引数が２つある点です。ワークシート上では以下のようにカンマで挟んで身長、体重の順に引数を与えることにします。

　身長、体重の順に引数を入力すると... BMI値算出関数の外見２

　このように、複数の引数をとる関数を作りたい場合、引数の宣言の箇所を以下のように記述します

Function BMI値算出(身長 As Double, 体重 As Double)

　上記のように、使用したい引数をカンマで区切りながら順番に宣言します。引数を宣言する順序は、そのまま関数の利用時の引数の記述順序となります。ですから、利用時にどのような順番で入力するのが良いのかをよく考慮して宣言しましょう（ちなみに返値は一つしか指定できません）。

　ちなみに、BMI値は以下の式で算出されます。

BMI値＝体重／（身長×身長）

　上記の式でも身長はm単位です。

　これらを手がかりに、自力で処理手順の構想を立てた上で関数を作成してみましょう。

2.2 VBAマクロにおける関数の利用

　上記のように作成した関数は、VBAマクロの記述においてサブルーチンとしても利用することができます。一般の、Subで記述されたプロシージャをサブルーチンとして呼び出す場合、プロシージャ名を記述します。Functionで定義された関数をサブルーチンとして呼び出す場合も、関数名で呼び出す点は変わりありません。

　関数の呼び出しが一般のプロシージャの呼び出しと異なる点は、引数と返値がある点です。以下は「BMIによる肥満度判定」マクロ（第２回、練習問題3.3参照）を、BMI値算出関数、標準体重算出関数を用いる形に書き換えたものです。さらに、肥満度判定の部分も関数化を施しました。書き換えられた箇所を見つけてみましょう。

Sub BMIによる肥満度判定()
	Dim 身長 As Double
	Dim 体重 As Double
	Dim 標準体重 As Double
	Dim BMI As Double
	Dim 判定結果 As String

	'　BMI算出に必要なデータを取得する
	・・・ソースコードは省略・・・

	'　BMIを算出し出力する
	BMI = BMI値算出(身長, 体重) 'BMIを算出する
	・・・ソースコードは省略・・・    '四捨五入して小数点１桁に変換する
	・・・ソースコードは省略・・・    'BMIを出力する

	'　標準体重算出に必要なデータを取得する
	身長 = Range("B1").Value

	'　標準体重を算出し出力する
	標準体重 = 標準体重算出(身長)   '標準体重を算出する
	・・・ソースコードは省略・・・    '四捨五入して小数点１桁に変換する
	・・・ソースコードは省略・・・    '標準体重を出力する

	'　BMIに基づいて肥満度判定をし結果を出力する
	判定結果 = 肥満度判定(BMI)
	・・・ソースコードは省略・・・
End Sub

　関数が用いられているのは、「BMIを算出し出力する」「標準体重を算出し出力する」「BMIに基づいて肥満度判定をし結果を出力する」の、それぞれ１行目です。以下にその３行だけ抜粋してみます。

BMI = BMI値算出(身長, 体重)
標準体重 = 標準体重算出(身長)
判定結果 = 肥満度判定(BMI)

　作成した関数をマクロ内で呼び出して利用するためには次のようにします。まず、利用したい箇所で関数名を記述するとともに、（）内に必要な引数の値（または値の入った変数名）を記述します。その際には、必ず引数の個数と順序、そして引数のデータ型が、あらかじめ定義しておいたものと一致するようにします。複数の引数がある場合はカンマで区切ります。次に、関数の返値を受け取れるように、代入文を指示する＝とともに、左辺に変数を記述します。左辺の変数のデータ型も、あらかじめ定義された返値のデータ型と必ず一致するようにします。

参考！　BMI算出後の身長の再取得

　上記のソースコードでは、BMIを算出したあとで、再び身長の値をセルB1から取得し直しています（コメント「標準体重算出に必要なデータを取得する」部分）。これはBMI値算出関数が身長をメートル単位に換算する処理を行うと、メインルーチン（「BMIによる肥満判定」プロシージャ）側の変数「身長」の値も同時にメートル単位に書き換えられてしまうからです。そのまま計算を続行すると、次の標準体重算出関数の内部でもメートル単位への換算を行うため、結果としてメートル単位の値がさらに1/100になってしまい正しい計算結果が得られません。そこで標準体重関数の処理に移る前に、改めてセルB1からセンチメートル単位の身長を取得しておくことで、正しい計算結果を得られるようにします。

2.3 練習問題

2.3.1 問題１

　Sheet2に掲載された表のBMI値と肥満度判定の欄を自動的に埋めるプログラムを作成しなさい。(practice05.xls, Sheet2)

2.3.2 問題２

　円ドル換算をするワークシート関数を作成してみましょう。以下のように引数に円元金（換算したい円の金額）を入力すると、返値としてドル換算額が出力されるようにします。ただし換算レートはマクロ内部にあらかじめ記述しておくものとします。また、同様にしてドル円換算をするワークシート関数も作ってみましょう(practice05.xls, Sheet3)。

　　実行すると...

2.3.3 問題３

　以下のように特定のセルにユーザーの選択、元金を入力すると換算額を出力する「円ドル双方向換算」マクロを作成しましょう。マクロの内部では、問題2で作成した円ドル換算関数、ドル円換算関数を呼び出して利用するようにしましょう。

2.3.4 問題４

　以下の条件に従って、西暦を元号に変換する関数「西暦元号変換」を作成してみましょう。また、この関数を利用してSheet4の年表の「元号」欄を自動的に埋めるプログラムを作成しましょう（practice05.xls, Sheet4）。

引数は西暦による年号。ただし「～年」は省略し、数値のみを受け取る（「1974」など。整数型）。
返値は元号による年号（「昭和49年」など。文字列型）。
変換は明治以降に対応。明治以前の西暦が引数として指定された場合は「明治以前です」という文字列を返す。
西暦と元号の対応は次の通り。1868年～1912年→明治、1912年～1926年→大正、1926年～1989年→昭和、1989年以降→平成。ただし、各元号の最初の年（1868年、1912年、1926年、1989年）は、前の元号の最終年でもあるため、次の形式で双方の年次について表記した文字列を返値として出力する。たとえば1868年ならば「慶応4年または明治元年」、1912年ならば「明治45年または大正元年」。
返値となる元号の表記の作成には文字列と数値の連結が必要である。文字列や数値の連結には「&」を使う。たとえば「昭和n年」（ただしnには任意の整数が当てられる）という表記を作成する場合で、なおかつnにあたる整数が変数「元号」に格納されている場合、連結したいもの同士を「&」でつなぎ、「"昭和" & 元号 & "年"」という命令文を書く。この命令文が実行されると、結果として「昭和＜変数元号に格納される数値＞年」という文字列データが作成される。これを代入文で文字列（String）型変数に格納して利用する。

3. 補足・グラフの表示

　ここでは補足としてグラフの表示を行う方法を学習します。

3.1 グラフの表示の手順

　ワークシート上に作成した表からグラフを表示するプログラムを作成することができます。Excelでは、独立したグラフシートにグラフを表示するやり方と、既存のワークシート上にグラフを埋め込むやり方の2種類が用意されています。ここでは小規模なグラフ作成を想定して、後者すなわち「埋め込みグラフ」をVBAによって表示する方法を取り上げます。

　たとえば、上記のようにA1からB13に作成された表（月別の来客者数の一覧）から右側の棒グラフを作成して表示するためには以下のようなプログラムを書きます。

Sub 月別来客数のグラフ表示()
	Dim グラフコンテナ As ChartObject
	Dim グラフ1 As Chart

	'ワークシート上にグラフコンテナとグラフを用意する
	Set グラフコンテナ = ActiveSheet.ChartObjects.Add(100, 20, 400, 300)	'ワークシート上にグラフコンテナを用意する
	Set グラフ1 = グラフコンテナ.Chart	'グラフコンテナ上にグラフを用意する

	'グラフの詳細を設定する
	グラフ1.SetSourceData Range("A1:B13")	'グラフのデータ範囲を設定する
	グラフ1.ChartType = xlColumnStacked	'グラフの種類を設定する
	グラフ1.HasTitle = True 'グラフのタイトル表示を設定する
	グラフ1.ChartTitle.Text = "月別来客者数"   'グラフのタイトルとなる文字列を設定する
End Sub

　グラフ表示の手順は大きく「ワークシート上にグラフコンテナとグラフを用意する」ことと「グラフの詳細を設定する」ことに分かれます。それぞれの詳細について順に説明します。

3.1.1 グラフコンテナとグラフの用意

　Excelにおける埋め込みグラフの表示は、概念的には「ワークシート上にグラフコンテナ（グラフの“台紙”のようなもの）を設置し、その上にグラフ本体を設置する」という形をとっています。プログラムはその手順をそのまま追う形で作成します。グラフコンテナはChartObjectというオブジェクトのコレクション（集合体）で表現されます。一方グラフはChartオブジェクトとして表現されます。以下の変数宣言はそれを反映しています


Dim グラフコンテナ As ChartObject
Dim グラフ1 As Chart

ChartObject及びChartは「オブジェクト型」に属します。オブジェクト型のデータの代入に際しては、Setという命令を加えて以下の書式をとります


Set オブジェクト型の変数 = 代入するデータ（または代入するデータを生成する命令文）

　変数宣言に続く2行の代入文は順に「ワークシート上にグラフコンテナを用意する」命令と、「グラフコンテナ上にグラフを用意する」命令です。まず現在アクティブになっているワークシート（すなわち埋め込み対象のワークシート）上にAddメソッドによってChartObjects（ChartObjectのコレクション）を追加します。Addメソッドの4つの引数はコンテナの表示位置を表します。順に「左端からの距離、上端からの距離、幅、高さ」をポイント数（整数値）で表します。Addメソッドを起動することで生成されたChartObjectを変数「グラフコンテナ」で受け取ります。

　次の行では変数「グラフコンテナ」を介して、ChartObjectの上にグラフを加えるChartメソッドを起動しています。この結果として生成されるChartオブジェクト（グラフ本体を表す）を変数「グラフ1」で受け取ります。

3.1.2 グラフの詳細の設定

　プログラムの後半では「グラフの詳細を設定」しています。


'グラフの詳細を設定する
グラフ1.SetSourceData Range("A1:B13")	'グラフのデータ範囲を設定する
グラフ1.ChartType = xlColumnStacked	'グラフの種類を設定する
グラフ1.HasTitle = True 'グラフのタイトル表示を設定する
グラフ1.ChartTitle.Text = "月別来客者数"   'グラフのタイトルとなる文字列を設定する

　グラフの設定項目は「グラフのデータ範囲」「グラフの種類」「グラフのタイトル表示（表示ならばTrue、非表示ならばFalse」「グラフのタイトルとなる文字列」の４項目です。すべての設定は、Chartオブジェクト（変数「グラフ1」を介してアクセスされる）に対して行われます。

　ちなみに、Excel VBAではグラフの種類に関して以下の名称（定数）を用います（抜粋）。


＜グラフの名称一覧＞
集合縦棒　xlColumnClustered
積み上げ縦棒　xlColumnStacked
集合横棒　xlBarClustered
積み上げ横棒　xlBarStacked
折れ線　xlLine
データマーカー付き折れ線　xlLineMarkers
円　xlPie
分割円　xlPieExploded
散布図　xlXYScatter
平滑線付き散布図　 xlXYScatterSmooth
折れ線付き散布図　xlXYScatterLines
集合円柱縦棒　xlCylinderColClustered
積み上げ円柱縦棒　xlCylinderColStacked
集合円錐縦棒　xlConeColClustered
積み上げ円錐縦棒　xlConeColStacked
集合ピラミッド縦棒　xlPyramidColClustered
積み上げピラミッド縦棒　xlPyramidColStacked

3.2 練習問題

Sheet2の体重及びBMI値の推移表からグラフを作成しましょう。ただしグラフの種類は折れ線グラフ（xlLine）を用いることにします。

ページ冒頭へ戻る
目次ページへ戻る

第５回 関数の作成と利用

概要