Însumați cele mai mici sau inferioare valori N pe baza criteriilor din Excel
În tutorialul anterior, am discutat cum să însumăm cele mai mici n valori dintr-un interval de date. În acest articol, vom efectua o altă operație avansată - pentru a însuma cele mai mici valori n bazate pe unul sau mai multe criterii în Excel.
Însumați cele mai mici sau inferioare valori N pe baza criteriilor din Excel
Presupunând că am o gamă de date așa cum se arată în captura de ecran de mai jos, acum vreau să însumez cele mai mici 3 comenzi ale produsului Apple.
În Excel, pentru a suma n valorile de jos într-un interval cu criterii, puteți crea o formulă matrice utilizând funcțiile SUM, SMALL și IF. Sintaxa generică este:
Array formula, should press Ctrl + Shift + Enter keys together.
- range=criteria: Gama de celule pentru a se potrivi cu criteriile specifice;
- values: Lista care conține cele n valori de jos pe care doriți să le însumați;
- N: A N-a valoare inferioară.
Pentru a rezolva problema de mai sus, vă rugăm să aplicați formula matricei de mai jos într-o celulă goală:
Și apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține rezultatul corect, așa cum se arată mai jos:
Explicația formulei:
=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))
- IF(($A$2:$A$14=D2), $B$2:$B$14): Dacă produsul din gama A2:A14 este egal cu „Mere”, acesta va returna numărul relativ din lista de comandă (B2:B14); Dacă produsul nu este „Apple”, va fi afișat un FALSE. Veți obține rezultatul astfel: {800;FALSE;FALSE;FALSE;1000;230;FALSE;FALSE;1600;FALSE;900;FALSE;500}.
- SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): Această funcție MICĂ va ignora valorile FALSE și va returna ultimele 3 valori din matrice, deci, rezultatul este următorul: {230,500,800}.
- SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): În cele din urmă, funcția SUM însumează numerele din matrice pentru a obține rezultatul: 1530.
Sfaturi: rezolvați două sau mai multe condiții:
Dacă trebuie să însumați valorile de jos n pe baza a două sau mai multe criterii, trebuie doar să adăugați alte intervale și criterii prin caracterul * în cadrul funcției IF, astfel:
Array formula, should press Ctrl + Shift + Enter keys together.
- Range1=criteria1: primul interval de celule care se potrivește cu primele criterii;
- Range2=criteria2: Al doilea interval de celule pentru a se potrivi celui de-al doilea criteriu;
- Range3=criteria3: al treilea interval de celule care se potrivește cu al treilea criteriu;
- values: Lista care conține cele n valori de jos pe care doriți să le însumați;
- N: A N-a valoare inferioară.
De exemplu, vreau să însumez ultimele 3 comenzi de produs Apple vândut de Kerry, vă rugăm să aplicați formula de mai jos:
Și apoi apăsați Ctrl + Shift + Enter cheile împreună pentru a obține rezultatul după cum aveți nevoie:
Funcția relativă utilizată:
- SUMĂ:
- Funcția SUM adaugă valori. Puteți adăuga valori individuale, referințe de celule sau intervale sau o combinație a tuturor celor trei.
- MIC:
- Funcția Excel SMALL returnează o valoare numerică bazată pe poziția sa într-o listă atunci când este sortată după valoare în ordine crescătoare.
- IF:
- Funcția IF testează o anumită condiție și returnează valoarea corespunzătoare pe care o furnizați pentru TRUE sau FALSE.
Mai multe articole:
- Suma cele mai mici sau inferioare N valori
- În Excel, ne este ușor să însumăm o serie de celule utilizând funcția SUM. Uneori, poate fi necesar să însumați cele mai mici sau cele mai mici 3, 5 sau n numere dintr-un interval de date, așa cum se arată mai jos. În acest caz, SUMPRODUCT împreună cu funcția SMALL vă pot ajuta să rezolvați această problemă în Excel.
- Subtotalul sumelor facturii în funcție de vârstă în Excel
- Însumarea sumelor facturii în funcție de vârstă, așa cum imaginea de mai jos poate fi o sarcină comună în Excel, acest tutorial va arăta cum să subtotalezi sumele facturilor în funcție de vârstă cu o funcție normală SUMIF.
- Sumați toate celulele numerice ignorând erorile
- Când însumați o gamă de numere care conțin unele valori de eroare, funcția SUM normală nu va funcționa corect. Pentru a însuma numai numerele și a sări peste valorile erorii, funcția AGREGARE sau SUM împreună cu funcțiile IFERROR vă pot face o favoare.
Cele mai bune instrumente de productivitate Office
Kutools for Excel - Te ajută să ieși în evidență din mulțime
Kutools for Excel Are peste 300 de caracteristici, Asigurați-vă că ceea ce aveți nevoie este la doar un clic distanță...
Office Tab - Activați citirea și editarea cu file în Microsoft Office (include Excel)
- O secundă pentru a comuta între zeci de documente deschise!
- Reduceți sute de clicuri de mouse pentru dvs. în fiecare zi, spuneți adio de la mâna mouse-ului.
- Vă crește productivitatea cu 50% atunci când vizualizați și editați mai multe documente.
- Aduce file eficiente la Office (inclusiv Excel), la fel ca Chrome, Edge și Firefox.
