Có thể bạn quan tâm: Cách Tự Động Điều Chỉnh Kích Thước Ô Trong Excel Để Nội Dung Hiển Thị Đầy Đủ (fit Cell To Text)
Mở Đầu
Excel là một trong những công cụ mạnh mẽ nhất cho việc xử lý dữ liệu, tính toán và báo cáo. Tuy nhiên, không ít người dùng, dù là mới bắt đầu hay đã có kinh nghiệm, vẫn gặp phải hiện tượng “Excel formula does not calculate” – tức là công thức trong Excel không thực hiện tính toán như mong đợi. Điều này không chỉ gây phiền toái mà còn có thể dẫn đến sai sót nghiêm trọng trong báo cáo, quyết định kinh doanh. Bài viết dưới đây sẽ cung cấp một hướng dẫn chi tiết, toàn diện, giúp bạn hiểu rõ nguyên nhân gây ra vấn đề, cách phát hiện và khắc phục từng trường hợp một cách hiệu quả.
Lưu ý: Để bài viết đạt ít nhất 1500 chữ, chúng tôi sẽ đi sâu vào từng khía cạnh, kèm theo ví dụ thực tế, hình ảnh mô phỏng (miêu tả) và những lưu ý quan trọng. Bạn chỉ cần đọc và áp dụng các bước dưới đây, hầu hết các trường hợp “formula does not calculate” sẽ được giải quyết.
Có thể bạn quan tâm: Cách Tìm Ngày Đầu Tháng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Kiểm Tra Cài Đặt Cơ Bản Của Excel
1.1. Kiểm tra chế độ tính toán (Calculation Mode)
Excel có hai chế độ tính toán chính:
| Chế độ | Mô tả | Khi nào sử dụng |
|---|---|---|
| Automatic | Mỗi khi bạn thay đổi dữ liệu, Excel sẽ tự động tính lại mọi công thức. | Đa số người dùng, khi muốn cập nhật nhanh. |
| Manual | Excel chỉ tính lại khi bạn nhấn F9 hoặc Shift+F9. | Khi làm việc với bảng tính rất lớn, muốn kiểm soát thời gian tính. |
Cách kiểm tra và thay đổi:
- Vào File > Options (hoặc Office Button > Excel Options trên phiên bản cũ).
- Chọn Formulas.
- Trong mục Calculation options, chọn Automatic (hoặc Automatic except for data tables nếu bạn muốn ngoại trừ các bảng dữ liệu).
- Nhấn OK.
Nếu bạn đang ở chế độ Manual, mọi công thức sẽ “đóng băng” không tính toán, gây ra lỗi “formula does not calculate”.
1.2. Kiểm tra “Show Formulas” (Hiển thị công thức)
Đôi khi bạn vô tình bật tính năng Show Formulas (Ctrl + `). Khi bật, Excel sẽ hiển thị chuỗi công thức thay vì kết quả tính toán.
- Để tắt: Nhấn Ctrl + ` (dấu huyền ngược) hoặc vào Formulas > Show Formulas và bỏ tick.
1.3. Kiểm tra “Enable Iterative Calculation”
Trong một số trường hợp, công thức có tham chiếu vòng (circular reference). Nếu Iterative Calculation không được bật, Excel sẽ báo lỗi và không tính toán.
- Vào File > Options > Formulas → Tick Enable iterative calculation → Đặt Maximum Iterations và Maximum Change phù hợp (thông thường 100 và 0.001).
Có thể bạn quan tâm: Cách Tìm Kiếm Ô Có Giá Trị Cụ Thể Trong Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao
2. Kiểm Tra Định Dạng Ô (Cell Formatting)
2.1. Ô được định dạng dưới dạng Text (Văn bản)
Nếu ô được định dạng là Text, bất kỳ công thức nào bạn nhập vào sẽ được xem như chuỗi ký tự, không thực hiện tính toán.
Cách khắc phục:
- Chọn ô hoặc vùng ô gặp vấn đề.
- Vào Home > Number → Chọn General hoặc Number.
- Nhấn F2 rồi Enter để Excel nhận lại công thức dưới dạng công thức thực sự.
2.2. Dấu cách (Leading/Trailing Spaces)
Nếu có dấu cách thừa trước dấu =, Excel sẽ coi đó là chuỗi văn bản. Ví dụ: " =SUM(A1:A5)" sẽ không tính.
- Để loại bỏ: Sử dụng Find & Replace (Ctrl + H), tìm
" =và thay bằng"=".
2.3. Ký tự đặc biệt (Non‑printable characters)
Đôi khi dữ liệu được copy từ web hoặc nguồn khác mang theo ký tự không hiển thị (CHAR(160), CHAR(13), v.v.). Những ký tự này làm công thức không nhận dạng.
- Cách xử lý: Sử dụng CLEAN và TRIM để làm sạch dữ liệu:
=TRIM(CLEAN(A1)).
3. Kiểm Tra Cú Pháp (Syntax) Và Tham Chiếu Ô
3.1. Sai dấu ngoặc, dấu phẩy, dấu chấm phẩy
- Tiếng Anh (US): Dấu phẩy
,để ngăn cách các đối số. - Tiếng Việt (EU): Dấu chấm phẩy
;thường được dùng.
Nếu máy tính của bạn cài đặt ngôn ngữ EU nhưng bạn dùng dấu phẩy, công thức sẽ báo lỗi.
Ví dụ:
=SUM(A1:A5, B1:B5) // Dành cho US
=SUM(A1:A5; B1:B5) // Dành cho EU
3.2. Tham chiếu tới ô bị lỗi (#REF!)
Nếu công thức tham chiếu tới ô đã bị xóa hoặc di chuyển, bạn sẽ nhận được lỗi #REF!. Khi có lỗi này, toàn bộ công thức sẽ không tính.
- Cách khắc phục: Kiểm tra lại các tham chiếu, sử dụng Trace Precedents (Formulas > Trace Precedents) để xác định ô nguồn.
3.3. Circular Reference (Tham chiếu vòng)
Khi công thức trong ô A1 phụ thuộc vào ô B1, trong khi B1 lại phụ thuộc vào A1, Excel sẽ báo Circular Reference.
Kiểm tra: Excel thường hiển thị thông báo ở thanh trạng thái. Bạn có thể vào Formulas > Error Checking > Circular References để thấy vị trí.
Giải pháp: Thay đổi công thức để loại bỏ vòng lặp, hoặc bật Iterative Calculation (như mục 1.3).
4. Các Lỗi Thông Thường Khi Công Thức Không Tính
| Lỗi | Mô tả | Nguyên nhân thường gặp | Cách khắc phục |
|---|---|---|---|
| #DIV/0! | Phép chia cho 0 | Giá trị mẫu số bằng 0 hoặc rỗng | Kiểm tra dữ liệu, dùng IFERROR/IF để tránh. |
| #VALUE! | Kiểu dữ liệu không hợp lệ | Cố gắng tính toán trên văn bản, ngày tháng không hợp lệ | Kiểm tra kiểu dữ liệu, dùng VALUE, DATEVALUE. |
| #NAME? | Excel không nhận dạng tên hàm hoặc tên định danh | Sai tên hàm, thiếu dấu ngoặc, dùng ngôn ngữ sai | Kiểm tra chính tả, dùng Formula Auditing. |
| #N/A | Giá trị không khả dụng | VLOOKUP không tìm thấy | Dùng IFERROR, IFNA. |
| #NUM! | Số không hợp lệ (ví dụ: sqrt(-1)) | Tham số sai, quá lớn, quá nhỏ | Kiểm tra tham số, dùng ABS. |
| #NULL! | Tham chiếu không hợp lệ (khoảng cách) | Dùng dấu cách thay dấu phẩy trong tham chiếu | Đảm bảo dùng dấu phẩy hoặc chấm phẩy đúng. |
5. Các Công Thức Thông Dụng Và Kiểm Tra Lại
5.1. SUM, AVERAGE, COUNT
- SUM:
=SUM(A1:A10) Kiểm tra: Đảm bảo không có ô dạng Text. Nếu có, chuyển sang Number hoặc dùng
SUMPRODUCT(--(ISNUMBER(A1:A10)),A1:A10).AVERAGE:
=AVERAGE(B1:B20)Kiểm tra: Đối với ô trống, Excel tự bỏ qua. Tuy nhiên, nếu có lỗi #VALUE!, AVERAGE sẽ trả lỗi. Sử dụng
=AVERAGEIF(B1:B20,"<>#N/A").COUNT:
=COUNT(C1:C30)- Đếm số ô chứa số. Nếu muốn đếm cả văn bản, dùng
COUNTA.
5.2. VLOOKUP, HLOOKUP, INDEX‑MATCH
VLOOKUP không trả về kết quả
- Nguyên nhân thường gặp:
- Lookup value chứa dấu cách hoặc ký tự ẩn.
- Table array không có cột tìm kiếm ở cột đầu tiên.
- Range_lookup được đặt sai (TRUE/1 cho tìm gần đúng, FALSE/0 cho chính xác).
Ví dụ:
=VLOOKUP(TRIM(A2), $D$2:$G$100, 3, FALSE)
INDEX‑MATCH thay thế VLOOKUP
- INDEX: Trả về giá trị tại vị trí cụ thể.
- MATCH: Tìm vị trí của giá trị trong một mảng.
=INDEX($D$2:$D$100, MATCH(TRIM(A2), $C$2:$C$100, 0))
5.3. IF, IFERROR, IFNA
IFERROR(value, value_if_error)giúp che giấu lỗi và trả về giá trị dự phòng.IFNAchỉ bắt lỗi #N/A.
=IFERROR(VLOOKUP(A2, $D$2:$G$100, 3, FALSE), "Không tìm thấy")
6. Khi Dữ Liệu Được Nhập Từ Nguồn Ngoài (Import)
6.1. Dữ liệu CSV, TXT
- Khi mở file CSV, Excel tự động xác định kiểu dữ liệu. Nếu cột chứa số được định dạng là Text, các công thức sẽ không tính.
- Giải pháp: Khi nhập, sử dụng Text Import Wizard → Chọn định dạng cột là General hoặc Number.
6.2. Dữ liệu từ Power Query
Power Query có thể mang lại dữ liệu ở dạng Text dù thực chất là số. Khi chuyển dữ liệu vào bảng tính, cần:
Có thể bạn quan tâm: Excel Cho Quản Lý Chuỗi Cung Ứng: Công Cụ Đắc Lực Đưa Doanh Nghiệp Lên Tầm Cao Mới
- Vào Query Editor → Chọn cột → Transform > Data Type > Decimal Number.
- Hoặc sau khi tải về, dùng Data > Text to Columns để đổi kiểu.
6.3. Dữ liệu từ Web (Web Query)
Khi sao chép bảng từ web, đôi khi số được bao quanh bởi ký tự không nhìn thấy (non‑breaking space). Dùng CLEAN + SUBSTITUTE để loại bỏ:
=SUBSTITUTE(CLEAN(A2), CHAR(160), "")
7. Kiểm Tra và Sửa Lỗi Bằng Công Cụ “Error Checking”
Excel cung cấp công cụ Error Checking giúp nhanh chóng phát hiện lỗi trong công thức.
- Vào Formulas > Error Checking > Trace Error.
- Khi Excel phát hiện lỗi, sẽ hiển thị biểu tượng màu xanh lá hoặc đỏ ở góc ô. Nhấp vào để xem giải thích và lựa chọn Help on this error.
8. Khi Công Thức Được Sử Dụng Trong Bảng Pivot
Pivot Table có một số đặc điểm riêng:
- Calculated Field: Được tạo trong Pivot, không phải công thức ô bình thường. Nếu sử dụng hàm không được hỗ trợ (VD:
GETPIVOTDATAtrong Calculated Field), sẽ không tính. - Refresh: Khi dữ liệu nguồn thay đổi, Pivot không tự động cập nhật công thức. Nhấn Refresh (Alt + F5) hoặc bật Refresh data when opening the file.
9. Khi Sử Dụng Các Add‑ins, VBA hoặc Macro
9.1. Macro vô tình vô hiệu hoá tính toán
Nếu trong macro có dòng:
Application.Calculation = xlCalculationManual
thì toàn bộ workbook sẽ chuyển sang chế độ Manual cho đến khi macro đặt lại:
Application.Calculation = xlCalculationAutomatic
Kiểm tra: Mở Visual Basic Editor (Alt + F11) → Tìm Application.Calculation trong các module.
9.2. Add‑in gây xung đột
Một số add‑in (như Analysis ToolPak, Power Pivot) có thể can thiệp vào cách Excel xử lý công thức, đặc biệt khi chúng cài đặt các hàm tùy chỉnh.
- Thử disable tạm thời các add‑in: File > Options > Add‑Ins → Manage: COM Add‑ins → Go → Bỏ tick các add‑in không cần.
10. Kiểm Tra “Array Formulas” (Công Thức Mảng)
10.1. Cú pháp Cũ vs. Cú pháp Mới (Dynamic Arrays)
- Excel 365/2026 hỗ trợ Dynamic Array Formulas: Bạn chỉ cần nhập
=SORT(A1:A10)và Excel sẽ tự động “spill” kết quả ra các ô liền kề. - Phiên bản cũ yêu cầu Ctrl + Shift + Enter (CSE) để công thức mảng hoạt động.
Nếu bạn đang dùng phiên bản cũ nhưng nhập công thức mảng mà không nhấn CSE, công thức sẽ trả về một giá trị duy nhất (hoặc lỗi). Ngược lại, nếu bạn dùng CSE trong phiên bản mới, Excel sẽ hiển thị thông báo lỗi “You entered an array formula in a cell that already contains a spilled array”.
10.2. Kiểm tra “Spill Errors”
Khi một công thức mảng cố gắng “spill” vào ô đã có dữ liệu, Excel sẽ trả về #SPILL!. Để khắc phục:
- Xóa dữ liệu trong vùng “spill”.
- Hoặc sử dụng hàm
@để ép công thức trả về một giá trị duy nhất (trong Excel 365).
=@SUM(A1:A10) // Trả về một giá trị, không spill
11. Kiểm Tra “Conditional Formatting” Và “Data Validation”
11.1. Conditional Formatting
Nếu một ô được định dạng theo điều kiện để hiển thị màu hoặc biểu tượng, người dùng có thể nhầm lẫn rằng công thức không tính khi thực tế chỉ là hiển thị. Kiểm tra:
- Vào Home > Conditional Formatting > Manage Rules → Xem các quy tắc áp dụng cho ô.
11.2. Data Validation
Data Validation có thể ngăn người dùng nhập công thức nếu Allow được đặt thành Whole number, Decimal, List, v.v. Khi bạn cố gắng nhập công thức, Excel sẽ hiển thị thông báo lỗi và không cho công thức được lưu.
- Để sửa: Data > Data Validation > Clear All hoặc thay đổi Allow thành Any value.
12. Khi Sử Dụng Các Hàm Đặc Thù (Financial, Statistical, Engineering)
12.1. Hàm tài chính (NPV, IRR, XIRR)
- NPV yêu cầu rate và value1, value2, …. Nếu các giá trị được nhập dưới dạng Text, NPV sẽ trả lỗi. Dùng
VALUEđể chuyển.
=NPV(VALUE(A1), VALUE(B2:B10))
- IRR/XIRR yêu cầu ít nhất một giá trị dương và một giá trị âm; nếu không, hàm sẽ trả lỗi #NUM!.
12.2. Hàm thống kê (STDEV.P, VAR.S)
- Đảm bảo không có ô chứa lỗi (#DIV/0!, #VALUE!). Dùng
IFERRORhoặcAGGREGATEđể bỏ qua.
=STDEV.P(IF(ISNUMBER(A1:A100), A1:A100))
(được nhập dưới dạng công thức mảng trong phiên bản cũ).
12.3. Hàm kỹ thuật (LOG, EXP, POWER)
- Các hàm này không chấp nhận giá trị âm nếu không phù hợp (ví dụ LOG của số âm). Kiểm tra dữ liệu đầu vào.
13. Khi Excel Bị “Corrupt” Hoặc File Bị Hỏng
Nếu workbook bị hỏng, một số công thức có thể không tính được dù mọi thiết lập đều đúng.
13.1. Kiểm tra bằng “Open and Repair”
- File > Open → Chọn file → Click mũi tên “Open” → Chọn Open and Repair.
13.2. Sao chép dữ liệu sang workbook mới
- Tạo một workbook mới, sao chép Values và Formulas (Paste Special) để loại bỏ các tham chiếu hỏng.
14. Các Bước Kiểm Tra Tổng Hợp (Checklist)
| Bước | Kiểm tra | Hành động |
|---|---|---|
| 1 | Calculation Mode | Đặt thành Automatic. |
| 2 | Show Formulas | Tắt nếu bật. |
| 3 | Cell Format | Đảm bảo ô không ở dạng Text. |
| 4 | Dấu cách / ký tự ẩn | Dùng TRIM, CLEAN. |
| 5 | Dấu phân cách (, ; ) | Kiểm tra ngôn ngữ hệ thống. |
| 6 | Tham chiếu lỗi (#REF!) | Sửa lại hoặc dùng Trace Precedents. |
| 7 | Circular Reference | Sửa hoặc bật Iterative Calculation. |
| 8 | Error Checking | Sử dụng công cụ Excel. |
| 9 | Array Formulas | Nhấn Ctrl+Shift+Enter (nếu cần). |
| 10 | Add‑ins / VBA | Kiểm tra macro thay đổi chế độ tính. |
| 11 | Import Data | Đảm bảo kiểu dữ liệu đúng. |
| 12 | Refresh Pivot | Nhấn Refresh để cập nhật. |
| 13 | Conditional Formatting / Data Validation | Kiểm tra và tắt nếu gây cản trở. |
| 14 | File Corruption | Dùng Open and Repair hoặc sao chép sang file mới. |
15. Ví Dụ Thực Tế: Khắc Phục “Formula Does Not Calculate” Trong 5 Phút
Tình huống
Bạn có một file bán hàng, cột E chứa công thức tính Doanh thu = Đơn giá Số lượng. Khi mở file, cột E hiện toàn bộ là 0 hoặc =A2B2 (chuỗi). Bạn đã kiểm tra và không thấy lỗi nào.
Các bước thực hiện
- Kiểm tra chế độ tính toán
- Vào File > Options > Formulas → Đặt Automatic.
- Kiểm tra định dạng ô
- Chọn cột E → Home > Number > General.
- Xóa dấu cách thừa
- Ctrl + H → Find
" =→ Replace"=". - Kiểm tra ký tự ẩn
- Trong một ô trống, nhập
=LEN(E2)→ Nếu độ dài > công thức thực tế, có ký tự ẩn. Dùng=CLEAN(E2)và sao chép lại. - Kiểm tra công thức
- Nhấn F2 rồi Enter để Excel nhận lại công thức.
- Refresh (nếu có Pivot) → Alt + F5.
Sau 5 phút, cột E hiển thị đúng giá trị doanh thu.
16. Kết Luận
Hiện tượng “Excel formula does not calculate” không phải là hiện tượng hiếm gặp, nhưng với kiến thức và quy trình kiểm tra đúng đắn, bạn có thể nhanh chóng xác định và khắc phục. Bài viết đã liệt kê đầy đủ các nguyên nhân từ cấu hình cơ bản, định dạng ô, cú pháp công thức, dữ liệu nhập khẩu, cho tới các yếu tố phức tạp như VBA, Add‑ins và lỗi file.
Những điểm quan trọng cần ghi nhớ:
- Luôn để chế độ tính toán ở Automatic trừ khi thực sự cần Manual.
- Kiểm tra định dạng ô – Text là kẻ thù số.
- Xóa dấu cách và ký tự ẩn – một lỗi thường gặp khi sao chép công thức.
- Sử dụng công cụ Error Checking và Trace Precedents/Dependents để nhanh chóng xác định vị trí lỗi.
- Đối với dữ liệu nhập từ nguồn bên ngoài, luôn làm sạch và chuyển đổi kiểu dữ liệu trước khi dùng công thức.
Hy vọng qua bài viết này, bạn sẽ không còn bối rối khi gặp phải thông báo “formula does not calculate”. Hãy áp dụng các bước kiểm tra và khắc phục đã nêu, và bạn sẽ trở thành “bậc thầy” trong việc quản lý và tối ưu công thức Excel. Chúc bạn làm việc hiệu quả và tránh mọi sai sót trong các bảng tính quan trọng!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.