Hướng dẫn cách làm báo cáo chi tiết 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. Ngoài nhu cầu báo cáo tổng hợp, trong nhiều trường hợp chúng ta còn cần phải trích lọc một số nội dung chi tiết từ bảng dữ liệu. Đây chính là quá trình tạo ra báo cáo chi tiết. Vậy chúng ta hãy cùng tìm hiểu xem báo cáo chi tiết là gì? và vì sao nó lại quan trọng?

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 chi tiết trên Excel từ A đến Z.

Xem thêm: Top khóa học tin học văn phòng nhiều học viên tại Gitiho

Khái niệm về báo cáo chi tiết

Báo cáo chi tiết là quá trình trích lọc những thông tin chi tiết từ bảng dữ liệu ra báo cáo theo một số tiêu chí nhất định. Kết quả của báo cáo chi tiết thường có sẵn trong bảng dữ liệu, chúng ta chỉ việc trích lọc các nội dung đó ra mà thôi.

Trong thực tế có nhiều trường hợp báo cáo yêu cầu vừa tổng hợp, vừa chi tiết trên cùng 1 mẫu báo cáo. Khi đó chúng ta sẽ cần xác định rõ trong bố cục báo cáo phần nào là tổng hợp, phần nào là chi tiết để có phương pháp làm phù hợp.

Để làm được báo cáo chi tiết, 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 tính năng của báo cáo
• Định dạng, trình bày báo cáo cho đẹp
• Sử dụng kỹ thuật trích lọc (hoặc đối chiếu) những dữ liệu thỏa mãn điều kiện.
• 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 báo cáo mỗi khi thay đổi điều kiện.

Bố cục của báo cáo chi tiết trên Excel

Báo cáo chi tiết thường có bố cục như sau:

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): Phần tổng cộng (tổng hợp) của báo cáo

(5): Dòng tiêu đề của bảng báo cáo

(6): Các dòng nội dung chi tiết

(7): Người ký duyệt nội dung báo cáo

Báo cáo chi tiết có đặc điểm cấu trúc gần giống với bảng dữ liệu tiêu chuẩn (gồm phần 5 và 6). Do đó để làm được báo cáo này bắt buộc bảng dữ liệu gốc phải đúng cấu trúc tiêu chuẩn này.

Quy trình xây dựng báo cáo chi tiết trên Excel

Quy trình thực hiện báo cáo chi tiết được thực hiện qua các bước sau:

Bước 1: Xác định loại báo cáo

Khi nhận được yêu cầu làm báo cáo, việc đầu tiên bạn cần làm là xác định xem báo cáo đó là dạng tổng hợp hay chi tiết. Hãy chú ý đặc điểm là kết quả cần thể hiện trong báo cáo có sẵn trong bảng dữ liệu không (hay nói cách khác là có thể trích lọc từ bảng dữ liệu ra được không). Nếu câu trả lời là có thì báo cáo bạn cần làm sẽ có phần chi tiết.

Bước 2: Xác định phần điều kiện và dòng tiêu đề cho báo cáo

Dựa trên yêu cầu của báo cáo, chúng ta sẽ phân làm 2 thành phần chính:

• Phần điều kiện của báo cáo: phần này tương ứng với những cột nào của bảng dữ liệu. Đây sẽ là căn cứ để lọc theo các điều kiện.
• Phần tiêu chí báo cáo: phần này là những nội dung chi tiết cần thể hiện trên báo cáo. Những nội dung đã đưa vào điều kiện của báo cáo thì thường sẽ không cần lặp lại (xuất hiện lại) trên phần tiêu chí báo cáo.

Ví dụ dưới đây thể hiện cách xác định các thành phần của báo cáo:

Phần điều kiện (mục 3 trong bố cục) và phần dòng tiêu chí của báo cáo (mục 5 trong bố cục) là hai thành phần quan trọng nhất trong báo cáo chi tiết. Bạn cần xác định rõ 2 thành phần này trước, sau đó mới thêm các thành phần khác theo cấu trúc báo cáo.

Bước 3: Lựa chọn phương thức trích lọc kết quả

Có nhiều cách để giúp bạn thu được kết quả cho báo cáo chi tiết. Trong đó có 3 cách phổ biến như sau:

Cách 1: Copy Paste thủ công

Cách này chính là việc bạn sẽ thực hiện thao tác Lọc + trích xuất dữ liệu hoàn toàn thủ công bằng tay, với các thao tác lọc trên bộ lọc Filter của Excel và thao tác copy paste dữ liệu. Cụ thể như sau:

• Bước 1: Lọc bằng Filter trong bảng dữ liệu theo các điều kiện của báo cáo.
• Bước 2: Copy các kết quả còn lại trong bảng dữ liệu theo từng cột.
• Bước 3: Dán kết quả vào vùng tiêu chí báo cáo theo từng cột. Lặp lại đến hết các cột trong bảng báo cáo (Các cột liền nhau có thể copy + paste chung 1 lượt)

Cách 2: Sử dụng công thức Logic trong báo cáo

Cách này là việc bạn sẽ biện luận logic để tìm những giá trị trong bảng dữ liệu mà phù hợp với điều kiện của báo cáo. Cụ thể như sau:

Bước 1: Chọn 1 cột làm mốc để đặt công thức logic (lựa chọn cột có đầy đủ nội dung nhất so với các cột khác)

Bước 2: Công thức logic tại cột làm mốc có dạng:

Nếu thỏa mãn tất cả các điều kiện thì lấy theo nội dung trong cột làm mốc (chú ý xác định mối quan hệ giữa các điều kiện là dạng AND hay OR khi xét nhiều điều kiện):

=IF(AND(Điều kiện 1=..., Điều kiện 2=...), Ô kết quả, "")

Thực hiện so sánh điều kiện với ô đầu tiên trong các cột tương ứng; Ô kết quả là ô đầu tiên trong cột chứa kết quả tương ứng

Bước 3: Các cột kết quả khác trong báo cáo sử dụng công thức logic dạng:

Nếu cột mốc có dữ liệu thì cột khác cũng có dữ liệu

=IF(Ô kết quả cột mốc = "", "", Ô kết quả cột tương ứng)

Bước 4: Sao chép kết quả từ dòng đầu tiên tới dòng cuối báo cáo

Nguyên tắc: Tổng số dòng trong báo cáo chi tiết = tổng số dòng trong bảng dữ liệu

Bước 5: Lọc bỏ những dòng không có nội dung (kết quả không phù hợp) bằng AutoFilter

Cách 3: Sử dụng bộ lọc nâng cao Advanced Filter

Đây là bộ lọc có sẵn trong Excel. Bộ lọc nâng cao cho phép chúng ta có thể vừa lọc, vừa trích xuất (hay gọi tắt là trích lọc) trong cùng 1 thao tác. Cách thực hiện Advanced Filter như sau:

Bước 1: bạn cần xác định vùng điều kiện cho Advanced Filter. Vùng điều kiện này có đặc điểm khác so với vùng điều kiện thông thường của báo cáo:

Cách tổ chức điều kiện dạng và (AND)

Các điều kiện nằm trên cùng 1 hàng

Nếu các điều kiện nằm trên cùng 1 cột, tiêu đề cột đó phải được lặp lại

Cách tổ chức điều kiện được mô tả qua hình bên dưới:

Cách tổ chức điều kiện dạng hoặc (OR): Các điều kiện nằm khác hàng (nếu khác tiêu đề thì sẽ đồng thời khác cột)

Bước 2: Xác định vùng kết quả trích lọc. Đây chính là việc xác định dòng tiêu đề của báo cáo chi tiết. Nếu tên tiêu đề của báo cáo không khớp với tiêu đề trên bảng dữ liệu thì không dùng Advanced Filter được. Bạn có thể tạo 1 dòng làm tiêu đề phụ bên dưới dòng tiêu đề báo cáo để khớp với tên tiêu đề của bảng dữ liệu. Dòng này sẽ giúp bạn dùng được chức năng Advanced Filter.

Bước 3: Sử dụng Advanced Filter. 

Tại nơi chứa báo cáo (Báo cáo đặt tại Sheet nào thì bạn chọn Sheet đó), rồi gọi Advanced Filter bằng cách chọn thẻ Data > Advanced…

Sau đó xuất hiện cửa sổ Advanced Filter như sau:

• Bạn chọn mục Copy to another location để có thể trích lọc dữ liệu chi tiết ra 1 vị trí khác.
• Xác định các vùng List range, Criteria range, Copy to
• Mục Unique records only: chỉ chọn khi bạn muốn loại bỏ những giá trị trùng lặp.
• Bước 4: Bấm OK để hoàn tất thao tác. Advanced Filter sẽ tự động trích lọc các kết quả ra báo cáo.

Lưu ý

Cả 3 cách trên đều yêu cầu có những thao tác thủ công. Mỗi khi thay đổi điều kiện báo cáo, muốn cập nhật lại nội dung báo cáo theo điều kiện mới, bạn sẽ cần thực hiện lại thao tác đó. Như vậy bạn sẽ phải lặp đi lặp lại 1 thao tác.

Để khắc phục điều này, bạn sẽ có thể kết hợp với việc sử dụng Macro trong Excel như sau:

Bước 1: chọn thẻ View trên thanh công cụ, sau đó chọn tiếp mục Macro > Record Macro

Nếu phiên bản Excel của bạn không có chức năng Macro trên thẻ View, bạn vui lòng đọc thêm tại bài viết:

Hướng dẫn cách mở thẻ Developer trên thanh công cụ của Excel

Bước 2: Thực hiện thao tác trích lọc dữ liệu (theo 1 trong 3 cách trên, chú ý nên chọn cách 3)

Bước 3: Bấm Stop Recording để kết thúc quá trình ghi Macro

Bước 4: Vẽ 1 hình khối (Shape) bất kỳ trên Sheet đặt báo cáo

Bước 5: Gán Macro vào hình khối để khi bấm vào hình khối này thì macro sẽ tự động chạy lại thao tác bạn vừa thực hiện.

Vì nội dung này liên quan nhiều tới thao tác, kỹ thuật sử dụng nên rất khó để giúp bạn hiểu rõ chỉ thông qua lời nói. Vậy nên bạn vui lòng tham khảo thêm trong khóa học Excel EXG01 của Gitiho để được hướng dẫn chi tiết các thao tác này:

Kết luận

Báo cáo chi tiết nặng về tính kỹ thuật: lọc, trích xuất, sử dụng macro… Hãy luyện tập nhiều kỹ thuật để làm 1 cách chính xác nhất, hạn chế lỗi sai cũng như các thao tác thừa. Bởi những sai sót hay thao tác thừa khi Record Macro cũng sẽ lặp lại mỗi khi bạn cập nhật báo cáo.