În acest articol, vom afla cum să creați coloane calculate în Excel.
Scenariu
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 scenarii diferite.
Cum se creează o coloană calculată într-un tabel:
- Selectați o celulă într-una dintre coloanele unui tabel sau o celulă necompletată până la ultima coloană, în exemplul de sub celula selectată este G2.
- Introduceți formula care calculează vânzările pe unitate, tastați simbolul egal (=), selectați celula F2, tastați simbolul divizare (/), apoi selectați celula E2 și apăsați Enter.
Formula este copiată automat în toate celulele din coloană.
Exemplu:
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.
Antet 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țul C2: 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) * {FALSE, 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,,MECI(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 ,,MECI(B10, C2: N2,0))) |
Aceasta returnează:
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(titlu)) |
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 & "]")) |
Sper că acest articol despre Cum să creați coloane calculate în Excel este explicativ. Găsiți mai multe articole despre calcularea valorilor și formulele Excel conexe aici. 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 să sumați prin potrivirea rândului și a coloanei în Excel : 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 …
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:
Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.
Cum se utilizează 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.
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.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.