Cách sử dụng Pivot Tables để lập báo cáo thống kê trong Excel

Pivot Table là công cụ giúp tóm tắt, phân tích và trực quan hóa dữ liệu nhanh chóng trong Excel. Người dùng có thể sử dụng Pivot Tables để lập báo cáo thống kê, biến dữ liệu thô thành báo cáo P&L, so sánh số liệu thực tế và ngân sách, thiết kế dashboard, cũng như tự động cập nhật dữ liệu chỉ với vài thao tác kéo – thả.

I. Pivot table và cách sử dụng Pivot Table để lập báo cáo thống kê hiệu quả

1. Pivot Table và cách sử dụng trong Excel

Pivot Table là công cụ giúp tóm tắt, phân tích và trực quan hóa dữ liệu nhanh chóng trong Excel. Người dùng có thể biến dữ liệu thô thành báo cáo P&L, so sánh số liệu thực tế và ngân sách, thiết kế dashboard, cũng như tự động cập nhật dữ liệu chỉ với vài thao tác kéo – thả.

2. Ứng dụng Pivot Table trong báo cáo tài chính (Case P&L)

Cùng đi qua một quy trình rất thực tế trong phân tích tài chính bằng Excel: Từ một bảng dữ liệu thô ban đầu, làm thế nào để xây dựng một báo cáo lãi lỗ (P&L – Profit & Loss) có thể tương tác theo thời gian, theo khu vực, và đặc biệt là so sánh giữa số liệu thực tế và ngân sách (Actual vs Budget)?

Đây là một trong những kỹ năng nền tảng và quan trọng nhất khi bạn sử dụng Excel để làm báo cáo phân tích tài chính, đặc biệt nếu bạn làm việc trong các phòng ban như Finance, Planning hoặc Business Intelligence.

Hãy biến từ một bảng dữ liệu tài chính thô ban đầu như thế này:

Xây dựng thành một báo cáo lãi lỗ (P&L – Profit & Loss) thế này:

• có thể tương tác theo khu vực
• có thể tương tác theo thời gian mong muốn
• và đặc biệt là so sánh giữa số liệu thực tế và ngân sách (Actual vs Budget)?

Việc chuyển đổi từ dữ liệu thô sang báo cáo trực quan là một trong những kỹ năng trong phân tích tài chính quan trọng nhất bạn sẽ cần thành thạo.

Để làm được file này, ta sẽ đi qua 4 bước chính:

1. P&L Pivot Table – Tạo bảng Pivot P&L
2. Dynamic P&L – Biến bảng đó thành báo cáo tương tác
3. Actual vs Budget – Thêm phần so sánh ngân sách – thực tế
4. Dashboard Design – Thiết kế giao diện dashboard

Toàn bộ quy trình có thể hoàn thành chỉ trong 10 phút.

II. Hướng dẫn các bước để lập báo cáo thống kê bằng Pivot Table

Bước 1: P&L Pivot Table – Tạo bảng Pivot P&L

Chuẩn bị dữ liệu và tạo Pivot Table

Cùng xem xét trước Dữ liệu đầu vào là dữ liệu tài chính của một công ty sản xuất ô tô. Trong bảng ta có các cột sau:

• Date – ngày giao dịch
• Region – khu vực
• Account – tài khoản kế toán
• Description – mô tả
• Actual – giá trị thực tế
• Budget – giá trị ngân sách dự kiến

Đầu tiên, chuyển vùng dữ liệu thành Table bằng cách nhấn Ctrl + T. Sau đó, tạo Pivot Table từ Table này thông qua tab Insert → Pivot Table → chọn đặt vào một worksheet mới.

Tiếp theo, xây dựng cấu trúc P&L trong Pivot Tavle Fields:

• Bạn đưa Account vào Row Labels
• Và tiếp tục đưa Budget và Actual vào Values

Bây giờ hãy nhìn qua phần Pivot Table ở đây, bạn thấy rằng thứ tự các trường thông tin chưa được sắp xếp đúng theo báo cáo P&L. Vậy việc tiếp theo bạn cần làm là kéo và sắp xếp lại các dòng như mẫu ở đây

Sắp xếp cấu trúc P&L trong Pivot Table

Bạn sẽ làm như sau:

• Trên cùng của bảng P&L sẽ là Revenue, là hàng này. Vậy bạn click chọn vào ô Revenue -> dùng chuột Hover lên phần viền khung này, và drag and drop Revenue lên trên cùng. Rất là dễ dàng,
• Bạn sẽ làm tương tự cho các trương thông tin Net Income, chọn và drag and drop xuống cuối cùng

Tiếp theo, để ý bảng của chúng ta đang có Grand Total ở bên phải và dưới cùng, và trong P&L ta không cần tới nó. Vậy chúng ta sẽ bỏ Grand Total cho bố cục chuẩn nhất:

• Ta vào Design → Grand Totals → Off for Rows and Columns.

Vậy là bạn đã có bảng Pivot Table này đúng thứ tự trong cấu trúc báo cáo lãi lỗ P&L.

Hoàn thiện báo cáo P&L chi tiết

Hiện trong Pivot Table hiện tại, chúng ta mới chỉ hiển thị tên tài khoản kế toán tổng hợp như Revenue, Cost of Goods Sold, hay Operating Income… Bây giờ, ta sẽ làm báo cáo này giống một báo cáo P&L thực thụ hơn bằng cách thêm một trường thông tin: đó là mô tả tài khoản (Description), là mô tả chi tiết từng dòng nghiệp vụ. Ví dụ như “Doanh thu bán xe”, “Chi phí vật liệu”, “Chi phí nhân sự”…

Vậy bạn sẽ kéo cột “Description” vào bên dưới mục “Account” trong vùng Rows.

Và có ngay Các dòng Doanh thu và Chi phí được phân tách cụ thể, giúp bạn nhìn rõ từng cấu phần của lợi nhuận gộp (Gross Profit), lợi nhuận vận hành (Operating Income), cho đến Net Income.

Để ý là một số mục tổng như ở đây là Net Income,Operating Income và Gross Profit đang hiển thị lặp lại. Vậy bạn thu gọn những mục này bằng cách nhấn vào dấu trừ (-) phía trước dòng tổng. Việc này giúp trình bày báo cáo gọn gàng và rõ ràng hơn – chỉ giữ lại cấp độ cần thiết khi phân tích.

Để tối ưu bố cục hiển thị, bạn thấy trên cùng của Pivot Table đang có dòng chữ “Column Labels” hay “Row Labels”. Những phần này không cần thiết nên chúng ta sẽ remove bằng cách:

• vào tab PivotTable Analyze -> Bỏ chọn Field Headers
• Và chọn các cột và kéo giãn ra để cân chỉnh đẹp hơn

Có thể thấy bố cục đã chuyên nghiệp hơn rất nhiều.

Và đây chính là kiểu trình bày được sử dụng khi gửi báo cáo tới cấp quản lý hoặc dùng cho phân tích nội bộ.

Bước 2: Biến Pivot P&L thành Dashboard tương tác

Tiếp theo, chúng ta sẽ biến báo cáo này trở thành một báo cáo tương tác cho phép người dùng lọc dữ liệu theo từng điều kiện cụ thể như theo khu vực kinh doanh hay theo từng tháng

Trước khi chèn các slicer, bạn sẽ cần mở rộng không gian ở bên trái bảng báo cáo để có chỗ bố trí bộ lọc.

• Chúng ta sẽ Di chuyển sang cột A , nhấn Ctrl + SPace để chọn toàn bộ cột -> Nhấn tổ hợp phím Ctrl + Shift + + hai lần liên tiếp để chèn thêm hai cột trống ở phía bên trái của bảng
• Bạn có thể kéo rộng hai cột này để tạo khoảng trống đủ lớn. Giúp khi chèn slicer vào sẽ không bị chồng lên phần dữ liệu.

Tiến hành thêm các slicer cho báo cáo.

• Click vào 1 ô bất kỳ bên trong Pivot Table -> Vào tab PivotTable Analyze → chọn Insert Slicer
• Bảng pop up sẽ hiện ra, hãy tick chọn 2 trường thông tin muốn lọc làm Slicer:
  • Chọn Region → để lọc theo khu vực địa lý như Asia, Europe, Americas
  • Chọn Date → để lọc theo tháng hoặc thời gian
  • Nhấn OK, và bạn sẽ thấy ngay hai slicer được tạo ra. Đây chính là công cụ giúp người dùng tương tác với báo cáo theo điều kiện lọc mà họ mong muốn.
  • Tiếp theo, bạn cần Kéo hai slicer vừa tạo sang phần trống đã chừa sẵn ở đây. Sau đó dùng chuột để resize lại cho cân đối với Dashboard.

Và Trước khi hoàn thành bước 2 này, hãy thử nhấn vào một tùy chọn trong slicer để kiểm tra:

• Click vào “Americas” trong slicer Region. Chúng ta sẽ thấy toàn bộ dữ liệu trong bảng P&L cập nhật lại, chỉ hiển thị thông tin tài chính liên quan đến khu vực Americas.
• Tiếp theo, thử chọn một tháng cụ thể như “Mar” trong slicer Date. Bảng sẽ hiển thị dữ liệu của riêng tháng 3.

Tips cho bạn ở đây là:

• Ta có thể chọn nhiều tháng cùng lúc bằng cách giữ Ctrl rồi chọn thêm từng tháng mong muốn, ví dụ: January, February, March
• Để xoá bộ lọc, chỉ cần click vào dấu “X” ở góc trên bên phải của mỗi slicer là được

Bước 3: Thêm phần So sánh Thực tế vs Ngân sách (Actual vs Budget)

Trở lại Pivot Table. Thường bạn sẽ muốn so sánh giữa thực tế so với ngân sách ở ngay trong P&L Statement này.

Vậy bạn sẽ Thêm các cột tính toán chênh lệch như sau:

• Vào: PivotTable Analyze → Fields, Items, & Sets → Calculated Field

Trong hộp thoại hiện ra, bạn sẽ tạo lần lượt:

• Trường 1: Difference ($) – chênh lệch giá trị
  • Gõ tên: Difference ($)
  • Công thức: = Actual - Budget vàBạn có thể click đúp vào từng trường để thêm vào công thức -> Nhấn Add để thêm trường thông tin.
• Trường 2: Difference (%) – chênh lệch phần trăm
  • Gõ tên: Difference (%)
  • Công thức đo phần trăm vượt/thiếu so với kế hoạch : = (Actual / Budget) - 1 -> Nhấn Add, sau đó nhấn OK

Sau khi 2 trường mới xuất hiện trong Pivot Table, chúng ta cần định dạng lại cho đúng chuẩn báo cáo tài chính

Bước 4: Thiết kế giao diện báo cáo (Dashboard Design)

Việc sắp xếp gọn gàng sẽ giúp báo cáo mang phong cách dashboard chuyên nghiệp. Giống như các phòng phân tích tài chính hoặc BI (Business Intelligence) thường sử dụng.

Gridlines và Header

• Dọn sạch giao diện Dashboard ở đây bằng cách Bỏ Gridlines. vào View → bỏ chọn Gridlines
• Sau đó Thêm tiêu đề báo cáo bằng cách tô màu hàng A màu xanh, -> sau đó Vào Insert → Text Box -> Gõ: “Profit & Loss Statement”
• Căn trái và chỉnh màu chữ trắng, fill nền transparent, No outline
• Và ở Header, cần có Logo công ty. Hãy chèn logo bên góc trái hoặc phải tuỳ theo style của bạn
• Nên điều chỉnh nhãn “Sum of Actual” bằng cách đặt tên khác là “Budget” và “Actual” để bảng đúng chuẩn nhất. Click chọn, và Gõ lại tên mới, nhưng lưu ý lúc nào cũng thêm 1 spacing ở cuối để cho ô nó khác tên của cột đã có. Sau đó Enter và tên của cột sẽ được thay đổi. Sau đó sẽ làm tương tự với các cột còn lại.

Tô nền slicer:

• Sau đó vào Insert -> Shape -> chọn Rounded rectangle để tạo 1 khung phần chia khu vực Slicer với phần báo cáo P&Lđể nhìn gọn gàng hơn, đặt phía sau slicer .
• Format lại khung này lại với điêu chỉnh lại bo góc, Fill nền màu xám nhạt. Và chọn no Outline để bỏ viền. và thêm shadow

Tuỳ chỉnh giao diện slicer:

• Để điều chỉnh giao diện Sliver, bạn có thể click chọn Slicer -> Vào tab Design và chọn template phù hợp.
• Bạn cũng có thể tuỳ chỉnh màu của Slicer bằng cách click phải -> chọn Duplicate để tạo template mới và tuỳ chỉnh theo hướng dẫn trong đó.

Cuối cùng, format lại số liệu cho chuyên nghiệp:

• Click phải vào bảng số liệu → chọn Value Field Settings → click chọn Number Format
• Trong bảng popup, chọn định dạng Number, click ở đây để thêm dấu phẩy phân cách hàng nghìn, và bỏ phần thập phân bằng cách chuyển về 0, click ok, và OK

Vậy là chúng ta đã hoàn thành báo cáo P&L đầy đủ: gồm số liệu Actual, Budget, và chênh lệch. Và người dùng Có thể lọc theo tháng, khu vực

Bước 5: Cập nhật dữ liệu động

Gả sử bạn vừa nhận được số liệu tài chính mới cho tháng 7. Làm sao để cập nhật báo cáo mà không cần làm mọi thứ lại từ đầu?

Với Dashboard, thì việc tự động cập nhật báo cáo khi có số liệu mới rất dễ dàng. Chúng ta chỉ cần Quay lại bảng dữ liệu

• Thử thêm dòng nhanh bằng cách Copy một dòng dữ liệu bất kỳ có sẵn
• Paste xuống cuối bảng
• Sau đó thay đổi cột Date thành tháng 7
• Sau đó quay lại Dashboard → vào tab PivotTable Analyze → chọn Refresh

Ngay lập tức, bạn sẽ thấy slicer Date tự động cập nhật thêm mục “July 2024”. Chỉ cần click vào đó, và báo cáo P&L của tháng mới sẽ hiện ra.

Vậy là chúng ta đã hoàn thành Dashboard P&L để sử dụng từ giờ về sau.

III. Thành thạo sử dụng Pivot Tables để lập báo cáo thống kê và các công cụ phân tích dữ liệu cùng ACE

Bài viết này đã giúp bạn hiểu được chi tiết cách sử dụng pivot tables để lập báo cáo thống kê. Tuy nhiên đây mới chỉ là một phần nhỏ trong thế giới phân tích dữ liệu thú vị đang chờ bạn tìm hiểu. Để thành thạo Pivot Table nói riêng, và các công cụ Excel, Power BI nói chung, việc lựa chọn đúng các khóa học cần thiết cho bước khởi đầu là vô cùng quan trọng.

Nếu bạn là newbie và chưa biết nên lựa chọn khóa học nào phù hợp, tham khảo ngay các khóa học Phân tích dữ liệu Advanced Data Analytics in Excel và Data Analysis for Business Professional tại ACE Academy. Khóa học không chỉ đào tạo về tư duy xử lí số liệu, mà còn trang bị cho bạn mọi kĩ năng về phân tích và trực quan hóa dữ liệu, ứng dụng ngay vào công việc chỉ sau vài buổi học đầu tiên.

Nếu bạn đang tìm kiếm một khóa học với nhiều case study thực tế, thực hành xuyên suốt các buổi học, đây cũng là sự lựa chọn tuyệt vời dành cho bạn.

Tham gia các khóa học thực chiến tại ACE để trang bị kĩ năng cho thời đại AI bùng nổ.

IV. FAQ – Câu hỏi thường gặp khi sử dụng Pivot Tables để lập báo cáo thống kê trong Excel

About the author

Alice Mai

Founder & Lead Instructor of ACE Academy

Cô Alice là founder tại ACE Academy – nền tảng đào tạo kỹ năng phân tích dữ liệu và tư duy kinh doanh thực chiến. Cô đồng thời là chuyên gia tư vấn tại ACE Partners Consulting & 180 Systems Canada, tập trung vào chiến lược, digital marketing và chuyển đổi số cho các doanh nghiệp. Với hơn 15 năm kinh nghiệm tại các tập đoàn đa quốc gia như TikTok, Lazada và ACFC, Alice kết hợp giữa tư duy lãnh đạo và công nghệ để kiến tạo giải pháp giáo dục phù hợp cho thế hệ trẻ trong thời đại AI.

Linkedin Threads Facebook