Công Thức Excel Không Hoạt Động, Không Cập Nhật, Không Tính ...

Công thức Excel không hoạt động

Hiện tượng : Công thức Excel không hoạt động chính xác, nó trả về lỗi hoặc kết quả sai.

Phần này cung cấp một bản tóm tắt các lỗi phổ biến nhất mà mọi người mắc phải khi tạo công thức trong Excel và các giải pháp để khắc phục chúng.

1. Ghép tất cả các dấu ngoặc đơn đóng và mở trong một công thức

Như bạn đã biết, các đối số của các hàm Excel được nhập trong ngoặc đơn. Trong các công thức phức tạp, bạn có thể cần phải nhập nhiều hơn một bộ dấu ngoặc, một trong các dấu ngoặc khác, để chỉ ra thứ tự sẽ diễn ra các phép tính. Khi tạo một công thức như vậy, hãy đảm bảo ghép đúng các dấu ngoặc đơn để bạn luôn có dấu ngoặc đơn bên phải cho mỗi dấu ngoặc trái trong công thức của bạn.

Microsoft Excel hiển thị các cặp dấu ngoặc đơn trong các màu khác nhau khi bạn nhập chúng vào một công thức. Nếu công thức của bạn thiếu một hoặc nhiều dấu ngoặc đơn, Excel sẽ hiển thị thông báo lỗi và đề xuất chỉnh sửa để cân bằng các cặp.

2. Nhập tất cả các đối số cần thiết trong một hàm Excel

Tất cả các hàm Excel có một hoặc nhiều đối số bắt buộc. Một số hàm cũng có các đối số tùy chọn, được đặt trong [dấu ngoặc vuông] trong cú pháp của công thức.

Công thức phải chứa tất cả các đối số được yêu cầu, nếu không, Excel sẽ hiển thị cảnh báo " Bạn đã nhập quá ít đối số cho hàm này ".

Nếu bạn đã nhập nhiều đối số hơn mức cho phép theo cú pháp của công thức, bạn sẽ nhận được thông báo lỗi " Bạn đã nhập quá nhiều đối số cho hàm này" .

3. Không lồng quá 64 hàm trong một công thức

Khi lồng hai hoặc nhiều hàm Excel vào nhau, ví dụ: tạo công thức IF lồng nhau , hãy nhớ về các giới hạn sau:

• Trong Excel 2016, Excel 2013, Excel 2010 và Excel 2007, bạn có thể sử dụng tối đa 64 hàm lồng nhau.
• Trong Excel 2003 trở xuống, chỉ có thể sử dụng tối đa 7 hàm lồng nhau.

4. Không bao gồm số trong dấu ngoặc kép

Trong các công thức Excel, bất kỳ giá trị nào được đặt trong dấu ngoặc kép đều được hiểu là một chuỗi văn bản.

Điều đó có nghĩa là nếu bạn nhập một công thức như thế nào =IF(A1>0, "1"), Excel sẽ coi số 1 là văn bản và do đó bạn sẽ không thể sử dụng số 1 được trả về trong các tính toán khác. Để khắc phục điều này, chỉ cần xóa dấu ngoặc kép quanh "1" : =IF(A1>0, 1).

Vì vậy, bất cứ khi nào bạn đang viết một công thức cho các giá trị số, hãy làm theo quy tắc đơn giản này: không bao gồm các số trong dấu ngoặc kép trừ khi bạn muốn chúng được coi là text (văn bản).

5. Nhập số mà không có bất kỳ định dạng.

Khi sử dụng số trong công thức Excel, không thêm bất kỳ dấu tách thập phân hoặc ký hiệu tiền tệ nào như $ hoặc €.

Hãy nhớ rằng trong các công thức Excel, dấu phẩy thường được sử dụng để phân tách các đối số của hàm và ký hiệu đô la làm tham chiếu ô tuyệt đối .

Chẳng hạn, thay vì nhập $50.000 trong công thức của bạn, chỉ cần nhập 50000 và sử dụng hộp thoại Định dạng ô (Ctrl + 1) để định dạng đầu ra theo ý thích của bạn.

6. Đảm bảo các số không được định dạng dưới dạng giá trị văn bản

Các số được định dạng dưới dạng giá trị văn bản là một lý do phổ biến khác khiến các công thức Excel không hoạt động. Chúng trông giống như những con số bình thường, nhưng Microsoft Excel coi chúng là các chuỗi văn bản và không tính toán.

Các chỉ số trực quan của số văn bản như sau:

• Các số được định dạng dưới dạng văn bản được căn trái theo mặc định, trong khi các số bình thường được căn phải trong các ô.
• Các Số Format hộp trên Trang chủ tab trong hiển thị nhóm Số của dạng chữ.
• Khi một số ô có số văn bản được chọn trên trang tính, Thanh trạng thái chỉ hiển thị Đếm , trong khi thông thường nó hiển thị Trung bình , Đếm và SUM cho các số.
• Có thể có dấu nháy đơn hàng đầu có thể nhìn thấy trong thanh công thức hoặc hình tam giác màu xanh lá cây xuất hiện ở góc trên cùng bên trái của các ô.

Ảnh dưới cho thấy ngay cả một công thức Excel SUM đơn giản cũng có thể không hoạt động do các số được định dạng dưới dạng văn bản:

Để khắc phục điều này, chọn tất cả các ô có vấn đề, bấm vào dấu hiệu cảnh báo, sau đó bấm Chuyển đổi thành Số:

Tuy nhiên, trong một số trường hợp, cả hình tam giác màu xanh lá cây và dấu hiệu cảnh báo đều không xuất hiện trong các ô. Ví dụ: nếu bạn đặt các giá trị số trong dấu ngoặc kép trong công thức của mình, Excel sẽ giả sử bạn muốn xuất một chuỗi văn bản thay vì số.

Ngay từ cái nhìn đầu tiên, công thức sau đây có vẻ hoạt động tốt: =IF(A1="Good", "1", "0")

Nhưng vấn đề là các giá trị 1 và 0 được trả về là các giá trị văn bản , không phải số! Và nếu bạn tham chiếu bất kỳ ô nào có công thức trên trong các công thức khác, các ô đó sẽ không được đưa vào tính toán. Ngay khi bạn xóa dấu nháy "" xung quanh 1 và 0 trong công thức trên, Excel sẽ coi các đầu ra là số và chúng sẽ được tính toán chính xác.

Nếu một số hình tam giác nhỏ màu xanh lá cây không xuất hiện trong các ô vì một số lý do khác, hãy xem hộp Định dạng số trên tab Trang chủ trong nhóm Number . Nếu nó hiển thị Text , hãy thử xóa tất cả định dạng cho các ô có vấn đề và đặt định dạng của các ô thành Number hoặc General . Nếu điều đó không hiệu quả, bạn có thể phải tạo một cột mới, nhập dữ liệu theo cách thủ công (ví dụ: sao chép số văn bản của bạn vào Notepad, sau đó quay lại cột mới) và xóa cột bị hỏng.

Một giải pháp khả thi khác là nhân các giá trị trong cột có vấn đề với 1 bằng cách sử dụng một công thức đơn giản như thế nào =A1*1. Và sau đó, sao chép các ô công thức và dán chúng dưới dạng các giá trị trong cùng hoặc trong bất kỳ cột nào khác thông qua Past expecial > Giá Trị (Dán Đặc biệt > Giá trị ).

7. Phân tách các đối số chức năng với một ký tự phù hợp

Hầu hết chúng ta được sử dụng để phân tách các đối số chức năng bằng dấu phẩy. Tuy nhiên, điều này không hoạt động đối với tất cả Excel. Ký tự bạn sử dụng để phân tách các đối số tùy thuộc vào Bộ tách danh sách được đặt trong Cài đặt khu vực của bạn .

Dấu phẩy là dấu phân cách danh sách mặc định ở Bắc Mỹ và một số quốc gia khác. Ở các nước châu Âu, dấu phẩy được sử dụng làm ký hiệu thập phân và dấu phân cách danh sách thường được đặt thành dấu chấm phẩy .

Ví dụ: ở Bắc Mỹ bạn sẽ viết =IF(A1>0, "OK", "Not OK"), trong khi người dùng Excel ở Châu Âu nên đặt công thức giống như =IF(A1>0; "OK"; "Not OK").

Vì vậy, nếu công thức Excel của bạn không hoạt động do lỗi " Chúng tôi đã gặp sự cố với công thức này ..." , hãy truy cập Cài đặt khu vực ( Bảng điều khiển > Vùng và ngôn ngữ > Cài đặt bổ sung ) và kiểm tra ký tự nào được đặt làm Dấu tách danh sách ở đó. Và sau đó, sử dụng chính xác ký tự đó để phân tách các đối số trong công thức Excel của bạn.

8. Gửi tên bảng tính và bảng tính trong dấu ngoặc đơn

Khi đề cập đến các bảng tính hoặc sổ làm việc khác có khoảng trắng hoặc ký tự không theo thứ tự chữ cái trong tên của chúng, hãy đặt tên trong 'dấu ngoặc kép đơn'. Ví dụ:

• Tham chiếu đến một tờ khác: =SUM('Jan Sales'!B2:B10)
• Tham khảo một sổ làm việc khác: =SUM('[2015 Sales.xlsx]Jan sales'!B2:B10)

9. Bao gồm đường dẫn đầy đủ đến một sổ làm việc kín

Nếu bạn đang viết một công thức tham chiếu sổ làm việc Excel đã đóng, tham chiếu bên ngoài của bạn phải bao gồm tên sổ làm việc và toàn bộ đường dẫn đến sổ làm việc.

Ví dụ: =SUM('D:\Reports\[Sales.xlsx]Jan'!B2:B10)

Nếu các mẹo trên không có ích, hãy thử đánh giá và gỡ lỗi từng phần trong công thức của bạn bằng cách sử dụng khóa F9.

Công thức Excel không cập nhật

Dấu hiệu : Giá trị trả về bởi công thức Excel của bạn không cập nhật tự động, tức là công thức tiếp tục cho thấy những giá trị cũ ngay cả sau khi bạn đã thay đổi các giá trị của các tế bào phụ thuộc.

Khi các công thức Excel không tự động cập nhật, rất có thể là do cài đặt Tính toán đã được thay đổi thành Thủ công thay vì Tự động . Để khắc phục điều này, chỉ cần đặt lại tùy chọn Tính toán thành Tự động .

Trên Ribbon, đến tab Formulas > Calculation Options và chọn Automatic.

Ngoài ra, bạn có thể thay đổi cài đặt này trong Tùy chọn Excel :

• Trong Excel 2003, click Tools > Options > Calculation > Calculation > Automatic.
• Trên Excel 2007, click Office button > Excel options > Formulas > Workbook Calculation > Automatic.
• Trên Excel 2010, Excel 2013, và Excel 2016, đến File > Options > Formulas > Calculation options , và chọn Automatic trong cửa sổ làm việc.

Cách buộc công thức Excel tính toán lại

Nếu vì lý do nào đó, bạn cần đặt tùy chọn Tính toán thành Thủ công , bạn có thể buộc các công thức tính toán lại bằng cách nhấp vào nút Tính toán trên ribbon hoặc bằng một trong các phím tắt sau:

Để tính toán lại toàn bộ bảng tính:

• Nhấn F9 hoặc nhấn vào Formulas > Calculate Now

Để tính toán lại một bảng hoạt động:

• Nhấn Shift + F9 hoặc nhấn vào Formulas > Calculate Sheet.

Để tính toán lại tất cả các trang tính trong tất cả các sổ làm việc đang mở , hãy nhấn Ctrl + Alt + F9.

Nếu bạn chỉ cần tính toán lại một công thức trên một trang tính, hãy chọn ô công thức, nhập chế độ chỉnh sửa bằng cách nhấn F2 hoặc bấm đúp vào ô, rồi bấm phím Enter.

Công thức Excel không tính toán

Dấu hiệu: Một ô hiển thị công thức, không phải kết quả.

Nếu công thức Excel của bạn không hoạt động vì một ô hiển thị hàm thay vì giá trị được tính, thì đó là vì một trong những lý do sau.

1. Hiển thị chế độ Công thức được bật

Lý do phổ biến nhất khiến công thức Excel không tính toán là bạn đã vô tình kích hoạt chế độ Hiển thị Công thức trong bảng tính.

Để có được công thức hiển thị kết quả được tính toán, chỉ cần tắt chế độ Hiển thị Công thức bằng cách thực hiện một trong các thao tác sau:

Nhấn Ctrl + ` phím tắt, hoặc nhấn vào Formulas > Show Formulas.

2. Một công thức được nhập dưới dạng văn bản

Một lý do thường gặp khác khiến công thức Excel của bạn không tính toán là công thức đã được định dạng dưới dạng text. Để kiểm tra điều này, hãy chọn ô công thức và xem hộp Định dạng number trong nhóm Số trên tab Trang chủ:

Nếu là text, hãy thay đổi định dạng ô thành General và nhấn Enter để công thức tính toán lại và hiển thị giá trị được tính.

3. Một ô công thức có dấu cách hàng đầu hoặc dấu nháy đơn trước dấu bằng

Nếu bạn vô tình nhập dấu cách hoặc dấu nháy đơn (') trước dấu bằng, Excel sẽ coi nội dung ô là văn bản và do đó không đánh giá bất kỳ công thức nào trong ô đó (một khoảng trắng hàng đầu thường xuất hiện khi bạn sao chép công thức từ web) . Để khắc phục điều này, chỉ cần xóa bỏ các khoảng đầu dòng như dấu (') là xong.

Đây là cách bạn xử lý các công thức không hoạt động trong Excel. Nếu bạn biết bất kỳ giải pháp nào khác để khắc phục các công thức không cập nhật hoặc không tính toán, vui lòng chia sẻ trong các bình luận.