Cách sửa 8 lỗi thường gặp trên Excel, Google Sheets chi tiết, đơn giản

Trong quá trình sử dụng Excel, đôi lúc bạn gặp những lỗi khi sử dụng các hàm tính toán, dẫn đến hệ thống thông hiển thị kết quả như mong muốn mà báo các lỗi như #NAME?, ####, #N/A,… Để biết các trường hợp nào xảy ra và cách khắc phục ra sao, hãy để mình mách bạn qua bài viết hướng dẫn dưới đây nhé!

• Giúp bạn biết nguyên nhân của các lỗi thường gặp trong Google sheet, Excel.
• Đưa ra những giải pháp nhanh, cụ thể, chi tiết để sửa lại các phép tính đang sai.
• Gợi ý những phương pháp khắc phục lỗi qua các bước đơn giản và hiệu quả.

Lỗi #N/A thường có trong hàm sau đây: hàm VLOOKUP, hàm HLOOKUP, hàm MATCH, hàm LOOKUP

Lý do thường gặp lỗi #N/A: Không tìm được giá trị cần tìm.

Lỗi #N/A

Cách khắc phục lỗi #N/A:

• Kiểm tra lại các bảng dữ liệu đối chiếu trong các hàm tìm kiếm và sắp xếp dữ liệu so sánh trong bảng theo giá trị từ nhỏ đến lớn.
• Đảm bảo dữ liệu tìm kiếm và đối chiếu phải cùng một kiểu định dạng dữ liệu đồng thời giá trị của dữ liệu tìm kiếm không được nhỏ hơn giá trị nhỏ nhất của dữ liệu đối chiếu.
• Lồng các hàm tìm kiếm với hàm xử lý lỗi IFNA hoặc IFERROR để giá trị trả về không bị lỗi #N/A.

Ví dụ 1: Lồng hàm IFNA

Công thức: =IFNA(value,value_if_na)

Từ lỗi #N/A ta nhập công thức sau: =IFNA(VLOOKUP(A3,D2:F5,2,FALSE),””)

Lồng hàm IFNA

Ví dụ 2: Lồng hàm IFERROR

Công thức: =IFERROR(value,value_if_error)

Từ lỗi #N/A ta nhập công thức sau: =IFERROR(VLOOKUP(A3,D2:F5,2,FALSE),””)

Lồng hàm IFERROR

Có thể nói, lỗi #VALUE! là một trong những lỗi hay gặp nhất khi sử dụng công thức Excel. Đây là cách Excel thông báo cho chúng ta rằng có vấn đề với công thức hoặc vùng bảng tính được tham chiếu. Vấn đề ở đây là gì thì tuỳ vào công thức mà bạn đang sử dụng

Lỗi #VALUE! thường có trong hàm sau đây: AVERAGE, SUM, CONCATENATE, COUNTIF, COUNTIFS, VLOOKUP, SUMPRODUCT, FIND, SEARCH.

• Hàm AVERAGE và hàm SUM

Khi sử dụng hàm AVERAGE hoặc hàm SUM, bạn có thể gặp lỗi #VALUE! nếu trong vùng tính toán của 2 hàm này có giá trị #VALUE! như hình sau:

Lỗi #VALUE! của hàm AVERAGE

Như trong hình minh hoạ, bạn có thể thấy rằng ô C2 và đang có lỗi #VALUE! bởi vì trong vùng B2:D2 có ô C2 đang chứa giá trị #VALUE!

Cách khắc phục lỗi #VALUE!: Để khắc phục lỗi này, chúng ta có thể lồng công thức phát hiện lỗi ISERROR trong ô E2 như sau:

=AVERAGE(IF(ISERROR(B2:D2),””,B2:D2))

Vì công thức này là công thức mảng, để nhập công thức này bạn phải dùng tổ hợp phím CTRL + SHIFT + ENTER để nhận kết quả như sau:

Sửa lỗi #VALUE!

Tương tự ta có thể áp dụng cho hàm SUM như sau: =SUM(IF(ISERROR(B2:D2),””,B2:D2))

Một trong những nguyên nhân gây ra lỗi #VALUE! khi sử dụng hàm CONCATENATE là trong vùng được nối chuỗi có chứa ô có giá trị #VALUE! như trong ví dụ minh hoạ sau đây:

Lỗi hàm CONCATENATE

Trong ví dụ minh họa phía trên, ô E2 chứa lỗi #VALUE! nên kết quả của hàm CONCATENATE cũng đưa kết quả lỗi #VALUE!

Cách khắc phục lỗi #VALUE!: Để khắc phục lỗi #VALUE! trong trường hợp này, bạn có thể lồng công thức phát hiện lỗi ISERROR trong ô E2 như sau:

=IF(ISERROR(E2),CONCATENATE(D2,” 0 “,F2),CONCATENATE(D2:F2))

Sửa lỗi hàm CONCATENATE

Với công thức này, hàm ISERROR sẽ xác định E2 có phải là ô bị lỗi hay không, nếu không bị lỗi thì kết quả trả về sẽ có ý nghĩa là “You have 0 minutes left to buy this product” thay vì hiển thị lỗi #VALUE! như trên.

1. Vùng tham chiếu bị lệch

Ở hình minh họa bên dưới, vùng tham chiếu trong đối số của hàm SUMPRODUCT có kích thước không giống nhau, điều này sẽ tạo ra lỗi #VALUE!.

Lỗi #VALUE! hàm SUMPRODUCT

Cách khắc phục lỗi #VALUE!: Sửa lại công thức để vùng tham chiếu thống nhất về kích thước như sau:

=SUMPRODUCT(D2:D13,E2:E13)

2. Trong vùng tham chiếu có 1 hay nhiều ô chứa lỗi #VALUE!

Trong vùng tham chiếu có 1 hay nhiều ô chứa lỗi #VALUE! cũng là lý do gây ra lỗi #VALUE! cho hàm SUMPRODUCT khi tham chiếu tới những vùng này.

Sửa lỗi #VALUE! hàm SUMPRODUCT

Cách khắc phục lỗi #VALUE!: Chỉnh sửa lại dữ liệu dùng để tính toán trước khi áp dụng hàm SUMPRODUCT.

Khi nhập sai tên hàm thì Excel sẽ không gợi ý chức năng và các tham số tương ứng của hàm đó.

Lỗi #NAME? 1

Cách khắc phục lỗi #NAME?: Kiểm tra lại từng ký tự trong công thức từ đầu đến cuối như tên hàm đã được viết đúng chưa và sử dụng tính năng Function Arguments bằng cách chọn thẻ Formulas (1) > Chọn Insert Function (2).

Function Arguments

Lỗi #NAME? 2

Cách khắc phục: Kiểm tra lại công thức và bổ sung ngoặc kép vào đúng chỗ.

Lỗi #NAME? 3

Cách khắc phục lỗi #NAME?: Kiểm tra lại công thức và bổ sung dấu hai chấm vào đúng chỗ.

Trong vài trường hợp bạn muốn dùng 1 tên làm giá trị để thay cho một cụm dữ liệu thì nếu bạn chưa khai báo tên, hệ thống vẫn sẽ hiển thị lỗi #NAME?.

Lỗi #NAME? 4

Cách khắc phục lỗi #NAME?: Khai báo tên bằng cách sử dụng chức năng Name Manager qua các bước sau:

Bước 1: Tại thẻ Formulas, chọn Name Manager để mở hộp thoại.

Formulas > Name Manager

Bước 2: Trong bảng Name Manager, để tạo một tên mới bạn nhấn New.

Name Manager

Bước 3: Tại bảng khai báo tên mới, điền tên vào mục Name (1), sau đó chọn vùng dữ liệu của tên trong mục Refers to (2), sau đó bạn nhấn chuột kéo vùng dữ liệu như hình (3), sau đó nhấn OK để hoàn tất.

Chọn vùng

Bước 4: Sau khi trở lại bảng Name Manager bạn nhấn Close để hoàn tất gán tên.

Nhấn Close

Trường hợp bạn đã khai báo nhưng kết quả vẫn #NAME? thì có thể do quá trình nhập bạn đã nhập sai tên.

Lỗi #NAME? 5

Cách khắc phục lỗi #NAME?: Kiểm tra lại cú pháp, ký tự và sửa lại tên này.

Sửa lỗi #NAME? 5

Nếu kết quả trong 1 ô sau khi thực hiện công thức là #### thì nhiều khả năng đây là lỗi đơn giản mà chiều rộng của cột có chứa ô đó không đủ rộng để có thể hiển thị toàn bộ nội dung.

Lý do thường gặp lỗi ####: Chiều rộng của cột có chứa ô đó không đủ rộng.

Cách khắc phục lỗi ####: Bạn chỉ cần bấm vào thẻ Home (1) > Format (2) > AutoFit Column Width (3)

Sửa lỗi ####

Lỗi #REF! thường có trong hàm sau đây: hàm VLOOKUP, hàm INDEX, hàm INDIRECT.

Trong công thức INDEX ở bên dưới, tham số ROW (Hàng) và COLUMN (Cột) lần lượt là 5 và 6, vượt quá kích thước của vùng dữ liệu B2:E5 nên đã gây ra lỗi #REF!

Lỗi #REF! 1

Cách khắc phục lỗi #REF!: Sửa lại tham số hàng và cột đúng với dữ liệu cần tìm như sau: =INDEX(B2:E5,2,3)

Sửa lỗi #REF! 1

Trong hình minh hoạ bên dưới, vùng tra cứu chỉ có 4 cột, nhưng tham số column_index của hàm VLOOKUP lại nhập giá trị 5, vậy nên ở đây kết quả là hàm VLOOKUP trả về lỗi #REF!

Lỗi #REF!

Cách khắc phục lỗi #REF!: Sửa lại công thức cho phù hợp. Ta có công thức sau: =VLOOKUP(B3,B2:E5,2,FALSE)

Sửa lỗi #REF! 2

Khi vùng tham chiếu trong công thức Excel bị xoá hoặc bị ghi đè lên.

Lỗi #REF! 3

Sau khi vùng tham chiếu trong công thức Excel bị xoá, lỗi #REF! sẽ hiển thị như hình dưới.

Sửa lỗi #REF! 3

Cách khắc phục lỗi #REF!: Nếu bạn lỡ tay xoá nhầm cột thì hãy sử dụng phím tắt CTRL + Z để có thể lấy lại cột vừa bị xoá (chức năng Undo).

Lỗi #REF! 3

Khi tham chiếu tới một file Excel đang đóng trong hàm INDIRECT, kết quả sẽ cho lỗi #REF!

Lỗi #REF! 4

Cách khắc phục lỗi #REF!: Mở file Excel đang đóng.

Lỗi #DIV/0! xảy ra khi trong công thức Excel xuất hiện phép chia cho 0. Vì phép chia cho 0 không được định nghĩa trong toán học nên đây là lỗi và được thể hiện bằng #DIV/0! trong Excel.

Lỗi #DIV/0! thường có trong hàm sau đây: Phép chia, hàm QUOTIENT.

Lý do thường gặp lỗi #DIV/0!: Thực hiện phép chia cho 0 hoặc không nhập số chia.

Lỗi #DIV/0!

Cách khắc phục lỗi #DIV/0!:

• Đảm bảo mẫu số của các phân số không bằng 0 hoặc các ô chứa số chia không rỗng khi thực hiện các phép tính toán trong Excel.
• Sử dụng thêm hàm IFERROR để kiểm soát lỗi gặp phải nếu có khả năng xảy ra trường hợp chia cho 0.

Lỗi #NUM! thường có trong hàm sau đây: Phép nhân lũy thừa, hàm căn bậc 2, hàm LOGARIT, hàm IRR, hàm lãi suất RATE.

Lý do thường gặp lỗi #NUM!:

• Khi một công thức yêu cầu tham số là một giá trị số nhưng lại được người dùng đưa vào một giá trị không phải là số hoặc không hợp lệ.
• Khi kết quả của phép tính trong Excel quá lớn hoặc quá nhỏ.

Công thức gây lỗi #NUM!

Cách khắc phục lỗi #NUM!: Chú ý điều kiện sử dụng số (số nguyên, số âm hay số dương,…)

Lý do thường gặp lỗi #NULL!: Trường hợp bạn chọn không đúng vùng dữ liệu, Excel không thể hiểu chính xác vùng bạn chọn thì kết quả sẽ hiện #NULL! xảy ra khi công thức lấy phần giao nhau giữa 2 vùng không giao nhau.

Lỗi #NULL!

Cách khắc phục lỗi #NULL!: Kiểm tra lại các vùng dữ liệu trong hàm và cập nhật lại.

Mua phần mềm Microsoft Office tại Thế Giới Di Động

Khi một công thức hiện giá trị lỗi (trừ #N/A) trong một ô, Excel sẽ hiển thị một biểu tượng báo lỗi hình thoi màu vàng có dấu chấm than kế bên ô hiển thị lỗi (1), biểu thị rằng nội dung ô vi phạm một trong các quy tắc kiểm tra lỗi của Excel. Bạn có thể chọn biểu tượng đó hoặc vào thẻ Formulas (2) > chọn Error Checking (3), hệ thống sẽ hiện các tùy chọn bên dưới để nhận biết các lỗi.

Error Checking

• Help on This Error: Mở cửa sổ trợ giúp lỗi với thông tin về loại giá trị lỗi trong ô hiện tại và cách sửa lỗi.

Help on Error

• Show Calculation Steps: Mở hộp thoại xem lại các bước tính. Với mỗi lần nhấn Evaluate (1), hệ thống sẽ đi qua từng bước trong phép tính để xem kết quả của mỗi phép tính. Nếu một bước bị sai (2) thì hệ thống sẽ hiển thị lỗi của phép tính đó.

Show Calculation Steps

• Ignore Error: Bỏ qua việc kiểm tra lỗi cho ô này và loại bỏ nút cảnh báo lỗi và nút tùy chọn lỗi khỏi nó.
• Edit in Formula Bar: Kích hoạt chế độ Chỉnh sửa và đặt dấu nháy ở cuối công thức trên thanh Công thức.

Edit in Formula Bar

• Error Checking Options: Mở tab Công thức của hộp thoại Tùy chọn lỗi của Excel, bạn có thể sửa đổi các tùy chọn được sử dụng trong việc kiểm tra bảng tính cho lỗi của các công thức.

Error Checking Options

Ngoài ra còn có các mẹo xử lý lỗi sau:

• Tìm kiếm các giá trị trong công thức bị gạch chân bằng màu đỏ vì điều này sẽ giúp xác định nguồn gốc lỗi của bạn. Ví dụ: Trường hợp có quá nhiều dấu ngoặc hoặc các dấu phụ bị thừa sẽ được tô sáng màu đỏ.
• Phương pháp lột củ hành: Kiểm tra công thức bao hàm trước, rồi mới đến công thức nhỏ, chi tiết sau. Đây là một cách để phát hiện lỗi cho các công thức dài và phức tạp. Kiểm tra công thức bao hàm trước, rồi mới đến công thức nhỏ, chi tiết sau. Sau đó, bạn có thể bắt đầu thêm từng hàm một lần nữa và xem chính xác bước nào gây ra sự cố và khắc phục điều đó.
• Kiểm tra dấu phẩy, chấm phẩy, thứ tự hiển thị ngày tháng năm trong cài đặt.

Trả về giá trị lỗi #N/A là giá trị lỗi có nghĩa là “không có giá trị nào”. Hãy dùng NA để đánh dấu các ô trống. Bằng cách nhập #N/A vào những ô mà bạn đang thiếu thông tin, bạn có thể tránh được rắc rối khi vô tình đưa các ô trống vào các phép tính. (Khi một công thức tham chiếu tới một ô có chứa #N/A, nó sẽ trả về giá trị #N/A).

Cú pháp: NA()

Ví dụ minh họa:

Ví dụ NA() 1

Trong trường hợp trên, ta cần trả về giá trị #N/A với ô không có chi phí (hoặc chi phí có giá trị #N/A). Để làm điều đó, tại ô E2 chèn hàm NA() vào trong hàm IF như sau: =IF(C2=””,NA(),(B2-D2)*C2)

Sau đó sử dụng AutoFill cho những ô còn lại, ta sẽ có kết quả như hình dưới:

Ví dụ NA() 2

ERROR.TYPE là công thức giúp phát hiện lỗi bằng cách trả về kết quả một số tương ứng với giá trị lỗi trong một ô. Trả về số tương ứng với một trong các giá trị lỗi trong Microsoft Excel hoặc trả về lỗi #N/A nếu không có lỗi. Bạn có thể dùng hàm ERROR.TYPE trong hàm IF để kiểm định giá trị lỗi và trả về một chuỗi văn bản, chẳng hạn như thông báo, thay vì giá trị lỗi.

Cú pháp: =ERROR.TYPE(tham_chiếu)

Trong đó: tham_chiếu là ô để tìm số lỗi (bạn cũng có thể trực tiếp cung cấp giá trị lỗi).

Ví dụ mẫu:

• ERROR.TYPE(A3)
• ERROR.TYPE(NA())

ERROR.TYPE sẽ trả về một số tương ứng với loại lỗi:

• 1 cho #NULL!
• 2 cho # DIV/0!
• 3 cho #VALUE!
• 4 cho #REF!
• 5 cho #NAME?
• 6 cho #NUM!
• 7 cho # N/A
• 8 cho tất cả các lỗi khác

Ví dụ: =ERROR.TYPE(#NULL!) sẽ đưa kết quả là 1.

Ví dụ minh họa: Kiểm tra mã số học sinh để xuất điểm trung bình, khi nhập đúng thì cột điểm trung bình sẽ ra số tương đương với tên học sinh đó, nếu sai sẽ xuất kết quả lỗi #N/A, khi đó ta lồng hàm IF với hàm ERROR.TYPE để tìm lỗi như sau:

=IF(ISERROR(F10), IF(ERROR.TYPE(F10)=7, “Không đúng mã số”, “Lỗi:Kiểm tra lại”),”Đúng mã số”)

Ví dụ ERROR.TYPE

Kiểm tra xem một giá trị có phải là lỗi #N/A hay không và sẽ đưa ra kết quả Đúng (TRUE) cho lỗi #N/A và Sai (FALSE) nếu không đúng lỗi #N/A.

Công thức: ISNA(giá_trị)

Trong đó: giá_trị là giá trị cần so sánh với giá trị lỗi #N/A.

Ví dụ mẫu: ISNA(A2)

Ví dụ minh họa: Lồng hàm IF để kiểm tra cột điểm trung bình, nếu ô tính có giá trị lỗi #N/A hệ thống sẽ xuất kết quả “Không xếp loại“, nếu sai sẽ tiến hành xếp loại học sinh.

Ví dụ hàm ISNA

Kiểm tra xem một giá trị có bất kỳ lỗi nào khác ngoài lỗi #N/A không.

Công thức: ISERR(giá_trị)

Trong đó: giá_trị – Giá trị cần được xác minh thuộc loại lỗi khác #N/A.

Ví dụ mẫu: ISERR(A2)

Ví dụ minh họa:

Hàm ISERR

Kiểm tra xem một giá trị có phải là lỗi hay không và sẽ đưa ra TRUE cho bất kỳ lỗi nào.

Công thức: ISERROR(giá_trị)

Trong đó: giá_trị – Giá trị cần được xác minh là loại lỗi.

Ví dụ mẫu:

• ISERROR(A2)
• ISERROR(A1/A2)
• IF(ISERROR(VLOOKUP(B1; A1:A100; 1; FALSE)); “Không_thấy_kết_quả”; VLOOKUP(B1; A1:A100; 1; FALSE))

Ví dụ minh họa: Bài toán dưới đây tính số lượng vật tư đã mua, ở ô D3 kết quả trả về là lỗi #DIV/0! vì ô C3 đang là giá trị 0

Ví dụ ISERROR 1

Sử dụng cách lồng hàm IF với hàm ISERROR để kiểm tra lỗi như sau:

=IF(ISERROR(B3/C3),””,B3/C3)

Trong đó hàm ISERROR phát hiện lỗi chia cho 0 nên sẽ cho kết quả là khoảng trống.

Ví dụ ISERROR 2

Như vậy là qua bài viết này, mình đã thống kê cho các bạn các lỗi thường gặp khi làm việc với hàm và công thức trong Excel. Hi vọng bạn sẽ hạn chế được những lỗi này trong quá trình làm việc với Excel để trở nên hiệu quả hơn.