Excel har allerede funktioner som SUMIF og SUMIFS til at summere data efter grupper. Men; de kan ikke fungere, hvis du har dato-tids-værdier kombineret. Denne artikel viser, hvordan du summerer efter dato i Excel uden tid ved hjælp af SUMPRODUCT- og INT-funktionerne.
Syntaks
=SUMPRODUCT( interval af værdier * ( INT( interval af dato-tid ) = celle af dato ) )
Strækninger
- Start med =SUMPRODUCT( funktion
- Type af Vælg det område, der indeholder værdier $C$3:$C$16
- Brug asterisk til at produkt række værdier med betingelse rækkevidde og åbne en parentes *(
- Fortsæt med INT($B$3:$B$16) for at fjerne tidsdelen
- Indtast betingelse med et lighedstegn og kriterieområde =E3
- Type )) for at lukke funktionen og afslutte formlen
Hvordan
Excel beholder dato- og tidsværdier som tal. Excel antager, at historikken starter fra den 1. januar 1900 og accepterer denne dato som 1. Mens hele tal repræsenterer dage, repræsenterer decimaltal tid. For eksempel; 1/1/2018 er lig med 43101, 12:00 er lig med 0,5.
Vi kan bruge INT-funktionen til at slippe af med tidsværdierne eller decimaldelen af tal. INT-funktionen returnerer simpelthen den hele taldel fra en numerisk værdi. I vores tilfælde;
INT($B$3:$B$16) returnerer {43292;43292;43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Hvorimod; for at få INT-funktionen til at fungere på et array, skal vi bruge funktionen SUMPRODUCT. SUMPRODUCT-funktionens evne til at håndtere arrays uden brug af array-formler giver en fordel i forhold til begrænsningerne i SUMIF- og SUMIFS-funktionerne. SUMPRODUCT kan evaluere og returnere arrays ud fra range – value betingelser. Vi bruger denne funktionalitet til at opløse kriterier interval – kriteriepar.
(INT($B$3:$B$16)=E3) returnerer {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSK;FALSK;FALSK;FALSK;FALSK;FALSK;FALSK;FALSK;FALSK;FALSK;FALSK;FALSK;FALSK;FALSK}
Dertil kommer, at SUMPRODUCT-funktionen summerer værdierne i sit argumentarray, hvis der kun er ét argument. Betingede ligninger returnerer imidlertid boolske værdier. De skal derfor konverteres til tal, som er meningsfulde for SUMPRODUCT-funktionen. Heldigvis gør produktoperatoren (*) dette.
Gennem at multiplicere et værdiarray med et betingelsesarray får vi arrayværdierne genereret i overensstemmelse med vores behov.
$C$3:$C$16*(INT($B$3:$B$16)=E3) returnerer {1;2;3;4;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
SumPRODUCT returnerer endelig resultaterne som sum efter dato i Excel, hvilket er det, vi er på udkig efter.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3))) returnerer 10 for datoen 7/11/2018.
Alternativ mulighed
En alternativ måde er at bruge PivotTable, der kan give dig flere muligheder end at opsummere værdier. Du kan finde detaljerede oplysninger i Sådan grupperes dage uden tid ved hjælp af Pivottabel.
Se også relaterede artikler om, hvordan du beregner vægtet gennemsnit med SUMPRODUCT, hvordan du summerer 2d intervaller med SUMPRODUCT, og hvordan du summerer værdier med OR-operatoren ved hjælp af SUMPRODUCT med flere kriterier.