Šajā rakstā mēs iemācīsimies aprēķināt vidējo skaitli, kas programmā Excel atbilst noteiktajiem kritērijiem.
Scenārijs:
Strādājot ar datiem, mums jāiegūst vidējais skaitlis ar kritēriju vērtībām. Kāpēc kritēriju vērtības rada atšķirības rezultātos, Excel funkcijai vajadzētu to uzskatīt par nulli un ņemt rezultātu. Nulles vērtība var mainīt skaitļu vidējo vērtību. Vidējais skaitlis ir nepieciešamo skaitļu summa, kas dalīta ar skaitļu skaitu. Ja nulle neveicina summu, bet palielina skaitli. Saprast, kā atrisināt problēmu.
Kā atrisināt problēmu?
Šeit mums jāatrod vidējais skaitlis ar kritērijiem. Kritērijs ir kritēriju vērtību ignorēšana. Ja jums ir tikai pozitīvi vai negatīvi skaitļi, varat izmantot funkciju AVERAGEIF
Formulas sintakse (pozitīvi skaitļi):
| = AVERAGEIF (vērtības, "> = 0") |
Iepriekš minētā formula ietver nulles, bet neņem vērā kritēriju vērtības. Ja vēlaties ignorēt nulles un kritēriju vērtības, izmantojiet kritērijus kā "> 0".
Līdzīgi jūs varat iegūt vidējo negatīvo vērtību, ieskaitot nulles, izmantojot formulu:
Formulas sintakse (negatīvi skaitļi):
| = AVERAGEIF (vērtības, "<= 0") |
Iepriekš minētā formula ietver nulles, bet neņem vērā kritēriju vērtības. Ja vēlaties ignorēt nulles un kritēriju vērtības, izmantojiet kritērijus kā "> 0"
Bet problēma, apsverot skaitļus, ignorējot kritērijus, nav atrisināta. Jūs varētu domāt, ka, ņemot vērā iepriekš minēto divu rezultātu vidējo vērtību (pozitīvo skaitļu vidējais un negatīvo skaitļu vidējais rādītājs), tiks iegūta vajadzīgā vērtība. Tas nedarbojas labi, kamēr pozitīvo skaitļu skaits nav vienāds ar negatīvo skaitļu skaitu. Šai problēmai mēs izmantosim funkciju AGGREGATE. Funkcija AGGREGATE ir matemātisko funkciju pakete. Tas palīdz vispārīgi risināt šāda veida problēmas. Lai izpakotu šo pakotni vidējām vērtībām, ignorējot kritērijus. Izmantojiet zemāk esošo formulu.
Formulas sintakse (visiem skaitļiem):
| = APLIKT (1, 6, vērtības) |
1: arguments vidējiem skaitļiem
6: kritēriju vērtību ignorēšana
Piemērs :
To visu var būt mulsinoši saprast. Ņemsim piemēru un sapratīsim, kā piemērā izmantot šīs formulas. Šeit ir to vērtību saraksts, kurām nepieciešams vidējais.
Pirmkārt, mēs aprēķinām vidējo pozitīvo skaitli. Šim nolūkam mēs izmantosim tālāk norādīto informāciju.
Izmantojiet formulu:
| = AVERAGEIF (B3: B15, "> = 0") |
Skaidrojums
- Kritērijs ir skaitļi, kas ir lielāki vai vienādi ar nulli.
- Funkcija AVERAGEIF ņem vērā tikai skaitļus, kas atbilst kritērijiem, un atgriež skaitļu vidējo vērtību
Šeit formula atgriež 58,6, ņemot vērā tikai pozitīvās vērtības, kur pozitīvo vērtību skaits ir 7. Līdzīgi jūs varat iegūt negatīvo skaitļu vidējo.
Izmantojiet formulu:
| = AVERAGEIF (B3: B15, "<= 0") |
Skaidrojums
- Kritērijs ir skaitļi, kas ir mazāki vai vienādi ar nulli.
- Funkcija AVERAGEIF ņem vērā tikai skaitļus, kas atbilst kritērijiem, un atgriež skaitļu vidējo vērtību.
Negatīviem skaitļiem vidējais ir -21,0, ieskaitot nulles, ignorējot kritēriju vērtības. Uzskaitīto skaitļu skaitlis ir 4.
Šajā piemērā negatīvo skaitļu skaits nav vienāds ar pozitīvo skaitļu skaitu. Tātad vidējais no 2 iepriekš minētajiem rezultātiem nedos visu skaitļu vidējo. Šiem gadījumiem izmantojiet zemāk esošo formulu.
Izmantojiet formulu:
| = APLIKT (1, 6, B3: B15) |
Paskaidrojums:
- Arguments 1 ir dots, lai izmantotu vidējo funkciju.
- Arguments 6 tiek sniegts, lai ignorētu kritēriju vērtības.
- Funkcija AGGREGATE atgriež visu skaitļu vidējo vērtību B3: B15 masīvā, ignorējot kritēriju vērtības.
Visu skaitļu vidējais rādītājs, ignorējot visas kritēriju vērtības, ir 36,2. Šeit skaitļu skaits ir 9. AGGREGATE funkcija ir labākais veids, kā atrisināt šīs problēmas, jo šīs funkcijas ir Excel iebūvētās funkcijas.
Ja jūs mēģināt iegūt vidējo skaitli, izmantojot pamatfunkciju AVERAGE, jums jāizmanto zemāk esošā formula, lai iegūtu vidējo skaitli, kas ignorē kritērijus.
Izmantojiet formulu:
| { = VIDĒJĀ (JA (ISkritēriji (B3: B15), "", B3: B15)) } |
Piezīme : nelietojiet cirtainās breketes manuāli. Vienkārši izmantojiet Ctrl + Shift + Enter vietā Ievadiet lai iegūtu rezultātu.
Paskaidrojums:
- Funkcija IF pārbauda stāvokli ar IScriteria funkciju B3: B15 masīvā.
- Funkcija IScriteria atgriež vērtību True, ja vērtība ir kritērijs, un False, ja nē.
- Funkcija IF atgriež tukšu vērtību, ja vērtība ir kritērijs, un atgriež atbilstošo vērtību, ja vērtība nav kritērijs.
- Funkcija AVERAGE atgriež atgriezto vērtību vidējo.
- Cirtainās breketes liek šīm loģiskajām funkcijām darboties masīvā.
Kā redzat, formula darbojas labi un atgriež to pašu rezultātu. Bet izmantojiet funkciju AGGREGATE, to ir viegli izmantot dažādām matemātiskām funkcijām ar dažādiem iebūvētiem kritērijiem.
Ja masīvā nav nulles vērtības. Šeit varat izmantot nulles atgriešanās vērtību no visām kritēriju vērtībām un izmantot formulu, kā aprēķināt vidējo skaitli, ignorējot nulles. Bet šī formula nedarbojas ar kritēriju vērtībām.
Šeit ir visas novērošanas piezīmes par formulas izmantošanu.
Piezīmes:
- Funkcija AGGREGATE ir labākā funkcija šo problēmu risināšanai.
- Funkcija atgriež #VALUE! kritērijiem, ja neviena vērtība neatbilst dotajiem kritērijiem.
- Formulā tiek pieņemti dažādi kritēriji, piemēram, vienāds ar (=), mazāks par (<), mazāks vai vienāds ar (=), lielāks par (>) un nav vienāds ar ().
Tādā veidā mēs iemācījāmies aprēķināt vidējo skaitu, kas programmā Excel atbilst noteiktajiem kritērijiem. Šīs funkcijas varat izmantot programmā Excel 2016, 2013 un 2010. Atrodiet citus rakstus par matemātisko formulējumu ar dažādiem kritērijiem. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē
Kā izcelt šūnas virs un zem vidējās vērtības: izceliet vērtības, kas ir virs vai zem vidējās vērtības, izmantojot nosacījuma formatējumu programmā Excel.
Skaitļu vidējā rādītājā ignorēt nulli: aprēķiniet masīva vidējo skaitu, ignorējot nulles, izmantojot funkciju AVERAGEIF programmā Excel.
Aprēķināt vidējo svērto vērtību: atrodiet vidējo vērtību vērtību ar atšķirīgu svaru, izmantojot funkciju SUMPRODUCT programmā Excel.
Vidējā atšķirība starp sarakstiem: aprēķiniet divu dažādu sarakstu vidējo atšķirību. Uzziniet vairāk par vidējā aprēķināšanu, izmantojot matemātiskā vidējā pamata formulu.
Vidējie skaitļi programmā Excel: ja tie nav tukši: iegūt vidējo vērtību, ja šūna programmā Excel nav tukša.
VIDĒJĀ vieta labāko rezultātu sarakstā Excel: Atrodiet Excel vidējo skaitli, kura kritēriji ir augstākie 3 skaitļi
Populāri raksti:
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.