Συνάρτηση AVERAGEIF
Επιστρέφει τον αριθμητικό μέσο όλων των κελιών σε μια περιοχή που ικανοποιεί μια δεδομένη συνθήκη. Η συνάρτηση AVERAGEIF αθροίζει όλα τα αποτελέσματα που ταιριάζουν τη λογική δοκιμή και διαιρεί αυτό το άθροισμα με το μέγεθος των επιλεγμένων τιμών.
AVERAGEIF(Περιοχή; Κριτήριο [; μέση_τιμή_περιοχής ])
Περιοχή – απαιτούμενο όρισμα. Ένας πίνακας, ένα όνομα της ονοματισμένης περιοχής ή μια ετικέτα μιας στήλης ή μιας γραμμής που περιέχει αριθμούς για τον μέσο όρο ή αριθμοί ή κείμενο για τη συνθήκη.
Η αναζήτηση υποστηρίζει χαρακτήρες Υποκατάστασης (μπαλαντέρ) ή κανονικές εκφράσεις. Με ενεργοποιημένες τις κανονικές εκφράσεις, μπορείτε να εισαγάγετε "all.*", για παράδειγμα, για να βρείτε την πρώτη θέση του "all" ακολουθούμενη από οποιουσδήποτε χαρακτήρες. Εάν θέλετε να αναζητήσετε ένα κείμενο που είναι επίσης κανονική έκφραση, πρέπει είτε να προηγηθεί κάθε μεταχαρακτήρα ή τελεστή κανονικής έκφρασης με έναν χαρακτήρα "\" ή να περικλείσετε το κείμενο στο \Q...\E. Μπορείτε να ενεργοποιήσετε και να απενεργοποιήσετε την αυτόματη αξιολόγηση χαρακτήρων υποκατάστασης ή κανονικής έκφρασης στο .
Όταν χρησιμοποιείτε συναρτήσεις όπου ένα ή περισσότερα ορίσματα είναι συμβολοσειρές κριτηρίων αναζήτησης που αντιπροσωπεύουν κανονική έκφραση, η πρώτη προσπάθεια είναι η μετατροπή των κριτηρίων συμβολοσειρών σε αριθμούς. Παραδείγματος χάρη, το ".0" θα μετατραπεί σε 0.0 κ.ο.κ. Εάν είναι πετυχημένο, η συμφωνία δεν θα είναι συμφωνία κανονικής έκφρασης, αλλά αριθμητική συμφωνία. Όμως, όταν αλλάζετε στις τοπικές ρυθμίσεις όπου ο δεκαδικός διαχωριστής δεν είναι η τελεία, η μετατροπή της κανονικής έκφρασης λειτουργεί. Για να εξαναγκάσετε την αξιολόγηση της κανονικής έκφρασης αντί για την αριθμητική έκφραση, χρησιμοποιήστε κάποια έκφραση που δεν μπορεί να παρανοηθεί ως αριθμητική, όπως ".[0]", ή ".\0", ή "(?i).0".
Περιοχή_μέσου_όρου – προαιρετική. Είναι μια περιοχή τιμών για υπολογισμό του μέσου όρου.
Εάν η ΠεριοχήΜέσουΌρου (Average_Range) δεν καθορίζεται, η Περιοχή χρησιμοποιείται και για τα δύο, τον υπολογισμό του διάμεσου και την αναζήτηση σύμφωνα με τη συνθήκη. Εάν η ΠεριοχήΜέσουΌρου ορίζεται, η Περιοχή χρησιμοποιείται μόνο για τον έλεγχο συνθήκης, ενώ η ΠεριοχήΜέσουΌρου χρησιμοποιείται για τον υπολογισμό του μέσου όρου.
Αυτή η συνάρτηση είναι τμήμα του προτύπου της μορφής Open Document για εφαρμογές γραφείου (OpenDocument) έκδοσης 1.2. (ISO/IEC 26300:2-2015)
Αν ένα κελί σε μια περιοχή τιμών για τον υπολογισμό του μέσου είναι κενή ή περιέχει κείμενο, η συνάρτηση AVERAGEIF αγνοεί αυτό το κελί.
Αν όλη η περιοχή είναι κενή, περιέχει μόνο κείμενο ή όλες οι τιμές της περιοχής δεν ικανοποιούν τη συνθήκη (ή οποιονδήποτε συνδυασμό αυτών), η συνάρτηση επιστρέφει το σφάλμα #ΔΙΑΙΡ/0!.
Θεωρήστε τον ακόλουθο πίνακα
|
A |
B |
C |
|
|---|---|---|---|
|
1 |
Όνομα προϊόντος |
Πωλήσεις |
Έσοδα |
|
2 |
μολύβι |
20 |
65 |
|
3 |
στυλό |
35 |
85 |
|
4 |
σημειωματάριο |
20 |
190 |
|
5 |
βιβλίο |
17 |
180 |
|
6 |
κασετίνα |
όχι |
όχι |
Σε όλα τα παρακάτω παραδείγματα, οι περιοχές για τον υπολογισμό περιέχουν τη γραμμή #6 που αγνοείται, επειδή περιέχει κείμενο.
Απλή χρήση
=AVERAGEIF(B2:B6;"<35")
Υπολογίζει τον μέσο όρο για τιμές στην περιοχή B2:B6 που είναι μικρότερες από 35. Επιστρέφει 19, επειδή η δεύτερη γραμμή δεν συμμετέχει στον υπολογισμό.
=AVERAGEIF(B2:B6;"<"&MAX(B2:B6))
Υπολογίζει τον μέσο όρο για τιμές της ίδιας περιοχής που είναι μικρότερες από τη μέγιστη τιμή αυτής της περιοχής. Επιστρέφει 19, επειδή η μέγιστη τιμή (η δεύτερη γραμμή) δεν συμμετέχει στον υπολογισμό.
=AVERAGEIF(B2:B6;">"&SMALL(B2:B6;1))
Υπολογίζει τον μέσο όρο για τιμές της ίδιας περιοχής που είναι μεγαλύτερες από την πρώτη ελάχιστη τιμή της περιοχής. Επιστρέφει 25, επειδή η πρώτη ελάχιστη τιμή (η τέταρτη γραμμή) δεν συμμετέχει στον υπολογισμό.
Χρήση του Περιοχή_μέσου_όρου
=AVERAGEIF(B2:B6;"<35";C2:C6)
Η συνάρτηση αναζητά ποιες τιμές είναι μικρότερες από 35 στην περιοχή B2:B6 και υπολογίζει τον μέσο όρο των αντίστοιχων τιμών από την περιοχή C2:C6. Επιστρέφει 145, επειδή η δεύτερη σειρά δεν συμμετέχει στον υπολογισμό.
=AVERAGEIF(B2:B6;">"&MIN(B2:B6);C2:C6)
Η συνάρτηση αναζητά ποιες τιμές από την περιοχή B2:B6 είναι μεγαλύτερες από την ελάχιστη τιμή στην περιοχή B2:B6 και υπολογίζει τον μέσο όρο των αντίστοιχων τιμών από την περιοχή C2:C6, Επιστρέφει 113,3, επειδή η τέταρτη γραμμή (όπου υπάρχει η ελάχιστη τιμή στην περιοχή B2:B6) δεν συμμετέχει στον υπολογισμό.
=AVERAGEIF(B2:B6;"<"&LARGE(B2:B6;2);C2:C6)
Η συνάρτηση αναζητά ποιες τιμές από 35 την περιοχή B2:B6 είναι μικρότερες από τη δεύτερη μεγάλη τιμή στην περιοχή B2:B6 και υπολογίζει τον μέσο όρο των αντίστοιχων τιμών από την περιοχή C2:C6. Επιστρέφει 180, επειδή μόνο η τέταρτη γραμμή συμμετέχει στον υπολογισμό.
Χρήση κανονικών παραστάσεων
=AVERAGEIF(A2:A6;"pen";B2:B6)
Η συνάρτηση αναζητά ποια κελιά από την περιοχή A2:A6 περιέχουν μόνο τη λέξη “pen” και υπολογίζει τον μέσο όρο των αντίστοιχων τιμών από την περιοχή B2:B6. Επιστρέφει 35, επειδή μόνο η δεύτερη γραμμή συμμετέχει στον υπολογισμό. Η αναζήτηση εκτελείται στην περιοχή A2:A6, αλλά οι τιμές επιστρέφονται από την περιοχή B2:B6.
=AVERAGEIF(A2:A6;"pen.*";B2:B6)
Η συνάρτηση αναζητά ποια κελιά από την περιοχή A2:A6 ξεκινούν με “pen” και τελειώνουν με οποιονδήποτε αριθμό άλλων χαρακτήρων και υπολογίζει τον μέσο όρο των αντίστοιχων τιμών από την περιοχή B2:B6. Επιστρέφει 27,5, επειδή τώρα και το “pencil” ικανοποιεί τη συνθήκη και οι δυο, πρώτη και δεύτερη γραμμή, συμμετέχουν στον υπολογισμό.
=AVERAGEIF(A2:A6;".*book.*";B2:B6)
Η συνάρτηση αναζητά ποια κελιά από την περιοχή A2:A6 περιέχουν το “book” αρχίζοντας και τελειώνοντας με οποιονδήποτε αριθμό άλλων χαρακτήρων και υπολογίζει τον μέσο όρο των αντίστοιχων τιμών από την περιοχή B2:B6. Επιστρέφει 18,5, επειδή μόνο η τρίτη και τέταρτη γραμμή συμμετέχουν στον υπολογισμό.
Αναφορά σε ένα κελί ως ένα κριτήριο
Αν χρειάζεται να αλλάξετε ένα κριτήριο εύκολα, μπορεί να θέλετε να το ορίσετε σε ένα ξεχωριστό κελί και να χρησιμοποιήσετε μια αναφορά σε αυτό το κελί στη συνθήκη της συνάρτησης AVERAGEIF.
=AVERAGEIF(A2:A6;".*"&E2&".*";B2:B6)
Η συνάρτηση αναζητά ποια κελιά από την περιοχή A2:A6 περιέχουν συνδυασμό χαρακτήρων που ορίζονται στο E2 αρχίζοντας και τελειώνοντας με οποιονδήποτε αριθμό άλλων χαρακτήρων και υπολογίζει τον μέσο όρο των αντίστοιχων τιμών από την περιοχή B2:B6. Αν E2 = book, η συνάρτηση επιστρέφει 18,5.
=AVERAGEIF(B2:B6;"<"&E2;C2:C6)
Η συνάρτηση αναζητά ποια κελιά από την περιοχή B2:A6 είναι μικρότερα από την τιμή που ορίστηκε στο E2 και υπολογίζει τον μέσο όρο των αντίστοιχων τιμών από την περιοχή C2:C6. Αν E2 = 35, η συνάρτηση επιστρέφει 145.