Iepriekšējā rakstā mēs uzzinājām, kā apkopot augšējās vai apakšējās N vērtības. Šajā rakstā mēs cenšamies apkopot augšējās vai apakšējās N vērtības ar kritērijiem.
TOP N vērtību summa ar kritērijiem
Kā atrisināt problēmu?
Šajā rakstā mums būs jāizmanto funkcija SUMPRODUCT. Tagad mēs izveidosim formulu no šīm funkcijām. Šeit mums ir dots diapazons un kritēriji. Mums ir jāiegūst 5 labākās vērtības diapazonā un jāiegūst vērtību summa, pamatojoties uz dotajiem kritērijiem.
Vispārējā formula:
= SUMPRODUCT (LARGE ((saraksts = kritēriji) * (diapazons), {1, 2,…., N}})
sarakstu: kritēriju saraksts
Kritēriji: kritēriji, kas atbilst
diapazons: vērtību diapazons
vērtības: skaitļi atdalīti, izmantojot komatus, piemēram, ja vēlaties atrast 3 populārākās vērtības, izmantojiet {1, 2, 3}.
Piemērs:
Šeit mums ir datu kopas vērtības no A1: D50.
Pirmkārt, mums ir jāatrod piecas galvenās vērtības, izmantojot funkciju LARGE, kas atbilst pilsētai "Boston", un pēc tam šīm 5 vērtībām jāveic summas darbība. Tagad, lai iegūtu summu, mēs izmantosim šādu formulu
Izmantojiet formulu:
= SUMPRODUCT (LARGE ((City = "Boston") * (daudzums), {1, 2, 3, 4, 5}))
Paskaidrojums:
- City "Boston" atbilst minētajam City diapazonam. Tas atgriež patiesu un nepatiesu masīvu.
- Funkcija LARGE atgriež 5 lielākās skaitliskās vērtības no daudzuma diapazona un atgriež masīvu funkcijai SUMPRODUCT.
= SUMPRODUCT {193, 149, 138, 134, 123}
- Funkcija SUMPRODUCT iegūst 5 lielāko vērtību masīvu, bet 5 labāko skaitļu masīvs atgriež šo skaitļu SUM.
Šeit diapazons Pilsēta un daudzums ir norādīts kā nosauktais diapazons. Nospiediet taustiņu Enter, lai iegūtu 5 populārāko skaitļu SUM.
Kā redzat iepriekšējā momentuzņēmumā, šī summa ir 737. Vērtību summa 193 + 149 + 138 + 134 + 123 = 737.
Iepriekš minētās vērtības varat pārbaudīt datu kopā, izmantojot opciju Excel filtrs. Lietojiet filtru galvenei Pilsēta un daudzums un noklikšķiniet uz bultiņas pogas pilsētas galvenē, kas parādās. Izpildiet tālāk norādītās darbības.
Soļi:
- Atlasiet galvenes šūnu Pilsēta. Lietojiet filtru, izmantojot saīsni Ctrl + Shift + L.
- Noklikšķiniet uz bultiņas, kas tiek parādīta kā filtra opcija.
- Atlasiet opciju (Atlasīt visu).
- Izvēlieties tikai Bostonas pilsētu.
- Tagad izvēlieties daudzuma galveni.
- Kārtojiet sarakstu no lielākā līdz mazākajam, un jūs varat apskatīt visas piecas galvenās vērtības, kuras mēs aprēķinājām, izmantojot formulu.
Kā redzat iepriekš minētajā gif, visas 5 vērtības, kas atbilst norādītajiem kritērijiem. Tas arī nozīmē, ka formula darbojas labi, lai iegūtu šo vērtību skaitu
LIELI N skaitļi
Iepriekš minēto procesu izmanto, lai aprēķinātu dažu skaitļu summu no augšas. Bet, lai aprēķinātu n (liels) vērtību skaits lielā diapazonā.
Izmantojiet formulu:
= SUMPRODUCT (LARGE ((City = "Boston") * (daudzums), ROW (NETIEŠS ("1:10"))
Šeit mēs ģenerējam 10 lielāko vērtību summu, iegūstot masīvu no 1 līdz 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, izmantojot ROW & NETIEŠĀS Excel funkcijas.
Šeit mums ir 10 labāko skaitļu summa, kuras rezultāts ir 1147.
Apakšējo N vērtību summa ar kritērijiem
Kā atrisināt problēmu?
Šajā rakstā mums būs jāizmanto funkcija SUMPRODUCT. Tagad mēs izveidosim formulu no šīm funkcijām. Šeit mums ir dots diapazons, un mums ir jāpazemina 5 vērtības diapazonā un jāiegūst vērtību summa.
Vispārējā formula:
{= SUM (SMALL (IF (City = "Boston", daudzums), {1, 2, 3, 4, 5}))}
Diapazons: vērtību diapazons
Vērtības: skaitļi, kas atdalīti ar komatiem, piemēram, ja vēlaties atrast 3 apakšējās vērtības, izmantojiet {1, 2, 3}.
Piemērs:
To visu var būt mulsinoši saprast. Tātad, pārbaudīsim šo formulu, palaižot to zemāk redzamajā piemērā.
Šeit mums ir vērtību diapazons no A1: D50.
Šeit ir norādīts diapazons Pilsēta un daudzums, izmantojot nosaukto diapazona Excel rīku.
Pirmkārt, mums ir jāatrod piecas zemākās vērtības, izmantojot funkciju SMALL, kas atbilst kritērijiem, un pēc tam šīm 5 vērtībām jāveic summa. Tagad, lai iegūtu summu, mēs izmantosim šādu formulu
Izmantojiet formulu:
{= SUM (SMALL (IF (City = "Boston", daudzums), {1, 2, 3, 4, 5}))}
Nelietojiet cirtainus breketes manuāli. Cirtaini breketes uzliktas, izmantojot Ctrl + Shift + Enter taisnības vietā Ievadiet.
Paskaidrojums:
- Funkcija SMALL ar IF funkciju atgriež 5 apakšējās skaitliskās vērtības, kas atbilst pilsētai "Boston", un atgriež masīvu funkcijai SUM.
= SUMMA ({23, 27, 28, 28, 30}))
- Funkcija SUM iegūst 5 apakšējo vērtību masīvu, kuram ir 5 apakšējo skaitļu masīvs, atgriež to skaitļu SUM, kuri tiek lietoti kopā ar CTRL + SHIFT + ENTER.
Šeit diapazons Pilsēta un daudzums ir norādīts kā nosauktais diapazons. Nospiediet Ctrl + Shift + Enter lai iegūtu SUM no 5 apakšējiem skaitļiem, jo šī ir masīva formula.
Kā redzat iepriekšējā momentuzņēmumā, šī summa ir 136.
Iepriekš minēto procesu izmanto, lai aprēķinātu dažu skaitļu summu no apakšas. Bet, lai aprēķinātu n (liels) vērtību skaits lielā diapazonā.
Izmantojiet formulu:
{ = SUM (SMALL (IF (pilsēta = "Boston", daudzums), ROW (netiešs ("1:10")))) }
NELIETOJIET cirtainās kronšteinus manuāli. Ievadīšanas vietā izmantojiet taustiņu kombināciju Ctrl + Shift + Enter.
Šeit mēs ģenerējam 10 zemāko vērtību summu, iegūstot masīvu no 1 līdz 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, izmantojot ROW & NETIEŠĀS Excel funkcijas.
Šeit ir 10 apakšējo skaitļu summa, kuras rezultātā tiks iegūti 155.
Tālāk ir norādītas dažas novērošanas piezīmes.
Piezīmes:
- Formula darbojas tikai ar skaitļiem.
- Formula darbojas tikai tad, ja uzmeklēšanas tabulā nav dublikātu
- Funkcija SUMPRODUCT neskaitliskas vērtības (piemēram, tekstu abc) un kļūdu vērtības (piemēram, #NUM!, #NULL!) Uzskata par nulles vērtībām.
- Funkcija SUMPRODUCT loģisko vērtību TRUE uzskata par 1 un False par 0.
- Argumentu masīvam jābūt tādā pašā garumā kā funkcijai.
Ceru, ka šis raksts par to, kā atgriezt piecu augšējo vērtību vai 5 zemāko vērtību summu ar kritērijiem programmā Excel, ir paskaidrojošs. Vairāk rakstu par SUMPRODUCT funkcijām meklējiet šeit. Lūdzu, kopīgojiet savu jautājumu zemāk komentāru lodziņā. Mēs jums palīdzēsim.
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ē
Funkcijas SUMPRODUCT izmantošana programmā Excel: Atgriež SUM pēc vērtību reizināšanas vairākos Excel masīvos.
SUM, ja datums ir starp : Atgriež vērtību SUM starp Excel datumiem vai periodu.
Summa, ja datums ir lielāks par norādīto: Atgriež vērtību SUM pēc Excel noteiktā datuma vai perioda.
2 veidi, kā apkopot summu pa mēnešiem programmā Excel: Atgriež Excel summu noteiktā noteiktā mēnesī.
Kā apkopot vairākas kolonnas ar nosacījumu: Atgriež vērtību SUM vairākās kolonnās ar nosacījumu programmā Excel
Kā programmā Excel izmantot aizstājējzīmes : Saskaitiet šūnas, kas atbilst frāzēm, izmantojot Excel aizstājējzīmes
Populāri raksti
50 Excel saīsne produktivitātes paaugstināšanai
Rediģējiet nolaižamo sarakstu
Absolūta atsauce programmā Excel
Ja ar nosacītu formatējumu
Ja ar aizstājējzīmēm
Vlookup pēc datuma
Pārvērst collas par pēdām un collas programmā Excel 2016
Pievienojieties Excel un uzvārdam
Saskaitiet šūnas, kas atbilst A vai B