破産手続のために法律事務員が行わなければならない仕事のひとつに、配当表の作成がありますが、配当率の計算が面倒ですよね。
そこで、エクセルのマクロを使って配当率を計算する方法を紹介します。
確かに、配当額の総額と配当可能原資とを完全に一致させようと思うと、すごく時間がかかってしまうんです。
マクロを使えば、簡単に計算できるようになりますよ。
そんなこともできるんですか？
ぜひ、教えて下さい。
このマクロをあらかじめ組み込んだ配当表も紹介しています。配当表自体を作るのが面倒な方は、こちらをご利用ください。
ダウンロード
まずは、サンプル・ファイルをダウンロードしてください。
インターネットからダウンロードしたマクロは、そのままでは警告メッセージが表示されて使用できません。マクロを使用可能にするための操作を行ってください。
このマクロは、法律事務所でマクロを活用する場合のサンプルです。そのまま使っていただいても構いませんが、マクロに修正を加え、それぞれの法律事務所に適合するようにご自身でカスタマイズすることをお勧めします。
サンプル・ファイルには、実際に使用する配当表は含まれていません。お手元にない場合は、こちらからダウンロードできます。
なお、配当表の一般的な書き方は、こちらに記載されています。
「破産管財実践マニュアル」では、「現実の配当額の総額と配当可能原資とは完全に一致しませんが、現実の配当額の総額が配当可能原資を上回り、配当原資が不足しない限り、厳密に考える必要はありません。」となっています。配当額の総額と配当可能原資とを完全に一致させなくても良いのではないでしょうか？
実態としては、厳密な計算をしても意味がないので、完全に一致させることを求めている裁判所は、少なくなってきているようです。ただし、未だにそれを求めている裁判所もあります。
このマクロが必要かどうかは、管轄の裁判所などによって異なるということですね。
使用方法
インストール
- ダウンロードしたZIPファイルの中にある「配当率計算（Ver.1.00）.xlam」をエクセルのアドインに追加（インストール）してお使いください。
- アドインのインストール方法については、次のサイトなどを参考にしてください。
ワークシートの準備
既に使用しているエクセルの配当表がある場合は、シートを次のとおり準備してください。
- ワークシート上の空いているセルに「配当率」と名前を付けてください。(セルの文字色を白にすれば、計算結果が表示されないようにすることもできます。）
- 各債権者行の「配当することができる金額」列に「配当の手続に参加することができる債権の額」と「配当率」から金額を求める数式を入力してください。
例：=IF(L10*配当率=0,””,ROUNDDOWN(L10*配当率,0))
- 「合計」行の「配当することができる金額」列のセルに「合計配当金額」と名前を付けてください。（=SUM(M10:M20)などの合計を求める数式はそのままにしてください。）
- 「配当率→…」と入力されているセルに「配当率」が表示されるように数式を入力してください。
例：=”一般破産債権” &配当率*100&”％”
操作手順
- Ctrl+Shift+Aを入力すると入力フォームが表示されます。
（表示されない場合は、「開発」－「マクロ」をクリックして設定してください。） - 「合計配当金額の目標値」と「配当率の小数点以下の最大桁数」を入力してください。最大桁数のデフォルト値は、「8」に設定しています。
- 「OK」をクリックすると配当率がワークシートの「合計配当金額」が「合計配当金額の目標値」になるように「配当率」が計算されます。
- 解答が得られなかった場合は、最大桁数を増加させるか、「配当することができる金額」の数値の丸め方を変更して再度試してください。
必要な設定を行って、マクロを使える状態にしてください。設定方法が分からない方は、こちらのサイトに詳しい説明があります。
なお、このサンプルは、マクロを使って配当率を計算する場合の参考にしていただくためのものです。それぞれの法律事務所によって、やり方が異なると思いますので、必要に応じ修正を加えてご使用下さい。
うちの事務所には、エクセルのテンプレートを使って配当表を作っています。
その場合は、このマクロをアドインにインストールし、テンプレートの計算式を修正し、セルの名前を追加すれば使えるようになります。また、テンプレート自体に、マクロを組み込むこともできます。
どちらがいいんでしょうか？
このマクロを使用するのは、配当表だけですから、テンプレートに組み込んだ方が便利な場合が多いですね。ただし、弁護士や他の法律事務員が配当率と配当額を入力した後で、計算だけをやらなければならない場合には、アドインの方が便利でしょうね。
マクロの構造
それでは、「配当率計算」のマクロについて説明しましょう。
いよいよマクロに関する説明ですね。
VBEへの入力位置
「開発」－「Visual Basic」をクリックして、VBEを起動してください。
「開発」タブは、既定では非表示になっています。表示させるためには、設定の変更が必要です。
左上の「プロジェクト・エクスプローラー」部で各モジュールをクリックすると、そこに記載されているコードが真ん中の「コード・エディター」部に表示されます。
まず、「標準モジュール」－「Module1」を開いてください。
「Module1」には、ユーザーフォームを表示するプロシージャだけが記載されています。それ以降のプロシージャは、フォームモジュールに記載しています。
このユーザーフォームを表示するプロシージャは、ショートカットキー（「Ctrl+Shift+A」）で起動するように設定しています。ショートカットキーの設定要領については、こちらの記事を参考にしてください。
なお、このマクロは、アドインとして使うことを予定しているため、このファイル自体のワークシートに変更を加えるプロシージャはありません。このため、シートモジュールには、何も記載されていません。
このマクロの起動には、イベントプロシージャは使っていないんですね。
そうですね。
シートモジュールのコードは、アドイン化できません。このため、イベントプロシージャを使うことができません（応用技はあるようですが．．．）。このため、ショートカットキーで起動するようにしています。
次に、「フォーム」－「UserForm1」を開いてください。
この画面では、このマクロで表示されるユーザーフォームが編集できます。上からラベルとその右にテキストボックス、その下にもう一度ラベルとテキストボックス、その下に2つのコマンドボタンを配置しています。
「F7」を押すと、このユーザーフォームのコードが表示されます。
この画面では、ユーザーフォームに配置した各コマンドボタンをクリックした場合に起動するマクロを編集することができます。
各プロシージャの機能
標準モジュールの「Module1」には、「SeekAllocationRate」プロシージャのみが組み込まれています。このプロシージャは、「UserForm1」を表示します。
フォームの「UserForm1」には、コマンドボタンをクリックした場合に動作するプロシージャなどが組み込まれています。
「CommandButton1_Click」プロシージャには、「ＯＫ」ボタンがクリックされた場合に配当率の計算を行うマクロが記載されています。このアドインプログラムの主要な部分になります。
「CommandButton2_Click」プロシージャには、「キャンセル」ボタンがクリックされた場合に、ユーザーフォームを非表示にするマクロが記載されています。
「TextBox1_Change」プロシージャには、テキストボックスの書式を設定するマクロが記載されています。
以上で、マクロの構造に関する説明を終わります。
マクロの全体的な構造は分かりました。あとはプログラムの細部ですね。
そうですね。それについては、サンプルファイルのコードにできるだけ詳しいコメントを付けてありますので、そちらをご覧ください。
ユーザーフォームの各コマンドは、ワークシートのイベントプロシージャと良く似ていますね。
そのとおりです。コードの実行方法に関しては、シートモジュールとフォームモジュールは、同じような機能を持っていると考えていいでしょう。
このマクロが行っている処理って、ワークシートのゴールシーク機能が行っている処理と同じではないですか？
そのとおりです。
ただし、ゴールシークが計算してくれる「最適値」は、このマクロで求められる「小数点以下の桁数が最も少ない値」とは違っています。ゴールシークを用いて計算してから、桁数を減らしていくマクロを作るという方法もあるかも知れませんが、それよりも最初からマクロで作ってしまった方が簡単だと思います。
これと同じことをワークシートの関数で実現することはできないのですか？
そのような関数はありませんし、あったとしても「配当率」セルに入れた数式が「配当率」を使って計算することになりますので、循環参照でエラーになってしまいます。
そもそも、ワークシートにゴールシークの機能があるのは、そのためなんですね。
コメント
各プロシージャの細部説明を記事から削除し、サンプルファイル内のコードにコメントとして記載しました。
これに伴い、バージョンを1.01から1.02に変更しました。
（マクロの機能には、変更ありません。）