Hướng dẫn này giải thích các lỗi phổ biến nhất khi tạo công thức trong Excel và cách sửa công thức không tính toán hoặc không cập nhật tự động.Sử dụng Microsoft Excel chắc chắn là có công thức, và đột ngột công thức excel không hoạt động. Bạn sẽ nghĩ công thức của bạn sao không tính toán? Tại sao công thức này không tự động cập nhật giá trị của nó? Excel bị hỏng hay do lỗi. Và làm cách nào để Excel của tự động tính toán và cập nhật công thức? Đừng lo lắng, tất cả các câu trả lời các vấn đề trên ngay lập tức dưới đây:
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.
Bạn đang xem: Không thực hiện được phép tính trong excel
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
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\
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ạitù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, đếnFile > 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 numbertrong 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.
Khi bạn nhập 1 công thức vào trong Excel mà Excel không nhận, chỉ ghi lại nội dung công thức mà không cho ra kết quả, hẳn điều này khiến bạn thật bối rối. Hãy vùng Học Excel Online tìm hiểu cách sửa lỗi không thực hiện được phép tính trong excel nhé.
Cách sửa lỗi Excel không nhận công thức
Có 2 trường hợp lỗi không thực hiện được phép tính trong excel này xảy ra:Thứ 1: Nhập công thức vào 1 ô đang định dạng Text
Trong hình trên chúng ta thấy ô A1 đang định dạng Text.
Khi nhập công thức tính tổng các ô từ C1 đến C5 thì không trả về kết quả tính tổng mà lại chỉ ghi lại nội dung của hàm SUM
Cách khắc phục:
Định đạng lại cho ô chứa công thức về dạng khác không phải dạng TextDouble click (bấm chuột 2 lần) vào công thức để vào chế độ sửa công thức, sau đó bấm Enter để cập nhật lại công thức. Khi đó Excel sẽ nhận công thức và trả về kết quả của công thức đóThứ 2: Excel đang ở chế độ chỉ hiển thị công thức
Đây là 1 chế độ của Excel, được thiết lập trong mục Options/Advanced/Show formulas in cells instead of their calculated results (phím tắt ctrl + `)
Ở chế độ này toàn bộ các công thức trong 1 sheet sẽ chỉ hiển thị nội dung công thức mà không hiển thị kết quả.
Khi bạn viết công thức thì cũng chỉ trả về nội dung công thức chứ không trả về kết quả.
Cách khắc phục:
Tắt tính năng này bằng cách bỏ dấu tích trong mục Show formulas in cells instead of their calculated resultsHoặc bạn có thể bấm phím tắt Ctrl + ` một lần nữa để tắt tính năng chỉ hiển thị công thức
Tại sao Excel lại có chế độ chỉ hiển thị công thức
Nhằm giúp người dùng sửa nội dung công thức được nhanh hơn, Excel cho phép đưa trạng thái Sheet về dạng chỉ hiển thị công thức để việc sửa công thức được nhanh hơn, dễ dàng hơn, tránh nhầm lẫn với các nội dung khác.Tác dụng thứ 2 là giúp bạn phát hiện ra những vị trí có công thức mà trả về kết quả rỗng, khiến bạn không nhận ra bằng mắt thường được.Xem thêm:
Như vậy đây là 1 tính năng trong Excel, khi chúng ta hiểu được điều này thì chúng ta hoàn toàn có thể tránh được các lỗi không thực hiện được phép tính trong excel.
Ngoài ra Học Excel Online xin giới thiệu với các bạn “Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm“. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để đáp ứng yêu cầu công việc tổ chức, quản lý dữ liệu và lập báo cáo trên Excel. Chi tiết khóa học xem tại: Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm