初めてのExcel VBA
目次
9 プログラムの解説
Excelは事務作業にとって欠かすことのできないソフトウェアとなっておりますが、Excel上で動作するプログラム(VBA)を使っている人はほとんどいません。できるわけはないと初めからあきらめているのでしょうか。VBAを使うと、事務処理の生産性が圧倒的に高まります。プロにお金を払って作ってもらう方法もありますが、ちょっとした仕事が自分でも自動化できれば、小さな改善の積み重ねで大きな効果を生むことができます。
Excelでのプログラム開発は目に見えるデータを使った開発です。シート上のデータを処理し、Excelシートに出力しますので、何が行われているのか容易に判断でき、素人でも簡単にプログラムが開発できます。プログラムを勉強するには地道に一つ一つ命令を勉強していく方法もありますが、ここではある集計プログラムを解説し、なぜその集計ができるのかを勉強します。実践的なプログラムで勉強したほうが興味もわきますし、役にたちます。使えない命令をおぼえてもしょうがありません。動作する理由がわかれば、それを応用して、自分の仕事に必要になるプログラムを作ることができるようになります。
Excelシートに2つのデータがあります。売上データと価格表です。このデータから商品別月別売上表を作成します。
今回勉強するプログラムをダウンロードして実行してみましょう。
http://kanacom2.la.coocan.jp/vba/URIAGE.zip
月集計ボタンを押します。
集計結果が瞬時に表示されます。
あなたならどうやって集計するでしょうか。
Excelの関数や集計機能をつかいながら、手作業でも次の手順作成できます。
1)売上データに単価を表示する。
関数のVLOOKUPを使うと、価格表より単価を求めることができます。
2)単価と販売数を掛け売上金額を求める
3)月別に集計するために、月を関数のTEXTを使用して求める。
4)Excel集計機能のピボットテーブルを使い縦横集計する。
結果をコピーして値のみ貼付する。
5)商品名を求める
関数のVLOOKUPを使うと、価格表より商品名を求めることができます。
6)月別の合計を求める
以上の6手順で手作業で作成できます。
慣れてくれば早くできますが、毎月、毎日作成するとしたらこの作業はむだです。
Excel VBAでプログラムを作ってボタンを押すだけで集計完了しましょう。
5. プログラムの入力方法
自動集計するには、プログラムを入力して実行します。そもそもプログラムはどこに入力してどうやって実行するのでしょうか。簡単なプログラムを入力して実行してみましょう。
開発タブを押し、Visual Basic を押します。
(開発タブが表示されていないパソコンでは開発タブを表示させる設定が必要になります。最後に説明しております。
[挿入]、[標準モジュール]と操作すると、右側に入力できる場所ができます。
|
SUB テスト A=1 B=2 C=A+B RANGE(“A3”).VALUE=C END SUB
|
と入力してみましょう。
[実行][SUB/ユーザーフォームの実行][実行]と操作すると、入力したプログラムが実行され、
A3セルに 3 と記入されました。
作成したプログラムはこのブックに記憶されています。
ブックを保存すれば、プログラムも保存され、再度利用できます。
開発タブの表示方法
開発タブが表示されていない場合、次の操作をします。一回設定すれば常に表示されます。
[ファイル][オプション][リボンのユーザー設定]を押し、[開発]にチェックし、[ok]を押します。
今回の集計プログラムのリストは次のようになります。このプログラムでなぜ集計できるのかを説明していきます。このプログラムはずいぶん長いですが、自動的にプログラムを作成する方法がありますので心配いりません。その方法は別の場所で説明しますが、ここでは動作の仕組みを理解しましょう。
|
Sub 月集計() 単価合成 式コピー 縦横集計 型式合成 合計計算式出力 End Sub
Sub 単価合成() Dim INS, EN, SBK, I, KEYD, F Application.ScreenUpdating = False Set INS = Sheets("売上") '入力出力シート EN = INS.Range("$A65536").End(xlUp).Row If EN < 3 Then Exit Sub INS.Range("$E3:$E" & EN).ClearContents '出力データクリヤ Set SBK = Sheets("価格") '参照ブックシート For I = 3 To EN KEYD = INS.Range("$A" & I).Value Set F = SBK.Range("$A:$A").Find(What:=KEYD, LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=False) If F Is Nothing Then INS.Range("$E" & I).Value = "" '見つからない Else INS.Range("$E" & I).Value = SBK.Range("$C" & F.Row).Value End If Application.StatusBar = I & "＜--" & EN Next I Application.StatusBar = "" End Sub
Sub 式コピー() Dim BK, INS, ENDROW Application.ScreenUpdating = False Set BK = ActiveWorkbook Set INS = BK.Sheets("売上") '入力出力シート ENDROW = INS.Range("$A65536").End(xlUp).Row If ENDROW < 3 Then Exit Sub End If ra = Array("$F", "$G") '''標準書式への変更 For K = LBound(ra) To UBound(ra) INS.Range(ra(K) & "3:" & ra(K) & ENDROW).NumberFormatLocal = "G/標準" Next K INS.Range("$F3").Formula = "=B3*E3" INS.Range("$G3").Formula = "=TEXT(C3,""yyyymm"")" For K = LBound(ra) To UBound(ra) INS.Range(ra(K) & "3").Copy INS.Range(ra(K) & "3:" & ra(K) & ENDROW).PasteSpecial Paste:=xlFormulas Next K INS.Calculate ''計算実行 自動計算ＯＦＦの場合への対応 End Sub
Sub 縦横集計() Dim INS, OUS, BBK, ENDROW, ENC, ENL, EndRow3 '入力シート 表示行について 新規ブックを作成 ピボットテーブルで集計後、出力シートにコピー Application.ScreenUpdating = False Set INS = Sheets("売上") '入力シート Set OUS = Sheets("月売上") '入力出力シート OUS.Range("$A$4:$IV65536").ClearContents '出力シートデータクリヤ 縦出力の右下 OUS.Range("$C$3:$IV8").ClearContents '横項目データクリヤ ＩＶ列まで ENDROW = INS.Range("$A65536").End(xlUp).Row If ENDROW < 3 Then Exit Sub End If Workbooks.Add Set BBK = ActiveWorkbook 'データを新規ブックにコピー INS.Range("$A$3:$A" & ENDROW).SpecialCells(xlVisible).Copy '縦項目 Range("A2").PasteSpecial Paste:=xlValues '非表示列があるとき 上のSpecialCells(xlVisible)が使えない Range("A1").Value = "縦" INS.Range("$F$3:$F" & ENDROW).SpecialCells(xlVisible).Copy '数量項目 Range("B2").PasteSpecial Paste:=xlValues Range("B1").Value = "数量" INS.Range("$G$3:$G" & ENDROW).SpecialCells(xlVisible).Copy '横項目 Range("C2").PasteSpecial Paste:=xlValues Range("C1").Value = "横" 'ピボットテーブル EndRow3 = Range("$A65536").End(xlUp).Row Range("A1:C" & EndRow3).Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ Selection, TableDestination:="", TableName:= _ "ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙZ" ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙZ").AddFields RowFields:="縦", ColumnFields:="横" With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙZ").PivotFields("数量") .Orientation = xlDataField .Function = xlSum 'XLCOUNT 'で件数カウント End With ENC = Range("iv2").End(xlToLeft).Column - 1 '最終列 縦横集計 ENL = Range("A65536").End(xlUp).Row - 1 '最終行 'データ出力 Range(Range("A3"), Cells(ENL, 1)).Copy '縦項目コピー OUS.Range("$A$4").PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False '縦項目 値のみ貼り付け Range(Range("B2"), Cells(2, ENC)).Copy '横項目コピー OUS.Range("$C$3").PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range(Range("B3"), Cells(ENL, ENC)).Copy 'データコピー OUS.Range("$C$4").PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.DisplayAlerts = False BBK.Close 'ピボットテーブルブック 'Application.DisplayAlerts = ｔｒｕｅ End Sub
Sub 型式合成() Dim INS, EN, SBK, I, KEYD, F Application.ScreenUpdating = False Set INS = Sheets("月売上") '入力出力シート EN = INS.Range("$A65536").End(xlUp).Row If EN < 4 Then Exit Sub INS.Range("$B4:$B" & EN).ClearContents '出力データクリヤ Set SBK = Sheets("価格") '参照ブックシート For I = 4 To EN KEYD = INS.Range("$A" & I).Value Set F = SBK.Range("$A:$A").Find(What:=KEYD, LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=False) If F Is Nothing Then INS.Range("$B" & I).Value = "" '見つからない Else INS.Range("$B" & I).Value = SBK.Range("$B" & F.Row).Value End If Application.StatusBar = I & "＜--" & EN Next I Application.StatusBar = "" End Sub
Sub 合計計算式出力() Set INS = Sheets("月売上") '入力出力シート EN = INS.Range("$A65536").End(xlUp).Row '最終行の判断 列を修正する '修正方法 最終行9のとき 9-> " & EN &" 10->" & EN +1 &" '修正例 ”＝SUM($A3:$A" & EN &") "=F11/G11" -> "F"& EN+2 &"/G"& EN+2 INS.Range("$B" & EN + 2).Formula = "=""計""" INS.Range("$C" & EN + 2).Formula = "=SUM(C4:C" & EN & ")" INS.Range("$D" & EN + 2).Formula = "=SUM(D4:D" & EN & ")" INS.Range("$E" & EN + 2).Formula = "=SUM(E4:E" & EN & ")" End Sub
|
上のプログラムを見ると独特の単語のようなものが記載されています。この単語(Excelに対する命令)を集計して、重要なもの、そうでもないものに分けました。ざっと目を通してください。
いろんな種類がごちゃまぜになっておりますが、例文を見ていく中で自然に意味が分かってくると思います。
<表 1> 重要なもの
|
単語 |
頻度(回) |
意味 |
|
" |
8 |
文字のはじめと終わりに記入 |
|
& |
38 |
文字をつなぐ |
|
+ |
4 |
数字の足し算 |
|
< |
4 |
大きい |
|
= |
53 |
等しい 代入する |
|
ActiveSheet |
3 |
表示されているシート |
|
ActiveWorkbook |
2 |
表示されているブック |
|
Array |
1 |
配列にいれる |
|
Cells |
3 |
セル |
|
ClearContents |
4 |
消す |
|
Column |
1 |
列 |
|
Copy |
7 |
コピー |
|
Else |
2 |
他の場合 |
|
End |
19 |
終了 |
|
Exit |
4 |
終了 |
|
Find |
2 |
見つける |
|
For |
4 |
から |
|
Function |
1 |
関数 |
|
If |
10 |
もし |
|
LBound |
2 |
配列の最小値 |
|
LookAt:=xlWhole |
2 |
完全一致で見つける |
|
Next |
4 |
次 |
|
Paste:=xlValues |
6 |
値で貼り付け |
|
PasteSpecial |
7 |
貼り付け方法 |
|
Range |
50 |
セル |
|
Row |
9 |
行 |
|
ScreenUpdating |
4 |
画面の表示有無 |
|
Select |
1 |
条件判断 |
|
Selection |
1 |
選択しているもの |
|
Sheets |
8 |
シート |
|
StatusBar |
4 |
画面下の表示 |
|
Sub |
16 |
プログラムの開始 |
|
Then |
6 |
そうしたら |
|
To |
4 |
まで |
|
TRUE |
1 |
正しい |
|
UBound |
2 |
配列の最大値 |
|
Value |
11 |
値 |
|
What:= |
2 |
FINDで見つけるもの |
|
With |
2 |
～について |
|
Workbooks |
1 |
ブック |
|
xlToLeft |
1 |
左方向に |
|
xlUp |
7 |
上に上がる |
<表 2> 重要でないもの
|
単語 |
頻度(回) |
意味 |
|
_ |
7 |
次の行に命令をつなげる |
|
Add |
1 |
追加 |
|
AddFields |
1 |
追加 |
|
Application |
10 |
|
|
Calculate |
1 |
計算する |
|
Close |
1 |
閉じる |
|
ColumnFields:="横" |
1 |
|
|
Dim |
4 |
定義 |
|
DisplayAlerts |
2 |
アラームの表示有無 |
|
FALSE |
5 |
やめる 間違い |
|
Formula |
6 |
式の内容 |
|
Is |
2 |
等しい |
|
LookIn:=xlValues |
2 |
|
|
MatchCase:=False |
2 |
|
|
Nothing |
2 |
|
|
NumberFormatLocal |
1 |
|
|
Operation:=xlNone |
3 |
|
|
Orientation |
1 |
|
|
Paste:=xlFormulas |
1 |
式の貼り付け |
|
PivotFields |
1 |
|
|
PivotTables |
2 |
|
|
PivotTableWizard |
1 |
|
|
RowFields:="縦" |
1 |
|
|
Set |
12 |
変数に入れる |
|
SkipBlanks:=False |
3 |
|
|
SourceData:= |
1 |
|
|
SourceType:=xlDatabase |
1 |
|
|
SpecialCells |
3 |
|
|
TableDestination:="" |
1 |
|
|
TableName:= |
1 |
|
|
Transpose:=False |
3 |
|
|
xlDataField |
1 |
|
|
xlSum |
1 |
合計 |
|
xlVisible |
4 |
見える |
6つのプログラムから成り立っています。
SUB で始まるところがプログラムの開始する場所です。
END SUB でプログラムが終了します。
<表 3>
|
プログラム |
機能 |
|
Sub 月集計 |
集計を実施する |
|
Sub単価合成 |
価格シートより単価を求め売上シートに記入 |
|
Sub式コピー |
売上シートに売上金額、年月を記入 |
|
Sub 縦横集計 |
売上シートを商品別、年月別に縦横集計し、月売上シートに記入 |
|
Sub型式合成 |
価格シートより商品名を求め月売上シートに記入 |
|
Sub合計計算式出力 |
月売上シートに全合計を記入 |
それぞれのプログラムはある機能をもっています。このように機能ごとにプログラムを区切っておくと、動作テストや変更する場合に便利です。
順番にプログラムを説明していきます。
9 プログラムの解説
9-1月集計
|
Sub 月集計() 単価合成 式コピー 縦横集計 型式合成 合計計算式出力 End Sub |
5つのプログラム名が記入されています。
このプログラムを実行すると、上から順にプログラムが実行されていき、集計が完了します。
9-2 単価合成
実行すると売上シートに単価が表示されます。
売上シート
価格シート
1) 「単価合成」プログラムの概要
簡単に言えば、売上シートのA列の商品コードを価格シートのA列から探し、見つけた価格を売上シートのE列に記入します。
次の手順で単価を調べます。
①売上データが何行あるか調べる。データがなければ終了する。
②商品コードを上から1つ取る。
③その商品コードが価格シートA列のどこにあるかを調べる。
④その行の価格を売上データに記入する。
⑤ ②へ戻り最後の行まで繰り返す。
2)プログラム
最初から難しいプログラムになります。
|
01行 Sub 単価合成() 02行 Dim INS, EN, SBK, I, KEYD, F 03行 Application.ScreenUpdating = False 04行 Set INS = Sheets("売上") '入力出力シート 05行 EN = INS.Range("$A65536").End(xlUp).Row 06行 If EN < 3 Then Exit Sub 07行 INS.Range("$E3:$E" & EN).ClearContents '出力データクリヤ 08行 Set SBK = Sheets("価格") '参照ブックシート 09行 For I = 3 To EN 10行 KEYD = INS.Range("$A" & I).Value 11行 Set F = SBK.Range("$A:$A").Find(What:=KEYD, LookIn:=xlValues, _ 12行 LookAt:=xlWhole, MatchCase:=False) 13行 If F Is Nothing Then 14行 INS.Range("$E" & I).Value = "" '見つからない 15行 Else 16行 INS.Range("$E" & I).Value = SBK.Range("$C" & F.Row).Value 17行 End If 18行 Application.StatusBar = I & "＜--" & EN 19行 Next I 20行 Application.StatusBar = "" 21行 End SUB |
3)命令の説明
このプログラムには多数の命令がはいっています。プログラムの説明をする前に、命令の意味を理解するために、使用例を見ていきます。
<表 4>
|
命令など |
使用例 |
意味 |
|
Sub End SUB |
Sub プロ1 A=3 End Sub |
プログラム 「プロ1」の開始、終了 |
|
Dim |
Dim a,b,c |
変数 a b cを定義 (Dim文はなくても可) |
|
. |
〇.□ 下の例を参照ください |
〇の□ セルの色 など |
|
Application.ScreenUpdating = False |
Application.ScreenUpdating = False |
Excelの画面表示を止めます。(処理スピードが速くなります) |
|
= |
A=3 |
変数Aに数値3を入れます |
|
“ |
A=”ABC” |
変数Aに文字「ABC」を入れます |
|
‘ |
A=3 ‘ Aに3を入れます |
意味など コメントを記入 |
|
Value |
下の例を参照ください |
値 |
|
Range |
Range(“A3”).Value=3 B=Range(“A3”).Row C= Range(“A3”).Font.ColorIndex Range(“A3:C6”). Value=3 Range(“C:E”).ClearContents |
セルA3に3を入れます セルA3の行をBに入れます セルA3の文字の色をCに入れます セルA3:C6に3をいれます 列C～Eの値を消します |
|
Set |
Set B=Range(“A3”) B.Value=4 |
セルA3をBと呼びます セルA3に数値4を入れます |
|
Sheets |
Sheets(“入力”).Range(“A2”).Value=4 |
シート「入力」のセルA2に数値4を入れます |
|
Row |
C=Range(“A2”).Row |
セルA2の行をCに入れる |
|
End(xlUp) |
A=Range(“B65536”).End(xlUp).Row |
セルB65536から上にさかのぼって、初めて見つけたセルの行をAにいれる |
|
If Then |
IF A=3 Then C=4 |
もしAが3ならCに4を入れます |
|
< |
IF A<3 Then A=3 |
もしAが3より小さければ、Aに3を入れます |
|
Exit Sub |
Exit Sub |
プログラムを終了する |
|
& |
A= B & ”,”
I=4 Range(“A”&I).Value=A |
変数Bと文字 「,」をつなげて、Aに入れます Iに4を入れます セルA4に Aの値を入れます |
|
ClearContents |
Rang(“A3”). ClearContents |
セルA3の値を消します |
|
For To Next |
C=0 For I=1 to 10 C=C+I Next I |
1から10まで加算するプログラムです。 Cに0を入れます Iに1 を入れます CにIを加えます Iに1を加えます Iが10以下であれは Forの次の文に戻ります。
|
|
Find |
Set F = SBK.Range("$A:$A").Find(What:=KEYD, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) |
シートSBKのA列の中に、変数KEYDを探し、見つけたセルをFとします。見つからなければ FをNothig とします。 |
|
Is Nothing |
If A Is Nothig Then C=0 |
AがNothigなら Cに0を入れます |
|
Else End If |
If A>=100 Then C=1 Range(“A3”).Value=1 Else C=0 End If |
もし Aが100以上なら Cに1を入れ、 セルA3に1を入れます。 それ以外なら Cに0を入れます |
|
Application.StatusBar |
Application.StatusBar = "終了" |
ステータスバーに「終了」と表示する |
4)プログラムの説明
それでは、1行づつプログラムの説明をしていきます。
02行 Dim INS, EN, SBK, I, KEYD, F
使用する変数をあらかじめ宣言しているもので、特になくとも可能です。
変数とは 値を記憶しておくメモリのようなものです。
|
参考 変数とはどのようなものか 次のプログラムを見てください。 プログラムを実行すると 変数Cには何がはいっているでしょうか A=3 B=4 C=A+B 答え 7 |
03行 Application.ScreenUpdating = False
画面の更新を停止させるという意味です。処理中に画面が表示されない分処理が速くなります。
04行 Set INS = Sheets("売上") '入力出力シート
売上シートをINSと呼ぶことにするという意味です。
毎回 Sheets("売上") と表示するとプログラムが見にくくなるため省略してINSと書きます。インプットシートの略でINSとしてみました。INSの代わりに何と書いても可能です。
「‘」の後ろはコメントですので、プログラムの動きには関係ありません。
①売上データが何行あるか調べる。データがなければ終了する。
作成するプログラムは売上データの件数が何件であっても動作するよう作成します。このため、何件までデータがあるのかを調べます。
05行 EN = INS.Range("$A65536").End(xlUp).Row
INS.Range("$A65536") はSheets("売上")のセルA65536 を意味します。
65536行は Excelバージョン2003における最大の行数です。
INS.Range("$A65536").End(xlUp) は Sheets("売上")のセルA65536から上の行にさかのぼって、初めてデータが入っていたセルを意味します。つまりA列のデータがある最後のセルとなります。
この文は ○.◇.△ のように単語が 「.」で区切られています。○の◇の△のような意味になります。使うパターンは決まっていますので、丸ごと覚えてしまえば簡単です。
EN = INS.Range("$A65536").End(xlUp).Row は A列のデータがある最後のセルの行を変数ENに入れる意味です。「=」の意味は数学の「=」とは違い、変数に入れるという意味になります。
06行 If EN < 3 Then Exit Sub
もし 変数ENが3より小さかったら プログラムを終了する。
データは3行目から入力されています。3行より小さければデータがないことになります。
データがなければプログラムを実行する意味はありませんので終了させます。
IF ○ THEN △ で もし○なら △を実行するという意味になります。
EN<3 は ENが3より小さいか という意味です。
EXIT SUB はプログラムを終了することを意味します。
07行 INS.Range("$E3:$E" & EN).ClearContents '出力データクリヤ
これも ○.◇.△ の形をしています。売上シートのE3セルからE列最後の行までの範囲セルを消すという意味です。RANGE()は ()内にセルの場所を記入するとその範囲のセルを意味します。
|
参考 SHEETS(“入力”).RANGE(“C4”).VALUE=5 入力シートのセルC4に 5を記入することを意味します。 Sheets("入力").Range("A3:B6").Value = 1 入力シートのセルA3:B6の範囲にすべて 1を記入することを意味します。 VALUEは値 を意味します。色を意味する単語をVALUEの代わりに記入すると、セルに色を付けたりできます。
|
08行 Set SBK = Sheets("価格") '参照ブックシート
04行と同じく、価格シートをSBKと呼ぶことにするという意味です。
②へ戻り最後の行まで繰り返す。
価格を調べるのに、上から1行ずつ調べます。3行目から最後の行まで繰り返すことになります。このような場合、for next という繰り返しの命令を使用します。
09行 For I = 3 To EN
19行 Next I
09行と19行はセットで使用します。間の10行から18行のプログラムを繰り返し実行する意味になります。ここでは売上シートの3行目からE列のデータの最後の行まで繰り返し処理することを意味します。
For I = 3 To ENは 最初にIに3を入れる。IがENを超えたら繰り返しを終了するという意味になります。
NEXT Iは Iに1を加え、10行から実行することを意味します
ここからが重要です。図に示すと下記となります。
②商品コードを上から1つ取る。
10行 KEYD = INS.Range("$A" & I).Value
売上シートのA列I行目の値を変数KEYD入れる。(最初、Iには3がはいっています。)
INS.Range("$A" & I).Valueをプログラムに何回も記述するのはめんどうなので、KEYDと短縮化し、容易化します。KEYDには商品コードが入ります。
③その商品コードが価格シートA列のどこにあるかを調べる。
11行 Set F = SBK.Range("$A:$A").Find(What:=KEYD, LookIn:=xlValues, _
12行 LookAt:=xlWhole, MatchCase:=False)
単価を検索するもっとも重要な行になります。
11行の最後の 「_」は次の行に文が続いていることを意味します。「_」を取って、1行に記述しても可です。
Find(What:=KEYD, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
は 探す条件を記述しています。複雑ですがポイントは
What:=KEYD KEYDを探すという意味
LookAt:=xlWhole 完全に一致した物を探すという意味
のみ覚えればいいです。
SBK.Range("$A:$A"). は探す場所を意味します。価格シートのA列から探す。
Set F = は 探したセルを Fと呼ぶ 意味です。
以上をまとめると、11行目は価格シートのA列からのKEYDを探し、そのセルをFと呼ぶという意味になります。
13行 If F Is Nothing Then
14行
15行 Else
16行
17行 End If
この3行がセットになります。
F Is Nothingとは、探したセルがない=見つからなかった という意味ですので、もし価格表になければ14行を実行し、あれば、16行を実行する意味となります。
|
参考 1行に記述する方法もあります。 IF ○ THEN □ ELSE △ |
14行 INS.Range("$E" & I).Value = "" '見つからない
売上シートのE列I行に 空白を入れる。 “はその間に文字を記入します。””は何もない文字となります。もともとE列は初めに消去していますので、この文は記述しなくても可です。
④その行の価格を売上データに記入する。
16行 INS.Range("$E" & I).Value = SBK.Range("$C" & F.Row).Value
F.ROW は 価格表の見つけたセルの行 を意味します。全体の意味は、価格表の見つけた行の価格を売上シートのE列のI行目に記入する意味となります。
18行 Application.StatusBar = I & "＜--" & EN
何行目を処理しているのか画面の下のステータスバーに記入します。
I & "＜--" & EN が表示されます。「&」は 文字をつなぐ意味になります。
例 Iが5、 ENが8であれば、 「5 ＜--8」が表示されることになります。
このプログラムでは一瞬に処理完了しますのでほとんど表示されません。
20行 Application.StatusBar = ""
ステータスバーを消します。消さなければ最後の表示が残ります。
以上でこのプログラムの説明を終わります。
プログラムが理解できるというのは、
「どの順でプログラムが実行され、その時点で変数に何が入っているのか、頭の中でシミュレーションできる」
ということだと思います。
この後いろいろなプログラムがでてきますが、それほど多くの処理パターンがあるわけではありません。
がんばって勉強しましょう。
9-3 式コピー
実行すると売上シートに売上金額、年月が表示されます。
売上シート
1) プログラムの概要
次の手順で売上シートに売上金額、年月の計算式を記入します。
①売上データが何行あるか調べる。データがなければ終了する。
②式を記入する売上金額の列、年月の列をあらかじめ覚えておきます。
(繰り返し処理するために使用します。)
③売上シート3行目に売上金額、年月の式を記入します。
=B3*E3 =TEXT(C3,”YYYYMM”)
④この式をコピーし、全部の行に貼り付けます。
(②で覚えた全部の列について繰り返す)
2)プログラム
このプログラムも難しい命令が続きますが、前のプログラムと同じ文も多いので頑張っていきましょう。
|
01行 Sub 式コピー() 02行 Dim BK, INS, ENDROW 03行 Application.ScreenUpdating = False 04行 Set BK = ActiveWorkbook 05行 Set INS = BK.Sheets("売上") '入力出力シート 06行 ENDROW = INS.Range("$A65536").End(xlUp).Row 07行 If ENDROW < 3 Then 08行 Exit Sub 09行 End If 10行 ra = Array("$F", "$G") 11行 '''標準書式への変更 12行 For K = LBound(ra) To UBound(ra) 13行 INS.Range(ra(K) & "3:" & ra(K) & ENDROW).NumberFormatLocal = "G/標準" 14行 Next K 15行 INS.Range("$F3").Formula = "=B3*E3" 16行 INS.Range("$G3").Formula = "=TEXT(C3,""yyyymm"")" 17行 For K = LBound(ra) To UBound(ra) 18行 INS.Range(ra(K) & "3").Copy 19行 INS.Range(ra(K) & "3:" & ra(K) & ENDROW).PasteSpecial Paste:=xlFormulas 20行 Next K 21行 INS.Calculate ''計算実行 自動計算ＯＦＦの場合への対応 22行 End Sub |
3)プログラムの説明
2行から9行は前のプログラムと同じです。
02行 Dim BK, INS, ENDROW
03行 Application.ScreenUpdating = False
04行 Set BK = ActiveWorkbook
05行 Set INS = BK.Sheets("売上") '入力出力シート
①売上データが何行あるか調べる。データがなければ終了する。
06行 ENDROW = INS.Range("$A65536").End(xlUp).Row
07行 If ENDROW < 3 Then
08行 Exit Sub
09行 End If
使用する変数を定義し、画面表示を止め、表示されているブックをBKと呼び、そのブックの売上シートをINSと呼び、INSのA列の最終行をENDROWに記憶し、ENDROWが3行より小さければプログラムを終了する。
|
参考 別のブックの利用 Set INS = WORKBOOKS(“DATA.XLS”).Sheets("売上") この文では 表示されていないが開かれているブックDATA.XLS内の売上シートを処理できます。この方法を使うと複数のブックを使って自動集計ができます。 |
②式を記入する売上金額の列、年月の列をあらかじめ覚えておきます。
複数の列に式を記入するのに、繰り返し処理を使用したほうがプログラムが見やすくなります。高度な処理になりますが、繰り替し処理をするため、記入する列名を配列に記入します。
10行 ra = Array("$F", "$G")
あらかじめ決められている複数の値を処理することができるやり方です。
Array() の()の中に、処理したい値を記入します。ここでは、”$F” と “$G”の2つを処理します。10個でも20個でも処理できます。
この内容が配列RAの中に記憶されます。
11行 '''標準書式への変更
コメントであり プログラムとしては動作しません
12行 For K = LBound(ra) To UBound(ra)
20行 Next K
配列の中にあるものを取り出し、繰り返し処理する方法です。
配列は RA(0) RA(1) RA(2)のように使用しますが、最少の番号 0 最大の番号 2を取り出す命令が LBound()とUBound()になります。
13行 INS.Range(ra(K) & "3:" & ra(K) & ENDROW).NumberFormatLocal = "G/標準"
これから式を記入する列の書式を標準にします。
INS.Range(ra(K) & "3:" & ra(K) & ENDROW)は ra(K) 列の3行から最終行までのセル範囲を意味します。NumberFormatLocalはセルの書式を意味します。
全体では、Arrayに設定した列の3行から最終行までのセル範囲の書式を標準書式にするという意味です。
|
参考 Excelのバグ 大量のデータで文字列の計算式を記入した場合、セルの書式が文字列に代わってしまい、計算式が記入できなくなるExcelの不具合がありました。念のため計算式を記入する前に標準書式に変更しています。今回の場合、G列は年月を記入しますので、文字列の計算式になります。 |
ここからが重要です。下図に示します。
③売上シート3行目に売上金額、年月の式を記入します。
15行 INS.Range("$F3").Formula = "=B3*E3"
F3セルに売上金額を求める計算式を入力します。
Formulaは 計算式を記入する意味になります。
16行 INS.Range("$G3").Formula = "=TEXT(C3,""yyyymm"")"
G3セルに年月を求める計算式を入力します。Yyyymmは年を4ケタ月を2ケタで作成する意味です。
計算式は 手入力する場合 =TEXT(C3,"yyyymm")と入力しますが、プログラム内で「”」を記述する場合は「””」のように2つ並べて入力する必要がありますので、=TEXT(C3,""yyyymm"") となります。
17行 For K = LBound(ra) To UBound(ra)
20行 Next K
さきほどとおなじ、配列の中にあるものを取り出しすべて処理する方法です。
④この式をコピーし、全部の行に貼り付けます。
18行 INS.Range(ra(K) & "3").Copy
Arrayに設定した列の3行のセルを意味します。ここには、すでに式が記入されています。Copy はそのセルをクリップボードにコピーします。
19行 INS.Range(ra(K) & "3:" & ra(K) & ENDROW).PasteSpecial Paste:=xlFormulas
先ほどと同じく、Arrayに設定した列の3行から最終行までのセル範囲を意味します。PasteSpecial Paste:=xlFormulasは18行でコピーした式を数式として貼り付ける意味です。
21行 INS.Calculate ''計算実行 自動計算ＯＦＦの場合への対応
売上シートについて計算式を実行します。自動計算がOFFになっている場合、この命令で計算が実行されます。
以上で説明を終わります。
9-4縦横集計
実行すると、月売上シートに集計されます。
売上シート
月売上シート
1) プログラムの概要
次の手順で売上シートを月売上シートに集計します。
①売上データが何行あるか調べる。データがなければ終了する。
②計算用に新規ブックを作成し、売上データの商品コード、年月、売上金額を作成したブックにコピーします。
③Excelのピボットテーブルの機能で集計します。
④集計結果を月売上シートにコピーします。
2)プログラム
非常に長いプログラムですが自動で作成できるもので、また修正する箇所もありませんので、一部の箇所だけ理解すればいいでしょう。
|
01行 Sub 縦横集計() 02行 Dim INS, OUS, BBK, ENDROW, ENC, ENL, EndRow3 03行 '入力シート 表示行について 新規ブックを作成 ピボットテーブルで集計後、出力シートにコピー 04行 Application.ScreenUpdating = False 05行 Set INS = Sheets("売上") '入力シート 06行 Set OUS = Sheets("月売上") '入力出力シート 07行 OUS.Range("$A$4:$IV65536").ClearContents '出力シートデータクリヤ 縦出力の右下 08行 OUS.Range("$C$3:$IV8").ClearContents '横項目データクリヤ ＩＶ列まで 09行 ENDROW = INS.Range("$A65536").End(xlUp).Row 10行 If ENDROW < 3 Then 11行 Exit Sub 12行 End If 13行 Workbooks.Add 14行 Set BBK = ActiveWorkbook 15行 'データを新規ブックにコピー 16行 INS.Range("$A$3:$A" & ENDROW).SpecialCells(xlVisible).Copy '縦項目 17行 Range("A2").PasteSpecial Paste:=xlValues '非表示列があるとき 上のSpecialCells(xlVisible)が使えない 18行 Range("A1").Value = "縦" 19行 INS.Range("$F$3:$F" & ENDROW).SpecialCells(xlVisible).Copy '数量項目 20行 Range("B2").PasteSpecial Paste:=xlValues 21行 Range("B1").Value = "数量" 22行 INS.Range("$G$3:$G" & ENDROW).SpecialCells(xlVisible).Copy '横項目 23行 Range("C2").PasteSpecial Paste:=xlValues 24行 Range("C1").Value = "横" 25行 'ピボットテーブル 26行 EndRow3 = Range("$A65536").End(xlUp).Row 27行 Range("A1:C" & EndRow3).Select 28行 ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ 29行 Selection, TableDestination:="", TableName:= _ 30行 "ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙZ" 31行 ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙZ").AddFields RowFields:="縦", ColumnFields:="横" 32行 With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙZ").PivotFields("数量") 33行 .Orientation = xlDataField 34行 .Function = xlSum 'XLCOUNT 'で件数カウント 35行 End With 36行 ENC = Range("iv2").End(xlToLeft).Column - 1 '最終列 縦横集計 37行 ENL = Range("A65536").End(xlUp).Row - 1 '最終行 38行 'データ出力 39行 Range(Range("A3"), Cells(ENL, 1)).Copy '縦項目コピー 40行 OUS.Range("$A$4").PasteSpecial Paste:=xlValues, Operation:=xlNone, _ 41行 SkipBlanks:=False, Transpose:=False '縦項目 値のみ貼り付け 42行 Range(Range("B2"), Cells(2, ENC)).Copy '横項目コピー 43行 OUS.Range("$C$3").PasteSpecial Paste:=xlValues, Operation:=xlNone, _ 44行 SkipBlanks:=False, Transpose:=False 45行 Range(Range("B3"), Cells(ENL, ENC)).Copy 'データコピー 46行 OUS.Range("$C$4").PasteSpecial Paste:=xlValues, Operation:=xlNone, _ 47行 SkipBlanks:=False, Transpose:=False 48行 Application.DisplayAlerts = False 49行 BBK.Close 'ピボットテーブルブック 50行 'Application.DisplayAlerts = ｔｒｕｅ 51行 End Sub |
3)プログラムの説明
02行 Dim INS, OUS, BBK, ENDROW, ENC, ENL, EndRow3
03行 '入力シート 表示行について 新規ブックを作成 ピボットテーブルで集計後、出力シートにコピー
04行 Application.ScreenUpdating = False
05行 Set INS = Sheets("売上") '入力シート
06行 Set OUS = Sheets("月売上") '入力出力シート
07行 OUS.Range("$A$4:$IV65536").ClearContents '出力シートデータクリヤ 縦出力の右下
08行 OUS.Range("$C$3:$IV8").ClearContents '横項目データクリヤ ＩＶ列まで
①売上データが何行あるか調べる。データがなければ終了する。
09行 ENDROW = INS.Range("$A65536").End(xlUp).Row
10行 If ENDROW < 3 Then
11行 Exit Sub
12行 End If
ここは今までと同じ内容です。
売上シートをINSと呼び、月売上シートをOUSと呼びます。(アウトプットシート)OUSを消去します。売上シートA列の最終行をENDROWに入れる。ENDROWが3より小さければ終了する。
ここからは売上シートのデータを新規ブックにコピーし、Excelピボットテーブルの機能で集計し、結果を月売上シートに貼り付けます。
②計算用に新規ブックを作成し、売上データの商品コード、年月、売上金額を作成したブックにコピーします。
13行 Workbooks.Add
14行 Set BBK = ActiveWorkbook
16行 INS.Range("$A$3:$A" & ENDROW).SpecialCells(xlVisible).Copy '縦項目
17行 Range("A2").PasteSpecial Paste:=xlValues
売上シートのA列商品コードを新規に作成した計算用ブックのA列にコピーする
19行 INS.Range("$F$3:$F" & ENDROW).SpecialCells(xlVisible).Copy '数量項目
20行 Range("B2").PasteSpecial Paste:=xlValues
売上シートのF列売上金額を新規に作成した計算用ブックのB列にコピーする
22行 INS.Range("$G$3:$G" & ENDROW).SpecialCells(xlVisible).Copy '横項目
23行 Range("C2").PasteSpecial Paste:=xlValues
売上シートのG列年月を新規に作成した計算用ブックのC列にコピーする
③Excelのピボットテーブルの機能で集計します。
28行～
34行 .Function = xlSum 'XLCOUNT 'で件数カウント
Excelピボットテーブルの機能で合計を集計する。
④集計結果を月売上シートにコピーします。
39行 Range(Range("A3"), Cells(ENL, 1)).Copy '縦項目コピー
40行 OUS.Range("$A$4").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
41行 SkipBlanks:=False, Transpose:=False '縦項目 値のみ貼り付け
集計結果の商品コードを月売上シートのA4に貼り付け
42行 Range(Range("B2"), Cells(2, ENC)).Copy '横項目コピー
43行 OUS.Range("$C$3").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
44行 SkipBlanks:=False, Transpose:=False
集計結果の年月項目を月売上シートのC3に貼り付け
45行 Range(Range("B3"), Cells(ENL, ENC)).Copy 'データコピー
46行 OUS.Range("$C$4").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
47行 SkipBlanks:=False, Transpose:=False
集計結果の売上金額を月売上シートのC4に貼り付け
9-5 型式合成
実行すると、月売上シートに型式が表示されます。
月売上シート
1) プログラムの概要
次の手順で商品名を調べます。
基本的には「単価合成」と同じです。
①月売上データが何行あるか調べる。データがなければ終了する。
②商品コードを上から1つ取る。
③その商品コードが価格シートA列のどこにあるかを調べる。
④その行の商品名を売上データに記入する。
⑤ ②へ戻り最後の行まで繰り返す。
|
01行 Sub 型式合成() 02行 Dim INS, EN, SBK, I, KEYD, F 03行 Application.ScreenUpdating = False 04行 Set INS = Sheets("月売上") '入力出力シート 05行 EN = INS.Range("$A65536").End(xlUp).Row 06行 If EN < 4 Then Exit Sub 07行 INS.Range("$B4:$B" & EN).ClearContents '出力データクリヤ 08行 Set SBK = Sheets("価格") '参照ブックシート 09行 For I = 4 To EN 10行 KEYD = INS.Range("$A" & I).Value 11行 Set F = SBK.Range("$A:$A").Find(What:=KEYD, LookIn:=xlValues, _ 12行 LookAt:=xlWhole, MatchCase:=False) 13行 If F Is Nothing Then 14行 INS.Range("$B" & I).Value = "" '見つからない 15行 Else 16行 INS.Range("$B" & I).Value = SBK.Range("$B" & F.Row).Value 17行 End If 18行 Application.StatusBar = I & "＜--" & EN 19行 Next I 20行 Application.StatusBar = "" 21行 End Sub |
このプログラムは 単価合成と同じですので説明しません。
9-6 合計計算式出力
1) プログラムの概要
|
01行 Sub 合計計算式出力() 02行 Set INS = Sheets("月売上") '入力出力シート 03行 EN = INS.Range("$A65536").End(xlUp).Row '最終行の判断 列を修正する 04行 '修正方法 最終行9のとき 9-> " & EN &" 10->" & EN +1 &" 05行 '修正例 ”＝SUM($A3:$A" & EN &") "=F11/G11" -> "F"& EN+2 &"/G"& EN+2 06行 INS.Range("$B" & EN + 2).Formula = "=""計""" 07行 INS.Range("$C" & EN + 2).Formula = "=SUM(C4:C" & EN & ")" 08行 INS.Range("$D" & EN + 2).Formula = "=SUM(D4:D" & EN & ")" 09行 INS.Range("$E" & EN + 2).Formula = "=SUM(E4:E" & EN & ")" 10行 End Sub |
実行すると、合計が計算されます。
月売上シート
月売上データが何行あるか調べる。
②最後の行の2行下に、月別の合計金額を求める式を記入します。
2)プログラム
02行 Set INS = Sheets("月売上") '入力出力シート
①月売上データが何行あるか調べる。
03行 EN = INS.Range("$A65536").End(xlUp).Row '最終行の判断 列を修正する
月売上シートをINSと呼ぶ。
月売上シートのA列の最終行を変数ENに入れる。
②最後の行の2行下に、月別の合計金額を求める式を記入します。
06行 INS.Range("$B" & EN + 2).Formula = "=""計"""
月売上シートのB列の最終行の2行下の行に “=計” を記入
07行 INS.Range("$C" & EN + 2).Formula = "=SUM(C4:C" & EN & ")"
「&」は文字をつなげる意味なので、
月売上シートのA列の最終行が6のとき、"=SUM(C4:C" & EN & ")"は
“=SUM(C4:C” と “6” と “)” をつないで “=SUM(C4:C6)”
となります。
C列4行から最終行を集計して、C列の最終行の2行下の行に記入します。
08行 INS.Range("$D" & EN + 2).Formula = "=SUM(D4:D" & EN & ")"
09行 INS.Range("$E" & EN + 2).Formula = "=SUM(E4:E" & EN & ")"
も同様に D列 E列を集計します。
以上ですべてのプログラムの説明を終わります。
|
参考 このプログラムでは月が増えると合計が計算されません。 月数に応じて、合計式をコピーするプログラムを載せます。 Sub 横式コピー() Dim INS, ENDROW, ENC Application.ScreenUpdating = False Set INS = Sheets("月売上") '入力出力シート EN = INS.Range("$A65536").End(xlUp).Row '最終行の判断 列を修正する ENC = INS.Range("IV3").End(xlToLeft).Column '最終列 INS.Range("$B" & EN + 2).Formula = "=""計""" INS.Range("$C" & EN + 2).Formula = "=SUM(C4:C" & EN & ")" INS.Range("$C" & EN + 2).Copy INS.Range("$C" & EN + 2).Resize(, ENC - 2).PasteSpecial _ Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False INS.Calculate '計算実行 End Sub |
ここまで理解できれば、プログラムの自動作成の方法を覚えて、どんどん自動化してください。業務効率大幅アップとなります。
岩崎システム教育