Az Excel már rendelkezik olyan funkciókkal, mint a SUMIF és a SUMIFS az adatok csoportok szerinti összegzésére. Azonban; ezek nem tudnak működni, ha dátum-idő értékeket kombinálunk. Ez a cikk bemutatja, hogyan lehet dátum szerint összegezni az Excelben idő nélkül a SUMPRODUCT és az INT függvények segítségével.
Szintaktika
=SUMPRODUCT( értéktartomány * ( INT( dátum-idő tartomány ) = dátum cella ) ))
Lépések
- Az =SUMPRODUCT( függvénnyel kezdjük
- Típus kiválasztása az értékeket tartalmazó tartomány $C$3:$C$16
- Csillagot használjon a tartomány értékeinek a feltétel tartományával való termékenyítéséhez, és nyisson zárójelet *(
- Folytassa az INT($B$3:$B$16) az idő rész eltávolításához
- Feltétel megadása egyenlőségjellel és kritériumtartomány =E3
- Típus )) a függvény lezárásához és a képlet befejezéséhez
Hogyan
Az Excel a dátum- és időértékeket számként tartja meg. Az Excel feltételezi, hogy az előzmények 1900. január 1-jétől kezdődnek, és ezt a dátumot 1-nek fogadja el. Míg az egész számok a napokat, addig a tizedes számok az időt jelölik. Például; 2018.1.1. egyenlő 43101, 12:00 egyenlő 0,5.
Az INT függvénnyel megszabadulhatunk az időértékektől vagy a számok tizedes részétől. Az INT függvény egyszerűen visszaadja az egész számrészt egy számértékből. A mi esetünkben;
INT($B$3:$B$16) {43292;43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43294;43294;43294}
Hogy azonban; ahhoz, hogy az INT függvény egy tömbön is működjön, a SUMPRODUCT függvényt kell használnunk. A SUMPRODUCT függvénynek az a képessége, hogy tömböket tömbképletek használata nélkül képes kezelni, előnyt jelent a SUMIF és SUMIFS függvények korlátaival szemben. A SUMPRODUCT ki tudja értékelni és vissza tudja adni a tartomány – érték feltételekből származó tömböket. Ezt a funkciót használjuk a tartomány – feltétel párok feloldására.
(INT($B$3:$B$16)=E3) {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
A SUMPRODUCT függvény továbbá összegzi az argumentumtömbjében lévő értékeket, ha csak egy argumentum van. A feltételes egyenletek azonban Boolean értékeket adnak vissza. Így azokat a SUMPRODUCT függvény számára értelmezhető számokká kell alakítani. Szerencsére a szorzatoperátor (*) ezt megteszi.
Egy értéktömb és egy feltételes tömb szorzásával megkapjuk az igényeinknek megfelelően előállított tömbértékeket.
$C$3:$C$16*(INT($B$3:$B$16)=E3) visszaadja {1;2;3;4;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
A SUMPRODUCT végül az Excelben dátum szerinti összegként adja vissza az eredményeket, amit keresünk.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3))) 10-et ad vissza a 2018.11.7-i dátumra.
Alternatív lehetőség
Egy másik lehetőség a PivotTable használata, amely az értékek összegzésénél több lehetőséget adhat. Részletes információkat a Hogyan csoportosíthat napokat idő nélkül PivotTáblával.
Még lásd a kapcsolódó cikkeket Hogyan számolhat súlyozott átlagot SUMPRODUCT segítségével, Hogyan összegezhet 2d tartományokat SUMPRODUCT segítségével, és Hogyan összegezhet értékeket OR operátorral SUMPRODUCT segítségével több kritériummal.