Giới thiệu về Dải động
Hàm Vlookup thường được sử dụng để tìm thông tin được lưu trữ trong các bảng trong Excel. Ví dụ: nếu chúng tôi có danh sách tên và tuổi của mọi người:
Và sau đó, trong một ô gần đó, chúng ta có thể sử dụng hàm VLOOKUP để xác định tuổi của Paul:
Cho đến nay đây là một tiêu chuẩn khá. Nhưng điều gì sẽ xảy ra nếu chúng ta cần thêm một số tên nữa vào danh sách? Ý nghĩ rõ ràng sẽ là sửa đổi phạm vi trong hàm VLOOKUP. Tuy nhiên, trong một mô hình thực sự phức tạp, có thể có một số tham chiếu đến VLOOKUP. Điều này có nghĩa là chúng tôi sẽ phải thay đổi từng tham chiếu - giả sử rằng chúng tôi biết chúng ở đâu.
Tuy nhiên, Excel cung cấp một cách thay thế - được gọi là phạm vi NĂNG ĐỘNG. Đây là phạm vi mở rộng bản cập nhật tự động. Điều này là hoàn hảo nếu danh sách của bạn luôn mở rộng (ví dụ: dữ liệu bán hàng theo tháng).
Để thiết lập phạm vi động, chúng tôi cần có tên phạm vi - vì vậy chúng tôi sẽ gọi là AGE_DATA của chúng tôi. Cách tiếp cận để thiết lập phạm vi động khác nhau giữa Excel 2007 và các phiên bản Excel trước:
Trong Excel 2007, hãy nhấp vào “Xác định Tên” bên dưới các công thức:
Trong các phiên bản Excel trước, hãy nhấp vào “Chèn” và sau đó nhấp vào Tên ”.
Trong hộp bật lên, hãy nhập tên của phạm vi động của chúng tôi - đó là “DỮ LIỆU TUỔI”:
Trong hộp có nhãn “Tham khảo”, chúng ta cần nhập phạm vi dữ liệu của mình. Đây sẽ là một thành tựu được sử dụng bởi một hàm OFFSET. Điều này có 5 đối số:
= OFFSET (Tham chiếu, Hàng, Cols, Chiều cao, Chiều rộng)
- Tham chiếu là địa chỉ của góc TRÊN TRÁI trong phạm vi của chúng ta - trong trường hợp này là ô B5
- Hàng là số hàng từ TRÊN TRÁI mà chúng ta muốn phạm vi đó là - sẽ là 0 trong trường hợp này
- Cols là số hàng từ TRÊN TRÁI mà chúng ta muốn phạm vi đó là - sẽ là 0 trong trường hợp này
- Chiều cao của phạm vi - xem bên dưới để biết điều này
- Chiều rộng của phạm vi - đây là 2 vì chúng tôi có HAI cột trong phạm vi của chúng tôi (tên người và tuổi của họ)
Bây giờ chiều cao của phạm vi sẽ phải thay đổi tùy thuộc vào số lượng mục nhập trong bảng của chúng ta (hiện là 7).
Tất nhiên, chúng tôi muốn có một cách đếm các hàng trong bảng của chúng tôi cập nhật tự động - vì vậy một cách để thực hiện việc này là sử dụng hàm COUNTA. Điều này chỉ đếm số lượng ô không trống trong một phạm vi. Vì tên của chúng tôi nằm trong cột B, số mục nhập trong dữ liệu của chúng tôi là COUNTA (B: B).
Lưu ý rằng nếu bạn đặt giá trị này vào một ô, bạn sẽ nhận được giá trị 8 - vì nó bao gồm Tên tiêu đề. Tuy nhiên, nó là phi vật chất.
Vì vậy, trong hộp "Tham khảo", chúng tôi đặt:
= OFFSET ($ B $ 5,0,0, counta (B: B), 2)
Và nhấp vào nút OK. Phạm vi động của chúng tôi hiện đã được tạo.
Bây giờ quay trở lại công thức VLOOKUP và thay thế phạm vi $ B: 4: $ C11 bằng tên của phạm vi động mới AGE_DATA của chúng tôi để chúng tôi có:
Cho đến nay vẫn không có gì thay đổi. Tuy nhiên, nếu chúng ta thêm một vài tên khác vào bảng của mình:
Và trong ô nơi chúng tôi có Paul, hãy thay thế nó bằng một cái tên mới chẳng hạn như Pedro (không có trong danh sách ban đầu):
Và chúng tôi thấy rằng Excel đã tự động trả về tuổi của Pedro - mặc dù chúng tôi không thay đổi công thức VLOOKUP. Thay vào đó, phạm vi của dải động đã tăng lên để bao gồm các tên bổ sung.
Phạm vi động rất hữu ích khi chúng ta có khối lượng dữ liệu ngày càng tăng - đặc biệt khi các bảng VLOOKUP và PIVOT được yêu cầu.
