Η Kaley από το Nashville εργάζεται σε ένα υπολογιστικό φύλλο εισιτηρίων. Για κάθε εκδήλωση, επιλέγει ένα πρόγραμμα έκδοσης εισιτηρίων. Αυτό το πρόγραμμα έκδοσης εισιτηρίων θα μπορούσε να υποδεικνύει οπουδήποτε από 4 έως 16 τύπους εισιτηρίων για την εκδήλωση. Ο Kaley θέλει μια φόρμουλα που θα πάει στον πίνακα αναζήτησης και θα επιστρέψει * όλα * ταιριάζει, εισάγοντας νέες σειρές, ανάλογα με την περίπτωση.
Αν και δεν έχω VLOOKUP που μπορεί να το λύσει, τα νέα εργαλεία Power Query ενσωματωμένα στο Excel 2016 μπορούν να το λύσουν.
Σημείωση
Εάν διαθέτετε την έκδοση Windows του Excel 2010 ή Excel 2013, μπορείτε να κάνετε λήψη του Power Query δωρεάν από τη Microsoft. Δυστυχώς, το Power Query δεν είναι ακόμη διαθέσιμο για Excel για Android, Excel για iOS ή Excel για Mac.
Για να δείξουμε τον στόχο: ο Mike McCann and the Mechanics εμφανίζεται στο Allen Theatre με σχέδιο εισιτηρίων C. Δεδομένου ότι υπάρχουν τέσσερις σειρές που ταιριάζουν στον πίνακα αναζήτησης, η Kaley θέλει τέσσερις σειρές που λένε Mike McCann και the Mechanics, καθεμία με διαφορετικό αγώνα από τον πίνακα αναζήτησης.
Επιλέξτε ένα κελί στον αρχικό πίνακα. Πατήστε Ctrl + T για να επισημάνετε αυτά τα δεδομένα ως πίνακα. Στην καρτέλα Εργαλεία πίνακα, μετονομάστε τον πίνακα από τον Πίνακα 1 σε Εμφάνιση. Επαναλάβετε για τον πίνακα αναζήτησης, ονομάζοντάς τον Εισιτήρια.
Επιλέξτε ένα κελί στον πίνακα "Εμφάνιση". Από την καρτέλα Δεδομένα, επιλέξτε Από πίνακα / εύρος.
Αφού ανοίξει ο επεξεργαστής Power Query, ανοίξτε το αναπτυσσόμενο μενού Κλείσιμο και φόρτωση και επιλέξτε Κλείσιμο και Φόρτωση σε….
Στο παράθυρο διαλόγου Εισαγωγή δεδομένων, επιλέξτε Μόνο Δημιουργία σύνδεσης.
Μεταβείτε στον πίνακα Εισιτηρίων. Επαναλάβετε τα βήματα για τη δημιουργία μόνο σύνδεσης σε εισιτήρια. Θα πρέπει να δείτε και τις δύο συνδέσεις στο παράθυρο ερωτημάτων:
Επιλέξτε οποιοδήποτε κενό κελί. Επιλέξτε Δεδομένα, Λήψη δεδομένων, Συνδυασμός ερωτημάτων, Συγχώνευση.
Υπάρχουν έξι βήματα στο διάλογο συγχώνευσης. Το 3ο και το 4ο δεν μου φαίνονται διαισθητικά.
- Επιλέξτε Εκθέσεις από το επάνω αναπτυσσόμενο μενού
- Επιλέξτε Εισιτήρια από το δεύτερο αναπτυσσόμενο μενού.
- Κάντε κλικ στον τίτλο Πρόγραμμα εισιτηρίων στην κορυφή για να επιλέξετε τη στήλη ως το ξένο κλειδί στον πίνακα "Εμφάνιση".
- Κάντε κλικ στην επικεφαλίδα Σχέδιο εισιτηρίων στο κάτω μέρος για να επιλέξετε τη στήλη ως το βασικό πεδίο στον πίνακα αναζήτησης.
- Ανοίξτε τον τύπο Συμμετοχής και επιλέξτε Εσωτερικό (μόνο αντίστοιχες σειρές)
- Κάντε κλικ στο OK
Τα αποτελέσματα αρχικά είναι απογοητευτικά. Βλέπετε όλα τα πεδία από τον πίνακα 1 και μια στήλη που λέει Πίνακας, Πίνακας, Πίνακας.
Κάντε κλικ στο εικονίδιο Ανάπτυξης στο επάνω μέρος της στήλης Εισιτήρια.
Αποεπιλέξτε το Πρόγραμμα εισιτηρίων αφού έχετε ήδη αυτό το πεδίο. Το υπόλοιπο πεδίο θα ονομάζεται Εισιτήρια. Τύπος εισιτηρίου εκτός εάν καταργήσετε την επιλογή Χρήση αρχικού ονόματος ως πρόθεμα.
Επιτυχία! Κάθε σειρά για κάθε παράσταση εκρήγνυται σε πολλές σειρές.
Δεν είμαι ιδιαίτερα ευχαριστημένος με τη διαλογή των δεδομένων. Η ταξινόμηση κατά ημερομηνία προκαλεί την ταξινόμηση των τύπων εισιτηρίων με περίεργο τρόπο.
Δες το βίντεο
Στη σημερινή περίπτωση, το βίντεο τραβήχτηκε μετά τη σύνταξη του άρθρου. Προτείνω να προσθέσετε μια στήλη ακολουθίας στους τύπους εισιτηρίων για να ελέγξετε τη σειρά ταξινόμησης.
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, Episode 2204: Return All VLOOKUPs.
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen. Η σημερινή ερώτηση από το Nashville Music City. Ήμουν εκεί στο Νάσβιλ, κάποιος είναι υπεύθυνος για τον προγραμματισμό φόρτωσης εισιτηρίων σε ένα σύστημα έκδοσης εισιτηρίων και έτσι έχουμε αυτό: Έχουμε μια λίστα εκδηλώσεων - επερχόμενες εκδηλώσεις - έχουμε την ημερομηνία, τον χώρο και ένα σχέδιο εισιτηρίων. Λοιπόν, όπως, παρόλο που κάτι πραγματοποιείται στο Παλάτι, μπορεί να υπάρχουν διαφορετικά σχέδια εισιτηρίων - όπως, ίσως το πάτωμα να είναι διαμορφωμένο, ξέρετε, με καθίσματα ή ίσως είναι απλώς ένα μόνιμο δωμάτιο, σωστά;
Έτσι, ανάλογα με το είδος του σχεδίου εισιτηρίων, πρέπει να έρθετε εδώ στον πίνακα αναζήτησης και να βρείτε όλα τα αντίστοιχα γεγονότα, και ουσιαστικά πρόκειται να κάνουμε αυτό που αποκαλώ έκρηξη VLOOKUP. Έτσι, αν κάτι είναι στο Hannah C, θα πάει στην Hannah C και αν υπάρχουν - 1, 2, 3, 4, 5, 6-7 αντικείμενα στο Hannah C, θα έχουμε για να επιστρέψετε επτά σειρές - που σημαίνει ότι θα πρέπει να εισαγάγετε έξι ακόμη σειρές και να αντιγράψετε αυτά τα δεδομένα. Καλώς.
Τώρα, δεν πρόκειται να το κάνουμε καθόλου με VLOOKUP, αλλά έχετε την ιδέα - κάνουμε ένα VLOOKUP και επιστρέφουμε όλες τις απαντήσεις ως νέες σειρές. Εντάξει, λοιπόν, θα πάρω και τα δύο αυτά τραπέζια και θα τα φτιάξω σε ένα πραγματικό τραπέζι με Ctrl + T. Οι πρώτοι ονομάστηκαν Πίνακας 1 φρικτό όνομα, ας το ονομάσουμε Εκδηλώσεις ή Παραστάσεις, ας το ονομάσουμε Παραστάσεις, όπως αυτό - και το δεύτερο, τώρα, hey, αυτό είναι που έμαθα γιατί το έκανα - πρέπει να έχουμε ένα πεδίο ακολουθίας εδώ. Άρα = ROW (A1), κάντε διπλό κλικ και αντιγράψτε το προς τα κάτω και μετά αντιγράψτε και επικολλήστε ειδικές τιμές. Καλώς. Τώρα το κάνουμε αυτό θα το κάνει σε έναν πίνακα - Ctrl + T, και θα το ονομάσουμε ένα εισιτήριο.
Καλώς. Έχουμε λοιπόν παραστάσεις, έχουμε εισιτήρια. Θα πάω στην καρτέλα Δεδομένα και είμαι εδώ στην εκπομπή, θέλω να πω ότι θέλω να λάβω τα δεδομένα μου από έναν Πίνακα ή Εύρος - αυτό είναι το Power Query, παρεμπιπτόντως. Εάν επιστρέψετε στο Excel 2010 ή 2013, μπορείτε να το κατεβάσετε δωρεάν από τη Microsoft, κατεβάστε το εργαλείο Power Query. Εάν χρησιμοποιείτε Mac ή iOS ή Android, λυπούμαστε, δεν υπάρχει Power Query για εσάς Εντάξει, οπότε από έναν πίνακα ή ένα εύρος … βρείτε κάποιον που έχει - βρείτε έναν φίλο που έχει - υπολογιστή με Windows και ζητήστε το να το ρυθμίσει. Καλώς. Εδώ είναι ένα τραπέζι, δεν πρόκειται να κάνουμε κάτι σε αυτό, απλώς Κλείσιμο & Φόρτωση, Κλείσιμο & Φόρτωση σε, και στη συνέχεια πείτε "Μόνο Δημιουργία σύνδεσης", τέλεια. Θα έρθουμε εδώ στο δεύτερο τραπέζι μας: Λήψη δεδομένων, από πίνακα ή εύρος, δεν κάνουμε τίποτα σε αυτό, Κλείσιμο & φόρτωση,Κλείσιμο & φόρτωση στο, "Δημιουργία σύνδεσης μόνο", OK Αυτό που έχουμε τώρα, είναι ότι έχουμε μια σύνδεση με τον πρώτο πίνακα και μια σύνδεση με τον δεύτερο πίνακα. Δεν πρόκειται να συγχωνεύσουμε αυτά τα δύο, τα οποία ουσιαστικά είναι σαν να κάνουμε VLOOKUP ή μια βάση δεδομένων βάσης δεδομένων, υποθέτω, είναι πραγματικά έτσι. Συνδυάστε ερωτήματα, πρόκειται να συγχωνεύσουμε. Καλώς.
Τώρα, επτά πράγματα που πρέπει να κάνετε σε αυτό το παράθυρο διαλόγου - και είναι λίγο μπερδεμένο - θα επιλέξουμε τις εκπομπές ως τον πρώτο πίνακα. επιλέξτε Εισιτήρια ως το δεύτερο τραπέζι. επιλέξτε ποιο κοινόχρηστο πεδίο και αυτό μπορεί να είναι πολλαπλά πεδία - μπορείτε να κάνετε κλικ-κλικ-- αλλά σε αυτήν την περίπτωση υπάρχει μόνο ένα σχέδιο εισιτηρίων και στη συνέχεια Πρόγραμμα εισιτηρίων. και μετά θα αλλάξουμε τον τύπο συμμετοχής σε έναν εσωτερικό σύνδεσμο με "μόνο τις αντίστοιχες σειρές". Καλώς. Τώρα, κάνετε κλικ στο ΟΚ και νομίζετε ότι ολόκληρο το πρόβλημά σας θα λυθεί, αλλά απλά συντρίψατε γιατί εδώ είναι όλα τα δεδομένα από το Α - δεν έχουν εισαγάγει καθόλου νέες σειρές - και εδώ, ακριβώς ένα βαρετό ηλίθιο πεδίο που ονομάζεται Εισιτήρια που έχει μόνο Τραπέζι, Τραπέζι, Τραπέζι, χα.
Αλλά, ευτυχώς, στην κορυφή αυτού είναι ένα εικονίδιο επέκτασης και θα το επεκτείνουμε - δεν χρειάζεται να κάνω σχέδιο, το έχω ήδη - Είδος εισιτηρίου και ακολουθία. Δεν θέλω να το ονομάσω Εισιτήρια. Τύπος εισιτηρίου, το οποίο θέλει να κάνει το Power Query - γι 'αυτό καταργώ την επιλογή αυτού του πλαισίου. Καλώς. Αυτήν τη στιγμή έχουμε 17 σειρές δεδομένων. όταν κάνω κλικ στο OK, BAM! Υπάρχει η έκρηξη. Έτσι, ο Michael Seeley και οι Starlighter εμφανίζονται με όλους τους διαφορετικούς τύπους εισιτηρίων, όπως αυτό. Εντάξει, και δείτε ότι αυτοί οι τύποι εισιτηρίων εμφανίζονται διαδοχικά, είναι υπέροχο. Αλλά ο Michael Seeley δεν είναι η επόμενη παράσταση, η επόμενη παράσταση είναι στις 5 Ιουνίου. Έτσι, όταν προσπαθώ να το ταξινομήσω κατά Ημερομηνία - αυτό με τρελαίνει, δεν το εξηγώ Ταξινόμηση κατά Ημερομηνία, και ο Mike Man and the Mechanics φτάνει τα 65, αλλά στη συνέχεια όλα τα εισιτήρια είναι τσαλακωμένα. Αυτοί'σχετικά με τη λάθος ακολουθία, και τότε γι 'αυτό έπρεπε να κάνω αυτήν την ακολουθία - αισθάνεται έτσι. Μπορώ να ταξινομήσω κατά Ακολουθία. Τώρα, 6, 5, όμορφα, και μετά μέσα σε αυτό, τα Εισιτήρια είναι σωστά. Και στην πραγματικότητα, σε αυτό το σημείο, δεν χρειαζόμαστε πλέον αυτήν τη στήλη. Έτσι μπορώ να κάνω δεξί κλικ και να αφαιρέσω και μετά Κλείσιμο & φόρτωση - αυτή τη φορά πρόκειται να κλείσω και να φορτώσω, όχι Κλείσιμο & φόρτωση σε - και έχουμε το αποτέλεσμα μας. Καλώς.
Λοιπόν, πήγαμε από μια λίστα εκδηλώσεων σε αυτήν τη μεγάλη λίστα, αλλά εδώ είναι το φοβερό μέρος: Το έκανα αυτό, ο Mike Man και η Mechanics δεν είναι το Palace B, το Palace C. Έτσι, επιστρέφω στο πρωτότυπο στην επάνω δεξιά γωνία - χέρι γωνία για περισσότερες πληροφορίες σχετικά με το βιβλίο.
Καλώς. Θέματα σε αυτό το επεισόδιο: Ο Kaley στο Νάσβιλ πρέπει να κάνει ένα VLOOKUP για να επιστρέψει όλους τους αγώνες, εισάγοντας συνήθως νέες σειρές. Και είναι μια βάση δεδομένων εισιτηρίων, εντάξει; Οπότε πρόκειται να το ονομάσω Έκρηξη VLOOKUP γιατί κάθε παράσταση θα εκραγεί σε έως και 16 σειρές. Θα χρησιμοποιήσουμε το Power Query για να το λύσουμε και έμαθα ότι η Ημερομηνία θα εμφανιστεί σε λάθος ακολουθία, εκτός αν προσθέσουμε ένα πεδίο Ακολουθίας στον τύπο εισιτηρίου. Κάντε και τα δύο σύνολα σε έναν πίνακα με Ctrl + T; ονομάστε τα ως Shows και Εισιτήρια. και, στη συνέχεια, από κάθε πίνακα, Λήψη δεδομένων, Από πίνακα, Κλείσιμο και φόρτωση, για δημιουργία μόνο σύνδεσης. επαναλάβετε για τον άλλο πίνακα. στη συνέχεια Δεδομένα, Λήψη δεδομένων, Συνδυασμός ερωτημάτων, Συγχώνευση. και έπειτα αυτό το παράθυρο διαλόγου, είναι αρκετά συγκεχυμένο για μένα - επιλέξτε Εκδηλώσεις, επιλέξτε Εισιτήρια, κάντε κλικ στο Τύπος εισιτηρίου και στα δύο, αλλάξτε την ένωση σε μια εσωτερική ένωση,κάντε κλικ στο OK και, στη συνέχεια, θα έχετε αυτό το τρομερά απογοητευτικό αποτέλεσμα, όπου είναι απλώς μια στήλη που λέει Πίνακας, Πίνακας, Πίνακας, Πίνακας. κάντε κλικ στο εικονίδιο Ανάπτυξης στην κορυφή αυτού. επιλέξτε πεδίο Ακολουθία εισιτηρίων. μην προθέσετε με το όνομα του πίνακα. και μπορείτε να Ταξινόμηση κατά Ημερομηνία, Ταξινόμηση κατά Ακολουθία. Κλείσιμο & φόρτωση στο υπολογιστικό φύλλο. Το όμορφο είναι ότι αν αλλάξουν τα υποκείμενα δεδομένα - απλώς ανανεώστε και έχετε τα αποτελέσματά σας.
Τώρα, hey, για να κατεβάσετε το βιβλίο εργασίας που χρησιμοποιείται από το σημερινό βίντεο, επισκεφθείτε τη διεύθυνση URL εκεί κάτω στην περιγραφή του YouTube. Επίσης, μια λίστα με τα επερχόμενα σεμινάρια-- Θα ήθελα πολύ να σας δω σε ένα από τα ζωντανά σεμινάρια Power Excel.
Θέλω να ευχαριστήσω την Kaley που εμφανίστηκε στο Νάσβιλ και μου έδωσε αυτήν την υπέροχη ερώτηση. Σε θέλω να σταματήσεις. Θα σας δω την επόμενη φορά για άλλο netcast από.
Λήψη αρχείου Excel
Για να κατεβάσετε το αρχείο excel: return-all-vlookups.xlsx
Το Power Query συνεχίζει να με εκπλήσσει. Αυτή είναι η δεύτερη σειρά τριών ημερών όπου η απάντηση είναι το Power Query:
- Τρίτη: Μετατροπή στήλης ημερομηνίας / ώρας σε ημερομηνία
- Σήμερα: Επιστρέψτε όλα τα VLOOKUPs
- Πέμπτη: Δημιουργήστε μια έρευνα για καθένα από 1100 αντικείμενα
Έχω μια ολόκληρη λίστα αναπαραγωγής YouTube με πράγματα που κατέληξα να λύσω με το Power Query
Excel Thought Of the Day
Ζήτησα από τους φίλους μου στο Excel Master τις συμβουλές τους σχετικά με το Excel. Η σημερινή σκέψη να σκεφτούμε:
"Όταν βρίσκεστε στο Doubt, χρησιμοποιήστε τη συνάρτηση ROUND!"
Mike Girvin
