În acest articol, vom învăța Calcularea mediei numerelor care îndeplinesc criteriile specificate în Excel.
Scenariu:
Când lucrăm cu date, de unde trebuie să obținem media numerelor care au valori de criteriu. De ce valorile criteriilor creează o diferență de rezultat, funcția excel ar trebui să o considere zero și să ia rezultatul. O valoare zero poate face diferența în Media numerelor. Media numerelor este suma numerelor necesare împărțită la numărul de numere. În cazul în care zero nu contribuie la suma, dar crește numărul. Înțelegeți cum să rezolvați problema.
Cum se rezolvă problema?
Aici trebuie să găsim media numerelor cu criterii. Criteriile ignoră valorile criteriilor. Dacă aveți doar numere pozitive sau negative, puteți utiliza funcția AVERAGEIF
Sintaxa formulei (numere pozitive):
| = AVERAGEIF (valori, "> = 0") |
Formula de mai sus include zero, dar ignoră valorile criteriilor. Dacă doriți să ignorați zerourile și valorile criteriilor, utilizați doar criteriile ca „> 0”.
În mod similar, puteți obține media valorilor negative, inclusiv zerouri, utilizând formula:
Sintaxa formulei (numere negative):
| = AVERAGEIF (valori, "<= 0") |
Formula de mai sus include zero, dar ignoră valorile criteriilor. Dacă doriți să ignorați zerourile și valorile criteriilor, utilizați doar criteriile ca „> 0”
Dar problema considerării numerelor ignorând criteriile nu este rezolvată. Ați putea crede că, luând media celor două rezultate de mai sus (media numerelor pozitive și media numerelor negative) veți da valoarea necesară. Nu funcționează bine până când numărul numerelor pozitive este egal cu numărul numerelor negative. Pentru această problemă vom folosi funcția AGREGARE. Funcția AGREGAT este pachetul de funcții matematice. Ajută la rezolvarea în general a acestor tipuri de probleme. Pentru a despacheta acest pachet pentru media valorilor ignorând criteriile. Folosiți formula de mai jos.
Sintaxa formulei (pentru toate numerele):
| = AGREGAT (1, 6, valori) |
1: Argument pentru numere medii
6: ignorarea valorilor criteriilor
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Să luăm un exemplu și să înțelegem cum să folosim aceste formule într-un exemplu. Aici avem o listă de valori pentru care este necesară media.
În primul rând, calculăm media pentru numerele pozitive. Pentru aceasta vom folosi cele de mai jos pentru date.
Folosiți formula:
| = MEDIEIF (B3: B15, "> = 0") |
Explicaţie
- Criteriile sunt numere mai mari sau egale cu zero.
- Funcția AVERAGEIF ia în considerare numai numerele care îndeplinesc criteriile și returnează media numerelor
Aici formula returnează 58,6 luând în considerare numai valorile pozitive în care numărul de valori pozitive este 7. În mod similar, puteți obține media numerelor negative.
Folosiți formula:
| = MEDIEIF (B3: B15, "<= 0") |
Explicaţie
- Criteriile sunt numere mai mici sau egale cu zero.
- Funcția AVERAGEIF ia în considerare numai numerele care îndeplinesc criteriile și returnează media numerelor.
Pentru numerele negative media este -21,0, inclusiv zerouri, ignorând valorile criteriilor. Numărul până la numere considerate este 4.
Aici, în acest exemplu, numărul numerelor negative nu este egal cu numărul numerelor pozitive. Deci media celor 2 rezultate de mai sus nu va produce media tuturor numerelor. Folosiți formula de mai jos pentru aceste cazuri.
Folosiți formula:
| = AGREGAT (1, 6, B3: B15) |
Explicaţie:
- Argumentul 1 este dat pentru a utiliza funcția medie.
- Argumentul 6 este dat pentru a ignora valorile criteriilor.
- Funcția AGREGARE returnează media tuturor numerelor din matricea B3: B15 ignorând valorile criteriilor.
Media tuturor numerelor care ignoră toate valorile criteriilor se dovedește a fi 36,2. Aici numărul de numere este 9. Funcția AGGREGATE este cea mai bună modalitate de a rezolva aceste probleme, deoarece aceste funcții sunt funcții încorporate excel.
Dacă încercați să obțineți media numerelor utilizând funcția de bază MEDIE, trebuie să utilizați formula de mai jos pentru a obține media numerelor ignorând criteriile.
Folosiți formula:
| { = MEDIE (IF (IScriteria (B3: B15), "", B3: B15)) } |
Notă : nu utilizați acoladele manual. Doar folosiți Ctrl + Shift + Enter in locul introduce pentru a obține rezultatul.
Explicaţie:
- Funcția IF verifică starea cu funcția IScriteria pe tabloul B3: B15.
- Funcția IScriteria returnează True pentru valoarea în care valoarea este un criteriu și False în cazul în care nu.
- Funcția IF returnează valoarea necompletată dacă valoarea este criteriu și returnează valoarea corespunzătoare dacă valoarea nu este criteriu.
- Funcția MEDIE returnează media valorilor returnate.
- Acoladele curlate fac ca aceste funcții logice să funcționeze pentru o matrice.
După cum puteți vedea, formula funcționează bine și returnează același rezultat. Dar utilizați funcția AGGREGATE, este ușor de utilizat pentru diverse funcții matematice cu diferite criterii încorporate.
Dacă nu aveți nicio valoare zero în matrice. Puteți utiliza zero returnat din toate valorile criteriilor și puteți utiliza formula cum să luați media numerelor ignorând zero aici. Dar această formulă nu funcționează cu valori ale criteriilor.
Iată toate notele observaționale privind utilizarea formulei.
Note:
- Funcția AGREGAT este cea mai bună funcție de utilizat pentru aceste probleme.
- Funcția returnează #VALUE! criterii dacă nicio valoare nu corespunde criteriilor date.
- Se acceptă diferite criterii în interiorul formulei, cum ar fi egal cu (=), mai mic decât (<), mai mic sau egal cu (=), mai mare decât (>) și nu egal cu ().
În acest fel, am învățat Calcularea mediei numerelor care îndeplinesc criteriile specificate în Excel. Puteți utiliza aceste funcții în Excel 2016, 2013 și 2010. Găsiți mai multe articole despre formularea matematică cu diferite criterii. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail
Cum să evidențiați celulele deasupra și sub valoarea medie: evidențiați valori care sunt peste sau sub valoarea medie utilizând formatarea condiționată în Excel.
Ignorați zero în Media numerelor: calculați media numerelor din matrice ignorând zerourile utilizând funcția AVERAGEIF în Excel.
Calculați media ponderată: găsiți media valorilor cu greutate diferită utilizând funcția SUMPRODUCT în Excel.
Diferența medie între liste: calculați diferența în medie a două liste diferite. Aflați mai multe despre cum să calculați media utilizând formula medie de bază matematică.
Numerele medii dacă nu sunt necompletate în Excel: extrageți media valorilor dacă celula nu este necompletată în Excel.
MEDIA celor mai bune 3 scoruri dintr-o listă în Excel: Găsiți media numerelor cu criterii ca cele mai mari 3 numere din listă în Excel
Articole populare:
Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.
Cum se utilizează funcția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.