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