Excel-tabeller er en af de bedste funktioner i Excel. Tabeller blev oprindeligt introduceret som en del af Excel 2003 under navnet Lister, men blev udvidet og omdøbt til tabeller i Excel 2007. Selvom det er muligt at bruge standard cellehenvisningen i en tabel, har de deres egen referencestil. Ved hjælp af denne nye henvisningsstil er der ingen enkel måde at skabe en pålideligt løbende total og opretholde fordelen ved at bruge en tabel.
Men med lidt kreativ tænkning kan vi oprette noget, der giver nøjagtigt det, vi har brug for.
Eksemplet
Alle scenarierne nedenfor bruger den samme tabel. Målet er at oprette en løbende total i kolonnen Balance, som vedligeholder brugen af en struktureret henvisning og stadig fungerer, når rækker slettes eller sorteres.
Normale cellehenvisninger
Vi kunne bruge normale cellehenvisninger i enten A1 eller R1C1-stilen. Da formålet med en tabel er at bruge den samme formel i hver række i kolonnen, er det imidlertid ikke muligt bare at bruge + (plus) og – (minus) i formlen.
Formlen i celle E2 er:
=[@Ind]-[@Ud]+E1
Resultatet af formlen er #VÆRDI!, fordi celle E1 er en tekstværdi, som ikke kan lægges til et tal.
Ved at bruge SUM funktionen med et komma (kendt som Forenings Operator), beregnes tekstværdien som et nul, hvilket undgår #VÆRDI! fejl.
Formlen i celle E2 er:
=SUM([@Ind];-[@Ud];E1)
Resultatet af denne formel er en løbende total i hver række i kolonnen Balance.
I stedet for SUM funktionen kan du bruge funktionen TAL. TAL vil være nul, hvis cellehenvisningen ikke er et tal, ellers viser tallet. Hvis celle E2 indeholdte følgende formel, ville det oprette en løbende total.
=[@Ind]-[@Ud]+TAL(E1)
Disse metoder kommer til kort, når en række slettes. Skærmbillede nedenfor viser resultatet, efter at række 4 er blevet slettet.
Som du kan se ud fra #REFERENCE! fejlen, så fungerer denne metode ikke, når rækker slettes. Hvilket ikke opfylder de krav, vi ønskede. Men rolig, resten af indlægget indeholder løsninger, som gør.
Funktionen FORSKYDNING
Funktionen forskydning bruges til at henvise til et specifikt område eller celle ved at bruge en anden celle som et startpunkt. Den kan have op til 5 argumenter.
- Reference: er den celle, hvorfra du vil basere forskydningen.
- Rækker: er antallet af rækker, som du ønsker, at referencecellen skal flytte forbi.
- Kolonner: er antallet af kolonner, som du ønsker, at referencecellen skal flytte forbi.
- Højde (valgfrit): er antallet af rækker, du vil have området skal være.
- Bredde (valgfrit): er antallet af kolonner, du vil have området skal være.
Som et simpelt eksempel, se på formlen herunder:
=FORSKYDNING(E2, -1,0,1,1)
Formlen ovenfor ville starte ved celle E2, bevæge sig op med -1, bevæge sig højre ved 0 og skabe et område, der er 1 celle højt og 1 celle bredt. Resultatet heraf ville være Cell E1.
Vi kan inkludere denne metode i vores løbende total formel.
=SUM([@Ind];-[@Ud];FORSKYDNING([@Balance];-1;0))
Resultatet af denne formel er et løbende total i hver række i kolonnen Balance. Selv hvis en række slettes, vil beregningen fortsat være den løbende total.
Det lyder som en rimelig mulighed, ikke? Du ved dog muligvis, at FORSKYDNING er en flygtig funktion, hvilket betyder, at den vil beregne resultatet ved hver celleændring, hvilket gør beregninger ineffektive under visse omstændigheder.
Relativ navngivet område
En mulighed, der ikke involverer en flygtig formel, er at bruge et relativ navngivet område. I dette scenarie betyder det at der oprettes et navngivet område, der altid refererer til cellen ovenfor.
Klik på Cell E2, vælg derefter på Formler -> Navnestyring.
Klik på Nyt i dialogboksen Navnestyring.
Sørg for, at feltet Refererer til indeholder celleadressen til cellen over den valgte celle (f.eks. er E1 cellen ovenfor, når E2 er den valgte celle). Vær opmærksom på, at cellehenvisningen ikke indeholder $ (dollartegn) symboler eller indeholder et specifikt regnearksnavn. Dette gør det muligt at bruge det relative navn, der bruges på ethvert regneark og enhver celle i projektmappen.
Skærmbillede ovenfor viser et relativ navngivet interval, kaldet CelleOvenfor, der refererer til cellen over den valgte celle.
Uanset hvor navnet CelleOvenfor bruges, vil det altid henvise til cellen ovenfor (det er ikke kun et smart navn). Vi kan inkludere dette i vores løbende total formel, mens vi også opretholder den strukturerede henvisning.
Formlen i celle E2 er:
=SUM([@Ind];-[@Ud];CelleOvenfor)
Resultatet af denne formel er en ikke-flygtigt løbende total i hver række i kolonnen Balance. Selv hvis en række slettes, vil beregningen fortsat være den løbende total.
Funktionen INDEKS
Der er mere end en måde at oprette en løbende total på. Indtil videre har alle indstillinger gået på at tilføje det løbende til den foregående løbende total. Opsummering af alle de tidligere bevægelser giver det samme resultat.
Ved at bruge funktionen INDEKS er det muligt at referere til en hvilken som helst celle i en kolonne.
Formlen nedenfor refererer til den første celle i kolonnen [Ind].
=INDEKS([Ind];1)
Placering af et kolon mellem to cellehenvisninger (kendt som Område operator) skaber et interval.
Formlen i celle E2 er (indeholder både område (:) og forenings (;) operatorer).
=SUM(INDEKS([Ind];1):[@Ind];-INDEKS([Ud];1):[@Ud])
Resultatet af denne formel er en ikke-flygtigt løbende total i hver række i kolonnen Balance. Hvilket, som jeg er sikker på, at du har gættet, fortsætter med at virke, selvom en række slettes.
Funktionen SAMLING
Da tabellerne inkluderer et filter, ville det være dejligt at inkludere en løbende total, der kun inkluderede de synlige celler. Ingen af de hidtidige overvejelser ville være egnede til dette. Så lad os byde funktionen SAMLING velkommen. Den blev introduceret i Excel 2010 og kan indstilles til at ignorere skjulte rækker.
Formlen i celle E2 er:
=SAMLING(9;5;INDEKS([Ind];1):[@Ind])-SAMLING(9;5;INDEKS([Ud];1):[@Ud])
Funktionen SAMLING er udstyret med følgende parametre:
- Funktionsnummer: 9 = SUM
- Valgmulighed: 5 = Ignorer skjulte celler
Den løbende total fungerer på samme måde som før, men med lidt ekstra. Hvis kolonnen filtreres, viser der den løbende total for de synlige celler. Det er smart ikke?
