Πώς λειτουργεί η συνάρτηση λήψης των δεδομένων του συγκεντρωτικού πίνακα

Νωρίτερα, μίλησα ήδη για το γεγονός ότι όταν αναφερόμαστε σε ένα κελί συγκεντρωτικός πίνακαςαντί για κανονική αναφορά, επιστρέφεται η συνάρτηση GET.PIVOTO.TABLE.DATA (βλ. ). Αν ενδιαφέρεσαι ΠωςΓια να ξεπεράσετε αυτήν την ταλαιπωρία, σας συνιστώ να ανατρέξετε στην αναφερόμενη σημείωση. Αν ενδιαφέρεσαι, Γιατίαυτό συμβαίνει, και επίσης, ποιες είναι οι θετικές πτυχές της συνάρτησης GET.DATA.PIVOTO.TABLE, τότε προσφέρω ένα απόσπασμα του βιβλίου του Jelen, Alexander. (κεφάλαιο 15). Η τεχνική που εξετάζουμε θα σας επιτρέψει να αντιμετωπίσετε πολλά προβλήματα που προκαλούν πονοκεφάλους στους χρήστες συγκεντρωτικών πινάκων, ιδίως:

Η ανανέωση ενός Συγκεντρωτικού Πίνακα καταργεί τη μορφοποίηση που εφαρμόστηκε προηγουμένως. Οι αριθμητικές μορφές χάνονται. Τα αποτελέσματα της προσαρμογής του πλάτους των στηλών εξαφανίζονται.
Δεν υπάρχει εύκολος τρόποςδημιουργώντας έναν ασύμμετρο πίνακα περιστροφής. Η μόνη επιλογή είναι να χρησιμοποιήσετε ονομασμένα σύνολα, αλλά αυτή η μέθοδος είναι διαθέσιμη μόνο σε όσους χρησιμοποιούν Συγκεντρωτικούς Πίνακες Μοντέλων Δεδομένων και όχι κανονικούς Συγκεντρωτικούς Πίνακες.
Το Excel δεν μπορεί να θυμηθεί πρότυπα. Εάν χρειάζεται να δημιουργείτε Συγκεντρωτικούς Πίνακες ξανά και ξανά, θα πρέπει να ομαδοποιήσετε εκ νέου, να εφαρμόσετε υπολογισμένα πεδία και μέλη και να εκτελέσετε μια σειρά από άλλες παρόμοιες εργασίες.

Στην πραγματικότητα, όλα όσα περιγράφονται εδώ δεν είναι καινούργια. Επιπλέον, παρόμοιες τεχνικές έχουν χρησιμοποιηθεί από το Excel 2002. Ωστόσο, η επικοινωνία μου με τους χρήστες δείχνει ότι λιγότερο από το 1% είναι εξοικειωμένοι με αυτές. Η μόνη ερώτηση που έχουν οι χρήστες είναι πώς να απενεργοποιήσουν την περίεργη λειτουργία GET.PIVOTO.TABLE.DATA. Είναι κρίμα…

Λήψη σημείωσης σε ή μορφή, παραδείγματα σε μορφή

Λοιπόν, ας ξεκινήσουμε με τη σειρά.

Πώς να εγκαταλείψετε την προβληματική λειτουργία GET.DATA.PIVOTO.TABLE

Η συνάρτηση GET.DATA.Pivot.TABLE ήταν πονοκέφαλος για πολλούς χρήστες εδώ και πολύ καιρό. Ξαφνικά, χωρίς καμία προειδοποίηση, η συμπεριφορά των Συγκεντρωτικών Πινάκων άλλαξε στο Excel 2002. Μόλις αρχίσετε να δημιουργείτε τύπους εκτός του Συγκεντρωτικού Πίνακα που αναφέρονται στα δεδομένα του, αυτή η δυνατότητα εμφανίζεται από το πουθενά.

Ας υποθέσουμε ότι στον πίνακα περιστροφής που φαίνεται στο Σχ. 1, πρέπει να συγκρίνετε τα δεδομένα για το 2015 και το 2014.

Ρύζι. 1. Αρχικός συγκεντρωτικός πίνακας

Προσθέστε την επικεφαλίδα "% Growth" στο κελί D3.
Αντιγράψτε τη μορφή από το κελί C3 στο κελί D3.
Στο κελί D4, εισαγάγετε ένα σύμβολο ίσου.
Κάντε κλικ στο κελί C4.
Εισαγάγετε το σύμβολο / (κάθετο) για να υποδείξετε τη λειτουργία διαίρεσης.
Κάντε κλικ στο κελί B4.
Πληκτρολογήστε -1 και πατήστε το συνδυασμό πλήκτρων να μείνει στο ίδιο κελί. Μορφοποιήστε το αποτέλεσμα ως ποσοστό. Θα δείτε ότι η Δυτική περιοχή παρουσίασε πτώση εσόδων κατά 43,8% (Εικόνα 2). Όχι πολύ καλά αποτελέσματα.
Αφού ολοκληρώσετε την εισαγωγή του πρώτου σας τύπου, επιλέξτε το κελί D4.
Κάντε διπλό κλικ στο μικρό τετράγωνο που βρίσκεται στην κάτω δεξιά γωνία του κελιού. Αυτό το πλαίσιο αντιπροσωπεύει μια λαβή πλήρωσης που μπορεί να χρησιμοποιηθεί για την αντιγραφή ενός τύπου για τη συμπλήρωση μιας ολόκληρης στήλης αναφοράς.

Αφού ολοκληρωθεί η αντιγραφή του τύπου, κοιτάζοντας την οθόνη, θα καταλάβετε ότι κάτι δεν πάει καλά - κάθε περιοχή παρουσίασε πτώση 43,8% κατά τη διάρκεια του έτους (Εικ. 3).

Ρύζι. 3. Αφού αντιγράψετε τον τύπο σε όλα τα κελιά της στήλης, θα δείτε ότι κάθε περιοχή παρουσίασε πτώση 43,8%

Δύσκολα συμβαίνει μέσα πραγματική ζωή. Οποιοσδήποτε χρήστης θα σας το πει αφού κάνει τα παραπάνω Ενέργειες του Excelθα δημιουργήσει τον τύπο =C4/B4–1. Επιστρέψτε στο κελί D4 και δώστε προσοχή στη γραμμή τύπων (Εικόνα 4). Είναι απλά κάτι καταραμένο! Ο απλός τύπος =C4/B4–1 δεν υπάρχει πλέον. Αντίθετα, το πρόγραμμα αντικαθιστά μια σύνθετη δομή με τη συνάρτηση GET.PIVOTAL.TABLE.DATA. Γιατί αυτός ο τύπος δίνει σωστά αποτελέσματα στο κελί D4, αλλά όταν αντιγράφεται στα παρακάτω κελιά δεν λειτουργεί;

Η πρώτη αντίδραση οποιουδήποτε χρήστη σε αυτό που συνέβη θα είναι η εξής: "Τι είναι αυτή η περίεργη κατασκευή GET.DATA.PIVOTO.TABLE που μπέρδεψε την αναφορά μου;" Οι περισσότεροι χρήστες θα θέλουν να απαλλαγούν από αυτήν τη δυνατότητα αμέσως. Κάποιοι θα κάνουν την ερώτηση: "Γιατί η Microsoft μας έδωσε αυτή τη δυνατότητα;"

Δεν υπήρχε κάτι παρόμοιο φορές excel 2000. Όταν άρχισα να βλέπω το GET.DATA.PIVOTOMATIC.TABLE σε τακτική βάση, απλώς το μισούσα. Όταν κάποιος με ρώτησε σε ένα από τα σεμινάρια πώς μπορεί να χρησιμοποιηθεί για το καλό του σκοπού, έμεινα άναυδος. Δεν έχω κάνει ποτέ στον εαυτό μου τέτοια ερώτηση! Κατά τη γνώμη μου, και κατά τη γνώμη των περισσότερων χρηστών του Excel, η συνάρτηση GET.DATA.PIVOTAL.TABLE ήταν ένα προϊόν του κακού που δεν είχε καμία σχέση με τις δυνάμεις του καλού. Ευτυχώς, υπάρχουν δύο τρόποι για να απενεργοποιήσετε αυτήν τη δυνατότητα.

Αποκλεισμός της συνάρτησης GET.PIVOTO.TABLE.DATA με την εισαγωγή ενός τύπου.Υπάρχει ένας εύκολος τρόπος για να αποτρέψετε την εμφάνιση της συνάρτησης GET.DATA.PIVOTO.TABLE. Για να γίνει αυτό, πρέπει να δημιουργήσετε έναν τύπο χωρίς να χρησιμοποιήσετε τα πλήκτρα του ποντικιού ή του δρομέα. Απλώς ακολουθήστε αυτά τα βήματα.

Μεταβείτε στο κελί D4 και πληκτρολογήστε = (σύμβολο ίσου).
Εισαγάγετε C4.
Τύπος / (κάθετο για διαίρεση).
Εισάγετε B4.
Εισαγάγετε -1.
Κάντε κλικ Εισαγω.

Τώρα έχετε δημιουργήσει ένα έθιμο Φόρμουλα Excel, το οποίο μπορεί να αντιγραφεί στα κελιά της παρακάτω στήλης και με το οποίο μπορείτε να λάβετε τα σωστά αποτελέσματα (Εικ. 5). Όπως μπορείτε να δείτε, μπορείτε να δημιουργήσετε τύπους σε περιοχές εκτός του Συγκεντρωτικού Πίνακα που αναφέρονται στα δεδομένα μέσα στον Συγκεντρωτικό Πίνακα. Και όσοι δεν πιστεύουν ότι αυτό είναι δυνατό, ας κάνουν μόνοι τους τις περιγραφόμενες ενέργειες.

Ρύζι. 5. Απλώς πληκτρολογήστε =С4/В4–1 από το πληκτρολόγιο και ο τύπος θα λειτουργήσει όπως θα έπρεπε

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

Απενεργοποιήστε τη συνάρτηση GET.PIVOTO.TABLE.DATA.Μπορείτε να απενεργοποιήσετε οριστικά τη συνάρτηση GET.PIVOTO.TABLE.DATA. Κάντε κλικ στην κορδέλα μενού Αρχείο → Επιλογές. Στο ανοιχτό παράθυρο Επιλογέςπροέχωμεταβείτε στην καρτέλα ΜΑΘΗΜΑΤΙΚΟΙ τυποικαι αποεπιλέξτε την επιλογή λειτουργία χρήσηςGetPivotData για συνδέσμους Συγκεντρωτικού Πίνακα. Κάντε κλικ Εντάξει.

Εναλλακτική επιλογη. Κάντε κλικ στον συγκεντρωτικό πίνακα και στην καρτέλα συμφραζομένων που εμφανίζεται Ανάλυσηκάντε κλικ στο αναπτυσσόμενο μενού δίπλα στο κουμπί Επιλογές. Καταργήστε την επιλογή του πλαισίου δίπλα Δημιουργήστε GetPivotData(Εικ. 7). Το πλαίσιο ελέγχου είναι ενεργοποιημένο από προεπιλογή.

Γιατί η Microsoft μας πρόσφερε τη συνάρτηση GET.PIVOTO.TABLE.DATA.Εάν αυτή η δυνατότητα είναι τόσο τρομερή, γιατί οι προγραμματιστές της Microsoft την ενεργοποίησαν από προεπιλογή; Γιατί ενδιαφέρονται για τη διατήρηση της υποστήριξης αυτής της δυνατότητας σε νεότερες εκδόσεις του Excel; Γνωρίζουν το συναίσθημα των χρηστών; Και προχωράμε στα πιο ενδιαφέροντα...

Χρησιμοποιώντας τη συνάρτηση GET.PIVOTO.TABLE.DATA για τη βελτίωση των συγκεντρωτικών πινάκων

Τα pivot tables είναι μια μεγάλη εφεύρεση της ανθρωπότητας. Ένας Συγκεντρωτικός Πίνακας δημιουργείται με λίγα μόνο κλικ του ποντικιού, εξαλείφοντας την ανάγκη για προηγμένα φίλτρα, τη λειτουργία BSUM και πίνακες δεδομένων. Με τους Συγκεντρωτικούς Πίνακες, μπορείτε να δημιουργήσετε αναφορές μίας σελίδας με βάση τεράστιες ποσότητες δεδομένων. Στο πλαίσιο αυτών των πλεονεκτημάτων, ορισμένα από τα μειονεκτήματα των Συγκεντρωτικών Πίνακες, τα οποία είναι η ανέκφραστη μορφοποίηση και η ανάγκη μετατροπής Συγκεντρωτικών Πίνακων σε τιμές για πρόσθετη προσαρμογή, εξαφανίζονται στο παρασκήνιο. Στο σχ. Το Σχήμα 8 δείχνει μια τυπική διαδικασία δημιουργίας συγκεντρωτικού πίνακα. Στην υπό εξέταση περίπτωση, όλα ξεκινούν από τα αρχικά δεδομένα. Δημιουργούμε έναν πίνακα περιστροφής και χρησιμοποιούμε όλες τις πιθανές τεχνικές για να τον προσαρμόσουμε και να τον βελτιώσουμε. Μερικές φορές μετατρέπουμε τον Συγκεντρωτικό Πίνακα σε τιμές και κάνουμε κάποια τελική μορφοποίηση.

Η νέα τεχνική Συγκεντρωτικού Πίνακα που εισήχθη από τον Rob Colley (έναν προγραμματιστή της Microsoft) και συζητείται παρακάτω είναι μια βελτίωση στη διαδικασία που περιγράφεται παραπάνω. Σε αυτήν την περίπτωση, δημιουργείται πρώτα ένας πρωτόγονος πίνακας περιστροφής. Αυτός ο πίνακας δεν χρειάζεται να μορφοποιηθεί. Στη συνέχεια, ακολουθείται μια διαδικασία ενός βήματος, σχετικά χρονοβόρα για τη δημιουργία ενός όμορφα διαμορφωμένου κελύφους που θα περιέχει την τελική αναφορά. Μετά από αυτό, η συνάρτηση GET.DATA.PIVOTOMATIC.TABLE χρησιμοποιείται για τη γρήγορη συμπλήρωση της αναφοράς στο κέλυφος με δεδομένα. Αφού λάβετε νέα δεδομένα, μπορείτε να τα τοποθετήσετε στο φύλλο, να ενημερώσετε τον πρωτόγονο συγκεντρωτικό πίνακα και να εκτυπώσετε την αναφορά που βρίσκεται στο κέλυφος (Εικ. 9). Αυτή η τεχνική έχει μια σειρά από αναμφισβήτητα πλεονεκτήματα. Για παράδειγμα, δεν χρειάζεται να ανησυχείτε για τη μορφοποίηση μιας αναφοράς αμέσως μετά τη δημιουργία της. Η διαδικασία δημιουργίας συγκεντρωτικών πινάκων γίνεται σχεδόν πλήρως αυτοματοποιημένη.

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

Δημιουργία ενός πρωτόγονου πίνακα περιστροφής.Τα αρχικά δεδομένα (Εικ. 10) παρουσιάζονται με τη μορφή συναλλαγών που περιέχουν πληροφορίες για προγραμματισμένους και πραγματικούς δείκτες για κάθε περιοχή στην οποία υπάρχουν υποκαταστήματα εταιρείας. Οι προγραμματισμένοι δείκτες είναι λεπτομερείς σε επίπεδο μηνών και πραγματικοί - σε επίπεδο μεμονωμένων ημερών. Δημιουργούνται προγραμματισμένοι δείκτες για το επόμενο έτος, και πραγματικοί - για τους τελευταίους μήνες. Επειδή η αναφορά θα ενημερώνεται κάθε μήνα, αυτή η διαδικασία απλοποιείται σημαντικά εάν η πηγή δεδομένων Συγκεντρωτικού Πίνακα μεγαλώσει σε μέγεθος καθώς προστίθενται νέα δεδομένα στο κάτω μέρος. Σε παλιότερες εκδόσεις Δημιουργία ExcelΜια τέτοια πηγή δεδομένων πραγματοποιήθηκε χρησιμοποιώντας μια ονομασμένη δυναμική περιοχή χρησιμοποιώντας τη συνάρτηση OFFSET (για περισσότερες λεπτομέρειες, βλ.). Όταν εργάζεστε στο Excel 2013, απλώς επιλέξτε ένα από τα κελιά δεδομένων και πατήστε Ctrl+T (δημιουργία πίνακα). Θα εμφανιστεί ένα ονομασμένο σύνολο δεδομένων, το οποίο επεκτείνεται αυτόματα καθώς προστίθενται νέες σειρές και στήλες.

Τώρα ας δημιουργήσουμε έναν συγκεντρωτικό πίνακα. Η συνάρτηση GET.PIVOT.TABLE. είναι αρκετά ισχυρή, αλλά μπορεί να επιστρέψει μόνο τις τιμές που εμφανίζονται στον πραγματικό συγκεντρωτικό πίνακα. Αυτή η συνάρτηση δεν μπορεί να ανιχνεύσει τη μνήμη cache για να υπολογίσει στοιχεία που δεν βρίσκονται στον Συγκεντρωτικό Πίνακα.

Δημιουργία συγκεντρωτικού πίνακα:

Επιλέξτε μια ομάδα Εισάγετε → συγκεντρωτικός πίνακαςκαι μετά στο παράθυρο διαλόγου Δημιουργήστε έναν Συγκεντρωτικό ΠίνακαΚάντε κλικ Εντάξει.
Στη λίστα Πεδία Συγκεντρωτικού Πίνακα, επιλέξτε το πεδίο ημερομηνία της. Μια λίστα ημερομηνιών θα εμφανιστεί στην αριστερή πλευρά του συγκεντρωτικού πίνακα (Εικ. 11).
Επιλέξτε οποιοδήποτε κελί ημερομηνίας, για παράδειγμα, A4. Στην καρτέλα περιβάλλοντος Ανάλυση, που βρίσκεται στο σύνολο των καρτελών με βάση τα συμφραζόμενα Εργασία με πίνακες περιστροφής, κάντε κλικ στο κουμπί Ομάδα ανά πεδίο(για λεπτομέρειες βλ.). Στο πλαίσιο διαλόγου Ομαδοποίησηεπιλογή επιλογής Μήνες(Εικ. 12). Κάντε κλικ Εντάξει. Τα ονόματα των μηνών θα εμφανίζονται στην αριστερή πλευρά του συγκεντρωτικού πίνακα (Εικ. 13).
Σύρετε το πλαίσιο ημερομηνία τηςστην περιοχή Στήλες Συγκεντρωτικού Πίνακα.
Σύρετε το πλαίσιο Δείκτηςστην περιοχή στήλης της λίστας πεδίων Συγκεντρωτικού Πίνακα.
Επιλέξτε πεδίο Περιφέρεια, το οποίο θα εμφανίζεται στην αριστερή στήλη του συγκεντρωτικού πίνακα.
Επιλέξτε πεδίο Εισόδημα, το οποίο θα εμφανιστεί στην περιοχή Τιμές Συγκεντρωτικού Πίνακα.

Ρύζι. 11. Ξεκινήστε ομαδοποιώντας ανά πεδίο ημερομηνία της

Σε αυτό το στάδιο, ο πίνακας περιστροφής μας φαίνεται μάλλον πρωτόγονος (Εικ. 14). Πραγματικά δεν μου αρέσουν οι ταμπέλες Ονόματα σειρώνκαι Ονόματα στηλών. Δεν είναι σωστό να εμφανίζονται τα σύνολα για Jan Planκαι Γιαν Γεγονόςστη στήλη Δ κ.λπ. Αλλά μην ανησυχείτε για εμφάνισηαυτόν τον συγκεντρωτικό πίνακα, γιατί κανείς άλλος δεν θα τον δει εκτός από εσάς. Ξεκινώντας από αυτό το σημείο, θα δημιουργήσουμε ένα κέλυφος αναφοράς, η πηγή δεδομένων του οποίου θα είναι ο συγκεντρωτικός πίνακας που μόλις δημιουργήσαμε.

Δημιουργία περιτυλίγματος αναφοράς.Επικόλληση μέσα ΤΕΤΡΑΔΙΟ ΕΡΓΑΣΙΩΝκενό φύλλο. Ας αφήσουμε για λίγο στην άκρη τα εργαλεία του Συγκεντρωτικού Πίνακα και ας προχωρήσουμε στα συνηθισμένα εργαλεία του Excel. Το καθήκον μας είναι να χρησιμοποιήσουμε τύπους και μορφοποίηση για να δημιουργήσουμε μια όμορφη αναφορά που δεν είναι ενοχλητικό να δείξουμε στον διαχειριστή.

Εκτελέστε τα παρακάτω βήματα (Εικ. 15).

Στο κελί A1, εισαγάγετε το όνομα της αναφοράς - Προγραμματισμένοι και πραγματικοί δείκτες ανά περιφέρεια.
Μεταβείτε στην καρτέλα Σπίτι, κάντε κλικ στο κουμπί Στυλ κυττάρωνεπιλέξτε μορφή Επικεφαλίδα 1.
Στο κελί A2, εισαγάγετε τον τύπο = MONTH (ΣΗΜΕΡΑ (); 0). Αυτή η συνάρτηση επιστρέφει την τελευταία ημέρα τρέχον μήνα. Για παράδειγμα, εάν το διαβάζετε στις 14 Αυγούστου 2014, το κελί A2 θα έγραφε 31 Αυγούστου 2014.
Επιλέξτε το κελί A2. Πατήστε το συνδυασμό πλήκτρων Ctrl+1 για να εμφανιστεί το πλαίσιο διαλόγου Μορφή κελιού. Στην καρτέλα Αριθμόςκάντε κλικ στο στοιχείο Όλες οι μορφές. Εισαγάγετε μια προσαρμοσμένη μορφή αριθμού στη φόρμα "Από τον μήνα" ΜΜΜΜ "προγραμματισμένοι δείκτες"(Εικ. 16). Ως αποτέλεσμα, η υπολογισμένη ημερομηνία θα μοιάζει με κείμενο.
Στο κελί A5, εισαγάγετε μια επικεφαλίδα Περιφέρεια.
Εισαγάγετε τους τίτλους περιοχών στα υπόλοιπα κελιά στη στήλη A. Οι τίτλοι περιοχών πρέπει να ταιριάζουν με τα ονόματα περιοχών στον Συγκεντρωτικό Πίνακα.
Εάν είναι απαραίτητο, προσθέστε ετικέτες στη στήλη για τα σύνολα των τμημάτων.
Προσθέστε μια γραμμή στο κάτω μέρος της αναφοράς Σύνολο για την εταιρεία.
Στο κελί B4, εισαγάγετε τον τύπο =DATE(YEAR($A$2),COLUMN(A1),1). Αυτός ο τύπος επιστρέφει τις ημερομηνίες 01/01/2014, 02/01/2104 κ.λπ., τις πρώτες ημέρες και των 12 μηνών του τρέχοντος έτους.
Επιλέξτε το κελί B4. Πατήστε το συνδυασμό πλήκτρων Ctrl+1 για να ανοίξετε το παράθυρο Μορφή κελιού. Στην καρτέλα ΑριθμόςΣτο κεφάλαιο Όλες οι μορφέςεισάγετε προσαρμοσμένη μορφή αριθμού ΜΜΜ. Αυτή η μορφή εμφανίζει το όνομα του μήνα με τρία γράμματα. Ευθυγραμμίστε το κείμενο στη δεξιά άκρη του κελιού.
Αντιγράψτε τα περιεχόμενα του κελιού B4 στην περιοχή C4:M4. Μια σειρά με τα ονόματα των μηνών θα εμφανίζεται στην κορυφή του συγκεντρωτικού πίνακα.
Στο κελί B5, εισαγάγετε τον τύπο =IF(MONTH(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Γεγονόςκαι για το παρόν και το μέλλον - Σχέδιο.
Προσθέστε μια επικεφαλίδα στο κελί N5 Αποτέλεσμα. Στο κελί O4 - Αποτέλεσμα, Ο5 - Σχέδιο, Р5 - % απόκλιση.
Εισαγάγετε τους συνήθεις τύπους του Excel που χρησιμοποιούνται κατά τον υπολογισμό των συνόλων τμημάτων, της σειράς συνόλου της εταιρείας, της στήλης γενικού συνόλου και της στήλης % διακύμανσης:
1. στο κελί B8, εισαγάγετε τον τύπο = SUM (B6: B7) και αντιγράψτε τον σε άλλα κελιά της σειράς.
2. στο κελί N6, εισαγάγετε τον τύπο = SUM (B6: M6) και αντιγράψτε τον σε άλλα κελιά της στήλης.
3. στο κελί P6 εισαγάγετε τον τύπο =IFERROR((N6/O10)-1;0) και αντιγράψτε τον σε άλλα κελιά της στήλης.
4. στο κελί B13, εισαγάγετε τον τύπο = SUM (B10: B12) και αντιγράψτε τον σε άλλα κελιά της σειράς.
5. στο κελί B17, εισαγάγετε τον τύπο = SUM (B15: B16) και αντιγράψτε τον σε άλλα κελιά της σειράς.
6. στο κελί B19, εισαγάγετε τον τύπο =SUM(B6:B18)/2 και αντιγράψτε τον σε άλλα κελιά της σειράς.
Εφαρμόστε το στυλ Επικεφαλίδας 4 στις λεζάντες στη στήλη Α και στις επικεφαλίδες στις σειρές 4 και 5.
Για την περιοχή κελιών B6:O19, επιλέξτε τη μορφή αριθμού ###0.
Για κελιά στη στήλη P, επιλέξτε τη μορφή αριθμού 0,0%.

Έτσι, ολοκληρώσαμε τη δημιουργία του περιτυλίγματος αναφοράς που φαίνεται στο Σχ. 15. Αυτή η αναφορά περιλαμβάνει όλη την απαιτούμενη μορφοποίηση. Η ακόλουθη ενότητα δείχνει πώς να χρησιμοποιήσετε τη συνάρτηση GET.PIVOTAL.TABLE DATA για να ολοκληρώσετε μια αναφορά.

Ρύζι. 15. Αναφέρετε το περιτύλιγμα πριν προσθέσετε τύπους

Χρησιμοποιώντας τη συνάρτηση GET.PIVOTO.DATA.για να συμπληρώσετε το κέλυφος της αναφοράς με δεδομένα.Από εδώ και στο εξής, θα μπορείτε να απολαμβάνετε όλα τα οφέλη από τη χρήση της συνάρτησης GET.PIVOTO.TABLE.DATA. Εάν έχετε διαγράψει το πλαίσιο ελέγχου που ενεργοποιεί αυτήν τη λειτουργία, επιστρέψτε στην κατάλληλη ρύθμιση και επιστρέψτε το πλαίσιο ελέγχου (δείτε την περιγραφή στην εικ. 6 ή 7).

Επιλέξτε το κελί B6 του κελύφους αναφοράς. Αυτό το κελί αντιστοιχεί στη βορειοανατολική περιοχή και τα πραγματικά στοιχεία για τον Ιανουάριο.

Πληκτρολογήστε = (σύμβολο ίσου) για να ξεκινήσετε την εισαγωγή του τύπου.
Μεταβείτε στο φύλλο από τον συγκεντρωτικό πίνακα και κάντε κλικ στο κελί που αντιστοιχεί στη βορειοανατολική περιοχή και στα πραγματικά στοιχεία για τον Ιανουάριο - C12 (Εικ. 17).
Πατήστε το πλήκτρο Εισαγωγια να ολοκληρώσετε την εισαγωγή του τύπου και να επιστρέψετε στο κέλυφος της αναφοράς. Ως αποτέλεσμα, το Excel θα προσθέσει τη συνάρτηση GET.PICTABLE.TABLE DATA στο κελί B6. Το κελί θα δείχνει 277.435 $.

Λάβετε υπόψη αυτόν τον αριθμό, καθώς θα τον χρειαστείτε όταν τον συγκρίνετε με τα αποτελέσματα του τύπου που θα επεξεργαστείτε αργότερα. Ο τύπος που δημιουργείται από το πρόγραμμα έχει την ακόλουθη μορφή: =GET.DATA.PIVOTAL.TABLE(" Εισόδημα " ;'Εικ. 11-14′!$A$3; "Περιοχή" ; "Βορειοανατολική" ; "Ημερομηνία" ; 1, "Δείκτης", "Γεγονός"). Αν μέχρι τώρα αγνοούσατε τη λειτουργία GET.PIVOTAL.TABLE, ήρθε η ώρα να τη γνωρίσετε καλύτερα. Στο σχ. 18 αυτός ο τύπος εμφανίζεται σε λειτουργία επεξεργασίας μαζί με μια υπόδειξη.

Ορίσματα συνάρτησης:

Πεδίο_δεδομένων. Ένα πεδίο από την περιοχή Τιμές Συγκεντρωτικού Πίνακα. Σημείωση: σε αυτήν την περίπτωση, χρησιμοποιείται το πεδίο Εισόδημα, αλλά όχι Ποσό στο πεδίο Εισόδημα.
Συγκεντρωτικός πίνακας. Με αυτήν την επιλογή, η Microsoft σάς ρωτά "Ποιον Συγκεντρωτικός Πίνακας θέλετε να χρησιμοποιήσετε;" Αρκεί να καθορίσετε ένα από τα κελιά του συγκεντρωτικού πίνακα. Η καταχώρηση «Εικ. 11-14'!$A$3 αναφέρεται στο πρώτο κελί του Συγκεντρωτικού Πίνακα όπου εισάγονται δεδομένα. Εφόσον στην περίπτωσή μας μπορείτε να ορίσετε οποιοδήποτε κελί που σχετίζεται με τον συγκεντρωτικό πίνακα, αφήστε το όρισμα αμετάβλητο. Η διεύθυνση κελιού $A$3 ταιριάζει με κάθε τρόπο.
Πεδίο 1; στοιχείο 1. Στον τύπο που δημιουργείται αυτόματα, το όνομα επιλέγεται ως όνομα πεδίου Περιφέρειακαι ως τιμή πεδίου - Βορειοανατολικός. Εδώ βρίσκεται η αιτία των προβλημάτων που προκύπτουν κατά την εργασία με τη συνάρτηση GET.PIVOT.TABLE DATA. Οι αυτόματα επιλεγμένες τιμές δεν μπορούν να αντιγραφούν επειδή είναι κωδικοποιημένες. Επομένως, εάν αντιγράψετε τύπους σε ολόκληρη την περιοχή αναφοράς, θα πρέπει να τους αλλάξετε με μη αυτόματο τρόπο. Για τα βορειοανατολικά, αντικαταστήστε την αναφορά κελιού ως $A6. Καθορίζοντας ένα σύμβολο δολαρίου μπροστά από το όνομα της στήλης Α, προσδιορίζετε ότι το τμήμα της γραμμής της αναφοράς μπορεί να αλλάξει κατά την αντιγραφή του τύπου στα κελιά της στήλης.
Πεδίο 2; στοιχείο 2. Αυτό το ζεύγος ορισμάτων καθορίζει το πεδίο ημερομηνία τηςμε τιμή 1. Εάν ο αρχικός συγκεντρωτικός πίνακας ομαδοποιήθηκε κατά μήνα, το πεδίο μήνα διατηρεί το αρχικό όνομα πεδίου ημερομηνία της. Η αριθμητική τιμή του μήνα είναι 1, που αντιστοιχεί στον Ιανουάριο. Δεν συνιστάται να χρησιμοποιείτε μια τέτοια τιμή όταν δημιουργείτε τεράστιους τύπους που ορίζονται σε δεκάδες ή και εκατοντάδες κελιά αναφοράς. Είναι καλύτερα να χρησιμοποιήσετε έναν τύπο που υπολογίζει τις τιμές των πεδίων ημερομηνία της, όπως ο τύπος στο κελί B4. Αντί για 1, σε αυτήν την περίπτωση, μπορείτε να χρησιμοποιήσετε τον τύπο MONTH (B $ 4). Το σύμβολο του δολαρίου πριν από το 4 υποδεικνύει ότι ο τύπος μπορεί να εκχωρήσει τιμές στο πεδίο ημερομηνία τηςμε βάση άλλους μήνες καθώς ο τύπος αντιγράφεται στα κελιά της σειράς.
Πεδίο 3; στοιχείο 3. Σε αυτήν την περίπτωση, το όνομα του πεδίου εκχωρείται αυτόματα Δείκτηςκαι τιμή πεδίου Γεγονός. Αυτές οι τιμές είναι σωστές για τον Ιανουάριο, αλλά για τους επόμενους μήνες η τιμή του πεδίου θα πρέπει να αλλάξει σε Σχέδιο. Αλλάξτε την τιμή του πεδίου με σκληρό κώδικα Γεγονόςγια να συνδέσετε B$5.
Πεδίο 4; στοιχείο 4. Αυτά τα ορίσματα δεν χρησιμοποιούνται επειδή τελείωσαν τα χωράφια.

Ο νέος τύπος φαίνεται στο σχ. 19. Σε ένα λεπτό, αντί για έναν σκληρά κωδικοποιημένο τύπο που έχει σχεδιαστεί για να λειτουργεί με μία μόνο τιμή, δημιουργήθηκε ένας ευέλικτος τύπος που μπορεί να αντιγραφεί σε όλα τα κελιά του συνόλου δεδομένων. Πατήστε το πλήκτρο Εισαγω, και θα έχετε το ίδιο αποτέλεσμα όπως πριν από την επεξεργασία του τύπου. Ο επεξεργασμένος τύπος γίνεται: =GET.PIVOTOMAT.TABLE DATA("Έσοδα" ;'Εικ. 11-14′!$A$3; "Περιοχή" ;$A6; "Ημερομηνία" ;MONTH(B$4); "Δείκτης" ; B$5)

Ρύζι. 19. Μετά την ολοκλήρωση της επεξεργασίας, ο τύπος GET.DATA.PIVOTO.TABLE είναι κατάλληλος για αντιγραφή σε όλα τα κελιά της περιοχής

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

Στο επόμενο βήμα, θα ρυθμίσουμε τον τύπο GET.PIVOTO.TABLE.DATA για τον υπολογισμό των συνολικών προγραμματισμένων δεικτών. Η απλή αντιγραφή του τύπου στο κελί O6 θα εμφανίσει το #REF! Ο λόγος για αυτό το σφάλμα είναι ότι η λέξη Αποτέλεσμαστο κελί O4 δεν είναι το όνομα του μήνα. Για να διασφαλιστεί η σωστή λειτουργία της συνάρτησης GET.PIVOTO.TABLE.DATA, η απαιτούμενη τιμή πρέπει να βρίσκεται στον συγκεντρωτικό πίνακα. Επειδή όμως στον αρχικό συγκεντρωτικό πίνακα το πεδίο Δείκτηςείναι το δεύτερο πεδίο στην περιοχή της στήλης, η στήλη δεδομένων Αποτέλεσμα σχεδίουουσιαστικά απουσιάζει. Μετακινήστε το κουτί Δείκτηςώστε να γίνει το πρώτο στην περιοχή της στήλης (Εικ. 20).

Ρύζι. 20. Προσαρμόστε τη διάταξη των πεδίων στην περιοχή στηλών έτσι ώστε να εμφανίζεται μια στήλη Σχέδιο Αποτέλεσμα

Σύγκριση με το σχ. 14. Εκεί, στην περιοχή ΣΤΗΛΗ, πρώτο πήγε το χωράφι ημερομηνία της, γεγονός που οδήγησε στο γεγονός ότι αρχικά οι στήλες ομαδοποιήθηκαν κατά ημερομηνία και εντός κάθε μήνα κατά σχέδιο / γεγονός. Τώρα το πρώτο πεδίο είναι ο δείκτης, και στη σύνοψη, οι στήλες είναι πρώτες Σχέδιο, εσωτερικά ταξινομημένη ανά μήνα, ακολουθούμενη από όλες τις στήλες Γεγονός.

Επιστρέφοντας στο φύλλο κελύφους της αναφοράς, σταθείτε στο κελί O6, πληκτρολογήστε = (=) και ανατρέξτε στο κελί N12 στο φύλλο του συγκεντρωτικού πίνακα που αντιστοιχεί στα προγραμματισμένα σύνολα για τη βορειοανατολική περιοχή. Κάντε κλικ Εισαγω. Το αποτέλεσμα είναι ο τύπος = GET. DATA. OF. A. PIVOT. TABLE ("Εισόδημα"; 'Εικ. 11-14'! $ A $ 3; "Περιοχή"; "Βορειοανατολικά"; "Δείκτης"; " Σχέδιο"). Επεξεργαστείτε το: =GET. SUMMARY.TABLE.DATA("Έσοδα";'Εικ. 11-14'!$A$3;"Περιοχή";$A6;"Δείκτης";O$5). Αντιγράψτε αυτόν τον τύπο σε άλλα κελιά στη στήλη Ο (Εικ. 21). Λάβετε υπόψη ότι ακόμη και όταν μετακινείτε διαφορετικές περιοχές της αναφοράς Συγκεντρωτικού Πίνακα, το περιτύλιγμα λειτουργεί σωστά. Φυσικά, αν κάνετε ορισμένα πεδία της σύνοψης ανενεργά, το κέλυφος δεν θα αντιμετωπίσει αυτό ...

Ρύζι. 21. Τελική έκθεση που μπορεί να παρουσιαστεί στον διαχειριστή

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

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

Εισαγάγετε τα πραγματικά κάτω από το αρχικό σύνολο δεδομένων. Επειδή η μορφή πίνακα έχει επιλεγεί για τα δεδομένα προέλευσης, η μορφοποίηση πίνακα μεταδίδεται αυτόματα σε νέες σειρές δεδομένων. Επεκτείνει επίσης τον ορισμό του αρχικού Συγκεντρωτικού Πίνακα (έχω ήδη προσθέσει τα πραγματικά στοιχεία για ολόκληρο το έτος στο αρχείο Excel).
Μετάβαση στον συγκεντρωτικό πίνακα. Κάντε δεξί κλικ και επιλέξτε Φρεσκάρω. Η εμφάνιση του πίνακα περιστροφής θα αλλάξει, αλλά δεν είναι τρομακτικό.
Μεταβείτε στο περιτύλιγμα της αναφοράς. Κατ' αρχήν, έχουν ήδη γίνει τα πάντα για την ενημέρωση της αναφοράς, αλλά δεν παρεμβαίνει στη δοκιμή των αποτελεσμάτων. Αλλάξτε τον τύπο στο κελί A2, για παράδειγμα, σε αυτό: =OMONTH(TODAY() +31 ;0) και δες τι θα γίνει.

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

Δεν πίστευα ότι θα έλεγα ποτέ το εξής: «Η συνάρτηση GET.PIVOTO.TABLE.Function είναι το μεγαλύτερο όφελος. Πώς ήμασταν χωρίς αυτό πριν;

Στο πρωτότυπο, τα αρχικά δεδομένα της Jelen ήταν τακτοποιημένα έτσι ώστε οι περαιτέρω τύποι να λειτουργούσαν σωστά μόνο τον Ιούλιο του 2015. Στο αρχείο Excel που επισυνάπτεται σε αυτήν τη σημείωση, τροποποίησα τα αρχικά δεδομένα, καθώς και ορισμένους τύπους, έτσι ώστε όλα να λειτουργούν, ανεξάρτητα από την ημερομηνία Θα πειραματιστείτε με το συνημμένο αρχείο Excel. Δυστυχώς, οι τύποι έπρεπε να είναι περίπλοκοι.

Νωρίτερα, μίλησα ήδη για το γεγονός ότι όταν αναφερόμαστε σε ένα κελί συγκεντρωτικού πίνακα, αντί για μια κανονική αναφορά, επιστρέφεται η συνάρτηση GET.PIVOTO.TABLE.DATA (βλ.). Αν ενδιαφέρεσαι ΠωςΓια να ξεπεράσετε αυτήν την ταλαιπωρία, σας συνιστώ να ανατρέξετε στην αναφερόμενη σημείωση. Αν ενδιαφέρεσαι, Γιατίαυτό συμβαίνει, και επίσης, ποιες είναι οι θετικές πτυχές της συνάρτησης GET.DATA.PIVOTO.TABLE, τότε προσφέρω ένα απόσπασμα του βιβλίου του Jelen, Alexander. (κεφάλαιο 15). Η τεχνική που εξετάζουμε θα σας επιτρέψει να αντιμετωπίσετε πολλά προβλήματα που προκαλούν πονοκεφάλους στους χρήστες συγκεντρωτικών πινάκων, ιδίως:

Η ανανέωση ενός Συγκεντρωτικού Πίνακα καταργεί τη μορφοποίηση που εφαρμόστηκε προηγουμένως. Οι αριθμητικές μορφές χάνονται. Τα αποτελέσματα της προσαρμογής του πλάτους των στηλών εξαφανίζονται.
Δεν υπάρχει εύκολος τρόπος για να δημιουργήσετε έναν ασύμμετρο συγκεντρωτικό πίνακα. Η μόνη επιλογή είναι να χρησιμοποιήσετε ονομασμένα σύνολα, αλλά αυτή η μέθοδος είναι διαθέσιμη μόνο σε όσους χρησιμοποιούν Συγκεντρωτικούς Πίνακες Μοντέλων Δεδομένων και όχι κανονικούς Συγκεντρωτικούς Πίνακες.
Το Excel δεν μπορεί να θυμηθεί πρότυπα. Εάν χρειάζεται να δημιουργείτε Συγκεντρωτικούς Πίνακες ξανά και ξανά, θα πρέπει να ομαδοποιήσετε εκ νέου, να εφαρμόσετε υπολογισμένα πεδία και μέλη και να εκτελέσετε μια σειρά από άλλες παρόμοιες εργασίες.

Λήψη σημείωσης σε ή μορφή, παραδείγματα σε μορφή

Λοιπόν, ας ξεκινήσουμε με τη σειρά.

Πώς να εγκαταλείψετε την προβληματική λειτουργία GET.DATA.PIVOTO.TABLE

Ρύζι. 1. Αρχικός συγκεντρωτικός πίνακας

Προσθέστε την επικεφαλίδα "% Growth" στο κελί D3.
Αντιγράψτε τη μορφή από το κελί C3 στο κελί D3.
Στο κελί D4, εισαγάγετε ένα σύμβολο ίσου.
Κάντε κλικ στο κελί C4.
Εισαγάγετε το σύμβολο / (κάθετο) για να υποδείξετε τη λειτουργία διαίρεσης.
Κάντε κλικ στο κελί B4.
Πληκτρολογήστε -1 και πατήστε το συνδυασμό πλήκτρων να μείνει στο ίδιο κελί. Μορφοποιήστε το αποτέλεσμα ως ποσοστό. Θα δείτε ότι η Δυτική περιοχή παρουσίασε πτώση εσόδων κατά 43,8% (Εικόνα 2). Όχι πολύ καλά αποτελέσματα.
Αφού ολοκληρώσετε την εισαγωγή του πρώτου σας τύπου, επιλέξτε το κελί D4.
Κάντε διπλό κλικ στο μικρό τετράγωνο που βρίσκεται στην κάτω δεξιά γωνία του κελιού. Αυτό το πλαίσιο αντιπροσωπεύει μια λαβή πλήρωσης που μπορεί να χρησιμοποιηθεί για την αντιγραφή ενός τύπου για τη συμπλήρωση μιας ολόκληρης στήλης αναφοράς.

Ρύζι. 3. Αφού αντιγράψετε τον τύπο σε όλα τα κελιά της στήλης, θα δείτε ότι κάθε περιοχή παρουσίασε πτώση 43,8%

Δεν συμβαίνει σχεδόν καθόλου στην πραγματική ζωή. Οποιοσδήποτε χρήστης θα σας πει ότι αφού κάνει τα παραπάνω βήματα, το Excel θα δημιουργήσει τον τύπο =C4/B4-1. Επιστρέψτε στο κελί D4 και δώστε προσοχή στη γραμμή τύπων (Εικόνα 4). Είναι απλά κάτι καταραμένο! Ο απλός τύπος =C4/B4–1 δεν υπάρχει πλέον. Αντίθετα, το πρόγραμμα αντικαθιστά μια σύνθετη δομή με τη συνάρτηση GET.PIVOTAL.TABLE.DATA. Γιατί αυτός ο τύπος δίνει σωστά αποτελέσματα στο κελί D4, αλλά όταν αντιγράφεται στα παρακάτω κελιά δεν λειτουργεί;

Δεν υπήρχε κάτι τέτοιο στις μέρες του Excel 2000. Όταν άρχισα να βλέπω τη συνάρτηση GET.DATA.PIVOTE.TABLE σε τακτική βάση, απλώς τη μισούσα. Όταν κάποιος με ρώτησε σε ένα από τα σεμινάρια πώς μπορεί να χρησιμοποιηθεί για το καλό του σκοπού, έμεινα άναυδος. Δεν έχω κάνει ποτέ στον εαυτό μου τέτοια ερώτηση! Κατά τη γνώμη μου, και κατά τη γνώμη των περισσότερων χρηστών του Excel, η συνάρτηση GET.DATA.PIVOTAL.TABLE ήταν ένα προϊόν του κακού που δεν είχε καμία σχέση με τις δυνάμεις του καλού. Ευτυχώς, υπάρχουν δύο τρόποι για να απενεργοποιήσετε αυτήν τη δυνατότητα.

Μεταβείτε στο κελί D4 και πληκτρολογήστε = (σύμβολο ίσου).
Εισαγάγετε C4.
Τύπος / (κάθετο για διαίρεση).
Εισάγετε B4.
Εισαγάγετε -1.
Κάντε κλικ Εισαγω.

Έχετε πλέον δημιουργήσει έναν κανονικό τύπο Excel που μπορείτε να αντιγράψετε στα κελιά της στήλης παρακάτω και να λάβετε τα σωστά αποτελέσματα (Εικόνα 5). Όπως μπορείτε να δείτε, μπορείτε να δημιουργήσετε τύπους σε περιοχές εκτός του Συγκεντρωτικού Πίνακα που αναφέρονται στα δεδομένα μέσα στον Συγκεντρωτικό Πίνακα. Και όσοι δεν πιστεύουν ότι αυτό είναι δυνατό, ας κάνουν μόνοι τους τις περιγραφόμενες ενέργειες.

Ρύζι. 5. Απλώς πληκτρολογήστε =С4/В4–1 από το πληκτρολόγιο και ο τύπος θα λειτουργήσει όπως θα έπρεπε

Χρησιμοποιώντας τη συνάρτηση GET.PIVOTO.TABLE.DATA για τη βελτίωση των συγκεντρωτικών πινάκων

Δημιουργία ενός πρωτόγονου πίνακα περιστροφής.Τα αρχικά δεδομένα (Εικ. 10) παρουσιάζονται με τη μορφή συναλλαγών που περιέχουν πληροφορίες για προγραμματισμένους και πραγματικούς δείκτες για κάθε περιοχή στην οποία υπάρχουν υποκαταστήματα εταιρείας. Οι προγραμματισμένοι δείκτες είναι λεπτομερείς σε επίπεδο μηνών και πραγματικοί - σε επίπεδο μεμονωμένων ημερών. Δημιουργούνται προγραμματισμένοι δείκτες για το επόμενο έτος, και πραγματικοί - για τους τελευταίους μήνες. Επειδή η αναφορά θα ενημερώνεται κάθε μήνα, αυτή η διαδικασία απλοποιείται σημαντικά εάν η πηγή δεδομένων Συγκεντρωτικού Πίνακα μεγαλώσει σε μέγεθος καθώς προστίθενται νέα δεδομένα στο κάτω μέρος. Σε παλαιότερες εκδόσεις του Excel, η δημιουργία μιας τέτοιας προέλευσης δεδομένων πραγματοποιήθηκε χρησιμοποιώντας μια ονομασμένη δυναμική περιοχή χρησιμοποιώντας τη συνάρτηση OFFSET (για περισσότερες λεπτομέρειες, βλ.). Όταν εργάζεστε στο Excel 2013, απλώς επιλέξτε ένα από τα κελιά δεδομένων και πατήστε Ctrl+T (δημιουργία πίνακα). Θα εμφανιστεί ένα ονομασμένο σύνολο δεδομένων, το οποίο επεκτείνεται αυτόματα καθώς προστίθενται νέες σειρές και στήλες.

Δημιουργία συγκεντρωτικού πίνακα:

Επιλέξτε μια ομάδα Εισάγετε → συγκεντρωτικός πίνακαςκαι μετά στο παράθυρο διαλόγου Δημιουργήστε έναν Συγκεντρωτικό ΠίνακαΚάντε κλικ Εντάξει.
Στη λίστα Πεδία Συγκεντρωτικού Πίνακα, επιλέξτε το πεδίο ημερομηνία της. Μια λίστα ημερομηνιών θα εμφανιστεί στην αριστερή πλευρά του συγκεντρωτικού πίνακα (Εικ. 11).
Επιλέξτε οποιοδήποτε κελί ημερομηνίας, για παράδειγμα, A4. Στην καρτέλα περιβάλλοντος Ανάλυση, που βρίσκεται στο σύνολο των καρτελών με βάση τα συμφραζόμενα Εργασία με πίνακες περιστροφής, κάντε κλικ στο κουμπί Ομάδα ανά πεδίο(για λεπτομέρειες βλ.). Στο πλαίσιο διαλόγου Ομαδοποίησηεπιλογή επιλογής Μήνες(Εικ. 12). Κάντε κλικ Εντάξει. Τα ονόματα των μηνών θα εμφανίζονται στην αριστερή πλευρά του συγκεντρωτικού πίνακα (Εικ. 13).
Σύρετε το πλαίσιο ημερομηνία τηςστην περιοχή Στήλες Συγκεντρωτικού Πίνακα.
Σύρετε το πλαίσιο Δείκτηςστην περιοχή στήλης της λίστας πεδίων Συγκεντρωτικού Πίνακα.
Επιλέξτε πεδίο Περιφέρεια, το οποίο θα εμφανίζεται στην αριστερή στήλη του συγκεντρωτικού πίνακα.
Επιλέξτε πεδίο Εισόδημα, το οποίο θα εμφανιστεί στην περιοχή Τιμές Συγκεντρωτικού Πίνακα.

Ρύζι. 11. Ξεκινήστε ομαδοποιώντας ανά πεδίο ημερομηνία της

Σε αυτό το στάδιο, ο πίνακας περιστροφής μας φαίνεται μάλλον πρωτόγονος (Εικ. 14). Πραγματικά δεν μου αρέσουν οι ταμπέλες Ονόματα σειρώνκαι Ονόματα στηλών. Δεν είναι σωστό να εμφανίζονται τα σύνολα για Jan Planκαι Γιαν Γεγονόςστη στήλη Δ κ.λπ. Αλλά μην ανησυχείτε για την εμφάνιση αυτού του συγκεντρωτικού πίνακα, γιατί κανείς άλλος δεν θα το δει εκτός από εσάς. Ξεκινώντας από αυτό το σημείο, θα δημιουργήσουμε ένα κέλυφος αναφοράς, η πηγή δεδομένων του οποίου θα είναι ο συγκεντρωτικός πίνακας που μόλις δημιουργήσαμε.

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

Εκτελέστε τα παρακάτω βήματα (Εικ. 15).

Στο κελί A1, εισαγάγετε το όνομα της αναφοράς - Προγραμματισμένοι και πραγματικοί δείκτες ανά περιφέρεια.
Μεταβείτε στην καρτέλα Σπίτι, κάντε κλικ στο κουμπί Στυλ κυττάρωνεπιλέξτε μορφή Επικεφαλίδα 1.
Στο κελί A2, εισαγάγετε τον τύπο = MONTH (ΣΗΜΕΡΑ (); 0). Αυτή η συνάρτηση επιστρέφει την τελευταία ημέρα του τρέχοντος μήνα. Για παράδειγμα, εάν το διαβάζετε στις 14 Αυγούστου 2014, το κελί A2 θα έγραφε 31 Αυγούστου 2014.
Επιλέξτε το κελί A2. Πατήστε το συνδυασμό πλήκτρων Ctrl+1 για να εμφανιστεί το πλαίσιο διαλόγου Μορφή κελιού. Στην καρτέλα Αριθμόςκάντε κλικ στο στοιχείο Όλες οι μορφές. Εισαγάγετε μια προσαρμοσμένη μορφή αριθμού στη φόρμα "Από τον μήνα" ΜΜΜΜ "προγραμματισμένοι δείκτες"(Εικ. 16). Ως αποτέλεσμα, η υπολογισμένη ημερομηνία θα μοιάζει με κείμενο.
Στο κελί A5, εισαγάγετε μια επικεφαλίδα Περιφέρεια.
Εισαγάγετε τους τίτλους περιοχών στα υπόλοιπα κελιά στη στήλη A. Οι τίτλοι περιοχών πρέπει να ταιριάζουν με τα ονόματα περιοχών στον Συγκεντρωτικό Πίνακα.
Εάν είναι απαραίτητο, προσθέστε ετικέτες στη στήλη για τα σύνολα των τμημάτων.
Προσθέστε μια γραμμή στο κάτω μέρος της αναφοράς Σύνολο για την εταιρεία.
Στο κελί B4, εισαγάγετε τον τύπο =DATE(YEAR($A$2),COLUMN(A1),1). Αυτός ο τύπος επιστρέφει τις ημερομηνίες 01/01/2014, 02/01/2104 κ.λπ., τις πρώτες ημέρες και των 12 μηνών του τρέχοντος έτους.
Επιλέξτε το κελί B4. Πατήστε το συνδυασμό πλήκτρων Ctrl+1 για να ανοίξετε το παράθυρο Μορφή κελιού. Στην καρτέλα ΑριθμόςΣτο κεφάλαιο Όλες οι μορφέςεισάγετε προσαρμοσμένη μορφή αριθμού ΜΜΜ. Αυτή η μορφή εμφανίζει το όνομα του μήνα με τρία γράμματα. Ευθυγραμμίστε το κείμενο στη δεξιά άκρη του κελιού.
Αντιγράψτε τα περιεχόμενα του κελιού B4 στην περιοχή C4:M4. Μια σειρά με τα ονόματα των μηνών θα εμφανίζεται στην κορυφή του συγκεντρωτικού πίνακα.
Στο κελί B5, εισαγάγετε τον τύπο =IF(MONTH(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Γεγονόςκαι για το παρόν και το μέλλον - Σχέδιο.
Προσθέστε μια επικεφαλίδα στο κελί N5 Αποτέλεσμα. Στο κελί O4 - Αποτέλεσμα, Ο5 - Σχέδιο, Р5 - % απόκλιση.
Εισαγάγετε τους συνήθεις τύπους του Excel που χρησιμοποιούνται κατά τον υπολογισμό των συνόλων τμημάτων, της σειράς συνόλου της εταιρείας, της στήλης γενικού συνόλου και της στήλης % διακύμανσης:
1. στο κελί B8, εισαγάγετε τον τύπο = SUM (B6: B7) και αντιγράψτε τον σε άλλα κελιά της σειράς.
2. στο κελί N6, εισαγάγετε τον τύπο = SUM (B6: M6) και αντιγράψτε τον σε άλλα κελιά της στήλης.
3. στο κελί P6 εισαγάγετε τον τύπο =IFERROR((N6/O10)-1;0) και αντιγράψτε τον σε άλλα κελιά της στήλης.
4. στο κελί B13, εισαγάγετε τον τύπο = SUM (B10: B12) και αντιγράψτε τον σε άλλα κελιά της σειράς.
5. στο κελί B17, εισαγάγετε τον τύπο = SUM (B15: B16) και αντιγράψτε τον σε άλλα κελιά της σειράς.
6. στο κελί B19, εισαγάγετε τον τύπο =SUM(B6:B18)/2 και αντιγράψτε τον σε άλλα κελιά της σειράς.
Εφαρμόστε το στυλ Επικεφαλίδας 4 στις λεζάντες στη στήλη Α και στις επικεφαλίδες στις σειρές 4 και 5.
Για την περιοχή κελιών B6:O19, επιλέξτε τη μορφή αριθμού ###0.
Για κελιά στη στήλη P, επιλέξτε τη μορφή αριθμού 0,0%.

Ρύζι. 15. Αναφέρετε το περιτύλιγμα πριν προσθέσετε τύπους

Πληκτρολογήστε = (σύμβολο ίσου) για να ξεκινήσετε την εισαγωγή του τύπου.
Μεταβείτε στο φύλλο από τον συγκεντρωτικό πίνακα και κάντε κλικ στο κελί που αντιστοιχεί στη βορειοανατολική περιοχή και στα πραγματικά στοιχεία για τον Ιανουάριο - C12 (Εικ. 17).
Πατήστε το πλήκτρο Εισαγωγια να ολοκληρώσετε την εισαγωγή του τύπου και να επιστρέψετε στο κέλυφος της αναφοράς. Ως αποτέλεσμα, το Excel θα προσθέσει τη συνάρτηση GET.PICTABLE.TABLE DATA στο κελί B6. Το κελί θα δείχνει 277.435 $.

Ορίσματα συνάρτησης:

Πεδίο_δεδομένων. Ένα πεδίο από την περιοχή Τιμές Συγκεντρωτικού Πίνακα. Σημείωση: σε αυτήν την περίπτωση, χρησιμοποιείται το πεδίο Εισόδημα, αλλά όχι Ποσό στο πεδίο Εισόδημα.
Συγκεντρωτικός πίνακας. Με αυτήν την επιλογή, η Microsoft σάς ρωτά "Ποιον Συγκεντρωτικός Πίνακας θέλετε να χρησιμοποιήσετε;" Αρκεί να καθορίσετε ένα από τα κελιά του συγκεντρωτικού πίνακα. Η καταχώρηση «Εικ. 11-14'!$A$3 αναφέρεται στο πρώτο κελί του Συγκεντρωτικού Πίνακα όπου εισάγονται δεδομένα. Εφόσον στην περίπτωσή μας μπορείτε να ορίσετε οποιοδήποτε κελί που σχετίζεται με τον συγκεντρωτικό πίνακα, αφήστε το όρισμα αμετάβλητο. Η διεύθυνση κελιού $A$3 ταιριάζει με κάθε τρόπο.
Πεδίο 1; στοιχείο 1. Στον τύπο που δημιουργείται αυτόματα, το όνομα επιλέγεται ως όνομα πεδίου Περιφέρειακαι ως τιμή πεδίου - Βορειοανατολικός. Εδώ βρίσκεται η αιτία των προβλημάτων που προκύπτουν κατά την εργασία με τη συνάρτηση GET.PIVOT.TABLE DATA. Οι αυτόματα επιλεγμένες τιμές δεν μπορούν να αντιγραφούν επειδή είναι κωδικοποιημένες. Επομένως, εάν αντιγράψετε τύπους σε ολόκληρη την περιοχή αναφοράς, θα πρέπει να τους αλλάξετε με μη αυτόματο τρόπο. Για τα βορειοανατολικά, αντικαταστήστε την αναφορά κελιού ως $A6. Καθορίζοντας ένα σύμβολο δολαρίου μπροστά από το όνομα της στήλης Α, προσδιορίζετε ότι το τμήμα της γραμμής της αναφοράς μπορεί να αλλάξει κατά την αντιγραφή του τύπου στα κελιά της στήλης.
Πεδίο 2; στοιχείο 2. Αυτό το ζεύγος ορισμάτων καθορίζει το πεδίο ημερομηνία τηςμε τιμή 1. Εάν ο αρχικός συγκεντρωτικός πίνακας ομαδοποιήθηκε κατά μήνα, το πεδίο μήνα διατηρεί το αρχικό όνομα πεδίου ημερομηνία της. Η αριθμητική τιμή του μήνα είναι 1, που αντιστοιχεί στον Ιανουάριο. Δεν συνιστάται να χρησιμοποιείτε μια τέτοια τιμή όταν δημιουργείτε τεράστιους τύπους που ορίζονται σε δεκάδες ή και εκατοντάδες κελιά αναφοράς. Είναι καλύτερα να χρησιμοποιήσετε έναν τύπο που υπολογίζει τις τιμές των πεδίων ημερομηνία της, όπως ο τύπος στο κελί B4. Αντί για 1, σε αυτήν την περίπτωση, μπορείτε να χρησιμοποιήσετε τον τύπο MONTH (B $ 4). Το σύμβολο του δολαρίου πριν από το 4 υποδεικνύει ότι ο τύπος μπορεί να εκχωρήσει τιμές στο πεδίο ημερομηνία τηςμε βάση άλλους μήνες καθώς ο τύπος αντιγράφεται στα κελιά της σειράς.
Πεδίο 3; στοιχείο 3. Σε αυτήν την περίπτωση, το όνομα του πεδίου εκχωρείται αυτόματα Δείκτηςκαι τιμή πεδίου Γεγονός. Αυτές οι τιμές είναι σωστές για τον Ιανουάριο, αλλά για τους επόμενους μήνες η τιμή του πεδίου θα πρέπει να αλλάξει σε Σχέδιο. Αλλάξτε την τιμή του πεδίου με σκληρό κώδικα Γεγονόςγια να συνδέσετε B$5.
Πεδίο 4; στοιχείο 4. Αυτά τα ορίσματα δεν χρησιμοποιούνται επειδή τελείωσαν τα χωράφια.

Ρύζι. 21. Τελική έκθεση που μπορεί να παρουσιαστεί στον διαχειριστή

Εισαγάγετε τα πραγματικά κάτω από το αρχικό σύνολο δεδομένων. Επειδή η μορφή πίνακα έχει επιλεγεί για τα δεδομένα προέλευσης, η μορφοποίηση πίνακα μεταδίδεται αυτόματα σε νέες σειρές δεδομένων. Επεκτείνει επίσης τον ορισμό του αρχικού Συγκεντρωτικού Πίνακα (έχω ήδη προσθέσει τα πραγματικά στοιχεία για ολόκληρο το έτος στο αρχείο Excel).
Μετάβαση στον συγκεντρωτικό πίνακα. Κάντε δεξί κλικ και επιλέξτε Φρεσκάρω. Η εμφάνιση του πίνακα περιστροφής θα αλλάξει, αλλά δεν είναι τρομακτικό.
Μεταβείτε στο περιτύλιγμα της αναφοράς. Κατ' αρχήν, έχουν ήδη γίνει τα πάντα για την ενημέρωση της αναφοράς, αλλά δεν παρεμβαίνει στη δοκιμή των αποτελεσμάτων. Αλλάξτε τον τύπο στο κελί A2, για παράδειγμα, σε αυτό: =OMONTH(TODAY() +31 ;0) και δες τι θα γίνει.

Για Συγκεντρωτικούς Πίνακες, η συνάρτηση GET.PIVOTO.TABLE DATA επιστρέφει τα δεδομένα που είναι αποθηκευμένα σε μια αναφορά Συγκεντρωτικού Πίνακα.

Για να αποκτήσετε γρήγορη πρόσβαση στη συνάρτηση, πρέπει να εισαγάγετε ένα σύμβολο ίσου σε ένα κελί (=) και να επιλέξετε το επιθυμητό κελί στον συγκεντρωτικό πίνακα. Το Excel θα δημιουργήσει αυτόματα τη συνάρτηση GET.PIVOTOMATIC.DATA.

Απενεργοποιήστε τη δημιουργία GetPivotData

Για να απενεργοποιήσετε την αυτόματη δημιουργία της συνάρτησης GET.PIVOTO.TABLE.DATA, επιλέξτε οποιοδήποτε κελί στον συγκεντρωτικό πίνακα και μεταβείτε στην καρτέλα Εργασία με συγκεντρωτικούς πίνακες -> Επιλογέςστην ομάδα Συγκεντρωτικός πίνακας.Κάντε κλικ στο κάτω βέλος δίπλα στην καρτέλα Επιλογές.Στο αναπτυσσόμενο μενού, καταργήστε την επιλογή του πλαισίου ΔημιουργώGetPivotData.

Χρήση αναφορών κελιών στη συνάρτηση GET.PIVOTO.TABLE DATA

Αντί να καθορίσετε τα ονόματα των στοιχείων ή των πεδίων στη συνάρτηση GET.DATA.PIVOTO.TABLE, μπορείτε να ανατρέξετε στα κελιά που βρίσκονται στο φύλλο. Στο παρακάτω παράδειγμα, το κελί E3 περιέχει το όνομα του προϊόντος και ο τύπος στο κελί E4 αναφέρεται σε αυτό. Ως αποτέλεσμα, ο συνολικός όγκος για κέικ θα επιστραφεί.

Χρήση Αναφορών Πεδίου Συγκεντρωτικού Πίνακα

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

Στο παράδειγμα, το κελί E3 περιέχει το όνομα του πεδίου δεδομένων "Count" και θα ήταν ωραίο να αναφερθούμε σε αυτό το κελί στη συνάρτηση, αντί να υπάρχει το όνομα του πεδίου στον τύπο GET.DATA.PIVOTO.TABLE.

Ωστόσο, αν αλλάξουμε το πρώτο επιχείρημα πεδίο_δεδομένωνσε μια αναφορά στο κελί E3, το Excel θα επιστρέψει το σφάλμα #LINK!

GET.PIVOTO.TABLE.DATA(E3,$A$3)

Μια απλή προσθήκη κενού συμβολοσειράς (“”) στην αρχή ή στο τέλος της αναφοράς κελιού θα λύσει το πρόβλημα.

GET.PIVOTO.TABLE.DATA(E3&"";$A$3)

Μια απλή διόρθωση του τύπου θα επιστρέψει τη σωστή τιμή.

Χρήση ημερομηνιών στο GET DATA

Εάν χρησιμοποιείτε ημερομηνίες στη συνάρτηση GET.PIVOTO.TABLE, ενδέχεται να έχετε προβλήματα ακόμα και αν η ημερομηνία εμφανίζεται στον Συγκεντρωτικό Πίνακα. Για παράδειγμα, το όρισμα τύπου παρακάτω είναι η ημερομηνία "21/04/2013" και ο Συγκεντρωτικός Πίνακας περιέχει ένα πεδίο με ημερομηνίες πωλήσεων. Ωστόσο, ο τύπος στο κελί E4 επιστρέφει ένα σφάλμα.

GET.PIVOTO.TABLE.DATA("Τόμος",$A$3,"Ημερομηνία","04/21/2013")

Για να αποτρέψετε σφάλματα που σχετίζονται με την ημερομηνία, μπορείτε να χρησιμοποιήσετε μία από τις ακόλουθες μεθόδους:

Συγκρίνετε τις μορφές ημερομηνίας στον τύπο και τον συγκεντρωτικό πίνακα
Χρησιμοποιήστε τη συνάρτηση DATEVALUE
Χρησιμοποιήστε τη συνάρτηση DATE
Ανατρέξτε στο κελί με έγκυρη ημερομηνία

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

Για να λάβετε το σωστό αποτέλεσμα, όταν χρησιμοποιείτε τη συνάρτηση GET.PIVOTOMAT.DATA, βεβαιωθείτε ότι οι μορφές ημερομηνίας του ορίσματος τύπου και του συγκεντρωτικού πίνακα είναι οι ίδιες.

Στο κελί E4, ο τύπος χρησιμοποιεί τη μορφή ημερομηνίας "ΗΗ.ΜΜ.ΕΕΕΕ" και επιστρέφει τις σωστές πληροφορίες.

Χρησιμοποιώντας τη συνάρτηση DATEVALUE

Αντί να εισάγετε μη αυτόματα την ημερομηνία στον τύπο, μπορείτε να προσθέσετε τη συνάρτηση DATEVALUE για να επιστρέψετε την ημερομηνία.

Στο κελί E4, η ημερομηνία εισάγεται χρησιμοποιώντας τη συνάρτηση DATEVALUE και το Excel επιστρέφει τις απαιτούμενες πληροφορίες.

GET.PIVOTO.TABLE.DATA("Τόμος",$A$3,"Ημερομηνία",DATEVALUE("21/04/2013"))

Χρησιμοποιώντας τη συνάρτηση DATE

Αντί να εισάγετε με μη αυτόματο τρόπο την ημερομηνία στον τύπο, μπορείτε να χρησιμοποιήσετε τη συνάρτηση DATE, η οποία θα επιστρέψει σωστά τις απαραίτητες πληροφορίες.

GET.PIVOTO.TABLE.DATA("Τόμος",$A$3,"Ημερομηνία",DATE(2013,4,21))

Σύνδεση σε ένα κελί με ημερομηνία

Αντί να εισάγετε με μη αυτόματο τρόπο μια ημερομηνία σε έναν τύπο, μπορείτε να ανατρέξετε σε ένα κελί που περιέχει μια ημερομηνία (σε οποιαδήποτε μορφή το Excel αντιμετωπίζει τα δεδομένα ως ημερομηνίες). Στο παράδειγμα στο κελί E4, ο τύπος αναφέρεται στο κελί E3 και το Excel επιστρέφει τα σωστά δεδομένα.

GET.PIVOTO.TABLE.DATA("Τόμος",$A$3,"Ημερομηνία",E3)