Sửa Lỗi & Giải Pháp 5/2022

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 tóm tắt về những lỗi phổ biến nhất mà mọi người thường 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.

Bạn Đang Xem: Sửa Lỗi & Giải Pháp 5/2022

1. Nối tất cả các dấu ngoặc mở và đóng ngoặc 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 dấu ngoặc đơn. Trong các công thức phức tạp, bạn có thể cần nhập nhiều hơn một tập hợp các dấu ngoặc, một trong các dấu ngoặc khác, để chỉ ra thứ tự thực hiện 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 nối các dấu ngoặc đơn đúng cách để bạn luôn có một dấu ngoặc đơn bên phải cho mỗi dấu ngoặc đơn bên trái trong công thức của mình.

Microsoft Excel hiển thị các cặp dấu ngoặc đơn bằng các màu khác nhau khi bạn nhập chúng vào 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 sửa chữa để cân bằng các cặp.

2. Nhập tất cả các đối số bắt buộc 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ố bắt buộc, 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 của cú pháp 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 nhiều hơn 64 hàm trong một công thức

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

• Trong Excel 2016, Excel 2013, Excel 2010 và Excel 2007bạn có thể sử dụng đến 64 hàm lồng nhau.

Xem Thêm : Video Hướng Dẫn Các Thành Phần Và Tùy Chỉnh Giao Diện Trong Excel 5/2022

• Trong Excel 2003 trở xuống, chỉ có thể được sử dụng đến 7 chức năng lồng nhau.

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

Trong công thức Excel, bất kỳ giá trị nào được đặt trong dấu ngoặc kép đượ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ư = 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 phép tính khác. Để khắc phục điều này, chỉ cần xóa các dấu ngoặc kép xung quanh “Đầu tiên” : = IF (A1> 0, 1).

Vì vậy, bất cứ khi nào bạn viết công thức cho các giá trị số, hãy làm theo quy tắc đơn giản sau: 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ần bất kỳ định dạng nào.

Khi sử dụng số trong công thức Excel, không thêm bất kỳ dấu phân 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ông thức Excel, dấu phẩy thường được sử dụng để tách các đối số của hàm và dấu đô la làm tham chiếu ô tuyệt đối.

Ví dụ: thay vì nhập 50.000 đô la 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 ý muốn 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ông thức Excel không hoạt động. Chúng trông giống như các số bình thường, nhưng Microsoft Excel coi chúng như các chuỗi văn bản và không tính toán chúng.

Các chỉ báo trực quan của số lượng 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 ô.

Xem Thêm : Video Hướng Dẫn Các Thành Phần Và Tùy Chỉnh Giao Diện Trong Excel 5/2022

• Hộp Định dạng Số trên tab Trang đầu trong nhóm Số hiển thị định dạng văn bản.

Xem Thêm : Video Hướng Dẫn Các Thành Phần Và Tùy Chỉnh Giao Diện Trong Excel 5/2022

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

Xem Thêm : Video Hướng Dẫn Các Thành Phần Và Tùy Chỉnh Giao Diện Trong Excel 5/2022

• Có thể có dấu nháy đơn ở đầu hiển thị trong thanh công thức hoặc hình tam giác màu xanh lục xuất hiện ở góc trên cùng bên trái của ô.

Hình ảnh bên dưới hiển thị ngay cả một công thức Excel TÓM LƯỢC simple cũng có thể không hoạt động vì các số được định dạng dưới dạng văn bản:

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

Tuy nhiên, trong một số trường hợp, cả 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ì một số.

Thoạt nhìn, công thức sau có vẻ hoạt động tốt:

= IF (A1 = “Tốt”, “1”, “0”)

Nhưng vấn đề là các giá trị Đầu tiên và 0 được trả về dưới dạng giá trị tài liệu , 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 phép tính. Ngay sau khi bạn xóa dấu nháy đơn “” vòng quanh Đầu tiên và 0 trong công thức trên, Excel sẽ coi các kết quả đầ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 không xuất hiện trong ô vì một số lý do khác, hãy xem hộp Định dạng số bên trên Tab trang chủ trong nhóm Con số . Nếu nó hiển thị Bản văn 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 Số hoặc Chung . Nếu cách đó 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ông thức đơn giản như = 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 qua Quá khứ đặc biệt> Giá trị (Dán Đặc biệt> Giá trị).

7. Tách các đối số hàm bằng một ký tự phù hợp

Hầu hết chúng ta đã quen với việc tách các đối số của hàm bằng cách dấu phẩy. Tuy nhiên, điều này không hoạt động cho tất cả Excel. Ký tự bạn sử dụng để phân tách các đối số phụ thuộc vào Danh sách tách đặt trong Thiết lập khu vực của bạn .

Dấu phẩy là dấu phân cách danh sách mặc định tại 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”, “Không ổn”)trong khi người dùng Excel ở Châu Âu nên đặt công thức như = IF (A1> 0; “OK”; “Không ổn”).

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 …” đi tới Cài đặt khu vực (Bảng điều khiển> Khu vực và ngôn ngữ> Cài đặt bổ sung) và kiểm tra xem ký tự nào được đặt làm Dấu phân tách danh sách ở đó. Và sau đó, sử dụng chính xác ký tự đó để tách các đối số trong công thức Excel của bạn.

8. Đặt tên sổ làm việc và sổ làm việc trong dấu ngoặc đơn

Khi đề cập đến các trang tính hoặc sổ làm việc khác có dấu cách hoặc ký tự không phải bảng chữ cái trong tên của chúng, hãy đặt tên vào ‘dấu nháy đơn’. Ví dụ:

• Tham chiếu đến trang tính khác: = SUM (‘Doanh số tháng 1’! B2: B10)

Xem Thêm : Video Hướng Dẫn Các Thành Phần Và Tùy Chỉnh Giao Diện Trong Excel 5/2022

• Tham khảo sổ làm việc khác: = SUM (‘[2015 Sales.xlsx]Doanh số tháng 1 ‘! B2: B10)

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

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

Ví dụ: = SUM (‘D: \ Báo cáo \[Sales.xlsx]Tháng 1 ‘! B2: B10)

Nếu các mẹo trên không hữu í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 một khóa F9.

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

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

Xem Thêm : Hướng dẫn cách tính tỷ lệ phần trăm trong Excel kèm ví dụ

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 Ruy-băng, chuyển đến F. chuyển hướngcông thức> Tùy chọn tính toán và chọn Tự động.

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

• Trong Excel 2003, bấm Công cụ> Tùy chọn> Tính toán> Tính toán> Tự động.

Xem Thêm : Video Hướng Dẫn Các Thành Phần Và Tùy Chỉnh Giao Diện Trong Excel 5/2022

• Trên Excel 2007, bấm Nút Office> Tùy chọn Excel> Công thức> Tính toán Sổ làm việc> Tự động.

Xem Thêm : Video Hướng Dẫn Các Thành Phần Và Tùy Chỉnh Giao Diện Trong Excel 5/2022

• Trên Excel 2010, Excel 2013 và Excel 2016, để Tệp> Tùy chọn> Công thức> Tùy chọn tính toán, và chọn Tự động trong không gian làm việc.

Cách buộc các công thức Excel phải 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 ruy-băng hoặc bằng một trong các phím tắt sau:

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

• Nhấn F9 hoặc bấm vào Công thức> Tính toán ngay bây giờ

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

• Nhấn Shift + F9 hoặc chạm Công thức> Tính toán Trang tính.

Để 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 bấm 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, vào chế độ chỉnh sửa bằng cách nhấn F2 hoặc bấm đúp vào ô, sau đó nhấn 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 hiển thị 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ị một hàm thay vì một giá trị được tính toán, đó 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 được là bạn đã vô tình bật chế độ Hiển thị công thức trong bảng tính.

Để 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 chạm Công thức> Hiển thị công thức.

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

Một lý do phổ biến khác khiến công thức Excel của bạn không tính được là công thức đã được định dạng là bản văn. Để kiểm tra điều này, hãy chọn ô công thức và nhìn vào hộp Định dạng con số trong nhóm Số trên tab Trang đầu:

Nếu bản vănthay đổi định dạng ô thành Chung và hãy nhấn đi vào để công thức tính toán lại và hiển thị giá trị đã tính.

3. Ô công thức có khoảng trắng ở đầu hoặc dấu nháy đơn trước dấu bằng

Nếu bạn vô tình vào không gian hoặc dấu nháy đơn (‘) trước dấu bằng, Excel 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 ô đó (khoảng trố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 loại bỏ các dấu đầu dòng như (‘) 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 để sửa chữa 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 phần bình luận.