Οφέλη του Pivot by Data Model

Κατά τη δημιουργία ενός συγκεντρωτικού πίνακα στο Excel, στο πρώτο κιόλας πλαίσιο διαλόγου, όπου μας ζητείται να ορίσουμε το αρχικό εύρος και να επιλέξουμε ένα μέρος για την εισαγωγή του συγκεντρωτικού πίνακα, υπάρχει ένα δυσδιάκριτο αλλά πολύ σημαντικό πλαίσιο ελέγχου παρακάτω – Προσθέστε αυτά τα δεδομένα στο μοντέλο δεδομένων (Προσθέστε αυτά τα δεδομένα στο μοντέλο δεδομένων) και, λίγο πιο πάνω, ο διακόπτης Χρησιμοποιήστε το μοντέλο δεδομένων αυτού του βιβλίου (Χρησιμοποιήστε το μοντέλο δεδομένων αυτού του βιβλίου εργασίας):

Δυστυχώς, πολλοί χρήστες που είναι εξοικειωμένοι με τους συγκεντρωτικούς πίνακες για μεγάλο χρονικό διάστημα και τους χρησιμοποιούν με επιτυχία στην εργασία τους, μερικές φορές δεν κατανοούν πραγματικά το νόημα αυτών των επιλογών και δεν τις χρησιμοποιούν ποτέ. Και μάταια. Εξάλλου, η δημιουργία ενός συγκεντρωτικού πίνακα για το μοντέλο δεδομένων μας δίνει αρκετά πολύ σημαντικά πλεονεκτήματα σε σύγκριση με τον κλασικό συγκεντρωτικό πίνακα του Excel.

Ωστόσο, πριν εξετάσουμε αυτά τα «κουλούρια» από κοντά, ας καταλάβουμε πρώτα τι είναι στην πραγματικότητα αυτό το μοντέλο δεδομένων;

Τι είναι ένα μοντέλο δεδομένων

Μοντέλο δεδομένων (συντομογραφία MD ή DM = Μοντέλο δεδομένων) είναι μια ειδική περιοχή μέσα σε ένα αρχείο Excel όπου μπορείτε να αποθηκεύσετε δεδομένα σε πίνακα – έναν ή περισσότερους πίνακες συνδεδεμένους, εάν θέλετε, μεταξύ τους. Στην πραγματικότητα, αυτή είναι μια μικρή βάση δεδομένων (κύβος OLAP) ενσωματωμένη σε ένα βιβλίο εργασίας του Excel. Σε σύγκριση με την κλασική αποθήκευση δεδομένων με τη μορφή κανονικών (ή έξυπνων) πινάκων σε φύλλα του ίδιου του Excel, το μοντέλο δεδομένων έχει πολλά σημαντικά πλεονεκτήματα:

• Οι πίνακες μπορούν να είναι μέχρι 2 δισεκατομμύρια γραμμές, και ένα φύλλο Excel μπορεί να χωρέσει λίγο περισσότερο από 1 εκατομμύριο.
• Παρά το γιγάντιο μέγεθος, η επεξεργασία τέτοιων πινάκων (φιλτράρισμα, ταξινόμηση, υπολογισμοί σε αυτούς, περίληψη κτιρίου κ.λπ.) πραγματοποιείται πολύ γρήγορα Πολύ πιο γρήγορα από το ίδιο το Excel.
• Με τα δεδομένα στο Μοντέλο, μπορείτε να εκτελέσετε πρόσθετους (αν θέλετε, πολύ σύνθετους) υπολογισμούς χρησιμοποιώντας ενσωματωμένη γλώσσα DAX.
  
• Όλες οι πληροφορίες που φορτώνονται στο μοντέλο δεδομένων είναι πολύ έντονα συμπιεσμένα χρησιμοποιώντας έναν ειδικό ενσωματωμένο αρχειοθέτηση και μάλλον αυξάνει μέτρια το μέγεθος του αρχικού αρχείου Excel.
  

Το μοντέλο διαχειρίζεται και υπολογίζεται από ένα ειδικό πρόσθετο ενσωματωμένο στο Microsoft Excel – Powerpivotγια το οποίο έχω ήδη γράψει. Για να το ενεργοποιήσετε, στην καρτέλα προγραμματιστή κλικ Πρόσθετα COM (Προγραμματιστής — Πρόσθετα COM) και επιλέξτε το κατάλληλο πλαίσιο:

Εάν καρτέλες προγραμματιστή (Προγραμματιστής)δεν μπορείτε να το δείτε στην κορδέλα, μπορείτε να το ενεργοποιήσετε από μέσα Αρχείο – Επιλογές – Ρύθμιση κορδέλας (Αρχείο — Επιλογές — Προσαρμογή κορδέλας). Εάν στο παράθυρο που εμφανίζεται παραπάνω στη λίστα των πρόσθετων COM δεν έχετε Power Pivot, τότε δεν περιλαμβάνεται στην έκδοση του Microsoft Office που διαθέτετε 🙁

Στην καρτέλα Power Pivot που εμφανίζεται, θα υπάρχει ένα μεγάλο ανοιχτό πράσινο κουμπί Κοινωνικών Δικτύων (Διαχειρίζονται), κάνοντας κλικ στο οποίο θα ανοίξει το παράθυρο Power Pivot πάνω από το Excel, όπου θα δούμε τα περιεχόμενα του μοντέλου δεδομένων του τρέχοντος βιβλίου:

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

Φόρτωση πινάκων στο μοντέλο δεδομένων

Για να φορτώσουμε δεδομένα στο Μοντέλο, πρώτα μετατρέπουμε τον πίνακα σε μια δυναμική «έξυπνη» συντόμευση πληκτρολογίου Ctrl+T και δώστε του ένα φιλικό όνομα στην καρτέλα Κατασκευαστής (Σχέδιο). Αυτό είναι ένα απαραίτητο βήμα.

Στη συνέχεια, μπορείτε να χρησιμοποιήσετε οποιαδήποτε από τις τρεις μεθόδους για να διαλέξετε:

• Πατήστε το πλήκτρο Προσθήκη στο μοντέλο (Προσθήκη στο μοντέλο δεδομένων) αυτί Powerpivot αυτί Αρχική (Σπίτι).
  
• Επιλογή ομάδων Εισαγωγή – Συγκεντρωτικός Πίνακας (Εισαγωγή — Συγκεντρωτικός πίνακας) και ενεργοποιήστε το πλαίσιο ελέγχου Προσθέστε αυτά τα δεδομένα στο μοντέλο δεδομένων (Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων). Σε αυτήν την περίπτωση, σύμφωνα με τα δεδομένα που έχουν φορτωθεί στο Μοντέλο, δημιουργείται αμέσως ένας συγκεντρωτικός πίνακας.
  
• Στην καρτέλα Για προχωρημένους ημερομηνία (Ημερομηνία) κάντε κλικ στο κουμπί Από Πίνακας/Εύρος (Από πίνακα/Εύρος)για να φορτώσουμε τον πίνακα μας στον επεξεργαστή Power Query. Αυτή η διαδρομή είναι η μεγαλύτερη, αλλά, αν θέλετε, εδώ μπορείτε να εκτελέσετε πρόσθετο καθαρισμό δεδομένων, επεξεργασία και κάθε είδους μετασχηματισμούς, στους οποίους το Power Query είναι πολύ ισχυρό.

  Στη συνέχεια, τα χτενισμένα δεδομένα μεταφορτώνονται στο Μοντέλο με την εντολή Αρχική σελίδα — Κλείσιμο και φόρτωση — Κλείσιμο και φόρτωση σε… (Αρχική — Κλείσιμο&Φόρτωση — Κλείσιμο&Φόρτωση σε…). Στο παράθυρο που ανοίγει, επιλέξτε την επιλογή Απλώς δημιουργήστε μια σύνδεση (Δημιουργία μόνο σύνδεσης) και, το πιο σημαντικό, βάλτε ένα τικ Προσθέστε αυτά τα δεδομένα στο μοντέλο δεδομένων (Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων).
  

Δημιουργούμε μια σύνοψη του μοντέλου δεδομένων

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

• ΠΑΤΗΣΤΕ το ΚΟΥΜΠΙ συνοπτικό πίνακα (Συγκεντρωτικός πίνακας) στο παράθυρο Power Pivot.
• Επιλέξτε εντολές στο Excel Εισαγωγή – Συγκεντρωτικός Πίνακας και μεταβείτε στη λειτουργία Χρησιμοποιήστε το μοντέλο δεδομένων αυτού του βιβλίου (Εισαγωγή — Συγκεντρωτικός πίνακας — Χρησιμοποιήστε το μοντέλο δεδομένων αυτού του βιβλίου εργασίας).
• Επιλογή ομάδων Εισαγωγή – Συγκεντρωτικός Πίνακας (Εισαγωγή — Συγκεντρωτικός πίνακας) και ενεργοποιήστε το πλαίσιο ελέγχου Προσθέστε αυτά τα δεδομένα στο μοντέλο δεδομένων (Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων). Ο τρέχων «έξυπνος» πίνακας θα φορτωθεί στο Μοντέλο και θα δημιουργηθεί ένας συνοπτικός πίνακας για ολόκληρο το Μοντέλο.

Τώρα που καταλάβαμε πώς να φορτώνουμε δεδομένα στο μοντέλο δεδομένων και να δημιουργήσουμε μια σύνοψη σε αυτό, ας εξερευνήσουμε τα πλεονεκτήματα και τα πλεονεκτήματα που μας προσφέρει αυτό.

Όφελος 1: Σχέσεις μεταξύ πινάκων χωρίς χρήση τύπων

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

Στην περίπτωση μιας περίληψης του Μοντέλου Δεδομένων, όλα είναι πολύ πιο απλά. Αρκεί να ρυθμίσετε τις σχέσεις μεταξύ των πινάκων μία φορά στο παράθυρο του Power Pivot – και είναι έτοιμο. Για να το κάνετε αυτό, στην καρτέλα Powerpivot πάτα το κουμπί Κοινωνικών Δικτύων (Διαχειρίζονται) και μετά στο παράθυρο που εμφανίζεται – το κουμπί Προβολή γραφήματος (Προβολή διαγράμματος). Απομένει να σύρετε τα κοινά (κλειδιά) ονόματα στηλών (πεδία) μεταξύ των πινάκων για να δημιουργήσετε συνδέσμους:

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

Όφελος 2: Μετρήστε μοναδικές τιμές

Ένας κανονικός συγκεντρωτικός πίνακας μας δίνει την ευκαιρία να επιλέξουμε μία από τις πολλές ενσωματωμένες συναρτήσεις υπολογισμού: άθροισμα, μέσος όρος, μέτρηση, ελάχιστο, μέγιστο, κ.λπ. Στη σύνοψη του μοντέλου δεδομένων, μια πολύ χρήσιμη συνάρτηση προστίθεται σε αυτήν την τυπική λίστα για την καταμέτρηση αριθμός μοναδικών (μη επαναλαμβανόμενες τιμές). Με τη βοήθειά του, για παράδειγμα, μπορείτε εύκολα να μετρήσετε τον αριθμό των μοναδικών ειδών αγαθών (γκάμα) που πουλάμε σε κάθε πόλη.

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

Όφελος 3: Προσαρμοσμένες φόρμουλες DAX

Μερικές φορές πρέπει να εκτελέσετε διάφορους πρόσθετους υπολογισμούς σε συγκεντρωτικούς πίνακες. Σε κανονικές περιλήψεις, αυτό γίνεται χρησιμοποιώντας υπολογισμένα πεδία και αντικείμενα, ενώ η σύνοψη του μοντέλου δεδομένων χρησιμοποιεί μέτρα σε μια ειδική γλώσσα DAX (DAX = Εκφράσεις ανάλυσης δεδομένων).

Για να δημιουργήσετε ένα μέτρο, επιλέξτε στην καρτέλα Powerpivot εντολή Μέτρα – Δημιουργία Μέτρου (Μέτρα — Νέο μέτρο) ή απλώς κάντε δεξί κλικ στον πίνακα στη λίστα Συγκεντρωτικά πεδία και επιλέξτε Προσθέστε μέτρο (Προσθήκη μέτρου) στο μενού περιβάλλοντος:

Στο παράθυρο που ανοίγει, ορίστε:

• Όνομα πίνακαόπου θα αποθηκευτεί το δημιουργημένο μέτρο.
• Όνομα μέτρησης – οποιοδήποτε όνομα καταλαβαίνετε για το νέο πεδίο.
• Περιγραφή - προαιρετικός.
• Τύπος – το πιο σημαντικό, γιατί εδώ είτε εισάγουμε χειροκίνητα είτε κάνουμε κλικ στο κουμπί fx και επιλέξτε μια συνάρτηση DAX από τη λίστα, η οποία θα πρέπει να υπολογίσει το αποτέλεσμα όταν ρίξουμε το μέτρο μας στην περιοχή Τιμών.
• Στο κάτω μέρος του παραθύρου, μπορείτε να ορίσετε αμέσως τη μορφή αριθμού για το μέτρο στη λίστα κατηγορία.
  

Η γλώσσα DAX δεν είναι πάντα κατανοητή γιατί δεν λειτουργεί με μεμονωμένες τιμές, αλλά με ολόκληρες στήλες και πίνακες, δηλαδή απαιτεί κάποια αναδιάρθρωση της σκέψης μετά από τους κλασικούς τύπους του Excel. Ωστόσο, αξίζει τον κόπο, γιατί η δύναμη των δυνατοτήτων του στην επεξεργασία μεγάλων ποσοτήτων δεδομένων είναι δύσκολο να υπερεκτιμηθεί.

Πλεονέκτημα 4: Προσαρμοσμένες ιεραρχίες πεδίων

Συχνά, όταν δημιουργείτε τυπικές αναφορές, πρέπει να ρίχνετε τους ίδιους συνδυασμούς πεδίων σε συγκεντρωτικούς πίνακες σε μια δεδομένη ακολουθία, για παράδειγμα Έτος-Τρίμηνο-Μήνας-Ημέρα, ή Κατηγορία-Προϊόν, ή Χώρα-Πόλη-Πελάτης κ.λπ. Στη σύνοψη του μοντέλου δεδομένων, αυτό το πρόβλημα επιλύεται εύκολα δημιουργώντας το δικό σας ιεραρχίες — προσαρμοσμένα σύνολα πεδίων.

Στο παράθυρο Power Pivot, μεταβείτε στη λειτουργία γραφήματος με το κουμπί Προβολή γραφήματος αυτί Αρχική (Αρχική — Προβολή διαγράμματος), επιλέξτε με Ctrl τα επιθυμητά πεδία και κάντε δεξί κλικ πάνω τους. Το μενού περιβάλλοντος θα περιέχει την εντολή Δημιουργία Ιεραρχίας (Δημιουργία ιεραρχίας):

Η δημιουργημένη ιεραρχία μπορεί να μετονομαστεί και να συρθεί μέσα σε αυτήν με το ποντίκι τα απαιτούμενα πεδία, ώστε αργότερα με μία κίνηση να μπουν στη σύνοψη:

Όφελος 5: Προσαρμοσμένα στένσιλ

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

Για να το κάνετε αυτό, στην καρτέλα Ανάλυση συγκεντρωτικού πίνακα στην αναπτυσσόμενη λίστα Πεδία, στοιχεία και σύνολα υπάρχουν αντίστοιχες εντολές (Ανάλυση — Πεδία, Ιtemps & Sets — Δημιουργία συνόλου με βάση στοιχεία γραμμής/στήλης):

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

Όλα τα δημιουργημένα σύνολα θα εμφανίζονται στον πίνακα Πεδία Συγκεντρωτικού Πίνακα σε ξεχωριστό φάκελο, από όπου μπορούν να συρθούν ελεύθερα στις περιοχές σειρών και στηλών οποιουδήποτε νέου Συγκεντρωτικού Πίνακα:

Όφελος 6: Επιλεκτική απόκρυψη πινάκων και στηλών

Αν και αυτό είναι ένα μικρό, αλλά πολύ ευχάριστο πλεονέκτημα σε ορισμένες περιπτώσεις. Κάνοντας δεξί κλικ στο όνομα του πεδίου ή στην καρτέλα του πίνακα στο παράθυρο Power Pivot, μπορείτε να επιλέξετε την εντολή Απόκρυψη από το Client Toolkit (Απόκρυψη από τα Εργαλεία Πελατών):

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

Όφελος 7. Προηγμένη διερεύνηση

Εάν κάνετε διπλό κλικ σε οποιοδήποτε κελί στην περιοχή τιμών σε έναν κανονικό συγκεντρωτικό πίνακα, τότε το Excel εμφανίζει σε ξεχωριστό φύλλο ένα αντίγραφο του τμήματος δεδομένων προέλευσης που συμμετείχε στον υπολογισμό αυτού του κελιού. Αυτό είναι ένα πολύ εύχρηστο πράγμα, που επίσημα ονομάζεται Drill-down (που συνήθως λένε "αποτυχία").

Στη σύνοψη του μοντέλου δεδομένων, αυτό το εύχρηστο εργαλείο λειτουργεί πιο διακριτικά. Στέκοντας σε οποιοδήποτε κελί με το αποτέλεσμα που μας ενδιαφέρει, μπορείτε να κάνετε κλικ στο εικονίδιο με ένα μεγεθυντικό φακό που αναδύεται δίπλα του (λέγεται Express Trends) και, στη συνέχεια, επιλέξτε οποιοδήποτε πεδίο που σας ενδιαφέρει σε οποιονδήποτε σχετικό πίνακα:

Μετά από αυτό, η τρέχουσα τιμή (Model = Explorer) θα μεταβεί στην περιοχή φίλτρου και η σύνοψη θα δημιουργηθεί από τα γραφεία:

Φυσικά, μια τέτοια διαδικασία μπορεί να επαναληφθεί πολλές φορές, εμβαθύνοντας με συνέπεια στα δεδομένα σας προς την κατεύθυνση που σας ενδιαφέρει.

Πλεονέκτημα 8: Μετατρέψτε τις συναρτήσεις Pivot σε Cube

Εάν επιλέξετε οποιοδήποτε κελί στη σύνοψη για το μοντέλο δεδομένων και, στη συνέχεια, επιλέξτε στην καρτέλα Ανάλυση συγκεντρωτικού πίνακα εντολή Εργαλεία OLAP – Μετατροπή σε τύπους (Ανάλυση — Εργαλεία OLAP — Μετατροπή σε τύπους), τότε ολόκληρη η περίληψη θα μετατραπεί αυτόματα σε τύπους. Τώρα οι τιμές των πεδίων στην περιοχή γραμμής-στήλης και τα αποτελέσματα στην περιοχή τιμών θα ανακτηθούν από το μοντέλο δεδομένων χρησιμοποιώντας τις ειδικές συναρτήσεις κύβου: CUBEVALUE και CUBEMEMBER:

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

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

• Ανάλυση σχεδίου-γεγονότος σε συγκεντρωτικό πίνακα με Power Pivot και Power Query
• Συγκεντρωτικός πίνακας με κεφαλίδα πολλών γραμμών
• Δημιουργήστε μια βάση δεδομένων στο Excel χρησιμοποιώντας το Power Pivot