Khi làm việc với dữ liệu và công thức trong Excel, bạn chắc chắn sẽ gặp phải lỗi.
Để xử lý lỗi, Excel cung cấp một hàm hữu ích - hàm IFERROR.
Trước khi chúng ta hiểu cơ chế sử dụng hàm IFERROR trong Excel, trước tiên chúng ta hãy hiểu các loại lỗi khác nhau mà bạn có thể gặp phải khi sử dụng công thức.
Nội dung
Các loại lỗi trong Excel
Việc hiểu rõ các lỗi trong Excel sẽ giúp bạn xác định nguyên nhân có thể xảy ra và cách tốt nhất để giải quyết chúng.
Dưới đây là các loại lỗi bạn có thể tìm thấy trong Excel.
# N / A lỗi
Đây được gọi là lỗi "giá trị không khả dụng".
Bạn thấy điều này khi bạn sử dụng công thức tra cứu và giá trị không được tìm thấy (và do đó không có sẵn).
Dưới đây là một ví dụ mà tôi sử dụng công thức VLOOKUP để tìm giá của một mặt hàng, nhưng nó trả về lỗi khi không thể tìm thấy mặt hàng đó trong mảng bảng.
# DIV / 0!Sai
Bạn có thể gặp lỗi này khi một số bị chia cho 0.
Đây được gọi là lỗi phân chia.Trong ví dụ dưới đây, nó cho giá trị # DIV / 0!Lỗi vì giá trị số lượng (ước số trong công thức) là 0.
#GIÁ TRỊ!Sai
Lỗi giá trị xảy ra khi bạn sử dụng kiểu dữ liệu không chính xác trong công thức.
Ví dụ, trong ví dụ dưới đây, khi tôi cố gắng thêm một ô có số và ký tự A, nó sẽ báo lỗi giá trị.
Điều này xảy ra bởi vì bạn chỉ có thể thêm các giá trị số, nhưng tôi đã thử thêm các số bằng các ký tự văn bản.
#REF! Sai
Đây được gọi là lỗi tham chiếu và bạn gặp lỗi này khi tham chiếu trong công thức không còn hợp lệ.Điều này có thể xảy ra khi một công thức tham chiếu đến một tham chiếu ô và tham chiếu ô đó không tồn tại (nó xảy ra khi bạn xóa một hàng / cột hoặc trang tính được tham chiếu trong công thức).
Trong ví dụ bên dưới, mặc dù công thức gốc là = A2 / B2, nhưng khi tôi xóa cột B, tất cả các tham chiếu đến nó sẽ trở thành lỗi #REF!Nó cũng cho #REF!lỗi do công thức.
Lỗi #NAME?
Lỗi này có thể do hàm sai chính tả.
Ví dụ, nếu bạn sử dụng nhầm VLOKUP thay vì VLOOKUP, bạn sẽ gặp lỗi tên.
#NUM lỗi
Số lỗi có thể xảy ra nếu bạn cố gắng tính toán một giá trị rất lớn trong Excel.Ví dụ, = 187 ^ 549 sẽ trả về một lỗi số.
Một tình huống khác có thể xảy ra lỗi NUM là khi bạn cung cấp công thức có đối số số không hợp lệ.Ví dụ: nếu bạn đang tính căn bậc hai, nó sẽ trả về lỗi số nếu đó là một số và bạn đưa ra một số âm làm đối số.
Ví dụ, trong trường hợp của hàm căn bậc hai, nếu bạn cung cấp một số âm làm đối số, nó sẽ trả về lỗi số (được hiển thị bên dưới).
Trong khi tôi chỉ trình bày một vài ví dụ ở đây, có thể có nhiều lý do khác gây ra lỗi trong Excel.Khi bạn gặp lỗi trong Excel, bạn không thể để nó ở đó.Nếu dữ liệu được sử dụng nhiều hơn trong tính toán, bạn cần đảm bảo rằng các lỗi được xử lý theo cách chính xác.
Hàm IFERROR trong Excel là một cách tuyệt vời để xử lý tất cả các loại lỗi trong Excel.
Hàm IFERROR trong Excel - tổng quan
Sử dụng hàm IFERROR, bạn có thể chỉ định những gì bạn muốn công thức trả về thay vì một lỗi.Nếu công thức trả về không có lỗi, nó sẽ trả về kết quả của chính nó.
Cú pháp hàm IFERROR
= IFERROR (giá trị, giá trị_if_error)
Thông số đầu vào
- giá trị - Đây là thông số để kiểm tra lỗi.Trong hầu hết các trường hợp, đó là một công thức hoặc một tham chiếu ô.
- giá trị_if_error - Đây là giá trị được trả về khi xảy ra lỗi.Các loại lỗi sau được đánh giá: # N / A, #REF !, # DIV / 0 !, #VALUE !, #NUM !, #NAME ?, và #NULL !.
Ghi chú bổ sung:
- Nếu bạn sử dụng "" làm tham số value_if_error, ô sẽ không hiển thị gì khi xảy ra lỗi.
- Nếu giá trị hoặc tham số value_if_error tham chiếu đến một ô trống, thì hàm IFERROR trong Excel sẽ coi nó như một giá trị chuỗi trống.
- Nếu đối số giá trị là một công thức mảng, hàm IFERROR trả về một mảng kết quả cho mỗi mục trong phạm vi được chỉ định bởi giá trị.
Hàm IFERROR trong Excel - ví dụ
Dưới đây là ba ví dụ về việc sử dụng hàm IFERROR trong Excel.
Ví dụ 1 - Trả về các ô trống thay vì lỗi
Nếu bạn có một hàm có thể trả về lỗi, bạn có thể bọc nó trong một hàm IFERROR và chỉ định khoảng trống làm giá trị trả về khi xảy ra lỗi.
Trong ví dụ được hiển thị bên dưới, kết quả trong D4 là # DIV / 0!Lỗi khi chia cho 0.
Trong trường hợp này, bạn có thể sử dụng công thức sau để trả về khoảng trống thay vì lỗi DIV xấu xí.
= IFERROR (A1 / A2, ””)
Hàm IFERROR này sẽ kiểm tra xem kết quả tính toán có bị lỗi hay không.Nếu vậy, nó chỉ trả về các khoảng trống được chỉ định trong công thức.
Tại đây bạn cũng có thể chỉ định bất kỳ chuỗi hoặc công thức nào khác để hiển thị thay vì để trống.
Ví dụ: công thức bên dưới sẽ trả về văn bản "Lỗi" thay vì một ô trống.
= IFERROR (A1 / A2, "Lỗi")
Lưu ý: Nếu bạn đang sử dụng Excel 2003 hoặc phiên bản cũ hơn, bạn sẽ không tìm thấy hàm IFERROR ở đó.Trong trường hợp này, bạn cần sử dụng hàm IF kết hợp với hàm ISERROR.
Ví dụ 2 - Trả về "Không tìm thấy" khi hàm VLOOKUP không thể tìm thấy giá trị
Khi bạn sử dụng hàm VLOOKUP trong Excel, lỗi # N / A được trả về nếu không tìm thấy giá trị tra cứu trong phạm vi đã chỉ định.
Ví dụ, dưới đây là tập dữ liệu về tên học sinh và điểm số của họ.Tôi đã sử dụng hàm VLOOKUP để lấy điểm của ba học sinh (D2, D3 và D4).
Trong khi công thức VLOOKUP trong ví dụ trên tìm thấy tên của hai học sinh đầu tiên, nó không thể tìm thấy tên của Josh trong danh sách, vì vậy nó trả về lỗi # N / A.
Ở đây chúng ta có thể sử dụng hàm IFERROR để trả về một văn bản trống hoặc một số văn bản có nghĩa thay vì một lỗi.
Dưới đây là công thức trả về "Không tìm thấy" thay vì lỗi.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 12,2,0), "Không tìm thấy")
Lưu ý rằng bạn cũng có thể sử dụng IFNA thay vì IFERROR trong VLOOKUP.Trong khi IFERROR xử lý tất cả các loại giá trị lỗi, IFNA chỉ áp dụng cho lỗi # N / A, không áp dụng cho các lỗi khác.
Ví dụ 3 - Trả về 0 khi bị lỗi
Nếu bạn không chỉ định giá trị mà IFERROR trả về trong trường hợp lỗi, nó sẽ tự động trả về 0.
Ví dụ, nếu tôi chia 100 cho 0 như hình dưới đây, nó sẽ trả về một lỗi.
Tuy nhiên, nếu tôi sử dụng hàm IFERROR bên dưới, nó trả về 0.Lưu ý rằng bạn vẫn cần sử dụng dấu phẩy sau tham số đầu tiên.
Ví dụ 4 - Sử dụng IFERROR lồng nhau với hàm VLOOKUP
Đôi khi khi sử dụng hàm VLOOKUP, bạn có thể cần phải xem bảng phân mảnh cho một mảng.Ví dụ: giả sử bạn có các giao dịch bán hàng trong 2 trang tính riêng biệt và bạn muốn tra cứu số mặt hàng và xem giá trị của nó.
Làm như vậy yêu cầu sử dụng hàm IFERROR lồng nhau với hàm VLOOKUP.
Giả sử bạn có một tập dữ liệu giống như sau:
Trong trường hợp này, để tìm điểm của Grace, bạn cần sử dụng công thức IFERROR lồng nhau sau:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
Việc lồng các công thức này đảm bảo rằng bạn nhận được các giá trị từ một trong hai bảng và xử lý bất kỳ lỗi nào được trả về.
Tuy nhiên, lưu ý rằng nếu các trang tính nằm trên cùng một trang tính, thì trong cuộc sống thực, nó có thể nằm trên một trang tính khác.
