Ας υποθέσουμε ότι έχετε την ακόλουθη αναφορά για τους αντιπροσώπους πωλήσεων:

Από αυτό πρέπει να μάθετε πόσο μολύβιαπωλείται από αντιπρόσωπο πωλήσεων Ιβάνοφ V Ιανουάριος.

ΠΡΟΒΛΗΜΑ: Πώς να συνοψίσετε τα δεδομένα σύμφωνα με διάφορα κριτήρια;

ΛΥΣΗ: Μέθοδος 1:

BDSUMM(A1:G16;F1;I1:K2)

Στην αγγλική έκδοση:

DSUM(A1:G16,F1,I1:K2)

ΠΩΣ ΔΟΥΛΕΥΕΙ:

Από τη βάση δεδομένων που καθορίσαμε A1: G16λειτουργία BDSUMMανακτά και συνοψίζει δεδομένα στηλών Ποσότητα(διαφωνία " Πεδίο" = F1) σύμφωνα με αυτά που καθορίζονται στα κελιά Ι1:Κ2 (Πωλητής = Ιβάνοφ; Προϊόντα = Μολύβια;Μήνας = Ιανουάριος) κριτήρια.

Μειονεκτήματα: Η λίστα των κριτηρίων πρέπει να βρίσκεται στο φύλλο.

ΣΗΜΕΙΩΣΕΙΣ: Ο αριθμός των κριτηρίων άθροισης περιορίζεται από τη μνήμη RAM.

ΠΕΡΙΟΧΗ ΕΦΑΡΜΟΓΗΣ: Οποιαδήποτε έκδοση του Excel

Μέθοδος 2:

SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

Στην αγγλική έκδοση:

SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

ΠΩΣ ΔΟΥΛΕΥΕΙ:

Η συνάρτηση SUMPRODUCT σχηματίζει πίνακες τιμών TRUE και FALSE, σύμφωνα με τα επιλεγμένα κριτήρια, στη μνήμη Excel.

Εάν οι υπολογισμοί πραγματοποιήθηκαν στα κελιά του φύλλου (για λόγους σαφήνειας, θα δείξω ολόκληρη τη λειτουργία του τύπου σαν να γίνονταν οι υπολογισμοί στο φύλλο και όχι στη μνήμη), τότε οι πίνακες θα μοιάζουν με αυτό:

Είναι προφανές ότι αν, για παράδειγμα, Δ2=Μολύβια, τότε η τιμή θα είναι TRUE και αν D3=Φάκελοι, μετά FALSE (καθώς το κριτήριο για την επιλογή ενός προϊόντος στο παράδειγμά μας είναι η τιμή Μολύβια).

Γνωρίζοντας ότι η τιμή TRUE είναι πάντα ίση με 1 και FALSE είναι πάντα ίση με 0, συνεχίζουμε να εργαζόμαστε με πίνακες όπως με τους αριθμούς 0 και 1.
Πολλαπλασιάζοντας τις τιμές του πίνακα που προκύπτουν μεταξύ τους διαδοχικά, παίρνουμε ΕΝΑ πίνακα μηδενικών και μονάδων. Όπου πληρούνταν και τα τρία κριτήρια επιλογής, ( IVANOV, ΜΟΛΥΒΙ, ΙΑΝΟΥΑΡΙΟΣ) δηλ. όλες οι συνθήκες πήραν τις τιμές TRUE, παίρνουμε 1 (1*1*1 = 1), αλλά αν δεν πληρούται τουλάχιστον μία συνθήκη, παίρνουμε 0 (1*1*0 = 0; 1*0*1 = 0, 0*1* 1 = 0).

Τώρα το μόνο που μένει είναι να πολλαπλασιάσουμε τον πίνακα που προκύπτει με έναν πίνακα που περιέχει τα δεδομένα που πρέπει τελικά να αθροίσουμε (εύρος F2:F16) και, στην πραγματικότητα, αθροίστε όσα δεν πολλαπλασιάστηκαν με 0.

Συγκρίνετε τώρα τους πίνακες που λαμβάνονται χρησιμοποιώντας τον τύπο και με τον βήμα προς βήμα υπολογισμό στο φύλλο (επισημασμένο με κόκκινο χρώμα).

Νομίζω ότι όλα είναι ξεκάθαρα :)

ΜΕΙΟΝ: SUMPRODUCT - "βαρύς" τύπος πίνακα. Κατά τον υπολογισμό σε μεγάλα εύρη δεδομένων, ο χρόνος επανυπολογισμού αυξάνεται αισθητά.

ΣΗΜΕΙΩΣΕΙΣ

ΠΕΡΙΟΧΗ ΕΦΑΡΜΟΓΗΣ: Οποιαδήποτε έκδοση του Excel

Μέθοδος 3: Τύπος πίνακα

SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

Στην αγγλική έκδοση:

SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

ΠΩΣ ΔΟΥΛΕΥΕΙ:Ακριβώς το ίδιο με τη μέθοδο Νο. 2. Υπάρχουν μόνο δύο διαφορές - αυτός ο τύπος εισάγεται πατώντας Ctrl+Shift+Enter, και όχι απλά πατώντας Εισαγωκαι ο πίνακας των 0 και 1 δεν πολλαπλασιάζεται με το εύρος άθροισης, αλλά επιλέγεται χρησιμοποιώντας τη συνάρτηση IF.

ΜΕΙΟΝ: Οι τύποι πίνακα κατά τον υπολογισμό σε μεγάλα εύρη δεδομένων αυξάνουν αισθητά το χρόνο επανυπολογισμού.

ΣΗΜΕΙΩΣΕΙΣ: Ο αριθμός των επεξεργασμένων πινάκων περιορίζεται σε 255.

ΠΕΡΙΟΧΗ ΕΦΑΡΜΟΓΗΣ: Οποιαδήποτε έκδοση του Excel

Μέθοδος 4:

SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)

13.12.5. Ολοκληρωμένα κριτήρια: αξιολόγηση της ποιότητας των οικοσυστημάτων με βάση διάφορους δείκτες

Οι κατηγορίες ποιότητας του νερού σύμφωνα με υδροβιολογικούς και μικροβιολογικούς δείκτες καθορίζονται από τους «Κανόνες για την παρακολούθηση της ποιότητας του νερού των υπερχειλιστών και υδάτινων ρευμάτων» [GOST 17.1.3.07–82], οι οποίοι ρυθμίζουν το περιεχόμενο των προγραμμάτων παρακολούθησης υδρολογικών, υδροχημικών και υδροβιολογικών δεικτών. συχνότητα παρακολούθησης, καθώς και ο σκοπός και η θέση των σημείων δειγματοληψίας
(Πίνακας 13.7) Σύμφωνα με αυτό το έγγραφο, ο βαθμός ρύπανσης των υδάτων αξιολογείται λαμβάνοντας υπόψη τον δείκτη θηριωδίας σύμφωνα με τους Pantle και Bucca όπως τροποποιήθηκε από τον Sladechek, τον δείκτη Goodnight–Whitley και Parele ολιγοχαετών, τον βιοτικό δείκτη Woodiwis και ένα παραδοσιακό σύνολο μικροβιολογικών δεικτών

Ολοκληρωμένος δείκτηςσύμφωνα με την E.V. Το Balushkina αναπτύχθηκε και χρησιμοποιήθηκε για την αξιολόγηση της κατάστασης των οικοσυστημάτων των υδάτινων σωμάτων που υπόκεινται σε μικτή οργανική και τοξική ρύπανση. Πέρασε εκτεταμένες δοκιμές στο σύστημα λίμνης Ladoga - ποταμού. Ο Νέβα είναι το ανατολικό τμήμα του Κόλπου της Φινλανδίας [Balushkina et al., 1996]. Ο ενσωματωμένος δείκτης IP υπολογίζεται χρησιμοποιώντας τον τύπο:

IP = K 1 * S t + K 2 *OI + K 3 *K ch + K 4 / BI,

όπου S t είναι ο δείκτης σαπροτοξικότητας V.A. Yakovleva (K 1 = 25); OI – Δείκτης ολιγοχαίτη Goodnight και Whitley, ίσος με την αναλογία του αριθμού των ολιγοχαιτών προς τον συνολικό αριθμό των ζωοβένθων ως ποσοστό (K 2 = 1). K ch – Δείκτης χειρονομιδίου Balushkina (K 3 = 8,7); 1 / BI είναι το αντίστροφο του βιοτικού δείκτη Woodiwis (K 4 @ 100).

E.V. Η Balushkina πιστεύει ότι ο αναπόσπαστος δείκτης που έλαβε περιελάμβανε όλα τα καλύτερα χαρακτηριστικάγονικούς δείκτες και λαμβάνει υπόψη όσο το δυνατόν περισσότερο τα χαρακτηριστικά των βενθικών κοινοτήτων: την παρουσία ειδών δείκτη σαπροβοτοξικότητας, την αναλογία ομάδων δεικτών ζώων υψηλότερης ταξινομικής κατάταξης, τον βαθμό κυριαρχίας των μεμονωμένων ομάδων και τη δομή της κοινότητας ως ένα ολόκληρο.

Συνδυασμένος δείκτης κοινοτικής υγείας σύμφωνα με το A.I. Μπακάνοφ.Κατά την αξιολόγηση της κατάστασης των βενθικών κοινοτήτων ορισμένων ποταμών, λιμνών και δεξαμενών στη Ρωσία, ο συγγραφέας χρησιμοποίησε τους ακόλουθους δείκτες για να χαρακτηρίσει ποσοτικά την κατάσταση του βένθου: αριθμός (N), δείγματα/m2. βιομάζα (Β), g/m2; αριθμός ειδών (S); Ποικιλότητα ειδών Shannon (N), bits/ind.; Δείκτης Parele oligochaete (OPI, %), ίσος με την αναλογία του αριθμού των ολιγοχαιτών-tubificids προς τον συνολικό αριθμό των βενθοειδών, μέση σαπροβατότητα (AS), που υπολογίζεται ως ο σταθμισμένος μέσος όρος σαπροβατότητας των τριών πρώτων κυρίαρχων ειδών βενθικών οργανισμών. Για να συνδυαστούν οι τιμές των παρατιθέμενων δεικτών και να αντικατασταθούν με έναν αριθμό, προτείνεται ένας δείκτης που προκύπτει - ο συνδυασμένος δείκτης της κατάστασης της κοινότητας (CISS; [Bakanov, 1997]), που βρέθηκε χρησιμοποιώντας τη συνήθη μέθοδο υπολογισμού των δεικτών ολοκληρωμένης κατάταξης:

όπου R i είναι η κατάταξη του σταθμού σύμφωνα με τον i-ο δείκτη, R i είναι το «βάρος» αυτού του δείκτη, k είναι ο αριθμός των δεικτών.

Αρχικά, όλοι οι σταθμοί κατατάσσονται για κάθε δείκτη και η κατάταξη 1 εκχωρείται στις μέγιστες τιμές των N, B, H και S. Εάν σε πολλούς σταθμούς οι τιμές οποιουδήποτε δείκτη ήταν ίδιες, τότε χαρακτηρίζονταν από μία μέση κατάταξη. Το άρθρο παρέχει διαφορετικές εκδόσεις του τελικού τύπου (4.22) (τονίζουμε ότι οι τύποι δεν περιλαμβάνουν τις απόλυτες τιμές των δεικτών, αλλά τις τάξεις τους):

KISS = (2B + N + H + S)/5, όπου στη βιομάζα δίνεται «βάρος» 2, καθώς σχετίζεται με την ποσότητα της ροής ενέργειας που διέρχεται από την κοινότητα, η οποία είναι εξαιρετικά σημαντική για την αξιολόγηση της κατάστασής της.

KISS = (2CC + 1,5OIP + 1,5B + N + H + S)/8, όπου πιστεύεται ότι η μέση ευαισθησία σχετίζεται περισσότερο με τη ρύπανση.

Όσο χαμηλότερη είναι η τιμή του KISS, τόσο καλύτερη είναι η κατάσταση της κοινότητας.

Δεδομένου ότι η κατάσταση της κοινότητας εξαρτάται τόσο από φυσικούς περιβαλλοντικούς παράγοντες (βάθος, έδαφος, ρεύμα, κ.λπ.) όσο και από την παρουσία, τη φύση και την ένταση της ρύπανσης, ένας συνδυασμένος δείκτης ρύπανσης (CPI; [Bakanov, 1999]), συμπεριλαμβανομένων των τιμών κατάταξης , υπολογίζονται επιπλέον τρεις δείκτες:

KIZ = (SS + OIP + B)/3. (4.23)

Η κατάταξη των δεικτών εδώ πραγματοποιείται με αντίστροφη σειρά (από ελάχιστες τιμές σε μέγιστες)

Το KISS και το KIZ είναι σχετικοί δείκτες που κατατάσσουν τους σταθμούς σε μια κλίμακα στην οποία η καλύτερη κατάσταση της κοινότητας σύμφωνα με το επιλεγμένο σύνολο δεικτών χαρακτηρίζεται από ελάχιστες τιμές δείκτη και η χειρότερη από τη μέγιστη. Εκτός από τις τιμές που χαρακτηρίζουν τις τιμές των δεικτών σε έναν συγκεκριμένο σταθμό, οι μέσες τιμές τους υπολογίζονται για ολόκληρο το σύνολο των σταθμών. Η διακύμανση των δεικτών σε μεμονωμένους σταθμούς σε σχέση με τον μέσο όρο καθιστά δυνατό να κρίνουμε εάν τα πράγματα είναι χειρότερα ή καλύτερα σε αυτούς σε σύγκριση με τη γενική τάση.

Ο υπολογισμός του συντελεστή συσχέτισης κατάταξης Spearman μεταξύ των τιμών των KISS και KIZ δείχνει πόσο η ρύπανση επηρεάζει την κατάσταση των κοινοτήτων zoobenthos. Εάν υπάρχει σημαντική θετική συσχέτιση μεταξύ των τιμών αυτών των δεικτών, τότε η κατάσταση των βενθικών ζωικών κοινοτήτων καθορίζεται σε μεγάλο βαθμό από την παρουσία ρύπανσης (αλλιώς καθορίζεται από φυσικούς περιβαλλοντικούς παράγοντες).

Προηγούμενος

1. Στην πρόσθετη στήλη στην οποία θα υποδείξουμε τη βαθμολογία, εισάγετε τη συνάρτηση RANK (γράψτε στο κελί =RANK και επιλέξτε την προτεινόμενη συνάρτηση EXCEL από τη λίστα, κάντε κλικ στο fx στη γραμμή τύπων)

2. Συμπληρώστε τα ορίσματα στο παράθυρο που ανοίγει: "Αριθμός" - υποδείξτε την πρώτη τιμή στον πίνακά μας στην ίδια γραμμή όπου βρίσκεται ο τύπος.

3. "Σύνδεσμος" - υποδεικνύουν ολόκληρο τον πίνακα δεδομένων, π.χ. εύρος με όλους τους αριθμούς (τιμές πώλησης).

4. Διορθώστε τα όρια αυτού του εύρους (πατήστε F4 στο πληκτρολόγιο) έτσι ώστε κατά τη μεταφορά στο μέλλον η διεύθυνση του εύρους να μην «μετακινείται» και πατήστε OK.

5. Τεντώνουμε τον τύπο προς τα κάτω σε όλα τα κελιά της στήλης "βαθμολόγηση".

Όταν χρησιμοποιείτε αυτήν τη λειτουργία, η βαθμολογία υπολογίζεται αυτόματα και εάν αλλάξετε οποιαδήποτε τιμή, η βαθμολογία θα επανυπολογιστεί αυτόματα.

Εάν σας άρεσε το υλικό ή ακόμα και το βρήκατε χρήσιμο, μπορείτε να ευχαριστήσετε τον συγγραφέα μεταφέροντας ένα συγκεκριμένο ποσό χρησιμοποιώντας το παρακάτω κουμπί:
(για μεταφορά με κάρτα, κάντε κλικ στο VISA και μετά «μεταφορά»)

Στο δεύτερο μέρος του σεμιναρίου λειτουργίας μας VLOOKUP(VLOOKUP) στο Excel θα δούμε αρκετά παραδείγματα που θα σας βοηθήσουν να διοχετεύσετε όλη σας τη δύναμη VLOOKUPγια την επίλυση των πιο φιλόδοξων προβλημάτων του Excel. Τα παραδείγματα υποθέτουν ότι έχετε ήδη βασικές γνώσεις για το πώς λειτουργεί αυτή η λειτουργία. Εάν όχι, μπορεί να σας ενδιαφέρει να ξεκινήσετε με το πρώτο μέρος αυτού του σεμιναρίου, το οποίο εξηγεί τη σύνταξη και τη βασική χρήση VLOOKUP. Λοιπόν, ας ξεκινήσουμε.

Αναζήτηση στο Excel χρησιμοποιώντας πολλά κριτήρια

Λειτουργία VLOOKUPΤο Excel είναι ένα πραγματικά ισχυρό εργαλείο για την αναζήτηση μιας συγκεκριμένης τιμής σε μια βάση δεδομένων. Ωστόσο, υπάρχει ένας σημαντικός περιορισμός - η σύνταξή του σας επιτρέπει να αναζητήσετε μόνο μία τιμή. Τι γίνεται αν χρειαστεί να κάνετε αναζήτηση χρησιμοποιώντας διάφορες συνθήκες; Θα βρείτε τη λύση παρακάτω.

Παράδειγμα 1: Αναζήτηση χρησιμοποιώντας 2 διαφορετικά κριτήρια

Ας πούμε ότι έχουμε μια λίστα με παραγγελίες και θέλουμε να βρούμε Ποσότητα εμπορευμάτων(Ποσ.), με βάση δύο κριτήρια – Ονομα Πελάτη(Πελάτης) και Το όνομα του προϊόντος(Προϊόν). Το θέμα περιπλέκεται από το γεγονός ότι καθένας από τους αγοραστές παρήγγειλε διάφορους τύπους αγαθών, όπως φαίνεται από τον παρακάτω πίνακα:

Τακτική λειτουργία VLOOKUPδεν θα λειτουργήσει σε αυτό το σενάριο επειδή θα επιστρέψει την πρώτη τιμή που βρέθηκε που ταιριάζει με τη δεδομένη τιμή αναζήτησης. Για παράδειγμα, εάν θέλετε να μάθετε την ποσότητα ενός αντικειμένου Γλυκα, παραγγελθεί από τον αγοραστή Τζέρεμι Χιλ, γράψτε τον ακόλουθο τύπο:

VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

– αυτός ο τύπος θα επιστρέψει το αποτέλεσμα 15 , που αντιστοιχεί στο προϊόν Μήλα, αφού είναι η πρώτη τιμή που ταιριάζει.

Υπάρχει μια απλή λύση - δημιουργήστε μια πρόσθετη στήλη στην οποία θα συνδυάσετε όλα τα απαραίτητα κριτήρια. Στο παράδειγμά μας αυτές είναι οι στήλες Ονομα Πελάτη(Πελάτης) και Το όνομα του προϊόντος(Προϊόν). Να θυμάστε ότι η συγχωνευμένη στήλη πρέπει πάντα να είναι η πιο αριστερή στήλη στο εύρος αναζήτησης, καθώς είναι η αριστερή στήλη που η συνάρτηση VLOOKUPφαίνεται κατά την αναζήτηση μιας τιμής.

Έτσι, προσθέτετε μια βοηθητική στήλη στον πίνακα και αντιγράφετε έναν τύπο όπως αυτός σε όλα τα κελιά του: =B2&C2. Εάν θέλετε η συμβολοσειρά να είναι πιο ευανάγνωστη, μπορείτε να διαχωρίσετε τις συνδυασμένες τιμές με ένα κενό: =B2&” “&C2. Μετά από αυτό μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο:

VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=VLOOKUP("Jeremy Hill Sweets";$A$7:$D$18,4;FALSE)

VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

Πού είναι το κελί Β1περιέχει τη συνενωμένη τιμή του ορίσματος αναζήτηση_τιμής(τιμή_αναζήτησης) και 4 - διαφωνία col_index_num(αριθμός_στήλης), δηλ. Ο αριθμός της στήλης που περιέχει τα προς ανάκτηση δεδομένα.

Παράδειγμα 2: VLOOKUP με βάση δύο κριτήρια με τον πίνακα να προβάλλεται σε άλλο φύλλο

Εάν πρέπει να ενημερώσετε τον Κύριο πίνακα προσθέτοντας δεδομένα από έναν δεύτερο πίνακα (πίνακας αναζήτησης), ο οποίος βρίσκεται σε άλλο φύλλο ή σε άλλο βιβλίο εργασίας του Excel, τότε μπορείτε να συλλέξετε την απαιτούμενη τιμή απευθείας στον τύπο που εισάγετε στον κύριο πίνακα .

Όπως και στο προηγούμενο παράδειγμα, θα χρειαστείτε μια βοηθητική στήλη στον πίνακα Αναζήτηση με τις συνδυασμένες τιμές. Αυτή η στήλη πρέπει να είναι η πιο αριστερή στήλη στο εύρος που έχει καθοριστεί για την αναζήτηση.

Έτσι, ο τύπος με VLOOKUPθα μπορούσε να είναι έτσι:

VLOOKUP(B2&" "&C2,Παραγγελίες!$A&$2:$D$2,4,FALSE)
=VLOOKUP(B2&" "Παραγγελίες!$A&$2:$D$2;4;FALSE)

Εδώ, οι στήλες Β και Γ περιέχουν ονόματα πελατών και ονόματα προϊόντων αντίστοιχα, και σύνδεσμο Παραγγελίες!$A&$2:$D$2ορίζει έναν πίνακα για αναζήτηση σε άλλο φύλλο εργασίας.

Για να κάνετε τον τύπο πιο ευανάγνωστο, μπορείτε να δώσετε ένα όνομα στο εύρος που βλέπετε, το οποίο θα κάνει τον τύπο να φαίνεται πολύ πιο απλός:

VLOOKUP(B2&" "&C2,Παραγγελίες,4,FALSE)
=VLOOKUP(B2&" "Παραγγελίες;4;FALSE)

Για να λειτουργήσει ο τύπος, οι τιμές στην πιο αριστερή στήλη του πίνακα που προβάλλετε πρέπει να συνδυαστούν με τον ίδιο τρόπο όπως στα κριτήρια αναζήτησης. Στο παραπάνω σχήμα, συνδυάσαμε τις τιμές​​και βάλαμε ένα κενό μεταξύ τους, το ίδιο πρέπει να γίνει και στο πρώτο όρισμα της συνάρτησης (B2&” “&C2”).

Θυμάμαι!Λειτουργία VLOOKUPπεριορίζεται στους 255 χαρακτήρες, δεν μπορεί να αναζητήσει μια τιμή μεγαλύτερη από 255 χαρακτήρες. Λάβετε αυτό υπόψη και βεβαιωθείτε ότι το μήκος της τιμής που αναζητάτε δεν υπερβαίνει αυτό το όριο.

Συμφωνώ ότι η προσθήκη μιας βοηθητικής στήλης δεν είναι η πιο κομψή και όχι πάντα αποδεκτή λύση. Μπορείτε να κάνετε το ίδιο πράγμα χωρίς τη βοηθητική στήλη, αλλά αυτό θα απαιτούσε έναν πολύ πιο περίπλοκο τύπο με συνδυασμό συναρτήσεων ΔΕΙΚΤΗΣ(ΕΥΡΕΤΗΣ) και ΑΓΩΝΑΣ(ΑΓΩΝΑΣ).

Εξάγουμε το 2ο, 3ο κ.λπ. τιμές χρησιμοποιώντας το VLOOKUP

Το ξέρεις ήδη VLOOKUPμπορεί να επιστρέψει μόνο μία τιμή που ταιριάζει, ή μάλλον την πρώτη που βρέθηκε. Τι γίνεται όμως αν αυτή η τιμή επαναληφθεί πολλές φορές στον πίνακα που βλέπετε και θέλετε να εξαγάγετε τη 2η ή την 3η από αυτές; Κι αν όλες οι αξίες; Το πρόβλημα φαίνεται περίπλοκο, αλλά υπάρχει λύση!

Ας υποθέσουμε ότι μια στήλη του πίνακα περιέχει τα ονόματα των πελατών (Όνομα πελάτη) και η άλλη περιέχει τα προϊόντα (Προϊόν) που αγόρασαν. Ας προσπαθήσουμε να βρούμε το 2ο, το 3ο και το 4ο προϊόντα που αγόρασε ένας συγκεκριμένος πελάτης.

Ο απλούστερος τρόπος είναι να προσθέσετε μια βοηθητική στήλη πριν από τη στήλη Όνομα πελάτηκαι συμπληρώστε το με ονόματα πελατών με αριθμό επανάληψης κάθε ονόματος, π.χ. John Doe1, John Doe2και τα λοιπά. Ας κάνουμε το κόλπο με την αρίθμηση χρησιμοποιώντας τη συνάρτηση COUNTIF(COUNTIF), δεδομένου ότι τα ονόματα πελατών βρίσκονται στη στήλη Β:

B2&COUNTIF($B$2:B2,B2)
=B2&COUNTIF($B$2:B2,B2)

Μετά από αυτό μπορείτε να χρησιμοποιήσετε την κανονική λειτουργία VLOOKUPγια να βρείτε την παραγγελία που χρειάζεστε. Για παράδειγμα:

• Βρίσκουμε 2ο Νταν Μπράουν:

  VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

• Βρίσκουμε 3ηαγαθά που παρήγγειλε ο αγοραστής Νταν Μπράουν:

  VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)
  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

Αν ψάχνετε μόνο 2οεπανάληψη, μπορείτε να το κάνετε αυτό χωρίς τη βοηθητική στήλη δημιουργώντας έναν πιο περίπλοκο τύπο:

IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,FALSE)"")
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,TRUE)"")

Σε αυτόν τον τύπο:

• $2 F $– ένα κελί που περιέχει το όνομα του αγοραστή (δεν έχει αλλάξει, παρακαλώ σημειώστε – ο σύνδεσμος είναι απόλυτος).
• $B$– στήλη Όνομα πελάτη;
• Πίνακας 4– Το τραπέζι σας (μπορεί επίσης να υπάρχει κανονική σειρά σε αυτό το μέρος).
• C16 $– το τελικό κελί του πίνακα ή του εύρους σας.

Αυτός ο τύπος βρίσκει μόνο τη δεύτερη τιμή που ταιριάζει. Εάν πρέπει να εξαγάγετε τις υπόλοιπες επαναλήψεις, χρησιμοποιήστε την προηγούμενη λύση.

Εάν χρειάζεστε μια λίστα με όλες τις αντιστοιχίσεις, χρησιμοποιήστε τη συνάρτηση VLOOKUPΔεν υπάρχει βοήθεια εδώ, αφού επιστρέφει μόνο μία τιμή κάθε φορά - αυτό είναι. Αλλά το Excel έχει μια λειτουργία ΔΕΙΚΤΗΣ(INDEX), το οποίο μπορεί εύκολα να αντιμετωπίσει αυτό το έργο. Θα μάθετε πώς θα μοιάζει ένας τέτοιος τύπος στο παρακάτω παράδειγμα.

Ανάκτηση όλων των επαναλήψεων της επιθυμητής τιμής

Οπως αναφέρθηκε προηγουμένως, VLOOKUPδεν μπορεί να εξαγάγει όλες τις διπλές τιμές από το εύρος που προβάλλεται. Για να το κάνετε αυτό, θα χρειαστείτε έναν ελαφρώς πιο περίπλοκο τύπο που αποτελείται από πολλές συναρτήσεις του Excel, όπως π.χ ΔΕΙΚΤΗΣ(ΔΕΙΚΤΗΣ), ΜΙΚΡΟ(ΜΙΚΡΟ) και ΣΕΙΡΑ(ΓΡΑΜΜΗ)

Για παράδειγμα, ο παρακάτω τύπος βρίσκει όλες τις εμφανίσεις μιας τιμής από το κελί F2 στην περιοχή B2:B16 και επιστρέφει το αποτέλεσμα από τις ίδιες σειρές στη στήλη C.

(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"") )
(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;"");ROW()-3));"") )

Εισαγάγετε αυτόν τον τύπο πίνακα σε πολλά γειτονικά κελιά, π.χ. F4:F8, όπως φαίνεται στο παρακάτω σχήμα. Ο αριθμός των κελιών πρέπει να είναι ίσος ή μεγαλύτερος από τον μέγιστο δυνατό αριθμό επαναλήψεων της τιμής αναζήτησης. Μην ξεχάσετε να κάνετε κλικ Ctrl+Shift+Enterγια να εισάγετε σωστά τον τύπο του πίνακα.

Εάν ενδιαφέρεστε να κατανοήσετε πώς λειτουργεί, ας βουτήξουμε λίγο πιο βαθιά στις λεπτομέρειες του τύπου:

Μέρος 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")
IF($F$2=B2:B16;ROW(C2:C16)-1;"")

$F$2=B2:B16– συγκρίνετε την τιμή στο κελί F2 με καθεμία από τις τιμές στην περιοχή B2:B16. Αν βρεθεί αντιστοιχία, τότε η έκφραση ΣΕΙΡΑ(C2:C16)-1επιστρέφει τον αριθμό της αντίστοιχης σειράς (τιμή -1 σας επιτρέπει να παραλείψετε τη γραμμή κεφαλίδας). Εάν δεν υπάρχουν αντιστοιχίες, η συνάρτηση ΑΝΤο (IF) επιστρέφει μια κενή συμβολοσειρά.

Το αποτέλεσμα της συνάρτησης ΑΝ(ΑΝ) θα υπάρχει ένας οριζόντιος πίνακας όπως αυτός: (1,"","3,"","5,""",,",",",",",",","","12," "," "," "" )

Μέρος 2ο:

ΣΕΙΡΑ()-3
ΣΕΙΡΑ()-3

Εδώ είναι η λειτουργία ΣΕΙΡΑ(ROW) λειτουργεί ως πρόσθετος μετρητής. Εφόσον ο τύπος αντιγράφεται στα κελιά F4:F9, αφαιρούμε τον αριθμό 3 από το αποτέλεσμα της συνάρτησης για να λάβετε την τιμή 1 σε ένα κελί F4(γραμμή 4, αφαιρέστε 3) για να πάρετε 2 σε ένα κελί F5(γραμμή 5, αφαιρέστε 3) και ούτω καθεξής.

Μέρος 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

Λειτουργία ΜΙΚΡΟ(ΜΙΚΡΟ) επιστρέφει απείρως μικρόςτη μικρότερη τιμή στον πίνακα δεδομένων. Στην περίπτωσή μας, ποια θέση (από τη μικρότερη) να επιστρέψουμε καθορίζεται από τη συνάρτηση ΣΕΙΡΑ(LINE) (βλ. Μέρος 2). Έτσι, για το κελί F4λειτουργία SMALL((πίνακας);1)επιστρέφει 1ος(μικρότερο) στοιχείο του πίνακα, δηλαδή 1 . Για κελί F5επιστρέφει 2οτο μικρότερο στοιχείο του πίνακα, δηλαδή 3 , και ούτω καθεξής.

Μέρος 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;""),ROW()-3))

Λειτουργία ΔΕΙΚΤΗΣ(INDEX) απλώς επιστρέφει την τιμή ενός συγκεκριμένου κελιού σε έναν πίνακα C2:C16. Για κελί F4λειτουργία INDEX($C$2:$C$16,1)θα επιστρέψει Μήλα, Για F5λειτουργία INDEX($C$2:$C$16,3)θα επιστρέψει Γλυκακαι ούτω καθεξής.

Μέρος 5:

IFERROR()
IFERROR()

Τέλος, βάζουμε τον τύπο μέσα στη συνάρτηση ΑΦΑΝΙΣΤΗΡΙΟ(IFERROR), καθώς είναι απίθανο να είστε ευχαριστημένοι με ένα μήνυμα σφάλματος #Δ/Υ(# N/A) εάν ο αριθμός των κελιών στα οποία αντιγράφεται ο τύπος είναι μικρότερος από τον αριθμό των διπλότυπων τιμών στην περιοχή προβολής.

Δισδιάστατη αναζήτηση κατά γνωστή γραμμή και στήλη

Η εκτέλεση μιας δισδιάστατης αναζήτησης στο Excel περιλαμβάνει την αναζήτηση μιας τιμής χρησιμοποιώντας έναν γνωστό αριθμό γραμμής και στήλης. Με άλλα λόγια, ανακτάτε την τιμή του κελιού στη διασταύρωση μιας συγκεκριμένης γραμμής και στήλης.

Ας πάμε λοιπόν στον πίνακα μας και ας γράψουμε τον τύπο με τη συνάρτηση VLOOKUP, το οποίο θα βρει πληροφορίες για το κόστος των λεμονιών που πωλούνται τον Μάρτιο.

Υπάρχουν διάφοροι τρόποι για να πραγματοποιήσετε μια δισδιάστατη αναζήτηση. Γνωρίστε τις διαθέσιμες επιλογές και επιλέξτε αυτή που σας ταιριάζει καλύτερα.

Λειτουργίες VLOOKUP και MATCH

Μπορείτε να χρησιμοποιήσετε μια δέσμη λειτουργιών VLOOKUP(VLOOKUP) και ΑΝΑΖΗΤΗΣΗ(MATCH) για να βρείτε την τιμή στη διασταύρωση των πεδίων Το όνομα του προϊόντος(χορδή) και Μήνας(στήλη) του εν λόγω πίνακα:

VLOOKUP("Λεμόνια",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=VLOOKUP("Λεμόνια",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

Ο παραπάνω τύπος είναι μια κανονική συνάρτηση VLOOKUP, το οποίο αναζητά μια ακριβή αντιστοίχιση της τιμής "Lemons" στα κελιά A2 έως A9. Αλλά επειδή δεν γνωρίζετε σε ποια στήλη βρίσκονται οι πωλήσεις για τον Μάρτιο, δεν θα μπορείτε να καθορίσετε τον αριθμό στήλης για το τρίτο όρισμα της συνάρτησης VLOOKUP. Αντίθετα χρησιμοποιείται η συνάρτηση ΑΝΑΖΗΤΗΣΗγια να ορίσετε αυτή τη στήλη.

MATCH("Mar",$A$1:$I$1,0)
MATCH("Mar",$A$1:$I$1,0)

Μεταφρασμένος στην ανθρώπινη γλώσσα, αυτός ο τύπος σημαίνει:

• Αναζητούμε τα σύμβολα «Mar» – επιχείρημα αναζήτηση_τιμής(τιμή_αναζήτησης);
• Ψάχνουμε στα κελιά από A1 έως I1 για το όρισμα αναζήτηση_συστοιχίας(view_array);
• Επιστροφή ακριβούς αντιστοίχισης - επιχειρήματος match_type(τύπος_ταιριάσματος).

Χρησιμοποιώντας 0 στο τρίτο όρισμα, λέτε τη συνάρτηση ΑΝΑΖΗΤΗΣΗαναζητήστε την πρώτη τιμή που ταιριάζει ακριβώς με την τιμή αναζήτησης. Αυτό ισοδυναμεί με την τιμή ΨΕΥΔΗΣ(FALSE) για το τέταρτο όρισμα VLOOKUP.

Με αυτόν τον τρόπο μπορείτε να δημιουργήσετε έναν τύπο για αμφίδρομη αναζήτηση στο Excel, ο οποίος είναι επίσης γνωστός ως αναζήτηση δύο διαστάσεων ή αμφίδρομη αναζήτηση.

Λειτουργία SUMPRODUCT

Λειτουργία SUMPRODUCT(SUMPRODUCT) επιστρέφει το άθροισμα των γινομένων των επιλεγμένων πινάκων:

SUMPRODUCT(($A$2:$A$9="Λεμόνια")*($A$1:$I$1="Μάρ"),$A$2:$I$9)
=SUMPRODUCT(($A$2:$A$9="Λεμόνια")*($A$1:$I$1="Mar");$A$2:$I$9)

Λειτουργίες INDEX και MATCH

Θα εξηγήσω αυτές τις λειτουργίες με μεγάλη λεπτομέρεια στο επόμενο άρθρο, οπότε προς το παρόν μπορείτε απλώς να αντιγράψετε αυτόν τον τύπο:

INDEX($A$2:$I$9,MATCH("Λεμόνια",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=INDEX($A$2:$I$9,MATCH("Λεμόνια",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

Με όνομα Ranges and the Intersection Operator

Εάν δεν τρελαίνεστε για όλους αυτούς τους περίπλοκους τύπους του Excel, μπορεί να σας αρέσει αυτός ο οπτικός και αξέχαστος τρόπος:

Καθώς εισάγετε ένα όνομα, το Microsoft Excel θα εμφανίσει μια επεξήγηση εργαλείου με μια λίστα ονομάτων που ταιριάζουν, ακριβώς όπως όταν εισάγετε έναν τύπο.

1. Κάντε κλικ Εισαγωκαι ελέγξτε το αποτέλεσμα

Γενικά, όποια από τις παραπάνω μεθόδους και αν επιλέξετε, το αποτέλεσμα μιας δισδιάστατης αναζήτησης θα είναι το ίδιο:

Χρησιμοποιούμε πολλά VLOOKUPS σε έναν τύπο

Συμβαίνει ο κύριος πίνακας και ο πίνακας αναζήτησης να μην έχουν μία κοινή στήλη και αυτό εμποδίζει τη χρήση της συνήθους συνάρτησης VLOOKUP. Ωστόσο, υπάρχει ένας άλλος πίνακας που δεν περιέχει τις πληροφορίες που μας ενδιαφέρουν, αλλά έχει κοινή στήλη με τον κύριο πίνακα και τον πίνακα αναζήτησης.

Ας δούμε το παρακάτω παράδειγμα. Έχουμε έναν Κύριο πίνακα με μια στήλη SKU (νέο), όπου πρέπει να προσθέσετε μια στήλη με τις αντίστοιχες τιμές από άλλο πίνακα. Επιπλέον, έχουμε 2 πίνακες αναζήτησης. Το πρώτο (πίνακας αναζήτησης 1) περιέχει ενημερωμένους αριθμούς SKU (νέο)και ονόματα προϊόντων, και το δεύτερο (Πίνακας αναζήτησης 2) – ονόματα προϊόντων και παλιοί αριθμοί SKU (παλιό).

Για να προσθέσετε τιμές από τον δεύτερο πίνακα αναζήτησης στον κύριο πίνακα, πρέπει να εκτελέσετε μια ενέργεια γνωστή ως διπλή VLOOKUPή φωλιασμένο VLOOKUP.

1. Γράψτε τη συνάρτηση VLOOKUP, το οποίο βρίσκει το όνομα του προϊόντος στον πίνακα Πίνακας αναζήτησης 1χρησιμοποιώντας SKU, ως επιθυμητή τιμή:

   VLOOKUP(A2,New_SKU,2,FALSE)
   =VLOOKUP(A2,New_SKU,2,FALSE)

   Εδώ New_SKU– ονομαζόμενη περιοχή $A:$Bστο τραπέζι Πίνακας αναζήτησης 1, ΕΝΑ 2 – αυτή είναι η στήλη Β, η οποία περιέχει τα ονόματα των προϊόντων (δείτε την παραπάνω εικόνα)

2. Καταγράψτε έναν τύπο για να εισαγάγετε τις τιμές από έναν πίνακα Πίνακας αναζήτησης 2με βάση διάσημα ονόματαεμπορεύματα. Για να το κάνετε αυτό, εισαγάγετε τον τύπο που δημιουργήσατε νωρίτερα ως τιμή αναζήτησης για τη νέα συνάρτηση VLOOKUP:

   VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Τιμή,3,FALSE)
   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Τιμή,3,FALSE)

   Εδώ Τιμή– ονομαζόμενη περιοχή $A:$Cστο τραπέζι Πίνακας αναζήτησης 2, ΕΝΑ 3 είναι η στήλη Γ που περιέχει τιμές.

Το παρακάτω σχήμα δείχνει το αποτέλεσμα που επιστρέφεται από τον τύπο που δημιουργήσαμε:

Δυναμική αντικατάσταση δεδομένων από διαφορετικούς πίνακες με χρήση VLOOKUP και INDIRECT

Αρχικά, ας διευκρινίσουμε τι εννοούμε με την έκφραση «Δυναμική αντικατάσταση δεδομένων από διαφορετικούς πίνακες» για να βεβαιωθούμε ότι κατανοούμε ο ένας τον άλλον σωστά.

Υπάρχουν περιπτώσεις όπου υπάρχουν πολλά φύλλα με δεδομένα της ίδιας μορφής και είναι απαραίτητο να εξαχθούν οι απαραίτητες πληροφορίες από ένα συγκεκριμένο φύλλο ανάλογα με την τιμή που έχει εισαχθεί σε ένα δεδομένο κελί. Νομίζω ότι είναι πιο εύκολο να το εξηγήσω με ένα παράδειγμα.

Φανταστείτε να έχετε αναφορές πωλήσεων για πολλές περιοχές με τα ίδια προϊόντα και την ίδια μορφή. Πρέπει να βρείτε στοιχεία πωλήσεων για μια συγκεκριμένη περιοχή:

Εάν έχετε μόνο δύο τέτοιες αναφορές, τότε μπορείτε να χρησιμοποιήσετε έναν εξωφρενικά απλό τύπο με συναρτήσεις VLOOKUPΚαι ΑΝ(IF) για να επιλέξετε την επιθυμητή αναφορά για αναζήτηση:

VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

• $D$2– αυτό είναι ένα κελί που περιέχει το όνομα του προϊόντος. Λάβετε υπόψη ότι εδώ χρησιμοποιούμε απόλυτες αναφορές για να αποφύγουμε την αλλαγή της τιμής αναζήτησης κατά την αντιγραφή του τύπου σε άλλα κελιά.
• $ D3– αυτό είναι ένα κελί με το όνομα της περιοχής. Χρησιμοποιούμε μια απόλυτη αναφορά για τη στήλη και μια σχετική αναφορά για τη σειρά, επειδή σκοπεύουμε να αντιγράψουμε τον τύπο σε άλλα κελιά της ίδιας στήλης.
• FL_SalesΚαι CA_Sales– ονόματα πινάκων (ή ονομασμένων σειρών) που περιέχουν τις αντίστοιχες αναφορές πωλήσεων. Μπορείτε φυσικά να χρησιμοποιήσετε κανονικά ονόματα φύλλων και αναφορές εύρους κελιών, π.χ. «Φύλλο FL»!$A$3:$B$10, αλλά οι επώνυμες σειρές είναι πολύ πιο βολικές.

Ωστόσο, όταν υπάρχουν πολλοί τέτοιοι πίνακες, η συνάρτηση ΑΝ– αυτή δεν είναι η καλύτερη λύση. Αντίθετα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση ΕΜΜΕΣΟΣ(ΕΜΜΕΣΗ) για να επιστρέψετε το επιθυμητό εύρος αναζήτησης.

Όπως ίσως γνωρίζετε, η λειτουργία ΕΜΜΕΣΟΣχρησιμοποιείται για την επιστροφή ενός συνδέσμου με μια συμβολοσειρά κειμένου, που είναι ακριβώς αυτό που χρειαζόμαστε τώρα. Επομένως, μη διστάσετε να αντικαταστήσετε την έκφραση με τη συνάρτηση στον παραπάνω τύπο ΑΝστον σύνδεσμο με τη συνάρτηση ΕΜΜΕΣΟΣ. Αυτός είναι ο συνδυασμός VLOOKUPΚαι ΕΜΜΕΣΟΣλειτουργεί τέλεια σε ζευγάρια:

VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

• $D$2– αυτό είναι ένα κελί με το όνομα του προϊόντος, είναι αμετάβλητο λόγω του απόλυτου συνδέσμου.
• $ D3– αυτό είναι το κελί που περιέχει το πρώτο μέρος του ονόματος της περιοχής. Στο παράδειγμά μας αυτό είναι FL.
• _Εκπτώσεις– το κοινό μέρος του ονόματος όλων των ονομαζόμενων περιοχών ή πινάκων. Σε συνδυασμό με την τιμή στο κελί D3, σχηματίζεται πλήρες όνομααπαιτούμενο εύρος. Ακολουθούν ορισμένες λεπτομέρειες για όσους δεν έχουν εμπειρία με τη λειτουργία ΕΜΜΕΣΟΣ.

Πώς λειτουργούν το INDIRECT και το VLOOKUP

Αρχικά, επιτρέψτε μου να σας υπενθυμίσω τη σύνταξη της συνάρτησης ΕΜΜΕΣΟΣ(ΕΜΜΕΣΟΣ):

• Α'1, αν το όρισμα είναι ίσο ΑΛΗΘΗΣ(TRUE) ή δεν προσδιορίζεται.
• R1C1, Αν φάΑΛΛΑ(ΨΕΜΑ).

Ας επιστρέψουμε λοιπόν στις αναφορές πωλήσεών μας. Αν θυμάστε, κάθε αναφορά είναι ένας ξεχωριστός πίνακας που βρίσκεται ξεχωριστό χαρτί. Για να λειτουργήσει σωστά ο τύπος, πρέπει να δώσετε ονόματα στους πίνακες (ή τις περιοχές) σας και όλα τα ονόματα πρέπει να έχουν ένα κοινό μέρος. Για παράδειγμα, όπως αυτό: CA_Sales, FL_Sales, TX_Salesκαι ούτω καθεξής. Όπως μπορείτε να δείτε, όλα τα ονόματα περιέχουν "_Sales".

Λειτουργία ΕΜΜΕΣΟΣσυνδέει την τιμή στη στήλη D και τη συμβολοσειρά κειμένου "_Sales", αναφέροντας έτσι VLOOKUPσε ποιο τραπέζι να κοιτάξω. Εάν η τιμή "FL" βρίσκεται στο κελί D3, ο τύπος θα πραγματοποιήσει αναζήτηση στον πίνακα FL_Sales, εάν το "CA" βρίσκεται στον πίνακα CA_Salesκαι ούτω καθεξής.

Αποτέλεσμα των λειτουργιών VLOOKUPΚαι ΕΜΜΕΣΟΣθα είναι το εξής:

Εάν τα δεδομένα βρίσκονται σε διαφορετικά βιβλία εργασίας του Excel, τότε πρέπει να προσθέσετε το όνομα του βιβλίου εργασίας πριν από την ονομαζόμενη περιοχή, για παράδειγμα:

VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

Εάν η συνάρτηση ΕΜΜΕΣΟΣαναφέρεται σε άλλο βιβλίο εργασίας, τότε αυτό το βιβλίο εργασίας πρέπει να είναι ανοιχτό. Εάν είναι κλειστή, η συνάρτηση θα αναφέρει ένα σφάλμα #REF!(#ΣΥΝΔΕΣΜΟΣ!).

Από αυτό το άρθρο θα μάθετε για 5 χρήσιμες ιδιότητες των συγκεντρωτικών πινάκων που θα σας βοηθήσουν να αναλύσετε γρήγορα και διεξοδικά την κατάσταση (χρησιμοποιώντας το παράδειγμα της ανάλυσης μιας πελατειακής βάσης). Θα μάθεις:

1. Πώς να ομαδοποιήσετε δεδομένα.
2. Ποιοι δείκτες μπορούν να υπολογιστούν κατά τον συνδυασμό δεδομένων.
3. Πώς να υπολογίσετε ταυτόχρονα πολλούς δείκτες για μία παράμετρο όταν συνδυάζετε δεδομένα.
4. Ποιες πρόσθετες επιλογές υπολογισμού μπορείτε να χρησιμοποιήσετε όταν συγκεντρώνετε δεδομένα;
5. Σχετικά με τη δυνατότητα διαλογής.

Και βάσει αυτής της ανάλυσης, θα θίξουμε την πιο ισχυρή τεχνική για τον προγραμματισμό δραστηριοτήτων προώθησης πωλήσεων στις αγορές FMCG.

Αρχικά, ας δημιουργήσουμε έναν συγκεντρωτικό πίνακα. Ας πάρουμε έναν απλό πίνακα πωλήσεων σε πελάτες ανά ημέρα.

Ας τοποθετήσουμε τον κέρσορα στην επάνω αριστερή γωνία του πίνακα μας, μετά πάμε στο μενού «Εισαγωγή» και κάνουμε κλικ στο κουμπί «Συγκεντρωτικός πίνακας»:

Στο παράθυρο διαλόγου Create PivotTable, κάντε κλικ στο OK:

Λάβαμε έναν συγκεντρωτικό πίνακα σε ένα νέο φύλλο:

Η πρώτη χρήσιμη ιδιότητα ενός συγκεντρωτικού πίνακα για επιχειρηματική ανάλυση είναι η ομαδοποίηση δεδομένων

Έτσι, έχουμε αποστολές σε πελάτες την ημέρα, θέλουμε να καταλάβουμε σε ποιο εύρος αποστολών έχουμε μέγιστους όγκους πωλήσεων. Για να γίνει αυτό, πρέπει να ομαδοποιήσουμε τις αποστολές σε σειρές.

Σύρετε το πεδίο "Ποσό αποστολής" στην περιοχή "Ονόματα γραμμών" του συγκεντρωτικού πίνακα (πατήστε το πεδίο "Ποσό_αποστολής" με το αριστερό κουμπί του ποντικιού και σύρετέ το στην ενότητα συνοπτικού πίνακα "Ονόματα γραμμών"):

Εμφανίσαμε όλες τις αποστολές στην αριστερή στήλη της περίληψης. Τώρα ορίζουμε τον κέρσορα για τις αποστολές μας (όπως στο σχήμα):

Μεταβείτε στο μενού "Δεδομένα" του Excel και κάντε κλικ στο κουμπί "Ομάδα".

Στο παράθυρο διαλόγου που εμφανίζεται, ορίστε το βήμα ομαδοποίησης σε "5000" (μπορείτε να εισαγάγετε οποιοδήποτε) και κάντε κλικ στο "OK"

Λαμβάνουμε ομαδοποιημένους όγκους πωλήσεων με ένα δεδομένο βήμα:

Ωστε να η ομάδα φαινόταν όμορφηκαι αντιληπτό, πατήστε ξανά Κουμπί "Ομάδα".και ορίστε ίσες τιμές με τα χέρια σας, για την τιμή "ξεκινώντας από" - "-15.000" (κάτω από την ελάχιστη τιμή, πολλαπλάσιο του 5000) "έως" - "45.000" (περισσότερο από τη μέγιστη ομάδα, πολλαπλάσιο του 5000).

Λαμβάνουμε ομαδοποιημένα δεδομένα ανά ποσό αποστολής:

Η 2η χρήσιμη ιδιότητα των συγκεντρωτικών πινάκων για επιχειρηματική ανάλυση είναι
τη δυνατότητα υπολογισμού διάφορων συνοπτικών παραμέτρων για πεδία από τον πίνακα προέλευσης

Έτσι, ομαδοποιήσαμε τις αποστολές, τώρα ας δούμε ποιοι όγκοι πωλήσεων εμπίπτουν σε κάθε εύρος αποστολών. Για να γίνει αυτό, ας συνοψίσουμε τις αποστολές στη σύνοψη.

Κάντε αριστερό κλικ στο πεδίο "Ποσό_Αποστολής" και σύρετέ το στο πεδίο σύνοψης "Τιμές":

Η περίληψη υπολογίζεται από προεπιλογή "Ποσότητα κατά πεδίο Ποσό_αποστολής", π.χ. τον αριθμό των εγγραφών στον πίνακα προέλευσης στο φύλλο "Δεδομένα". Επειδή Ο πίνακάς μας περιέχει πληροφορίες σχετικά με τις πωλήσεις σε πελάτες ανά ημέρα και, στη συνέχεια, ο δείκτης μας "Ποσότητα κατά πεδίο Ποσό_αποστολής" είναι ο αριθμός των αποστολών σε πελάτες.

Ως αποτέλεσμα, στον συνοπτικό πίνακα βλέπουμε τον αριθμό των αποστολών σε πελάτες σε διαφορετικά εύρη αποστολών:

Πώς μπορούμε να πάρουμε το ποσό των αποστολών από τον αριθμό των αποστολών;

Κάντε αριστερό κλικ στο πεδίο "Ποσότητα με βάση το ποσό αποστολής" στην περιοχή "Τιμές" του συγκεντρωτικού πίνακα και στο αναπτυσσόμενο μενού επιλέξτε "Επιλογές πεδίου αξίας..."

Στο παράθυρο που ανοίγει, επιλέξτε τη λειτουργία μείωσης δεδομένων που μας ενδιαφέρει (Άθροισμα, ποσότητα, μέσος όρος, μέγιστο, ελάχιστο...). Επιλέξτε τη λειτουργία "sum" που χρειαζόμαστε και κάντε κλικ στο "OK".

Λαμβάνουμε τον συνολικό όγκο πωλήσεων για κάθε εύρος αποστολών:

Εκείνοι. βλέπουμε τι όγκο πωλήσεων αντιπροσωπεύουν οι αποστολές από 0 έως 5.000 ρούβλια, από 5.000 έως 10.000 ρούβλια. και τα λοιπά. Και είναι σαφές ότι ο μέγιστος όγκος αποστολών κυμαίνεται από μηδέν έως 5.000 ρούβλια.

Ιδιότητα 3 – η δυνατότητα υπολογισμού διαφόρων πράξεων μείωσης δεδομένων για ένα πεδίο

Τώρα θα θέλαμε να δούμε τι αριθμό αποστολών και τι μέσο όρο αποστολών έχουμε για καθεμία από τις σειρές. Για να γίνει αυτό, θα χρησιμοποιήσουμε έναν συγκεντρωτικό πίνακα για να υπολογίσουμε τον αριθμό των αποστολών και τον μέσο όρο αποστολών.

Στην περιοχή "Τιμές" του συγκεντρωτικού πίνακα, σύρετε το πεδίο "Ποσό_Αποστολής" άλλες 2 φορές και στις παραμέτρους του πεδίου τιμής, για το δεύτερο πεδίο, επιλέξτε "ποσότητα" και για το τρίτο πεδίο επιλέξτε "μέσος όρος".

Για κάθε σειρά αποστολών λαμβάνουμε τον όγκο πωλήσεων, τον αριθμό των αποστολών και τη μέση αποστολή:

Τώρα βλέπουμε σε ποιο εύρος αποστολών ο μέγιστος όγκος πωλήσεων και ο μέγιστος αριθμός αποστολών. Στο παράδειγμά μας, αυτό είναι για το εύρος από 0 έως 5000 ρούβλια. και ο όγκος πωλήσεων και ο αριθμός των αποστολών είναι μέγιστος.

Η 4η ιδιότητα των συγκεντρωτικών πινάκων είναι η δυνατότητα διεξαγωγής πρόσθετων υπολογισμών

Για να γίνει πιο ξεκάθαρη η ανάλυση δεδομένων, ας προσθέσουμε 2 ακόμη παραμέτρους - "Μερίδιο όγκου πωλήσεων για κάθε ομάδα" και "Μερίδιο του αριθμού αποστολών για κάθε ομάδα".

Για να το κάνετε αυτό, στο πεδίο "Τιμές" του συγκεντρωτικού πίνακα, σύρετε το πεδίο "Ποσό αποστολής" άλλες 2 φορές.

Επιπλέον, για μια παράμετρο στο μενού «Επιλογές πεδίου τιμών» () θα επιλέξουμε τη λειτουργία «άθροισμα» και για τη δεύτερη τη λειτουργία «ποσότητα».

Παίρνουμε έναν πίνακα σαν αυτό:

Τώρα μεταβείτε ξανά στις "Επιλογές πεδίου τιμών" και μεταβείτε στην καρτέλα "Πρόσθετοι υπολογισμοί":

Στο πεδίο "Πρόσθετοι υπολογισμοί" επιλέξτε "Μερίδιο του συνολικού ποσού"

Λαμβάνουμε έναν πίνακα στον οποίο για κάθε εύρος αποστολών προς πελάτες βλέπουμε τον όγκο πωλήσεων, τον αριθμό των αποστολών, τη μέση αποστολή, το μερίδιο του όγκου πωλήσεων για κάθε ομάδα και το μερίδιο του αριθμού αποστολών για κάθε ομάδα:

5 χρήσιμη ιδιότητα - ταξινόμηση

Τώρα, για λόγους σαφήνειας, θα ταξινομήσουμε από τη μέγιστη στην ελάχιστη ομάδα κατά όγκο πωλήσεων. Για να το κάνετε αυτό, τοποθετήστε τον κέρσορα στο πεδίο με όγκο πωλήσεων ανά ομάδα και κάντε κλικ στο κουμπί "ταξινόμηση από το μέγιστο στο ελάχιστο":

Μπορεί να φανεί ότι η μέγιστη ομάδα όσον αφορά τον όγκο πωλήσεων και τον αριθμό των αποστολών είναι η ομάδα "από 0 έως 5000 ρούβλια". Οι μέσες πωλήσεις σε αυτήν την ομάδα είναι 1971 ρούβλια.

Σημείωση! Η μέση αποστολή για όλους τους πελάτες διαφέρει σημαντικά από το 86% των αποστολών. Επιπλέον, διαφέρει σημαντικά

• για όλες τις ομάδες η μέση αποστολή είναι 2.803 ρούβλια. (συνολικό σύνολο στη σειρά).
• Και για το 86% των αποστολών 1.971 ρούβλια.

Αυτή είναι μια σοβαρή διαφορά και αν τονώσουμε τις πωλήσεις με βάση το 86% των αποστολών και ο μέσος όρος για αυτές - 1.971 ρούβλια, τότε οι ενέργειές μας θα είναι πιο ακριβείς και το αποτέλεσμα θα είναι πολύ υψηλότερο, επειδή θα μπορούμε να ενδιαφέρουμε τον μέγιστο αριθμό πελατών.

Αυτό το παράδειγμα δείχνει μια ισχυρή τεχνική για τον προγραμματισμό προωθήσεων πωλήσεων σε μαζικές αγορές και την πρόβλεψη του όγκου πωλήσεων που μπορεί να σας βοηθήσει να επηρεάσετε σημαντικά και να αλλάξετε την κατάσταση.

Αν έχετε οποιεσδήποτε απορίες, παρακαλούμε επικοινωνήστε μαζί μας.

Ακριβείς προβλέψεις για εσάς!

Ελα μαζί μας!

Κατεβάστε δωρεάν εφαρμογές προβλέψεων και επιχειρηματικής ανάλυσης:

• Novo Forecast Lite- αυτόματο υπολογισμός πρόβλεψης V Προέχω.
• 4 analytics - Ανάλυση ABC-XYZκαι ανάλυση εκπομπών Προέχω.
• Qlik SenseΕπιφάνεια εργασίας και QlikViewPersonal Edition - Συστήματα BI για ανάλυση και οπτικοποίηση δεδομένων.

Δοκιμάστε τις δυνατότητες των λύσεων επί πληρωμή:

• Novo Forecast PRO- πρόβλεψη στο Excel για μεγάλα σύνολα δεδομένων.