Šī apmācība parāda, kā izmantot Excel AVERAGEIF un AVERAGEIFS funkcijas programmā Excel un Google izklājlapas, lai noteiktu vidējos datus, kas atbilst noteiktiem kritērijiem.
AVERAGEIF funkciju pārskats
Programmā Excel varat izmantot funkciju AVERAGEIF, lai saskaitītu šūnas, kurās ir noteikta vērtība, saskaitītu šūnas, kas ir lielākas vai vienādas ar vērtību utt.
Lai izmantotu AVERAGEIF Excel darblapas funkciju, atlasiet šūnu un ierakstiet:
(Ievērojiet, kā parādās formulas ievades)
Funkcijas AVERAGEIF sintakse un argumenti:
= AVERAGEIF (diapazons, kritēriji, [vidējais_diapazons])
diapazons - Šūnu diapazons, kas jāskaita.
kritērijiem - Kritēriji, kas nosaka, kuras šūnas jāuzskaita.
vidēji_ diapazons - [pēc izvēles] Vidējās šūnas. Ja tas tiek izlaists, tiek izmantots diapazons.
Kas ir funkcija AVERAGEIF?
Funkcija AVERAGEIF ir viena no vecākajām funkcijām, ko izmanto izklājlapās. To izmanto, lai skenētu šūnu diapazonu, pārbaudot konkrētu kritēriju un pēc tam norādot vidējo (jeb matemātisko vidējo), ja vērtības diapazonā atbilst šīm vērtībām. Sākotnējā AVERAGEIF funkcija bija ierobežota tikai ar vienu kritēriju. Pēc 2007. gada tika izveidota funkcija AVERAGEIFS, kas pieļauj dažādus kritērijus. Lielākā daļa vispārējā lietojuma starp abiem paliek nemainīgi, taču sintaksē ir dažas būtiskas atšķirības, kuras mēs apspriedīsim šajā rakstā.
Ja vēl neesat to izdarījis, varat pārskatīt lielu daļu līdzīgas struktūras un piemērus rakstā COUNTIFS.
Pamata piemērs
Apskatīsim šo reģistrēto pārdošanas sarakstu un vēlamies uzzināt vidējos ienākumus.
Tā kā mums bija izdevumi, negatīvā vērtība, mēs nevaram veikt tikai pamata vidējo. Tā vietā mēs vēlamies aprēķināt tikai tās vērtības, kas ir lielākas par 0. “Lielāks par 0” ir mūsu kritēriji funkcijā AVERAGEIF. Mūsu formula, kā to pateikt
= AVERAGEIF (A2: A7, "> 0")
Divu kolonnu piemērs
Lai gan sākotnējā funkcija AVERAGEIF tika izstrādāta, lai ļautu jums piemērot kritēriju skaitļu diapazonam, kuru vēlaties summēt, lielāko daļu laika jums būs jāpiemēro viens vai vairāki kritēriji citām kolonnām. Apskatīsim šo tabulu:
Tagad, ja mēs izmantojam sākotnējo AVERAGEIF funkciju, lai noskaidrotu, cik banānu mums ir vidēji. Mēs ievietosim savus kritērijus šūnā D1, un mums būs jānorāda vēlamais diapazons vidēji kā pēdējais arguments, un tā būtu mūsu formula
= AVERAGEIF (A2: A7, D1, B2: B7)
Tomēr, kad programmētāji galu galā saprata, ka lietotāji vēlas norādīt vairāk nekā vienu kritēriju, tika izveidota funkcija AVERAGEIFS. Lai izveidotu vienu struktūru, kas derētu vairākiem kritērijiem, AVERAGEIFS pieprasa vispirms norādīt summu diapazonu. Mūsu piemērā tas nozīmē, ka formulai ir jābūt
= AVERAGEIFS (B2: B7, A2: A7, D1)
PIEZĪME. Abām formulām ir vienāds rezultāts un tās var izskatīties līdzīgas, tāpēc pievērsiet uzmanību tam, kura funkcija tiek izmantota, lai pārliecinātos, ka visi argumenti ir uzskaitīti pareizā secībā.
Darbs ar datumiem, vairāki kritēriji
Strādājot ar datumiem izklājlapā, lai gan datumu ir iespējams ievadīt tieši formulā, vislabāk ir datumu iekļaut šūnā, lai jūs varētu vienkārši atsaukties uz šūnu šūnā. Piemēram, tas palīdz datoram zināt, ka vēlaties izmantot datumu 27.5.2020, nevis skaitli 5 dalot ar 27, dalot ar 2022. gadu.
Apskatīsim nākamo tabulu, kurā reģistrēts vietnes apmeklētāju skaits ik pēc divām nedēļām.
Mēs varam norādīt diapazona sākuma un beigu punktus, kurus vēlamies apskatīt D2 un E2. Mūsu formula, lai atrastu vidējo apmeklētāju skaitu šajā diapazonā, varētu būt šāda:
= AVERAGEIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Ņemiet vērā, kā mēs varējām apvienot “=” salīdzinājumus ar šūnu atsaucēm, lai izveidotu kritērijus. Turklāt, lai gan abi kritēriji tika piemēroti vienam un tam pašam šūnu diapazonam (A2: A7), diapazons ir jāraksta divreiz, vienu reizi katrā kritērijā.
Vairākas kolonnas
Izmantojot vairākus kritērijus, varat tos piemērot tam pašam diapazonam, kā mēs to darījām iepriekšējā piemērā, vai arī varat tos piemērot dažādiem diapazoniem. Apvienosim mūsu datu paraugus šajā tabulā:
Mēs esam iestatījuši dažas šūnas, lai lietotājs šūnās E2 līdz G2 ievadītu to, ko vēlas meklēt. Tādējādi mums ir vajadzīga formula, kas saskaitīs februārī savākto ābolu kopskaitu. Mūsu formula izskatās šādi:
= AVERAGEIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
AVERAGEIFS ar OR tipa loģiku
Līdz šim visi izmantotie piemēri ir bijuši salīdzināmi ar AND, un mēs meklējam rindas, kas atbilst visiem mūsu kritērijiem. Tagad mēs izskatīsim gadījumu, kad vēlaties meklēt iespēju, ka rinda atbilst vienam vai otram kritērijam.
Apskatīsim šo pārdošanas sarakstu:
Mēs vēlētos saskaitīt vidējos pārdošanas apjomus gan Ādamam, gan Bobam. Pirmkārt, ātra diskusija par vidējo rādītāju ņemšanu. Ja jums ir nevienmērīgs skaits lietu, piemēram, 3 ieraksti Ādamam un 2 Bobam, jūs nevarat vienkārši ņemt katras personas pārdošanas vidējo vērtību. To sauc par vidējo vidējo rādītāju, un jūs galu galā piešķirat negodīgu svērumu vienumam, kurā ir maz ierakstu. Ja tas tā ir ar jūsu datiem, jums ir jāaprēķina vidējais “manuālais” veids: ņemiet visu vienumu summu, dalītu ar vienumu skaitu. Lai apskatītu, kā to izdarīt, varat izlasīt rakstus šeit:
Tagad, ja ierakstu skaits ir vienāds, piemēram, mūsu tabulā, jums ir vairākas iespējas. Vienkāršākais ir pievienot divus AVERAGEIFS kopā un pēc tam dalīt ar 2 (vienumu skaits mūsu sarakstā)
= (AVERAGEIFS (B2: B7, A2: A7, "Adam")+AVERAGEIFS (B2: B7, A2: A7, "Bob"))/2
Šeit mēs esam likuši datoram aprēķināt mūsu individuālos rādītājus un pēc tam tos saskaitīt.
Mūsu nākamā iespēja ir piemērota, ja jums ir vairāk kritēriju diapazonu, piemēram, ja nevēlaties, lai visa formula būtu jāpārraksta atkārtoti. Iepriekšējā formulā mēs manuāli teicām datoram pievienot divus dažādus AVERAGEIFS kopā. Tomēr to var izdarīt, ierakstot savus kritērijus masīvā, piemēram:
= VIDĒJĀ (AVERAGEIFS (B2: B7, A2: A7, {"Ādams", "Bobs"}))
Paskatieties, kā masīvs ir veidots cirtainās iekavās. Kad dators novērtēs šo formulu, tas zinās, ka mēs vēlamies aprēķināt AVERAGEIFS funkciju katram mūsu masīva vienumam, tādējādi izveidojot skaitļu masīvu. Ārējā VIDĒJĀ funkcija pēc tam ņems šo skaitļu masīvu un pārvērtīs to par vienu skaitli. Veicot formulas novērtējumu, tas izskatītos šādi:
= VIDĒJĀ (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"}))) = AVERAGE (13701, 21735) = 17718
Mēs iegūstam tādu pašu rezultātu, bet mēs varējām formulēt formulējumu nedaudz lakoniskāk.
Darbs ar sagatavēm
Dažreiz jūsu datu kopā ir tukšas šūnas, kuras jums jāatrod vai jāizvairās. To kritēriju noteikšana var būt nedaudz sarežģīta, tāpēc apskatīsim citu piemēru.
Ņemiet vērā, ka šūna A3 ir patiesi tukša, savukārt šūnā A5 ir formula, kas atgriež nulles garuma virkni “”. Ja mēs vēlamies atrast kopējo vidējo patiesi tukšas šūnas, mēs izmantotu kritēriju “=”, un mūsu formula izskatītos šādi:
= AVERAGEIFS (B2: B7, A2: A7, "=")
No otras puses, ja mēs vēlamies iegūt vidējo vērtību visām šūnām, kuras vizuāli izskatās tukšas, mēs mainīsim kritērijus uz “”, un formula izskatās šādi
= AVERAGEIFS (B2: B7, A2: A7, "")
Apvērsīsim to: ko darīt, ja vēlaties atrast tukšo šūnu vidējo vērtību? Diemžēl pašreizējais dizains neļaus jums izvairīties no nulles garuma virknes. Jūs varat izmantot kritēriju “”, bet, kā redzams piemērā, tas joprojām ietver vērtību no 5. rindas.
= AVERAGEIFS (B2: B7, A2: A7, "")
Ja jums nav jāskaita šūnas, kurās ir nulles garuma virknes, apsveriet iespēju izmantot funkciju LEN SUMPRODUCT
AVERAGEIF Google izklājlapās
Funkcija AVERAGEIF Google izklājlapās darbojas tieši tāpat kā programmā Excel:
