Làm việc với PivotTable trong Excel

Ví dụ thực tế 

Hướng dẫn này sẽ sử dụng một tập dữ liệu về doanh số bán xe đạp toàn cầu từ 2011 đến 2016. Thông tin nhân khẩu học về khách hàng và sản phẩm của tập dữ liệu này được sắp xếp cùng các cột lợi nhuận, chi phí và doanh thu. Bạn sẽ tạo một PivotTable từ tập dữ liệu này để phân tích dữ liệu bên trong. 

Dữ liệu có sẵn tại đây để bạn làm theo. 

Xem xét dữ liệu 

Hãy bắt đầu bằng cách xem một mẫu dữ liệu cho hướng dẫn này. Chúng ta có một bảng gồm 11 cột, bao gồm kiểu ngày tháng, văn bản và số. Từ phần dữ liệu này, ta thấy có nhiều cách để thao tác nhằm thực hiện phân tích và tìm ra những insight hữu ích. 

Tạo PivotTable đầu tiên 

Để tạo PivotTable đầu tiên, hãy chọn bảng bạn muốn dùng để tạo dữ liệu, đi đến thẻ Insert và chọn PivotTable từ các tùy chọn bên dưới.

Sau đó, bạn sẽ thấy một cửa sổ bật lên hỏi liệu bạn có muốn thay đổi phạm vi dữ liệu dùng để tạo PivotTable hay không và bạn muốn đặt PivotTable ở trang tính mới hay trang tính hiện có. Với hướng dẫn này, các tùy chọn mặc định là phù hợp. Nhấp OK để tạo PivotTable trên trang tính mới bên dưới. 

Trang tính mới hiển thị khung PivotTable đã được tạo tự động cho bạn. Bên trái màn hình, bạn có thể thấy đường viền vị trí PivotTable sẽ xuất hiện sau khi được xây dựng. Bên phải, bạn sẽ thấy ngăn trường PivotTable, nơi bạn sẽ thực hiện phần lớn công việc. 

Khi dữ liệu của bạn đã nằm trong một Table trong Excel, PivotTable sẽ tự động bao gồm mọi thứ bên trong. Tuy nhiên, cần lưu ý rằng nếu dữ liệu của bạn không nằm trong phần tử Table, bạn có thể thủ công chọn vùng dữ liệu muốn bao gồm bằng cách bôi chọn dữ liệu rồi tạo PivotTable tương tự như trước. 

Thêm dữ liệu của bạn 

Chúng ta sẽ giả định rằng mỗi hàng gắn với một khách hàng riêng lẻ; do đó không có đơn hàng lặp lại. Đây là cách đơn giản hóa cao bộ dữ liệu, nhưng vì không có định danh khách hàng duy nhất, việc giả định một khách hàng cho mỗi hàng sẽ giúp hướng dẫn này dễ theo dõi hơn. 

Trước tiên, chúng ta sẽ đưa một trường phân loại vào phần Rows. Ta sẽ dùng Country làm danh mục; cần tìm trường này trong danh sách trường của trình chỉnh sửa PivotTable rồi kéo vào phần Rows. Tiếp theo, ta cần xem giá trị muốn đánh giá — trong trường hợp này, ta muốn đếm số khách hàng, nhưng vì không có trường ID, ta có thể dùng trường Customer_Age và kéo vào phần Values. Mặc định phép tổng hợp là SUM, nhưng ta cần đổi thành COUNT. Có thể thực hiện bằng cách nhấp chuột phải vào Customer_Age trong phần Values, chọn Field Settings, đổi Summarise từ Sum sang Count và nhấp OK.

Tuyệt vời! Giờ đây chúng ta có thể thấy số lượng khách hàng theo Country mà họ đặt hàng. Điều này hữu ích, nhưng hãy sắp xếp theo Country có nhiều khách hàng nhất. Để làm vậy, bạn cần nhấp chuột phải vào một giá trị trong cột Count of Customer_Age, chọn Sort, rồi chọn Sort Largest to Smallest. Ta thấy Hoa Kỳ có nhiều khách hàng nhất, với 39.206.

Tiến thêm một bước 

Giờ khi đã quen cách thiết lập PivotTable cơ bản, hãy tiến thêm một bước với việc thêm bộ lọc và cột, cũng như làm mới PivotTable. Hãy xóa các hàng và giá trị đã thêm ở bước trước bằng cách nhấp chuột phải vào chúng và chọn Remove field. Bây giờ ta có một trang trống hoàn toàn để làm việc. 

Với PivotTable này, câu hỏi cần trả lời là: “Năm 2015, quý nào tạo ra doanh thu cao nhất và thuộc về nhóm sản phẩm/phân nhóm nào?” 

Hãy bắt đầu bằng cách thêm các hàng mà ta muốn phân tích dữ liệu theo; ở đây sẽ là Product_Category và Sub_Category. Tiếp theo, ở phần cột, ta muốn xem phân tách theo Quarters. Nhưng khoan, ta không có cột Quarters… Không vấn đề gì; Excel đã tự động nhận diện trường ngày từ dữ liệu, nên khi bạn kéo Date vào phần cột, bạn sẽ thấy xuất hiện hai trường mới: Years và Quarters. Vì không cần xem dữ liệu ở mức ngày riêng lẻ, ta có thể xóa trường Date khỏi phần Columns. 

Vì ta muốn lọc năm 2015 cho câu hỏi này, ta có thể chuyển trường Years vào phần Filter. PivotTable của bạn lúc này sẽ trông như sau:

Hiện chúng ta đã có cấu trúc bảng, có thể chọn trước năm cần lọc bằng cách nhấp vào menu thả xuống của Years và chọn 2015. Đây là cách PivotTable hiển thị lúc này. 

Hiện tại, các điểm dữ liệu của chúng ta đang ở mức đơn lẻ nên đơn giá và đơn chi phí gắn với từng món hàng. Chúng ta muốn tóm tắt thông tin cho đúng, do đó cần rời PivotTable để quay lại bảng dữ liệu.

Trong tab dữ liệu, ta sẽ cần tạo một cột tính toán mới để tính doanh thu cho mỗi hàng. Với phép tính này, ta cần nhân Order Quantity với Unit Price. 

Để thêm cột mới vào dữ liệu, hãy quay lại trang dữ liệu và cạnh cột Unit Price tại ô L2, chúng ta sẽ thêm phép tính mới; công thức sẽ là:

[@[Order_Quantity]]*[@[Unit_Price]]

Bây giờ chúng ta có một cột mới có thể sử dụng trong PivotTable. Quay lại trang chứa PivotTable, bạn sẽ thấy trường này chưa xuất hiện. Để làm mới PivotTable, nhấp chuột phải vào bất kỳ trường nào và chọn Refresh.

Cột mới đã được thêm, và ta có thể kéo nó vào phần Values. Nếu bạn muốn thêm nhiều cột hơn, bạn có thể dùng Excel Cheatsheet để xem các kiểu phép tính khác có thể áp dụng. 

Trông hơi lộn xộn, vậy hãy dọn dẹp bằng cách tắt tất cả tổng phụ và tổng cộng, bạn có thể làm ở thẻ Design. Mở menu thả xuống Grand Totals và chọn Off for Rows and Columns. Tiếp theo, hãy cập nhật các con số sang định dạng tiền tệ. Tô chọn các giá trị trong bảng và ở thẻ Home, cập nhật định dạng sang Currency. 

Tuyệt, bây giờ chúng ta đã có PivotTable hoàn chỉnh. Từ đây, ta thấy Bikes tạo ra doanh thu cao nhất trong năm 2015, đặc biệt là ở quý 4. Ngoài ra, trong các dòng xe đạp mà công ty bán, có thể thấy Road Bikes là phổ biến nhất và mang lại doanh thu cao nhất cho tổ chức.

Tiến xa hơn!

Hướng dẫn này là phần giới thiệu tốt về PivotTable trong Excel; nếu bạn theo kịp một cách dễ dàng, rất tốt! Nếu bạn gặp vướng mắc ở đâu đó, bạn có thể tìm tệp lời giải tại đây. 

Hãy thử nghiệm với một tập dữ liệu phức tạp hơn, áp dụng các thuộc tính khác nhau, thao tác với chúng và xem liệu bạn có thể hiểu được dữ liệu hay không.

Nếu bạn muốn kiểm tra thêm kỹ năng Excel của mình, hãy cân nhắc tham khảo khóa học Data Analysis in Excel, nếu bạn chưa học.