Acest tutorial arată cum să utilizați funcțiile Excel AVERAGEIF și AVERAGEIFS în Excel și Foi de calcul Google pentru a obține media datelor care îndeplinesc anumite criterii.
Prezentare generală a funcției AVERAGEIF
Puteți utiliza funcția AVERAGEIF în Excel pentru a număra celulele care conțin o anumită valoare, pentru a număra celulele care sunt mai mari sau egale cu o valoare etc.
Pentru a utiliza funcția Foaie de lucru AVERAGEIF Excel, selectați o celulă și tastați:
(Observați cum apar datele introduse de formulă)
Sintaxa și argumentele funcției AVERAGEIF:
= AVERAGEIF (interval, criterii, [interval_mediu])
gamă - Gama de celule de numărat.
criterii - Criteriile care controlează ce celule ar trebui să fie numărate.
interval_mediu - [opțional] Celulele la medie. Când este omis, se utilizează intervalul.
Ce este funcția AVERAGEIF?
Funcția AVERAGEIF este una dintre funcțiile mai vechi utilizate în foile de calcul. Se utilizează pentru a scana printr-o gamă de celule verificând un anumit criteriu și apoi oferind media (cunoscută și ca media matematică) dacă valorile dintr-un interval care corespund acelor valori. Funcția originală AVERAGEIF a fost limitată la un singur criteriu. După 2007, a fost creată funcția AVERAGEIFS care permite o multitudine de criterii. Cea mai mare parte a utilizării generale rămâne aceeași între cele două, dar există unele diferențe critice în sintaxă pe care le vom discuta pe parcursul acestui articol.
Dacă nu ați făcut-o deja, puteți examina o mare parte din structura și exemplele similare din articolul COUNTIFS.
Exemplu de bază
Să luăm în considerare această listă a vânzărilor înregistrate și dorim să aflăm venitul mediu.
Deoarece am avut o cheltuială, valoarea negativă, nu putem face doar o medie de bază. În schimb, dorim să mediați doar valorile mai mari de 0. „Mai mare decât 0” este criteriul nostru într-o funcție AVERAGEIF. Formula noastră pentru a afirma acest lucru este
= AVERAGEIF (A2: A7, "> 0")
Exemplu cu două coloane
În timp ce funcția AVERAGEIF originală a fost concepută pentru a vă permite să aplicați un criteriu intervalului de numere pe care doriți să le însumați, de cele mai multe ori va trebui să aplicați unul sau mai multe criterii altor coloane. Să luăm în considerare acest tabel:
Acum, dacă folosim funcția AVERAGEIF originală pentru a afla câte Banane avem în medie. Vom pune criteriile noastre în celula D1 și va trebui să oferim intervalul dorit in medie ca ultim argument, și așa ar fi formula noastră
= AVERAGEIF (A2: A7, D1, B2: B7)
Cu toate acestea, când programatorii au realizat în cele din urmă că utilizatorii doreau să dea mai multe criterii, a fost creată funcția AVERAGEIFS. Pentru a crea o structură care să funcționeze pentru orice număr de criterii, AVERAGEIFS necesită ca intervalul sumelor să fie listat mai întâi. În exemplul nostru, aceasta înseamnă că formula trebuie să fie
= MEDII (B2: B7, A2: A7, D1)
NOTĂ: Aceste două formule obțin același rezultat și pot arăta asemănător, deci acordați o atenție deosebită funcției utilizate pentru a vă asigura că listați toate argumentele în ordinea corectă.
Lucrul cu date, criterii multiple
Când lucrați cu date într-o foaie de calcul, deși este posibilă introducerea datei direct în formulă, este recomandată să aveți data într-o celulă, astfel încât să puteți face referire doar la celulă într-o formulă. De exemplu, acest lucru ajută computerul să știe că doriți să utilizați data 27.05.2020 și nu numărul 5 împărțit la 27 împărțit la 2022.
Să ne uităm la următorul nostru tabel care înregistrează numărul de vizitatori pe un site la fiecare două săptămâni.
Putem specifica punctele de început și de sfârșit ale intervalului pe care dorim să îl privim în D2 și E2. Formula noastră pentru a găsi media numărul de vizitatori din acest interval ar putea fi:
= AVERAGEIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Rețineți cum am reușit să concatenăm comparațiile dintre „=” și referințele celulei pentru a crea criteriile. De asemenea, chiar dacă ambele criterii erau aplicate aceluiași interval de celule (A2: A7), trebuie să scrieți intervalul de două ori, o dată pentru fiecare criteriu.
Coloane multiple
Când utilizați mai multe criterii, le puteți aplica aceluiași interval ca și în exemplul anterior sau le puteți aplica unor intervale diferite. Să combinăm datele noastre eșantion în acest tabel:
Am configurat câteva celule pentru ca utilizatorul să introducă ceea ce dorește să caute în celulele E2 până la G2. Avem astfel nevoie de o formulă care să adune numărul total de mere culese în februarie. Formula noastră arată astfel:
= AVERAGEIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
MEDII cu logică de tip SAU
Până în prezent, exemplele pe care le-am folosit au fost toate bazate pe comparație bazată pe ȘI, în care căutăm rânduri care să îndeplinească toate criteriile noastre. Acum, vom lua în considerare cazul atunci când doriți să căutați posibilitatea ca un rând să îndeplinească unul sau alt criteriu.
Să vedem această listă de vânzări:
Am dori să adăugăm vânzările medii atât pentru Adam, cât și pentru Bob. În primul rând, o discuție rapidă despre luarea mediilor. Dacă aveți un număr inegal de lucruri, cum ar fi 3 intrări pentru Adam și 2 pentru Bob, nu puteți lua pur și simplu media vânzărilor fiecărei persoane. Acest lucru este cunoscut ca luând media mediilor și ajungeți să dați o pondere nedreaptă articolului care are puține intrări. Dacă acesta este cazul cu datele dvs., ar trebui să calculați o medie în modul „manual”: luați suma tuturor articolelor dvs. împărțită la numărul de articole. Pentru a examina cum puteți face acest lucru, puteți consulta articolele aici:
Acum, dacă numărul de intrări este același, cum ar fi în tabelul nostru, atunci aveți câteva opțiuni pe care le puteți face. Cel mai simplu este să adăugați două AVERAGEIFS împreună, ca așa, și apoi să împărțiți la 2 (numărul de articole din lista noastră)
= (AVERAGEIFS (B2: B7, A2: A7, "Adam") + AVERAGEIFS (B2: B7, A2: A7, "Bob")) / 2
Aici am primit computerul să ne calculeze scorurile individuale și apoi le adăugăm împreună.
Următoarea noastră opțiune este bună atunci când aveți mai multe intervale de criterii, astfel încât să nu doriți să fie necesar să rescrieți întreaga formulă în mod repetat. În formula anterioară, am spus manual computerului să adauge două AVERAGEIFS diferite împreună. Cu toate acestea, puteți face acest lucru scriind criteriile într-o matrice, astfel:
= MEDIE (MEDII (B2: B7, A2: A7, {"Adam", "Bob"}))
Uită-te la modul în care matricea este construită în parantezele crețate. Când computerul evaluează această formulă, va ști că dorim să calculăm o funcție AVERAGEIFS pentru fiecare element din matricea noastră, creând astfel o matrice de numere. Funcția MEDIE exterioară va lua apoi acea matrice de numere și o va transforma într-un singur număr. Trecând prin evaluarea formulei, ar arăta astfel:
= MEDIE (MEDII (B2: B7, A2: A7, {"Adam", "Bob"})) = MEDIE (13701, 21735) = 17718
Obținem același rezultat, dar am reușit să scriem formula puțin mai succint.
Tratarea golurilor
Uneori, setul de date va avea celule goale pe care trebuie să le găsiți sau să le evitați. Configurarea criteriilor pentru acestea poate fi puțin dificilă, așa că să analizăm un alt exemplu.
Rețineți că celula A3 este cu adevărat necompletată, în timp ce celula A5 are o formulă care returnează un șir de lungime zero de „”. Dacă vrem să găsim media totală a cu adevărat celule goale, am folosi un criteriu „=”, iar formula noastră ar arăta astfel:
= MEDIILE (B2: B7, A2: A7, "=")
Pe de altă parte, dacă dorim să obținem media pentru toate celulele care arată vizual necompletate, vom schimba criteriile pentru a fi „”, iar formula arată ca
= MEDII (B2: B7, A2: A7, "")
Să o răsfoim: ce se întâmplă dacă doriți să găsiți media celulelor care nu sunt goale? Din păcate, designul actual nu vă permite să evitați șirul de lungime zero. Puteți utiliza un criteriu de „”, dar după cum puteți vedea în exemplu, acesta include în continuare valoarea din rândul 5.
= MEDIILE (B2: B7, A2: A7, "")
Dacă nu trebuie să numărați celule care conțin șiruri de lungime zero, vă recomandăm să utilizați funcția LEN într-un SUMPRODUCT
AVERAGEIF în Foi de calcul Google
Funcția AVERAGEIF funcționează exact la fel în Foi de calcul Google ca în Excel:
