Šajā rakstā mēs uzzināsim, kā Excel apkopot augšējās vai apakšējās N vērtības, ņemot vērā skaitļu masīvu. Ja vēlaties noteiktā diapazonā apkopot top 4, top 5 vai top N vērtības, šis raksts palīdzēs to izdarīt.
TOP N vērtību summa
Š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āpievieno piecas vērtības diapazonā un jāiegūst vērtību summa.
Vispārējā formula:
= SUMPRODUCT (LIELS (diapazons, {1, 2,…., N}})
diapazons: vērtību diapazons
Vērtības: skaitļi, kas atdalīti ar komatiem, piemēram, ja vēlaties atrast 3 populārākās vērtības, izmantojiet {1, 2, 3}.
Piemērs: Summa Top 5 vērtības diapazonā
Pārbaudīsim šo formulu, palaižot to zemāk redzamajā piemērā.
Šeit mums ir vērtību diapazons no A1: A23.
Diapazons A1: A23 satur vērtības, un tas tiek nosaukts kā vērtības.
Pirmkārt, mums ir jāatrod piecas galvenās vērtības, izmantojot funkciju LARGE, un pēc tam šīm 5 vērtībām jāveic summas darbība. Tagad mēs izmantosim šādu formulu, lai iegūtu 5 lielāko vērtību summu.
Izmantojiet formulu:
= SUMPRODUCT (LARGE (diapazons, {1, 2, 3, 4, 5}))
Paskaidrojums:
- Funkcija LARGE atgriež 5 populārākās skaitliskās vērtības un atgriež masīvu funkcijai SUMPRODUCT.
= SUMPRODUCT ({149, 123, 100, 87, 85}))
- Funkcija SUMPRODUCT apkopo 5 populārāko vērtību masīvu.
Šeit diapazons ir norādīts kā nosauktais diapazons. Nospiediet Enter, lai iegūtu 5 populārāko skaitļu SUM.
Kā redzat iepriekšējā momentuzņēmumā, šī summa ir 544. Vērtību summa 149 + 123 + 100 + 87 + 85 = 544.
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 (diapazons, Rinda (NETIEŠA ("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 mēs saņēmām 10 labāko skaitļu summu {149; 123; 100; 87; 85; 82; 61; 58; 54; 51}), kura rezultāts ir 850.
Apakšējo N vērtību summa
Kā atrisināt problēmu?
Vispārējā formula:
= SUMPRODUCT (SMALL (diapazons, {1, 2,…., N}})
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: A23.
Šeit ir diapazons, kas tiek piešķirts, izmantojot nosaukto diapazona Excel rīku.
Pirmkārt, mums ir jāatrod piecas zemākās vērtības, izmantojot funkciju SMALL, 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 (SMALL (diapazons, {1, 2, 3, 4, 5}))
Paskaidrojums:
- Funkcija SMALL atgriež 5 apakšējās skaitliskās vērtības un atgriež masīvu funkcijai SUMPRODUCT.
= SUMPRODUCT ({23, 27, 28, 28, 30}))
- Funkcija SUMPRODUCT apkopo 5 apakšējo vērtību masīvu.
Šeit diapazons ir norādīts kā nosauktais diapazons. Nospiediet taustiņu Enter, lai iegūtu 5 apakšējo skaitļu SUM.
Kā redzat iepriekšējā momentuzņēmumā, šī summa ir 136. Vērtību summa 23 + 27 + 28 + 28 + 30 = 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:
= SUMPRODUCT (SMALL (diapazons, Rinda (NETIEŠA ("1:10"))
Š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 mums ir 10 apakšējo skaitļu summa, kuras rezultātā tiks iegūti 307.
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ā programmā Excel atgriezt augšējo n vērtību vai apakšējo n vērtību summu, 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.
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
