Cách sửa 8 lỗi thường gặp trên Excel, Google Sheets

Hiển.ANP
26-08-2023

Cách sửa 8 lỗi thường gặp trên Excel, Google Sheets
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é!
I. Lợi ích
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ả.
II. 8 lỗi thường gặp trong Google Sheets và Excel
1. #N/A
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.

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),"")

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),"")

2. #VALUE!
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:

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:

Tương tự ta có thể áp dụng cho hàm SUM như sau: =SUM(IF(ISERROR(B2:D2),””,B2:D2))
Cách làm này không những xử lý được được cho lỗi #VALUE!, mà còn xử lý được các lỗi khác như #N/A, #NULL!, #DIV/0!
Hàm CONCATENATE
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:

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))

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.
Hàm SUMPRODUCT
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!.

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.

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.
3. #NAME?
Lỗi#NAME? khi bạn nhập sai tên hàm ở trong Excel
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 đó.

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).

Lỗi #NAME? khi dữ liệu kiểu chữ không có dấu ngoặc kép bao quanh

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? khi thiếu dấu : ngăn cách giữa địa chỉ vùng

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ỗ.
Lỗi #NAME? khi tên sử dụng chưa được khai báo
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?.

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.

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

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.

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.

Lỗi #NAME? khi tên sử dụng bị viết sai
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.

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.

4. ####
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)

5. #REF!
Lỗi #REF! thường có trong hàm sau đây: hàm VLOOKUP, hàm INDEX, hàm INDIRECT.
Lỗi #REF! khi sử dụng tham số Row (Hàng) hoặc Column (Cột) của hàm INDEX không phù hợp
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!

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)

Lỗi #REF! khi sử dụng giá trị cho tham số không phù hợp trong hàm VLOOKUP
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!

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)

Lỗi #REF! khi xoá vùng tham chiếu trong công thức
Khi vùng tham chiếu trong công thức Excel bị xoá hoặc bị ghi đè lên.

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.

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! khi tham chiếu tới một file Excel đang đóng trong hàm INDIRECT
Khi tham chiếu tới một file Excel đang đóng trong hàm INDIRECT, kết quả sẽ cho lỗi #REF!

Cách khắc phục lỗi #REF!: Mở file Excel đang đóng.
6. #DIV/0!
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.

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.
7. #NUM!
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á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,…)
8. #NULL!
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.

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.
II. Các mẹo xử lý khi gặp lỗi trong Google, Excel
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.

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.

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 đó.

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.

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.

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.
Đôi khi sử dụng phiên bản Excel tại các quốc gia khác nhau có thể khiến phát sinh lỗi giữa việc sử dụng dấu phẩy hay chấm phẩy trong các hàm. Ví dụ dưới đây là 2 hàm có kết quả giống nhau nhưng cú pháp khác nhau:
=ArrayFormula(VLOOKUP(A1;Sheet2!A:I;{2\3\4\5\6\7\8};FALSE))
Và
=ArrayFormula(VLOOKUP(A1,Sheet2!A:I,{2,3,4,5,6,7,8};FALSE))
III. Các công thức để nhận biết, phát hiện lỗi
1. =NA()
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()

Lưu ý
#N/A là một lỗi, nên cả hàm ISNA lẫn ISERROR sẽ trả về giá trị TRUE. Hãy sử dụng hàm ISERR để tìm lỗi không phải #N/A.
Nhập =NA() vào ô tương đương với nhập trực tiếp giá trị lỗi #N/A.
#N/A được dùng để đánh dấu thông tin bị thiếu và để cho biết các hàm đang hoạt động trên dải ô hoặc các ô có chứa các giá trị đó để tạm dừng tính toán. Ví dụ: nếu ô B2 chứa kết quả của mệnh đề IF: =IF(ISBLANK(A1);0;A1) và B2 sau đó có liên quan đến một tổng hay công thức khác thì công thức đó sẽ giả định rằng B2 chứa thông tin chính xác. Khi thay đổi công thức trong B2 thành =IF(ISBLANK(A1);NA();A1), thì mọi thao tác tiếp theo trên B2 đều sẽ tạm dừng sau khi gặp lỗi #N/A và trả về lỗi đó.
Các lỗi #N/A cho biết thông tin bị thiếu và ra hiệu cho các hàm dừng tính toán. Hãy sử dụng giá trị #N/A thay vì 0 hoặc kết quả của ô. Ví dụ: nếu A1 chứa giá trị #N/A hoặc =NA(), công thức =A1+A2 sẽ cho kết quả là #N/A.

Ví dụ minh họa:

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:

Lưu ý:
Khi một công thức liên quan tới ô chứa giá trị #N/A, kết quả trả về sẽ là #N/A.
Hàm NA không cần đối số tuy nhiên phải có cặp ngoặc đơn ().
Bạn có thể gõ trực tiếp #N/Avào ô tính từ bàn phím.

2. =ERROR.TYPE
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 ô. Trả về số tương ứng với một trong các giá trị lỗi trong Microsoft Excel hoặc trả về lỗi #N/A nếu không có lỗi. Bạn có thể dùng hàm ERROR.TYPE trong hàm IF để kiểm định giá trị lỗi và trả về một chuỗi văn bản, chẳng hạn như thông báo, thay vì giá trị 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ố")

3. =ISNA (value) (có ví dụ minh họa)
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)

Lưu ý
Hàm này thường được dùng kết hợp nhiều nhất với hàm IF trong câu lệnh có điều kiện.
ISNA trả về giá trị Đúng (TRUE) nếu giá_trị là #N/A hoặc tham chiếu đến một ô có chứa #N/A và ngược lại là Sai (FALSE).

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.

4. = ISERR (value) (có ví dụ minh họa)
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)

Lưu ý
ISERR trả về giá trị Đúng (TRUE) nếu giá_trị là bất cứ lỗi nào khác #N/A, kể cả #DIV/0!, #NAME?, #NULL!, #NUM!, #VALUE! và #REF!, nếu không có lỗi nào khác ngoài #N/A hệ thống sẽ cho kết quả Sai (FALSE).
Hàm này hữu ích trong một số ứng dụng khi #N/A có thể là kết quả hợp lệ, trong khi các loại lỗi khác luôn là thể hiện vấn đề mang tính quy tắc cơ bản.
Hàm này thường được dùng kết hợp nhiều nhất với hàm IF trong câu lệnh có điều kiện.

Ví dụ minh họa:

5. = ISERROR (value) (có ví dụ minh họa)
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))

Lưu ý
ISERROR trả về giá trị Đúng (TRUE) nếu giá_trị có bất cứ lỗi nào, kể cả #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #VALUE! và #REF!, nếu giá_trị không có lỗi nào hệ thống sẽ cho kết quả Sai (FALSE)
ISERROR trả về giá trị Đúng (TRUE) đối với bất cứ lỗi nào, không giống hàm ISERR trả về giá trị Đúng (TRUE) đối với mọi lỗi trừ #N/A.
Hàm này thường được dùng kết hợp nhiều nhất với hàm IF trong câu lệnh có điều kiện.

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

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.

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.

Từ khóa

##Thủ Thuật Hay