Πώς να ρυθμίσετε τους πίνακες περιστροφής του Excel 2010

by Τιμ Περντέ

01 από 15

Τελικό αποτέλεσμα

Αυτό είναι το τελικό αποτέλεσμα αυτού του οδηγού βήμα προς βήμα - Κάντε κλικ στην εικόνα για να δείτε μια έκδοση πλήρους μεγέθους.

Υπήρξε ένα χάσμα μεταξύ των Microsoft Excel και των κορυφαίων πλατφόρμων Business Intelligence (BI) για πολλά χρόνια. Οι βελτιώσεις του Pivot Table του Microsoft Excel 2010 μαζί με μερικές άλλες λειτουργίες BI έχουν καταστήσει έναν πραγματικό ανταγωνιστή για την επιχείρηση BI. Το Excel έχει παραδοσιακά χρησιμοποιηθεί για την αυτόνομη ανάλυση και το πρότυπο εργαλείο που όλοι εξάγουν τις τελικές αναφορές τους. Η επαγγελματική επιχειρησιακή ευφυΐα έχει παραδοσιακά διατηρηθεί για όσους ενδιαφέρονται για τις SAS, Business Objects και SAP.

Το Microsoft Excel 2010 (με τον Πίνακα Συγκεντρωτικού Πίνακα του Excel 2010) μαζί με το SQL Server 2008 R2, το SharePoint 2010 και το δωρεάν πρόσθετο "PowerPivot" του Microsoft Excel 2010 οδήγησαν σε μια λύση επιχειρηματικής ευφυΐας και αναφοράς υψηλού επιπέδου.

Αυτό το σεμινάριο καλύπτει ένα ευθεία σενάριο με ένα Συγκεντρωτικό Πεδίο του Excel 2010 συνδεδεμένο σε μια βάση δεδομένων του SQL Server 2008 R2 χρησιμοποιώντας ένα απλό ερώτημα SQL. Χρησιμοποιώ επίσης Slicers για οπτικό φιλτράρισμα που είναι καινούριο στο Excel 2010. Θα καλύψω πιο σύνθετες τεχνικές BI χρησιμοποιώντας Expressions Data Analysis (DAX) στο PowerPivot για Excel 2010 στο εγγύς μέλλον. Αυτή η τελευταία έκδοση του Microsoft Excel 2010 μπορεί να προσφέρει μια πραγματική αξία για την κοινότητα χρηστών σας.

02 από 15

Εισαγωγή πίνακα περιστροφής

Τοποθετήστε το δρομέα σας ακριβώς εκεί που θέλετε το συγκεντρωτικό σας τραπέζι και κάντε κλικ στο Εισαγωγή | Συγκεντρωτικός πίνακας.

Μπορείτε να εισαγάγετε έναν πίνακα περιστροφής σε ένα νέο ή υπάρχον βιβλίο εργασίας του Excel. Ίσως θελήσετε να εξετάσετε τη θέση του δρομέα σας κάτω από μερικές σειρές από την κορυφή. Αυτό θα σας δώσει χώρο για μια κεφαλίδα ή πληροφορίες επιχείρησης σε περίπτωση που μοιραστείτε το φύλλο εργασίας ή το εκτυπώσετε.

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

03 από 15

Συνδέστε τον πίνακα περιστροφής στον SQL Server (ή άλλη βάση δεδομένων)

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

Το Excel 2010 μπορεί να ανακτήσει δεδομένα από όλους τους σημαντικούς παρόχους RDBMS (System Relational Database Management System) . Τα προγράμματα οδήγησης του SQL Server πρέπει να είναι διαθέσιμα για τη σύνδεση από προεπιλογή. Όμως, όλα τα μεγάλα λογισμικά βάσεων δεδομένων κάνουν ODBC (Open Database Connectivity) προγράμματα οδήγησης για να σας επιτρέψουν να πραγματοποιήσετε τη σύνδεση. Ελέγξτε την ιστοσελίδα τους αν χρειαστεί να κατεβάσετε προγράμματα οδήγησης ODBC.

Στην περίπτωση αυτού του φροντιστηρίου, συνδέω τον SQL Server 2008 R2 (δωρεάν έκδοση SQL Express).

A - Η φόρμα Create PivotTable είναι η πρώτη μορφή στη δημιουργία της σύνδεσης στον SQL Server. Επιλέξτε "Χρήση εξωτερικής πηγής δεδομένων" και κάντε κλικ στο κουμπί Επιλογή σύνδεσης. Αφήστε τη θέση όπου θα τοποθετηθεί ο Πίνακας Συγκεντρώσεων, εκτός αν θέλετε να δημιουργήσετε ένα νέο φύλλο εργασίας και να το τοποθετήσετε εκεί.
B - Η φόρμα Υπάρχουσες συνδέσεις παραθέτει οποιεσδήποτε συνδέσεις στο τρέχον βιβλίο εργασίας, στον υπολογιστή σας και στο δίκτυο στο οποίο είστε συνδεδεμένοι. Οι υπάρχουσες συνδέσεις είναι πραγματικά απλά αρχεία κειμένου με τις πληροφορίες σύνδεσης που απαιτούνται για την πρόσβαση σε μια συγκεκριμένη πηγή δεδομένων. Στην περίπτωσή μας, πρόκειται να δημιουργήσουμε μια νέα πηγή δεδομένων. Κάντε κλικ στο κουμπί Αναζήτηση για Περισσότερα.
C - Κάντε κλικ στο κουμπί Νέα προέλευση θα ξεκινήσει τον Οδηγό σύνδεσης δεδομένων.
D - Επιλέξτε Microsoft SQL Server και κάντε κλικ στο Next.
E - Εισάγετε το όνομα του διακομιστή και καταχωρίστε τα διαπιστευτήρια. Επιλέξτε την κατάλληλη μέθοδο επαλήθευσης ταυτότητας. Εάν δεν είστε σίγουροι για τη μέθοδο που θα χρησιμοποιήσετε, επικοινωνήστε με τον διαχειριστή της βάσης δεδομένων σας.
- Χρησιμοποιήστε τον έλεγχο ταυτότητας των Windows: Αυτή η μέθοδος χρησιμοποιεί τα στοιχεία σύνδεσης δικτύου σας για να αποκτήσετε πρόσβαση σε βάσεις δεδομένων του SQL Server.
- Χρησιμοποιήστε το ακόλουθο όνομα χρήστη και κωδικό πρόσβασης: Αυτή η μέθοδος χρησιμοποιείται όταν ο SQL Server έχει ρυθμιστεί με αυτόνομους χρήστες για πρόσβαση σε βάσεις δεδομένων.
F - Σε αυτό το βήμα, θα επιλέξουμε ένα τραπέζι ως σύμβολο κράτησης θέσης. Πρόκειται να αντικαταστήσουμε τον πίνακα με προσαρμοσμένη SQL που θα παρέχει ακριβώς τα δεδομένα που θέλουμε στο βιβλίο εργασίας του Excel.
- Επιλέξτε τη βάση δεδομένων με την οποία θα συνδεθείτε. Σε αυτό το παράδειγμα, συνδέουμε με την βάση δεδομένων AdventureWorks που παρέχεται από τη Microsoft. Ελέγξτε τη σύνδεση σε ένα συγκεκριμένο πίνακα και επιλέξτε τον πρώτο πίνακα. Θυμηθείτε, δεν πρόκειται να ανακτήσουμε δεδομένα από αυτόν τον πίνακα.
- Κάντε κλικ στο κουμπί Τέλος, το οποίο θα κλείσει τον οδηγό και θα σας επιστρέψει στο βιβλίο εργασίας. Θα αλλάξουμε τον πίνακα placeholder για το προσαρμοσμένο ερώτημα SQL μας.

Θα επιστρέψετε στη φόρμα Δημιουργία Συγκεντρωτικού Πίνακα (A). Κάντε κλικ στο κουμπί OK.

04 από 15

Ο πίνακας περιστροφής είναι προσωρινά συνδεδεμένος με τον πίνακα SQL

Ο Συγκεντρωτικός Πίνακας συνδέεται με τον SQL Server με τον πίνακα θέσης.

Σε αυτό το σημείο, έχετε συνδεθεί με τον πίνακα κράτησης θέσης και έχετε έναν άδειο Συγκεντρωτικό Πίνακα. Μπορείτε να δείτε στην αριστερή πλευρά ότι ο Συγκεντρωτικός Πίνακας θα είναι και στα δεξιά υπάρχει μια λίστα με τα διαθέσιμα πεδία.

05 από 15

Ανοίξτε τις ιδιότητες σύνδεσης

Ανοίξτε τη φόρμα Ιδιότητες σύνδεσης.

Πριν αρχίσουμε να επιλέγουμε δεδομένα για τον Συγκεντρωτικό Πίνακα, πρέπει να αλλάξουμε τη σύνδεση στο ερώτημα SQL. Βεβαιωθείτε ότι βρίσκεστε στην καρτέλα Επιλογές και κάντε κλικ στο αναπτυσσόμενο μενού Αλλαγή προέλευσης δεδομένων από την ενότητα Δεδομένα. Επιλέξτε Ιδιότητες σύνδεσης.

Αυτό εμφανίζει τη φόρμα "Ιδιότητες σύνδεσης". Κάντε κλικ στην καρτέλα Ορισμός. Αυτό σας δείχνει τις πληροφορίες σύνδεσης για την τρέχουσα σύνδεση με τον SQL Server. Ενώ αναφέρει ένα αρχείο σύνδεσης, τα δεδομένα είναι ενσωματωμένα στο υπολογιστικό φύλλο.

06 από 15

Ενημέρωση ιδιοτήτων σύνδεσης με ερώτηση

Αλλάξτε τον πίνακα σε ερώτημα SQL.

Αλλάξτε τον τύπο της εντολής από τον πίνακα στον SQL και αντικαταστήστε το υπάρχον κείμενο εντολών με το ερώτημα SQL. Εδώ είναι το ερώτημα που δημιουργήσαμε από την βάση δεδομένων AdventureWorks:

SELECT Sales.SalesOrderHeader.SalesOrderID,
Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.ShipDate,
Sales.SalesOrderHeader.Status,
Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderHeader.TaxAmt,
Sales.SalesOrderHeader.Freight,
Sales.SalesOrderHeader.TotalDue,
Sales.SalesOrderDetail.SalesOrderDetailID,
Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.UnitPrice,
Sales.SalesOrderDetail.LineTotal,
Production.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
Production.Product.ProductLine,
Production.ProductSubcategory.Name AS Κατηγορία προϊόντος
FROM Sales.SalesOrderDetail ΕΙΣΑΓΩΓΗ ΕΙΣΟΔΟΣ Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
ΕΙΣΑΓΩΓΗ JOIN Παραγωγή.Προϊόν ON πωλήσεων.SalesOrderDetail.ProductID =
Production.Product.ProductID ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΡΟΜΗ Πωλήσεων
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ΚΑΙ
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Παραγωγή.ΠροϊόνταΜεταξύ προϊόντων παραγωγής.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

Κάντε κλικ στο κουμπί OK.

07 από 15

Λήψη προειδοποίησης σύνδεσης

Κάντε κλικ στο Ναι (Yes) για προειδοποίηση σύνδεσης.

Θα λάβετε ένα παράθυρο διαλόγου Προειδοποίηση του Microsoft Excel. Αυτό οφείλεται στο γεγονός ότι άλλαξαμε τις πληροφορίες σύνδεσης. Όταν δημιουργήσαμε αρχικά τη σύνδεση, αποθηκεύσαμε τις πληροφορίες σε ένα εξωτερικό αρχείο .ODC (ODBC Data Connection). Τα δεδομένα στο βιβλίο εργασίας ήταν τα ίδια με το αρχείο .ODC μέχρι να αλλάξουμε από τον τύπο πίνακα εντολών στον τύπο εντολής SQL στο βήμα # 6. Η προειδοποίηση σας λέει ότι τα δεδομένα δεν είναι πλέον συγχρονισμένα και ότι θα αφαιρεθεί η αναφορά στο εξωτερικό αρχείο στο βιβλίο εργασίας. Αυτό είναι εντάξει. Κάντε κλικ στο κουμπί Ναι.

08 από 15

Πίνακας περιστροφής συνδεδεμένο στον SQL Server με ερώτημα

Ο Συγκεντρωτικός πίνακας είναι έτοιμος για να προσθέσετε δεδομένα.

Αυτό επιστρέφει στο βιβλίο εργασίας του Excel 2010 με κενό Συγκεντρωτικό Πίνακα. Μπορείτε να δείτε ότι τα διαθέσιμα πεδία είναι τώρα διαφορετικά και αντιστοιχούν στα πεδία του ερωτήματος SQL. Τώρα μπορούμε να αρχίσουμε να προσθέτουμε πεδία στον Συγκεντρωτικό Πίνακα.

09 από 15

Προσθήκη πεδίων στον πίνακα περιστροφής

Προσθέστε πεδία στον Συγκεντρωτικό πίνακα.

Στη λίστα πεδίων του Συγκεντρωτικού Πίνακα, σύρετε την κατηγορία Κατηγορίας Προϊόντος σε Περιοχή ετικετών γραμμής, την Περιοχή ετικετών εντολής σε ετικέτα στήλης και την περιοχή TotalDue to Values. Στην εικόνα εμφανίζονται τα αποτελέσματα. Όπως μπορείτε να δείτε, το πεδίο ημερομηνίας έχει μεμονωμένες ημερομηνίες, έτσι ώστε ο Συγκεντρωτικός πίνακας έχει δημιουργήσει μια στήλη για κάθε μοναδική ημερομηνία. Ευτυχώς, το Excel 2010 έχει μερικές ενσωματωμένες λειτουργίες για να μας βοηθήσει να οργανώσουμε πεδία ημερομηνίας.

10 από 15

Προσθέστε Ομαδοποίηση για πεδία ημερομηνίας

Προσθέστε Ομαδοποιήσεις για πεδίο ημερομηνίας.

Η λειτουργία Ομαδοποίησης μας επιτρέπει να οργανώνουμε ημερομηνίες σε έτη, μήνες, τετάρτες κλπ. Αυτό θα βοηθήσει να συνοψίσουμε τα δεδομένα και να διευκολύνουμε τον χρήστη να αλληλεπιδράσει με αυτό. Κάντε δεξί κλικ σε μία από τις κεφαλίδες στήλης ημερομηνίας και επιλέξτε Ομάδα που εμφανίζει τη φόρμα ομαδοποίησης.

11 από 15

Επιλέξτε Ομαδοποίηση βάσει τιμών

Επιλέξτε αντικείμενα ομαδοποίησης για το πεδίο ημερομηνίας.

Ανάλογα με το είδος των δεδομένων που ομαδοποιείτε, η φόρμα θα φαίνεται λίγο διαφορετική. Το Excel 2010 σάς επιτρέπει να ομαδοποιείτε ημερομηνίες, αριθμούς και επιλεγμένα δεδομένα κειμένου. Ομαδοποιούμε την OrderDate σε αυτό το εγχειρίδιο, έτσι ώστε στη φόρμα να εμφανίζονται επιλογές σχετικά με τις ομαδοποιήσεις ημερομηνιών.

Κάντε κλικ σε Μήνες και Χρόνια και κάντε κλικ στο OK.

12 από 15

Συγκεντρωτικό πίνακα συγκεντρωμένο ανά έτη και μήνες

Τα πεδία ημερομηνίας ομαδοποιούνται ανά έτη και μήνες.

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

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

13 από 15

Εισαγωγή Slicer (νέο στο Excel 2010)

Προσθέστε το Slicers στο Συγκεντρωτικό Πίνακα.

Τα Slicers είναι νέα στο Excel 2010. Οι Slicers είναι βασικά το ισοδύναμο των οπτικών φίλτρων για τα υπάρχοντα πεδία και τη δημιουργία Φίλτρων Αναφοράς στην περίπτωση που το στοιχείο στο οποίο θέλετε να φιλτράρετε δεν βρίσκεται στην τρέχουσα προβολή Συγκεντρωτικού Πίνακα. Αυτό το ωραίο πράγμα για τους Slicers καθιστά πολύ εύκολο για το χρήστη να αλλάξει την άποψη των δεδομένων στον Συγκεντρωτικό Πίνακα καθώς και να παρέχει οπτικούς δείκτες ως προς την τρέχουσα κατάσταση των φίλτρων.

Για να εισαγάγετε το Slicers, κάντε κλικ στην καρτέλα Επιλογές και κάντε κλικ στο Insert Insert Slicer από την ενότητα Sort & Filter. Επιλέξτε Insert Slicer που ανοίγει τη φόρμα Insert Slicers. Ελέγξτε τα περισσότερα από τα πεδία που θέλετε να έχετε στη διάθεσή σας. Στο παράδειγμά μας, πρόσθεσα Years, CountryRegionName και ProductCategory. ίσως χρειαστεί να τοποθετήσετε το Slicers όπου θέλετε. Από προεπιλογή, όλες οι τιμές είναι επιλεγμένες, πράγμα που σημαίνει ότι δεν έχουν εφαρμοστεί φίλτρα.

14 από 15

Συγκεντρωτικός πίνακας με φιλικά προς το χρήστη Slicers

Τα Slicers διευκολύνουν τους χρήστες να φιλτράρουν τους Συγκεντρωτικούς Πίνακες.

Όπως μπορείτε να δείτε, τα Slicers εμφανίζουν όλα τα δεδομένα ως επιλεγμένα. Είναι πολύ σαφές στον χρήστη τι ακριβώς είναι τα δεδομένα στην τρέχουσα προβολή του Συγκεντρωτικού Πίνακα.

15 από 15

Επιλέξτε Τιμές από Διακομιστές Ποιες Πίνακες Συγκεντρώσεων Ενημερώσεων

Επιλέξτε συνδυασμούς των Slicers για να αλλάξετε την προβολή των δεδομένων.

Κάντε κλικ σε διάφορους συνδυασμούς τιμών και δείτε πώς αλλάζει η προβολή του Συγκεντρωτικού Πίνακα. Μπορείτε να χρησιμοποιήσετε το τυπικό κλειδί της Microsoft στο Slicers, που σημαίνει ότι εάν μπορείτε να χρησιμοποιήσετε το Control + Click για να επιλέξετε πολλαπλές τιμές ή Shift + Κάντε κλικ για να επιλέξετε ένα εύρος τιμών. Κάθε Slicer εμφανίζει τις επιλεγμένες τιμές, γεγονός που καθιστά πραγματικά προφανές ποια είναι η κατάσταση του Συγκεντρωτικού Πίνακα όσον αφορά τα φίλτρα. Μπορείτε να αλλάξετε τα στυλ των Slicers, αν θέλετε, κάνοντας κλικ στο αναπτυσσόμενο μενού Quick Styles στην ενότητα Slicer της καρτέλας Options.

Η εισαγωγή του Slicers έχει βελτιώσει πραγματικά τη χρηστικότητα του Συγκεντρωτικού Πίνακα και έχει μεταφέρει το Excel 2010 πολύ πιο κοντά στο να είναι ένα επαγγελματικό εργαλείο επιχειρηματικής ευφυΐας. Οι Συγκεντρωτικοί Πίνακες έχουν βελτιωθεί αρκετά στο Excel 2010 και όταν συνδυάζονται με το νέο PowerPivot δημιουργούν ένα πολύ αναλυτικό περιβάλλον απόδοσης.