În articolul anterior am învățat cum să rezumăm valorile N de sus sau de jos. În acest articol încercăm să rezumăm valorile N de sus sau de jos cu un criteriu.
Suma valorilor TOP N cu criterii
Cum se rezolvă problema?
Pentru acest articol ni se va cere să folosim funcția SUMPRODUCT. Acum vom face o formulă din aceste funcții. Aici ni se oferă o gamă și un criteriu. Trebuie să obținem primele 5 valori din interval și să obținem suma valorilor pe baza criteriilor date.
Formula generică:
= SUMPRODUCT (LARGE ((list = criterii) * (interval), {1, 2,…., N}})
listă: lista criteriilor
Criterii: criterii pentru a se potrivi
gamă: gama de valori
valori: numere separate prin virgulă, cum ar fi dacă doriți să găsiți primele 3 valori, utilizați {1, 2, 3}.
Exemplu:
Aici avem valorile setului de date din A1: D50.
În primul rând, trebuie să găsim primele cinci valori folosind funcția LARGE care se potrivește cu orașul „Boston” și apoi operația de sumă să fie efectuată peste acele 5 valori. Acum vom folosi următoarea formulă pentru a obține suma
Utilizați formula:
= SUMPRODUCT (LARGE ((City = "Boston") * (cantitate), {1, 2, 3, 4, 5}))
Explicaţie:
- „Boston” City se potrivește cu gama City menționată. Aceasta returnează o serie de adevărate și false.
- Funcția LARGE returnează primele 5 valori numerice din intervalul de cantități și returnează matricea la funcția SUMPRODUCT.
= SUMPRODUCT {193, 149, 138, 134, 123}
- Funcția SUMPRODUCT obține o matrice de top 5 valori, cu o matrice de top 5 numere care returnează SUMA acelor numere.
Aici intervalul Oraș și cantitate este dat ca interval numit. Apăsați Enter pentru a obține SUMA din primele 5 numere.
După cum puteți vedea în instantaneul de mai sus, suma respectivă este 737. Suma valorilor 193 + 149 + 138 + 134 + 123 = 737.
Puteți verifica valorile de mai sus în setul de date folosind opțiunea de filtrare excel. Aplicați filtrul la antetul Oraș și cantitate și faceți clic pe butonul săgeată de pe antetul orașului care apare. Urmați pașii așa cum se arată mai jos.
Pași:
- Selectați celula antet Oraș. Aplicați filtrul utilizând comanda rapidă Ctrl + Shift + L
- Faceți clic pe săgeata care apare ca opțiune de filtrare.
- Selectați opțiunea (Select All).
- Selectați doar orașul Boston.
- Selectați antetul cantității acum.
- Sortați lista de la cea mai mare la cea mai mică și puteți vedea toate cele 5 valori de top pe care le-am calculat folosind formula.
După cum puteți vedea în giful de mai sus, toate cele 5 valori care corespund criteriilor date. Aceasta înseamnă, de asemenea, că formula funcționează bine pentru a obține numărul acestor valori
N numere MARI
Procesul de mai sus este utilizat pentru a calcula suma câtorva numere din partea de sus. Dar de calculat pentru n (mare) număr de valori într-un interval lung.
Folosiți formula:
= SUMPRODUCT (LARGE ((City = "Boston") *) (cantitate), ROW (INDIRECT ("1:10"))
Aici generăm suma primelor 10 valori prin obținerea unei matrice de la 1 la 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} folosind funcțiile Excel ROW & INDIRECT.
Aici avem suma primelor 10 numere care rezultă în 1147.
Suma valorilor N inferioare cu criterii
Cum se rezolvă problema?
Pentru acest articol ni se va cere să folosim funcția SUMPRODUCT. Acum vom face o formulă din aceste funcții. Aici ni se oferă un interval și trebuie să coborâm 5 valori în interval și să obținem suma valorilor.
Formula generică:
{= SUM (SMALL (IF (City = "Boston", cantitate), {1, 2, 3, 4, 5}))}}
Gama: gama de valori
Valori: numere separate prin virgulă, cum ar fi dacă doriți să găsiți cele 3 valori inferioare, utilizați {1, 2, 3}.
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Deci, să testăm această formulă executând-o pe exemplul prezentat mai jos.
Aici avem o gamă de valori de la A1: D50.
Aici intervalul Oraș și cantitate este dat ca folosind instrumentul excel numit interval.
În primul rând, trebuie să găsim cele cinci valori de jos folosind funcția SMALL care corespunde criteriilor și apoi operația de sumă să fie efectuată peste aceste 5 valori. Acum vom folosi următoarea formulă pentru a obține suma
Utilizați formula:
{= SUM (SMALL (IF (City = "Boston", cantitate), {1, 2, 3, 4, 5}))}
NU folosiți manual aparate dentare. Aparate dentare ondulate aplicate folosind Ctrl + Shift + Enter în locul doar introduce.
Explicaţie:
- Funcția MICĂ cu funcția IF returnează cele 5 valori numerice inferioare care se potrivește cu orașul "Boston" și returnează matricea la funcția SUM.
= SUMĂ ({23, 27, 28, 28, 30}))
- Funcția SUM obține matricea de 5 valori inferioare, care are o matrice de 5 numere inferioare returnează SUMA acelor numere utilizate cu CTRL + SHIFT + ENTER.
Aici intervalul Oraș și cantitate este dat ca interval numit. presa Ctrl + Shift + Enter pentru a obține SUMA celor 5 numere inferioare, deoarece aceasta este o formulă matrice.
După cum puteți vedea în instantaneul de mai sus, suma este 136.
Procesul de mai sus este folosit pentru a calcula suma câtorva numere de jos. Dar de calculat pentru n (mare) număr de valori într-un interval lung.
Folosiți formula:
{ = SUM (SMALL (IF (City = "Boston", cantitate), ROW (INDIRECT ("1:10")))) }
NU folosiți parantezele cretate manual. Utilizați Ctrl + Shift + Enter în locul utilizării Enter.
Aici generăm suma celor 10 valori inferioare prin obținerea unei matrice de la 1 la 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} folosind funcțiile Excel ROW & INDIRECT.
Aici avem suma celor 10 numere de jos, care vor avea ca rezultat 155.
Iată câteva note observaționale prezentate mai jos.
Note:
- Formula funcționează numai cu numere.
- Formula funcționează numai atunci când nu există duplicate pe tabelul de căutare
- Funcția SUMPRODUCT consideră valori numerice (cum ar fi textul abc) și valori de eroare (cum ar fi #NUM!, #NULL!) Ca valori nule.
- Funcția SUMPRODUCT consideră valoarea logică TRUE ca 1 și False ca 0.
- Argumentul trebuie să aibă aceeași lungime cu funcția.
Sperăm că acest articol despre cum să restitui suma dintre primele 5 valori sau cele 5 valori inferioare cu criterii în Excel este explicativ. Găsiți mai multe articole despre funcțiile SUMPRODUCT aici. Vă rugăm să împărtășiți interogarea dvs. mai jos în caseta de comentarii. Vă vom ajuta.
Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail
Cum se utilizează funcția SUMPRODUCT în Excel: Returnează SUMA după multiplicarea valorilor în mai multe tablouri în excel.
SUMA dacă data este între : Returnează SUMA valorilor între date sau perioade date în Excel.
Suma dacă data este mai mare decât data dată: Returnează SUMA valorilor după data sau perioada dată în Excel.
2 moduri de a suma prin lună în Excel: Returnează SUMA valorilor într-o anumită lună specifică în Excel.
Cum să însumați mai multe coloane cu condiție: Returnează SUMA valorilor pe mai multe coloane cu condiție în excel
Cum se utilizează metacaracterele în Excel : Numărați celule care se potrivesc cu fraze folosind metacaracterele în excel
Articole populare
50 Comandă rapidă Excel pentru a vă crește productivitatea
Editați o listă derulantă
Referință absolută în Excel
Dacă cu formatare condiționată
Dacă cu wildcards
Vizualizați după dată
Convertiți inci în picioare și inci în Excel 2016
Alăturați-vă prenumelui și prenumelui în Excel
Numărați celulele care se potrivesc fie cu A, fie cu B