Cách tránh các công thức bị lỗi trong Excel

Công thức có nhiều đối số sử dụng dấu tách danh sách để phân tách các đối số. Dấu tách được sử dụng có thể khác nhau dựa trên cài đặt Ngôn ngữ hệ điều hành và Excel của bạn. Dấu tách danh sách thường gặp nhất là dấu phẩy "," và dấu chấm phẩy ";".

Công thức sẽ bị hỏng nếu bất kỳ hàm nào của công thức sử dụng dấu tách không chính xác.

Để biết thêm thông tin, vui lòng xem: Lỗi công thức khi dấu tách danh sách không được đặt đúng cách 

Excel trả về nhiều lỗi dấu thăng (#) khác nhau như #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? và #NULL!, để cho biết nội dung nào đó trong công thức của bạn không hoạt động đúng. Ví dụ: lỗi #VALUE! là do định dạng không chính xác hoặc kiểu dữ liệu không được hỗ trợ trong đối số. Hoặc bạn sẽ thấy lỗi #REF! nếu công thức tham chiếu đến các ô đã bị xóa hoặc được thay thế bằng dữ liệu khác. Hướng dẫn khắc phục sự cố sẽ khác nhau đối với từng lỗi.

Tham chiếu đến chủ đề bất kỳ trong số các chủ đề sau tương ứng với lỗi dấu thăng mà bạn thấy:

• Sửa lỗi #NUM! .

• Sửa lỗi #VALUE! .

• Sửa lỗi #N/A

• Sửa lỗi #DIV/0! .

• Sửa lỗi #REF! .

• Sửa lỗi #NAME? .

• Sửa lỗi #NULL! .

Mỗi lần bạn mở bảng tính chứa các công thức tham chiếu đến giá trị trong các bảng tính khác, bạn sẽ được nhắc cập nhật các tham chiếu hoặc giữ nguyên.

Excel hiển thị hộp thoại ở trên để đảm bảo rằng các công thức trong bảng tính hiện tại luôn hướng tới giá trị cập nhật nhất, trong trường hợp giá trị tham chiếu đã thay đổi. Bạn có thể chọn cập nhật các tham chiếu hoặc bỏ qua nếu không muốn cập nhật. Ngay cả khi chọn không cập nhật các tham chiếu, bạn luôn có thể cập nhật thủ công các liên kết trong bảng tính bất cứ khi nào bạn muốn.

Bạn luôn có thể tắt hộp thoại để hộp thoại không xuất hiện khi khởi động. Để thực hiện thao tác này, hãy đi đến Tệp > Tùy chọn > Nâng cao > Tổng quát, rồi xóa ô Yêu cầu cập nhật liên kết tự động.

Nếu công thức không hiển thị giá trị, hãy làm theo các bước sau:

• Đảm bảo Excel được đặt để hiển thị công thức trong bảng tính. Để thực hiện thao tác này, hãy bấm vào tab Công thức và trong nhóm Kiểm tra công thức, chọn Hiển thị công thức.

  : Bạn cũng có thể sử dụng phím tắt Ctrl + ` (phím ở trên phím Tab). Khi bạn thực hiện thao tác này, các cột sẽ tự động mở rộng để hiển thị công thức nhưng đừng lo lắng, vào lúc bạn chuyển đổi trở lại chế độ xem thường, các cột sẽ đổi kích cỡ.

• Nếu các bước ở trên vẫn không giải quyết được sự cố thì có thể ô được định dạng dưới dạng văn bản. Bạn có thể bấm chuột phải vào ô, rồi chọn Định dạng Ô > Tổng quát (hoặc Ctrl + 1), sau đó nhấn F2 > Enter để thay đổi định dạng.

• Nếu bạn có phạm vi lớn các ô trong cột được định dạng dưới dạng văn bản thì bạn có thể chọn phạm vi đó, áp dụng định dạng số cho các ô bạn chọn, và đi đến Dữ liệu > Văn bản thành Cột > Hoàn tất. Thao tác này sẽ áp dụng định dạng cho mọi ô được chọn.

  

Khi một công thức không tính toán, bạn sẽ cần kiểm tra xem tính năng tính toán tự động có được bật trong Excel không. Công thức sẽ không tính nếu tính năng tính toán thủ công được bật. Làm theo các bước sau để kiểm tra Tính toán tự động.

1. Chọn tab Tệp, chọn Tùy chọn rồi chọn thể loại Công thức.

2. Trong mục Tùy chọn tính toán, dưới Tính toán Sổ làm việc, hãy đảm bảo tùy chọn Tự động được chọn.

   

Để có thêm thông tin về tính toán, hãy xem mục Thay đổi tính toán lại, lần lặp hoặc độ chính xác của công thức.

Tham chiếu vòng xảy ra khi công thức tham chiếu đến ô nằm trong. Biện pháp khắc phục là di chuyển công thức sang một ô khác hoặc thay đổi cú pháp công thức thành một cú pháp tránh tham chiếu vòng. Tuy nhiên, trong một số trường hợp bạn có thể cần tham chiếu vòng vì chúng khiến hàm của bạn lặp lại cho đến khi đáp ứng được một điều kiện số cụ thể. Trong những trường hợp như vậy, bạn sẽ cần phải bật Loại bỏ hoặc cho phép tham chiếu vòng.

Để biết thêm thông tin về tham chiếu vòng, hãy xem Loại bỏ hoặc cho phép tham chiếu vòng.

Nếu mục nhập của bạn không bắt đầu bằng ký hiệu dấu bằng thì đó không phải là công thức và sẽ không được tính—một lỗi phổ biến.

Khi bạn nhập dữ liệu như SUM(A1:A10), Excel hiển thị chuỗi văn bản SUM(A1:A10) thay vì kết quả công thức. Bây giờ, nếu bạn nhập 11/2, Excel sẽ hiển thị một ngày, như 2-T11 hoặc 02/11/2009, thay vì chia 11 cho 2.

Để tránh kết quả không mong muốn, hãy luôn bắt đầu hàm bằng ký hiệu dấu bằng. Ví dụ, nhập: =SUM(A1:A10) và =11/2.

Khi bạn dùng hàm trong công thức, mỗi dấu mở ngoặc đơn cần phải có dấu đóng ngoặc đơn để hàm hoạt động chính xác. Hãy đảm bảo rằng tất cả các dấu ngoặc đơn là một phần của cặp dấu khớp nhau. Ví dụ, công thức =IF(B5<0),"Not valid",B5*1.05) không hoạt động vì có hai dấu ngoặc đóng nhưng chỉ có một dấu ngoặc mở. Công thức chính xác sẽ là: =IF(B5<0,"Not valid",B5*1.05).

Các hàm Excel có tham đối—giá trị bạn cần để cung cấp giúp hàm hoạt động. Chỉ một số hàm (như PI hoặc TODAY) không có tham đối. Kiểm tra cú pháp công thức xuất hiện khi bạn bắt đầu nhập trong hàm, nhằm đảm bảo hàm có các tham đối bắt buộc.

Ví dụ, hàm UPPER chỉ nhận chuỗi văn bản hoặc tham chiếu ô làm đối số của nó: =UPPER("hello") hoặc =UPPER(C2)

Ngoài ra, một số hàm, như SUM, chỉ yêu cầu tham đối dạng số, trong khi các hàm khác, như REPLACE, lại yêu cầu giá trị văn bản cho ít nhất một trong các tham đối của hàm. Nếu bạn sử dụng kiểu dữ liệu không chính xác, hàm có thể trả về kết quả không mong muốn hoặc hiển thị lỗi #VALUE!.

Nếu bạn cần tra cứu nhanh cú pháp của một hàm cụ thể, hãy xem danh sách Hàm Excel (theo danh mục).

Không nhập số được định dạng với dấu đô-la ($) hoặc dấu tách thập phân (,) trong công thức, vì dấu đô-la cho biết Tham chiếu tuyệt đối và dấu phẩy là dấu tách phân số. Thay vì nhập $1,000, hãy nhập 1000 trong công thức.

Nếu bạn sử dụng số được định dạng trong đối số, bạn sẽ nhận được kết quả tính toán không mong muốn, nhưng bạn cũng có thể nhìn thấy lỗi #NUM!. Ví dụ: nếu bạn nhập công thức =ABS(-2,134) để tìm giá trị tuyệt đối của -2134, Excel sẽ hiển thị lỗi #NUM! lỗi, vì hàm ABS chỉ chấp nhận một đối số và hàm sẽ xem các đối số -2 và 134 là các đối số riêng biệt.

Công thức của bạn có thể trả về kết quả ngoài dự kiến nếu không thể sử dụng được kiểu dữ liệu của ô trong tính toán. Ví dụ, nếu bạn nhập công thức đơn giản =2+3 trong ô có định dạng là văn bản, TE102825393 không thể tính toán dữ liệu mà bạn đã nhập. Tất cả những gì bạn nhìn thấy trong ô là =2+3. Để khắc phục vấn đề này, hãy thay đổi kiểu dữ liệu của ô từ Văn bản thành Chung giống như thế này:

1. Chọn ô.

2. Chọn Trang chủ và chọn mũi tên để bung rộng nhóm Số hoặc Định dạng số (hoặc nhấn Ctrl + 1). Sau đó chọn Chung.

3. Nhấn F2 để đưa ô vào chế độ chỉnh sửa, rồi nhấn vào Enter để chấp nhận công thức.

Ngày bạn nhập trong ô bằng cách sử dụng kiểu dữ liệu Số có thể được hiển thị dưới dạng giá trị ngày dạng số thay vì ngày. Để hiển thị số dưới dạng ngày, hãy chọn định dạng Ngày trong bộ sưu tập Định dạng Số.

Việc sử dụng x dưới dạng toán tử nhân trong công thức là khá phổ biến nhưng Excel chỉ có thể chấp nhận dấu sao (*) cho phép nhân. Nếu bạn sử dụng hằng số trong công thức, Excel sẽ hiển thị thông báo lỗi và có thể sửa công thức cho bạn bằng cách thay x bằng dấu sao (*).

Tuy nhiên, nếu bạn sử dụng tham chiếu ô, Excel sẽ trả về lỗi #NAME? lỗi.

Nếu bạn tạo công thức bao gồm văn bản, hãy đặt văn bản đó vào trong dấu ngoặc.

Ví dụ: công thức ="Hôm nay là " & TEXT(TODAY(),"dddd, mmmm dd") kết hợp với văn bản "Hôm nay là ", cùng kết quả của các hàm TEXT và TODAY và trả về các kết quả là Hôm nay là thứ Hai, ngày 30 tháng 5.

Trong công thức, "Hôm nay là " có khoảng trắng trước dấu ngoặc kép đóng để cung cấp khoảng cách mà bạn muốn giữa các từ "Hôm này là" và "thứ Hai, ngày 30 tháng Năm". Không có dấu ngoặc kép xung quanh văn bản, công thức có thể hiện lỗi #NAME?.

Bạn có thể kết hợp (hay lồng) tối đa 64 cấp độ hàm trong một công thức.

Ví dụ, công thức =IF(SQRT(PI())<2,"Nhỏ hơn hai!","Lớn hơn hai!") có 3 cấp độ hàm: Hàm PI nằm trong hàm SQRT, kết quả là nằm trong hàm IF.

Khi bạn nhập một tham chiếu đế giá trị hay ô trong trang tính khác, và tên của trang này có một ký tự không thuộc bảng chữ cái (như khoảng trắng), hãy đặt tên đó trong dấu nháy đơn (').

Ví dụ, để trả về giá trị từ ô D3 trong trang tính có tên Dữ liệu hàng quý trong cùng sổ làm việc, hãy nhập: ='Dữ liệu hàng quý'!D3. Không có dấu ngoặc kép xung quanh tên trang tính, công thức sẽ hiện lỗi #NAME?.

Bạn cũng có thể chọn giá trị hay ô ở trang tính khác để tham chiếu đến giá trị đó trong công thức của mình. Khi đó Excel sẽ tự động thêm dấu nháy ở hai bên tên trang tính.

Khi bạn nhập tham chiếu đến giá trị hay ô trong sổ làm việc khác, hãy đưa tên sổ làm việc nằm trong hai dấu ngoặc vuông ([]) và tiếp theo sau là tên của trang tính mà có giá trị hay ô đó.

Ví dụ, để tham chiếu đến ô A1 đến A8 trên trang tính Bán hàng trong sổ làm việc Hoạt động kinh doanh Q2 đang được mở trong Excel, hãy nhập: =[Q2 Operations.xlsx]'Bán hàng'!A1:A8. Không có dấu ngoặc vuông, công thức sẽ hiện lỗi #REF!.

Nếu sổ làm việc không mở trong Excel, hãy nhập đường dẫn đầy đủ đến tệp đó.

Ví dụ, =ROWS('C:\My Documents\[Hoạt động kinh doanh Q2.xlsx]'Bán hàng''!A1:A8).

Cách chia một ô bởi một ô khác mà có giá trị không (0) hoặc không có giá trị dẫn đến lỗi #DIV/ 0!.

Để tránh lỗi này, bạn có thể giải quyết trực tiếp và kiểm tra sự tồn tại của mẫu số. Bạn có thể sử dụng: 

=IF(B1,A1/B1,0)

Hàm cho biết IF(B1 tồn tại, rồi chia A1 cho B1, nếu không thì trả về 0).

Luôn kiểm tra xem bạn có bất kỳ công thức nào tham chiếu đến dữ liệu trong ô, phạm vi, tên đã xác định, trang tính hay sổ làm việc, trước khi xóa bất kỳ thứ gì. Sau đó bạn có thể thay thế những công thức này bằng kết quả của chúng trước khi loại bỏ dữ liệu được tham chiếu.

Nếu bạn không thể thay thế công thức bằng kết quả của chúng, hãy xem lại thông tin này về các lỗi và các giải pháp khả dĩ:

• Nếu công thức tham chiếu đến các ô đã bị xóa hoặc được thay thế bằng dữ liệu khác và nếu công thức trả về lỗi #REF!, chọn ô có #REF! lỗi. Trong thanh công thức, hãy chọn #REF! và xóa nó. Sau đó nhập lại phạm vi cho công thức.

• Nếu thiếu một tên đã xác định và một công thức tham chiếu đến tên đó trả về lỗi #NAME?hãy xác định tên mới tham chiếu đến phạm vi bạn muốn hoặc thay đổi công thức để tham chiếu trực tiếp đến phạm vi ô đó (ví dụ: A2:D8).

• Nếu thiếu một trang tính, và công thức tham chiếu đến trang tính đó trả về lỗi #REF! thì không may là không cách gì có thể khắc phục điều này - không thể phục hồi một trang tính bị xóa.

• Nếu thiếu một sổ làm việc, công thức tham chiếu đến sổ làm việc đó vẫn còn nguyên vẹn cho đến khi bạn cập nhật công thức.

  VÍ dụ, nếu công thức của bạn là =[Book1.xlsx]Sheet1'!A1 và bạn không còn Book1.xlsx nữa, thì những giá trị được tham chiếu trong sổ làm việc đó vẫn còn đó. Tuy nhiên, nếu bạn sửa rồi sau đó lưu công thức mà có tham chiếu đến sổ làm việc đó, Excel sẽ hiện hộp thoại Cập nhật Giá trị và nhắc bạn nhập tên tệp. Chọn Hủy, và sau đó đảm bảo rằng dữ liệu này không bị mất bằng cách thay thế công thức có tham chiếu đến sổ làm việc đang thiếu bằng kết quả của công thức.

Đôi khi, khi bạn sao chép nội dung của một ô, bạn chỉ muốn dán giá trị chứ không phải là công thức cơ bản được hiển thị trong thanh công thức.

Ví dụ, bạn có thể muốn sao chép giá trị kết quả của công thức vào một ô trên trang tính khác. Hoặc bạn có thể muốn xóa các giá trị đã sử dụng trong công thức sau khi sao chép giá trị kết quả vào một ô khác trên trang tính. Cả hai hành động này đều gây ra lỗi tham chiếu ô không hợp lệ (#REF!) để xuất hiện trong ô đích, vì không thể tham chiếu các ô chứa giá trị mà bạn đã sử dụng trong công thức được nữa.

Bạn có thể tránh lỗi này bằng cách dán các giá trị kết quả của công thức mà không cần tới công thức trong ô đích.

1. Trên trang tính, hãy chọn các ô chứa giá trị kết quả của công thức mà bạn muốn sao chép.

2. Trên tab Trang chủ, trong nhóm Bảng tạm, chọn Sao chép .

   

   Phím tắt: Nhấn CTRL+C.

3. Chọn ô phía trên cùng bên trái của vùng dán.

   : Để di chuyển hoặc sao một vùng chọn sang một trang tính hay sổ làm việc khác, hãy chọn tab trang tính khác hoặc chuyển sang sổ làm việc khác rồi chọn ô phía trên bên trái của vùng dán.

4. Trên tab Trang chủ, trong nhóm Bảng tạm, chọn Dán , rồi chọn Dán giá trị hoặc nhấn Alt > E > S > V > Enter cho Windows hoặc Tùy chọn > Lệnh > V > V > Enter trên máy Mac.

Để hiểu làm cách nào mà một công thức phức tạp hay có dạng lồng nhau tính toán ra được kết quả cuối cùng, bạn có thể đánh giá công thức đó.

1. Chọn công thức bạn muốn đánh giá.

2. Chọn Công thức > Đánh giá công thức.

   

3. Chọn Đánh giá để kiểm tra giá trị của tham chiếu được gạch dưới. Kết quả đánh giá được hiển thị dạng in nghiêng.

   

4. Nếu phần gạch dưới trong công thức là một tham chiếu đến công thức khác, hãy chọn Bước vào để hiện công thức kia trong ô Đánh giá. Chọn Bước ra để trở lại ô và công thức trước.

   Nút Bước vào không sẵn có ở lần thứ nhì tham chiếu đó hiện ra trong công thức, hoặc nếu công thức tham chiếu đến một ô trong sổ làm việc khác.

5. Tiếp tục cho đến khi đánh giá xong từng phần của công thức.

   Công cụ Đánh giá công thức sẽ không cần cho bạn biết lý do tại sao công thức của bạn bị hỏng nhưng công cụ này có thể giúp chỉ ra nơi công thức bị hỏng. Đây có thể là một công cụ rất hữu ích trong các công thức lớn hơn, vì có thể khó tìm thấy sự cố trong các công thức này.

   : 

   • Một số phần của hàm IF và CHOOSE sẽ không được đánh giá, nhưng lỗi #N/A có thể xuất hiện trong hộp Đánh giá.

   • Những tham chiếu trống được hiển thị dưới dạng giá trị không (0) trong hộp Đánh giá.

   • Một số hàm sẽ được tính toán lại mỗi lần thay đổi trang tính. Những hàm đó bao gồm hàm RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY, và RANDBETWEEN, và chúng có thể làm cho hộp thoại Đánh giá Công thức hiện kết quả khác với kết quả thật sự trong ô của trang tính.