Table of Contents
IFFERROR là một hàm khá đặc biệt, không được sử dụng nhiều trong Excel và Google Sheets. Với nhiều bạn, có thể vẫn chưa biết được IFERROR là hàm gì, cách sử dụng cũng như kết hợp thêm với các hàm khác. Vậy để hiểu rõ hơn, hãy cùng GhienCongNghe tham khảo bài viết sau đây nhé.
IFERROR là hàm gì?
Hàm IFERROR trong Excel được thiết kế để làm đòn bẫy và quản lý các lỗi trong công thức và phép tính. Cụ thể hơn, IFERROR kiểm tra một công thức và nếu nó cho kết quả là một lỗi, sẽ trả về một giá trị khác mà bạn chỉ định; nếu không, trả về kết quả của công thức.
Cú pháp của hàm IFERROR trong Excel như sau:
=IFERROR(value, value_if_error)
Ở đâu:
- Value (bắt buộc) – những gì để kiểm tra lỗi. Nó có thể là một công thức, biểu thức, giá trị hoặc tham chiếu ô.
- Value_if_error (bắt buộc) – giá trị trả về nếu tìm thấy lỗi. Nó có thể là một chuỗi trống (ô trống), tin nhắn văn bản, giá trị số, một công thức hoặc phép tính khác.
Ví dụ: khi chia hai cột số, bạn có thể gặp một loạt các lỗi khác nhau nếu một trong các cột chứa ô trống, số không hoặc văn bản.
Để ngăn điều đó xảy ra, hãy sử dụng hàm IFERROR để bắt và xử lý lỗi theo cách bạn muốn.
Nếu lỗi, sau đó để trống. Sau đó, cung cấp một chuỗi trống (“) cho đối số value_if_error để trả về một ô trống nếu tìm thấy lỗi:
=IFERROR(A2/B2, “”)
Nếu lỗi, sau đó bạn muốn thể hiển thị thông báo của riêng mình thay vì ký hiệu lỗi tiêu chuẩn của Excel:
=IFERROR(A2/B2, “Error in calculation”)
5 điều bạn nên biết về hàm IFERROR
- Hàm IFERROR trong Excel xử lý tất cả các loại lỗi bao gồm #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, và #VALUE!.
- Tùy thuộc vào nội dung của đối số value_if_error , IFERROR có thể thay thế lỗi bằng tin nhắn văn bản tùy chỉnh, số, ngày tháng hoặc giá trị logic của bạn, kết quả của một công thức khác hoặc một chuỗi trống (ô trống).
- Nếu đối số giá trị là một ô trống, nó được coi là một chuỗi trống (” “) nhưng không phải là một lỗi.
- IFERROR đã được giới thiệu trong Excel 2007 và có sẵn trong tất cả các phiên bản tiếp theo của Excel 2010, Excel 2013 và Excel 2016.
» Tìm hiểu: Null là gì? Null trong lập trình khác gì với lỗi trên Excel?
Ví dụ ứng dụng hàm IFERROR
Các ví dụ sau đây cho thấy cách sử dụng hàm IFERROR trong Excel và Google Sheets kết hợp với các hàm khác để hoàn thành các tác vụ phức tạp hơn.
IFERROR với VLOOKUP
Một trong những cách sử dụng phổ biến nhất của hàm IFERROR là cho người dùng biết rằng giá trị họ đang tìm kiếm không tồn tại trong tập dữ liệu. Đối với điều này, bạn đặt một công thức VLOOKUP trong IFERROR như thế này:
IFERROR(VLOOKUP(…),”Not found”)
IFERROR (VLOOKUP ( … ), “Not found”)
Nếu giá trị tra cứu không có trong bảng bạn đang tìm, thì công thức VLOOKUP thông thường sẽ trả về lỗi #N/A:
Đối với người dùng của bạn, hãy bọc hàm VLOOKUP trong IFERROR và hiển thị thông báo thân thiện với người dùng và nhiều thông tin hơn:
=IFERROR(VLOOKUP(A2, ‘Lookup table’!$A$2:$B$4, 2,FALSE), “Not found”)
Ảnh chụp màn hình bên dưới hiển thị công thức IFERROR này trong Excel:
Nếu bạn chỉ muốn mắc lỗi # N / A nhưng không phải tất cả các lỗi, hãy sử dụng hàm IFNA thay vì IFERROR.
Các hàm IFERROR lồng nhau để thực hiện các VLOOKUP tuần tự
Trong các tình huống khi bạn cần thực hiện nhiều VLOOKUP dựa trên việc VLOOKUP trước đó thành công hay thất bại, bạn có thể lồng hai hoặc nhiều hàm IFERROR vào một hàm khác.
Giả sử bạn có một số báo cáo bán hàng từ các chi nhánh khu vực của công ty và bạn muốn nhận được số tiền cho một ID đơn đặt hàng nhất định. Với A2 là giá trị tra cứu trong trang tính hiện tại và A2: B5 là phạm vi tra cứu trong 3 trang tính tra cứu (Báo cáo 1, Báo cáo 2 và Báo cáo 3), công thức như sau:
=IFERROR(VLOOKUP(A2,’Report 1′!A2:B5,2,0),IFERROR(VLOOKUP(A2,’Report 2′!A2:B5,2,0),IFERROR(VLOOKUP(A2,’Report 3′!A2:B5,2,0),”not found”)))
Kết quả sẽ giống như sau:
IFERROR trong công thức mảng
Như bạn có thể biết, công thức mảng trong Excel có nghĩa là thực hiện nhiều phép tính trong một công thức duy nhất. Nếu bạn cung cấp công thức mảng hoặc biểu thức dẫn đến một mảng trong đối số giá trị của hàm IFERROR, nó sẽ trả về một mảng giá trị cho mỗi ô trong phạm vi được chỉ định. Ví dụ dưới đây cho thấy các chi tiết.
Giả sử, bạn có Tổng số trong cột B và Giá trong cột C và bạn muốn tính Tổng số lượng . Điều này có thể được thực hiện bằng cách sử dụng công thức mảng sau , công thức này chia mỗi ô trong phạm vi B2: B4 cho ô tương ứng của phạm vi C2: C4, rồi cộng các kết quả:
=SUM($B$2:$B$4/$C$2:$C$4)
Công thức hoạt động tốt miễn là phạm vi số chia không có số 0 hoặc ô trống. Nếu có ít nhất một giá trị 0 hoặc ô trống, giá trị #DIV/0! lỗi được trả lại:
Để khắc phục lỗi đó, chỉ cần thực hiện phép chia trong hàm IFERROR:
=SUM(IFERROR($B$2:$B$4/$C$2:$C$4,0))
Công thức thực hiện là chia một giá trị trong cột B cho một giá trị trong cột C trong mỗi hàng (100/2, 200/5 và 0/0) và trả về mảng kết quả {50; 40; # DIV / 0!}. Hàm IFERROR bắt tất cả # DIV / 0! lỗi và thay thế chúng bằng số không. Và sau đó, hàm SUM cộng các giá trị trong mảng kết quả {50; 40; 0} và xuất ra kết quả cuối cùng (50 + 40 = 90).
Ghi chú: Hãy nhớ rằng công thức mảng phải được hoàn thành bằng cách nhấn Ctrl + Shift + Enter – đường tắt.
IFERROR so với IF ISERROR
Bây giờ bạn đã biết việc sử dụng hàm IFERROR trong Excel dễ dàng như thế nào, bạn có thể thắc mắc tại sao một số người vẫn nghiêng về việc sử dụng kết hợp IFERROR. Nó có lợi thế gì so với IFERROR? Không ai. Trong những ngày xa xưa của Excel 2003 trở xuống khi IFERROR không tồn tại, IF ISERROR là cách duy nhất có thể để bẫy lỗi. Trong Excel 2007 trở lên, hơi phức tạp hơn một chút để đạt được kết quả tương tự.
Ví dụ: để bắt lỗi VLOOKUP, bạn có thể sử dụng một trong các công thức dưới đây.
Trong Excel 2007 – Excel 2016:
IFERROR(VLOOKUP(…), “Not found”)
Trong tất cả các phiên bản Excel:
IF(ISERROR(VLOOKUP(…)), “Not found”, VLOOKUP(…))
Lưu ý rằng trong công thức VLOOKUP IF ISERROR, bạn phải VLOOKUP hai lần. Bằng tiếng Anh đơn giản, công thức có thể được đọc như sau: Nếu kết quả VLOOKUP bị lỗi, hãy trả về “Not found”, nếu không thì xuất kết quả VLOOKUP.
Và đây là một ví dụ thực tế về công thức Excel If Iserror VLOOKUP:
=IF(ISERROR(VLOOKUP(D2, A2:B5,2,FALSE)),”Not found”, VLOOKUP(D2, A2:B5,2,FALSE ))
IFERROR so với IFNA
Được giới thiệu cùng với Excel 2013, IFNA là một chức năng nữa để kiểm tra lỗi của công thức. Cú pháp của nó tương tự như IFERROR:
IFNA(value, value_if_na)
- IFNA khác IFERROR ở điểm nào? – Hàm IFNA chỉ bắt lỗi #N/A trong khi hàm IFERROR xử lý tất cả các loại lỗi.
- Bạn có thể muốn sử dụng IFNA trong những trường hợp nào? – Ví dụ: khi làm việc với dữ liệu quan trọng hoặc nhạy cảm, bạn có thể muốn được cảnh báo về các lỗi có thể xảy ra trong tập dữ liệu của mình và thông báo lỗi Excel tiêu chuẩn với ký hiệu “#” có thể là các chỉ báo trực quan sinh động.
Hãy xem cách bạn có thể tạo công thức hiển thị thông báo “Not found” thay vì lỗi #N/A, xuất hiện khi giá trị tra cứu không có trong tập dữ liệu, nhưng lại khiến bạn chú ý đến các lỗi Excel khác.
Giả sử bạn muốn kéo Qty. từ bảng tra cứu sang bảng tóm tắt như trong ảnh chụp màn hình bên dưới. Sử dụng công thức IFERROR VLOOKUP trong Excel sẽ tạo ra kết quả đẹp mắt về mặt thẩm mỹ, điều này không chính xác về mặt kỹ thuật vì Lemons tồn tại trong bảng tra cứu:
Để bắt #N/A nhưng hiển thị lỗi #DIV/0, hãy sử dụng hàm IFNA trong Excel 2013 và Excel 2016:
=IFNA(VLOOKUP(F3,$A$3:$D$6,4,FALSE), “Not found”)
Hoặc, kết hợp IF ISNA trong Excel 2010 và các phiên bản trước đó:
=IF(ISNA(VLOOKUP(F3,$A$3:$D$6,4,FALSE)),”Not found”, VLOOKUP(F3,$A$3:$D$6,4,FALSE))
Cú pháp của công thức IFNA VLOOKUP và IF ISNA VLOOKUP tương tự như công thức IFERROR VLOOKUP và IF ISERROR VLOOKUP đã thảo luận trước đó.
Như được hiển thị trong ảnh chụp màn hình bên dưới, công thức Ifna VLOOKUP chỉ trả về “Not found” cho mục không có trong bảng tra cứu (Peaches). Đối với Lemons , nó hiển thị #DIV/0! chỉ ra rằng bảng tra cứu của chúng tôi chứa lỗi chia cho 0:
Các phương pháp hay nhất để sử dụng hàm IFERROR
Bây giờ bạn đã biết rằng hàm IFERROR là cách dễ nhất để bắt lỗi trong Excel và che dấu chúng bằng các ô trống, giá trị 0 hoặc thông báo tùy chỉnh của riêng bạn. Tuy nhiên, điều đó không có nghĩa là bạn nên kết thúc từng công thức với việc xử lý lỗi. Những đề nghị đơn giản sau đây có thể giúp bạn giữ cân bằng.
- Đừng mắc lỗi mà không có lý do.
- Gói phần nhỏ nhất có thể của một công thức trong IFERROR.
- Để chỉ xử lý các lỗi cụ thể, hãy sử dụng chức năng xử lý lỗi với phạm vi nhỏ hơn:
– IFNA hoặc IF ISNA để chỉ bắt lỗi #N/A.
– ISERR để bắt tất cả các lỗi ngoại trừ lỗi #N/A.
Xem thêm:
- Cách Wrap Text trong Google Sheets
- Cách tô màu dòng xen kẽ trong Google Sheets
- Cách tạo bảng trong Google Sheet
Trên đây GhienCongNghe đã giải đáp cho bạn IFERROR là hàm gì. Nếu còn bất kỳ thắc mắc nào hãy Comment bên dưới nhé. Đừng quên Like và Share nếu bạn thấy bài viết này có ích và đó cũng là động lực cho GhienCongNghe đem đến những nội dung bổ ích hơn trong tương lai.
Tham khảo Ablebits
