Până acum am învățat cum să însumăm întreaga coloană potrivită a unui tabel în Excel. Dar cum sumăm valori atunci când trebuie să potrivim coloana și rândul. În acest articol, vom învăța cum să facem suma rândurilor și coloanelor potrivite.
Există două formule pentru a face acest lucru, dar mai întâi, permiteți-ne scenariul.
Aici, am un tabel care înregistrează vânzările realizate de angajați în diferite luni. Numele angajaților se poate repeta. Vezi figura de mai jos:
Trebuie să obținem suma lunii mai în care vânzătorul este Donald.
Metoda 1: Rezumând antetul de coloană și antetul de rând potrivite Utilizând funcția SUMPRODUCT.
The Funcția SUMPRODUCT este cea mai versatilă funcție atunci când vine vorba de suma și numărarea valorilor cu criterii dificile. Funcția generică de sumat prin potrivirea coloanei și rândului este:
| = SUMPRODUCT ((coloane) * (column_headers = column_heading) * (row_headers = row_heading) |
Coloane:Este domeniul bidimensional al coloanelor pe care doriți să le însumați. Nu ar trebui să conțină anteturi. În tabelul de mai sus este C3: N7.
head_ columnEste gama de antet acoloane pe care vrei să o însumezi. În datele de mai sus, este C2: N2.
head_ column: Este titlul pe care doriți să îl potriviți. În exemplul de mai sus, se află în B13.
Fără întârziere să folosim formula.
head_headers:Este gama de antet arânduri pe care vrei să o însumezi. În datele de mai sus, este B3: B10.
head_heading: Este antetul pe care doriți să îl potriviți pe rânduri. În exemplul de mai sus, este în F13.
Fără întârziere să folosim formula.
Scrieți această formulă în celula D13 și lăsați excel-ul să facă magie (nu există magie) …
| = SUMPRODUCT ((C3: N10) * (C2: N2 = B13) * (B3: B10 = E13)) |
Aceasta returnează valoarea:
Acum, când schimbați luna sau vânzătorul, suma se va modifica conform titlului rândului și titlului coloanei.
Cum functioneazã?
Această logică booleană simplă.
(C2: N2 = B13): Această declarație va returna o serie de TRUE și FALSE. Toate valorile potrivite din coloană sunt adevărate, iar altele vor fi false. În acest caz, vom avea un singur adevărat, întrucât intervalul C2: N2 conține o singură instanță mai la 5a Locație.
(B3: B10 = E13): Acest lucru va funcționa la fel ca mai sus și va returna o matrice ADEVĂRAT și FALS. Toate valorile potrivite vor avea ADEVĂRAT, iar altele vor avea FALS. În acest caz, vom avea 2 ADEVĂRATE deoarece gama B3: B10 are două instanțe de „Donald”.
(C2: N2 = B13) * (B3: B10 = E13): Acum înmulțim matricile returnate prin instrucțiuni. Aceasta va fi implementată și logică și vom obține o serie de 1 și 0. Acum vom avea o matrice 2D care va conține 2 1s și restul 0s.
(C3: N10) *(C2: N2 = B13) * (B3: B10 = E13)= În cele din urmă, înmulțim matricea 2D cu tabelul 2D. Se va returna din nou o matrice de 0 și numerele care corespund criteriilor.
În cele din urmă, SUMPRODUCT funcția va însuma matricea care va duce la ieșirea dorită.
Metoda 2: Rezumând antetul de coloană și antetul de rând potrivite Utilizând funcțiile SUM și IF
Formula generică pentru însumarea rândului și coloanei potrivite utilizând funcția SUM și IF Excel este:
| = SUM (IF (column_headers = column_heading, IF (row_headers = row_heading, columns))) |
Toate variabilele sunt la fel ca în metoda explicată mai sus. Aici, trebuie doar să fie utilizate într-o ordine diferită.
Scrieți această formulă în celula D13:
| = SUM (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
Aceasta returnează răspunsul corect. Vedeți captura de ecran de mai jos:
Cum functioneazã?
Logica este aceeași cu prima metodă SUMPRODCUT, doar mecanismul este diferit. Dacă o explic pe scurt, funcția IF interioară returnează o matrice 2D cu aceeași dimensiune ca tabelul. Această matrice conține numărul de două rânduri potrivite. Apoi, funcția IF interioară se potrivește cu titlurile cu două coloane din această matrice și returnează matricea 2D care conține doar numerele care se potrivesc atât cu coloana, cât și cu titlul. Toate celelalte elemente ale matricei vor fi FALSE.
În cele din urmă, funcția SUM rezumă această matrice și obținem suma noastră.
Deci, băieți, așa puteți rezuma rândurile și coloanele potrivite dintr-un tabel în Excel. Sper că ți-a fost explicativ și util. Dacă aveți îndoieli cu privire la acest subiect sau aveți orice altă îndoială legată de Excel / VBA, întrebați în secțiunea de comentarii de mai jos.
Cum se sumează coloana într-un Excel prin potrivirea antetului | Dacă doriți să obțineți suma unei coloane utilizând doar numele coloanei, puteți face acest lucru în 3 moduri ușoare în Excel. Sintaxa metodei SUMPRODUCT pentru coloana de potrivire a sumelor este:
SUMIF cu referință 3D în Excel |Faptul distractiv este că referințarea Excel 3D normală nu funcționează cu funcții condiționale, cum ar fi funcția SUMIF. În acest articol, vom învăța cum să obținem referințe 3D funcționând cu funcția SUMIF.
Referință relativă și absolută în Excel | Referința în Excel este un subiect important pentru fiecare începător. Chiar și utilizatorii Excel cu experiență fac greșeli în referință.
Referință dinamică a foii de lucru | Oferiți foi de referință dinamic folosind funcția INDIRECTĂ a excel. Acest lucru este simplu …
Extinderea referințelor în Excel | Referința în expansiune se extinde atunci când este copiată în jos sau în dreapta. Pentru a face acest lucru, folosim semnul $ înainte de numărul coloanei și al rândului. Iată un exemplu …
Totul despre referințe absolute | Tipul de referință implicit în Excel este relativ, dar dacă doriți ca referința celulelor și a intervalelor să fie absolută utilizați semnul $. Iată toate aspectele referințării absolute în Excel.
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face munca și mai rapidă pe Excel.
Funcția VLOOKUP în Excel | Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
COUNTIF în Excel 2016 | Numărați valorile cu condiții folosind această funcție uimitoare. Nu este nevoie să vă filtrați datele pentru a număra o anumită valoare. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.