Cách lọc dữ liệu Excel ngay trong trang trình chiếu Powerpoint

Trong bài viết này, Blog Học Excel Online sẽ hướng dẫn bạn cách để lọc dữ liệu Excel ngay trong trang trình chiếu Powerpoint.

Bộ lọc Powerpoint

Liệu ta có thể tạo ra một bộ lọc dữ liệu Excel bằng cách sử dụng một trang trình chiếu bất kỳ trong Powerpoint hay không? Câu trả lời là hoàn toàn có thể, bằng cách sử dụng một danh sách hỗn hợp và thêm 1 chút mã lệnh VBA là xong.

Thêm danh sách hỗn hợp vào trang trình chiếu của bạn

Để chèn thêm một danh sách hỗn hợp, bạn cần truy cập thanh tác vụ Developer trong cửa sổ Powerpoint. Trong thanh tác vụ này hãy tìm đến biểu tượng của danh sách hỗn hợp (được bôi đậm như hình dưới). Sau khi bấm vào đó bạn hoàn toàn có thể tự vẽ 1 hình hộp bất kỳ chỉ với con trỏ chuột.

Nạp các giá trị dữ liệu vào trong danh sách hỗn hợp

Tiếc rằng Powerpoint không có khả năng truy cập vào các thiết lập của Form, mà chỉ thiết lập được ActiveX mà thôi. Một ưu điểm của thiết lập Form là việc bạn có thể đồng bộ nội dung form theo các giá trị trang tính của Excel. Hiển nhiên là trong quá trình làm việc với Powerpoint, bạn sẽ không được tiếp xúc với các kiểu lưu trữ dữ liệu theo dạng trang tính, vì thế việc thiết lập Form là không khả thi. Thông qua thiết lập ActiveX, ta phải tự nạp dữ liệu vào danh sách hỗn hợp qua các mã lệnh VBA. Điều đầu tiên phải làm đó là phải tạo ra 1 “sự kiện” để kích hoạt mã lệnh. Giả sử ở đây ta mặc định sự kiện đó là Got Focus (có nghĩa là bất cứ lúc nào hộp hỗn hợp được lựa chọn hoặc được làm nổi trên màn hình hiển thị thì mã lệnh sẽ tự khởi chạy). Nhờ đó các hàm thủ tục sẽ được kích hoạt theo, tạo ra một danh sách hỗn hợp xổ xuống  rất nhiều lựa chọn dành cho người dùng.

Private Sub ComboBox1_GotFocus()
‘PURPOSE: Event to capture when a user selects the combo box
‘SOURCE: www.TheSpreadsheetGuru.com

‘Run Macro to populate combo box with values
  Call AddItemsToComboBox

End Sub

Hàm thủ tục có tên AddItemsToComboBox sẽ có nhiệm vụ nạp dữ liệu vào danh sách hỗn hợp trong cửa sổ Powerpoint. Để tránh tình trạng danh sách hỗn hợp nạp đi nạp lại cùng một dạng dữ liệu nhiều lần (gây mất thời gian), bạn có thể thêm một số điều kiện dạng logic để ngăn nó tự nạp thêm dữ liệu nếu như dữ liệu đó đã có sẵn. Tùy theo thiết lập của từng danh sách hỗn hợp mà bạn sẽ phải tự biến tấu điều kiện logic của mình sao cho phù hợp (chẳng hạn nếu danh sách xổ xuống của bạn có 10 lựa chọn khác nhau, bạn cần điền thêm điều kiện “<10” vào công thức).

Sub AddItemsToComboBox()
‘PURPOSE: Populate a Combo Box with Values if not Already Populated
‘SOURCE: www.TheSpreadsheetGuru.com

If Slide2.ComboBox1.ListCount < 5 Then
  Slide2.ComboBox1.ListRows = 5
  Slide2.ComboBox1.AddItem (“All”)
  Slide2.ComboBox1.AddItem (“Bob”)
  Slide2.ComboBox1.AddItem (“John”)
  Slide2.ComboBox1.AddItem (“Ben”)
  Slide2.ComboBox1.AddItem (“Sally”)
End If

End Sub

Truy xuất dữ liệu từ Excel

Và giờ là đến phần mã lệnh. Tiếp theo ta cần phải truy xuất dữ liệu đã được lọc từ tệp Excel gốc. Lệnh này sẽ được khởi động bởi hàm Change, có nghĩa là toàn bộ mã lệnh sẽ khởi chạy một khi người dùng thay đổi giá trị của danh sách hỗn hợp. Cụ thể, đoạn mã lệnh sẽ xử lý các thao tác như sau:

• Mở tệp Excel gốc lên
• Lọc dữ liệu dựa trên thiết lập mà người dùng đã đặt sẵn trên danh sách hỗn hợp
• Thay ảnh đại diện của trang trình chiếu bằng 1 hình ảnh khác đã được lọc qua
• Đóng tệp Excel lại (và không sao lưu nội dung)
• Căn chỉnh lại kích cỡ ảnh minh họa mới dựa trên thông số của ảnh cũ

Private Sub ComboBox1_Change()
‘PURPOSE: Bring Filtered Excel Data into PowerPoint Presentation
‘SOURCE: www.TheSpreadsheetGuru.com

Dim wb As Object
Dim tbl As Object
Dim ExcelApp As Object
Dim sld As Slide
Dim ComboBx As Shape, NewShape As Shape, OldShape As Shape
Dim myCriteria As String, ExcelFilePath As String
Dim ComboBoxName As String, DataImageName As String
Dim ExcelTableName As String, TableSheet As String
Dim SlideNumber As Long

‘Input Values
  ExcelFilePath = “C:\Users\chris\Desktop\Data.xlsm”
  SlideNumber = 2
  ComboBoxName = “ComboBox1”
  DataImageName = “SalesData”
  ExcelTableName = “Table1”
  TableSheet = “Sheet1”
  
‘Store Object Variables
  Set sld = ActivePresentation.Slides(SlideNumber)
  Set ComboBx = sld.Shapes(ComboBoxName)

‘Create an Instance of Excel
  On Error Resume Next
    
    ‘Is Excel already opened?
      Set ExcelApp = GetObject(class:=”Excel.Application”)
    
    ‘Clear the error between errors
      Err.Clear

    ‘If Excel is not already open then open PowerPoint
      If ExcelApp Is Nothing Then Set ExcelApp = CreateObject(class:=”Excel.Application”)
    
    ‘Handle if the Excel Application is not found
      If Err.Number = 429 Then
        MsgBox “Excel could not be found, aborting.”
        Exit Sub
      End If

  On Error GoTo 0

‘Open Excel File
   Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

‘Determine User Selection
  myCriteria = ComboBx.OLEFormat.Object.Value

‘Filter on select item
Set tbl = wb.Worksheets(TableSheet).ListObjects(ExcelTableName)

  If myCriteria = “All” Then
    tbl.Range.AutoFilter Field:=1
  Else
    tbl.Range.AutoFilter Field:=1, Criteria1:=myCriteria
  End If
  
‘Copy/Paste Data
  tbl.Range.Copy

‘Store characteristics about current Data Image & remove
  Set OldShape = sld.Shapes(DataImageName)
  x = OldShape.Left
  y = OldShape.Top
  Z = OldShape.Width
  OldShape.Delete
  
‘Paste to PowerPoint and position
  sld.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
  
‘Set Variable Equal To Newly Pasted Image
  ‘Handle Excel 2010 & prior bug where combobox stays “in front” of all images no matter what
    If sld.Shapes(sld.Shapes.Count).Type = msoOLEControlObject Then
      Set NewShape = sld.Shapes(sld.Shapes.Count – 1)
    Else
      Set NewShape = sld.Shapes(sld.Shapes.Count)
    End If
  
‘Reposition and Resize Filtered Picture
  NewShape.Left = x
  NewShape.Top = y
  NewShape.Width = Z
  NewShape.Name = DataImageName
         
‘Close Excel File
  ExcelApp.CutCopyMode = False
  wb.Close SaveChanges:=False
  
End Sub

Liệt kê các biến số

Để giúp bạn có thể hiểu được cơ chế hoạt động của mã lệnh, hãy tham khảo đoạn clip ở đây trong đó hướng dẫn bạn cách để thiết lập tệp Powerpoint và Excel sao cho chúng hoạt động hòa hợp với nhau. Đoạn clip cũng sẽ hướng dẫn cách liệt kê biến số cũng như các giá trị quan trọng cần thiết để macro có thể hoạt động được, tùy theo mục đích sử dụng của bạn

Liệu có lỗi xảy ra ở Powerpoint?

Lưu ý rằng đôi khi bạn sẽ thấy trong Powerpoint xuất hiện một lỗi nhỏ trong quá trình chạy lệnh VBA. Khi khởi động trang trình chiếu Powerpoint và lần đầu click chọn danh sách hỗn hợp, hộp sẽ có hình dạng như sau:

Tuy vậy, kể từ lần click chọn thứ hai trở đi, miễn là cửa sổ Powerpoint còn hoạt động thì danh sách hỗn hợp sẽ xổ xuống và hoạt động bình thường trở lại, như hình minh họa dưới đây: