Kiến thức

Các lỗi thông dụng thường gặp trong Excel-xaydungthuchanh.vn

Victor Vuong

01/10/2020 1676

Các lỗi thông dụng thường gặp trong Excel

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


Xem thêm: Ôn thi vào 10 môn Toán-Dạng 3: Giải phương trình bậc hai

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à :mrgreen: ) 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 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


B – Lỗi công thức, hàm khi tính toán (10 lỗi)

Xem thêm: Cách giải phương trình bậc 2 số phức cực hay, chi tiết-Toán lớp 12

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

Xem thêm: NaOH tác dụng được với những chất nào? Có nguy hiểm không?

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


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

 

  • Xem ngay hướng dẫn cài đặt sử dụng và Download XDTH Add-ins Excel miễn phí tại link này.

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ỗikhô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!

  • Xem ngay hướng dẫn cài đặt sử dụng và Download XDTH Add-ins Excel miễn phí tại link này.


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. Bằng cách sử dụng

XDTH Add-ins Excel

chỉ trong một nốt nhạc chúng ta sẽ triệt tiêu các lỗi cài đặt Excel đưa về mặc định cũng như tìm thấy tất cả các lỗi công thức, hàm khi tính toán.

Do mình đang để tiêu đề bài viết về Add-ins dành cho QS nhưng thực ra tất cả người dùng Excel đều cần các tiện ích trong Add-ins này, nó sẽ giúp công việc nhanh và chất lượng hơn nhiều, các bạn có thể Download miễn phí về dùng nhé.

Download XDTH Add-ins Excel 1.0

Download XDTH Add-ins Excel 1.1

(Bổ sung thêm Format Shapes, bổ sung thêm một số Settings Excel)

Download XDTH Add-ins Excel 1.2

(Bổ sung thêm Trim Text và Group Qulity Control trong tab Utilities và một số Settings Excel về mặc định)

Nếu thấy bài viết hữu ích cũng như Add-ins đem lại hiệu quả công việc tốt hơn bạn hãy chia sẻ bài viết này tới những người bạn của bạn nhé, Add-ins không giữ bản quyền hoàn toàn miễn phí!

Có gì chưa rõ hoặc bạn muốn đóng góp bổ sung cho bài viết cũng như về Add-ins vui lòng để lại bình luận bên dưới nhé.

Còn nếu bạn quan tâm đến các khóa học thực hành chuyên môn xây dựng tại trung tâm, có thể xem thông tin các khóa học tại link bên dưới, liên hệ với trung tâm theo các cách

TẠI ĐÂY

.

Xem thêm: Các khóa học QS, đo bóc khối lượng thực tế tại trung tâm

Chúc bạn thành công!

Victor Vuong,

5 / 5 ( 1 bình chọn )

Victor Vuong

Victor Vuong

CHIẾN DỊCH: Mình sẽ chia sẻ miễn phí trên kênh Youtube các kiến thức, thủ thuật tin học văn phòng Excel, Word, Powerpoint, Hệ điều hành Window, AutoCAD, Google Sheet và kiến thức trong xây dựng: Đo bóc khối lượng, lập giá, biện pháp thi công, Shop drawing, dưới dạng cầm tay chỉ việc, từ cơ bản cho người mới, người mất gốc đến nâng cao.

Anh chị và các bạn có nhu cầu hãy đăng ký kênh

YOUTUBE

để theo dõi nhé. Chiến dịch bắt đầu: 22/02/2021.

Bài cùng chủ đề / The same topic:

  • Excel cơ bản khóa học online miễn phí cho người mới bắt đầu, dân văn phòng

  • Add-ins Excel cho dân văn phòng, công cụ không thể thiếu đối với QS

  • Xóa Styles rác và Names rác trong Excel (tạo một file Excel nhẹ, không bị treo)

  • Sửa lỗi Excel không tự động trả kết quả khi đã Enter

  • In đen trắng bỏ màu nền ở trang in nhưng vẫn giữ nguyên màu nền trong bảng tính

  • Sử dụng Goal Seek để tìm giá trị nội suy/ mục tiêu (2020)

  • Sử dụng Data Remove Duplicates kết hợp hàm Sumif gộp các đầu việc trùng lặp và tạo BOQ

  • Sử dụng Consolidate để gộp các dữ liệu trùng nhau

Chuyên mục: Kiến thức

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Back to top button