Khắc phục 8 vấn đề phổ biến trên Excel, Google Sheets một cách chi tiết và đơn giản

Trong quá trình sử dụng Excel, đôi khi bạn có thể gặp phải những vấn đề khi sử dụng các hàm tính toán, dẫn đến hiển thị kết quả không như mong đợi và xuất hiện các lỗi như #NAME?, ####, #N/A,... Để hiểu rõ về các trường hợp này và cách khắc phục, hãy theo dõi bài viết hướng dẫn dưới đây nhé!

I. Điều này mang lại

• Hiểu rõ nguyên nhân của các vấn đề thường gặp trong Google Sheets và Excel.
• Cung cấp giải pháp nhanh chóng, chi tiết để khắc phục các lỗi phép tính không chính xác.
• Đề xuất các phương pháp sửa lỗi thông qua các bước đơn giản và hiệu quả.

II. 8 vấn đề phổ biến trong Google Sheets và Excel

1. #N/A

Lỗi #N/A thường xuất hiện trong các hàm như: hàm VLOOKUP, hàm HLOOKUP, hàm MATCH, hàm LOOKUP

Nguyên nhân thường gặp của lỗi #N/A: Không thể định vị giá trị cần tìm.

Lỗi #N/A

Cách sửa lỗi #N/A:

• Revis lại bảng dữ liệu để đảm bảo sự nhất quán trong các hàm tìm kiếm và sắp xếp dữ liệu so sánh theo thứ tự từ nhỏ đến lớn.
• Chắc chắn rằng dữ liệu tìm kiếm và dữ liệu đối chiếu phải có cùng định dạng và giá trị của dữ liệu tìm kiếm không nhỏ hơn giá trị nhỏ nhất của dữ liệu đối chiếu.
• Nhúng hàm tìm kiếm trong các hàm xử lý lỗi như IFNA hoặc IFERROR để tránh lỗi #N/A.

Mẫu số 1: Sử dụng hàm IFNA

Công thức: =IFNA(giá_trị, giá_trị_nếu_lỗi_na)

Đối với lỗi #N/A, sử dụng công thức sau: =IFNA(VLOOKUP(A3, D2:F5, 2, FALSE), '')

Hàm IFNA lồng

Ví dụ 2: Sử dụng hàm IFERROR

Công thức: =IFERROR(giá_trị, giá_trị_nếu_lỗi)

Đối với lỗi #N/A, nhập công thức sau: =IFERROR(VLOOKUP(A3, D2:F5, 2, FALSE), '')

Hàm IFERROR lồng

2. Lỗi #VALUE!

Là một trong những vấn đề phổ biến khi sử dụng Excel, lỗi #VALUE! thường xuất hiện khi có vấn đề với công thức hoặc vùng bảng tính được tham chiếu. Nguyên nhân cụ thể phụ thuộc vào công thức bạn đang sử dụng.

Thường gặp lỗi #VALUE! trong các hàm: AVERAGE, SUM, CONCATENATE, COUNTIF, COUNTIFS, VLOOKUP, SUMPRODUCT, FIND, SEARCH.

• Đối với hàm AVERAGE và hàm SUM

Khi sử dụng hàm AVERAGE hoặc hàm SUM, bạn có thể đối mặt với lỗi #VALUE! khi 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

Trong hình minh hoạ, ô C2 và đang báo lỗi #VALUE! do vùng B2:D2 có ô C2 chứa giá trị #VALUE!

Cách khắc phục lỗi #VALUE!: Để sửa 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ì đây là công thức mảng, bạn cần nhấn tổ hợp phím CTRL + SHIFT + ENTER để nhận kết quả như sau:

Sửa lỗi #VALUE!

Tương tự, áp dụng cho hàm SUM với công thức: =SUM(IF(ISERROR(B2:D2),””,B2:D2))

• 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:

Lỗi của hàm CONCATENATE

Trong ví dụ trên, ô E2 chứa lỗi #VALUE!, dẫn đến kết quả của hàm CONCATENATE cũng là 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 của hàm CONCATENATE

Với công thức này, hàm ISERROR sẽ kiểm tra xem E2 có phải là ô bị lỗi hay không. Nếu không có lỗi, kết quả trả về sẽ là 'Bạn còn 0 phút để mua sản phẩm này' thay vì hiển thị lỗi #VALUE! như trước.

• Hàm SUMPRODUCT

1. Vùng tham chiếu không cùng kích thước

Trong hình minh họa dưới đây, vùng tham chiếu trong đối số của hàm SUMPRODUCT có kích thước không đồng nhất, điều này sẽ gây ra lỗi #VALUE!.

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

Cách sửa lỗi #VALUE!: Điều chỉnh công thức để vùng tham chiếu có kích thước đồng đều như sau:

=SUMPRODUCT(D2:D13,E2:E13)

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

Trong khu vực tham chiếu, có một hoặc nhiều ô chứa lỗi #VALUE!, gây ra lỗi #VALUE! cho hàm SUMPRODUCT khi tham chiếu đến những khu vực này.

Khắc phục lỗi #VALUE! trong hàm SUMPRODUCT

Giải quyết lỗi #VALUE!: Chỉnh sửa dữ liệu được sử dụng để tính toán trước khi áp dụng hàm SUMPRODUCT.

3. #NAME?

Phát hiện lỗi #NAME? khi nhập sai tên hàm trong Excel

Khi nhập tên hàm sai, Excel sẽ không đề xuất chức năng và các tham số tương ứng của hàm đó.

Lỗi #NAME? 1

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

Tham số Hàm

Lỗi #NAME? xuất hiện khi dữ liệu kiểu chữ không được bao quanh bởi dấu ngoặc kép

Lỗi #NAME? 2

Khắc phục: Kiểm tra lại công thức và thêm dấu ngoặc kép vào đúng vị trí.

Lỗi #NAME? xuất hiện khi thiếu dấu : phân cách giữa địa chỉ vùng

Lỗi #NAME? 3

Khắc phục lỗi #NAME!: Kiểm tra lại công thức và thêm dấu hai chấm vào đúng vị trí.

Lỗi #NAME? khi tên sử dụng chưa được khai báo

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

Lỗi #NAME? 4

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: Trong thẻ Formulas, chọn Name Manager để mở hộp thoại.

Thẻ Formulas > Name Manager

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

Quản lý Tên

Bước 3: Trong 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). Bạn chuột kéo vùng dữ liệu như trong hình (3), sau đó nhấn OK để hoàn tất.

Chọn Khu vực

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

Nhấn Close

Lỗi #NAME? xuất hiện khi tên sử dụng bị viết sai

Nếu bạn đã khai báo nhưng vẫn nhận #NAME?, có thể bạn đã nhập sai tên trong quá trình nhập liệu.

Lỗi #NAME? 5

Khắc phục lỗi #NAME!: Kiểm tra cú pháp, ký tự và sửa lại tên nếu cần.

Sửa lỗi #NAME? 5

4. ####

Nếu kết quả trong một ô sau khi thực hiện công thức là ####, có thể lỗi đơn giản là chiều rộng của cột chứa ô đó không đủ để 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 chứa ô đó không đủ.

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

Sửa lỗi ####

5. #REF!

Lỗi #REF! thường xuất hiện trong các hàm sau: VLOOKUP, INDEX, 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 dưới đây, 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, gây ra lỗi #REF!

Lỗi #REF! 1

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

Sửa lỗi #REF! 1

Lỗi #REF! khi sử dụng giá trị không phù hợp cho tham số trong hàm VLOOKUP

Trong ví dụ 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 được nhập giá trị 5, dẫn đến kết quả hàm VLOOKUP trả về lỗi #REF!

Lỗi #REF!

Khắc phục lỗi #REF!: Sửa lại công thức để phù hợp. Công thức sau sẽ giải quyết: =VLOOKUP(B3,B2:E5,2,FALSE)

Sửa lỗi #REF! 2

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.

Lỗi #REF! 3

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

Sửa lỗi #REF! 3

Khắc phục lỗi #REF!: Nếu bạn vô tình xoá cột, hãy sử dụng tổ hợp phím CTRL + Z để hoàn tác việc xoá cột (chức năng Undo).

Lỗi #REF! 3

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ẽ hiển thị lỗi #REF!

Lỗi #REF! 4

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

6. #DIV/0!

Lỗi #DIV/0! xuất hiện khi công thức Excel thực hiện phép chia cho 0. Do phép chia cho 0 không có định nghĩa trong toán học, nên đây là một lỗi và được hiển thị là #DIV/0! trong Excel.

Lỗi #DIV/0! thường gặp trong các hàm sau: 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!

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 hàm IFERROR để xử lý lỗi nếu có khả năng xảy ra trường hợp chia cho 0.

7. #NUM!

Lỗi #NUM! thường xuất hiện trong các hàm sau: 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 người dùng lại nhập vào 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!

Khắc phục lỗi #NUM!: Chú ý đến điều kiện sử dụng số (số nguyên, số âm hoặc số dương, …)

8. #NULL!

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

Lỗi #NULL!

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 Mytour

II. Các mẹo xử lý khi gặp lỗi trong Google, Excel

Khi một công thức hiển thị giá trị lỗi (trừ #N/A) trong một ô, Excel sẽ hiển thị 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 thị các tùy chọn bên dưới để nhận biết các lỗi.

Kiểm tra lỗi

• Trợ giúp cho Lỗi Này: 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.

Trợ giúp về Lỗi

• Hiển Thị Bước Tính Tính Toán: Mở hộp thoại xem lại các bước tính. Với mỗi lần nhấn Đánh giá (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 đó.

Hiển Thị Bước Tính Tính Toán

• Bỏ Qua Lỗi: 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ó.
• Chỉnh Sửa trong Thanh Công thức: 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.

Chỉnh Sửa trong Thanh Công thức

• Tùy Chọn Kiểm Tra Lỗi: 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.

Tùy Chọn Kiểm Tra Lỗi

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 đỏ để xác định nguồn gốc lỗi. 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.

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'. Sử dụng NA để đánh dấu các ô trống. Bằng cách nhập #N/A vào những ô 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)

Sử dụng AutoFill cho các ô còn lại để nhận kết quả như hình bên dưới:

Ví dụ NA() 2

2. =ERROR.TYPE

ERROR.TYPE là hàm giúp phát hiện lỗi bằng cách trả về một số tương ứng với giá trị lỗi trong ô. 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ể sử dụng hàm ERROR.TYPE trong hàm IF để kiểm tra giá trị lỗi và trả về một chuỗi văn bản, ví dụ 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ụ minh họa:

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

Hàm 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: Check mã số học sinh để xuất điểm trung bình. Khi nhập đúng, cột điểm trung bình sẽ hiển thị số tương ứng với tên học sinh. Nếu sai, sẽ xuất kết quả lỗi #N/A. Lúc đó, 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

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ẽ trả kết quả Đúng (TRUE) nếu là lỗi #N/A, và Sai (FALSE) nếu không phải là 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 không phải là lỗi sẽ tiến hành xếp loại học sinh.

Ví dụ hàm ISNA

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)

Ví dụ minh họa:

Hàm ISERR

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

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 kết hợp hàm IF và ISERROR để kiểm tra lỗi như sau:

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

Trong trường hợp hàm ISERROR phát hiện lỗi chia cho 0, kết quả sẽ là khoảng trống.

Ví dụ ISERROR 2

Qua bài viết này, hy vọng bạn có thêm kiến thức và tự tin hơn khi sử dụng Excel. Chúc bạn thành công!