Οι προϋπολογισμοί γίνονται στο ανώτερο επίπεδο - έσοδα ανά γραμμή προϊόντος ανά περιοχή ανά μήνα. Τα πραγματικά συσσωρεύονται αργά με την πάροδο του χρόνου - τιμολόγιο ανά τιμολόγιο, στοιχείο γραμμής ανά στοιχείο γραμμής. Η σύγκριση του μικρού αρχείου προϋπολογισμού με τα ογκώδη πραγματικά δεδομένα ήταν ένας πόνος για πάντα. Μου αρέσει αυτό το κόλπο από τον Rob Collie, γνωστό και ως PowerPivotPro.com.
Για να ρυθμίσετε το παράδειγμα, έχετε έναν πίνακα προϋπολογισμού 54 σειρών: 1 σειρά ανά μήνα ανά περιοχή ανά προϊόν.
Το αρχείο τιμολογίου βρίσκεται στο επίπεδο λεπτομέρειας: 422 σειρές μέχρι στιγμής φέτος.
Δεν υπάρχει VLOOKUP στον κόσμο που θα σας αφήσει ποτέ να ταιριάξετε με αυτά τα δύο σύνολα δεδομένων. Όμως, χάρη στο Power Pivot (γνωστό και ως μοντέλο δεδομένων στο Excel 2013+), αυτό γίνεται εύκολο.
Πρέπει να δημιουργήσετε μικροσκοπικούς πίνακες που ονομάζω «ξυλουργούς» για να συνδέσετε τα δύο μεγαλύτερα σύνολα δεδομένων.
Στην περίπτωσή μου, το προϊόν, η περιοχή και η ημερομηνία είναι κοινά μεταξύ των δύο πινάκων. Ο πίνακας προϊόντων είναι ένας μικροσκοπικός πίνακας τεσσάρων κελιών. Ditto για περιοχή. Δημιουργήστε κάθε ένα από αυτά αντιγράφοντας δεδομένα από έναν πίνακα και χρησιμοποιώντας το Remove Duplicates.
Ο πίνακας ημερολογίου στα δεξιά ήταν στην πραγματικότητα πιο δύσκολο να δημιουργηθεί. Τα δεδομένα προϋπολογισμού έχουν μία σειρά ανά μήνα, πάντα πέφτοντας στο τέλος του μήνα. Τα δεδομένα τιμολογίου εμφανίζουν ημερήσιες ημερομηνίες, συνήθως τις καθημερινές. Επομένως, έπρεπε να αντιγράψω το πεδίο Ημερομηνία και από τα δύο σύνολα δεδομένων σε μία μόνο στήλη και έπειτα να αφαιρέσω τα διπλότυπα για να βεβαιωθώ ότι αντιπροσωπεύονται όλες οι ημερομηνίες. Στη συνέχεια, =TEXT(J4,"YYYY-MM")δημιουργούσα μια στήλη Μήνας από τις καθημερινές ημερομηνίες.
Εάν δεν έχετε το πλήρες πρόσθετο Power Pivot, πρέπει να δημιουργήσετε έναν συγκεντρωτικό πίνακα από τον πίνακα Προϋπολογισμός και να επιλέξετε το πλαίσιο ελέγχου για Προσθήκη αυτών των δεδομένων στο μοντέλο δεδομένων.
Όπως συζητήθηκε στην προηγούμενη συμβουλή, καθώς προσθέτετε πεδία στον συγκεντρωτικό πίνακα, θα πρέπει να ορίσετε έξι σχέσεις. Ενώ θα μπορούσατε να το κάνετε αυτό με έξι επισκέψεις στο διάλογο Δημιουργία σχέσης, ενεργοποίησα το πρόσθετο Power Pivot και χρησιμοποίησα την προβολή διαγράμματος για να καθορίσω τις έξι σχέσεις.
Εδώ είναι το κλειδί για να γίνει όλη αυτή η εργασία: Είστε ελεύθεροι να χρησιμοποιήσετε τα αριθμητικά πεδία από τον προϋπολογισμό και από την πραγματική. Αλλά αν θέλετε να εμφανίσετε την περιοχή, το προϊόν ή το μήνα στον συγκεντρωτικό πίνακα, πρέπει να προέρχονται από τους πίνακες ξυλουργών!
Εδώ είναι ένας συγκεντρωτικός πίνακας με δεδομένα που προέρχονται από πέντε πίνακες. Η στήλη Α προέρχεται από τον ξυλουργό περιοχής. Η σειρά 2 προέρχεται από το πρόγραμμα εγγραφής ημερολογίου. Το προϊόν Slicer προέρχεται από το Product joiner. Οι αριθμοί προϋπολογισμού προέρχονται από τον πίνακα προϋπολογισμού και οι πραγματικοί αριθμοί προέρχονται από τον πίνακα τιμολογίων.
Αυτό λειτουργεί επειδή οι πίνακες ξυλουργών εφαρμόζουν φίλτρα στον πίνακα προϋπολογισμού και πραγματικού. Είναι μια όμορφη τεχνική και δείχνει ότι το Power Pivot δεν είναι μόνο για μεγάλα δεδομένα.
