Cum se calculează suma unei coloane în Excel. Cum se calculează suma unei coloane în funcția Excel Subtotal în Excel

Funcția „Subtotaluri()” folosit pentru a calcula un subtotal (sumă, medie, număr de valori etc.) într-un interval în care există date ascunse.

Particularitatea funcției este că este destinată utilizării împreună cu alte instrumente Excel (de exemplu, autofiltre).

Pentru a demonstra beneficiile funcției Subtotals() din Excel, să ne uităm la o descărcare redusă din baza de date de evenimente.

Sarcină: folosind parametrii selectați, evaluați indicatorii cheie în comparație cu situația din toate domeniile.

Evenimentele pot fi filtrate după diferiți parametri, dar pentru comparație am dori să înțelegem indicatorii prin parametri filtrați și să-i comparăm cu situația în ansamblu în toate domeniile.

Pentru a face acest lucru, puteți utiliza funcția Excel =Subtotals(). Funcția =Subtotals() efectuează calcule pe baza valorilor filtrate.

Puteți specifica calculul în formula:

• Mediu – numărul 1 sau 101 – pentru probă
• Numărarea valorilor – numărul 3 sau 103
• Sumă - 9 sau 109
• etc.

• Dacă numărul funcției este format din trei cifre, de exemplu, 109, atunci funcția funcționează ca subtotaluri, de exemplu. calculează valori pe baza parametrilor filtrați.
• Dacă numărul este cu o singură cifră sau cu două cifre 2 sau 11, atunci formula subtotalurilor () funcționează ca o formulă standard pentru funcția selectată.

Să trecem la evenimentele noastre:

Avem o descărcare din baza de date folosind următoarele coloane:

• Situatie
• Acțiuni
• Numar de clienti
• Volum_Vânzări_Până la
• Volum_Vânzări_Plan
• Volum_Vânzări_Act
• Costs_Plan
• Costs_Act

Pentru a evalua situația în ansamblu, introducem o formulă standard de însumare:

Pentru a calcula subtotaluri pentru parametrii selectați, introduceți =SUBTOTAL(109;RC:RC), unde

• 109 – funcția sumă pentru calcularea subtotalurilor;
• RC:RC – referință la intervalul de însumare.

Acum, după ce am filtrat coloana „Situație” - „Situație 4” și „Acțiune” - Acțiunile 2 și 3, vom obține o recalculare a subtotalurilor pentru fiecare dintre indicatori și le putem compara cu indicatorii „Total”:

Ca urmare: se poate observa că creșterea procentuală a vânzărilor pentru evenimentele filtrate este mai mare decât pentru toate evenimentele și au plănuit să obțină rezultate mai mici decât totalul, dar au crescut. Acestea. a funcționat mai bine decât era planificat și mai bine decât toate evenimentele.

Formula Excel =subtotals() este un instrument excelent pentru calcularea indicatorilor pe baza parametrilor filtrați.

Dacă aveți întrebări, vă rugăm să ne contactați!

Alăturaţi-ne!

Descărcați aplicații gratuite de prognoză și analiză de afaceri:

• Novo Forecast Lite- automată calculul prognozei V excela.
• 4analitica - Analiza ABC-XYZși analiza emisiilor Excela.
• Qlik Sense Desktop și QlikViewPersonal Edition - Sisteme BI pentru analiza și vizualizarea datelor.

Returnează un subtotal la o listă sau o bază de date. De obicei, este mai ușor să creezi o listă de subtotaluri folosind comanda Rezultateîn meniu Date. Dar dacă a fost deja creată o listă cu subtotaluri, o puteți modifica prin editarea formulei cu funcția SUBTOTALURI.

Sintaxă

număr_funcție este un număr de la 1 la 11 care specifică ce funcție să folosească atunci când se calculează totalurile dintr-o listă.

Atenţie!

Dacă există deja formule de însumare în interiorul argumentelor link1;link2;... (totaluri imbricate), atunci aceste totaluri imbricate sunt ignorate pentru a evita dubla însumare.

Funcția SUBTOTAL ignoră toate rândurile ascunse care rezultă din filtrarea listei. Acest lucru este important atunci când doriți să rezumați numai datele vizibile care rezultă din filtrarea listei.

Referințele 3D sunt folosite atunci când trebuie să analizați date din aceeași celulă sau interval de celule pe mai multe foi din același registru de lucru. O referință 3D include o referință de celulă sau interval precedată de nume de foi. Microsoft Excel folosește toate foile stocate între numele de început și de sfârșit specificate în link. De exemplu, formula =SUM(Sheet2:Sheet13!B5) însumează toate valorile conținute în celula B5 pe toate foile de la Sheet2 la Sheet13, inclusiv.

Exemplu

Proprietatea funcției de a ignora rândurile ascunse s-a dovedit a fi foarte convenabilă pentru analiza datelor operaționale folosind un filtru.

Să presupunem că avem un fel de contabilitate pentru arsenalul de a lupta cu „invadatorii”. Să creăm un tabel. Dacă tabelul este mare, pentru comoditate, pentru a nu urca de fiecare dată până la capătul mesei, linia de rezumat poate fi plasată deasupra antetului, iar antetul în sine poate fi fixat ca zonă.

O astfel de analiză operațională este foarte utilă pentru executantul tehnic. Imaginați-vă că stați calm în fața computerului personal, la locul de muncă și vă desfășurați în pace treburile personale. Și deodată, șeful a spus: „Spune-mi Tyapkin-Lyapkin, cât avem pentru așa și așa”? În timp ce șeful vorbește despre ce are nevoie, lansați rapid fișierul bazei de date și utilizați filtrul pentru a selecta ceea ce este interesat de conducere. Răspunsul este gata, șeful este fericit, colegii tăi sunt șocați și tu îți treci din nou cu calm treburile.

Este clar că pentru un astfel de rezultat este nevoie chiar de această bază de date, iar crearea și menținerea ei la zi depinde de profesionalismul dumneavoastră. Crede-mă, nimeni nu îți va spune niciodată cum să obții rezultate, toată lumea este interesată doar de rezultat, așa că gândește-te constant la cum să-ți simplifici și să automatizezi munca folosind Excel, cum să minimizezi erorile și profesionalismul tău va atinge cote fără precedent.

Cred că v-ați întrebat cum să rezumați, pe lângă rezultatele generale într-un tabel Excel, și pe cele intermediare, astfel încât, având în vedere, să zicem, un tabel de vânzări de produse pe o lună, să puteți afișa veniturile unui anumit produs. doar pentru o zi. Și apoi, la sfârșit, rezumați rezultatul general al vânzărilor, deoarece, după cum arată statisticile, este mult mai convenabil să luați în considerare vânzările dvs. de produse. În acest articol vom explica în detaliu cum se poate face acest lucru.

Utilizarea funcției Subtotaluri din Excel

Cu toate acestea, așa cum ar părea la prima vedere, funcția de subtotal nu este întotdeauna implementată în toate tabelele Excel. Iată câteva criterii principale care vă vor ajuta să vă dați seama dacă baza de date îndeplinește aceste condiții:

• Rândurile de tabel trebuie să fie în formatul unei zone de celule obișnuite;
• Partea superioară a mesei trebuie să fie făcută dintr-o singură linie și să fie pe prima linie a foii;
• Masa nu trebuie să fie niciodată goală.

Crearea subtotalurilor în Excel

Acum să trecem direct la cel mai important lucru - folosirea funcției în practică. Departamentul responsabil de „Subtotaluri”, situat în meniul de sus al Excel.

Acțiunea 1

Folosiți mouse-ul pentru a determina zona necesară, apoi priviți secțiunea "Date". Găsim acolo un bloc numit "Structura"și selectați ultima linie dintr-o listă mică - „Subtotal”.

Acțiunea 2:

Ar trebui să vedeți o fereastră nouă în care trebuie să configurați afișarea subtotalului dvs. În exemplul pe care l-am dat, ne uităm la totalul existent al vânzărilor totale pentru toate produsele pentru toate zilele. Data vânzării este indicată într-o coloană separată. Prin urmare, în zonă „Cu fiecare schimbare în...” indica coloana "Data de".

Actul 3

Acum să mergem la câmp "Operațiune"și indicați valoarea "Sumă" pentru fiecare zi în care trebuie să ne retragem. Dacă săpați, puteți găsi acolo și o valoare setare: maxim, produs, cantitate, minim.

Acțiunea 4:

Apoi vorbim cu terenul „Adăugați totalurile după”, unde acum trebuie să indicați coloana cu suma veniturilor, adică coloana "Suma de venit, freacă.".

Acțiunea 5:

Bifați caseta de lângă „Înlocuiește totalurile curente”, dacă nu există acolo. Acest pas vă va asigura că atunci când calculați baza de date nu veți avea informații înregistrate duplicat pentru fiecare operațiune a funcției „Subtotal”.

Acțiunea 6:

De asemenea, ar trebui să plasați o bifă lângă element „Sfârșitul paginii între grupuri”. Acest lucru vă va permite să imprimați pe o anumită pagină blocuri individuale ale tabelului cu subtotalurile indicate pe acesta.

Acțiunea 7:

Bifa de lângă „Totale sub date” este responsabil pentru stabilirea sumei subtotalurilor din zona de sub linie. Când eliminați bifa, datele vor fi transferate în zona din partea de sus a liniei. Această setare se face întotdeauna individual de către utilizator, astfel încât să îi fie convenabil să acceseze informațiile afișate.

Acțiunea 8:

Faceți clic pe butonul "BINE"și finalizați configurarea.

Acțiunea 9:

După finalizarea algoritmului de acțiuni de mai sus, veți ajunge la faptul că rezultatele intermediare ale vânzărilor dvs. vor apărea în tabel. Am dori să vă atragem atenția asupra faptului că puteți, dacă doriți, să ascundeți toate rândurile conectate printr-un subtotal doar făcând clic RMB la icoană "-" , situat în partea stângă a tabelului acestui grup.

Acțiunea 10:

Sau puteți, dimpotrivă, să ascundeți toate celelalte rânduri, cu excepția unor subtotaluri.

Acțiunea 11:

Nu trebuie să vă faceți griji cu privire la funcționalitatea instrumentului pe care îl utilizați. Dacă trebuie să faceți modificări în tabel, acesta își va actualiza automat datele.

Formula „REZULTATE INTERMEDIARE”

Există un buton separat pentru operația de care avem nevoie, care ne permite să calculăm folosind un instrument special creat pentru aceasta. Se numeste „Inserare funcție”.

Acțiunea 1:

Faceți clic în avans RMBîn celula dorită unde doriți să indicați subtotalurile. Acum faceți clic pe butonul pe care l-am numit, situat în partea stângă a câmpului de introducere a formulei.

Acțiunea 2:

Ar trebui să apară fereastra dvs „Asistent de funcții”. Selectați o linie din listă „SUBTOTALURI”, apoi faceți clic pe "BINE".

Acțiunea 3:

În câmpurile corespunzătoare definim argumentele funcției. După ce în zonă „Numărul funcției” trebuie să specificați unul dintre 11 parametrii de prelucrare si anume:

1. Media aritmetică;
2. Numărul de celule active;
3. Numărul de celule cu date deja introduse în ele în avans;
4. Cea mai mare valoare posibilă a matricei de date selectate;
5. Valoarea sa minimă;
6. Produsul total al informațiilor conținute în celule;
7. Deviația implicită a datelor eșantionului;
8. Varianta implicită a populației;
9. Valoare totală;
10. Varianta eșantionului;
11. Varianta asupra populatiei generale.

    

    Acțiunea 4:

    Unde este câmpul „Link1”, trebuie să desemnați adresa direct matricei dorite de celule în care doriți să afișați subtotalul. Este posibil să se mențină simultan doar 4 matrice separate. Când specificați locația unui interval de celule, programul însuși va afișa toate intervalele ulterioare pentru o posibilă adăugare ulterioară. Specificarea singura a intervalelor nu este o sarcină foarte plăcută, așa că pentru a fi mai ușor, există un buton care vă permite să faceți acest lucru pentru dvs.

Când lucrați cu date în Excel, ajungeți adesea să construiți tabele mari cu diverse calcule și comentarii, ceea ce le face adesea dificil de înțeles. Pentru a evita astfel de probleme, utilizați structura de date Excel. Se află în fila „Date”, secțiunea „Structură”.

Această secțiune conține 3 funcții. Să le analizăm pe scurt (pentru informații mai complete, urmați linkurile):

• Grup – combină mai multe rânduri sau coloane într-un singur grup. Acest lucru vă permite să ascundeți informații detaliate și, dacă este necesar, să le accesați rapid;
• Degrupare – anulează îmbinarea rândurilor și coloanelor în grupuri complet sau parțial;
• Rezultate intermediare – combinarea datelor „conexe” în grupuri și extragerea unor rezultate din acestea.

Două butoane („+” și „-”) din partea dreaptă sus a secțiunii dezvăluie sau ascund detaliile grupului, dar este adesea mai convenabil să utilizați aceste butoane în câmpurile foii Excel care apar la combinarea datelor în structuri.

Când construiți tabele mari, este util să folosiți gruparea datelor pentru a ascunde informații detaliate, prezentând cele mai importante informații în cel mai bun mod posibil. În același timp, atunci când este necesar să se obțină clarificări asupra oricăror indicatori, datele grupate pot fi afișate rapid prin extinderea unui anumit grup. Să aruncăm o privire mai atentă.

Avem un raport de vânzări pentru toți agenții care lucrează pentru anul. Informațiile sunt prezentate pe lună. Dar astfel de detalii în scopul analizei noastre sunt redundante, dar dacă este necesar, trebuie să le obținem rapid. Prin urmare, nu este nevoie să-l ștergeți. Am putea pur și simplu să ascundem coloanele și asta ne-ar servi scopului. Dar dacă faci un raport nu pentru tine, ci pentru conducere sau altcineva. Nu poți fi 100% sigur că cei care vor lucra cu tabelele tale vor fi pricepuți în Excel. Ieșirea din situație este gruparea. Se află în fila „Date”, secțiunea „Structură”.

În tabelul nostru exemplu, selectați coloanele raportului care conțin informații detaliate, apoi faceți clic pe pictograma „Grup”. Nu este posibil să creați mai multe grupuri deodată, așa că creați-le unul câte unul. Iată cum arăta raportul înainte de grupare:

Figura arată că datele sunt grupate pe lună pentru anumite trimestre, formând astfel 4 grupuri. Nu sunt incluse doar rezultatele trimestriale. Făcând clic pe pictogramele minus, informațiile detaliate sunt ascunse. Raportul arată astfel:

Acum arată mai compact și afișează cele mai importante informații. În același timp, deschizând oricare dintre grupuri, vă puteți familiariza cu detaliile.

Atenție la numerele afișate în imagine. Acestea prezintă butoane care vă permit să dezvăluiți și să ascundeți rapid toate grupurile simultan. Butonul 1 ascunde toate nivelurile de grupuri, butonul 2 dezvăluie grupurile din primul nivel. Dacă se creează un alt grup în cadrul unui grup, acesta va fi atribuit la nivelul 3, iar la butoane se va adăuga altul, cu numărul 3 etc.

Pentru a elimina coloanele dintr-un grup, trebuie să le selectați și în secțiunea „Structură” faceți clic pe pictograma „Degrupare”. Dacă doriți să eliminați întreaga grupare simultan, selectați întreaga foaie Excel și faceți clic pe aceeași pictogramă. Vi se va solicita să degrupați coloanele sau datele. Selectați opțiunea dorită și faceți clic pe OK. Aplicația va elimina toate grupările de nivel superior, lăsându-le pe cele inferioare.

Puteți grupa atât coloanele, cât și rândurile. Ambele sunt supuse acelorași reguli.

Secțiunea „Structură”, pe lângă gruparea și degruparea datelor, are o altă funcție – „Subtotaluri”. Cu ajutorul acestuia, puteți seta gruparea automată a datelor cu un rezumat al acestora. Pentru a înțelege mai clar despre ce vorbim, să ne uităm la un exemplu de tabel al angajaților cu rezultatele vânzărilor lor pentru trimestrul.

Selectând tabelul și făcând clic pe pictograma „Subtotaluri”, aplicația va afișa o fereastră:

În câmpul „Pentru fiecare modificare în:”, trebuie să specificați titlul coloanei tabelului după care va fi determinată rezumatul. Celulele din rândurile de sus ale tabelului selectat acționează ca antet. Etichetele din celulele coloanei specificate vor servi ca titluri de rând. Programul va verifica titlurile pentru similaritate. Dacă titlurile rândurilor se potrivesc, atunci se adaugă la calculul total dacă nu se potrivesc, atunci rezultatul pentru grupul definit de un titlu este afișat într-o linie suplimentară. Prin urmare, vă recomandăm să sortați în prealabil după această coloană.

În al doilea câmp „Operațiune:” selectați care total trebuie rezumat: Sumă, cantitate, medie etc.. În ultimul câmp „Adăugați total prin:” selectați coloana care conține datele necesare pentru afișarea rezultatului.

Mai jos sunt 3 casete de selectare (steaguri):

• Înlocuiți totalurile curente – dacă se setează, atunci rezultatele însumate mai devreme vor fi înlocuite, dacă nu, atunci se vor adăuga rezultate noi la cele vechi;
• Sfârșitul paginii între grupuri – dacă este setat, atunci când este tipărit, fiecare grup separat cu totalul va fi amplasat pe o coală nouă;
• Totaluri sub date – dacă se setează, rezultatul va fi situat la sfârșitul grupului, dacă nu, atunci la început.

Butonul Remove All elimină întreaga structură de date.

Iată cum a fost transformat tabelul după aplicarea funcției descrise:

Noile celule adăugate conțin funcția matematică SUBTOTAL(), care poate fi găsită în articolul despre funcții matematice - Excel Mathematical Functions.

Mecanismul de însumare a subtotalurilor are caracteristici comune cu un alt instrument mai puternic din Excel - tabelele pivot.

Bună ziua, dragi cititori! Astăzi vom vorbi despre formula REZULTATE INTERMEDIARE. Pentru mine, cea mai mare întrebare a fost întotdeauna scopul acestei formule. În acest articol voi încerca să dezvălui esența manipulărilor noastre cu această funcție.

Judecând după nume, formula însumează (adică însumează) indicatorii de care avem nevoie. Pentru a fi mai clar, să ne imaginăm următorul tabel.

Dacă dintr-un motiv oarecare trebuie să ascund rânduri individuale (un filtru, de exemplu), formula obișnuită de autosumă (SUM) va include în continuare chiar și valorile ascunse.

Ce ar trebui să fac dacă vreau să fac o selecție doar pentru contrapartea Yulmart și să calculez rezultatele doar pentru aceasta? Aici este utilă formula subtotalului.

Apropo, de îndată ce pornim filtrul, Excel, când faceți clic pe butonul Autosum, va converti formula în subtotaluri singur, adică ținând cont de conținutul filtrat al tabelului.

Să aruncăm o privire la formula - =INTERMEDIATE.TOTAL(109,[Amount]). Ne vor interesa mai ales argumentele dintre paranteze, deoarece numele formulei, cred, nu merită explicat.

În paranteze vedem misteriosul număr 109 și cuvântul Sum. Nici aici nu este nimic misterios, deoarece privind certificatul de la Microsoft totul va deveni imediat clar.

Numărul 109 indică ce trebuie făcut dacă filtrul este pornit (în numele coloanei cu sute este scris - „cu excluderea valorilor ascunse”, dar dacă avem nevoie de „cu includerea valorilor ascunse”, pur și simplu îl vom înlocui cu elementul dorit din tabel). Dacă aveam nevoie de o valoare diferită, de exemplu, 104 este valoarea maximă din tabel, pot pur și simplu corecta formula manual sau pur și simplu o selectez din lista derulantă.

Vedeți, îmi va arăta imediat suma maximă de vânzare către o altă contraparte - Topcomputer. În mod similar, puteți încerca valoarea minimă.

De asemenea, puteți corecta totul nu manual, dar utilizați lista derulantă după celula noastră cu formula.