Phát hiện lỗi trong các công thức

Bảng sau tóm tắt một số lỗi phổ biến nhất mà người dùng có thể gây ra khi nhập công thức và giải thích cách sửa lỗi đó.

Hãy đảm bảo bạn	Thêm thông tin
Bắt đầu mọi hàm với dấu bằng (=)	Nếu bạn bỏ dấu bằng, những gì bạn nhập có thể được hiển thị dưới dạng văn bản hoặc ngày tháng. Ví dụ: nếu bạn nhập SUM(A1:A10), Excel sẽ hiển thị chuỗi văn bản SUM(A1:A10) mà không thực hiện tính toán. Nếu bạn nhập 11/2, Excel sẽ hiển thị ngày Ngày 2 Tháng Mười một (giả sử định dạng ô là Chung) thay vì chia 11 cho 2.
Khớp tất cả các dấu mở ngoặc và đóng ngoặc	Hãy đảm bảo tất cả các dấu ngoặc đơn đều là một phần trong một cặp dấu khớp nhau (mở và đóng). Khi bạn sử dụng hàm trong công thức, điều quan trọng là mỗi dấu ngoặc đơn phải ở đúng vị trí để hàm hoạt động chính xác. Ví dụ: công thức =IF(B5<0),"Không hợp lệ",B5*1.05) sẽ không hoạt động do có hai dấu đóng ngoặc và chỉ có một dấu mở ngoặc trong khi chỉ nên có mỗi loại một dấu. Công thức phải có dạng như thế này: =IF(B5<0,"Không hợp lệ",B5*1.05).
Sử dụng dấu hai chấm để chỉ phạm vi	Khi bạn tham chiếu đến một phạm vi ô, sử dụng dấu hai chấm (:) để phân tách tham chiếu tới ô đầu tiên trong phạm vi và tham chiếu tới ô cuối cùng trong phạm vi. Ví dụ: =SUM(A1:A5), không phải =SUM(A1 A5), hàm này sẽ trả về kết quả #NULL! Lỗi.
Nhập tất cả các tham đối bắt buộc	Một số hàm không có đối số bắt buộc. Ngoài ra, đảm bảo rằng bạn không nhập quá nhiều đối số.
Nhập đúng loại đối số	Một số hàm như SUM yêu cầu tham đối dạng số. Các hàm khác như REPLACE yêu cầu giá trị văn bản cho ít nhất một tham đối của mình. Nếu bạn sử dụng sai kiểu dữ liệu làm đối số, hàm Excel trả về kết quả không mong muốn hoặc hiển thị lỗi.
Không lồng quá 64 hàm	Bạn có thể nhập hoặc lồng tối đa 64 mức hàm trong một hàm.
Đặt các tên trang tính khác vào dấu ngoặc đơn	Nếu công thức tham chiếu đến các giá trị hay ô trên trang tính hay sổ làm việc khác và tên của sổ làm việc hay trang tính còn lại này chứa khoảng trắng hoặc các ký tự không thuộc bảng chữ cái thì bạn phải đặt tên đó trong dấu ngoặc đơn ( ' ), như ='Dữ liệu Quý'!D3 hoặc =‘123’!A1.
Đặt dấu chấm than (!) sau tên trang tính khi bạn tham chiếu đến trang tính đó trong công thức	Ví dụ, để trả về giá trị từ ô D3 trong trang tính có tên Dữ liệu Hàng quý trong cùng sổ làm việc, hãy dùng công thức này: ='Dữ liệu Hàng quý'!D3.
Bao gồm cả đường dẫn đến sổ làm việc ngoài	Hãy đảm bảo mỗi tham chiếu ngoài đều chứa tên sổ làm việc và đường dẫn đến sổ làm việc đó. Tham chiếu đến sổ làm việc bao gồm tên sổ làm việc và phải nằm trong dấu ngoặc vuông ([Tên_sổ_làm_việc.xlsx]). Tham chiếu cũng phải chứa tên trang tính trong sổ làm việc. Nếu sổ làm việc bạn muốn tham chiếu đến không mở trong Excel, bạn vẫn có thể đưa tham chiếu đó vào công thức. Bạn cung cấp đường dẫn đầy đủ đến tệp, như trong ví dụ sau: =ROWS('C:\Tài liệu của Tôi\[Hoạt động Q2.xlsx]Doanh số'!A1:A8). Công thức này trả về số hàng trong phạm vi bao gồm các ô A1 đến A8 trong sổ làm việc còn lại (8). Lưu ý: Nếu đường dẫn đầy đủ chứa các ký tự khoảng trắng, như trong ví dụ trước, bạn phải đặt đường dẫn trong dấu ngoặc đơn (ở đầu đường dẫn và sau tên trang tính, trước dấu chấm than).
Nhập số mà không định dạng	Không định dạng số khi bạn nhập số vào công thức. Ví dụ: nếu giá trị bạn muốn nhập là $1.000, hãy nhập 1000 vào công thức. Nếu bạn nhập dấu phẩy như một phần của số, Excel sẽ hiểu dấu phẩy là kí tự phân tách. Nếu bạn muốn các số hiển thị dấu phẩy dưới dạng có dấu tách hàng nghìn, hàng triệu hoặc ký hiệu tiền tệ, hãy định dạng ô sau khi bạn nhập số. Ví dụ, nếu bạn muốn thêm 3100 vào giá trị trong ô A3 và bạn nhập công thức =SUM(3.100,A3), Excel sẽ cộng các số 3 và 100 rồi cộng tổng đó vào giá trị từ A3, thay vì cộng 3100 vào Ô A3, vốn sẽ là =SUM(3100,A3). Hoặc nếu bạn nhập công thức =ABS(-2,134), Excel sẽ hiển thị lỗi do hàm ABS chỉ chấp nhận một tham đối: =ABS(-2134).

Bạn có thể thực hiện một số quy tắc để kiểm tra lỗi trong công thức. Các công thức này không đảm bảo rằng trang tính sẽ không có lỗi nhưng chúng có thể giúp bạn tìm được các lỗi thường gặp. Bạn có thể bật hoặc tắt riêng từng quy tắc trong số này.

Lỗi có thể được đánh dấu và sửa theo hai cách: từng lỗi một (như trình soát chính tả) hoặc ngay khi xảy ra lỗi trên trang tính lúc nhập dữ liệu.

Bạn có thể giải quyết lỗi bằng cách sử dụng các tùy chọn Excel hiển thị hoặc bạn có thể bỏ qua lỗi bằng cách chọn Bỏ qua Lỗi. Nếu bạn bỏ qua lỗi trong một ô cụ thể, thì lỗi trong ô đó sẽ không xuất hiện trong những lần kiểm tra thêm. Tuy nhiên, bạn có thể đặt lại tất cả các lỗi đã bỏ qua trước đó để chúng xuất hiện lại.

1. Đối với Excel trên Windows, đi đến mục > Chọn > Công thức hoặc
   cho Excel trên Mac, chọn menu Excel để > Chọn và > tra Lỗi.

2. Bên dưới Kiểm tra Lỗi, hãy chọn Bật kiểm tra lỗi trong nền. Bất kỳ lỗi nào tìm thấy đều được đánh dấu bằng hình tam giác ở góc trên bên trái của ô.

   

3. Để thay đổi màu của hình tam giác đánh dấu vị trí xuất hiện lỗi, trong hộp Chỉ ra lỗi bằng màu này, hãy chọn màu mà bạn muốn.

4. Dưới Quy tắc kiểm tra Excel, hãy chọn hoặc bỏ chọn hộp kiểm của bất kỳ quy tắc nào sau đây:

   • Ô chứa công thức gây ra lỗi: Công thức không sử dụng cú pháp, đối số hoặc kiểu dữ liệu dự kiến. Giá trị lỗi bao gồm #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, và #VALUE!. Mỗi giá trị lỗi này có các nguyên nhân khác nhau và được giải quyết theo nhiều cách khác nhau.

     Lưu ý: Nếu bạn nhập giá trị lỗi trực tiếp vào ô, giá trị lỗi đó sẽ được lưu trữ dưới dạng giá trị lỗi nhưng không được đánh dấu là lỗi. Tuy nhiên, nếu một công thức trong ô khác tham chiếu tới ô đó, thì công thức trả về giá trị lỗi từ ô đó.

   • Công thức trong cột được tính không nhất quán trong các bảng: Cột được tính có thể bao gồm các công thức riêng khác với công thức cột chính, gây ra lỗi. Các lỗi đối với cột được tính xảy ra khi bạn thực hiện bất kỳ thao tác nào sau đây:

     • Nhập dữ liệu khác với công thức trong ô cột được tính.

     • Nhập công thức vào ô cột được tính, rồi sử dụng Ctrl +Z hoặc chọn Hoàn tác trên Thanh công cụ Truy nhập Nhanh.

     • Nhập một công thức mới vào một cột được tính toán đã chứa sẵn một hoặc nhiều ngoại lệ.

     • Sao chép dữ liệu vào cột được tính toán mà dữ liệu đó không khớp với công thức của cột được tính toán. Nếu dữ liệu sao chép có chứa công thức, thì công thức này sẽ ghi đè dữ liệu trong cột được tính toán.

     • Di chuyển hoặc xóa bỏ một ô trên một vùng trang tính khác được tham chiếu bởi một trong các hàng trong cột được tính toán.

   • Ô chứa năm được biểu thị bằng 2 chữ số: Ô chứa ngày tháng dạng văn bản có thể bị hiểu sai thế kỷ khi được dùng trong công thức. Ví dụ: ngày tháng trong công thức =YEAR("1/1/31") có thể được hiểu là 1931 hoặc 2031. Sử dụng quy tắc này để kiểm tra các ngày tháng dạng văn bản không rõ ràng.

   • Số được định dạng dưới dạng văn bản hoặc có nháy đơn phía trước: Ô chứa số được lưu trữ dưới dạng văn bản. Điều này thường xảy ra khi dữ liệu được nhập từ nguồn khác. Số được lưu trữ dưới dạng văn bản có thể dẫn đến kết quả sắp xếp không mong muốn, vì vậy, tốt nhất là bạn nên chuyển đổi các số đó thành số. ‘=SUM(A1:A10) hiển thị dưới dạng văn bản.

   • Công thức không nhất quán với công thức khác trong vùng: Công thức đó không khớp với mẫu của các công thức khác gần đó. Trong nhiều trường hợp, các công thức liền kề với các công thức khác chỉ khác nhau trong các tham chiếu được sử dụng. Trong ví dụ về bốn công thức liền kề sau đây, Excel sẽ hiển thị lỗi bên cạnh công thức =SUM(A10:C10) trong ô D4 vì các công thức liền kề tăng thêm một hàng, còn công thức này lại tăng thêm 8 hàng — Excel cho rằng công thức phải là =SUM(A4:C4).

     

     Nếu các tham chiếu được sử dụng trong công thức không nhất quán với tham chiếu trong các công thức liền kề, Excel sẽ hiển thị lỗi.

   • Công thức bỏ sót ô trong vùng: Công thức không thể tự động bao gồm tham chiếu đến dữ liệu bạn chèn giữa phạm vi dữ liệu gốc và ô chứa công thức đó. Quy tắc này so sánh tham chiếu trong công thức với phạm vi ô thực tế liền kề với ô chứa công thức đó. Nếu các ô liền kề có chứa giá trị bổ sung và không phải là ô trống, Excel sẽ hiển thị lỗi bên cạnh công thức đó.

     Ví dụ, Excel chèn lỗi bên cạnh công thức =SUM(D2:D4) khi quy tắc này được áp dụng, vì các ô D5, D6 và D7 nằm liền kề các ô được tham chiếu trong công thức và ô chứa công thức (D8) và các ô chứa dữ liệu cần được tham chiếu trong công thức.

     

   • Các ô chưa khóa có chứa công thức: Công thức chưa được khóa bảo vệ. Theo mặc định, tất cả các ô trên trang tính đều được khóa để không bị thay đổi khi trang tính được bảo vệ. Điều này có thể giúp tránh các sai lầm vô ý như vô tình xóa hoặc thay đổi công thức. Lỗi này cho biết ô được đặt là chưa khóa nhưng trang tính vẫn chưa được bảo vệ. Kiểm tra để đảm bảo rằng bạn không muốn khóa ô.

   • Công thức tham chiếu tới các ô trống: Công thức chứa tham chiếu đến một ô trống. Điều này có thể dẫn đến các kết quả không mong đợi, như được minh họa trong ví dụ dưới đây.

     Giả sử bạn muốn tính giá trị trung bình của các số trong cột ô sau đây. Nếu ô thứ ba là ô trống, nó không được bao gồm trong phép tính và kết quả là 22,75. Nếu ô thứ ba chứa 0, kết quả sẽ là 18,2.

     

   • Dữ liệu được nhập vào trong bảng không hợp lệ: Xảy ra lỗi xác thực trong bảng. Kiểm tra thiết đặt xác thực cho ô bằng cách đi đến tab Dữ liệu > nhóm Công cụ Dữ liệu > Xác thực Dữ liệu.

1. Chọn trang tính mà bạn muốn kiểm tra lỗi.

2. Nếu trang tính được tính toán theo cách thủ công, hãy nhấn F9 để tính toán lại.

   Nếu hộp thoại Kiểm tra Lỗi không hiển thị, hãy chọn Công thức > Kiểm tra Công > Tra Lỗi.

3. Nếu trước đó bạn đã bỏ qua bất kỳ lỗi nào, bạn có thể kiểm tra lại các lỗi đó bằng cách làm như sau: đi tới Tệp >Chọn > Công thức. Đối với Excel trên máy Mac, chọn menu Excel để > Chọn > tra Lỗi.

   Trong phần Kiểm tra Lỗi , chọn Đặt lại Lỗi đã Bỏ qua > OK.

   

   Lưu ý: Việc đặt lại lỗi đã bỏ qua sẽ đặt lại tất cả lỗi trong tất cả trang tính trong sổ làm việc hiện hoạt.

   Mẹo: Việc này có thể hữu ích nếu bạn di chuyển hộp thoại Kiểm tra Lỗi ngay bên dưới thanh công thức.

   

4. Chọn một trong các nút hành động ở bên phải hộp thoại. Các hành động sẵn có sẽ khác nhau tùy theo mỗi kiểu lỗi.

5. Chọn Tiếp theo.

1. Bên cạnh ô, chọn Kiểm tra , rồi chọn tùy chọn bạn muốn. Các lệnh khả dụng sẽ khác nhau tùy theo từng loại lỗi và mục nhập đầu tiên sẽ mô tả lỗi.

   Nếu bạn chọn Bỏ qua Lỗi, lỗi sẽ được đánh dấu để bỏ qua cho mỗi lần kiểm tra liên tiếp.

   

Nếu một công thức không thể đánh giá đúng kết quả, Excel sẽ hiển thị giá trị lỗi, chẳng hạn như #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! và #VALUE!. Mỗi loại lỗi có các nguyên nhân khác nhau và các giải pháp khác nhau.

Bảng sau chứa kết nối đến bài viết mô tả chi tiết về các lỗi này và mô tả ngắn gọn để giúp bạn bắt đầu.

Chủ đề	Mô tả
Sửa lỗi ####	Excel sẽ hiển thị lỗi này khi cột không đủ rộng để hiển thị tất cả các ký tự trong một ô hoặc một ô chứa giá trị âm hoặc giá trị thời gian. Ví dụ, một công thức trừ một ngày trong tương lai từ một ngày trong quá khứ, chẳng hạn như = 06/15/2008-07/01/2008, dẫn đến giá trị ngày âm. Mẹo: Cố gắng điều chỉnh ô tự động bằng cách bấm đúp vào giữa các tiêu đề cột. Nếu ### được hiển thị vì Excel không thể hiển thị tất cả các ký tự, thao tác này sẽ sửa lại lỗi đó.
Sửa lỗi #DIV/0! .	Excel sẽ hiển thị lỗi này khi có số chia cho không (0) hoặc ô không chứa giá trị nào. Mẹo: Thêm một bộ xử lý lỗi như trong ví dụ sau, là =IF(C2,B2/C2,0)
Sửa lỗi #N/A	Excel sẽ hiển thị lỗi này khi giá trị không có sẵn cho hàm hoặc công thức. Nếu bạn đang sử dụng một hàm như VLOOKUP, nội dung bạn đang cố gắng tra cứu có kết quả khớp với trong phạm vi tra cứu không? Hầu hết thường là không có. Thử sử dụng IFERROR để ngăn #N/A. Trong trường hợp này, bạn có thể sử dụng: =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)
Sửa lỗi #NAME? .	Lỗi này hiển thị khi Excel không nhận dạng được văn bản trong công thức. Ví dụ: tên phạm vi hoặc tên hàm có thể bị viết sai chính tả. Lưu ý: Nếu bạn đang sử dụng một hàm, hãy đảm bảo tên hàm được viết đúng. Trong trường hợp này, hàm SUM bị viết sai chính tả. Loại bỏ "e" và Excel sửa lại.
Sửa lỗi #NULL! .	Excel sẽ hiển thị lỗi này khi bạn xác định giao nhau của hai vùng không giao nhau (chéo). Toán tử giao nhau là một ký tự khoảng trắng để tách biệt các tham chiếu trong một công thức. Lưu ý: Hãy đảm bảo rằng các phạm vi của bạn được phân tách chính xác - các vùng C2:C3 và E4:E6 không giao nhau, vì vậy nhập công thức =SUM(C2:C3 E4:E6) sẽ trả về giá #NULL! lỗi. Đặt dấu phẩy giữa phạm vi C và E sẽ sửa lỗi =SUM(C2:C3,E4:E6)
Sửa lỗi #NUM! .	Excel sẽ hiển thị lỗi này khi công thức hoặc hàm chứa giá trị số không hợp lệ. Bạn đang sử dụng một hàm lặp lại, như IRR hoặc RATE? Nếu vậy, lỗi #NUM! có thể xay ra do hàm không tìm thấy kết quả. Tham khảo chủ đề trợ giúp để biết các bước giải quyết.
Sửa lỗi #REF! .	Excel sẽ hiển thị lỗi này khi tham chiếu ô không hợp lệ. Ví dụ, bạn có thể xóa các ô đã được tham chiếu bởi các công thức khác hoặc bạn có thể dán các ô mà bạn đã di chuyển ở phía trên cùng của ô được tham chiếu bởi các công thức khác. Bạn vô tình xóa một hàng hoặc cột? Chúng tôi đã xóa cột B trong công thức =SUM(A2,B2,C2) này, hãy xem chuyện gì xảy ra. Sử dụng Hoàn tác (Ctrl+Z) để hoàn tác thao tác xóa, xây dựng lại công thức hoặc sử dụng tham chiếu phạm vi liên tiếp như sau: =SUM(A2:C2), hàm này sẽ tự động cập nhật khi cột B bị xóa.
Sửa lỗi #VALUE! .	Excel có thể hiển thị lỗi này nếu công thức của bạn bao gồm các ô chứa các kiểu dữ liệu khác nhau. Bạn đang sử dụng các toán tử Toán học (+, -, *, /, ^) với các kiểu dữ liệu khác nhau? Nếu vậy, hãy thử thay thế bằng hàm. Trong trường hợp này, hàm =SUM(F2:F5) có thể khắc phục sự cố.

Khi ô không hiển thị trên trang tính, bạn có thể xem các ô đó và các công thức trong thanh công cụ Cửa sổ Giám sát. Cửa sổ Giám sát giúp bạn thuận tiện trong việc giám sát, kiểm tra hoặc xác nhận tính toán công thức và kết quả trong trang tính lớn. Bằng cách sử dụng Cửa sổ Giám sát, bạn không cần cuộn đi cuộn lại hoặc đi đến các phần khác nhau của trang tính.

Bạn có thể di chuyển hoặc cố định thanh công cụ này như mọi thanh công cụ khác. Ví dụ: bạn có thể cố định thanh công cụ ở cuối cửa sổ. Thanh công cụ theo dõi những thuộc tính ô sau: 1) Sổ làm việc, 2) Trang tính, 3) Tên (nếu ô có Phạm vi Có tên tương ứng), 4) Địa chỉ ô, 5) Giá trị và 6) Công thức.

Thêm ô vào Cửa sổ Giám sát

1. Chọn ô bạn muốn giám sát.

   Để chọn tất cả các ô trên trang tính có công thức, hãy đi đến Trang đầu > Soạnthảo > chọn Tìm & Chọn (hoặc bạn có thể sử dụng Ctrl+G hoặc Control+G trên máy Mac)> Đi đến Công thức > Đặc biệt.

   

2. Đi đến Mục Công thức > Kiểm tra Công thức > Chọn Cửa sổ Giám sát.

3. Chọn Thêm Giám sát.

   

4. Xác nhận rằng bạn đã chọn tất cả các ô mà bạn muốn xem, rồi chọn Thêm.

   

5. Để thay đổi độ rộng của cột Cửa sổ Giám sát, hãy kéo viền ở bên phải đầu đề cột.

6. Để hiển thị ô mà mục nhập trong thanh công cụ Cửa sổ Giám sát tham chiếu đến, hãy bấm đúp vào mục nhập.

   Lưu ý: Ô có tham chiếu bên ngoài sổ làm việc khác sẽ hiển thị trong thanh công cụ Cửa sổ Giám sát chỉ khi sổ làm việc khác mở.

Loại bỏ ô khỏi Cửa sổ Giám sát

1. Nếu thanh công cụ Cửa sổ Giám sát không hiển thị, hãy đi đến mục Công > kiểm tra Công > chọn Cửa sổ Giám sát.

2. Chọn ô bạn muốn loại bỏ.

   Để chọn nhiều ô, nhấn CTRL, rồi chọn các ô.

3. Chọn Xóa Giám sát.

   

Đôi khi, rất khó để hiểu được cách công thức lồng nhau tính toán kết quả cuối cùng vì có một vài tính toán trung gian và kiểm tra lô-gic. Tuy nhiên, bằng cách sử dụng hộp thoại Đánh giá Công thức, bạn có thể thấy các phần khác nhau của công thức lồng nhau theo thứ tự mà công thức được tính toán. Ví dụ, công thức =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) sẽ dễ hiểu hơn khi bạn có thể thấy kết quả trung gian sau đây:

Trong hộp thoại Đánh giá Công thức	Mô tả
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)	Công thức lồng nhau hiển thị đầu tiên. Hàm AVERAGE và hàm SUM lồng trong hàm IF. Phạm vi ô D2:D5 chứa các giá trị 55, 35, 45 và 25, do đó, kết quả của hàm AVERAGE(D2:D5) là 40.
=IF(40>50,SUM(E2:E5),0)	Phạm vi ô D2:D5 chứa các giá trị 55, 35, 45 và 25, do đó, kết quả của hàm AVERAGE(D2:D5) là 40.
=IF(False,SUM(E2:E5),0)	Vì 40 không lớn hơn 50 nên biểu thức trong tham đối đầu tiên của hàm IF (tham đối logical_test) là False. Hàm IF trả về giá trị của đối số thứ ba (đối số value_if_false). Hàm SUM không được đánh giá vì nó là đối số thứ hai của hàm IF (đối số value_if_true) và nó được trả về chỉ khi biểu thức là True.

1. Chọn ô bạn muốn đánh giá. Chỉ có thể đánh giá một ô tại một thời điểm.

2. Đi đến Công thức >kiểm tra công thức > trị Công thức.

3. Chọn Đánh giá để kiểm tra giá trị của tham chiếu được gạch dưới. Kết quả đánh giá được hiển thị dạng in nghiêng.

   Nếu phần gạch dưới của công thức là tham chiếu đến công thức khác, hãy chọn Bước Vào để hiển thị công thức khác trong hộp Đánh giá. Chọn Bước ra để trở lại ô và công thức trước.

   Nút Bước Vào không có sẵn cho tham chiếu vào lần thứ hai tham chiếu đó hiện ra trong công thức hoặc nếu công thức tham chiếu đến một ô trong sổ làm việc khác.

4. Tiếp tục chọn Đánh giá cho đến khi từng phần của công thức được đánh giá.

5. Để xem lại đánh giá, hãy chọn Khởi động lại.

6. Để kết thúc đánh giá, chọn Đóng.