Σύνολο λειτουργίας (ονομάζεται επίσης αθροιστικό άθροισμα) χρησιμοποιείται αρκετά συχνά σε πολλές περιπτώσεις. Είναι μια μέτρηση που σας λέει ποιο είναι το άθροισμα των τιμών μέχρι τώρα.
Για παράδειγμα, εάν διαθέτετε τα μηνιαία δεδομένα πωλήσεων, τότε το τρέχον σύνολο θα σας πει πόσες πωλήσεις έχουν πραγματοποιηθεί μέχρι μια συγκεκριμένη ημέρα από την πρώτη ημέρα του μήνα.
Υπάρχουν επίσης κάποιες άλλες καταστάσεις στις οποίες χρησιμοποιείται συχνά το συνολικό τρέξιμο, όπως ο υπολογισμός του υπολοίπου των μετρητών σας στις τραπεζικές καταστάσεις/καθολικό σας, καταμέτρηση θερμίδων στο πρόγραμμα γευμάτων σας κ.λπ.
Στο Microsoft Excel, υπάρχουν πολλοί διαφορετικοί τρόποι υπολογισμού των συνόλων λειτουργίας.
Η μέθοδος που θα επιλέξετε θα εξαρτηθεί επίσης από τη δομή των δεδομένων σας.
Για παράδειγμα, εάν έχετε απλά πίνακα δεδομένων, μπορείτε να χρησιμοποιήσετε έναν απλό τύπο SUM, αλλά εάν έχετε έναν πίνακα Excel, τότε είναι καλύτερο να χρησιμοποιήσετε δομημένες αναφορές. Μπορείτε επίσης να χρησιμοποιήσετε το Power Query για να το κάνετε αυτό.
Σε αυτό το σεμινάριο, θα καλύψω όλες αυτές τις διαφορετικές μεθόδους υπολογίστε τα σύνολα που εκτελούνται στο Excel.
Ας ξεκινήσουμε λοιπόν!
Υπολογισμός του συνολικού τρεξίματος με δεδομένα πίνακα
Εάν έχετε δεδομένα πίνακα (δηλαδή, έναν πίνακα στο Excel που δεν μετατρέπεται σε πίνακα Excel), μπορείτε να χρησιμοποιήσετε μερικούς απλούς τύπους για να υπολογίσετε τα σύνολα που εκτελούνται.
Χρήση του τελεστή προσθήκης
Ας υποθέσουμε ότι διαθέτετε δεδομένα πωλήσεων βάσει ημερομηνίας και θέλετε να υπολογίσετε το τρέχον σύνολο στη στήλη Γ.
Παρακάτω είναι τα βήματα για να το κάνετε αυτό.
Βήμα 1 - Στο κελί C2, το οποίο είναι το πρώτο κελί όπου θέλετε να τρέχει το σύνολο, εισαγάγετε
= Β2
Αυτό θα έχει απλώς τις ίδιες τιμές πώλησης στο κελί 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)
- Β2 - αυτό είναι το δεύτερο μέρος της αναφοράς που είναι μια σχετική αναφορά, πράγμα που σημαίνει ότι αυτό θα προσαρμόζεται καθώς αντιγράφω τον τύπο προς τα κάτω ή προς τα δεξιά. Έτσι, κατά την αντιγραφή του τύπου στο παρακάτω κελί, αυτή η τιμή θα γίνει B3
Διαβάστε επίσης: Απόλυτες, σχετικές και μικτές αναφορές κυττάρων στο Excel
Το υπέροχο με αυτήν τη μέθοδο είναι ότι σε περίπτωση που διαγράψετε οποιαδήποτε από τις γραμμές στο σύνολο δεδομένων, αυτός ο τύπος θα προσαρμοστεί και θα σας δώσει τα σωστά σύνολα.
Υπολογισμός του συνόλου εκτέλεσης στον πίνακα Excel
Όταν εργάζεστε με δεδομένα πίνακα στο Excel, είναι καλή ιδέα να τα μετατρέψετε σε πίνακα Excel. Διευκολύνει πολύ τη διαχείριση των δεδομένων και επιτρέπει επίσης τη χρήση εργαλείων όπως το Power Query και το Power Pivot.
Η εργασία με τους πίνακες Excel συνοδεύεται από οφέλη όπως δομημένες αναφορές (πράγμα που καθιστά πολύ εύκολη την αναφορά στα δεδομένα του πίνακα και τη χρήση τους σε τύπους) και αυτόματη προσαρμογή των αναφορών σε περίπτωση που προσθέσετε ή διαγράψετε δεδομένα από τον πίνακα.
Ενώ μπορείτε ακόμα να χρησιμοποιήσετε τον παραπάνω τύπο που σας έχω δείξει σε έναν πίνακα Excel, επιτρέψτε μου να σας δείξω μερικές καλύτερες μεθόδους για να το κάνετε αυτό.
Ας υποθέσουμε ότι έχετε έναν πίνακα Excel όπως φαίνεται παρακάτω και θέλετε να υπολογίσετε το τρέχον σύνολο στη στήλη Γ.
Παρακάτω είναι ο τύπος που θα το κάνει αυτό:
= SUM (SalesData [[#Headers], [Sale]]: [@Sale])
Ο παραπάνω τύπος μπορεί να φαίνεται λίγο μακρύς, αλλά δεν χρειάζεται να το γράψετε μόνοι σας. αυτό που βλέπετε μέσα στον τύπο αθροίσματος ονομάζονται δομημένες αναφορές, ο οποίος είναι ο αποτελεσματικός τρόπος για την αναφορά του Excel σε συγκεκριμένα σημεία δεδομένων σε έναν πίνακα Excel.
Για παράδειγμα, το SalesData [[#Headers], [Sale]] αναφέρεται στην κεφαλίδα Sale στον πίνακα SalesData (SalesData είναι το όνομα του πίνακα Excel που έδωσα όταν δημιούργησα τον πίνακα)
Και το [@Sale] αναφέρεται στην τιμή στο κελί της ίδιας γραμμής στη στήλη Πώληση.
Απλώς το εξήγησα εδώ για την κατανόησή σας, αλλά ακόμα κι αν δεν γνωρίζετε τίποτα για δομημένες αναφορές, μπορείτε ακόμα εύκολα να δημιουργήσετε αυτόν τον τύπο.
Παρακάτω είναι τα βήματα για να το κάνετε αυτό:
- Στο κελί C2, πληκτρολογήστε = SUM (
- Επιλέξτε το κελί B1, το οποίο είναι η κεφαλίδα της στήλης που έχει την τιμή πώλησης. Μπορείτε να χρησιμοποιήσετε το ποντίκι ή τα πλήκτρα βέλους. Θα παρατηρήσετε ότι το Excel εισάγει αυτόματα τη δομημένη αναφορά για αυτό το κελί
- Προσθέστε ένα: (σύμβολο άνω και κάτω τελεία)
- Επιλέξτε το κελί B2. Το Excel θα εισάγει ξανά αυτόματα τη δομημένη αναφορά για το κελί
- Κλείστε την αγκύλη και πατήστε enter
Θα παρατηρήσετε επίσης ότι δεν χρειάζεται να αντιγράψετε τον τύπο σε ολόκληρη τη στήλη, ένας πίνακας Excel το κάνει αυτόματα για σας.
Ένα άλλο μεγάλο πράγμα για αυτήν τη μέθοδο είναι ότι σε περίπτωση που προσθέσετε μια νέα εγγραφή σε αυτό το σύνολο δεδομένων, ο πίνακας Excel θα υπολογίσει αυτόματα το συνολικό τρέξιμο για όλες τις νέες εγγραφές.
Ενώ έχουμε συμπεριλάβει την κεφαλίδα της στήλης στον τύπο μας, θυμηθείτε ότι ο τύπος θα αγνοήσει το κείμενο της κεφαλίδας και θα λάβει υπόψη μόνο τα δεδομένα στη στήλη
Υπολογισμός του συνολικού τρεξίματος χρησιμοποιώντας το ερώτημα ισχύος
Το Power Query είναι ένα καταπληκτικό εργαλείο όταν πρόκειται για σύνδεση με βάσεις δεδομένων, εξαγωγή δεδομένων από πολλαπλές πηγές και μετατροπή τους πριν τα τοποθετήσετε στο Excel.
Εάν εργάζεστε ήδη με το Power Query, θα ήταν πιο αποτελεσματικό να προσθέσετε σύνολα που εκτελούνται ενώ μεταμορφώνετε τα δεδομένα στον ίδιο τον επεξεργαστή Power Query (αντί να λαμβάνετε πρώτα τα δεδομένα στο Excel και στη συνέχεια να προσθέτετε τα τρέχοντα σύνολα χρησιμοποιώντας οποιοδήποτε από τα παραπάνω μεθόδους που αναφέρονται παραπάνω).
Παρόλο που δεν υπάρχει ενσωματωμένη λειτουργία στο Power Query για προσθήκη συνόλων λειτουργίας (που θα ήθελα να υπήρχε), μπορείτε ακόμα να το κάνετε χρησιμοποιώντας έναν απλό τύπο.
Ας υποθέσουμε ότι έχετε έναν πίνακα Excel όπως φαίνεται παρακάτω και θέλετε να προσθέσετε τα τρέχοντα σύνολα σε αυτά τα δεδομένα:
Παρακάτω είναι τα βήματα για να το κάνετε αυτό:
- Επιλέξτε οποιοδήποτε κελί στον πίνακα Excel
- Κάντε κλικ στο στοιχείο Δεδομένα
- Στην καρτέλα Λήψη & Μεταμόρφωση, κάντε κλικ στο εικονίδιο από τον πίνακα/Εύρος. Αυτό θα ανοίξει τον πίνακα στον επεξεργαστή Power Query
- [Προαιρετικό] Σε περίπτωση που η στήλη Ημερομηνία δεν έχει ταξινομηθεί ήδη, κάντε κλικ στο εικονίδιο φίλτρου στη στήλη Ημερομηνία και, στη συνέχεια, κάντε κλικ στην επιλογή Ταξινόμηση κατά αύξουσα κλίμακα
- Κάντε κλικ στην καρτέλα Προσθήκη στήλης στον επεξεργαστή Power Query
- Στην ομάδα Γενικά, κάντε κλικ στο αναπτυσσόμενο μενού Στήλη ευρετηρίου (μην κάνετε κλικ στο εικονίδιο Στήλη ευρετηρίου, αλλά στο μικρό μαύρο κεκλιμένο βέλος ακριβώς δίπλα για να εμφανιστούν περισσότερες επιλογές)
- Κάντε κλικ στην επιλογή «Από 1». Με αυτόν τον τρόπο θα προστεθεί μια νέα στήλη ευρετηρίου που θα ξεκινούσε από μία και θα εισάγει αριθμούς που θα αυξάνονται κατά 1 σε ολόκληρη τη στήλη
- Κάντε κλικ στο εικονίδιο «Προσαρμοσμένη στήλη» (το οποίο βρίσκεται επίσης στην καρτέλα Προσθήκη στήλης)
- Στο πλαίσιο διαλόγου προσαρμοσμένης στήλης που ανοίγει, εισαγάγετε ένα όνομα για τη νέα στήλη. σε αυτό το παράδειγμα, θα χρησιμοποιήσω το όνομα "Running Total"
- Στο πεδίο Τύπος προσαρμοσμένης στήλης, εισαγάγετε τον παρακάτω τύπο: List.Sum (List.Range (#”Προστέθηκε Ευρετήριο” [Πώληση], 0, [Ευρετήριο]))
- Βεβαιωθείτε ότι υπάρχει ένα πλαίσιο ελέγχου στο κάτω μέρος του παραθύρου διαλόγου που λέει - "Δεν εντοπίστηκαν σφάλματα σύνταξης"
- Κάντε κλικ στο OK. Αυτό θα προσθέσει μια νέα τρέχουσα συνολική στήλη
- Αφαιρέστε τη στήλη ευρετηρίου
- Κάντε κλικ στην καρτέλα Αρχείο και, στη συνέχεια, κάντε κλικ στο «Κλείσιμο και φόρτωση»
Τα παραπάνω βήματα θα εισάγουν ένα νέο φύλλο στο βιβλίο εργασίας σας με έναν πίνακα που έχει τα τρέχοντα σύνολα.
Τώρα αν σκέφτεστε ότι αυτά είναι πάρα πολλά βήματα σε σύγκριση με τις προηγούμενες μεθόδους χρήσης απλών τύπων, έχετε δίκιο.
Εάν έχετε ήδη ένα σύνολο δεδομένων και το μόνο που χρειάζεται να κάνετε είναι να προσθέσετε τρέχοντα σύνολα, είναι καλύτερα να μην χρησιμοποιήσετε το Power Query.
Η χρήση του Power Query έχει νόημα όταν πρέπει να εξαγάγετε δεδομένα από μια βάση δεδομένων ή να συνδυάσετε δεδομένα από πολλά διαφορετικά βιβλία εργασίας και στην πορεία να προσθέσετε επίσης σύνολα σε λειτουργία.
Επίσης, μόλις κάνετε αυτόν τον αυτοματισμό χρησιμοποιώντας το Power Query, την επόμενη φορά που αλλάζει το σύνολο δεδομένων σας δεν χρειάζεται να το επαναλάβετε, μπορείτε απλά να ανανεώσετε το ερώτημα και θα σας δώσει το αποτέλεσμα με βάση το νέο σύνολο δεδομένων.
Πως λειτουργεί αυτό?
Τώρα επιτρέψτε μου να εξηγήσω γρήγορα τι συμβαίνει σε αυτήν τη μέθοδο.
Το πρώτο πράγμα που κάνουμε στον επεξεργαστή Power Query είναι να εισαγάγουμε μια στήλη ευρετηρίου που ξεκινά από μία και αυξάνεται κατά μία καθώς κατεβαίνει τα κελιά.
Το κάνουμε αυτό επειδή πρέπει να χρησιμοποιήσουμε αυτήν τη στήλη ενώ υπολογίζουμε το συνολικό τρέξιμο σε μια άλλη στήλη που εισάγουμε στο επόμενο βήμα.
Στη συνέχεια, εισάγουμε μια προσαρμοσμένη στήλη και χρησιμοποιούμε τον παρακάτω τύπο
List.Sum (List.Range (#"Προστέθηκε Ευρετήριο" [Πώληση], 0, [Ευρετήριο]))
Αυτός είναι ένας τύπος List.Sum που θα σας δώσει το άθροισμα του εύρους που καθορίζεται σε αυτό.
Και αυτό το εύρος καθορίζεται χρησιμοποιώντας τη λειτουργία List.Range.
Η συνάρτηση List.Range δίνει το καθορισμένο εύρος στη στήλη πώλησης ως έξοδο και αυτό το εύρος αλλάζει με βάση την τιμή του ευρετηρίου. Για παράδειγμα, για την πρώτη εγγραφή, το εύρος θα ήταν απλώς η πρώτη τιμή πώλησης. Και καθώς κατεβαίνετε τα κελιά, αυτό το εύρος θα διευρυνθεί.
Έτσι, για το πρώτο κύτταρο. Λίστα. Το άθροισμα θα σας έδινε μόνο το άθροισμα της πρώτης αξίας πώλησης και για το δεύτερο κελί, θα σας έδινε το άθροισμα για τις δύο πρώτες τιμές πώλησης κ.ο.κ.
Ενώ αυτή η μέθοδος λειτουργεί καλά, γίνεται πολύ αργή με μεγάλα σύνολα δεδομένων - χιλιάδες σειρές. Εάν αντιμετωπίζετε ένα μεγάλο σύνολο δεδομένων και θέλετε να προσθέσετε σύνολα σε λειτουργία, ρίξτε μια ματιά σε αυτό το σεμινάριο που δείχνει άλλες μεθόδους που είναι πιο γρήγορες.Υπολογισμός συνολικού τρεξίματος βάσει κριτηρίων
Μέχρι στιγμής, έχουμε δει παραδείγματα όπου έχουμε υπολογίσει το τρέχον σύνολο για όλες τις τιμές σε μια στήλη.
Αλλά θα μπορούσαν να υπάρχουν περιπτώσεις όπου θέλετε να υπολογίσετε το τρέχον σύνολο για συγκεκριμένες εγγραφές.
Για παράδειγμα, παρακάτω έχω ένα σύνολο δεδομένων και θέλω να υπολογίσω το τρέχον σύνολο για εκτυπωτές και σαρωτές ξεχωριστά σε δύο διαφορετικές στήλες.
Αυτό μπορεί να γίνει χρησιμοποιώντας έναν τύπο 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 και στους Συγκεντρωτικούς Πίνακες.
Ελπίζω να βρήκατε χρήσιμο αυτό το σεμινάριο.
