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, điều này có nghĩa là Excel không thể tìm hiểu những gì bạn đang cố gắng thực hiện, vì vậy bạn có thể chỉ muốn bắt đầu lại.

Bắt đầu bằng cách chọn 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 cần 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 chọn nút hủy bỏ trong thanh công thức, mà sẽ đưa bạn ra khỏi chế độ chỉnh sửa.

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

Nếu bạn muốn di chuyển tiếp, thì danh sách kiểm tra sau đây sẽ cung cấp các bước khắc phục sự cố để giúp bạn tìm hiểu những gì có thể xảy ra.

Lưu ý: Nếu bạn đang sử dụngOffice dành cho web, bạn có thể không nhìn thấy lỗi tương tự hoặc các giải pháp có thể không áp dụng.

Excel đã ném một loạt các lỗi pound (#) chẳng hạn 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 cách. Ví dụ, #VALUE! lỗi được gây ra theo định dạng không đúng hoặc kiểu dữ liệu không được hỗ trợ 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 các ô đã 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 đối với 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 bất kỳ chủ đề nào sau đây tương ứng với lỗi pound bạn thấy:

Mỗi lần bạn mở một bảng tính có chứa các công thức tham chiếu đến các 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 rời khỏi dạng xem đó.

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

Excel sẽ 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 trỏ đến giá trị được 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 điều đó, hãy đi đến tùy chọn tệp > > nâng cao > chungvà xóa hộp yêu cầu cập nhật các nối kết tự động .

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 tiên bạn làm việc với các nối kết bị hỏng trong công thức, nếu bạn cần bồi dưỡng về việc giải quyết các nối kết bị hỏng, hoặc nếu bạn không biết liệu để cập nhật tham chiếu, hãy xem điều khiển khi tham chiếu bên ngoài (liên kết) đượ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:

  • Hãy đảm bảo rằng Excel được đặt để hiển thị công thức trong bảng tính của bạn. Để thực hiện điều này, hãy chọn 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.

    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 điều này, các 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 thường, các cột của bạn sẽ đổi kích cỡ.

  • Nếu 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ó một cột có một phạm vi lớn các ô được định dạng dưới dạng văn bản, bạn có thể chọn phạm vi, áp dụng định dạng số của lựa chọn của bạn và đi đến dữ liệu > văn bản thành cột > kết thúc. 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 công thức không tính toán, bạn sẽ cần phải kiểm tra xem liệu tính năng tính toán tự động được bật trong Excel không. Công thức sẽ không tính toán nếu tính toán thủ công được bật. Hãy làm theo các bước sau đây để 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.

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

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

Tham chiếu vòng xảy ra khi một công thức đề cập đến ô mà nó được đặt trong đó. Bản sửa lỗi là di chuyển công thức sang một ô khác, hoặc thay đổi cú pháp công thức cho một trong đó 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 các trường hợp như vậy, bạn sẽ cần bật loại bỏ hoặc cho phép tham chiếu vòng.

Để biết thêm thông tin về các tham chiếu vòng, hãy xem mục 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. Ngoài ra, nếu bạn nhập 11/2, Excel sẽ hiển thị một ngày, chẳng hạn như 2-tháng mười một hoặc 11/02/2009, thay vì cách chia 11 bằng 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 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), "không hợp lệ", B5 * 1.05) sẽ không hoạt động vì có hai dấu ngoặc đơn đóng, nhưng chỉ có một dấu mở ngoặc. 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 đối số 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 đang nhập nó.

Ảnh chụp màn hình của 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 sai, 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 các số được định dạng với các dấu hiệu đô-la ($) hoặc dấu tách thập phân (,) trong công thức bởi các dấu hiệu đô-la cho biết tham chiếu tuyệt đối và dấu phẩy là đối số tách. Thay vì nhập $1,000, hãy nhập 1000 trong công thức.

Nếu bạn dùng số đị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ể 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à nó nhìn thấy các-2 và 134 là các đối số riêng biệt.

Lưu ý: 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ố). Nó thường không phải là một ý kiến hay để đặt hằng số trong công thức, vì chúng có thể khó tìm thấy nếu bạn cần cập nhật sau đó và chúng dễ bị nhập sai. Nó sẽ tốt hơn để đặt các hằng số của bạn trong các ô, nơi họ đang ở trong quá trình mở và dễ dàng 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 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 đầu và chọn mũi tên để bung rộng nhóm định dạngsố hoặc 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 (*).

Hộp thông báo yêu cầu thay x bằng * cho phép nhân

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

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

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 ngoặc kép để cung cấp khoảng trống bạn muốn giữa các từ "hôm nay là" và "thứ hai, ngày 30 tháng 5." Không có dấu ngoặc kép xung quanh văn bản, công thức có thể hiển thị #NAME? lỗi.

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!", "nhiều hơn hai!") có 3 cấp độ của hàm; hàm Pi được lồng vào trong hàm sqrt, trong đó lần lượt được lồng vào bên 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 là dữ liệu hàng quý trong sổ làm việc của bạn, nhập: = ' dữ liệu hàng quý '! D3. Nếu không có dấu ngoặc kép xung quanh tên trang tính, công thức sẽ hiển thị #NAME? lỗi.

Bạn cũng có thể chọn các giá trị hoặc ô trong một trang tính khác để tham chiếu đến chúng trong công thức của bạn. 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 các ô A1 thông qua A8 trên trang tính bán hàng trong sổ làm việc của Q2 Operations được mở trong Excel, hãy gõ: = [Q2 Operations.xlsx] doanh số! A1: A8. Nếu không có dấu ngoặc vuông, công thức sẽ hiển thị 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 để có được đườ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 = và dùng Alt + Tab để chuyển sang sổ làm việc khác. Chọn bất kỳ ô nào trên trang tính mà bạn muốn, rồi đó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 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.

Chia một ô bởi một ô khác có số không (0) hoặc không có kết quả giá trị trong 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 các ô, phạm vi, tên đã xác định, trang tính hoặc sổ làm việc trước khi bạn xóa bất kỳ điều 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 các ô đã bị xóa hoặc thay thế bằng dữ liệu khác, và nếu nó 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 bỏ nó. Sau đó nhập phạm vi cho công thức một lần nữa.

  • Nếu thiếu tên đã xác định, và một công thức tham chiếu đến tên đó trả về #NAME? lỗi, xác định tên mới tham chiếu đến phạm vi mà 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 bỏ, sau đó đảm bảo rằng dữ liệu này không bị mất bằng cách thay thế các công thức tham chiếu đến sổ làm việc bị thiếu với kết quả công thức.

Đôi khi, khi bạn sao chép nội dung của một ô, bạn muốn dán chỉ giá trị đó chứ không phải công thức cơ sở đượ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 một trang tính khác. Hoặc bạn có thể muốn xóa các giá trị mà bạn đã sử dụng trong một công thức sau khi sao chép giá trị kết quả sang một ô khác trên trang tính. Cả hai hành động này gây ra lỗi tham chiếu ô không hợp lệ (#REF!) để xuất hiện trong ô đích, bởi vì các ô có chứa các giá trị bạn đã sử dụng trong công thức không còn được tham chiếu.

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ó công thức, trong các ô đí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 đầu, trong nhóm bảng tạm , chọn sao chép Ả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 một vùng chọn đến một trang tính hoặc sổ làm việc khác, hãy chọn một 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 đầu, trong nhóm bảng tạm , chọn dán Hình ảnh nút, 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.

    Nhóm Kiểm nghiệm Công thức trên tab 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.

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

  4. Nếu phần gạch dưới của công thức là một tham chiếu đến một công thức khác, hãy chọn bước để hiển thị công thức khác trong hộp đánh giá . Chọn bước ra để quay lại ô và công thức trước đó.

    Nút bước vào không sẵn dùng lần thứ hai tham chiếu xuất hiện 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 nhất 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í. Đâ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.

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

    • Một số hàm được tính toán lại mỗi khi trang tính thay đổi. 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ể hỏi một chuyên gia trong Cộng đồng Kỹ thuật Excel hoặc nhận sự hỗ trợ trongCộng đồng trả lời.

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)

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

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 Microsoft dùng nội bộ

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

Bạn hài lòng đến đâu với chất lượng dịch thuật?

Điều gì ảnh hưởng đến trải nghiệm của bạn?

Bạn có góp ý gì thêm không? (Không bắt buộc)

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

×