PivotTable là một trong những công cụ mạnh mẽ nhất của Excel để phân tích dữ liệu—chúng giúp bạn tạo bảng thông tin tương tác. Tuy nhiên, hầu hết người dùng hầu như không khai thác hết tiềm năng của họ. Ngoài các phép tính tổng và đếm đơn giản, PivotTable còn cung cấp các tính năng nâng cao có thể biến dữ liệu thô thành các báo cáo chuyên sâu, có tính tương tác.
Trong hướng dẫn này, chúng ta sẽ khám phá 5 thủ thuật PivotTable cần thiết để tìm hiểu sâu hơn về dữ liệu của bạn.
Bộ dữ liệu mẫu (Bảng bán hàng)
Hãy tưởng tượng bạn có dữ liệu bán hàng trong Excel; chúng tôi sẽ sử dụng tập dữ liệu này cho tất cả các ví dụ bên dưới.
Chuyển đổi dữ liệu thành bảng:
- Chọn toàn bộ tập dữ liệu.
- Đi tới Chèn tab>> chọn Bảng .
- Kiểm tra “Bảng của tôi có tiêu đề” .
- Nhấp vào OK .
- Đi tới Thiết kế bảng>> đặt tên bảng là “SalesData”.
Tạo PivotTable cơ bản:
- Nhấp vào bất kỳ ô nào trong SalesData.
- Đi tới Chèn tab>> chọn PivotTable .
- Chọn Bảng tính mới và nhấp vào OK .
Từ Trường PivotTable danh sách:
- Kéo Vùng đến Hàng .
- Kéo Danh mục tới Cột .
- Kéo Doanh thu tới Giá trị .
Bây giờ bạn có báo cáo tiêu chuẩn về doanh thu theo khu vực và danh mục. Chúng tôi sẽ biến nó thành thứ gì đó thông minh hơn nhiều.
Thủ thuật 1:Chèn Slicers để lọc tương tác
Slicers là công cụ lọc tương tác, có thể nhấp vào. Họ có thể biến bất kỳ báo cáo hoặc trang tổng quan tĩnh nào thành báo cáo tương tác. Bộ cắt hiển thị tất cả các tùy chọn lọc dưới dạng nút, giúp việc khám phá dữ liệu trở nên trực quan và nhanh chóng.
Các bước:
- Nhấp vào bất kỳ đâu bên trong PivotTable.
- Đi tới Phân tích PivotTable tab>> chọn Chèn Bộ cắt .
- Chọn Nhân viên bán hàng , Khách hàng và Danh mục , sau đó nhấp vào OK .
Excel sẽ hiển thị ba hộp slicer có các nút.
- Nhấp vào nhân viên bán hàng để lọc PivotTable nhằm chỉ hiển thị dữ liệu của người đó.
- Giữ Ctrl trong khi nhấp để chọn nhiều tên.
- Nhấp vào biểu tượng kênh trong bộ cắt (trên cùng bên phải) để xóa bộ lọc.
Kết nối Bộ cắt với nhiều PivotTable:
Một trong những tính năng mạnh mẽ nhất là khả năng kết nối một slicer với nhiều PivotTable.
- Chọn bộ cắt.
- Nhấp chuột phải>> chọn Báo cáo kết nối .
- Chọn tất cả PivotTable>> nhấp vào OK .
Đây là chìa khóa để xây dựng một bảng điều khiển thực sự trên một trang tính. Bằng cách sử dụng bộ cắt, người quản lý có thể nhanh chóng xem số liệu theo nhân viên bán hàng, khách hàng hoặc sản phẩm mà không cần chạm vào các trường PivotTable.
Tùy chỉnh giao diện bộ cắt:
- Chọn bộ cắt.
- Đi tới Bộ cắt tab xuất hiện.
- Chọn từ nhiều Phong cách máy cắt trong phòng trưng bày.
- Điều chỉnh Cột để hiển thị các nút trong nhiều cột.
- Sử dụng Cài đặt bộ cắt (nhấp chuột phải> Cài đặt bộ cắt) để thay đổi chú thích tiêu đề.
- Thay đổi kích thước và định vị bộ cắt để tạo bố cục trang tổng quan rõ ràng.
Thủ thuật 2:Phân tích xu hướng thời gian ngay lập tức bằng dòng thời gian
Dòng thời gian là các bộ cắt chuyên dụng được thiết kế đặc biệt cho các trường ngày. Bạn có thể lọc dữ liệu PivotTable theo khoảng thời gian, cho phép bạn chuyển đổi nhanh chóng giữa ngày, tháng, quý và năm.
Các bước:
- Nhấp vào PivotTable.
- Đi tới Phân tích PivotTable tab>> chọn Chèn dòng thời gian .
- Chọn Ngày>> sau đó nhấp vào OK .
Bạn sẽ thấy một thanh ngang có phạm vi ngày của bạn.
- Sử dụng menu thả xuống trên Dòng thời gian để chuyển đổi cấp độ giữa Năm, Quý, Tháng và Ngày.
- Kéo tay cầm bên trái hoặc bên phải để phóng to một khoảng thời gian cụ thể.
- Nhấp vào một tháng như tháng 1 năm 2025 để chỉ xem doanh số của tháng đó.
- Xóa bộ lọc bằng cách nhấp vào biểu tượng bộ lọc trong Dòng thời gian.
Bạn có thể xem xét ngay xu hướng bán hàng. Kết hợp với máy thái, bạn có thể trả lời các câu hỏi như “Doanh số bán hàng điện tử ở khu vực phía Bắc của Alex trong tháng 1 là bao nhiêu?” chỉ với vài cú nhấp chuột.
Thủ thuật 3:Nhóm dữ liệu theo độ tuổi hoặc doanh số bán hàng
Việc nhóm cho phép bạn phân loại dữ liệu thành các nhóm lớn hơn, có ý nghĩa hơn. Điều này rất có giá trị trong việc phân tích phân bổ, tạo khung tuổi, phân đoạn theo phạm vi hoặc sắp xếp ngày thành các khoảng thời gian tùy chỉnh.
Nhóm khách hàng thành các nhóm tuổi:
- Tạo PivotTable mới.
- Kéo Tuổi khách hàng đến Hàng .
- Kéo Doanh thu tới Giá trị .
- Nhấp chuột phải bất kỳ độ tuổi nào trong nhãn hàng và chọn Nhóm .
- Trong hộp thoại Nhóm, đặt:
- Bắt đầu lúc: 20
- Kết thúc lúc: 70
- Bởi: 10
- Nhấp vào OK . PivotTable sẽ hiển thị các nhóm tuổi.
Bây giờ bạn có thể quan sát nhóm tuổi nào tạo ra nhiều doanh thu nhất. Đơn hàng lớn nhất đến từ khách hàng lớn tuổi hay trẻ tuổi?
Nhóm Doanh thu vào Phạm vi Số tiền Bán hàng:
- Tạo một PivotTable khác.
- Kéo Doanh thu đến Hàng .
- Kéo Doanh thu tới Giá trị .
- Kéo Vùng tới Cột .
- Nhấp chuột phải vào bất kỳ giá trị doanh thu nào trong nhãn hàng và chọn Nhóm… .
- Đặt giới hạn hợp lý:
- Bắt đầu lúc: 0
- Kết thúc lúc: 40000
- Bởi: 2000
- Nhấp vào OK .
Bạn sẽ thấy các dải sau.
Các mức giá sản phẩm:
- Bắt đầu lúc: 0
- Kết thúc lúc: 500
- Bởi: 100
Ví dụ:0–100 (Bình dân), 100–200 (Bình dân), 200–300 (Tiêu chuẩn), 300–400 (Cao cấp), 400–500 (Cao cấp).
Thủ thuật 4:Xây dựng số liệu tùy chỉnh với các trường được tính toán
Đôi khi các trường thô không đủ. Trường được tính toán cho phép bạn tạo trường mới bằng cách sử dụng công thức dựa trên các trường PivotTable hiện có. Điều này có nghĩa là bạn có thể tính toán lợi nhuận, tỷ suất lợi nhuận, chênh lệch, tỷ lệ chuyển đổi và các số liệu tùy chỉnh khác trực tiếp trong PivotTable của mình—không cần thêm cột trong dữ liệu nguồn.
Các bước:
- Nhấp vào bất kỳ đâu trong PivotTable của bạn.
- Đi tới Phân tích PivotTable tab>> nhấp vào Trường, Mục và Bộ>> chọn Trường được tính toán .
- Trong Chèn trường được tính toán hộp thoại:
- Nhập Tên cho lĩnh vực của bạn (ví dụ:“Biên lợi nhuận”).
- Nhấp vào Công thức vào hộp và tạo công thức của bạn.
- Nhấp đúp vào tên trường từ danh sách Trường để chèn chúng.
- Sử dụng các toán tử chuẩn:+ (cộng), − (trừ), * (nhân), / (chia).
- Nhấp vào OK .
Lợi nhuận:
- Tên: Lợi nhuận
- Công thức:
Tỷ lệ phần trăm biên lợi nhuận:
- Tên: Tỷ suất lợi nhuận
- Công thức:
= (Revenue - Cost) / Revenue
- Định dạng dưới dạng Phần trăm .
Tạo “Lợi nhuận” bằng 30% Doanh thu:
- Tên: Lợi nhuận bằng 30% doanh thu
- Công thức:
Bằng cách sử dụng các trường được tính toán, bạn có thể xác định KPI dành riêng cho doanh nghiệp ngay trong PivotTable. Nếu bạn thêm nhiều hàng hơn vào SalesData và làm mới, các trường được tính toán sẽ tự động cập nhật.
Thủ thuật 5:Xem tỷ lệ phần trăm và tổng số đang chạy với “Hiển thị giá trị dưới dạng”
PivotTable thường hiển thị số liệu thô nhưng thường thì chế độ xem tương đối sẽ sâu sắc hơn. PivotTable cung cấp một số tùy chọn hiển thị, chẳng hạn như phần trăm của tổng số, phần trăm của hàng/cột và tổng số đang chạy theo thời gian.
Phần trăm tổng doanh thu theo khu vực:
- Xây dựng PivotTable với Vùng trong Hàng và Doanh thu trong Giá trị.
- Nhấp chuột phải vào bất kỳ số nào trong cột Doanh thu.
- Chọn Hiển thị giá trị dưới dạng>> chọn % tổng cộng .
Giờ đây, mỗi khu vực hiển thị phần trăm tổng doanh thu mà khu vực đó đóng góp—dễ hiểu hơn nhiều so với số liệu thô khi so sánh các phân khúc.
Bạn cũng có thể chọn:
- % tổng số cột để xem từng vùng dưới dạng phần trăm của cột.
- % tổng số hàng để xem từng danh mục dưới dạng phần trăm trong từng khu vực.
Mẹo:Sử dụng Định dạng PivotTable để có thông tin chi tiết rõ ràng
PivotTable được định dạng phù hợp giúp thông tin chi tiết dễ hiểu hơn.
Thêm định dạng số:
- Nhấp chuột phải vào bất kỳ giá trị nào>> Định dạng số>> chọn Tiền tệ, Tỷ lệ phần trăm, v.v.
Hiển thị 5 người đóng góp hàng đầu:
- Nhấp vào menu thả xuống bên cạnh Nhãn hàng.
- Chọn Bộ lọc giá trị>> chọn Top 10>> chuyển sang Top 5.
- Chọn Tổng doanh thu .
Sử dụng định dạng có điều kiện:
- Chọn các giá trị PivotTable.
- Truy cập Trang chủ tab>> chọn Định dạng có điều kiện .
- Chọn Thang màu, Thanh dữ liệu hoặc Bộ biểu tượng.
Thông tin chi tiết: Làm nổi bật hiệu suất cao nhất giúp nhanh chóng xác định các mặt hàng bán chạy nhất hoặc khu vực mạnh nhất.
Kết hợp tất cả lại với nhau:Bảng điều khiển bán hàng tương tác đơn giản
Bằng cách sử dụng năm thủ thuật này, bạn có thể xây dựng một bảng thông tin nhỏ trên một trang tính duy nhất. Đặt các phần tử lại với nhau trên một trang tính để tạo trang tổng quan gắn kết:
- Doanh thu theo khu vực và danh mục với bộ phân tích dành cho Nhân viên bán hàng và Khách hàng.
- Doanh thu theo Khung tuổi, được nhóm bằng Thủ thuật 3.
- Dòng thời gian kiểm soát phạm vi ngày cho tất cả các PivotTable dựa trên thời gian.
- Bộ cắt được kết nối với tất cả PivotTable bằng cách sử dụng Kết nối báo cáo , do đó, một cú nhấp chuột sẽ thay đổi toàn bộ chế độ xem.
- Các trường được tính toán cung cấp Lợi nhuận và Giá trung bình trên tất cả các báo cáo.
Sau khi thiết lập xong mọi thứ, bạn chỉ cần dán các hàng mới vào SalesData và nhấp vào Làm mới . PivotTable, trường được tính toán, bộ cắt, dòng thời gian và nhóm tự động cập nhật, cung cấp cho bạn thông tin chi tiết mới mà không cần phải xây dựng lại báo cáo của mình.
Kết luận
Bạn có thể sử dụng năm thủ thuật PivotTable này để khám phá những hiểu biết sâu sắc hơn về dữ liệu của mình và chuyển đổi các báo cáo tĩnh thành bảng thông tin phân tích, tương tác. Bằng cách sử dụng bộ cắt, dòng thời gian, trường được tính toán và nhóm, bạn có thể diễn giải các mẫu nhanh hơn nhiều và trình bày những phát hiện của mình một cách rõ ràng. Thực hành với dữ liệu của riêng bạn và thử nghiệm kết hợp chúng trong trang tổng quan của bạn. Nếu phiên bản Excel của bạn thiếu tính năng (Dòng thời gian yêu cầu Excel 2013 trở lên), hãy kiểm tra các bản cập nhật.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!