Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε τη συνάρτηση IFERROR του Excel για να εντοπίσετε σφάλματα τύπου, αντικαθιστώντας τα με έναν άλλο τύπο, κενή τιμή, 0 ή ένα προσαρμοσμένο μήνυμα.
Επισκόπηση συνάρτησης IFERROR
Η συνάρτηση IFERROR Ελέγχει εάν ένας τύπος οδηγεί σε σφάλμα. Εάν είναι ΛΑΘΟΣ, επιστρέψτε το αρχικό αποτέλεσμα του τύπου. Εάν είναι TRUE, επιστρέψτε μια άλλη καθορισμένη τιμή.
Για να χρησιμοποιήσετε τη συνάρτηση φύλλου εργασίας IFERROR Excel, επιλέξτε ένα κελί και πληκτρολογήστε:
(Παρατηρήστε πώς εμφανίζονται οι εισαγωγές τύπου)
Σύνταξη και είσοδοι λειτουργίας IFERROR:
= IFERROR (VALUE, value_if_error)
αξία - Μια έκφραση. Παράδειγμα: 4/Α1
value_if_error - Τιμή ή Υπολογισμός για εκτέλεση εάν η προηγούμενη εισαγωγή καταλήξει σε σφάλμα. Παράδειγμα 0 ή "" (κενό)
Τι είναι η συνάρτηση IFERROR;
Η συνάρτηση IFERROR εμπίπτει στην κατηγορία Λογικές λειτουργίες στο Microsoft Excel, η οποία περιλαμβάνει ISNA, ISERROR και ISERR. Όλες αυτές οι λειτουργίες βοηθούν στον εντοπισμό και τον χειρισμό σφαλμάτων τύπου.
Το IFERROR σάς επιτρέπει να εκτελέσετε έναν υπολογισμό. Αν ο υπολογισμός δεν προκύψει σφάλμα, τότε εμφανίζεται το αποτέλεσμα υπολογισμού. Αν ο υπολογισμός κάνει οδηγεί σε σφάλμα και στη συνέχεια πραγματοποιείται άλλος υπολογισμός (ή στατική τιμή όπως 0, κενό ή εξάγεται κάποιο κείμενο).
Πότε θα χρησιμοποιούσατε τη συνάρτηση IFERROR;
- Κατά τη διαίρεση των αριθμών για την αποφυγή σφαλμάτων που προκαλούνται από τη διαίρεση με το 0
- Κατά την εκτέλεση αναζητήσεων για την αποφυγή σφαλμάτων εάν η τιμή δεν βρεθεί.
- Όταν θέλετε να εκτελέσετε έναν άλλο υπολογισμό εάν ο πρώτος καταλήξει σε σφάλμα (π.χ. Αναζητήστε μια τιμή σε ένα 2nd πίνακας εάν δεν βρίσκεται στον πρώτο πίνακα)
Τα σφάλματα τύπου χωρίς χειρισμό μπορούν να προκαλέσουν σφάλματα στο βιβλίο εργασίας σας, αλλά τα ορατά σφάλματα καθιστούν το υπολογιστικό φύλλο σας λιγότερο εμφανώς ελκυστικό.
Αν Σφάλμα Τότε 0
Ας δούμε ένα βασικό παράδειγμα. Παρακάτω διαιρείτε δύο αριθμούς. Εάν επιχειρήσετε να διαιρέσετε με το μηδέν, θα λάβετε ένα σφάλμα:
Αντ 'αυτού, εισαγάγετε τον υπολογισμό στη συνάρτηση IFERROR και εάν διαιρέσετε με το μηδέν, ένα 0 εξάγεται αντί για σφάλμα:
= IFERROR (A2/B2,0)
Αν Σφάλμα Τότε Κενό
Αντί να ορίσετε τα σφάλματα στο 0, μπορείτε να τα ορίσετε σε "κενά" με διπλές παραθέσεις (""):
= IFERROR (A2/B2, "")
Θα εξετάσουμε περισσότερες χρήσεις IFERROR με τη λειτουργία VLOOKUP…
IFERROR με VLOOKUP
Οι λειτουργίες αναζήτησης όπως το VLOOKUP θα δημιουργήσουν σφάλματα εάν δεν βρεθεί η τιμή αναζήτησης. Όπως φαίνεται παραπάνω, μπορείτε να χρησιμοποιήσετε τη συνάρτηση IFERROR για να αντικαταστήσετε τα σφάλματα με κενά ("") ή 0s:
= IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "δεν βρέθηκε")
Αν Σφάλμα Τότε Κάνε Κάτι Άλλο
Η συνάρτηση IFERROR μπορεί επίσης να χρησιμοποιηθεί για την εκτέλεση 2ου υπολογισμού εάν ο 1ος υπολογισμός καταλήξει σε σφάλμα:
= IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE))
Εδώ αν τα δεδομένα δεν βρίσκονται στο ‘LookupTable1’, ένα VLOOKUP εκτελείται στο ‘LookupTable2’.
Περισσότερα παραδείγματα τύπου IFERROR
Nested IFERROR - Πολλαπλά φύλλα VLOOKUP
Μπορείτε να τοποθετήσετε ένα IFERROR μέσα σε ένα άλλο IFERROR για να εκτελέσετε 3 ξεχωριστούς υπολογισμούς. Εδώ θα χρησιμοποιήσουμε δύο IFERROR για να εκτελέσουμε VLOOKUPs σε 3 ξεχωριστά φύλλα εργασίας:
= 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)))
Ευρετήριο / Αντιστοίχιση & XLOOKUP
Φυσικά, το IFERROR θα λειτουργήσει επίσης με τύπους Index / Match και XLOOKUP επίσης.
IFERROR XLOOKUP
Η λειτουργία XLOOKUP είναι μια προηγμένη έκδοση της λειτουργίας VLOOKUP.
= IFERROR (XLOOKUP (A2, LookupTable1! $ A $ 2: $ A $ 4, LookupTable1! $ B $ 2: $ B $ 4), "Not Found")
IFERROR INDEX / MATCH
INDEX και MATCH μπορούν να χρησιμοποιηθούν για τη δημιουργία ισχυρότερων VLOOKUP (παρόμοια με τον τρόπο λειτουργίας της νέας λειτουργίας XLOOKUP) στο Excel.
= IFERROR (INDEX (LookupTable1! $ B $ 2: $ B $ 4, MATCH (A3, LookupTable1! $ A $ 2: $ A $ 4,0)), "Not Found")
IFERROR σε Arrays
Οι τύποι πίνακα στο Excel χρησιμοποιούνται για την εκτέλεση πολλών υπολογισμών μέσω ενός μόνο τύπου. Ας υποθέσουμε ότι υπάρχουν τρεις στήλες του έτους, πωλήσεις και μέση τιμή. Μπορείτε να μάθετε τη συνολική ποσότητα με τον ακόλουθο τύπο στη στήλη Ε.
{= SUM ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4)}
Ο τύπος αποδίδει καλά μέχρι να επιχειρήσει να διαιρεθεί με το μηδέν, με αποτέλεσμα το #DIV/0! λάθος.
Μπορείτε να χρησιμοποιήσετε τη συνάρτηση IFERROR όπως αυτή για να επιλύσετε το σφάλμα:
{= SUM (IFERROR ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4,0))}
Παρατηρήστε ότι η συνάρτηση IFERROR πρέπει να τοποθετηθεί μέσα στη συνάρτηση SUM, διαφορετικά το IFERROR θα εφαρμοστεί στο σύνολο και όχι σε κάθε μεμονωμένο στοιχείο του πίνακα.
IFNA εναντίον IFERROR
Η συνάρτηση IFNA λειτουργεί ακριβώς το ίδιο με τη συνάρτηση IFERROR, εκτός από τη συνάρτηση IFNA που θα εντοπίζει μόνο σφάλματα #N/A. Αυτό είναι εξαιρετικά χρήσιμο όταν εργάζεστε με λειτουργίες αναζήτησης: τα κανονικά σφάλματα τύπου εξακολουθούν να ανιχνεύονται, αλλά δεν θα εμφανιστεί σφάλμα εάν δεν βρεθεί η τιμή αναζήτησης.
= IFNA (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "Δεν βρέθηκε")
Αν ISERROR
Εάν εξακολουθείτε να χρησιμοποιείτε το Microsoft Excel 2003 ή παλαιότερη έκδοση, τότε μπορείτε να αντικαταστήσετε το IFERROR με συνδυασμό IF και ISERROR. Εδώ είναι ένα σύντομο παράδειγμα:
= IF (ISERROR (A2/B2), 0, A2/B2)
IFERROR στα Υπολογιστικά φύλλα Google
Η συνάρτηση IFERROR λειτουργεί ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel:
IFERROR Παραδείγματα σε VBA
Το VBA δεν διαθέτει ενσωματωμένο IFERROR Fucntion, αλλά μπορείτε επίσης να έχετε πρόσβαση στη συνάρτηση IFERROR του Excel μέσα από το VBA:
Dim n όσο n = Application.WorksheetFunction.IfError (Value, value_if_error)
Application.WorksheetFunction σας δίνει πρόσβαση σε πολλές (όχι όλες) λειτουργίες Excel στο VBA.
Συνήθως το IFERROR χρησιμοποιείται κατά την ανάγνωση τιμών από κελιά. Εάν ένα κελί περιέχει σφάλμα, το VBA ενδέχεται να ρίξει ένα μήνυμα σφάλματος κατά την προσπάθεια επεξεργασίας της τιμής του κελιού. Δοκιμάστε το με τον παρακάτω κώδικα (όπου το κελί Β2 περιέχει σφάλμα):
Sub IFERROR_VBA () Dim n As Long, m As Long 'IFERROR n = Application.WorksheetFunction.IfError (Range ("b2"). Value, 0)' No IFERROR m = Range ("b2"). Value End End
Ο κώδικας εκχωρεί το κελί Β2 σε μια μεταβλητή. Η δεύτερη εκχώρηση μεταβλητής ρίχνει ένα σφάλμα επειδή η τιμή κελιού είναι #N/A, αλλά η πρώτη λειτουργεί καλά λόγω της συνάρτησης IFERROR.
Μπορείτε επίσης να χρησιμοποιήσετε το VBA για να δημιουργήσετε έναν τύπο που περιέχει τη συνάρτηση IFERROR:
Εύρος ("C2"). FormulaR1C1 = "= IFERROR (RC [-2]/RC [-1], 0)"
Ο χειρισμός σφαλμάτων στο VBA είναι πολύ διαφορετικός από ό, τι στο Excel. Συνήθως, για τον χειρισμό σφαλμάτων στο VBA, θα χρησιμοποιήσετε το VBA Error Handling. Ο χειρισμός σφαλμάτων VBA μοιάζει με αυτό:
Sub TestWS () MsgBox DoesWSExist ("test") End Sub Function DoesWSExist (wsName As String) As Boolean Dim ws As Works Sheet On Error Resume Next set ws = Sheets (wsName) 'If Error WS Δεν υπάρχει Εάν Err.Number 0 then DoesWSExist = Λάθος άλλο DoWSExist = True End If On Error GoTo -1 End Function
Σημείωση που χρησιμοποιούμε Εάν Err.Number 0 Τότε για να προσδιορίσετε εάν έχει συμβεί σφάλμα. Αυτός είναι ένας τυπικός τρόπος για να εντοπίσετε σφάλματα στο VBA. Ωστόσο, η συνάρτηση IFERROR έχει ορισμένες χρήσεις κατά την αλληλεπίδραση με κελιά Excel.
