Hướng dẫn sử dụng công thức tự động để tạo báo cáo chi tiết trong Excel

Trong quá trình làm việc, chúng ta thường cần phải thực hiện việc tạo các báo cáo chi tiết, giúp theo dõi thông tin chính xác và chỉ liên quan đến một đối tượng cụ thể nào đó. Việc này được thực hiện với mục đích là để tách biệt các dữ liệu liên quan đến đối tượng cần báo cáo, qua đó hỗ trợ công việc thống kê và phân tích dữ liệu. Việc lập báo cáo chi tiết là một kỹ thuật nâng cao, yêu cầu người làm phải có:

  • Khả năng trình bày báo cáo một cách khoa học, thẩm mỹ trên nền tảng Excel
  • Kỹ năng lấy thông tin cần thiết từ bảng dữ liệu gốc để tạo báo cáo đúng theo các điều kiện đã đặt ra

Để hiểu rõ hơn, chúng ta sẽ cùng nhau tìm hiểu về một công thức cho phép tạo báo cáo chi tiết một cách dễ dàng và nhanh chóng thông qua ví dụ sau:

Ta có một bảng dữ liệu về thông tin bán hàng theo ngày cho từng sản phẩm (Bảng A2:E12), và cột Tên hàng đã được sắp xếp.

Yêu cầu

Tạo ra báo cáo chi tiết cho mỗi sản phẩm (khu vực G1:J12), với giả định rằng khi tên sản phẩm thay đổi, báo cáo cũng sẽ tự động cập nhật thông tin phù hợp với sản phẩm mới.

Cách tiến hành

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

Tại bảng dữ liệu bán hàng, có ba sản phẩm là Hàng A, Hàng B, và Hàng C. Vì vậy, ta có thể tạo một danh sách để lựa chọn tên mặt hàng ở ô H2 theo cách sau:

Vào tab Data / Data Validation

Trong cửa sổ Data Validation:

  • Chọn List trong mục Allow
  • Nhập thông tin cho mục Data như sau: Hàng A, Hàng B, Hàng C

Nhấn OK

Lúc này, tại ô H2, ta có thể chọn một trong ba tên sản phẩm trên.

Bước 2: Áp dụng công thức để thu thập dữ liệu vào báo cáo

Dựa vào vị trí tên hàng đã xác định, ta thu thập các thông tin như Ngày, Tên nhân viên, số lượng, và thành tiền cho mỗi sản phẩm tương ứng sử dụng hàm OFFSET

Vì kết quả trả về là một mảng dữ liệu, nên ta cần áp dụng công thức mảng.

Để viết công thức mảng, ta chọn toàn bộ khu vực sẽ hiển thị kết quả (G5:J12), nhập công thức và hoàn tất bằng phím tổ hợp Ctrl + Shift + Enter

Cấu trúc của hàm OFFSET như sau:

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

  • reference: Là tên hàng đã chọn tại ô H2, phải trùng với tên hàng trong cột A => Ta có thể sử dụng hàm Index+Match để xác định vị trí cụ thể trong cột A
  • rows: Số dòng tính từ tên hàng, tại đây là dòng đầu tiên có tên hàng nên rows = 0
  • cols: Số cột tính từ tên hàng, chúng ta mở rộng từ cột B nên cols = 1
  • Độ lớn của vùng cần lấy bằng hàm OFFSET sẽ được xác định bởi:

+) Height (số dòng): Là số dòng có tên hàng ở H2 trùng với cột Tên hàng => Sử dụng hàm COUNTIF để đếm số lượng

+) Width (số cột): Chúng ta cần 4 cột cho báo cáo chi tiết theo tên hàng

Ví dụ cụ thể:

  • Hàm Index + Match ghi như sau:

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

  • Hàm COUNTIF ghi như sau:

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

Công thức cuối cùng sẽ 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)

Kết quả thu được như sau:

Bước thứ ba: Hướng dẫn cách khắc phục lỗi

Khi áp dụng công thức OFFSET trên phạm vi bảng từ G5:J12 với tên cột là Hàng A, ta nhận được 4 hàng kết quả đúng theo danh sách đề ra.

Tuy nhiên, phạm vi bảng G5:J12 gồm có tổng cộng 8 hàng. Do vậy, những hàng thừa này sẽ hiển thị lỗi #N/A.

Để xử lí lỗi và ngăn chặn việc hiển thị nó, ta có thể làm theo các bước sau đây:

Bắt đầu bằng cách dùng chức năng Định dạng có điều kiện (Conditional Formatting):

  • Lựa chọn cả bảng từ G5:J12
  • Tìm đến thẻ Home > Click vào Conditional formatting > Sau đó chọn New Rule
  • Ở mục Select a Rule Type, ta chọn Format only cells that contain
  • Tại mục Format only cells with, lựa chọn Errors

Tiếp theo, thiết lập định dạng cho những ô có lỗi bằng cách chọn màu chữ là màu trắng trong phần Format.

Nhấn ok để kết quả hiển thị như hình sau:

Cần chú ý những điểm sau

  • Vì hàm OFFSET sẽ trả về một mảng dữ liệu liên tục, nên dữ liệu trong phần bảng kê (nguồn tạo báo cáo) cần sắp xếp theo thứ tự cụ thể của tên hàng.
  • Các cột hiển thị kết quả trong báo cáo chi tiết sẽ ứng với vị trí của chúng trong bảng kê, theo cột có tên hàng. Tùy thuộc vào vị trí của dữ liệu cần lấy trong báo cáo chi tiết mà các tham số Row, Cols, Height, Weight trong hàm OFFSET nên được điều chỉnh cho thích hợp. Để thuận tiện hơn, các cột kết quả nên xếp liền kề và cần tránh sự xen kẽ của cột điều kiện (tên hàng) giữa chúng.

Tải file mẫu từ địa chỉ: http://bit.ly/2IhmiYq

Xem video hướng dẫn chi tiết:

[wpcc-iframe title=”Công thức lập báo cáo chi tiết tự động với hàm offset và index match” width=”500″ height=”281″ src=”https://www.youtube.com/embed/iVHqvrkAVZo?feature=oembed” frameborder=”0″ allow=”accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture” allowfullscreen=””]

Đừng bỏ lỡ:

Bài viết hướng dẫn cách tạo báo cáo tổng hợp tự động bằng Excel và VBA

Nếu bạn quan tâm và muốn tìm hiểu thêm về Excel, thì có thể tham gia khóa học Excel từ cơ bản tới nâng cao dành cho dân văn phòng. Khóa học này cung cấp đầy đủ kiến thức và hỗ trợ tuyệt vời để bạn làm quen và sử dụng các công cụ, hàm của Excel một cách hiệu quả. Xem chi tiết tại đây:

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop