Công thức mảng bị tràn mà bạn đang tìm cách nhập sẽ mở rộng ra ngoài phạm vi trang tính. Thử lại với dải ô hoặc mảng nhỏ hơn.
Trong ví dụ sau đây, việc di chuyển công thức đến ô F1 sẽ giải quyết được lỗi và công thức sẽ tràn chính xác.
Nguyên nhân Phổ biến: Tham chiếu cột đầy đủ
Có một phương pháp thường bị hiểu lầm là tạo công thức VLOOKUP bằng cách chỉ định đối lookup_value này. Trước excel có khả năng mảng động, Excel sẽ chỉ xem xét giá trị trên cùng một hàng với công thức và bỏ qua bất kỳ giá trị nào khác, vì VLOOKUP chỉ mong đợi một giá trị duy nhất. Với việc giới thiệu các mảng động, Excel sẽ xem xét tất cả các giá trị được cung cấp cho lookup_value. Điều này có nghĩa là nếu toàn bộ cột được cung cấp dưới dạng đối số lookup_value, Excel sẽ tìm cách tra cứu tất cả 1.048.576 giá trị trong cột. Sau khi hoàn tất, nó sẽ cố gắng để tràn chúng vào lưới, và rất có khả năng sẽ nhấn vào cuối lưới dẫn đến một lỗi #SPILL! .
Ví dụ, khi được đặt trong ô E2 như trong ví dụ dưới đây, công thức =VLOOKUP(A:A,A:C,2,FALSE) trước đây sẽ chỉ tra cứu ID trong ô A2. Tuy nhiên, trong mảng động Excel, công thức sẽ gây ra lỗi #SPILL! vì Excel sẽ tra cứu toàn bộ cột, trả về 1.048.576 kết quả và nhấn vào cuối lưới Excel.
Có 3 cách đơn giản để giải quyết sự cố này:
| # | Tiếp cận | Công thức |
|---|---|---|
| 1 | Chỉ tham chiếu các giá trị tra cứu mà bạn quan tâm. Kiểu công thức này sẽ trả về mảng động nhưng không hoạt động với bảng Excel.
|
=VLOOKUP(A2:A7,A:C,2,FALSE) |
| 2 | Chỉ tham chiếu giá trị trên cùng một hàng, rồi sao chép công thức xuống dưới. Kiểu công thức truyền thống này hoạt động trong các bảng nhưng sẽ không trả về mảng động.
|
=VLOOKUP(A2,A:C,2,FALSE) |
| 3 | Yêu cầu Excel thực hiện giao điểm ẩn bằng cách dùng toán tử @ rồi sao chép công thức xuống. Kiểu công thức này hoạt động trong bảng nhưng sẽ không trả về mảng động.
|
=VLOOKUP(@A:A,A:C,2,FALSE) |
Bạn cần thêm trợ giúp?
Bạn luôn có thể yêu cầu chuyên gia trong Cộng đồng Kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.