Metoda 1. Formule
Să începem, pentru încălzire, cu cea mai simplă variantă – formulele. Dacă avem un tabel mic sortat după dată ca intrare, atunci pentru a calcula totalul curent într-o coloană separată, avem nevoie de o formulă elementară:
Principala caracteristică aici este fixarea dificilă a intervalului în interiorul funcției SUM – referința la începutul intervalului este făcută absolută (cu semnele dolarului), iar la sfârșit – relativă (fără dolari). În consecință, atunci când copiați formula în întreaga coloană, obținem un interval de extindere, a cărui sumă o calculăm.
Dezavantajele acestei abordări sunt evidente:
- Tabelul trebuie sortat după dată.
- Când adăugați noi rânduri cu date, formula va trebui extinsă manual.
Metoda 2. Tabel pivot
Această metodă este puțin mai complicată, dar mult mai plăcută. Și pentru a agrava, să luăm în considerare o problemă mai serioasă – un tabel de 2000 de rânduri de date, unde nu există sortare după coloana de dată, dar există repetări (adică putem vinde de mai multe ori în aceeași zi):
Transformăm tabelul nostru original într-o scurtătură „inteligentă” (dinamică) de la tastatură Ctrl+T sau echipa Acasă – Formatați ca tabel (Acasă — Format ca tabel), iar apoi construim un tabel pivot pe el cu comanda Insert – PivotTable (Inserați — Tabel Pivot). Punem data în zona rândurilor din rezumat și numărul de bunuri vândute în zona de valori:
Vă rugăm să rețineți că, dacă aveți o versiune de Excel nu tocmai veche, atunci datele sunt grupate automat pe ani, trimestre și luni. Dacă aveți nevoie de o grupare diferită (sau nu aveți nevoie de ea deloc), atunci o puteți repara făcând clic dreapta pe orice dată și selectând comenzi Grupați / Degrupați (Grupați/Degrupați).
Dacă doriți să vedeți atât totalurile rezultate pe perioade, cât și totalul cumulat într-o coloană separată, atunci este logic să aruncați câmpul în zona de valori Vândut din nou pentru a obține un duplicat al câmpului – în el vom activa afișarea totalurilor cumulate. Pentru a face acest lucru, faceți clic dreapta pe câmp și selectați comanda Calcule suplimentare – Total cumulativ (Afișați valorile ca — Totaluri curente):
Acolo puteți selecta și opțiunea de creștere a totalurilor ca procent, iar în fereastra următoare trebuie să selectați câmpul pentru care va merge acumularea – în cazul nostru, acesta este câmpul de dată:
Avantajele acestei abordări:
- O cantitate mare de date este citită rapid.
- Nu trebuie introduse manual formule.
- La modificarea datelor sursă este suficient să actualizați rezumatul cu butonul din dreapta al mouse-ului sau cu comanda Date – Refresh All.
Dezavantajele rezultă din faptul că acesta este un rezumat, ceea ce înseamnă că nu poți face tot ce vrei în el (inserați linii, scrieți formule, construiți diagrame etc.) nu va mai funcționa.
Metoda 3: Power Query
Să încărcăm tabelul nostru „inteligent” cu date sursă în editorul de interogări Power Query folosind comanda Date – Din tabel/gamă (Date - Din tabel/gamă). În cele mai recente versiuni de Excel, apropo, a fost redenumit – acum se numește Cu frunze (Din foaie):
Apoi vom efectua următorii pași:
1. Sortați tabelul în ordine crescătoare după coloana de dată cu comanda Sortare ascendentă în lista derulantă a filtrului din antetul tabelului.
2. Puțin mai târziu, pentru a calcula totalul curent, avem nevoie de o coloană auxiliară cu numărul de rând ordinal. Să-l adăugăm cu comanda Adăugați coloană – coloană index – de la 1 (Adăugați coloană — coloană index — De la 1).
3. De asemenea, pentru a calcula totalul curent, avem nevoie de o referință la coloană Vândut, unde se află datele noastre rezumate. În Power Query, coloanele sunt numite și liste (listă) și pentru a obține un link către aceasta, faceți clic dreapta pe antetul coloanei și selectați comanda detalierea (Arată detalii). Expresia de care avem nevoie va apărea în bara de formule, constând din numele pasului anterior #”Index adăugat”, de unde luăm tabelul și numele coloanei [Vânzări] din acest tabel între paranteze drepte:
Copiați această expresie în clipboard pentru utilizare ulterioară.
4. Ștergeți ultimul pas care nu este necesar Vândut și adăugați în schimb o coloană calculată pentru calcularea totalului curent cu comanda Adăugarea unei coloane – coloană personalizată (Adăugați coloană — coloană personalizată). Formula de care avem nevoie va arăta astfel:
Aici funcția Listă.Interval preia lista originală (coloana [Vânzări]) și extrage elemente din acesta, începând de la primul (în formulă, acesta este 0, deoarece numerotarea în Power Query începe de la zero). Numărul de elemente de preluat este numărul rândului pe care îl luăm din coloană [Index]. Deci, această funcție pentru primul rând returnează doar o primă celulă a coloanei Vândut. Pentru a doua linie – deja primele două celule, pentru a treia – primele trei etc.
Ei bine, atunci funcția Lista.Suma însumează valorile extrase și obținem în fiecare rând suma tuturor elementelor anterioare, adică total cumulativ:
Rămâne să ștergem coloana Index de care nu mai avem nevoie și să încărcăm rezultatele înapoi în Excel cu comanda Home – Închidere și încărcare la.
Problema este rezolvată.
Rapid și furios
În principiu, acest lucru ar fi putut fi oprit, dar există o mică muscă în unguent – cererea pe care am creat-o funcționează cu viteza unei țestoase. De exemplu, pe computerul meu care nu este cel mai slab, un tabel de numai 2000 de rânduri este procesat în 17 secunde. Dacă există mai multe date?
Pentru a accelera, puteți utiliza buffering folosind funcția specială List.Buffer, care încarcă lista (lista) dată ca argument în RAM, ceea ce accelerează foarte mult accesul la ea în viitor. În cazul nostru, este logic să salvați lista #”Index adăugat”[Vândut], pe care Power Query trebuie să o acceseze atunci când calculează totalul curent din fiecare rând din tabelul nostru de 2000 de rânduri.
Pentru a face acest lucru, în editorul Power Query din fila Principal, faceți clic pe butonul Editor avansat (Acasă – Editor avansat) pentru a deschide codul sursă al interogării noastre în limbajul M încorporat în Power Query:
Și apoi adăugați o linie cu o variabilă acolo Lista mea, a cărui valoare este returnată de funcția de buffering, iar la pasul următor înlocuim apelul la listă cu această variabilă:
După efectuarea acestor modificări, interogarea noastră va deveni semnificativ mai rapidă și va face față unui tabel de 2000 de rânduri în doar 0.3 secunde!
Alt lucru, nu? 🙂
- Diagrama Pareto (80/20) și cum să o construiți în Excel
- Căutare de cuvinte cheie în text și tamponarea interogărilor în Power Query