Bảng tổng hợp là một công cụ báo cáo tích hợp tuyệt vời trong Excel. Mặc dù nó thường được sử dụng để tóm tắt dữ liệu với tổng số, nhưng nó cũng có thể được sử dụng để tính phần trăm thay đổi giữa các giá trị. Thậm chí tốt hơn: nó rất dễ thực hiện.
Bạn có thể sử dụng kỹ thuật này để thực hiện tất cả mọi thứ – gần như ở bất kỳ đâu bạn muốn thấy giá trị này so với giá trị khác. Trong bài viết này, chúng tôi sẽ sử dụng một ví dụ đơn giản để tính toán và hiển thị tỷ lệ phần trăm mà tổng doanh số bán hàng thay đổi từ tháng này sang tháng khác.
Đây là trang tính chúng tôi sẽ sử dụng.
Đây là một ví dụ khá điển hình về bảng bán hàng bao gồm ngày đặt hàng, tên khách hàng, đại diện bán hàng, tổng giá trị bán hàng và một số thứ khác.
Để thực hiện tất cả điều này, trước tiên, chúng tôi sẽ định dạng phạm vi giá trị của mình dưới dạng bảng trong Excel, sau đó tạo PivotTable để thực hiện và hiển thị các phép tính phần trăm thay đổi của chúng tôi.
Định dạng một phạm vi dưới dạng bảng
Nếu phạm vi dữ liệu của bạn chưa được định dạng dưới dạng bảng, chúng tôi khuyên bạn nên làm như vậy. Dữ liệu được lưu trữ trong bảng có nhiều lợi thế hơn dữ liệu trong phạm vi ô của trang tính, đặc biệt là khi sử dụng bảng tổng hợp (đọc thêm về lợi ích của việc sử dụng bảng).
Để định dạng một phạm vi dưới dạng bảng, hãy chọn một phạm vi ô và nhấp vào Chèn > Bảng.
Xác minh rằng phạm vi là chính xác, rằng bạn có các tiêu đề trong hàng đầu tiên của phạm vi này, sau đó nhấp vào “OK”.
Phạm vi hiện được định dạng dưới dạng bảng. Việc đặt tên cho bảng sẽ giúp bạn tham khảo bảng dễ dàng hơn trong tương lai khi tạo bảng tổng hợp, biểu đồ và công thức.
Nhấp vào tab “Thiết kế” bên dưới Công cụ Bảng và nhập tên vào hộp ở đầu Dải băng. Bảng này được đặt tên là “Bán hàng”.
Bạn cũng có thể thay đổi kiểu bảng nếu muốn.
Tạo một bảng tổng hợp để hiển thị phần trăm thay đổi
Bây giờ, hãy chuyển sang tạo bảng tổng hợp. Trong bảng mới, bấm Chèn > PivotTable.
Cửa sổ Tạo PivotTable xuất hiện. Nó sẽ tự động phát hiện bảng của bạn. Nhưng tại thời điểm này, bạn có thể chọn bảng hoặc phạm vi bạn muốn sử dụng cho bảng tổng hợp.
Nhóm ngày thành tháng
Tiếp theo, chúng tôi sẽ kéo trường ngày mà chúng tôi muốn nhóm theo khu vực hàng của bảng tổng hợp. Trong ví dụ này, trường được đặt tên là Ngày đặt hàng.
Bắt đầu từ Excel 2016, các giá trị ngày được tự động nhóm thành năm, quý và tháng.
Nếu phiên bản Excel của bạn không làm được điều này hoặc bạn chỉ muốn thay đổi cách nhóm, hãy nhấp chuột phải vào ô chứa giá trị ngày và sau đó chọn “Nhóm”.
Chọn các nhóm bạn muốn sử dụng. Trong ví dụ này, chỉ năm và tháng được chọn.
Năm và tháng hiện là các trường mà chúng ta có thể sử dụng để phân tích. Các tháng vẫn được gọi là ngày đặt hàng.
Thêm các trường giá trị vào bảng tổng hợp
Di chuyển trường Năm từ Hàng sang vùng Bộ lọc. Điều này cho phép người dùng lọc bảng tổng hợp trong một năm thay vì làm lộn xộn bảng tổng hợp với quá nhiều thông tin.
Kéo trường chứa các giá trị (Tổng Doanh số trong ví dụ này) mà bạn muốn tính toán và biểu thị sự thay đổi sang vùng Giá trị hai lần.
Nó có thể không giống như nó được nêu ra. Nhưng điều đó sẽ sớm thay đổi.
Cả hai trường giá trị sẽ được tính tổng theo mặc định và hiện không có định dạng.
Các giá trị trong cột đầu tiên mà chúng tôi muốn giữ dưới dạng tổng. Tuy nhiên, chúng yêu cầu định dạng.
Nhấp chuột phải vào số trong cột đầu tiên và chọn “Định dạng số” từ trình đơn lối tắt.
Chọn định dạng “Kế toán” từ 0 vị trí thập phân từ hộp thoại Format Cells.
Bảng tổng hợp bây giờ trông như thế này:
Tạo cột Tỷ lệ phần trăm thay đổi
Nhấp chuột phải vào giá trị trong cột thứ hai, trỏ tới “Hiển thị giá trị” rồi nhấp vào “Phần trăm khác biệt so với”.
Chọn “(Trước)” làm mục cơ sở. Điều này có nghĩa là giá trị tháng hiện tại luôn được so sánh với giá trị tháng trước (trường Ngày đặt hàng).
Crosstab hiện hiển thị cả giá trị và phần trăm thay đổi.
Nhấp vào ô chứa nhãn hàng và nhập “Tháng” làm tiêu đề cho cột đó. Sau đó nhấp vào ô tiêu đề của cột giá trị thứ hai và nhập “Biến thể”.
Thêm một số mũi tên phương sai
Để thực sự đánh bóng bảng tổng hợp này, chúng tôi muốn hình dung rõ hơn phần trăm thay đổi bằng cách thêm một số mũi tên màu lục và đỏ.
Chúng sẽ cung cấp cho chúng ta một cách tuyệt vời để xem sự thay đổi đó là tích cực hay tiêu cực.
Bấm vào bất kỳ giá trị nào trong cột thứ hai, sau đó bấm vào Trang chủ > Định dạng có điều kiện > Quy tắc mới. Trong cửa sổ Chỉnh sửa quy tắc định dạng mở ra, hãy làm như sau:
Chọn “Tất cả các ô hiển thị giá trị ‘Biến thể’ cho ngày đặt hàng”.
Chọn “Bộ biểu tượng” từ danh sách Kiểu định dạng.
Chọn các hình tam giác màu đỏ, hổ phách và xanh lục từ danh sách Kiểu biểu tượng.
Trong cột Loại, thay đổi tùy chọn danh sách thành “Số” thay vì Tỷ lệ phần trăm. Điều này sẽ thay đổi cột Giá trị thành 0. Chính xác những gì chúng tôi muốn.
Nhấp vào “OK” và định dạng có điều kiện sẽ được áp dụng cho bảng tổng hợp.
Bảng tổng hợp là một công cụ tuyệt vời và là một trong những cách dễ dàng nhất để hiển thị các thay đổi phần trăm theo thời gian.
