Eelmises artiklis õppisime, kuidas liita kokku ülemised või alumised N väärtused. Selles artiklis proovime kriteeriumidega kokku võtta ülemised või alumised N väärtused.
TOP N väärtuste summa koos kriteeriumidega
Kuidas probleemi lahendada?
Selle artikli jaoks peame kasutama funktsiooni SUMPRODUCT. Nüüd teeme neist funktsioonidest valemi. Siin antakse meile vahemik ja kriteeriumid. Peame hankima vahemiku 5 parimat väärtust ja saama antud kriteeriumide alusel väärtuste summa.
Üldine valem:
= SUMPRODUCT (SUUR ((loend = kriteeriumid) * (vahemik), {1, 2,…., N}})
nimekirja: kriteeriumide loend
Kriteeriumid: vastavad kriteeriumid
vahemik: väärtuste vahemik
väärtused: numbrid eraldatakse komadega, näiteks kui soovite leida 3 parimat väärtust, kasutage {1, 2, 3}.
Näide:
Siin on meil andmestiku väärtused A1: D50.
Esiteks peame leidma viis parimat väärtust, kasutades funktsiooni LARGE, mis sobib linnaga "Boston", ja seejärel tuleb nende viie väärtuse korral teha summaoperatsioon. Nüüd kasutame summa saamiseks järgmist valemit
Kasutage valemit:
= SUMPRODUCT (SUUR ((linn = "Boston") * (kogus), {1, 2, 3, 4, 5}))
Selgitus:
- City "Boston" vastab mainitud City vahemikule. See tagastab hulga tõeseid ja valesid.
- Funktsioon LARGE tagastab kogusevahemiku 5 parimat arvväärtust ja tagastab massiivi funktsioonile SUMPRODUCT.
= SUMPRODUCT {193, 149, 138, 134, 123}
- Funktsioon SUMPRODUCT saab massiivi 5 parimat väärtust, kusjuures 5 parima arvu massiiv tagastab nende numbrite summa.
Siin on nimega vahemik Linn ja koguste vahemik. Vajutage sisestusklahvi, et saada 5 parima numbri SUMM.
Nagu ülaltoodud pildil näha, on see summa 737. Väärtuste summa 193 + 149 + 138 + 134 + 123 = 737.
Ülaltoodud väärtusi saate kontrollida andmekogumis, kasutades valikut Excel filter. Rakendage filter päisele Linn ja kogus ja klõpsake kuvataval linna päisel nooleklahvi. Järgige alltoodud samme.
Sammud:
- Valige linna päise lahter. Rakendage filter otsetee abil Ctrl + Tõstuklahv + L
- Klõpsake filtrivalikuna kuvatavat noolt.
- Valige suvand (Vali kõik).
- Valige ainult Bostoni linn.
- Valige kohe koguse päis.
- Sorteerige loend suurimast väiksemaks ja saate vaadata kõiki 5 parimat väärtust, mille valemi abil arvutasime.
Nagu ülaltoodud gifist näete, on kõik 5 väärtust, mis vastavad antud kriteeriumidele. See tähendab ka seda, et valem töötab nende väärtuste loendamiseks hästi
SUUR N numbrit
Ülaltoodud protsessi kasutatakse ülaosast mõne numbri summa arvutamiseks. Aga selleks, et arvutada n (suur) arv väärtusi pikas vahemikus.
Kasutage valemit:
= SUMPRODUCT (SUUR ((Linn = "Boston") * (kogus), RIDA (KAUDNE ("1:10"))
Siin genereerime 10 parima väärtuse summa, saades massiivi 1 kuni 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} kasutades rida ja kaudseid Exceli funktsioone.
Siin on kümne parima numbri summa, mille tulemuseks on 1147.
Alumise N väärtuse summa koos kriteeriumidega
Kuidas probleemi lahendada?
Selle artikli jaoks peame kasutama funktsiooni SUMPRODUCT. Nüüd teeme neist funktsioonidest valemi. Siin antakse meile vahemik ja peame vahemikus viit väärtust alla laskma ning saama väärtuste summa.
Üldine valem:
{= SUM (VÄIKE (IF (linn = "Boston", kogus), {1, 2, 3, 4, 5}))})
Vahemik: väärtuste vahemik
Väärtused: komadega eraldatud numbrid, näiteks kui soovite leida kolm viimast väärtust, kasutage {1, 2, 3}.
Näide:
Kõiki neid võib segadusse ajada. Niisiis, katsetame seda valemit, kasutades seda allpool näidatud näitel.
Siin on meil väärtuste vahemik A1: D50.
Siin on linna- ja kogustevahemik antud nimega Excel Exceli tööriista kasutades.
Esiteks peame leidma viis alumist väärtust, kasutades funktsiooni SMALL, mis vastab kriteeriumidele, ja seejärel tegema nende 5 väärtuse summa. Nüüd kasutame summa saamiseks järgmist valemit
Kasutage valemit:
{= SUM (VÄIKE (IF (linn = "Boston", kogus), {1, 2, 3, 4, 5}))})
ÄRGE kasutage käsitsi lokkis trakse. Lokkis traksid rakendati Ctrl + Tõstuklahv + Enter õiglase asemel Sisenema.
Selgitus:
- Funktsioon SMALL koos funktsiooniga IF tagastab alumise 5 numbrilise väärtuse, mis vastab linnale "Boston" ja tagastab massiivi funktsioonile SUM.
= SUMMA ({23, 27, 28, 28, 30}))
- Funktsioon SUM saab alumise 5 väärtuse massiivi, mis sisaldab alumise 5 numbrite massiivi, tagastab nende numbrite summa, mida kasutatakse klahvikombinatsiooni CTRL + SHIFT + ENTER abil.
Siin on nimega vahemik Linn ja koguste vahemik. Vajutage Ctrl + Tõstuklahv + Enter et saada 5 alumise numbri SUMM, kuna see on massiivivalem.
Nagu näete ülaltoodud pildil, on summa 136.
Ülaltoodud protsessi kasutatakse mõne numbri summa arvutamiseks altpoolt. Aga selleks, et arvutada n (suur) arv väärtusi pikas vahemikus.
Kasutage valemit:
{ = SUM (VÄIKE (KUI (Linn = "Boston", kogus)), RIDA (KAUDNE ("1:10")))) }
ÄRGE kasutage lokkisulgi käsitsi. Kasutage sisestusklahvi asemel Ctrl + tõstuklahvi + sisestusklahvi.
Siin genereerime 10 madalaima väärtuse summa, saades massiivi 1 kuni 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} kasutades rida ja kaudseid Exceli funktsioone.
Siin on meil 10 alumise numbri summa, mille tulemuseks on 155.
Siin on mõned allpool toodud vaatlusmärkused.
Märkused:
- Valem töötab ainult numbritega.
- Valem töötab ainult siis, kui otsingulaual pole duplikaate
- Funktsioon SUMPRODUCT arvestab nullväärtustena mittearvulisi väärtusi (nt tekst abc) ja veaväärtusi (nt #NUM!, #NULL!).
- Funktsioon SUMPRODUCT loeb loogilise väärtuse TRUE väärtuseks 1 ja vale väärtuseks 0.
- Argumendimassiiv peab olema sama pikk kui funktsioon.
Loodetavasti see artikkel selle kohta, kuidas Exceli kriteeriumidega 5 parima või 5 madalama väärtuse summa tagastada, on selgitav. Siit leiate rohkem artikleid funktsioonide SUMPRODUCT kohta. Palun jagage oma päringut allpool kommentaaride kastis. Me aitame teid.
Kui teile meeldisid meie ajaveebid, jagage seda oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis. Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile
Funktsiooni SUMPRODUCT kasutamine Excelis: Tagastab SUM -i pärast väärtuste korrutamist mitmes Exceli massiivis.
SUM, kui kuupäev jääb vahele : Tagastab Exceli kuupäevade või perioodide vaheliste väärtuste SUMMA.
Summa, kui kuupäev on antud kuupäevast suurem: Tagastab väärtuste SUM pärast Excelis antud kuupäeva või perioodi.
2 võimalust Excelis iga kuu summeerimiseks: Tagastab Exceli konkreetse kuu väärtuste summa.
Kuidas liita mitu veergu olekuga: Tagastab väärtuste SUM väärtuse mitmes veerus, mille tingimus on Excelis
Kuidas kasutada Excelis metamärke : Loendage fraasidele vastavaid lahtreid, kasutades Excelis metamärke
Populaarsed artiklid
50 Exceli otsetee tootlikkuse suurendamiseks
Muutke ripploendit
Absoluutne viide Excelis
Kui tingimusvorminguga
Kui metamärkidega
Vlookup kuupäeva järgi
Teisendage tollid Excelis 2016 jalgadeks ja tollideks
Liituge Excelis ees- ja perekonnanimega
Loendage lahtrid, mis vastavad kas A või B.