Συνάρτηση SUMIF στο Excel και άθροιση με πολλαπλές συνθήκες

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

Σήμερα θα μιλήσουμε για μια από αυτές τις λειτουργίες − SUMMESLIMN.

Εάν ο χρήστης αντιμετωπίζει το καθήκον να συνοψίσει πολλές τιμές, εστιάζοντας σε ορισμένα κριτήρια, τότε είναι απαραίτητο να χρησιμοποιήσει τη συνάρτηση SUMMESLIMN. Ο τύπος που χρησιμοποιεί αυτή τη συνάρτηση παίρνει αυτές τις συνθήκες ως ορίσματα, στη συνέχεια αθροίζει τις τιμές που τις πληρούν και, στη συνέχεια, η τιμή που βρέθηκε εισάγεται στο κελί στο οποίο είναι γραμμένη. 

Λεπτομερής περιγραφή λειτουργίας SUMIFS

Πριν εξετάσετε τη λειτουργία SUMMESLIMN, πρέπει πρώτα να καταλάβετε ποια είναι μια απλούστερη εκδοχή του – ΣΟΥΜΜΕΣΛΙ, αφού σε αυτό βασίζεται η συνάρτηση που εξετάζουμε. Ο καθένας από εμάς είναι πιθανώς ήδη εξοικειωμένος με δύο λειτουργίες που χρησιμοποιούνται συχνά – ΑΘΡΟΙΣΜΑ (πραγματοποιεί την άθροιση των τιμών) και ΑΝ (ελέγχει μια τιμή έναντι μιας καθορισμένης συνθήκης).

Αν τα συνδυάσετε, θα έχετε μια άλλη συνάρτηση - ΣΟΥΜΜΕΣΛΙ, το οποίο ελέγχει τα δεδομένα βάσει κριτηρίων που καθορίζονται από τον χρήστη και αθροίζει μόνο τους αριθμούς που πληρούν αυτά τα κριτήρια. Αν μιλάμε για την αγγλική έκδοση του Excel, τότε αυτή η συνάρτηση ονομάζεται SUMIF. Με απλά λόγια, το όνομα -language είναι μια άμεση μετάφραση του αγγλόφωνου. Αυτή η λειτουργία μπορεί να χρησιμοποιηθεί για διάφορους σκοπούς. Συγκεκριμένα, μπορεί να χρησιμοποιηθεί ως εναλλακτική λύση VPR, δηλαδή γράψε

Η κύρια διαφορά μεταξύ της συνάρτησης SUMMESLIMN  από τη συνήθη λειτουργία ΣΟΥΜΜΕΣΛΙ είναι ότι χρησιμοποιούνται διάφορα κριτήρια. Η σύνταξή του είναι αρκετά περίπλοκη με την πρώτη ματιά, αλλά μετά από πιο προσεκτική εξέταση, αποδεικνύεται ότι η λογική αυτής της συνάρτησης είναι πολύ απλή. Πρώτα πρέπει να επιλέξετε το εύρος όπου θα ελέγχονται τα δεδομένα και, στη συνέχεια, να ορίσετε τις προϋποθέσεις συμμόρφωσης με τις οποίες θα πραγματοποιηθεί η ανάλυση. Και μια τέτοια επέμβαση μπορεί να πραγματοποιηθεί για έναν αρκετά μεγάλο αριθμό συνθηκών.

Η ίδια η σύνταξη είναι:

SUMIFS(εύρος_αθροίσματος, εύρος_συνθήκης1, συνθήκη1, [εύρος_συνθήκης2, συνθήκη2], …)

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

Ας δούμε τα επιχειρήματα πιο αναλυτικά:

1. Sum_range. Αυτό το όρισμα, καθώς και το εύρος της συνθήκης 1 και συνθήκης 1, απαιτείται. Είναι ένα σύνολο κελιών που πρέπει να αθροιστούν.
2. Condition_range1. Αυτό είναι το εύρος όπου θα ελεγχθεί η συνθήκη. Συνδυάζεται με το επόμενο όρισμα – Condition1. Η άθροιση των τιμών που αντιστοιχούν στο κριτήριο πραγματοποιείται εντός των κελιών που καθορίζονται στο προηγούμενο όρισμα.
3. Κατάσταση 1. Αυτό το όρισμα καθορίζει τα κριτήρια που πρέπει να ελέγχονται. Μπορεί να ρυθμιστεί, για παράδειγμα, με αυτόν τον τρόπο: "> 32".
4. Εύρος συνθηκών 2, Συνθήκη 2… Εδώ, οι ακόλουθες συνθήκες ρυθμίζονται με τον ίδιο τρόπο. Εάν πρέπει να καθοριστούν περισσότερες από μερικές συνθήκες, τότε προστίθενται τα ορίσματα Εύρος συνθήκης 3 και συνθήκη 3. Η σύνταξη είναι ίδια για τα ακόλουθα ορίσματα.

Η λειτουργία επιτρέπει τη μέγιστη επεξεργασία έως και 127 ζευγών συνθηκών και περιοχών. 

Μπορείτε να το χρησιμοποιήσετε σε πολλούς τομείς ταυτόχρονα (θα δώσουμε μόνο μερικούς, η λίστα είναι στην πραγματικότητα ακόμη μεγαλύτερη):

1. Λογιστική. Για παράδειγμα, είναι καλό να χρησιμοποιήσετε τη συνάρτηση SUMMESLIMN για να δημιουργήσετε συνοπτικές αναφορές, ανά τρίμηνο για δαπάνες πάνω από ένα συγκεκριμένο ποσό, για παράδειγμα. Ή δημιουργήστε μια αναφορά για ένα προϊόν από μια συγκεκριμένη κατηγορία τιμής.
2. ΔΙΕΥΘΥΝΣΗ ΠΩΛΗΣΕΩΝ. Και εδώ η λειτουργία μπορεί να είναι πολύ χρήσιμη. Για παράδειγμα, αντιμετωπίζουμε το καθήκον να συνοψίσουμε μόνο το κόστος των αγαθών που πωλήθηκαν σε έναν συγκεκριμένο πελάτη σε μια συγκεκριμένη χρονική στιγμή. Και σε μια τέτοια κατάσταση, η λειτουργία SUMMESLIMN μπορεί να φανούν πολύ χρήσιμες.
3. Εκπαίδευση. Θα δώσουμε πιο πρακτικά παραδείγματα από αυτόν τον τομέα σήμερα. Συγκεκριμένα, μπορείτε να το χρησιμοποιήσετε για να λάβετε μια περίληψη των βαθμών των μαθητών. Μπορείτε να επιλέξετε για ένα μόνο μάθημα ή για μεμονωμένες βαθμίδες. Ένα άτομο μπορεί να ορίσει αμέσως πολλά κριτήρια βάσει των οποίων θα επιλεγεί η αξιολόγηση, κάτι που είναι πολύ βολικό και μπορεί να εξοικονομήσει πολύ χρόνο.

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

1. Δυνατότητα καθορισμού πολλαπλών κριτηρίων. Γιατί είναι αυτό πλεονέκτημα; Μπορείτε να χρησιμοποιήσετε την κανονική λειτουργία ΣΟΥΜΜΕΣΛΙ! Και όλα αυτά επειδή είναι βολικό. Δεν χρειάζεται να κάνετε ξεχωριστούς υπολογισμούς για καθένα από τα κριτήρια. Όλες οι ενέργειες μπορούν να προγραμματιστούν εκ των προτέρων, ακόμη και πριν. πώς θα σχηματιστεί ο πίνακας δεδομένων. Αυτό είναι μια εξαιρετική εξοικονόμηση χρόνου.
2. Αυτοματοποίηση. Η σύγχρονη εποχή είναι η εποχή του αυτοματισμού. Μόνο αυτός που ξέρει πώς να αυτοματοποιεί σωστά τη δουλειά του μπορεί να κερδίσει πολλά. Αυτός είναι ο λόγος για τον οποίο η ικανότητα κυριαρχίας του Excel και της συνάρτησης SUMMESLIMN ιδιαίτερα, είναι τόσο σημαντικό για κάθε άτομο που θέλει να χτίσει μια καριέρα. Η γνώση μιας λειτουργίας σάς επιτρέπει να εκτελέσετε πολλές ενέργειες ταυτόχρονα, ως μία. Και εδώ προχωράμε στο επόμενο πλεονέκτημα αυτής της δυνατότητας.
3. Εξοικονόμηση χρόνου. Ακριβώς λόγω του γεγονότος ότι μια λειτουργία εκτελεί πολλές εργασίες ταυτόχρονα.
4. Απλότητα. Παρά το γεγονός ότι η σύνταξη είναι αρκετά βαριά με την πρώτη ματιά λόγω του όγκου της, στην πραγματικότητα, η λογική αυτής της λειτουργίας είναι πολύ απλή. Αρχικά, επιλέγεται ένα εύρος δεδομένων και μετά ένα εύρος τιμών, το οποίο θα ελεγχθεί για συμμόρφωση με μια συγκεκριμένη συνθήκη. Και φυσικά, η ίδια η συνθήκη πρέπει επίσης να προσδιορίζεται. Και έτσι αρκετές φορές. Στην πραγματικότητα, αυτή η συνάρτηση βασίζεται μόνο σε ένα λογικό κατασκεύασμα, το οποίο την κάνει πιο απλή από τη γνωστή VPR παρά το γεγονός ότι μπορεί να χρησιμοποιηθεί για τους ίδιους σκοπούς, λαμβάνοντας επίσης υπόψη έναν μεγαλύτερο αριθμό κριτηρίων. 

Δυνατότητες χρήσης της συνάρτησης SUMIFS

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

1. Μπορείτε να χρησιμοποιήσετε αυτούς τους τύπους τιμών ως προϋποθέσεις για την επιλογή κελιών για να προσθέσετε περαιτέρω τις τιμές που περιέχονται σε αυτά: αριθμητικές τιμές, δυαδικές εκφράσεις, αναφορές κελιών και ούτω καθεξής. 
2. Εάν ελέγχονται κείμενο, λογικές εκφράσεις ή μαθηματικά σημάδια, τότε αυτά τα κριτήρια καθορίζονται μέσω εισαγωγικών.
3. Δεν μπορείτε να χρησιμοποιήσετε όρους μεγαλύτερους από 255 χαρακτήρες.
4. Είναι δυνατό να χρησιμοποιηθούν κατά προσέγγιση κριτήρια για την επιλογή τιμών με χρήση χαρακτήρων μπαλαντέρ. Το ερωτηματικό χρησιμοποιείται για την αντικατάσταση ενός μεμονωμένου χαρακτήρα και το σύμβολο πολλαπλασιασμού (αστερίσκος) απαιτείται για την αντικατάσταση πολλών χαρακτήρων. 
5. Οι τιμές Boole που βρίσκονται στο εύρος άθροισης μετατρέπονται αυτόματα σε αριθμητικές τιμές ανάλογα με τον τύπο τους. Έτσι, η τιμή "TRUE" μετατρέπεται σε ένα και "FALSE" - σε μηδέν. 
6. Αν ένα #VALUE! εμφανίζεται σφάλμα σε ένα κελί, σημαίνει ότι ο αριθμός των κελιών στο εύρος συνθηκών και άθροισης είναι διαφορετικός. Πρέπει να βεβαιωθείτε ότι τα μεγέθη αυτών των ορισμάτων είναι τα ίδια. 

Παραδείγματα χρήσης της συνάρτησης SUMIFS

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

Δυναμικό εύρος άθροισης συνθηκών

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

Ο πίνακας μοιάζει με αυτό.

Για να υπολογίσουμε τη συνολική βαθμολογία με βάση τα κριτήρια που περιγράφονται παραπάνω, πρέπει να εφαρμόσουμε τον ακόλουθο τύπο.

Ας περιγράψουμε τα επιχειρήματα με περισσότερες λεπτομέρειες:

1. Το C3:C14 είναι το εύρος αθροίσματος μας. Στην περίπτωσή μας, συμπίπτει με το εύρος συνθηκών. Από αυτό θα επιλεγούν τα σημεία που χρησιμοποιούνται για τον υπολογισμό του ποσού, αλλά μόνο αυτά που εμπίπτουν στα κριτήριά μας.
2. Το ">5" είναι η πρώτη μας προϋπόθεση.
3. Το B3:B14 είναι το δεύτερο εύρος άθροισης που υποβάλλεται σε επεξεργασία για να ταιριάζει με το δεύτερο κριτήριο. Βλέπουμε ότι δεν υπάρχει σύμπτωση με το εύρος άθροισης. Από αυτό συμπεραίνουμε ότι το εύρος της άθροισης και το εύρος της συνθήκης μπορεί να είναι ή να μην είναι πανομοιότυπα. 
4. Το "A*" είναι το δεύτερο εύρος, το οποίο καθορίζει την επιλογή βαθμών μόνο για εκείνους τους μαθητές των οποίων το επίθετο αρχίζει με Α. Στην περίπτωσή μας, ένας αστερίσκος σημαίνει οποιονδήποτε αριθμό χαρακτήρων. 

Μετά τους υπολογισμούς, έχουμε τον παρακάτω πίνακα.

Όπως μπορείτε να δείτε, ο τύπος συνόψισε τις τιμές με βάση το δυναμικό εύρος και με βάση τις συνθήκες που καθορίζονται από τον χρήστη.

Επιλεκτική άθροιση κατά συνθήκη στο Excel

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

Ο ίδιος ο πίνακας μοιάζει με αυτό. 

Για να προσδιορίσουμε το τελικό αποτέλεσμα, χρειαζόμαστε έναν τέτοιο τύπο.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Ως αποτέλεσμα των υπολογισμών που πραγματοποιήθηκαν με αυτόν τον τύπο, έχουμε το ακόλουθο αποτέλεσμα.

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

Η λειτουργία SUMIFS αθροίζει τιμές σε πολλαπλές συνθήκες

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

Χρησιμοποιούμε τον παρακάτω τύπο (αλλά τον γράφουμε ως τύπο πίνακα, τον εισάγουμε δηλαδή μέσω του συνδυασμού πλήκτρων CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Μετά τη λειτουργία SUMMESLIMN θα αθροίσει τον πίνακα τιμών με βάση τα κριτήρια που καθορίζονται στον τύπο (δηλαδή οι χώρες της Κίνας και της Γεωργίας), ο πίνακας που προκύπτει αθροίζεται από τη συνήθη συνάρτηση ΑΘΡΟΙΣΜΑ, που γράφεται ως τύπος πίνακα.

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

Τώρα ας ρίξουμε μια ματιά στον πίνακα που περιέχει τα σύνολα.

Όπως μπορείτε να δείτε, τα καταφέραμε. Σίγουρα θα τα καταφέρεις κι εσύ. Μεγάλη επιτυχία σε αυτόν τον τομέα. Αυτή είναι μια πολύ απλή λειτουργία που μπορεί να καταλάβει ένα άτομο που μόλις έχει πατήσει το πόδι του στο μονοπάτι της εκμάθησης του Excel. Και γνωρίζουμε ήδη ότι η συνάρτηση SUMMESLIMN σας επιτρέπει να είστε αποτελεσματικοί σε οποιονδήποτε τομέα δραστηριότητας, από τη λογιστική μέχρι και την εκπαίδευση. Ακόμα κι αν χτίζετε μια καριέρα σε οποιονδήποτε άλλο τομέα που δεν έχει περιγραφεί παραπάνω, αυτή η δυνατότητα θα σας βοηθήσει να κερδίσετε χρήματα. Γι' αυτό είναι πολύτιμη.

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