Cum să începeți să utilizați COUNTIF, SUMIF și AVERAGEIF în Excel

Învățarea Microsoft Excel vizează adăugarea mai multor formule și funcții în toolbelt. Combinați destul de multe dintre acestea, și puteți face practic orice cu o foaie de calcul.

În acest tutorial, veți învăța cum să utilizați trei formule Excel puternice: SUMIF, COUNTIF, și AVERAGEIF.

Aici avem o listă de tranzacții pentru lună cu unele date privind cheltuielile.

În captura de ecran de mai sus, puteți vedea că avem o listă de tranzacții pe partea stângă. Dacă vreau să țin o privire asupra cheltuielilor mele, pot folosi aceste trei formule pentru a le monitoriza. 

În partea dreaptă, Aflați cheltuieli caseta folosește cele trei formule pentru a mă ajuta să-mi urmăresc cheltuielile:

• COUNTIF - Folosit pentru a număra de câte ori apare "Restaurant" în listă.
• SUMIF - Calculează cheltuielile totale pentru articolele etichetate "Restaurant".
  
• AVERAGIEF - Mărește toate cheltuielile mele "Restaurant" din listă.
  

Mai generic, iată ce face fiecare dintre aceste formule și cum le puteți folosi în avantajul dvs.:

• SUMIF - Adăugați valori dacă este îndeplinită o condiție, cum ar fi adăugarea tuturor achizițiilor dintr-o singură categorie.
• COUNTIF - Numărați numărul de elemente care îndeplinesc o condiție, cum ar fi numărarea de câte ori apare un nume într-o listă.
• AVERAGEIF - Valori medii condiționate; de exemplu, puteți să vă mediați notele doar pentru examene.

Aceste formule vă permit să adăugați logică în foaia dvs. de calcul. Să ne uităm la modul de utilizare a fiecărei formule.

COUNTIF, SUMIF și AVERAGEIF în Excel (Quick Video Tutorial)

Screencast-urile sunt una dintre cele mai bune modalități de a viziona și de a învăța o nouă abilitate, inclusiv de a începe cu aceste trei formule cheie în Microsoft Excel. Consultați videoclipul de mai jos pentru a urmări modul în care lucrez în Excel. Asigurați-vă și descărcați fișierul de lucru pentru exemple gratuite de utilizat cu acest tutorial.

 

Dacă preferați să învățați cu instrucțiuni scrise, pas cu pas, continuați să citiți. Voi împărtăși sfaturi despre cum să folosiți aceste formule și idei de ce sunt utile.

Cum se utilizează SUMIF în Excel

Utilizați fila intitulată SUMIF în registrul de lucru cu exemple libere pentru această secțiune a tutorialului.

Gândiți-vă la SUMIF ca modalitate de a adăuga valori care respectă o regulă. Putem adăuga o listă de valori dintr-o anumită categorie sau toate valorile mai mari sau mai mici decât o anumită sumă.

Iată cum funcționează formula SUMIF:

= SUMIF (Celule pentru a verifica, ce să verifice, suma celulelor care respectă regulile)

Să ne întoarcem la exemplul de cheltuieli pentru restaurant pentru a afla formula SUMIF. Mai jos, afișez o listă a tranzacțiilor mele pentru luna.

Am o listă de tranzacții și o să folosesc SUMIF pentru a mă ajuta să țin cont de cheltuielile mele.

Vreau să știu două lucruri:

1. Totalul a ceea ce am petrecut la restaurante pentru luna.
2. Toate achizițiile mai mari de 50 USD pentru lună, din orice categorie.

În loc să adăugăm date manual, putem scrie două SUMIF formule pentru automatizarea procesului. Voi pune rezultatele în verde Cheltuielile restaurantului caseta din partea dreaptă. Să ne uităm la fel.

Cheltuielile totale pentru restaurant

Pentru a-mi găsi cheltuielile totale pentru restaurant, voi rezuma toate valorile cu tipul de cheltuieli al "Restaurantului", care se află în coloana B.

Iată formula pe care o voi folosi pentru acest exemplu:

= SUMIF (B2: B17, "Restaurant", C2: C17)

Observați că fiecare secțiune este separată de o virgulă. Această formulă are trei lucruri cheie:

• Se uită la ce se află în celulele B2 - B17 pentru categoria de cheltuieli
• Folosește "Restaurantul" pentru criteriile de rezumat
• Utilizează valorile din celulele C2 până la C17 pentru a obține suma totală

În acest exemplu, însumez toate valorile din tipul de cheltuieli "Restaurant".

Când apăs pe Enter, Excel calculează totalul cheltuielilor pentru restaurant. Utilizând SUMIF, este ușor să creați aceste statistici rapide pentru a vă ajuta să monitorizați date de anumite tipuri.

Achiziții mai mari de 50 USD

Am verificat o anumită categorie, dar acum să însumăm toate valorile care sunt mai mare ca o sumă din orice categorie. În acest caz, vreau să găsesc toate achizițiile care au depășit 50 USD.

Să scriem o formulă simplă pentru a găsi suma tuturor achizițiilor mai mari de 50 USD:

= SUMIF (C2: C17 "> 50")

În acest caz, formula este un pic mai simplă: deoarece rezumăm aceleași valori pe care le testăm (C2 - C17), trebuie doar să specificăm acele celule. Apoi, vom adăuga o virgulă și "> 50" numai la valori mai mari de 50 $.

Sumați toate achizițiile mai mari de 50 USD cu o formulă Excel excelentă.

Acest exemplu folosește un semn mai mare decât semnul, dar pentru puncte bonus: încercați să rezumați toate achizițiile mici, cum ar fi toate achizițiile de 20 $ sau mai puțin.

Cum se utilizează COUNTIF în Excel

Utilizați fila intitulată COUNTIF în registrul de lucru cu exemple libere pentru această secțiune a tutorialului.

In timp ce SUMIF este folosit pentru a adăuga valori care întrunesc o anumită condiție, COUNTIF va număra de câte ori apare ceva într-un anumit set de date.

Iată formatul general pentru COUNTIF formulă:

= COUNTIF (celulele de numărare, criteriile de numărare)

Folosind același set de date, să numărăm două elemente-cheie:

• Numărul de achiziții de îmbrăcăminte pe care le-am făcut într-o lună
• Numărul de achiziții este de 100 USD sau mai mare

Numărul de achiziții de îmbrăcăminte

Primul meu COUNTIF va examina tipul de cheltuieli și va număra numărul de achiziții "îmbrăcăminte" din tranzacțiile mele.

Formula finală va fi:

= COUNTIF (B2: B17, "Îmbrăcăminte")

Formula se uită la coloana "Expense Type", numără de câte ori vede îmbrăcămintea și le numără. Rezultatul este 2.

Formula "COUNTIF" numără numărul de cheltuieli marcate cu "Îmbrăcăminte" și le numără.

Număr de 100 $ + Achiziții

Acum, să numărăm numărul de tranzacții care au fost de 100 de dolari sau mai mult pe lista mea. 

Iată formula pe care o voi folosi:

= COUNTIF (C2: C17 "> 100")

Aceasta este o formulă simplă, cu două părți: pur și simplu punctul Excel la lista de date de numărare și regula de numărare. În acest caz, verificăm celulele C2 - C17, pentru toate valorile mai mari de 100 $. 

Numără numărul de tranzacții de $ 100 + cu această formulă Excel COUNTIF.

Cum se utilizează AVERAGEIF în Excel

Utilizați fila intitulată AVERAGEIF în registrul de lucru cu exemple libere pentru această secțiune a tutorialului.

În cele din urmă, să ne uităm la modul de utilizare a unui AVERAGEIF formulă. Până acum, ar trebui să nu fie o surpriză faptul că AVERAGEIF poate fi folosit pentru a media valori specifice, pe baza unei condiții pe care o vom oferi Excel.

Formatul pentru o formulă AVERAGEIF este:

= AVERAGEIF (celule pentru a verifica, ce să verifice, media celulelor care respectă regulile)

Formatul formulei AVERAGEIF este cel mai similar cu formula SUMIF.

Să folosim formula AVERAGEIF pentru a calcula două statistici cheie despre cheltuielile mele:

1. Media cheltuielilor pentru restaurant.
2. Media tuturor cheltuielilor este mai mică de 25 USD.

Media achizițiilor din restaurant

Să-mi mediez cheltuielile pentru restaurant, Voi scrie o formulă AVERAGEIF pentru a mări toate sumele pe baza categoriei.

= AVERAGEIF (B2: B17, "Restaurant", C2: C17)

Există trei părți la această formulă, separate fiecare de o virgulă:

• B2: B17 specifică celulele pentru a verifica o condiție pentru. Deoarece tipul de cheltuieli este specificat în această coloană.
• "Restaurantul" dă formula ceva de căutat.
• În cele din urmă, C2: C17 sunt celulele care au o medie de calcul în calculul nostru.

Aici folosesc un AVERAGEIF pentru a-mi medie cheltuielile de excursie anuală în restaurant.

În cele din urmă, Excel mediește cheltuielile de călătoriile mele în restaurant. I-am dat acea formulă.

Puteți încerca, de asemenea, această formulă prin înlocuirea "Restaurantului" cu altă categorie, cum ar fi "Îmbrăcăminte".

Suma medie a cheltuielilor este sub 25 USD

Dacă țin un ochi la achizițiile mele mai mici și vreau să-mi cunosc media, pot scrie un AVERAGEIF pentru toate achizițiile mai mici decât o sumă.

Iată formula pe care o voi folosi pentru a face asta:

= AVERAGEIF (C2: C17“<25")

Această formulă simplă verifică doar valorile din coloana C și mediază toate valorile mai mari de 25 $.

Formulare simple Excel AVERAGEIF pentru cheltuieli Mai puțin de 25 USD.

Recapitulați și păstrați învățarea

În acest tutorial, ați învățat cum să utilizați trei formule matematice condiționate pentru a vă examina datele. Indiferent dacă estimați însumarea, numărarea sau medierea datelor, aceste funcții sunt avansate în abilitățile Excel pe care le puteți folosi bine.

Pentru toate formulele "IF" din acest tutorial, reținerea principală este că puteți aplica condițiile calculelor dvs. în Excel.

Învățarea duce la învățare. Iată trei tutoriale Excel pentru a continua să crească:

• În plus față de COUNTIF, SUMIF și AVERAGEIF, există și declarații generale "IF" care pot fi folosite în alte condiții. Consultați tutorialul nostru despre modul de utilizare a instrucțiunilor simple IF.
• Aflați cum să utilizați Datele și orele Excel împreună cu aceste formule pentru a lucra cu valori bazate pe dată.
• Funcția Excel VLOOKUP poate fi utilizată pentru a se potrivi valorilor din mai multe liste. Aflați mai multe despre modul de utilizare a funcției Excel VLOOKUP în acest tutorial.

Vă puteți gândi la alte modalități de a utiliza aceste formule pentru a adăuga logică și condiții la foaia dvs. de calcul? Anunță-mă cu un comentariu de mai jos.