Dynamisk SUM
I et forholdsvis stort regneark, registreres nogle ugentlige tal for et eller andet. I første række står ugenumre. I anden og følgende rækker står så tallene. Kolonnerne B til BB kan rumme tal. Regnearket kan omfatte et ubegrænset antal rækker uden at ændre i formlen. I A-kolonnen fra række 2 står navne eller numre på det eller de, der skal registreres. Indholdet af denne kolonne SKAL være entydigt. Se nedenstående eksempel (Vist i uddrag).
Vi vil nu gerne kunne uddrage en "dynamisk sum". Det vil sige, at vi skal kunne angive identifikationen fra kolonne A, samt et ugenummer for begyndelsen af summen og et for slutningen af summen. Vi vil fx gerne have summen af "4 Mette" fra uge 3 til uge 6. Det kræver tre celler til indtastning af Navn, Startuge og Slutuge. I dette tilfælde bruger jeg henholdsvis cellerne BD1 (navn), BD2 (startdato) og BD3 (slutdato). Så mangler bare formlen, Her er det vigtigt at huske, at HELE formlen skal skrives på én linje, uanset hvordan den vises her:
=SUM(INDIREKTE(ADRESSE(SAMMENLIGN(BD1;A:A;0);SAMMENLIGN(BD2;B1:BB1;0)+1)):INDIREKTE(ADRESSE(SAMMENLIGN(BD1;A:A;0);SAMMENLIGN(BD3;B1:BB1;0)+1)))
Forklaring:
BD1, BD2 og BD3 er indtastningscellerne. Området B1:BB1 indeholde ugenumrene 1 til 53 og området A:A indeholder de entydige navne, numre eller lignende, på det, man ønsker summeret.
SUM-funktionen beregner summen af de ønskede. Denne funktion kræver et område, fx B7:AA7 for at virke, altså =SUM(B7:AA7). I dette tilfælde vil vi dog gerne gøre dette område dynamisk, så samme formel kan anvendes til flere forskellige summeringer. Det kræver at vi kan "beregne" de to adresser i stedet for at taste dem. Funktionen ADRESSE() finder netop adressen på en given celle. Den kræver at man kender række- og kolonnenummer. Fx vil =ADRESSE(7;5) returnere E7 (eller rettere $E$7, da funktionen returnerer absolutte adresser*).
* Ønsker man den relative adresse retur, kan man tilføje er 4 som tredje argument, altså =ADRESSE(7;5;4) i det fire netop betyder "relativ". 1 betyder absolut, 2 betyder absolut række/relativ kolonne og 3 betyder relativ række/absolut kolonne. =ADRESSE(7;5;2) vil derfor returnere E$7.
Problemet i dette tilfælde er, at vi ikke på forhånd kender hverken række eller kolonne, da disse også skal være dynamiske. Så også disse må vi beregne. Rækken skal kun beregnes en gang, da det er den samme, vi skal bruge i begge SUM's argumenter. SAMMENLIGN(BD1;A:A;0)* sammenligner den værdi, vi har tastet i BD1 (her "4 Mette" (uden anførselstegn) med A-kolonnen og returneret den række, som værdien står i, i det konkrete tilfælde række 5. Nu mangler vi så de to kolonnenumre til henholdsvis start og slut. SAMMENLIGN(BD2;B1:BB1;0) sammenligner det ugenummer, vi har indtastet i BD2 (startuge) med området B1:BB1 og returnere kolonnen det står i; SAMMENLIGN(BD2;B1:BB1;0) gør det samme for ugenummeret i BD3. SAMMENLIGN returnerer et såkaldt kolonneindeksnummer, altså nummeret på den kolonne indenfor matrixen, som det fundne står i. Det betyder, at uge 3 står i kolonne nummer tre og uge 6 står i kolonne 6. I regnearket står det imidlertid i kolonne 4 og 7, hvorfor der skal lægges 1 til, når vi skal finde adressen.
* 0 i det sidste argument betyder, at der kun skal returneres noget, hvis værdien findes eksakt i området.
ADRESSE(SAMMENLIGN(BD1;A:A;0);SAMMENLIGN(BD2;B1:BB1;0)+1) vil nu returnere $D$5 hvilket præcis er startkolonnen; den anden adresse-funktion returnerer tilsvarende $G$5, hvilket er slutkolonnen. Vi har nu de to adresser, men vi kan desværre ikke bruge dem direkte i SUM-funktionen, da denne vil opfatte dem som tekst. Det duer ikke. Ved at "indpakke" ADRESSE-funktionerne i funktionen INDREKTE() fortæller vi Excel, at adressen netop skal opfattes som en adresse. Vi er nu fremme ved en funktion, der når den "oversættes" til en almindelig formel, kunne hedde =SUM(D5:G5) og summen af disse er netop 16, som vi også kunne have beregnet manuelt. Funktionen er nu dynamisk, så hvis vi ændrer i BD1, BD2 og BD3 til henholdsvis fx "9 Poul", "4" og "9" returneres 6, som netop er summen af Pouls værdier i de pågældende uger.
NB! Sammenlign er IKKE case-sensitiv, så det er ligegyldigt om du indtaster nav net som 4 Mette eller 4 mette.