ExcelVBA学習3日目(データの処理メイン)
▼データエリアに外枠を付ける
セルA1 を含むセル範囲にデータを入力してから実行する
Option Explicit
Sub Dataline()
Range("A1").CurrentRegion.Select
Selection.BorderAround ColorIndex:=5, Weight:=xlMedium
End Sub
▼データエリアから項目行を除く
Resizeプロパティ、OffSetプロパティを使用して、アクティブセル領域から各列のタイトル行(項目行)を除く。データエリアからデータ部分のみを抽出するときに使用する。
Resizeプロパティは、行・列レベルでセル範囲のサイズを、OffSetプロパティは、行・列レベルで指定セル範囲からの相対的な位置を設定する。
セルA1を含むセル範囲にデータを入力してから事項する。
Option Explicit
Sub ResizeOffSet()
Dim RowsCount As Integer
Range("A1").CurrentRegion.Select
MsgBox "select the area of acrivecell!"
RowsCount = Selection.Rows.Count
Selection.Offset(1).Select
MsgBox "shift to the range to 1 row down"
Selection.Resize(RowsCount - 1).Select
MsgBox "delete a extra row"
End Sub
▼オートフィルタでデータを抽出
AutoFilterメソッドの引数fieldは、フィルタの対象となるフィールド番号を整数で指定、引数Criteria1は抽出条件となる文字列を指定する。
データを入力してから実行すると、データを抽出してくれる。
Option Explicit
Sub Dataselect()
Range("A1").AutoFilter field:=1, Criteria1:="福岡支店"
MsgBox "abstruct data"
End Sub
▼検索したセルのフォントカラーを変更する
Findメソッドはセル範囲を対象にして、検索条件に一致したセル（Range型）を返す。条件に一致するセルが複数ある場合は、FindNextメソッドで順次検索を行う。
検索条件に一致するセルのフォントを青にする。
Option Explicit
Sub Datalookup()
Dim RangeObje As Range
Dim FirstRanAddress As String
Set RangeObje = Cells.Find("ball") ’最初に見つあったセルを選択
If Not RangeObje Is Nothing Then ’Findメソッドの返り値がNothingでないことを確認
FirstRanAddress = RangeObje.Address ’そのアドレスを取得
Do
RangeObje.Font.ColorIndex = 5 ’フォントカラーを青にする
Set RangeObje = Cells.FindNext(RangeObje) ’その他検索条件に一致するセルを検索
Loop Until RangeObje Is Nothing Or RangeObje.Address = _
FirstRanAddress ’FindメソッドがNothingを返すか、Findメソッドの返したセル（Range）のアドレスが取得したアドレスとイコールになるまでループ
End If
End Sub
▼ソーティング
Sortメソッドを使用してデータを並べ替える。引数Key1は並べ替えで最優先されるフィールド、引数Order1は昇順、降順の指定。
例では、B列を基準として降順に並べ替える。
Option Explicit
Sub DataLineup()
Range("A1").Sort Key1:=Range("B1"), Order1:=xlDescending
End Sub
▼セル範囲を集計する
Subtotalメソッドは、指定セル範囲についての集計の作成を行う。
Subtotalメソッドには次の引数を設定する
GroupBy→何列目にあるデータを基準に分類するかを設定
Function→集計方法(合計、平均等)
TotalList→何列目を集計の対象するかどうかを設定
Replace→既存の集計表を更新するかどうかを設定
PageBreaks→分類毎に改ページにするかどうかを設定
SummaryBlowData→集計行の位置
Option Explicit
Sub shuukei()
Worksheets("Sheet8").Activate
Range("A1").CurrentRegion.Select ←表全体を選択
'chenge in order(priority key is subject)
Selection.SortSpecial SortMethod:=xlSyllabary, Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
'do the Sumfuction
Selection.Subtotal GroupBy:=2, Function:=xlSum, _
TotalList:=Array(3, 4, 5), Replace:=True, _
PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Range("A1").CurrentRegion.Copy ←集計表をコピー
ActiveSheet.Paste Destination:=Worksheets("Sheet9").Range("A1") ←Sheet9に張り付け
ActiveSheet.Range("A1").CurrentRegion.RemoveSubtotal ←集計状態解除
End Sub
▼ピボットテーブルを作成
PivotTableWizardメソッドによって、Sheet1のスポーツ用品店野球用品売り上げデータベースをもとに、ピボットテーブルをSheet2に作成して、”summary sheet”と名付ける。
このピボットテーブルにAddFieldsプロパティで、行フィールド(商品名)、列フィールド(月)、ページフィールド(支店名)を追加する。
Orientationプロパティで、”売上金額”をデータフィールドに配置する。
- Worksheets("シート名").PivotTableWizardメソッド→PivotTable型
- PivotTables(”ピボットテーブル名”).AddFieldsメソッド
- PivotTables(”ピボットテーブル名”).PivotFieldsメソッド(”ピボットテーブル名”)→PivotField型
Option Explicit
Sub PivotTW()
Worksheets("Sheet2").Activate
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:=Worksheets("Sheet1").Range("A1:D16"), _ ←参照データ
TableDestination:=Worksheets("Sheet2").Range("A1"), _ ←ピボットテーブルの配置場所
TableName:="summary sheet" ビボットテーブル名
ActiveSheet.PivotTables("summary sheet").AddFields _ ←フィールドの追加
RowFields:="goods", ColumnFields:="month", _ ←行、列フィールドの追加
PageFields:="store" ←ページフィールドの追加
ActiveSheet.PivotTables("summary sheet").PivotFields("sales"). _
Orientation = xlDataField ”売上金額” をデータフィールドに配置
End Sub
▼エラーの種類に応じて、エラー処理を行う
On Error GoTo ステートメント
On Errorステートメントは、エラーの発生時に指定行ラベルに処理を移行する。
指定行ラベル以下にはエラー処理ルーチンを記述しておく。
