Chào mọi người,
Dạo này mình đang làm một báo cáo bán hàng khá phức tạp, số lượng mặt hàng và thời gian thay đổi liên tục. Mình gặp khó khăn khi muốn tính tổng doanh thu cho từng mặt hàng theo từng tháng, nhưng vùng dữ liệu lại thay đổi. Mỗi lần như vậy lại phải chỉnh sửa công thức SUM thủ công, rất mất thời gian.
Sau một hồi mày mò, mình đã tìm ra một cách khá hay ho bằng cách kết hợp hàm OFFSET và hàm SUM để tạo ra một công thức có thể tự động tính tổng theo vùng dữ liệu động. Mình nghĩ cách này sẽ hữu ích cho nhiều bạn đang gặp tình huống tương tự.
Cách hoạt động cơ bản là:
- Hàm
OFFSET(reference, rows, cols, [height], [width])sẽ giúp mình xác định một vùng dữ liệu mới dựa trên một ô tham chiếu ban đầu. - Mình sẽ dùng
OFFSETđể tạo ra một vùng chứa các giá trị cần tính tổng, vớiheightvàwidthcó thể thay đổi dựa trên số lượng mặt hàng và số tháng. - Sau đó, mình dùng hàm
SUMđể tính tổng các giá trị trong vùng màOFFSETtrả về.
Ví dụ, giả sử dữ liệu của bạn nằm từ A1 đến D10, với cột A là tên mặt hàng, cột B là tháng 1, C là tháng 2, D là tháng 3. Nếu bạn muốn tính tổng doanh thu của mặt hàng "Sản phẩm X" trong 3 tháng, bạn có thể dùng công thức tương tự như sau (cần điều chỉnh các tham số cho phù hợp với dữ liệu thực tế của bạn):
=SUM(OFFSET(Sheet1!$B$2, MATCH("Sản phẩm X", Sheet1!$A$2:$A$10, 0)-1, 0, 1, 3))Trong đó:
Sheet1!$B$2là ô đầu tiên của vùng dữ liệu cần tính tổng (tháng 1).MATCH("Sản phẩm X", Sheet1!$A$2:$A$10, 0)-1xác định số dòng dịch chuyển để tìm đúng mặt hàng.0là số cột dịch chuyển (giữ nguyên cột).1là chiều cao của vùng (chỉ lấy 1 dòng cho mặt hàng đó).3là chiều rộng của vùng (lấy tổng của 3 tháng).
Cách này giúp công thức của mình linh hoạt hơn rất nhiều. Có ai đã từng dùng cách này chưa, hoặc có cách nào khác hay hơn không? Cùng thảo luận nhé!