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

Lưu ý: Chúng tôi muốn cung cấp cho bạn nội dung trợ giúp mới nhất ở chính ngôn ngữ của bạn, nhanh nhất có thể. Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Vui lòng cho chúng tôi biết ở cuối trang này rằng thông tin có hữu ích với bạn không? Dưới đây là bài viết bằng Tiếng Anh để bạn tham khảo dễ hơn.

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 hỏng, vốn sẽ nằm trong chế độ chỉnh sửa, và Excel sẽ tô sáng vị trí nơi nó đang gặp vấn đề. Nếu bạn vẫn không biết phải làm gì từ đó và muốn bắt đầu lại, bạn có thể nhấn ESC một lần nữa, hoặc bấm hủy bỏ nút trong thanh công thức, bạn sẽ 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 ném nhiều băm (#) lỗi như #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, và #NULL!, để cho biết điều gì đó trong công thức của bạn không hoạt động bên phải. Ví dụ, #VALUE! lỗi gây ra bởi định dạng không chính xác, hoặc không được hỗ trợ kiểu dữ liệu trong đối số. Hoặc, bạn sẽ thấy #REF! lỗi nếu một công thức tham chiếu đến ô đã bị xóa hoặc thay thế bằng dữ liệu khác. Hướng dẫn khắc phục sự cố sẽ khác nhau cho từng lỗi.

Lưu ý: #### 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 khi bạn mở một bảng tính có chứa công thức tham chiếu đến giá trị trong bảng tính khác, bạn sẽ được nhắc để cập nhật các tham chiếu hoặc rời khỏi chúng dưới dạng-.

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 lối tắt bàn phím Ctrl + ' (phím ở phía trên phím Tab). Khi bạn thực hiện điều này, cột của bạn sẽ tự động mở rộng để hiển thị công thức của bạn, nhưng đừng lo, khi bạn chuyển đổi trở lại dạng xem thường cột của bạn sẽ thay đổi kích cỡ.

  • Nếu bước trên đây vẫn không giải quyết vấn đề này, có thể là ô được định dạng dưới dạng văn bản. Bạn có thể bấm chuột phải vào ô và chọn định dạng ô > chung (hoặc Ctrl + 1), rồi nhấn F2 > Enter để thay đổi định dạng.

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

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

Khi công thức không tính toán, bạn phải kiểm tra nếu tính toán tự động được hỗ trợ trong Excel. Công thức sẽ không tính nếu tính toán theo cách thủ công được bật. Hãy làm theo các bước này để 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)

Lưu ý: 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ố chức năng, chẳng hạn như SUM, yêu cầu đối số số chỉ, trong khi các hàm khác, chẳng hạn như thay thế, yêu cầu giá trị văn bản cho ít nhất một đối số của họ. Nếu bạn sử dụng kiểu dữ liệu sai, chức năng có thể trả về kết quả không mong muốn hoặc hiển thị một #VALUE! lỗi.

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 dùng định dạng số trong đối số, bạn sẽ nhận được kết quả không mong muốn tính toán, nhưng bạn cũng có thể thấy các #NUM! lỗi. 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 Hiển thị #NUM! lỗi, vì hàm ABS nhận chỉ có một đối số.

Lưu ý: Bạn có thể định dạng kết quả công thức với dấu tách thập phân và tiền tệ ký hiệu sau khi bạn nhập công thức sử dụng các số (hằng số). Thật nói chung không nên đặt hằng số trong công thức, vì chúng có thể khó tìm nếu bạn cần cập nhật sau này, và chúng có dễ mắc phải được nhập không chính xác. Thật tốt hơn nhiều để đặt các hằng số trong ô, nơi họ xuất trong mở 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).

Lưu ý: 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 để lấy đường dẫn đến sổ làm việc khác là để mở sổ làm việc khác, sau đó từ sổ làm việc gốc của bạn, nhập =, sau đó sử dụng Phím Alt + Tab để chuyển sang sổ làm việc khác và chọn bất kỳ ô nào trên trang tính mà bạn muốn. Sau đó đóng sổ làm việc nguồn. Công thức của bạn sẽ tự động Cập Nhật để hiển thị toàn bộ đường dẫn và trang tính tên tệp cùng với các yêu cầu cú pháp. Bạn có thể thậm chí sao chép và dán đường dẫn và sử dụng bất kỳ lúc nào bạn cần 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 thiết cho bạn biết tại sao công thức của bạn bị hỏng, nhưng nó có thể giúp chỉ ra vị trí. Điều này có thể một công cụ rất hữu ích trong công thức lớn hơn mà nếu không thì khó để tìm các vấn đề.

    Lưu ý: 

    • 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ác công thức trong Excel

Phát hiện lỗi trong các 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 kỹ năng Office 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.

×