Έχετε χρησιμοποιήσει ποτέ τη συνάρτηση VLOOKUP για να μεταφέρετε μια στήλη από έναν πίνακα σε έναν άλλο; Τώρα που το Excel διαθέτει ένα ενσωματωμένο μοντέλο δεδομένων, η συνάρτηση VLOOKUP είναι παρωχημένη. Μπορείτε να δημιουργήσετε μια σχέση μεταξύ δύο πινάκων δεδομένων, με βάση δεδομένα που ταιριάζουν σε κάθε πίνακα. Στη συνέχεια, μπορείτε να δημιουργήσετε φύλλα του Power View και να δημιουργήσετε Συγκεντρωτικούς Πίνακες και άλλες αναφορές με πεδία από κάθε πίνακα, ακόμα και όταν οι πίνακες προέρχονται από διαφορετικές προελεύσεις. Για παράδειγμα, εάν έχετε δεδομένα πωλήσεων πελατών, μπορεί να θέλετε να εισαγάγετε και να συσχετίσετε δεδομένα χρονικής ευφυΐας για να αναλύσετε μοτίβα πωλήσεων ανά έτος και μήνα.
Όλοι οι πίνακες σε ένα βιβλίο εργασίας παρατίθενται στις λίστες πεδίων του Συγκεντρωτικού Πίνακα και του Power View.
Όταν εισάγετε σχετικούς πίνακες από μια σχεσιακή βάση δεδομένων, το Excel μπορεί να δημιουργεί συχνά αυτές τις σχέσεις στο μοντέλο δεδομένων που δημιουργεί στο παρασκήνιο. Για όλες τις άλλες περιπτώσεις, θα χρειαστεί να δημιουργήσετε σχέσεις με μη αυτόματο τρόπο.
-
Βεβαιωθείτε ότι το βιβλίο εργασίας περιέχει τουλάχιστον δύο πίνακες και ότι κάθε πίνακας έχει μια στήλη που μπορεί να αντιστοιχιστεί σε μια στήλη σε έναν άλλο πίνακα.
-
Κάντε ένα από τα εξής: Μορφοποιήστε τα δεδομένα ως πίνακα ή Εισαγάγετε εξωτερικά δεδομένα ως πίνακα σε ένα νέο φύλλο εργασίας.
-
Δώστε σε κάθε πίνακα ένα χαρακτηριστικό όνομα: Στα Εργαλεία πίνακα, κάντε κλικ στις επιλογές Σχεδίαση > Όνομα πίνακα και πληκτρολογήστε ένα όνομα.
-
Βεβαιωθείτε ότι η στήλη σε έναν από τους πίνακες έχει μοναδικές τιμές δεδομένων, χωρίς διπλότυπα. Το Excel μπορεί να δημιουργήσει τη σχέση μόνο όταν μια στήλη περιέχει μοναδικές τιμές.
Για παράδειγμα, για να συσχετίσετε πωλήσεις πελατών με χρονική ευφυΐα και οι δύο πίνακες πρέπει να περιλαμβάνουν ημερομηνίες στην ίδια μορφή (για παράδειγμα, 1/1/2012) και τουλάχιστον ένας πίνακας (χρονική ευφυΐα) να παραθέτει κάθε ημερομηνία μόνο μία φορά εντός της στήλης.
-
Επιλέξτε Δεδομένα > Σχέσεις.
Εάν το πεδίο Σχέσεις είναι γκρι, το βιβλίο εργασίας σας περιέχει μόνο έναν πίνακα.
-
Στο πλαίσιο Διαχείριση σχέσεων, κάντε κλικ στην επιλογή Δημιουργία.
-
Στο παράθυρο διαλόγου Δημιουργία σχέσης, κάντε κλικ στο βέλος για το στοιχείο Πίνακας και επιλέξτε έναν πίνακα από τη λίστα. Σε μια σχέση ένα-προς-πολλά, αυτός ο πίνακας πρέπει να είναι στην πλευρά των πολλών. Εάν χρησιμοποιούσατε το παράδειγμα πελατών και χρονικής ευφυΐας, θα επιλέγατε πρώτα τον πίνακα πωλήσεων πελατών, επειδή πολλές πωλήσεις είναι πιθανό να προκύψουν οποιαδήποτε δεδομένη ημέρα.
-
Στο πλαίσιο Στήλη (Εξωτερική), επιλέξτε τη στήλη που περιέχει τα δεδομένα που σχετίζονται με το πλαίσιο Σχετική στήλη (Κύρια). Για παράδειγμα, εάν είχατε μια στήλη ημερομηνιών και στους δύο πίνακες, θα έπρεπε να επιλέξετε αυτήν τη στήλη τώρα.
-
Στο πλαίσιο Σχετικός πίνακας, επιλέξτε έναν πίνακα που έχει τουλάχιστον μία στήλη δεδομένων που σχετίζεται με τον πίνακα που μόλις επιλέξατε στο πλαίσιο Πίνακας.
-
Στο πλαίσιο Σχετική στήλη (Κύρια), επιλέξτε μια στήλη που έχει μοναδικές τιμές που συμφωνούν με τις τιμές στη στήλη που έχετε επιλέξει ως Στήλη.
-
Κάντε κλικ στο κουμπί OK.
Περισσότερες πληροφορίες σχετικά με τις σχέσεις μεταξύ πινάκων στο Excel
Σημειώσεις σχετικά με τις σχέσεις
-
Θα γνωρίζετε εάν υπάρχει σχέση όταν σύρετε πεδία από διαφορετικούς πίνακες στη λίστα πεδίων Συγκεντρωτικού Πίνακα. Εάν δεν σας ζητηθεί να δημιουργήσετε μια σχέση, το Excel έχει ήδη τις πληροφορίες σχέσης που χρειάζεται για να συσχετίσει τα δεδομένα.
-
Η δημιουργία σχέσεων είναι παρόμοια με τη χρήση των συναρτήσεων VLOOKUP: Χρειάζεστε στήλες που περιέχουν αντίστοιχα δεδομένα, ώστε το Excel να έχει τη δυνατότητα παραπομπής σε γραμμές σε έναν πίνακα με τις γραμμές ενός άλλου πίνακα. Στο παράδειγμα χρονικής ευφυΐας, ο πίνακας πελατών θα έπρεπε να διαθέτει τιμές ημερομηνίας που υπάρχουν και σε έναν πίνακα χρονικής ευφυΐας.
-
Σε ένα μοντέλο δεδομένων, οι σχέσεις πινάκων μπορεί να είναι ένα-προς-ένα (κάθε επιβάτης έχει μία κάρτα επιβίβασης) ή ένα-προς-πολλά (κάθε πτήση έχει πολλούς επιβάτες), αλλά όχι πολλά-προς-πολλά. Οι σχέσεις πολλά-προς-πολλά έχουν ως αποτέλεσμα σφάλματα κυκλικής εξάρτησης, όπως "Εντοπίστηκε μια κυκλική εξάρτηση". Αυτό το σφάλμα θα παρουσιαστεί εάν θέλετε να κάνετε μια άμεση σύνδεση μεταξύ δύο πινάκων που έχουν συνδέσεις πολλά-προς-πολλά ή έμμεσες συνδέσεις (μια αλυσίδα σχέσεων πίνακα που είναι ένα-προς-πολλά μέσα σε κάθε σχέση, αλλά πολλά-προς-πολλά όταν την βλέπετε από την αρχή έως το τέλος. Διαβάστε περισσότερα σχετικά με τις Σχέσεις μεταξύ πινάκων σε ένα μοντέλο δεδομένων.
-
Οι τύποι δεδομένων στις δύο στήλες πρέπει να είναι συμβατοί. Για λεπτομέρειες, ανατρέξτε στο θέμα Τύποι δεδομένων στα μοντέλα δεδομένων του Excel.
-
Ενδέχεται να υπάρχουν άλλοι τρόποι για να δημιουργήσετε σχέσεις οι οποίοι μπορεί να είναι πιο διαισθητικοί, ειδικά εάν δεν είστε βέβαιοι ποιες στήλες θα χρησιμοποιήσετε. Ανατρέξτε στο θέμα Δημιουργία σχέσης σε προβολή διαγράμματος στο Power Pivot.
Παράδειγμα: Συσχέτιση δεδομένων χρονικής ευφυΐας με δεδομένα πτήσεων αεροπορικής εταιρείας
Μπορείτε να μάθετε σχετικά με τις σχέσεις πινάκων και τη χρονική ευφυΐα χρησιμοποιώντας δωρεάν δεδομένα στο Microsoft Azure Marketplace. Ορισμένα από αυτά τα σύνολα δεδομένων είναι πολύ μεγάλα και απαιτούν γρήγορη σύνδεση στο Internet για την ολοκλήρωση της λήψης των δεδομένων σε μια εύλογη χρονική περίοδο.
-
Ξεκινήστε το πρόσθετο Power Pivot στο Microsoft Excel και ανοίξτε το παράθυρο του Power Pivot.
-
Επιλέξτε Λήψη εξωτερικών δεδομένων > Από υπηρεσία δεδομένων > Από το Microsoft Azure Marketplace. Η αρχική σελίδα του Microsoft Azure Marketplace ανοίγει στον "Οδηγό εισαγωγής πίνακα".
-
Στην περιοχή Price, επιλέξτε Free.
-
Στην περιοχή Category, επιλέξτε Science & Statistics.
-
Βρείτε το DateStream και επιλέξτε Εγγραφή.
-
Μεταβείτε στο λογαριασμό σας Microsoft και επιλέξτε Είσοδος. Θα πρέπει να εμφανιστεί μια προεπισκόπηση των δεδομένων στο παράθυρο.
-
Πραγματοποιήστε κύλιση προς τα κάτω και επιλέξτε Ερώτημα επιλογής.
-
Κάντε κλικ στο κουμπί Επόμενο.
-
Επιλέξτε BasicCalendarUS και κατόπιν Τέλος για να εισαγάγετε τα δεδομένα. Εάν υπάρχει γρήγορη σύνδεση στο Internet, η εισαγωγή θα πρέπει να διαρκέσει περίπου ένα λεπτό. Μόλις ολοκληρώσετε, θα πρέπει να δείτε μια αναφορά κατάστασης για τη μεταφορά 73.414 γραμμών. Επιλέξτε Κλείσιμο.
-
Επιλέξτε Λήψη εξωτερικών δεδομένων > Από υπηρεσία δεδομένων > Από το Microsoft Azure Marketplace για να εισαγάγετε ένα δεύτερο σύνολο δεδομένων.
-
Στην περιοχή Type, επιλέξτε Data.
-
Στην περιοχή Price, επιλέξτε Free.
-
Βρείτε το US Air Carrier Flight Delays και επιλέξτε Επιλογή.
-
Πραγματοποιήστε κύλιση προς τα κάτω και επιλέξτε Ερώτημα επιλογής.
-
Κάντε κλικ στο κουμπί Επόμενο.
-
Επιλέξτε Τέλος για να εισαγάγετε τα δεδομένα. Εάν υπάρχει γρήγορη σύνδεση στο Internet, η εισαγωγή μπορεί να διαρκέσει 15 λεπτά. Μόλις ολοκληρώσετε, θα πρέπει να δείτε μια αναφορά κατάστασης για τη μεταφορά 2.427.284 γραμμών. Επιλέξτε Κλείσιμο. Τώρα θα πρέπει να έχετε δύο πίνακες στο μοντέλο δεδομένων. Για να τους συσχετίσετε, θα χρειαστείτε συμβατές στήλες σε κάθε πίνακα.
-
Παρατηρήστε ότι το DateKey στο BasicCalendarUS έχει τη μορφή 1/1/2012 12:00:00 AM. Ο πίνακας On_Time_Performance διαθέτει επίσης μια στήλη ημερομηνίας/ώρας, τη FlightDate, της οποίας οι τιμές έχουν καθοριστεί στην ίδια μορφή: 1/1/2012 12:00:00 AM. Οι δύο στήλες περιέχουν αντίστοιχα δεδομένα, του ίδιου τύπου δεδομένων, και τουλάχιστον μία από τις στήλες (DateKey) περιέχει μόνο μοναδικές τιμές. Στα επόμενα βήματα, θα χρησιμοποιήσετε αυτές τις στήλες για να συσχετίσετε τους πίνακες.
-
Στο παράθυρο του Power Pivot, επιλέξτε Συγκεντρωτικός Πίνακας για να δημιουργήσετε έναν Συγκεντρωτικό Πίνακα σε ένα νέο ή υπάρχον φύλλο εργασίας.
-
Στη λίστα πεδίων, αναπτύξτε το On_Time_Performance και επιλέξτε ArrDelayMinutes για να το προσθέσετε στην περιοχή "Τιμές". Στον Συγκεντρωτικό Πίνακα, θα πρέπει να δείτε τον συνολικό χρόνο καθυστέρησης των πτήσεων, μετρημένο σε λεπτά.
-
Αναπτύξτε το BasicCalendarUS και επιλέξτε MonthInCalendar για να το προσθέσετε στην περιοχή "Γραμμές".
-
Παρατηρήστε ότι ο Συγκεντρωτικός Πίνακας τώρα παραθέτει μήνες, αλλά το σύνολο των λεπτών είναι το ίδιο για κάθε μήνα. Οι επαναλαμβανόμενες, ταυτόσημες τιμές υποδεικνύουν ότι είναι απαραίτητη μια σχέση.
-
Στη λίστα πεδίων, στην περιοχή "Ίσως χρειάζονται σχέσεις μεταξύ πινάκων", επιλέξτε Δημιουργία.
-
Στο πεδίο "Σχετικός πίνακας", επιλέξτε On_Time_Performance και στο πεδίο "Σχετική στήλη (Κύρια)", επιλέξτε FlightDate.
-
Στο πεδίο "Πίνακας", επιλέξτε BasicCalendarUS και στο πεδίο "Στήλη (Εξωτερική)", επιλέξτε DateKey. Επιλέξτε OK για να δημιουργήσετε τη σχέση.
-
Παρατηρήστε ότι το άθροισμα των λεπτών καθυστέρησης τώρα διαφέρει για κάθε μήνα.
-
Στο BasicCalendarUS, σύρετε το YearKey στην περιοχή "Γραμμές", επάνω από το MonthInCalendar.
Τώρα μπορείτε να αναλύσετε τις καθυστερήσεις άφιξης κατά έτος και μήνα ή σύμφωνα με άλλες τιμές στο ημερολόγιο.
Συμβουλές: Από προεπιλογή, οι μήνες παρατίθενται με αλφαβητική σειρά. Χρησιμοποιώντας το πρόσθετο Power Pivot, μπορείτε να αλλάξετε την ταξινόμηση, έτσι ώστε οι μήνες να εμφανίζονται με χρονολογική σειρά.
-
Βεβαιωθείτε ότι ο πίνακας BasicCalendarUS είναι ανοιχτός στο παράθυρο του Power Pivot.
-
Στον Κεντρικό πίνακα, επιλέξτε Ταξινόμηση κατά στήλη.
-
Στο πεδίο "Ταξινόμηση", επιλέξτε MonthInCalendar
-
Στο πεδίο "Κατά", επιλέξτε MonthOfYear.
Ο Συγκεντρωτικός Πίνακας τώρα ταξινομεί κάθε συνδυασμό μήνα-έτους (Οκτώβριος 2011, Νοέμβριος 2011) κατά τον αριθμό των μηνών εντός ενός έτους (10, 11). Η αλλαγή της σειράς ταξινόμησης είναι εύκολη, επειδή η τροφοδοσία DateStream παρέχει όλες τις απαραίτητες στήλες για τη λειτουργία αυτού του σεναρίου. Εάν χρησιμοποιείτε έναν διαφορετικό πίνακα χρονικής ευφυΐας, το βήμα θα είναι διαφορετικό.
"Οι σχέσεις μεταξύ πινάκων ίσως φανούν χρήσιμες"
Καθώς προσθέτετε πεδία σε έναν Συγκεντρωτικό Πίνακα, θα ενημερώνεστε εάν είναι απαραίτητη μια σχέση πίνακα για να κατανοείτε τα πεδία που έχετε επιλέξει στον Συγκεντρωτικό Πίνακα.
Παρόλο που το Excel μπορεί να σας ειδοποιεί όταν μια σχέση είναι απαραίτητη, δεν μπορεί να σας υποδείξει τους πίνακες και τις στήλες που θα χρησιμοποιήσετε, ούτε εάν μια σχέση πίνακα είναι εφικτή. Προσπαθήστε να ακολουθήσετε αυτά τα βήματα για να πάρετε τις απαντήσεις που χρειάζεστε.
Βήμα 1: Προσδιορίστε τους πίνακες που θα καθορίσετε στη σχέση
Εάν το μοντέλο περιέχει λίγους πίνακες, ίσως να είναι αμέσως προφανές ποιους πρέπει να χρησιμοποιήσετε. Αλλά για μεγαλύτερα μοντέλα, θα σας ήταν μάλλον χρήσιμη κάποια βοήθεια. Μια προσέγγιση είναι να χρησιμοποιήσετε την Προβολή διαγράμματος στο πρόσθετο Power Pivot. Η Προβολή διαγράμματος παρέχει μια οπτική αναπαράσταση όλων των πινάκων του μοντέλου δεδομένων. Με την Προβολή διαγράμματος, μπορείτε να καθορίσετε γρήγορα ποιοι πίνακες διαχωρίζονται από το υπόλοιπο μοντέλο.
Σημείωση: Είναι πιθανό να δημιουργήσετε ασαφείς σχέσεις που δεν είναι έγκυρες όταν χρησιμοποιούνται σε μια αναφορά Συγκεντρωτικού Πίνακα ή Power View. Ας υποθέσουμε ότι όλοι οι πίνακες σχετίζονται με κάποιον τρόπο με άλλους πίνακες στο μοντέλο, αλλά όταν προσπαθείτε να συνδυάσετε πεδία από διαφορετικούς πίνακες, λαμβάνετε το μήνυμα "Ίσως χρειάζονται σχέσεις μεταξύ πινάκων". Η πιο πιθανή αιτία είναι ότι έχετε συναντήσει μια σχέση πολλά-προς-πολλά. Εάν ακολουθήσετε την αλυσίδα των σχέσεων πινάκων που συνδέονται με τους πίνακες που θέλετε να χρησιμοποιήσετε, μάλλον θα ανακαλύψετε ότι έχετε δύο ή περισσότερες σχέσεις πινάκων ένα-προς-πολλά. Δεν υπάρχει εύκολη λύση που να είναι κατάλληλη για κάθε περίπτωση, αλλά μπορείτε να δοκιμάσετε τη δημιουργία υπολογιζόμενων στηλών για να ενοποιήσετε τις στήλες που θέλετε να χρησιμοποιήσετε σε έναν πίνακα.
Βήμα 2: Εύρεση στηλών που μπορούν να χρησιμοποιηθούν για τη δημιουργία μιας διαδρομής από έναν πίνακα στον επόμενο
Αφού έχετε προσδιορίσει ποιος πίνακας είναι αποσυνδεδεμένος από το υπόλοιπο μοντέλο, αναθεωρήστε τις στήλες του για να καθορίσετε εάν μια άλλη στήλη, σε άλλο σημείο στο μοντέλο, περιέχει αντίστοιχες τιμές.
Για παράδειγμα, ας υποθέσουμε ότι έχετε ένα μοντέλο που περιέχει πωλήσεις προϊόντων κατά περιοχή και ότι αργότερα εισάγετε δημογραφικά δεδομένα για να μάθετε εάν υπάρχει συσχέτιση μεταξύ των πωλήσεων και των δημογραφικών τάσεων σε κάθε περιοχή. Επειδή τα δημογραφικά δεδομένα προέρχονται από διαφορετική προέλευση δεδομένων, οι πίνακές τους αρχικά είναι απομονωμένοι από το υπόλοιπο μοντέλο. Για την ενσωμάτωση των δημογραφικών δεδομένων με το υπόλοιπο μοντέλο, θα χρειαστεί να βρείτε μια στήλη σε έναν από τους δημογραφικούς πίνακες που να αντιστοιχεί σε αυτήν που χρησιμοποιείτε ήδη. Για παράδειγμα, εάν τα δημογραφικά δεδομένα είναι οργανωμένα κατά περιοχή και τα δεδομένα πωλήσεων καθορίζουν σε ποια περιοχή πραγματοποιήθηκε η πώληση, μπορείτε να συσχετίσετε τα δύο σύνολα δεδομένων, βρίσκοντας μια κοινή στήλη, όπως Νομός, Ταχυδρομικός κώδικας ή Περιοχή, για την παροχή της αναζήτησης.
Εκτός από αντίστοιχες τιμές, υπάρχουν μερικές πρόσθετες απαιτήσεις για τη δημιουργία μιας σχέσης:
-
Οι τιμές των δεδομένων στη στήλη αναζήτησης πρέπει να είναι μοναδικές. Με άλλα λόγια, η στήλη δεν μπορεί να περιέχει διπλότυπα. Σε ένα μοντέλο δεδομένων, οι συμβολοσειρές null και οι κενές συμβολοσειρές ισοδυναμούν με ένα κενό, το οποίο αποτελεί ξεχωριστή τιμή δεδομένων. Αυτό σημαίνει ότι δεν πρέπει να υπάρχουν πολλές τιμές null στη στήλη αναζήτησης.
-
Οι τύποι δεδομένων από τη στήλη προέλευσης και τη στήλη αναζήτησης πρέπει να είναι συμβατοί. Για περισσότερες πληροφορίες σχετικά με τους τύπους δεδομένων, ανατρέξτε στο θέμα Τύποι δεδομένων σε Μοντέλα δεδομένων.
Για να μάθετε περισσότερα σχετικά με τις σχέσεις πινάκων, ανατρέξτε στο θέμα Σχέσεις μεταξύ πινάκων σε ένα μοντέλο δεδομένων.
