エクセルVBA SUM関数を利用。金種数から合計金額を計算

kinsyusaku1eyecatch

こんにちは、じゅんぱ店長(@junpa33)です。

 

今回からは、「金種表作成エクセル完全版」の作成のためのVBAコードの組み立てを行っていきます。

このソフトの中で使うエクセルシートは「設定」・「金種数量」・「印刷用」という名称にしていますが、

この記事では、最初に、「設定」シートの部分を説明します。

今回のキーポイントは「SUM」関数を使ってのVBAコード組み立てです。

 

この「設定」シートでは、

  1. 「金種数から合計金額を表示する」VBAの組み立てと
  2. 「合計金額から金種枚数を計算する」ための「相手先とその金額」データ入力欄
  3. 「各計算を実行するコントロールボタン」の設置

の設置作業を行います。

 

本題に入る前に、エクセルBOOKの準備をしておきます。

新しくエクセルを起動してください。

そのエクセルBOOKを「金種表作成.xlsm」として一度保存をしてください。

これで、下準備OKです。

 

本題に入るその前に、あらためて先回の記事をチェックするにはこちら↓になります。

無料DL金種表作成エクセルソフト完全版!金額と枚数計算。かんたん取説

 

金種表作成関係の記事はこちらです。

「金種表作成」の記事一覧を開く

 

先ず全体としての金種表エクセルの形です

 

  1. 金額から金種数を算出する
  2. 金種数から合計金額を計算する

ことについて、

元の数量や金額の入力欄を作成します。

 

入力値をリセットするボタンを設置

 

1、2を計算実行するボタンを設置

 

金種の設定で、2000円札を使うかどうかを選択できるようにする

 

計算結果のエクセルシート表示だけでなく、個別に結果をプリントアウトできるようにする

 

計算実行の日時日付を残せるように、結果のプリントアウトの時に印刷表示されるようにする

のようなことが出来るように組み立てて行きます。

 

今回作成する金種表エクセルの「設定」シートは

 

kinsyu011ab

 

SUM関数を使ったエクセルVBA組み立て

 

エクセルVBAの操作で最初に行う事については、

こちらの「エクセルVBA 始めての起動。VBEの立ち上げ、保存と終了」が、参考になります。

「設定」シートの準備作業

 

「Sheet1」のシート名を「設定」に変更します。

kinsyu001a

「開発」タブ→「Visual Basic」より「標準Module」を挿入します。

kinsyu002a

準備は完了です。

 

「金種数から合計金額を表示する」入力欄を作ります

 

入力値をリセットして各セルサイズ、文字サイズを調整し項目を表示します。

 

VBAコードを組み立てるについて、まず、

流れとしては、まず最初に表示される(作業する)シート上のデータをクリアしセルサイズをデフォルトの状態に戻すところから始めます。

 

入力値をリセットするコード

各セルのフォーマットと値、セルサイズをデフォルトに戻します。

プロシージャー名を「設定CL」とします。Module1に記述します。

Sub 設定CL()

 

Worksheets(“設定”).Select
  With Cells
    .ClearFormats
    .ClearContents
    .UseStandardHeight = True
    .UseStandardWidth = True
  End With

End Sub

 

項目を表示しセルサイズフォントサイズとカラーをセットするコード

プロシージャー名を「設定作表」とします。

最初に先ほどの「設定CL」プロシージャーを呼び出します。

金種の数量入力欄を設置していきます。

フォントサイズやカラーは適宜お好みで設定してください。

Sub 設定作表()

 

設定CL

 

Worksheets(“設定”).Select
  With ActiveSheet
    .Rows.RowHeight = 21
    .Columns.ColumnWidth = 18
  End With
  Rows(1).RowHeight = 33
  Range(“A2”) = “相手先・項目”
  Range(“B2”) = “金額”
  Range(“E2”) = “金種”
  With Range(“F1”)
    .Value = “ここに項目を入力”
    .Font.Size = 12
    .Font.ColorIndex = 15
  End With
  Range(“F2”) = “数量”
  Range(“G2”) = “金額”
  Range(“E3”) = “10000”
  Range(“E4”) = “5000”
  Range(“E5”) = “2000”
  Range(“E6”) = “1000”
  Range(“E7”) = “500”
  Range(“E8”) = “100”
  Range(“E9”) = “50”
  Range(“E10”) = “10”
  Range(“E11”) = “5”
  Range(“E12”) = “1”
  Range(“E13”) = “合計”

  With Range(“A2”, “G2”)
    .Font.Size = 15
    .Font.ColorIndex = 5
    .Font.Bold = True
  End With

  With Range(“E3”, “E13”)
    .Font.Size = 15
    .Font.ColorIndex = 5
    .Font.Bold = True
  End With

 

End Sub

 

金種数量からSUM関数で合計の計算

 

各金種ごとに入力した数量から総合計までを計算するコードです。

合計計算は、今回「SUM関数」を使って計算します。

「WorksheetFunction.Sum」というように記述します。カッコ内は計算範囲になります。

Sub 金種合計()

 

For i = 3 To 12
  Cells(i, 7) = Cells(i, 5) * Cells(i, 6)
Next i
  Cells(13, 6) = WorksheetFunction.Sum(Range(Cells(3, 6), Cells(12, 6)))
  Cells(13, 7) = WorksheetFunction.Sum(Range(Cells(3, 7), Cells(12, 7)))

With Range(“F3”, “G13”)
  .Font.Size = 15
  .Font.Bold = True
End With
Range(“G3:G13”).NumberFormatLocal = “\#,##0;\-#,##0”

End Sub

 

Module1に記述したコードの確認

 

Module1に記述したコードはこのようになります。

kinsyu009_1a

 

動作ボタンを設置します

 

「金種・数量表示」、「2000円札利用チェックボックス」、「リセットスタート」、「金種・合計表示」、「金種金額計算印刷」の動作ボタンを設置していきます。

動作ボタンは今までに何回か設置していますが、今回も同様になります。

 

「開発」タブ→「挿入」より「フォームコントロール」の「ボタン」をクリックし設置します。

kinsyu005a

 

「ボタン」のテキストを「金種・数量表示」としてフォントの変更は「コントロールの書式設定」から行います。

kinsyu006a

 

同様に順番に、各ボタンを設置してください。

kinsyu007a

 

動作ボタンとプロシージャーの紐づけは、「マクロの登録」から行うことが出来ます。

kinsyu008a

 

マクロ名欄からプロシージャーを選択します。

kinsyu010a

 

ここまでの作成したプロシージャーの紐づけは、

「始めにここをクリックします」には「設定作表」プロシージャーを

「金種・合計表示」には「金種合計」プロシージャーを選択します。

 

2000円チェックボックスを設置します

 

ボタンの設置と同じ方法で、チェックボックスを設置します。B1セルに設置してください。

チェックしたときの動作でイベントを起こすようにします。

VBAコードの記述は、Sheet1(設定)のシートイベントとして行います。

チェックボックスに変化があったとき、それが「チェックされた」のであれば、

B1セルに「〇」を記述します。

というコードとします。

kinsyu024a

 

SUM関数を使ったVBAコード作成のまとめ

 

Module1に記述した中の設定シートでの作成はこれまでになります。

合計計算も「SUM」関数を使えば楽チンにコード化実行できます。

ループ計算で合計を出していくことも出来ますが、

SUM関数を利用した方が遥かに明確で簡単です。

エクセル関数でもVBAでも、SUM関数はベーシックなスタンダードな関数になっています。

 

次回からは、

Module2で「金種・数量表示」ボタンに対応したVBAコードと

プリントアウトできるようにするVBAコードを組み立てて行きます。

 

今回の記事はここまでとなります。

 

次の記事に進むのはこちら↓になります。

金種表作成エクセルVBAの作り方②自動計算の金種数量表が作れる

 

エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておきたいテーマを纏めてみました。

「VBA最速理解」の記事一覧を開く

 

最後までお読みいただきありがとうございました。

 

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。