Excelissä on jo funktioita, kuten SUMIF ja SUMIFS, tietojen summaamiseen ryhmittäin. Kuitenkin; ne eivät voi toimia, jos sinulla on päivämäärä-aika-arvoja yhdistettynä. Tässä artikkelissa näytetään, miten Excelissä summataan päivämäärän mukaan ilman aikaa käyttämällä SUMPRODUCT- ja INT-funktioita.
Syntaksi
=SUMPRODUCT( arvojen alue * ( INT( päivämäärän ja ajan alue ) = päivämäärän solu ) ))
Vaiheet
- Aloita =SUMPRODUCT( -funktiolla
- Tyyppi Valitse alue, joka sisältää arvoja $C$3:$C$16
- Käytä tähtimerkkiä tuoteryhmän arvojen kanssa ehtoalue ja avaa sulku *(
- Jatka INT($B$3:$B$16) poistaaksesi aikaosan
- Syötä ehto yhtäläisyysmerkillä ja kriteerialueella =E3
- Tyyppi )) sulkeaksesi funktion ja viimeistelläksesi kaavan
Miten
Excel säilyttää päiväys- ja aika-arvot numeroina. Excel olettaa, että historia alkaa 1.1.1900 ja hyväksyy tämän päivämäärän arvoksi 1. Kun kokonaisluvut edustavat päiviä, desimaaliluku edustaa aikaa. Esimerkiksi; 1.1.2018 on yhtä kuin 43101, 12:00 on yhtä kuin 0,5.
Voimme käyttää INT-funktiota päästäksemme eroon aika-arvoista tai numeroiden desimaaliosasta. INT-funktio palauttaa yksinkertaisesti kokonaislukuosan numeroarvosta. SUMPRODUCT-funktion kyky käsitellä matriiseja ilman matriisikaavoja tarjoaa edun SUMIF- ja SUMIFS-funktioiden rajoituksiin nähden. SUMPRODUCT-funktio voi arvioida ja palauttaa matriiseja alue – arvo -ehdoista. Ehdolliset yhtälöt palauttavat kuitenkin Boolen arvoja. Ne on siis muunnettava luvuiksi, joilla on merkitystä SUMPRODUCT-funktiolle. Onneksi produktio-operaattori (*) tekee tämän.
Kertomalla arvomääräkkeen ehtomääräkkeellä saamme tarpeidemme mukaan muodostetut arvojoukon arvot.
$C$3:$C$16*(INT($B$3:$B$16)=E3) palauttaa {1;2;3;4;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
Loppujen lopuksi SUMPRODUCT palauttaa tulokset summana päivämäärän mukaan excelissä, joka on se, mitä etsimme.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) palauttaa 10 päivämäärälle 7/11/2018.
Vaihtoehtoinen vaihtoehto
Vaihtoehtoinen tapa on käyttää PivotTable-taulukkoa, joka voi antaa enemmän vaihtoehtoja kuin arvojen yhteenlasku. Löydät yksityiskohtaiset tiedot artikkelista Kuinka ryhmitellä päiviä ilman aikaa PivotTaulukon avulla.
Katso myös aiheeseen liittyvät artikkelit Kuinka laskea painotettu keskiarvo SUMPRODUCT-operaattorilla, Kuinka summataan 2d vaihteluväliä SUMPRODUCT-operaattorilla ja Kuinka summataan arvot OR-operaattorilla käyttäen SUMPRODUCT-operaattoria useiden kriteerien kanssa.