Chào các bác, em là dân văn phòng mới tập tành dùng Excel chuyên sâu. Dạo này em hay phải làm báo cáo động, cập nhật liên tục nên em đang tìm cách để bảng tính của mình tự động co giãn theo số lượng dữ liệu nhập vào.
Em có tìm hiểu thì thấy hàm OFFSET khá hay, kết hợp với COUNTA để đếm số lượng dữ liệu, từ đó xác định vùng dữ liệu cần lấy. Em đã thử áp dụng và thấy khá ổn, nên muốn chia sẻ lại cho anh em nào đang gặp vấn đề tương tự.
Ví dụ, giả sử dữ liệu của em nằm từ A2 trở xuống, và em muốn tạo một danh sách động để sử dụng trong hàm khác (ví dụ VLOOKUP, INDEX). Em có thể dùng công thức sau để xác định vùng dữ liệu từ A2 đến hết dữ liệu:
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)Giải thích sơ qua:
Sheet1!$A$2: Ô bắt đầu của vùng dữ liệu.0, 0: Số hàng và cột dịch chuyển so với ô bắt đầu (ở đây là không dịch chuyển).COUNTA(Sheet1!$A:$A)-1: Số lượng hàng của vùng dữ liệu. Em dùngCOUNTA(Sheet1!$A:$A)để đếm tất cả các ô không trống trong cột A, trừ đi 1 (vì em bỏ qua ô tiêu đề ở A1).1: Số lượng cột của vùng dữ liệu (ở đây là 1 cột).
Với công thức này, mỗi khi em thêm dữ liệu vào cột A, vùng tham chiếu sẽ tự động mở rộng theo. Em thấy nó hữu ích khi làm các danh sách thả xuống động hoặc khi cần tham chiếu đến một vùng dữ liệu thay đổi thường xuyên.
Có anh em nào có cách nào hay hơn hoặc ứng dụng hàm OFFSET vào các trường hợp khác không, chia sẻ cho em với ạ!