Hướng dẫn này trình bày cách sử dụng Hàm IFERROR trong Excel để bắt lỗi công thức, thay thế chúng bằng một công thức khác, giá trị trống, 0 hoặc một thông báo tùy chỉnh.
Tổng quan về hàm IFERROR
Hàm IFERROR Kiểm tra xem công thức có dẫn đến lỗi không. Nếu FALSE, trả về kết quả ban đầu của công thức. Nếu TRUE, hãy trả về một giá trị được chỉ định khác.
Cú pháp IFERROR
Để sử dụng Hàm IFERROR Trang tính Excel, hãy chọn một ô và nhập:= IFERROR (
Lưu ý cách các đầu vào công thức IFERROR xuất hiện:
Hàm IFERROR Cú pháp và đầu vào:
| 1 | = IFERROR (VALUE, value_if_error) |
giá trị - Một biểu thức. Ví dụ: 4 / A1
value_if_error - Giá trị hoặc Tính toán để thực hiện nếu đầu vào trước đó dẫn đến lỗi. Ví dụ 0 hoặc “” (trống)
Hàm IFERROR là gì?
Hàm IFERROR thuộc danh mục Hàm logic trong Microsoft Excel, bao gồm ISNA, ISERROR và ISERR. Tất cả các chức năng này giúp phát hiện và xử lý các lỗi công thức.
IFERROR cho phép bạn thực hiện một phép tính. Nếu tính toán không làm kết quả là một lỗi, sau đó kết quả tính toán được hiển thị. Nếu tính toán làm dẫn đến lỗi sau đó một phép tính khác được thực hiện (hoặc một giá trị tĩnh như 0, trống hoặc một số văn bản được xuất ra).
Khi nào bạn sử dụng Hàm IFERROR?
- Khi chia số để tránh sai số do chia cho 0
- Khi thực hiện tra cứu để tránh lỗi nếu không tìm thấy giá trị.
- Khi bạn muốn thực hiện một phép tính khác nếu kết quả đầu tiên dẫn đến lỗi (ví dụ: Tra cứu giá trị bằng 2NS bảng nếu nó không được tìm thấy trong bảng đầu tiên)
Các lỗi công thức chưa được xử lý có thể gây ra lỗi trong sổ làm việc của bạn, nhưng các lỗi hiển thị cũng làm cho bảng tính của bạn kém hấp dẫn hơn.
Nếu lỗi thì 0
Hãy xem một ví dụ cơ bản. Dưới đây bạn đang chia hai số. Nếu bạn cố gắng chia cho 0, bạn sẽ nhận được lỗi:
Thay vào đó, hãy chèn phép tính vào trong hàm IFERROR và nếu bạn chia cho 0 thì kết quả là 0 được xuất ra thay vì lỗi:
| 1 | = IFERROR (A2 / B2,0) |
If Error Then Blank
Thay vì đặt lỗi thành 0, bạn có thể đặt chúng thành 'trống' với dấu ngoặc kép (“”):
| 1 | = IFERROR (A2 / B2, "") |
Chúng ta sẽ xem xét thêm các cách sử dụng IFERROR với hàm VLOOKUP…
IFERROR với VLOOKUP
Các hàm tra cứu như VLOOKUP sẽ tạo ra lỗi nếu không tìm thấy giá trị tra cứu. Như được hiển thị ở trên, bạn có thể sử dụng Hàm IFERROR để thay thế các lỗi bằng khoảng trống (“”) hoặc 0:
| 1 | = IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "không tìm thấy") |
Nếu lỗi thì hãy làm gì đó khác
Hàm IFERROR cũng có thể được sử dụng để thực hiện phép tính thứ 2 nếu phép tính thứ nhất dẫn đến lỗi:
| 12 | = IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE),VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE)) |
Tại đây, nếu dữ liệu không được tìm thấy trong ‘LookupTable1’, thay vào đó, hàm VLOOKUP sẽ được thực hiện trên ‘LookupTable2’.
Thêm Ví dụ về Công thức IFERROR
IFERROR lồng nhau - VLOOKUP Nhiều Trang tính
Bạn có thể lồng IFERROR bên trong IFERROR khác để thực hiện 3 phép tính riêng biệt. Ở đây, chúng tôi sẽ sử dụng hai IFERROR để thực hiện các VLOOKUP trên 3 trang tính riêng biệt:
| 123 | = IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE),IFERROR (VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE),VLOOKUP (A2, LookupTable3! $ A $ 2: $ B $ 4,2, FALSE))) |
Chỉ mục / Đối sánh & XLOOKUP
Tất nhiên, IFERROR cũng sẽ hoạt động với các công thức Index / Match và XLOOKUP.
IFERROR XLOOKUP
Hàm XLOOKUP là phiên bản nâng cao của hàm VLOOKUP.
| 1 | = IFERROR (XLOOKUP (A2, LookupTable1! $ A $ 2: $ A $ 4, LookupTable1! $ B $ 2: $ B $ 4), "Không tìm thấy") |
IFERROR INDEX / MATCH
INDEX và MATCH có thể được sử dụng để tạo các VLOOKUP mạnh hơn (tương tự như cách hoạt động của hàm XLOOKUP mới) trong Excel.
| 1 | = IFERROR (INDEX (LookupTable1! $ B $ 2: $ B $ 4, MATCH (A3, LookupTable1! $ A $ 2: $ A $ 4,0)), "Không tìm thấy") |
IFERROR trong Mảng
Công thức mảng trong Excel được sử dụng để thực hiện một số phép tính thông qua một công thức duy nhất. Giả sử có ba cột Năm, Doanh số và Giá trung bình. Bạn có thể tìm ra tổng số lượng bằng công thức sau trong cột E.
| 1 | {= SUM ($ B $ 2: $ B $ 4 / $ C $ 2: $ C $ 4)} |
Công thức hoạt động tốt cho đến khi nó cố gắng chia hết cho 0, dẫn đến lỗi # DIV / 0! lỗi.
Bạn có thể sử dụng hàm IFERROR như thế này để giải quyết lỗi:
| 1 | {= SUM (IFERROR ($ B $ 2: $ B $ 4 / $ C $ 2: $ C $ 4,0))} |
Lưu ý rằng hàm IFERROR phải được lồng bên trong Hàm SUM, nếu không hàm IFERROR sẽ áp dụng cho tổng tổng chứ không phải cho từng mục riêng lẻ trong mảng.
IFNA so với IFERROR
Hàm IFNA hoạt động giống hệt như Hàm IFERROR ngoại trừ hàm IFNA sẽ chỉ bắt lỗi # N / A. Điều này cực kỳ hữu ích khi làm việc với các hàm tra cứu: các lỗi công thức thông thường vẫn sẽ được phát hiện, nhưng không có lỗi nào xuất hiện nếu không tìm thấy giá trị tra cứu.
| 1 | = IFNA (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "Không tìm thấy") |
Nếu ISERROR
Nếu bạn vẫn đang sử dụng Microsoft Excel 2003 hoặc phiên bản cũ hơn, thì bạn có thể thay thế IFERROR bằng sự kết hợp của IF và ISERROR. Đây là một ví dụ ngắn gọn:
| 1 | = IF (ISERROR (A2 / B2), 0, A2 / B2) |
IFERROR trong Google Trang tính
Hàm IFERROR hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
Ví dụ về IFERROR trong VBA
VBA không có IFERROR Fucntion được tích hợp sẵn, nhưng bạn cũng có thể truy cập Hàm IFERROR Excel từ bên trong VBA:
| 12 | Dim n càng lâun = Application.WorksheetFunction.IfError (Giá trị, value_if_error) |
Application.WorksheetFunction cung cấp cho bạn quyền truy cập vào nhiều (không phải tất cả) các hàm Excel trong VBA.
Thông thường IFERROR được sử dụng khi đọc giá trị từ các ô. Nếu một ô có lỗi, VBA có thể đưa ra thông báo lỗi khi cố gắng xử lý giá trị ô. Hãy thử điều này với mã ví dụ bên dưới (trong đó ô B2 có lỗi):
| 1234567891011 | IFERROR_VBA phụ ()Dim n As Long, m As Long'IFERRORn = Application.WorksheetFunction.IfError (Phạm vi ("b2"). Giá trị, 0)'Không có IFERRORm = Phạm vi ("b2"). Giá trịKết thúc Sub |
Mã gán ô B2 cho một biến. Phép gán biến thứ hai gây ra lỗi vì giá trị ô là # N / A, nhưng lần đầu tiên hoạt động tốt do hàm IFERROR.
Bạn cũng có thể sử dụng VBA để tạo công thức chứa Hàm IFERROR:
| 1 | Phạm vi ("C2"). Công thứcR1C1 = "= IFERROR (RC [-2] / RC [-1], 0)" |
Xử lý lỗi trong VBA khác nhiều so với trong Excel. Thông thường, để xử lý lỗi trong VBA, bạn sẽ sử dụng Xử lý lỗi VBA. Xử lý lỗi VBA trông giống như sau:
| 12345678910111213141516171819 | Kiểm tra phụWS ()MsgBox DoesWSExist ("thử nghiệm")Kết thúc SubHàm DoesWSExist (wsName As String) As BooleanDim ws As WorksheetKhi có lỗi Tiếp tục tiếp theoĐặt ws = Sheets (wsName)'Nếu Lỗi WS không tồn tạiIf Err.Number 0 ThìDoesWSExist = SaiKhácDoesWSExist = ĐúngKết thúc nếuVề lỗi GoTo -1Chức năng kết thúc |
Chú ý chúng tôi sử dụng If Err.Number 0 Thì để xác định xem có lỗi xảy ra hay không. Đây là một cách điển hình để bắt lỗi trong VBA. Tuy nhiên, Hàm IFERROR có một số cách sử dụng khi tương tác với các ô Excel.
