消費税率計算に役立つマクロ

テーマ：価格の率計算をするマクロの作り方



◆リクエスト紹介

今回は、下記のリクエストをご紹介します。


投稿者：2Suさん

　> マクロ初心者なのですが、教えて下さい。
　> 価格が入力された表があり、率計算で入力されている価格を
　> 変動したのですが・・・・・
　> ひとつづつ関数を埋め込むのが大変で、マクロ処理できないか
　> 教えて下さい。


2Suさん、リクエストありがとうございます。
このリクエストのイメージを文字にて表現すると以下のような感じです。

　－－－－－－－－－－－－－
｜会社名｜ 商品１｜商品２ ｜
　－－－－－－－－－－－－－
｜Ａ社　｜ 5,500 ｜10,000 ｜
　－－－－－－－－－－－－－　・・・
｜Ｂ社　｜ 7,000 ｜12,000 ｜
　－－－－－－－－－－－－－
｜Ｃ社　｜ 9,300 ｜13,800 ｜
　－－－－－－－－－－－－－
　　　　　　　　・
　　　　　　　　・
　　　　　　　　・

　　　　　　　　↓　　up率　105%

　－－－－－－－－－－－－－
｜会社名｜ 商品１｜商品２ ｜
　－－－－－－－－－－－－－
｜Ａ社　｜ 5,775 ｜10,500 ｜
　－－－－－－－－－－－－－
｜Ｂ社　｜ 7,350 ｜12,600 ｜
　－－－－－－－－－－－－－
｜Ｃ社　｜ 9,765 ｜14,490 ｜
　－－－－－－－－－－－－－




◆今日の講座


今回のリクエストは、特に小売業の方などで消費税の計算とか、3割引きの70%
の計算とかには役立ちそうな内容なので今回ご紹介していきます。これは、
消費税率の変更時の価格計算にも役立つと思います。



それでは、前回作成したエクセルマクロ"gogo102.xls"をご用意ください。



1)
まずはじめに、プログラムの中身の確認です。
――――――――――――――――――――――――――――＋
1."gogo102.xls"を開きます。（セキュリティ確認画面が表示された
　場合には、[マクロを有効にする]を選んでください。）

2.プログラムの画面を表示します。
　[ツール(T)] → [マクロ(M)] → [Visual Basic Editor(V)]

3.この画面の左上半分の[－標準モジュール]という所の
　[Module1]の方をダブルクリックしてください。
――――――――――――――――――――――――――――＋


＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―
Sub Macro1()
'
　　Call Macro4
　　b = InputBox("検索したい商品名を入力してください。")
　　k = 0
　　For j = 2 To 2
　　　　n = Cells(Rows.Count, j).End(xlUp).Row
　　　　For i = 1 To n
　　　　　　Cells(i, j).Select
　　　　　　a = ActiveCell.Value
　　　　　　If a Like "*" & b & "*" Then
　　　　　　　　Call Macro2
　　　　　　　　k = k + 1
　　　　　　End If
　　　　Next i
　　Next j
　　Range("E2").Select
　　MsgBox k
End Sub
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―


これは、前々回( Lesson 101 で)作成した「検索結果の数をカウントするマク
ロ」のプログラムです。

ちょっとおさらいですが、
このマクロは、シートのある列(B列)を検索してその結果のセルを黄色く塗り
つぶし、かつ、その結果の数をカウントしてメッセージボックスへ表示する。
といったプログラムでした。

今回は、このプログラムを少々修正していくことで（再利用して）、このリク
エストのマクロをより簡単に完成させていくことにします。



2)
それでは、まず、プログラムの主となる部分についての修正をします。
――――――――――――――――――――――――――――＋
1.このプログラムの最初のFor文の
　 For j = 2 To 2
　という行を、下記に書き変えます。
　 For j = 2 To 3

2.次に、2番目のFor文の
　 For i = 1 To n
　という行を、下記に書き変えます。
　 For i = 2 To n

3.次に、このプログラム中ほどのIf文の
　 If a Like "*" & b & "*" Then
　という行を、下記に書き変えます。
　 If a <> "" Then

4.最後に、このプログラム中ほどのIf文の
　 Call Macro2
　という行を、下記に書き変えます。
　 ActiveCell.FormulaR1C1 = a * b / 100
――――――――――――――――――――――――――――＋


修正した後のプログラムは下記の様になります。


＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―
Sub Macro1()
'
　　Call Macro4
　　b = InputBox("検索したい商品名を入力してください。")
　　k = 0
　　For j = 2 To 3
　　　　n = Cells(Rows.Count, j).End(xlUp).Row
　　　　For i = 2 To n
　　　　　　Cells(i, j).Select
　　　　　　a = ActiveCell.Value
　　　　　　If a <> "" Then
　　　　　　　　ActiveCell.FormulaR1C1 = a * b / 100
　　　　　　　　k = k + 1
　　　　　　End If
　　　　Next i
　　Next j
　　Range("E2").Select
　　MsgBox k
End Sub
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―


このプログラムは2重のループ構造となっているものですが、最初の(外側の)
ループのFor文で処理をする列の範囲を列番号（A列が1番目、B列が2番目、C列
が3番目、といった具合）の数字で指定しています。
次の(内側の)ループのFor文は、処理をする行の範囲を指定しています。

そこで、いま行なった修正というのは単に、横がB列～C列まで、縦が2行目～
n行目まで( n はその列の最後の行)の範囲を指定したということになります。

なので、この２つのFor文の数字の部分をちょっといじってやれば、どんな
セルの範囲でも簡単に指定することができます。


3.は、金額がゼロだった場合に、（ 0 が入力させていれば問題はないのです
が）空欄となっていた場合の対処です。
4.は、元の金額に率をかけて（率計算なので100で割っている）、元のセルに
書き出しています。


ここまでできたら、あとは要らない部分を削除するなど、ちょっとプログラム
を整理するだけでもう完成です。



3)
では、プログラムの要らない部分を削除して整理していきます。
――――――――――――――――――――――――――――＋
1.まず、プログラム3行目の
　 k = 0
　という1行を削除します。

2.続けて、プログラム中ほどの
　 k = k + 1
　という1行を削除します。

3.さらに、プログラム最後の
　 MsgBox k
　という1行を削除します。

4.ついでに、プログラム最初の
　 Call Macro4
　というのも要らないので削除します。

5.最後に、プログラム2行目の
　 b = InputBox("検索したい商品名を入力してください。")
　という行を、下記に書き変えます。
　 b = InputBox("UP率(%)を入力してください。")
――――――――――――――――――――――――――――＋


修正した後のプログラムは下記の様になります。


＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―
Sub Macro1()
'
　　b = InputBox("UP率(%)を入力してください。")
　　For j = 2 To 3
　　　　n = Cells(Rows.Count, j).End(xlUp).Row
　　　　For i = 2 To n
　　　　　　Cells(i, j).Select
　　　　　　a = ActiveCell.Value
　　　　　　If a <> "" Then
　　　　　　　　ActiveCell.FormulaR1C1 = a * b / 100
　　　　　　End If
　　　　Next i
　　Next j
　　Range("E2").Select
End Sub
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―


いま削除した部分というのは、今回の率計算マクロではカウントする必要とい
うのはありませんので、再利用したプログラム「カウントするマクロ」のカウ
ントの処理に該当部分は全部消してしまってよいわけですので、そのカウント
で用いている変数n 使っているプログラムの部分は単純に削除していけばよい
わけです。

何かのプログラムを元に再利用してプログラムを作るという場合、こうした要
らない部分の見分け方は、このように変数に着目して不要な変数が含まれる部
分を削除していく、というのが簡単に見分ける一つのコツです。



4)
それでは、実行してみましょう。
――――――――――――――――――――――――――――＋
1.エクセルの画面に戻って、

2.まずは、テスト用のデータを入力していきます。
　以下のテキスト（5行）をコピーして、[Sheet1]のセルA1の位置に
　そのまま貼り付けてください。

社名
Ａ社
Ｂ社
Ｃ社
Ｄ社


3.同様に、
　以下のテキスト（5行）をコピーして、今度は隣のセルB1の位置に
　そのまま貼り付けてください。

商品１
5,500
7,000
9,300
11,300


4.続けて同様に、
　以下のテキスト（5行）をコピーして、その隣のセルC1の位置に
　そのまま貼り付けてください。

商品２
10,000
12,000
13,800
17,300


5.では、実行します。
　[ツール(T)] → [マクロ(M)] → [マクロ(M)...] → マクロ画面が出るので
　そのまま（「Macro1」が選択された状態で）[実行]をクリック。

6.すると、入力窓のあるポップアップ画面が表示されたと思いますので、
　その入力窓に以下の数字

105

　と入力してください。

7.そうしたら、このポップアップ画面にある[ＯＫ]ボタンをクリックします。
――――――――――――――――――――――――――――＋

実行した結果、各金額が消費税分(5%)プラスされた金額に変わっていればＯＫ
ということになります。



今回はこれで終了です。
今日作ったマクロプログラムは次回もこの続きでまた使いますので、大切に
保管しておいてください。（ファイル名 "gogo103.xls" ）

ハイパーリンクを一発ですべて削除するマクロ

増刊号 Vol. 30 より


１つ１つのセルのハイパーリンクを解除するには
右ボタンでクリック→「ハイパーリンクの削除(R)」
で簡単に行なえるわけですが、

こと複数のセルとなると1度に解除することができません。
（上記の操作を1つ1つやるか、ちょっと面倒な裏技的な操作を
駆使する以外にありません。）


ちなみに、下記がマクロで
「ハイパーリンクを一発ですべて削除する」
方法のプログラムです。



＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―
Sub Macro1()
'
　　Cells.Select
　　Selection.Hyperlinks.Delete
End Sub
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―


至って短く、簡単なマクロですね。^^
興味のある方はぜひ試してみてください。


　▼ゼロから７日間でスタートする！マクロ講座▼
　エクセルマクロ(VBA)の『スターターズ・キッド』７日間メールセミナー
　只今、無料登録受付中です↑