Λειτουργία SUMIF. Πρώτα από τα τρία Φάλαινα Excel. Ξεκινάω την ανασκόπηση των κύριων εργαλείων του αγαπημένου μας προγράμματος από τη Microsoft. Και φυσικά, στην αρχή θέλω να μιλήσω για τη συνάρτηση που χρησιμοποιώ πιο συχνά («φόρμουλα»). Πιο συγκεκριμένα, η συνάρτηση SUMMESLI. Αν δεν έχετε ιδέα τι είναι και πώς να το χρησιμοποιήσετε - σας ζηλεύω! Για μένα ήταν μια πραγματική ανακάλυψη.
Έπρεπε να συνοψίσετε δεδομένα για υπαλλήλους ή πελάτες από έναν μεγάλο πίνακα, να επιλέξετε πόσα έσοδα ήταν για τη μία ή την άλλη ονοματολογία; Φιλτράρατε με βάση το επίθετο/θέση και μετά εισαγάγατε τους αριθμούς με το χέρι σε ξεχωριστά κελιά; Ίσως σε μια αριθμομηχανή; Τι γίνεται αν υπάρχουν περισσότερες από χίλιες σειρές; Πώς να υπολογίσετε γρήγορα;
Εδώ το SUMMESLI είναι χρήσιμο!
Εργασία 1. Υπάρχουν στατιστικά στοιχεία για αγαθά, πόλεις και ποιοι δείκτες επιτεύχθηκαν για αυτές τις θέσεις. Είναι απαραίτητο να υπολογίσετε:Πόσο πωλήθηκε το προϊόν 1;
Πώς λειτουργεί η συνάρτηση SUMIF στο Excel; Άθροισμα κατά αξία
Πριν προχωρήσουμε στη λύση του 1ου προβλήματος, ας αναλύσουμε από τι αποτελείται η συνάρτηση SUMIF:
- Εύρος.Το εύρος που περιέχει τους όρους αναζήτησης. Απαιτείται συμπλήρωση. Για την 1η εργασία, η στήλη Προϊόν.
- Κριτήριο.Μπορεί να συμπληρωθεί με έναν αριθμό (85), μια έκφραση (">85"), μια αναφορά κελιού (B1), μια συνάρτηση (σήμερα()). Ορίζει την συνθήκη με την οποία αθροίζεται (!). Όλες οι συνθήκες κειμένου περικλείονται σε εισαγωγικά ( « ) ">85". Απαιτείται συμπλήρωση. Για την 1η στήλη εργασίας =Προϊόν1
- Sum_range.Κελιά προς άθροιση εάν είναι διαφορετικά από τα κελιά στο εύρος. Για την 1η εργασία, η στήλη Έσοδα.
Ας γράψουμε λοιπόν τον τύπο, έχοντας προηγουμένως εισαγάγει το όρισμα συνθήκης στο κελί F3
Μην ξεχάσετε να ελέγξετε;) Μετρήσατε; Σωστά? Εξοχος!
Πώς λειτουργεί η συνάρτηση SUMIFS στο Excel; Άθροισμα πάνω από τιμές
Εκείνοι. Η επιλογή πρέπει να γίνει σε δύο παραμέτρους. Για να το κάνετε αυτό, χρησιμοποιήστε τη συνάρτηση SUMIF για διάφορες συνθήκες - SUMIFS, όπου η σειρά εγγραφής και ο αριθμός των ορισμάτων αλλάζουν ελαφρώς.
Λειτουργία ΑΘΡΟΙΣΜΑείναι ίσως το πιο συχνά χρησιμοποιούμενο. Αλλά μερικές φορές χρειάζεστε περισσότερη ευελιξία από αυτή που μπορεί να σας προσφέρει. Λειτουργία SUMIFθα είναι χρήσιμο όταν χρειάζεται να υπολογίσετε πλασματικά ποσά. Για παράδειγμα, μπορεί να θέλετε να λάβετε το άθροισμα μόνο αρνητικών τιμών σε μια περιοχή κελιών. Αυτό το άρθρο δείχνει πώς να χρησιμοποιήσετε τη συνάρτηση SUMIFΓια υπό όρους άθροισηχρησιμοποιώντας ένα κριτήριο.
Οι τύποι βασίζονται στον πίνακα που φαίνεται στο σχ. 118.1 και έχει ρυθμιστεί για παρακολούθηση τιμολογίων. Η στήλη ΣΤ περιέχει έναν τύπο που αφαιρεί την ημερομηνία στη στήλη Ε από την ημερομηνία της στήλης Δ. Ένας αρνητικός αριθμός στη στήλη ΣΤ σημαίνει ότι η πληρωμή έχει καθυστερήσει. Το φύλλο χρησιμοποιεί επώνυμες περιοχές που αντιστοιχούν στις ετικέτες στη σειρά 1.
Ρύζι. 118.1. Μια αρνητική τιμή στη στήλη F υποδηλώνει καθυστερημένη πληρωμή Άθροισμα μόνο αρνητικές τιμές
Ο παρακάτω τύπος επιστρέφει το άθροισμα των αρνητικών τιμών στη στήλη F. Με άλλα λόγια, επιστρέφει τον συνολικό αριθμό ημερών καθυστέρησης για όλους τους λογαριασμούς (για τον πίνακα στο Σχήμα 118.1, ο τύπος θα επέστρεφε -63):
=SUMIF(Διαφορά;" .
Λειτουργία SUMIFμπορεί να πάρει τρία επιχειρήματα. Επειδή παραλείπετε το τρίτο όρισμα, το δεύτερο όρισμα (") αναφέρεται σε τιμές εύρους Διαφορά.
Άθροιση τιμών με βάση διαφορετικά εύρη
Ο ακόλουθος τύπος επιστρέφει το άθροισμα των ληξιπρόθεσμων ποσών τιμολογίων (στη στήλη C): =SUMIF(διαφορά;" . Αυτός ο τύπος χρησιμοποιεί τιμές εύρους Διαφοράγια να προσδιορίσετε εάν οι αντίστοιχες τιμές εύρους συμβάλλουν στο άθροισμα Αθροισμα.
Αθροιστικές τιμές με βάση τη σύγκριση κειμένου
Ο ακόλουθος τύπος επιστρέφει το συνολικό ποσό τιμολογίου για το γραφείο του Όρεγκον: =SUMIF(Office,"=Oregon",Sum) .
Η χρήση του ίσου είναι προαιρετική. Ο παρακάτω τύπος δίνει το ίδιο αποτέλεσμα:
Στο σύνολο των τιμολογίων για όλα τα γραφεία εκτός του Όρεγκον, εισαγάγετε τον ακόλουθο τύπο:
=SUMIF(Office,"Oregon",Sum) .
Αθροιστικές τιμές με βάση τη σύγκριση ημερομηνιών
Ο ακόλουθος τύπος επιστρέφει το συνολικό ποσό των τιμολογίων που οφείλονται στις ή μετά τις 10 Μαΐου 2010:
=SUMIF(Date;">="&DATE(2010:5;10);Sum) .
Σημειώστε ότι το δεύτερο όρισμα στη συνάρτηση SUMIFείναι έκφραση. Η έκφραση χρησιμοποιεί συνάρτηση ΗΜΕΡΟΜΗΝΙΑ ΤΗΣ, το οποίο επιστρέφει την ημερομηνία. Επιπλέον, ο τελεστής σύγκρισης που περικλείεται σε εισαγωγικά συνδέεται (χρησιμοποιώντας τον τελεστή & ) με το αποτέλεσμα της συνάρτησης ΗΜΕΡΟΜΗΝΙΑ ΤΗΣ.
Ο ακόλουθος τύπος επιστρέφει το συνολικό ποσό των τιμολογίων που δεν είναι ακόμη οφειλόμενα (συμπεριλαμβανομένων των σημερινών τιμολογίων):
=SUMIF(Ημερομηνία;">="&TODAY();Άθροισμα) .
Η οξεία στοματίτιδα στους ενήλικες είναι μια ασθένεια ιογενούς αιτιολογίας που εμφανίζεται τόσο σε ενήλικες όσο και σε παιδιά. Πρόσφατα, η οξεία ερπητική στοματίτιδα θεωρείται ως εκδήλωση πρωτοπαθούς λοίμωξης από έρπητα με τον ιό του απλού έρπητα στη στοματική κοιλότητα. Η περίοδος επώασης της νόσου διαρκεί 1-4 ημέρες. Η πρόδρομη περίοδος χαρακτηρίζεται από αύξηση των υπογνάθιων (σε σοβαρές περιπτώσεις, αυχενικών) λεμφαδένων. Η θερμοκρασία του σώματος αυξάνεται στους 37-41 ° C, σημειώνεται αδυναμία, ωχρότητα του δέρματος, πονοκέφαλος και άλλα συμπτώματα.
Οι συναρτήσεις SUMIF και SUMIFS σας βοηθούν να υπολογίσετε το άθροισμα των τιμών σε μια στήλη που ταιριάζει με τα καθορισμένα κριτήρια.
Για παράδειγμα, λάβετε υπόψη τον παρακάτω πίνακα. Υποδεικνύει το όνομα και το φύλο του μαθητή, σε ποια τάξη σπουδάζει και τη μέση βαθμολογία. Χρησιμοποιώντας τη συνάρτηση SUMIF, λύνουμε το ακόλουθο πρόβλημα: υπολογίζουμε το άθροισμα των τιμών από τη στήλη "Μέση βαθμολογία" για όλα τα αγόρια και για όλα τα κορίτσια. Η εργασία για τη συνάρτηση SUMIFS είναι η εξής: να υπολογιστεί το άθροισμα της μέσης βαθμολογίας για αγόρια και κορίτσια που φοιτούν στην 8η και την 9η τάξη ξεχωριστά.
Σκεφτείτε επιχειρήματα για το SUMIF.
SUMIF(εύρος, κριτήριο, [εύρος άθροισης])
Εύρος - εδώ πρέπει να καθορίσετε τη στήλη στην οποία θα συγκριθούν τα δεδομένα με τα καθορισμένα κριτήρια. Στο παράδειγμα, αυτό είναι το εύρος D2:D26 .
Κριτήρια - εδώ πρέπει να επιλέξετε μία τιμή από το προηγουμένως καθορισμένο εύρος. Θα απαριθμήσουμε πρώτα τα αγόρια και μετά τα κορίτσια.
Αθροιστικό εύροςείναι η στήλη της οποίας οι τιμές πρέπει να αθροιστούν εάν ανήκουν στα επιλεγμένα κριτήρια. Για παράδειγμα, εάν για το επιλεγμένο εύρος D2:D26 καθορίζεται το Κριτήριο "αγόρι", τότε στη στήλη "Μέση βαθμολογία": C2:C26, θα συνοψιστούν όλες οι τιμές που σχετίζονται με αγόρια.
Παράδειγμα συνάρτησης SUMIF.
Επιλέξτε το κελί G4 και βάλτε "=". Γράφουμε SUMIF σε αυτό και ανοίγουμε την αγκύλη "(". Ως εύρος από το οποίο θα επιλεγούν οι τιμές, επιλέξτε D2: D26. Ως κριτήριο, πρέπει να επιλέξετε μία από τις τιμέςστο προηγούμενο Το έχουμε "αγόρι", οπότε επιλέγουμε το κελί D3 Το εύρος άθροισης είναι η στήλη στην οποία υποδεικνύεται "Μέση βαθμολογία"... Επιλέξτε κελιά C2: C26... Βάλτε ";" μεταξύ των ορισμάτων, στο στο τέλος, κλείστε την αγκύλη ")". Πατήστε Enter".
Από τον αρχικό πίνακα, θα συνοψιστούν όλες οι τιμές ΣΔΣ που ισχύουν για τα αγόρια.
Τώρα ας τεντώσουμε τον τύπο σε μια στήλη. Φυσικά, μπορείτε να το συνταγογραφήσετε και για κορίτσια, αλλά αν έχετε πολλά διαφορετικά κριτήρια, θα χρειαστεί πολύς χρόνος. Ας διορθώσουμε τα εύρη για τις στήλες C και D έτσι ώστε κατά την αντιγραφή του τύπου, να μην ανιχνεύονται προς τα κάτω.
Επιλέξτε το κελί G4 και στη "Γραμμή τύπων" μετά τα D2, D26, C2, C26 πατήστε το πλήκτρο "F4". Στις διευθύνσεις κελιών, θα εμφανιστούν τα σημάδια "$". Έτσι, κάνουμε τις αναφορές απόλυτες. Για να τεντώσετε τον τύπο σε μια στήλη, σύρετε την κάτω δεξιά γωνία του κελιού, ο δρομέας θα αλλάξει σε μαύρο σύμβολο συν.
Αν σας ενδιαφέρει: απόλυτο, σχετικό, με άλλο φύλλο ή βιβλίο, διαβάστε το άρθρο για αυτό το θέμα.
Στη συνέχεια, κοιτάξτε το "Formula Bar". Οι σωστές τιμές λαμβάνονται ως ορίσματα για το εύρος και το εύρος αθροίσματος: D2:D26, C2:C26. Ως κριτήρια, καθορίστε τη διεύθυνση του επιθυμητού κελιού. Δεδομένου ότι πρέπει να υπολογίσετε τη μέση βαθμολογία για όλα τα κορίτσια, στη στήλη Δ επιλέγουμε τη διεύθυνση οποιουδήποτε κελιού που λέει "κορίτσι": D4.
Τώρα σκεφτείτε ορίσματα για τη συνάρτηση SUMIFSστο Excel.
SUMIFS(εύρος άθροισης, εύρος συνθηκών1, συνθήκη1, εύρος συνθηκών2, συνθήκη2)
Αθροιστικό εύροςείναι η στήλη από την οποία θα επιλεγούν οι τιμές εάν πληρούν τη μία και τη δεύτερη δεδομένη συνθήκη. Στο παράδειγμα, αυτό είναι το C2:C26.
Εύρος συνθηκών 1είναι το πρώτο εύρος σύγκρισης. Πρέπει να επιλέξετε το φύλο του μαθητή, οπότε το εύρος είναι D2:D26 .
Συνθήκη 1 - πρέπει να καθορίσετε την τιμή που θα επιλεγεί από το εύρος συνθηκών1. Μας ενδιαφέρουν τα αγόρια, επομένως θα καθορίσουμε το κελί D3.
Εύρος καταστάσεων 2- το δεύτερο εύρος σύγκρισης. Εδώ θα επιλέξουμε την τάξη στην οποία σπουδάζει το παιδί. Αντίστοιχα, υποδεικνύουμε το εύρος B2:B26.
Το Condition2 είναι μια συγκεκριμένη τιμή που μας ενδιαφέρει από το Condition Range2. Πρώτα θα χρειαστεί να επιλέξετε το κελί στο οποίο υποδεικνύεται ο "βαθμός 8" και μετά "βαθμός 9".
Παράδειγμα συνάρτησης SUMIFS.
Ας λύσουμε το δεύτερο πρόβλημα. Επιλέξτε το κελί H8, βάλτε "=", γράψτε SUMIFS και ανοίξτε την αγκύλη "(". Ως εύρος άθροισης, καθορίστε τη στήλη με "Μέση βαθμολογία": C2: C26. Το εύρος συνθήκης 1 είναι το φύλο του μαθητή: D2: D26, Συνθήκη 1 είναι αγόρια: κελί D3... Το εύρος της συνθήκης 2 είναι κλάση: B2: B26, Συνθήκη 2 είναι κατηγορία 8: κελί B3... Κλείστε την αγκύλη, πατήστε "Enter".
Στη γραμμή Formula, κάντε αμέσως απόλυτες αναφορές στις στήλες: Κατηγορία, Μέση βαθμολογία, Μικρές / Προγραμματιστές.
Τεντώνουμε τον τύπο στα κελιά H8:H11. Τώρα ας καθορίσουμε τα σωστά κελιά ως Condition1 και Condition2.
Το κελί H9 είναι ο μέσος όρος βαθμολογίας για τα αγόρια που είναι στην 9η τάξη. Επομένως, Συνθήκη1 για το πρώτο εύρος "αγόρι" : D3 , Συνθήκη2 για το δεύτερο εύρος "9" : B6 .
Στο κελί H10, λαμβάνουμε υπόψη τη μέση βαθμολογία των κοριτσιών που είναι στην τάξη 8. Κατάσταση 1 - "κορίτσι", D4, Κατάσταση2 - "8", Β3.
Στο κελί H11, λαμβάνουμε υπόψη τη μέση βαθμολογία των κοριτσιών που είναι στην τάξη 9. Κατάσταση 1 - "κορίτσι", D4, Κατάσταση2 - "9", Β2.
Έτσι, υπολογίσαμε το άθροισμα της μέσης βαθμολογίας για αγόρια και κορίτσια που φοιτούν στην 8η και στην 9η τάξη ξεχωριστά.
Ας ελέγξουμε τώρα ότι το αποτέλεσμα είναι σωστό. Ας υπολογίσουμε το άθροισμα της "Μέσης βαθμολογίας" για όλες τις τιμές στον αρχικό πίνακα, για την Εργασία 1 και την Εργασία 2. Το άθροισμα πρέπει να είναι το ίδιο.
Με αυτά τα απλά παραδείγματα, καταλάβαμε πώς λειτουργούν η συνάρτηση SUMIF και η συνάρτηση SUMIFS στο Excel.
Τώρα θα δούμε μια άλλη πολύ συχνά χρησιμοποιούμενη συνάρτηση του Excel - SUMIF.
Ας δούμε ένα παράδειγμα χρήσης της συνάρτησης SUMIF. Ας υποθέσουμε ότι στα δεδομένα πηγής έχουμε έναν πίνακα για παραγγελίες σε διαφορετικές πόλεις. Κάθε γραμμή εμφανίζει τον αριθμό των παραγγελιών σε μια συγκεκριμένη πόλη ορισμένη ημερομηνία(η οποία ημερομηνία δεν μας ενδιαφέρει αυτό το πρόβλημα).
Το καθήκον μας είναι να υπολογίσουμε το άθροισμα όλων των παραγγελιών ανά πόλη για ολόκληρη την περίοδο. Αυτή η εργασία μπορεί να αντιμετωπιστεί εύκολα από τη συνάρτηση Excel - SUMIF. Όπως υποδηλώνει το όνομά της, αυτή η λειτουργία ποσάεπαναλαμβάνει τις τιμές ανπληρούν ορισμένα κριτήρια.
SUMIF(εύρος , κριτήριο , άθροισμα_εύρος)
Στο παράδειγμά μας, πρέπει να συμπληρώσουμε τον δεύτερο πίνακα στα δεξιά - τη στήλη για το ποσό των παραγγελιών ανά πόλη. Όπως μπορείτε να δείτε από τη σύνταξη της συνάρτησης SUMIF, χρειαζόμαστε τρία ορίσματα.
εύρος - αυτό είναι το εύρος σύγκρισης, δηλαδή ο πίνακας στον οποίο θα συγκριθούν με τα κριτήρια. Στο παράδειγμά μας αυτό A5: A504 , για να μην μετακινείται το εύρος μας όταν σύρουμε τον τύπο, θα το κάνουμε απόλυτο βάζοντας το σύμβολο $. Παίρνουμε -$A$5:$A$504
κριτήριο είναι ένα όρισμα, το οποίο μπορεί να εκφραστεί ως αριθμός, έκφραση, κείμενο κ.λπ., που καθορίζει ποια κελιά θα αθροιστούν. Στο παράδειγμά μας, τα κριτήρια είναι οι πόλεις.
άθροισμα_εύρος - αυτό είναι το εύρος που πρέπει να αθροιστεί εάν τα κριτήρια ταιριάζουν. Στο παράδειγμά μας, αυτό είναι το εύρος: B5:B504 , το οποίο θα μετατρέψουμε επίσης σε απόλυτο $B$ 5:$B$504 για ευκολία
Παράδειγμα χρήσης της συνάρτησης SUMIF
Για να λύσετε το πρόβλημα με αυτό το παράδειγμα, στο κελί F5, εισαγάγετε τον ακόλουθο τύπο:
SUMIF($A$5:$A$504 ;E5 ;$B$5:$B$504 )
Η λογική της συνάρτησης SUMIF είναι η εξής: στην περιοχή $A$5:$A$504 ψάχνοντας για κριτήριο Ε5 (Αγία Πετρούπολη), εάν βρίσκεται η Αγία Πετρούπολη, τότε ο αριθμός των παραγγελιών από αυτήν τη γραμμή αθροίζεται, δηλαδή από το εύρος $B$5:$B$504
Αυτό είναι ένα απλό παράδειγμα χρήσης της συνάρτησης SUMIF, θα το δούμε αργότερα αυτή τη λειτουργίαπερισσότερες λεπτομέρειες με παραδείγματα για τον τρόπο χρήσης αυτής της λειτουργίας.
Ελπίζουμε ότι το άρθρο ήταν χρήσιμο για εσάς. Εάν έχετε οποιεσδήποτε ερωτήσεις, φροντίστε να τις ρωτήσετε.
Θα είμαστε ευγνώμονες αν κάνετε κλικ +1 και/ή μου αρέσειστο κάτω μέρος αυτού του άρθρου ή μοιραστείτε με τους φίλους σας χρησιμοποιώντας τα παρακάτω κουμπιά.
Σας ευχαριστώ για την προσοχή σας.
Κατά την επεξεργασία μεγάλα τραπέζιαΜερικές φορές υπάρχει ανάγκη να ληφθούν σύνολα με βάση δεδομένα που βρίσκονται σε εύρη κελιών που δεν επιτρέπουν τη χρήση συναρτήσεων ενοποίησης άμεσης διευθυνσιοδότησης ή απαιτούν πρόσθετους υπολογισμούς. Το άρθρο περιγράφει διάφορες επιλογές για την κατασκευή μοντέλων στο παράδειγμα του οικονομικού προβλήματος του υπολογισμού των ακαθάριστων εσόδων.
Για ευκολία κατανόησης, θα εξετάσουμε όλα τα παραδείγματα τύπων με βάση το απλούστερο οικονομικό πρόβλημα: τον υπολογισμό των συνολικών εσόδων για γνωστές αξίεςόγκος πωλήσεων και τιμές για αρκετούς αγοραστές.
Το πρόβλημα προϋποθέτει ότι ο αριθμός των αγοραστών και των περιόδων δεν είναι σταθερός. Θέλετε να λάβετε σύνολα για πωλήσεις και έσοδα. Προφανώς, η εφαρμογή μιας απλής συνάρτησης αθροίσματος (SUM) δεν θα λειτουργήσει ακόμη και για τη λήψη συνόλων τόμου. Ομοίως, η χρήση της συνάρτησης SUMPRODUCT «στο μέτωπο» δεν είναι κατάλληλη για τον υπολογισμό των εσόδων, καθώς οι περιοχές πολλαπλασιαστικών κυψελών δεν είναι διασυνδεδεμένες.
Στην πράξη, η λύση τέτοιων προβλημάτων συχνά καταλήγει στη σύνταξη "μη αντιγραφής" τύπων ή απαιτεί τη μετατροπή των τελικών τύπων μετά την προσθήκη δεδομένων (βλ. φύλλο Παράδειγμα 1, κελιά C10, C11). Αυτή η μέθοδος ισχύει μόνο για μικρές ποσότητες δεδομένων και παραβιάζει τις βασικές αρχές της βελτιστοποίησης της εργασίας σε υπολογιστικά φύλλα.
Η δεύτερη τυπική μέθοδος επίλυσης του προβλήματος είναι η δημιουργία πρόσθετων πινάκων με ενδιάμεσους υπολογισμούς.
Αυτή η μέθοδος σάς επιτρέπει να εφαρμόζετε τους ίδιους (αντιγραμμένους) τύπους στα κελιά που προκύπτουν και επίσης δεν απαιτεί αλλαγή των τελικών τύπων κατά την προσθήκη σειρών ή στηλών με δεδομένα. Αυτές οι δύο συνθήκες εξασφαλίζουν τη μέγιστη αξιοπιστία των υπολογισμών κατά την ανάπτυξη και λειτουργία του οικονομικού μοντέλου.
Λάβετε υπόψη ότι το πρόβλημα της ορθότητας των συνολικών τύπων κατά την προσθήκη σειρών επιλύεται προσθέτοντας μια κενή γραμμή υπηρεσίας πριν από τη συνολική σειρά (φύλλο Παράδειγμα 2, γραμμές 6 και 22). Χωρίς κενή συμβολοσειρά, όταν ένας νέος πελάτης προστίθεται στο κάτω μέρος του πίνακα, η συνάρτηση SUM δεν συλλέγει αυτόματα τα νέα δεδομένα. Οι γραμμές εξυπηρέτησης είναι μια τυπική "ασφαλής" τεχνική όταν εργάζεστε με μεταβλητό αριθμό γραμμών δεδομένων. Εάν είναι επιθυμητό, μπορούν να κρυφτούν χρησιμοποιώντας τις τυπικές μεθόδους της διεπαφής του Excel.
Τα μειονεκτήματα της μεθόδου επίλυσης του προβλήματος μέσω της προσθήκης ενδιάμεσων υπολογισμών περιλαμβάνουν:
- Πρόσθετοι μετασχηματισμοί των δεδομένων πηγής, που συχνά είναι δύσκολο να εφαρμοστούν στην πράξη (φανταστείτε ότι στο αρχείο προέλευσης δεν υπάρχουν 3, αλλά 3000 αγοραστές).
- Η πολυπλοκότητα της προσθήκης πληροφοριών - ένας νέος αγοραστής πρέπει να προστεθεί όχι σε έναν, αλλά σε τρεις πίνακες.
Ας προσπαθήσουμε να λύσουμε το πρόβλημα χωρίς τη χρήση ενδιάμεσων πινάκων, αλλά διατηρώντας τη δυνατότητα αντιγραφής και την ορθότητα των τελικών τύπων κατά την προσθήκη αγοραστών και περιόδων.
Όπως σημειώθηκε παραπάνω, οι τύποι στα κελιά C10:C11 επιλύουν σωστά το πρόβλημα, αλλά περιπλέκουν τη δουλειά με το μοντέλο. Θέλω απλώς να επιστήσω την προσοχή στη χρήση της συνάρτησης SUM με πρόσθετους υπολογισμούς στο κελί C11.
Οι υπόλοιποι τύποι στην περιοχή D10:E11 είναι επίσης σωστές και ταυτόχρονα συμμορφώνονται πλήρως με τις απαιτήσεις του προβλήματος. Ας τα εξετάσουμε λεπτομερέστερα.
Συνολικός όγκος, kg: D10
SUMIF($B3:$B9;$B$3;D3:D9)
Ο τύπος είναι πολύ απλός και προφανής στην κατανόηση. Η άθροιση προκύπτει σύμφωνα με τη συνθήκη που έχει καθοριστεί στο κελί $B$3. Αντί για $B$3, θα μπορούσατε να γράψετε "όγκος, kg" απευθείας στη συνάρτηση. Αφενός, αυτό καθιστά τον τύπο πιο ευανάγνωστο, αλλά από την άλλη είναι λιγότερο ασφαλής, καθώς όταν αλλάζετε την τιμή στα κελιά της κεφαλίδας, για παράδειγμα, από "volume, kg" σε "volume, l", το η λειτουργία δεν θα λειτουργήσει σωστά.
Θα πρέπει επίσης να σημειωθεί ότι η δυνατότητα χρήσης SUMIF είναι συχνά περιορισμένη λόγω του παραδεκτού ελέγχου μόνο με μία προϋπόθεση (μόνο στο Excel 2007, εμφανίστηκε η συνάρτηση SUMIFS). Σε περίπλοκες περιπτώσεις, οι τύποι με επεξεργασία συστοιχιών είναι απαραίτητοι.
Συνολικός όγκος, kg: E10
(=SUM(IF(MOD(ROW(E3:E9)-ROW(E2);2)=1;E3:E9)))
Η ιδέα του τύπου είναι να υπολογιστεί το άθροισμα μόνο σε περιττές σειρές δεδομένων. Ο τύπος λειτουργεί με τον πίνακα κελιών E3:E9 (τα σγουρά άγκιστρα είναι το χαρακτηριστικό). Οι περιττές σειρές ορίζονται εδώ ως υπόλοιπο ίσο με 1 όταν διαιρείται ο τακτικός αριθμός της σειράς εύρους με το 2. Επιπλέον, για να διατηρηθεί η ακεραιότητα, οι αριθμοί υπολογίζονται από την κεφαλίδα του πίνακα ROW (E2). Αυτό σας επιτρέπει να μεταφέρετε αυτό το τραπέζιεντελώς σε άλλη θέση στο τρέχον ή σε άλλο φύλλο του μοντέλου.
Οι τύποι επεξεργασίας πίνακα εφαρμόζονται πατώντας Ctrl+Shift+Enter
Συνολικά έσοδα, τρίψιμο: D11
SUMPRODUCT((D3:D9)*(D4:D10)*($B3:$B9=$B$3))
Μία από τις μη τυπικές χρήσεις της λειτουργίας SUMPRODUCT. Η ιδέα είναι να πολλαπλασιάσουμε τρεις πίνακες αριθμών. Ο πρώτος πίνακας είναι οι όγκοι, ο δεύτερος είναι οι τιμές και ο τρίτος είναι ένα έναυσμα που πολλαπλασιάζει τα περιττά δεδομένα με το μηδέν.
Ας μετατρέψουμε τον τύπο επιλέγοντας το κείμενο σε αγκύλες και πατώντας F9, παίρνουμε:
SUMPRODUCT((400;0.06;500;0.15;600;0.14;0)*(0.06;500;0.15;600;0.14;0;1500)*(TRUE;FALSE ;TRUE;FALSE;TRUE;FALSE;FALSE)
Ας το θέσουμε με πιο κατανοητό τρόπο:
| 400 | 0,06 | ΑΛΗΘΗΣ | =24 |
| 0,06 | 500 | ΨΕΜΑ | =0 |
| 500 | 0,15 | ΑΛΗΘΗΣ | 75 |
| 0,15 | 600 | ΨΕΜΑ | =0 |
| 600 | 0,14 | ΑΛΗΘΗΣ | 84 |
| 0,14 | 0 | ΨΕΜΑ | =0 |
| 0 | 1500 | ΨΕΜΑ | =0 |
Κατά τον πολλαπλασιασμό πινάκων, η λογική τιμή FALSE μετατρέπεται σε 0 και TRUE σε 1.
Συνολικά έσοδα, τρίψιμο: E11
(=SUM(IF(MOD(ROW(E3:E9)-ROW(E2);2)=1;(E3:E9)*(E4:E10))))
Ο τύπος είναι πολύ παρόμοιος με τον τύπο για τον υπολογισμό των συνολικών όγκων, ο οποίος βρίσκεται στο κελί E10 (βλ. παραπάνω), μόνο που υπάρχει ένας επιπλέον πολλαπλασιασμός με το εύρος με μετατόπιση προς τα κάτω (E4:E10) - ας το ονομάσουμε υπό όρους "εύρος τιμών ", αν και αυτό δεν είναι απολύτως σωστό ως προς το νόημα. Κατά την επεξεργασία ενός πίνακα, τη στιγμή που η συνάρτηση υπό όρους καθορίζει ότι η συμβολοσειρά περιέχει όγκους, λαμβάνει χώρα ένας επιπλέον πολλαπλασιασμός από ένα κελί με τον ίδιο σειριακό αριθμό στον πίνακα του, αλλά από το εύρος τιμών. Πρέπει να σημειωθεί ότι όταν χρησιμοποιούνται πολλαπλοί πίνακες σε έναν τύπο, πρέπει να έχουν την ίδια διάσταση. Εξαιτίας αυτού, οι περιοχές επεξεργασίας στους τύπους "σταματούν" στη γραμμή 10, κάτι που στην πραγματικότητα δεν είναι πολύ σωστό. Στο παράδειγμά μας, αυτό δεν οδηγεί σε σφάλματα, αλλά σε πρακτική δουλειάσυνιστάται η αποφυγή τέτοιων καταστάσεων. Η απλούστερη λύση εδώ μπορεί να είναι να προσθέσετε μια άλλη κενή γραμμή πριν από τα σύνολα.