Method 1. Formulen
Loosst eis ufänken, fir d'Erwiermung, mat der einfachster Optioun - Formelen. Wa mir e klengen Dësch hunn no Datum als Input zortéiert, dann fir de lafenden Total an enger separater Kolonn ze berechnen, brauche mir eng elementar Formel:
D'Haaptfunktioun hei ass déi komplizéiert Fixéierung vun der Gamme bannent der SUM Funktioun - d'Referenz op den Ufank vum Gamme gëtt absolut gemaach (mat Dollar Zeechen), an zum Schluss - relativ (ouni Dollar). Deementspriechend, wann Dir d'Formel op d'ganz Kolonn kopéiert, kréie mir en erweiderten Beräich, d'Zomm vun deem mir berechent.
D'Nodeeler vun dëser Approche sinn evident:
- Den Dësch muss no Datum zortéiert ginn.
- Wann Dir nei Zeile mat Daten bäidréit, muss d'Formel manuell verlängert ginn.
Method 2. Pivot Dësch
Dës Method ass e bësse méi komplizéiert, awer vill méi agreabel. A fir ze verschäerfen, loosst eis e méi eeschte Problem betruechten - en Dësch vun 2000 Zeilen vun Daten, wou et keng Sortéierung vun der Datumkolonne gëtt, awer et gëtt Widderhuelungen (dh mir kënnen e puer Mol am selwechten Dag verkafen):
Mir konvertéieren eis ursprénglech Dësch an eng "Smart" (dynamesch) Tastatur Ofkiirzung Ctrl+T oder Team Home - Format als Dësch (Home - Format als Table), an dann bauen mir e Pivot-Table op et mam Kommando Insert - PivotTable (Insert - Pivot Table). Mir setzen den Datum an de Reihenberäich am Resumé, an d'Zuel vun de Wueren am Wäertberäich verkaaft:
Maacht weg datt wann Dir eng net ganz al Versioun vun Excel hutt, da ginn d'Datumer automatesch no Joeren, Véierel a Méint gruppéiert. Wann Dir eng aner Gruppéierung braucht (oder se guer net braucht), da kënnt Dir et fixéieren andeems Dir op all Datum klickt a wielt Kommandoen Group / Ungroup (Grupp / Ungroup).
Wann Dir souwuel déi resultéierend Totalen duerch Perioden wéi och de lafenden Total an enger separater Kolonn wëllt gesinn, dann ass et Sënn fir d'Feld an d'Wäertergebitt ze werfen Verkaaft erëm eng Duplikat vum Feld ze kréien - an et wäerte mir d'Display vun de lafenden Totalen ausschalten. Fir dëst ze maachen, klickt riets op d'Feld a wielt de Kommando Zousätzlech Berechnungen - Kumulativ Total (Wäerter weisen als - Lafend Total):
Do kënnt Dir och d'Optioun auswielen fir Gesamtzuelen als Prozentsaz ze wuessen, an an der nächster Fënster musst Dir de Feld auswielen fir deen d'Akkumulation geet - an eisem Fall ass dëst den Datumfeld:
D'Virdeeler vun dëser Approche:
- Eng grouss Quantitéit un Daten gëtt séier gelies.
- Keng Formelen mussen manuell aginn ginn.
- Wann Dir d'Quelldaten ännert, ass et genuch fir de Resumé mat der rietser Maus Knäppchen ze aktualiséieren oder mam Kommando Data - All Update.
D'Nodeeler entstinn aus der Tatsaach datt dëst e Resumé ass, dat heescht datt Dir net alles maache kënnt wat Dir wëllt dran (Zeilen setzen, Formelen schreiwen, Diagrammer bauen, etc.) net méi funktionnéiert.
Method 3: Power Query
Loosst eis eis "Smart" Tabelle mat Quelldaten an de Power Query Ufro Editor mat dem Kommando lueden Donnéeën - Vun Table / Range (Daten - Vun Table / Range). An de leschte Versioune vun Excel, iwwregens, gouf et ëmbenannt - elo heescht et Mat Blieder (vum Blat):
Da wäerte mir déi folgend Schrëtt maachen:
1. Sortéiert den Dësch an opsteigend Uerdnung no der Datumkolonne mam Kommando Sortéieren opsteigend an der Dropdown-Lëscht vum Filter am Tabellkopf.
2. E bësse méi spéit, fir de lafenden Total ze berechnen, brauche mir eng Hëllefskolonn mat der Ordinal Zeilnummer. Loosst eis et mam Kommando addéieren Füügt Kolonn - Index Kolonn - Vun 1 (Füügt Kolonn - Index Kolonn - Vun 1).
3. Och, fir de lafenden Total ze berechnen, brauche mir eng Referenz op d'Kolonn Verkaaft, wou eis zesummegefaasst Date leien. An Power Query ginn Kolonnen och Lëschte (Lëscht) genannt a fir e Link dorop ze kréien, klickt op de Kolonnheader a wielt de Kommando Detailer (Detail weisen). Den Ausdrock, dee mir brauchen, erschéngt an der Formelbar, besteet aus dem Numm vum virege Schrëtt #"Index dobäigesat", vu wou mir den Dësch an de Kolonnnumm huelen [Verkaf] aus dëser Tabell a véiereckege Klammeren:
Kopéiert dësen Ausdrock op de Clipboard fir weider ze benotzen.
4. Läschen onnéideg méi leschte Schrëtt Verkaaft a füügt amplaz eng berechent Kolonn derbäi fir de lafenden Total mam Kommando ze berechnen Eng Kolonn derbäisetzen - Benotzerdefinéiert Kolonn (Füügt Kolonn - Benotzerdefinéiert Kolonn). D'Formel déi mir brauchen wäert esou ausgesinn:
Hei d'Funktioun Lëscht.Gamme hëlt d'Original Lëscht (Kolonn [Verkaf]) an extrahéiert Elementer dovun, ab der éischter (an der Formel ass dat 0, well d'Nummeréierung an der Power Query vun Null ufänkt). D'Zuel vun den Elementer fir ze recuperéieren ass d'Zeilnummer déi mir aus der Kolonn huelen [Index]. Also dës Funktioun fir déi éischt Zeil gëtt nëmmen eng éischt Zell vun der Kolonn zréck Verkaaft. Fir déi zweet Linn - schonn déi éischt zwou Zellen, fir déi drëtt - déi éischt dräi, etc.
Gutt, dann d'Funktioun Lëscht.Zomm summéiert déi extrahéiert Wäerter a mir kréien an all Zeil d'Zomm vun alle fréiere Elementer, dh kumulative Gesamt:
Et bleift d'Index Kolonn ze läschen, déi mir net méi brauchen an d'Resultater zréck op Excel eropluede mam Home - Close & Load to command.
De Problem ass geléist.
Fast a Furious
Prinzipiell konnt dëst gestoppt ginn, awer et gëtt e klenge Moucher an der Sallef - d'Ufro, déi mir erstallt hunn, funktionnéiert mat der Geschwindegkeet vun enger Schildkröt. Zum Beispill, op mengem net de schwaachste PC, gëtt en Dësch vun nëmmen 2000 Reihen a 17 Sekonnen veraarbecht. Wat wann et méi Daten gëtt?
Fir ze beschleunegen, kënnt Dir Puffer benotze mat der spezieller List.Buffer Funktioun, déi d'Lëscht (Lëscht) déi et als Argument gëtt an de RAM lued, wat den Zougang dozou an Zukunft staark beschleunegt. An eisem Fall ass et Sënn fir d'Lëscht #"Added Index"[Sold] ze bufferen, op déi Power Query Zougang huet wann Dir de lafenden Total an all Zeil vun eiser 2000-Zeilen Tabelle berechnen.
Fir dëst ze maachen, am Power Query Editor op der Haaptreiter, klickt op de Advanced Editor Knäppchen (Home - Advanced Editor) fir de Quellcode vun eiser Ufro opzemaachen an der M Sprooch agebaut an Power Query:
An dann eng Zeil mat enger Variabel do Meng Lëscht, de Wäert vun deem gëtt vun der Pufferfunktioun zréckginn, an am nächste Schrëtt ersetzen mir den Uruff op d'Lëscht mat dëser Variabel:
Nodeems Dir dës Ännerungen gemaach hutt, wäert eis Ufro wesentlech méi séier ginn a wäert mat enger 2000-Zeilen Dësch an nëmmen 0.3 Sekonnen eens ginn!
Eng aner Saach, richteg? 🙂
- Pareto-Diagramm (80/20) a wéi een se an Excel opbaut
- Schlësselwuert Sich am Text a Query Puffer an Power Query