Ši pamoka parodo, kaip naudoti „Excel AVERAGEIF“ ir „AVERAGEIFS“ funkcijas „Excel“ ir „Google“ skaičiuoklėse, norint apskaičiuoti tam tikrus kriterijus atitinkančius duomenis.
AVERAGEIF funkcijų apžvalga
Galite naudoti funkciją „AVERAGEIF“ programoje „Excel“, kad suskaičiuotumėte ląsteles, kuriose yra tam tikra reikšmė, suskaičiuotumėte ląsteles, kurios yra didesnės arba lygios reikšmei ir pan.
Norėdami naudoti „AVERAGEIF Excel“ darbalapio funkciją, pasirinkite langelį ir įveskite:
(Atkreipkite dėmesį, kaip atrodo formulės įvestys)
AVERAGEIF funkcijos sintaksė ir argumentai:
= AVERAGEIF (diapazonas, kriterijai, [vidutinis_diapazonas])
diapazonas - Skaičiuojamų ląstelių diapazonas.
kriterijai - Kriterijai, pagal kuriuos kontroliuojama, kurios ląstelės turėtų būti skaičiuojamos.
vidutinis_dydis - [neprivaloma] Ląstelių vidurkis. Kai praleidžiama, naudojamas diapazonas.
Kas yra AVERAGEIF funkcija?
Funkcija AVERAGEIF yra viena iš senesnių funkcijų, naudojamų skaičiuoklėse. Jis naudojamas skenuoti per langelių diapazoną, tikrinant konkretų kriterijų, ir tada pateikiant vidurkį (dar žinomą kaip matematinį vidurkį), jei diapazono vertės atitinka tas reikšmes. Pradinė AVERAGEIF funkcija buvo apribota tik vienu kriterijumi. Po 2007 m. Buvo sukurta funkcija AVERAGEIFS, kuri leidžia taikyti daugybę kriterijų. Didžioji dalis bendro naudojimo tarp jų išlieka ta pati, tačiau yra keletas esminių sintaksės skirtumų, kuriuos aptarsime šiame straipsnyje.
Jei to dar nepadarėte, galite peržiūrėti daug panašios struktūros ir pavyzdžių straipsnyje COUNTIFS.
Pagrindinis pavyzdys
Apsvarstykite šį įrašytų pardavimų sąrašą ir norime sužinoti vidutines pajamas.
Kadangi turėjome išlaidų, neigiamą vertę, negalime padaryti tik bazinio vidurkio. Vietoj to, mes norime vidurkinti tik tas reikšmes, kurios yra didesnės nei 0. „Didesnis nei 0“ yra mūsų kriterijai, naudojant funkciją AVERAGEIF. Mūsų formulė tai pasakyti
= AVERAGEIF (A2: A7, "> 0")
Dviejų stulpelių pavyzdys
Nors pradinė AVERAGEIF funkcija buvo sukurta taip, kad galėtumėte taikyti kriterijų skaičių diapazonui, kurį norite sudėti, daugumą laiko turėsite taikyti vieną ar kelis kriterijus kitiems stulpeliams. Apsvarstykite šią lentelę:
Dabar, jei naudosime originalią AVERAGEIF funkciją, norėdami sužinoti, kiek bananų turime vidutiniškai. Mes įvesime savo kriterijus į D1 langelį ir turėsime nurodyti norimą diapazoną vidutinis kaip paskutinis argumentas, ir tokia būtų mūsų formulė
= AVERAGEIF (A2: A7, D1, B2: B7)
Tačiau kai programuotojai galiausiai suprato, kad vartotojai nori pateikti daugiau nei vieną kriterijų, buvo sukurta funkcija AVERAGEIFS. Norint sukurti vieną struktūrą, kuri atitiktų bet kokį kriterijų skaičių, AVERAGEIFS reikalauja, kad pirmiausia būtų nurodytas sumų diapazonas. Mūsų pavyzdyje tai reiškia, kad formulė turi būti tokia
= VIDUTINIAI (B2: B7, A2: A7, D1)
PASTABA: Šios dvi formulės gauna tą patį rezultatą ir gali atrodyti panašiai, todėl atidžiai stebėkite, kuri funkcija naudojama, kad būtinai išvardytumėte visus argumentus teisinga tvarka.
Darbas su datomis, keli kriterijai
Dirbant su datomis skaičiuoklėje, nors datą galima įvesti tiesiai į formulę, geriausia, kad data būtų langelyje, kad galėtumėte tiesiog nurodyti formulės langelį. Pavyzdžiui, tai padeda kompiuteriui žinoti, kad norite naudoti datą 2020-05-27, o ne skaičių 5, padalytą iš 27, padalytą iš 2022 m.
Pažvelkime į kitą lentelę, kurioje kas dvi savaites įrašomas svetainės lankytojų skaičius.
D2 ir E2 galime nurodyti diapazono, į kurį norime žiūrėti, pradžios ir pabaigos taškus. Mūsų formulė, pagal kurią galima rasti vidutinį lankytojų skaičių šiame diapazone, gali būti tokia:
= AVERAGEIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Atkreipkite dėmesį, kaip mums pavyko sujungti „=“ palyginimus su ląstelių nuorodomis, kad sukurtume kriterijus. Be to, nors abu kriterijai buvo taikomi tam pačiam langelių diapazonui (A2: A7), diapazoną turite parašyti du kartus, vieną kartą pagal kiekvieną kriterijų.
Keli stulpeliai
Kai naudojate kelis kriterijus, galite juos taikyti tame pačiame diapazone, kaip ir ankstesniame pavyzdyje, arba galite taikyti skirtingiems diapazonams. Sujunkime pavyzdinius duomenis į šią lentelę:
Nustatėme kai kuriuos langelius, kad vartotojas įvestų tai, ko nori ieškoti ląstelėse E2 – G2. Taigi mums reikia formulės, kuri sudarytų bendrą vasario mėnesį nuskintų obuolių skaičių. Mūsų formulė atrodo taip:
= AVERAGEIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
AVERAGEIFS su OR tipo logika
Iki šiol visi mūsų naudojami pavyzdžiai buvo palyginimas AND ir ieškome eilučių, atitinkančių visus mūsų kriterijus. Dabar mes apsvarstysime atvejį, kai norite ieškoti galimybės, kad eilė atitiktų vieną ar kitą kriterijų.
Pažvelkime į šį pardavimo sąrašą:
Norėtume pridėti vidutinius Adomo ir Bobo pardavimus. Pirma, greita diskusija apie vidurkių nustatymą. Jei turite netolygų skaičių dalykų, pvz., 3 įrašai Adomui ir 2 Bobui, jūs negalite tiesiog atsižvelgti į kiekvieno asmens pardavimo vidurkį. Tai vadinama vidurkių vidurkio nustatymu, o jūs galų gale nesąžiningai įvertinate elementą, kuriame yra nedaug įrašų. Jei taip yra su jūsų duomenimis, turite apskaičiuoti vidurkį „rankiniu būdu“: paimkite visų elementų sumą, padalytą iš elementų skaičiaus. Norėdami peržiūrėti, kaip tai padaryti, galite perskaityti straipsnius čia:
Dabar, jei įrašų skaičius yra toks pat, kaip mūsų lentelėje, galite pasirinkti keletą variantų. Paprasčiausia yra pridėti du AVERAGEIFS kartu, ir taip padalyti iš 2 (elementų skaičius mūsų sąraše)
= (AVERAGEIFS (B2: B7, A2: A7, „Adam“)+AVERAGEIFS (B2: B7, A2: A7, „Bob“))/2
Čia kompiuteris apskaičiavo mūsų individualius balus ir tada juos sudėjo.
Kitas mūsų pasirinkimas tinka, kai turite daugiau kriterijų diapazonų, todėl nenorite, kad visa formulė turėtų būti pakartotinai perrašyta. Ankstesnėje formulėje rankiniu būdu liepėme kompiuteriui pridėti du skirtingus AVERAGEIFS. Tačiau tai galite padaryti ir įvesdami kriterijus į masyvą, pavyzdžiui:
= VIDUTINIS (AVERAGEIFS (B2: B7, A2: A7, {"Adomas", "Bobas"}))
Pažiūrėkite, kaip masyvas sukonstruotas garbanotų skliaustų viduje. Kai kompiuteris įvertins šią formulę, jis žinos, kad mes norime apskaičiuoti AVERAGEIFS funkciją kiekvienam mūsų masyvo elementui, taip sukurdami skaičių masyvą. Išorinė VIDUTINĖ funkcija paims tą skaičių masyvą ir pavers jį vienu skaičiumi. Žvelgiant į formulės vertinimą, tai atrodytų taip:
= VIDUTINIS (AVERAGEIFS (B2: B7, A2: A7, {"Adomas", "Bobas"}))) = VIDUTINIS (13701, 21735) = 17718
Gauname tą patį rezultatą, tačiau sugebėjome formulę išrašyti kiek lakoniškiau.
Tvarkymasis su ruošiniais
Kartais jūsų duomenų rinkinyje yra tuščių langelių, kuriuos turite surasti arba jų išvengti. Šių kriterijų nustatymas gali būti šiek tiek sudėtingas, todėl pažvelkime į kitą pavyzdį.
Atminkite, kad langelis A3 yra tuščias, o A5 langelis turi formulę, grąžinančią nulinio ilgio eilutę „“. Jei norime rasti bendrą vidurkį nuoširdžiai tuščių langelių, mes naudojame kriterijų „=“, o mūsų formulė atrodytų taip:
= VIDUTINIAI (B2: B7, A2: A7, "=")
Kita vertus, jei norime gauti visų ląstelių, kurios vizualiai atrodo tuščios, vidurkį, pakeisime kriterijus į „“, o formulė atrodo taip
= VIDUTINIAI (B2: B7, A2: A7, "")
Apverskime: ką daryti, jei norite rasti tuščių langelių vidurkį? Deja, dabartinis dizainas neleis išvengti nulio ilgio eilutės. Galite naudoti kriterijų „“, tačiau, kaip matote pavyzdyje, jis vis tiek apima 5 eilutės reikšmę.
= VIDUTINIAI (B2: B7, A2: A7, "")
Jei reikia neskaičiuoti langelių, kuriuose yra nulinio ilgio eilučių, apsvarstykite galimybę naudoti funkciją LEN SUMPRODUCT
AVERAGEIF „Google“ skaičiuoklėse
Funkcija AVERAGEIF „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
