Περιεχόμενα
Μέθοδος 1. Φόρμουλες
Ας ξεκινήσουμε, για προθέρμανση, με την απλούστερη επιλογή – τις φόρμουλες. Εάν έχουμε έναν μικρό πίνακα ταξινομημένο κατά ημερομηνία ως είσοδο, τότε για να υπολογίσουμε το τρέχον σύνολο σε ξεχωριστή στήλη, χρειαζόμαστε έναν στοιχειώδη τύπο:
Το κύριο χαρακτηριστικό εδώ είναι η δύσκολη στερέωση του εύρους εντός της συνάρτησης SUM – η αναφορά στην αρχή του εύρους γίνεται απόλυτη (με τα σύμβολα του δολαρίου) και στο τέλος – σχετική (χωρίς δολάρια). Αντίστοιχα, κατά την αντιγραφή του τύπου σε ολόκληρη τη στήλη, παίρνουμε ένα διευρυνόμενο εύρος, το άθροισμα του οποίου υπολογίζουμε.
Τα μειονεκτήματα αυτής της προσέγγισης είναι προφανή:
- Ο πίνακας πρέπει να ταξινομηθεί κατά ημερομηνία.
- Όταν προσθέτετε νέες σειρές με δεδομένα, ο τύπος θα πρέπει να επεκταθεί με μη αυτόματο τρόπο.
Μέθοδος 2. Συγκεντρωτικός πίνακας
Αυτή η μέθοδος είναι λίγο πιο περίπλοκη, αλλά πολύ πιο ευχάριστη. Και για να το επιδεινώσουμε, ας εξετάσουμε ένα πιο σοβαρό πρόβλημα – έναν πίνακα 2000 σειρών δεδομένων, όπου δεν υπάρχει ταξινόμηση με βάση τη στήλη ημερομηνία, αλλά υπάρχουν επαναλήψεις (δηλαδή μπορούμε να πουλήσουμε πολλές φορές την ίδια μέρα):
Μετατρέπουμε τον αρχικό μας πίνακα σε μια «έξυπνη» (δυναμική) συντόμευση πληκτρολογίου Ctrl+T ή ομάδα Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα), και μετά χτίζουμε πάνω του έναν πίνακα περιστροφής με την εντολή Εισαγωγή – Συγκεντρωτικός Πίνακας (Εισαγωγή — Συγκεντρωτικός πίνακας). Βάζουμε την ημερομηνία στην περιοχή σειρών στη σύνοψη και τον αριθμό των προϊόντων που πωλήθηκαν στην περιοχή τιμών:
Λάβετε υπόψη ότι εάν έχετε μια όχι αρκετά παλιά έκδοση του Excel, τότε οι ημερομηνίες ομαδοποιούνται αυτόματα κατά έτη, τρίμηνα και μήνες. Εάν χρειάζεστε διαφορετική ομαδοποίηση (ή δεν τη χρειάζεστε καθόλου), τότε μπορείτε να τη διορθώσετε κάνοντας δεξί κλικ σε οποιαδήποτε ημερομηνία και επιλέγοντας εντολές Ομάδα / Κατάργηση ομαδοποίησης (Ομάδα / Κατάργηση ομαδοποίησης).
Εάν θέλετε να δείτε τόσο τα προκύπτοντα σύνολα ανά περιόδους όσο και το τρέχον σύνολο σε ξεχωριστή στήλη, τότε είναι λογικό να ρίξετε το πεδίο στην περιοχή τιμών Πωληθεί και πάλι για να λάβετε ένα αντίγραφο του πεδίου - σε αυτό θα ενεργοποιήσουμε την εμφάνιση των τρεχόντων συνόλων. Για να το κάνετε αυτό, κάντε δεξί κλικ στο πεδίο και επιλέξτε την εντολή Πρόσθετοι Υπολογισμοί – Αθροιστικό σύνολο (Εμφάνιση τιμών ως — Εκτελούμενα σύνολα):
Εκεί μπορείτε επίσης να επιλέξετε την επιλογή αύξησης των συνόλων ως ποσοστό και στο επόμενο παράθυρο πρέπει να επιλέξετε το πεδίο στο οποίο θα πάει η συσσώρευση - στην περίπτωσή μας, αυτό είναι το πεδίο ημερομηνίας:
Τα πλεονεκτήματα αυτής της προσέγγισης:
- Ένας μεγάλος όγκος δεδομένων διαβάζεται γρήγορα.
- Δεν απαιτείται η μη αυτόματη εισαγωγή τύπων.
- Κατά την αλλαγή των δεδομένων προέλευσης, αρκεί να ενημερώσετε τη σύνοψη με το δεξί κουμπί του ποντικιού ή με την εντολή Data – Refresh All.
Τα μειονεκτήματα προκύπτουν από το γεγονός ότι πρόκειται για μια σύνοψη, που σημαίνει ότι δεν μπορείτε να κάνετε ό,τι θέλετε σε αυτήν (εισάγετε γραμμές, γράφετε τύπους, δημιουργείτε διαγράμματα κ.λπ.) δεν θα λειτουργεί πλέον.
Μέθοδος 3: Power Query
Ας φορτώσουμε τον "έξυπνο" μας πίνακα με δεδομένα πηγής στο πρόγραμμα επεξεργασίας ερωτημάτων Power Query χρησιμοποιώντας την εντολή Δεδομένα – Από πίνακα/Εύρος (Δεδομένα — Από πίνακα/εύρος). Στις τελευταίες εκδόσεις του Excel, παρεμπιπτόντως, μετονομάστηκε - τώρα ονομάζεται Με φύλλα (Από Φύλλο):
Στη συνέχεια θα εκτελέσουμε τα παρακάτω βήματα:
1. Ταξινομήστε τον πίνακα σε αύξουσα σειρά κατά τη στήλη ημερομηνία με την εντολή Αύξουσα ταξινόμηση στην αναπτυσσόμενη λίστα φίλτρων στην κεφαλίδα του πίνακα.
2. Λίγο αργότερα, για να υπολογίσουμε το τρέχον σύνολο, χρειαζόμαστε μια βοηθητική στήλη με τον αριθμό της σειράς. Ας το προσθέσουμε με την εντολή Προσθήκη στήλης – Στήλη ευρετηρίου – Από 1 (Προσθήκη στήλης — Στήλη ευρετηρίου — Από 1).
3. Επίσης, για να υπολογίσουμε το τρέχον σύνολο, χρειαζόμαστε μια αναφορά στη στήλη Πωληθεί, όπου βρίσκονται τα συνοπτικά δεδομένα μας. Στο Power Query, οι στήλες ονομάζονται επίσης λίστες (λίστα) και για να λάβετε έναν σύνδεσμο προς αυτήν, κάντε δεξί κλικ στην κεφαλίδα της στήλης και επιλέξτε την εντολή Λεπτομέρεια (Δείξε λεπτομέρεια). Η έκφραση που χρειαζόμαστε θα εμφανιστεί στη γραμμή τύπων, που αποτελείται από το όνομα του προηγούμενου βήματος #"Προστέθηκε ευρετήριο", από όπου παίρνουμε τον πίνακα και το όνομα της στήλης [Εκπτώσεις] από αυτόν τον πίνακα σε αγκύλες:
Αντιγράψτε αυτήν την έκφραση στο πρόχειρο για περαιτέρω χρήση.
4. Διαγράψτε το περιττό πιο τελευταίο βήμα Πωληθεί και αντ' αυτού προσθέστε μια υπολογιζόμενη στήλη για τον υπολογισμό του συνόλου που εκτελείται με την εντολή Προσθήκη στήλης – Προσαρμοσμένη στήλη (Προσθήκη στήλης — Προσαρμοσμένη στήλη). Ο τύπος που χρειαζόμαστε θα μοιάζει με αυτό:
Εδώ η συνάρτηση Λίστα.Εύρος παίρνει την αρχική λίστα (στήλη [Εκπτώσεις]) και εξάγει στοιχεία από αυτό, ξεκινώντας από το πρώτο (στον τύπο, αυτό είναι 0, αφού η αρίθμηση στο Power Query ξεκινά από το μηδέν). Ο αριθμός των στοιχείων προς ανάκτηση είναι ο αριθμός σειράς που παίρνουμε από τη στήλη [Δείκτης]. Έτσι, αυτή η συνάρτηση για την πρώτη σειρά επιστρέφει μόνο ένα πρώτο κελί της στήλης Πωληθεί. Για τη δεύτερη γραμμή - ήδη τα δύο πρώτα κελιά, για την τρίτη - τα τρία πρώτα κ.λπ.
Λοιπόν, τότε η συνάρτηση Λίστα.Άθροισμα αθροίζει τις εξαγόμενες τιμές και παίρνουμε σε κάθε σειρά το άθροισμα όλων των προηγούμενων στοιχείων, δηλαδή το αθροιστικό σύνολο:
Απομένει να διαγράψουμε τη στήλη Ευρετήριο που δεν χρειαζόμαστε πλέον και να ανεβάσουμε τα αποτελέσματα πίσω στο Excel με την εντολή Home – Close & Load.
Το πρόβλημα λύθηκε.
Γρήγορα και εξαγριωμένα
Κατ 'αρχήν, αυτό θα μπορούσε να είχε σταματήσει, αλλά υπάρχει μια μικρή μύγα στην αλοιφή - το αίτημα που δημιουργήσαμε λειτουργεί με την ταχύτητα μιας χελώνας. Για παράδειγμα, στον υπολογιστή μου που δεν είναι ο πιο αδύναμος, ένας πίνακας μόνο 2000 σειρών επεξεργάζεται σε 17 δευτερόλεπτα. Τι γίνεται αν υπάρχουν περισσότερα δεδομένα;
Για να επιταχύνετε, μπορείτε να χρησιμοποιήσετε την προσωρινή αποθήκευση χρησιμοποιώντας την ειδική συνάρτηση List.Buffer, η οποία φορτώνει τη λίστα (λίστα) που της δίνεται ως όρισμα στη μνήμη RAM, η οποία επιταχύνει σημαντικά την πρόσβαση σε αυτήν στο μέλλον. Στην περίπτωσή μας, είναι λογικό να αποθηκεύουμε προσωρινά τη λίστα #"Προστέθηκε ευρετήριο"[Sold], στην οποία πρέπει να έχει πρόσβαση το Power Query κατά τον υπολογισμό του τρέχοντος συνόλου σε κάθε γραμμή του πίνακα 2000 σειρών μας.
Για να το κάνετε αυτό, στο πρόγραμμα επεξεργασίας Power Query στην καρτέλα Main, κάντε κλικ στο κουμπί Advanced Editor (Αρχική σελίδα – Advanced Editor) για να ανοίξετε τον πηγαίο κώδικα του ερώτημά μας στη γλώσσα M που είναι ενσωματωμένη στο Power Query:
Στη συνέχεια, προσθέστε μια γραμμή με μια μεταβλητή εκεί Η λίστα μου, η τιμή της οποίας επιστρέφεται από τη συνάρτηση buffering και στο επόμενο βήμα αντικαθιστούμε την κλήση στη λίστα με αυτήν τη μεταβλητή:
Αφού κάνουμε αυτές τις αλλαγές, το ερώτημά μας θα γίνει σημαντικά πιο γρήγορο και θα αντιμετωπίσει έναν πίνακα 2000 σειρών σε μόλις 0.3 δευτερόλεπτα!
Άλλο πράγμα, σωστά; 🙂
- Διάγραμμα Pareto (80/20) και πώς να το δημιουργήσετε στο Excel
- Αναζήτηση λέξεων-κλειδιών σε κείμενο και αποθήκευση ερωτημάτων στο Power Query