Hướng dẫn giải thích điều gì có thể gây ra lỗi #SPILL trong công thức INDEX MATCH, VLOOKUP, SUMIF và COUNTIF và cách bạn có thể giải quyết lỗi này một cách hiệu quả.
Thật đáng buồn khi một tính năng hoàn toàn mới không hoạt động trong Excel của bạn. Nhưng thậm chí còn khó chịu hơn là khi một thứ cũ tốt đột nhiên ngừng hoạt động, và bạn nhận được lỗi cho một công thức đã hoạt động hoàn hảo trong nhiều năm.
Các ví dụ dưới đây cho thấy cách sửa một số công thức phổ biến bị hỏng do giao nhau ngầm không còn được thực hiện ẩn trong Excel nữa. Nếu bạn chưa bao giờ nghe thuật ngữ này trước đây, tôi khuyến khích bạn đọc kỹ Lỗi #SPILL hướng dẫn để hiểu những gì đang xảy ra đằng sau hậu trường.
Lỗi #SPILL với công thức hàm VLOOKUP trong Excel
Đây là công thức VLOOKUP chuẩn hoạt động tốt trong Excel tiền động (2019 trở về trước) và gây ra lỗi #SPILL trong Excel 365:
=VLOOKUP(A:A, D:E, 2, FALSE)
Như chúng ta có thể giả định một cách hợp lý, vấn đề là trong đối số đầu tiên (tham chiếu màu đỏ ở trên) buộc Hàm VLOOKUP để tra cứu tất cả các giá trị trong cột A, có hơn một triệu ô (con số chính xác là 1.048.576)! Trước đây, đó không phải là vấn đề – Excel chỉ có thể tra cứu một giá trị tại một thời điểm, vì vậy nó loại bỏ tất cả trừ một giá trị trong cùng một hàng với công thức. Hành vi này được gọi là ngụ ý hoặc giao lộ ngầm.
Với sự giới thiệu của mảng động, tất cả các hàm Excel đều có khả năng xử lý và xuất ra nhiều giá trị, ngay cả những giá trị ban đầu không được thiết kế để hoạt động với mảng! Vì vậy, mỗi khi hàm VLOOKUP nhận được một mảng giá trị tra cứu, nó sẽ cố gắng xử lý tất cả chúng. Trong trường hợp không có đủ dung lượng để xuất tất cả kết quả, bạn sẽ thấy lỗi #SPILL.
Để giải quyết lỗi tràn hàm VLOOKUP trong Excel, bạn có thể sử dụng một trong các phương pháp sau.
Tra cứu dải ô chứ không phải cột
Vì chúng tôi chỉ có 3 giá trị tra cứu, chúng tôi giới hạn đối số lookup_value ở ba ô:
=VLOOKUP(A3:A5, D:E, 2, FALSE)
Công thức chỉ cần được nhập vào một ô và nó sẽ tự động điền vào nhiều ô nếu cần. Kết quả là một phạm vi tràn giống cái này:
Tra cứu một giá trị duy nhất
Viết công thức cho giá trị tra cứu đầu tiên và sao chép nó xuống bao nhiêu ô nếu cần:
=VLOOKUP(A3, D:E, 2, FALSE)
Đây là tùy chọn ưa thích của tôi vì nó đơn giản nhất để triển khai và hoạt động đẹp trong tất cả các phiên bản Excel, từ trong phạm vi và bảng bình thường.
Thực thi giao lộ ngầm định
Để giới hạn một mảng ở một giá trị tra cứu, hãy đặt toán tử giao nhau @ trước tham chiếu cột:
=VLOOKUP(@A:A, D:E, 2, FALSE)
Như với ví dụ trước, bạn nhập công thức vào một ô và kéo nó xuống cột.
Cho dù bạn chọn giải pháp nào, lỗi #SPILL sẽ biến mất và công thức VLOOKUP của bạn bắt đầu hoạt động bình thường trong Excel 365.
Lỗi #SPILL với công thức Excel INDEX MATCH
Trong trường hợp bạn đang sử dụng kết hợp INDEX và MATCH để kéo các kết quả phù hợp, lỗi #SPILL có thể phát sinh vì lý do tương tự – không đủ khoảng trắng cho mảng bị tràn.
Ví dụ: đây là công thức trả về số bán hàng một cách hoàn hảo trong Excel 2019 và các phiên bản trước đó, nhưng từ chối hoạt động trong Excel 365:
=INDEX(E:E, MATCH(A:A, D:E, 0))
Cách khắc phục đã được biết đến – giảm số lượng giá trị tra cứu bằng cách áp dụng một trong các kỹ thuật sau.
- Tra cứu một phạm vi, không phải là một cột:
=INDEX(E:E, MATCH(A3:A5, D:D, 0)) - Tra cứu một giá trị duy nhất:
=INDEX(E:E, MATCH(A3, D:D, 0)) - Kích hoạt giao lộ ngầm – thêm ký tự @ trước tham chiếu cột để Excel chỉ xử lý một giá trị:
=INDEX(E:E, MATCH(@A:A, D:D, 0))
Kết quả của 1st công thức là một mảng tràn động, đây là một điều tuyệt vời giúp bạn tiết kiệm được khó khăn khi sao chép công thức sang các ô khác. Hạn chế là mảng động chỉ hoạt động trong một phạm vi, không phải một bảng.
2nd và 3rd công thức trả về một giá trị duy nhất mà một bảng cũng có thể chấp nhận. Nếu dữ liệu của bạn được tổ chức như một phạm vi thông thường, hãy kéo công thức xuống để sao chép nó vào các ô bên dưới. Trong một bảng, công thức sẽ tự động truyền. Trong trường hợp sau, bạn cũng có thể sử dụng tham chiếu có cấu trúc ký hiệu đề cập đến các tiêu đề cột:
=INDEX(E:E, MATCH([@[Seller ]], D:D, 0))
Ảnh chụp màn hình dưới đây chứng minh 3rd công thức trong hành động:
Lỗi #SPILL với công thức SUMIF và COUNTIF của Excel
Khi một SUMIF, COUNTIF, SUMIFS hoặc là COUNTIFS công thức trả về lỗi #SPILL, lỗi này có thể do nhiều yếu tố khác nhau gây ra. Những cái thường xuyên nhất được thảo luận dưới đây.
Phạm vi tràn quá lớn
Một nguyên nhân rất điển hình là cung cấp toàn bộ cột cho tiêu chí. Đúng, đã từng hoạt động trong các phiên bản Excel cũ hơn, nhưng không còn nữa, kể từ phiên bản mới Spill tính năng này tạo ra gần 1,05 triệu kết quả và không có đủ dung lượng để chứa tất cả!
Đối với ví dụ này, chúng ta hãy thử tìm tổng doanh số bán hàng của ba nhà cung cấp (A3: A5). Trong Excel 2019 và các phiên bản thấp hơn, bạn có thể sử dụng thành công cú pháp dưới đây. Trong Excel 365, bạn sẽ gặp lỗi #SPILL:
=SUMIF(D:D, A:A, E:E)
Nếu bạn đã theo dõi chặt chẽ các ví dụ trước, bạn biết rằng lỗi có thể được giải quyết theo ba cách khác nhau:
- Sử dụng một phạm vi cho tiêu chí của bạn, không phải toàn bộ cột:
=SUMIF(D:D, A3:A5, E:E) - Xác định một đơn bào cho các tiêu chí và sao chép công thức xuống:
=SUMIF(D:D, A3, E:E) - Bao gồm cái giao lộ ngầm toán tử (@) để giới hạn tham chiếu cột trong một ô:
=SUMIF(D:D, @A:A, E:E)Theo cách tương tự, bạn có thể tính doanh số bán hàng cho từng người bằng cách sử dụng hàm COUNTIF:
=COUNTIF(D:D, A3:A5)= COUNTIF (D:D, A3)= COUNTIF (D:D, @A:A)
Hãy nhớ rằng 1st công thức tự động tràn vào các hàng bên dưới và chỉ có thể được sử dụng trong một phạm vi, không phải một bảng.
2nd và 3rd công thức trả về một giá trị duy nhất, vì vậy bạn nhập chúng vào ô đầu tiên và sau đó sao chép xuống cột như bình thường.
Tiêu chí được thể hiện không chính xác
Các tiêu chí SUMIF và COUNTIF cũng là một nguồn phổ biến của các vấn đề. Đôi khi, mọi người nghĩ quá kỹ và viết các tiêu chí là D3 = “carter” hoặc D3: D11 = “carter” hoặc D: D = “carter”. Cả ba biểu thức đều sai và khiến công thức tạo ra số 0 hoặc lỗi #SPILL!
Cách đúng là tham chiếu dải ô / ô như trong các ví dụ trên hoặc văn bản được đặt trong dấu ngoặc kép:
=SUMIF(D:D, "carter", E:E)
Để tìm hiểu thêm về điều gì được chấp nhận trong tiêu chí và điều gì không được chấp nhận, các nguyên tắc sau có thể hữu ích: Cú pháp tiêu chí SUMIF.
Thứ tự lập luận sai
Trong các phiên bản số ít và số nhiều của hàm SUMIF, thứ tự của các đối số là khác nhau:
- Với SUMIF, trước tiên bạn xác định phạm vi, sau đó tiêu chí, sau đó sum_range (không bắt buộc).
- Trong trường hợp SUMIFS, đối số đầu tiên phải là sum_range, và sau đó phạm vi/tiêu chí cặp.
Nếu bạn xáo trộn mọi thứ, lỗi #SPILL sẽ xảy ra.
Trong ví dụ của chúng tôi, tiêu_phí là D: D và sum_range tôi hiểu rồi. Nếu bạn đặt một cái này thay cho cái kia, công thức sẽ lại xuất hiện lỗi #SPILL:
=SUMIFS(D:D, A3:A5, E:E)
Sắp xếp các đối số theo đúng thứ tự và SUMIF sẽ cho bạn kết quả mong muốn:
=SUMIFS(E:E, D:D, A3:A5)
Đó là cách giải quyết lỗi #SPILL với Excel INDEX MATCH, VLOOKUP, SUMIF và các hàm khác. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của Dịch vụ kế toán MVB vào tuần tới!
The post Lỗi #SPILL với Excel VLOOKUP, INDEX MACH và SUMIF đã được giải quyết appeared first on Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng | KetoanMVB.
https://ift.tt/3wxQ6tl Bá Teppi https://ift.tt/2SVQIdu May 20, 2021 at 10:26AM
Không có nhận xét nào:
Đăng nhận xét