Έχετε έναν τύπο Excel που τοποθετεί πολλές συναρτήσεις IF; Όταν φτάσετε στο σημείο με πάρα πολλές ένθετες δηλώσεις IF, πρέπει να δείτε αν το όλο πράγμα θα γίνει απλούστερο με μια απλή λειτουργία VLOOKUP. Έχω δει τους τύπους 1000 χαρακτήρων να απλοποιούνται σε έναν τύπο VLOOKUP 30 χαρακτήρων. Είναι εύκολο να διατηρηθεί όταν πρέπει να προσθέσετε νέες τιμές αργότερα.
Πριν από πολύ καιρό, δούλευα για τον αντιπρόεδρο πωλήσεων στην εργασία. Πάντα μοντελοποιώ κάποιο νέο πρόγραμμα μπόνους ή σχέδιο προμήθειας. Έχω συνηθίσει πολύ να αναθέτω σχέδια με κάθε είδους συνθήκες. Το παρακάτω είναι αρκετά ήσυχο.
Η κανονική προσέγγιση είναι να ξεκινήσετε να δημιουργείτε έναν ένθετο τύπο IF. Ξεκινάτε πάντα είτε από το υψηλό είτε από το χαμηλό άκρο του εύρους. «Εάν οι πωλήσεις υπερβαίνουν τα $ 500K, τότε η έκπτωση είναι 20%. σε διαφορετική περίπτωση… ." Το τρίτο όρισμα της συνάρτησης IF είναι μια εντελώς νέα συνάρτηση IF που ελέγχει για το δεύτερο επίπεδο: Εάν οι πωλήσεις είναι πάνω από 250.000 $, τότε η έκπτωση είναι 15%. σε διαφορετική περίπτωση,… "
Αυτοί οι τύποι γίνονται όλο και περισσότερο καθώς υπάρχουν περισσότερα επίπεδα. Το πιο δύσκολο μέρος μιας τέτοιας φόρμουλας είναι να θυμόμαστε πόσες παρενθέσεις κλείνουν στο τέλος του τύπου.
Συμβουλή μίνι μπόνους
Ταίριασμα των παρενθέσεων
Το Excel περνάει μέσα από μια ποικιλία χρωμάτων για κάθε νέο επίπεδο παρενθέσεων. Ενώ το Excel επαναχρησιμοποιεί τα χρώματα, χρησιμοποιεί μαύρο μόνο για την παρένθεση ανοίγματος και για την αντίστοιχη παρένθεση κλεισίματος. Καθώς ολοκληρώνετε τον παρακάτω τύπο, απλώς συνεχίστε να πληκτρολογείτε παρενθέσεις κλεισίματος έως ότου πληκτρολογήσετε μια μαύρη παρένθεση.
Επιστροφή στη συμβουλή ένθετου τύπου
Εάν χρησιμοποιείτε το Excel 2003, ο τύπος σας πλησιάζει ήδη στο όριο. Τότε, δεν μπορούσατε να τοποθετήσετε περισσότερες από 7 συναρτήσεις IF. Ήταν μια άσχημη μέρα που οι εξουσίες που άλλαξαν το σχέδιο προμηθειών και χρειάστηκε ένας τρόπος για να προσθέσετε μια όγδοη συνάρτηση IF. Σήμερα, μπορείτε να τοποθετήσετε λειτουργίες 64 IF. Δεν πρέπει ποτέ να το κάνετε αυτό, αλλά είναι ωραίο να γνωρίζετε ότι δεν υπάρχει πρόβλημα να τοποθετηθεί 8 ή 9.
Αντί να χρησιμοποιήσετε την ένθετη συνάρτηση IF, δοκιμάστε να χρησιμοποιήσετε την ασυνήθιστη χρήση για τη συνάρτηση VLOOKUP. Όταν το τέταρτο όρισμα του VLOOKUP αλλάζει από False σε True, η συνάρτηση δεν αναζητά πλέον ακριβή αντιστοίχιση.
Λοιπόν, το πρώτο VLOOKUP προσπαθεί να βρει έναν ακριβή αγώνα. Αλλά αν δεν βρεθεί ακριβής αντιστοίχιση, τότε το Excel μπαίνει στη σειρά πολύ λιγότερο από αυτό που ψάχνετε.
Εξετάστε τον παρακάτω πίνακα. Στο κελί C13, το Excel θα αναζητήσει έναν αγώνα για $ 28.355 στον πίνακα. Όταν δεν μπορεί να βρει 28355, το Excel θα επιστρέψει την έκπτωση που σχετίζεται με την τιμή που είναι λίγο μικρότερη. Σε αυτήν την περίπτωση, η έκπτωση 1% για το επίπεδο των 10K $.
Όταν μετατρέπετε τους κανόνες στον πίνακα στο E13: F18, πρέπει να ξεκινήσετε από το μικρότερο επίπεδο και να προχωρήσετε στο υψηλότερο επίπεδο. Παρόλο που δεν είχε δηλωθεί στους κανόνες, εάν κάποιος έχει πωλήσεις κάτω των 10.000 $, η έκπτωση θα είναι 0%. Πρέπει να το προσθέσετε ως την πρώτη σειρά στον πίνακα.
Προσοχή
Όταν χρησιμοποιείτε την έκδοση "True" του VLOOKUP, ο πίνακας σας πρέπει να ταξινομηθεί αύξουσα. Πολλοί άνθρωποι πιστεύουν ότι όλοι οι πίνακες αναζήτησης πρέπει να ταξινομηθούν. Αλλά ένας πίνακας πρέπει να ταξινομηθεί μόνο σε περίπτωση που ταιριάζει κατά προσέγγιση.
Τι γίνεται αν ο διευθυντής σας θέλει μια εντελώς αυτόνομη φόρμουλα και δεν θέλει να δει τον πίνακα μπόνους στα δεξιά; Αφού δημιουργήσετε τον τύπο, μπορείτε να ενσωματώσετε τον πίνακα απευθείας στον τύπο.
Βάλτε τον τύπο σε κατάσταση επεξεργασίας κάνοντας διπλό κλικ στο κελί ή επιλέγοντας το κελί και πατώντας F2.
Χρησιμοποιώντας τον κέρσορα, επιλέξτε ολόκληρο το δεύτερο όρισμα: $ E $ 13: $ F $ 18.
Πατήστε το πλήκτρο F9. Το Excel θα ενσωματώσει τον πίνακα αναζήτησης ως σταθερά πίνακα. Στη σταθερά πίνακα, ένα ερωτηματικό δείχνει μια νέα σειρά και ένα κόμμα υποδεικνύει μια νέα στήλη.
Πατήστε Enter. Αντιγράψτε τον τύπο προς τα κάτω στα άλλα κελιά.
Τώρα μπορείτε να διαγράψετε τον πίνακα. Ο τελικός τύπος φαίνεται παρακάτω.
Ευχαριστώ τον Mike Girvin που με έμαθε σχετικά με τις αντίστοιχες παρενθέσεις. Η τεχνική VLOOKUP προτάθηκε από τους Danny Mac, Boriana Petrova, Andreas Thehos και @mvmcos.
Δες το βίντεο
- Με ένα κλιμακωτό προμήθεια, μπόνους ή πρόγραμμα έκπτωσης, συχνά πρέπει να τοποθετήσετε τις λειτουργίες IF
- Το όριο του Excel 2003 ήταν 7 ένθετες δηλώσεις IF.
- Τώρα μπορείτε να φωλιάσετε 32, αλλά δεν νομίζω ότι πρέπει να φωλιάσετε ποτέ 32
- Πότε θα χρησιμοποιούσατε ποτέ την έκδοση αντιστοίχισης κατά προσέγγιση του VLOOKUP; Αυτή είναι η ώρα.
- Μεταφράστε το πρόγραμμα έκπτωσης σε έναν πίνακα αναζήτησης
- Το VLOOKUP δεν θα βρει την απάντηση στις περισσότερες περιπτώσεις.
- Βάζοντας, True στο τέλος θα πει στο VLOOKUP να βρει την τιμή λίγο λιγότερο.
- Αυτή είναι η μόνη φορά που πρέπει να ταξινομηθεί ο πίνακας VLOOKUP.
- Δεν θέλετε το τραπέζι VLOOKUP στο πλάι; Ενσωματώστε τον στον τύπο.
- F2 για επεξεργασία του τύπου. Επιλέξτε τον πίνακα αναζήτησης. Πατήστε F9. Εισαγω.
Μεταγραφή βίντεο
Μάθετε το Excel από το podcast, επεισόδιο 2030 - Αντικαταστάθηκε Nested IF με VLOOKUP!
Θα μεταδώσω ολόκληρο το βιβλίο σε podcast, κάντε κλικ στο "i" στην επάνω δεξιά γωνία για να μεταβείτε στη λίστα αναπαραγωγής!
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen. Εντάξει, αυτό είναι πολύ συνηθισμένο είτε με ένα πρόγραμμα προμήθειας, είτε με ένα πρόγραμμα έκπτωσης ή ένα πρόγραμμα μπόνους, όπου έχουμε τα επίπεδα, διαφορετικά επίπεδα, έτσι; Εάν είστε πάνω από 10000 $, θα λάβετε έκπτωση 1%, 50000 $ έκπτωση 5%. Πρέπει να είστε προσεκτικοί όταν δημιουργείτε αυτήν την ένθετη δήλωση IF, την ξεκινάτε στο επάνω άκρο αν ψάχνετε για περισσότερα από ή στο κάτω άκρο εάν ψάχνετε λιγότερα από. Έτσι B10> 50000, 20%, αλλιώς άλλο IF, B10> 250000, 25%, και ούτω καθεξής και ούτω καθεξής. Αυτός είναι απλώς ένας μακρύς και περίπλοκος τύπος και αν ο πίνακας σας είναι μεγαλύτερος, πίσω στο Excel 2003, δεν θα μπορούσατε να τοποθετήσετε περισσότερες από 7 δηλώσεις IF, είμαστε σχεδόν κοντά στο όριο εδώ. Μπορείτε να πάτε στο 32 τώρα, δεν νομίζω ότι πρέπει να πάτε ποτέ στο 32, και ακόμα κι αν ουρλιάζετε «Γεια σου, περίμενε,τι γίνεται με τη νέα λειτουργία που μόλις κυκλοφόρησε στο Excel 2016, την κυκλοφορία του Φεβρουαρίου 2016, με τη λειτουργία IFS; " Ναι, σίγουρα, υπάρχουν λιγότερες παρενθέσεις εδώ και 87 χαρακτήρες αντί για 97 χαρακτήρες, είναι ακόμα ένα χάος, ας το ξεφορτωθούμε και να το κάνουμε με ένα VLOOKUP!
Εντάξει, εδώ είναι τα βήματα, ακολουθείτε αυτούς τους κανόνες και τους ενεργοποιείτε. Το πρώτο πράγμα που πρέπει να πούμε είναι, εάν είχατε πωλήσεις 0 $, θα έχετε έκπτωση 0%, εάν έχετε πωλήσεις 10000 $, θα έχετε έκπτωση 1%, εάν έχετε πωλήσεις 50000 $, θα έχετε έκπτωση 5% . 100000 $, 10% έκπτωση, 250000 $, 15% έκπτωση, και τέλος, οτιδήποτε> 500000 $ παίρνει την έκπτωση 20%, εντάξει. Τώρα πολλές φορές, κάθε φορά που μιλάω για το VLOOKUP, λέω ότι κάθε VLOOKUP που δημιουργείτε ποτέ θα τελειώνει σε FALSE και οι άνθρωποι θα πουν "Λοιπόν περιμένετε ένα δευτερόλεπτο, ποια είναι η Αληθινή έκδοση εκεί για ούτως ή άλλως;" Είναι για αυτήν ακριβώς την περίπτωση όπου ψάχνουμε ένα εύρος, οπότε ψάχνουμε αυτήν την τιμή, ένα κανονικό VLOOKUP, φυσικά, 2, το FALSE δεν θα βρει 550000, θα επιστρέψει το # N / A!Έτσι σε αυτήν την πολύ ειδική περίπτωση πρόκειται να αλλάξουμε αυτό το FALSE σε TRUE, και αυτό θα πει στο Excel "Πηγαίνετε να αναζητήσετε το 550000, αν δεν μπορείτε να το βρείτε, δώστε μας την τιμή που είναι λίγο λιγότερο." Μας δίνει λοιπόν το 20%, αυτό κάνει, εντάξει; Και αυτή είναι η μόνη φορά που πρέπει να ταξινομηθεί ο πίνακας VLOOKUP, όλες τις άλλες φορές με, FALSE, μπορεί να είναι όπως θέλετε. Και ναι, θα μπορούσατε να αφήσετε το, TRUE off, αλλά το έβαλα πάντα εκεί για να υπενθυμίσω στον εαυτό μου ότι αυτό είναι ένα από αυτά τα περίεργα εξαιρετικά επικίνδυνα VLOOKUPs και δεν θέλω να αντιγράψω αυτόν τον τύπο κάπου αλλού. Εντάξει, οπότε θα αντιγράψουμε και θα επικολλήσουμε ειδικούς τύπους, εντάξει.καλώς? Και αυτή είναι η μόνη φορά που ο πίνακας VLOOKUP πρέπει να ταξινομηθεί, όλες τις άλλες φορές με, FALSE, μπορεί να είναι όπως θέλετε. Και ναι, θα μπορούσατε να αφήσετε το, TRUE off, αλλά το έβαλα πάντα εκεί για να υπενθυμίσω στον εαυτό μου ότι αυτό είναι ένα από αυτά τα περίεργα εξαιρετικά επικίνδυνα VLOOKUPs και δεν θέλω να αντιγράψω αυτόν τον τύπο κάπου αλλού. Εντάξει, οπότε θα αντιγράψουμε και θα επικολλήσουμε ειδικούς τύπους, εντάξει.καλώς? Και αυτή είναι η μόνη φορά που ο πίνακας VLOOKUP πρέπει να ταξινομηθεί, όλες τις άλλες φορές με, FALSE, μπορεί να είναι όπως θέλετε. Και ναι, θα μπορούσατε να αφήσετε το, TRUE off, αλλά το έβαλα πάντα εκεί για να υπενθυμίσω στον εαυτό μου ότι αυτό είναι ένα από αυτά τα περίεργα εξαιρετικά επικίνδυνα VLOOKUPs και δεν θέλω να αντιγράψω αυτόν τον τύπο κάπου αλλού. Εντάξει, οπότε θα αντιγράψουμε και θα επικολλήσουμε ειδικούς τύπους, εντάξει.
Επόμενο παράπονο: ο διαχειριστής σας δεν θέλει να δει τον πίνακα αναζήτησης, το θέλει "Απλώς ξεφορτωθείτε αυτόν τον πίνακα αναζήτησης." Εντάξει, μπορούμε να το κάνουμε ενσωματώνοντας τον πίνακα αναζήτησης, δείτε αυτό, ένα τέλειο κόλπο που πήρα από τον Mike Girvin στο ExcelIsFun. F2 για να θέσετε τον τύπο σε κατάσταση επεξεργασίας και, στη συνέχεια, επιλέξτε πολύ προσεκτικά μόνο το εύρος για τον πίνακα αναζήτησης. Πατήστε F9, και παίρνει αυτόν τον πίνακα και το βάζει σε ονοματολογία πίνακα και μετά απλώς πατάω Enter όπως αυτό. Αντιγράψτε το κάτω, Επικόλληση ειδικών τύπων και μετά είμαι ελεύθερος να απαλλαγούμε από τον πίνακα αναζήτησης, που όλα συνεχίζουν να δουλεύουν στη σειρά. Αυτό είναι μια τεράστια ταλαιπωρία εάν πρέπει να επιστρέψετε και να το επεξεργαστείτε, πρέπει να το κοιτάξετε με μεγάλη σαφήνεια. Το κόμμα σημαίνει ότι πηγαίνουμε στην επόμενη στήλη, το σύμβολο με ερωτηματικό πηγαίνει στην επόμενη σειρά, εντάξει,αλλά θα μπορούσατε θεωρητικά να επιστρέψετε εκεί και να αλλάξετε αυτόν τον τύπο εάν αλλάξει ένας από τους αριθμούς αλυσίδων.
Εντάξει, οπότε χρησιμοποιώντας ένα VLOOKUP αντί για ένθετη δήλωση IF είναι η συμβουλή # 32 στο δρόμο μας προς 40, "40 Greatest Excel Tips of All Time", μπορείτε να έχετε ολόκληρο το βιβλίο. Κάντε κλικ στο "i" στην επάνω δεξιά γωνία, 10 $ για ένα ηλεκτρονικό βιβλίο, 25 $ για το έντυπο βιβλίο, εντάξει. Έτσι, σήμερα προσπαθούμε να λύσουμε ένα κλιμακωτό μπόνους προμήθειας ή ένα πρόγραμμα έκπτωσης. Τα ένθετα IF είναι πραγματικά συνηθισμένα, προσέξτε, 7 είναι το μόνο που μπορείτε να έχετε στο Excel 2003, σήμερα μπορείτε να έχετε 32, αλλά δεν θα έπρεπε ποτέ να έχετε 32. Επομένως, όταν χρησιμοποιείτε την κατά προσέγγιση έκδοση MATCH του VLOOKUP, αυτό είναι. Πάρτε αυτό το πρόγραμμα έκπτωσης, γυρίστε το ανάποδα, μετατρέψτε το σε έναν πίνακα αναζήτησης ξεκινώντας από τον μικρότερο αριθμό και πηγαίνοντας στον μεγαλύτερο αριθμό. Το VLOOKUP, φυσικά, δεν θα βρει την απάντηση, αλλά αλλάζοντας το VLOOKUP σε, TRUE στο τέλος, θα του πει να βρει την τιμή λίγο λιγότερο,αυτή είναι η μόνη φορά που πρέπει να ταξινομηθεί ο πίνακας. Εάν δεν θέλετε να δείτε αυτόν τον πίνακα εκεί έξω, μπορείτε να τον ενσωματώσετε στον τύπο χρησιμοποιώντας το κόλπο Mike Girvin, F2 για να επεξεργαστείτε τον τύπο, επιλέξτε τον πίνακα αναζήτησης, πατήστε F9 και, στη συνέχεια, Enter.
Εντάξει γεια, θέλω να σας ευχαριστήσω που σταματήσατε, θα σας δούμε την επόμενη φορά για ένα άλλο netcast από!
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2030.xlsx
