VBAでSUM関数を使い合計金額を計算する。金種表作成

kinsyusaku1eyecatch

金種表作成ソフトのコード組み立てです。今回はSUM関数を使って、金種数から合計金額を計算し、さらにコード実行ボタンを設置します。「設定」シートでの作業となります。

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

今回からは、「金種表作成ソフト」の作成のためのVBAコードの組み立てを行っていきます。

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

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

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

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

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

の設置作業を行います。

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

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

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

これで、下準備OKです。

今回の金種表作成作業はSUM関数を使う

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

ことについて、SUM関数を利用して作成していきます。

  • 元の数量や金額の入力欄を作成します。
  • 入力値をリセットするボタンを設置
  • ①、②を計算実行するボタンを設置
  • 金種の設定で、２０００円札を使うかどうかを選択できるようにする
  • 計算結果のエクセルシート表示だけでなく、個別に結果をプリントアウトできるようにする
  • 計算実行の日時日付を残せるように、結果のプリントアウトの時に印刷表示されるようにする

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

目次へ戻る

金種表作成ソフトの完成形「設定」シート

kinsyugoukeip032

今回の作業で、このように「設定」シートを作っていきます。

kinsyu011ab
目次へ戻る

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

kinsyugoukeip033

エクセルVBAの立ち上げに少々不安がある方は、こちらを参考にしてください。

vbavbekidoeyecatch VBA初めての起動。VBEの立ち上げ、保存と終了

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

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

kinsyu001a
矢印下001

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

kinsyu002a

準備は完了です。

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

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

VBAコードを組み立てるについて、まず最初に表示される（作業する）シート上のデータをクリアしセルサイズをデフォルトの状態に戻すところから始めます。

入力値をリセットする

vbacleareyecatch シートクリアーを目的のメソッド別にVBA最速理解

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

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

VBA
Option Explicit

Sub 設定CL()
        Worksheets("設定").Select
            With Cells
                .ClearFormats
                .ClearContents
                .UseStandardHeight = True
                .UseStandardWidth = True
            End With
End Sub

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

vbacalleyecatch 部品化プロシージャーでCallステートメントは必須 vbawithstateeyecatch With～End Withの使い方。VBAコードを簡潔に記述する vbacellsfonteyecatch 「フォント」の操作を最速理解する エクセルVBA

プロシージャー名を「設定作表」とします。Module1に続けて記述します。

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

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

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

VBA
Sub 設定作表()
        Call Module1.設定CL
        With Worksheets("設定")
            .Rows.RowHeight = 21
            .Columns.ColumnWidth = 18
            .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 With
End Sub

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

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

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

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

fornextirekoeyecatch For～Nextのループと入れ子構造をVBA最速理解 vbasumeyecatch SUM関数で合計計算！実務で使えるVBAコード作成 vbacellssyoshikieyecatch 「表示形式」をVBAコード的に最速理解する
VBA
Sub 金種合計()
    Dim i As Long
        Worksheets("設定").Select
        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

動作ボタンを設置します

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

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

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

kinsyu005a
矢印下001

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

kinsyu006a
矢印下001

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

kinsyu007a
矢印下001

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

kinsyu008a
矢印下001

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

kinsyu010a

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

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

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

２０００円チェックボックスの設置

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

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

VBAコードの記述は、Sheet1（設定）のシートイベントとして行います。（シートモジュールに記述）

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

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

B1セルに文字が入力されても、チェックボックスの下敷きになっているので、前面表示されません。

というコードとします。

VBA
Option Explicit
Private Sub CheckBox1_Change()
        If CheckBox1.Value = True Then
            Range("B1").Value = "〇"
        ElseIf CheckBox1.Value = False Then
            Range("B1").Value = ""
        End If
        Range("B1").HorizontalAlignment = xlCenter
End Sub
目次へ戻る

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

kinsyugoukeip034

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

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

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

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

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

次回からは、

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

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

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

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

目次へ戻る

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min

前の記事

kinsyuhyoueyecatch
金種表作成ソフトの使い方と無料ダウンロード

次の記事

合計金額から各金種数量を自動算出。金種数量表で表示
kinsyusaku2eyecatch