Edellisessä artikkelissa opimme laskemaan yhteen ylä- tai alin N -arvot. Tässä artikkelissa yritämme tiivistää ylä- tai alin N -arvot kriteereillä.
TOP N -arvojen summa ehtoineen
Kuinka ratkaista ongelma?
Tässä artikkelissa meidän on käytettävä SUMPRODUCT -toimintoa. Nyt teemme kaavan näistä funktioista. Tässä annetaan alue ja kriteerit. Meidän on saatava alueen viisi parasta arvoa ja saatava arvojen summa annettujen kriteerien perusteella.
Yleinen kaava:
= SUMPRODUCT (LARGE ((lista = kriteerit) * (alue), {1, 2,…., N}})
lista: kriteeriluettelo
Kriteeri: vastaavat kriteerit
valikoima: arvoalue
arvot: numerot erotettu pilkuilla, kuten jos haluat löytää 3 parasta arvoa, käytä {1, 2, 3}.
Esimerkki:
Tässä meillä on tietojoukon arvot A1: D50.
Ensinnäkin meidän on löydettävä viisi parasta arvoa käyttämällä LARGE -funktiota, joka vastaa kaupunkia "Boston", ja sitten suoritetaan summaoperaatio näille viidelle arvolle. Käytämme nyt seuraavaa kaavaa summan saamiseksi
Käytä kaavaa:
= SUMPRODUCT (SUURI ((Kaupunki = "Boston") * (määrä), {1, 2, 3, 4, 5}))
Selitys:
- Cityn "Boston" vastaa mainittua City -valikoimaa. Tämä palauttaa joukon tosi ja epätosi.
- LARGE -funktio palauttaa 5 suurinta numeerista arvoa määräalueelta ja palauttaa taulukon SUMPRODUCT -funktioon.
= SUMPRODUCT {193, 149, 138, 134, 123}
- SUMPRODUCT -funktio saa joukon 5 parasta arvoa, ja 5 parhaan numeron joukko palauttaa näiden numeroiden SUMMA.
Tässä kaupunki- ja määräalue annetaan nimetyllä alueella. Paina Enter, niin saat 5 parhaan numeron yhteenvedon.
Kuten yllä olevasta tilannekuvasta näkyy, summa on 737. Arvojen summa 193 + 149 + 138 + 134 + 123 = 737.
Voit tarkistaa yllä olevat arvot tietojoukosta käyttämällä Excel -suodatinvaihtoehtoa. Käytä suodatinta Kaupunki ja määrä -otsikkoon ja napsauta näkyviin tulevassa kaupungin otsikossa olevaa nuolipainiketta. Noudata alla olevia ohjeita.
Askeleet:
- Valitse kaupungin otsikkosolu. Käytä suodatinta pikavalinnan avulla Ctrl + Vaihto + L
- Napsauta suodatinvaihtoehtona näkyvää nuolta.
- Valitse (Valitse kaikki) -vaihtoehto.
- Valitse vain Boston.
- Valitse määräotsikko nyt.
- Lajittele luettelo suurimmasta pienimpään ja näet kaikki 5 parasta arvoa, jotka laskimme kaavan avulla.
Kuten yllä olevasta gifistä näet, kaikki viisi arvoa, jotka vastaavat annettuja ehtoja. Tämä tarkoittaa myös sitä, että kaava toimii hyvin näiden arvojen laskemiseksi
SUURI N numeroa
Yllä olevaa prosessia käytetään laskettaessa muutaman numeron summa ylhäältä. Mutta laskeakseen n (suuri) arvot pitkällä alueella.
Käytä kaavaa:
= SUMPRODUCT (SUURI ((Kaupunki = "Boston") * (määrä), RIVI (Epäsuora ("1:10"))
Tässä luomme 10 parhaan arvon summan saamalla taulukon 1 - 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} käyttämällä RIVI & Epäsuora Excel -toimintoja.
Tässä on kymmenen parhaan numeron summa, jonka tuloksena on 1147.
Alempien N -arvojen summa ehtoineen
Kuinka ratkaista ongelma?
Tässä artikkelissa meidän on käytettävä SUMPRODUCT -toimintoa. Nyt teemme kaavan näistä funktioista. Täällä meille annetaan alue ja meidän on laskettava alimman 5 arvoa alueelle ja saatava arvojen summa.
Yleinen kaava:
{= SUMMA (PIENI (JOS (kaupunki = "Boston", määrä), {1, 2, 3, 4, 5}))}
Alue: arvoalue
Arvot: pilkuilla erotetut numerot, kuten jos haluat löytää alimmat 3 arvoa, käytä {1, 2, 3}.
Esimerkki:
Kaikkien näiden ymmärtäminen voi olla hämmentävää. Joten testataan tätä kaavaa suorittamalla se alla olevassa esimerkissä.
Tässä meillä on arvoalue A1: D50.
Tässä Kaupunki ja määrä -alue on annettu käyttämällä nimettyä alueen Excel -työkalua.
Ensinnäkin meidän on löydettävä viisi alinta arvoa käyttämällä SMALL -funktiota, joka vastaa ehtoja, ja suoritettava sitten summaoperaatio näille 5 arvolle. Käytämme nyt seuraavaa kaavaa summan saamiseksi
Käytä kaavaa:
{= SUMMA (PIENI (JOS (kaupunki = "Boston", määrä), {1, 2, 3, 4, 5}))}
ÄLÄ käytä kiharoita manuaalisesti. Kiharat olkaimet asetettu käyttämällä Ctrl + Vaihto + Enter vain sijasta Tulla sisään.
Selitys:
- SMALL -funktio IF -funktiolla palauttaa alimmat 5 numeerista arvoa, jotka vastaavat kaupunkia "Boston", ja palauttaa taulukon SUM -funktioon.
= SUMMA ({23, 27, 28, 28, 30}))
- SUMMA -funktio hakee matalimpien 5 -arvojen matriisin, jossa on matalimpien viiden numeron matriisi, palauttaa CTRL + VAIHTO + ENTER -näppäinten kanssa käytettyjen numeroiden summan.
Tässä kaupunki- ja määräalue annetaan nimetyllä alueella. Lehdistö Ctrl + Vaihto + Enter saada alimman 5 numeron SUMMA, koska tämä on taulukkokaava.
Kuten yllä olevasta tilannekuvasta näkyy, summa on 136.
Yllä olevaa prosessia käytetään laskemaan muutaman luvun summa alhaalta. Mutta laskeakseen n (suuri) arvot pitkällä alueella.
Käytä kaavaa:
{ = SUMMA (PIENI (JOS (kaupunki = "Boston", määrä), RIVI (Epäsuora ("1:10")))) }
ÄLÄ käytä kiharoita manuaalisesti. Käytä Enter -näppäimen sijasta Ctrl + Vaihto + Enter.
Tässä muodostamme alimman 10 arvon summan saamalla taulukon 1-10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} käyttämällä RIVI & Epäsuora Excel -toimintoja.
Tässä meillä on 10 alimman numeron summa, josta saadaan 155.
Seuraavassa on muutamia huomautuksia alla.
Huomautuksia:
- Kaava toimii vain numeroiden kanssa.
- Kaava toimii vain, jos hakutaulukossa ei ole päällekkäisyyksiä
- SUMPRODUCT -toiminto pitää muita kuin numeerisia arvoja (kuten teksti abc) ja virhearvoja (kuten #NUM!, #NULL!) Nolla -arvoina.
- SUMPRODUCT -funktio pitää logiikka -arvoa TRUE 1: nä ja False 0: na.
- Argumenttiryhmän on oltava yhtä pitkä kuin funktio.
Toivottavasti tämä artikkeli siitä, miten palautetaan Excelin kriteerien 5 parhaan arvon tai alimman 5 arvon summa, on selittävä. Lisää artikkeleita SUMPRODUCT -toiminnoista löydät täältä. Jaa kyselysi alla olevaan kommenttikenttään. Autamme sinua.
Jos pidit blogistamme, jaa se ystävillesi Facebookissa. Voit myös seurata meitä Twitterissä ja Facebookissa. Haluaisimme kuulla sinusta, kerro meille, kuinka voimme parantaa, täydentää tai innovoida työtämme ja parantaa sitä sinulle. Kirjoita meille sähköpostitse
SUMPRODUCT -toiminnon käyttäminen Excelissä: Palauttaa summan Excelin useiden matriisien arvojen kertomisen jälkeen.
SUM, jos päivämäärä on välillä : Palauttaa Excelissä annettujen päivämäärien tai ajanjaksojen välisten arvojen SUMMA.
Summa, jos päivämäärä on suurempi kuin annettu päivämäärä: Palauttaa arvojen SUMMAN annetun päivämäärän tai jakson jälkeen Excelissä.
2 tapaa laskea yhteen kuukausittain Excelissä: Palauttaa arvojen SUMMA tietyn kuukauden aikana Excelissä.
Kuinka laskea useita sarakkeita ehdolla: Palauttaa useiden sarakkeiden arvojen summan, joiden ehto on Excel
Kuinka käyttää jokerimerkkejä Excelissä : Laske ilmauksia vastaavat solut käyttämällä Excelin yleismerkkejä
Suosittuja artikkeleita
50 Excel -pikakuvake tuottavuuden lisäämiseksi
Muokkaa avattavaa luetteloa
Absoluuttinen viittaus Excelissä
Jos ehdollinen muotoilu
Jos käytetään jokerimerkkejä
Vlookup päivämäärän mukaan
Muunna tuumat jalkoiksi ja tuumiksi Excel 2016: ssa
Liity etu- ja sukunimeen Excelissä
Laske solut, jotka vastaavat joko A: ta tai B: tä