Dacă lucrați în Excel: construiți grafice, creați tabele, diverse rapoarte etc., atunci pentru a adăuga anumite date nu trebuie să căutați un calculator. Suma numerelor din Excel este calculată în câteva clicuri de mouse, iar acest lucru se poate face în diferite moduri.

Folosind selecția

Cel mai simplu vă va permite să adăugați toate numerele care vor fi în zona selectată. Selectați intervalul necesar și priviți bara de stare. Aceasta va afișa „Media” tuturor valorilor, „Numărul” celulelor selectate și „Suma” valorilor. Puteți selecta blocuri goale și cele cu text, deoarece sunt adăugate doar numere.

Dacă tabelul dvs. are o linie „Totale”, această metodă nu va funcționa. Desigur, puteți introduce un număr acolo, dar dacă datele pentru tabel se modifică, atunci trebuie să vă amintiți să îl schimbați în blocul „Rezultate”. Acest lucru nu este în întregime convenabil, iar în Excel puteți utiliza alte metode care vor recalcula automat rezultatul în bloc.

Folosind autosum

Puteți însuma numerele dintr-o coloană folosind butonul AutoSum. Pentru a face acest lucru, selectați o celulă goală în coloana imediat sub valori. Apoi accesați fila Formule și faceți clic pe AutoSum. Excel va selecta automat blocurile de sus, până la primul gol. Apăsați „Enter” pentru a calcula totul.

De asemenea, puteți selecta mai întâi blocurile din coloană, ținând cont atât de cele goale, cât și de cele cu text - pur și simplu nu vor fi luate în considerare în calcul, apoi faceți clic pe „Suma automată”. Rezultatul apare în prima celulă goală a coloanei selectate.

Folosind formula

Putem face calculul de care avem nevoie în Excel folosind o formulă matematică familiară. Plasați „=” în celula necesară, apoi utilizați mouse-ul pentru a le selecta pe toate de care aveți nevoie. Nu uitați să puneți un semn plus între ele. Apoi apăsați „Enter”.

Cel mai convenabil mod de a calcula este utilizarea funcției SUM. Puneți „=" în celulă, apoi introduceți „SUM”, deschideți paranteza „(” și evidențiați intervalul dorit. Puneți „)” și apăsați „Enter”.

De asemenea, puteți scrie o funcție direct în bara de formule.

Dacă specificați o gamă de blocuri ca argument pentru funcție, care, pe lângă numere, conține atât text, cât și blocuri goale, funcția va returna rezultatul corect. Acest lucru se datorează faptului că numai celulele cu numere vor fi luate în considerare pentru calcul.

În această lecție nu ne vom uita la cum să calculăm o sumă în Excel folosind operatorul de adăugare, autosum și alte instrumente. Astăzi ne vom uita la doar două funcții: SUMĂȘi SUMIF. Mă grăbesc să vă mulțumesc, funcționalitatea lor este suficientă pentru a rezolva aproape toate problemele posibile de sumare în Excel.

Funcția SUM - însumarea simplă a celulelor în Excel

Funcţie SUMĂ calculează suma tuturor argumentelor sale. Este cea mai folosită funcție în Excel. De exemplu, trebuie să adăugăm valorile în trei celule. Putem folosi, desigur, operatorul obișnuit de sumă:

Dar putem folosi și funcția SUMĂși scrieți formula după cum urmează:

Din moment ce funcţia SUMĂ acceptă lucrul nu numai cu celule individuale, ci și cu intervale întregi, atunci formula de mai sus poate fi modificată:

Adevărata putere a caracteristicii SUMĂ este dezvăluit atunci când trebuie să adăugați un număr mare de celule în Excel. Exemplul de mai jos necesită însumarea a 12 valori. Funcţie SUMĂ vă permite să faceți acest lucru cu câteva clicuri de mouse, dar dacă utilizați operatorul de adăugare, va dura mult timp.

În exemplul următor, funcția SUMĂ adună întreaga coloană A, care reprezintă 1048576 de valori:

Următoarea formulă calculează suma tuturor celulelor conținute într-o foaie de lucru Foaia 1. Pentru a preveni ca această formulă să provoace o eroare ciclică, trebuie utilizată pe o altă foaie de lucru Excel (alta decât Sheet1).

Funcţie SUMĂ poate lua până la 255 de argumente și poate suma mai multe intervale sau celule neadiacente simultan:

Dacă valorile însumate conțin text, atunci funcția SUMĂ le ignoră, adică nu include in calcul:

Dacă încercați să adăugați valori de text utilizând operatorul de însumare, formula va returna o eroare:

Funcţie SUMĂ este destul de universal și vă permite să utilizați ca argumente nu numai referințe la celule și intervale, ci și diverși operatori matematici și chiar alte funcții Excel:

SUMIF – sumă condiționată în Excel

De exemplu, următoarea formulă însumează numai numere pozitive din intervalul A1:A10. Rețineți că condiția este cuprinsă între ghilimele duble.

Puteți utiliza valoarea celulei ca o condiție. În acest caz, schimbarea condiției va schimba rezultatul:

Schimbăm condiția, iar rezultatul se schimbă:

Condițiile pot fi combinate folosind operatorul de concatenare. În exemplul de mai jos, formula va returna suma valorilor care sunt mai mari decât valoarea din celula B1.

În toate exemplele date mai devreme, am însumat și verificat condiția în același interval. Dar ce se întâmplă dacă trebuie să însumați un interval și să verificați starea diferit?

În acest caz, funcția SUMIF are un al treilea argument opțional, care este responsabil pentru intervalul care trebuie însumat. Acestea. funcția verifică condiția folosind primul argument, iar al treilea este supus însumării.

În exemplul următor, vom aduna costul total al tuturor fructelor vândute. Pentru a face acest lucru, folosim următoarea formulă:

Făcând clic introduce obtinem rezultatul:

Dacă o condiție nu este suficientă pentru dvs., atunci puteți utiliza întotdeauna funcția SUMIFS, care vă permite să efectuați însumarea condiționată în Excel pe baza mai multor criterii.

Însumarea este una dintre acțiunile principale pe care un utilizator le efectuează în Microsoft Excel. Funcții SUMĂȘi SUMIF creat pentru a ușura această sarcină și pentru a oferi utilizatorilor cel mai convenabil instrument posibil. Sper că această lecție te-a ajutat să stăpânești funcțiile de sumă de bază din Excel, iar acum poți aplica liber aceste cunoștințe în practică. Mult succes și succes în învățarea Excel!

Mulți oameni care lucrează cu foi de calcul EXCEL la un moment dat se confruntă cu întrebarea „cum se adaugă numere în celule în funcție de o anumită condiție în EXCEL”. Într-o zi însorită, am dat și eu peste această întrebare, dar nu am putut găsi răspunsul la această întrebare pe Internet, dar am fost persistent și am decis să studiez ajutorul programului, care mi-a dat ideea, și apoi, folosind încercare și eroare, am compilat o formulă și, după cum s-a dovedit, formula este elementară.

Totul a început cu faptul că am decis să țin cont de cheltuielile mele lunare și pentru asta am creat un tabel, pe care l-am atașat acestui articol, pentru că ți-ar putea fi de folos și ție.

Acum voi încerca să descriu în detaliu principiul creării formulei. Am statistici detaliate și un raport rezumat în raport. În statisticile detaliate îmi introduc cheltuielile zilnice, iar în raportul de sinteză se calculează suma cheltuielilor pentru anumite categorii și suma totală a cheltuielilor.

De exemplu, să luăm categoria de cheltuieli „Cumpărături în magazine”. Avem nevoie de EXCEL pentru a găsi toate cheltuielile pentru o anumită categorie în statistici detaliate, să însumăm cheltuielile pentru această categorie și să scrieți suma rezultată într-o celulă D10.

Mai întâi, notați formula pregătită, pe care o lipim în celulă D10, iar apoi vom începe să ne uităm în detalii. Formula finală arată astfel (doar pentru articolul nostru):

=SUMIF ($G$5:$G$300 ;(" Cumpărături în magazine");$H$5:$H$300)

Diverse condiții sunt evidențiate în culoare pentru a face mai clar. Să o luăm în ordine. În timpul procesului de descriere, priviți imaginea de mai sus pentru a fi mai clară. Când faceți o fotografie, literele coloanelor și numerele rândurilor au fost surprinse special. Asadar, haideti sa începem.

• SUMIF– cu această condiție spunem că suma valorilor anumitor celule trebuie să fie scrisă într-o celulă dacă îndeplinesc anumite condiții;
• $G$5:$G$300– aici indicăm către EXCEL în ce coloană trebuie să căutăm condiția de selecție. În cazul nostru, căutarea are loc în coloană G pornind de la linie 5 și terminând cu linia 300 ;
• (« Cumpărături în magazine») – aici indicăm condiția cerută și în funcție de această condiție se vor însuma valorile celulelor pe care le indicăm în continuare...;
• $H$5:$H$300– aici indicăm coloana din care vor fi luate numerele pentru însumare. În cazul nostru, valorile sunt luate în coloană H pornind de la linie 5 și terminând cu linia 300.

Pentru a rezuma, putem spune că EXCEL însumează doar acele valori din interval H5:H300, pentru care valorile corespunzătoare din interval G5:G300 sunt egale cu „Achiziții în magazine” și scrie rezultatul în celulă D10.

În consecință, puteți în EXCEL adăugați numere în celule în funcție de orice condiție.

Semn $ în formulă este utilizat astfel încât la copierea formulei din celula D10 în alte celule să nu existe decalaj. Să ne uităm la un exemplu de formulă nesemnată $ . De exemplu, într-o celulă D10 Avem formula:

=SUMIF(G5:G300,(„Achiziții în magazine”),H5:H300)

În continuare vrem să afișăm suma prânzurilor din celulă D11. Pentru a evita să rescriem formula, putem copia celula D10și lipiți în celulă D11. Datorită acestui fapt, formula va fi inserată în D11, dar aici putem observa că formula a schimbat valorile prin înlocuirea 5 cu 6 și 300 cu 301:

=SUMIF(G6:G301,(„Achiziții în magazine”),H6:H301)

A avut loc o schimbare. Dacă copiem formula în D12, atunci vom vedea o schimbare de 2 și așa mai departe. Pentru a evita acest lucru, scriem formula cu un semn $ . Astfel de caracteristici ale EXCEL.

Tabelul „Contabilitatea cheltuielilor lunare” -

Anterior am descris cum să utilizați o funcție personalizată pentru a găsi . Din păcate, această caracteristică nu funcționează dacă celulele sunt colorate folosind formatarea condiționată. Am promis că voi „finaliza” funcția. Dar în cei doi ani care au trecut de la publicarea acelei note, nu am reușit să scriu cod digerabil nici pe cont propriu, nici cu ajutorul informațiilor de pe internet... ( Actualizare din 29 martie 2017 După încă cinci ani, am reușit totuși să scriu codul; vezi partea finală a notei). Și recent am dat peste o idee cuprinsă în cartea „Excel 2007. Tricks” de D. Hawley, R. Hawley, care îți permite să faci deloc fără cod.

Să existe o listă de numere de la 1 la 100, plasate în intervalul A1:A100 (Fig. 1; vezi și foaia „SUMIF” din fișierul Excel). Intervalul are formatare condiționată care marchează celulele care conțin numere mai mari de 10 și mai mici sau egale cu 20.

Orez. 1. Gama de numere; formatarea condiționată selectează celulele care conțin valori de la 10 la 20

Descărcați nota în format, exemple în format

Acum trebuie să adăugați valorile în celulele care îndeplinesc criteriile pe care tocmai le-ați setat. Nu contează ce formatare se aplică acestor celule, dar trebuie să cunoașteți criteriile după care sunt evidențiate celulele.

Pentru a adăuga un interval de celule corespunzătoare singur criteriu, puteți utiliza funcția SUMIF (Fig. 2).

Orez. 2. Însumarea celulelor care îndeplinesc aceeași condiție

Daca ai nisteîn condiții, puteți utiliza funcția SUMIFS (Fig. 3).

Orez. 3. Însumarea celulelor care îndeplinesc mai multe condiții

Puteți utiliza funcția COUNTIF pentru a număra numărul de celule care îndeplinesc un criteriu.

Puteți utiliza funcția COUNTIFS pentru a număra numărul de celule care îndeplinesc mai multe criterii.

Excel are o altă funcție care vă permite să specificați mai multe condiții. Această funcție face parte din setul de funcții de bază de date Excel și se numește BDSUMM. Pentru a o verifica, utilizați același set de numere în intervalul A2:A100 (Fig. 4; vezi și foaia „BDSUMM” din fișierul Excel).

Orez. 4. Utilizarea funcţiilor bazei de date

Selectați celulele C1:D2 și denumiți intervalul Criterii introducându-l în caseta de nume din stânga barei de formule. Acum selectați celula C1 și introduceți =$A$1, adică un link către prima celulă de pe foaia care conține numele bazei de date. Introduceți =$A$1 în celula D1 și veți obține două copii ale titlului coloanei A. Aceste copii vor fi folosite ca titluri pentru condițiile BDSUMM(C1:D2), pe care le-ați numit Criterii. În celula C2, introduceți >10. În celula D2, introduceți<=20. В ячейке, где должен быть результат, введите следующую формулу:

BDSUMM($A$1:$A$101,1, Criterii)

Puteți utiliza funcția COUNT pentru a număra numărul de celule care îndeplinesc mai multe criterii.

Citind cartea lui John Walkenbach, am aflat că începând cu Excel 2010, a apărut o nouă proprietate DisplayFormat în VBA (vezi, de exemplu, Range.DisplayFormat Property). Adică, VBA poate citi formatul afișat pe ecran. Nu contează cum a fost obținut, prin setările directe ale utilizatorului sau folosind formatarea condiționată. Din păcate, dezvoltatorii MS au făcut astfel încât proprietatea DisplayFormat să funcționeze numai în procedurile numite din VBA, iar funcțiile definite de utilizator bazate pe această proprietate să arunce #VALOARE! Cu toate acestea, puteți obține suma valorilor dintr-un interval pentru celule de o anumită culoare utilizând o procedură (o macrocomandă, dar nu o funcție). Deschis (conține cod VBA). Treceți prin meniu Vedere -> Macro-uri -> Macro-uri; La fereastră Macro, selectați linia SumColorUsl, și apăsați A executa. Rulați macrocomanda, selectați intervalul de însumare și criteriul. Răspunsul va apărea în fereastră.

Cod de procedură

Sub SumColorConv() Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " Range Request Set UserRange = Application.InputBox(_ Prompt:="Select a summation range", _ Title : ="Selectare interval", _ Implicit:=ActiveCell.Address, _ Tip:=8) " Solicitare criteriu Set CriterionRange = Application.InputBox(_ Prompt:="Selectați criteriul de sumare", _ Title:="Selectare criteriu", _ Implicit:=ActiveCell.Address, _ Type:=8) " Însumează celulele „corecte” pentru fiecare i din UserRange Dacă i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat.Interior.Color Apoi SumColor = SumColor + i End Dacă Următorul MsgBox SumColor End Sub

Funcționalitatea principală a Microsoft Excel se bazează pe formule, datorită cărora, folosind acest procesor de foi de calcul, nu numai că puteți crea un tabel de orice complexitate și volum, ci și să calculați anumite valori în celulele sale. Calcularea sumei într-o coloană este una dintre cele mai simple sarcini pe care le întâlnesc cel mai des utilizatorii, iar astăzi vom vorbi despre diverse opțiuni pentru rezolvarea acesteia.

Puteți calcula suma anumitor valori din coloanele unei foi de calcul Excel fie manual, fie automat, folosind instrumentele de bază ale programului. În plus, este posibil să vizualizați valoarea totală în mod normal, fără a o scrie într-o celulă. Vom începe cu ultimul, cel mai simplu.

Metoda 1: Vedeți suma totală

Dacă trebuie doar să vedeți valoarea totală pentru o coloană ale cărei celule conțin anumite informații, dar păstrați constant această sumă în fața ochilor și nu trebuie să creați o formulă pentru a o calcula, faceți următoarele:

1. Folosind mouse-ul, selectați intervalul de celule din coloana a cărei sumă de valori doriți să o calculați. În exemplul nostru, acestea vor fi celulele 3 până la 9 din coloana G.



2. Aruncă o privire la bara de stare (panoul de jos al programului) - suma necesară va fi indicată acolo. Această valoare va fi afișată numai atâta timp cât intervalul de celule este selectat.



3. În plus. Această însumare funcționează chiar dacă există celule goale în coloană.
   
   
   În plus, într-un mod similar, puteți calcula suma tuturor valorilor din celule din mai multe coloane simultan - doar selectați intervalul necesar și priviți bara de stare. Vă vom spune mai multe despre cum funcționează acest lucru în a treia metodă a acestui articol, dar folosind exemplul unei formule șablon.



Notă:În stânga sumei se află numărul de celule selectate, precum și valoarea medie pentru toate numerele specificate în acestea.

Metoda 2: AutoSum

Evident, mult mai des trebuie să păstrați suma valorilor într-o coloană în fața ochilor, să o vedeți într-o celulă separată și, bineînțeles, să urmăriți modificările, dacă sunt făcute în tabel. Soluția optimă în acest caz ar fi însumarea automată efectuată folosind o formulă simplă.

Se întâmplă ca suma să fie afișată nu în celula care se află sub cele însumate, ci într-o altă celulă, poate situată într-o coloană complet diferită a tabelului. În acest caz, trebuie să procedați după cum urmează:

Metoda 3: Autosumă mai multe coloane

1. Faceți clic pe LMB pe celula în care intenționați să afișați suma tuturor valorilor.



2. Faceți clic pe butonul "Sumă" pe bara de instrumente sau utilizați comanda rapidă de la tastatură furnizată pentru introducerea acestei formule.



3. Prima coloană (de deasupra formulei) va fi deja evidențiată. Așa că țineți apăsată tasta „CTRL” de pe tastatură și selectați următorul interval de celule, care ar trebui, de asemenea, inclus în suma calculată.



Notă: Dacă coloanele pe care doriți să le numărați sunt pe rând, le puteți selecta pe toate împreună.

4. Apoi, în același mod, dacă este necesar, țineți apăsată tasta „CTRL”, selectați celule din alte coloane al căror conținut ar trebui inclus și în total.
   
   
   Clic "INTRODUCE" pentru a calcula, după care calculul rezultat apare în celula și rândul desemnate pentru formula din bara de instrumente.


Este ușor de ghicit că, într-un mod similar, puteți calcula suma valorilor în celule individuale, constând atât în ​​coloane diferite, cât și într-una singură.

Pentru a face acest lucru, trebuie doar să faceți clic pe celula pentru formulă, să faceți clic pe butonul "Sumă" sau folosind tastele rapide, selectați primul interval de celule și apoi, în timp ce țineți apăsată tasta „CTRL”, selectați toate celelalte „secțiuni” ale tabelului. După ce ați terminat, faceți clic "INTRODUCE" pentru a afișa rezultatul.


Ca și în metoda anterioară, formula sumei poate fi scrisă în oricare dintre celulele libere ale tabelului, și nu doar în cea situată sub cele însumate.

Metoda 4: Însumarea manuală

Ca orice formulă din Microsoft Excel, "Sumă" are propria sa sintaxă și, prin urmare, poate fi scris manual. De ce este necesar acest lucru? Cel puțin pentru faptul că în acest fel puteți evita cu siguranță erorile accidentale atunci când selectați intervalul însumat și indicați celulele incluse în acesta. În plus, chiar dacă greșești la oricare dintre pași, corectarea acesteia va fi la fel de simplă ca o banală greșeală de tipar din text, ceea ce înseamnă că nu va trebui să faci toată munca de la zero, așa cum trebuie să faci uneori.

Printre altele, introducerea unei formule vă permite să o plasați nu numai oriunde în tabel, ci și pe oricare dintre foile unui anumit document electronic. Merită menționat că în acest fel puteți însuma nu numai coloanele și/sau intervalele acestora, ci și pur și simplu celule individuale sau grupuri ale acestora, excluzându-le pe cele care nu ar trebui luate în considerare?

Formula sumei are următoarea sintaxă:

SUM (celule care trebuie însumate sau intervalul acestora)


Adică tot ce ar trebui rezumat este scris între paranteze. Valorile specificate în paranteze ar trebui să arate astfel:

Acum să trecem direct la însumarea manuală a valorilor din coloana/coloanele foii de calcul Excel.

1. Selectați celula care va conține formula finală apăsând LMB.



2. Introduceți următoarele în el:

   SUMĂ(
   
   
   apoi începeți unul câte unul introducând adresele intervalelor sumate și/sau celulelor individuale în bara de formule, fără a uita de delimitatori ( «:» - pentru gama, «;» - pentru celulele individuale, introduceți fără ghilimele), adică faceți totul conform algoritmului, a cărui esență am descris-o în detaliu mai sus.



3. După ce ați indicat toate elementele care trebuie însumate și asigurându-vă că nu ați omis nimic (puteți naviga nu numai după adresele din bara de formule, ci și prin evidențierea celulelor selectate), închideți paranteza și faceți clic "INTRODUCE" pentru numărare.

   

   Notă: Dacă observați o eroare în formula finală (de exemplu, o celulă suplimentară a fost inclusă sau una a fost omisă), pur și simplu introduceți manual notația corectă (sau ștergeți-o pe cea suplimentară) în bara de formule. În mod similar, dacă și când este necesar, puteți face același lucru cu caracterele delimitare din formular «;» Și «:» .

   

   După cum probabil ați ghicit deja, în loc să introduceți manual adresele celulelor și intervalele acestora, puteți selecta pur și simplu elementele necesare folosind mouse-ul. Principalul lucru este să nu uitați să țineți apăsată tasta „CTRL” când treci de la evidențierea unui element la altul.


Calculul independent, manual al sumei dintr-o coloană/coloane și/sau celule individuale de tabel nu este sarcina cea mai convenabilă sau cea mai rapidă de implementat. Dar, așa cum am spus mai sus, această abordare oferă oportunități mult mai largi de interacțiune cu valorile și vă permite să corectați instantaneu erori aleatorii. Dar dacă să faceți acest lucru complet manual, introducerea fiecărei variabile în formulă de la tastatură sau să utilizați mouse-ul și tastele rapide pentru aceasta depinde de dvs.

Concluzie

Chiar și o sarcină aparent simplă precum calcularea sumei într-o coloană poate fi rezolvată în procesorul de foi de calcul Microsoft Excel în mai multe moduri, iar fiecare dintre ele își va găsi aplicația într-o situație sau alta.