Hướng dẫn sử dung pivot table khách hàng trên excel

Pivot Table là một trong những công cụ mạnh nhất của excel giúp cho những bảng dữ liệu khổng lồ phức tạp trở nên đơn giản hơn. Trong bài viết này, Cole cùng các bạn tìm hiểu thêm về công cụ Pivot Table và cách dùng Pivot Table cũng như Pivot Table nâng cao

I. Pivot Table là gì và ứng dụng pivot table trong excel?

Pivot Table là một công cụ trong Excel giúp thống kê, thu gọn và tổ chức lại các cột và hàng dữ liệu đã chọn trong bảng tính hoặc bảng cơ sở dữ liệu để có được một báo cáo mong muốn. Công cụ này không thay đổi tích chất của bảng tính hoặc cơ sở dữ liệu, nó chỉ đơn giản là “xoay” hoặc biến dữ liệu để xem nó từ các góc độ khác nhau.

Pivot Table trong Excel đặc biệt hữu ích khi xử lý với lượng lớn dữ liệu, nếu dùng cách tính toán thủ công sẽ tốn rất nhiều thời gian.

Một số chức năng xử lý dữ liệu mà Pivot Table trong Excel có thể thực hiện bao gồm:

• xác định tổng, trung bình, phạm vi hoặc giá trị ngoại lệ
• Sau đó, sắp xếp thông tin này theo một bố cục đơn giản, có ý nghĩa và làm nổi bật các giá trị chính.

II. Cách dùng Pivot Table

Ví dụ chúng ta có bảng thống kê giá trị xuất khẩu hàng hóa bao gồm 213 dữ liệu và 6 trường: Order ID, Product, Category, Amount, Date and Country.

Cơ sở dữ liệu

1. Cách tạo Pivot Table

• Bước 1: Chọn các ô bạn muốn dùng để tạo PivotTable.

Lưu ý: Dữ liệu của bạn không được có bất kỳ hàng hay cột trống nào. Dữ liệu chỉ được có một đầu đề hàng.

• Bước 2: Chọn Insert \> PivotTable.

• Bước 3: Ở hộp thoại Choose the data that you want to analyze (Chọn dữ liệu bạn muốn phân tích), Chọn Select a table or range (Chọn một bảng hoặc dải ô).

Cách tạo pivot table

• Bước 4: Trong hộp Table/Range (Bảng/Dải ô), hãy xác minh dải ô.
• Bước 5: Bên dưới phần Choose where you want the PivotTable report to be placed (Chọn vị trí bạn muốn đặt báo cáo PivotTable),chọn New Worksheet (Trang tính mới để đặt PivotTable vào trang tính mới) hoặc Existing Worksheet (Trang tính hiện có rồi chọn vị trí bạn muốn PivotTable xuất hiện).
• Bước 6: Chọn OK.

2. Sắp xếp các trường dữ liệu

Trên màn hình xuất hiện bảng PivotTable Fields nơi bạn có thể sắp xếp trường cơ sở dữ liệu mong muốn.

Tại đây sẽ có 2 lựa chọn:

• Choose fields to add to report: Chọn vùng dữ liệu để thêm vào báo cáo
• Drag fields between areas below: Kéo thả vùng dữ liệu vào từng khu vực bên dưới

Và 4 vùng để thả các trường dữ liệu được chọn

1. Filters
2. Columns
3. Rows
4. Values

Áp dụng vào trong ví dụ ở trên, ta kéo các trường sau vào các khu vực tương ứng

1. Trường Product (sản phẩm) đến vùng Rows.
2. Trường Amount (số tiền) cho vùng Values.
3. Trường Country (quốc gia) đến khu vực Filters.

Kết quả ta có bảng tổng hợp dữ liệu được thu gọn như ở dưới. Dữ liệu phức tạp trở nên đơn giản hơn nhờ PivotTable

3. Sắp xếp cơ sở dữ liệu

Trong bảng thống kê ở ví dụ, ta có banana là sản phẩm xuất khẩu chính với tổng giá trị xuất khẩu lớn nhất. Để sắp xếp sản phẩm có giá trị xuất khẩu lớn nhất nhỏ nhất Banana xuất hiện, ta thực hiện các bước sau:

• Bước 1: Nhấp vào bất kỳ ô nào bên trong cột Sum of Amount (Tổng số tiền).
• Bước 2: Nhấp chuột phải và nhấp vào Sort (Sắp xếp), và chọn Sort Largest to Smallest (Sắp xếp Lớn nhất đến Nhỏ nhất).

Kết quả ta có bảng tổng hợp dữ liệu được thu gọn như ở dưới. Dữ liệu phức tạp trở nên đơn giản hơn nhờ PivotTable

4. Lọc dữ liệu

Vì chúng tôi đã thêm trường Country (Quốc gia) vào vùng Filters (Bộ lọc), nên chúng ta có thể lọc bảng tổng hợp này theo Country (Quốc gia). Ví dụ bạn muốn biết sản phẩm nào được xuất khẩu nhiều nhất sang France (Pháp).

• Để áp dụng Filters nhằm tìm ra sản phẩm được xuất khẩu nhiều nhất sang France (Pháp), chúng ta làm như sau:

Lọc dữ liệu

• Nhấp vào mũi tên hình tam giác bên cạnh ô (All) và chọn France (Pháp).
• Kết quả hiển thị ở dưới đây, chúng ta có thể thấy Apple (Táo) là sản phẩm xuất khẩu chính sang France (Pháp).

Lưu ý: bạn có thể sử dụng bộ lọc tiêu chuẩn (hình tam giác bên cạnh Row Labels (Nhãn Hàng)) để chỉ hiển thị số lượng sản phẩm cụ thể.

5. Thay đổi phép tính tổng

Theo mặc định, Excel sẽ tóm tắt dữ liệu của bạn bằng cách tính tổng hoặc đếm các mục. Để thay đổi kiểu tính toán bạn muốn sử dụng, hãy thực hiện các bước sau:

• Bước 1: Nhấp vào bất kỳ ô nào bên trong cột Sum of Amount (Tổng số tiền).
• Bước 2: Nhấp chuột phải và nhấp vào Value Field Settings (Cài đặt Trường Giá trị).

• Bước 3: Chọn kiểu tính toán bạn muốn sử dụng. Ví dụ: Count (Đếm) => Nhấn OK.

Kết quả ta có 16 trong số 28 đơn đặt hàng đến France (Pháp) là đơn hàng Apple (Táo).

III. Pivot Table nâng cao

1. Cách chuyển cơ sở dữ liệu từ file excel này sang file excel khác

Sau đây Cole sẽ hướng dẫn bạn cách lấy dữ liệu từ file excel này sang file excel khác trong PivotTable Excel kết hợp với hàm VLOOKUP

• Bước 1: Chọn dữ liệu trên trang tính mà bạn muốn khớp với giá trị từ một trang tính khác. Bỏ chọn danh mục từ bảng tổng hợp, chúng tôi sẽ sử dụng VLookup ở đây để lấy danh mục Sản phẩm.

Công thức trong ví dụ dưới đây tra cứu “Apple” trong cột B trong trang tính dữ liệu.

• Bước 2: Nhập công thức hàm VLOOKUP

Cú pháp hàm VLOOKUP

\=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Trong đó:

• * Lookup_value: Là giá trị dùng để dò tìm.
  • Table_array: Là vùng dữ liệu chứa giá trị cần dò tìm. Vùng có từ 2 cột trở lên. Nhấn phím F4 sau khi chọn vùng để cố định vùng.
  • Col_index_num: Là số thứ tự cột đếm từ vùng dò tìm chứa giá trị cần trả về.
  • Range_lookup: Là phương pháp dò tìm. Range_lookup là 0 hoặc FALSE để tìm giá trị chính xác, range_lookup là 1 hoặc TRUE hoặc bỏ qua để tìm giá trị gần đúng.

Pivot Table nâng cao

Công thức trong ví dụ đang tìm kiếm giá trị sản phẩm “Apple” nằm trong ô B5 từ một trang tính khác.

Data!C3 D42: Có nghĩa là chúng ta đang nhận giá trị tra cứu từ bảng Pivot Table so với apple.

2,0 cho biết rằng giá trị chúng ta đang tìm kiếm một sản phẩm nằm trong cột 2 và 0 có nghĩa là tìm giá trị khớp chính xác với mỗi hàng.

• Bước 3: Ấn Enter để cho ra kết quả

• Bước 4: Kéo cùng một công thức với từng danh mục sản phẩm.
  Bạn sẽ nhận được danh mục sản phẩm được trích xuất từ cột Row Labels bằng cách sử dụng công thức VLookup.

2. Lấy dữ liệu từ file excel khác vba

Bước 1: Mô tả quy trình

• Xác định Workbook cần lấy dữ liệu, gồm 2 phần: Folder chứa file đó và Tên file
• Mở file cần lấy dữ liệu
• Xác định nội dung cần lấy: Ở sheet nào, vùng nào
• Copy dữ liệu
• Paste vào vị trí ở file kết quả: Sheet nào, Range nào, dạng dữ liệu cần lấy là gì
• Đóng file cần lấy dữ liệu

Bước 2: Câu lệnh VBA mẫu

1

Sub ImportData_Test() ‘Lấy dữ liệu từ 1 Workbook khác 2 Dim owb As Workbook 3 4 Set sh=Sheet1 5 ‘Mở file cần lấy dữ liệu 6 Set owb=Workbooks.Open(“C:\Test\England.xlsm”) 7 ‘C:\Test\ là thư mục chứa file 8 ‘England.xlsm là tên file, dạng file 9 ‘Copy vùng dữ liệu cần lấy: Vùng A1:F100 trong Sheet Data của file cần lấy dữ liệu 10 owb.Sheets(“Data”).Range(“A1:F100”).Copy 11 ‘Dán vào vị trí cần lấy kết quả: ô A1 trong Sheet1 của Workbook kết quả 12 sh.Range(“A1”).PasteSpecial xlPasteValues 13 sh.Range(“A1”).PasteSpecial xlPasteValues 14 ‘Đóng file cần lấy dữ liệu 15 owb.Close False 16 17

End Sub

3. Update dữ liệu trong pivottable

Bất kỳ thay đổi nào bạn thực hiện đối với tập dữ liệu sẽ không được bảng tổng hợp tự động chọn. Làm mới bảng tổng hợp hoặc thay đổi nguồn dữ liệu để cập nhật bảng tổng hợp với các thay đổi được áp dụng. Sau đây là các cách làm mới Pivot Table trong Excel

Cách thông thường

• Bước 1: Chọn bất cứ ô nào trong Pivot Table
• Bước 2: Nhấn chuột phải và chọn Refresh

Làm mới khi mở file

Ngoài ra bạn cũng có thể thiết lập để một PivotTable cập nhật tự động khi mở file.

• Bước 1: Bấm chuột phải vào một ô trong pivot table
• Bước 2: Nhấn vào PivotTable Options
• Bước 3: Chọn thẻ Data
• Trong mục PivotTable Data, tích vào mục Refresh data when opening the file
• Nhấn OK, để lưu thiết lập của pivot table

Làm mới Pivot Table bằng đồng hồ bấm giờ

Nếu trang tính của bạn có một pivot table dựa trên xử lý phân tích dữ liệu trực tuyến (OLAP-based) bạn cũng có thể cập nhật tự động dựa vào đồng hồ bấm giờ được kết nối với nó.

Ghi chú: Khi bạn tạo pivot table, nếu bạn thêm dữ liệu của nó vào Dữ liệu mẫu, pivot table của bạn được dựa trên xử lý phân tích dữ liệu trực tuyến.