Hogyan összegezzünk dátum szerint az Excelben idő nélkül

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

  1. Az =SUMPRODUCT( függvénnyel kezdjük
  2. Típus kiválasztása az értékeket tartalmazó tartomány $C$3:$C$16
  3. 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 *(
  4. Folytassa az INT($B$3:$B$16) az idő rész eltávolításához
  5. Feltétel megadása egyenlőségjellel és kritériumtartomány =E3
  6. 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.

Vélemény, hozzászólás?

Az e-mail-címet nem tesszük közzé.