Excel SUM και OFFSET Formula

Εάν το φύλλο εργασίας του Excel περιλαμβάνει υπολογισμούς που βασίζονται σε μια μεταβαλλόμενη περιοχή κυττάρων, η χρήση των λειτουργιών SUM και OFFSET μαζί σε έναν τύπο SUM OFFSET απλοποιεί την εργασία διατήρησης των υπολογισμών μέχρι σήμερα.

Δημιουργήστε μια δυναμική περιοχή με τις λειτουργίες SUM και OFFSET

Αν χρησιμοποιείτε υπολογισμούς για μια χρονική περίοδο που αλλάζει συνεχώς - όπως οι συνολικές πωλήσεις για το μήνα - η λειτουργία OFFSET στο Excel σάς επιτρέπει να ρυθμίσετε ένα δυναμικό εύρος που αλλάζει καθώς προστίθενται αριθμοί πωλήσεων κάθε ημέρας.

Από μόνη της, η συνάρτηση SUM μπορεί συνήθως να διευκολύνει την εισαγωγή νέων στοιχείων δεδομένων στην αθροιστική περιοχή.

Μια εξαίρεση προκύπτει όταν τα δεδομένα εισάγονται στο κελί όπου βρίσκεται αυτή τη λειτουργία.

Στην παραπάνω εικόνα παραδείγματος, τα νέα στοιχεία πωλήσεων για κάθε ημέρα προστίθενται στο κάτω μέρος της λίστας, αναγκάζοντας το σύνολο να μετατοπίζεται συνεχώς κάτω από ένα κελί κάθε φορά με την προσθήκη των νέων δεδομένων.

Εάν η συνάρτηση SUM χρησιμοποιήθηκε μόνη της για τη συμπλήρωση των δεδομένων, θα ήταν απαραίτητο να τροποποιηθεί το εύρος των κυττάρων που χρησιμοποιήθηκε ως όρισμα της λειτουργίας κάθε φορά που προστέθηκαν νέα δεδομένα.

Χρησιμοποιώντας ταυτόχρονα τις λειτουργίες SUM και OFFSET, η περιοχή που ολοκληρώνεται γίνεται δυναμική. Με άλλα λόγια, αλλάζει για να φιλοξενήσει νέα στοιχεία δεδομένων. Η προσθήκη νέων κελιών δεδομένων δεν προκαλεί προβλήματα επειδή το εύρος συνεχίζει να προσαρμόζεται καθώς προστίθεται κάθε νέο κελί.

Σύνταξη και επιχειρήματα

Ανατρέξτε στην παραπάνω εικόνα που ακολουθεί μαζί με αυτό το σεμινάριο.

Στον τύπο αυτό, η συνάρτηση SUM χρησιμοποιείται για να ομαδοποιήσει το εύρος των δεδομένων που παρέχονται ως επιχείρησή της. Το σημείο εκκίνησης για αυτό το εύρος είναι στατικό και αναγνωρίζεται ως η αναφορά κυψελών στον πρώτο αριθμό που θα συμπληρωθεί από τον τύπο.

Η συνάρτηση OFFSET είναι ένθετη μέσα στη συνάρτηση SUM και χρησιμοποιείται για τη δημιουργία ενός δυναμικού τελικού σημείου στην περιοχή των δεδομένων που συνθέτουν ο τύπος. Αυτό επιτυγχάνεται θέτοντας το τελικό σημείο της περιοχής σε μία κυψέλη πάνω από την τοποθεσία του τύπου.

Η σύνταξη του τύπου:

= SUM (Εκκίνηση εύρους: OFFSET (Αναφορά, Γραμμές, Πινακίδες))

• Εύρος εκκίνησης: Το σημείο εκκίνησης για το εύρος κυψελών που θα συμπληρωθεί από τη λειτουργία SUM απαιτείται. Στην εικόνα του παραδείγματος, αυτό είναι το κελί B2.
• Αναφορά: Η απαιτούμενη αναφορά κυψελών που χρησιμοποιείται για τον υπολογισμό του τελικού σημείου της περιοχής βρίσκεται σε πολλές σειρές και στήλες μακριά. Στην εικόνα παραδειγμάτων, το όρισμα αναφοράς είναι η αναφορά κυττάρων για τον ίδιο τον τύπο επειδή θέλετε πάντα το εύρος να τελειώνει ένα κελί πάνω από τον τύπο.
• Γραμμές: Ο αριθμός των γραμμών πάνω ή κάτω από το όρισμα αναφοράς που χρησιμοποιείται για τον υπολογισμό της μετατόπισης είναι απαραίτητος. Αυτή η τιμή μπορεί να είναι θετική, αρνητική ή μηδενική. Αν η θέση του μετατόπιου είναι πάνω από το Αναφορά argument, η τιμή είναι αρνητική. Αν είναι κάτω, το παράγωγο Rows είναι θετικό. Εάν η μετατόπιση βρίσκεται στην ίδια σειρά, το όρισμα είναι μηδέν. Σε αυτό το παράδειγμα, η μετατόπιση ξεκινά μία γραμμή πάνω από το όρισμα αναφοράς, οπότε η τιμή για αυτό το όρισμα είναι αρνητική (-1).
• Cols: Ο αριθμός των στηλών αριστερά ή δεξιά του επιχειρήματος αναφοράς που χρησιμοποιείται για τον υπολογισμό της μετατόπισης είναι απαραίτητος. Αυτή η τιμή μπορεί να είναι θετική, αρνητική ή μηδενική. Εάν η θέση της μετατόπισης βρίσκεται στα αριστερά του παραθύρου αναφοράς, αυτή η τιμή είναι αρνητική. Αν προς τα δεξιά, το επιχείρημα Cols είναι θετικό. Σε αυτό το παράδειγμα, τα δεδομένα που αθροίζονται είναι στην ίδια στήλη με τον τύπο, οπότε η τιμή για αυτό το όρισμα είναι μηδέν.

Χρησιμοποιώντας το SUM OFFSET Formula σε συνολικά δεδομένα πωλήσεων

Αυτό το παράδειγμα χρησιμοποιεί έναν τύπο SUM OFFSET για να επιστρέψει το σύνολο για τα στοιχεία ημερήσιας πώλησης που αναφέρονται στη στήλη Β του φύλλου εργασίας.

Αρχικά, ο τύπος εισήχθη στο κελί B6 και ανήλθε στα στοιχεία πωλήσεων για τέσσερις ημέρες.

Το επόμενο βήμα είναι να μετακινήσετε τον τύπο SUM OFFSET κάτω από μια σειρά για να κάνετε χώρο για τις συνολικές πωλήσεις της πέμπτης ημέρας. Αυτό επιτυγχάνεται με την εισαγωγή μιας νέας γραμμής 6, η οποία μετακινεί τον τύπο στη σειρά 7.

Ως αποτέλεσμα της μετακίνησης, το Excel ενημερώνει αυτόματα το όρισμα αναφοράς στο κελίΒ7 και προσθέτει κελιάΒ6 στο εύρος που συνόψισε ο τύπος.

Εισαγωγή του τύπου SUM OFFSET

1. Κάντε κλικ στο κελί B6, που είναι η θέση όπου αρχικά θα εμφανιστούν τα αποτελέσματα του μαθηματικού τύπου.
2. Κάνε κλικ στοΜΑΘΗΜΑΤΙΚΟΙ τυποι καρτέλα του μενού κορδέλας.
3. ΕπιλέγωMath & Trigαπό την κορδέλα για να ανοίξετε την αναπτυσσόμενη λίστα λειτουργιών.
4. Κάντε κλικ στοΑΘΡΟΙΣΜΑ στη λίστα για να εμφανιστεί το παράθυρο διαλόγου της λειτουργίας.
5. Στο παράθυρο διαλόγου, κάντε κλικ στοΝούμερο 1γραμμή.
6. Κάντε κλικ στο κελί Β2 για να εισαγάγετε αυτήν την αναφορά κυττάρων στο παράθυρο διαλόγου. Αυτή η θέση είναι το στατικό τελικό σημείο για τον τύπο.
7. Στο παράθυρο διαλόγου, κάντε κλικ στοΝούμερο 2γραμμή.
8. Εισαγάγετε την ακόλουθη λειτουργία OFFSET: OFFSET (B6, -1,0) για να σχηματίσουμε το δυναμικό τελικό σημείο για τον τύπο.
9. Κάντε κλικ Εντάξει για να ολοκληρώσετε τη λειτουργία και να κλείσετε το παράθυρο διαλόγου.

Το σύνολο εμφανίζεται στο κελί B7.

Προσθήκη δεδομένων πωλήσεων της επόμενης ημέρας

Για να προσθέσετε τα δεδομένα πωλήσεων της επόμενης ημέρας:

1. Κάντε δεξί κλικ στην κεφαλίδα γραμμής για τη γραμμή 6 για να ανοίξετε το μενού περιβάλλοντος.
2. Στο μενού, κάντε κλικ στοΕισάγετε για να εισαγάγετε μια νέα σειρά στο φύλλο εργασίας.
3. Ως αποτέλεσμα, ο τύπος SUM OFFSET μετακινείται προς τα κάτω στο κελί B7 και η σειρά 6 είναι τώρα κενή.
4. Κάντε κλικ στο κελί A6.
5. Καταχωρίστε τον αριθμό5 για να δηλωθεί ότι εισάγεται το σύνολο πωλήσεων για την πέμπτη ημέρα.
6. Κάντε κλικ στο κελί B6.
7. Πληκτρολογήστε τον αριθμό$1458.25 και πατήστε τοΕισαγω στο πληκτρολόγιο.

Το στοιχείο B7 ενημερώνεται στο νέο σύνολο$7137.40.

Όταν κάνετε κλικ στο κελί B7, ο ενημερωμένος τύπος= SUM (B2: OFFSET (B7, -1,0)) εμφανίζεται στη γραμμή τύπων.

Η λειτουργία OFFSET έχει δύο προαιρετικά επιχειρήματα: Ύψος και Πλάτος , που δεν χρησιμοποιήθηκαν σε αυτό το παράδειγμα.

Αυτά τα επιχειρήματα μπορούν να χρησιμοποιηθούν για να αποκαλύψουν τη λειτουργία OFFSET τη μορφή της εξόδου από την άποψη τόσων πολλών σειρών υψηλών και τόσων πολλών στηλών.

Παραλείποντας αυτά τα επιχειρήματα, η συνάρτηση χρησιμοποιεί από προεπιλογή το ύψος και το πλάτος του επιχειρήματος αναφοράς, το οποίο, σε αυτό το παράδειγμα, είναι μία σειρά υψηλών και μια στήλη πλάτη.