ここ最近ずっと暑い日が続いていますが、以前に比べれば涼しくはなってきている気がします。最近は、『アプリ作成で学ぶExcelVBAプログラミングユーザーフォーム&コントロール』という本を読んでいました。
仕事でExcelVBAを使ってツールやシステムをつくる機会があるわけではありませんし、ここまでExcelVBAに関してはもう基礎的な部分はある程度勉強してきたのではないかということで、何かこう「ある程度まとまったシステム」をつくるための勉強ができる本はないかと以前から考えていました。
そういった考えでインターネットで調べていたら『アプリ作成で学ぶExcelVBAプログラミングユーザーフォーム&コントロール』という本を見つけて早速購入し、最近読んでいたというわけです。
この本はまだ読み始めたばかりなので、本書全体の書評はまだ書けませんが、とあるページから気になったコードを見つけたので、そのコードで感じたことを今回書いていってみます。
金種を自動で計算できるプログラムのコードを見て感じたこと
「あぁ、すごい。こういったやり方だと金種が上手く計算できるんだな」というのが最初にそのコードを見たときの感想でした。
とあるコードを最初に見たとき、その考えられた手順からちょっとした感動があったのですが、そのコードというのは、『アプリ作成で学ぶExcelVBAプログラミングユーザーフォーム&コントロール』の168ページのコラムに書かれた、次のような金種を自動で計算できるプログラムのコードです。
p.168
Sub GetMaisu()
Dim maisu(8) As Integer ‘枚数格納用の配列変数
Dim Kinshu() As Variant ‘金種用の配列変数
Kinshu = Array(10000, 5000, 1000, 500, 100, 50, 10, 5, 1)
Dim Target As Currency
Target = 36789 ‘任意の金額を代入
Dim i As Integer
For i = 0 To 8
’金額を金種で割った商(枚数)を配列変数に格納
maisu(i) = Int(Target / Kinshu(i))
’余りをTargetに代入し直す
Target = Target Mod Kinshu(i)
Next
For i = 0 To 8
MsgBox Kinshu(i) & “円は、” & maisu(i) & “枚です”
Next
End Sub
このプログラムの概要として、現金の各金種が何枚になるかを自動で計算できるものとなっています。会社の業務などで金種の枚数の計算を迫られる時ってどんな場面があるでしょうか。
例えば経理の仕事には「小口現金」といったものがあります。この仕事は、会社の現金の一部を小さな金庫にしまっておき、社員が外出するときの交通費や、ペンや消しゴムなどの備品を買うためなどに使う少額の現金のことを言います。
通常、その日に使われた現金の合計と金庫の中から減った現金の合計が一致している必要があります。その計算のためには金庫の中の現金を全て計算しなければなりません。
なぜなら自分の知らない所で不正な流出があるかもしれませんし、もしくは自分の把握していない所で備品用に何か使われる可能性もあります。金庫の中にある決して少なくない現金を毎日計算するというのは、非常に面倒くさいですし盗難の危険性もあります。そのため、今のような時代はそもそも現金を持たない方向に進んでいます。
そういった世の中でも小口現金として現金を扱う会社もあるわけなので、会社のお金の動きを把握するために毎日ちゃんと計算しないといけません。
毎日計算しないといけないわけですが、ここで問題が発生する時があります。毎日の計算がちゃんと合えばいいですが、いつもいつもそうなるとは限りません。ちょっとした計算ミスで数値が合わない時もありますし、先ほど書いたように自分が知らない所でいつの間にか業務上必要経費として、いくらか金庫のお金が使われていたなんてこともあります。
そういった場合は再度計算しなおしたり、どうしても合わない金額の分は周りの人に確認をしなければいけません。その一致しない金額がちゃんと計算されていればいいですが、必ずしもそうじゃない場合もあります。とにかくいつも完璧というわけにはいかない日もあるということです。
以上のような小口現金の業務において特に金庫から出したお金の計算を合計する時などに引用したプログラムが使えると便利ということです。もちろんプログラムをつくらなくても会社ごとに金種を計算できるフォーマットなどは用意されている所もあります。
とはいってもいろんなやり方があって良いと思いますし、その方が何かと便利です。ちょっと前置きが長くなりましたが、引用したコードについて自分なりに簡単に解説してみます。
金種自動計算プログラムのコードの解説
コードの解説のためにもう一度以下に引用します。
p.168
Sub GetMaisu()
Dim maisu(8) As Integer ‘枚数格納用の配列変数
Dim Kinshu() As Variant ‘金種用の配列変数
Kinshu = Array(10000, 5000, 1000, 500, 100, 50, 10, 5, 1)
Dim Target As Currency
Target = 36789 ‘任意の金額を代入
Dim i As Integer
For i = 0 To 8
’金額を金種で割った商(枚数)を配列変数に格納
maisu(i) = Int(Target / Kinshu(i))
’余りをTargetに代入し直す
Target = Target Mod Kinshu(i)
Next
For i = 0 To 8
MsgBox Kinshu(i) & “円は、” & maisu(i) & “枚です”
Next
End Sub
Sub GetMaisu()が1行目として2行目のDim maisu(8) As Integer で整数型のmaisu(8)の配列変数を宣言しています。maisu(8)は金種の枚数格納用の配列変数です。
3行目のDim Kinshu() As Variantでバリアント型の金種用の配列変数を宣言しています。
4行目のKinshu = Array(10000, 5000, 1000, 500, 100, 50, 10, 5, 1)では3行目で宣言した配列変数Kinshu()にArray関数を挿入しています。Array関数に10000から1の引数がありますが、これは現金の金種になります。
5行目のDim Target As Currencyは通貨型の変数Targetを宣言しています。
6行名のTarget = 36789では、5行目で宣言した通貨型の変数Targetに36,789という任意の金額を挿入しています。
7行目のDim i As Integerでは、次のFor…Nextステートメントに使われるカウンタとして、整数型の変数iを宣言しています。
10行目のmaisu(i) = Int(Target / Kinshu(i))では、For…Nextステートメントの中にあるので、カウンタ変数iには、最初0から挿入されていきます。
まず右辺で36789という数値が代入された通貨型の変数Target÷10000が挿入された金種用の配列変数Kinshu(0)の計算がされて、その結果である3.6789がInt関数により、整数部分である3だけが抽出されます。その3が左辺である金種の枚数格納用の配列変数maisu(0)に格納されます。
12行目のTarget = Target Mod Kinshu(i)では、右辺で、36789が挿入された通貨型の変数Target÷10000が挿入された金種用の配列変数Kinshu(0)が計算されて、その余りである6789が左辺である変数Targetに挿入されます。これがカウンタ変数iが8になるまで繰り返されます。
14行目から16行目がもうひとつのFor…Nextステートメントです。15行目のMsgBox Kinshu(i) & “円は、” & maisu(i) & “枚です”では、まず、変数Kinshu(0)に10000、変数maisu(0) に10000の金種の枚数である3が挿入されます。
指定の数値が挿入された変数からメッセージボックスを通して「10000円は、3枚です」と表示されます。これがカウンタ変数を通して0から8になるまでの計算された結果がメッセージボックスを通して表示されて、17行目のEnd Subでプログラムの終了となります。
自分がちょっと感動した部分というのは、For…Nextステートメントで囲まれた
- コードの10行目maisu(i) = Int(Target / Kinshu(i))
- コードの12行目のTarget = Target Mod Kinshu(i)
の部分です。
最初見た時は「あぁなるほど、こういった使い方もできるんだな」と思いました。10行目で金種の枚数を出すのに入力金額÷10,000(金種)でその整数部分をInt関数で抽出しているというのと、
12行目では、算術演算子であるMod(ExcelVBAにおいて、2つの数値の除算を行い余りを求める算術演算子)を上手く利用しています。
入力金額÷10,000(金種)の余りを算出できるようにすることによって、まだ金種の枚数が計算されていない分の金額を算出できるようになっています。
もしかしたら、途中で間違った部分もあるかもしれませんが、とりあえず自分なりに簡単にコードの説明をしてみました。
金種自動計算プログラムをExcel上で動くように少し手を加えてみた
先ほどの金種自動計算プログラムだとメッセージボックスに計算結果が表示されるだけで終わってしまいます。なので、以下のコードのようにExcel上で動作するように少しいじってみました。修正したのは黄色に塗ったコードの部分だけ。
Sub GetMaisu() Dim maisu(8) As Integer '枚数格納用の配列変数 Dim Kinshu() As Variant '金種用の配列変数 Kinshu = Array(10000, 5000, 1000, 500, 100, 50, 10, 5, 1) Dim Target As Currency Target = Range("B2") '任意の金額を代入 Dim i As Integer For i = 0 To 8 '金額を金種で割った商(枚数)を配列変数に格納 maisu(i) = Int(Target / Kinshu(i)) '余りをTargetに代入し直す Target = Target Mod Kinshu(i) Next For i = 0 To 8 Range("B" & i + 5) = maisu(i) Next End Sub
以上のように修正することで以下の画像のように表示されるようになります。B2セルに適当な金額を入力して「計算」のボタンを押すと、B5からB13までのセルに必要な枚数が表示されるようになります。
プログラムの計算結果が実際に表示されるのは、B5からB13までのセルだけです。それ以外の文字列や表の部分は自分がシート上で手入力しています。
もう少し補足すると、確認用のためにC5セルには計算式として「=A5*B5」を入力することで、元になる金種とプログラムで自動で入力される金種の枚数を掛けた数値が表示されるようにしました。
そのような計算式をC5セルからC13セルまでオートフィルで入力し、最後のC14セルはSUM関数でC5セルからC13セルまでの数値の合計が表示されるようになっています。
最後に、B2セルの入力金額とC14セルの合計金額が一致していれば金種の枚数が正しく計算された、ということがわかるようになっています。この表をつくるまでには次のような設定をしています。
Excelの画面の「開発タブ」→コードグループの「Visual Basic」を選択後、VBEの画面が表示されるのでプロジェクトエクスプローラを右クリックして、「標準モジュール」の挿入を選択。挿入された「標準モジュール」に上記のコードをコピーして貼付。
その後、Excelの画面の「開発タブ」→コントロールグループの「挿入」→「フォームコントロール」から「ボタン」を選択してシート上で任意の大きさに範囲設定した後、ボタンを右クリックして「マクロの登録」を選択。
表示されたダイアログボックスから先ほど上記のコードをコピペした「標準モジュール」を選択して「OK」をクリック。これによって先ほどの画像のように自動で金種の計算、それとプラスして自動で表示された金種から計算された合計金額と入力金が正しいかどうかを確認できる表ができます。
まとめ
今回はとあるきっかけ、気まぐれから、引用したプログラムを利用して以上のような金種を自動計算できるプログラムに手直ししてみました。
ここまで書いたような知識を自分が経理の仕事を始めた頃に知っていれば、仕事がいろいろと捗っただろうなぁと思います。というのも、経理という職種の仕事は非常にミスに煩くて、自分はこの点でかなり指摘されたからです。
当時の自分は、今の自分のようにミス防止のためには「システムやプログラムに働いてもらえばいい」という発想が無く、「とにかく集中して見る」ぐらいしかできませんでした。
経理に限らず、今回の金種の自動計算のプログラムに限らず、発想の転換というか、目視とかマンパワーに頼らずに「システムやプログラムに働いてもらう」ために、多くの人がプログラミングを覚えればいろいろと仕事は楽になるんじゃないかと思います。
コメント