Các lỗi trong Excel (Phần 1)

Home / Thủ thuật / Các lỗi trong Excel (Phần 1)

Trong Excel, có rất nhiều thông báo lỗi trong Excel khác nhau để cho chúng ta biết lỗi gì xảy ra. Khi thao tác với Excel, chắc không ít lần các bạn gặp phải lỗi công thức chứa dấu thăng (#) như #NUM, #VALUE,… Vậy làm cách nào để sửa? Bài viết này, Coffee Excel sẽ giới thiệu nguyên nhân lỗi và hướng dẫn khắc phục sự cố đối với từng loại lỗi nhé.

Hãy cùng Coffee Excel học các thủ thuật Excel tài chính khác tại Coffee Excel nhé.

Lỗi #VALUE!

*Lỗi #VALUE! trong hàm AVERAGE hoặc SUM

– Nếu trong vùng tính toán của 2 hàm AVERAGE hoặc SUM có ô chứa lỗi #VALUE! Thì kết quả bạn nhận được sẽ là lỗi #VALUE!

lỗi trong Excel

Cách khắc phục: Để bỏ qua các giá trị lỗi, chúng ta sẽ xây dựng một công thức bỏ qua các lỗi trong dải ô tham chiếu khi tính trổng các giá trị “bình thường” còn lại.

Chúng ta sử dụng kết hợp hàm SUM cùng với IF và ISERROR để xác định xem có lỗi trong dải ô đã xác định hay không. Công thức mảng được sử dụng là:

=SUM(IF(ISERROR(B3:B8),””,B3:B8))

 

lỗi trong Excel

Lưu ý: Đây là một công thức mảng nên để nhập được công thức này, cần bấm tổ hợp phím CTRL+SHIFT+ENTER. Excel sẽ tự động bao công thức vào trong ngoặc móc {}.

Lưu ý: Hàm trên đây sẽ không chỉ có hiệu lực đối với #VALUE! mà còn với #N/A, #NULL, #DIV/0! và các hàm khác.

Tương tự, ta có thể áp dụng với sửa lỗi #VALUE! trong hàm AVERAGE

* Lỗi #VALUE! trong hàm COUNTIF/COUNTIFS

Cú pháp hàm COUNTIF

= COUNTIF(<vùng>, <điều kiện đếm>)

 

– Hàm COUNTIF/COUNTIFS tham chiếu đến một ô hoặc dải ô trong sổ làm việc đã đóng sẽ cho kết quả là lỗi #VALUE!.

Cách khắc phục:

Cách 1: Mở sổ làm việc liên kết được biểu thị trong công thức, rồi nhấn F9 để làm mới công thức.

Cách 2: Bạn cũng có thể giải quyết sự cố này bằng cách sử dụng hàm SUM và IF cùng nhau trong công thức mảng.

– Chuỗi nhiều hơn 255 ký tự

Nếu tham sô <điều kiện đếm> có nhiều hơn 255 ký tự thì kết quả trả về là lỗi #VALUE!

Cách khắc phục: Rút ngắn chuỗi nếu có thể. Nếu bạn không thể rút ngắn chuỗi, hãy sử dụng hàm CONCATENATE hoặc toán tử dấu và (&) để ngắt giá trị thành nhiều chuỗi.

+Sử dụng hàm CONCATENATE

Cú pháp

=CONCATENTE(text1,text2…)

 

Trong đó:

– text1: chuỗi cần nối thứ nhất.

– text2: là chuỗi cần nối tiếp theo, có thể nối tối đa 255 chuỗi, tổng cộng là 8194 kí tự.

lỗi trong Excel

* Lỗi #VALUE! trong hàm DATEVALUE

Khi sử dụng hàm Date trong Excel, luôn nhớ rằng ngày mà bạn sử dụng trong công thức của mình sẽ bị ảnh hưởng bởi cài đặt Ngày và Giờ trên hệ thống của bạn. Khi Excel phát hiện sự không trùng khớp giữa các định dạng trong tham chiếu văn_bản_ngày và cài đặt Ngày và Giờ của hệ thống, bạn sẽ gặp phải lỗi #VALUE!.

Ví dụ: Nếu cài đặt ngày và giờ hệ thống của bạn theo định dạng mm/dd/yyyy thì công thức =DATEVALUE(“23/01/1999”) sẽ dẫn đến lỗi #VALUE!.

Cách khắc phục: Sửa ngày và giờ của hệ thống được đặt thành định dạng dd/mm/yyyy.

* Lỗi #VALUE! trong các hàm FIND/FINDB và SEARCH/SEARCHB

Hàm FIND và SEARCH tương đối giống nhau. Cả hai đều dùng để định vị một ký tự hoặc một chuỗi văn bản trong một chuỗi văn bản khác. Sự khác biệt giữa hai hàm này là hàm FIND có phân biệt chữ hoa/thường, còn SEARCH thì không phân biệt chữ hoa/thường.

– Không tìm được giá trị trong tham đối văn_bản_tìm_kiếm trong chuỗi trong_văn_bản

Ví dụ

=FIND(“Google”,”Coffee Excel”,1)

 

Cách khắc phục: Sửa cú pháp cho chính xác

– Vị trí bắt đầu tìm kiếm có giá trị là số 0

Trong cú pháp của các hàm này thì tham số thứ 3 là vị trí bắt đầu tìm kiếm, tham số này là một tham số tùy chọn và nếu bạn bỏ qua, giá trị mặc định sẽ được giả định là 1. Tuy nhiên, nếu tham số được gán giá trị thành 0, bạn sẽ gặp lỗi #VALUE!.

Cách khắc phục: Loại bỏ tham số này nếu không bắt buộc hoặc đặt thành giá trị chính xác.

– Vị trí bắt đầu tìm lớn hơn độ dài chuỗi đang tìm kiếm

Ví dụ:

=FIND(“e”,”Coffee Excel”,90)

 

Cách khắc phục: Sửa lại giá trị bắt đầu tìm kiếm cho hợp lí.

* Lỗi #VALUE! trong hàm SUMIF/SUMIFS

– Hàm SUMIF/SUMIFS tham chiếu đến một ô hoặc dải ô trong sổ làm việc đã đóng

Cách khắc phục: Tương tự như cách khắc phục lỗi #VALUE! trong hàm COUNTIF/COUNTIFS

– Chuỗi nhiều hơn 255 ký tự

Cách khắc phục: Tương tự như cách khắc phục lỗi #VALUE! trong hàm COUNTIF/COUNTIFS

– Trong hàm SUMIFS, các vùng tham chiếu bị lệch kích thước với nhau

Ta sẽ nhắc lại cú pháp của hàm này để các bạn có thể tiện theo dõi

Cú pháp:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

 

Ví dụ:

lỗi trong Excel

Việc nhập phần sum_range không nhất quán với các phần criteria_range1 hoặc criteria_range2,…đã dẫn đến lỗi

Cách khắc phục: Sửa lại phạm vi các vùng tha, chiếu cho khớp. Cụ thể trong ví vụ này là sửa lại thành “=SUMIFS(D4:D18,C4:C18,”Hàng loại A”,B4:B18,”Số 1″)”

* Lỗi #VALUE! trong hàm SUMPRODUCT

– Vùng tham chiếu bị lệch kích thước

Ví dụ:

lỗi trong Excel

Các bạn cũng thấy ở ví dụ trên, việc nhập lệch kích thước các vùng tham chiếu đã khiến ta nhận kết quả lỗi #VALUE!

Cách khắc phục: Nhập lại vùng tham chiếu cho khớp. Và cụ thể ở ví dụ trên thì cần nhập lại là “=SUMPRODUCT(B4:B8,C4:C8)”

– Trong vùng tham chiếu có 1 hoặc nhiều ô có lỗi #VALUE!

Cách khắc phục: Kiểm tra và chỉnh thành kiểu dữ liệu đúng trước khi sử dụng hàm.

* Lỗi #VALUE! trong hàm VLOOKUP

– Giá trị tra cứu vượt quá 255 kí tự

lỗi trong Excel

Cách khắc phục: Rút ngắn giá trị hoặc sử dụng kết hợp hàm INDEX và MATCH như một giải pháp thay thế.

lỗi trong Excel

Nhập công thức “=INDEX(B4:B8,MATCH(TRUE,INDEX(A4:A8=E5,0),0))”

 

Có lẽ phần 1 sẽ tạm dừng ở đây, mong các bạn hãy đón đón các phần tiếp theo của bài viết này nhé

Nguồn: Thơ Trần from Coffee Excel

Thay đổi màu Sheet theo giá trị ô trong Excel
Các lỗi trong Excel (Phần 2)

Bài viết liên quan

Sống và làm việc hết mình, hay viết về ti tỉ thứ trên đời. Tìm hiểu và viết về Ecxel là một niềm đam mê