I den här artikeln lär vi oss beräkna genomsnittet för siffror som uppfyller specifika kriterier i Excel.
Scenario:
När vi arbetar med data, där vi behöver få medelvärdet av siffror som har kriterievärden. Varför kriterievärden skapar en skillnad i resultat, bör Excel -funktion betrakta det som noll och ta resultatet. Ett nollvärde kan göra skillnad i Medelvärde. Genomsnittet av siffror är summan av erforderliga nummer dividerat med antalet räkningar. Där nollan inte bidrar till summan men det ökar antalet. Förstå hur man löser problemet.
Hur löser man problemet?
Här måste vi hitta genomsnittet av siffror med kriterier. Kriterier ignorerar kriteriernas värden. Om du bara har positiva eller negativa tal kan du använda AVERAGEIF -funktionen
Formelsyntax (positiva tal):
| = AVERAGEIF (värden, "> = 0") |
Ovanstående formel innehåller nollor men ignorerar kriterievärden. Om du vill ignorera nollor och kriterievärden använder du bara kriterierna som "> 0".
På samma sätt kan du få genomsnittet av negativa värden inklusive nollor med hjälp av formeln:
Formelsyntax (negativa tal):
| = AVERAGEIF (värden, "<= 0") |
Ovanstående formel innehåller nollor men ignorerar kriterievärden. Om du vill ignorera nollor och kriterievärden använder du bara kriterierna som "> 0"
Men problemet med att överväga siffror som ignorerar kriterier är inte löst. Du kanske tror att med genomsnittet av de två ovanstående resultaten (genomsnitt av positiva siffror och genomsnitt av negativa siffror) ger det önskade värdet. Det fungerar inte bra förrän antalet positiva tal är lika med antalet negativa tal. För detta problem kommer vi att använda funktionen AGGREGATE. AGGREGATE -funktionen är paketet med matematiska funktioner. Det hjälper i allmänhet att lösa den här typen av problem. För att packa upp detta paket för genomsnittet av värden som ignorerar kriterier. Använd formeln nedan.
Formelsyntax (för alla nummer):
| = AGGREGATE (1, 6, värden) |
1: Argument till genomsnittliga tal
6: ignorerar kriteriernas värden
Exempel:
Allt detta kan vara förvirrande att förstå. Låt oss ta ett exempel och förstå hur man använder dessa formler i ett exempel. Här har vi en lista med värden för vilka medelvärdet krävs.
För det första beräknar vi genomsnittet för de positiva siffrorna. För detta kommer vi att använda nedanstående för data.
Använd formeln:
| = AVERAGEIF (B3: B15, "> = 0") |
Förklaring
- Kriterier är tal som är större än eller lika med noll.
- AVERAGEIF -funktionen tar endast hänsyn till de siffror som uppfyller kriterierna och returnerar genomsnittet av siffrorna
Här returnerar formeln 58,6 med tanke på endast de positiva värdena där antalet positiva värden är 7. På samma sätt kan du få medelvärdet av de negativa talen.
Använd formeln:
| = AVERAGEIF (B3: B15, "<= 0") |
Förklaring
- Kriterier är tal som är mindre än eller lika med noll.
- AVERAGEIF -funktionen tar endast hänsyn till de siffror som uppfyller kriterierna och returnerar genomsnittet av siffrorna.
För negativa tal är genomsnittet -21,0 inklusive nollor som ignorerar kriterier. Räkna till antalet är 4.
Här i detta exempel är antalet negativa tal inte lika med antalet positiva tal. Så genomsnittet av de 2 ovanstående resultaten ger inte genomsnittet av alla siffror. Använd nedanstående formel för dessa fall.
Använd formeln:
| = AGGREGATE (1, 6, B3: B15) |
Förklaring:
- Argument 1 ges för att använda genomsnittsfunktionen.
- Argument 6 ges för att ignorera kriterier.
- AGGREGATE -funktionen returnerar genomsnittet av alla nummer i B3: B15 -arrayen och ignorerar kriterievärden.
Medelvärdet för alla nummer som ignorerar alla kriterievärden blir 36,2. Här är antalet siffror 9. AGGREGATE -funktionen är det bästa sättet att lösa dessa problem, eftersom dessa funktioner är utmärkta inbyggda funktioner.
Om du försöker få medelvärdet av siffror med hjälp av den genomsnittliga medelvärdesfunktionen, måste du använda formeln nedan för att få genomsnittet av siffror att ignorera kriterier.
Använd formeln:
| { = MEDEL (IF (IScriteria (B3: B15), "", B3: B15)) } |
Notera : använd inte de lockiga stagen manuellt. Använd bara Ctrl + Skift + Enter istället för Stiga på för att få resultatet.
Förklaring:
- IF -funktionen kontrollerar tillståndet med IScriteria -funktionen på B3: B15 -arrayen.
- IScriteria -funktionen returnerar True för värdet där värdet är ett kriterium och Falskt där inte.
- IF -funktionen returnerar ett tomt värde om värdet är kriterier och returnerar motsvarande värde om värdet inte är kriterier.
- AVERAGE -funktionen returnerar genomsnittet av de returnerade värdena.
- Lockiga hängslen gör att dessa logiska funktioner fungerar för en array.
Som du kan se fungerar formeln bra och returnerar samma resultat. Men använd AGGREGATE -funktionen, den är enkel att använda för olika matematiska funktioner med olika inbyggda kriterier.
Om du inte har något nollvärde i matrisen. Du kan använda returnollan ur alla kriterievärden och använda formeln hur man tar medelvärdet av siffror som ignorerar nollor här. Men den här formeln fungerar inte med kriterievärden.
Här är alla observationsanteckningar om användning av formeln.
Anmärkningar:
- AGGREGATE -funktionen är den bästa funktionen att använda för dessa problem.
- Funktionen returnerar #VÄRDE! kriterier om inget värde matchar de angivna kriterierna.
- Olika kriterier inuti formeln accepteras, till exempel lika med (=), mindre än (<), mindre än eller lika med (=), större än (>) & inte lika med ().
På detta sätt lärde vi oss att beräkna genomsnittet för siffror som uppfyller specifika kriterier i Excel. Du kan använda dessa funktioner i Excel 2016, 2013 och 2010. Hitta fler artiklar om matematisk formulering med olika kriterier. Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen
Hur man markerar celler över och under genomsnittligt värde: markera värden som ligger över eller under medelvärdet med hjälp av villkorlig formatering i Excel.
Ignorera noll i medeltal: beräkna genomsnittet av siffror i matrisen och ignorera nollor med hjälp av AVERAGEIF -funktionen i Excel.
Beräkna vägt genomsnitt: hitta genomsnittet av värden med olika vikt med funktionen SUMPRODUCT i Excel.
Genomsnittlig skillnad mellan listor: beräkna skillnaden i genomsnitt på två olika listor. Lär dig mer om hur du beräknar genomsnittet med hjälp av matematisk grundformel.
Genomsnittliga siffror om de inte är tomma i Excel: extrahera medelvärdet av värden om cellen inte är tom i excel.
GENNOMSNITT av de tre bästa poängen i en lista i Excel: Hitta genomsnittet av siffror med kriterier som högst 3 nummer från listan i Excel
Populära artiklar:
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
Så här använder du VLOOKUP -funktionen i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.