Nội Dung Bài Viết

Bạn thường xuyên phải lập báo cáo tổng hợp trên Excel và muốn tự động cập nhật số liệu khi thay đổi thời gian? VBA có thể giúp bạn thực hiện điều này một cách dễ dàng. Hãy cùng Thủ Thuật tìm hiểu nhé!

Bài viết này sẽ hướng dẫn bạn cách tạo báo cáo tổng hợp tự động cập nhật trên Excel bằng VBA, dựa trên ví dụ bảng dữ liệu sau:

Dữ liệu gốc

Mục tiêu là tạo báo cáo tổng hợp có thể tự động cập nhật khi thay đổi thời gian, như hình minh họa:

Báo cáo mẫu

Cách Tạo Báo Cáo Tự Động

Bước 1: Đặt Tên Cho Cột Dữ Liệu

Để dễ dàng sử dụng trong công thức, hãy đặt tên cho các cột dữ liệu trong sheet “Data”.

Đặt tên cột

Vào tab “Formulas” > “Define Name” và đặt tên cho cột “Ngày” của sheet “Data”. Lưu ý giới hạn vùng dữ liệu trong cột.
Lặp lại tương tự cho các cột khác. Đảm bảo độ rộng của các vùng tên trong cùng một bảng là bằng nhau.

Bước 2: Tính Dữ Liệu Cho Báo Cáo Tổng Hợp

Mỗi đối tượng trong báo cáo tổng hợp được tính dựa trên các điều kiện:

Nội dung cần tính: Số lượng, Đơn giá, Thành tiền.
Thời gian bắt đầu: >= “Từ ngày”.
Thời gian kết thúc: <= “Đến ngày”.
Mã hàng hóa.

Xem thêm: Tính Tổng Bình Phương trong Excel: Hướng Dẫn Chi Tiết

Chúng ta sẽ sử dụng hàm SUMIFS để tính tổng theo nhiều điều kiện.

Công thức SUMIFS

Cấu trúc hàm SUMIFS cho Số lượng:

=SUMIFS(Data_Cot_SoLuong,Data_Cot_TenHang,A5,Data_Cot_Ngay,">="&B$2,Data_Cot_Ngay,"<="&B$3)

Data_Cot_SoLuong: Vùng cần tính tổng.
Data_Cot_TenHang: Vùng điều kiện 1.
A5: Điều kiện 1 (Hàng A).
Data_Cot_Ngay: Vùng điều kiện 2 và 3.
">="&B$2: Điều kiện 2 (>= Từ ngày).
"<="&B$3: Điều kiện 3 (<= Đến ngày).

Tương tự, để tính Thành tiền, thay Data_Cot_SoLuong bằng Data_Cot_ThanhTien.

Công thức Thành tiền

Để tính Đơn giá, lấy Thành tiền chia cho Số lượng. Xử lý trường hợp Số lượng bằng 0 để tránh lỗi chia cho 0:

=IF(B5=0,0,D5/B5)

Áp dụng công thức cho các cột B, C, D từ hàng 5 xuống hàng 11.

Bước 3: Lọc Dữ Liệu Trong Báo Cáo

Tại cột E, lọc bỏ các dòng không phát sinh dữ liệu:

Lọc dữ liệu

=IF(SUM(B5:D5)=0,"","x")

Kéo công thức từ E5 xuống E11.

Bước 4: Tự Động Cập Nhật Báo Cáo Với VBA

Ghi Macro cho thao tác lọc dữ liệu – bỏ dòng trống.

Ghi macro

Lọc dòng trống

Mở cửa sổ VBA (Alt + F11), thiết lập sự kiện Change trong sheet “BCTH” cho ô B2 và B3:

VBA Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("B2:B3"), Range(Target.Address)) Is Nothing Then
        ActiveSheet.Range("$E$4:$E$11").AutoFilter Field:=1, Criteria1:=""
    End If
End Sub

Đoạn code này sẽ tự động lọc bỏ các dòng trống trong cột “Lọc” (cột E) khi thay đổi giá trị trong ô “Từ ngày” hoặc “Đến ngày”.