独自の関数を作成する。
関数はあらかじめ用意されているSUM関数などの他に、独自に作成した関数を作成することが出来ます。独自に作成した関数は「ユーザー定義関数」と言います。
ユーザー定義関数は、VBAを使用して作成します。
サンプルファイルをダウンロード出来ます。
サンプルでは、売上総利益率を求めいる計算式を作成します。
売上総利益は、売上高から売上原価を差し引き、その値を、売上高で割って求めます、次に「%」表示にするため100倍にし、売上総利益率を求めることが出来ます。
売上総利益率=(売上高ー売上原価)/売上高*100
今回のサンプルでは、Format関数を使用して表示形式を設定しています。その為一旦
売上総利益率=(売上高ー売上原価)/売上高として求めてから、format関数で「%」表示に変更しています。
ユーザー定義関数の作成手順
<手順>
1、 [Alt]+[F11]キー押下で、Visual Basic Editor が起動します。
2、 メニューから[挿入]-[標準モジュール]をクリック
3、開いたウィンドウに次のコードをコピー&ペースト
Function 売上総利益率(売上高, 売上原価)
売上総利益率 = (売上高 - 売上原価) / 売上高
売上総利益率 = Format(売上総利益率, "0.00%")
End Function
4、「ファイル」「終了してMicrosoft Excel」に戻る」をクリックし Visual
Basic Editor を終了します。
-----------------------------------------------------------------------------------------------------------------------
5、作成したユーザー定義関数を使用して、売上総利益率を求めます。
1、売上総利益率を求めるセル「C2」を選択し「Fx」をクリックします。 |
2、「関数の分類」「ユーザー定義」を選択します。 |
|
|
3、「関数名」で、使用する関数「売上総利益」を選択して「OK」をクリックします。 |
4、「関数の引数」の画面が表示されます。 |
|
|
5、ワークシートの画面に戻って「A2」の売上高をクリックします。 |
6、「関数の引数」の場面に戻って「売上原価」の欄にカーソルを移動します。 |
|
|
7、ワークシートに戻って「B2」の売上原価をクリックします。 |
8、「関数の引数」画面に戻って「OK」をクリックします。 |
|
|
9、以下のように売上総利益率が求められます。
(表示形式を三桁区切り、中央揃えにしてあります。) |
|
|
|
ユーザー定義関数サンプル2
以下のサンプルは、退社時間から出勤時間を引いて勤務時間を求め、それに時給を掛けて、日給を求めるユーザー定義関数です。
1、最初に出勤時間の処理では、15分単位で出勤時間を処理しますが15で割り切れない場合は、15分単位で切り上げ処理をしています。
2、退社時間の処理では、15分単位で切り下げ処理をしています。
3、日給の計算処理では、四者五入で処理をしています。
サンプルファイルをダウンロード出来ます。
<手順>
1、[Alt]+[F11]キー押下で、Visual Basic Editor が起動します。
2、メニューから[挿入]-[標準モジュール]をクリック
3、開いたウィンドウに次のコードをコピー&ペースト 4、「ファイル」「終了してMicrosoft Excel」に戻る」をクリックし Visual Basic Editor を終了します。
Option Explicit
Dim Smin As Date, Tmin As Date, KEI As Date
Function 日給計算(出勤時間, 退社時間, 時給)
'出勤時間の処理・・・・・・・・・・・・・・・・・・・・・・・・・・
'15分単位で切上げ処理をします
If Minute(出勤時間) / 15 - Int(Minute(出勤時間) / 15) > 0 Then ’「MInute(出勤時間)」で分を取り出しています。
Smin = Int(Minute(出勤時間) / 15 + 1) * 15 ’「15 + 1」で切り上げ処理をしています。
Else
Smin = Minute(出勤時間) ’「15、30、45、00」のように出勤時間の分の部分が15で割り切れた場合は、ココに処理が移ります。
End If
'退社時間の処理・・・・・・・・・・・・・・・・・・・・・・・・・・・
'15分単位で切下げ処理をする
If Minute(退社時間) / 15 - Int(Minute(退社時間) / 15) > 0 Then
Tmin = Int(Minute(退社時間) / 15) * 15
Else
Tmin = Minute(退社時間)
End If
'給与計算処理・・・・・・・・・・・・・・・・・・・・・・・・・・・・
'四者五入で処理します。
KEI = Hour(退社時間) + Tmin / 60 - Hour(出勤時間) - Smin / 60
日給計算 = KEI * 時給 + 0.5 ’「+ 0.5」することで、int関数と組み合わせて四者五入の処理を行うことが出来ます。
日給計算 = Int(日給計算) ’「+ 0.9」だと切り上げ処理になります。
End Function
以下の手順でユーザー定義関数を入力してみて下さい。
手順は先に紹介した「売上総利益率」と同じです。
1、「D3」を選択し「Fx」をクリックし、関数を入力します |
2、「関数の挿入」画面で「ユーザー定義」を選択し「OK」をクリックします。 |
|
|
3、「関数名」のところに、作成した「日給計算」関数が表示されていますからそれを選択して「OK」をクリックします。 |
4、 |
|
|
5、 |
6、 |
|
|
7、 |
8、 |
|
|
9、 |
10、 |
|
|
|