Lỗi Excel thường gặp
Mã lỗi hiện ngay trong ô và thông báo bật ra khi đang làm việc. Mỗi mục nói nguyên nhân hay gặp trước, cách chữa sau.
Thử:
Lỗi hiện ngay trong ô
- #N/APhổ biến
Không tìm thấy. Hàm dò (VLOOKUP/XLOOKUP/MATCH) không thấy giá trị cần tìm. Nguyên nhân hay gặp theo thứ tự: một bên là chữ một bên là số · dư dấu cách (bọc TRIM) · quên đối số cuối = 0 nên nó dò gần đúng · vùng dò không chứa cột chứa giá trị tìm. Muốn giấu thì bọc IFNA, đừng bọc IFERROR.
Dò đơn giá từ bảng giá sang bảng bán hàng mà mã hàng bên này là chữ, bên kia là số, hoặc dư dấu cách khi copy từ phần mềm
=IFNA(VLOOKUP(TRIM(B2)&"",Gia!$A:$D,4,0),"Chưa có giá") - #VALUE!Phổ biến
Sai kiểu dữ liệu: đang bắt Excel làm toán trên chữ. Thường do ô trông giống số nhưng thật ra là chữ, hoặc trong vùng tính có ô chứa chữ. Dò bằng =ISTEXT(ô); chữa bằng Text to Columns hoặc VALUE.
Cộng cột số tiền vừa tải từ phần mềm kế toán ra, trong cột lẫn vài ô ghi chú bằng chữ, thế là phép nhân đơn giá mã NPK-045 chết đứng
Bôi cột số tiền → Data → Text to Columns → Finish - #REF!Phổ biến
Tham chiếu chết — ô mà công thức trỏ tới đã bị xoá. Thường xảy ra khi xoá dòng/cột hoặc xoá sheet. Ctrl+Z ngay nếu vừa lỡ tay; đã lỡ lưu rồi thì phải viết lại công thức, không khôi phục được.
Xoá bớt một cột thừa trong sổ chi tiết của Nam Tiến Phát, lập tức các công thức dò bên bảng tổng hợp và bảng cân đối chết theo cả loạt
Ctrl+Z ngay; lỡ lưu rồi thì sửa lại số cột: =VLOOKUP(B2,So!A:H,7,0) - #DIV/0!Phổ biến
Chia cho 0 hoặc chia cho ô rỗng. Chữa gốc: =IF(mẫu_số=0, "", tử/mẫu) hoặc =IFERROR(tử/mẫu, "").
Tính tỷ lệ thực hiện ngân sách hoặc vòng quay hàng tồn cho tháng chưa phát sinh, mẫu số đang bằng 0
=IF(C2=0,"",B2/C2) - #NAME?Phổ biến
Excel không hiểu tên đang gõ. Do gõ sai tên hàm · quên nháy kép quanh chuỗi · dùng tên vùng chưa đặt · dùng hàm chỉ có ở bản Excel mới hơn (XLOOKUP, TEXTSPLIT... trên bản 2019 trở về trước).
Gửi file báo cáo cho kế toán trưởng, máy bên đó dùng Excel 2016 nên các hàm đời mới trong công thức không chạy
=INDEX(Gia!D:D,MATCH(B2,Gia!A:A,0)) - #NUM!Nên biết
Số vượt quá sức tính hoặc đối số vô lý: căn bậc hai của số âm, luỹ thừa quá lớn, hoặc hàm lặp (IRR, RATE) không hội tụ — thử cho nó giá trị đoán ban đầu.
Tính IRR cho phương án đầu tư dây chuyền, dòng tiền chưa ghi vốn bỏ ra là số âm nên hàm không tìm ra nghiệm
Để vốn đầu tư là số âm rồi thêm giá trị đoán: =IRR(B2:B14,0.1) - #NULL!Nên biết
Hai vùng không giao nhau. Gần như luôn là gõ nhầm DẤU CÁCH thay cho dấu phẩy giữa hai vùng: =SUM(A1:A5 C1:C5).
Cộng gộp chi phí trực tiếp ở cột A với chi phí quản lý ở cột C mà gõ dấu cách thay cho dấu phẩy giữa hai vùng
=SUM(A1:A5,C1:C5) - #SPILL!Nên biết
Công thức mảng động muốn tràn ra nhiều ô nhưng vùng tràn đang bị vướng. Xoá thứ đang chắn bên dưới/bên phải là hết. Ô gộp (Merge) cũng chặn tràn.
Lấy danh sách khách hàng không trùng bằng UNIQUE để dựng báo cáo công nợ, nhưng bên dưới còn dòng tổng cộng cũ
Xoá dòng đang chắn bên dưới; nếu là ô gộp thì bôi vùng → Alt › H › M › U - #CALC!
Công thức mảng ra kết quả rỗng hoặc không tính nổi — hay gặp khi FILTER không lọc được dòng nào mà không cho đối số "nếu rỗng".
Lọc danh sách hoá đơn quá hạn bằng FILTER, tháng nào không còn hoá đơn nào quá hạn thì ô báo lỗi này.
=FILTER(A2:D500, D2:D500>30, 0) — thêm đối số khi rỗng - #GETTING_DATA
Không phải lỗi — Excel đang nạp dữ liệu, đợi một chút.
Bảng tổng hợp doanh thu vừa nạp lại, ô còn hiện dòng này trong lúc Excel kéo dữ liệu về và tính.
Đợi vài giây, đừng sửa ô; lâu quá thì Esc rồi Ctrl+Alt+F5 - #####Phổ biến
Không phải lỗi: cột hẹp quá không đủ chỗ hiện số. Nới cột (bấm đúp mép tiêu đề cột hoặc Alt › H › O › I). Nếu nới rồi vẫn thấy thì là ngày/giờ mang giá trị ÂM.
In bảng cân đối phát sinh, cột số dư cuối kỳ hàng chục triệu nên bị co lại thành một dãy dấu thăng
Bôi cột số tiền → Alt › H › O › I
Thông báo bật ra khi làm việc
- Circular ReferenceNên biết
Công thức tự trỏ vào chính nó (trực tiếp hoặc vòng qua ô khác). Formulas → Error Checking → Circular References để biết ô nào. Thanh trạng thái cũng ghi địa chỉ ô gây vòng.
Tính thưởng theo phần trăm lợi nhuận rồi lại trừ chính khoản thưởng đó vào lợi nhuận, hoặc SUM trùm luôn ô tổng cộng
Formulas → Error Checking → Circular References rồi sửa vùng SUM cho hết trùm ô tổng - "...is not valid" khi tạo PivotNên biết
PivotTable Field Name Is Not Valid — vùng nguồn có cột THIẾU TIÊU ĐỀ hoặc tiêu đề trùng nhau. Điền đủ tiêu đề cho mọi cột rồi tạo lại.
Dựng pivot doanh thu theo khách hàng từ file xuất thẳng ra từ phần mềm, hàng tiêu đề có vài ô bị bỏ trống
Điền tiêu đề cho mọi cột, xoá dòng trống phía trên rồi Insert → PivotTable - "We couldn't free up space"
File quá nặng hoặc thiếu bộ nhớ. Xoá định dạng thừa (Ctrl+End nhảy xuống tận dòng trăm nghìn là dấu hiệu), xoá dòng/cột rỗng thừa rồi lưu lại.
File sổ chi tiết bị kéo định dạng xuống tận hàng trăm nghìn dòng trống, tới lúc lưu hoặc mở lại thì Excel báo hết bộ nhớ
Ctrl+End → chọn từ dưới dữ liệu tới hết → Ctrl+- → lưu, đóng, mở lại - File bị khoá bởi người khácNên biết
"...is locked for editing" — file đang mở ở máy khác, hoặc file tạm ~$ còn sót lại. Đóng hết Excel rồi xoá file ẩn có tên bắt đầu bằng ~$ trong cùng thư mục.
Mở file bảng lương để trên ổ chung của phòng, Excel báo chỉ mở được ở chế độ đọc dù không ai đang dùng
Đóng hết Excel → View → Hidden items → xoá file ~$Bang luong.xlsx - Protected ViewNên biết
File tải từ mạng/email bị mở ở chế độ chỉ xem. Bấm Enable Editing. Không sửa được gì cho tới khi bấm nút đó.
Tải sao kê ngân hàng hoặc bảng kê hoá đơn nhà cung cấp gửi qua email về, gõ vào ô nào cũng không được
Bấm Enable Editing trên dải vàng rồi F12 lưu lại thành .xlsx - Macro bị chặn
"Macros have been disabled" — file .xlsm tải từ ngoài về. Chuột phải file → Properties → tick Unblock, rồi mới mở lại.
Nhận file mẫu bảng lương đuôi xlsm qua email, mở lên bấm nút thì không chạy và hiện dải vàng cảnh báo
Chuột phải file → Properties → tick Unblock → OK → mở lại file - Links to other workbooks
File đang trỏ sang file khác. Data → Edit Links để xem trỏ đi đâu, sửa nguồn, hoặc Break Link để cắt đứt (cắt xong công thức biến thành giá trị chết, không hoàn tác được).
Chép file báo cáo hợp nhất từ máy kế toán cũ, mở lên thì hỏi cập nhật liên kết tới một file không còn trên máy này
Data → Edit Links → Change Source trỏ lại file, hoặc Break Link - Không sắp xếp đượcNên biết
Sort báo lỗi hoặc sắp ra sai — thường vì có ô GỘP trong vùng, hoặc vùng chọn không trùm hết bảng nên dữ liệu bị xé lệch dòng.
Sắp bảng theo dõi công nợ theo số ngày quá hạn giảm dần, tên khách hàng chạy lệch khỏi số tiền của họ
Bôi bảng → Alt › H › M › U bỏ gộp → chọn lại cả bảng → Data → Sort - Số không cộng đượcNên biết
SUM ra 0 dù nhìn thấy số: dữ liệu đang là chữ. Xem góc ô có tam giác xanh không, và thử =COUNT(vùng) — ra 0 là đúng bệnh đó.
Copy số dư đầu kỳ từ trang web thuế hay bản PDF vào Excel, nhìn thấy số rõ ràng mà dòng tổng cộng vẫn ra 0
Gõ 1 vào ô trống, Copy, bôi cột số → Ctrl+Alt+V → Multiply - Công thức hiện nguyên chữNên biết
Ô hiện =A1+B1 thay vì kết quả: hoặc đang bật Show Formulas (Ctrl+`), hoặc ô đang để định dạng Text — đổi về General rồi bấm F2 và Enter lại.
Nhận file mẫu đơn giá của phòng kinh doanh, cột thành tiền hiện đúng chữ =D2*E2 chứ không ra con số
Bôi cột → Ctrl+1 → General → F2 → Enter (nếu cả sheet thì bấm Ctrl+`) - File mở ra trống trơn
Cửa sổ Excel trắng: sheet đang bị ẩn (chuột phải tab → Unhide) hoặc cửa sổ workbook bị Hide ở View → Unhide.
Nhận file quyết toán từ đối tác, mở lên chỉ thấy nền xám trắng bóc, tưởng file hỏng nên định xin gửi lại
View → Unhide chọn workbook, hoặc chuột phải tab sheet → Unhide