Làm thế nào để tránh công thức bị lỗi

Làm thế nào để tránh công thức bị lỗi

Nếu Excel không thể giải quyết công thức mà bạn đang tìm cách tạo, bạn có thể nhận được thông báo lỗi như sau:

Hình ảnh hộp thoại "Sự cố với công thức này" của Excel

Thật không may, thông báo này đồng nghĩa với việc Excel không thể hiểu công thức bạn đang cố gắng tạo, do đó, có thể bạn chỉ muốn rời khỏi đây và bắt đầu lại.

Bắt đầu bằng cách bấm vào OK hoặc nhấn ESC để đóng thông báo lỗi.

Bạn sẽ trở về ô chứa công thức bị hỏng, nằm trong chế độ chỉnh sửa, đồng thời Excel sẽ tô sáng vị trí đang có sự cố. Nếu bạn vẫn không biết phải làm gì khi nhận được thông báo lỗi và muốn bắt đầu lại, bạn có thể nhấn ESC lần nữa hoặc bấm nút Hủy bỏ trong thanh công thức, thao tác này sẽ giúp bạn thoát khỏi chế độ chỉnh sửa.

Hình ảnh nút Hủy bỏ Thanh Công thức

Nếu bạn không chắc chắn về việc cần làm tại thời điểm này hoặc loại trợ giúp bạn cần, bạn có thể tìm kiếm các câu hỏi tương tự trong Diễn đàn Cộng đồng Excel hoặc đăng một câu hỏi riêng của mình.

Liên kết đến Diễn đàn Cộng đồng của Excel

Nếu bạn muốn di chuyển lên trước thì danh sách kiểm sau đây sẽ cung cấp các bước giúp khắc phục sự cố nhằm giúp bạn tìm ra những gì có thể đã sai trong công thức của mình.

Excel đưa ra một loạt lỗi dấu thăng (#) như #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? và #NULL!, để cho biết công thức của bạn có phần khô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 tham đối. 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.

Ghi chú: #### không phải là lỗi liên quan đến công thức. Chuỗi ký tự này chỉ có nghĩa là cột không đủ rộng để hiện thị nội dung trong ô. Chỉ cần kéo cột để mở rộng hoặc đi đến Trang chủ > Định dạng > Tự Khớp Độ rộng Cột.

Hình ảnh Trang chủ > Định dạng > Tự Khớp Độ rộng Cột

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:

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.

Hộp thoại tham chiếu bị hỏng trong Excel

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 bỏ chọn Yêu cầu cập nhật liên kết tự động. Trong Excel 2007, bạn sẽ bấm vào Nút Office > Tùy chọn Excel. Nút Office 2007

Hình ảnh tùy chọn Yêu cầu cập nhật liên kết tự động

Quan trọng: Nếu đây là lần đầu, bạn làm việc với liên kết bị hỏng trong công thức, cần ôn lại cách giải quyết liên kết bị hỏng hoặc bạn không biết có cần cập nhật các tham chiếu hay không, hãy xem mục Điều khiển khi (liên kết) tham chiếu ngoài được cập nhật.

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 định Công thức, bấm Hiển thị Công thức.

    Mẹo: 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, rồi đ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.

    Hình ảnh hộp thoại Dữ liệu > Văn bản thành Cột

Khi một công thức không tính toán, bạn phải 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. Bấm tab Tệp, bấm Tùy chọn rồi bấm 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.

    Hình ảnh các tùy chọn Tính toán Tự động & Thủ công

Để 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, một cách 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 trường hợp đó, bạn sẽ cần bật tính năng Tính toán Lặp.

Để có thêm thông tin về tham chiếu vòng, hãy xem mục Tìm và khắc phục 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)=11/2

Khi bạn sử dụng hàm trong công thức, mỗi dấu ngoặc mở cần có một dấu ngoặc đóng để hàm được chính xác, vì thế, hãy đảm bảo mọi dấu ngoặc đơn đều nằm trong một cặp dấu ngoặc. Ví dụ: công thức =IF(B5<0),"Not valid",B5*1.05) không chính xác 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)

Ghi chú: Bạn sẽ thấy các tham đối của hàm được liệt kê trong thanh công cụ tham chiếu hàm trôi nổi bên dưới công thức khi bạn nhập.

Ảnh chụp màn hình của thanh công cụ Tham chiếu Hàm
Thanh công cụ tham chiếu hàm

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 bằng ký hiệu đô la ($) hoặc dấu tách thập phân (,) trong công thức vì ký hiệu đô la chỉ ra Tham chiếu Tuyệt đối và dấu phẩy là dấu tách tham đối. Thay vì nhập $1.000, hãy nhập 1000 trong công thức.

Nếu bạn sử dụng số đã định dạng trong tham đối, 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! vì hàm ABS chỉ chấp nhận một tham đối.

Ghi chú: Bạn có thể định dạng kết quả công thức bằng dấu tách thập phân và ký hiệu tiền tệ sau khi nhập công thức bằng các số chưa được định dạng (hằng số). Thông thường, không nên đặt hằng số vào các công thức, vì bạn có thể khó tìm thấy chúng nếu cần cập nhật sau đó, đồng thời các hằng số cũng dễ bị nhập sai hơn. Tốt hơn hết là bạn nên đặt hằng số vào các ô, nơi dễ thấy và dễ dàng được tham chiếu.

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 một công thức đơn giản =2+3 vào ô được định dạng dưới dạng văn bản, Excel sẽ không thể tính toán dữ liệu bạn đã nhập. Tất cả những gì bạn sẽ thấy trong ô là =2+3. Để khắc phục điều này, hãy thay đổi kiểu dữ liệu của ô từ Văn bản sang Tổng quát giống như này:

  1. Chọn ô.

  2. Bấm vào Trang chủ > mũi tên cạnh Định dạng Số (hoặc nhấn vào Ctrl + 1) rồi bấm vào Tổng quát.

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

Hộp thông báo yêu cầu thay x bằng * cho phép nhân
Thông báo lỗi sử dụng x với hằng số để nhân thay vì *

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

lỗi #NAME? khi sử dụng x với tham chiếu ô thay vì * cho phép nhân
Lỗi #NAME? sử dụng x với tham chiếu ô thay vì *

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 TEXTTODAY 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 đóng ngoặc kép để 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 hai bên 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 nháy hai bên tên của trang tính, công thức sẽ hiện lỗi #NAME?.

Bạn cũng có thể bấm vào 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).

Ghi chú: Nếu đường dẫn đầy đủ chứa các ký tự khoảng trắng, hãy đặt đường dẫn trong dấu nháy đơn (ở đầu đường dẫn và sau tên trang tính, trước dấu chấm than).

Mẹo: Cách dễ nhất để có được đường dẫn đến sổ làm việc khác là mở sổ làm việc khác, rồi từ sổ làm việc ban đầu, nhập =, sau đó sử dụng Alt+Tab để chuyển đến sổ làm việc khác và chọn ô bất kỳ trên trang tính bạn muốn. Sau đó, đóng sổ làm việc ban đầu. Công thức của bạn sẽ tự động cập nhật để hiển thị toàn bộ đường dẫn tệp và tên trang tính cùng với cú pháp bắt buộc. Bạn thậm chí có thể sao chép và dán đường dẫn, cũng như sử dụng vào mọi thời điểm bạn cần.

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

=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 một công thức tham chiếu đến những ô đã bị xóa hay bị thay thế bằng dữ liệu khác và trả về lỗi #REF!, hãy chọn ô có lỗi #REF! đó . Trong thanh công thức, hãy chọn #REF! và xóa nó. Sau đó, nhấn lại phạm vi cho công thức.

  • Nếu thiếu tên đã xác định, và tham chiếu đến tên đã xác định đó sẽ 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. Bấm 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, thay 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, bấm vào Sao chép Hình ảnh nút .

    Ảnh Ribbon Excel

    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.

    Mẹo: Để di chuyển hoặc sao chép vùng chọn vào một trang tính hoặc sổ làm việc khác, hãy bấm vào tab trang tính khác hoặc chuyển sang sổ làm việc khác, rồi sau đó 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, bấm vào Dán Ảnh nút , rồi bấm vào 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. Bấm Công thức > Đánh giá Công thức.

    Nhóm Kiểm nghiệm Công thức trên tab Công thức

  3. Bấm Đá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.

    Hộp thoại Định trị Công thức

  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 bấm Bước Vào để hiện công thức khác kia trong hộp Đánh giá. Bấm 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.

    Ghi chú: 

    • Một số phần của hàm IFCHOOSE 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á.

    • Những hàm 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.

Bạn cần thêm trợ giúp?

Bạn luôn có thể nhờ chuyên gia trong Cộng đồng Kỹ thuật Excel, tìm sự hỗ trợ trongCộng đồng Giải pháp hoặc đề xuất tính năng hay cải tiến mới trên Excel User Voice.

Xem Thêm

Tổng quan về công thức trong Excel

Phát hiện lỗi trong công thức

Các hàm Excel (theo thứ tự bảng chữ cái)

Các hàm Excel (theo thể loại)

Phát triển các kỹ năng của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×