Pentru a primi diverse informații sumare în MS Excel, se utilizează comanda Data → Totals. Înainte de a executa această comandă, datele trebuie prezentate într-o listă. Dar, înainte de a rezuma, trebuie să sortați în mod necesar lista.
În comanda Totaluri, se deschide caseta de dialog Intermediate Totals din meniul Date. Un câmp este setat în acesta, de fiecare dată când se modifică valoarea, vor fi calculate valorile finale și operația care se va aplica valorilor câmpurilor marcate în lista Adăugați totaluri după.
Crearea subtotaluri bazate pe pre-sortarea listei de înregistrări, iar ordinea de sortare este important - compoziția și subordonarea sortare cheie. Dacă sortarea a fost efectuată pe câmpuri: câmpul 1, câmpul 2, câmpul 3, adică Câmpul 1 este cel mai semnificativ în zona de sortare 2 determină ordinea rândurilor de sortare enumeră aceleași valori ale câmpului 1 și câmpul 3 specifică ordinea de sortare pentru aceleași valori și câmpul 1 și câmpul 2, - și apoi însumarea are o ordine strictă: Câmpul 1 , câmpul 2 sau câmpul 1.
Dacă tabelul conține deja rânduri rezumate, puteți adăuga noi totaluri la acesta, calculate utilizând o altă funcție. Pentru aceasta, în fereastra Subtotaluri, debifați caseta de selectare Înlocuiți totalurile curente și specificați câmpul și funcția necesare (funcționare). Celelalte două casete vă permit să plasați totaluri sub sau peste liniile de date și să afișați fiecare grup de valori pe o foaie separată.
Dacă nu mai aveți nevoie de subtotaluri, puteți să reinițializați lista în starea inițială făcând clic pe butonul Anulare. dar anularea funcționează numai dacă nu există alte modificări în listă după formatarea totalurilor, altfel faceți clic pe butonul Ștergere toate. care returnează lista în starea inițială.
Comanda Totaluri din meniul de date vă permite să efectuați următoarele acțiuni:
- prin câmpul separat al listei utilizate ca câmp de grupare, este posibilă generarea rezultatelor diferitelor tipuri de operații (funcții);
- pentru aceeași valoare a câmpului de grupare, puteți genera totaluri pentru unul sau mai multe câmpuri din listă, în timp ce tipul de operație determină câmpurile care pot fi folosite pentru a rezuma. Deci, pentru operațiunile Sum. În medie. Maximul. Minime și așa mai departe, pot fi selectate numai câmpurile de tip numeric. Pentru funcționare Număr de valori - câmpuri de orice tip (numeric, text, date).
Prin studierea procesului de sintetizare a subtotalurilor, vom opera datele din tabelul afișat în Fig.
Figura 97 - Tabel exemplu
Sarcină: Utilizați funcția de calcul automat a totalurilor pentru a determina cifra de afaceri a fiecărui vânzător pentru o anumită perioadă de timp.
1. Setați indicatorul de celule în orice celulă din tabel și activați comanda Sortare din meniul Date. Intervalul de sortare va fi selectat automat. Setați criteriile de sortare în caseta de dialog Sortare, după cum se arată în Fig.
Figura 98 - Intervalul de sortare al casetei de dialog
După ce faceți clic pe OK în caseta de dialog Sortare sortare, datele din tabel vor fi sortate (în coloana vânzător, numele vânzătorilor vor fi sortate în ordine alfabetică).
2. Activați una dintre celulele tabelului și selectați comanda Rezultate din meniul Date. Aceasta va deschide caseta de dialog Rezumat subtotal. În lista la fiecare schimbare în, selectați antetul coloanei vândut pentru care să calculeze subtotaluri după fiecare schimbare a datelor din foaia de lucru. Pentru a calcula suma totală în câmpul Listă operații, setați funcția Sum. Specificați și o coloană pentru calcularea totalurilor, de exemplu, coloana Cifra de afaceri. Pentru a face acest lucru, activați opțiunea Cifra de afaceri din zona Adăugați totaluri și dezactivați opțiunea Data. așa cum se arată în fig. În final, faceți clic pe butonul OK.
Figura 99 - caseta de dialog Rezultatul subtotal
Ca rezultat, tabelul va fi completat cu linii care conțin valorile finale pentru fiecare vânzător. Ultimul rând introdus în tabel include informații despre rezultatul total pentru toți vânzătorii (Figura 100).
Figura 100 - Tabel exemplu după calculul automat al totalurilor
Pentru a procesa date din coloanele specificate în caseta de dialog Intermediare Total. pot fi utilizate următoarele funcții enumerate în Tabelul 1:
Tabelul 23 - Funcții pentru prelucrarea datelor utilizând subtotaluri
Determină valoarea varianței pentru eșantionul general
3. Când se calculează totalurile, tabelul este structurat. Crearea unor niveluri de structură conduce la o creștere a vizibilității tabelului. Pentru a afișa numai date sumare pe ecran, faceți clic pe butonul celui de-al doilea nivel al structurii, astfel încât datele din al treilea nivel (valorile inițiale) să fie ascunse (figura 101). Pentru a restabili afișarea valorilor inițiale, faceți clic pe butonul al treilea nivel.
Figura 101 - Datele treilea nivel sunt ascunse
Atenție vă rog! Înainte de a calcula automat totalurile, nu uitați să sortați datele din tabel. În caz contrar, valorile totale vor fi definite pentru fiecare înregistrare.
4. Luați în considerare o altă caracteristică MS Excel - combinând mai multe totaluri (folosind totaluri imbricate). Adăugați la masă un indicator suplimentar - numărul de mașini vândute de fiecare vânzător.
a) Redeschideți caseta de dialog Rezumat intermediar.
b) În câmpul Every time you change, selectați Seller.
Pentru a determina numărul de mașini vândute de fiecare vânzător, setați câmpul Operație la funcția Număr de valori și activați în zona Add totals prin opțiunea Mark. Puteți specifica orice coloană, deoarece datele sunt deja sortate pe grupuri și trebuie doar să determinați numărul de rânduri din grup.
Notă: Pentru a afișa toate sumele din tabel (Fig.102), înainte de a apăsa butonul OK trebuie să opriți opțiunea Înlocuirea totalurilor curente.
Pentru a șterge rânduri cu valori sumare, faceți clic pe butonul Eliminați toate. localizat în caseta de dialog Rezultate interimar.
Figura 102 - Tabel exemplu după calcularea subtotalurilor
Consolidarea este un alt mod de a obține informații sumare; Agregare în funcție de funcția de procesare a datelor selectată, reprezentată în regiuni sursă sursă. Rezultatul consolidării este în zona de destinație. Sursele pot fi în diferite foi de lucru sau cărți de lucru. În consolidare, pot participa până la 255 de regiuni sursă, iar sursele pot fi închise în timpul consolidării.
Pentru a consolida datele, cursorul este setat la zona de locație. Se execută comanda Data → Consolidare. selectați opțiunea și setați condițiile pentru consolidare.
Există următoarele opțiuni pentru consolidare:
- consolidare pe locație pentru surse organizate la fel de bine (locație fixă);
- consolidarea datelor externe.
Când consolidați datele externe, faceți clic pe butonul Răsfoiți. În caseta de dialog Răsfoiți, selectați fișierul care conține zonele sursă pentru a adăuga la listă, apoi adăugați o referință la celulă sau specificați numele blocului celular.
Crearea legăturii Creare relații cu datele sursă creează atunci când zona de destinație este consolidată în zonele sursă.
În studiul procesului de consolidare, vom funcționa cu datele din tabelele din figura 103 (a, b, c)
Figura 103 - Date privind volumul vânzărilor în ramuri: a) - Orenburg, b) - Buzuluk, c) - Orsk
Sarcină: utilizând operația de consolidare, calculați volumul total al vânzărilor de automobile pentru toate sucursalele.
1. În rândul "Total" pentru fiecare tabel, rezumați datele.
2. În fila 4, activați celula A4, care va porni gama de celule cu totaluri.
3. Activați comanda Consolidare din meniul Date.
4. În fereastra Consolidare cu același nume din câmpul Funcție, selectați funcția care va fi utilizată atunci când datele sunt fuzionate (în exemplul nostru Suma).
8. Setați valoarea valorii coloanei din stânga în zona de utilizare ca nume ale casetei de dialog Consolidare. În acest fel, specificați consolidarea după nume. Valorile din liniile cu aceleași etichete vor fi însumate.
9. Setați opțiunea de a crea legături la datele originale (figura 104), astfel încât să se stabilească o legătură dinamică între datele originale și rezultatele de consolidare, care asigură actualizarea automată a datelor.
Figura 104 - Caseta de dialog de consolidare, care specifică parametrii de consolidare
10. După ce faceți clic pe OK, trebuie să activați foaia de lucru în care ar trebui afișate rezultatele de consolidare.
Tabelul rezultat constă din două coloane. Prima conține numele producătorilor, al doilea indică numărul de mașini vândute de toate sucursalele companiei.
Sarcini de auto-împlinire:
1. Formați tabelul cu datele consolidate, setați limitele (Fig.105).
2. Construiți o diagramă bazată pe un tabel cu date consolidate.
Figura 105 - Date consolidate
Întrebări pentru autocontrol
1. Cum sunt rezultatele în listele formate conform criteriilor specificate?
2. Listați funcțiile care pot fi folosite pentru a calcula totalurile?
3. Ce este consolidarea tabelului?
4. Ce opțiuni de consolidare știți?
5. Indicați principalele caracteristici ale fiecărui tip de consolidare.
Articole similare
Trimiteți-le prietenilor: