Generisk formel
=SUM.HVISER(Beløb;Datoer;">="&A1;Datoer;"<"&A1+7)
Resumé
For at summere værdier efter uge, kan du bruge en formel baseret på funktionen SUM.HVISER. I det viste eksempel er formlen i E2:
=SUM.HVISER(Data[Beløb];Data[Datoer];">="&D2;Data[Datoer];"<"&D2+7)
hvor Data er en Excel-tabel i området A1:B20, og datoerne i D2:D5 er mandage.
Forklaring
I dette eksempel er målet at summere beløbene i kolonne B efter uge ved at bruge datoerne i intervallet D2:D5, som alle er mandage. Alle data er i en Excel-tabel med navnet Data i området A1:B20. Dette problem kan løses med funktionen SUM.HVISER. I den nuværende version af Excel (365), som understøtter dynamiske matrixformler, kan du også oprette en alt-i-en-formel, der bygger hele oversigtstabellen i ét trin. Begge tilgange er forklaret i detaljer nedenfor.
Løsningen SUM.HVISER
Funktionen SUM.HVISER kan summere værdier i et interval med betingelse baseret på flere kriterier. Mønstret for funktionen SUM.HVISER ser således ud:
=SUM.HVISER(sumområde; kriterieområde 1;kriterie1,…)
Bemærk sumområde kommer først, efterfulgt af interval/kriterie-par. Hvert område/kriteriepar af argumenter repræsenterer en anden betingelse.
I dette tilfælde skal vi konfigurere SUM.HVISER til at summere værdier efter uge ved hjælp af to kriterier: et til at matche datoer større end eller lig med den første dag i ugen, et til at matche datoer mindre end den første dag i den næste uge. Vi starter med sumområde og den første betingelse:
=SUM.HVISER(Data[Beløb];Data[Datoer];">="&D2
Bemærk: fordi vi bruger en Excel-tabel til at opbevare dataene, får vi automatisk de strukturerede referencer, der ses ovenfor.
Sumområde er Data[Beløb], kriterierområde1 er Data[Dato], og kriterier1 er “>=”&D2. Bemærk, at vi er nødt til at sammenkæde større end eller lig med operatoren (>=) til referencen D2. Dette skyldes, at SUM.HVISER er i en gruppe på otte funktioner, der opdeler formelkriterier i to dele.
Dernæst skal vi tilføje et andet interval/kriteriepar af argumenter til måldatoer til og med den sidste dag i ugen:
=SUM.HVISER(Data[Beløb];Data[Datoer];">="&D2;Data[Datoer];"<"&D2+7)
Her er kriterieområde2 igen Data[Dato], og kriterie2 er “<“&D2+7. Grundlæggende tilføjer vi 7 dage til datoen i D2 og bruger mindre end-operatoren (<) til at fange alle dage før den næste uge. Igen skal vi bruge sammenkædning for at forbinde operatoren med cellereferencen. Grunden til, at vi kan tilføje 7 dage til D2 med simpel tilføjelse, er fordi Excel-datoer kun er serietal.
Formlen er nu færdig. Da formlen er kopieret ned i kolonne E, vil SUM.HVISER-formlen generere en sum for hver uge ved hjælp af datoen i kolonne D.
Datoer uge
Datoerne i kolonne D er mandage. Den første dato i D2 (04-08-22) er hardkodet, og resten af datoerne er beregnet med en simpel formel:
=D2+7
Ved hver ny række vender formlen tilbage næste mandag i kalenderen.
Dynamisk array løsning
I den nuværende version af Excel, som understøtter dynamiske matrixformler, er det muligt at oprette en enkelt alt-i-en-formel, der opbygger hele oversigtstabellen, inklusive overskrifter, i ét trin som dette:
=LET(
Datoer;Data[Dato];
Beløber;Data[Beløb];
uger;Datoer-UGEDAG(Datoer;3);
uuger;ENTYDIGE(uger);
totaler;BYROW(uuger;LAMBDA(r;SUM((uger=r)*Beløber)));
VSTAK({"Uge startende med"\"Samlet"};HSTAK(uuger;totaler)))
LET-funktionen bruges til at tildele værdier til fem variable: Datoer, Beløber, uger, uuger og totaler. Først tildeler vi værdier til Datoer og Beløber som dette:
=LET(
Datoer;Data[Dato];
Beløber;Data[Beløb];
Teknisk set kunne vi bare bruge referencerne Data[dato] og Data[Beløb] i hele formlen, men ved at definere disse variabler for disse på forsiden, holdes alle regnearksreferencer øverst i koden, hvor de nemt kan ændres. Med andre ord, ved blot at redigere disse to referencer, kan du nemt tilpasse formlen til at arbejde med et andet datasæt.
Dernæst oprettes værdien for uger på denne måde:
uger;Datoer-UGEDAG(Datoer;3);
Her bruges funktionen UGEDAG til at beregne “mandag i ugen” for hver dato i Data[Dato]. Fordi tabellen indeholder 12 rækker med data, er resultatet et array med 19 datoer som dette:
{44795;44774;44788;44781;44774;44788;44774;44795;44781;44781;44781;44788;44788;44795;44795;44795;44795;44802;44788}
Excel-datoer er bare serietal, så det er de rå tal, der svarer til de datoer, der ses i A2:A20, som alle er mandage.
I den næste linje definerer vi uuger (unikke uger) med funktionen ENTYDIGE sådan her:
uuger;ENTYDIGE(uger)
Vi gør dette, fordi vi kun ønsker en række om ugen i vores endelige oversigtstabel. Funktionen ENTYDIGE returnerer de 6 datoer, der ses i E5:E10, som alle er mandage:
{44795;44774;44788;44781;44802}
Bemærk: du kan sortere resultatet fra ENTYDIGE med funktion SORTER for at sikre, at ugerne er i den rigtige rækkefølge, hvis det er nødvendigt.
Vi er nu klar til at beregne de samlede beløb for hver uge. Vi gør dette med funktionen BYROW, som genererer summerne og tildeler resultatet til variablen totaler sådan her:
totaler;BYROW(uuger;LAMBDA(r;SUM((uger=r)*Beløber)))
BYROW løber gennem værdierne uuger række for række. Ved hver række anvender den denne beregning med LAMBDA-funktionen:
LAMBDA(r;SUM((uger=r)*Beløber)))
Værdien af r er datoen i den “aktuelle” række af uuger. Inde i SUM-funktionen sammenlignes r’et med uger. Da uger indeholder 19 datoer for alle 19 rækker, er resultatet en matrix med 19 SAND og FALSK resultater. SAND- og FALSK-værdierne ganges med Beløber. Denne matematiske operation konverterer automatisk værdierne SAND og FALSK til 1’er og 0’er, og nullerne “ophæver” effektivt værdierne i uger, der ikke er lig med r. Funktionen SUM summerer derefter det resulterende array. Når BYROW er færdig, har vi et array med 5 ugentlige summer som dette:
{2480;1060;3370;2360;630} // totaler
Dette er den værdi, der er tildelt variablens totaler.
Til sidst bruges funktionerne HSTAK og VSTAK til at samle en komplet tabel:
VSTAK({"Uge startende med"\"Samlet"};HSTAK(uuger;totaler)))
Øverst i tabellen opretter matrixkonstanten { Uge startende med”\”Samlet”} en overskriftsrække. Funktionen HSTAK kombinerer uuger og totaler vandret, og funktionen VSTAK kombinerer overskriftsrækken og resultatet fra HSTAK lodret for at lave den endelige tabel. Det endelige resultat udfyldes i flere celler i regnearket.