Hướng dẫn cách khắc phục lỗi công thức không hoạt động trong Google Sheets

Cho dù bạn là người mới bắt đầu hay là một chuyên gia dày dạn kinh nghiệm với Google Sheets, sớm hay muộn, một trong các công thức trên trang tính cũng sẽ có lúc gặp thông báo lỗi cú pháp. Nó có thể sẽ rất ức chế, đặc biệt khi đó là 1 công thức dài và cảnh báo lỗi hệ thống đưa ra đọc chẳng hiểu gì cả.

 

Trong bài đăng này, Gitiho sẽ giải thích lỗi phân tích cú pháp công thức (formula parse error) Google Sheets là gì, cách xác định nguyên nhân và cách khắc phục.

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

Trước khi chúng ta đi sâu vào phân tích nguyên nhân và cách phục, ta cần xác định được lỗi phân tích cú pháp công thức là gì. Về cơ bản, lỗi này có nghĩa là Google Sheets không thể diễn giải công thức của bạn, nó không thể đáp ứng yêu cầu công thức và vì vậy hiện thông báo lỗi. Nguyên nhân của lỗi này có thể đến từ nhiều lí do - từ lỗi chính tả đến lỗi toán học.

Kiểm tra và sửa các lỗi phân tích cú pháp công thức trong Google Sheets 

1. Cửa sổ báo lỗi phân tích cú pháp công thức ngăn bạn nhập công thức

 

Đây là trường hợp giống như thế này:

Thật ra, trường hợp này tương đối hiếm gặp và đến từ một số vấn đề cơ bản trong công thức của bạn.

 

Ví dụ: hãy tưởng tượng rằng khi bạn nhấn phím Enter, bạn cũng vô tình nhấn phím “\” (nằm ngay trên phím Enter) và vô tình thêm phím đó vào cuối công thức của bạn:

Sơ suất này sẽ làm hệ thống báo lỗi; và ta có thể dễ dàng sửa nó bằng cách xóa ký tự không mong muốn.

Làm thế nào để sửa lỗi này?

Hãy cố gắng tránh những sơ suất 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.

Xem thêm: Hướng dẫn tô màu xen kẽ giữa các hàng trong Google Sheets

 

 

2. Xuất hiện báo lỗi #N/A. Làm thế nào để sửa nó đây?

Lỗi phân tích cú pháp công thức #N/A biểu thị rằng một giá trị không có sẵn.

Điều này xảy ra thường xuyên nhất khi bạn đang sử dụng công thức tra cứu (ví dụ: VLOOKUP) và không tìm thấy cụm từ tìm kiếm. Cụ thể hơn, như trong ví dụ trên hình: cụm từ tìm kiếm A-051 không có trong bảng dữ liệu do đó, công thức trả về #N/A.

Công thức này không sai hoặc bị hỏng nên bạn không cần phải xóa nó đi. Gitiho nghĩ nếu thông báo là “Không tìm thấy kết quả”, thay vì thông báo lỗi # N / A, sẽ giúp người dùng bớt nhầm lẫn hơn (nhưng thôi không sao, vì chúng ta có cách sau đây)

 

Làm thế nào để sửa lỗi #N/A?

Có 1 công thức tên là IFERROR siêu tiện dụng này:

=IFERROR(original formula, value to display if the original formula gives an error)

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

 

như được hiển thị dưới đây:

Thay vì hiển thị lỗi phân tích cú pháp công thức #N/A khi không tìm thấy giá trị, công thức sẽ xuất ra thông báo tùy chỉnh của riêng bạn thay vì "Không tìm thấy cụm từ tìm kiếm".

Xem thêm: Cách khắc phục lỗi về ô và dòng trong Google Sheets

 

 

3. Hệ thống báo lỗi #DIV/0! 

Lỗi phân tích cú pháp công thức này xảy ra khi một số bị chia cho 0, xảy ra khi bạn có một số 0 hoặc một tham chiếu ô trống trong mẫu số.

Một ví dụ khác là sử dụng một công thức như AVERAGE với một phạm vi trống.

Cụ thể, giả sử bạn nhập = AVERAGE (A1: A10) , hệ thống sẽ báo lỗi #DIV/0! nếu phạm vi A1: A10 không chứa giá trị số.

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

Điều đầu tiên cần làm là xác định lý do tại sao mẫu số của bạn được đánh giá bằng 0.

 

Bạn có thể chọn mẫu số và xem nó đang đánh giá cái gì bằng cách: đánh dấu nó trong thanh công thức và xem kết quả trong hộp bật lên nhỏ, như được hiển thị trong hình ảnh này:

Trong trường hợp này, công thức ở mẫu số SUM (A1: A7) cho kết quả là 0, và nó gây 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.

Tiếp theo, hãy kiểm tra xem bạn có liên kết đến các ô trống hay một dải ô trống trong mẫu số của mình hay không. Sau đó, bạn có thể điền số vào ô hoặc dải ô trống đó hoặc chọn một ô hoặc dải ô khác cho công thức của mình.

 

Và cuối cùng, như với ví dụ về lỗi #N/A, bạn có thể sử dụng công thức IFERROR để bao bọc công thức hiện tại của bạn, chỉ định kết quả khi lỗi #DIV/0! xảy ra. Bạn có thể muốn xuất một thông báo lỗi, ví dụ: “Lỗi chia cho số 0” hoặc có thể là một giá trị cụ thể, ví dụ: 0:

4. Hệ thống báo lỗi #VALUE!

Lỗi cú pháp này thường xảy ra khi công thức của bạn đang mong đợi một kiểu dữ liệu nhất định làm đầu vào nhưng nhận được kiểu dữ liệu sai, ví dụ: cố gắng thực hiện các phép toán trên giá trị văn bản thay vì giá trị số. Khoảng trắng trong ô của bạn 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 đó, như được thấy như sau:

Nhìn chung, Google Sheets thực hiện khá tốt việc chuyển dữ liệu văn bản thành dữ liệu số khi cần thiết. Nếu bạn nhập một giá trị vào ô có khoảng trắng, hãy định dạng nó dưới dạng văn bản và sau đó thử làm toán trên đó, Google Trang tính sẽ thực sự buộc văn bản bạn nhập chuyển thành một số và vẫn thực hiện phép tính.

Vậy nên, lỗi này sẽ thường gặp hơn trong trường hợp bạn thao tác với dữ liệu ngày tháng. Khi đó, nguyên nhân khiến hệ thống báo lỗi #VALUE! là sự không thống nhất trong định dạng ngày. Ví dụ: định dạng ngày ở Hoa Kỳ có dạng MM/DD/YYYY trong khi phần còn lại của Thế giới là DD/MM/YYYY. Nếu bạn trừ một ô được định dạng theo format Mỹ với một ô theo format Việt, lỗi sẽ xảy ra.

 

Trên thực tế, cũng giống như vấn đề văn bản/số ở trên, ngày tháng được lưu trữ dưới dạng số, nhưng nếu ngày của bạn ở định dạng khác đi với nguyên nhân là theo quốc gia, thì ngày đó sẽ được lưu trữ dưới dạng chuỗi văn bản và Google sẽ không biết nó có nghĩa là một ngày. Giống như sau:

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

Thông báo lỗi sẽ cho bạn một số thông tin về phần nào trong công thức của bạn đang gây ra sự cố.

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 sai. Nếu bạn nhấp vào một ô và con trỏ nhấp nháy có một khoảng trống giữa chính nó và phần tử bên cạnh, thì có nghĩa là có một khoảng trống ở đó.

 

Các ô có thể trông không có gì nhưng vẫn chứa khoảng trắng:

Những ngày có khoảng trắng ở giữa cũng sẽ không hoạt động:

Xem thêm: 4 cách tạo các loại biểu đồ trong Google Sheets chuyên nghiệp

5. Hệ thống hiện báo lỗi #REF!

Thông báo lỗi #REF! thường xảy ra khi bạn có tham chiếu không hợp lệ. Chúng đến từ 1 trong lí do như:

Thiếu tham chiếu: 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 trang tính 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:

Một lí do khác, công thức có thể đã tham chiếu đến các tham chiếu bị thiếu; ví dụ như bạn đang muốn sao chép ô có công thức SUM của 3 ô, nhưng lại dán ở ô hiển thị tổng của 2 ô, như trong hình này: 

Tra cứu ngoài giới hạn: Có thể bạn đã thấy lỗi #REF! khi bạn sử dụng công thức tra cứu thường xuyên, nhất là lúc bạn cố gắng trả về một giá trị nằm ngoài phạm vi bạn đã chỉ định. Trong ví dụ về VLOOKUP dưới đây, chúng ta sẽ có câu trả cho trường hợp từ cột thứ 3 của bảng tìm kiếm chỉ có 2 cột:

 

 

Phạm phải vòng lặp: Bạn cũng sẽ nhận được #REF! lỗi khi công thức tham chiếu đến chính nó, như tính tổng của 3 ô nhưng kéo đến 4 ô như trong hình:

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 đang giải quyết là gì; nó sẽ gợi ý cho bạn.

Đố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 của bạn và kiểm tra kích thước phạm vi của bạn 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 lỗi phạm phải vòng lặp, hãy tìm tham chiếu đang 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, 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ả 2 công cụ này, bạn có thể tham khảo và áp dụng để khắc phục lỗi nhé.

6. Hệ thống báo lỗi #NAME?

 

 

Lỗi #NAME thông báo có sự cố với cú pháp công thức của bạn. Lý do phổ biến nhất là lỗi chính tả ở một trong các tên hàm của bạn. Như trong ví dụ dưới đây, hàm SUM bị viết sai thành SUMM và hệ thống không nhận dạng được, vậy nên nó trả về lỗi:

Một lý do khác dẫn đến lỗi #NAME? là hệ thống đang tham chiếu đến một dải ô được đặt tên không thực sự tồn tại hoặc đã bị sai.

Vì thế

=SUM(profit)

sẽ làm hệ thống báo lỗi #NAME? nếu phạm vi “profit” được đặt tên không tồn tại

Thiếu dấu ngoặc kép xung quanh giá trị văn bản, như được hiển thị trong công thức đơn giản này, cũng sẽ gây ra lỗi #NAME?:

=CONCAT(“First”,Second)

 

(Từ Second bị thiếu dấu ngoặc kép.)

Cách sửa lỗi #NAME?

Đầu tiên, kiểm tra tên hàm của bạn có chính xác không. Bạn có thể sử dụng trình hướng dẫn trợ giúp hàm để giảm thiểu khả năng xảy ra lỗi, đặc biệt là đối với các hàm có dài. Khi bắt đầu nhập công thức, bạn sẽ thấy một menu các chức năng, bạn có thể chọn menu này bằng các mũi tên lên xuống và Tab.

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 hay chưa, đồng thời đảm bảo chúng đều được viết đúng.

Kiểm tra bất kỳ giá trị nào có sự xuất hiện của dấu ngoặc kép và đảm bảo chúng chuẩn xác.

Cuối cùng, kiểm tra xem bạn có bỏ sót dấu hai chấm trong tham chiếu phạm vi của mình không, vì nó sẽ không được hệ thống đánh dấu là chính xác. Ví dụ như công thức này:

=SUM(A1A10)

thiếu dấu hai chấm giữa A1 và A10 và sẽ phạm lỗi #NAME?.

Hàm đúng phải là =SUM(A1:A10)

Xem thêm: Cách tạo modal dialog, modeless dialog và sizebar trên google sheets

7. Hệ thống báo lỗi #NUM!

Lỗi #NUM! là lỗi xảy ra khi công thức của bạn chứa các giá trị số không hợp lệ.

 

Ví dụ như bạn lệnh cho hệ thống tìm căn bậc hai của một số âm, (ai cũng biết điều này là bất khả thi)

Một số hàm khác có thể dẫn đến lỗi #NUM! là các hàm SMALL và LARGE. Nếu bạn cố gắng 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 của bạn, bạn sẽ nhận được thông báo lỗi #NUM!.

 

Ví dụ: bạn yêu cầu Google Sheets tìm số nhỏ thứ 10 trong tập dữ liệu chỉ có 5 giá trị trong đó:

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

 

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

8. Hệ thống báo lỗi #ERROR!

 

Đây là lỗi duy nhất trong Google Sheets không có thông báo tương tự như trong Excel. Lỗi 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 bạn nhập ký hiệu $ theo cách thủ công để chỉ số tiền, nhưng Google Sheets cho rằng bạn đang đề cập đến một tham chiếu tuyệt đối:

Hoặc bạn đã bỏ lỡ 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 xảy ra khi bạn có sự nhầm lẫn các dấu đóng ngoặc trong một công thức:

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

Kiểm tra kỹ công thức của bạn để biết độ chính xác. Bạn cần đảm bảo rằng đã có đúng số lượng dấu ngoặc và cú pháp nối chính xác giữa giá trị văn bản và số (ví dụ: sử dụng “&”).

 

Khi bạn muốn hiển thị các giá trị bằng ký hiệu tiền tệ hoặc dưới dạng tỷ lệ phần trăm, không nhập “$” hoặc “%” theo cách thủ công. 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.

Xem thêm: Hướng dẫn cách cố định hàng/cột trong Google Sheets

 

10. Các phương pháp 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ụ: trong trường hợp có quá nhiều dấu ngoặc, những dấu ngoặc thừa sẽ được tô màu đỏ.

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

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ủa bạn. So sánh hai công thức này, có đầu vào và đầu ra giống hệt nhau, nhưng cú pháp khác nhau đối với người dùng ở các quốc gia khác nhau (ngôn ngữ).

=ArrayFormula(VLOOKUP(A1;Sheet2!A:I;{2\3\4\5\6\7\8};FALSE))

là công thức tương tự như sau:

=ArrayFormula(VLOOKUP(A1,Sheet2!A:I,{2,3,4,5,6,7,8};FALSE))

(Đây là ví dụ về hàm VLOOKUP trả về nhiều giá trị (một mảng) thay vì chỉ một giá trị duy nhất.)

Mẹo chuyên nghiệp:

 

Sử dụng dấu nháy đơn ở đầu công thức để biến nó thành một chuỗi văn bản, chuỗi này sẽ không thực thi. Mẹo này đôi khi khá hữu ích để bạn xem lại toàn bộ công thức và gỡ lỗi, giữ một bản sao công thức để bạn có thể sao chép và dán các phần của nó vào ô khác để kiểm tra.

Xem thêm: Hướng dẫn tạo danh sách tùy chọn Drop List trong Google Sheets

Kết luận

Hi vọng sau bài viết này, bạn sẽ không phải bối rối khi đột nhiên, trang tính nhảy lỗi tứ tung mà không biết bắt đầu từ đâu để xử lí.

Hoặc nếu muốn không gặp phải mấy lỗi phiền phức này thì tham khảo khóa học Google Sheets của Gitiho nhé!