Chào mọi người,
Dạo này mình có làm việc với một file Excel mà vùng dữ liệu cứ thay đổi liên tục, thêm bớt dòng là phải chỉnh lại công thức thủ công mệt quá. Tìm tòi mãi thì mình phát hiện ra một cách kết hợp hàm OFFSET và COUNTA để tự động cập nhật vùng dữ liệu cho các hàm khác. Chia sẻ với anh em nào cần nhé!
Tình huống:
Giả sử bạn có một danh sách dữ liệu ở cột A, từ A1 đến A bao nhiêu đó tùy bạn thêm vào. Bạn muốn dùng hàm SUM để tính tổng toàn bộ danh sách này, nhưng không muốn phải sửa lại số cuối của vùng dữ liệu mỗi khi thêm/bớt hàng.
Cách giải quyết:
Chúng ta sẽ dùng hàm OFFSET để tạo ra một tham chiếu động, và COUNTA để đếm số lượng ô không trống trong cột đó, từ đó xác định chiều cao của vùng dữ liệu.
Ví dụ, nếu dữ liệu của bạn nằm từ A1 trở xuống, bạn có thể dùng công thức sau:
=SUM(OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1))Giải thích công thức:
Sheet1!$A$1: Ô bắt đầu của vùng dữ liệu.0(thứ hai): Số hàng dịch chuyển so với ô bắt đầu (ở đây là 0, nghĩa là bắt đầu từ A1).0(thứ ba): Số cột dịch chuyển so với ô bắt đầu (ở đây là 0, nghĩa là vẫn ở cột A).COUNTA(Sheet1!$A:$A): Đây là phần quan trọng, nó đếm tất cả các ô không trống trong cột A. Kết quả này sẽ là chiều cao của vùng dữ liệu màOFFSETtrả về.1: Chiều rộng của vùng dữ liệu (ở đây là 1 cột).
Với công thức này, mỗi khi bạn thêm dữ liệu vào cột A, hàm COUNTA sẽ tự động tăng lên, và OFFSET sẽ trả về một vùng dữ liệu mới bao gồm cả các ô vừa thêm. Hàm SUM sau đó sẽ tính tổng trên vùng dữ liệu động này.
Anh em thử áp dụng xem sao nhé. Nếu có cách nào hay hơn hoặc gặp khó khăn gì thì cứ comment bên dưới nha!