Nguyên nhân và cách khắc phục Lỗi công thức trong EXCEL

Có đôi khi Excel sẽ không thể thực hiện công thức của bạn, đó là do trong công thức của bạn đang có lỗi.

Sau đây bài viết của Trungtamtinhoc.edu.vn sẽ giới thiệu một số lỗi cơ bản thường gặp và cách khắc phục chúng khi bạn nhận được thông báo lỗi công thức.

Lỗi #

Phần lớn các lỗi do công thức thường nằm ở mục lỗi này. Gồm các lỗi như #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, và #NULL!

Trungtamtinhoc.edu.vn đã có tất cả các bài viết về các lỗi này cũng như cách khắc phục chúng. Mời bạn xem thêm các bài viết về các lỗi này của Trungtamtinhoc.edu.vn

Lưu ý: #### không phải là lỗi liên quan đến công thức. Chuỗi ký tự này chỉ có nghĩa là cột không đủ rộng để hiện thị nội dung trong ô. Chỉ cần kéo cột để mở rộng là được.

Công thức không thực hiện, tính toán

Khi một công thức không được tính. Bạn phải kiểm tra lại xem tính năng tính tự động đã được bật chưa.

Vào File chọn Options chọn Formulas, tại mục Calculation options tích vào ô Automatic

Thiếu dấu “=” trong công thức

Đây là một lỗi phổ biến. Ví dụ bạn nhập SUM(A1:A2) và nhấn ENTER thì đây chưa phải là công thức vì thế EXCEL sẽ không thể thực hiện được phép tính. Bạn phải nhập dấu “=” trước mỗi công thức: =SUM(A1:A2)

Các dấu ngoặc không khớp nhau

Đây là một lỗi khá phổ biến khi sử dụng các hàm lồng nhau nhiều. Các dấu ngoặc “( )” cần khớp với nhau, ví dụ: =IF(B5<0),”Not valid”,B5*1.05) không chính xác vì có hai dấu ngoặc đóng nhưng chỉ có một dấu ngoặc mở. Công thức chính xác sẽ là: =IF(B5<0,”Not valid”,B5*1.05).

Thiếu tham đối trong cú pháp

Hầu hết các hàm đều yêu cầu có tham đối để thực hiện tính toán. Tham đối là phần nằm trong dấu ngoặc đơn sau mỗi công thức. Có một số công thức không cần tham đối như hàm IP hoặc TODAY công thức sẽ là: =TODAY()

Còn các hàm còn lại hầu như đều cần đến tham đối theo sau, ví dụ: =SUM(A1:A2)

Lưu ý: Các tham đối sẽ được gợi ý khi bạn tiến hành nhập công thức ngay bên dưới.

Ô chứa công thức có định dạng dữ liệu là Text?

Nếu ô chứa công thức có định dạng dữ liệu là Text thì công thức bạn nhập vào sẽ được hiển thị y hệt trong ô đó chứ không trả về kết quả, ví dụ: nhập công thức =1+2 vào ô có định dạng dữ liệu là Text thì kết quả sẽ trả về là =1+2.

Để khắc phục bạn chỉ cần thay đổi định dạng dữ liệu của ô thành General hoặc Number.

Dùng dấu “x” để thực hiện phép nhân?

Trong EXCEL khi thực hiện phép nhân bạn phải dùng dấu “*” chứ không phải dấu “x”, ví dụ khi muốn thực hiện phép 1×2 ta gõ công thức: =1*2

Văn bản trong công thức không được đặt trong dấu ngoặc kép “”

Trong công thức nếu có chứa văn bản bạn cần phải đặt chúng vào bên trong dấu ngoặc kép “”, ví dụ: =”hôm nay là ngày ” & TEXT(TODAY(),”dd/mm”) kết hợp với văn bản “Hôm nay là ngày”, cùng kết quả của các hàm TEXT và TODAY sẽ trả về các kết quả là Hôm nay là ngày 23/12

Có hơn 64 cấp độ hàm trong một công thức?

Bạn có thể kết hợp (hay lồng) tối đa 64 cấp độ hàm trong một công thức.

Ví dụ: công thức =IF(SQRT(PI())<2,”Nhỏ hơn hai!”,”Lớn hơn hai!”) có 3 cấp độ hàm: Hàm PI nằm trong hàm SQRT, kết quả là nằm trong hàm IF.

Tên trang tính mà công thức tham chiếu đến thiếu dấu nháy đơn ‘ ‘

Khi bạn nhập một tham chiếu đến giá trị hay ô trong trang tính khác, và tên của trang này có một ký tự không thuộc bảng chữ cái (như khoảng trắng), hãy đặt tên đó trong dấu nháy đơn (‘).

Ví dụ, để trả về giá trị từ ô D3 trong trang tính có tên là dữ liệu hàng quý trong sổ làm việc của bạn, nhập: = ‘ dữ liệu hàng quý ‘! D3. Nếu không có dấu ngoặc kép xung quanh tên trang tính, công thức sẽ hiển thị lỗi #NAME?

Đường dẫn tham chiếu đến của sổ làm việc khác có chính xác không?

Khi bạn nhập tham chiếu đến giá trị hay ô trong một cửa sổ làm việc khác, hãy đưa tên cửa sổ làm việc nằm trong hai dấu ngoặc vuông ([ ]) và tiếp theo sau là tên của trang tính mà có giá trị hay ô đó.

Ví dụ, để tham chiếu đến các ô A1 thông qua A8 trên trang tính bán hàng trong cửa sổ làm việc của Q2 Operations được mở trong Excel, hãy gõ: = [Q2 Operations. xlsx] doanhso! A1:A8. Nếu không có dấu ngoặc vuông, công thức sẽ hiển thị lỗi #REF!.

Nếu sổ làm việc không mở trong EXCEL, hãy nhập đường dẫn đầy đủ đến tệp đó.

Ví dụ, =ROWS(‘C:\My Documents\[Hoạt động kinh doanh Q2.xlsx] doanhso!A1:A8)

Công thức tham chiếu đến các giá trị đã bị xóa

Nếu công thức của bạn là =[Book1.xlsx]Sheet1′!A1 và bạn không còn Book1.xlsx nữa, thì những giá trị được tham chiếu trong sổ làm việc đó vẫn còn đó. Tuy nhiên, nếu bạn sửa rồi sau đó lưu công thức mà có tham chiếu đến sổ làm việc đó, Excel sẽ hiện hộp thoại Cập nhật Giá trị và nhắc bạn nhập tên tệp. Nếu bạn muốn giữ các giá trị đã tính toán thì bạn bấm Hủy và bạn cần thay thế thủ công các giá trị tham chiếu đến cửa sổ đang thiếu bằng kết quả tham chiếu.

Coppy và dán các công thức có chứa liên kết

Nếu bạn muốn chỉ muốn coppy giá trị của ô này sang ô khác nhưng bạn chọn lên coppy và paste thông thường thì EXCEL sẽ hiểu là bạn đang coppy cả công thức trong ô đó. Do đó khi coppy sang ô mới thì các giá trị tham chiếu có thể bị thay đổi theo, dẫn đến công thức không phù hợp và có thể vi phạm các lỗi về công thức như trên.

Để chỉ coppy và paste giá trị ta dùng lệnh paste như sau: thực hiện lệnh coppy Ctril + C như thông thường, sau đó di chuyển đến ô cần paste, click chuột phải đến mục Paste options: chọn dán kiểu Value.

Đánh giá cẩn thận các công thức có hàm lồng nhau

Các công thức có nhiều hàm lồng vào nhau đôi khi gây nhầm lẫn. Cần đánh giá kỹ các công thức này để tránh công thức bị lỗi.

Hy vọng những giới thiệu trên của Trungtamtinhoc.edu.vn sẽ giúp bạn đọc trong quá trình thực hành EXCEL.

Minh An – trungtamtinhoc.edu.vn