Η συνάρτηση SUMIF χρησιμοποιείται όταν θέλετε να αθροίσετε τις τιμές σε ένα εύρος που πληρούν ένα καθορισμένο κριτήριο.

Περιγραφή Λειτουργίας

Η συνάρτηση SUMIF χρησιμοποιείται όταν θέλετε να αθροίσετε τις τιμές σε ένα εύρος που πληρούν ένα καθορισμένο κριτήριο. Για παράδειγμα, σε μια στήλη αριθμών, θέλετε να αθροίσετε μόνο τιμές μεγαλύτερες από 5. Μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο για να το κάνετε αυτό:
=SUMIF(B2:B25,">5") αυτό το παράδειγμαΟι αθροιστικές τιμές ελέγχονται για συμμόρφωση με το κριτήριο. Εάν είναι απαραίτητο, η συνθήκη μπορεί να εφαρμοστεί σε ένα εύρος και να αθροιστούν οι αντίστοιχες τιμές από ένα άλλο εύρος. Για παράδειγμα, ο τύπος:
=SUMIF(B2:B5, "Jan", C2:C5) αθροίζει μόνο εκείνες τις τιμές στην περιοχή C2:C5 για τις οποίες οι αντίστοιχες τιμές στο εύρος B2:B5 είναι "Jan".
Ένα άλλο παράδειγμα χρήσης της συνάρτησης φαίνεται στο παρακάτω σχήμα:

Ένα παράδειγμα χρήσης του SUMIF για τον υπολογισμό του κέρδους με μια συνθήκη

Σύνταξη

=SUMIF(εύρος, συνθήκη, [άθρο_εύρος])

Επιχειρήματα

κατάσταση εμβέλειας άθροισμα_εύρος

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

Απαιτούμενο επιχείρημα.Μια συνθήκη με τη μορφή αριθμού, έκφρασης, αναφοράς κελιού, κειμένου ή συνάρτησης που καθορίζει ποια κελιά θα αθροιστούν. Για παράδειγμα, μια συνθήκη μπορεί να αναπαρασταθεί ως: 32, ">32", B5, "32", "μήλα" ή TODAY().

Σπουδαίος!Όλες οι συνθήκες και προϋποθέσεις κειμένου με λογικό και μαθηματικά σημάδιαπρέπει να περικλείεται σε διπλά εισαγωγικά (“). Εάν η συνθήκη είναι αριθμός, δεν χρειάζεται να χρησιμοποιήσετε εισαγωγικά.

Προαιρετικό επιχείρημα.Κελιά των οποίων οι τιμές αθροίζονται εάν διαφέρουν από τα κελιά που καθορίζονται ως εύρος. Εάν το όρισμα sum_range παραλειφθεί, Microsoft Excelαθροίζει τα κελιά που καθορίζονται στο όρισμα εύρους (τα ίδια κελιά στα οποία ισχύει η συνθήκη).

Παρατηρήσεις

• Μπορείτε να χρησιμοποιήσετε χαρακτήρες μπαλαντέρ στο όρισμα συνθήκης: ερωτηματικό (?) και αστερίσκο (*). Ένα ερωτηματικό ταιριάζει με οποιονδήποτε μεμονωμένο χαρακτήρα και ένας αστερίσκος ταιριάζει με οποιαδήποτε ακολουθία χαρακτήρων. Εάν θέλετε να αναζητήσετε απευθείας ένα ερωτηματικό (ή έναν αστερίσκο), πρέπει να τοποθετήσετε πριν από αυτό μια περισπωμένη (~).
• Η συνάρτηση SUMIF επιστρέφει λανθασμένα αποτελέσματα όταν χρησιμοποιείται για την αντιστοίχιση συμβολοσειρών άνω των 255 χαρακτήρων με το #VALUE!.
• Το όρισμα sum_range ενδέχεται να μην έχει το ίδιο μέγεθος με το όρισμα εύρους. Κατά τον καθορισμό των πραγματικών κελιών που θα αθροιστούν, το κελί πάνω αριστερά του ορίσματος sum_range χρησιμοποιείται ως αρχικό κελί και, στη συνέχεια, αθροίζονται τα κελιά του τμήματος της περιοχής που ταιριάζει με το μέγεθος του ορίσματος εύρους.

Λειτουργία SUMIF. Η πρώτη από τις τρεις φάλαινες του Excel

Ξεκινάω την ανασκόπηση των κύριων εργαλείων του αγαπημένου μας προγράμματος από τη Microsoft. Και φυσικά, στην αρχή θέλω να μιλήσω για τη συνάρτηση που χρησιμοποιώ πιο συχνά («φόρμουλα»). Πιο συγκεκριμένα, η συνάρτηση SUMMESLI. Αν δεν έχετε ιδέα τι είναι και πώς να το χρησιμοποιήσετε - σας ζηλεύω! Για μένα ήταν μια πραγματική ανακάλυψη.

Έπρεπε να συνοψίσετε δεδομένα για υπαλλήλους ή πελάτες από έναν μεγάλο πίνακα, να επιλέξετε πόσα έσοδα ήταν για τη μία ή την άλλη ονοματολογία; Φιλτράρατε με βάση το επίθετο/θέση και μετά εισαγάγατε τους αριθμούς με το χέρι σε ξεχωριστά κελιά; Ίσως σε μια αριθμομηχανή; Τι γίνεται αν υπάρχουν περισσότερες από χίλιες σειρές; Πώς να υπολογίσετε γρήγορα;

Εδώ είναι που το SUMMESLI είναι χρήσιμο!

Εργασία 1. Υπάρχουν στατιστικά στοιχεία για αγαθά, πόλεις και ποιοι δείκτες επιτεύχθηκαν για αυτές τις θέσεις. Είναι απαραίτητο να υπολογιστεί:Πόσο πωλήθηκε το προϊόν 1;

Πριν προχωρήσουμε στη λύση του 1ου προβλήματος, ας αναλύσουμε από τι αποτελείται η συνάρτηση SUMIF:

• Εύρος.Το εύρος που περιέχει τους όρους αναζήτησης. Απαιτείται συμπλήρωση. Για την 1η εργασία, η στήλη Προϊόν.
• Κριτήριο.Μπορεί να συμπληρωθεί με έναν αριθμό (85), μια έκφραση (">85"), μια αναφορά κελιού (B1), μια συνάρτηση (σήμερα()). Ορίζει την συνθήκη με την οποία αθροίζεται (!). Όλες οι συνθήκες κειμένου περικλείονται σε εισαγωγικά ( « ) ">85". Απαιτείται συμπλήρωση. Για την 1η στήλη εργασίας =Προϊόν1
• Sum_range.Κελιά προς άθροιση εάν είναι διαφορετικά από τα κελιά στο εύρος. Για την 1η εργασία, η στήλη Έσοδα.

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

Μην ξεχάσετε να ελέγξετε το Μέτρησε; Σωστά? Εξαιρετική!

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

Και επίσης για να μετρήσετε τον αριθμό των επαναλήψεων μιας συγκεκριμένης θέσης, χρησιμοποιήστε τις συναρτήσεις COUNTIF (COUNTIFS)

Είναι ακόμα πιο εύκολο να λυθεί από τα δύο προηγούμενα.

Μέρος 2ο

Τι είναι το SUMMESLI;

Πιθανώς όλοι όσοι εργάζονται αρκετά συχνά στο Excel γνωρίζουν για την ύπαρξη μιας τέτοιας συνάρτησης όπως το SUMIF, η οποία σας επιτρέπει να αθροίζετε μόνο αυτές τις τιμές​​από ένα σύνολο δεδομένων που ικανοποιούν ορισμένα μόνοςκατάσταση. Έχω ήδη δώσει ένα παράδειγμα δεδομένων που μπορούν να αναλυθούν με αυτόν τον τρόπο:

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

=SUMIF(Πίνακας 1 [Προϊόν]; "Yacht" ;Πίνακας 1 [Αριθμός]) =SUMIF(C2 : C40 ; "Yacht" ; D2 : D40 )

Πρόβλημα

Ωστόσο, με μια πιο σύνθετη ανάλυση, η συνάρτηση SUMIF δεν θα αντεπεξέλθει για έναν πολύ προφανή λόγο - επιλέγει δεδομένα μόνο ένα προς ένακατάσταση. Δεν υπάρχουν επιλογές. Έτσι, για παράδειγμα, εάν θέλετε να μάθετε πόσα γιοτ πουλήθηκαν στη Βόρεια Αμερική, τότε η λειτουργία SUMIF δεν θα σας βοηθήσει.

Ωστόσο, υπάρχουν τουλάχιστον τρεις εναλλακτικές λύσεις:

1. Χρησιμοποιήστε τύπους πίνακα για να συνοψίσετε δεδομένα με πολλαπλά κριτήρια.
2. Χρησιμοποιήστε τη λειτουργία SUMPRODUCT
3. Χρησιμοποιήστε τη συνάρτηση SUMIFS

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

Λύση

Λοιπόν, ας χρησιμοποιήσουμε την τρίτη από τις προτεινόμενες λύσεις και ας γράψουμε τον ακόλουθο τύπο:

=SUMIFS(Πίνακας 1 [Ποσότητα];Πίνακας 1 [Περιοχή]; "North America" ​​· Πίνακας 1 [Προϊόν]; "Σκάφος αναψυχής")

Δώστε προσοχή στο πώς αντιστοιχούν τα εύρη δεδομένων και τα ορίσματα αυτής της συνάρτησης:

Πώς λειτουργεί το SUMIFS;

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

Για να θυμάστε καλύτερα τη σειρά των ορισμάτων αυτής της συνάρτησης, δείτε το σχήμα:

Δώρο

Και όχι ένα, αλλά δύο!

Πρώτον, στο Excel 2007 και μεταγενέστερα, υπάρχει μια μικρή οικογένεια παρόμοιων συναρτήσεων, συμπεριλαμβανομένων των AVERAGEIFS (για τον υπολογισμό των μέσων όρων) και του COUNTIFS (για την καταμέτρηση του αριθμού των τιμών).

Δεύτερον, οι λειτουργίες αυτής της οικογένειας μπορούν να λειτουργήσουν με σχεσιακούς τελεστές (>,<, = и т.п.) и знаками подстановки, такими как «*» — любое количество любых символов, и «?» — один любой символ. Так, например, чтобы подсчитать количество самолетов, проданных в Северной и Южной Америке, можно воспользоваться такой формулой:

=SUMIFS(Πίνακας 1 [Ποσότητα];Πίνακας 1 [Περιοχή]; "*Αμερική"; Πίνακας 1 [Προϊόν]; "Αεροπλάνο")

Ας προσθέσουμε τις τιμές που βρίσκονται στις σειρές, τα πεδία των οποίων ικανοποιούν δύο κριτήρια ταυτόχρονα (Συνθήκη ΚΑΙ). Εξετάστε τα κριτήρια κειμένου, τα αριθμητικά κριτήρια και τα κριτήρια ημερομηνίας. Ας αναλύσουμε τη συνάρτηση SUMIFS( ), η αγγλική έκδοση του SUMIFS().

Ως πίνακας πηγής, ας πάρουμε έναν πίνακα με δύο στήλες (πεδία): κείμενο " Φρούτα"και αριθμητικα" Ποσότητα σε απόθεμα» (Δείτε παράδειγμα αρχείου).

Εργασία 1 (1 κριτήριο κειμένου και 1 αριθμητικό κριτήριο)

Ας βρούμε τον αριθμό των κιβωτίων εμπορευμάτων με ένα ορισμένο καρπός ΚΑΙ, οι οποίες Υπόλοιπα κουτιά σε απόθεμαόχι λιγότερο από το ελάχιστο. Για παράδειγμα, ο αριθμός των κουτιών με εμπορεύματα ροδάκινα (κύτταρο ρε 2 ), που έχουν υπόλοιπο κουτιών σε απόθεμα >=6 (κύτταρο μι 2 ) . Θα πρέπει να λάβουμε το αποτέλεσμα 64. Ο υπολογισμός μπορεί να πραγματοποιηθεί με διάφορους τύπους, εδώ είναι μερικοί (δείτε το παράδειγμα αρχείου Κείμενο και αριθμός φύλλου):

1. =SUMIFS(B2:B13;A2:A13;D2;B2:B13;">="&E2)

Σύνταξη συνάρτησης: SUMIFS(άθροισμα_διάστημα, συνθήκη_διάστημα1, συνθήκη1, διάστημα_συνθήκης2, συνθήκη2…)

• B2:B13 Άθροισμα_διάστημα- κελιά για άθροιση, συμπεριλαμβανομένων ονομάτων, πινάκων ή παραπομπών που περιέχουν αριθμούς. Οι κενές τιμές και το κείμενο αγνοούνται.
• A2:A13Και B2:B13 Condition_interval1; condition_interval2; …είναι από 1 έως 127 εύρη στα οποία ελέγχεται η αντίστοιχη συνθήκη.
• Δ2Και ">="&E2 Condition1; συνθήκη2; …είναι από 1 έως 127 συνθήκες με τη μορφή αριθμού, έκφρασης, αναφοράς κελιού ή κειμένου που καθορίζουν ποια κελιά θα αθροιστούν.

Η σειρά των ορισμάτων είναι διαφορετική στις συναρτήσεις SUMIFS() και SUMIFS(). Το όρισμα SUMIFS(). sum_intervalείναι το πρώτο όρισμα και στη SUMIF() είναι το τρίτο. Κατά την αντιγραφή και την επεξεργασία αυτών των παρόμοιων συναρτήσεων, πρέπει να λαμβάνεται μέριμνα ώστε να διασφαλίζεται ότι τα ορίσματα καθορίζονται με τη σωστή σειρά.

2. άλλη επιλογή = SUMPRODUCT((A2:A13=D2)*(B2:B13);--(B2:B13>=E2))
Ας ρίξουμε μια πιο προσεκτική ματιά στη χρήση της συνάρτησης SUMPRODUCT():

• Το αποτέλεσμα του A2:A13=D2 είναι ένας πίνακας (FALSE:FALSE:FALSE:FALSE:TRUE:TRUE:TRUE:FALSE:FALSE:FALSE:FALSE:FALSE:FALSE) ΕΝΑ κριτήριο, δηλ. λέξη ροδάκινα. Μπορείτε να δείτε τον πίνακα επισημαίνοντας στο A2:A13=D2 και στη συνέχεια πατώντας ;
• Το αποτέλεσμα του υπολογισμού του B2:B13 είναι (3:5:11:98:4:8:56:2:4:6:10:11), δηλ. απλώς τιμές από μια στήλη σι ;
• Το αποτέλεσμα του πολλαπλασιασμού των πινάκων κατά στοιχεία (A2:A13=D2)*(B2:B13) είναι (0:0:0:0:4:8:56:0:0:0:0:0). Πολλαπλασιάζοντας έναν αριθμό με FALSE προκύπτει 0. και στην τιμή TRUE (=1) προκύπτει ο ίδιος ο αριθμός.
• Ας αναλύσουμε τη δεύτερη συνθήκη: Το αποτέλεσμα του υπολογισμού --(B2:B13>=E2) είναι ένας πίνακας (0:0:1:1:0:1:1:0:0:1:1:1). Τιμές στη στήλη " Αριθμός κιβωτίων σε απόθεμα” που ικανοποιούν το κριτήριο >=E2 (δηλ. >=6) αντιστοιχούν σε 1.
• Επιπλέον, η συνάρτηση SUMPRODUCT() πολλαπλασιάζει τα στοιχεία του πίνακα σε ζεύγη και αθροίζει τα προκύπτοντα γινόμενα. Παίρνουμε - 64.

3. Μια άλλη περίπτωση χρήσης για τη συνάρτηση SUMPRODUCT() είναι ο τύπος =SUMPRODUCT((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)).

Εάν είναι απαραίτητο, οι ημερομηνίες μπορούν να εισαχθούν απευθείας στον τύπο = SUMIFS(B6:B17;A6:A17;">=15.01.2010";A6:A17;"<=06.03.2010")

Για να εμφανίσετε τα κριτήρια επιλογής σε μια γραμμή κειμένου, χρησιμοποιήστε τον τύπο ="Πωλήσεις για την περίοδο από "&TEXT(D6;"ηη.ΜΜ.εε")&" έως "&TEXT(E6;"ηη.ΜΜ.εε")

χρησιμοποιείται στην τελευταία φόρμουλα.

Εργασία 4 (Μήνας)

Τροποποιούμε ελαφρώς την κατάσταση της προηγούμενης εργασίας: βρείτε τις συνολικές πωλήσεις για τον μήνα (δείτε το αρχείο παραδείγματος Μήνας Φύλλων).

Οι τύποι κατασκευάζονται παρόμοια με την εργασία 3, αλλά ο χρήστης δεν εισάγει 2 ημερομηνίες, αλλά το όνομα του μήνα (υποτίθεται ότι τα δεδομένα στον πίνακα είναι εντός 1 έτους).

Για να λύσετε την 3η εργασία, ο πίνακας με το διαμορφωμένο αυτόματο φίλτρο μοιάζει με αυτό (δείτε το αρχείο παραδείγματος Φύλλο 2 Ημερομηνίες).

Ο πίνακας πρέπει πρώτα να μετατραπεί και να περιλαμβάνει τη σειρά Σύνολο.

Καλησπέρα, αγαπητοί συνδρομητές και επισκέπτες του ιστότοπου του ιστολογίου. Πιο πρόσφατα, καταλάβαμε τον τύπο VLOOKUP και αμέσως μετά αποφάσισα να γράψω για μια άλλη πολύ χρήσιμη συνάρτηση του Excel - SUMIF. Και οι δύο αυτές λειτουργίες είναι σε θέση να «συνδέσουν» δεδομένα από διαφορετικές πηγές (πίνακες) ανά πεδίο κλειδιού και υπό ορισμένες συνθήκες είναι εναλλάξιμες. Ταυτόχρονα, υπάρχουν σημαντικές διαφορές ως προς τον σκοπό και τη χρήση τους.

Εάν ο σκοπός του VLOOKUP είναι απλώς να "ανασύρει" δεδομένα από μια θέση στο Excel σε άλλη, τότε το SUMIF χρησιμοποιείται για να αθροίσει τα αριθμητικά δεδομένα σύμφωνα με ένα δεδομένο κριτήριο. Ο κύριος σκοπός αυτού του τύπου είναι να αθροίσει τα δεδομένα σύμφωνα με κάποια συνθήκη. Αυτό λέει στο εγχειρίδιο. Ωστόσο, αυτό το εργαλείο είναι χρήσιμο και για άλλους σκοπούς. Για παράδειγμα, χρησιμοποιώ συχνά τον τύπο SUMIF αντί για VLOOKUP επειδή δεν πετάει #N/A σφάλματα όταν δεν μπορεί να βρει κάτι. Αυτό κάνει τη δουλειά πολύ πιο εύκολη και γρήγορη.

Η συνάρτηση SUMIF μπορεί να προσαρμοστεί με επιτυχία για την επίλυση μεγάλης ποικιλίας προβλημάτων. Επομένως, σε αυτό το άρθρο θα εξετάσουμε όχι 1 (ένα), αλλά έως και 2 (δύο) παραδείγματα. Το πρώτο θα συσχετιστεί με την άθροιση σύμφωνα με ένα δεδομένο κριτήριο, το δεύτερο - με τα δεδομένα που "ανασύρονται", δηλαδή ως εναλλακτική λύση στο VLOOKUP.

Παράδειγμα άθροισης χρησιμοποιώντας τη συνάρτηση SUMIF

Ο πίνακας δείχνει τις θέσεις, τις ποσότητες τους, καθώς και τις θέσεις που ανήκουν σε μια συγκεκριμένη ομάδα αγαθών (πρώτη στήλη). Προς το παρόν, ας εξετάσουμε την απλοποιημένη χρήση του SUMIF, όταν πρέπει να υπολογίσουμε το άθροισμα μόνο για εκείνες τις θέσεις, οι τιμές για τις οποίες πληρούν μια συγκεκριμένη προϋπόθεση. Για παράδειγμα, θέλουμε να μάθουμε πόσες κορυφαίες θέσεις πουλήθηκαν, π.χ. αυτών των οποίων η αξία υπερβαίνει τις 70 μονάδες. Δεν είναι πολύ βολικό και γρήγορο να αναζητήσετε τέτοια αγαθά με τα μάτια σας και στη συνέχεια να τα συνοψίσετε χειροκίνητα, επομένως η λειτουργία SUMIF είναι πολύ κατάλληλη εδώ.

Πρώτα απ 'όλα, επιλέξτε το κελί όπου θα υπολογιστεί το ποσό. Στη συνέχεια, καλούμε τον Οδηγό συνάρτησης. Αυτό είναι ένα εικονίδιο fxστη γραμμή τύπων. Στη συνέχεια, αναζητήστε τη συνάρτηση SUMIF στη λίστα και κάντε κλικ σε αυτήν. Ανοίγει ένα πλαίσιο διαλόγου όπου, για να επιλύσετε μια συγκεκριμένη εργασία, πρέπει να συμπληρώσετε μόνο δύο (πρώτα) πεδία από τα τρία προτεινόμενα.

Γι' αυτό ονόμασα αυτό το παράδειγμα απλοποιημένο. Γιατί 2 (δύο) στα 3 (τρία); Επειδή το κριτήριό μας βρίσκεται στο ίδιο το εύρος άθροισης.

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

Το πεδίο Κριτήρια καθορίζει την συνθήκη με την οποία θα επιλεγεί ο τύπος. Στην περίπτωσή μας, υποδεικνύουμε "> 70". Εάν δεν βάλετε εισαγωγικά, τότε θα κληρωθούν μόνα τους. Αλλά σε γενικές γραμμές, ένα τέτοιο κριτήριο πρέπει να είναι σε εισαγωγικά.

Το τελευταίο πεδίο "Summation_range" δεν είναι συμπληρωμένο, αφού υποδεικνύεται ήδη στο πρώτο πεδίο.

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

Αφού συμπληρώσετε τα απαιτούμενα πεδία στον Οδηγό λειτουργιών, πατήστε το μεγάλο κουμπί «Enter» στο πληκτρολόγιο ή κάντε κλικ στο «OK» στο παράθυρο του Οδηγού. Μετά από αυτήν την ενέργεια, η υπολογιζόμενη τιμή θα πρέπει να εμφανίζεται στη θέση της εισαγόμενης συνάρτησης. Στο παράδειγμά μου, βγήκαν 224 κομμάτια. Δηλαδή, η συνολική αξία των εμπορευμάτων που πωλήθηκαν σε ποσότητα άνω των 70 τεμαχίων ήταν 224 τεμάχια. (αυτό φαίνεται στην κάτω αριστερή γωνία του παραθύρου του Wizard ακόμη και πριν κάνετε κλικ στο "ok"). Αυτό είναι όλο. Αυτό ήταν ένα απλοποιημένο παράδειγμα όπου το κριτήριο και το εύρος άθροισης βρίσκονται στην ίδια θέση.

Τώρα ας δούμε ένα παράδειγμα όπου το κριτήριο δεν ταιριάζει με το εύρος άθροισης. Αυτή η κατάσταση είναι πολύ πιο συνηθισμένη. Εξετάστε τα ίδια δεδομένα υπό όρους. Ας υποθέσουμε ότι πρέπει να μάθουμε το ποσό όχι περισσότερο ή μικρότερο από κάποια αξία και το ποσό μιας συγκεκριμένης ομάδας αγαθών, για παράδειγμα, την ομάδα G.

Για να το κάνετε αυτό, επιλέξτε ξανά το κελί με τον μελλοντικό υπολογισμό του αθροίσματος και καλέστε τον Οδηγό συνάρτησης. Τώρα στο πρώτο παράθυρο υποδεικνύουμε το εύρος, το οποίο περιέχει το κριτήριο με το οποίο θα γίνει η επιλογή για άθροιση, δηλαδή, στην περίπτωσή μας, αυτή είναι μια στήλη με τα ονόματα των ομάδων προϊόντων. Στη συνέχεια, το ίδιο το κριτήριο γράφεται είτε χειροκίνητα, αφήνοντας την καταχώρηση "G group" στο αντίστοιχο πεδίο, είτε απλά δείχνοντας το κελί με το επιθυμητό κριτήριο με το ποντίκι. Το τελευταίο πλαίσιο είναι το εύρος όπου βρίσκονται τα συνοπτικά δεδομένα. Το υποδεικνύουμε με το ποντίκι, αλλά στους λάτρεις της εκτύπωσης δεν απαγορεύεται να το πληκτρολογήσουν χειροκίνητα.

Το αποτέλεσμα θα είναι το άθροισμα των προϊόντων που πωλήθηκαν από την ομάδα G - 153 τεμάχια.

Έτσι, εξετάσαμε πώς να υπολογίσουμε ένα άθροισμα με ένα συγκεκριμένο κριτήριο. Ωστόσο, πιο συχνά προκύπτει πρόβλημα όταν απαιτείται να υπολογιστούν πολλά ποσά για πολλά κριτήρια. Δεν υπάρχει τίποτα πιο εύκολο! Για παράδειγμα, πρέπει να μάθουμε την ποσότητα των αγαθών που πωλούνται για κάθε ομάδα. Δηλαδή, 4 (τέσσερις) τιμές ενδιαφέρουν 4 (τέσσερις) ομάδες (Α, Β, Γ και Δ). Για να γίνει αυτό, συνήθως κάπου στις εκτάσεις του Excel δημιουργείται μια λίστα ομάδων με τη μορφή ξεχωριστής πλάκας. Είναι σαφές ότι τα ονόματα των ομάδων πρέπει να ταιριάζουν ακριβώς με τα ονόματα των ομάδων στον πίνακα προέλευσης. Προσθέστε αμέσως την τελική γραμμή, όπου το άθροισμα είναι ακόμα μηδέν.

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

Τα συμπληρωμένα πεδία του Function Wizard σε έναν τέτοιο υπολογισμό θα μοιάζουν κάπως έτσι.

Όπως μπορείτε να δείτε, για την πρώτη ομάδα Α, η ποσότητα των εμπορευμάτων που πωλήθηκαν ήταν 161 τεμάχια (κάτω αριστερή γωνία του σχήματος). Τώρα πατήστε enter και σύρετε τον τύπο προς τα κάτω.

Όλα τα ποσά έχουν υπολογιστεί και το σύνολο τους είναι 535, το ίδιο με το σύνολο των αρχικών στοιχείων. Έτσι, όλες οι αξίες συνοψίστηκαν, τίποτα δεν χάθηκε. Η τελευταία περίπτωση χρήσης του SUMMESLI είναι αρκετά συνηθισμένη για μένα προσωπικά.

Παράδειγμα χρήσης της συνάρτησης SUMIF για αντιστοίχιση δεδομένων

Όπως έχω επισημάνει περισσότερες από μία φορές, η συνάρτηση SUMIF μπορεί να χρησιμοποιηθεί χρήσιμα για δέσμευση δεδομένων. Πράγματι, αν συνοψίσετε μια τιμή, θα λάβετε αυτή την τιμή. Αυτή η αριθμητική ιδιότητα δεν είναι αμαρτία να χρησιμοποιηθεί για καλό. Είναι μάλιστα πιθανό οι σχεδιαστές του Excel να μην γνωρίζουν μια τόσο βάρβαρη χρήση των απογόνων τους.

Εν ολίγοις, το SUMIF προσαρμόζεται εύκολα για δέσμευση δεδομένων ως εναλλακτική της συνάρτησης VLOOKUP. Γιατί να χρησιμοποιήσετε το SUMIF εάν υπάρχει VLOOKUP; Εξηγώ. Πρώτον, το SUMMIF, σε αντίθεση με το VLOOKUP, δεν είναι ευαίσθητο στη μορφή δεδομένων και δεν εκπέμπει σφάλμα εκεί που δεν το περιμένεις. δεύτερον, αντί για σφάλματα λόγω έλλειψης τιμών για ένα δεδομένο κριτήριο, το SUMMIF δίνει 0 (μηδέν), το οποίο σας επιτρέπει να υπολογίσετε τα σύνολα του εύρους με τον τύπο SUMMESLI χωρίς περιττές χειρονομίες. Ωστόσο, υπάρχει ένα μειονέκτημα. Εάν οποιοδήποτε κριτήριο επαναληφθεί στον απαιτούμενο πίνακα, τότε θα συνοψιστούν οι αντίστοιχες τιμές, κάτι που δεν είναι πάντα "pull-up". Καλύτερα να είσαι σε επιφυλακή. Από την άλλη πλευρά, αυτό είναι συχνά αυτό που χρειάζεται - να τραβήξετε τις τιμές σε ένα δεδομένο μέρος και να προσθέσετε τις διπλές θέσεις ταυτόχρονα. Απλά πρέπει να γνωρίζετε τις ιδιότητες της συνάρτησης SUMIF και να τη χρησιμοποιήσετε σύμφωνα με το εγχειρίδιο οδηγιών.

Ας δούμε τώρα ένα παράδειγμα για το πώς η συνάρτηση SUMIF είναι πιο κατάλληλη για ανάσυρση δεδομένων από το VLOOKUP. Αφήστε τα δεδομένα από το παράδειγμά σας να είναι οι πωλήσεις ορισμένων αγαθών για τον Ιανουάριο. Θέλουμε να μάθουμε πώς άλλαξαν τον Φεβρουάριο. Είναι βολικό να κάνετε μια σύγκριση στον ίδιο πίνακα, αφού προσθέσετε μια ακόμη στήλη στα δεξιά και τη συμπληρώσετε με δεδομένα για τον Φεβρουάριο. Κάπου σε ένα άλλο αρχείο Excel υπάρχουν στατιστικά στοιχεία για τον Φεβρουάριο για ολόκληρη τη σειρά, αλλά θέλουμε να αναλύσουμε αυτές τις συγκεκριμένες θέσεις, για τις οποίες πρέπει να τραβήξουμε τις απαραίτητες τιμές στον πίνακά μας από ένα μεγάλο αρχείο με στατιστικά στοιχεία πωλήσεων για όλα τα προϊόντα . Αρχικά, ας προσπαθήσουμε να χρησιμοποιήσουμε τον τύπο VLOOKUP. Ως κριτήριο θα χρησιμοποιήσουμε τον κωδικό προϊόντος. Το αποτέλεσμα είναι στην εικόνα.

Φαίνεται ξεκάθαρα ότι μια θέση δεν τραβήχτηκε προς τα πάνω και αντί για μια αριθμητική τιμή, επιστρέφεται το σφάλμα #N/A. Πιθανότατα, αυτό το προϊόν απλώς δεν πωλήθηκε τον Φεβρουάριο και επομένως δεν βρίσκεται στη βάση δεδομένων για τον Φεβρουάριο. Κατά συνέπεια, το σφάλμα #N/A εμφανίζεται επίσης στο σύνολο. Εάν δεν υπάρχουν πολλές θέσεις, τότε το πρόβλημα δεν είναι μεγάλο, αρκεί να αφαιρέσετε χειροκίνητα το σφάλμα και το ποσό θα υπολογιστεί ξανά σωστά. Ωστόσο, ο αριθμός των γραμμών μπορεί να μετρηθεί σε εκατοντάδες και το να υπολογίζουμε στη χειροκίνητη ρύθμιση δεν είναι η σωστή απόφαση. Τώρα ας χρησιμοποιήσουμε τον τύπο SUMIF αντί για το VLOOKUP.

Το αποτέλεσμα είναι το ίδιο, αλλά αντί για το σφάλμα #N/A SUMMIF, επιστρέφει το μηδέν, το οποίο σας επιτρέπει να υπολογίζετε κανονικά το άθροισμα (ή άλλο δείκτη, για παράδειγμα, τον μέσο όρο) στη συνολική σειρά. Αυτή είναι η κύρια ιδέα γιατί το SUMIF πρέπει μερικές φορές να χρησιμοποιείται αντί για το VLOOKUP. Με μεγάλο αριθμό θέσεων, το αποτέλεσμα θα είναι ακόμα πιο αισθητό.

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

Και εδώ είναι ένα άλλο μάθημα για το SUMIF