Δεν έχετε Power Pivot; Δεν πειράζει. Το μεγαλύτερο μέρος του Power Pivot είναι ενσωματωμένο στο Excel 2013 και ακόμη περισσότερο στο Excel 2016. Σήμερα, η συμβουλή μας από την Ash είναι να ενώνει πίνακες σε έναν συγκεντρωτικό πίνακα.
Κάθε Τετάρτη για επτά εβδομάδες, παρουσιάζω μια από τις αγαπημένες συμβουλές του Ash Sharma. Ο Ash είναι υπεύθυνος προϊόντων στην ομάδα του Excel. Η ομάδα του σας φέρνει συγκεντρωτικά τραπέζια και πολλά άλλα καλά πράγματα. Σήμερα, το αγαπημένο χαρακτηριστικό του Ash είναι η σύνδεση πολλών συνόλων δεδομένων χρησιμοποιώντας Σχέσεις και το μοντέλο δεδομένων
Ας πούμε ότι το τμήμα πληροφορικής σας παρέχει το σύνολο δεδομένων που εμφανίζεται στις στήλες Α: Δ. Υπάρχουν πεδία για τον πελάτη και την αγορά. Πρέπει να συνδυάσετε ορισμένες αγορές σε περιοχές. Κάθε πελάτης ανήκει σε έναν τομέα. Η περιοχή και ο τομέας δεν βρίσκονται στα αρχικά δεδομένα, αλλά έχετε πίνακες αναζήτησης για να παρέχετε αυτές τις πληροφορίες.
Κανονικά, θα ισοπεδώσετε τα δεδομένα χρησιμοποιώντας το VLOOKUP για να τραβήξετε δεδομένα από τους πορτοκαλί και τους κίτρινους πίνακες στον μπλε πίνακα. Αλλά επειδή το πεδίο κλειδιού δεν βρίσκεται στην αριστερή πλευρά κάθε πίνακα, θα πρέπει είτε να μεταβείτε σε INDEX και MATCH, είτε να αναδιατάξετε τους πίνακες αναζήτησης.
Ξεκινώντας από το Excel 2013, μπορείτε να αφήσετε τους πίνακες αναζήτησης όπου βρίσκονται και να τους συνδυάσετε στην αναφορά συγκεντρωτικού πίνακα.
Για να λειτουργήσει αυτή η τεχνική, και οι τρεις πίνακες πρέπει να μορφοποιηθούν ως πίνακας. Επιλέξτε ένα κελί σε κάθε σύνολο δεδομένων και επιλέξτε Home, Format as Table ή πατήστε Ctrl + T. Οι τρεις πίνακες θα ονομάζονται αρχικά Table1, Table2 και Table3. Χρησιμοποιώ την καρτέλα Σχεδιασμός εργαλείων πίνακα της κορδέλας και μετονομάζω κάθε πίνακα. Αλλάζω επίσης το χρώμα κάθε πίνακα. Σε αυτό το παράδειγμα, ο μπλε πίνακας ονομάζεται Δεδομένα. Ο πορτοκαλί πίνακας είναι RegionTable. Ο κίτρινος πίνακας είναι SectorTable.
Σημείωση
Μερικοί θα σας πουν ότι πρέπει να χρησιμοποιήσετε geeky ονόματα όπως Fact, TblSector και TblRegion. Αν σας αρέσει κάτι τέτοιο, απλώς κλέψτε το προστατευτικό τσέπης του και ενημερώστε τους ότι προτιμάτε αγγλικά ονόματα.
Για να μετονομάσετε έναν πίνακα, πληκτρολογήστε ένα νέο όνομα στο πλαίσιο στην αριστερή πλευρά της καρτέλας Σχεδιασμός εργαλείων πίνακα. Τα ονόματα των πινάκων δεν πρέπει να έχουν κενά.
Μόλις οριστούν οι τρεις πίνακες, κατευθυνθείτε στην καρτέλα Δεδομένα και κάντε κλικ στο Σχέσεις.
Στο παράθυρο διαλόγου Διαχείριση σχέσεων, κάντε κλικ στο Νέο. Στο παράθυρο διαλόγου Δημιουργία σχέσης, καθορίστε ότι το πεδίο Πελάτης του πίνακα δεδομένων σχετίζεται με το Πεδίο πελάτη του SectorTable. Κάντε κλικ στο OK.
Ορίστε μια άλλη νέα σχέση μεταξύ του πεδίου Market στα πεδία Data and RegionTable. Αφού ορίσετε και τις δύο σχέσεις, θα τις δείτε στο διάλογο Διαχείριση σχέσεων.
Συγχαρητήρια: μόλις δημιουργήσατε ένα μοντέλο δεδομένων στο βιβλίο εργασίας σας. Είναι καιρός να δημιουργήσετε έναν συγκεντρωτικό πίνακα.
Επιλέξτε το κενό κελί όπου θέλετε να εμφανίζεται ο συγκεντρωτικός πίνακας. Από προεπιλογή, ο διάλογος Δημιουργία Συγκεντρωτικού Πίνακα θα επιλέξει Χρήση του Μοντέλου Δεδομένων αυτού του Βιβλίου Εργασίας. Η θέση του συγκεντρωτικού πίνακα θα προεπιλεγεί στο κελί που επιλέξατε. Κάντε κλικ στο OK.
Η λίστα Πεδία Συγκεντρωτικού Πίνακα θα εμφανίσει και τους τρεις πίνακες. Χρησιμοποιήστε το τρίγωνο στα αριστερά ενός πίνακα για να επεκτείνετε το όνομα του πίνακα για να εμφανίσετε τα πεδία.
Αναπτύξτε τον πίνακα δεδομένων. Επιλέξτε το πεδίο Έσοδα. Θα μετακινηθεί αυτόματα στην περιοχή Τιμές. Αναπτύξτε το SectorTable. Επιλέξτε το πεδίο Sector. Θα μετακινηθεί στην περιοχή Rows. Αναπτύξτε τον Πίνακα Περιοχής. Σύρετε το πεδίο Περιοχή στην περιοχή Στήλες. Τώρα θα έχετε έναν συγκεντρωτικό πίνακα που θα συνοψίζει δεδομένα από τους τρεις πίνακες.
Σημείωση
Σε κάθε βιβλίο που έχω γράψει πριν από σήμερα, χρησιμοποιώ μια διαφορετική τεχνική για τη δημιουργία αυτής της αναφοράς. Αφού ορίσω τους τρεις πίνακες, επιλέγω το κελί A1 και το Insert, Pivot Table. Επιλέγω το πλαίσιο για Προσθήκη δεδομένων στο μοντέλο δεδομένων. Στη λίστα Συγκεντρωτικά πεδία, επιλέξτε Όλα από την κορυφή της λίστας. Επιλέξτε πεδία για την αναφορά και, στη συνέχεια, καθορίστε τις σχέσεις μετά το γεγονός. Η τεχνική που περιγράφεται παραπάνω φαίνεται πιο ομαλή και στην πραγματικότητα περιλαμβάνει ένα μικρό κομμάτι του προγραμματισμού μπροστά. Τα άτομα που χρησιμοποιούν το Option Explicit στον κώδικα VBA τους θα ήθελαν σίγουρα αυτή τη μέθοδο.
Οι σχέσεις στο μοντέλο δεδομένων κάνουν το Excel να μοιάζει περισσότερο με την Access ή τον SQL Server, αλλά με όλη την καλοσύνη του Excel.
Μου αρέσει να ρωτάω την ομάδα του Excel για τις αγαπημένες τους δυνατότητες. Κάθε Τετάρτη, θα μοιραστώ μια από τις απαντήσεις τους. Ευχαριστώ τον Ash Sharma για την παροχή αυτής της ιδέας.
Excel Thought Of the Day
Ζήτησα από τους φίλους μου στο Excel Master τις συμβουλές τους σχετικά με το Excel. Η σημερινή σκέψη να σκεφτούμε:
"Μην ψάχνετε αν είστε σε σχέση"
Ιωάννης Μιχαλούδης
