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:
= 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:
= 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 (“”):
= 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:
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:
= 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:
= 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.
IFERROR INDEX / MATCH
Bạn cũng có thể tra cứu giá trị bằng cách sử dụng hàm INDEX và MATCH trong Excel.
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.
{= SUM ($ B $ 2: $ B $ 4 / $ C $ 2: $ C $ 4)}
Công thức hoạt động tốt cho đến khi phạm vi ước số nhận được một ô trống hoặc các số 0. Do đó, bạn sẽ gặp lại lỗi # DIV / 0 !.
Lần này, bạn có thể sử dụng hàm IFERROR như sau:
{= 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.
= 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:
= 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:
Dim n as long n = 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):
Sub IFERROR_VBA () Dim n As Long, m As Long 'IFERROR n = Application.WorksheetFunction.IfError (Range ("b2"). Value, 0)' No IFERROR m = Range ("b2"). Giá trị End 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:
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:
Sub TestWS () MsgBox DoesWSExist ("test") End Sub Function DoesWSExist (wsName As String) As Boolean Dim ws As Worksheet On Error Resume Next Set ws = Sheets (wsName) 'Nếu Lỗi WS Không tồn tại Nếu Err.Number 0 Thì DoesWSExist = False Else DoesWSExist = True End If On Error GoTo -1 End Function
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.
Bài tập thực hành IFERROR + Ví dụ
Nhấp đúp vào trong một ô để xem công thức của ô đó và chỉnh sửa.
làm:
xóa các ví dụ bảng tính ở dưới cùng ngay bây giờ…
để kiểm tra / suy nghĩ về:
- hình ảnh so với gif ở trên cùng
- gọi chú ý đến các ví dụ tương tác trong bài?
- gắn nhãn lại / định kiểu lại các khối mã…
- TOC ở đầu? xóa chúng khỏi các trang này và thêm [TOC] theo cách thủ công?
- đặt các liên kết VBA “excel, googlesheets” lên đầu và có thể loại bỏ TOC? “Hàm IFERROR khả dụng trong…”
- còn một số hình ảnh khác thì sao… như biểu tượng Excel hoặc google sheet hoặc VBA để làm cho nó trông đẹp hơn
- ya tôi nghĩ hãy xem email nháp nơi logo excel được sử dụng và thêm vào đó…. và làm cho nó trở thành một tiêu đề lạ mắt… tương tự với g sheet và vba!
- làm cho kích thước phông chữ nhỏ hơn trên trang web!
- sửa CSS… TOC, Vùng cú pháp… vv!
thêm nút tải xuống để tải xuống bảng tính + yêu cầu email SAU KHI tải xuống…
hoặc làm điều gì đó giống như các nhà sản xuất mẫu làm… nơi họ yêu cầu bạn hoàn thành một cuộc khảo sát
