Αποφύγετε το VLOOKUP χρησιμοποιώντας το μοντέλο δεδομένων. Έχετε λοιπόν δύο πίνακες που πρέπει να ενωθούν με το VLOOKUP για να μπορέσετε να κάνετε έναν συγκεντρωτικό πίνακα. Εάν έχετε Excel 2013 ή νεότερο σε υπολογιστή με Windows, μπορείτε τώρα να το κάνετε απλά και εύκολα.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων με πληροφορίες προϊόντος, πελάτη και πωλήσεων.
Το τμήμα πληροφορικής ξέχασε να βάλει τον τομέα εκεί. Εδώ είναι ένας πίνακας αναζήτησης που χαρτογραφεί τον πελάτη στον τομέα. Ώρα για VLOOKUP, σωστά;
Δεν χρειάζεται να κάνετε VLOOKUP για συμμετοχή σε αυτά τα σύνολα δεδομένων εάν έχετε Excel 2013 ή Excel 2016. Και οι δύο αυτές εκδόσεις του Excel έχουν ενσωματώσει τον κινητήρα Power Pivot στον πυρήνα του Excel. (Θα μπορούσατε επίσης να το κάνετε αυτό χρησιμοποιώντας το πρόσθετο Power Pivot για το Excel 2010, αλλά υπάρχουν μερικά επιπλέον βήματα.)
Τόσο στο αρχικό σύνολο δεδομένων όσο και στον πίνακα αναζήτησης, χρησιμοποιήστε την αρχική σελίδα, τη μορφή ως πίνακα. Στην καρτέλα Table Tools, μετονομάστε τον πίνακα από τον πίνακα 1 σε κάτι σημαντικό. Έχω χρησιμοποιήσει δεδομένα και τομείς.
Επιλέξτε ένα κελί στον πίνακα δεδομένων. Επιλέξτε Εισαγωγή, Συγκεντρωτικός Πίνακας. Ξεκινώντας από το Excel 2013, υπάρχει ένα επιπλέον πλαίσιο Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων που πρέπει να επιλέξετε πριν κάνετε κλικ στο OK.
Η λίστα Πεδία Συγκεντρωτικού Πίνακα εμφανίζεται με τα πεδία από τον Πίνακα δεδομένων. Επιλέξτε Έσοδα. Επειδή χρησιμοποιείτε το Μοντέλο δεδομένων, εμφανίζεται μια νέα γραμμή στην κορυφή της λίστας, προσφέροντας Ενεργό ή Όλα. Κάντε κλικ στο Όλα.
Παραδόξως, η λίστα Πεδία Συγκεντρωτικού Πίνακα προσφέρει όλους τους άλλους πίνακες στο βιβλίο εργασίας. Αυτό είναι πρωτοποριακό. Δεν έχετε κάνει ακόμα VLOOKUP. Αναπτύξτε τον πίνακα Sectors και επιλέξτε Sector. Δύο πράγματα συμβαίνουν για να σας προειδοποιήσουν ότι υπάρχει πρόβλημα.
Αρχικά, ο συγκεντρωτικός πίνακας εμφανίζεται με τον ίδιο αριθμό σε όλα τα κελιά.
Ίσως η πιο λεπτή προειδοποίηση είναι ένα κίτρινο πλαίσιο να εμφανίζεται στην κορυφή της λίστας Συγκεντρωτικών πινάκων που υποδεικνύει ότι πρέπει να δημιουργήσετε μια σχέση. Επιλέξτε Δημιουργία. (Εάν είστε στο Excel 2010 ή 2016, πάρτε την τύχη σας με το Auto-Detect.)
Στο παράθυρο διαλόγου Δημιουργία σχέσης, έχετε τέσσερα αναπτυσσόμενα μενού. Επιλέξτε Δεδομένα κάτω από τον Πίνακα, Πελάτης κάτω από τη Στήλη (Ξένο) και Τομείς στον Σχετικό Πίνακα. Το Power Pivot θα συμπληρώσει αυτόματα την αντίστοιχη στήλη κάτω από τη σχετική στήλη (κύρια). Κάντε κλικ στο OK.
Ο συγκεντρωτικός πίνακας που προκύπτει είναι ένας συνδυασμός των αρχικών δεδομένων και του πίνακα αναζήτησης. Δεν απαιτούνται VLOOKUP.
Δες το βίντεο
- Ξεκινώντας από το Excel 2013, ο διάλογος συγκεντρωτικού πίνακα προσφέρει το μοντέλο δεδομένων
- Αυτή είναι η κωδική λέξη για το Power Pivot Engine
- Για να χρησιμοποιήσετε το μοντέλο δεδομένων, δημιουργήστε έναν πίνακα Ctrl + T από κάθε πίνακα στο βιβλίο εργασίας
- Δημιουργήστε έναν συγκεντρωτικό πίνακα από τον πρώτο πίνακα
- Στη λίστα πεδίων συγκεντρωτικού πίνακα, αλλάξτε από Ενεργό σε Όλα
- Επιλέξτε ένα πεδίο από τον πίνακα αναζήτησης
- Δημιουργήστε τη σχέση ή Αυτόματη ανίχνευση
- Το Auto-Detect δεν ήταν εκεί το 2013
- Χάρη στους Colin Michael και Alejandro Quiceno που πρότειναν το Power Pivot γενικά.
Μεταγραφή βίντεο
Μάθετε το Excel από το podcast, επεισόδιο 2014 - Εξαλείψτε το VLOOKUP!
Podcast ολόκληρο το βιβλίο, κάντε κλικ στο "i" στην επάνω δεξιά γωνία για τη λίστα αναπαραγωγής!
Γεια, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen, αυτό ονομάζεται στην πραγματικότητα Eliminate VLOOKUP with the Data Model! Τώρα ζητώ συγγνώμη, αυτό είναι το Excel 2013 και νεότερο, αν επιστρέψετε στο Excel 2010, πρέπει να κατεβάσετε το πρόσθετο Power Pivot, το οποίο φυσικά είναι δωρεάν το 2010. Άρα αυτό που έχουμε εδώ είναι να έχουμε το δικό μας κύριο σύνολο δεδομένων, υπάρχει ένα πεδίο Πελάτης εδώ, και έπειτα έχω έναν μικρό πίνακα που χαρτογραφεί τον πελάτη στον τομέα, πρέπει να δημιουργήσω συνολικά έσοδα ανά τομέα, σωστά; Αυτό είναι ένα VLOOKUP, απλώς κάντε ένα VLOOKUP, αλλά hey, χάρη στο Excel 2013, δεν χρειάζεται να κάνουμε ένα VLOOKUP! Έκανα και τα δύο σε έναν πίνακα, και στο Table Tools, Design, μετονομάσαμε τους πίνακες, το ονομάζω αυτό Sectors και το ονομάζω αυτό Data, για να το κάνω σε πίνακα, απλά επιλέξτε ένα κελί, πατήστε Ctrl Τ. Έτσι, εάν έχουμε κάποιες επικεφαλίδες και μερικούς αριθμούς, όταν πατάτε Ctrl + T,ρωτούν "Πού είναι τα δεδομένα για τον πίνακα σας;", Ο πίνακας μου έχει κεφαλίδες και, στη συνέχεια, το ονομάζουν Table3, το ονομάζετε κάτι άλλο. Εντάξει, έτσι δημιούργησα αυτά τα δύο τραπέζια, θα ξεφορτωθώ αυτόν τον πίνακα, εντάξει.
Για να λειτουργήσει αυτό το κόλπο, όλα τα δεδομένα πρέπει να εμφανίζονται σε πίνακες. Πηγαίνουμε στην καρτέλα Εισαγωγή, επιλέξτε Συγκεντρωτικός Πίνακας και εδώ ακριβώς στο κάτω μέρος, Προσθέστε αυτά τα δεδομένα στο Μοντέλο δεδομένων. Αυτό ακούγεται πολύ αθώο, σωστά; Δεν υπάρχει τίποτα σαν το σημείο ανάφλεξης που λέει "Γεια σου, θα σε αφήσει να κάνεις καταπληκτικά πράγματα!" Και αυτό που λένε εδώ, αυτό που προσπαθούν να μην πω είναι ότι - Ω, παρεμπιπτόντως, κάθε αντίγραφο του Excel 2013 έχει πίσω του τον κινητήρα Power Pivot. Ξέρετε, αν βρίσκεστε στο Office 365, πληρώνετε 10 $ το μήνα και θέλουν να πληρώνετε 12 $ ή 15 $ το μήνα για να αποκτήσετε το Power Pivot, τα επιπλέον δύο ή πέντε δολάρια. Λοιπόν, γεια, μην πείτε, στην πραγματικότητα έχετε το μεγαλύτερο μέρος του Power Pivot ήδη στο Excel 2013. Εντάξει, οπότε κάνω κλικ στο OK, χρειάζεται λίγο περισσότερο χρόνο για να φορτώσει το μοντέλο δεδομένων, εντάξει, αλλά αυτό είναι εντάξει και αμέσως εδώ,στα πεδία Συγκεντρωτικού Πίνακα, έχω μια λίστα με όλα τα πεδία. Οπότε, θέλω να δείξω τα έσοδα, αλλά αυτό που διαφέρει είναι εδώ με τα Active και All. Όταν επιλέγω Όλα, παίρνω όλους τους πίνακες στο βιβλίο εργασίας. Εντάξει, οπότε πηγαίνω στους τομείς, και είπα ότι θέλω να βάλω τομέα στην περιοχή Rows. Τώρα, αρχικά, η έκθεση θα είναι λανθασμένη, δείτε τα 6,7 εκατομμύρια μέχρι το τέλος, και αυτή η κίτρινη προειδοποίηση εδώ θα πουν ότι πρέπει να δημιουργήσετε μια σχέση.και αυτή η κίτρινη προειδοποίηση εδώ θα πει ότι πρέπει να δημιουργήσετε μια σχέση.και αυτή η κίτρινη προειδοποίηση εδώ θα πει ότι πρέπει να δημιουργήσετε μια σχέση.
Εντάξει τώρα, το 2010 με το Power Pivot, απλώς, προσέφερε το AutoDetect, το 2013 έβγαλε το AutoDetect και το 2016 έφεραν το AutoDetect πίσω, εντάξει; Θα πρέπει να σας δείξω πώς φαίνεται το CREATE, αλλά όταν κάνω κλικ σε αυτό το κουμπί ΔΗΜΙΟΥΡΓΙΑ, ω ναι, αυτό είναι, εντάξει, καλό. Έτσι, από τον πρώτο μας πίνακα Δεδομένα, έχω ένα πεδίο που ονομάζεται Πελάτης, από τους σχετικούς πίνακες Τομείς, έχω ένα πεδίο που ονομάζεται Πελάτης και, στη συνέχεια, κάντε κλικ στο OK, εντάξει. Αλλά επιτρέψτε μου να σας δείξω πόσο δροσερό είναι το AutoDetect, αν τυχαίνει να είστε το 2016, εκεί, το κατάλαβαν, πόσο φοβερό είναι αυτό, σωστά; Δεν χρειάζεται να ανησυχείτε για το VLOOKUP και το κόμμα πέφτει στο τέλος, εάν το VLOOKUP κάνει το κεφάλι σας τραυματισμένο, θα λατρέψετε το μοντέλο δεδομένων. Πήρατε αυτά τα δύο τραπέζια, τα ενωθήκατε, ξέρετε, όπως θα έκανε η Access, υποθέτω, και δημιούργησα έναν Pivot table, απολύτως εκπληκτικό.Επομένως, ελέγξτε το μοντέλο δεδομένων την επόμενη φορά που θα πρέπει να κάνετε ένα VLOOKUP μεταξύ δύο πινάκων. Λοιπόν αυτό και όλες οι άλλες 40 συμβουλές υπάρχουν στο βιβλίο, Κάντε κλικ στο "i" στην επάνω δεξιά γωνία. Μπορείτε να αγοράσετε το βιβλίο, να έχετε μια πλήρη παραπομπή σε ολόκληρη τη σειρά βίντεο, όλο τον Αύγουστο, όλο τον Σεπτέμβριο, καλό, θα μπορούσαμε ακόμη και να μεταφέρουμε τον Οκτώβριο για να ολοκληρώσουμε το όλο πράγμα.
Εντάξει, ανακεφαλαιώστε σήμερα: ξεκινώντας από το Excel 2013, ο διάλογος Συγκεντρωτικός Πίνακας προσφέρει κάτι που ονομάζεται Μοντέλο δεδομένων, είναι η λέξη κωδικός για τον κινητήρα Power Pivot. Πριν δημιουργήσετε τους Pivot πίνακες σας, κάντε Ctrl + T για να δημιουργήσετε έναν πίνακα από κάθε βιβλίο εργασίας, πήρα τον επιπλέον χρόνο για να ονομάσω κάθε ένα. Δημιουργήστε έναν συγκεντρωτικό πίνακα από τον πρώτο πίνακα και, στη συνέχεια, στη λίστα πεδίων, μεταβείτε στην κορυφή και αλλάξτε από Ενεργό σε Όλα. Επιλέξτε ένα πεδίο από τον πίνακα αναζήτησης και, στη συνέχεια, θα σας προειδοποιήσει ότι είτε πρέπει να δημιουργήσετε μια σχέση, είτε η Αυτόματη ανίχνευση, το 2013, πρέπει να κάνετε κλικ στο ΔΗΜΙΟΥΡΓΙΑ. Αλλά είναι αυτό, 4 κλικ για να το δημιουργήσετε, 5 αν μετρήσετε το κουμπί ΟΚ, τόσο πραγματικά, πολύ εύκολο να το κάνετε.
Εντάξει, ο Colin, ο Michael και ο Alejandro Quiceno πρότειναν το Power Pivot γενικά για τα βιβλία, χάρη σε αυτά, χάρη σε εσάς που σταματήσατε, θα σας δούμε την επόμενη φορά για ένα άλλο δίκτυο από!
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2014.xlsx
