Περιεχόμενα
Στην πράξη, πολύ συχνά υπάρχουν περιπτώσεις που εσείς και εγώ πρέπει να βρούμε την πλησιέστερη τιμή σε ένα σύνολο (πίνακα) σε σχέση με έναν δεδομένο αριθμό. Θα μπορούσε να είναι, για παράδειγμα:
- Υπολογισμός έκπτωσης ανάλογα με τον όγκο.
- Υπολογισμός του ποσού των μπόνους ανάλογα με την υλοποίηση του σχεδίου.
- Υπολογισμός τιμών αποστολής ανάλογα με την απόσταση.
- Επιλογή κατάλληλων δοχείων για εμπορεύματα κ.λπ.
Επιπλέον, μπορεί να απαιτείται στρογγυλοποίηση τόσο προς τα πάνω όσο και προς τα κάτω, ανάλογα με την κατάσταση.
Υπάρχουν διάφοροι τρόποι – προφανείς και όχι τόσο προφανείς – για να λυθεί ένα τέτοιο πρόβλημα. Ας τα δούμε διαδοχικά.
Αρχικά, ας φανταστούμε έναν προμηθευτή που δίνει εκπτώσεις στη χονδρική και το ποσοστό της έκπτωσης εξαρτάται από την ποσότητα των αγαθών που αγοράζονται. Για παράδειγμα, όταν αγοράζετε περισσότερα από 5 τεμάχια, παρέχεται έκπτωση 2% και όταν αγοράζετε από 20 τεμάχια – ήδη 6%, κ.λπ.
Πώς να υπολογίσετε γρήγορα και όμορφα το ποσοστό έκπτωσης κατά την εισαγωγή της ποσότητας των προϊόντων που αγοράσατε;
Μέθοδος 1: Ένθετα IF
Μια μέθοδος από τη σειρά "τι υπάρχει να σκεφτείς - πρέπει να πηδήξεις!". Χρήση ένθετων συναρτήσεων IF (ΑΝ) για να ελέγξετε διαδοχικά εάν η τιμή του κελιού εμπίπτει σε καθένα από τα διαστήματα και να εμφανίσετε μια έκπτωση για το αντίστοιχο εύρος. Αλλά ο τύπος σε αυτή την περίπτωση μπορεί να αποδειχθεί πολύ δυσκίνητος:
Νομίζω ότι είναι προφανές ότι η αποσφαλμάτωση μιας τέτοιας «κούκλας τέρας» ή η προσπάθεια να προσθέσετε μερικές νέες συνθήκες σε αυτήν μετά από κάποιο χρονικό διάστημα είναι διασκεδαστικό.
Επιπλέον, το Microsoft Excel έχει ένα όριο ένθεσης για τη συνάρτηση IF – 7 φορές σε παλαιότερες εκδόσεις και 64 φορές σε νεότερες εκδόσεις. Τι γίνεται αν χρειάζεστε περισσότερα;
Μέθοδος 2. VLOOKUP με προβολή διαστήματος
Αυτή η μέθοδος είναι πολύ πιο συμπαγής. Για να υπολογίσετε το ποσοστό έκπτωσης, χρησιμοποιήστε τη θρυλική συνάρτηση VPR (VLOOKUP) σε κατάσταση κατά προσέγγιση αναζήτησης:
όπου
- B4 – την αξία της ποσότητας των αγαθών στην πρώτη συναλλαγή για την οποία αναζητούμε έκπτωση
- $4 G$: $H $8 – σύνδεσμος προς τον πίνακα εκπτώσεων – χωρίς «κεφαλίδα» και με τις διευθύνσεις καθορισμένες με το σύμβολο $.
- 2 — ο τακτικός αριθμός της στήλης στον εκπτωτικό πίνακα από τον οποίο θέλουμε να πάρουμε την τιμή έκπτωσης
- ΑΛΗΘΙΝΗ – εδώ είναι θαμμένος ο «σκύλος». Αν ως τελευταίο όρισμα συνάρτησης VPR προσδιορίζω ΞΑΠΛΩΜΕΝΗ (ΨΕΥΔΗΣ) ή 0, τότε θα αναζητήσει η συνάρτηση αυστηρή αντιστοιχία στη στήλη ποσότητα (και στην περίπτωσή μας θα δώσει σφάλμα #N/A, αφού δεν υπάρχει τιμή 49 στον εκπτωτικό πίνακα). Αλλά αν αντί ΞΑΠΛΩΜΕΝΗ γράφω ΑΛΗΘΙΝΗ (ΑΛΗΘΗΣ) ή 1, τότε η συνάρτηση δεν θα αναζητήσει το ακριβές, αλλά το πλησιέστερο μικρότερο αξία και θα μας δώσει το ποσοστό έκπτωσης που χρειαζόμαστε.
Το μειονέκτημα αυτής της μεθόδου είναι η ανάγκη ταξινόμησης του πίνακα εκπτώσεων σε αύξουσα σειρά κατά την πρώτη στήλη. Εάν δεν υπάρχει τέτοια ταξινόμηση (ή γίνεται με αντίστροφη σειρά), τότε ο τύπος μας δεν θα λειτουργήσει:
Συνεπώς, αυτή η προσέγγιση μπορεί να χρησιμοποιηθεί μόνο για την εύρεση της πλησιέστερης μικρότερης τιμής. Εάν πρέπει να βρείτε το πλησιέστερο μεγαλύτερο, τότε πρέπει να χρησιμοποιήσετε μια διαφορετική προσέγγιση.
Μέθοδος 3. Εύρεση του πλησιέστερου μεγαλύτερου χρησιμοποιώντας τις συναρτήσεις INDEX και MATCH
Τώρα ας δούμε το πρόβλημά μας από την άλλη πλευρά. Ας υποθέσουμε ότι πουλάμε πολλά μοντέλα βιομηχανικών αντλιών διαφόρων χωρητικότητας. Ο πίνακας πωλήσεων στα αριστερά δείχνει την ισχύ που απαιτείται από τον πελάτη. Πρέπει να επιλέξουμε μια αντλία της πλησιέστερης μέγιστης ή ίσης ισχύος, αλλά όχι μικρότερης από αυτή που απαιτείται από το έργο.
Η συνάρτηση VLOOKUP δεν θα βοηθήσει εδώ, επομένως θα πρέπει να χρησιμοποιήσετε την αναλογική της – μια δέσμη συναρτήσεων INDEX (ΔΕΙΚΤΗΣ) και ΠΕΡΙΣΣΟΤΕΡΟ ΕΚΤΙΘΕΜΕΝΟ (ΑΓΩΝΑΣ):
Εδώ, η συνάρτηση MATCH με το τελευταίο όρισμα -1 λειτουργεί με τον τρόπο εύρεσης της πλησιέστερης μεγαλύτερης τιμής και η συνάρτηση INDEX εξάγει το όνομα μοντέλου που χρειαζόμαστε από τη διπλανή στήλη.
Μέθοδος 4. Νέα συνάρτηση VIEW (XLOOKUP)
Εάν έχετε μια έκδοση του Office 365 με εγκατεστημένες όλες τις ενημερώσεις, τότε αντί για VLOOKUP (VLOOKUP) μπορείτε να χρησιμοποιήσετε το αναλογικό του – τη λειτουργία VIEW (XLOOKUP), το οποίο έχω ήδη αναλύσει αναλυτικά:
Εδώ:
- B4 – την αρχική αξία της ποσότητας του προϊόντος για το οποίο αναζητούμε έκπτωση
- $4G$:$G$8 – το εύρος όπου αναζητούμε αγώνες
- $H$4:$H$8 – το εύρος των αποτελεσμάτων από τα οποία θέλετε να επιστρέψετε την έκπτωση
- τέταρτο επιχείρημα (-1) περιλαμβάνει την αναζήτηση για τον πλησιέστερο μικρότερο αριθμό που θέλουμε αντί για ακριβή αντιστοίχιση.
Τα πλεονεκτήματα αυτής της μεθόδου είναι ότι δεν υπάρχει ανάγκη ταξινόμησης του εκπτωτικού πίνακα και η δυνατότητα αναζήτησης, εάν είναι απαραίτητο, όχι μόνο της πλησιέστερης μικρότερης αλλά και της πλησιέστερης μεγαλύτερης τιμής. Το τελευταίο όρισμα σε αυτή την περίπτωση θα είναι 1.
Αλλά, δυστυχώς, δεν έχουν όλοι αυτήν τη δυνατότητα ακόμα - μόνο ευτυχισμένοι κάτοχοι του Office 365.
Μέθοδος 5. Power Query
Εάν δεν είστε ακόμη εξοικειωμένοι με το ισχυρό και εντελώς δωρεάν πρόσθετο Power Query για το Excel, τότε είστε εδώ. Εάν είστε ήδη εξοικειωμένοι, τότε ας προσπαθήσουμε να το χρησιμοποιήσουμε για να λύσουμε το πρόβλημά μας.
Ας κάνουμε πρώτα κάποιες προπαρασκευαστικές εργασίες:
- Ας μετατρέψουμε τους πίνακες προέλευσης σε δυναμικούς (έξυπνους) χρησιμοποιώντας μια συντόμευση πληκτρολογίου Ctrl+T ή ομάδα Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα).
- Για λόγους σαφήνειας, ας τους δώσουμε ονόματα. ΕΚΠΤΩΣΕΙΣ и Εκπτώσεις αυτί Κατασκευαστής (Σχέδιο).
- Φορτώστε κάθε έναν από τους πίνακες με τη σειρά του στο Power Query χρησιμοποιώντας το κουμπί Από Πίνακας/Εύρος αυτί ημερομηνία (Δεδομένα — Από πίνακα/εύρος). Σε πρόσφατες εκδόσεις του Excel, αυτό το κουμπί έχει μετονομαστεί σε Με φύλλα (Από φύλλο).
- Εάν οι πίνακες έχουν διαφορετικά ονόματα στηλών με ποσότητες, όπως στο παράδειγμά μας ("Ποσότητα αγαθών" και "Ποσότητα από ..."), τότε πρέπει να μετονομαστούν σε Power Query και να ονομαστούν το ίδιο.
- Μετά από αυτό, μπορείτε να επιστρέψετε στο Excel επιλέγοντας την εντολή στο παράθυρο του προγράμματος επεξεργασίας Power Query Αρχική σελίδα — Κλείσιμο και φόρτωση — Κλείσιμο και φόρτωση σε… (Αρχική — Κλείσιμο&Φόρτωση — Κλείσιμο&Φόρτωση σε…) και μετά επιλογή Απλώς δημιουργήστε μια σύνδεση (Δημιουργία μόνο σύνδεσης).
- Τότε αρχίζει το πιο ενδιαφέρον. Εάν έχετε εμπειρία στο Power Query, τότε υποθέτω ότι η περαιτέρω γραμμή σκέψης θα πρέπει να είναι προς την κατεύθυνση της συγχώνευσης αυτών των δύο πινάκων με ένα ερώτημα σύνδεσης (συγχώνευση) a la VLOOKUP, όπως συνέβαινε στην προηγούμενη μέθοδο. Στην πραγματικότητα, θα χρειαστεί να συγχωνευθούμε στη λειτουργία προσθήκης, κάτι που δεν είναι καθόλου προφανές με την πρώτη ματιά. Επιλέξτε στην καρτέλα Excel Δεδομένα – Λήψη δεδομένων – Συνδυασμός αιτημάτων – Προσθήκη (Δεδομένα — Λήψη δεδομένων — Συνδυασμός ερωτημάτων — Προσάρτηση) και μετά τα τραπέζια μας ΕΚΠΤΩΣΕΙΣ и Εκπτώσεις στο παράθυρο που εμφανίζεται:
- Μετά το κλικ OK τα τραπέζια μας θα είναι κολλημένα σε ένα ενιαίο σύνολο – το ένα κάτω από το άλλο. Σημειώστε ότι οι στήλες με την ποσότητα των εμπορευμάτων σε αυτούς τους πίνακες έπεσαν η μία κάτω από την άλλη, επειδή. έχουν το ίδιο όνομα:
- Εάν η αρχική ακολουθία γραμμών στον πίνακα πωλήσεων είναι σημαντική για εσάς, τότε ώστε μετά από όλους τους επόμενους μετασχηματισμούς να μπορείτε να την επαναφέρετε, προσθέστε μια αριθμημένη στήλη στον πίνακά μας χρησιμοποιώντας την εντολή Προσθήκη στήλης – Στήλη ευρετηρίου (Προσθήκη στήλης — Στήλη ευρετηρίου). Εάν η σειρά των γραμμών δεν σας ενδιαφέρει, τότε μπορείτε να παραλείψετε αυτό το βήμα.
- Τώρα, χρησιμοποιώντας την αναπτυσσόμενη λίστα στην κεφαλίδα του πίνακα, ταξινομήστε την ανά στήλη Ποσότητα (τεμάχια) Αύξουσα:
- Και το κύριο κόλπο: κάντε δεξί κλικ στην κεφαλίδα της στήλης Έκπτωση επιλέξτε μια ομάδα Συμπλήρωση – Κάτω (Γέμισμα - Κάτω). Κενά κελιά με μηδέν συμπληρώνεται αυτόματα με τις προηγούμενες τιμές έκπτωσης:
- Απομένει να επαναφέρουμε την αρχική ακολουθία σειρών με ταξινόμηση ανά στήλη Περιεχόμενα (μπορείτε να το διαγράψετε με ασφάλεια αργότερα) και απαλλαγείτε από τις περιττές γραμμές με ένα φίλτρο μηδέν κατά στήλη Κωδικός συναλλαγής:
- Χρήση της συνάρτησης VLOOKUP για αναζήτηση και αναζήτηση δεδομένων
- Η χρήση του VLOOKUP (VLOOKUP) γίνεται με διάκριση πεζών-κεφαλαίων
- XNUMXD VLOOKUP (VLOOKUP)