EXCEL VBA スライサー・ワークシート上のテーブルにスライサーを作成して複数のデータを抽出(Slicer)
EXCEL VBA スライサー(Slicer)ワークシート上のテーブルにスライサーを作成して複数のデータを抽出
今回説明するのは、ワークシート上に作成したテーブルを利用して簡単にデータを抽出する事のできるスライサーについて説明をいたします。このスライサーは、テーブルで作成しているデータをボダン化して表示されるので、同じデータを抽出する際は、その該当するボダンをクリックする事で簡単にデータを抽出する事が出来ます。また、該当するボタンも複数選択または、列ごとにデータを抽出するボタンを作成する事が出来るので、複雑なデータを抽出する際もボタンをクリックする事で素早く該当データを抽出する事ができます。それでは、サンプルプログラムを交えて順番に説明致します。
●【EXCEL VBA テーブル作成・テーブル解除・設定の方法については、下記を参照して下さい】
● ワークシート上のテーブルデータを元にスライサーを設置するには、下記の通りに設定を行います。
下図は、テーブルデータを元にスライサーを設定する方法を説明したものです。事前にテーブルを作成し、下記のプログラムを実行する事でスライサーを作成する事ができます。
【設定方法】
① ワークシート名を設定します。ここでは、「Sheet1」を設定します。
② スライサーの位置を設定します。ここでは、セル「G2~H10」の場所にスライサーを設置します。
③ テーブル名を設定します。ここでは、「テーブル1」でテーブル名を登録されているのでそのまま設定します。
④ スライサーの対象位置(列)を設定します。ここでは、「性別」を設定します。
【サンプルプログラム】
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
' ' Sub Set_Slicer01() 'テーブルよりスライサーを作成します。 Dim Ws As Worksheet Dim SlicerSete As Range Set Ws = Worksheets("Sheet1") 'テーブルの設置しているワークシートを設定します。 Set SlicerSete = Ws.Range("G2:H10") 'スライサーを設置する位置を指定します。 With ThisWorkbook.SlicerCaches.Add(Ws.ListObjects("テーブル1"), "性別") _ .Slicers.Add(Ws, Top:=SlicerSete.Top, Left:=SlicerSete.Left, Width:=SlicerSete.Width, Height:=SlicerSete.Height) 'スライサーを設置します。(テーブル1の性別) End With End Sub ' |
【サンプルプログラム実行前~実行後】
【注意点】
・上記のプログラムはスライサーを追加する様に作成していますので、既にスライサーが作成されている場合は、スライサーを削除してからプログラムを実行する必要があります。
(削除せずに実行すると同じスライサーが複数作成されます。)
EXCEL VBA スライサー(Slicer)ワークシート上のテーブルに複数のスライサーを作成
下記のサンプルプログラムは、テーブル(社員台帳)にデータが登録されています。このデータから「所属」と「役職」を簡単に抽出できるようにスライサーの設定を行います。事前準備として、テーブル(社員台帳)については、事前にテーブル化する必要があります。それでは、順番に説明致します。
【プログラムの流れ】
① テーブルが登録されているワークシート名を「社員台帳」に指定します。
② スライサーの表示位置を指定します。所属(セル:G2~H10)
③ 1つ目のスライサー(所属)を設置します。
④ スライサーの表示位置を指定します。役職(セル:I2~J10)
⑤ 2つ目のスライサー(役職)を設置します。
【プログラム実行条件】
① テーブル名「社員一覧」でテーブルを作成しておく。
② テーブルデータ内に、「所属」・「役職」を作成します。
③ テーブル名「社員台帳」を作成したシート名を「社員台帳」に設定します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
' ' Sub Set_Slicer02() 'テーブルよりスライサーを作成します。(スライサーを2つ作成) Dim Ws As Worksheet Dim SlicerSet As Range Set Ws = Worksheets("社員台帳") 'テーブルが登録されているシート「社員台帳」を指定します。 Set SlicerSet = Ws.Range("G2:H10") 'スライサーの位置を指定します。 With ThisWorkbook.SlicerCaches.Add(Ws.ListObjects("社員一覧"), "所属") _ .Slicers.Add(Ws, Top:=SlicerSet.Top, Left:=SlicerSet.Left, Width:=SlicerSet.Width, Height:=SlicerSet.Height) 'スライサーを設置します。(テーブル:社員一覧の所属) End With Set SlicerSet = Ws.Range("I2:J10") 'スライサーの位置を指定します。 With ThisWorkbook.SlicerCaches.Add(Ws.ListObjects("社員一覧"), "役職") _ .Slicers.Add(Ws, Top:=SlicerSet.Top, Left:=SlicerSet.Left, Width:=SlicerSet.Width, Height:=SlicerSet.Height) 'スライサーを設置します。(テーブル:社員一覧の役職) End With End Sub ' |
(画面クリックして拡大)
EXCEL VBA テーブルを作成し、スライサー(Slicer)を複数項目作成する(テーブル作成 ⇒ スライサー作成:自動作成)
下記のサンプルプログラムは、サンプル①同様、テーブルデータを元に複数項目のスライサーを作成します。また、今回のプログラムでは、スライサーを使う事前準備として利用するテーブルも作成し、3つのスライサーも順番に作成するサンプルプログラムです。テーブル作成からスライサー作成の一連の流れで動作するので、業務上様々な場面でも利用できると思います。それでは、順番に説明致します。
【プログラムの流れ】
① データが登録されているワークシート名「商品一覧」に指定します。
② エラーが発生した場合は、「Err_syori」へ飛びます。
③ セル「A1」を起点にテーブルを作成します。テーブルが未作成の場合はそのままテーブルを作成します。既にテーブルが作成されている場合は、エラーが発生して、「Err_syori」へ飛びます。
④ 既にワークシート上にスライサーが作成している場合は、全て削除します。
⑤ スライサーを設置します。(支店名)
⑥ スライサーを設置します。(カテゴリー)
⑦ スライサーを設置します。(商品)
【プログラム実行条件】
① セル「A1」を起点にデータを作成する。
② 見出し名に「支店名・カテゴリー・商品」を指定作成します。
③ ワークシート名を「商品一覧」にします。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
' ' Sub Set_Slicer03() 'テーブル作成からスライサーも作成 Dim Ws As Worksheet Dim I As Long Dim SlicerSet As Range Set Ws = Worksheets("商品一覧") 'データが登録されているワークシート「商品一覧」を指定します。 On Error GoTo Err_Shori 'エラーが発生した場合は、「Err_Shori」へ飛びます。 With Ws .ListObjects.Add Source:=Ws.Range("A1").CurrentRegion, xllistobjecthasheaders:=xlYes, tablestylename:="TableStylelight2" 'セル「A1」を起点にテーブルを作成します。既にテーブルが作成している場合は、エラーが発生して、「Err_syori」へ進む Err_Shori: For I = .Shapes.Count To 1 Step -1 If .Shapes(I).Type = msoSlicer Then .Shapes(I).Delete '既にスライサーが作成して有る場合は、削除します。 Next I End With Set SlicerSet = Ws.Range("F2:G10") 'スライサー位置を指定します。(支店名:位置) With ThisWorkbook.SlicerCaches.Add(Ws.ListObjects(Ws.ListObjects(1).Name), "支店名") _ .Slicers.Add(Ws, Top:=SlicerSet.Top, Left:=SlicerSet.Left, Width:=SlicerSet.Width, Height:=SlicerSet.Height) 'スライサーを設置します。(テーブル:支店名) End With Set SlicerSet = Ws.Range("H2:I10") 'スライサー位置を指定します。(カテゴリー:位置) With ThisWorkbook.SlicerCaches.Add(Ws.ListObjects(Ws.ListObjects(1).Name), "カテゴリー") _ .Slicers.Add(Ws, Top:=SlicerSet.Top, Left:=SlicerSet.Left, Width:=SlicerSet.Width, Height:=SlicerSet.Height) 'スライサーを設置します。(テーブル:カテゴリー) End With Set SlicerSet = Ws.Range("J2:K10") 'スライサー位置を指定します。(商品:位置) With ThisWorkbook.SlicerCaches.Add(Ws.ListObjects(Ws.ListObjects(1).Name), "商品") _ .Slicers.Add(Ws, Top:=SlicerSet.Top, Left:=SlicerSet.Left, Width:=SlicerSet.Width, Height:=SlicerSet.Height) 'スライサーを設置します。(テーブル:商品) End With End Sub ' |
(画面クリックして拡大)
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。