Excel。マクロ039。表をテーブルにして抽出までをマクロにしてみよう
＜Excel VBA：テーブル化と抽出＞
データを読み込んだ後に、データを抽出するに当たり、テーブルにしてから抽出を行うということは、現場でも日々行われることがあるかと思います。日々同じような処理をするようでしたら、【マクロ】を実行するようにすれば、時短することができますね。
そこで、今回は、読み込んだデータをテーブルにする。
そして、テーブルにした場合に、どのようにして抽出するか確認してみましょう。
【テーブルにする】
通常テーブルにするには、挿入タブからテーブルをクリックしますが、そのあと、範囲選択を確認するダイアログボックスが表示されますが、ほとんど変える必要がありません。その分だけ作業時間が無駄です。そこでExcel VBAをつかっていきます。
Sub テーブル化()
ActiveSheet.ListObjects.Add xlSrcRange, Range("a1").CurrentRegion
End Sub
この一行だけでOKですので、実行してみましょう。
ListObjects【リストオブジェクツ】コレクションのAdd【アド】メソッドを使用することで、テーブルにすることができます。
xlSrcRange【エックスエルソースレンジ】は、セル範囲という意味です。
テーブルになりましたが、テーブルにするたびに、『テーブルスタイル（中間）2』という青色の縞々が設定されてしまします。
そこで、縞々はいらない場合には、次の行を追加してみましょう。
Sub テーブル化()
ActiveSheet.ListObjects.Add xlSrcRange, Range("a1").CurrentRegion
ActiveSheet.ListObjects(1).TableStyle = ""
End Sub
これで、縞々を解除することができました。TableStyle = ""とするとスタイルを解除することができます。
では、ここで、テーブルの解除についてもExcel VBAで作ってみましょう。
【テーブルを解除するには】
TableStyle = "" を紹介しましたが、この構文を使うことで、テーブルを解除して元の表に戻すことができます。テーブルを元の表に戻す処理は、Excelでも結構面倒な処理の代表格で、縞々などのスタイルを”なし”にしたあとに、『範囲に変換』を実行することになります。
では、次の構文を確認してみましょう。
Sub テーブル解除()
ActiveSheet.ListObjects(1).TableStyle = ""
ActiveSheet.ListObjects(1).Unlist
End Sub
Unlistプロパティを使うことで範囲に変換することができました。
そして、テーブルですが、テーブル名をつけることで様々な処理を行う時に効率がアップしますので、テーブル名の付け方も確認しておきましょう。
【テーブル名を設定するには】
テーブルと関数を合わせて使うなど、コラボさせるとExcelの処理が格段に改善することができるものもあります。そこで、テーブル名も併せて設定するには、どうしたらいいのでしょうか？
Sub テーブル化()
ActiveSheet.ListObjects.Add xlSrcRange, Range("a1").CurrentRegion
ActiveSheet.ListObjects(1).TableStyle = ""
ActiveSheet.ListObjects(1).Name = "実績"
End Sub
Nameプロパティを設定すれば簡単にテーブル名を設定することができます。
【データを抽出してみる。集計行も表示させる】
テーブルには、オートフィルター機能もありますので、抽出も紹介していきます。Sub 抽出()
With ActiveSheet.ListObjects(1).Range
.AutoFilter 2, "日"
.AutoFilter 3, "サンドイッチセット"
.AutoFilter 6, ">9000"
End With
With ActiveSheet.ListObjects(1)
.ShowTotals = True
.ListColumns(6).TotalsCalculation = xlTotalsCalculationSum
End With
End Sub
AutoFilterメソッドを使いますが、テーブルになっているので、テーブルの左から何列目のフィールドなのかがわかればいいようになっています。
条件は、Criteria（クライテリア）を省略することができます。
ShowTotals = True
これで、集計行を表示します。
ListColumns(6).TotalsCalculation = xlTotalsCalculationSum
ListColumns(6)は、フィールドの6列目。すなわち金額フィールドを、集計方法（TotalsCalculation）が合計（xlTotalsCalculationSum）をするという構文です。
今回は、テーブル化して抽出から集計行を表示させるまで確認してみました。機会があえれば、色々日ごろの作業をマクロ化してみませんか？
