Table of Contents
Công thức Excel không hoạt động
Hiện tượng : Công thức Excel không hoạt động đúng đắn, nó trả về lỗi hoặc kết quả sai.
Phần này phân phối một bản tóm tắt các lỗi thông dụng nhất mà mọi người mắc phải khi tạo công thức trong Excel và các phương án để khắc phục chúng.
1. Ghép toàn bộ 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, để nêu 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ông báo lỗi và đề xuất chỉnh sửa để thăng bằng các cặp.
2. Nhập toàn bộ các đối số thiết yếu trong một hàm Excel
Toàn bộ 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 toàn bộ các đối số được yêu cầu, nếu không, Excel sẽ hiển thị nhắc nhở ” 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ẽ thu đượ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 hạn chế 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ư vậy nào =IF(A1andgt;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(A1andgt;0, 1).
Vì vậy, bất kì 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 .
Ví dụ, 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 nguyên nhân thông dụng 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 toàn bộ các ô có vấn đề, nhấp vào dấu hiệu nhắc nhở, sau đó bấm Chuyển hóa 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 nhắc nhở đề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 trước nhất, 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 mang vào tính toán. Ngay khi bạn xóa dấu nháy “” xung quanh 1 và 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 đúng đắn.
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ố nguyên nhân 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 toàn bộ đị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 phương án 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ư vậy 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ố tính năng với một ký tự thích hợp
Hầu hết tất cả chúng ta được sử dụng để phân tách các đối số tính năng bằng dấu phẩy. Tuy nhiên, điều này không hoạt động so với toàn bộ 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 Setup 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(A1andgt;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(A1andgt;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 Setup khu vực ( Bảng điều khiển > Vùng và ngôn ngữ > Setup bổ sung ) và xác minh ký tự nào được đặt làm Dấu tách danh sách ở đó. Và sau đó, sử dụng đúng đắn 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 theo 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ận xét 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 update
Dấu hiệu : Giá trị trả về bởi công thức Excel của bạn không update tự động, tức là công thức tiếp tục cho thấy những giá trị cũ ngay cả sau thời điểm 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 update, rất có thể là do seting 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 seting 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ì nguyên nhân 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 > https://vuongquocdongu.com/wp-content/uploads/2021/10/calculate20now.html
Để tính toán lại một bảng hoạt động:
- Nhấn Shift + F9 hoặc nhấn vào Formulas > https://vuongquocdongu.com/wp-content/uploads/2021/10/calculate20sheet.html.
Để tính toán lại toàn bộ các trang tính trong toàn bộ 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ính sá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 nguyên nhân sau.
1. Hiển thị chính sách Công thức được bật
Nguyên nhân thông dụng 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ính sá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ính sá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 nguyên nhân 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. Để xác minh đ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ận xét 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ừ website) . Để 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ỳ phương án nào khác để khắc phục các công thức không update hoặc không tính toán, vui lòng chia sẻ trong các phản hồi.
