Αυτό το επεισόδιο δείχνει τρεις τρόπους για να εκτελέσετε σύνολα λειτουργίας.
Ένα σύνολο εκτέλεσης είναι, για μια λίστα αριθμητικών τιμών, ένα άθροισμα των τιμών από την πρώτη σειρά έως τη σειρά του τρέχοντος συνόλου. Οι κοινές χρήσεις ενός συνόλου που τρέχουν βρίσκονται σε ένα μητρώο βιβλίων ελέγχου ή σε ένα λογιστικό φύλλο. Υπάρχουν πολλοί τρόποι για να δημιουργήσετε ένα τρέχον σύνολο-δύο από τους οποίους περιγράφονται παρακάτω.
Η απλούστερη τεχνική είναι να προσθέσετε, σε κάθε σειρά, το τρέχον σύνολο από την παραπάνω γραμμή στην τιμή της σειράς. Έτσι, ο πρώτος τύπος στη σειρά 2 είναι:
=SUM(D1,C2)
Ο λόγος που χρησιμοποιούμε τη συνάρτηση SUM είναι επειδή, στην πρώτη σειρά, εξετάζουμε την κεφαλίδα στην παραπάνω σειρά. Εάν χρησιμοποιήσουμε τον απλούστερο, πιο διαισθητικό τύπο =D1+C2τότε θα δημιουργηθεί ένα σφάλμα, επειδή η τιμή κεφαλίδας είναι κείμενο έναντι αριθμητικού. Η μαγεία είναι ότι η συνάρτηση SUM αγνοεί τις τιμές κειμένου, οι οποίες προστίθενται ως μηδενικές τιμές. Όταν ο τύπος αντιγράφεται σε όλες τις σειρές στις οποίες είναι επιθυμητό ένα σύνολο λειτουργίας, οι αναφορές κελιού προσαρμόζονται ανάλογα:
Η άλλη τεχνική χρησιμοποιεί επίσης τη συνάρτηση SUM, αλλά κάθε τύπος αθροίζει όλες τις τιμές από την πρώτη σειρά έως τη γραμμή που εμφανίζει το τρέχον σύνολο. Σε αυτήν την περίπτωση χρησιμοποιούμε ένα σύμβολο δολαρίου ($) για να κάνουμε το πρώτο κελί στην αναφορά μια απόλυτη αναφορά που σημαίνει ότι δεν προσαρμόζεται όταν αντιγράφεται:
Και οι δύο τεχνικές δεν επηρεάζονται με ταξινόμηση και διαγραφή σειρών, αλλά, όταν εισάγετε σειρές, ο τύπος πρέπει να αντιγραφεί στις νέες σειρές.
Το Excel 2007 εισήγαγε τον Πίνακα που αποτελεί επανεκτέλεση της λίστας στο Excel 2003. Οι πίνακες εισήγαγαν μια σειρά από πολύ χρήσιμες δυνατότητες για πίνακες δεδομένων, όπως μορφοποίηση, ταξινόμηση και φιλτράρισμα. Με την εισαγωγή των πινάκων μας δόθηκε επίσης ένας νέος τρόπος αναφοράς των τμημάτων ενός πίνακα. Αυτό το νέο στυλ αναφοράς ονομάζεται δομημένη αναφορά.
Για να μετατρέψουμε το παραπάνω παράδειγμα σε πίνακα, επιλέγουμε τα δεδομένα που θέλουμε να συμπεριλάβουμε στον πίνακα και πατήστε Ctrl + T. Αφού εμφανιστεί ένα μήνυμα που μας ζητά να επιβεβαιώσουμε το εύρος του πίνακα και εάν υπάρχουν ή όχι υπάρχουσες κεφαλίδες, το Excel μετατρέπει τα δεδομένα σε έναν μορφοποιημένο πίνακα:
Σημειώστε ότι οι τύποι που εισαγάγαμε νωρίτερα παραμένουν οι ίδιοι.
Μία από τις χρήσιμες δυνατότητες που προσφέρει ο πίνακας είναι η αυτόματη μορφοποίηση και η συντήρηση των τύπων καθώς προστίθενται, αφαιρούνται, ταξινομούνται και φιλτράρονται σειρές. Πρόκειται κυρίως για τη συντήρηση της φόρμουλας στην οποία θα επικεντρωθούμε και που μπορεί να είναι προβληματική. Για να διατηρήσει τους Πίνακες να λειτουργούν όσο χειρίζονται, το Excel χρησιμοποιεί υπολογισμένες στήλες που είναι στήλες με τύπους όπως η στήλη D στο παραπάνω παράδειγμα. Όταν εισάγονται νέες σειρές προστίθενται στο κάτω μέρος, το Excel συμπληρώνει αυτόματα τις νέες σειρές με τον τύπο "προεπιλογή" για τη συγκεκριμένη στήλη. Το πρόβλημα με το παραπάνω παράδειγμα είναι ότι το Excel συγχέεται με τους τυπικούς τύπους και δεν τα χειρίζεται πάντα σωστά. Αυτό γίνεται εμφανές όταν προστίθενται νέες σειρές στο κάτω μέρος του πίνακα (επιλέγοντας το κάτω δεξί κελί στον πίνακα και πατώντας TAB):
Αυτή η ανεπάρκεια επιλύεται χρησιμοποιώντας τη νεότερη δομημένη αναφορά. Η δομημένη αναφορά εξαλείφει την ανάγκη αναφοράς συγκεκριμένων κελιών χρησιμοποιώντας το στυλ αναφοράς A1 ή R1C1 και αντ 'αυτού χρησιμοποιεί ονόματα στηλών και άλλες λέξεις-κλειδιά για τον προσδιορισμό και την αναφορά των τμημάτων ενός πίνακα. Για παράδειγμα, για να δημιουργήσετε τον ίδιο τρέχοντα συνολικό τύπο που χρησιμοποιήθηκε παραπάνω αλλά χρησιμοποιώντας δομημένη αναφορά έχουμε:
=SUM(INDEX((Sales),1):(@Sales))
Σε αυτό το παράδειγμα έχουμε μια αναφορά στο όνομα της στήλης, "Πωλήσεις", μαζί με το σύμβολο στο (@) για να αναφέρουμε τη σειρά στη στήλη στην οποία βρίσκεται ο τύπος που είναι επίσης γνωστή ως η τρέχουσα σειρά.
Για να εφαρμόσετε το πρώτο παράδειγμα παραπάνω όπου προσθέσαμε την τρέχουσα συνολική τιμή στην προηγούμενη σειρά στο ποσό πωλήσεων στην τρέχουσα σειρά, μπορείτε να χρησιμοποιήσετε τη συνάρτηση OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Εάν τα ποσά που χρησιμοποιούνται για τον υπολογισμό του τρέχοντος συνόλου είναι σε δύο στήλες, για παράδειγμα μία για "Χρεώσεις" και μία για "Πιστώσεις", τότε ο τύπος είναι:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Εδώ χρησιμοποιούμε τη συνάρτηση INDEX για να εντοπίσουμε τα κελιά πιστωτικών και χρεωστικών στοιχείων της πρώτης σειράς και να συνοψίσουμε ολόκληρη τη στήλη έως και τις τιμές της τρέχουσας γραμμής. Το συνολικό τρέξιμο είναι το άθροισμα όλων των πιστώσεων έως και την τρέχουσα σειρά μείον το άθροισμα όλων των χρεώσεων έως και την τρέχουσα σειρά.
Για περισσότερες πληροφορίες σχετικά με τις δομημένες αναφορές συγκεκριμένα και τους πίνακες γενικά, προτείνουμε το βιβλίο Excel Tables: Ένας πλήρης οδηγός για τη δημιουργία, τη χρήση και την αυτοματοποίηση λιστών και πινάκων των Zack Barresse και Kevin Jones.
Όταν ζήτησα από τους αναγνώστες να ψηφίσουν τις αγαπημένες τους συμβουλές, τα τραπέζια ήταν δημοφιλή. Χάρη στους Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel και Paul Peton για την πρόταση αυτού του χαρακτηριστικού. Ο Peter Albert έγραψε τη συμβουλή μπόνους ανάγνωσης αναφορών. Ο Zack Barresse έγραψε τη συμβουλή μπόνους Running Totals. Τέσσερις αναγνώστες πρότειναν τη χρήση του OFFSET για τη δημιουργία διευρυμένων περιοχών για δυναμικά γραφήματα: Charley Baak, Don Knowles, Francis Logan και Cecelia Rieb. Οι πίνακες κάνουν τώρα το ίδιο πράγμα στις περισσότερες περιπτώσεις.
Δες το βίντεο
- Αυτό το επεισόδιο δείχνει τρεις τρόπους για να εκτελέσετε σύνολα λειτουργίας
- Η πρώτη μέθοδος έχει διαφορετικό τύπο στη σειρά 2 από όλες τις άλλες σειρές
- Η πρώτη μέθοδος είναι = Αριστερά στη σειρά 2 και = Αριστερά + Πάνω στις σειρές 3 έως Ν
- Εάν προσπαθήσετε να χρησιμοποιήσετε τον ίδιο τύπο, λαμβάνετε ένα σφάλμα #Value με = Σύνολο + Αριθμός
- Η μέθοδος 2 χρησιμοποιεί
=SUM(Up,Left)ή=SUM(Previous Total,This Row Amount) - Το SUM αγνοεί το Κείμενο, ώστε να μην εμφανιστεί ένα VALUE σφάλμα
- Η μέθοδος 3 χρησιμοποιεί ένα αναπτυσσόμενο εύρος:
=SUM(B$2:B2) - Τα αναπτυσσόμενα εύρη είναι δροσερά αλλά είναι αργά
- Διαβάστε τη Λευκή Βίβλο του Charles Williams στο Excel Formula Speed
- Η τρίτη μέθοδος είναι ένα πρόβλημα όταν χρησιμοποιείτε Ctrl + T και προσθέτετε νέες σειρές
- Το Excel δεν μπορεί να καταλάβει πώς να γράψει τον τύπο
- Οι λύσεις απαιτούν κάποια γνώση της δομημένης αναφοράς στους Πίνακες
- Η λύση 1 είναι η αργή
=SUM(INDEX((Qty),1):(@Qty)) - Η λύση 2 είναι η ευμετάβλητη
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) αναφέρεται στο Qty σε αυτήν τη σειρά
- (Qty) αναφέρεται σε όλες τις τιμές Qty
Μεταγραφή βίντεο
Μάθετε το Excel για Podcast, Episode 2004 - Running Total
Θα μεταδώσω ολόκληρο το βιβλίο σε podcast. Κάντε κλικ σε αυτό στην επάνω δεξιά γωνία για εγγραφή.
Γεια σας, καλώς ήλθατε πίσω στο μυστικιστικό netcast. Είμαι ο Μπιλ Τζέλεν. Τώρα αυτό το θέμα στο βιβλίο, μου συνέβαλε ο φίλος μου Zach Parise. Μιλήστε για πίνακες Excel, το Zach είναι ο ειδικός στον κόσμο σε πίνακες Excel. Έχει γράψει ένα βιβλίο για τους πίνακες του Excel, αλλά πρώτα ας μιλήσουμε για τα σύνολα που τρέχουν όχι σε πίνακες.
Έτσι, όταν σκέφτομαι το σύνολο των τρεξίματος, υπάρχουν τρεις διαφορετικοί τρόποι για να κάνω τα σύνολα τρεξίματος, και ο τρόπος με τον οποίο ξεκίνησα πάντα είναι στην πρώτη σειρά που απλώς λέτε, φέρτε την αξία. Τόσο ίσα ό, τι είναι στα αριστερά μου Εντάξει, έτσι αυτή η μορφή εδώ είναι απλώς = B2. Αυτά είναι όλα τα κείμενα τύπου εδώ στη δεξιά γωνία, οπότε βλέπετε τι χρησιμοποιούμε και, στη συνέχεια, από εκεί κάτω, είναι ένας απλός μικρός τύπος ίσης με την προηγούμενη τιμή, συν την τρέχουσα τιμή δεξιά και αντιγράψτε την κάτω , αλλά ξέρετε τώρα, έχουμε αυτό το πρόβλημα που απαιτούσε δύο διαφορετικούς τύπους και ξέρετε ότι σε μια τέλεια κατάσταση έχετε τον ίδιο ακριβώς τύπο μέχρι κάτω, και ο λόγος που πρέπει να έχουμε διαφορετική φόρμουλα εκεί στην πρώτη σειρά είναι ότι όταν δοκιμάζετε και προσθέτετε ίσο 7 συν τη λέξη συνολικά είναι σφάλμα τιμής,αλλά ο δροσερός εργαζόμενος εδώ, είναι να μην χρησιμοποιήσετε μόνο το αριστερό συν το πάνω, αλλά να χρησιμοποιήσετε το = (SUM) της προηγούμενης τιμής συν την ποσότητα σε αυτήν τη σειρά και να δείτε μερικά είναι αρκετά μακριά για να αγνοήσετε τα κείμενα. Ακριβώς έτσι επιτρέπει τον ίδιο τύπο. σε όλη τη διαδρομή.
Εντάξει, έτσι ώστε όταν ξεκίνησα να χρησιμοποιώ το Excel, το χρησιμοποιούσα και στη συνέχεια ανακάλυψα το επεκτεινόμενο εύρος, το αναπτυσσόμενο εύρος λέει ότι θα κάνουμε L $ 2: L2 και αυτό που συμβαίνει είναι ότι ξεκινά πάντα από τη σειρά 2, αλλά μετά πηγαίνει στην τρέχουσα σειρά. Έτσι, όταν κοιτάζετε πώς λειτουργεί αυτό όταν αντιγράφεται, ξεκινήσαμε πάντα τη σειρά 2, αλλά πηγαίνουμε στην τρέχουσα σειρά και αυτή έγινε η αγαπημένη μου μέθοδος. Ήμουν σαν, ω, αυτό είναι πολύ πιο εξελιγμένο και όταν μπαίνουμε στις Επιλογές του Excel, μεταβείτε στην καρτέλα Τύποι και επιλέξτε R1C1 στο Στυλ αναφοράς. Εντάξει δείτε, R1C1, όλοι αυτοί οι τύποι είναι ακριβώς οι ίδιοι μέχρι κάτω. Δεν ξέρω αν καταλαβαίνετε το R1C1, είναι καλό να γνωρίζουμε ότι έχουμε πανομοιότυπους τύπους R1C1 μέχρι κάτω.
Ας πάμε πίσω. Αυτή η μέθοδος εδώ λοιπόν είναι η μέθοδος που μου άρεσε, μέχρι που ο Charles Williams, ένα MBP του Excel από την Αγγλία, ο οποίος έχει ένα καταπληκτικό χαρτί σχετικά με την ταχύτητα φόρμουλας, την ταχύτητα φόρμουλας του Excel, απενεργοποίησε πλήρως αυτήν τη μέθοδο. Αυτή η μέθοδος, ας υποθέσουμε ότι έχετε 10.000 σειρές, κάθε τύπος εξετάζει δύο παραπομπές. Εξετάζετε λοιπόν 20.000 αναφορές, αλλά αυτή, δύο, δύο, τρία, τέσσερα, και τελευταία τελευταία 10.000 αναφορές και είναι τρομερά πιο αργή και έτσι σταμάτησα να χρησιμοποιώ αυτήν τη μέθοδο.
Στη συνέχεια, συνεχίζω να διαβάζω τον Zack στο βιβλίο του Kevin Jones σχετικά με τους πίνακες του Excel και ανακαλύπτω ένα ακόμη πρόβλημα με αυτήν τη μέθοδο. Έτσι, ένα από τα χρήσιμα χαρακτηριστικά που προσφέρει ο πίνακας, είναι «αυτόματη μορφοποίηση και συντήρηση τύπων, προστίθενται γραμμές, αφαιρούνται, ταξινομούνται και φιλτράρονται». Εντάξει, αυτό είναι ένα απόσπασμα από το βιβλίο του. Και για να προσθέσετε μια σειρά σε έναν πίνακα, απλώς πηγαίνετε στο τελευταίο κελί του πίνακα και πατήστε την καρτέλα. Έτσι όλα λειτουργούν εδώ. Είμαστε κάτω από 70 δεξιά που είναι φοβερό και μετά το A104 και θα βάλω 100 εδώ. Εντάξει, έτσι ώστε το 70 να αλλάξει σε 170 και το κάνει, αλλά αυτό το 70 δεν θα έπρεπε να έχει αλλάξει καθόλου. Εντάξει 68 + 2 δεν είναι 170. Θα το κάνω ξανά. Ένα 104 και έβαλε άλλα εκατό στο τελευταίο είναι σωστό. Αυτά τα δύο δεν είναι σωστά. Εντάξει, οπότε έχουμε μια περίεργη κατάσταση που ανεπαναχρησιμοποιείτε αυτόν τον τύπο και μετατρέπετε σε πίνακα αρχίζετε να προσθέτετε σειρές, το τρέχον σύνολο δεν πρόκειται να λειτουργήσει. Πόσο κακό είναι αυτό;
Εντάξει, οπότε ο Zack προσφέρει δύο δουλειές και οι δύο απαιτούν λίγη γνώση σχετικά με τον τρόπο λειτουργίας των αναφορών δομής. Απλώς πρόκειται να έχουμε μια νέα στήλη εδώ και αν ήθελα να κάνω ποσότητα, ίση ποσότητα, σωστά, έτσι ώστε = (@ Qty) να λέει ποσότητα σε αυτήν τη σειρά. Ωχ, καλά, υπάρχει και ένα άλλο είδος αναφοράς όπου χρησιμοποιούμε το Qty χωρίς το @. Κοίτα αυτό. Έτσι = SUM (INDEX ((Qty), 1: (@ Qty)) σημαίνει όλες τις ποσότητες και πρόκειται να πούμε ότι θέλουμε να SUM από την πρώτη ποσότητα, έτσι (INDEX ((Qty), 1 λέει ο πρώτη τιμή εδώ, μέχρι την τρέχουσα ποσότητα σειράς, και αυτό χρησιμοποιεί μια πραγματικά ειδική έκδοση του ευρετηρίου, όταν το ευρετήριο ακολουθείται από άνω και κάτω τελεία, αλλάζει πραγματικά σε μια αναφορά κελιού. Εντάξει, λοιπόν, αυτός ο τρόπος αντιμετώπισης παραβαίνει δυστυχώς τον κανόνα του Charles Williams από, εμείςθα πρέπει να κοιτάξουμε κάθε μεμονωμένη αναφορά, και έτσι όταν λάβετε 10.000 σειρές από αυτές θα πάει πραγματικά, πολύ αργή.
Ο Zach έχει μια άλλη λύση που δεν παραβιάζει το πρόβλημα του Charles Williams, αλλά χρησιμοποιεί το φοβερό OFFSET. Το OFFSET είναι μια πτητική συνάρτηση, οπότε κάθε φορά που υπολογίζετε κάτι, το OFFSET πρόκειται να υπολογίσει ξανά και όλα κάτω από το OFFSET πρόκειται να υπολογίσουν ξανά. Είναι απλώς ένας πολύ καλός τρόπος για να βγάλετε εντελώς, εντελώς τις φόρμουλες σας και αυτό που κάνει, λέει, παίρνουμε το σύνολο από αυτήν τη σειρά, ανεβαίνοντας μια σειρά, πάνω από μηδενικές στήλες και έτσι αυτό που κάνει είναι να λέει: αρπάξτε το σύνολο από την προηγούμενη σειρά και μετά προσθέτουμε σε αυτό την ποσότητα από αυτήν τη σειρά. Εντάξει, λοιπόν, τώρα εξετάζει δύο αναφορές κάθε φορά, αλλά δυστυχώς το OFFSET εισάγει πτητικές λειτουργίες.
Λοιπόν, το έχετε, περισσότερο από ό, τι θέλατε να μάθετε για το Running Total. Υποθέτω ότι η τελική μου άποψη εδώ είναι να χρησιμοποιήσω αυτήν τη μέθοδο, γιατί φαίνεται μόνο δύο. Θα λειτουργεί ο ίδιος τύπος και οι αναφορές δομημένου πίνακα.
Για αυτήν την εξερεύνηση και 39 άλλες πραγματικά καλές συμβουλές, δείτε αυτό το βιβλίο XL, τις 40 καλύτερες συμβουλές του Excel όλων των εποχών.
Recap for this episode we talked about three ways to do running totals. The first method has a different formula, row 2, than all the other rows. It's equal left in row 2 and then equal left plus up in rows 3 through N, but if you try and just use that same formula, equal left plus up, all the way down, how you're going to get a #Value Error. So =SUM(Up,Left), which is previous total, plus this roadmap, that works great, no Value Errors and then the expanding range which I use to love. They're cool, but until I read Charles Williams white paper on Excel form of speed. Then I started to hate these expanding references. It also has a problem when you use CTRL T and add new rows. Excel can't figure out how to expand that formula, how to add new rows. I love this tip go to the very last cell in the table and press Tab, that will add a new row and then we talked about some structured referencing, where we're using quantity in this row and then all quantities. =SUM(OFFSET((@Total),-1,00,(@Qty)).
Εντάξει, θέλω να ευχαριστήσω τον Zach για τη συμβολή του. Θέλω να σας ευχαριστήσω που σταματήσατε. Θα σας δούμε την επόμενη φορά για ένα άλλο netcast από.
Λήψη αρχείου
Κάντε λήψη του δείγματος αρχείου εδώ: Podcast2004.xlsx
