Εάν το φύλλο εργασίας του Excel περιλαμβάνει υπολογισμούς που βασίζονται σε ένα μεταβαλλόμενο εύρος κελιών, χρησιμοποιήστε τις συναρτήσεις SUM και OFFSET μαζί σε έναν τύπο SUM OFFSET για να απλοποιήσετε την εργασία της ενημέρωσης των υπολογισμών.
Οι οδηγίες σε αυτό το άρθρο ισχύουν για το Excel για Microsoft 365, Excel 2019, Excel 2016, Excel 2013 και Excel 2010.
Δημιουργία δυναμικού εύρους με τις συναρτήσεις SUM και OFFSET
Εάν χρησιμοποιείτε υπολογισμούς για μια χρονική περίοδο που αλλάζει συνεχώς - όπως ο προσδιορισμός των πωλήσεων για τον μήνα - χρησιμοποιήστε τη συνάρτηση OFFSET στο Excel για να ρυθμίσετε ένα δυναμικό εύρος που αλλάζει καθώς προστίθενται τα στοιχεία πωλήσεων κάθε ημέρας.
Από μόνη της, η συνάρτηση SUM μπορεί συνήθως να φιλοξενήσει την εισαγωγή νέων κελιών δεδομένων στην περιοχή που αθροίζεται. Μια εξαίρεση προκύπτει όταν τα δεδομένα εισάγονται στο κελί όπου βρίσκεται αυτή τη στιγμή η συνάρτηση.
Στο παρακάτω παράδειγμα, τα νέα στοιχεία πωλήσεων για κάθε ημέρα προστίθενται στο κάτω μέρος της λίστας, αναγκάζοντας το σύνολο να μετακινείται συνεχώς προς τα κάτω κατά ένα κελί κάθε φορά καθώς προστίθενται τα νέα δεδομένα.
Για να ακολουθήσετε αυτόν τον οδηγό, ανοίξτε ένα κενό φύλλο εργασίας του Excel και εισαγάγετε τα δείγματα δεδομένων. Το φύλλο εργασίας σας δεν χρειάζεται να έχει μορφοποίηση όπως στο παράδειγμα, αλλά φροντίστε να εισαγάγετε τα δεδομένα στα ίδια κελιά.
Εάν χρησιμοποιείται μόνο η συνάρτηση SUM για το σύνολο των δεδομένων, το εύρος των κελιών που χρησιμοποιείται ως όρισμα συνάρτησης θα πρέπει να τροποποιείται κάθε φορά που προστίθενται νέα δεδομένα.
Χρησιμοποιώντας τις συναρτήσεις SUM και OFFSET μαζί, το εύρος που αθροίζεται γίνεται δυναμικό και αλλάζει για να φιλοξενήσει νέα κελιά δεδομένων. Η προσθήκη νέων κελιών δεδομένων δεν προκαλεί προβλήματα επειδή το εύρος συνεχίζει να προσαρμόζεται καθώς προστίθεται κάθε νέο κελί.
Σύνταξη και Επιχειρήματα
Σε αυτόν τον τύπο, η συνάρτηση SUM χρησιμοποιείται για να αθροίσει το εύρος των δεδομένων που παρέχονται ως όρισμα. Το σημείο εκκίνησης για αυτό το εύρος είναι στατικό και προσδιορίζεται ως η αναφορά του κελιού στον πρώτο αριθμό που αθροίζεται από τον τύπο.
Η συνάρτηση OFFSET είναι ένθετη μέσα στη συνάρτηση SUM και δημιουργεί ένα δυναμικό τελικό σημείο στο εύρος δεδομένων που αθροίζεται από τον τύπο. Αυτό επιτυγχάνεται ορίζοντας το τελικό σημείο του εύρους σε ένα κελί πάνω από τη θέση του τύπου.
Η σύνταξη του τύπου είναι:
=SUM(Έναρξη εύρους:OFFSET(Αναφορά, σειρές, στήλες))
Τα ορίσματα είναι:
- Εύρος Έναρξη: Το σημείο εκκίνησης για το εύρος των κελιών που θα αθροιστούν από τη συνάρτηση SUM. Σε αυτό το παράδειγμα, το σημείο εκκίνησης είναι το κελί B2.
- Αναφορά: Η απαιτούμενη αναφορά κελιού που χρησιμοποιείται για τον υπολογισμό του τελικού σημείου εύρους. Στο παράδειγμα, το όρισμα Αναφορά είναι η αναφορά κελιού για τον τύπο, επειδή το εύρος τελειώνει ένα κελί πάνω από τον τύπο.
- Σειρά: Απαιτείται ο αριθμός των σειρών πάνω ή κάτω από το όρισμα αναφοράς που χρησιμοποιείται για τον υπολογισμό της μετατόπισης. Αυτή η τιμή μπορεί να είναι θετική, αρνητική ή μηδενική. Εάν η θέση μετατόπισης βρίσκεται πάνω από το όρισμα Αναφορά, η τιμή είναι αρνητική. Εάν η μετατόπιση είναι παρακάτω, το όρισμα Γραμμές είναι θετικό. Εάν η μετατόπιση βρίσκεται στην ίδια σειρά, το όρισμα είναι μηδέν. Σε αυτό το παράδειγμα, η μετατόπιση ξεκινά μια σειρά πάνω από το όρισμα αναφοράς, επομένως η τιμή για το όρισμα είναι αρνητική μία (-1).
- Cols: Ο αριθμός των στηλών στα αριστερά ή στα δεξιά του ορίσματος Αναφορά που χρησιμοποιείται για τον υπολογισμό της μετατόπισης. Αυτή η τιμή μπορεί να είναι θετική, αρνητική ή μηδενική. Εάν η θέση μετατόπισης βρίσκεται στα αριστερά του ορίσματος Αναφορά, αυτή η τιμή είναι αρνητική. Εάν η μετατόπιση είναι προς τα δεξιά, το όρισμα Cols είναι θετικό. Σε αυτό το παράδειγμα, τα δεδομένα που αθροίζονται βρίσκονται στην ίδια στήλη με τον τύπο, επομένως η τιμή για αυτό το όρισμα είναι μηδέν.
Χρησιμοποιήστε τον τύπο SUM OFFSET σε δεδομένα συνολικών πωλήσεων
Αυτό το παράδειγμα χρησιμοποιεί έναν τύπο SUM OFFSET για να επιστρέψει το σύνολο για τα ημερήσια στοιχεία πωλήσεων που αναφέρονται στη στήλη Β του φύλλου εργασίας. Αρχικά, ο τύπος εισήχθη στο κελί B6 και συγκέντρωσε τα δεδομένα πωλήσεων για τέσσερις ημέρες.
Το επόμενο βήμα είναι να μετακινήσετε τον τύπο SUM OFFSET μία σειρά προς τα κάτω για να δημιουργήσετε χώρο για το σύνολο των πωλήσεων της πέμπτης ημέρας. Αυτό επιτυγχάνεται με την εισαγωγή μιας νέας σειράς 6, η οποία μετακινεί τον τύπο στη σειρά 7.
Ως αποτέλεσμα της μετακίνησης, το Excel ενημερώνει αυτόματα το όρισμα αναφοράς στο κελί B7 και προσθέτει το κελί B6 στο εύρος που αθροίζεται από τον τύπο.
- Επιλέξτε το κελί B6, το οποίο είναι η τοποθεσία όπου θα εμφανιστούν αρχικά τα αποτελέσματα του τύπου.
-
Επιλέξτε την καρτέλα Formulas της κορδέλας.
Image -
Επιλέξτε Math & Trig.
Image -
Επιλέξτε SUM.
Image -
Στο πλαίσιο διαλόγου Function Arguments, τοποθετήστε τον κέρσορα στο πλαίσιο κειμένου Number1.
-
Στο φύλλο εργασίας, επιλέξτε το κελί B2 για να εισαγάγετε αυτήν την αναφορά κελιού στο παράθυρο διαλόγου. Αυτή η τοποθεσία είναι το στατικό τελικό σημείο για τον τύπο.
Image - Στο πλαίσιο διαλόγου Function Arguments, τοποθετήστε τον κέρσορα στο πλαίσιο κειμένου Number2.
-
Εισαγάγετε OFFSET(B6, -1, 0). Αυτή η συνάρτηση OFFSET σχηματίζει το δυναμικό τελικό σημείο για τον τύπο.
Image -
Επιλέξτε OK για να ολοκληρώσετε τη λειτουργία και να κλείσετε το παράθυρο διαλόγου. Το σύνολο εμφανίζεται στο κελί B6.
Image
Προσθήκη δεδομένων πωλήσεων της επόμενης ημέρας
Για να προσθέσετε τα δεδομένα πωλήσεων της επόμενης ημέρας:
- Κάντε δεξί κλικ στην κεφαλίδα της σειράς για τη σειρά 6.
-
Επιλέξτε Insert για να εισαγάγετε μια νέα σειρά στο φύλλο εργασίας. Ο τύπος SUM OFFSET μετακινείται προς τα κάτω κατά μία σειρά στο κελί B7 και η σειρά 6 είναι τώρα κενή.
Image - Επιλέξτε το κελί A6 και εισαγάγετε τον αριθμό 5 για να υποδείξετε ότι εισάγεται το σύνολο των πωλήσεων για την πέμπτη ημέρα.
-
Επιλέξτε το κελί B6, πληκτρολογήστε $1458.25 και, στη συνέχεια, πατήστε Enter.
Image - Ενημερώσεις Cell B7 στο νέο σύνολο των 7137,40 $.
Όταν επιλέγετε το κελί B7, ο ενημερωμένος τύπος εμφανίζεται στη γραμμή τύπων.
=SUM(B2:OFFSET(B7, -1, 0))
Η συνάρτηση OFFSET έχει δύο προαιρετικά ορίσματα: Height και Width, τα οποία δεν χρησιμοποιήθηκαν σε αυτό το παράδειγμα. Αυτά τα ορίσματα λένε στη συνάρτηση OFFSET το σχήμα της εξόδου ως προς τον αριθμό των γραμμών και στηλών.
Παραλείποντας αυτά τα ορίσματα, η συνάρτηση χρησιμοποιεί αντί αυτού το ύψος και το πλάτος του ορίσματος αναφοράς, το οποίο, σε αυτό το παράδειγμα έχει ύψος μία γραμμή και πλάτος μία στήλη.
