Ankstesniame straipsnyje mes išmokome apibendrinti viršutines arba apatines N reikšmes. Šiame straipsnyje mes stengiamės apibendrinti viršutines arba apatines N reikšmes pagal kriterijus.
TOP N reikšmių su kriterijais suma
Kaip išspręsti problemą?
Šiame straipsnyje mes turėsime naudoti SUMPRODUCT funkciją. Dabar iš šių funkcijų sudarysime formulę. Čia mums pateikiamas diapazonas ir kriterijai. Turime gauti 5 geriausias diapazono vertes ir gauti vertybių sumą pagal pateiktus kriterijus.
Bendra formulė:
= SUMPRODUCT (DIDELIS ((sąrašas = kriterijai) * (diapazonas), {1, 2,…., N}})
sąrašą: kriterijų sąrašas
Kriterijai: atitikimo kriterijus
diapazonas: reikšmių diapazonas
vertybes: skaičiai atskirti kableliais, pvz., jei norite rasti 3 populiariausias vertes, naudokite {1, 2, 3}.
Pavyzdys:
Čia turime duomenų rinkinio reikšmes iš A1: D50.
Pirmiausia turime rasti penkias didžiausias vertes naudodami LARGE funkciją, kuri atitinka miestą „Bostonas“, ir tada suminė operacija turi būti atlikta per šias 5 vertes. Dabar sumai gauti naudosime šią formulę
Naudokite formulę:
= SUMPRODUCT (DIDELIS ((miestas = "Boston") * (kiekis), {1, 2, 3, 4, 5}))
Paaiškinimas:
- Miesto „Bostonas“ atitinka minėtą „City“ diapazoną. Tai grąžina daugybę teisingų ir klaidingų.
- Funkcija LARGE grąžina 5 didžiausias skaitines reikšmes iš kiekių diapazono ir grąžina masyvą funkcijai SUMPRODUCT.
= SUMPRODUCT {193, 149, 138, 134, 123}
- Funkcija SUMPRODUCT gauna 5 geriausių reikšmių masyvą, o 5 geriausių skaičių masyvas grąžina tų skaičių SUM.
Čia nurodytas diapazonas Miestas ir kiekis. Paspauskite „Enter“, kad gautumėte 5 populiariausių skaičių SUM.
Kaip matote aukščiau esančioje momentinėje nuotraukoje, ši suma yra 737. Skaičių 193 + 149 + 138 + 134 + 123 = 737 suma.
Galite patikrinti aukščiau pateiktas duomenų rinkinio vertes naudodami „Excel“ filtro parinktį. Taikykite filtrą antraštėje Miestas ir kiekis ir spustelėkite rodyklės mygtuką ant rodomos miesto antraštės. Atlikite toliau nurodytus veiksmus.
Žingsniai:
- Pasirinkite miesto antraštės langelį. Taikykite filtrą naudodami nuorodą „Ctrl“ + „Shift“ + L.
- Spustelėkite rodyklę, kuri rodoma kaip filtro parinktis.
- Pasirinkite parinktį (Pasirinkti viską).
- Pasirinkite tik Bostono miestą.
- Dabar pasirinkite kiekio antraštę.
- Rūšiuokite sąrašą nuo didžiausio iki mažiausio ir galite peržiūrėti visas 5 populiariausias vertes, kurias apskaičiavome naudodami formulę.
Kaip matote aukščiau esančiame gif, visos 5 vertės, atitinkančios nurodytus kriterijus. Tai taip pat reiškia, kad formulė puikiai tinka norint gauti šių verčių skaičių
DIDELIS N skaičius
Aukščiau pateiktas procesas naudojamas apskaičiuoti kelių skaičių sumą iš viršaus. Bet paskaičiuoti n (didelis) reikšmių skaičius dideliame diapazone.
Naudokite formulę:
= SUMPRODUCT (DIDELIS ((miestas = "Boston") * (kiekis), EILUTĖ (NETIESIOGINĖ ("1:10"))
Čia mes sugeneruojame 10 geriausių reikšmių, gaudami masyvą nuo 1 iki 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} naudojant „ROW & INDIRECT Excel“ funkcijas.
Čia yra 10 geriausių skaičių suma, kurios rezultatas yra 1147.
Apatinių N reikšmių su kriterijais suma
Kaip išspręsti problemą?
Šiame straipsnyje mes turėsime naudoti SUMPRODUCT funkciją. Dabar iš šių funkcijų sudarysime formulę. Čia mums pateikiamas diapazonas ir turime sumažinti 5 diapazono vertes ir gauti verčių sumą.
Bendra formulė:
{= SUMA (SMALL (IF (miestas = „Boston“, kiekis), {1, 2, 3, 4, 5}))}
Diapazonas: verčių diapazonas
Vertės: skaičiai, atskirti kableliais, pvz., Jei norite rasti apatines 3 reikšmes, naudokite {1, 2, 3}.
Pavyzdys:
Visa tai gali būti painu suprasti. Taigi, išbandykime šią formulę, paleisdami ją žemiau pateiktame pavyzdyje.
Čia turime reikšmių diapazoną nuo A1: D50.
Čia pateikiamas Miesto ir kiekio diapazonas, kaip naudojant pavadintą „Excel“ įrankių diapazoną.
Pirma, mes turime rasti penkias apatines vertes, naudodami funkciją SMALL, kuri atitinka kriterijus, ir tada suminė operacija turi būti atlikta per šias 5 vertes. Dabar sumai gauti naudosime šią formulę
Naudokite formulę:
{= SUMA (SMALL (IF (miestas = „Boston“, kiekis), {1, 2, 3, 4, 5}))}
NENAUDOKITE garbanotų petnešų rankiniu būdu. Garbanotieji breketai uždėti naudojant Ctrl + Shift + Enter vietoje tik Įveskite.
Paaiškinimas:
- Funkcija SMALL su IF funkcija grąžina 5 apatines skaitmenines vertes, kurios atitinka „Boston“ miestą, ir grąžina masyvą į SUM funkciją.
= SUMA ({23, 27, 28, 28, 30}))
- Funkcija SUM gauna 5 apatinių verčių masyvą, kuris turi apatinių 5 skaičių masyvą, grąžina tų skaičių SUM, naudojamų naudojant CTRL + SHIFT + ENTER.
Čia nurodytas diapazonas Miestas ir kiekis. Paspauskite Ctrl + Shift + Enter gauti 5 apatinių skaičių SUM, nes tai yra masyvo formulė.
Kaip matote aukščiau esančioje nuotraukoje, suma yra 136.
Aukščiau pateiktas procesas naudojamas apskaičiuoti kelių skaičių sumą iš apačios. Bet paskaičiuoti n (didelis) reikšmių skaičius dideliame diapazone.
Naudokite formulę:
{ = SUM (SMALL (IF (miestas = "Boston", kiekis), ROW (NETIESIOGINIS ("1:10")))) }
NEGALIMA rankiniu būdu naudoti garbanotus laikiklius. Vietoj „Enter“ naudokite „Ctrl“ + „Shift“ + „Enter“.
Čia mes sugeneruojame 10 apatinių verčių sumą, gaudami masyvą nuo 1 iki 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} naudojant „ROW & INDIRECT Excel“ funkcijas.
Čia yra 10 apatinių skaičių suma, kurios rezultatas bus 155.
Štai keletas stebėjimo pastabų, parodytų žemiau.
Pastabos:
- Formulė veikia tik su skaičiais.
- Formulė veikia tik tada, kai peržiūros lentelėje nėra dublikatų
- Funkcija SUMPRODUCT neskaitines reikšmes (pvz., Tekstą abc) ir klaidų vertes (pvz., #NUM!, #NULL!) Laiko nulinėmis reikšmėmis.
- Funkcija SUMPRODUCT loginę reikšmę TRUE laiko 1, o klaidingą - 0.
- Argumentų masyvas turi būti tokio pat ilgio kaip funkcija.
Tikimės, kad šis straipsnis yra aiškinantis, kaip grąžinti 5 geriausių ar 5 apatinių verčių sumą su kriterijais „Excel“. Daugiau straipsnių apie SUMPRODUCT funkcijas rasite čia. Prašome pasidalinti savo užklausa žemiau komentarų laukelyje. Mes jums padėsime.
Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Parašykite mums el
Kaip naudoti „SUMPRODUCT“ funkciją programoje „Excel“: Grąžina SUM, padauginus reikšmes keliuose „Excel“ masyvuose.
SUM, jei data yra tarp : Grąžina verčių SUM tarp nurodytų datų ar „Excel“ laikotarpio.
Suma, jei data yra didesnė už nurodytą datą: Grąžina verčių SUM po nurodytos datos ar „Excel“ laikotarpio.
2 būdai suskaičiuoti pagal mėnesį „Excel“: Grąžina verčių SUM per tam tikrą „Excel“ mėnesį.
Kaip suskaičiuoti kelis stulpelius su sąlyga: Grąžina verčių SUM keliuose stulpeliuose su sąlyga „Excel“
Kaip „Excel“ naudoti pakaitos simbolius : Suskaičiuokite frazes atitinkančias frazes naudodami „Excel“ pakaitos simbolius
Populiarūs straipsniai
50 „Excel“ spartusis klavišas produktyvumui padidinti
Redaguoti išskleidžiamąjį sąrašą
Absoliuti nuoroda „Excel“
Jei su sąlyginiu formatavimu
Jei su pakaitos simboliais
„Vlookup“ pagal datą
Konvertuokite colius į pėdas ir colius programoje „Excel 2016“
Prisijunkite prie „Excel“ vardo ir pavardės
Suskaičiuokite A arba B atitinkančias ląsteles
