Hướng dẫn cách làm báo cáo tổng hợp trên Excel từ A đến Z

Một trong những yêu cầu thường gặp trong Excel đó là bạn phải làm báo cáo. Có rất nhiều loại, nhiều kiểu báo cáo khác nhau, trong đó báo cáo tổng hợp là dạng hay sử dụng nhất. Vậy thì báo cáo tổng hợp là dạng báo cáo như thế nào? Chúng ta cần chú ý những gì khi làm báo cáo tổng hợp? Trong bài viết này Gitiho sẽ hướng dẫn các bạn cách làm báo cáo tổng hợp trên Excel từ A đến Z.

Tham khảo: Power Query là gì? Ứng dụng của Power Query trong Excel

Khái niệm về báo cáo tổng hợp

Báo cáo tổng hợp là việc thống kê, tổng kết từ số liệu chi tiết trong bảng dữ liệu. Do đó kết quả trong báo cáo thường không có sẵn mà phải qua quá trình tính toán mới có được.

Trong thực tế khi nhận được yêu cầu làm báo cáo, bạn sẽ thấy yêu cầu thường chỉ nói tên báo cáo chứ không nói cụ thể đó là loại báo cáo tổng hợp hay chi tiết. Do đó bạn cần căn cứ vào kết quả cần đạt được của báo cáo để biết đó là loại báo cáo gì. Với mỗi loại báo cáo sẽ có cách làm khác nhau.

Để làm được báo cáo tổng hợp, chúng ta cần có các kỹ năng sau:

• Xây dựng bố cục báo cáo: gồm các thành phần và các chiều của báo cáo.
• Định dạng, trình bày báo cáo cho đẹp.
• Sử dụng hàm hoặc sử dụng công cụ PivotTable để tính ra kết quả báo cáo.
• Kiểm tra tính chính xác của báo cáo
• Tối ưu báo cáo: tự động cập nhật, tăng tốc độ tính toán…

Nếu bạn là quản lý hay người thường xuyên phải làm báo cáo, nhưng thành phẩm làm ra vẫn còn sơ sài, chỉ có một mình mình hiểu được, không trực quan, hay không biết làm như thế nào, bạn có thể tham khảo khóa học sau của Gitiho:

EXG07: Trực quan Báo cáo, Dữ liệu với Biểu đồ, Đồ thị bằng Excel (Excel Data Visualization)

G-LEARNING

499,000^đ 799,000^đ

Đăng ký Học thử

Bố cục của báo cáo tổng hợp

Báo cáo tổng hợp thường có bố cục gồm 7 phần như hình dưới đây:

Bố cục thông thường của báo cáo tổng hợp

Trong đó:

• (1): Tên cơ quan, nơi làm việc có thể kèm theo logo
• (2): Tên của báo cáo
• (3): Điều kiện của báo cáo
• (4) Dòng tiêu đề của báo cáo
• (5) Các dòng nội dung
• (6): Dòng tổng cộng
• (7): Người ký duyệt nội dung báo cáo

Báo cáo có thể giản lược đi các mục (1), (3), (7), nhưng bắt buộc phải có các mục (2), (4), (5), (6).

Ngoài ra khi nhắc tới bố cục báo cáo, bạn cần nắm rõ thêm 1 khái niệm nữa là chiều của báo cáo. Chiều của báo cáo là xu hướng thay đổi kích thước báo cáo. Để mô tả chiều của báo cáo, chúng ta cùng xem hình bên dưới:

• (1) chiều Row: nội dung tăng dần theo dòng (khi thêm nội dung sẽ tăng thêm dòng). Mục này tương ứng với phần 5_ các dòng nội dung báo cáo
• (2) chiều Column: nội dung tăng dần theo cột (khi thêm nội dung sẽ tăng thêm cột). Mục này tương ứng với phần 4_ dòng tiêu đề của báo cáo
• (3) chiều Filter: giới hạn phạm vi tính toán kết quả báo cáo. Mục này tương ứng với phần 3_ vùng điều kiện của báo cáo

Khi nắm rõ bố cục này thì việc xây dựng báo cáo sẽ trở nên đơn giản hơn nhiều.

Quy trình xây dựng báo cáo tổng hợp trên Excel

Khi làm báo cáo trên Excel, việc khó nhất với mọi người hầu hết là không biết xây dựng bố cục như thế nào. Bởi chúng ta chỉ nhận được yêu cầu là “Hãy làm báo cáo…” hoặc “Báo cáo cho tôi nội dung ...” chứ không có sẵn mẫu báo cáo. Công việc của người làm báo cáo phải tự xây dựng cấu trúc báo cáo cho phù hợp để đưa ra kết quả chính xác, dễ xem, dễ hiểu nhất có thể.

Vậy khi nhận được các yêu cầu báo cáo thì chúng ta sẽ bắt đầu từ đâu? Hãy cùng tìm hiểu về quy trình xây dựng báo cáo nhé:

Bước 1: Nhận yêu cầu báo cáo. 

Ví dụ: Báo cáo biến động nhân sự trong tháng

Bước 2: Xác định những từ khóa quan trọng trong yêu cầu

Các từ khóa này thường gắn với các câu hỏi để làm rõ hơn yêu cầu báo cáo:

• Cái gì, Ở đâu (Đối tượng)
• Khi nào, thời gian nào (Thời gian)
• Như thế nào (Cách tính)
• Như trong ví dụ trên ta thấy: Báo cáo biến động nhân sự trong tháng
• Cái gì, ở đâu: Nhân sự. Nhưng không nói rõ nhân sự ở đâu, bộ phận nào
• Thời gian nào: Trong tháng
• Như thế nào: Biến động được hiểu như thế nào? Là số nhân sự có mặt đầu tháng, tăng thêm trong tháng, giảm trong tháng và có mặt ở cuối tháng.

Bước 3: Xác định đối tượng trong các chiều của báo cáo

• Để thể hiện số người có mặt Đầu tháng, Tăng, giảm, có mặt cuối tháng => Cần có 4 cột => Đây là dòng tiêu đề của báo cáo (chiều 2)
• Không nói rõ nhân sự ở đâu => Có thể báo cáo theo nhân sự trong các bộ phận của công ty => Đây là nội dung của báo cáo (chiều 1)
• Thời gian là trong tháng => 1 năm có 12 tháng => Có thể chọn 1 tháng bất kỳ để báo cáo => Đây là điều kiện của báo cáo = > đưa vào chiều thứ 3

Như vậy báo cáo có cấu trúc như sau:

Cấu trúc báo cáo biến động nhân sự

Có thể bạn sẽ thắc mắc: tôi có thể đưa ra cấu trúc khác được không?

Câu trả lời là Có. Bạn hãy thử đưa ra một số cấu trúc mà bạn thấy có thể tạo được. Sau đó so sánh các cấu trúc báo cáo với nhau để tìm ra cấu trúc phù hợp nhất với mục tiêu báo cáo.

Ví dụ chúng ta có thể đưa Tháng vào chiều thứ 1, Phòng ban vào chiều thứ 3 thì báo cáo sẽ có dạng như sau:

Cấu trúc báo cáo biến động nhân sự theo cách khác

Hai dạng này đều có cấu trúc tốt và dùng được. Tùy theo mục đích chúng ta muốn thể hiện là theo 1 phòng ban hay theo 1 tháng cụ thể.

Nếu chiếu theo yêu cầu báo cáo không nói rõ phòng ban nào thì chúng ta nên thể hiện hết các phòng ban. Như vậy khả năng báo cáo theo hình dạng ban đầu sẽ phù hợp hơn.

Tiêu chuẩn định dạng báo cáo tổng hợp

Các tiêu chuẩn định dạng báo cáo tổng hợp

Xin bạn hãy nhớ kỹ điều sau đây: Báo cáo làm ra là để người khác xem, bởi vậy tính thẩm mỹ rất quan trọng. Báo cáo có đẹp, dễ xem thì mới khiến người khác quan tâm. 

Vậy thế nào là đẹp? Xét về thẩm mỹ, mỗi người sẽ có 1 gu thẩm mỹ riêng: về bố cục, về màu sắc, về cách trang trí, định dạng, kẻ khung, cỡ chữ… chưa kể với từng mục đích báo cáo khác nhau thì tiêu chuẩn thẩm mỹ cũng khác. Ở đây chúng ta sẽ cùng tham khảo 1 tiêu chuẩn định dạng mà tác giả hay sử dụng. Dựa vào đây bạn có thể điều chỉnh lại cho phù hợp.

Các nguyên tắc chung trong trình bày báo cáo

Trong những nội dung nêu trên có thể rút ra một số nguyên tắc chung:

• Phối màu: Dùng thống nhất 1-2 màu trong báo cáo, hạn chế sử dụng nhiều màu sắc. Chỉ nên thay đổi về độ đậm nhạt của màu đang sử dụng.
• Kẻ khung: hạn chế kẻ đường viền cả dòng & cột trong bảng báo cáo, bởi nó gây ra cảm giác dữ liệu bị đóng khung, cứng nhắc và rối mắt hơn.
• Tỷ lệ độ rộng của dòng phù hợp với cỡ chữ: Thay đổi tùy theo tính quan trọng của nội dung cần thể hiện. Trong đó nhấn mạnh vào Tên báo cáo, dòng tiêu đề và dòng tổng cộng.
• Điều kiện báo cáo không được phép nhập sai, do đó cần có sự kiểm soát việc nhập dữ liệu với Data Validation.

Bạn hãy tham khảo nhiều mẫu báo cáo khác nhau và học tập cách định dạng theo những báo cáo mà bạn cho là đẹp. Điều đó sẽ giúp ích rất nhiều cho bạn  trong quá trình làm báo cáo trên Excel.

Cách viết công thức tính kết quả báo cáo tổng hợp

Sau khi đã xây dựng xong bố cục và trình bày, định dạng báo cáo, chúng ta sẽ tiến hành viết công thức (hàm) để tính ra kết quả cho báo cáo. Việc này được thực hiện qua các bước sau đây:

Bước 1: Xác định các điều kiện cần tính và các điều kiện này nằm ở cột nào trong bảng dữ liệu.

• Các điều kiện xác định dựa trên các chiều của báo cáo.
• Các cột trong bảng dữ liệu chứa điều kiện cần xét các yếu tố:
• Được đặt tên chưa (Define Name)? 
• Phạm vi vùng Refer to của Name Range có bằng nhau không?
• Nội dung đã chính xác chưa?
• Đang ở dạng dữ liệu nào, có thống nhất về định dạng không?

Tham khảo: Cách đặt tên và sử dụng Name Range trong Excel

Bước 2: Xác định kết quả cần tính là dạng thống kê (đếm) hay tính tổng để chọn hàm phù hợp

• Để làm báo cáo thống kê, dùng hàm COUNTIFS
• Để làm báo cáo tổng hợp (tính tổng), dùng hàm SUMIFS

Hãy tìm hiểu kỹ 2 hàm này bởi bạn có thể làm được hầu hết các báo cáo chỉ với 2 hàm này.

Bước 3: Viết hàm tính toán

Chú ý viết đúng theo cấu trúc quy định của hàm.

• Các vùng Range (Sum_Range, Criteria_Range) sẽ tham chiếu tới bảng dữ liệu (có thể gọi theo tên đã đặt)
• Các Criteria (Criteria1, Criteria2...) sẽ tham chiếu tới vị trí điều kiện đặt tại báo cáo (trong các chiều của báo cáo)

Bước 4: Cố định phần điều kiện của hàm

Cố định tọa độ là việc đặt dấu $ vào trước các chỉ số dòng, cột trong tọa độ tham chiếu. Dấu $  đặt trước nội dung nào thì sẽ cố định nội dung đó.

Ví dụ: tham chiếu tới ô B2 có thể có các cách cố định như sau:

• $B$2: Cố định toàn bộ cả cột B và dòng 2.
• $B2: chỉ cố định cột B, không cố định dòng 2.
• B$2: chỉ cố định dòng 2, không cố định cột B.

Các vùng Range thường được gọi theo tên đã đặt tên không cần cố định. Nếu tham chiếu bằng tọa độ thì phải cố định toàn bộ.

Các điều kiện (Criteria) sẽ cố định theo nguyên tắc

• Điều kiện đặt tại chiều 1 (vị trí số 5 - nằm trên cùng 1 cột) sẽ cố định về cột
• Điều kiện đặt tại chiều 2 (vị trí số 4, dòng tiêu đề - nằm trên cùng 1 dòng) sẽ cố định về dòng
• Điều kiện đặt tại chiều 3 (vị trí số 3 - nằm ngoài phạm vi bảng) sẽ cố định toàn bộ (cả dòng và cột)

Tham khảo: Cách sử dụng phím F4 để cố định tọa độ trong Excel

Bước 5: Kéo công thức áp dụng cho toàn bộ những ô có cách tính giống nhau (chỉ khác về điều kiện tính)

Bước 6: Tại các dòng tính tổng (theo nhóm hoặc toàn bộ) sử dụng hàm Subtotal

Lưu ý:

• Lặp lại từ bước 1 với cột có yêu cầu tính toán khác
• Điều kiện thay đổi về dấu (>, <, >=, <=, =, <>)
• Vùng tính tổng khác hoặc vùng chứa điều kiện khác (Range có thay đổi)
• Yêu cầu tính toán thay đổi (giữa thống kê với tính tổng)

Cách kiểm tra kết quả của báo cáo tổng hợp

Sau khi làm ra kết quả cho báo cáo, bạn nên có thói quen kiểm tra lại. Việc này giúp bạn tránh được các lỗi và rèn tính cẩn thận, bởi một khi báo cáo đã gửi đi thì bạn không thể sửa lại lỗi sai được nữa. Dù bạn có sửa lại thì họ vẫn ghi nhớ rằng bạn từng làm sai và đánh giá bạn thiếu cẩn thận. 

Các lỗi hay gặp khi làm báo cáo tổng hợp:

1. Chưa cố định điều kiện trong công thức (hoặc cố định sai) => Khi kéo công thức (Fill) cho những ô tương tự sẽ ra kết quả sai.

2. Dữ liệu chưa được làm sạch, vẫn còn lỗi nên dù công thức viết đúng cũng không ra kết quả đúng.

3. Cấu trúc báo cáo chưa đúng => không xác định được điều kiện của báo cáo là gì

Phương pháp kiểm tra: 

Sử dụng Filter để kiểm tra ngẫu nhiên 1 kết quả tính toán để xem công thức tính đúng chưa

Bước 1: Tại bảng dữ liệu cần áp dụng bộ lọc Filter

Bước 2: Lọc trong các cột theo điều kiện được dùng để kiểm tra

Bước 3: Trong cột chứa dữ liệu tính tổng (với yêu cầu tính tổng) hoặc 1 cột bất kỳ (với yêu cầu thống kê) chọn toàn bộ dữ liệu trong cột đó để kiểm tra kết quả của mục Count hoặc Sum (phần phía dưới bên phải thanh Sheet Tab)

Vị trí kết quả dùng để kiểm tra

Kết luận

Sau khi tìm hiểu các nội dung trên, chúng ta có thể rút ra một số bài học như sau:

1. Báo cáo tổng hợp rất quan trọng và là dạng phổ biến nhất. Bạn cần nắm vững kỹ năng làm báo cáo tổng hợp và thực hành nhiều để thành thạo.
2. Đừng quên việc kiểm tra lại kết quả báo cáo. Việc này không tốn nhiều thời gian nhưng lại vô cùng quan trọng. Đừng gửi báo cáo cho người khác khi chưa kiểm tra lại ít nhất 1 lần.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS... Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table… hay kỹ năng làm báo cáo tổng hợp, kỹ năng làm báo cáo chi tiết... Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EXG01 - Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ. Hãy nhanh tay đăng ký để nhận được nhiều ưu đãi hấp dẫn.