συνολικό τρέξιμο (γνωστό και ωςσωρευτικό άθροισμα) χρησιμοποιείται πολύ συχνά σε πολλές περιπτώσεις.Αυτή είναι μια μέτρηση που σας λέει ποιο είναι το άθροισμα των μέχρι τώρα τιμών.
Για παράδειγμα, εάν έχετε δεδομένα μηνιαίων πωλήσεων, ένα τρέχον σύνολο θα σας δείξει πόσες πωλήσεις πραγματοποιήθηκαν από την πρώτη ημέρα του μήνα έως μια συγκεκριμένη ημερομηνία.
Υπάρχουν και άλλες περιπτώσεις όπου χρησιμοποιούνται συχνά σύνολα, όπως υπολογισμός υπολοίπων μετρητών σε τραπεζικές καταστάσεις/βιβλία, υπολογισμός θερμίδων σε προγράμματα γευμάτων κ.λπ.
Στο Microsoft Excel, υπάρχουν αρκετοί διαφορετικοί τρόποι υπολογισμού των συνόλων που εκτελούνται.
Η μέθοδος που επιλέγετε εξαρτάται επίσης από τη δομή των δεδομένων.
Για παράδειγμα, εάν έχετε απλά δεδομένα σε πίνακα, μπορείτε να χρησιμοποιήσετε έναν απλό τύπο SUM, αλλά εάν έχετε έναν πίνακα Excel, είναι καλύτερο να χρησιμοποιήσετε δομημένες αναφορές.Μπορείτε επίσης να χρησιμοποιήσετε το Power Query για να το κάνετε αυτό.
Σε αυτό το σεμινάριο, θα τα καλύψω όλαΥπολογίστε ένα τρέχον σύνολο στο Excelδιαφορετικές μεθόδους.
Ας ξεκινήσουμε λοιπόν!
Περιεχόμενα
Υπολογίστε τα τρέχοντα σύνολα χρησιμοποιώντας δεδομένα πίνακα
Εάν έχετε δεδομένα σε πίνακα (δηλαδή, πίνακες στο Excel που δεν μετατρέπονται σε πίνακες Excel), μπορείτε να χρησιμοποιήσετε μερικούς απλούς τύπους για να υπολογίσετε τα τρέχοντα σύνολα.
Χρησιμοποιήστε τον τελεστή προσθήκης
Ας υποθέσουμε ότι έχετε δεδομένα πωλήσεων ανά ημερομηνία και θέλετεσε στη στήλη ΓυπολογίζωΤρέξιμο συνολικά.
Εδώ είναι τα βήματα για να το κάνετε αυτό.
βήμα 1 – Στο κελί C2, το πρώτο κελί για το οποίο θέλετε να εκτελέσετε το σύνολο, εισάγετε
= B2
Αυτό λαμβάνει ακριβώς την ίδια τιμή πώλησης στο κελί B2.
βήμα 2– Στο κελί C3, εισαγάγετε τον ακόλουθο τύπο:
= C2 + B3
βήμα 3– Εφαρμόστε τον τύπο σε ολόκληρη τη στήλη.Μπορείτε να χρησιμοποιήσετε τη λαβή πλήρωσης για να το επιλέξετε και να το σύρετε ή απλώς να κάνετε αντιγραφή-επικόλληση του κελιού C3 σε όλα τα υπόλοιπα κελιά (αυτό θα προσαρμόσει αυτόματα την αναφορά και θα δώσει το σωστό αποτέλεσμα).
Αυτό θα σας δώσει το αποτέλεσμα που φαίνεται παρακάτω.
Αυτή είναι μια πολύ απλή μέθοδος που λειτουργεί καλά στις περισσότερες περιπτώσεις.
Η λογική είναι απλή - κάθε κελί παίρνει την τιμή σε αυτό (που είναι το σωρευτικό άθροισμα μέχρι την προηγούμενη ημέρα) και προσθέτει την τιμή στο διπλανό κελί (που είναι η τιμή πώλησης της τρέχουσας ημέρας).
Υπάρχει μόνο ένα μειονέκτημα - εάν διαγράψετε οποιαδήποτε υπάρχουσα σειρά σε αυτό το σύνολο δεδομένων, όλα τα παρακάτω κελιά θα επιστρέψουν ένα σφάλμα αναφοράς (#REF!)
Εάν το σύνολο δεδομένων σας έχει αυτήν τη δυνατότητα, χρησιμοποιήστε την επόμενη μέθοδο χρησιμοποιώντας τον τύπο SUM
Χρησιμοποιήστε το SUM με μερικώς κλειδωμένες αναφορές κελιών
Ας υποθέσουμε ότι έχετε δεδομένα πωλήσεων κατά ημερομηνία και θέλετε να υπολογίσετε ένα τρέχον σύνολο στη στήλη Γ.
Παρακάτω είναι ο τύπος SUM που θα σας δώσει ένα τρέχον σύνολο.
= SUM ($ B $ 2: B2)
Επιτρέψτε μου να εξηγήσω πώς λειτουργεί αυτός ο τύπος.
Στον παραπάνω τύπο SUM, χρησιμοποίησα την αναφορά για να προσθέσω ως $B$2:B2
- $B$2 - Αυτή είναι μια απόλυτη αναφορά, που σημαίνει ότι αυτή η αναφορά δεν αλλάζει όταν αντιγράφω τον ίδιο τύπο στο παρακάτω κελί.Έτσι, όταν αντιγράφετε τον τύπο στο παρακάτω κελί, ο τύπος θα αλλάξει σε SUM($B$2:B3)
- B2 - Αυτό είναι το δεύτερο μέρος της αναφοράς, είναι μια σχετική αναφορά, που σημαίνει ότι όταν αντιγράφω τον τύπο προς τα κάτω ή προς τα δεξιά, αυτό προσαρμόζεται.Έτσι, κατά την αντιγραφή του τύπου στο παρακάτω κελί, η τιμή γίνεται B3
Το ωραίο με αυτήν την προσέγγιση είναι ότι εάν διαγράψετε οποιεσδήποτε σειρές στο σύνολο δεδομένων, αυτός ο τύπος προσαρμόζεται και εξακολουθεί να σας δίνει το σωστό σύνολο εκτέλεσης.
Υπολογίστε το σύνολο εκτέλεσης στο φύλλο Excel
Όταν εργάζεστε με δεδομένα σε πίνακα στο Excel, είναι καλύτερο να τα μετατρέψετε σε πίνακα Excel.Διευκολύνει τη διαχείριση δεδομένων και επίσης διευκολύνει τη χρήση εργαλείων όπως το Power Query και το Power Pivot.
Η χρήση ενός πίνακα Excel παρέχει πολλά πλεονεκτήματα, όπως δομημένες αναφορές (που καθιστούν πολύ εύκολη την αναφορά δεδομένων στον πίνακα και τη χρήση τους σε τύπους) και αυτόματη προσαρμογή των αναφορών καθώς προσθέτετε ή αφαιρείτε δεδομένα από τον πίνακα.
Ενώ μπορείτε ακόμα να χρησιμοποιήσετε τον παραπάνω τύπο που σας έδειξα σε ένα φύλλο Excel, επιτρέψτε μου να σας δείξω μερικούς καλύτερους τρόπους για να το κάνετε.
Ας υποθέσουμε ότι έχετε έναν πίνακα Excel όπως ο παρακάτω και θέλετε να υπολογίσετε ένα τρέχον σύνολο στη στήλη Γ.
Εδώ είναι ο τύπος για να το κάνετε αυτό:
=SUM(SalesData[[#Headers],[Sale]]:[@Sale])
Η παραπάνω φόρμουλα μπορεί να φαίνεται λίγο μεγάλη, αλλά δεν χρειάζεται να τη γράψετε μόνοι σας.Αυτό που βλέπετε σε έναν τύπο άθροισης ονομάζεται δομημένη αναφορά και είναι ένας αποτελεσματικός τρόπος για το Excel να αναφέρεται σε συγκεκριμένα σημεία δεδομένων σε έναν πίνακα του Excel.
Για παράδειγμα, το SalesData[[#Headers],[Sale]] αναφέρεται στην κεφαλίδα Sales στον πίνακα SalesData (SalesData είναι το όνομα του πίνακα Excel που έδωσα όταν δημιούργησα τον πίνακα)
Ενώ το [@Sale] αναφέρεται στην τιμή σε ένα κελί στην ίδια γραμμή της στήλης Πώληση.
Απλώς το εξηγώ εδώ για να καταλάβετε, αλλά ακόμα κι αν δεν γνωρίζετε τίποτα για δομημένες αναφορές, μπορείτε να δημιουργήσετε εύκολα αυτόν τον τύπο.
Εδώ είναι τα βήματα για να το κάνετε αυτό:
- Στο κελί C2, πληκτρολογήστε =SUM(
- Επιλέξτε το κελί B1, το οποίο είναι η κεφαλίδα της στήλης με τιμή πώλησης.Μπορείτε να χρησιμοποιήσετε το ποντίκι ή να χρησιμοποιήσετε τα πλήκτρα βέλους.Θα παρατηρήσετε ότι το Excel εισάγει αυτόματα μια δομημένη αναφορά στο κελί
- Προσθήκη: (άνω τελεία)
- Επιλέξτε το κελί B2.Το Excel θα εισαγάγει ξανά αυτόματα δομημένες αναφορές σε κελιά
- Κλείστε την αγκύλη και πατήστε Enter
Θα παρατηρήσετε επίσης ότι δεν χρειάζεται να αντιγράψετε τον τύπο σε ολόκληρη τη στήλη, το φύλλο Excel το κάνει αυτόματα για εσάς.
Ένα άλλο πλεονέκτημα αυτής της προσέγγισης είναι ότι εάν προσθέσετε μια νέα εγγραφή σε αυτό το σύνολο δεδομένων, το φύλλο Excel υπολογίζει αυτόματα ένα τρέχον σύνολο για όλες τις νέες εγγραφές.
Παρόλο που συμπεριλάβαμε τις κεφαλίδες στηλών στον τύπο, έχετε υπόψη σας ότι ο τύπος αγνοεί το κείμενο της κεφαλίδας και λαμβάνει υπόψη μόνο τα δεδομένα στη στήλη
Υπολογίστε τα τρέχοντα σύνολα με το Power Query
Το Power Query είναι ένα εκπληκτικό εργαλείο όταν πρόκειται για τη σύνδεση σε βάσεις δεδομένων, την εξαγωγή δεδομένων από πολλές πηγές και τη μετατροπή τους προτού τα τοποθετήσετε στο Excel.
Εάν χρησιμοποιείτε ήδη το Power Query, είναι πιο αποτελεσματικό να προσθέτετε τρεχούμενα σύνολα κατά τη μετατροπή των δεδομένων στο ίδιο το Power Query Editor (αντί να λαμβάνετε πρώτα τα δεδομένα στο Excel και στη συνέχεια να προσθέτετε τρέχοντα σύνολα χρησιμοποιώντας οποιαδήποτε από τις παραπάνω μεθόδους).
Αν και δεν υπάρχει ενσωματωμένη λειτουργία στο Power Query για την προσθήκη συνόλων λειτουργίας (μακάρι να υπήρχε), μπορείτε να το κάνετε με έναν απλό τύπο.
Ας υποθέσουμε ότι έχετε έναν πίνακα του Excel που μοιάζει με αυτό και θέλετε να προσθέσετε τρεχούμενα σύνολα σε αυτά τα δεδομένα:
Εδώ είναι τα βήματα για να το κάνετε αυτό:
- Επιλέξτε οποιοδήποτε κελί σε έναν πίνακα του Excel
- δεδομένα κλικ
- Στην καρτέλα Λήψη & Μεταμόρφωση, κάντε κλικ στο εικονίδιο από Πίνακας/Εύρος.Αυτό θα ανοίξει τον πίνακα στο Power Query Editor
- [Προαιρετικό] Εάν η στήλη ημερομηνίας δεν είναι ήδη ταξινομημένη, κάντε κλικ στο εικονίδιο φίλτρου στη στήλη ημερομηνίας και, στη συνέχεια, κάντε κλικ στην επιλογή Αύξουσα ταξινόμηση
- Κάντε κλικ στην καρτέλα Προσθήκη στήλης στο πρόγραμμα επεξεργασίας Power Query
- Στην ομάδα Γενικά, κάντε κλικ στο αναπτυσσόμενο μενού Στήλη ευρετηρίου (μην κάνετε κλικ στο εικονίδιο Στήλη ευρετηρίου, αλλά στο μικρό μαύρο λοξό βέλος δίπλα του για να αποκαλύψετε περισσότερες επιλογές)
- Κάντε κλικ στην επιλογή "Από 1".Με αυτόν τον τρόπο θα προστεθεί μια νέα στήλη ευρετηρίου που θα ξεκινά από το 1 και θα εισάγει αριθμούς αυξημένους κατά 1 σε όλη τη στήλη
- Κάντε κλικ στο εικονίδιο Προσαρμοσμένες στήλες (επίσης στην καρτέλα Προσθήκη στηλών)
- Στο παράθυρο διαλόγου Προσαρμοσμένες στήλες που ανοίγει, εισαγάγετε ένα όνομα για τη νέα στήλη.Σε αυτό το παράδειγμα θα χρησιμοποιήσω το όνομα "Running Total"
- Στο πεδίο Τύπος προσαρμοσμένης στήλης, εισαγάγετε τον ακόλουθο τύπο:List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
- Βεβαιωθείτε ότι υπάρχει ένα πλαίσιο ελέγχου στο κάτω μέρος του διαλόγου που λέει "Δεν εντοπίστηκαν συντακτικά σφάλματα"
- Κάντε κλικ στο OK.Αυτό θα προσθέσει μια νέα τρέχουσα συνολική στήλη
- απόθεση στήλης ευρετηρίου
- Κάντε κλικ στην καρτέλα Αρχείο και, στη συνέχεια, κάντε κλικ στην επιλογή Κλείσιμο και φόρτωση
Τα παραπάνω βήματα θα εισαγάγουν ένα νέο φύλλο εργασίας στο βιβλίο εργασίας σας με έναν πίνακα με τρέχοντα σύνολα.
Τώρα, αν νομίζετε ότι αυτά είναι πάρα πολλά βήματα σε σύγκριση με την προηγούμενη προσέγγιση με απλούς τύπους, έχετε δίκιο.
Εάν έχετε ήδη ένα σύνολο δεδομένων και το μόνο που χρειάζεται να κάνετε είναι να προσθέσετε τρέχοντα σύνολα, τότε είναι καλύτερο να μην χρησιμοποιήσετε το Power Query.
Η χρήση του Power Query έχει νόημα σε περιπτώσεις όπου πρέπει να εξαγάγετε δεδομένα από μια βάση δεδομένων ή να συνδυάσετε δεδομένα από πολλά διαφορετικά βιβλία εργασίας και επίσης να προσθέσετε σύνολα εκτέλεσης σε αυτά στη διαδικασία.
Επίσης, μόλις το αυτοματοποιήσετε με το Power Query, την επόμενη φορά που θα αλλάξει το σύνολο δεδομένων σας, δεν χρειάζεται να το κάνετε ξανά, μπορείτε απλώς να ανανεώσετε το ερώτημα και θα σας δώσει αποτελέσματα με βάση το νέο σύνολο δεδομένων.
Πως λειτουργεί αυτό?
Τώρα επιτρέψτε μου να εξηγήσω γρήγορα τι συμβαίνει με αυτήν την προσέγγιση.
Το πρώτο πράγμα που κάνουμε στο Power Query Editor είναι να εισάγουμε μια στήλη ευρετηρίου που ξεκινά από το 1 και αυξάνεται καθώς κατεβαίνει στο κελί.
Το κάνουμε αυτό επειδή πρέπει να χρησιμοποιήσουμε αυτήν τη στήλη κατά τον υπολογισμό του συνόλου που εκτελείται σε μια άλλη στήλη που εισάγεται στο επόμενο βήμα.
Στη συνέχεια εισάγουμε μια προσαρμοσμένη στήλη και χρησιμοποιούμε τον ακόλουθο τύπο
List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
Αυτός είναι ένας τύπος List.Sum που θα σας δώσει το άθροισμα του εύρους που καθορίζεται σε αυτόν.
Το εύρος καθορίζεται χρησιμοποιώντας τη συνάρτηση List.Range.
Η συνάρτηση List.Range λαμβάνει ως έξοδο το καθορισμένο εύρος στη στήλη πώλησης και το εύρος ποικίλλει ανάλογα με την τιμή Index.Για παράδειγμα, για την πρώτη εγγραφή, το εύρος είναι η πρώτη τιμή πώλησης.Αυτό το εύρος επεκτείνεται καθώς κατεβαίνετε στο κελί.
Έτσι, για το πρώτο κελί.Το List.Sum θα σας δώσει μόνο το άθροισμα της πρώτης τιμής πώλησης, για το δεύτερο κελί θα σας δώσει το άθροισμα των δύο πρώτων τιμών πώλησης και ούτω καθεξής.
Αν και αυτή η προσέγγιση λειτουργεί καλά, μπορεί να γίνει πολύ αργή για μεγάλα σύνολα δεδομένων (χιλιάδες σειρές).
Υπολογίστε τα τρέχοντα σύνολα με βάση κριτήρια
Μέχρι στιγμής, έχουμε δει παραδείγματα υπολογισμού ενός τρέχοντος συνόλου όλων των τιμών σε μια στήλη.
Αλλά σε ορισμένες περιπτώσεις, μπορεί να θέλετε να υπολογίσετε ένα τρέχον σύνολο για μια συγκεκριμένη εγγραφή.
Για παράδειγμα, παρακάτω έχω ένα σύνολο δεδομένων όπου θέλω να μετρήσω το τρέχον σύνολο εκτυπωτών και σαρωτών σε δύο διαφορετικές στήλες.
Αυτό μπορεί να γίνει χρησιμοποιώντας τον τύπο SUMIF, ο οποίος υπολογίζει ένα τρέχον σύνολο διασφαλίζοντας ταυτόχρονα ότι πληρούνται οι καθορισμένες προϋποθέσεις.
Ακολουθεί ο τύπος για να γίνει αυτό για τη στήλη του εκτυπωτή:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Ομοίως, για να υπολογίσετε το σύνολο λειτουργίας του σαρωτή, χρησιμοποιήστε τον ακόλουθο τύπο:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Στον παραπάνω τύπο, έχω χρησιμοποιήσει SUMIF που θα μου δώσει ένα άθροισμα σε ένα εύρος όταν πληρούνται συγκεκριμένες προϋποθέσεις.
Ο τύπος παίρνει τρεις παραμέτρους:
- σειρά: Αυτό είναι το εύρος των συνθηκών που θα ελεγχθούν σε σχέση με την καθορισμένη συνθήκη
- κριτήρια: αυτό είναι το κριτήριο που θα ελεγχθεί μόνο εάν πληρούται αυτό το κριτήριο, τότε θα προστεθεί η τιμή στην τρίτη παράμετρο, το εύρος αθροίσματος
- [εύρος_αθροίσματος]: αυτό είναι το εύρος του αθροίσματος των τιμών που πρέπει να προστεθούν όταν πληρούται η συνθήκη
Επιπλέον, στοσειρά和εύρος αθροίσματοςπαράμετρο, έχω κλειδώσει το δεύτερο μέρος της αναφοράς, έτσι ώστε καθώς μετακινούμαστε προς τα κάτω στο κελί, το εύρος συνεχίζει να επεκτείνεται.Αυτό μας επιτρέπει να λαμβάνουμε υπόψη και να προσθέτουμε τιμές μόνο σε αυτό το εύρος (άρα ένα τρέχον σύνολο).
Σε αυτόν τον τύπο, χρησιμοποιώ τις στήλες κεφαλίδας (Εκτυπωτές και Σαρωτές) ως κριτήρια.Μπορείτε επίσης να κάνετε σκληρό κώδικα στο πρότυπο εάν οι επικεφαλίδες των στηλών σας δεν είναι ακριβώς οι ίδιες με το τυπικό κείμενο.
Εάν χρειάζεται να ελέγξετε πολλές συνθήκες, μπορείτε να χρησιμοποιήσετε τον τύπο SUMIFS.
Εκτελούμενα σύνολα σε έναν συγκεντρωτικό πίνακα
Εάν θέλετε να προσθέσετε ένα τρέχον σύνολο στα αποτελέσματα του Συγκεντρωτικού Πίνακα, μπορείτε να το κάνετε εύκολα χρησιμοποιώντας την ενσωματωμένη λειτουργία στους Συγκεντρωτικούς Πίνακες.
Ας υποθέσουμε ότι έχετε έναν συγκεντρωτικό πίνακα όπως ο παρακάτω με ημερομηνίες σε μια στήλη και τιμές πωλήσεων σε μια άλλη.
Ακολουθούν τα βήματα για να προσθέσετε μια επιπλέον στήλη που θα εμφανίζει τις σωρευτικές πωλήσεις κατά ημερομηνία:
- Σύρετε το πεδίο Εκπτώσεις και αποθέστε το στην περιοχή Τιμή.
- Αυτό θα προσθέσει μια άλλη στήλη με την αξία πώλησης
- Κάντε κλικ στην επιλογή Συνολικές πωλήσεις 2 στην περιοχή Αξία
- Κάντε κλικ στην επιλογή Ρυθμίσεις πεδίου τιμών
- Στο παράθυρο διαλόγου Ρυθμίσεις πεδίου τιμής, αλλάξτε το Προσαρμοσμένο όνομα σε Εκτελούμενο σύνολο
- Κάντε κλικ στην καρτέλα "Εμφάνιση τιμής ως".
- Στο αναπτυσσόμενο μενού Εμφάνιση τιμής ως, επιλέξτε την επιλογή "Συνολική εκτέλεση".
- Στις επιλογές Βασικά πεδία, βεβαιωθείτε ότι είναι επιλεγμένη Ημερομηνία
- Κάντε κλικ στο OK
Τα παραπάνω βήματα θα αλλάξουν τη δεύτερη στήλη πωλήσεων σε στήλη "τρέχον σύνολο".
Έτσι, αυτές είναι μερικές από τις μεθόδους που μπορείτε να χρησιμοποιήσετε για να υπολογίσετε τα τρέχοντα σύνολα στο Excel.Εάν έχετε δεδομένα σε πίνακα, μπορείτε να χρησιμοποιήσετε απλούς τύπους και εάν έχετε πίνακες Excel, μπορείτε να χρησιμοποιήσετε τύπους που χρησιμοποιούν δομημένες αναφορές.
Κάλυψα επίσης τον τρόπο υπολογισμού των συνόλων που εκτελούνται χρησιμοποιώντας το Power Query και τους Συγκεντρωτικούς Πίνακες.
Ελπίζω να σας φάνηκε χρήσιμο αυτό το σεμινάριο.
