Denna handledning visar hur du använder Excel AVERAGEIF- och AVERAGEIFS -funktionerna i Excel och Google Sheets till genomsnittlig data som uppfyller vissa kriterier.
AVERAGEIF Funktionsöversikt
Du kan använda AVERAGEIF -funktionen i Excel för att räkna celler som innehåller ett visst värde, räkna celler som är större än eller lika med ett värde, etc.
Om du vill använda funktionen AVERAGEIF Excel -kalkylblad väljer du en cell och skriver:
(Lägg märke till hur formelinmatningarna ser ut)
AVERAGEIF Funktionssyntax och argument:
= AVERAGEIF (intervall, kriterier, [genomsnittligt område])
räckvidd - Cellernas intervall att räkna.
kriterier - Kriterierna som styr vilka celler som ska räknas.
genomsnittligt område - [valfritt] Cellerna i genomsnitt. Om den utelämnas används intervallet.
Vad är AVERAGEIF -funktionen?
AVERAGEIF -funktionen är en av de äldre funktionerna som används i kalkylblad. Den används för att söka igenom ett cellintervall som söker efter ett specifikt kriterium och sedan ge medelvärdet (aka det matematiska medelvärdet) om värdena i ett intervall som motsvarar dessa värden. Den ursprungliga AVERAGEIF -funktionen var begränsad till bara ett kriterium. Efter 2007 skapades AVERAGEIFS -funktionen som tillåter en mängd kriterier. Det mesta av den allmänna användningen förblir densamma mellan de två, men det finns några kritiska skillnader i syntaxen som vi kommer att diskutera i hela denna artikel.
Om du inte redan har gjort det kan du granska mycket av den liknande strukturen och exemplen i COUNTIFS -artikeln.
Grundläggande exempel
Låt oss överväga denna lista över registrerade försäljningar, och vi vill veta den genomsnittliga inkomsten.
Eftersom vi hade en kostnad, det negativa värdet, kan vi inte bara göra ett grundmedelvärde. Istället vill vi bara genomsnittliga värdena som är större än 0. ”Större än 0” är vad som kommer att vara våra kriterier i en AVERAGEIF -funktion. Vår formel för att säga detta är
= AVERAGEIF (A2: A7, "> 0")
Exempel på två kolumner
Medan den ursprungliga AVERAGEIF -funktionen var utformad för att låta dig tillämpa ett kriterium på det antal nummer du vill summera, måste du ofta tillämpa ett eller flera kriterier på andra kolumner. Låt oss överväga denna tabell:
Om vi nu använder den ursprungliga AVERAGEIF -funktionen för att ta reda på hur många bananer vi har i genomsnitt. Vi kommer att sätta våra kriterier i cell D1, och vi måste ge det intervall vi vill genomsnitt som det sista argumentet, och så skulle vår formel vara
= GENNOMSNITT (A2: A7, D1, B2: B7)
Men när programmerare så småningom insåg att användare ville ge mer än ett kriterium skapades funktionen AVERAGEIFS. För att skapa en struktur som fungerar för ett antal kriterier, kräver AVERAGEIFS att summan anges först. I vårt exempel betyder detta att formeln måste vara
= GENNOMSNITT (B2: B7, A2: A7, D1)
OBS: Dessa två formler får samma resultat och kan se likadana ut, så var noga med vilken funktion som används för att se till att du listar alla argument i rätt ordning.
Arbeta med datum, flera kriterier
När du arbetar med datum i ett kalkylblad, medan det är möjligt att mata in datumet direkt i formeln, är det bäst att ha datumet i en cell så att du bara kan referera till cellen i en formel. Till exempel hjälper detta datorn att veta att du vill använda datumet 27/05/2020, och inte siffran 5 dividerat med 27 dividerat med 2022.
Låt oss titta på vår nästa tabell som registrerar antalet besökare på en webbplats varannan vecka.
Vi kan ange start- och slutpunkter för intervallet vi vill titta på i D2 och E2. Vår formel för att hitta genomsnittet av antalet besökare i detta intervall kan vara:
= AVERAGEIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Lägg märke till hur vi kunde sammanfoga jämförelserna av “=” med cellreferenserna för att skapa kriterierna. Även om båda kriterierna tillämpades på samma cellintervall (A2: A7), måste du skriva ut intervallet två gånger, en gång per varje kriterium.
Flera kolumner
När du använder flera kriterier kan du tillämpa dem på samma intervall som vi gjorde med föregående exempel, eller så kan du tillämpa dem på olika intervall. Låt oss kombinera våra provdata i denna tabell:
Vi har konfigurerat några celler för användaren att ange vad de vill söka efter i cellerna E2 till G2. Vi behöver därför en formel som kommer att summera det totala antalet äpplen som plockades i februari. Vår formel ser ut så här:
= AVERAGEIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
AVERAGEIFS med ELLER typ logik
Fram till denna tidpunkt har alla exempel vi använt varit OCH -baserad jämförelse, där vi letar efter rader som uppfyller alla våra kriterier. Nu kommer vi att överväga fallet när du vill söka efter möjligheten att en rad uppfyller ett eller annat kriterium.
Låt oss titta på denna försäljningslista:
Vi skulle vilja lägga till den genomsnittliga försäljningen för både Adam och Bob. Först en snabb diskussion om att ta genomsnitt. Om du har ett ojämnt antal saker som har tre poster för Adam och 2 för Bob, kan du inte helt enkelt ta genomsnittet av varje persons försäljning. Detta är känt som att ta genomsnittet av medelvärden, och du slutar ge en orättvis viktning för objektet som har få poster. Om så är fallet med dina data måste du beräkna ett genomsnitt på det "manuella" sättet: ta summan av alla dina artiklar dividerat med antalet artiklar. För att granska hur du gör detta kan du kolla in artiklarna här:
Om antalet poster är samma, som i vår tabell, har du ett par alternativ du kan göra. Det enklaste är att lägga till två AVERAGEIFS tillsammans, så, och sedan dela med 2 (antalet objekt i vår lista)
= (AVERAGEIFS (B2: B7, A2: A7, "Adam")+AVERAGEIFS (B2: B7, A2: A7, "Bob"))/2
Här har vi fått datorn att beräkna våra individuella poäng och sedan lägger vi ihop dem.
Vårt nästa alternativ är bra när du har fler kriterier, så att du inte vill behöva skriva om hela formeln upprepade gånger. I den föregående formeln sa vi manuellt till datorn att lägga till två olika AVERAGEIFS tillsammans. Men du kan också göra detta genom att skriva dina kriterier i en array, så här:
= MEDEL (MEDELAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"}))
Titta på hur matrisen är konstruerad inuti de lockiga parenteserna. När datorn utvärderar denna formel kommer den att veta att vi vill beräkna en AVERAGEIFS -funktion för varje objekt i vår matris och därmed skapa en rad siffror. Den yttre medelvärdesfunktionen tar sedan det antal siffror och gör det till ett enda nummer. Genom att gå igenom formelutvärderingen skulle det se ut så här:
= GENNOMSNITT (GENNOMSNITT (B2: B7, A2: A7, {"Adam", "Bob"})) = GENomsnitt (13701, 21735) = 17718
Vi får samma resultat, men vi kunde skriva ut formeln lite mer kortfattat.
Hanterar ämnen
Ibland kommer din datamängd att ha tomma celler som du antingen måste hitta eller undvika. Att sätta upp kriterierna för dessa kan vara lite knepigt, så låt oss titta på ett annat exempel.
Observera att cell A3 verkligen är tom, medan cell A5 har en formel som returnerar en sträng med "". Om vi vill hitta det totala genomsnittet av verkligt tomma celler, skulle vi använda ett kriterium "=", och vår formel skulle se ut så här:
= AVERAGEIFS (B2: B7, A2: A7, "=")
Å andra sidan, om vi vill få genomsnittet för alla celler som visuellt ser tomma ut, ändrar vi kriterierna till att vara "" och formeln ser ut som
= AVERAGEIFS (B2: B7, A2: A7, "")
Låt oss vända på det: vad händer om du vill hitta genomsnittet av icke-tomma celler? Tyvärr låter den nuvarande designen dig inte undvika nolllängdssträngen. Du kan använda ett kriterium ””, men som du kan se i exemplet innehåller det fortfarande värdet från rad 5.
= AVERAGEIFS (B2: B7, A2: A7, "")
Om du inte behöver räkna celler som innehåller strängar med noll längd, vill du överväga att använda LEN -funktionen i en SUMPRODUCT
AVERAGEIF i Google Kalkylark
AVERAGEIF -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
