Excel VBAを使って簡単な商品管理表を作成する

IT
スポンサーリンク

今回は英語系の記事ではなく、初めてITに関する記事を紹介していきたいと思います。

今回は最近業務でも多用するExcelのVBAについて書いていきます。

皆さんも日々の業務でExcelを使われる方は多いかと思いますが、マクロやVBAに関してはちらほらといった所でしょうか。

マクロやVBAを使いこなすことができればめんどくさい業務をボタン一つで一瞬で終わらせることができるので、ぜひとも身に着けるべきスキルであるといえます。

簡単な商品管理表を例にとって説明していきたいと思います。

まずはマクロとVBAの概要を簡単に説明していこうと思います。

スポンサーリンク

Excel マクロとは

マクロはExcelにおいて、複数の手順を記憶して、それをボタン一つで自動的に実行させる機能です。

スポンサーリンク

VBAとは

ExcelなどのOffice製品でマクロを開発するために使うプログラミング言語のことです。

またマクロはこのVBAを使用して作成されたプログラムの事を指します。

つまりマクロ≒VBAという認識で間違いないかと思います。

VBAのプログラムによってマクロが動作するということを頭の中に入れておいて頂ければ大丈夫です。

今回作成する表はFor文とIf文を使用しますので、その二つの基礎知識がある前提で説明をしていきます。

スポンサーリンク

管理表の作成


今回作成するものとしては、上のような表に各項目があって、
「値段」「個数」を入力したら「合計計算」ボタンを押すことによって「合計金額」が表示できるような簡単なものです。

「合計計算」ボタンを押すと…

「値段」と「個数」をかけた金額が「合計金額」に表示されました。

また、「削除」ボタンを押すと、「合計金額」と「備考」欄が空欄になります。

ひとまずやりたいことをまとめると、

  • 「値段」と「個数」を掛け合わせて、「合計金額」を表示させる
  • 「合計金額」が10,000円以上であれば「合計金額」を赤字表示させて「備考」に※をつける
  • 「削除」ボタンで「合計金額」と「備考」を空欄にする

事前準備として、簡単で良いのでExcelで上記の表を作っておいて頂きたいと思います。

必要な項目は、管理番号、名前、値段、個数、合計金額、備考の6つで、冒頭に書いたら、枠で囲むだけで大丈夫です。

合計金額を求める

まずは、「合計金額」を計算するプログラムを書いていきたいと思います。

以下のように「合計計算」ボタンを押したら、金額を計算するようなプロシージャを用意します。

————————————————————–

Sub Total()

End

————————————————————–

ご覧の通り、C列が「値段」、D列が「個数」、E列が「合計金額」の欄となっています。

これらの列は固定されているので、行を変化させて計算をしていけば、答えを導くことができます。

また最終行を求めるコードをここで用意しておきます。

Lastrow=cells(Rows.count,1).End(Xlup).Row

現時点でコードは以下のようになっています。

————————————————————–

Sub Total()

Lastrow=cells(Rows.count,1).End(Xlup).Row

End

————————————————————–

左辺の変数(=Lastrow)は自分の好きな変数を作ってもらって構いませんが、右辺の式は、Excel VBAにおいて最終行が行番号何番に該当するかをもとめるコードになりますので、このまま暗記しておいた方が良いです。

cells(Rows.count,1).End(Xlup).Row

ここで使用するのがFor文です。
count変数iを用意して、

————————————————————–

Sub Total()

Lastrow=cells(Rows.count,1).End(Xlup).Row

for i= 1 to Lastrow

cells(i,5)=cells(i,3)*cells(i,4)

Next

End

————————————————————–

iは行を表していて、数値がある2行目から最終行(ここでは4行目=Lastrow)までの間を、ループしてfor文の式を実行します。

(行,列)のように表すので覚えておきましょう。

例えば、Cells(2,1)であればA2セル、Cells(4,4)であればD4セルになります。

よってこちらの式では(2〰4,3)のセルと(2〰4,4)のセル同士を順番に計算して、(2〰4,5)に掛け合わせた答えが入るようになっています。

これで2行目から最後の行までの「値段」と「個数」を掛け合わせて、「合計金額」を求めることができます。

このプロシージャを実行すると、(合計計算ボタンを押します。)

「合計金額」に「値段」と「個数」を掛け合わせた数値が表示されました。

ここまでのコードは以下のようになっています。

————————————————————–

Sub Total()

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lastrow

Cells(i, 5) = Cells(i, 3) * Cells(i, 4)

Next

End Sub

————————————————————–

「合計金額」の大きさで字の色を変える、※印をつける

先ほど作成した、「Total」プロシージャを利用していきます。

「合計金額」が10,000円以上、未満で字の色が変わるようなVBAを組んでいきたいと思います。

これはFor文を回している中でプログラムを組んであげる必要があり、1行目からLastRow行目までの各一行ずつ判定を行っていきます。

これはIf文を用いて値の判定を行います。

「合計金額」が10,000円以上であれば、赤字表示なおかつ「備考」欄に※印をつける。

————————————————————–

If Cells(i, 5) >= 10000 Then

Cells(i, 5).Font.ColorIndex = 3
Cells(i, 6) = “※”

————————————————————–

Font.ColorIndexメソッドはセルの中の文字の色を変えることができるメソッドです。

色によって指定する数字は異なりますが、赤色の場合は「3」なのでそれを指定します。

「合計金額」が10,000円未満であれば、黒字表示のまま

————————————————————–

ElseIf Cells(i, 5) < 10000 Then

Cells(i, 5).Font.ColorIndex = 1
Cells(i, 6).ClearContents

End If

————————————————————–

先ほどと同じようにセルにFont.ColorIndexメソッドを指定します。(黒の場合は「1」)

そして10,000円未満の場合は「備考」欄に何も入れないので、セルにClearContentsメソッドを指定します。

今作成したIf文とElseIf文も先ほどから使用しているコードに追加してみましょう。

ここまでで以下のコードになっているかと思います。

————————————————————–

Sub Total()

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lastrow
Cells(i, 5) = Cells(i, 3) * Cells(i, 4)

  If Cells(i, 5) >= 10000 Then

   Cells(i, 5).Font.ColorIndex = 3
   Cells(i, 6) = “※”

  ElseIf Cells(i, 5) < 10000 Then

   Cells(i, 5).Font.ColorIndex = 1
   Cells(i, 6).ClearContents

  End If

Next

End Sub

————————————————————–

赤色部分が今追加したIf文とElseIf文です。

この場合、For文の中に書かないと想定通りに動かないので注意してください。

再度このプロシージャを実行させると、

この通り、想定通りに動きました。

(削除機能はこれから作るのですが、フライングで先に作ってあるときの画像になっています…m(__)m)

「削除」ボタンを加える

次は削除機能です。「合計金額」と「備考」を削除させていきたいと思います。

さきほどはTotalプロシージャを使用していましたが、今回は新たに削除用のプロシージャを作ります。

————————————————————–

Sub Del()

End Sub

————————————————————–

手順としてはTotalプロシージャと似たところがあり、

  • 最終行の設定
    Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
  • For文の使用、そしてその中でのIf文使用

です。

先ほどのように2番目の行からLastRow(最終)行までFor文を動かすプログラムを書きます。

————————————————————–

Sub Del()

For i = 2 To Lastrow

Next

End Sub

————————————————————–

このFor文の中にIf文を書いていきます。

条件としては、「合計金額」もしくは「備考」が空白じゃなければ、値を消去させていくプログラムになります。

————————————————————–

Sub Del()

For i = 2 To Lastrow

If Cells(i, 5) <> “” Or Cells(i, 6) <> “” Then

Cells(i, 5).ClearContents
Cells(i, 6).ClearContents

End If

Next

End Sub

————————————————————–

<>はVBAにおいて!=や≠すなわち右辺と左辺が等しくないことを意味します。

また””は空白を意味します。

「合計金額」もしくは「備考」のどちらかが空白でなければ、(値が入っていれば)入っている値を消すプログラムになります。

値は先ほど使用したClearContentsメソッドで消去します。

Delプロシージャを「削除」ボタンに挿入して実行すると、

 

 

「合計金額」と「備考」に入っていた値が消えました。

Delプロシージャは以下のようになっています。

————————————————————–

Sub Del()

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lastrow

  If Cells(i, 5) <> “” Or Cells(i, 6) <> “” Then

    Cells(i, 5).ClearContents
    Cells(i, 6).ClearContents

  End If

Next

End Sub

————————————————————–

先ほどの合計ボタンのプログラムと合わせると、完成形は以下のようになります。

————————————————————–
//合計金額を求める
Sub Total()

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lastrow

Cells(i, 5) = Cells(i, 3) * Cells(i, 4)

  If Cells(i, 5) >= 10000 Then

    Cells(i, 5).Font.ColorIndex = 3
    Cells(i, 6) = “※”

  ElseIf Cells(i, 5) < 10000 Then

    Cells(i, 5).Font.ColorIndex = 1
    Cells(i, 6).ClearContents

  End If

Next

End Sub

//合計金額と備考を削除する
Sub Del()

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lastrow

  If Cells(i, 5) <> “” Or Cells(i, 6) <> “” Then

    Cells(i, 5).ClearContents
    Cells(i, 6).ClearContents

  End If

Next

End Sub

————————————————————–

これで商品の金額と個数をかけて合計金額を求めるプログラムと、合計金額と備考の値を消すプログラムが完成しました。

基本構文を抑える

今回は

  • 「合計金額」を計算する
  • 値を削除する
  • 合計金額に応じて表示を変える

プログラムを書いてきました。

いずれも基本的なFor文やIf文で作成が可能ですので、まずは文法をしっかり覚え、どういったロジックを組めば想定通りの動きをするかを考えていくのがいいかと思います。

この表を使って他の機能も実装していく予定なので、楽しくVBAを覚えていきましょう。

IT
スポンサーリンク
Yumaをフォローする
Yuma

愛知県在住の29歳会社員。

同志社大学卒業後、約3年間のメーカーでの営業職としての勤務経験を経て、フィリピン・セブ島へ3ヵ月プログラミング×英語留学。

帰国後、愛知のIT企業でWEBプログラマーとして勤務開始。

保有資格:TOEIC865点、日商簿記検定2級

Yumaをフォローする
Daybreak

コメント