Ngăn lỗi công thức trong Excel bằng IFERROR hoặc IFNA. Chúng tốt hơn ISERROR ISERR và ISNA cũ. Tim hiểu thêm ở đây.
Lỗi công thức có thể phổ biến. Nếu bạn có một tập dữ liệu với hàng trăm bản ghi, một số chia cho-0 hoặc một # N / A nhất định sẽ bật lên ngay bây giờ và sau đó.
Trong quá khứ, việc ngăn ngừa sai sót đòi hỏi những nỗ lực rất lớn. Hãy cố ý gật đầu nếu bạn đã từng bị đánh gục =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Ngoài thời gian nhập thực sự dài, giải pháp đó yêu cầu Excel phải thực hiện gấp đôi số hàm VLOOKUP. Đầu tiên, bạn thực hiện một hàm VLOOKUP để xem liệu hàm VLOOKUP có tạo ra lỗi hay không. Sau đó bạn thực hiện lại hàm VLOOKUP tương tự để nhận được kết quả không lỗi.
Excel 2010 đã giới thiệu = IFERROR (Công thức, Giá trị Nếu Lỗi) được cải tiến đáng kể. Tôi biết rằng IFERROR nghe giống các chức năng ISERROR, ISERR, ISNA cũ cũ, nhưng nó hoàn toàn khác.
Đây là một chức năng tuyệt vời: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Nếu bạn có 1.000 VLOOKUP và chỉ 5 trả về # N / A, thì 995 đã hoạt động chỉ yêu cầu một VLOOKUP duy nhất. Chỉ số 5 trả về # N / A cần chuyển sang đối số thứ hai của IFERROR.
Thật kỳ lạ, Excel 2013 đã thêm hàm IFNA (). Nó giống như IFERROR nhưng chỉ tìm lỗi # N / A. Người ta có thể tưởng tượng một tình huống kỳ lạ khi giá trị trong bảng tra cứu được tìm thấy, nhưng câu trả lời kết quả là một phép chia cho 0. Nếu bạn muốn giữ lại lỗi chia cho 0 vì một lý do nào đó, thì IFNA () sẽ thực hiện điều này.
Tất nhiên, người xây dựng bảng tra cứu nên sử dụng IFERROR để ngăn chặn phép chia cho số 0 ngay từ đầu. Trong hình bên dưới, “nm” là mã của người quản lý cũ có nghĩa là “không có ý nghĩa”.
Cảm ơn Justin Fishman, Stephen Gilmer và Excel của Joe.
Xem video
- Trong những ngày xưa, bạn sẽ sử dụng
=IF(ISNA(Formula),0,Formula) - Bắt đầu trong Excel 2010,
=IFERROR(Formula,0) - Nhưng IFERROR xử lý lỗi DIV / 0 giống như lỗi # N / A! sai sót
- Bắt đầu trong Excel 2013, hãy sử dụng
=IFNA(Formula,0)để chỉ phát hiện lỗi # N / A - Cảm ơn Justin Fishman, Stephen Gilmer và Excel của Joe.
Bản ghi video
Học Excel từ podcast, tập 2042 - IFERROR và IFNA!
Tôi sẽ podcasting tất cả các mẹo của tôi từ cuốn sách này, hãy nhấp vào "i" ở góc trên bên phải để truy cập toàn bộ danh sách phát!
Được rồi, hãy quay lại những ngày cũ, Excel 2007 trở về trước, nếu bạn cần ngăn lỗi # N / A! từ công thức VLOOKUP chẳng hạn như công thức này, chúng tôi đã từng làm điều điên rồ này. Lấy tất cả các ký tự của VLOOKUP trộn lên ngoại trừ dấu =, Ctrl + C để đưa vào khay nhớ tạm, = IF (ISNA (, nhấn phím End để đến cuối, nếu là # N / A !, thì chúng ta nói “Không tìm thấy”, dấu phẩy, nếu không chúng ta thực hiện hàm VLOOKUP! Đúng vậy, tôi dán nó vào đó và xem công thức đó dài bao nhiêu, Ctrl + Enter, thêm a) ngay tại đó, Ctrl + Enter, được rồi, thật tuyệt. Được rồi, nhưng vấn đề là, trong khi nó giải quyết vấn đề của # N / A! Mọi công thức đơn lẻ đều thực hiện VOOKUP hai lần. Nó không muốn chúng tôi nói "Này, đây có phải là lỗi không? Ồ, không, không phải đã xảy ra lỗi. Hãy làm lại lần nữa! ”Vì vậy, mất gấp đôi thời gian để thực hiện tất cả các VLOOKUP này. Trong Excel 2010,họ cung cấp cho chúng tôi công thức tuyệt vời, tuyệt vời của IFERROR!
Bây giờ tôi biết điều đó nghe giống như những gì chúng ta đã có trước đây, ISERROR hoặc ISERR, nhưng đây là IFERROR. Và cách nó hoạt động, lấy bất kỳ công thức nào có thể trả lại lỗi, sau đó bạn nói = IFERROR, có công thức, dấu phẩy, phải làm gì nếu đó là lỗi, vì vậy "Không tìm thấy". Được rồi, điều tuyệt vời về điều này là, giả sử bạn có hàng nghìn VLOOKUP để làm và 980 trong số đó hoạt động. Đối với 980, nó chỉ thực hiện hàm VLOOKUP, nó không phải là một lỗi, nó trả về câu trả lời, nó không bao giờ tiếp tục thực hiện hàm VLOOKUP thứ hai, đó là vẻ đẹp của IFERROR. IFERROR của Excel xuất hiện trong Excel 2010, nhưng tất nhiên, một vấn đề thực sự ngu ngốc ở đây là bản thân bảng đang trả về một lỗi. Đúng vậy, ai đó đã có 0 số lượng, Doanh thu / Số lượng và vì vậy chúng tôi nhận được # DIV / 0! ở đó, và lỗi đó cũng sẽ được IFERROR phát hiện là lỗi. Ổn thỏa,và nó sẽ giống như không được tìm thấy, anh ta được tìm thấy, chỉ là ai đã xây dựng chiếc bàn này đã không làm tốt công việc xây dựng chiếc bàn này.
Được rồi, lựa chọn số 1, Excel 2013 hoặc mới hơn, hãy chuyển sang hàm họ đã thêm vào năm 2013 không tìm kiếm tất cả các lỗi, t chỉ tìm kiếm # N / A! Ctrl + Enter, và bây giờ chúng ta sẽ nhận được Không tìm thấy cho ExcelIsFun, nhưng trả về lỗi # DIV / 0! lỗi. Tuy nhiên, thực sự thì những gì chúng ta nên làm là ở đây trong công thức này, chúng ta nên xử lý công thức này để ngăn chặn phép chia đó cho số 0 ngay từ đầu. Vì vậy, = IFERROR, điều này hoạt động cho tất cả các loại, nhấn phím End để đến cuối, dấu phẩy, sau đó phải làm gì? Tôi sẽ luôn đặt số 0 ở đây làm giá trung bình.
Tôi đã từng có một người quản lý, Susanna (?), "Điều đó không chính xác về mặt toán học, bạn phải đặt" nm "vì nó không có ý nghĩa." Chính xác là như vậy, thật là điên rồ khi quản lý của tôi đã khiến tôi phát điên với yêu cầu điên rồ của họ, sso, hãy sao chép nó xuống, Dán Công thức Đặc biệt, alt = "" ES F. Bây giờ chúng tôi nhận được một lỗi "không có ý nghĩa" ở đây, " Không tìm thấy ”được tìm thấy bởi IFERROR và“ không có nghĩa ”thực sự là kết quả của hàm VLOOKUP. Được rồi, vì vậy, có một vài cách khác nhau để thực hiện, có lẽ điều an toàn cần làm ở đây là sử dụng IFNA, miễn là bạn có Excel 2013 và mọi người bạn đang chia sẻ sổ làm việc của mình đều có Excel 2013. Cuốn sách này, cùng với nhiều cuốn sách khác là trong cuốn sách này, nhỏ giọt với các mẹo nhỏ, 200 trang, dễ đọc, đầy đủ màu sắc tuyệt vời, cuốn sách tuyệt vời. Kiểm tra nó, nhấp vào "Tôi" ở góc trên bên phải,bạn có thể mua cuốn sách.
Được rồi, tóm tắt tập: Ngày xưa, chúng ta sử dụng định dạng dài = IF (ISNA (công thức, 0, nếu không thì công thức, công thức được tính hai lần, điều này thật kinh khủng đối với hàm VLOOKUP. Bắt đầu từ Excel 2010, = IFERROR , chỉ cần đặt công thức một lần, dấu phẩy, phải làm gì nếu đó là lỗi. Tuy nhiên, IFERROR xử lý lỗi # DIV / 0! giống như lỗi # N / A!, vì vậy, khởi động Excel 2013, hàm IFNA hoạt động giống như IFERROR, nhưng nó sẽ chỉ phát hiện lỗi # N / A !.
Nhân tiện, mẹo này do các độc giả Justin Fishman, Stephen Gilmer và Excel do Joe gợi ý. Cảm ơn họ đã đề xuất điều này và cảm ơn bạn đã ghé qua, chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ!
Tải tập tin
Tải file mẫu tại đây: Podcast2042.xlsm
