V prejšnjem članku smo se naučili, kako sešteti zgornje ali spodnje N vrednosti. V tem članku poskušamo z merili povzeti zgornje ali spodnje N vrednosti.
Vsota TOP N vrednosti s kriteriji
Kako rešiti težavo?
Za ta članek bomo morali uporabiti funkcijo SUMPRODUCT. Zdaj bomo iz teh funkcij naredili formulo. Tukaj imamo razpon in merilo. V razponu moramo dobiti prvih 5 vrednosti in na podlagi danih meril dobiti vsoto vrednosti.
Splošna formula:
= SUMPRODUCT (LARGE ((seznam = merila) * (obseg), {1, 2,…., N}})
seznam: seznam meril
Merila: merila, ki se ujemajo
obseg: obseg vrednosti
vrednote: številke, ločene z vejicami, na primer, če želite poiskati prve tri vrednosti, uporabite {1, 2, 3}.
Primer:
Tu imamo vrednosti nabora podatkov od A1: D50.
Najprej moramo poiskati prvih pet vrednosti s funkcijo LARGE, ki se ujema z mestom "Boston", nato pa nad temi 5 vrednostmi izvesti operacijo seštevanja. Zdaj bomo uporabili naslednjo formulo, da dobimo vsoto
Uporabite formulo:
= PODROČJE (VELIKO ((Mesto = "Boston") * (količina), {1, 2, 3, 4, 5}))
Pojasnilo:
- City "Boston" se ujema z omenjenim obsegom City. To vrne polje true in false.
- Funkcija LARGE vrne prvih 5 numeričnih vrednosti iz obsega količin in vrne matriko v funkcijo SUMPRODUCT.
= PODROČJE {193, 149, 138, 134, 123}
- Funkcija SUMPRODUCT dobi niz prvih 5 vrednosti, pri čemer niz prvih 5 številk vrne SUM teh števil.
Tukaj je območje mesta in količine podano kot imenovano območje. Pritisnite Enter, da dobite SUM prvih 5 številk.
Kot lahko vidite na zgornjem posnetku, je ta vsota 737. Vsota vrednosti 193 + 149 + 138 + 134 + 123 = 737.
Zgornje vrednosti v nizu podatkov lahko preverite z možnostjo filtra excel. Filter uporabite za glavo mesta in količine in kliknite puščico na glavi mesta, ki se prikaže. Sledite spodnjim korakom.
Koraki:
- Izberite celico glave mesta. Uporabi filter z bližnjico Ctrl + Shift + L
- Kliknite puščico, ki se prikaže kot možnost filtra.
- Izberite možnost (Izberi vse).
- Izberite samo mesto Boston.
- Zdaj izberite naslov količine.
- Seznam razvrstite od največjega do najmanjšega in si lahko ogledate vseh prvih 5 vrednosti, ki smo jih izračunali po formuli.
Kot lahko vidite na zgornjem gifu, vseh 5 vrednosti, ki ustrezajo danim merilom. To tudi pomeni, da formula dobro šteje te vrednosti
VELIKE N številke
Zgornji postopek se uporablja za izračun vsote nekaj številk od vrha. Ampak za izračun n (veliko) število vrednosti na dolgem razponu.
Uporabite formulo:
= SUMPRODUCT (LARGE ((City = "Boston") * (količina), ROW (INDIRECT ("1:10"))
Tukaj ustvarimo vsoto prvih 10 vrednosti, tako da dobimo niz od 1 do 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} z uporabo funkcij ROW & INDIRECT Excel.
Tu imamo vsoto prvih 10 številk, ki ima za posledico 1147.
Vsota spodnjih vrednosti N s kriteriji
Kako rešiti težavo?
Za ta članek bomo morali uporabiti funkcijo SUMPRODUCT. Zdaj bomo iz teh funkcij naredili formulo. Tukaj imamo razpon in moramo določiti 5 vrednosti v razponu in dobiti vsoto vrednosti.
Splošna formula:
{= SUM (MALO (ČE (Mesto = "Boston", količina), {1, 2, 3, 4, 5}))}
Obseg: obseg vrednosti
Vrednosti: številke, ločene z vejicami, na primer, če želite najti spodnje 3 vrednosti, uporabite {1, 2, 3}.
Primer:
Vse to je lahko zmedeno za razumevanje. Torej, preizkusimo to formulo tako, da jo zaženemo na spodnjem primeru.
Tu imamo razpon vrednosti od A1: D50.
Tukaj je območje mesta in količine podano z uporabo imenovanega orodja excel.
Najprej moramo s funkcijo SMALL najti spodnjih pet vrednosti, ki se ujemajo s kriteriji, nato pa za teh 5 vrednosti opraviti operacijo seštevanja. Zdaj bomo uporabili naslednjo formulo, da dobimo vsoto
Uporabite formulo:
{= SUM (MALO (ČE (Mesto = "Boston", količina), {1, 2, 3, 4, 5}))}
NE uporabljajte kodrastih naramnic ročno. Kodraste naramnice, nanesene z uporabo Ctrl + Shift + Enter namesto samo Vnesite.
Pojasnilo:
- SMALL funkcija s funkcijo IF vrne spodnjih 5 numeričnih vrednosti, ki se ujemajo z City "Boston" in vrne polje v funkcijo SUM.
= SUM ({23, 27, 28, 28, 30}))
- Funkcija SUM dobi niz spodnjih 5 vrednosti, ki ima niz spodnjih 5 številk in vrne SUM tistih številk, ki se uporabljajo s CTRL + SHIFT + ENTER.
Tukaj je območje mesta in količine podano kot imenovano območje. Pritisnite Ctrl + Shift + Enter da dobite SUM spodnjih 5 številk, saj je to matrična formula.
Kot lahko vidite na zgornjem posnetku, je ta vsota 136.
Zgornji postopek se uporablja za izračun vsote nekaj številk od spodaj. Ampak za izračun n (veliko) število vrednosti na dolgem razponu.
Uporabite formulo:
{ = SUM (MALO (ČE (Mesto = "Boston", količina), ROW (INDIREKTNO ("1:10")))) }
NE uporabljajte kodrastih oklepajev ročno. Namesto tipke Enter pritisnite Ctrl + Shift + Enter.
Tukaj ustvarimo vsoto spodnjih 10 vrednosti, tako da dobimo niz od 1 do 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} z uporabo funkcij ROW & INDIRECT Excel.
Tukaj imamo vsoto spodnjih 10 številk, iz katerih bo nastalo 155.
Spodaj je nekaj opazovalnih opomb.
Opombe:
- Formula deluje samo s številkami.
- Formula deluje le, če v iskalni tabeli ni dvojnikov
- Funkcija SUMPRODUCT upošteva neštevilčne vrednosti (na primer besedilo abc) in vrednosti napak (na primer #NUM!, #NULL!) Kot ničelne vrednosti.
- Funkcija SUMPRODUCT upošteva logično vrednost TRUE kot 1 in False kot 0.
- Niz argumentov mora biti enake dolžine kot funkcija.
Upam, da je ta članek o tem, kako vrniti vsoto prvih 5 vrednosti ali 5 spodnjih vrednosti s kriteriji v Excelu, razložiti. Več člankov o funkcijah SUMPRODUCT najdete tukaj. Prosimo, delite svoje vprašanje spodaj v polju za komentar. Mi vam bomo pomagali.
Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
SUM, če je datum med : Vrne SUM vrednosti med danimi datumi ali obdobjem v Excelu.
Vsota, če je datum večji od navedenega datuma: Vrne SUM vrednosti po danem datumu ali obdobju v Excelu.
2 načina seštevanja po mesecih v Excelu: Vrne SUM vrednosti v določenem mesecu v Excelu.
Kako sešteti več stolpcev s pogojem: Vrne SUM vrednosti v več stolpcih s pogojem v excelu
Kako uporabljati nadomestne znake v Excelu : Preštejte celice, ki se ujemajo z frazami, z uporabo nadomestnih znakov v Excelu
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost
Uredite spustni seznam
Absolutna referenca v Excelu
Če s pogojnim oblikovanjem
Če z nadomestnimi znaki
Vpogled po datumu
Pretvorite palce v stopala in palce v Excelu 2016
Pridružite se imenu in priimku v Excelu
Preštejte celice, ki ustrezajo bodisi A ali B
