Lỗi của hàm

tuhocvba

Administrator
Thành viên BQT
Công thức được nhập trong cells có thể sai hoặc không được tính toán đúng, từ đó nảy sinh ra lỗi được hiển thị trên cells.
Hiểu được nguyên nhân lỗi là gì và sửa như thế nào để tránh lỗi? Nếu không hiểu nguyên nhân vì sao nảy sinh lỗi thì bạn không thể sửa lại được.
Trước tiên, nếu không hiểu lỗi đó có ý nghĩa gì thì bạn sẽ không thể làm gì được.
Không phải lúc nào cũng có cơ hội gặp được những lỗi ngoài ý muốn mà được giải thích rõ ràng. Do đó, tôi lập topic này để trình bày các lỗi có thể phát sinh khi dùng hàm trong Excel, nguyên nhân và cách xử lý.

1. #####
2. #DIV/0!
3. #N/A
4. #NAME?
5. #NULL!
6. #NUM!
7. #REF!
8. #VALUE!

Nguồn;
 

vbano1

SMod
Thành viên BQT
1. #####
Bạn cần đăng nhập để thấy đính kèm


Nguyên nhân 1:
Đây không phải là lỗi của hàm. Giá trị số được nhập vào, hoặc kết quả tính toán tạo ra con số lớn, độ rộng cells không đủ để hiển thị đầy đủ, và thế là bị hiển thị thành ra như trên.
Ở Excel, kết quả của hàm khi được thể hiện, thông thường thì độ rộng của cells sẽ được tự động nới rộng ra.

Tuy nhiên, độ rộng của cells được tự động điều chỉnh chỉ định dạng của cells là giá trị tiêu chuẩn (General).
Bạn cần đăng nhập để thấy đính kèm

Ở ô B2 tôi nhập vào công thức:
Mã:
=12345678*10
Vốn dĩ định dạng cells là tiêu chuẩn (General), giá trị hiển thị ra là 1E + 10.
Tôi thay đổi định dạng cells là số.
Bạn cần đăng nhập để thấy đính kèm

Lúc này giá trị hiển thị ra là #####. Nếu trên máy tính của bạn không hiển thị như vậy, thế thì hãy thử thay đổi độ rộng của cells như sau:
Bạn cần đăng nhập để thấy đính kèm


Kết quả sẽ được:
Bạn cần đăng nhập để thấy đính kèm


Phương án giải quyết 1:
Hãy nới chiều rộng của cells ra như dưới đây:
Bạn cần đăng nhập để thấy đính kèm


Kết quả:
Bạn cần đăng nhập để thấy đính kèm


Ngoài ra, bạn có thể chỉnh lại cỡ font size, tuy nhiên trong thực tế, cách giải quyết như vậy ít được dùng.
Nguồn tham khảo và dịch:
 

Euler

Administrator
Thành viên BQT
1. #####
Nguyên nhân 2:

Có một nguyên nhân khác nữa, đó là khi chúng ta thực hiện phép trừ thời gian.
Bạn cần đăng nhập để thấy đính kèm


Cells A1 là 17h, cells A2 là 11h. Tại cells C2 ta thực hiện phép trừ = A1 - A2 .
A1 lớn hơn A2 nên phép trừ thực hiện ra kết quả đúng như kỳ vọng.

Bây giờ ta thử thay đổi A1 là 9:00, mục đích để A1 < A2.
Bạn cần đăng nhập để thấy đính kèm


Excel quản lý ngày giờ bằng giá trị serial có tính liên tục.
Khi tính toán với giá trị serial này, chúng ta có thể tính chênh lệch giờ từ giờ này tới giờ kia, hay từ ngày này tới ngày kia.
Tuy nhiên, giá trị serial định dạng chuẩn không thể hiện được con số âm.
Nếu thực hiện phép trừ 9:00 trừ đi 11:00, ta kỳ vọng là ra -2:00, nhưng kết quả hiển thị lại là #####.
Như vậy nguyên nhân hiển thị ra ##### đã khác với bài viết trước, vấn đề ở đây không phải là độ rộng cells không đủ để hiển thị. Dù cho bạn có nới rộng cells ra bao nhiêu đi chăng nữa thì cũng vẫn bị hiển thị là #####.

Phương án giải quyết 2:
Đối với Excel 2003, tôi không có phiên bản Excel 2003 để thuyết minh. Bạn hãy tham khảo link bài viết để khắc phục.
Theo đó, bạn mở Option, tìm tới mục phương pháp tính toán tích chọn vào mục tính toán từ 1904.
Mục lựa chọn này có nghĩa là giá trị serial mà Excel sử dụng sẽ được thiết định từ ngày 2 tháng 1 năm 1904.
Nếu không tích chọn vào mục này thì giá trị serial mà Excel tính toán sẽ bắt đầu từ ngày 1 tháng 1 năm 1900.

Với Excel 2007 hoặc mới hơn:
Vào File => Option=> Advanced: Tích chọn vào 1904 và ấn OK.
Bạn cần đăng nhập để thấy đính kèm


Lúc này giá trị hiển thị đúng như kỳ vọng:
Bạn cần đăng nhập để thấy đính kèm


Trong trường hợp phiên bản Excel 2007 hoặc mới hơn, khi tích chọn vào 1904, thời điểm bắt đầu tính giá trị serial bắt đầu sau 4 năm 1 ngày.
Điều này không ảnh hưởng tới giá trị tính toán nhưng hãy chú ý khi nhập ngày tháng.

Nếu bạn không muốn lấy giá trị âm, bạn có thể kết hợp hàm lấy giá trị truyệt đối. Khi đó hãy nhập vào cells C2 hàm là:
Mã:
=ABS(A1-A2)
Bạn cần đăng nhập để thấy đính kèm


Nguồn tham khảo:
 

tuhocvba

Administrator
Thành viên BQT
2. #DIV/0!
Bạn cần đăng nhập để thấy đính kèm

Đây là lỗi do ta thực hiện một phép chia cho 0, hiển nhiên về mặt toán học không có số nào chia được cho 0. Và vì vậy trên Excel nếu có phép toán như vậy thì sẽ có lỗi #DIV/0! , chúng ta cần phải xử lý như thế nào khi gặp lỗi này?

Chúng ta cần xử lý trung gian qua hàm ISERROR để phán đoán một phép toán có xảy ra lỗi hay không, nếu xảy ra lỗi thì hàm ISERROR sẽ có giá trị trả về là TRUE , khi đó giá trị trả về của phép toán mong muốn là rỗng "", nếu không xảy ra lỗi thì lấy kết quả của phép toán bình thường.
Bạn cần đăng nhập để thấy đính kèm

Tham khảo và dịch từ:
 

vbano1

SMod
Thành viên BQT
3. #N/A
Bạn cần đăng nhập để thấy đính kèm

Nguyên nhân: Công thức tính toán (thường là tìm kiếm) không có kết quả.
Ví dụ: Trong trường hợp bình thường ta có :
Bạn cần đăng nhập để thấy đính kèm

Hàm Rank trên sẽ cho biết 46 có vị trí nào từ to tới bé trong dãy số trên. Kết quả là ở vị trí số 4. (Sau các số 64, 62, 53).
Tuy nhiên nếu ta xóa giá trị ô A1:
Bạn cần đăng nhập để thấy đính kèm

Việc tìm kiếm sẽ không có kết quả, vì vậy nó trả về giá trị là #N/A .

Một ví dụ khác:
Bạn cần đăng nhập để thấy đính kèm

Bây giờ ta sẽ xóa giá trị ô B1.
Bạn cần đăng nhập để thấy đính kèm

Kết quả là #N/A do không tìm thấy giá trị nào phù hợp.
Tương tự như vậy, với các hàm tìm kiếm như VLookup thì cũng có khả năng cho ra kết quả #N/A nếu không tìm kiếm thấy.

Cách khắc phục, ta dùng hàm IFERROR:
Mã:
=IFERROR(MATCH(B1,A1:A4,0),"")
Bạn cần đăng nhập để thấy đính kèm
 

Euler

Administrator
Thành viên BQT
4. #NAME?
Lỗi này là do đánh sai công thức.
Ví dụ:
Bạn cần đăng nhập để thấy đính kèm

Nguyên nhân là vì tôi đã gõ sai công thức:
Bạn cần đăng nhập để thấy đính kèm

Gõ đúng phải là:
Mã:
=SUM(A1:A3)
Nguồn:
 

giaiphapvba

Administrator
Thành viên BQT
5. #NULL!
Lỗi này là vì đánh sai địa chỉ cho công thức. Hãy xem ví dụ dưới đây:
Bạn cần đăng nhập để thấy đính kèm

Nguyên nhân vì:
Bạn cần đăng nhập để thấy đính kèm

Thông thường phía trong công thức thường là các phép toán +, *,... Trong trường hợp này đúng ra ta phải gõ dấu : , nhưng thay vì thế ta lại gõ dấu cách. Dấu cách không phải là không có, nếu chúng ta muốn tìm phần giao nhau giữa hàng và cột, ta sẽ sử dụng dấu cách:
Bạn cần đăng nhập để thấy đính kèm

Lỗi này trong thực tế hiếm gặp.
Nguồn tham khảo và dịch:
 

tuhocvba

Administrator
Thành viên BQT
6. #NUM!
Đây là lỗi khá phổ biến.
Bạn cần đăng nhập để thấy đính kèm

Chúng ta hãy xem ví dụ sau:
Bạn cần đăng nhập để thấy đính kèm

Ở trên, tôi thực thi tìm kiếm số nhỏ ở vị trí thứ hai trong dãy A1 : A3 .
Tuy nhiên nếu tôi thực thi tìm kiếm số nhỏ ở vị trí thứ năm thì sẽ lỗi.
Bạn cần đăng nhập để thấy đính kèm

Bởi vì dãy số của ta chỉ có 3 số: 192, 189, 178. Vì vậy việc tìm kiếm số nhỏ ở vị trí thứ năm là điều không thể.
Để khắc phục lỗi này, chúng ta chỉ có cách khắc phục đó là chỉ định tham số chính xác.
Nguồn tham khảo:
 
Top