[Excel] Cách khắc phục lỗi SPILL đơn giản, nhanh chóng và hiệu quả nhất

[Excel] Cách khắc phục lỗi SPILL đơn giản, nhanh chóng và hiệu quả nhất

Excel là một trong những phần mềm mà mọi dân văn phòng nào cũng phải sử dụng, đây là một phần mềm bảng tính nằm trong bộ Microsoft Office được sử dụng để nhập, tổ chức và tính toán phân tích dữ liệu, Excel cung cấp các công cụ mạnh mẽ như hàm, biểu đồ, công thức toán học và thống kê, giúp người dùng xử lý dữ liệu một cách dễ dàng và hiệu quả. Tuy nhiên, Excel vẫn hay có các lỗi vặt và trong bài viết ngày hôm nay, Xuê PC sẽ hướng dẫn bạn cách khắc phục lỗi SPILL trong Excel.

I. Lỗi SPILL trong Excel được hiểu như thế nào?

Lỗi SPILL trong Excel thường được xuất hiện khi bạn sử dụng các công thức trả về một dải kết quả (mảng động) và Excel không thể "tràn" kết quả ra các ô bên cạnh do có vật cản. Kể từ khi phiên bản Excel 365 và Excel 2019 các hàm mảng động như FILTER, UNIQUE, SORT, và SEQUENCE được giới thiệu cho phép một công thức trả về nhiều giá trị, mà Excel sẽ tự động điền vào một dải ô gọi là "spill range" - Phạm vi tràn.

II. Nguyên nhân chính dẫn đến lỗi SPILL

Lỗi SPILL trong Excel xảy ra khi một công thức mảng động không thể trả về kết quả đầy đủ do các yếu tố cản trở phạm vi tràn. Dưới đây là các nguyên nhân chính dẫn đến lỗi này:

• Ô bị chặn (Blocked Cells): Các ô trong phạm vi tràn đã chứa dữ liệu hoặc công thức khác, gây cản trở cho việc "tràn" kết quả.
• Ô hợp nhất (Merged Cells): Nếu phạm vi tràn chứa một hoặc nhiều ô đã được hợp nhất, Excel không thể tự động điền dữ liệu vào các ô này.
• Công thức mảng cũ (Legacy Array Formula): Nếu có các công thức mảng cũ (nhập bằng Ctrl+Shift+Enter) trong bảng tính, chúng có thể gây xung đột với các công thức mảng động mới.
• Phạm vi tràn ngoài giới hạn của bảng tính: Khi phạm vi tràn vượt quá số hàng hoặc cột tối đa của Excel (1.048.576 hàng hoặc 16.384 cột) kết quả sẽ không thể được hiển thị đầy đủ.
• Tham chiếu vòng lặp (Circular Reference): Khi công thức tham chiếu ngược lại chính phạm vi tràn của nó, điều này tạo ra một vòng lặp vô tận, gây lỗi.
• Dữ liệu không hợp lệ (Invalid Data): Công thức có thể tham chiếu đến dữ liệu bị lỗi hoặc không hợp lệ, như chia cho 0 hoặc lỗi #N/A, gây ra lỗi SPILL.

III. Cách khắc phục lỗi SPILL trong Excel đơn giản, nhanh chóng và tiện lợi

Để khắc phục lỗi SPILL trong Excel, bạn có thể thực hiện theo các bước dưới đây tùy thuộc vào nguyên nhân gây ra lỗi:

 Xóa hoặc di chuyển dữ liệu chặn

• Kiểm tra phạm vi tràn của công thức (Excel sẽ hiển thị vùng tràn bằng đường viền chấm xung quanh). Nếu có bất kỳ ô nào trong phạm vi đó chứa dữ liệu hoặc công thức khác, bạn cần xóa hoặc di chuyển chúng.
  • Cách thực hiện: Di chuyển dữ liệu ra khỏi phạm vi tràn hoặc xóa chúng đi nếu không cần thiết.

 Bỏ hợp nhất ô

• Nếu phạm vi tràn có chứa ô hợp nhất, Excel không thể điền dữ liệu vào các ô này. Bạn cần tách các ô hợp nhất.
  • Cách thực hiện: Chọn ô hoặc dải ô hợp nhất → Home → Merge & Center → chọn Unmerge Cells (Bỏ hợp nhất ô).

 Kiểm tra phạm vi công thức

• Kiểm tra xem phạm vi mà công thức tham chiếu có vượt quá giới hạn số hàng hoặc số cột của Excel (Excel chỉ hỗ trợ tối đa 1.048.576 hàng và 16.384 cột).
  • Cách thực hiện: Điều chỉnh công thức hoặc phạm vi để phù hợp với giới hạn của bảng tính.

 Loại bỏ công thức mảng cũ

• Nếu có công thức mảng cũ (Ctrl+Shift+Enter) trong phạm vi tràn, bạn cần thay thế nó bằng công thức mảng động mới (chỉ cần nhấn Enter với các hàm mảng động như FILTER, UNIQUE, SORT).
  • Cách thực hiện: Xóa công thức mảng cũ và sử dụng công thức mảng động thay thế.

 Sửa lỗi tham chiếu vòng lặp

• Nếu công thức tham chiếu lại chính nó (tạo vòng lặp), bạn cần điều chỉnh tham chiếu để không tạo ra vòng lặp.
  • Cách thực hiện: Kiểm tra và sửa đổi công thức để loại bỏ tham chiếu vòng.

 Kiểm tra lỗi trong dữ liệu

• Công thức có thể trả về lỗi do dữ liệu không hợp lệ (ví dụ, chia cho 0 hoặc tham chiếu tới giá trị lỗi như #N/A).
  • Cách thực hiện: Sử dụng hàm như IFERROR hoặc IF để xử lý dữ liệu lỗi, hoặc kiểm tra lại nguồn dữ liệu của công thức.

 Giảm kích thước mảng hoặc phạm vi công thức

• Nếu công thức cố gắng trả về một mảng quá lớn cho bảng tính, hãy thu nhỏ phạm vi tràn của công thức.
  • Cách thực hiện: Giảm số lượng hàng/cột mà công thức trả về hoặc điều chỉnh phạm vi tính toán.

 Kiểm tra định dạng công thức

• Đảm bảo công thức không bị định dạng sai hoặc có ký tự thừa gây lỗi.

Sau khi thực hiện các bước trên, lỗi SPILL sẽ được khắc phục và công thức sẽ trả về kết quả mong muốn.

Tìm hiểu thêm >>> [CS:GO2] Hướng dẫn thiết lập Radar trong Counter Strike 2 - 2024

IV. Một số câu hỏi thường gặp về lỗi SPILL trong Excel

Dưới đây là một số câu hỏi thường gặp về lỗi SPILL trong Excel, cùng với các câu trả lời chi tiết:

 Lỗi SPILL là gì?

Lỗi SPILL trong Excel xảy ra khi một công thức mảng động không thể trả về kết quả vào phạm vi mong muốn do bị cản trở. Điều này có thể do các ô đã có dữ liệu, ô hợp nhất, hoặc các vấn đề khác ngăn cản kết quả được tràn vào vùng xung quanh.

 Làm thế nào để biết phạm vi tràn bị chặn?

Khi xảy ra lỗi SPILL, Excel sẽ đánh dấu phạm vi mà công thức dự kiến sẽ tràn kết quả vào bằng đường viền chấm xung quanh. Nếu trong phạm vi này có dữ liệu, bạn sẽ thấy thông báo lỗi, cho biết ô nào đang gây ra vấn đề.

 Tại sao ô hợp nhất lại gây lỗi SPILL?

Ô hợp nhất không thể chứa nhiều giá trị khác nhau từ một công thức mảng động. Do đó, nếu một công thức cần trả về nhiều kết quả vào các ô đã hợp nhất, Excel không thể phân bổ kết quả, dẫn đến lỗi SPILL.

 Làm sao để khắc phục lỗi SPILL khi có ô bị chặn?

Để khắc phục, bạn cần xác định các ô trong phạm vi tràn và kiểm tra xem chúng có chứa dữ liệu hay không. Nếu có, hãy xóa hoặc di chuyển dữ liệu ra khỏi phạm vi tràn để công thức có thể tràn kết quả vào đúng vị trí.

 Công thức mảng cũ có gây lỗi SPILL không?

Có. Các công thức mảng cũ (nhập bằng Ctrl+Shift+Enter) không tương thích với công thức mảng động mới. Nếu trong phạm vi tràn có công thức mảng cũ, bạn cần xóa nó và thay thế bằng các công thức mảng động hiện đại.

 Làm thế nào để sửa lỗi SPILL khi có tham chiếu vòng lặp?

Tham chiếu vòng lặp xảy ra khi công thức mảng tham chiếu ngược lại chính nó, tạo ra một vòng lặp vô hạn. Để sửa lỗi, bạn cần điều chỉnh công thức sao cho không tham chiếu lại chính nó hoặc phạm vi mà nó đang cố tràn kết quả vào.

 Excel có giới hạn nào liên quan đến lỗi SPILL không?

Có, nếu công thức mảng động trả về một phạm vi kết quả lớn hơn giới hạn hàng (1.048.576 hàng) hoặc cột (16.384 cột) của Excel, kết quả sẽ không thể hiển thị và dẫn đến lỗi SPILL. Trong trường hợp này, bạn cần giảm kích thước mảng kết quả.

 Tại sao lỗi SPILL xảy ra khi sử dụng hàm FILTER hoặc UNIQUE?

Các hàm như FILTER, UNIQUE, SORT trả về nhiều kết quả vào phạm vi tràn. Nếu vùng tràn bị chặn bởi dữ liệu, ô hợp nhất hoặc các vấn đề khác, Excel không thể hoàn tất phép tính và sẽ báo lỗi SPILL.

 Có thể ngăn ngừa lỗi SPILL bằng cách nào?

Để ngăn ngừa lỗi SPILL, hãy đảm bảo phạm vi tràn của công thức mảng không bị chặn, không có ô hợp nhất, và kiểm tra các tham chiếu công thức để tránh lỗi tham chiếu vòng lặp hoặc dữ liệu lỗi.

 Có công cụ nào trong Excel hỗ trợ xác định lỗi SPILL không?

Khi xảy ra lỗi SPILL, Excel cung cấp một thông báo cụ thể và vùng tràn sẽ được đánh dấu bằng đường viền chấm xung quanh. Điều này giúp bạn dễ dàng xác định phạm vi tràn và các vấn đề cản trở công thức mảng động.

Những câu hỏi này giúp bạn hiểu sâu hơn về lỗi SPILL trong Excel và cách khắc phục hiệu quả.

V. Tổng Kết

 Trên đây là bài viết hướng dẫn bạn fix lỗi SPILL trong excel, hy vọng những thông tin trên đây sẽ có ích tới bạn, bên cạnh đó nếu anh em đang có nhu cầu xây dựng cấu hình máy tính để sử dụng cho công việc văn phòng, mục đích cá nhân thì đừng quên ghé qua Xuê PC Store nhé, tại đây đang có rất nhiều chương trình khuyến mại đang được triển khai. Đừng quên theo dõi Xuê PC để không bỏ lỡ các tin tức thú vị trong ngày nhé.