Το μοντέλο δεδομένων Excel (Power Pivot) σάς επιτρέπει να συνδέσετε ένα μεγάλο λεπτομερές σύνολο πραγματικών δεδομένων σε έναν προϋπολογισμό ανώτερου επιπέδου χρησιμοποιώντας πίνακες ξυλουργικής.
Οι προϋπολογισμοί γίνονται στο ανώτερο επίπεδο - έσοδα ανά γραμμή προϊόντος ανά περιοχή ανά μήνα. Τα πραγματικά συσσωρεύονται αργά με την πάροδο του χρόνου - τιμολόγιο ανά τιμολόγιο, στοιχείο γραμμής ανά στοιχείο γραμμής. Η σύγκριση του μικρού αρχείου προϋπολογισμού με τα ογκώδη δεδομένα Actuals ήταν ένας πόνος για πάντα. Μου αρέσει αυτό το κόλπο από τον Rob Collie, γνωστό και ως PowerPivotPro.com.
Για να ρυθμίσετε το παράδειγμα, έχετε έναν πίνακα προϋπολογισμού 54 σειρών: μία σειρά ανά μήνα ανά περιοχή ανά προϊόν.
Το αρχείο τιμολογίου βρίσκεται στο επίπεδο λεπτομέρειας: 422 σειρές μέχρι στιγμής φέτος.
Δεν υπάρχει VLOOKUP στον κόσμο που θα σας αφήσει ποτέ να ταιριάξετε με αυτά τα δύο σύνολα δεδομένων. Όμως, χάρη στο Power Pivot (γνωστό και ως μοντέλο δεδομένων στο Excel 2013+), αυτό γίνεται εύκολο.
Πρέπει να δημιουργήσετε μικροσκοπικούς πίνακες που ονομάζω «ξυλουργούς» για να συνδέσετε τα δύο μεγαλύτερα σύνολα δεδομένων. Στην περίπτωσή μου, το προϊόν, η περιοχή και η ημερομηνία είναι κοινά μεταξύ των δύο πινάκων. Ο πίνακας προϊόντων είναι ένας μικροσκοπικός πίνακας τεσσάρων κελιών. Ditto για περιοχή. Δημιουργήστε κάθε ένα από αυτά αντιγράφοντας δεδομένα από έναν πίνακα και χρησιμοποιώντας το Remove Duplicates.
Ο πίνακας ημερολογίου στα δεξιά ήταν στην πραγματικότητα πιο δύσκολο να δημιουργηθεί. Τα δεδομένα προϋπολογισμού έχουν μία σειρά ανά μήνα, πάντα πέφτοντας στο τέλος του μήνα. Τα δεδομένα τιμολογίου εμφανίζουν ημερήσιες ημερομηνίες, συνήθως τις καθημερινές. Επομένως, έπρεπε να αντιγράψω το πεδίο Ημερομηνία και από τα δύο σύνολα δεδομένων σε μία μόνο στήλη και έπειτα να αφαιρέσω τα διπλότυπα για να βεβαιωθώ ότι αντιπροσωπεύονται όλες οι ημερομηνίες. Στη συνέχεια, =TEXT(J4,"YYYY-MM")δημιουργούσα μια στήλη Μήνας από τις καθημερινές ημερομηνίες.
Εάν δεν έχετε το πλήρες πρόσθετο Power Pivot, πρέπει να δημιουργήσετε έναν συγκεντρωτικό πίνακα από τον πίνακα προϋπολογισμού και να επιλέξετε το πλαίσιο Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων.
Όπως συζητήθηκε στην προηγούμενη συμβουλή, καθώς προσθέτετε πεδία στον συγκεντρωτικό πίνακα, θα πρέπει να ορίσετε έξι σχέσεις. Ενώ θα μπορούσατε να το κάνετε αυτό με έξι επισκέψεις στο διάλογο Δημιουργία σχέσης, ενεργοποίησα το πρόσθετο Power Pivot και χρησιμοποίησα την προβολή διαγράμματος για να καθορίσω τις έξι σχέσεις.
Εδώ είναι το κλειδί για να γίνει όλη αυτή η εργασία: Είστε ελεύθεροι να χρησιμοποιήσετε τα αριθμητικά πεδία από τον προϋπολογισμό και από την πραγματική. Αλλά αν θέλετε να εμφανίσετε την περιοχή, το προϊόν ή το μήνα στον συγκεντρωτικό πίνακα, πρέπει να προέρχονται από τους πίνακες ξυλουργών!
Εδώ είναι ένας συγκεντρωτικός πίνακας με δεδομένα που προέρχονται από πέντε πίνακες. Η στήλη Α προέρχεται από τον ξυλουργό περιοχής. Η σειρά 2 προέρχεται από το πρόγραμμα εγγραφής ημερολογίου. Το προϊόν Slicer προέρχεται από το Product joiner. Οι αριθμοί προϋπολογισμού προέρχονται από τον πίνακα προϋπολογισμού και οι πραγματικοί αριθμοί προέρχονται από τον πίνακα τιμολογίων.
Αυτό λειτουργεί επειδή οι πίνακες ξυλουργών εφαρμόζουν φίλτρα στον πίνακα προϋπολογισμού και πραγματικού. Είναι μια όμορφη τεχνική και δείχνει ότι το Power Pivot δεν είναι μόνο για μεγάλα δεδομένα.
Δες το βίντεο
- Έχετε ένα μικρό σύνολο δεδομένων προϋπολογισμού από πάνω προς τα κάτω
- Θέλετε να συγκρίνετε με ένα σύνολο δεδομένων πραγματικών πραγμάτων
- Τα πραγματικά μπορεί να προέρχονται από ένα μητρώο τιμολογίων
- Το μοντέλο δεδομένων θα σας επιτρέψει να συγκρίνετε αυτά τα σύνολα δεδομένων διαφορετικού μεγέθους
- Κάντε και τα δύο σύνολα δεδομένων σε πίνακα Ctrl + T
- Για κάθε πεδίο κειμένου από το οποίο θέλετε να αναφέρετε, δημιουργήστε έναν πίνακα ξυλουργικής
- Αντιγράψτε τις τιμές και καταργήστε τα διπλά
- Για ημερομηνίες, μπορείτε να συμπεριλάβετε ημερομηνίες και από τους δύο πίνακες και να μετατρέψετε σε τέλος μήνα
- Κάντε τους ξυλουργούς να είναι πίνακες Ctrl + T
- Προαιρετικό αλλά χρήσιμο να αναφέρουμε και τους πέντε πίνακες
- Δημιουργήστε έναν συγκεντρωτικό πίνακα από τον προϋπολογισμό και επιλέξτε το μοντέλο δεδομένων
- Δημιουργήστε έναν συγκεντρωτικό πίνακα χρησιμοποιώντας τον προϋπολογισμό και το πραγματικό από τους αρχικούς πίνακες
- Όλα τα άλλα πεδία πρέπει να προέρχονται από τους ξυλουργικούς πίνακες
- Προσθέστε slicers ανά προϊόν
- Δημιουργήστε τρεις σχέσεις από το Budget to Joiners
- Δημιουργήστε τρεις σχέσεις από την Πραγματική έως τους Συμμετέχοντες
- Αύριο: πώς είναι πιο εύκολη η οικοδόμηση σχέσεων με τους τύπους Power Pivot και DAX
Μεταγραφή βίντεο
Μάθετε το Excel από το podcast, επεισόδιο 2016 - Προϋπολογισμός από κάτω προς τα κάτω έναντι πραγματικών πραγμάτων!
Γεια, μεταδίδω ολόκληρο το βιβλίο σε podcast, κάντε κλικ στο "i" στην επάνω δεξιά γωνία και ακολουθήστε τη λίστα αναπαραγωγής.
Γεια, πρόκειται να το διακόψω, αυτός είναι ο Μπιλ Τζέλεν από 15 λεπτά από τώρα. Συνειδητοποιώ τώρα ότι αυτό είναι ένα απίστευτα μεγάλο podcast και μπείτε στον πειρασμό να κάνετε κλικ πάνω του, αλλά επιτρέψτε μου να σας δώσω αυτό το σύντομο. Εάν βρίσκεστε στο Excel 2013 και είχατε ποτέ έναν μικρό πίνακα προϋπολογισμού και έναν τεράστιο πίνακα πραγματικών στοιχείων και πρέπει να τα χαρτογραφήσετε μαζί, αυτή είναι μια εκπληκτική νέα ικανότητα που έχουμε στο Excel 2013, την οποία δεν έχουν εξηγήσει πολλοί άνθρωποι και μάλλον δεν το γνωρίζετε. Εάν είστε εσείς, είστε το 2013 και πρέπει να χαρτογραφήσετε αυτά τα δύο σύνολα δεδομένων, αφιερώστε χρόνο, ίσως σήμερα, ίσως αύριο, ίσως προσθέστε το στη λίστα παρακολούθησης, αξίζει τον κόπο, είναι μια καταπληκτική τεχνική.
Εντάξει, εδώ έχουμε, στην αριστερή πλευρά έχουμε έναν προϋπολογισμό, αυτόν τον προϋπολογισμό, γίνεται στο ανώτατο επίπεδο, από πάνω προς τα κάτω, δεξιά για κάθε σειρά προϊόντων, για κάθε περιοχή, για κάθε μήνα, υπάρχει προϋπολογισμός . Δεν υπάρχουν πολλοί δίσκοι εδώ, αριθμός 55, στη δεξιά πλευρά προσπαθούμε να το συγκρίνουμε με τα πραγματικά. Τα πραγματικά στοιχεία προέρχονται από ένα μητρώο τιμολογίων, επομένως έχουμε Περιοχή, Προϊόν και Έσοδα, αλλά είναι μεμονωμένα τιμολόγια, πολύ περισσότερα δεδομένα εδώ, έχουμε ήδη στα μισά του χρόνου και έχω ήδη 423 εγγραφές. Εντάξει, λοιπόν, πώς χαρτογραφείτε αυτά τα 55 σε αυτά τα 423; Ίσως είναι δύσκολο να κάνετε με το VLOOKUP, θα πρέπει πρώτα να συνοψίσετε, αλλά ευτυχώς στο Excel 2013, το μοντέλο δεδομένων το καθιστά πραγματικά, πολύ εύκολο. Αυτό που πρέπει να επιτρέψουμε σε αυτόν τον μεγάλο τεράστιο πίνακα να επικοινωνήσει με αυτό το μικροσκοπικό τραπέζι είναι μεσάζοντες, τους αποκαλώ ξυλουργούς.Μικρά μικρά τραπέζια, Προϊόν, Περιοχή και Ημερολόγιο, πρόκειται να εντάξουμε τον προϋπολογισμό σε αυτά τα τρία τραπέζια, θα ενώσουμε το πραγματικό σε αυτά τα τρία τραπέζια, και θαύμα θα λειτουργήσει ο Συγκεντρωτικός πίνακας. Εντάξει, έτσι λοιπόν το κάνουμε αυτό.
Πρώτα απ 'όλα πρέπει να δημιουργήσω τους ξυλουργούς, οπότε παίρνω αυτό το πεδίο προϊόντος από τη στήλη Α και το αντιγράφω στη στήλη F και στη συνέχεια Δεδομένα, Κατάργηση διπλότυπων, κάντε κλικ στο OK και έμεινα με ένα μικρό μικρό τραπέζι, 1 τίτλος 3 σειρές. Το ίδιο πράγμα για την περιοχή, πάρτε τις περιοχές, Ctrl + C, μεταβείτε στη στήλη G, Επικόλληση, Κατάργηση διπλότυπων, κάντε κλικ στο OK, 3 σειρές 1 κεφαλίδα, εντάξει. Τώρα για τις ημερομηνίες, οι ημερομηνίες δεν είναι ίδιες, αυτές είναι ημερομηνίες λήξης μήνα, αποθηκεύονται στην πραγματικότητα ως ημερομηνίες λήξης μήνα και αυτές είναι τις καθημερινές. Θα πάρω και τις δύο λίστες, Ctrl + C τη δεύτερη λίστα και θα την επικολλήσω εδώ, Ctrl + V, μετά θα πάρω τη μικρότερη λίστα, θα την αντιγράψω και θα την επικολλήσω παρακάτω, εντάξει. Και είναι πραγματικά ενοχλητικό ότι, παρόλο που αποθηκεύονται ως ημερομηνίες, εμφανίζονται ως μήνες και το Remove Duplicates δεν θα τα βλέπει ως ίδια.Επομένως, πριν χρησιμοποιώ το Remove Duplicates, πρέπει να το αλλάξω σε σύντομο χρονικό διάστημα. Επιλέξτε αυτά τα δεδομένα, Δεδομένα, Κατάργηση διπλότυπων, κάντε κλικ στο OK και, στη συνέχεια, κάντε ένα είδος εδώ για να λειτουργήσει.
Εντάξει, τώρα δεν θέλω να αναφέρω την ημερήσια ημερομηνία, οπότε πρόκειται να προσθέσω μια στήλη εδώ, μια στήλη αναζήτησης που λέει Μήνα, και αυτό θα είναι ίσο με την ΗΜΕΡΟΜΗΝΙΑ την ημερομηνία, ΤΕΛΟΣ του μηνα. Θα το διαμορφώσει ως σύντομη ημερομηνία και θα το αντιγράψει, εντάξει. Τώρα, πρέπει να τα κάνουμε όλα σε πίνακα Ctrl + T, οπότε από εδώ Ctrl + T, ο πίνακας μου έχει κεφαλίδες, όμορφες. Τα μικρά, δεν συνειδητοποιούν ότι είναι επικεφαλίδες εκεί, οπότε πρέπει να βεβαιωθούμε ότι το σημειώνουμε και Ctrl + T, εντάξει, και ονομάζουν αυτούς τους πίνακες Table1, Table2, Table3, πραγματικά βαρετά ονόματα, σωστά; Έτσι θα τα μετονομάσω και θα το ονομάσω το BudTable, το ProdTable, RegTable, το CalTable μου και μετά το ActTable, εντάξει.
Ξεκινάμε από τον πρώτο πίνακα και παρεμπιπτόντως δεν πρόκειται να χρησιμοποιήσουμε το PowerPivot σήμερα, θα τα κάνουμε όλα αυτά με το μοντέλο δεδομένων. Έτσι, στο Excel 2013 ή νεότερο, έχετε αυτό το Εισαγωγή, Συγκεντρωτικός Πίνακας, θα επιλέξουμε το πλαίσιο για «Προσθήκη αυτών των δεδομένων στο Μοντέλο Δεδομένων», κάντε κλικ στο OK και θα λάβουμε τη λίστα πεδίων μας με το μαγικό κουμπί Όλα, που επιτρέπει επιλέγω και από τους πέντε πίνακες στο βιβλίο εργασίας, Πραγματικός, Προϋπολογισμός, Ημερολόγιο, Προϊόν, Περιοχή. Εντάξει, οπότε οι αριθμοί θα προέλθουν από τον πίνακα Προϋπολογισμός, θα βάλω τον προϋπολογισμό εκεί, και από τον Πραγματικό πίνακα θα βάλω το πραγματικό εκεί, αλλά τότε είναι το θέμα για τον υπόλοιπο πίνακα Συγκεντρωτικού. Οποιαδήποτε άλλα πεδία κειμένου που πρόκειται να βάλουμε στην περιοχή της σειράς ή στην περιοχή της στήλης ή ως slicers, πρέπει να προέρχονται από τους ξυλουργούς, πρέπει να προέρχονται από αυτούς τους πίνακες μεταξύ των πινάκων.
Εντάξει, οπότε από τον πίνακα Ημερολόγιο θα πάρουμε αυτό το πεδίο Μήνας και θα το βάλουμε στην κορυφή, θα αγνοήσουμε άλλες σχέσεις αυτήν τη στιγμή. Θα δημιουργήσω τις σχέσεις, αλλά θέλω να τις δημιουργήσω ταυτόχρονα. Και τον πίνακα Περιοχή, βάλτε τις περιοχές στο πλάι. Θα μπορούσα να βάλω τα προϊόντα στο πλάι, αλλά στην πραγματικότητα πρόκειται να χρησιμοποιήσω τον πίνακα προϊόντων ως slicer, οπότε Ανάλυση, Εισαγωγή Slicer, πάλι πρέπει να μεταβείτε στο All αν δεν έχετε χρησιμοποιήσει ακόμα τον πίνακα Product. Μεταβείτε λοιπόν στο All και θα δείτε ότι το Προϊόν είναι διαθέσιμο για δημιουργία ως πιο slic από τα προϊόντα, όπως αυτό. Εντάξει τώρα, σε αυτό το σημείο δεν έχουμε δημιουργήσει σχέσεις, οπότε όλοι αυτοί οι αριθμοί είναι λάθος. Και οι σχέσεις που πρέπει να δημιουργήσουμε, πρέπει να δημιουργήσουμε 3 πίνακες από αυτόν τον μικρό πίνακα προϋπολογισμού, έναν προς τα προϊόντα, έναν προς περιοχές, έναν έως ημερολόγιο,αυτό είναι 3 σχέσεις. Και τότε πρέπει να δημιουργήσουμε σχέσεις από τον Πραγματικό πίνακα στην περιοχή Προϊόντων στο Ημερολόγιο, οπότε συνολικά 6 πίνακες. Και ναι, αυτό θα ήταν σίγουρα πιο εύκολο εάν είχαμε το PowerPivot, αλλά δεν το κάνουμε ή ας υποθέσουμε ότι δεν το κάνουμε.
Και έτσι θα χρησιμοποιήσω τον παλιομοδίτικο τρόπο, τον διάλογο Δημιουργία εδώ, όπου έχουμε τον πίνακα προϋπολογισμού στα αριστερά και θα χρησιμοποιήσουμε το πεδίο Περιοχή και θα το συσχετίσω με τον πίνακα Περιοχή, το πεδίο Περιοχή . Εντάξει, δημιουργούνται 1/6. Θα επιλέξω Δημιουργία, πάλι από τον πίνακα Προϋπολογισμός πηγαίνουμε στο Προϊόν και, στη συνέχεια, θα τον συνδέσουμε στον πίνακα Προϊόντος, στο Προϊόν, κάντε κλικ στο OK. Από τον πίνακα Προϋπολογισμός το πεδίο Ημερομηνία, πηγαίνουμε στον πίνακα Ημερολόγιο και στο πεδίο Μοίρα, κάντε κλικ στο OK, είμαστε στα μισά του δρόμου, εντάξει. Από τον πίνακα Actuals, πηγαίνουμε Region, στον πίνακα Region, κάντε κλικ στο OK, από τον πίνακα Actuals στο Product και από τον πίνακα Actuals στο Ημερολόγιο. Στην πραγματικότητα πρόκειται να πάρω τις Τιμές και να την κάνω να πάει κάτω, εντάξει Σχεδίαση, διάταξη αναφοράς, εμφάνιση σε μορφή πίνακα για να λάβετε μια προβολή που προτιμώ, Επανάληψη όλων των ετικετών στοιχείων, εντάξει,αυτό είναι απολύτως καταπληκτικό! Τώρα έχουμε αυτόν τον μικροσκοπικό μικρό πίνακα, 50 δίσκους σε αυτόν τον πίνακα εκατοντάδων εγγραφών και έχουμε δημιουργήσει έναν ενιαίο συγκεντρωτικό πίνακα χάρη στο μοντέλο δεδομένων. Για καθένα από τα οποία μπορούμε να δούμε τον Προϋπολογισμό, μπορούμε να δούμε τα Έσοδα, κατανέμεται ανά Περιοχή, κατανέμεται ανά Μήνα και χωρίζεται ανά Προϊόν.
Τώρα αυτή η ιδέα ήρθε από τον Rob Collie, ο οποίος διαχειρίζεται το Power Pivot Pro και ο Rob έχει δημιουργήσει πολλά βιβλία εκεί έξω, το τελευταίο του είναι "Power Pivot and Power BI". Νομίζω ότι αυτό ήταν στην πραγματικότητα στο βιβλίο "Power Pivot Alchemy", είναι αυτό που το είδα και είπα "Λοιπόν, παρόλο που δεν έχω εκατομμύρια σειρές για να αναφέρω μέσω του Power Pivot, αυτό είναι που θα Έχω κάνει μια τεράστια διαφορά στη ζωή μου, έχοντας δύο σύνολα δεδομένων που δεν ταιριάζουν μεγέθη, και πρέπει να αναφέρω και από τα δύο. " Λοιπόν, αυτό το παράδειγμα και πολλά άλλα βρίσκονται σε αυτό το βιβλίο, θα λάβω τελικά ολόκληρο το podcast του βιβλίου, που φαίνεται ότι θα διαρκέσει δυόμισι μήνες. Αλλά μπορείτε να πάρετε ολόκληρο το βιβλίο σήμερα, ταυτόχρονα, να πάτε εκεί, να αγοράσετε το βιβλίο, 10 $ για το ηλεκτρονικό βιβλίο, 25 $ για το έντυπο βιβλίο και μπορείτε να έχετε όλες αυτές τις συμβουλές ταυτόχρονα.
Εντάξει, ένα πολύ μεγάλο επεισόδιο εδώ: έχουμε έναν μικρό προϋπολογισμό από πάνω προς τα κάτω και ένα πραγματικό ύψος, είναι διαφορετικά μεγέθη, αλλά χρησιμοποιώντας το μοντέλο δεδομένων στο Excel 2013… Και παρεμπιπτόντως εάν είστε το 2010, θα μπορούσατε , θεωρητικά, κάντε το με το πρόσθετο Power Pivot και ακολουθήστε όλα αυτά τα βήματα το 2010. Μετατρέψτε και τα δύο σύνολα δεδομένων σε έναν πίνακα Ctrl + T και, στη συνέχεια, ενώστε τους πίνακες σας για οτιδήποτε θέλετε να αναφέρετε, στο ετικέτα γραμμής, ή την ετικέτα στήλης, ή τα slicers, επομένως αντιγράψτε αυτές τις τιμές και καταργήστε τα διπλότυπα για τις ημερομηνίες. Πήρα πραγματικά τιμές και από τους δύο πίνακες, επειδή υπήρχαν κάποιες μοναδικές τιμές σε κάθε έναν, και στη συνέχεια χρησιμοποίησα το EOMONTH για να βγούμε εκεί, να κάνω αυτούς τους πίνακες ξυλουργών να ελέγχονται πίνακες. Είναι προαιρετικό, αλλά ονόμασα και τους 5 πίνακες, γιατί είναι ευκολότερο όταν ρυθμίζεις αυτές τις σχέσεις, αντί να ονομάζεται Πίνακας 1,Πίνακας2, Πίνακας3.
Έτσι, ξεκινήστε από τον πίνακα Προϋπολογισμός, Εισαγωγή, Συγκεντρωτικός Πίνακας, επιλέξτε το πλαίσιο για Μοντέλο δεδομένων και, στη συνέχεια, δημιουργήστε έναν Συγκεντρωτικό πίνακα χρησιμοποιώντας Προϋπολογισμός και Πραγματικός. Όλα τα υπόλοιπα προέρχονται από τους πίνακες ξυλουργικής, έτσι Περιοχή και Μήνας στην περιοχή της σειράς και της στήλης, τα slicers προήλθαν από τον πίνακα Προϊόντων Και έπειτα έπρεπε να δημιουργήσουμε 3 σχέσεις από τον Προϋπολογισμό στους ξυλουργούς, 3 σχέσεις από την Πραγματική έως τους ξυλουργούς και έχουμε έναν καταπληκτικό Συγκεντρωτικό πίνακα. Τώρα αύριο θα ρίξουμε μια ματιά στη χρήση της καρτέλας Power Pivot και στη δημιουργία ορισμένων επιπλέον υπολογισμών. Έτσι, όλα αυτά είναι δυνατά, είναι όταν θέλουμε να εισαγάγουμε ένα υπολογιζόμενο πεδίο, τότε πρέπει να πληρώσετε τα επιπλέον $ 2 το μήνα για να λάβετε την έκδοση Pro Plus του Office 365.
Λοιπόν, χάρη στον Rob Collie από το Power Pivot Pro για αυτήν την συμβουλή και σε ευχαριστώ που σταματήσατε, θα σας δούμε την επόμενη φορά για ένα άλλο netcast από!
Λήψη αρχείου
Κάντε λήψη του δείγματος αρχείου εδώ: Podcast2016.xlsx
