Χρησιμοποιήστε τα SUM και OFFSET για να βρείτε σύνολα για δυναμικά εύρη δεδομένων
Εάν το φύλλο εργασίας του Excel περιλαμβάνει υπολογισμούς που βασίζονται σε μια μεταβαλλόμενη περιοχή κυττάρων, η χρήση των λειτουργιών SUM και OFFSET μαζί σε έναν τύπο SUM OFFSET απλοποιεί την εργασία διατήρησης των υπολογισμών μέχρι σήμερα.
Δημιουργήστε μια δυναμική περιοχή με τις λειτουργίες SUM και OFFSET
Αν χρησιμοποιείτε υπολογισμούς για μια χρονική περίοδο που αλλάζει συνεχώς - όπως οι συνολικές πωλήσεις για το μήνα - η λειτουργία OFFSET σας επιτρέπει να ρυθμίσετε ένα δυναμικό εύρος που συνεχίζει να αλλάζει καθώς προστίθενται αριθμοί πωλήσεων κάθε ημέρας.
Από μόνη της, η συνάρτηση SUM μπορεί συνήθως να φιλοξενήσει νέα κελιά δεδομένων που εισάγονται στην περιοχή που αθροίζεται.
Μια εξαίρεση προκύπτει όταν τα δεδομένα εισάγονται στο κελί όπου βρίσκεται αυτή τη λειτουργία.
Στην εικόνα του παραδείγματος που συνοδεύει αυτό το άρθρο, τα νέα στοιχεία πωλήσεων για κάθε ημέρα προστίθενται στο κάτω μέρος της λίστας, γεγονός που αναγκάζει το σύνολο να μετατοπίζει συνεχώς ένα κελί κάθε φορά που προστίθενται τα νέα δεδομένα.
Εάν η συνάρτηση SUM χρησιμοποιήθηκε μόνη της για τη συμπλήρωση των δεδομένων, θα ήταν απαραίτητο να τροποποιηθεί το εύρος των κυττάρων που χρησιμοποιήθηκε ως όρισμα της λειτουργίας κάθε φορά που προστέθηκαν νέα δεδομένα.
Χρησιμοποιώντας ταυτόχρονα τις λειτουργίες SUM και OFFSET, η περιοχή που ολοκληρώνεται γίνεται δυναμική. Με άλλα λόγια, αλλάζει για να φιλοξενήσει νέα στοιχεία δεδομένων. Η προσθήκη νέων κελιών δεδομένων δεν προκαλεί προβλήματα επειδή το εύρος συνεχίζει να προσαρμόζεται καθώς προστίθεται κάθε νέο κελί.
Σύνταξη και επιχειρήματα
Ανατρέξτε στην εικόνα που συνοδεύει αυτό το άρθρο για να ακολουθήσετε μαζί με αυτό το σεμινάριο.
Στον τύπο αυτό, η συνάρτηση SUM χρησιμοποιείται για να ομαδοποιήσει το εύρος των δεδομένων που παρέχονται ως επιχείρησή της. Το σημείο εκκίνησης για αυτό το εύρος είναι στατικό και αναγνωρίζεται ως η αναφορά κυψελών στον πρώτο αριθμό που θα συμπληρωθεί από τον τύπο.
Η συνάρτηση OFFSET είναι ένθετη μέσα στη συνάρτηση SUM και χρησιμοποιείται για τη δημιουργία ενός δυναμικού τελικού σημείου στην περιοχή των δεδομένων που συνθέτουν ο τύπος. Αυτό επιτυγχάνεται θέτοντας το τελικό σημείο της περιοχής σε μία κυψέλη πάνω από την τοποθεσία του τύπου.
Η σύνταξη του τύπου:
= SUM (Εκκίνηση εύρους: OFFSET (Αναφορά, Γραμμές, Πινακίδες))Εύρος εκκίνησης - (απαιτείται) το σημείο εκκίνησης για το εύρος κυψελών που θα συμπληρωθεί από τη λειτουργία SUM. Στην εικόνα του παραδείγματος, αυτό είναι το κελί B2.
Αναφορά - (απαιτείται) η αναφορά κυψέλης που χρησιμοποιείται για τον υπολογισμό του τελικού σημείου της περιοχής, που βρίσκεται μακριά από πολλές σειρές και στήλες. Στην εικόνα παραδειγμάτων, το όρισμα αναφοράς είναι η αναφορά κυττάρων για τον ίδιο τον τύπο δεδομένου ότι πάντα θέλουμε το εύρος να τελειώσει ένα κύτταρο πάνω από τον τύπο.
Γραμμές - (απαιτείται) ο αριθμός των γραμμών πάνω ή κάτω από το όρισμα αναφοράς που χρησιμοποιείται για τον υπολογισμό της μετατόπισης. Αυτή η τιμή μπορεί να είναι θετική, αρνητική ή μηδενική.
Εάν η θέση της μετατόπισης βρίσκεται πάνω από το όρισμα αναφοράς , αυτή η τιμή είναι αρνητική. Αν είναι κάτω, το παράγωγο Rows είναι θετικό. Εάν η μετατόπιση βρίσκεται στην ίδια σειρά, αυτό το όρισμα είναι μηδέν. Σε αυτό το παράδειγμα, η μετατόπιση ξεκινά μία γραμμή πάνω από το όρισμα αναφοράς , οπότε η τιμή για αυτό το όρισμα είναι αρνητική (-1).
Cols - (απαιτείται) ο αριθμός των στηλών αριστερά ή δεξιά του επιχειρήματος αναφοράς που χρησιμοποιείται για τον υπολογισμό της μετατόπισης. Αυτή η τιμή μπορεί να είναι θετική, αρνητική ή μηδενική
Εάν η θέση της μετατόπισης βρίσκεται στα αριστερά του παραθύρου αναφοράς , αυτή η τιμή είναι αρνητική. Αν προς τα δεξιά, το επιχείρημα Cols είναι θετικό. Σε αυτό το παράδειγμα, τα συνολικά δεδομένα είναι στην ίδια στήλη με τον τύπο, οπότε η τιμή για αυτό το όρισμα είναι μηδέν.
Χρησιμοποιώντας το SUM OFFSET Formula σε συνολικά δεδομένα πωλήσεων
Αυτό το παράδειγμα χρησιμοποιεί έναν τύπο SUM OFFSET για να επιστρέψει το σύνολο για τα στοιχεία ημερήσιας πώλησης που αναφέρονται στη στήλη Β του φύλλου εργασίας.
Αρχικά, ο τύπος εισήχθη στο κελί B6 και ανήλθε στα στοιχεία πωλήσεων για τέσσερις ημέρες.
Το επόμενο βήμα είναι να μετακινήσετε τον τύπο SUM OFFSET κάτω από μια σειρά για να κάνετε χώρο για τις συνολικές πωλήσεις της πέμπτης ημέρας.
Αυτό επιτυγχάνεται με την εισαγωγή μιας νέας γραμμής 6, η οποία μετακινεί τον τύπο στη σειρά 7.
Ως αποτέλεσμα της μετακίνησης, το Excel ενημερώνει αυτόματα το όρισμα αναφοράς στο κελί B7 και προσθέτει το κελί B6 στην περιοχή που συνοψίζεται από τον τύπο.
Εισαγωγή του τύπου SUM OFFSET
- Κάντε κλικ στο κελί B6, που είναι η θέση όπου αρχικά θα εμφανιστούν τα αποτελέσματα του μαθηματικού τύπου.
- Κάντε κλικ στην καρτέλα Φόρμες του μενού κορδέλας .
- Επιλέξτε Math & Trig από την κορδέλα για να ανοίξετε την αναπτυσσόμενη λίστα λειτουργιών.
- Κάντε κλικ στο SUM στη λίστα για να εμφανιστεί το παράθυρο διαλόγου της λειτουργίας.
- Στο παράθυρο διαλόγου, κάντε κλικ στη γραμμή Αριθμός1 .
- Κάντε κλικ στο κελί B2 για να εισαγάγετε αυτήν την αναφορά κυττάρων στο παράθυρο διαλόγου. Αυτή η θέση είναι το στατικό τελικό σημείο για τον τύπο.
- Στο παράθυρο διαλόγου, κάντε κλικ στη γραμμή Αριθμός2 .
- Εισάγετε την ακόλουθη λειτουργία OFFSET: OFFSET (B6, -1,0) για να διαμορφώσετε το δυναμικό τελικό σημείο για τον τύπο.
- Κάντε κλικ στο κουμπί OK για να ολοκληρώσετε τη λειτουργία και να κλείσετε το παράθυρο διαλόγου.
Το συνολικό ποσό των 5679,15 δολαρίων εμφανίζεται στο κελί B7.
Όταν κάνετε κλικ στο κελί B3, η πλήρης λειτουργία = SUM (B2: OFFSET (B6, -1,0)) εμφανίζεται στη γραμμή τύπων πάνω από το φύλλο εργασίας.
Προσθήκη δεδομένων πωλήσεων της επόμενης ημέρας
Για να προσθέσετε τα δεδομένα πωλήσεων της επόμενης ημέρας:
- Κάντε δεξί κλικ στην κεφαλίδα γραμμής για τη γραμμή 6 για να ανοίξετε το μενού περιβάλλοντος.
- Στο μενού, κάντε κλικ στο Εισαγωγή για να εισαγάγετε μια νέα σειρά στο φύλλο εργασίας.
- Ως αποτέλεσμα, ο τύπος SUM OFFSET μετακινείται προς τα κάτω στο κελί B7 και η σειρά 6 είναι τώρα κενή.
- Κάντε κλικ στο κελί A6 .
- Καταχωρίστε τον αριθμό 5 για να δηλώσετε ότι έχει εισαχθεί το σύνολο πωλήσεων για την πέμπτη ημέρα.
- Κάντε κλικ στο κελί B6.
- Πληκτρολογήστε τον αριθμό 1458.25 $ και πατήστε το πλήκτρο Enter στο πληκτρολόγιο.
Το Cell B7 ενημερώνεται για το νέο σύνολο των 7137,40 δολαρίων.
Όταν κάνετε κλικ στο κελί B7, ο ενημερωμένος τύπος = SUM (B2: OFFSET (B7, -1,0)) εμφανίζεται στη γραμμή τύπων.
Σημείωση : Η συνάρτηση OFFSET έχει δύο προαιρετικά επιχειρήματα: Ύψος και Πλάτος, τα οποία παραλείφθηκαν σε αυτό το παράδειγμα.
Αυτά τα επιχειρήματα μπορούν να χρησιμοποιηθούν για να λένε στη λειτουργία OFFSET τη μορφή της εξόδου από την άποψη ότι είναι τόσες πολλές σειρές υψηλές και τόσες πολλές στήλες ευρείες.
Παραλείποντας αυτά τα επιχειρήματα, η συνάρτηση χρησιμοποιεί από προεπιλογή το ύψος και το πλάτος του επιχειρήματος αναφοράς, το οποίο, σε αυτό το παράδειγμα, είναι μία σειρά υψηλών και μια στήλη πλάτη.