Πριν από πολύ καιρό, δούλευα για τον αντιπρόεδρο πωλήσεων σε μια εταιρεία. Πάντα μοντελοποιώ κάποιο νέο πρόγραμμα μπόνους ή σχέδιο προμήθειας. Έχω συνηθίσει πολύ να αναθέτω σχέδια με κάθε είδους συνθήκες. Αυτό που φαίνεται σε αυτήν την άκρη είναι αρκετά ήσυχο.
Η κανονική προσέγγιση είναι να ξεκινήσετε να δημιουργείτε έναν ένθετο τύπο IF. Ξεκινάτε πάντα είτε από το υψηλό είτε από το χαμηλό άκρο του εύρους. «Εάν οι πωλήσεις υπερβαίνουν τα $ 500K, τότε η έκπτωση είναι 20%. σε διαφορετική περίπτωση,… ." Το τρίτο όρισμα της συνάρτησης IF είναι μια εντελώς νέα συνάρτηση IF που ελέγχει για το δεύτερο επίπεδο: "Εάν οι πωλήσεις είναι πάνω από 250.000 $, τότε η έκπτωση είναι 15% · διαφορετικά, …."
Αυτοί οι τύποι γίνονται όλο και περισσότερο καθώς υπάρχουν περισσότερα επίπεδα. Το πιο δύσκολο μέρος μιας τέτοιας φόρμουλας είναι να θυμόμαστε πόσες παρενθέσεις κλείνουν στο τέλος του τύπου.
Εάν χρησιμοποιείτε το 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.
