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. Să explorăm aceste moduri.
Spre deosebire de alte articole, să vedem mai întâi scenariul.
Aici am un tabel de vânzări realizat de diferiți vânzători în diferite luni.
Acum sarcina este de a obține suma vânzărilor lunii date în Cell C10. Dacă schimbăm luna în B10, suma ar trebui să se schimbe și să returneze suma lunii respective, fără a modifica nimic în formulă.
Metoda 1: Suma întregii coloane din tabel utilizând funcția SUMPRODUCT.
Sintaxa metodei SUMPRODUCT pentru coloana de potrivire a sumelor este:
| = SUMPRODUCT ((coloane) * (anteturi = antet)) |
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.
Anteturi:Este gama de antet a coloane pe care vrei să o însumezi. În datele de mai sus, este C2: N2.
Titlu: Este titlul pe care doriți să îl potriviți. În exemplul de mai sus, se află în B10.
Fără întârziere să folosim formula.
| = SUMPRODUCT ((C3: N7) * (C2: N2 = B10)) |
și acest lucru va reveni:
Cum functioneazã?
Este simplu. În formulă, enunțulC2: N2 = B10 returnează o matrice care conține toate valorile FALSE, cu excepția uneia care se potrivește cu B10. Acum formula este
| =SUMPRODUCT ((C3: N7) * {FALS, FALSE, FALSE, FALSE,ADEVĂRAT, FALS, FALS, FALS, FALS, FALSE, FALSE, FALSE}) |
Acum C3: N7 este înmulțit la fiecare valoare a acestei matrice. Fiecare coloană devine zero, cu excepția coloanei care este înmulțită cu ADEVĂRAT. Acum formula devine:
| = SUMPRODUCT ({0,0,0,0,6,0,0,0,0,0,0,0,0; 0,0,0,0,12,0,0,0,0,0,0,0, 0; 0,0,0,0,15,0,0,0,0,0,0,0,0; 0,0,0,0,15,0,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0,0})) |
Acum această matrice este rezumată și obținem suma coloanei care se potrivește cu coloana din celula B10.
Metoda 2: Suma întregii coloane din tabel utilizând funcția INDEX-MATCH.
Sintaxa metodei de însumare a antetului de coloană potrivită în excel este:
| = SUM (INDEX (coloane ,, MATCH (antet, anteturi, 0))) |
Toate variabilele din această metodă sunt identice cu metoda SUMPRODUCT. Să o implementăm doar pentru a rezolva problema. Scrieți această formulă în C10.
| = SUMĂ (INDEX (C3: N7 ,, MATCH (B10, C2: N2,0))) |
Aceasta returnează:
Cum functioneazã?
Formula este rezolvată din afară. În primul rând, funcția MATCH returnează indexul lunii potrivite din intervalul C2: N2. Din moment ce avem mai în B1o, obținem 5. Acum formula devine
| = SUM (INDEX (C3: N7,, 5)) |
Apoi, funcția INDEX returnează valori din a cincea coloană a C3: N7. Acum formula devine:
| = SUMĂ ({6; 12; 15; 15; 8}) |
Și, în cele din urmă, obținem suma acestor valori.
Metoda 3: Suma întreagă coloană din tabel folosind gama numită și funcția INDIRECT
Totul devine simplu dacă vă denumiți intervalele ca titluri de coloană. În această metodă, trebuie mai întâi să numim coloanele ca nume de titlu.
Selectați tabelul, inclusiv titlurile și apăsați CTRL + SHIFT + F3. Se va deschide un dialog pentru a crea un nume din intervale. Verificați rândul de sus și apăsați butonul OK.
Va denumi toate coloanele de date ca titluri.
Acum formula generică pentru a însuma coloana potrivită va fi:
| = SUM (INDIRECT (antet)) |
Antet Este numele coloanei pe care doriți să o însumați. În acest exemplu, B10 conține mai de acum.
Pentru a implementa această formulă generică, scrieți această formulă în celula C10.
| = SUM (INDIRECT (B10)) |
Aceasta returnează suma lunii mai:
O altă metodă este similară cu aceasta. În această metodă, folosim tabele Excel și denumirea structurată. Să spunem dacă ați numit tabelul de mai sus ca tabel1. Apoi, această formulă va funcționa la fel ca formula de mai sus.
| = SUM (INDIRECT ("Tabelul 1 [" & B10 & "]")) |
Cum functioneazã?
În această formulă, funcția INDIRECT ia referința numelui și o convertește în referința reală a numelui. Procedura în continuare este simplă. Funcția SUM rezumă intervalul numit.
Deci, băieți, așa puteți rezuma coloana potrivită în Excel. Sper că vă va fi de ajutor și explicativ. Dacă aveți îndoieli cu privire la acest articol sau la orice alt subiect legat de Excel / VBA, întrebați în secțiunea de comentarii de mai jos.
Cum se face suma prin potrivirea rândului și a coloanei în ExcelSUMPRODUCT 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 …
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.