サンプル

配当率計算(エクセルVBA)

破産手続のために法律事務員が行わなければならない仕事のひとつに、配当表の作成がありますが、配当率の計算が面倒ですよね。
そこで、エクセルのマクロを使って配当率を計算する方法を紹介します。

確かに、配当額の総額と配当可能原資とを完全に一致させようと思うと、すごく時間がかかってしまうんです。

マクロを使えば、簡単に計算できるようになりますよ。

そんなこともできるんですか?
ぜひ、教えて下さい。

ダウンロード

まずは、サンプル・ファイルをダウンロードしてください。

サンプル・ファイルには、実際に使用する配当表は含まれていません。お手元にない場合は、こちらからダウンロードできます。

なお、配当表の一般的な書き方は、こちらに記載されています。

「破産管財実践マニュアル」では、「現実の配当額の総額と配当可能原資とは完全に一致しませんが、現実の配当額の総額が配当可能原資を上回り、配当原資が不足しない限り、厳密に考える必要はありません。」となっています。そもそも、配当額の総額と配当可能原資とを完全に一致させる必要は、ないのでしょうか?

実態としては、厳密な計算をしても意味がないので、完全に一致させることを求めている裁判所は、少なくなってきているようです。ただし、未だにそれを求めている裁判所もあります。

そもそも、このマクロが必要かどうかは、管轄の裁判所などによって異なるということですね。

使用方法

インストール

  • ダウンロードしたZIPファイルの中にある「配当率計算(Ver.1.00).xlam」をエクセルのアドインに追加(インストール)してお使いください。
  • アドインのインストール方法については、次のサイトなどを参考にしてください。

ワークシートの準備

既に使用しているエクセルの配当表がある場合は、シートを次のとおり準備してください。

  • ワークシート上の空いているセルに「配当率」と名前を付けてください。(セルの文字色を白にすれば、計算結果が表示されないようにすることもできます。)
配当率入力セルの設定
  • 各債権者行の「配当することができる金額」列に「配当の手続に参加することができる債権の額」と「配当率」から金額を求める数式を入力してください。
    例:=IF(L10*配当率=0,””,ROUNDDOWN(L10*配当率,0))
配当金額計算セルの設定
  • 「合計」行の「配当することができる金額」列のセルに「合計配当金額」と名前を付けてください。(=SUM(M10:M20)などの合計を求める数式はそのままにしてください。)
合計配当金額計算セルの設定
  • 「配当率→…」と入力されているセルに「配当率」が表示されるように数式を入力してください。
    例:=”一般破産債権” &配当率*100&”%”

操作手順

  • Ctrl+Shift+Aを入力すると入力フォームが表示されます。
  • 「合計配当金額の目標値」と「配当率の小数点以下の最大桁数」を入力してください。最大桁数のデフォルト値は、「8」に設定しています。
  • 「OK」をクリックすると配当率がワークシートの「合計配当金額」が「合計配当金額の目標値」になるように「配当率」が計算されます。
  • 解答が得られなかった場合は、最大桁数を増加させるか、「配当することができる金額」の数値の丸め方を変更して再度試してください。

このマクロには、デジタル証明を添付しています。必要な設定を行って、マクロを使える状態にしてください。設定方法が分からない方は、こちらのサイトに詳しい説明があります。

なお、このサンプルは、マクロを使って配当率を計算する場合の参考にしていただくためのものです。それぞれの法律事務所によって、やり方が異なると思いますので、必要に応じ修正を加えてご使用下さい。

うちの事務所には、エクセルのテンプレートを使って配当表を作っています。

その場合は、このマクロをアドインにインストールし、テンプレートの計算式を修正し、セルの名前を追加すれば使えるようになります。また、テンプレート自体に、マクロを組み込むこともできます。

どちらがいいんでしょうか?

このマクロを使用するのは、配当表だけですから、テンプレートに組み込んだ方が便利な場合が多いですね。ただし、弁護士や他の法律事務員が配当率と配当額を入力した後で、計算だけをやらなければならない場合には、アドインの方が便利でしょうね。

マクロの構造

それでは、「配当率計算」のマクロについて説明しましょう。

いよいよマクロに関する説明ですね。

エクセルの初期設定では、「開発」タブが表示されていないので、VBAのエディターを開くことができません。
まだ、設定が終わっていない方は、下のサイトを参考に設定を行ってください。

VBEへの入力位置

「開発」-「Visual Basic」をクリックして、VBEを起動してください。

左上の「プロジェクト・エクスプローラー」部で各モジュールをクリックすると、そこに記載されているコードが真ん中の「コード・エディター」部に表示されます。

まず、「標準モジュール」-「Module1」を開いてください。

標準モジュールに記載されているコード

「Module1」には、ユーザーフォームを表示するプロシージャだけが記載されています。それ以降のプロシージャは、フォームモジュールに記載しています。

このユーザーフォームを表示するプロシージャは、ショートカットキー(「Ctrl+Shift+A」)で起動するように設定しています。ショートカットキーの設定要領については、こちらの記事を参考にしてください。

なお、このマクロは、アドインとして使うことを予定しているため、このファイル自体のワークシートに変更を加えるプロシージャはありません。このため、シートモジュールには、何も記載されていません。

このマクロの起動には、イベントプロシージャは使っていないんですね。

そうですね。
シートモジュールのコードは、アドイン化できません。このため、イベントプロシージャを使うことができません(応用技はあるようですが...)。このため、ショートカットキーで起動するようにしています。

次に、「フォーム」-「UserForm1」を開いてください。

フォームモジュールのオブジェクト画面

この画面では、このマクロで表示されるユーザーフォームが編集できます。上からラベルとその右にテキストボックス、その下にもう一度ラベルとテキストボックス、その下に2つのコマンドボタンを配置しています。

「F7」を押すと、このユーザーフォームのコードが表示されます。

フォームモジュールのコード画面

この画面では、ユーザーフォームに配置した各コマンドボタンをクリックした場合に起動するマクロを編集することができます。

各プロシージャの機能

標準モジュールの「Module1」には、「SeekAllocationRate」プロシージャのみが組み込まれています。このプロシージャは、「UserForm1」を表示します。

フォームの「UserForm1」には、コマンドボタンをクリックした場合に動作するプロシージャなどが組み込まれています。
CommandButton1_Click」プロシージャには、「OK」ボタンがクリックされた場合に配当率の計算を行うマクロが記載されています。このアドインプログラムの主要な部分になります。
CommandButton2_Click」プロシージャには、「キャンセル」ボタンがクリックされた場合に、ユーザーフォームを非表示にするマクロが記載されています。
TextBox1_Change」プロシージャには、テキストボックスの書式を設定するマクロが記載されています。

以上で、マクロの構造に関する説明を終わります。

ユーザーフォームの各コマンドは、ワークシートのイベントプロシージャと良く似ていますね。

そのとおりです。コードの実行方法に関しては、シートモジュールとフォームモジュールは、同じような機能を持っていると考えていいでしょう。

各プロシージャのコード

SeekAllocationRateプロシージャ

Module1」モジュールの「SeekAllocationRate」プロシージャには、ユーザーフォームを表示するためのコードが入力されています。

Sub SeekAllocationRate()
    
'ユーザーフォームを表示する
UserForm1.Show

End Sub

CommandButton1_Clickプロシージャ

UserForm1」モジュールの「CommandButton1_Click」プロシージャには、「OK」のコマンドボタンがクリックされた場合に起動するマクロのコードが入力されています。

Option Explicit

Private Sub CommandButton1_Click()
Dim lngTarget As Long   '合計配当金額の目標値
Dim lngResult As Long   '合計配当金額の計算値
Dim i As Long           '配分率の小数点以下の桁数
Dim iMax As Long        '配分率の小数点以下の最大桁数
Dim j As Currency           '配分率の小数点以下の数値
Dim jLower As Currency      '配分率の小数点以下の数値の下限値
Dim jUpper As Currency      '配分率の小数点以下の数値の上限値

'目標値と小数点以下の最大桁数を取得する
lngTarget = TextBox1.Value
iMax = TextBox2.Value

'最大桁数が通貨型変数の最大値を超える場合は終了する
If iMax > 14 Then
    MsgBox "最大桁数は、14以下に設定してください。"
    Exit Sub
End If

'実行前の処理を行う
With Application
    .ScreenUpdating = False
    .Cursor = xlWait
    .StatusBar = "計算中です。"
End With

'配分率の小数点以下の数値の下限値と上限値の初期値を設定する
jLower = 0
jUpper = 1

'小数点以下1桁から最大桁数まで計算を繰り返す
For i = 1 To iMax
    '配分率の小数点以下の数値の下限値と上限値を1桁繰り上げる
    jLower = jLower * 10
    jUpper = jUpper * 10
    
    '小数点以下の数値の下限値から上限値まで計算を繰り返す
    For j = jLower To jUpper Step 1
        '参照範囲が定義されていない場合にエラー処理を行う
        On Error GoTo HdlErr
        
        'シートに配分率の計算結果を出力する
        ActiveSheet.Range("配当率").Value = j / (10 ^ i)
        
        'シートから合計配当金額の計算値を取得する
        lngResult = ActiveSheet.Range("合計配当金額").Value
        
        'エラー処理を無効にする
        On Error GoTo 0
        
        '合計配当金額の計算値が目標値よりも小さい場合は、その小数点以下の数値を下限値とする
        If lngResult < lngTarget Then
            jLower = j
        End If
        
        '合計配当金額の計算値が目標値よりも大きい場合は、その小数点以下の数値を上限値とし、次の桁の計算に移行する
        If lngResult > lngTarget Then
            jUpper = j
            Exit For
        End If
        
        '合計配当金額の計算値が目標値と等しい場合は、計算を終了する
        If lngResult = lngTarget Then GoTo Final
    Next j
Next i

'実行後の処理を行う
Final:
    With Application
        .ScreenUpdating = True
        .Cursor = xlNormal
        .StatusBar = ""
    End With
       
    If lngResult = lngTarget Then
        MsgBox "解答が見つかりました。"
    Else
        MsgBox "小数点以下" & iMax & "桁までの間に解答が見つかりませんでした。" & vbCr & vbCr & _
            "「小数点以下の最大桁数」を増やすか、「配当することができる金額」の数値の丸め方を変えてください。"
        ActiveSheet.Range("配当率") = 0
    End If
        
    Unload Me
    
    Exit Sub

'エラー処理
HdlErr:
MsgBox "セルに名前が付けられていません。" & vbCr & vbCr & _
    "該当するセルに「配当率」と「合計配当金額」という名前を付けてください。", vbExclamation

End Sub

目標値と小数点以下の最大桁数を取得する」のプロセスでは、ユーザーフォームに入力された、「合計配当金額の目標値」と「配当率の小数点以下の最大桁数」の値をそれぞれの変数に取り込んでいます。

最大桁数が通貨型変数の最大値を超える場合は終了する」のプロセスでは、最大桁数が14を超えるとエラーが生じるため、それを回避しています。

実行前の処理を行う」のプロセスでは、ワークシートの表示の更新を停止させることによって、マクロの実行速度を向上させています。また、マウスカーソルの表示を変更するとともに、ステータスバーに「計算中です。」と表示させています(計算処理は、通常、あっという間に終わってしまうので、気づかないかも知れませんが...)。

配分率の小数点以下の数値の下限値と上限値の初期値を設定する」のプロセスでは、小数点以下の数値の下限値と上限値を初期値を設定しています。この下限値と上限値は、次のFor~Next構文で計算の桁数が増えるたびに更新されるようになっています。
ここがこのマクロのポイントです。この下限値と上限値を設定しないで計算をすると、最初は0.0~1.0、次の桁の際には0.00~1.00の間のすべての数値について、目標値と計算値が合致するかどうかを確認しなければならず、膨大な時間がかかってしまいます。(私のPCでは5~6桁が限界でした。)このため、ある数値で計算を行った結果が目標値を下回ったのか、目標値を上回ったのかに応じて、下限値と上限値を設定するようにしています。例えば、0.07の次の桁を計算する場合は、この設定を行わなければ、0.000~1.000のすべての値について計算しなければなりませんが、この設定をおこなえば、0.070~0.080を計算するだけで済みます。

小数点以下の数値の下限値から上限値まで計算を繰り返す」のプロセスは、For~Next構文のネスト(入れ子)になっています。細部の説明は省略しますが、For~Next構文の練習問題のようなマクロですので、ぜひ、理解しておくことをお勧めします。

実行後の処理を行う」のプロセスは、「実行前の処理を行う」で設定した画面表示の停止などを解除するとともに、計算の結果をメッセージボックスで表示しています。

CommandButton2_Clickプロシージャ

「キャンセル」ボタンがクリックされた場合にユーザーフォームの表示を停止します。

Private Sub CommandButton2_Click()
Unload Me
End Sub

TextBox1_Changeプロシージャ

ユーザーフォームのテキストボックスには、3桁ごとに「,」を入れる機能がありませんので、値が変更されるたびに書式を設定しています。
この件に関しては、次の記事を参考にさせていただきました。

Private Sub TextBox1_Change()
TextBox1.Text = Format(TextBox1.Text, "#,##0")
End Sub

お疲れさまでした。
以上で「配当率計算」マクロの構造と機能の説明を終わります。

これって、ワークシートのゴールシーク機能が行っている処理と同じではないですか?

そのとおりです。

ただし、ゴールシークが計算してくれる「最適値」は、このマクロで求められる「小数点以下の桁数が最も少ない値」とは違っています。ゴールシークを用いて計算してから、桁数を減らしていくマクロを作るという方法もあるかも知れませんが、それよりも最初からマクロで作ってしまった方が簡単だと思います。

これと同じことをワークシートの関数で実現することはできないのですか?

そのような関数はありませんし、あったとしても「配当率」セルに入れた数式が「配当率」を使って計算することになりますので、循環参照でエラーになってしまいます。

そもそも、ワークシートにゴールシークの機能があるのは、そのためなんですね。

コメント

タイトルとURLをコピーしました