Cách tạo bảng báo cáo , bảng chọn và bảng tổng hợp trong excel

HƯỚNG DẪN CÁCH LẬP BÁO CÁO VỚI EXCEL

 

Đề bài: Cho bảng dữ liệu

Yêu cầu: Lập báo cáo tổng hợp có thể tự động cập nhật khi thay đổi thời gian của báo cáo

               

CÁCH LÀM:

Bước 1: Đặt tên cho các cột dữ liệu trong bảng Data để sử dụng trong công thức cho dễ thực hiện hơn

  • Mở tab Formulas => Chọn Define Name và đặt tên cho cột Ngày của Sheet Data. Lưu ý giới hạn của vùng dữ liệu trong cột
  • Làm tương tự cho các cột khác. Lưu ý độ rộng của các vùng Tên trong cùng 1 bảng phải bằng nhau

Bước 2: Sử dụng công thức Excel để tính dữ liệu vào bảng 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 theo các điều kiện:

  • Nội dung cần tính: Số lượng / Đơn giá / Thành tiền. Xét trên nội dung Số lượng trước, các nội dung khác có thể làm tương tự
  • Thời gian giới hạn điểm đầu: >= Từ ngày
  • Thời gian giới hạn điểm cuối: <= Đến ngày
  • Mã hàng hóa cần tính: dựa theo mỗi mã hàng trong báo cáo.

Như vậy chúng ta sử dụng hàm SUMIFS để có thể tính tổng theo nhiều điều kiện.

Cấu trúc của hàm SUMIFS cho yêu cầu tính Số lượng:

  • Vùng cần tính tổng = Data_Cot_SoLuong
  • Vùng điều kiện thứ 1 = Data_Cot_TenHang
  • Điều kiện thứ 1 trong vùng điều kiện thứ 1 = Hàng A (ô A5)
  • Vùng điều kiện thứ 2 = Data_Cot_Ngay
  • Điều kiện thứ 2 trong vùng điều kiện thứ 2 là >= Từ ngày, vì dấu >= là dấu toán tử nên phải đặt trong dấu nháy kép
  • Vùng điều kiện thứ 3 = Data_Cot_Ngay
  • Điều kiện thứ 2 trong vùng điều kiện thứ 3 là <= Đến ngày, vì dấu <= là dấu toán tử nên phải đặt trong dấu nháy kép

Tương tự như vậy với yêu cầu tính Thành tiền, chúng ta chỉ cần thay Vùng cần tính tổng trong hàm SUMIFS thành Data_Cot_ThanhTien

Với yêu cầu tính đơn giá, chúng ta tính bằng cách lấy Thành tiền / Số lượng

Nếu Số lượng = 0 thì Đơn giá sẽ = 0 (vì mẫu số của phép chia = 0 thì sẽ có lỗi, nên cần biện luận trường hợp này trước khi thực hiện phép chia

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

=> Filldown toàn bộ công thức ở các cột B, C, D từ hàng 5 xuống hàng 11 cho các Hàng hóa tiếp theo

Bước 3: Lọc dữ liệu trong báo cáo

Tại cột E, sử dụng công thức lọc bỏ những nội dung có phát sinh = 0 (hoặc không phát sinh) như sau:

E5 = IF(SUM(B5:D5)=0,””,”x”)

Filldown công thức từ E5 xuống E11

Đừng bỏ lỡ: lớp học word excel ở Hà Nội

Bước 4: Thiết lập tự động cập nhật báo cáo trong VBA

  • Record Macro cho thao tác Lọc dữ liệu – Lọc bỏ dòng trống

  • Mở cửa sổ VBA (Alt + F11), thiết lập sự kiện Change trong sheet BCTH: thay đổi dữ liệu ở ô B2 hoặc ô B3

If Not Application.Intersect(Range(“B2:B3”), Range(Target.Address)) Is Nothing Then

End if

Dòng code này được hiểu là: Nếu có sự thay đổi dữ liệu ở vùng B2:B3 thì…

ActiveSheet.Range(“$E$4:$E$11″).AutoFilter Field:=1, Criteria1:=”<>”

Dòng code này có được từ việc Record Macro ở trên, được hiểu là lọc bỏ giá trị dòng trống (blanks) ở cột Lọc (cột E, áp dụng từ dòng 4 đến dòng 11)

Như vậy chúng ta đã hoàn thành công việc lập báo cáo tự động cập nhật khi thay đổi thời gian báo cáo.

 

Add your comment

Uy tín

HTBK Computer có kinh nghiệm nhiều năm trong lĩnh vực kinh doanh và dịch vụ. Dịch vụ của chúng tôi luôn được khách hàng đánh giá cao và tin dùng.

Chất Lượng

Sản phẩm của HTBK Computer là sản phẩm chính hãng được nhập từ các nhà cung cấp lớn và uy tín cùng với nhiều quyền lợi dành cho khách hàng.

Đẳng cấp

Dịch vụ của HTBK Computer ưu tiên hàng đầu đối với quyền lợi của khách hàng. Giải quyết vấn đề của bạn một cách nhanh chóng và tiện lợi nhất.
Contact Me on Zalo

Tư vấn miễn phí (24/7) 0357 222 161