Διαδικασίες Χρηματοδότησης
Power Pivot for Excel Tutorial: Κορυφαίες περιπτώσεις και παραδείγματα χρήσης
Περίληψη των κυριότερων σημείων
Τι είναι το Power Pivot;- Παρουσιάστηκε στο Excel 2010 και 2013 ως πρόσθετο, αλλά τώρα εγγενές στην εφαρμογή, το Power Pivot είναι μέρος της στοίβας επιχειρηματικής ευφυΐας της Microsoft, ικανή (αλλά δεν περιορίζεται σε), να λειτουργεί μεγάλη ανάλυση δεδομένων χωρίς ειδική υποδομή ή λογισμικό.
- Σύμφωνα με τη Microsoft, « Power Pivot σας δίνει τη δυνατότητα να εισαγάγετε εκατομμύρια σειρές δεδομένων από πολλές πηγές δεδομένων σε ένα ενιαίο βιβλίο εργασίας του Excel, να δημιουργήσετε σχέσεις μεταξύ ετερογενών δεδομένων, να δημιουργήσετε υπολογισμένες στήλες και μέτρα χρησιμοποιώντας τύπους, να δημιουργήσετε Συγκεντρωτικούς Πίνακες και Συγκεντρωτικούς Χάρτες και να αναλύσετε περαιτέρω τα δεδομένα, ώστε να μπορείτε να κάνετε έγκαιρη επιχείρηση αποφάσεις χωρίς να απαιτείται βοήθεια ΤΠ. '
- Το Power Pivot δημιουργήθηκε σε άμεση ανταπόκριση στις μεγάλες απαιτήσεις δεδομένων των σύγχρονων αναγκών επιχειρηματικής ευφυΐας, τις οποίες οι προηγούμενες γενιές του Excel - δεδομένου του 1.048.576 ορίου σειράς ή των ελλείψεων ταχύτητας επεξεργασίας - προσπάθησαν να αντιμετωπίσουν.
- Το Power Pivot εκφράζεται από τη Microsoft χρησιμοποιώντας DAX (Data Analysis Expressions), η οποία είναι μια συλλογή συναρτήσεων, τελεστών και σταθερών που μπορούν να χρησιμοποιηθούν σε έναν τύπο ή έκφραση για τον υπολογισμό / επιστροφή μίας ή περισσότερων τιμών.
- Το Power Pivot σάς επιτρέπει να εισάγετε και να χειρίζεστε εκατοντάδες εκατομμύρια σειρές δεδομένων, ενώ το Excel έχει σκληρό περιορισμό πάνω από ένα εκατομμύριο σειρές.
- Το Power Pivot σάς επιτρέπει να εισάγετε δεδομένα από πολλές πηγές σε ένα βιβλίο εργασίας μιας πηγής χωρίς να χρειάζεται να δημιουργήσετε πολλά φύλλα προέλευσης και να αντιμετωπίσετε πιθανά ζητήματα ελέγχου έκδοσης και μεταφοράς.
- Το Power Pivot σάς επιτρέπει να χειρίζεστε τα εισαγόμενα δεδομένα, να τα αναλύετε και να εξαγάγετε συμπεράσματα χωρίς να επιβραδύνετε το σύστημα του υπολογιστή σας, όπως είναι το τυπικό του Basic Excel.
- Το Power Pivot σάς επιτρέπει να οπτικοποιήσετε και να χειριστείτε τα μεγάλα σύνολα δεδομένων σας με PivotCharts και Power BI, όπου το βασικό Excel δεν διαθέτει αυτές τις δυνατότητες.
- Δουλεύοντας μαζί σας ως σκέψη σύντροφος για το σχεδιασμό, τη δομή, την κατασκευή και την παράδοση μιας σειράς χρηματοοικονομικών μοντέλων, προϋπολογισμών και αναλύσεων / έργων μεγάλων δεδομένων, όλα σε πορεία για αποφάσεις σχετικά με δεσμευμένα έργα, συγχωνεύσεις και εξαγορές ή στρατηγικές επενδύσεις.
- Δημιουργώντας προσαρμοσμένα μοντέλα μοναδικά για την επιχείρησή σας, χρησιμοποιώντας το Power Pivot και άλλες εξειδικευμένες λειτουργίες excel.
- Από επίσης δημιουργία προκατασκευασμένων προτύπων μετάβασης που μπορεί να προσαρμοστεί με μοναδικό τρόπο από σχεδόν οποιονδήποτε στον οργανισμό σας, για σχεδόν οποιονδήποτε σκοπό, χρησιμοποιώντας το Power Pivot και άλλες ειδικές λειτουργίες του Excel.
- Εκπαιδεύοντας άτομα ή ομάδες εντός του οργανισμού σας για τα πάντα, από τα βασικά του excel, μοντελοποίησης και ανάλυσης έως προηγμένων ποσοτικών μεθόδων χρησιμοποιώντας Power Pivot, Power Pivot tables, Power Pivot charts και PowerQuery.
- Πραγματοποιώντας κάθε ένα από αυτά και πολλά άλλα, παράλληλα με το σχεδιασμός, δημιουργία και παράδοση μιας στιλβωμένης και επαγγελματικής παρουσίασης PowerPoint , μπροστά από τις στρατηγικές αποφάσεις.
Κατεβάστε το σύνολο δεδομένων εδώ για να ακολουθήσετε μαζί με το σεμινάριο.
Τα νέα ρούχα του αυτοκράτορα: Power Pivot Tutorial
Σε διάφορους τομείς και επιμέρους τομείς που καλύπτουν τη χρηματοδότηση, τη χρηματοοικονομική ανάλυση, τις χρηματοοικονομικές αγορές και τις χρηματοοικονομικές επενδύσεις, Microsoft Προέχω είναι βασιλιάς. Με την άφιξη και την εκθετική ανάπτυξη του μεγάλα δεδομένα , ωστόσο, καθοδηγούμενες από δεκαετίες συγκέντρωσης και συσσώρευσης δεδομένων, την έλευση φθηνού χώρου αποθήκευσης στο cloud και την άνοδο του διαδικτύου των πραγμάτων - δηλ., Ηλεκτρονικό εμπόριο, κοινωνικά μέσα και διασύνδεση συσκευών —Η παλαιότερη λειτουργικότητα και δυνατότητες της Excel έχουν φτάσει στα όριά τους.
Πιο συγκεκριμένα, οι περιορισμοί υποδομής και επεξεργασίας του Excel παλαιότερης γενιάς, όπως το όριο σειράς 1.048.576 σειρών ή η αναπόφευκτη επιβράδυνση της επεξεργασίας σε ό, τι αφορά μεγάλα σύνολα δεδομένων, πίνακες δεδομένων και διασυνδεδεμένα υπολογιστικά φύλλα, μείωσαν τη χρηστικότητα του ως αποτελεσματικό εργαλείο μεγάλων δεδομένων. Ωστόσο, το 2010, η Microsoft πρόσθεσε μια νέα διάσταση στο Excel, που ονομάζεται Power Pivot . Το Power Pivot προσέφερε λειτουργίες επιχειρηματικής ευφυΐας επόμενης γενιάς και επιχειρηματικής ανάλυσης στο Excel με την ικανότητά του να εξάγει, να συνδυάζει και να αναλύει σχεδόν απεριόριστα σύνολα δεδομένων χωρίς επεξεργασία της ταχύτητας. Παρά την κυκλοφορία του πριν από οκτώ χρόνια, ωστόσο, οι περισσότεροι χρηματοοικονομικοί αναλυτές εξακολουθούν να μην γνωρίζουν πώς να χρησιμοποιούν το Power Pivot και πολλοί δεν γνωρίζουν ότι υπάρχει ακόμη.
Σε αυτό το άρθρο, θα σας δείξω πώς να χρησιμοποιήσετε το Power Pivot για να ξεπεράσετε τα συνηθισμένα ζητήματα του Excel και να ρίξετε μια ματιά στα πρόσθετα βασικά πλεονεκτήματα του λογισμικού χρησιμοποιώντας μερικά παραδείγματα. Αυτό το σεμινάριο Power Pivot προορίζεται να χρησιμεύσει ως οδηγός για το τι μπορείτε να επιτύχετε με αυτό το εργαλείο, και στο τέλος, θα διερευνήσει μερικές περιπτώσεις δειγμάτων χρήσης όπου το Power Pivot αποδεικνύεται συχνά ανεκτίμητο.
Τι είναι το Power Pivot και γιατί είναι χρήσιμο;
Το Power Pivot είναι μια δυνατότητα του Microsoft Excel που εισήχθη ως πρόσθετο στο Excel 2010 και 2013 και είναι πλέον εγγενές χαρακτηριστικό για τα Excel 2016 και 365. Ως Microsoft εξηγεί , Το Power Pivot for Excel 'σας επιτρέπει να εισάγετε εκατομμύρια σειρές δεδομένων από πολλές πηγές δεδομένων σε ένα ενιαίο βιβλίο εργασίας του Excel, να δημιουργείτε σχέσεις μεταξύ ετερογενών δεδομένων, να δημιουργείτε υπολογισμένες στήλες και μέτρα χρησιμοποιώντας τύπους, να δημιουργείτε Συγκεντρωτικούς Πίνακες και Συγκεντρωτικούς Χάρτες και, στη συνέχεια, να αναλύετε περαιτέρω τα δεδομένα ώστε να μπορείτε να λαμβάνετε έγκαιρες επιχειρηματικές αποφάσεις χωρίς να χρειάζεστε βοήθεια πληροφορικής. '
Η κύρια γλώσσα έκφρασης που χρησιμοποιεί η Microsoft στο Power Pivot είναι DAX (Data Analysis Expressions), αν και άλλες μπορούν να χρησιμοποιηθούν σε συγκεκριμένες καταστάσεις. Και πάλι, όπως εξηγεί η Microsoft, «Το DAX είναι μια συλλογή συναρτήσεων, τελεστών και σταθερών που μπορούν να χρησιμοποιηθούν σε έναν τύπο ή μια έκφραση, για τον υπολογισμό και την επιστροφή μιας ή περισσότερων τιμών. Με πιο απλά λόγια, το DAX σάς βοηθά να δημιουργήσετε νέες πληροφορίες από δεδομένα που υπάρχουν ήδη στο μοντέλο σας. ' Ευτυχώς για όσους είναι ήδη εξοικειωμένοι με το Excel, οι τύποι DAX θα φαίνονται εξοικειωμένοι, καθώς πολλοί από τους τύπους έχουν παρόμοια σύνταξη (π.χ. SUM, AVERAGE, TRUNC).
Για λόγους σαφήνειας, τα βασικά οφέλη από τη χρήση του Power Pivot έναντι του βασικού Excel μπορούν να συνοψιστούν ως εξής:
- Σας επιτρέπει να εισάγετε και να χειρίζεστε εκατοντάδες εκατομμύρια σειρές δεδομένων, όπου το Excel έχει έναν σκληρό περιορισμό πάνω από ένα εκατομμύριο σειρές.
- Σας επιτρέπει να εισάγετε δεδομένα από πολλές πηγές σε ένα βιβλίο εργασίας μιας πηγής χωρίς να χρειάζεται να δημιουργήσετε πολλά φύλλα προέλευσης που αντιμετωπίζουν προβλήματα ελέγχου έκδοσης και μεταφοράς.
- Σας επιτρέπει να χειριστείτε τα εισαγόμενα δεδομένα, να τα αναλύσετε και να εξαγάγετε συμπεράσματα χωρίς να επιβραδύνετε τον υπολογιστή σας στο ρυθμό του σαλιγκαριού.
- Σας επιτρέπει να οπτικοποιήσετε τα δεδομένα με PivotCharts και Power BI.
Στις ακόλουθες ενότητες, θα εξετάσω όλα τα παραπάνω και θα σας δείξω πώς μπορεί να είναι χρήσιμο το Power Pivot για Excel.
Τρόπος χρήσης του Power Pivot
1) Εισαγωγή μεγάλων συνόλων δεδομένων
Όπως αναφέρθηκε προηγουμένως, ένας από τους σημαντικότερους περιορισμούς του Excel αφορά την εργασία με εξαιρετικά μεγάλα σύνολα δεδομένων. Ευτυχώς για εμάς, το Excel μπορεί τώρα να φορτώσει πολύ πάνω από το όριο ενός εκατομμυρίου σειρών απευθείας στο Power Pivot.
Για να το δείξω αυτό, δημιούργησα ένα δείγμα συνόλου δεδομένων πωλήσεων αξίας δύο ετών για έναν λιανοπωλητή αθλητικών ειδών με εννέα διαφορετικές κατηγορίες προϊόντων και τέσσερις περιοχές. Το σύνολο δεδομένων που προκύπτει είναι δύο εκατομμύρια σειρές.
Χρησιμοποιώντας το Δεδομένα καρτέλα στην κορδέλα, δημιούργησα ένα Νέο ερώτημα από το αρχείο CSV (βλ Δημιουργία νέου ερωτήματος παρακάτω). Αυτή η λειτουργικότητα κάλεσε το PowerQuery, αλλά από το Excel 2016 και το 365, ενσωματώθηκε πιο σφιχτά στην καρτέλα Δεδομένα του Excel.
Δημιουργία νέου ερωτήματος
Από ένα κενό βιβλίο εργασίας στο Excel έως τη φόρτωση και των δύο εκατομμυρίων σειρών στο Power Pivot, χρειάστηκε περίπου ένα λεπτό! Παρατηρήστε ότι μπόρεσα να πραγματοποιήσω κάποια ελαφριά μορφοποίηση δεδομένων προωθώντας την πρώτη σειρά για να γίνω τα ονόματα των στηλών. Τα τελευταία χρόνια, η λειτουργικότητα του Power Query έχει βελτιωθεί σημαντικά από ένα πρόσθετο του Excel σε ένα καλά ενσωματωμένο τμήμα της καρτέλας Δεδομένα στη γραμμή εργαλείων. Το Power Query μπορεί να περιστραφεί, να ισιώσει, να καθαρίσει και να διαμορφώσει τα δεδομένα σας μέσω της σειράς επιλογών και της δικής του γλώσσας, M.
2) Εισαγωγή δεδομένων από πολλές πηγές
Ένα από τα άλλα βασικά πλεονεκτήματα του Power Pivot για Excel είναι η δυνατότητα εύκολης εισαγωγής δεδομένων από πολλές πηγές. Προηγουμένως, πολλοί από εμάς δημιουργήσαμε πολλά φύλλα εργασίας για τις διάφορες πηγές δεδομένων μας. Συχνά, αυτή η διαδικασία περιελάμβανε σύνταξη κώδικα VBA και αντιγραφή / επικόλληση από αυτές τις διαφορετικές πηγές. Ευτυχώς για εμάς, ωστόσο, το Power Pivot σάς επιτρέπει να εισάγετε δεδομένα από διαφορετικές πηγές δεδομένων απευθείας στο Excel χωρίς να χρειάζεται να αντιμετωπίσετε τα ζητήματα που αναφέρονται παραπάνω.
Χρησιμοποιώντας τη λειτουργία ερωτήματος στην έκθεση 1, μπορούμε να αντλήσουμε οποιαδήποτε από τις ακόλουθες πηγές:
- Microsoft Azure
- Διακομιστής SQL
- Τερατάτα
- Salesforce
- Αρχεία JSON
- Βιβλία εργασίας του Excel
- …και πολλά άλλα
Επιπλέον, πολλαπλές πηγές δεδομένων μπορούν να συνδυαστούν είτε στη λειτουργία ερωτήματος είτε στο παράθυρο Power Pivot για την ενσωμάτωση δεδομένων. Για παράδειγμα, μπορείτε να τραβήξετε δεδομένα κόστους παραγωγής από ένα βιβλίο εργασίας του Excel και πραγματικά αποτελέσματα πωλήσεων από τον διακομιστή SQL μέσω του ερωτήματος στο Power Pivot. Από εκεί, μπορείτε να συνδυάσετε τα δύο σύνολα δεδομένων αντιστοιχίζοντας τους αριθμούς παρτίδας παραγωγής για να παράγετε μικτά περιθώρια ανά μονάδα.
3) Εργασία με μεγάλα σύνολα δεδομένων
Ένα άλλο βασικό πλεονέκτημα του Power Pivot για Excel είναι η ικανότητα χειρισμού και εργασίας με μεγάλα σύνολα δεδομένων για την εξαγωγή σχετικών συμπερασμάτων και αναλύσεων. Θα ακολουθήσω μερικά κοινά παραδείγματα παρακάτω για να σας δώσω μια αίσθηση της δύναμης του εργαλείου.
Μέτρα
Οι χρήστες του Excel θα συμφωνήσουν αναμφίβολα ότι οι Συγκεντρωτικοί Πίνακες είναι και οι δύο από τις πιο χρήσιμες και ταυτόχρονα, μία από τις πιο απογοητευτικές εργασίες που εκτελούμε. Απογοητευτικό ιδιαίτερα όταν πρόκειται για εργασία με μεγαλύτερα σύνολα δεδομένων. Ευτυχώς, το Power Pivot για Excel μας επιτρέπει να δημιουργούμε εύκολα και γρήγορα PivotTables όταν δουλεύουμε με μεγαλύτερα σύνολα δεδομένων.
Στην παρακάτω εικόνα, με τίτλο Δημιουργία μέτρων , παρατηρήστε πώς το παράθυρο Power Pivot χωρίζεται σε δύο παράθυρα. Το επάνω παράθυρο έχει τα δεδομένα και το κάτω μέρος περιλαμβάνει τα μέτρα. Ένα μέτρο είναι ένας υπολογισμός που εκτελείται σε ολόκληρο το σύνολο δεδομένων. Έχω εισαγάγει ένα μέτρο πληκτρολογώντας στο επισημασμένο κελί.
Total Sales:=SUM('Accounting Data'[Amount]) Αυτό δημιουργεί ένα νέο μέτρο που συνοψίζει τη στήλη Ποσό. Ομοίως, μπορώ να πληκτρολογήσω ένα άλλο μέτρο στο κελί παρακάτω
Average Sales:=AVERAGE('Accounting Data'[Amount]) Δημιουργία μέτρων
Από εκεί, παρακολουθήστε πόσο γρήγορα είναι να δημιουργήσετε έναν οικείο Συγκεντρωτικό Πίνακα σε ένα μεγάλο σύνολο δεδομένων.
Δημιουργία Συγκεντρωτικού Πίνακα
Πίνακες διαστάσεων
Ως χρηματοοικονομικοί αναλυτές που χρησιμοποιούν το Excel, γινόμαστε έμπειροι στη χρήση περίπλοκων τύπων για να κάμψουμε την τεχνολογία στη βούλησή μας. Κυριαρχούμε VLOOKUP, SUMIF, ακόμη και ο φοβισμένος INDEX(MATCH()). Ωστόσο, χρησιμοποιώντας το Power Pivot, μπορούμε να πετάξουμε πολλά από αυτά έξω από το παράθυρο.
Προσθήκη πίνακα που δημιουργήθηκε από χρήστη σε μοντέλο Power Pivot
Για να δείξω αυτήν τη λειτουργικότητα, δημιούργησα έναν μικρό πίνακα αναφοράς στον οποίο εκχώρησα κάθε κατηγορία σε έναν τύπο. Επιλέγοντας 'Προσθήκη στο μοντέλο δεδομένων', αυτός ο πίνακας φορτώνεται στο Power Pivot (βλ Προσθήκη πίνακα που δημιουργήθηκε από χρήστη σε μοντέλο Power Pivot πάνω από).
Δημιούργησα επίσης έναν πίνακα ημερομηνιών για χρήση με το σύνολο δεδομένων μας (βλ Δημιουργία πίνακα ημερομηνιών παρακάτω). Το Power Pivot για Excel διευκολύνει τη γρήγορη δημιουργία πίνακα ημερομηνιών για ενοποίηση κατά μήνες, τρίμηνα και ημέρες της εβδομάδας. Ο χρήστης μπορεί επίσης να δημιουργήσει έναν πιο προσαρμοσμένο πίνακα ημερομηνιών για ανάλυση ανά εβδομάδες, οικονομικά έτη ή ομαδοποιήσεις για συγκεκριμένους οργανισμούς.
Δημιουργία πίνακα ημερομηνιών
Υπολογισμένες στήλες
Εκτός από τα μέτρα, υπάρχει ένας άλλος τύπος υπολογισμού: υπολογιζόμενες στήλες. Οι χρήστες του Excel θα είναι άνετοι να γράφουν αυτούς τους τύπους, καθώς μοιάζουν πολύ με τη σύνταξη τύπων σε πίνακες δεδομένων. Έχω δημιουργήσει μια νέα υπολογισμένη στήλη παρακάτω (βλ Δημιουργία υπολογιζόμενης στήλης παρακάτω) ο οποίος ταξινομεί τον πίνακα Λογιστικών Δεδομένων κατά Ποσό. Οι πωλήσεις κάτω των 50 $ φέρουν την ένδειξη 'Μικρές' και όλες οι άλλες φέρουν την ένδειξη 'Μεγάλη'. Η φόρμουλα δεν είναι διαισθητική;
Δημιουργία υπολογιζόμενης στήλης
Σχέσεις
Στη συνέχεια, μπορούμε να δημιουργήσουμε μια σχέση μεταξύ του πεδίου Κατηγορία του πίνακα Λογιστικών Δεδομένων και του πεδίου Κατηγορία του πίνακα Κατηγοριών χρησιμοποιώντας την Προβολή Διαγράμματος. Επιπλέον, μπορούμε να ορίσουμε μια σχέση μεταξύ του πεδίου Ημερομηνία πωλήσεων του πίνακα Λογιστικών Δεδομένων και του πεδίου Ημερομηνία του πίνακα Ημερολογίου.
Καθορισμός σχέσεων
Τώρα, χωρίς κανένα SUMIF ή VLOOKUP απαιτούμενες συναρτήσεις, μπορούμε να δημιουργήσουμε έναν Συγκεντρωτικό Πίνακα που υπολόγιζε τις Συνολικές Πωλήσεις ανά έτος και πληκτρολογήσαμε, με ένα slicer για το Μέγεθος Συναλλαγής.
Συγκεντρωτικός πίνακας χρησιμοποιώντας σχέσεις
Εναλλακτικά, μπορούμε να δημιουργήσουμε ένα γράφημα μέσων πωλήσεων για κάθε ημέρα της εβδομάδας χρησιμοποιώντας τον νέο πίνακα ημερολογίου.
PivotChart Χρήση σχέσεων
Ενώ αυτό το γράφημα φαίνεται απλό, είναι εντυπωσιακό ότι χρειάστηκαν λιγότερο από δέκα δευτερόλεπτα για να δημιουργήσει μια ενοποίηση πάνω από δύο εκατομμύρια σειρές δεδομένων, χωρίς να προσθέσει μια νέα στήλη στα δεδομένα πωλήσεων.
Ενώ είμαστε σε θέση να εκτελέσουμε όλα αυτά τα σενάρια ενοποιημένων αναφορών, μπορούμε πάντα να αναλύουμε τα μεμονωμένα στοιχεία γραμμής. Διατηρούμε τα πολύ λεπτομερή δεδομένα μας.
Προηγμένες λειτουργίες
Μέχρι στιγμής, το μεγαλύτερο μέρος της ανάλυσης που έχω δείξει είναι σχετικά απλοί υπολογισμοί. Τώρα, θέλω να δείξω μερικές από τις πιο προηγμένες δυνατότητες αυτής της πλατφόρμας.
Έξυπνη ώρα
Συχνά, όταν εξετάζουμε τα οικονομικά αποτελέσματα, θέλουμε να τα συγκρίνουμε με ένα συγκρίσιμο χρονικό πλαίσιο από το προηγούμενο έτος. Το Power Pivot διαθέτει ορισμένες ενσωματωμένες λειτουργίες ευφυΐας χρόνου.
Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK()) Για παράδειγμα, η προσθήκη δύο παραπάνω μέτρων στον πίνακα Λογιστικών δεδομένων στο Power Pivot μου δίνει τη δυνατότητα να δημιουργήσω τον ακόλουθο Συγκεντρωτικό Πίνακα με λίγα κλικ.
νόμος κλεισίματος πραγματικό παράδειγμα
Συγκεντρωτικός πίνακας Time Intelligence
Αναντιστοιχίες
Ως χρηματοοικονομικός αναλυτής, ένα πρόβλημα που πρέπει να λύσω συχνά είναι το πρόβλημα των αναντιστοιχιών. Στο παράδειγμά μας, τα πραγματικά δεδομένα πωλήσεων εμφανίζονται στο επίπεδο κατηγορίας, αλλά ας προετοιμάσουμε έναν προϋπολογισμό που είναι μόνο σε εποχιακό επίπεδο. Για να προωθήσουμε αυτήν την αναντιστοιχία, θα προετοιμάσουμε έναν τριμηνιαίο προϋπολογισμό, ακόμη και μέσω των δεδομένων πωλήσεων που είναι καθημερινά.
Αναντιστοιχία λεπτομερειών - Πίνακας προϋπολογισμού
Με το Power Pivot για Excel, αυτή η ασυνέπεια επιλύεται εύκολα. Δημιουργώντας δύο επιπλέον πίνακες αναφοράς ή πίνακες διαστάσεων στην ονοματολογία της βάσης δεδομένων, μπορούμε τώρα να δημιουργήσουμε τις κατάλληλες σχέσεις για να αναλύσουμε τις πραγματικές πωλήσεις μας έναντι των προϋπολογισμένων ποσών.
Αναντιστοιχία των λεπτομερειών - Σχέσεις
Στο Excel, ο ακόλουθος Συγκεντρωτικός Πίνακας συγκεντρώνεται γρήγορα.
Αναντιστοιχία λεπτομερειών - Προϋπολογισμός έναντι πραγματικών αποτελεσμάτων
Επιπλέον, μπορούμε να ορίσουμε νέα μέτρα που υπολογίζουν τη διακύμανση μεταξύ των πραγματικών πωλήσεων και των προϋπολογισμένων πωλήσεων όπως παρακάτω:
Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1 Χρησιμοποιώντας αυτό το μέτρο, μπορούμε να δείξουμε τη διακύμανση σε έναν Συγκεντρωτικό Πίνακα.
Αναντιστοιχία των λεπτομερειών - Αποτελέσματα διακύμανσης
Ποσοστό του συνόλου
Τέλος, ας εξετάσουμε τις πωλήσεις σε μια συγκεκριμένη κατηγορία ως ποσοστό επί του συνόλου των πωλήσεων (π.χ., συνεισφορά κατηγορίας στις συνολικές πωλήσεις) και πωλήσεις σε μια συγκεκριμένη κατηγορία ως ποσοστό όλων των πωλήσεων του ίδιου τύπου (π.χ., συνεισφορά κατηγορίας σε εποχιακό τύπο εκπτώσεις). Δημιούργησα τα ακόλουθα δύο μέτρα:
Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category])) Αυτά τα μέτρα μπορούν τώρα να αναπτυχθούν σε έναν νέο Συγκεντρωτικό Πίνακα:
Ποσοστό του συνόλου
Παρατηρήστε πώς εκτελούνται οι υπολογισμοί και τα δυο το επίπεδο κατηγορίας και εποχιακού τύπου. Μου αρέσει πόσο γρήγορα και αβίαστα αυτοί οι υπολογισμοί εκτελούνται σε ένα τόσο μεγάλο σύνολο δεδομένων. Αυτά είναι μόνο μερικά παραδείγματα της κομψότητας και της απόλυτης υπολογιστικής δύναμης του Power Pivot.
Συμπίεση
Ένα άλλο πλεονέκτημα είναι ότι τα μεγέθη αρχείων συρρικνώνονται. Το αρχικό μέγεθος αρχείου ήταν 91MB και τώρα είναι κάτω από 4MB. Αυτή είναι μια συμπίεση του 96% του αρχικού αρχείου.
Μεγέθη αρχείου
Πώς συμβαίνει αυτό; Το Power Pivot χρησιμοποιεί το x Ταχύτητα μηχανή για συμπίεση των δεδομένων. Με απλούς όρους, τα δεδομένα αποθηκεύονται σε στήλες και όχι σε σειρές. Αυτή η μέθοδος αποθήκευσης επιτρέπει στον υπολογιστή να συμπιέζει διπλές τιμές. Στο σύνολο δεδομένων παραδείγματος, υπάρχουν μόνο τέσσερις περιοχές που επαναλαμβάνονται και στις δύο εκατομμύρια σειρές. Το Power Pivot για Excel μπορεί να αποθηκεύσει αυτά τα δεδομένα πιο αποτελεσματικά. Το αποτέλεσμα είναι ότι για δεδομένα που έχουν πολλές επαναλαμβανόμενες τιμές, κοστίζει πολύ λιγότερο για την αποθήκευση αυτών των δεδομένων.
Ένα πράγμα που πρέπει να σημειωθεί είναι ότι χρησιμοποίησα ποσά ολόκληρου του δολαρίου σε αυτό το σύνολο δεδομένων δείγματος. Αν είχα συμπεριλάβει δύο δεκαδικά ψηφία για να αντικατοπτρίσω σεντς, το αποτέλεσμα συμπίεσης θα μειωνόταν σε ένα ακόμη εντυπωσιακό 80% του αρχικού μεγέθους αρχείου.
Πίνακας SSAS
Τα μοντέλα Power Pivot μπορούν επίσης να επεκταθούν σε ολόκληρη την επιχείρηση. Ας υποθέσουμε ότι δημιουργείτε ένα μοντέλο Power Pivot που αρχίζει να κερδίζει πολλούς χρήστες στον οργανισμό ή τα δεδομένα αυξάνονται σε δέκα εκατομμύρια σειρές ή και τα δύο. Σε αυτό το σημείο, μπορεί να μην θέλετε τριάντα διαφορετικοί χρήστες να ανανεώσουν το μοντέλο ή να κάνουν αλλαγές. Το μοντέλο μπορεί να μετατραπεί απρόσκοπτα σε πίνακα SSAS. Όλοι οι πίνακες και οι σχέσεις διατηρούνται, αλλά τώρα μπορείτε να ελέγξετε τη συχνότητα ανανέωσης, να εκχωρήσετε ρόλους (π.χ. μόνο για ανάγνωση, ανάγνωση και επεξεργασία) σε διάφορους χρήστες και να αναπτύξετε μόνο μια μικρή διεπαφή Excel που συνδέεται με το μοντέλο πίνακα. Το αποτέλεσμα είναι ότι οι χρήστες σας θα μπορούσαν στη συνέχεια να έχουν πρόσβαση στο μοντέλο Tabular που έχει αναπτυχθεί με ένα μικρό βιβλίο εργασίας, αλλά δεν θα έχουν πρόσβαση στους τύπους και τα μέτρα.
4) Οπτικοποίηση και ανάλυση δεδομένων
Τύποι CUBE
Ένα από τα συνεχή αιτήματα των πελατών μου είναι ότι δημιουργώ αναφορές που συμμορφώνονται με μια αυστηρά καθορισμένη διάταξη. Έχω πελάτες που ζητούν συγκεκριμένα πλάτη στηλών, κωδικούς χρώματος RGB και προκαθορισμένα ονόματα και μεγέθη γραμματοσειρών. Εξετάστε τον ακόλουθο πίνακα ελέγχου:
Ενσωματωμένοι τύποι CUBE
Πώς συμπληρώνουμε τους αριθμούς πωλήσεων χωρίς να δημιουργούμε Συγκεντρωτικούς Πίνακες αν όλες οι πωλήσεις μας διαθέτουν Power Pivot για Excel; Χρησιμοποιώντας τύπους CUBE! Μπορούμε να γράψουμε τύπους CUBE σε οποιοδήποτε κελί του Excel και θα εκτελέσει τον υπολογισμό χρησιμοποιώντας το μοντέλο Power Pivot που έχουμε ήδη κατασκευάσει.
Για παράδειγμα, ο ακόλουθος τύπος πληκτρολογείται στο κελί στην ενότητα 'Συνολικές πωλήσεις 2016:'
SumExpPctTotalΤο πρώτο μέρος του τύπου, που επισημαίνεται με κίτρινο χρώμα, αναφέρεται στο όνομα του μοντέλου Power Pivot. Σε γενικές γραμμές, είναι συνήθως ThisWorkbookDataModel για νεότερες εκδόσεις του Power Pivot για Excel. Το τμήμα με πράσινο ορίζει ότι θέλουμε να χρησιμοποιήσουμε το μέτρο Συνολικές Πωλήσεις. Το μέρος με μπλε χρώμα δίνει οδηγίες στο Excel να φιλτράρει μόνο σειρές που έχουν ημερομηνία πώλησης με έτος ίσο με το 2016.
Πίσω από τα παρασκήνια, το Power Pivot έχει κατασκευάσει έναν κύβο Online Αναλυτικής Επεξεργασίας (OLAP) με τα δεδομένα, τις υπολογιζόμενες στήλες και τα μέτρα. Αυτή η σχεδίαση επιτρέπει στον χρήστη του Excel να έχει στη συνέχεια πρόσβαση στα δεδομένα λαμβάνοντας απευθείας με τις λειτουργίες CUBE. Χρησιμοποιώντας τους τύπους CUBE, κατάφερα να φτιάξω πλήρεις οικονομικές καταστάσεις που συμφωνούν με προκαθορισμένες διατάξεις. Αυτή η ικανότητα είναι ένα από τα κυριότερα σημεία της χρήσης του Power Pivot για Excel για οικονομική ανάλυση.
Power BI
Ένα άλλο πλεονέκτημα του Power Pivot για Excel είναι ότι μπορείτε να πάρετε γρήγορα οποιοδήποτε βιβλίο εργασίας Power Pivot που δημιουργείτε και να το μετατρέψετε γρήγορα σε μοντέλο Power BI. Εισάγοντας το βιβλίο εργασίας του Excel απευθείας στην εφαρμογή Power BI Desktop ή στο Power BI Online, μπορείτε να αναλύσετε, να οπτικοποιήσετε και να μοιραστείτε τα δεδομένα σας με οποιονδήποτε στον οργανισμό σας. Ουσιαστικά, το Power BI είναι Power Pivot, PowerQuery και SharePoint όλα σε ένα. Παρακάτω, έχω δημιουργήσει έναν πίνακα εργαλείων εισάγοντας το προηγούμενο βιβλίο εργασίας Power Pivot για Excel στην εφαρμογή Power BI desktop. Παρατηρήστε πόσο διαδραστική είναι η διεπαφή:
Power BI
Ένα σπουδαίο πράγμα για το Power BI είναι το Q&A της φυσικής γλώσσας. Για να το δείξω, ανέβασα το μοντέλο Power BI στον online λογαριασμό μου στο Power BI. Από τον ιστότοπο, μπορώ να κάνω ερωτήσεις και το Power BI κατασκευάζει την κατάλληλη ανάλυση καθώς πληκτρολογώ:
Q&A φυσικής γλώσσας
Αυτός ο τύπος ικανότητας ερωτήματος επιτρέπει στο χρήστη να κάνει ερωτήσεις σχετικά με το μοντέλο δεδομένων και να αλληλεπιδρά με τα δεδομένα με ευκολότερο τρόπο από ό, τι στο Excel.
Ένα άλλο πλεονέκτημα του Power BI είναι ότι οι προγραμματιστές της Microsoft κυκλοφορούν συνεχώς ενημερώσεις σε αυτό. Νέες δυνατότητες, πολλές από τις οποίες ζητήθηκε από τον χρήστη, απορρίπτονται κάθε μήνα. Το καλύτερο από όλα, είναι μια απρόσκοπτη μετάβαση από το Power Pivot για Excel. Έτσι, ο χρόνος που επενδύσατε μαθαίνοντας τους τύπους DAX μπορεί να αναπτυχθεί στο Power BI! Για τον αναλυτή που πρέπει να μοιραστεί την ανάλυσή του σε πολλούς χρήστες σε διάφορες συσκευές, το Power BI μπορεί να αξίζει να το εξερευνήσετε.
Βέλτιστες πρακτικές
Μόλις ξεκινήσετε, υπάρχουν μερικές βέλτιστες πρακτικές που πρέπει να ακολουθήσετε.
Το πρώτο είναι να αποφασίσετε προσεκτικά τι θα εισαγάγετε πρώτα. Θα χρησιμοποιήσετε ποτέ τη διεύθυνση κατοικίας του πωλητή; Πρέπει να γνωρίζω τη διεύθυνση email του πελάτη μου στο πλαίσιο αυτού του βιβλίου εργασίας; Εάν ο στόχος είναι να συγκεντρωθούν τα δεδομένα σε έναν πίνακα ελέγχου, τότε ορισμένα από τα διαθέσιμα δεδομένα δεν θα είναι απαραίτητα για αυτούς τους υπολογισμούς. Αφιερώνοντας χρόνο για την επιμέλεια των δεδομένων που εισέρχονται θα ανακουφίσει σημαντικά τα προβλήματα και τη χρήση μνήμης αργότερα όταν επεκταθεί το σύνολο δεδομένων σας.
Μια άλλη βέλτιστη πρακτική είναι να θυμάστε ότι το Power Pivot δεν είναι το Excel. Στο Excel, έχουμε συνηθίσει να δημιουργούμε υπολογισμούς επεκτείνοντας συνεχώς τα φύλλα εργασίας μας προς τα δεξιά. Το Power Pivot για Excel επεξεργάζεται πιο αποτελεσματικά τα δεδομένα εάν περιορίσουμε αυτήν την επιθυμία για πρόδηλο πεπρωμένο. Αντί να δημιουργείτε συνεχώς υπολογισμένες στήλες στα δεξιά των δεδομένων σας, μάθετε να γράφετε μέτρα στο κάτω τμήμα του παραθύρου. Αυτή η συνήθεια θα εξασφαλίσει μικρότερα μεγέθη αρχείων και γρηγορότερους υπολογισμούς.
Τέλος, θα πρότεινα τη χρήση απλών αγγλικών ονομάτων για μέτρα. Αυτό μου πήρε πολύ χρόνο να υιοθετήσω. Πέρασα τα πρώτα χρόνια δημιουργώντας ονόματα όπως Expense Line Item as Percent of Total Expenses, αλλά όταν άλλα άτομα άρχισαν να χρησιμοποιούν τα ίδια βιβλία εργασίας, είχα πολλές εξηγήσεις να κάνω. Τώρα, όταν ξεκινώ ένα νέο βιβλίο εργασίας, χρησιμοποιώ ονόματα μέτρησης όπως
|_+_|. Ενώ το όνομα είναι μεγαλύτερο, είναι πολύ πιο εύκολο για κάποιον άλλο να το χρησιμοποιήσει.
Θήκες χρήσης σε πραγματικό κόσμο
Σε αυτό το άρθρο έχω παρουσιάσει μόνο μερικούς από τους τρόπους με τους οποίους το Power Pivot για Excel σάς επιτρέπει να κάνετε ένα σημαντικό βήμα πέρα από το απλό vanilla Excel. Σκέφτηκα ότι θα ήταν χρήσιμο να επισημάνω ορισμένες πραγματικές περιπτώσεις χρήσης στις οποίες έχω διαπιστώσει ότι το Power Pivot για Excel είναι εξαιρετικά χρήσιμο.
Εδω είναι μερικά:
- Αναλύστε την απόδοση ενός μεγάλου χαρτοφυλακίου περιουσιακών στοιχείων σε διαφορετικά χρονικά διαστήματα: Δεδομένου ότι το Power Pivot για Excel μας επιτρέπει να καθορίσουμε μέτρα που συγκρίνουν μια χρονική περίοδο με μια προηγούμενη, μπορούμε γρήγορα να έχουμε απόδοση από τρίμηνο σε τρίμηνο, από έτος σε έτος και από μήνα σε μήνα σε κυλιόμενη βάση γράφοντας μόνο μερικά μέτρα.
- Συνοψίστε τα λογιστικά δεδομένα χρησιμοποιώντας προσαρμοσμένα επίπεδα συγκέντρωσης: Με τον προσδιορισμό κάθε στοιχείου γραμμής γενικού βιβλίου κατά όνομα, κατηγορία και οικονομική κατάσταση, μπορούν να δημιουργηθούν γρήγορα αναφορές που περιλαμβάνουν τα κατάλληλα στοιχεία γραμμής.
- Οι αλυσίδες μπορούν να εντοπίσουν πωλήσεις στο ίδιο κατάστημα: Χρησιμοποιώντας έναν πίνακα που χαρτογραφεί όταν τα καταστήματα συνδέονται στο διαδίκτυο, τα οικονομικά αποτελέσματα μπορούν να συγκριθούν με βάση το ίδιο κατάστημα.
- Εντοπίστε τις πωλήσεις με υψηλές και χαμηλότερες επιδόσεις: Μπορούν να δημιουργηθούν Συγκεντρωτικοί Πίνακες που επισημαίνουν τα πέντε κορυφαία SKU και τα πέντε SKU με βάση τις πωλήσεις, τα μικτά περιθώρια, τα χρονοδιαγράμματα παραγωγής κ.λπ.
- Οι έμποροι λιανικής μπορούν να ορίσουν πίνακες ημερολογίου που χρησιμοποιούν διαμόρφωση 4-4-5: Χρησιμοποιώντας έναν προσαρμοσμένο πίνακα ημερομηνιών, ένας λιανοπωλητής μπορεί να αντιστοιχίζει κάθε μέρα σε έναν συγκεκριμένο 4-4-5 μήνα εύκολα, και στη συνέχεια τα αποτελέσματα των ημερήσιων πωλήσεων μπορούν να μεταφερθούν στον αντίστοιχο μήνα.
Από Clunky Spreadsheets έως Modern Workbooks
Ως χρηματοοικονομικοί αναλυτές, απαιτείται να εκτελέσουμε πολύπλοκους υπολογισμούς σε συνεχώς διευρυνόμενα σύνολα δεδομένων. Δεδομένου ότι το Excel είναι ήδη το προεπιλεγμένο αναλυτικό εργαλείο, η καμπύλη εκμάθησης Power Pivot είναι εύκολη και πολλές από τις λειτουργίες αντικατοπτρίζουν τις εγγενείς λειτουργίες του Excel.
Με τη χρήση των λειτουργιών CUBE, το Power Pivot για Excel συνδυάζεται άψογα με τα υπάρχοντα βιβλία εργασίας σας στο Excel. Το υπολογιστικό κέρδος απόδοσης δεν μπορεί να αγνοηθεί. Υποθέτοντας 20% ταχύτερη ταχύτητα επεξεργασίας, η οποία είναι συντηρητική, ο οικονομικός αναλυτής που ξοδεύει έξι ώρες την ημέρα στο Excel μπορεί να εξοικονομήσει 300 ώρες το χρόνο!
Επιπλέον, μπορούμε πλέον να αναλύσουμε σύνολα δεδομένων που είναι πολύ μεγαλύτερα από ό, τι στο παρελθόν με το παραδοσιακό μας Excel. Με μοντέλα που έχουν σχεδιαστεί αποτελεσματικά, μπορούμε εύκολα να έχουμε 10 φορές τον αριθμό των δεδομένων που είχαμε προηγουμένως επιτραπεί στο παραδοσιακό Excel, διατηρώντας ταυτόχρονα γρήγορη αναλυτική ευελιξία. Με τη δυνατότητα μετατροπής των μοντέλων από Power Pivot σε SSAS Tabular, ο όγκος των δεδομένων που μπορούν να υποβληθούν σε επεξεργασία είναι 100-1.000 φορές αυτό που μπορούμε να επιτύχουμε στο Excel.
Το Power Pivot για την ικανότητα του Excel να εκτελεί γρήγορους υπολογισμούς σε μεγάλες ποσότητες δεδομένων και εξακολουθεί να διατηρεί την ικανότητα να εμβαθύνετε στις λεπτομέρειες μπορεί να μετατρέψει την οικονομική ανάλυση από clunky υπολογιστικά φύλλα σε σύγχρονα βιβλία εργασίας.
Εάν σας ενδιαφέρει να δοκιμάσετε το Power Pivot για Excel, παρακάτω είναι μερικά χρήσιμα υλικά για να ξεκινήσετε.
Χρήσιμες αναφορές και οδηγοί
Collie, R., & Singh, A. (2016). Power Pivot και Power BI: Ο οδηγός χρήσης του Excel για DAX, Power Query, Power BI & Power Pivot στο Excel 2010-2016. Ηνωμένες Πολιτείες: Holy Macro! Βιβλία.
Ferrari, A., & Russo, M. (2015). Ο οριστικός οδηγός για το DAX: Επιχειρηματική ευφυΐα με Microsoft Excel, SQL Server Analysis Services και Power BI. Ηνωμένες Πολιτείες: Microsoft Press, ΗΠΑ.
Κατανόηση των βασικών
Τι είναι ένας πίνακας Power Pivot;
Το Power Pivot είναι μια δυνατότητα του Excel που επιτρέπει την εισαγωγή, τον χειρισμό και την ανάλυση μεγάλων δεδομένων χωρίς απώλεια ταχύτητας / λειτουργικότητας. Οι πίνακες Power Pivot είναι συγκεντρωτικοί πίνακες που επιτρέπουν στον χρήστη να αναμειγνύει δεδομένα από διαφορετικούς πίνακες, παρέχοντάς τους ισχυρή αλυσίδα φίλτρου όταν εργάζεται σε πολλούς πίνακες.
Πώς μπορώ να δημιουργήσω έναν πίνακα Power Pivot;
Αρχικά, εισαγάγετε το σύνολο δεδομένων σας στο βιβλίο εργασίας Power Pivot. Στη συνέχεια, στο παράθυρο Power Pivot στην καρτέλα Home Pivot Home, κάντε κλικ στο Συγκεντρωτικός πίνακας. Στη συνέχεια, επιλέξτε 'Νέο φύλλο εργασίας' (το Excel θα προσθέσει έναν κενό Συγκεντρωτικό Πίνακα). Στη συνέχεια, επιλέξτε τον κενό Συγκεντρωτικό Πίνακα και ακολουθήστε τις οδηγίες που περιγράφονται εκεί.
Πώς μπορώ να δημιουργήσω ένα γράφημα Power Pivot;
Αρχικά, εισαγάγετε το σύνολο δεδομένων σας στο βιβλίο εργασίας Power Pivot. Στη συνέχεια, δημιουργήστε έναν πίνακα Power Pivot. Στη συνέχεια, στην καρτέλα Power Pivot, κάντε κλικ στο βέλος κάτω από το PivotTable και επιλέξτε PivotChart. Επιλέξτε 'Υφιστάμενο φύλλο εργασίας' και κάντε κλικ στο 'OK'. Το Excel θα προσθέσει ένα κενό PivotChart στο ίδιο φύλλο εργασίας. Ακολουθήστε τις οδηγίες εκεί.
