Hướng dẫn cách viết công thức lập báo cáo chi tiết tự động trong Excel

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

• 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

 

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ả.