Các lỗi thường gặp trong Excel: Hướng dẫn đầy đủ để hiểu và khắc phục sự cố #VALUE!, #REF!, #NAME?, #DIV/0!, #####, #NULL!, #NUM! và #N/A

Excel đã trở thành công cụ hữu ích để quản lý dữ liệu, phân tích thông tin và tự động hóa các phép tính trong mọi loại môi trường chuyên nghiệp và cá nhân. Tuy nhiên, những người sử dụng Excel thường xuyên đều biết rằng sớm hay muộn, chúng ta cũng gặp phải những thông báo lỗi bí ẩn có thể biến một bảng tính đang hoạt động thành một câu đố đầy rắc rối. biểu tượng thật khó chịu. Bạn đã bao giờ gặp phải một #COURAGE! đáng sợ hay một ##### hấp dẫn mà bạn không biết nó đến từ đâu chưa?

Trong bài viết chi tiết và thiết thực này, chúng tôi sẽ hướng dẫn bạn khám phá tất cả bí mật ẩn sau những lỗi Excel phổ biến nhất: #VALUE!, #REF!, #NAME?, #DIV/0!, #####, #NULL!, #NUM! và #N/A. Bạn sẽ biết lý do tại sao chúng xuất hiện, cách xác định chúng, nguyên nhân gây ra chúng và trên hết là các giải pháp hiệu quả để chúng không lãng phí thời gian hoặc sự kiên nhẫn của bạn. Ngoài ra, bạn sẽ biết điều tốt nhất Tricks để ngăn ngừa các lỗi trong tương lai và tận dụng các tính năng như CÓ.LỖI y SI có thể cứu bạn khi bạn ít ngờ tới nhất.

Tại sao lỗi lại xuất hiện trong Excel và những mã lạ này có ý nghĩa gì?

Làm việc với Excel đòi hỏi phải sử dụng nhiều công thức, tham chiếu, hàm và dữ liệu khác nhau. Do đó, khi có lỗi trong logic, trong các đối số hoặc trong chính định dạng dữ liệu, Excel không thể trả về kết quả mong đợi và hiển thị mã lỗi trong ô như một dấu hiệu cho thấy có điều gì đó không ổn. Những lỗi này không phải là ngẫu nhiên; mỗi lỗi đều có nguyên nhân cụ thể và biết cách diễn giải chúng là chìa khóa để tìm ra giải pháp.

Các lỗi điển hình nhất được biểu thị bằng các ký hiệu và thông báo viết hoa, thường có dấu thăng (#) ở trước, để chỉ ra loại vấn đề khiến công thức không tính toán chính xác. Hiểu được ý nghĩa của từng câu là bước đầu tiên để giải quyết chúng. Chúng ta hãy cùng khám phá từng câu một nhé!

#DŨNG CẢM! – Khi Excel không hiểu phải làm gì với dữ liệu của bạn

Lỗi #VALUE! thường xuất hiện khi công thức yêu cầu một kiểu dữ liệu cụ thể (thường là số) và gặp phải điều gì đó khác, chẳng hạn như văn bản, khoảng trắng hoặc ký tự không hợp lệ. Đây có lẽ là lỗi phổ biến nhất vì các công thức toán học của Excel yêu cầu các ô số. Nếu có bất kỳ văn bản, ký hiệu hoặc thậm chí ô trống nào bị đặt sai chỗ, công thức sẽ bị đóng băng.

• Điều gì gây ra nó? Thông thường, điều này là do cố gắng thêm, trừ hoặc nhân các ô có chứa văn bản, khoảng trắng, ô trống/bị che giấu hoặc ký tự lạ. Nó cũng có thể là do lỗi trong việc viết công thức.
• Ví dụ: Tổng giữa ô A1 (chứa '10') và ô B1 (chứa 'Pedro' hoặc ký tự đặc biệt) sẽ trả về #VALUE!.

Giải pháp thiết thực:

• Kiểm tra xem tất cả các ô được sử dụng trong công thức có đúng loại không. Nếu bạn mong đợi số, hãy kiểm tra xem không có khoảng trắng, dấu nháy đơn hoặc ký hiệu không phải số (bạn có thể sử dụng chia văn bản thành các cột trong Excel để sửa những trường hợp này).
• Nếu bạn thấy các số được căn chỉnh sang bên trái, Excel sẽ coi chúng là văn bản. Chuyển đổi các văn bản đó thành số bằng cách sử dụng lời nhắc ở góc Excel hoặc tùy chọn “Chuyển đổi thành số”.
• Đảm bảo không có ô nào khác liên quan mang lỗi trước đó: lỗi trong ô có thể lan truyền #VALUE! vào các công thức tham chiếu đến ô đó.

#REF! – Lỗi tham chiếu bị mất (và cách khôi phục!)

Một trong những lỗi khó chịu nhất là #REF!, xuất hiện khi công thức trỏ tới một ô hoặc phạm vi không còn tồn tại. Lỗi này thường xuất hiện sau khi xóa các hàng hoặc cột được tham chiếu trong công thức hoặc khi sao chép và dán sai.

• Nguyên nhân nào gây ra hiện tượng này? Xóa các ô, cột hoặc hàng được sử dụng trong công thức; sửa đổi phạm vi tìm kiếm không đúng cách; hoặc yêu cầu hàm tìm kiếm trong một cột không tồn tại.
• Lỗi này cũng có thể phát sinh khi tham chiếu đến một bảng tính Excel đã đóng (tệp) hoặc một trang tính không còn tồn tại.

Làm thế nào để sửa chữa nó?

• Nếu bạn đã nhận thấy ngay lập tức, hãy sử dụng Ctrl + Z trở lại. Bằng cách này, bạn có thể khôi phục ô, cột hoặc hàng đã xóa và lấy lại tham chiếu chức năng.
• Nếu bạn đã lưu thay đổi, bạn sẽ cần xác định tham chiếu bị thiếu và sửa công thức theo cách thủ công.
• Trong các công thức với VLOOKUP và tương tự như vậy, hãy đảm bảo rằng số cột mà bạn yêu cầu hàm thực sự tồn tại trong phạm vi đã chọn.
• Tốt hơn nên sử dụng các phạm vi (A1:A10) thay vì các tham chiếu riêng lẻ (A1; A2; A3…) để tránh mất toàn bộ logic của công thức khi xóa một ô.

#NAME? – Khi Excel không nhận dạng được hàm hoặc tên

Lỗi này cho biết Excel không thể xác định đúng tên hàm, phạm vi hoặc đối số đã nhập. Thông thường là do lỗi chính tả hoặc cú pháp hoặc do phần tử cần tìm không tồn tại.

• Nguyên nhân Posibles:
• Viết sai chính tả hàm (ví dụ: “SUM” thay vì “ADD” hoặc “CONCATNATE” thay vì “CONCATENATE”).
• Bỏ dấu ngoặc kép trong tiêu chí tìm kiếm hoặc không kết thúc chuỗi văn bản đúng cách.
• Cố gắng sử dụng một phạm vi hoặc tên chưa được xác định trước đó.
• Bỏ dấu hai chấm (:) trong tham chiếu phạm vi, khiến Excel không thể hiểu được biểu thức.

Tôi phải giải quyết nó như thế nào?

• Xem xét kỹ lưỡng và sửa tên hàm, đối số hoặc phạm vi được sử dụng. Tốt nhất là chọn các chức năng và phạm vi từ trình hướng dẫn. công thức excel để tránh viết sai.
• Tránh nhập trực tiếp chuỗi văn bản; sẽ đáng tin cậy hơn khi tham chiếu đến các ô có chứa thuật ngữ mong muốn.
• Kiểm tra xem bất kỳ tên tùy chỉnh nào bạn sử dụng (phạm vi, ô, biến) có tồn tại và được định nghĩa rõ ràng hay không.

#DIV/0! – Cẩn thận không chia cho số không (hoặc ô trống)

Nếu giáo viên toán của bạn từng cảnh báo bạn về việc không thể chia cho số 0, thì giờ đây Excel sẽ nhắc bạn bằng thông báo này: #DIV/XNUMX! Hiện tượng này xảy ra khi công thức cố gắng chia một giá trị cho số không hoặc một ô trống, và Excel thấy điều này vô nghĩa.

• Những lý do phổ biến:
• Mẫu số (ô hoặc giá trị mà bạn chia) bằng không.
• Một ô trống được gọi là số chia, được Excel hiểu là số không.

Các cách để ngăn ngừa và loại bỏ lỗi này:

• Kiểm tra xem số chia có luôn có giá trị khác không. Bạn có thể thay đổi tham chiếu sang ô khác có chứa giá trị hợp lệ.
• Nếu ô chia thường rỗng hoặc bằng 0, Sử dụng hàm IF để đánh giá tình huống đó: =SI(B3=0;"";A3/B3) Bằng cách này, bạn chỉ có thể chia nếu số chia không phải là số không.
• Một lựa chọn khác là sử dụng CÓ.LỖI, cho phép bạn trả về một giá trị khác (chẳng hạn như 0 hoặc một chuỗi rỗng) khi phép chia không thành công: =SI.ERROR(A2/A3;0)

Hãy nhớ rằng công thức này sẽ loại bỏ mọi lỗi, vì vậy nếu công thức của bạn có vấn đề khác, bạn nên xem xét kỹ lưỡng để tránh ẩn chứa những lỗi nghiêm trọng.

##### – Khi nội dung ô không phù hợp

Lỗi dấu thăng (#####) thực chất không phải là lỗi tính toán mà là tín hiệu trực quan từ Excel cho biết nội dung ô không thể hiển thị chính xác.

• Tại sao nó xảy ra? Thông thường là do số, văn bản, ngày tháng hoặc kết quả phép toán dài hơn chiều rộng ô hoặc do kết quả của phép toán ngày/giờ là số âm (Excel không thể hiển thị).

Giải pháp? Dễ thôi:

• Mở rộng chiều rộng cột từ đường viền tiêu đề hoặc sử dụng menu ngữ cảnh.
• Giảm số chữ số thập phân để làm cho số đó ngắn hơn.
• Xem lại các phép tính liên quan đến ngày và giờ: Nếu lỗi là do kết quả âm khi trừ ngày, hãy sửa lại thứ tự các ô liên quan (ví dụ: đổi =A1-B1 thành =B1-A1).

#NULL! – Khi Excel không tìm thấy giao điểm giữa các phạm vi

Lỗi #NULL! xuất hiện khi Excel không xác định được mối quan hệ chính xác giữa các phạm vi được chỉ định trong công thức. Nguyên nhân chủ yếu là do lỗi sử dụng toán tử tham chiếu hoặc dấu phân cách.

• Nó phát sinh như thế nào?
• Chỉ định sai dấu phân cách trong đối số hàm, chẳng hạn như đặt một khoảng trắng (Excel hiểu là giao điểm) giữa hai ô khi không có ô chung.
• Sử dụng dấu chấm phẩy, dấu phẩy hoặc các dấu phân cách không chính xác khác để nối các phạm vi trong công thức.

Giải pháp nhanh chóng:

• Hiệu chỉnh các dấu phân cách theo toán tử yêu cầu:
• Toán tử phạm vi là dấu hai chấm (:) để xác định từ ô này sang ô khác (ví dụ: A1:D1).
• Toán tử hợp là dấu chấm phẩy (;) để thêm các tham chiếu riêng biệt (ví dụ: =SUM(A1:D1;A2:D2)).
• Kiểm tra các ô chung nếu bạn muốn giao nhau về phạm vi hoặc thay đổi loại toán tử tùy thuộc vào mục tiêu bạn muốn đạt được.

#NUM! – Các vấn đề với số không thể

Lỗi #NUM! xảy ra khi công thức nhận được các giá trị mà nó không thể xử lý chính xác vì chúng không khả thi về mặt toán học hoặc vượt quá khả năng của Excel.

• Nguyên nhân phổ biến:
• Thử thực hiện các phép toán không được phép, chẳng hạn như căn bậc hai của một số âm (=SQRT(-2)).
• Kết quả tính toán quá lớn hoặc quá nhỏ để thể hiện trong Excel (ví dụ: hiển thị giá trị quá mức, =POWER(1000;103)).

Làm thế nào để khắc phục nó?

• Kiểm tra cẩn thận các giá trị số bạn sử dụng trong công thức. Nếu phép toán không có ý nghĩa về mặt toán học, hãy kiểm tra nó.
• Khi làm việc với các lũy thừa lớn, logarit, v.v., hãy đảm bảo không vượt quá giới hạn được Excel hỗ trợ.
• Kiểm tra định dạng ô của bạn: Đôi khi, một số được viết dưới dạng văn bản (ví dụ: “10$” thay vì “$10” theo định dạng tiền tệ) sẽ gây nhầm lẫn.

#N/A – Giá trị không khả dụng hoặc tìm kiếm không thành công

Lỗi #N/A nghĩa đen là “Không khả dụng” và thường gặp khi sử dụng các chức năng tìm kiếm (chẳng hạn như VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, v.v.) khi giá trị cần tìm không tồn tại trong phạm vi đã chỉ định hoặc có lỗi đối số hoặc định dạng.

• Những lý do điển hình:
• Giá trị cần tìm kiếm thực tế không tồn tại trong phạm vi.
• Chúng tôi đã nhập sai văn bản tìm kiếm hoặc nhập sai thời điểm, hoặc định dạng giá trị không tương thích (ví dụ: tìm kiếm số nhưng dữ liệu được lưu dưới dạng văn bản).
• Các đối số của hàm tìm kiếm có độ dài khác nhau, khiến việc so sánh trở nên bất khả thi.
• Công thức tìm kiếm có lỗi về phạm vi, tiêu chí hoặc đối số.

Tôi phải khắc phục điều này thế nào?

• Xác minh rằng dữ liệu cần tìm kiếm có tồn tại và được viết đúng chính tả. Hãy cẩn thận với các định dạng: Để tìm một số, ô có giá trị phải được định dạng là số. Đối với văn bản, cũng áp dụng tương tự.
• Xem lại các đối số trong công thức và đảm bảo các phạm vi có cùng kích thước.
• Nếu dữ liệu không tồn tại và nó đúng, bạn có thể che lỗi bằng công thức hoặc NẾU(ISERROR()) để trả về một chuỗi tùy chỉnh, chẳng hạn như “Không tìm thấy giá trị”.
• Trong các tìm kiếm phức tạp, hãy đảm bảo rằng dữ liệu được so sánh không chứa lỗi trước đó trong bất kỳ ô nào.

Ví dụ thực tế về IFERROR:
=SI.ERROR(BUSCARV(A10;A3:B5;2;0);"Valor no encontrado")

Xử lý lỗi nâng cao trong Excel: Công thức thông minh

Một trong những chìa khóa để làm việc chuyên nghiệp trong Excel không chỉ là biết cách phát hiện và sửa lỗi mà còn biết cách dự đoán chúng bằng các hàm ngăn ngừa và quản lý lỗi tự động.

Hàm IF: Kiểm soát cơ bản để tránh lỗi trong tính toán

Hàm IF cho phép bạn đưa ra quyết định hợp lý trước khi lỗi xảy ra. Ví dụ, để tránh #DIV/0!, bạn có thể sử dụng:

=SI(B3=0;"";A3/B3)

Vì vậy, nếu số chia là số 0, ô vẫn trống thay vì hiển thị lỗi.

Hàm IFERROR: Lá chắn của bạn chống lại các lỗi có thể nhìn thấy

Khi bạn gói một thao tác bên trong , Excel sẽ trả về kết quả của công thức nếu mọi thứ diễn ra bình thường hoặc, trong trường hợp có lỗi, bất kỳ kết quả nào bạn chỉ định (số không, chuỗi rỗng hoặc bất kỳ thông báo nào bạn thích):

=SI.ERROR(A2/A3;0)
=SI.ERROR(BUSCARV(A10;A3:B5;2;0);"Valor no encontrado")

Con mắt: Nó che giấu mọi lỗi, không chỉ một lỗi cụ thể; do đó, hãy sử dụng nó một cách thận trọng và kiểm tra xem các công thức có hoạt động chính xác không trước khi ẩn thông báo lỗi.

Các chức năng hữu ích khác để xử lý lỗi

• LỖI: Trả về TRUE nếu có lỗi trong kết quả; bạn có thể kết hợp nó để cung cấp các giá trị thay thế.
• KHÔNG CÓ: Trong công thức, bạn có thể trả về #N/A một cách rõ ràng bằng cách sử dụng =NA(), hữu ích để đánh dấu các giá trị không khả dụng.