Công thức Excel: Dải ô được đặt tên động với OFFSET -

Công thức chung

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Tóm lược

Một cách để tạo phạm vi được đặt tên động với công thức là sử dụng hàm OFFSET cùng với hàm COUNTA. Phạm vi động còn được gọi là phạm vi mở rộng - chúng tự động mở rộng và co lại để chứa dữ liệu mới hoặc đã xóa.

Lưu ý: OFFSET là một hàm dễ bay hơi, có nghĩa là nó sẽ tính toán lại với mọi thay đổi đối với trang tính. Với một máy hiện đại và tập dữ liệu nhỏ hơn, điều này sẽ không gây ra sự cố nhưng bạn có thể thấy hiệu suất chậm hơn trên các tập dữ liệu lớn. Trong trường hợp đó, hãy xem xét việc tạo một phạm vi được đặt tên động bằng hàm INDEX.

Trong ví dụ được hiển thị, công thức được sử dụng cho phạm vi động là:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Giải trình

Công thức này sử dụng hàm OFFSET để tạo một phạm vi mở rộng và thu hẹp bằng cách điều chỉnh chiều cao và chiều rộng dựa trên số lượng các ô không trống.

Đối số đầu tiên trong OFFSET đại diện cho ô đầu tiên trong dữ liệu (gốc), trong trường hợp này là ô B5. Hai đối số tiếp theo là hiệu số cho các hàng và cột, và được cung cấp bằng 0.

Hai đối số cuối cùng đại diện cho chiều cao và chiều rộng. Chiều cao và chiều rộng được tạo nhanh chóng bằng cách sử dụng COUNTA, làm cho tham chiếu kết quả động.

Đối với chiều cao, chúng tôi sử dụng hàm COUNTA để đếm các giá trị không trống trong phạm vi B5: B100. Điều này giả định không có giá trị trống nào trong dữ liệu và không có giá trị nào vượt quá B100. COUNTA trả về 6.

Đối với chiều rộng, chúng tôi sử dụng hàm COUNTA để đếm các giá trị không trống trong phạm vi B5: Z5. Điều này giả định không có ô tiêu đề và không có tiêu đề nào ngoài Z5. COUNTA trả về 6.

Tại thời điểm này, công thức trông giống như sau:

=OFFSET(B5,0,0,6,6)

Với thông tin này, OFFSET trả về một tham chiếu đến B5: G10, tương ứng với một phạm vi chiều cao 6 hàng x 6 cột ngang.

Lưu ý: Các phạm vi được sử dụng cho chiều cao và chiều rộng phải được điều chỉnh để phù hợp với bố cục trang tính.

Biến thể với đầy đủ tham chiếu cột / hàng

Bạn cũng có thể sử dụng tham chiếu cột và hàng đầy đủ cho chiều cao và chiều rộng như sau:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Lưu ý rằng chiều cao đang được điều chỉnh bằng -2 để tính đến giá trị tiêu đề và tiêu đề trong ô B4 và B2. Ưu điểm của cách tiếp cận này là sự đơn giản của các phạm vi bên trong COUNTA. Điểm bất lợi đến từ các cột và hàng có kích thước lớn - cần phải cẩn thận để ngăn chặn các giá trị sai bên ngoài phạm vi, vì chúng có thể dễ dàng làm sai số lượng.

Xác định hàng cuối cùng

Có một số cách để xác định hàng cuối cùng (vị trí tương đối cuối cùng) trong tập hợp dữ liệu, tùy thuộc vào cấu trúc và nội dung của dữ liệu trong trang tính: