Công ty của Jan gần đây đã nâng cấp lên Office 365 ProPlus. Cùng với đó là #SPILL! lỗi khi sử dụng hàm VLOOKUP, mà anh ấy sử dụng LOT. Anh ấy hiện không thể sử dụng hàm VLOOKUP với bộ lọc. Jan biết sẽ rất dễ dàng để sắp xếp và / hoặc xóa # N / A đến từ VLOOKUP trước đó, nhưng lỗi này khiến anh mất thêm nhiều thời gian và công sức. Anh ấy tự hỏi liệu có cách nào để tắt “tính năng” này không.

Trong quá khứ gần đây, Microsoft đã thay đổi cách tính toán trang tính.

Đây là một thay đổi LỚN và bạn có thể đã đọc về nó ở những nơi khác. Đây là một bài viết cung cấp một số thông tin tuyệt vời về sự thay đổi:

https://exceljet.net/dynamic-array-formulas-in-excel

Bài báo khá dài; bạn sẽ muốn dành một chút thời gian để tìm hiểu thông tin trong đó. Tuy nhiên, nếu bạn tạo nhiều công thức trong Excel, bạn sẽ muốn làm như vậy — thay đổi đối với chương trình có nghĩa là cuối cùng bạn must hiểu nó.

Về cơ bản, Microsoft đã loại bỏ khái niệm về các hàm mảng (mặc dù chúng vẫn sẽ hoạt động), thay vào đó cho phép hầu hết các hàm, bao gồm cả hàm VLOOKUP, trả về một mảng giá trị. Nếu mảng các giá trị trả về không vừa với không gian có sẵn, bạn sẽ nhận được #SPILL mới! lỗi.

Thành thật mà nói, câu trả lời không phải là tắt #SPILL! sai sót; thực sự không có cách nào để làm như vậy. Câu trả lời là hiểu những gì Excel hiện đang làm khi nó tính toán và sau đó sửa đổi các công thức của bạn cho phù hợp.

Hãy xem một ví dụ. Giả sử bạn có một trang tính liệt kê các mặt hàng và giá của chúng trong một tập hợp dữ liệu hai cột đơn giản. Sau đó, ở bên phải của mục này, bạn nhập một số mục và sử dụng hàm VLOOKUP để trả về giá được liên kết với từng mục đó. Khi bạn mở sổ làm việc này trong phiên bản Excel cũ hơn (2019 trở xuống), bạn sẽ nhận được kết quả tuyệt vời. (Xem Hình 1.)

image

Hình 1. Một công thức VLOOKUP đơn giản trong Excel 2010.

Ảnh chụp màn hình này được chụp bằng hệ thống Excel 2010, nhưng nó sẽ hoạt động tương tự nếu bạn xem nó trong Excel 2016 hoặc thậm chí Excel 2019. Lưu ý trong ví dụ này rằng công thức VLOOKUP trong ô F2 (được hiển thị trong thanh Công thức vì ô F2 được chọn) được sao chép xuống phạm vi F2: F8. Bạn nhận được kết quả mong muốn vì hàm VLOOKUP trả về một giá trị duy nhất từ ​​bảng.

Bây giờ, hãy xem điều gì sẽ xảy ra nếu bạn tạo cùng một sổ làm việc, sử dụng các công thức giống nhau, trong phiên bản Excel được cung cấp với Office 365. Trong trường hợp này, bạn sẽ thấy lỗi. (Xem Hình 2.)

image

Hình 2. Công thức VLOOKUP đơn giản tương tự trong phiên bản Excel mới nhất.

Lưu ý #SPILL! lỗi. Lỗi này xảy ra vì công thức VLOOKUP hiện có thể trả về nhiều giá trị. Trên thực tế, khi bạn sử dụng một dải ô trong tham số đầu tiên cho hàm VLOOKUP, nó sẽ trả về một giá trị cho mỗi ô trong phạm vi đó. Do đó, sử dụng phạm vi E2: E8 cho tham số đầu tiên có nghĩa là hàm VLOOKUP trả về 7 giá trị. Nói cách khác, nó tự động trả về một mảng giá trị. Nếu tất cả các giá trị đó không thể được hiển thị, thì bạn sẽ nhận được lỗi #SPILL! lỗi. Đó là lý do tại sao bạn nhìn thấy #SPILL! lỗi trong các ô F2: F7; có những thứ bên dưới chúng ngăn không cho hiển thị tất cả các giá trị được trả về trong các công thức đó. Bạn không thấy lỗi trong ô F8 vì không có gì bên dưới ô đó dừng hiển thị.

Vì vậy, làm thế nào để bạn sửa chữa điều này? Thực tế có ba cách bạn có thể sửa chữa nó.

Trong ví dụ cụ thể này, cách đơn giản nhất là xóa mọi thứ trong ô F3: F8. Điều này cho phép công thức trong ô F2 “tràn”

chính xác đến phần còn lại của các ô bên dưới nó.

Cách tiếp cận thứ hai sẽ là thay đổi công thức trong ô F2, vì vậy nó trông giống như sau:

=VLOOKUP(@E$2:E$8,A$2:B$19,2)

Lưu ý việc sử dụng ký hiệu @ ngay trước tham số đầu tiên. Điều này cho Excel biết rằng bạn muốn công thức VLOOKUP chỉ trả về một giá trị duy nhất. Một cách khác để điều chỉnh công thức là làm cho nó giống như thế này trong ô F2:

=VLOOKUP(E2,A$2:B$19,2)

Bây giờ bạn có thể sao chép một trong hai công thức xuống từ ô F2 vào toàn bộ dải F2: F8 và bạn sẽ không gặp vấn đề gì. Tại sao? Bởi vì (một lần nữa) hàm VLOOKUP trong các trường hợp này chỉ trả về một giá trị duy nhất, không phải một mảng giá trị.

Kết quả là cách tốt nhất để thay đổi công thức của bạn là (1)

đảm bảo rằng không có gì cản trở việc hiển thị toàn bộ mảng giá trị mà bạn đang yêu cầu hàm VLOOKUP trả về hoặc (2) sửa đổi tham số đầu tiên để tham chiếu chỉ một ô duy nhất.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13750) áp dụng cho Microsoft Excel Excel trong Office 365.