Hướng dẫn cách sửa các lỗi công thức phổ biến trong Google Sheets

Sử dụng Google Sheets để tính toán sẽ khó có thể tránh được các lỗi, mỗi lỗi sẽ có thông báo khác nhau. Dưới đây Gitiho sẽ Hướng dẫn các bạn cách sửa các lỗi công thức phổ biến trong Google Sheets

Lỗi phân tích cú pháp công thức là gì?

Trước khi tìm hiểu các loại lỗi khác nhau, chúng ta cần hiểu lỗi phân tích cú pháp công thức nghĩa là gì?

Về cơ bản, điều đó có nghĩa là Google Sheets không thể diễn giải công thức của bạn nhập vào. Khi không thể đáp ứng yêu cầu công thức nó sẽ trả về một thông báo lỗi.
 

Điều này có thể xảy ra theo nhiều cách khác nhau, có thể từ lỗi chính tả đến lỗi toán học. Việc hiểu được ý nghĩa đằng sau các thông báo lỗi và tìm cách khắc phục là một bước quan trọng để sử dụng các công thức trong Google Sheets tính toán dễ hơn. 

Dưới đây là 9 thông báo lỗi phổ biến nhất trong Google Sheets và cách khắc phụ. 

Một cửa sổ bật lên thông báo lỗi phân tích cú pháp công thức, không cho nhập công thức khác. 

Bạn nghĩ rằng đã hoàn thành công thức của mình và nhấn Enter. Ngay lập tức một hộp thông báo bật lên với thông báo dạng …There  was aproblem hoặc tương tự như hình dưới đây.

Thường thì hiếm khi gặp lỗi này vì đây là lỗi khá cơ bản. Nhưng nếu có thì có thể khi nhấn phím Enter kết thúc, bạn cũng vô tình nhấn phím “\” và thêm phím đó vào cuối công thức.

Cách sửa lỗi này

Hãy cố gắng tránh những điều này ngay từ đầu bằng cách kiểm tra công thức trước khi nhấn Enter. Đảm bảo rằng bạn không thiếu tham chiếu ô và không có bất kỳ ký tự không mong muốn nào khác.

Thông báo lỗi #N/A 

Các lỗi #N/A biểu thị lỗi công thức phân tích rằng một giá trị tìm kiếm là không có sẵn.

Lỗi này xảy ra thường xuyên nhất khi bạn đang sử dụng các công thức tra cứu (ví dụ: VLOOKUP) và không tìm thấy cụm từ tìm kiếm. Về cơ bản lỗi này không phải là sai công thức nên bạn không cần phải xóa nó. 

Thay vào đó có thể hiển thị một thông báo tùy chỉnh, chẳng hạn như “Không tìm thấy kết quả”, thay vì thông báo lỗi #N/A như mặc định. 

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

Bạn có thể sử dụng hàm IFERROR

= IFERROR (công thức ban đầu, giá trị để hiển thị nếu công thức ban đầu có lỗi)

Trong ví dụ về hàm VLOOKUP này, công thức đầy đủ sẽ giống như sau:

=IFERROR(VLOOKUP(Search Term, Table, Column Index, FALSE),”Search term not found”)

Trong đó Search term not found để báo Không tìm thấy cụm từ tìm kiếm thay vì báo lỗi #N/A như trong ảnh hiển thị dưới đây.

Thông báo lỗi #DIV/0! 

Lỗi #DIV/0! Này chỉ xảy ra khi chia một số cho 0, khi đó số bị chia có thể là số 0 hoặc là ô trống. Ví dụ kiểu  như công thức 

=A/0

Một ví dụ khác là sử dụng hàm AVERAGE để tính giá trị trung bình với một phạm vi trống, ví dụ như công thức:

=AVERAGE(A1:A10)

Cách sửa lỗi #DIV/0!

Kiểm tra xem mẫu số có phải là 0 hoặc là ô trống không. Như trong trường hợp dưới đây, công thức ở mẫu số SUM(A1:A7) cho kết quả là 0, nên gây ra lỗi. Vì vậy, hãy kiểm tra xem kết quả mẫu số của bạn có bằng 0 hay không.

Nếu sau khi kiểm tra mà công thức vẫn đúng thì tông báo lỗi #DIV/0! nhìn khá tệ. Bây giờ bạn nên chỉnh sửa thông báo lỗi giống như cách thực hiện ở thông báo lỗi #N/A ở trên.

 

Hãy sử dụng hàm IFERROR để bao bọc công thức hiện tại của bạn và chỉ định kết quả khi lỗi #DIV/0! xảy ra. 

Thông báo lỗi #VALUE!

Thông báo lỗi trên thường xảy ra khi công thức của bạn có kiểu dữ liệu đầu vào không thống nhất. Ví dụ thực hiện các phép toán trên giá trị văn bản thay vì giá trị số. Hay khoảng trắng trong ô công thức cũng có thể gây ra thông báo lỗi này.

Trong ví dụ này, ô B1 chứa một khoảng trắng là một giá trị chuỗi và gây ra lỗi #VALUE! do Google Sheets không thể thực hiện một phép toán trên đó.

Một nguyên nhân khác gây ra lỗi #VALUE! là trộn các định dạng ngày của Mỹ (MM/DD/YYYY) và các nước còn lại trên thế giới (DD/MM/YYYY).

Cách sửa lỗi #VALUE!

Tìm kiếm bất kỳ văn bản/số nào có thể không khớp hoặc các ô chứa khoảng trắng. Nếu bạn nhấp vào một ô và con trỏ nhấp nháy có khoảng trống giữa chính nó và phần tử bên cạnh, thì bạn có một khoảng trống ở đó.

Các ô có thể nhìn trống vì không có số nhưng vẫn chứa khoảng trắng, như trong hình dưới đây.

Hay khoảng trống giữa định dạng ngày, tháng, năm cũng sẽ không hoạt động:

Thông báo lỗi #REF! 

Đây là lỗi phân tích cú pháp công thức xảy ra khi bạn có tham chiếu không hợp lệ. Ví dụ khi bạn tham chiếu một ô trong công thức đã bị xóa (không phải giá trị bên trong ô mà toàn bộ ô đã bị xóa, thường là khi bạn đã xóa một hàng hoặc cột trong sheets của mình).

Trong ví dụ này, công thức ban đầu là =A1*B1, nhưng khi xóa cột A, công thức bị lỗi do thiếu tham chiếu:

Ngoài ra khi bạn sao chép và dán bảng dữ liệu, phạm vi tương đối có thể di chuyển nằm ngoài giới hạn của trang tính, điều này không được phép và sẽ gây ra lỗi #REF!.

Bạn có thể đã thấy lỗi #REF! nếu sử dụng công thức tra cứu thường xuyên, khi bạn cố gắng trả về một giá trị nằm ngoài phạm vi đã chỉ định. 

Lỗi #REF! cũng xuất hiện khi phát hiện phụ thuộc vòng tròn (khi công thức tham chiếu đến chính nó).

Cách sửa lỗi #REF!

Trước hết, hãy đọc thông báo lỗi để xác định loại lỗi #REF! bạn cần giải quyết. 

Đối với các tham chiếu đã xóa, hãy tìm lỗi #REF! nằm trong công thức của bạn và thay thế lỗi #REF! với tham chiếu chính xác đến một ô hoặc dải ô.

Đối với các lỗi tra cứu ngoài giới hạn, hãy xem kỹ công thức và kiểm tra kích thước phạm vi với bất kỳ chỉ mục hàng hoặc cột nào bạn đang sử dụng.

Đối với các phụ thuộc vòng tròn, hãy tìm tham chiếu gây ra sự cố (tức là nơi bạn cũng tham chiếu đến ô hiện tại bên trong công thức của mình) và sửa đổi nó.

Ngoài ra, cách sửa lỗi #REF trên Excel cũng tương tự như Google Sheet, nếu bạn đang sử dụng cùng lúc cả 2 công cụ này, hãy tham khảo cách trên để khắc phục khi gặp phải lỗi nha.

Thông báo lỗi #NAME? 

Lỗi này là do cú pháp công thức của bạn sử dụng. Lỗi này chủ yếu do người dùng nhập tên các hàm không chính xác. Ví dụ như hình dưới đây, hàm SUM lại nhập thành SUMM

Lỗi #NAME? cũng xuất hiện khi tham chiếu đến một dải ô được đặt tên không thực sự tồn tại hoặc sai.

Lỗi này cũng xuất hiện khi công thức thiếu dấu ngoặc kép xung quanh giá trị văn bản, như công thức:

= CONCAT (“Thứ nhất”, Thứ hai)

Ở đây, Thứ hai thiếu dấu ngoặc kép

Cách sửa lỗi #NAME

Kiểm tra tên hàm của bạn đã chính xác chưa. Sử dụng trình hướng dẫn trợ giúp hàm để giảm khả năng xảy ra lỗi này, đặc biệt là đối với các hàm có tên dài. 

Kiểm tra xem bạn đã xác định tất cả các phạm vi được đặt tên trước khi sử dụng chúng trong công thức của mình và chúng đều có cách viết chính xác.

Kiểm tra bất kỳ giá trị văn bản nào được nhập với dấu ngoặc kép bắt buộc.

Cuối cùng, liệu bạn có bỏ lỡ dấu hai chấm trong tham chiếu phạm vi của mình không? Ví dụ như công thức

=SUM(A1A10)

Công thức này bị thiếu dấu hai chấm trong tham chiếu, phải sửa lại cho đúng là

= SUM(A1:A10)

Thông báo lỗi #NUM! 

Lỗi này xuất hiện khi công thức của bạn chứa các giá trị số không hợp lệ. Ví dụ như hình dưới đây, sử dụng công thức tính căn bậc hai của một số âm.

Một số hàm khác có thể dẫn đến lỗi #NUM! là các hàm SMALL và LARGE. Ví dụ nếu bạn tìm giá trị thứ n nhỏ nhất trong tập dữ liệu của mình, trong đó n nằm ngoài số giá trị trong tập dữ liệu, bạn sẽ nhận được lỗi #NUM!.

Như hình trên đây, công thức là tìm số nhỏ thứ 10, nhưng dữ liệu nguồn chỉ có 5 giá trị trong đó:

Cách sửa lỗi #NUM! 

Bạn cần kiểm tra các đối số trong công thức của mình. Thông báo lỗi sẽ cung cấp cho bạn một số gợi ý về phần nào của công thức đang gây ra sự cố.

Thông báo lỗi #ERROR! 

Đây là thông báo lỗi phân tích cú pháp công thức. Điều này có nghĩa là Google Sheets không thể hiểu công thức bạn đã nhập, vì nó không thể phân tích cú pháp công thức để thực thi.

Ví dụ: nếu nhập ký hiệu $ theo cách thủ công để đơn vị tiền tệ, Google Sheets sẽ cho rằng đó là một tham chiếu tuyệt đối.

hoặc bạn đã bỏ dấu “&” khi nối các giá trị văn bản và số:

Trong trường hợp này, công thức phải là: =”Total “&sum(A1:A3)

Một trường hợp khác, cũng có lỗi trên là nhầm lẫn các dấu ngoặc đóng của một công thức:

 

Cách sửa lỗi #ERROR!

Kiểm tra kỹ công thức của bạn để xem chính xác chưa.

Đảm bảo rằng bạn có đúng số lượng dấu ngoặc và cú pháp nối đúng giữa các giá trị văn bản và số (ví dụ: sử dụng “&”).

Khi muốn hiển thị các giá trị bằng ký hiệu tiền tệ hoặc dưới dạng phần trăm, đừng nhập thủ công “$” hoặc “%”. Thay vào đó, hãy nhập một số đơn giản và sau đó sử dụng các tùy chọn định dạng để thay đổi nó thành kiểu bạn muốn.

Thông báo lỗi #NULL! 

Hiện tại thông báo lỗi #NULL! phân tích cú pháp hiện tại rất hiếm và hầu như không xảy ra, nhưng về mặt lý thuyết nó vẫn tồn tại.

Nếu trong quá trình sử dụng các bạn bắt gặp lỗi này, hãy chia sẻ với chúng tôi để cùng nhau giải quyết nhé.

Các cách khác để xử lý lỗi phân tích cú pháp công thức

Tìm phần đánh dấu màu đỏ trong công thức của bạn vì điều này sẽ giúp xác định nguồn gốc của lỗi, ví dụ như trong trường hợp có quá nhiều dấu ngoặc, phần thừa, không cần thiết sẽ được đánh dấu bằng màu đỏ.

Lột vỏ hành tây: Đây là kỹ thuật gỡ lỗi cho các công thức phức tạp và dài. Bằng cách mở từng hàm bên ngoài trong công thức cho đến khi bạn làm cho nó hoạt động trở lại. Sau đó bắt đầu thêm lại từng hàm một và xem chính xác bước nào đang gây ra sự cố và khắc phục lỗi đó.

Các cú pháp khác nhau ở các quốc gia khác nhau : Một số quốc gia Châu Âu sẽ sử dụng dấu chấm phẩy “;” thay cho dấu phẩy “,” vì vậy đây có thể là nguyên nhân gây ra lỗi. 

Các chức năng giúp xử lý lỗi phân tích cú pháp công thức trong Google Sheets

Trên thực tế, thậm chí vẫn còn có một hàm tạo lỗi # N/A, hàm này được sử dụng hạn chế, nhưng có thể hữu ích để thực hiện xác thực dữ liệu trong các công thức phức tạp hơn.

=NA() sẽ xuất ra lỗi #N/A

=ERROR.TYPE(value) 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

=ISNA(value)

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ả là TRUE đối với lỗi #N/A và nếu không thì là FALSE. 

=ISERR(value)

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

=ISERROR(value)

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

Các chức năng này có thể được tóm tắt trong bảng sau:

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

CÓ THỂ BẠN CŨNG QUAN TÂM

Hướng dẫn cách chuyển văn bản thành số trong Google Sheets
Cách sao chép định dạng có điều kiện trong Google Sheets
Cách sắp xếp dữ liệu theo hàng ngang trong Google Sheets

Cuối cùng, đừng quên tham khảo khóa học Google Sheets của Gitiho bạn nhé! Hẹn gặp lại các bạn trong những bài viết tiếp theo.