du vil sandsynligvis støde på behovet for at køre totaler, hvis du har at gøre med nogen form for daglige data.

Forestil dig, at du sporer salg hver dag. Dine data indeholder en række for hver dato med et samlet salgsbeløb, men måske vil du vide det samlede salg for måneden hver dag. Dette er en løbende total, det er summen af alle salg op til og med de aktuelle dage salg.

i dette indlæg dækker vi flere måder at beregne en løbende total for dine daglige data. Vi undersøger, hvordan du bruger regnearksformler, pivottabeller, pivot pivot med automatisk og strømforespørgsel.

Vi undersøger også, hvad der sker med den løbende totalberegning, når du indsætter eller sletter rækker med data, og hvordan du opdaterer resultaterne.

Hent filen med alle eksemplerne.

løbende totaler med en simpel formel

det er muligt at oprette en grundlæggende løbende totalformel ved hjælp af operatoren+.

Vi skal dog bruge to forskellige formler for at få arbejdet gjort.

1. =C3 vil være den første formel og vil kun være i den første række af den løbende total.
2. =C4 + D3 vil være i den anden række og kan kopieres ned de resterende rækker for den løbende total.

formlen i vores første række kan ikke tilføje cellen over den til totalen, da den indeholder en tekstværdi for en kolonneoverskrift. Dette ville medføre en # værdi! fejl, der skal vises i den løbende total, da + ikke kan håndtere tekstværdier. Vi undgår dette med en anden formel i den første række, som ikke refererer til cellen ovenfor.

Hvad sker der med den løbende total, når vi indsætter eller sletter rækker i vores data?

indsættelse af en ny række vil resultere i et hul i den løbende total. For at løse dette skal vi kopiere formlen ned fra den første celle over de nyligt indsatte rækker helt ned til den sidste række.

sletning af rækker vil resultere i #REF! fejl siden sletning af en række betyder sletning af en celle, der henvises til med formlen under den. For at løse dette skal vi kopiere formlen ned fra den sidste fejlfri celle helt ned til den sidste række.

løbende totaler med en sumformel

Vi kan undgå akavet ved at bruge to forskellige formler i vores løbende total-kolonne ved at bruge SUM-funktionen i stedet for + – operatøren. Når SUM-funktionen støder på en tekstcelle, behandler den den samme som en, selvom den indeholdt en 0.

på denne måde kan vi bruge følgende formel ensartet for hver række inklusive den første række.

=SUM(C3,D2)

denne formel henviser til kolonneoverskriften, der indeholder tekst for den første række, men dette ok, da det behandles som et 0.

når vi indsætter eller sletter rækker, vil vi stadig støde på de samme problemer med tomme celler og fejl. Vi kan rette dem på samme måde som ved at køre totaler i den enkle formelmetode.

kørende totaler med et delvist fast interval

en anden mulighed med funktionen SUM er kun at henvise til Salgskolonnen og bruge en delvist fast områdereference.

Hvis vi bruger følgende formel =SUM($C$3:C3), kan vi kopiere og indsætte dette ned i området. Det refererer ikke til nogen kolonneoverskrifter, og det område, der henvises til, vokser til hver række.

desværre vil dette også have de samme problemer (og løsninger) med at indsætte eller slette rækker.

kører totaler med et relativ navngivet område

Vi kan undgå problemer med at indsætte og slette rækker fra vores data, hvis vi bruger et relativ navngivet område. Dette henviser til cellen direkte ovenfor, uanset hvor mange rækker vi indsætter eller sletter.

Dette er et trick, der involverer midlertidigt at skifte referencestilen fra A1 til R1C1. Derefter definerer et navngivet interval ved hjælp af R1C1 notation. Skift derefter referencestilen tilbage til A1.

i R1C1-referencestil henvises celler til, hvor langt væk de er fra cellen ved hjælp af referencen. For eksempel henviser =RC til cellen 2 op og 3 til højre for cellen ved hjælp af denne formel.

Vi kan bruge denne relative henvisning til at oprette et navngivet område, der altid er en celle over den henvisende celle med formlen =RC.

for at skifte referencestil skal du gå til fanen Filer og derefter vælge Indstillinger. Gå til Formelsektionen i menuen Indstillinger, og marker afkrydsningsfeltet R1C1-referenceformat, og tryk derefter på knappen OK.

nu kan vi tilføje vores navngivne rækkevidde. Gå til fanen formel på båndet, og vælg kommandoen Definer navn.

Indsæt et navn som “ovenfor” som navnet på området. Tilføj formlen =RC I refererer til input og tryk på OK-knappen.

Vi kan nu skifte tilbage til standardreferencestilen. Gå til fanen Filer > indstillinger Formelsektionen > fjern markeringen i feltet R1C1 referencestil > tryk derefter på OK-knappen.

nu kan vi bruge formlen =SUM (ovenfor) i vores løbende total kolonne.

det navngivne område ovenfor henviser altid til cellen direkte ovenfor. Når vi indsætter eller sletter rækker, justeres det relative navngivne interval i overensstemmelse hermed, og der er ikke behov for handling.faktisk, hvis vi placerer vores data i en tabel, udfyldes formlen automatisk for eventuelle nye rækker, da formlen er ensartet for hele kolonnen. Ingen handling er nødvendig for at kopiere nogen formler.

kører totaler med en pivottabel

ivottabeller er super nyttige til opsummering af enhver type data. Der er mere ved dem end blot at tilføje, tælle og finde gennemsnit. Der er mange andre typer beregninger indbygget, og der er faktisk en løbende total beregning!

først skal vi indsætte en pivottabel baseret på dataene. Vælg en celle inde i dataene, og gå til fanen Indsæt, og vælg kommandoen pivottabel. Gå derefter gennem vinduet Opret pivottabel for at vælge, hvor du vil have pivottabellen, enten i et nyt regneark eller et sted i en eksisterende.

Tilføj datofeltet i Rækkeområdet i pivottabellen, og tilføj derefter Salgsfeltet i værdiområdet i pivottabellen. Tilføj nu en anden forekomst af Salgsfeltet i Rækkeområdet.

vi skulle nu have to identiske Salgsfelter, hvor et af dem er mærket Sum of Sales2. Vi kan omdøbe denne etiket når som helst ved blot at skrive over det med noget som at køre Total.

Højreklik på en af værdierne i feltet Sum of Sales2, og vælg Vis værdi som vælg derefter Running Total in.

Vi vil vise den løbende total efter dato, så i det næste vindue skal vi vælge dato som Basisfelt.

det er det, vi har nu en ny beregning, der viser den løbende total af vores salg inde i pivottabellen.

Hvad sker der, hvis vi tilføjer eller sletter en række i vores kildedata, hvordan påvirker dette den løbende total? Pivottabelberegningerne er dynamiske og vil tage nye data i betragtning i dens løbende samlede beregning, vi bliver bare nødt til at opdatere pivottabellen.

Højreklik hvor som helst inde i pivottabellen, og vælg Opdater i menuen.

kører totaler med effekt Pivot og DAK måler

de første par trin til dette er nøjagtigt de samme ved hjælp af en almindelig pivottabel.

vælg en celle inde i dataene, og gå til fanen Indsæt, og vælg kommandoen pivottabel.

Når du kommer til menuen Opret pivottabel, skal du markere afkrydsningsfeltet Føj disse data til Datamodellen for at føje dataene til datamodellen og aktivere dem til brug med strømpivot.

Placer datofeltet i Rækkeområdet og Salgsfeltet i værdiområdet i pivottabellen.

med magt pivot, bliver vi nødt til at oprette eventuelle ekstra beregninger, vi ønsker at bruge sproget. Højreklik på tabelnavnet i vinduet Pivottabelfelter, og vælg derefter Tilføj mål for at oprette en ny beregning. Bemærk, Dette er kun tilgængeligt med datamodellen.

=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

nu kan vi oprette vores nye løbende samlede mål.

1. i Målevinduet skal vi tilføje et Målenavn. I dette tilfælde kan vi navngive den nye foranstaltning som løbende Total.
2. Vi skal også tilføje ovenstående formel i Formelboksen.
3. det seje ved magtpivot er evnen til at tildele et talformat til et mål. Vi kan vælge valutaformatet for vores foranstaltning. Når vi bruger denne foranstaltning i en pivottabel, anvendes formatet automatisk.

tryk på OK-knappen, og den nye foranstaltning oprettes.

der vises et nyt felt i vinduet Pivottabelfelter. Det har et lille valutaikon til venstre for at angive, at det er et mål og ikke et almindeligt felt i dataene.

Vi kan bruge dette nye felt ligesom ethvert andet felt og trække det ind i værdiområdet for at tilføje vores løbende samlede beregning i pivottabellen.

Hvad sker der med den løbende total, når vi tilføjer eller fjerner data fra kildetabellen? Ligesom en almindelig pivottabel skal vi blot højreklikke på pivottabellen og vælge Opdater for at opdatere beregningen.

løbende totaler med en Strømforespørgsel

Vi kan også tilføje løbende totaler til vores data ved hjælp af strømforespørgsel.

først skal vi importere tabellen til strømforespørgsel. Vælg datatabellen, og gå til fanen Data, og vælg indstillingen fra tabel/område. Dette åbner strømforespørgseleditoren.

næste kan vi sortere vores data efter dato. Dette er et valgfrit trin, vi kan tilføje, så hvis vi ændrer rækkefølgen af vores kildedata, vises den løbende total stadig efter dato.

Klik på filterskiftet i kolonneoverskriften dato og vælg Sorter stigende fra indstillingerne.

Vi skal tilføje en indekskolonne. Dette vil blive brugt i den løbende samlede beregning senere. Gå til fanen Tilføj kolonne, og klik på den lille pil ved siden af Indekskolonnen for at indsætte et indeks, der starter ved 1 i første række.

Vi skal tilføje en ny kolonne til vores forespørgsel for at beregne den løbende total. Gå til fanen Tilføj kolonne, og vælg kommandoen brugerdefineret kolonne.

Vi kan navngive kolonnen som løbende Total og tilføje følgende formel.

listen.Områdefunktion opretter en liste over værdier fra Salgskolonnen, der starter ved 1. række (0.element), der spænder over et antal rækker baseret på værdien i indekskolonnen.

listen.Sum funktion tilføjer derefter denne liste over værdier, som er vores løbende total.

Vi har ikke længere brug for indekskolonnen, den har tjent sit formål, og vi kan fjerne den. Højreklik på kolonneoverskriften, og vælg Fjern fra indstillingerne.

Vi har vores løbende total og er færdige med forespørgselseditoren. Vi kan lukke forespørgslen og indlæse resultaterne i et nyt regneark. Gå til fanen Hjem i forespørgselseditoren, og tryk på knappen Luk & Indlæs.

Hvad sker der med den løbende total, når vi tilføjer eller fjerner rækker fra vores kildedata? Vi bliver nødt til at opdatere strømforespørgselsudgangstabellen for at opdatere den løbende total med ændringerne. Højreklik et vilkårligt sted på bordet, og vælg Opdater for at opdatere tabellen.

med det valgfrie sorteringstrin ovenfor, hvis vi tilføjer datoer, der ikke er i orden til kildedataene, sorteres strømforespørgslen efter dato og returnerer den korrekte rækkefølge efter dato for den løbende total.

konklusioner

der er mange forskellige muligheder for beregning af løbende totaler.

Vi har undersøgt muligheder, herunder formler i regnearket, pivottabeller, pivotformler og strømforespørgsel. Nogle tilbyder en mere robust løsning, når du tilføjer eller fjerner rækker fra dataene, andre metoder giver en lettere implementering.

enkle formler i regnearket er nemme at konfigurere, men håndterer ikke let at indsætte eller slette nye rækker med data. Andre løsninger som pivottabeller, STRØMFORESPØRGSEL og strømforespørgsel er mere robuste og håndterer let at indsætte eller slette rækker med data, men er sværere at konfigurere.

det er godt at være opmærksom på fordele og ulemper ved hver metode og vælge den bedst egnede. Hvis du ikke indsætter eller sletter nye data, kan regnearksformler være vejen at gå.