Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε το Εκτel SUMIF και SUMIFS Funcαναφορές στο Excel και στα Υπολογιστικά φύλλα Google για να αθροίσετε δεδομένα που πληρούν ορισμένα κριτήρια.
Επισκόπηση συνάρτησης SUMIF
Μπορείτε να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel για να αθροίσετε κελιά που περιέχουν μια συγκεκριμένη τιμή, να αθροίσετε κελιά που είναι μεγαλύτερα ή ίσα με μια τιμή κ.λπ.
(Παρατηρήστε πώς εμφανίζονται οι εισαγωγές τύπου)
Σύνταξη και επιχειρήματα συνάρτησης SUMIF:
| 1 | = SUMIF (εύρος, κριτήρια, [άθροισμα]] |
εύρος - Το εύρος των κελιών στα οποία θέλετε να εφαρμόσετε τα κριτήρια.
κριτήρια - Τα κριτήρια που χρησιμοποιούνται για τον προσδιορισμό των κελιών που θα προσθέσουμε.
άθροισμα_διάγραμμα - [προαιρετικά] Τα κελιά που θα προστεθούν. Εάν το sum_range παραλείπεται, τα κελιά στο εύρος προστίθενται μαζί.
Τι είναι η συνάρτηση SUMIF;
Η συνάρτηση SUMIF είναι μία από τις παλαιότερες συναρτήσεις που χρησιμοποιούνται στα υπολογιστικά φύλλα. Χρησιμοποιείται για σάρωση σε μια σειρά κελιών που ελέγχουν για ένα συγκεκριμένο κριτήριο και στη συνέχεια προσθέτουν τιμές σε μια περιοχή που αντιστοιχεί σε αυτές τις τιμές. Η αρχική συνάρτηση SUMIF περιορίστηκε σε ένα μόνο κριτήριο. Μετά το 2007, δημιουργήθηκε η συνάρτηση SUMIFS που επιτρέπει πληθώρα κριτηρίων. Το μεγαλύτερο μέρος της γενικής χρήσης παραμένει το ίδιο μεταξύ των δύο, αλλά υπάρχουν κάποιες κρίσιμες διαφορές στη σύνταξη που θα συζητήσουμε σε αυτό το άρθρο.
Εάν δεν το έχετε κάνει ήδη, μπορείτε να ελέγξετε μεγάλο μέρος της παρόμοιας δομής και παραδειγμάτων στο άρθρο COUNTIFS.
Βασικό παράδειγμα
Ας εξετάσουμε αυτόν τον κατάλογο καταγεγραμμένων πωλήσεων και θέλουμε να γνωρίζουμε το συνολικό εισόδημα.
Επειδή είχαμε μια δαπάνη, την αρνητική αξία, δεν μπορούμε να κάνουμε απλώς ένα βασικό άθροισμα. Αντ 'αυτού, θέλουμε να αθροίσουμε μόνο τις τιμές που είναι μεγαλύτερες από 0. Το "μεγαλύτερο από 0" είναι αυτό που θα είναι τα κριτήριά μας σε μια συνάρτηση SUMIF. Ο τύπος μας για να το δηλώσουμε αυτό είναι
| 1 | = SUMIF (A2: A7, "> 0") |
Παράδειγμα δύο στηλών
Ενώ η αρχική συνάρτηση SUMIF σχεδιάστηκε για να σας επιτρέπει να εφαρμόσετε ένα κριτήριο στο εύρος των αριθμών που θέλετε να αθροίσετε, τις περισσότερες φορές θα χρειαστεί να εφαρμόσετε ένα ή περισσότερα κριτήρια σε άλλες στήλες. Ας εξετάσουμε αυτόν τον πίνακα:
Τώρα, αν χρησιμοποιήσουμε την αρχική συνάρτηση SUMIF για να μάθουμε πόσες μπανάνες έχουμε (αναφέρονται στο κελί D1), θα πρέπει να δώσουμε το εύρος που θέλουμε άθροισμα ως το τελευταίο επιχείρημα, και έτσι θα ήταν ο τύπος μας
| 1 | = SUMIF (A2: A7, D1, B2: B7) |
Ωστόσο, όταν τελικά οι προγραμματιστές συνειδητοποίησαν ότι οι χρήστες ήθελαν να δώσουν περισσότερα από ένα κριτήρια, δημιουργήθηκε η συνάρτηση SUMIFS. Προκειμένου να δημιουργηθεί μια δομή που να λειτουργεί για οποιονδήποτε αριθμό κριτηρίων, το SUMIFS απαιτεί να αναγράφεται πρώτα το εύρος αθροίσματος. Στο παράδειγμά μας, αυτό σημαίνει ότι ο τύπος πρέπει να είναι
| 1 | = SUMIFS (B2: B7, A2: A7, D1) |
ΣΗΜΕΙΩΣΗ: Αυτοί οι δύο τύποι έχουν το ίδιο αποτέλεσμα και μπορεί να μοιάζουν, οπότε προσέξτε πολύ ποια συνάρτηση χρησιμοποιείται για να βεβαιωθείτε ότι παραθέτετε όλα τα ορίσματα με τη σωστή σειρά.
Εργασία με Ημερομηνίες, Πολλαπλά κριτήρια
Όταν εργάζεστε με ημερομηνίες σε υπολογιστικό φύλλο, ενώ είναι δυνατή η εισαγωγή της ημερομηνίας απευθείας στον τύπο, είναι η καλύτερη πρακτική να έχετε την ημερομηνία σε ένα κελί, ώστε να μπορείτε απλώς να αναφέρετε το κελί σε έναν τύπο. Για παράδειγμα, αυτό βοηθά τον υπολογιστή να γνωρίζει ότι θέλετε να χρησιμοποιήσετε την ημερομηνία 27/5/2020 και όχι τον αριθμό 5 διαιρούμενος με 27 διαιρούμενος με το 2022.
Ας δούμε τον επόμενο πίνακα που καταγράφει τον αριθμό των επισκεπτών σε έναν ιστότοπο κάθε δύο εβδομάδες.
Μπορούμε να καθορίσουμε τα σημεία έναρξης και λήξης του εύρους που θέλουμε να δούμε στα D2 και E2. Ο τύπος μας για να αθροίσουμε τον αριθμό των επισκεπτών σε αυτό το εύρος θα μπορούσε να είναι:
| 1 | = SUMIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Σημειώστε πώς καταφέραμε να συνδυάσουμε τις συγκρίσεις του "=" με τις αναφορές κελιών για να δημιουργήσουμε τα κριτήρια. Επίσης, παρόλο που και τα δύο κριτήρια εφαρμόζονταν στο ίδιο εύρος κελιών (A2: A7), πρέπει να γράψετε το εύρος δύο φορές, μία φορά για κάθε κριτήριο.
Πολλαπλές στήλες
Όταν χρησιμοποιείτε πολλαπλά κριτήρια, μπορείτε να τα εφαρμόσετε στο ίδιο εύρος όπως κάναμε στο προηγούμενο παράδειγμα ή μπορείτε να τα εφαρμόσετε σε διαφορετικά εύρη. Ας συνδυάσουμε τα δείγματα δεδομένων μας σε αυτόν τον πίνακα:
Έχουμε ρυθμίσει ορισμένα κελιά ώστε ο χρήστης να εισάγει αυτό που θέλει να αναζητήσει στα κελιά E2 έως G2. Χρειαζόμαστε λοιπόν έναν τύπο που θα προσθέσει τον συνολικό αριθμό των μήλων που συλλέχθηκαν τον Φεβρουάριο. Ο τύπος μας μοιάζει με αυτόν:
| 1 | = SUMIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
SUMIFS με λογική τύπου OR
Μέχρι αυτό το σημείο, τα παραδείγματα που χρησιμοποιήσαμε ήταν όλα ΚΑΙ βάσει σύγκρισης, όπου αναζητούμε σειρές που πληρούν όλα τα κριτήριά μας. Τώρα, θα εξετάσουμε την περίπτωση όταν θέλετε να αναζητήσετε τη δυνατότητα μιας σειράς να πληροί ένα ή άλλο κριτήριο.
Ας δούμε αυτήν τη λίστα πωλήσεων:
Θα θέλαμε να προσθέσουμε τις συνολικές πωλήσεις τόσο για τον Adam όσο και για τον Bob. Για να το κάνετε αυτό, έχετε μερικές επιλογές. Το πιο απλό είναι να προσθέσετε δύο SUMIFS μαζί, όπως έτσι:
| 1 | = SUMIFS (B2: B7, A2: A7, "Adam")+SUMIFS (B2: B7, A2: A7, "Bob") |
Εδώ, είχαμε τον υπολογιστή να υπολογίζει τις μεμονωμένες βαθμολογίες μας και στη συνέχεια να τις προσθέτουμε μαζί.
Η επόμενη επιλογή μας είναι καλή όταν έχετε περισσότερα εύρη κριτηρίων, έτσι ώστε να μην θέλετε να ξαναγράψετε ολόκληρο τον τύπο επανειλημμένα. Στον προηγούμενο τύπο, είπαμε χειροκίνητα στον υπολογιστή να προσθέσει δύο διαφορετικά SUMIFS μαζί. Ωστόσο, μπορείτε επίσης να το κάνετε γράφοντας τα κριτήριά σας μέσα σε έναν πίνακα, όπως αυτό:
| 1 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"})) |
Κοιτάξτε πώς κατασκευάζεται ο πίνακας μέσα στα σγουρά αγκύλια. Όταν ο υπολογιστής αξιολογήσει αυτόν τον τύπο, θα γνωρίζει ότι θέλουμε να υπολογίσουμε μια συνάρτηση SUMIFS για κάθε στοιχείο του πίνακα μας, δημιουργώντας έτσι έναν πίνακα αριθμών. Η εξωτερική συνάρτηση SUM θα λάβει στη συνέχεια αυτή τη σειρά αριθμών και θα την μετατρέψει σε έναν αριθμό. Περνώντας στην αξιολόγηση του τύπου, θα μοιάζει με αυτό:
| 123 | = SUM (SUMIFS (B2: B7, A2: A7, {"Adam", "Bob"}))= SUM (27401, 43470)= 70871 |
Παίρνουμε το ίδιο αποτέλεσμα, αλλά καταφέραμε να γράψουμε τον τύπο λίγο πιο συνοπτικά.
Αντιμετώπιση κενών
Μερικές φορές το σύνολο δεδομένων σας θα έχει κενά κελιά που πρέπει είτε να βρείτε είτε να αποφύγετε. Ο καθορισμός των κριτηρίων για αυτά μπορεί να είναι λίγο δύσκολο, οπότε ας δούμε ένα άλλο παράδειγμα.
Σημειώστε ότι το κελί Α3 είναι πραγματικά κενό, ενώ το κελί Α5 έχει έναν τύπο που επιστρέφει μια συμβολοσειρά μηδενικού μήκους "". Αν θέλουμε να βρούμε το συνολικό άθροισμα των στα αληθεια κενά κελιά, θα χρησιμοποιούσαμε ένα κριτήριο "=" και ο τύπος μας θα μοιάζει με αυτόν:
| 1 | = SUMIFS (B2: B7, A2: A7, "=") |
Από την άλλη πλευρά, εάν θέλουμε να πάρουμε το άθροισμα για όλα τα κελιά που εμφανίζονται κενά, αλλάζουμε τα κριτήρια σε "" και ο τύπος μοιάζει με
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Ας το γυρίσουμε: τι γίνεται αν θέλετε να βρείτε το άθροισμα των μη κενών κελιών; Δυστυχώς, ο τρέχων σχεδιασμός δεν θα σας επιτρέψει να αποφύγετε τη συμβολοσειρά μηδενικού μήκους. Μπορείτε να χρησιμοποιήσετε ένα κριτήριο "", αλλά όπως μπορείτε να δείτε στο παράδειγμα, εξακολουθεί να περιλαμβάνει την τιμή από τη γραμμή 5.
| 1 | = SUMIFS (B2: B7, A2: A7, "") |
Εάν δεν χρειάζεται να μετράτε κελιά που περιέχουν συμβολοσειρές μηδενικού μήκους, θα πρέπει να εξετάσετε τη χρήση της συνάρτησης LEN μέσα σε ένα SUMPRODUCT
SUMIF στα Υπολογιστικά φύλλα Google
Η συνάρτηση SUMIF λειτουργεί ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel:
