Trong công việc chúng ta thường gặp phải yêu cầu về lập báo cáo chi tiết để có thể theo dõi được nội dung cụ thể, chỉ liên quan riêng tới 1 đối tượng cụ thể cần báo cáo. Mục đích để tách riêng dữ liệu liên quan tới đối tượng cần báo cáo để giúp thống kê, phân tích những dữ liệu đó. Kỹ thuật lập báo cáo chi tiết cũng là một kỹ thuật cao, đòi hỏi nhiều yếu tố về:

• Trình bày báo cáo khoa học, đẹp mắt trong excel
• Cách để lấy nội dung báo cáo từ dữ liệu ban đầu ở bảng kê theo đúng điều kiện lập báo cáo

Sau đây chúng ta cùng tìm hiểu về 1 công thức giúp việc lập báo cáo chi tiết trở nên rất dễ dàng và thuận tiệnthông qua ví dụ sau:

Chúng ta có 1 bảng kê thông tin bán hàng của từng mặt hàng theo các ngày (Bảng A2:E12), trong đó đã được sắp xếp theo thứ tự cột Tên mặt hàng.

Yêu cầu

Lập báo cáo chi tiết cho từng mặt hàng (vùng G1:J12), biết rằng khi thay đổi tên hàng thì báo cáo sẽ tự động thay đổi nội dung tương ứng với mặt hàng đó.

Cách thực hiện

Bước 1: Tạo danh sách chọn tên mặt hàng

Ở bảng kê bán hàng có 3 mặt hàng là Hàng A, Hàng B, Hàng C. Do đó chúng ta có thể tạo danh sách chọn tên mặt hàng tại ô H2 như sau:

Chọn tab Data / Data Validation

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

Trong bảng Data Validation:

• Mục Allow chúng ta chọn List
• Mục Data nhập nội dung như sau: Hàng A, Hàng B, Hàng C

Bấm ok

Khi đó chúng ta tại ô H2 chúng ta có thể chọn tên hàng theo danh sách gồm 3 mặt hàng này.

Bước 2: Công thức lấy dữ liệu vào báo cáo

Căn cứ vào vị trí tên hàng xác định được ở trên, lấy các nội dung gồm: Ngày, Tên nhân viên, số lượng, thành tiền tương ứng theo mặt hàng đó => Sử dụng hàm OFFSET

Bởi vì kết quả trả về là 1 mảng dữ liệu nên chúng ta cần sử dụng tới công thức mảng.

Khi viết công thức mảng, chúng ta chọn toàn bộ vùng sẽ thể hiện kết quả (G5:J12), sau đó nhập công thức và kết thúc công thức này chúng ta sẽ dùng tổ hợp phím Ctrl + Shift + Enter

Cấu trúc hàm OFFSET gồm:

=OFFSET(reference, rows, cols, [height], [width])

• references: Tên hàng được chọn để lập báo cáo tại ô H2 trùng với tên hàng trong cột A => Có thể sử dụng hàm Index+Match để xác định vị trí nào ở cột A có kết quả đúng
• row: số dòng bắt đầu tính từ kết quả, ở đây tính tại dòng đầu tiên xuất hiện tên hàng nên row = 0
• cols: Số cột bắt đầu tính từ kết quả, ở đây lấy kết quả bắt đầu từ cột B tới các cột khác (cột B cách cột A là 1 cột) nên cols = 1
• Độ lớn của vùng cần lấy bởi hàm OFFSET sẽ là:

+) Height (số dòng): Bằng số dòng có Tên hàng tại H2 trùng với cột Tên hàng => Sử dụng hàm COUNTIF để đếm số lần xuất hiện tên hàng này

+) Weight (số cột): ở đây báo cáo chi tiết cần 4 cột, do đó lấy 4 cột tương ứng theo tên hàng này.

Trong đó:

• Hàm Index + Match được viết như sau:

INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0))

• Hàm COUNTIF được viết như sau:

COUNTIF($A$3:$A$12,$H$2)

Công thức hoàn chỉnh là:

=OFFSET(INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0)),0,1,COUNTIF($A$3:$A$12,$H$2),4)

Chúng ta có kết quả như sau:

Bước 3: Cách bẫy lỗi

Khi sử dụng công thức OFFSET cho vùng bảng G5:J12, tên hàng là Hàng A, chúng ta thu được 4 dòng kết quả tương ứng theo bảng kê. Như vậy là kết quả đã đúng.

Nhưng vùng bảng G5:J12 có tới 8 dòng. Vậy những dòng thừa sẽ xuất hiện lỗi #N/A

Để bẫy lỗi này, không để lỗi hiển thị thì chúng ta làm như sau:

Sử dụng chức năng Conditional Formatting:

• Chọn toàn bộ bảng tính từ G5:J12
• Trong tab Home > Chọn Conditional formatting > Chọn New Rule
• Mục Select a Rule Type chọn Format only cells that conain
• Mục Format only cells with chọn Errors

Chúng ta sẽ thiết lập định dạng cho các ô có chứa lỗi trong vùng bảng này trong mục Format > Chọn màu chữ là màu trắng

Bấm ok và chúng ta sẽ thu được kết quả như sau:

Những điều cần lưu ý

• Do hàm OFFSET lấy ra kết quả là 1 mảng liền nhau, do đó dữ liệu trong bảng kê (phần nguyên liệu để lập báo cáo) sẽ phải được sắp xếp theo thứ tự theo tên hàng
• Các cột kết quả trong báo cáo chi tiết sẽ lấy tương ứng theo vị trí trong bảng kê tương ứng theo vị trí cột tên hàng. Do đó tùy theo vị trí các vùng dữ liệu cần lấy trong báo cáo chi tiết mà chúng ta có thể thiết lập các tham số: Row, Cols, Height, Weight trong hàm offset cho phù hợp. Để thuận lợi hơn khi làm việc này thì chúng ta đặt các cột kết quả liền kề nhau, tránh việc cột điều kiện lập báo cáo (tên hàng) nằm xen giữa các cột kết quả.

Tải về file mẫu: http://bit.ly/2IhmiYq

Video hướng dẫn chi tiết

Xem thêm:

Hướng dẫn cách lập báo cáo tổng hợp tự động với Excel và VBA

Nếu bạn muốn tìm hiểu thêm các kiến thức khác về Excel, hãy tham gia ngay khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để giúp bạn làm việc trên Excel được tốt hơn, hiệu quả hơn. Chi tiết xem tại: