Dưới đây là mọi thứ bạn cần biết về hàm IFERROR là gì và cách sử dụng nó để xử lý lỗi trong Excel.
Hàm IFERROR được thiết kế để xử lý các lỗi do công thức trong Excel. Hàm IFERROR là một công thức logic được sử dụng để phát hiện lỗi trong công thức và trả về một giá trị thay thế hoặc thực hiện một phép tính khác thay cho lỗi. Nếu công thức được lồng bên trong hàm IFERROR dẫn đến lỗi, thì hàm IFERROR sẽ bắt lỗi đó và trả về một giá trị được chỉ định ở vị trí của nó.
Excel hiển thị lỗi bất cứ khi nào có điều gì đó sai với công thức hoặc ô mà nó đang tham chiếu, tuy nhiên, đôi khi lỗi là không thể tránh khỏi. Có một lỗi không chỉ làm cho bảng tính trông xấu mà nếu một công thức khác tham chiếu đến ô chứa lỗi đó, (ví dụ: khi bạn tính tổng một dải ô), nó sẽ gây ra một lỗi khác trong công thức thứ hai. Vì vậy, thay vì hiển thị lỗi, bạn có thể sử dụng hàm IFERROR để trả về một cái gì đó khác như không, trống hoặc văn bản.
Hàm IFERROR là một cách dễ dàng và thanh lịch để xử lý lỗi và giữ cho bảng tính sạch sẽ và công thức trong suốt. Hơn nữa, hàm này có thể được sử dụng trong tất cả các phiên bản Excel từ Excel 2007. Trong hướng dẫn này, chúng ta sẽ tìm hiểu hàm IFERROR là gì và cách sử dụng nó để quản lý lỗi trong Excel.
Xem nhanh
Hàm IFERROR trong Excel là gì?
Hàm IFERROR là một hàm tích hợp trả về một giá trị được chỉ định nếu công thức được lồng vào nhau dẫn đến lỗi.
Cú pháp của hàm IFERROR:
=IFERROR(value,value_if_error)Hàm chỉ có hai đối số:
value(bắt buộc) – Đây là giá trị, biểu thức, công thức hoặc tham chiếu ô sẽ được kiểm tra lỗi. Nếu giá trị hoặc kết quả là lỗi, nó sẽ trả về TRUE nếu không sẽ trả về FALSE.value_if_error(bắt buộc) – Đối số này chỉ định giá trị nào sẽ trả về hoặc phép tính sẽ thực hiện nếu tìm thấy lỗi. Nó có thể là bất kỳ thứ gì một chuỗi văn bản, một giá trị số, một chuỗi trống (ô trống), một công thức, biểu thức hoặc phép tính khác. Nếu bạn chỉ định một chuỗi văn bản, bạn phải đặt nó trong dấu ngoặc kép (””).
Ngoài ra, nếu không tìm thấy lỗi, nó sẽ trả về kết quả thông thường của công thức hoặc biểu thức trong valueđối số.
Bạn có thể sử dụng hàm IFERROR để phát hiện và xử lý các lỗi công thức khác nhau, chẳng hạn như sau:
- #N/A.
- #VALUE!
- #REF!
- #DIV/0!
- #NUM!
- #NAME?
- #NULL!
Thông thường, khi gặp những lỗi này, bạn có thể giải quyết vấn đề bằng cách sửa công thức. Tuy nhiên, đôi khi, những lỗi này là không thể tránh khỏi. Vì vậy, để đối phó với những lỗi này, bạn có thể sử dụng hàm IFERROR để thay thế một giá trị hoặc hàm cho lỗi.
Đừng nhầm lẫn hàm IFERROR với một hàm kiểm tra lỗi tương tự khác, hàm ISERROR được sử dụng để kiểm tra lỗi và trả về TRUE hoặc FALSE đơn giản.
Cách sử dụng hàm IFERROR
Hãy để chúng tôi xem cách bắt lỗi bằng cách sử dụng hàm IFERROR và trả về các loại giá trị khác nhau thay vì lỗi với các ví dụ.
Nếu lỗi, sau đó trả về 0
Nếu bạn gặp lỗi trong công thức của mình, bạn có thể sử dụng hàm IFERROR để trả về ‘0’ thay vì lỗi.
Ví dụ 1:
Ví dụ: trong ví dụ dưới đây, chúng tôi muốn tìm hiểu doanh số bán hàng trung bình (Cột Average Sales) của các mặt hàng khác nhau, vì vậy chúng tôi chia giá trị Doanh số trong cột A cho giá trị Qty (Chất lượng) trong Cột B.
Trong bảng, giá trị Số lượng trong B2 là 0 và giá trị bán hàng trong A5 là lỗi #N/A error (Không khả dụng). Do đó, chúng tôi đã kết thúc với #DIV/0! trong C2 vì khi bất kỳ số nào bị chia cho 0 (số chia), Excel sẽ ném ra lỗi #DIV/0! lỗi. Và khi lỗi #N/A error chia cho 50, nó sẽ tạo ra một lỗi #N/A error khác để đổi lại.
Với sự trợ giúp của hàm IFERROR, kết quả là chúng tôi có thể trả về ‘0’ thay vì hiển thị mã lỗi.
Để xử lý các lỗi, bạn có thể lồng công thức vào bên trong hàm IFERROR:
=IFERROR(A2/B2,0)Trong ví dụ dưới đây, chúng tôi đã kết hợp hàm IFERROR với cùng một công thức và nhập nó vào một cột riêng biệt. Công thức trên được nhập vào ô D2 và được sao chép xuống toàn bộ cột bằng cách sử dụng chốt điền. Như bạn có thể thấy bên dưới, công thức trả về ‘0’ là Giá bán hàng trung bình khi phát hiện lỗi.
Ví dụ 2:
Nếu bạn bỏ qua đối số thứ hai trong hàm IFERROR, công thức sẽ trả về ‘0’ theo mặc định.
=IFERROR(A2/B2,)
Nếu lỗi, sau đó trả về trống
Thay vì thay thế 0 cho các lỗi, bạn chỉ định một chuỗi trống (“) trong đối số value_if_error của IFERROR để trả về một ô trống nếu kết quả là lỗi.
Ví dụ 1:
Để trả về một ô trống nếu tìm thấy lỗi, hãy sử dụng công thức dưới đây:
=IFERROR(A2/B2,"")Trong công thức, chúng tôi đã nhập dấu ngoặc kép (“”) có nghĩa là một chuỗi trống để trả về một ô trống.
Ví dụ 2:
Bạn cũng có thể sử dụng bất kỳ ký hiệu nào để thay thế một lỗi thay vì một ô trống bằng cách sử dụng hàm IFERROR.
Để trả về một ký hiệu hoặc một ký tự nếu tìm thấy lỗi, hãy sử dụng công thức sau:
=IFERROR(A2/B2,"-")
Nếu lỗi, sau đó hiển thị một tin nhắn / chuỗi văn bản
Thay vì hiển thị 0 hoặc trống hoặc một lỗi, bạn có thể hiển thị thông báo của riêng mình bằng cách sử dụng hàm IFERROR. Khi bạn thêm một chuỗi văn bản vào đối số thứ hai của hàm IFERROR, hãy đảm bảo đặt nó trong dấu ngoặc kép.
Ví dụ: công thức này sẽ trả về thông báo “Invalid input” thay vì mã lỗi:
=IFERROR(A2/B2,"Invalid Input")
Nếu lỗi, sau đó thực hiện tính toán
Với hàm IFERROR, bạn có thể thực hiện phép tính thứ hai nếu công thức đầu tiên được đánh giá là lỗi. Bạn có thể đạt được điều này bằng cách nhập công thức thứ hai vào đối số value_if_error của IFERROR.
Ví dụ: chúng tôi muốn tìm doanh số của Kho 1 bằng cách nhân giá của một mặt hàng với số lượng Kho 1. Tuy nhiên, chúng tôi không có dự trữ hai mặt hàng, vì vậy khi giá được nhân với biểu tượng dấu gạch ngang (-), chúng tôi nhận được lỗi Giá trị.
Vì vậy, nếu nhân kho 1 số lượng với giá dẫn đến sai số, hãy nhân số lượng tồn kho 2 với giá để tìm số lượng bán của mỗi mặt hàng:
=IFERROR(B2*C2,B2*D2)Trong công thức trên khi nhân B2 với C2 tạo ra lỗi, hàm IFERROR nhân B2 với D2 và trả về số tiền bán hàng. Nếu công thức đầu tiên bên trong hàm IFERROR không dẫn đến lỗi, nó sẽ trả về kết quả đó.
IFERROR với công thức mảng
Array Formula được sử dụng để thực hiện nhiều phép tính trong một mảng thông qua một công thức duy nhất. Nhưng đôi khi, công thức Mảng cũng có thể dẫn đến lỗi.
Ví dụ: chúng tôi muốn tìm tổng doanh số bán hàng trung bình cho bảng dưới đây bằng cách chia giá trị trong cột A cho một giá trị trong cột B trong mỗi hàng và tính tổng kết quả. Nhưng khi chúng tôi sử dụng công thức mảng dưới đây, nó dẫn đến lỗi:
=SUM(A2:A6/B2:B6,0)Công thức trên chia mỗi giá trị ô trong phạm vi A2: A6 cho giá trị ô tương ứng của phạm vi B2: B6 và trả về mảng kết quả này là mảng {# DIV / 0 !, 33,33,15, #N/A, 133,32}. Sau đó, hàm SUM tổng hợp mảng kết quả để tạo ra tổng doanh số bán hàng trung bình (Cột Total Average Sales) cũng dẫn đến lỗi.
Công thức chia các giá trị trong A2 cho 0 và lỗi trong A5 cho B5 dẫn đến hai lỗi. Và khi hai giá trị lỗi đó được cộng với các giá trị khác, chúng tôi nhận được lỗi #DIV0! error.
Để giải quyết vấn đề này, bạn chỉ cần bao gồm phép toán chia trong hàm IFERROR và sau đó bọc hàm IFERROR bằng hàm SUM:
=SUM(IFERROR(A2:A6/B2:B6,0))Để thực thi công thức mảng, hãy nhấn Ctrl+ Shift+ Enter.
Hàm IFERROR bẫy tất cả các lỗi và thay thế chúng bằng 0, tạo ra mảng kết quả này – {0,33.33,15,0,133.32). Sau đó, hàm SUM cộng tất cả các kết quả và trả về ‘181,65’.
IFERROR với hàm VLOOKUP
Hàm IFERROR thường được kết hợp với hàm Vlookup để trả về thông báo hoặc văn bản tùy chỉnh nếu giá trị tìm kiếm không tồn tại trong tập dữ liệu.
Hàm VLOOKUP tìm kiếm giá trị trong cột đầu tiên của bảng hoặc một dải ô và trích xuất giá trị tương ứng từ cột bên phải. Tuy nhiên, nếu giá trị tra cứu không được tìm thấy trong bảng hoặc viết sai chính tả, bạn sẽ nhận được lỗi #N/A error! erro lại lỗi. Vì vậy, để làm gọn gàng bảng tính của mình, bạn có thể sử dụng hàm IFERROR để trả về thông báo của riêng bạn thay vì các lỗi.
Ví dụ:
Giả sử bạn có dữ liệu dưới đây, nơi bạn muốn tra cứu tên của một sinh viên và trích xuất điểm của họ từ một môn học cụ thể.
Để tra cứu một sinh viên tên là ‘Roger’ (J5) và trả về điểm sinh học của anh ta, chúng ta có thể sử dụng công thức dưới đây:
=VLOOKUP(J5,A2:G15,5,FALSE)Tuy nhiên, ở trên điều này kết thúc với một lỗi vì giá trị tra cứu trong ô J5 ‘Roger’ không được tìm thấy trong bảng. Ở đây, đối số đầu tiên J5 chỉ định giá trị tra cứu, A2:G15 đại diện cho phạm vi, 5 yêu cầu hàm truy xuất các giá trị từ cột thứ năm và FALSE có nghĩa là tìm kiếm giá trị chính xác.
Nếu không tìm thấy giá trị, bạn có thể sử dụng hàm IFERROR để trả về một chuỗi văn bản tùy chỉnh như “Not found” hoặc “wrong input” trong trường hợp có lỗi. Để làm điều đó, bạn phải bao bọc công thức VLOOKUP làm đối số đầu tiên và chuỗi văn bản bạn muốn trả về làm đối số thứ hai trong công thức IFERROR.
Để trả về một chuỗi văn bản tùy chỉnh, nếu hàm VLOOKUP cho kết quả là một lỗi, hãy nhập công thức dưới đây:
=IFERROR(VLOOKUP(J5,A2:G15,5,FALSE),"Not Found")Công thức trên tìm kiếm giá trị ‘Roger’ (J5) trong phạm vi ‘A2: G15’ và tạo ra lỗi #N/A error error vì không tìm thấy giá trị trong bảng. Tuy nhiên, hàm IFERROR bắt lỗi đó và trả về chuỗi được chỉ định “Not Found” ở vị trí của nó.
Nếu hàm VLOOKUP lồng nhau không gây ra lỗi, thì hàm IFERROR sẽ chỉ trả về kết quả chuẩn của hàm VLOOKUP.
Ví dụ: nếu bạn thay đổi giá trị tra cứu thành ‘Robert’ thay vì ‘Roger’, bạn sẽ nhận được kết quả dưới đây:
Ở đây, chúng tôi đã sử dụng cùng một công thức nhưng chỉ thay đổi giá trị đầu vào cho công thức thành ‘Robert’. Kết quả là, chúng tôi đạt điểm ’86’ cho môn Sinh học.
Hàm IFERROR để thực hiện các Vlookup tuần tự
Hàm IFERROR cũng có thể được sử dụng để thực hiện nhiều Vlookup hoặc Vlookup tuần tự dựa trên việc tra cứu trước đó có dẫn đến lỗi hay không.
Giả sử bạn có danh sách điểm của học sinh trong các môn học khác nhau cho Lớp 1 và Lớp 2 và bạn muốn trích xuất điểm kiểm tra của học sinh cụ thể trong một môn học nhất định từ lớp 1 bằng Vlookup.
Tuy nhiên, nếu học sinh bạn đang tìm kiếm không được tìm thấy trong Lớp 1, bạn sẽ gặp lỗi. Vì vậy, nếu bạn lồng hai hàm Vlookup (một cho Lớp 1 và một cho Lớp 2) làm hai đối số của hàm IFERROR, nó sẽ thực hiện thao tác Vlookup thứ 2 và trả về kết quả nếu hàm VLOOKUP đầu tiên bị lỗi.
Để thực hiện các VLOOKUP tuần tự với IFERROR, hãy sử dụng công thức dưới đây:
=IFERROR(VLOOKUP(H17,A3:G12,3,FALSE),VLOOKUP(H17,I3:O13,3,FALSE))Trong công thức trên, hàm Vlookup đầu tiên tìm kiếm giá trị trong H17 (Django) bằng cách tìm kiếm mảng A3: G12 (Lớp 1) và nếu giá trị tra cứu được tìm thấy, nó sẽ trả về giá trị tương ứng trong cột 3 (Điểm Vật lý). Nếu Vlookup đầu tiên không thể tìm thấy giá trị trong Lớp 1, thì nó sẽ chuyển sang Vlookup thứ hai tìm kiếm các giá trị trong phạm vi I3: O13 (Lớp 2) và trả về điểm Vật lý cho sinh viên Django.
Các hàm IFERROR lồng nhau cho Vlookup tuần tự
Tuy nhiên, điều gì sẽ xảy ra nếu Vlookup thứ hai cũng không thể tìm thấy giá trị tra cứu và không thành công trong công thức trên, thì chúng ta sẽ gặp lỗi. Để ngăn điều này, bạn phải tạo một công thức để trả về một thông báo tùy chỉnh, nếu giá trị tra cứu không được tìm thấy trong cả hai mảng.
Bạn cần tạo một công thức IFERROR với hàm VLOOKUP đầu tiên làm đối số đầu tiên (giá trị) và một hàm IFERROR khác làm đối số thứ hai (value_if_error). Sau đó, lồng thông báo VLOOKUP thứ hai và một thông báo value_if_error trong hàm IFERROR thứ hai:
=IFERROR(VLOOKUP(H17,A3:G12,3,FALSE),IFERROR(VLOOKUP(H17,I3:O13,3,FALSE),"Enter a valid name"))Vlookup đầu tiên bên trong IFERROR thứ nhất tìm kiếm giá trị ‘Dan’ trong mảng A3: G12 và nếu nó không thành công, Vlookup thứ hai bên trong IFERROR thứ 2 sẽ tìm kiếm giá trị ‘Dan’ trong phạm vi I3: O3. Nếu nó cũng không thành công, IFERROR thứ 2 trả về thông báo được chỉ định “Enter a valid name”.
IFERROR với hàm INDEX MATCH
Hàm INDEX và MATCH là một hàm tra cứu mạnh mẽ, nâng cao hơn hàm Vlookup, cho phép bạn tìm kiếm một giá trị cụ thể trong một phạm vi ô và trả về một giá trị tại giao điểm hàng và cột được chỉ định.
Cũng giống như với hàm VLOOKUP, nếu hàm INDEX MATCH không thể tìm thấy cần tra cứu trong phạm vi đã cho, nó sẽ trả về thông báo lỗi. Trong những trường hợp như vậy, chúng ta có thể sử dụng hàm IFERROR để tạo một thông báo thay thế hoặc tra cứu giá trị trong một phạm vi khác bằng cách lồng hàm INDEX MATCH.
Ví dụ:
=IFERROR(INDEX(A2:G11,MATCH(D14,A2:A11,0),4),"Not available")Trong công thức trên, hàm MATCH cố gắng tìm vị trí của giá trị tra cứu trong phạm vi A2: A11, nhưng giá trị tra cứu không được tìm thấy trong mảng nên hàm IFERROR trả về chuỗi thay thế đã cho “Not available”.
Hàm IF và IFERROR
Bạn cũng có thể kết hợp hàm IFERROR với hàm IF để tạo công thức nâng cao và xử lý lỗi.
Ví dụ, chúng ta muốn kiểm tra xem học sinh Margaret được đậu hay trượt bằng cách sử dụng hàm IF trong bảng. Nhưng điểm của Margaret không có trong bảng, vì vậy kết quả là chúng tôi nhận được lỗi #N/A error.
Để khắc phục điều này, chúng ta có thể sử dụng tổ hợp hàm IF và IFERROR bên dưới:
=IFERROR(IF(B5>50,"Pass","Fail"),"Score not available")Ở đây, hàm IF kiểm tra xem giá trị trong B5 có lớn hơn 50 hay không và trả về lỗi. Sau đó, hàm IFERROR bẫy lỗi đó và thay vào đó hiển thị chuỗi văn bản thay thế đã cho.
IFERROR so với ISERROR
Hàm ISERROR là một hàm xử lý lỗi khác được sử dụng để kiểm tra xem công thức có đánh giá là lỗi hay không và trả về kết quả là TRUE hoặc FALSE. Hàm IFERROR trả về một giá trị thay thế nếu một công thức dẫn đến lỗi.
Cú pháp của ISERROR:
=ISERROR(value)Hàm ISERROR (điều kiện logic) thường được sử dụng với hàm IF để thực hiện một phép toán hoặc trả về một giá trị nếu điều kiện logic dẫn đến lỗi và phải làm gì nếu không dẫn đến lỗi.
Ví dụ: chúng tôi muốn trích xuất điểm của Janet trong môn Sinh học bằng cách sử dụng công thức IF ISERROR này:
=IF(ISERROR(VLOOKUP(H5,A2:G15,5,FALSE)),"Student not found",VLOOKUP(H5,A2:G15,5,FALSE))Ở đây, ISERROR kiểm tra xem công thức Vlookup lồng nhau có dẫn đến lỗi hay không. Nếu nó là TRUE, thì hàm IF trả về thông báo “Student not found”, nếu không nó sẽ thực hiện lại hàm Vlookup và trả về điểm số ’98’.
Nếu không tìm thấy giá trị tra cứu (sinh viên):
hoặc, nếu tên học sinh được tìm thấy:
IFNA so với IFERROR
IFNA là một hàm kiểm tra lỗi khác tương tự như IFERROR được sử dụng để chỉ bắt lỗi #N/A error trong khi IFERROR xử lý tất cả các loại lỗi. Công thức IFNA hữu ích nếu bạn chỉ muốn trả lại phản hồi tùy chỉnh cho lỗi #N/A error mà không phải cho tất cả các lỗi khác.
Cú pháp của hàm IFNA :
IFNA(value, value_if_na)value– giá trị, biểu thức, công thức hoặc tham chiếu ô cần được kiểm tra lỗi.value_if_na– đại diện cho giá trị nào cần trả về hoặc phép tính sẽ thực hiện trong trường hợp có lỗi #N/A error. Nó có thể là bất kỳ thứ gì một chuỗi văn bản, một giá trị số, chuỗi trống (ô trống), một công thức, biểu thức hoặc phép tính khác.
Giả sử, bạn có bảng báo cáo bán hàng bên dưới và bạn muốn lấy Số lượng bán hàng của một vài mặt hàng từ bảng. Để làm điều đó, chúng tôi đang sử dụng công thức VLOOKUP đơn giản sau:
=VLOOKUP(F2,$A$2:$D$7,4,FALSE)Công thức trên được nhập vào ô G2 và công thức tương tự được áp dụng cho phạm vi G2: G6 bằng cách sử dụng chốt điền. Khi chúng tôi làm điều đó, chúng tôi nhận được lỗi #VALUE! và lỗi #N/A error cho hai mục.
Để xử lý những lỗi này, bạn có thể lồng công thức VLOOKUP ở trên vào bên trong hàm IFERROR:
=IFERROR(VLOOKUP(F2,$A$2:$D$7,4,FALSE),"Not Found")Công thức IFERROR này sẽ xử lý tất cả các lỗi và trả về thông báo “Not Found” bất cứ khi nào tìm thấy lỗi. Công thức trên được áp dụng cho toàn bộ dãy G2: G6.
Nhưng chúng tôi không muốn điều đó, chúng tôi chỉ muốn hiển thị thông báo “Not Found” cho các mục không tồn tại trong bảng (Tomatoes). Điều đó có nghĩa là, chúng ta chỉ cần bắt và xử lý lỗi #N/A error chỉ xuất hiện nếu giá trị tra cứu không có trong bảng tra cứu. Để làm điều đó, chúng ta phải lồng hàm VLOOKUP vào bên trong hàm IFNA.
Để chỉ bắt lỗi #N/A error, hãy sử dụng công thức dưới đây:
=IFNA(VLOOKUP(F2,$A$2:$D$7,4,FALSE),"Not Found")Công thức trên được áp dụng cho phạm vi G2: G6. Nó chỉ kiểm tra hàm VLOOKUP cho lỗi #N/A error và trả về văn bản “Not Found” nếu mục không được tìm thấy trong bảng. Như bạn có thể thấy bên dưới, nó chỉ bắt lỗi #N/A error xảy ra trong ô G6 và trả về thông báo. Và nó không bắt lỗi #VALUE! lỗi xảy ra trong ô G4 và hiển thị lỗi như nó vốn có.
