Përmbajtje
Metoda 1. Formulat
Le të fillojmë, për ngrohjen, me opsionin më të thjeshtë - formulat. Nëse kemi një tabelë të vogël të renditur sipas datës si hyrje, atëherë për të llogaritur totalin e ekzekutuar në një kolonë të veçantë, na duhet një formulë elementare:
Karakteristika kryesore këtu është fiksimi i ndërlikuar i diapazonit brenda funksionit SUM - referenca në fillim të diapazonit bëhet absolute (me shenja dollari), dhe në fund - relative (pa dollarë). Prandaj, kur kopjojmë formulën në të gjithë kolonën, marrim një gamë në zgjerim, shumën e të cilit llogarisim.
Disavantazhet e kësaj qasjeje janë të dukshme:
- Tabela duhet të renditet sipas datës.
- Kur shtoni rreshta të rinj me të dhëna, formula do të duhet të zgjerohet manualisht.
Metoda 2. Tabela kryesore
Kjo metodë është pak më e ndërlikuar, por shumë më e këndshme. Dhe për ta përkeqësuar, le të shqyrtojmë një problem më serioz – një tabelë me 2000 rreshta të dhënash, ku nuk ka renditje sipas kolonës së datës, por ka përsëritje (dmth mund të shesim disa herë në të njëjtën ditë):
Ne e konvertojmë tabelën tonë origjinale në një shkurtore të tastierës "të zgjuar" (dinamike). Ctrl+T ose ekipi Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë), dhe më pas ndërtojmë një tabelë kryesore mbi të me komandën Insert – PivotTable (Fut - Tabela kryesore). Ne vendosim datën në zonën e rreshtave në përmbledhje, dhe numrin e mallrave të shitura në zonën e vlerave:
Ju lutemi vini re se nëse keni një version jo mjaft të vjetër të Excel, atëherë datat grupohen automatikisht sipas viteve, tremujorëve dhe muajve. Nëse keni nevojë për një grupim tjetër (ose nuk ju nevojitet fare), atëherë mund ta rregulloni duke klikuar me të djathtën në çdo datë dhe duke zgjedhur komandat Grupi / Çgruponi (Grupi / Çgrupoj).
Nëse dëshironi të shihni të dyja totalet që rezultojnë sipas periudhave dhe totalin e ekzekutimit në një kolonë të veçantë, atëherë ka kuptim të hidhni fushën në zonën e vlerës Shitur përsëri për të marrë një dublikatë të fushës - në të do të aktivizojmë shfaqjen e totaleve të ekzekutimit. Për ta bërë këtë, klikoni me të djathtën në fushë dhe zgjidhni komandën Llogaritjet shtesë – Totali kumulativ (Trego vlerat si - Totalet e ekzekutimit):
Aty mund të zgjidhni edhe opsionin e rritjes së totaleve si përqindje, dhe në dritaren tjetër duhet të zgjidhni fushën për të cilën do të shkojë akumulimi - në rastin tonë, kjo është fusha e datës:
Përparësitë e kësaj qasjeje:
- Një sasi e madhe e të dhënave lexohet shpejt.
- Asnjë formula nuk duhet të futet manualisht.
- Gjatë ndryshimit të të dhënave burimore, mjafton të përditësoni përmbledhjen me butonin e djathtë të mausit ose me komandën Data – Refresh All.
Disavantazhet rrjedhin nga fakti se kjo është një përmbledhje, që do të thotë se nuk mund të bëni çfarë të doni në të (futni rreshta, shkruani formula, ndërtoni ndonjë diagram, etj.) nuk do të funksionojë më.
Metoda 3: Query Power
Le të ngarkojmë tabelën tonë "të zgjuar" me të dhëna burimore në redaktuesin e pyetjeve të Power Query duke përdorur komandën Të dhënat – Nga tabela/vargu (Të dhënat - nga tabela/vargu). Në versionet më të fundit të Excel, nga rruga, u riemërua - tani quhet Me gjethe (Nga Fleta):
Më pas do të kryejmë hapat e mëposhtëm:
1. Renditni tabelën në rend rritës sipas kolonës së datës me komandën Rendit në rritje në listën rënëse të filtrit në kokën e tabelës.
2. Pak më vonë, për të llogaritur totalin e ekzekutimit, na duhet një kolonë ndihmëse me numrin rendor të rreshtit. Le ta shtojmë me komandën Shto kolonën – kolonën e indeksit – Nga 1 (Shto kolonën — kolonën e indeksit — Nga 1).
3. Gjithashtu, për të llogaritur totalin e ekzekutimit, na duhet një referencë për kolonën Shitur, ku qëndrojnë të dhënat tona të përmbledhura. Në Power Query, kolonat quhen gjithashtu lista (lista) dhe për të marrë një lidhje me të, kliko me të djathtën në kokën e kolonës dhe zgjidhni komandën Detajuar (Trego detajet). Shprehja që na nevojitet do të shfaqet në shiritin e formulës, i përbërë nga emri i hapit të mëparshëm #"Indeksi u shtua", nga ku marrim tabelën dhe emrin e kolonës [Shitjet] nga kjo tabelë në kllapa katrore:
Kopjojeni këtë shprehje në clipboard për përdorim të mëtejshëm.
4. Fshi hapin e fundit më të panevojshëm Shitur dhe në vend të kësaj shtoni një kolonë të llogaritur për llogaritjen e totalit të ekzekutimit me komandën Shtimi i një kolone - Kolona e personalizuar (Shto kolonë - kolonë e personalizuar). Formula që na nevojitet do të duket si kjo:
Këtu funksioni Lista.Raga merr listën origjinale (kolona [Shitjet]) dhe nxjerr elemente prej tij, duke filluar nga e para (në formulë, kjo është 0, pasi numërimi në Power Query fillon nga zero). Numri i elementeve që duhen marrë është numri i rreshtit që marrim nga kolona [Indeksi]. Pra, ky funksion për rreshtin e parë kthen vetëm një qelizë të parë të kolonës Shitur. Për rreshtin e dytë - tashmë dy qelizat e para, për të tretën - tre të parat, etj.
Epo, atëherë funksioni Lista.Suma mbledh vlerat e nxjerra dhe marrim në çdo rresht shumën e të gjithë elementëve të mëparshëm, pra totalin kumulativ:
Mbetet të fshijmë kolonën Index që nuk na nevojitet më dhe të ngarkojmë rezultatet përsëri në Excel me komandën Home – Close & Load.
Problemi është zgjidhur.
Të shpejtë dhe të furishëm
Në parim, kjo mund të ishte ndalur, por ka një mizë të vogël në vaj - kërkesa që krijuam funksionon me shpejtësinë e një breshke. Për shembull, në kompjuterin tim jo më të dobët, një tabelë me vetëm 2000 rreshta përpunohet në 17 sekonda. Po sikur të ketë më shumë të dhëna?
Për të shpejtuar, mund të përdorni buferimin duke përdorur funksionin special List.Buffer, i cili ngarkon listën (listën) që i është dhënë si argument në RAM, gjë që përshpejton shumë aksesin në të në të ardhmen. Në rastin tonë, ka kuptim të fshihet lista #"Indeks i shtuar"[Shitur], në të cilën Power Query duhet të ketë akses kur llogarit totalin e ekzekutuar në çdo rresht të tabelës sonë me 2000 rreshta.
Për ta bërë këtë, në redaktuesin Power Query në skedën kryesore, klikoni butonin Advanced Editor (Home – Advanced Editor) për të hapur kodin burimor të pyetjes sonë në gjuhën M të integruar në Power Query:
Dhe pastaj shtoni një rresht me një ndryshore atje Lista ime, vlera e të cilit kthehet nga funksioni buffering dhe në hapin tjetër zëvendësojmë thirrjen në listë me këtë variabël:
Pas kryerjes së këtyre ndryshimeve, pyetja jonë do të bëhet dukshëm më e shpejtë dhe do të përballet me një tabelë me 2000 rreshta në vetëm 0.3 sekonda!
Një gjë tjetër, apo jo? 🙂
- Grafiku Pareto (80/20) dhe si ta ndërtoni atë në Excel
- Kërkimi i fjalëve kyçe në tekst dhe buferimi i pyetjeve në Power Query