Gửi Email
Liên hệ Zalo
Nội dung bài viết
Có nhiều người dùng phần mềm Microsoft Excel vẫn nhầm tưởng là #SPILL là một lỗi và thắc mắc rằng lỗi #SPILL là lỗi gì? Tuy nhiên, trên thực tế thì SPILL trong Excel là một phần của tính năng Spilled array và được Microsoft giới thiệu phần mềm Excel trong bộ công cụ Microsoft 365. Để giúp cho bạn có thể hiểu rõ được SPILL là lỗi gì và giúp hướng dẫn bạn tối ưu hóa việc sử dụng tính năng Spilled array với những dữ liệu phức tạp, MSO sẽ cung cấp bài viết này với toàn bộ thông tin về tính năng Spilled array và giúp bạn khắc phục lỗi SPILL trong Excel.
Lỗi #SPILL là lỗi gì trong Excel?
Trong quá trình sử dụng phần mềm Excel để phân tích và xử lý dữ liệu, nhiều người dùng thường hay thắc mắc và không biết lỗi #SPILL là lỗi gì trong Excel. Tuy nhiên, lỗi #SPILL! trong Excel không hẳn là một lỗi “sai sót” mà thực chất là một thông báo liên quan đến tính năng mảng động Spilled Array. #SPILL thường được trả về khi công thức mà người dùng thực hiện trả về nhiều kết quả, nhưng Excel không thể trả kết quả “tràn” tất cả các kết quả của công thức đó vào các ô lân cận.
Tính năng Spilled Array cho phép một công thức tự động điền kết quả vào nhiều ô liền kề mà không cần người dùng thực hiện các thao tác thủ công để kéo hoặc sao chép công thức các ô khác liền kề, kết quả sẽ tạo nên một phạm vi tràn sang các ô lân cận. Điều này giúp tăng cường hiệu suất làm việc và tiết kiệm thời gian cho các thao tác lặp đi lặp lại cho người dùng trong bảng tính Excel.
Trong quá trình sử dụng, nếu như Excel không đủ không gian trống để chứa tất cả các kết quả do một công thức mảng động tạo ra hoặc do các ô bị chặn bởi những dữ liệu khác, do ô gộp hay do mở rộng ra ngoài rìa bảng tính, thì lỗi #SPILL sẽ được trả về thay vào đó.
Dưới đây là các khái niệm chính về tính năng Spill và lỗi #SPILL:
- Phạm vi tràn (Spill Range): Là vùng các ô mà kết quả trả về từ công thức mảng động sẽ tự động điền vào.
- Công thức mảng động (Dynamic Array Formulas): Là các hàm như FILTER(), SORT() và SEQUENCE(). Đây là những hàm mới được cung cấp trong các phiên bản Microsoft Excel 365 trở lên, các hàm này có khả năng trả về nhiều kết quả vào nhiều ô trong cùng một lúc thay cho việc kéo và sao chép công thức đến các ô khác.
- Lỗi #SPILL: Xảy ra khi công thức mảng động cần “tràn” dữ liệu và trả về kết quả trong nhiều ô lân cận, nhưng lại gặp phải lỗi và không thể trả về kết quả trong phạm vi cần tràn.
Ví dụ: Giả sử bạn nhập công thức =SEQUENCE(5) vào ô A1, Excel sẽ giúp bạn tràn kết quả là từ 1 đến 5 cho các ô từ ô A1 đến ô A5. Nhưng nếu như A3 đang chứa dữ liệu, phần mềm Excel sẽ trả về lỗi #SPILL!.
Với những thông tin được chúng tôi cung cấp phía trên đây, bạn đã có thể nắm rõ được lỗi SPILL là lỗi gì rồi. Để biết rõ với về lỗi này cũng như những nguyên nhân dẫn đến lỗi đó, bạn hãy tiếp tục theo dõi nội dung được chia sẻ chi tiết trong bài viết này nhé.
Nguyên nhân xảy ra lỗi SPILL trong Excel
Lỗi #SPILL trong Excel có thể xảy ra do nhiều nguyên nhân khác nhau khi người dùng sử dụng các công thức tràn nhưng Excel không thể xử lý được. Dưới đây là thông tin chi tiết về các nguyên nhân phổ biến dẫn đến lỗi #SPILL trong Excel và giải thích chi tiết của chúng tôi về các nguyên nhân để giúp bạn nắm rõ hơn về lỗi Excel này:
Phạm vi tràn của kết quả bị chặn
Khi Excel cố gắng tràn kết quả vào các ô lân cận của công thức mảng mà bạn đã nhập, nhưng có một vài ô trong vùng mà công thức muốn tràn kết quả vào đã chứa dữ liệu, thì công thức đó sẽ không thể thực hiện việc tràn kết quả và báo lỗi #SPILL trong Excel.
Ví dụ: Bạn nhập công thức mảng =SEQUENCE(5) vào ô A1, tuy nhiên ô A4 đã chứa dữ liệu là “Excel” và ô A5 chứa dữ liệu là “Microsoft 365”. Dự kiến, kết quả của công thức trên sẽ trả về lần lượt từ 1 đến 5 vào các ô A1 đến A5, nhưng trong vùng dữ liệu từ A1:A5 có một vài ô đã có dữ liệu, nên công thức không thể thực hiện tràn kết quả được mà trả về lỗi #SPILL.
Phạm vi tràn có ô hợp nhất
Phần mềm Microsoft Excel không hỗ trợ việc tràn kết quả vào các ô đã được gộp (merged cells). Vì thế, nếu như phạm vi cần trả kết quả của công thức mà bạn đã nhập có một vài ô đã bị hợp nhất với các ô khác, kết quả của công thức đó sẽ không thể tràn vào các ô bị hợp nhất được, mà sẽ xuất hiện lỗi #SPILL. Chính vì thế, bạn cần phải kiểm tra các phạm vi cần trả kết quả trước khi sử dụng các công thức mảng, đảm bảo phạm vi trả kết quả đó không có bất cứ ô hợp nhất nào.
Ví dụ: Nếu như bạn nhập công thức mảng =SEQUENCE(5) vào ô A1, kết quả dự kiến sẽ được trả về vào các ô từ A1 đến A5. Tuy nhiên, trong phạm vi A1:A5 của bảng dữ liệu đó có chứa ô hợp nhất giữa ô A4 và A5, vì thế kết quả của công thức sẽ không được tràn vào phạm vi A1:A5, mà sẽ xuất hiện lỗi #SPILL.
Sử dụng công thức mảng động trong bảng Table
Việc sử dụng các công thức mảng động trong bảng dữ liệu Table cũng là một nguyên nhân dẫn đến lỗi #SPILL xảy ra trong bảng tính Excel đó của bạn. Bởi vì, hiện tại phần mềm Microsoft Excel chưa được hỗ trợ các công thức mảng động cho vùng dữ liệu của định dạng Table. Nếu bạn sử dụng các hàm như FILTER(), SORT() và SEQUENCE() để tạo công thức mảng động trong bảng table của mình, thì kết quả sẽ báo lỗi #SPILL! cho công thức đó của bạn.
Bên cạnh đó, một số hàm như RANDARRAY() có thể sẽ thay đổi kích thước của mảng, nó sẽ khiến cho kích thước mảng động không được ổn định và dẫn đến lỗi #SPILL!.
Ví dụ: Giả sử bạn đang có một bảng dữ liệu với phạm vi A1:E6, trong đó cột đầu tiên (Cột A) chứa số thứ tự của từng sinh viên và bạn muốn trả về kết quả đã được thêm vào trong cột A đó bằng cách sử dụng hàm SORT(). Bởi vì hàm SORT() là một hàm mảng động, nền nó không được hỗ trợ hoạt động trong phạm vi vùng dữ liệu là dạng bảng Table. Vì thế, kết quả sẽ trả về là lỗi #SPILL!.
Phạm vi tràn của kết quả quá lớn
Nếu kết quả tràn mà bạn muốn trả về cho một công thức có quá nhiều dòng hoặc cột, vượt quá kích thước sẵn có của bảng tính Excel của bạn, kết quả sẽ không được hiển thị mà thay vào đó là lỗi #SPILL sẽ xuất hiện tại ô mà bạn đã nhập công thức.
Ví dụ: Bảng tính của bạn đang có số lượng dòng là 10 và số lượng cột là 6, những bạn đang sử dụng công thức mảng để trả về kết quả tràn với số dòng là 11 và số cột là 7, thì lúc này phần mềm Excel sẽ không thể xử lý và trả về kết quả mà sẽ báo lỗi #SPILL!.
Trên đây là một số nguyên nhân phổ biến dẫn đến lỗi #SPILL, bên cạnh những nguyên nhân này ra, thì vẫn còn một số các nguyên nhân khác khiến cho kết quả tràn của công thức là #SPILL. Bao gồm như khi bạn thực hiện tham chiếu đến một hoặc nhiều ô không thể xác định được hay ô tham chiếu trong công thức đã bị xóa và cũng có thể xảy ra khi có sự xung đột giữa các công thức hàm với nhau.
Để khắc phục được lỗi SPILL trong Excel, bạn cần phải kiểm tra vùng tràn hoặc công thức, đồng thời hãy đảm bảo rằng không phải là một trong những trường hợp được chúng tôi đề cập phía trên. Hãy theo dõi ngay những hướng dẫn trong phần tiếp theo của bài viết này để biết được các cách thức khắc phục lỗi SPILL trong Excel nhanh chóng và hiệu quả nhất nhé.
Các cách khắc phục lỗi SPILL trong Excel hiệu quả
Để xử lý và khắc phục lỗi #SPILL trong Excel một cách hiệu quả nhất, bạn cần phải kiểm tra lại vùng kết quả và hãy xóa các dữ liệu cản trở khi chúng không cần thiết hoặc di chuyển công thức mảng đến vùng trống, nơi mà có đủ không gian để hiển thị kết quả. Bên cạnh đó, bạn cũng cần đặc biệt lưu ý rằng không nên sử dụng công thức mảng trong vùng dữ liệu được định dạng bảng Table, nếu cần thì hãy chuyển bảng đó về dạng phạm vi dữ liệu bình thường thông qua tính năng Convert to Range.
Để biết được cách khắc phục lỗi #SPILL trong Excel chi tiết, hãy theo dõi nội dung hướng dẫn của chúng tôi trong các phần phía dưới đây nhé:
Xóa tất cả các ô bị chặn
Việc giải phóng không gian và xóa các dữ liệu hay ký tự trong phạm vi cần trả về kết quả của công thức sẽ giúp bạn đảm bảo cho công thức có đủ chỗ trống để trả về kết quả thay vì báo lỗi #SPILL!. Vì thế, nếu gặp lỗi #SPILL! trong Excel, bạn hãy di chuyển hoặc xóa tất cả những dữ liệu đang cản trở trong vùng hiển thị kết quả của công thức là có thể khắc phục được lỗi.
Các thao tác thực hiện, bạn hãy tiến hành lần lượt theo hướng dẫn chi tiết dưới đây của chúng tôi:
– Bước 1: Đầu tiên, bạn hãy kiểm tra vùng mà công thức mảng động đang trả về kết quả.
– Bước 2: Nếu thấy có ô chứa dữ liệu, bạn hãy xóa hoặc di chuyển dữ liệu đó.
- Để xóa dữ liệu trong ô, bạn hãy nhấn vào ô đó >> rồi nhấn phím Delete. Hoặc nhấn chuột phải vào ô đó >> rồi chọn mục Delete trong danh sách các tùy chọn được hiển thị trên màn hình.
- Để di chuyển ô chứa dữ liệu qua một vị trí khác, bạn hãy nhấn vào ô chứa dữ liệu đó >> rồi nhấn tổ hợp phím Ctrl + X >> di chuyển chuột và nhấn vào vị trí cần chuyển dữ liệu đến >> rồi nhấn tổ hợp phím Ctrl + V.
* Mẹo: Để biết những ô nằm trong vùng kết quả được trả về bởi công thức của mình, bạn hãy nhấn chọn chuột vào ô chứa công thức đó, bảng tính Excel sẽ hiển thị vùng tràn bằng đường viền nét đứt. Lúc này, bạn hãy kiểm tra xem trong vùng tràn đó có ô nào đang chứa dữ liệu không, nếu có hãy tiến hành xóa hoặc di chuyển nó đến nơi khác mà không ảnh hưởng đến vùng kết quả của công thức bạn nhập.
Ngay sau đó, lỗi #SPILL! đã được khắc phục và kết quả của công thức mảng đó sẽ được hiển thị như trong hình dưới đây:
Như vậy, nếu như nguyên nhân gây lỗi #SPILL! trong Excel là do vùng tràn kết quả của công thức mảng đang có một số ô chứa dữ liệu, bạn có thể thực hiện những thao tác phía trên đây để khắc phục lỗi này sao cho nhanh chóng và hiệu quả nhất
Bỏ hợp nhất ô trong vùng kết quả
Nếu như ô trong vùng kết quả mà công thức trả về chứa một vài ô hợp nhất, kết quả của công thức mảng sẽ trả về là lỗi #SPILL!. Vì thế, để sửa chữa và khắc phục được lỗi #SPILL! trong trường hợp này, bạn cần phải kiểm tra và đảm bảo rằng không có bất kỳ ô hợp nhất nào nằm trong phạm vi mà công thức trả về dữ liệu.
Để kiểm tra xem có ô hợp nhất nào nằm trong phạm vi trả về kết quả của công thức hay không, bạn chỉ cần thực hiện thao tác như sau:
- Đầu tiên, hãy nhấn chuột vào ô chứa công thức mảng đó trong bảng tính của mình.
- Sau đó, màn hình sẽ hiển thị vùng tràn mà công thức trả về kết quả nằm trong vùng nét đứt như hình sau đây.
- Lúc này, bạn chỉ cần kiểm tra xem có ô hợp nhất nào nằm trong vùng này hay không. Nếu có, bạn chỉ cần hủy bỏ hợp nhất cho các ô đó là hoàn tất.
Nếu có ô hợp nhất, bạn hãy bỏ hợp nhất cho các ô đó bằng cách thực hiện các bước như sau:
- Bạn hãy nhấn chuột vào ô đang bị hợp nhất >> sau đó nhấn chọn thẻ Home trên thanh Ribbon.
- Tiếp theo, bạn hãy điều hướng đến và nhấn chọn vào mục Alignment >> rồi nhấn chọn mục Merge & Center là hoàn tất.
Ngay sau đó, ô trong vùng tràn kết quả của công thức sẽ được hủy hợp nhất và hiển thị các kết quả mà không phải lỗi #SPILL!.
Như vậy, nếu như lỗi #SPILL! do ô hợp nhất trong vùng tràn kết quả gây ra, bạn có thể thực hiện theo những thao tác được chúng tôi hướng dẫn trên đây để hủy hợp nhất cho ô và khắc phục lỗi SPILL trong Excel này đó nhé.
Khắc phục lỗi #SPILL trong bảng Table
Như chúng tôi đã đề cập trong phần nguyên nhân, việc sử dụng công thức mảng động trong vùng dữ liệu có định dạng là bảng Table có thể sẽ gây ra lỗi #SPILL! trong file Excel của bạn. Nếu như bạn vẫn muốn sử dụng công thức mảng cho vùng dữ liệu đó và khắc phục lỗi SPILL, bạn có thể chuyển bảng Table về dạng vùng dữ liệu như mặc định, điều này sẽ đảm bảo cho công thức được hoạt động bình thường. Bên cạnh đó, bạn cũng có thể đặt công thức mảng đó ở ngoài phạm vi bảng table để trả về kết quả chính xác mà không phải là lỗi SPILL.
Để khắc phục lỗi SPILL trong Excel với trường hợp này, bạn hãy tiến hành các bước thực hiện được chúng tôi hướng dẫn chi tiết dưới đây:
Cách 1: Đặt công thức ở bên ngoài bảng Table để sửa lỗi SPILL.
- Bạn hãy di chuyển chuột và nhấn vào một ô tại vùng dữ liệu ngoài phạm vi bảng table và có không gian rộng hơn.
- Sau đó, bạn hãy nhập công thức mảng mà mình muốn vào ô đó như bình thường >> rồi nhấn phím Enter để hiển thị kết quả.
Kết quả của công thức sẽ được trả về như hình dưới đây mà không gặp lỗi #SPILL!.
Cách 2: Chuyển đổi vùng dữ liệu từ dạng Table sang phạm vi bình thường.
- Đầu tiên, bạn hãy nhấn chuột vào một ô bất kỳ trên bảng Table của mình.
- Sau đó, bạn hãy nhấn chọn tab chọn vào mục Table Design (Thiết kế bảng) nằm trên thanh công cụ Ribbon.
- Tiếp đến, bạn hãy điều hướng đến nhóm công cụ Tools >> rồi nhấn chọn mục Convert to Range để chuyển đổi bảng Table thành dải ô là hoàn tất.
Ngoài cách trên, bạn có thể chuyển đổi từ bảng có định dạng Table sang dạng dải ô mặc định bằng cách: Nhấn chuột trái vào bảng Table >> sau đó chọn tùy chọn Table trong danh sách >> rồi nhấn chọn mục Convert to Range là hoàn tất.
Lúc này, bảng dữ liệu của bạn sẽ được chuyển đổi từ dạng Table sang thành vùng dữ liệu như mặc định. Bạn hãy thực hiện lại công thức để nhận được kết quả như mong muốn mà không phải lỗi #SPILL!.
Như vậy, nếu bạn cần thực hiện công thức mảng tham chiếu với bảng dữ liệu của mình, bạn nên chuyển bảng dữ liệu đó thành phạm vi dữ liệu như mặc định hoặc tiến hành nhập công thức vào một vị trí khác trong bảng tính Excel để nhận kết quả mà không phải là lỗi SPILL.
Thực hiện cẩn thận khi tham chiếu
Một trường hợp khác dẫn đến lỗi #SPILL! trong Excel đó chính là khi bạn thực hiện tham chiếu đến toàn một cột hoặc hàng của bảng tính. Khi thực hiện tham chiếu như thế này, phạm vi kết quả của công thức sẽ tràn ra quá kích thước của bảng tính, đây cũng chính là nguyên nhân dẫn đến lỗi SPILL trong Excel thay vì hiển thị kết quả như bình thường.
Vì thế, khi sử dụng công thức mảng để tham chiếu, bạn chỉ nên tham chiếu dữ liệu trong phạm vi dữ liệu cần thiết mà không phải là toàn bộ hàng hoặc cột trong bảng tính. Điều này có thể mất một chút thời gian để bạn nhập địa chỉ phạm vi chính xác của vùng tham chiếu, nhưng nó sẽ giúp bạn có thể nhận được một kết quả chính xác mà không phải là lỗi #SPILL.
Trên đây là phần tổng hợp và hướng dẫn chi tiết của chúng tôi về các cách xử lý lỗi #SPILL! trong Excel, bạn có thể tham khảo và thực hiện theo những cách thức này để đảm bảo sửa lỗi một cách chính xác và hiệu quả nhất nhé.
Mẹo để hạn chế gặp lỗi SPILL trong Excel
Khi gặp lỗi #SPILL trong Excel, nó có thể sẽ làm gián đoạn quá trình phân tích, xử lý và làm việc với những dữ liệu của mình thông qua các công thức mảng động trong Excel. Để hạn chế gặp phải lỗi này, bạn hãy áp dụng và tham khảo những mẹo sử dụng được chúng tôi cung cấp phía dưới đây:
- Hãy luôn kiểm tra vùng trống và kiểm tra số cột hoặc số hàng trước khi nhập công thức mảng. Điều này sẽ giúp bạn đảm bảo rằng không có bất kỳ dữ liệu nào ngăn chặn kết quả tràn của công thức mà bạn sử dụng và đảm bảo có đủ không gian hiển thị cho kết quả của công thức mà không gặp lỗi.
- Không sử dụng công thức mảng trong bảng dữ liệu có định dạng Table, vì hiện tại phần mềm Excel chưa cho phép người dùng sử dụng công thức mảng trong bảng Table. Nếu cần tham chiếu đến các dữ liệu trong bảng đó, bạn nên nhập công thức ở bên ngoài bảng hoặc chuyển bảng Table đó sang định dạng phạm vi dữ liệu mặc định để các công thức mảng không bị lỗi.
- Hãy sử dụng toán tử @ khi chỉ cần lấy một kết quả thay vì toàn bộ mảng, điều này giúp bạn hạn chế được lỗi tràn không mong muốn.
- Luôn kiểm tra và sửa lỗi ngay sau khi nhập công thức hoặc khi có một số thay đổi đối với bảng dữ liệu của mình, điều này sẽ giúp đảm bảo cho kết quả được chính xác mà không gây lỗi sau này.
Các câu hỏi thường gặp
Làm thế nào để tránh lỗi #SPILL khi làm việc với dữ liệu lớn trong Excel?
Để tránh gặp phải lỗi #SPILL khi làm việc với dữ liệu lớn trong Excel, bạn hãy giới hạn phạm vi công thức, kiểm tra vùng tràn của kết quả trước khi nhập công thức, tránh dùng công thức mảng động trong bảng table và có thể sử dụng các hàm kiểm soát kết quả. Điều này sẽ giúp bạn kiểm soát kết quả tốt hơn và tránh xảy ra lỗi SPILL khi sử dụng công thức mảng động.
Làm thế nào để phát hiện và sửa lỗi #SPILL trong Excel tự động?
Hiện tại Excel chưa có công cụ tự động sửa lỗi này, nhưng bạn có thể dùng Evaluate Formula để kiểm tra từng bước tính toán để xác định lỗi. Bên cạnh đó bạn cũng có thể nhận biết được lỗi thông qua biểu tượng ⚠️ bên cạnh ô lỗi hoặc bạn cũng có thể kiểm tra vùng tràn bằng cách theo dõi đường viền nét đứt được hiển thị khi chọn ô công thức. Các bước này sẽ giúp bạn phát hiện lỗi SPILL trong Excel, tìm ra nguyên nhân và cách để khắc phục nhanh chóng và hiệu quả nhất.
Làm sao để nhận biết được lỗi #SPILL trong Excel và các lỗi khác?
Lỗi #SPILL! có sự khác biệt lớn đối với các lỗi như #REF!, #VALUE!, #NAME? trong Excel. Lỗi #SPILL! trong Excel là một lỗi đặc thù của mảng động, còn các lỗi khác thường liên quan đến tham chiếu sai, kiểu dữ liệu không hợp lệ hoặc khi viết sai tên hàm và công thức. Để nhận biết được lỗi SPILL trong Excel, bạn hãy kiểm tra tại ô nhập công thức, ô đó sẽ hiển thị #SPILL! khi gặp lỗi.
Xem thêm: Lỗi #REF Excel là gì? Hướng dẫn sửa lỗi #REF đơn giản, dễ hiểu
Lời kết
Trên đây là thông tin chi tiết về lỗi SPILL trong Excel được cung cấp từ MSO, qua đó giúp bạn có thể hiểu rõ được lỗi SPILL là lỗi gì trong Excel, đồng thời cũng có thể nắm rõ được những nguyên nhân và các cách khắc phục lỗi SPILL trong Excel hiệu quả nhất. Nếu có vấn đề gì cần được chúng tôi giải đáp, bạn hãy liên hệ ngay với chúng tôi qua những thông tin liên hệ sau đây để được hỗ trợ và giải đáp chi tiết nhất nhé.
———————————————————
Fanpage: MSO.vn – Microsoft 365 Việt Nam
Hotline: 024.9999.7777
