Trong quá trình làm việc có khá nhiều đồng nghiệp hỏi mình về các lỗi của Excel. Ví dụ: #### trong excel là lỗi gì, value trong excel là lỗi gì, excel bị lỗi công thức, lỗi excel không chạy công thức, lỗi #n/a trong excel, enter không xuống dòng mà nhảy lên trên hoặc sang trái/ phải, hoặc nhập vào số nguyên nhưng kết quả lại ra số thập phân… Bài viết này mình sẽ chia sẻ tất cả các lỗi thường gặp trong Excel, phổ biến nhất mà ai cũng gặp phải ít nhất một lần. Ngoài ra còn có khoảng 10 lỗi công thức, hàm khi thực hiện tính toán nữa: #####, #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, #NULL!…
OK, LET’S GO!
A – Lỗi cài đặt Excel (12 lỗi thông dụng)
Hầu hết các lỗi này đều xử lý bằng cách chỉnh sửa lại cài đặt trong Options của Excel: Vào Menu File \ Options:
1. Không tự động thực hiện phép tính khi ấn Enter
Lỗi này rất hay gặp phải, vì một lý do nào đó mà Excel tự động chuyển sang chế độ tính tay Manual. Tức là sẽ không tự động Automatic thực hiện phép tính khi bấm Enter nữa.
Không tự động thực hiện phép tính khi Enter
Vào Menu File \ Options \ Formulas \ Calculation options \ Workbook Calculation \ Tích chọn vào Automatic để cài đặt lại chế độ Automatic
2. Enter không xuống dòng mà nhảy lên trên hoặc sang trái/ phải
Thông thường khi Enter kết thúc lệnh thì con trỏ được chuyển xuống ô ngay bên dưới, tuy nhiên nếu gặp phải trường hợp con trỏ nhảy lên trên thậm chí là sang trái sang phải thì bạn cũng đừng lo lắng, đây chỉ là do Excel đã bị cài đặt khác mặc định xuống dưới thôi.
Khắc phục: Vào Options \ Advanced \ Editing options \ trong phần Direction chọn lại Down:
Chọn Down để con trỏ Enter xuống dòng
3. Enter không di chuyển con trỏ
Tương tự lỗi Enter con trỏ không xuống dòng, lần này thì con trỏ không chịu di chuyển, vẫn đứng im ô đang thao tác.
Khắc phục: Vào Options \ Advanced \ Editing options \ Tích chọn vào After pressing Enter, move selection (Sau khi bấm Enter sẽ di chuyển => và ngay bên dưới là hướng chuyển của con trỏ).
Tích chọn để di chuyển con trỏ khi bấm Enter
4. Mất chức năng Auto Fill (kéo copy công thức tự động)
Chức năng này nghĩa là tự động điền đầy các ô khác theo công thức hoặc quy luật của vùng được chọn. Thường chọn vùng cột, đưa con trỏ xuống ô dưới cùng khi xuất hiện dấu cộng thì bấm kéo hoặc click đúp chuột. Khi chức năng này bị mất, chúng ta sẽ không thấy xuất hiện dấu cộng và không thực hiện được Auto Fill.
Khắc phục: Vào Options \ Advanced \ Editing options \ Tích chọn vào Enable fill handle and cell drag-and-drop:
Tích chọn Enable fill handle để thực hiện Auto Fill
[adinserter block=”4″]5. Không sửa trực tiếp ô hiện hành bằng cách bấm đúp chuột được
Bình thường khi bấm đúp chuột hoặc bấm phím F2 thì chúng ta sẽ truy cập trực triếp vào ô hiện hành để chỉnh sửa được. Nhưng khi bị lỗi nó cứ trơ trơ ra không truy cập trực tiếp vào ô hiện hành được.
Khắc phục: Vào Options \ Advanced \ Editing options \ Tích chọn vào Alow editing direcly in cells:
Tích chọn vào Alow editing direcly in cells
6. Tự động chèn dấu thập phân khi nhập liệu (ví dụ nhập 150 nhưng kết quả là 1,5)
Trường hợp này khá nhiều bạn không biết lý do tại sao và tra Google cũng ít thấy bài viết nên rất hoang mang nghi là Excel bị lỗi (crack mà 
) rồi hì hục cài lại Excel 😆
Nhưng đây chỉ là cài đặt của Excel, lý do là Microsoft muốn có một công cụ nhập liệu số phần thập phân nhanh mà thôi.
Khắc phục: Vào Options \ Advanced \ Editing options \ Bỏ tích chọn Automatically insert a decimal point:
Bỏ tích chọn Automatically insert a decimal point
7. Không thấy địa chỉ ô (ví dụ B8/ C15) và tiêu đề cột ABC… mà chỉ thấy hiển thị RxCy trên thanh công thức, cột và hàng đều là tiêu đề số
Đây là cách hiện thị, truy vấn tham chiếu ô tính khác trong Excel. Cách hiện thị, tham chiếu này ít được sử dụng. Chúng ta hay dùng và quen thuộc với hệ cột ABC và hàng 123.
Khắc phục: Vào Options \ Formulas \ Working with formulas \ Bỏ tích chọn tại R1C1 reference style
Bỏ tích chọn tại R1C1 reference style
8. Mất sheet tab (tên các sheet biến mất)
Bình thường chúng ta có các bảng tính sheet1, sheet2, sheet3… (hoặc tên được thay đổi) và thao tác tính toán trên các sheet tab này. Excel có chức năng cho phép ẩn các sheet tab này đi đối với từng file (các file khác không ẩn thì vẫn hiển thijh bình thường).
Khắc phục: Vào Options \ Advanced \ Display options for this workbook \ Chọn File bị ẩn \ Tích chọn vào Show sheet tabs
Chọn File bị ẩn \ Tích chọn vào Show sheet tabs
9. Mất thanh cuộn ngang dọc
Thanh cuộn ngang dọc ở bên dưới cùng và bên phải bảng tính bị mất.
Khắc phục: Vào Options \ Advanced \ Display options for this workbook \ Tích chọn vào Show horizontal scroll bar và Show vertical scroll bar
Tích chọn vào Show horizontal scroll bar và Show vertical scroll bar
10, 11, 12. Mất thanh tiêu đề cột hàng, mất các đường Gridlines mờ, mất thanh công thức (thanh Function)
Khắc phục: Vào menu View tích chọn vào Headings, Formula Bar và Gridlines
Vào menu View tích chọn vào Headings, Formula Bar và Gridlines
[adinserter block=”7″]B – Lỗi công thức, hàm khi tính toán (10 lỗi)
13. Lỗi phím mũi tên – lỗi thường gặp trong Excel
– Khi nhấn các phím mũi tên trên bàn phím thì lại cuộn màn hình chứ không nhảy sang ô bên trên, dưới, trái, phải. Nguyên nhân là do bạn đã bật phím Scroll Lock.
– Khắc phục: Bạn hãy xem đèn của phím Scroll Lock có sáng không, nếu có thì bạn nhấn phím Scroll Lock để tắt nó đi. Lưu ý một số bàn phím thế hệ mới hoặc bàn phím Laptop tắt mở Scroll Lock phải bấm tổ hợp phím: Fn + Scroll Lock
Lỗi phím mũi tên do bật Scroll Lock
14. #NULL! Lỗi dữ liệu rỗng
– Dùng một dãy toán tử không phù hợp
– Dùng một mảng không có phân cách “;” hoặc “,”
Lỗi này xảy ra khi sử dụng các phép toán tử như =SUM(A1:A10 B1:B10) => Thiếu dấu “ ; ” hoặc “ , ”
#NULL! Lỗi dữ liệu rỗng
15. #### trong excel là lỗi gì? ##### Là lỗi thiếu độ rộng cột
– Khi cột thiếu độ rộng.
– Khi bạn nhập giá trị ngày tháng hoặc thời gian là số âm.
Khắc phục: Dùng chuột kéo độ rộng cột ra cho phù hợp hoặc bấm đúp vào cột để chọn Autofix.
Lỗi thiếu độ rộng cột
16. Value trong excel là lỗi gì? #VALUE! Lỗi giá trị
– Tính toán các phép toán không cùng một kiểu dữ liệu. Ví dụ cộng số Number và chuỗi Text: 10 + “abc”
– Đang nhập một hoặc chỉnh sửa các thành phần của công thức mà nhấn Enter.
– Nhập quá nhiều tham số cho một toán tử hoặc một hàm trong khi chúng chỉ dùng một tham số. Ví dụ =LEFT(A1:A5)
– Thi hành một marco (lệnh tự động) liên quan đến một hàm mà hàm đó trả về lỗi #VALUE!
#VALUE! Lỗi giá trị
17. #DIV/0! Lỗi chia cho 0
– Nhập vào công thức số chia là 0 hoặc chia cho ô trống. Ví dụ = 15/0
18. #NAME? Sai tên hàm – lỗi thường gặp trong Excel
– Khi chúng ta dùng những hàm không thường trực trong Excel như EDATE, EOMONTH, NETWORKDAYS, WORKDAYS,… Khi đó cần phải vào menu Tools – Add-in. Đánh dấu vào tiện ích Analysis ToolPak.
– Nhập sai tên hàm số. Trường hợp này xảy tra khi bạn dùng bộ gõ tiếng Việt ở chế độ Telex vô tình làm sai tên hàm như IF thành Ì, VLOOKUP thành VLÔKUP, hàm SUM thành SUMM hoặc bạn gõ bất kỳ một hàm nào không có trong Excel.
– Dùng những ký tự không được phép trong công thức.
– Nhập một chuỗi trong công thức mà không có đóng và mở dấu nháy kép “ ”
– Không có dấu 2 chấm: trong dãy địa chỉ ô trong công thức.
Sai tên hàm – lỗi thường gặp trong Excel
19. Lỗi #n/a trong excel: Lỗi tham chiếu dữ liệu
– Giá trị trả về không tương thích từ các hàm dò tìm như VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH.
– Dùng hàm HLOOKUP, VLOOKUP, MATCH để trả về một giá trị trong bảng chưa được sắp xếp.
– Không đồng nhất dữ liệu khi bạn sử dụng địa chỉ mảng trong Excel.
– Quên một hoặc nhiều đối số trong các hàm tự tạo.
– Dùng một hàm tự tạo không hợp lý.
Hay xảy ra khi công thức tham chiếu đến một ô trong mảng nhưng ô đó lại không có giá trị trong mảng. Lỗi này thường xảy ra với các hàm tìm kiếm như VLOOKUP, HLOOKUP… Bạn chỉ cần xem kỹ lại công thức và chỉnh sửa ô mà hàm tham chiếu tới.
#N/A Lỗi tham chiếu dữ liệu
[adinserter block=”10″]20. #REF! Sai vùng tham chiếu
– Đây là lỗi tham chiếu ô, chẳng hạn bạn xóa dòng hay cột mà dòng cột đó được dùng trong công thức, khi đó sẽ bị báo lỗi #REF!
– Hàm Vlookup tham chiếu ra ngoài vùng.
#REF! Sai vùng tham chiếu
21. #NUM! Lỗi dữ liệu kiểu số
– Dùng một đối số không phù hợp trong công thức sử dụng đối số là dữ liệu kiểu số. Ví dụ bạn đưa vào công thức số âm trong khi nó chỉ tính số dương.
– Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về.
– Dùng một hàm trả về một số quá lớn hoặc quá nhỏ so với khả năng tính toán của Excel.
– Chẳng hạn như phép tính =SQRT(-2) sẽ bị báo lỗi #NUM!, vì không có căn bậc 2 của số âm.
22. Lỗi công thức vòng tròn
– Xảy ra khi bạn thực hiện các công thức, link tạo thành một vòng tròn, khi đó Excel sẽ không thể thực hiện phép tính hoặc sẽ cho giá trị bằng 0 vì không xác định được biến số.
Khắc phục: Kiểm tra các ô và bỏ công thức, link vòng tròn. Bạn vào Tab Formulas \ Error Checking \ Circular References => Sẽ chỉ cho chúng ta vị trí các Cells lỗi vòng lặp.
Lỗi công thức vòng tròn
Cách tìm các ô tính bị lỗi vòng tròn
Tuy nhiên, trong Excel cũng có lựa chọn cài đặt cho phép Excel thực hiện phép tính vòng lặp, khi đó sẽ không báo lỗi nữa. Nếu muốn Excel thực hiện phép tính vòng lặp bạn vào File \ Options và tích vào nút, và chọn số lần thực hiện phép tính vòng lặp mong muốn, mặc định là 100. Kết quả sẽ cho chúng ta số liệu CỰC LỚN, vì nó đã thực hiện vòng lặp đến tận 100 lần!
Cài đặt chế độ tính toán vòng lặp trong Excel
C – Cách tìm và xử lý tất tần tật các lỗi trên chỉ trong một nốt nhạc:
1. Xử lý tất cả các lỗi cài đặt Excel về mặc định chỉ trong một nốt nhạc (12 lỗi):
Thay vì phải vào phần Options Excel kéo kéo cuộn cuộn, dịch dịch sang tiếng Việt để hiểu sau đó tích chọn hoặc bỏ chọn các cài đặt, việc làm này mất kha khá thời gian cũng như gây khó khăn cho các bạn có vốn tiếng Anh ít.
Giờ đây đã có XDTH Add-ins Excel, Add-ins này được mình tích hợp rất nhiều công cụ hữu ích cho người dùng Excel như kẻ bảng nhanh, tính tổng nhanh, phím tắt Group/ Ungroup hàng… dù là kế toán thủ kho hay QS đều dùng được. Add-ins sạch không quảng cáo và hoàn toàn miễn phí nhé!
Chỉ cần cài đặt Add-ins này, chọn tab Utilities và bấm vào nút Settings Excel là toàn bộ 12 lỗi cài đặt trên được trả về mặc định.
Việc cài đặt Add-ins này cực kỳ đơn giản, chỉ cần Download Add-ins về sau đó load vào Excel là xong (khoảng 15s).
Bấm Settings Excel trong XDTH Add-ins Excel để cài đặt Excel về mặc định
Một số tính năng hữu ích được thêm vào tab Home trong XDTH Add-ins Excel
2. Tìm các lỗi công thức, hàm khi tính toán (10 lỗi)
Trong bảng tính có nhiều số liệu, nhiều trang, file chúng ta lại có nhiều sheet như vậy thì việc phát hiện ra một lỗi tính toán khá mất thời gian. Hiện nay có 2 cách làm phổ biến sau:
Cách 1: Sử các hàm tìm lỗi có sẵn trong Excel kết hợp định dạng có điều kiện để tìm và tô màu ô lỗi.
Thông dụng nhất là hàm ISERROR(). Hàm này sẽ trả về giá trị True nếu tìm thấy lỗi.
Các hàm tìm lỗi trong Excel (IFEROR, IFNA, ISERR, ISERROR)
Thực hiện: Chọn vùng cần tìm lỗi, sau đó vào tab Home\ Conditonal Formatting \ New Rule:
Chọn vùng dữ liệu cần tìm lỗi
Tab Home\ Conditonal Formatting \ New Rule
Gõ lệnh = iserror(a14) sau đó chọn Format để chọn màu tô. Với a14 là ô bất kỳ trong vùng chọn, thường lấy ô đầu tiên cho dễ nhớ
Sử dụng hàm ISERROR kết hợp định dạng có điều kiện để tìm các ô lỗi
Cách 2: Sử dụng XDTH Add-ins Excel
Chỉ cần một Click vào nút Find Error trong vòng một nốt nhạc là chúng ta đã tìm ra (tô màu) các ô lỗi kèm tổng số lỗi mà không cần chọn vùng trước khi click (cách 1 chúng ta phải chọn vùng cần tìm lỗi trước khi thực hiện lệnh tìm)! Không cần phải thực hiện nhiều bước phức tạp như cách 1 nữa!
[adinserter block=”13″]
Tìm lỗi bằng XDTH Add-ins Excel chỉ trong một nốt nhạc!
Kết bài:
Vậy là mình đã trình bày xong các lỗi Excel thông dụng và các cách để xử lý khắc phục lỗi.
Chúc bạn thành công!
Victor Vuong,
