Excel are deja funcții precum SUMIF și SUMIFS pentru însumarea datelor pe grupuri. Cu toate acestea; ele nu pot funcționa dacă aveți valori de dată-timp combinate. Acest articol arată Cum să însumați după dată în Excel fără timp folosind funcțiile SUMPRODUCT și INT.
Sintaxa
=SUMPRODUCT( interval de valori * ( INT( interval de dată-timp ) = celulă de dată ) )
Etapele
- Începeți cu funcția =SUMPRODUCT(
- Tip de selectați intervalul care conține valorile $C$3:$C$16 $C$16
- Utilizați asterisc pentru a produce intervalul de valori cu intervalul de condiții și deschideți o paranteză *(
- Continuați cu INT($B$3:$B$16) pentru a elimina partea de timp
- Introduceți condiția cu semnul egal și intervalul de criterii =E3
- Type )) pentru a închide funcția și a termina formula
Cum
Excel păstrează valorile de dată și oră ca numere. Excel presupune că istoricul începe de la 1 ianuarie 1900 și acceptă această dată ca fiind 1. În timp ce numerele întregi reprezintă zilele, zecimalele reprezintă timpul. De exemplu; 1/1/2018 este egal cu 43101, 12:00 este egal cu 0,5.
Potem folosi funcția INT pentru a scăpa de valorile de timp sau de partea zecimală a numerelor. Funcția INT returnează pur și simplu partea de număr întreg dintr-o valoare numerică. În cazul nostru;
INT($B$3:$B$16) returnează {43292;43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Cu toate acestea; pentru a face funcția INT să funcționeze pe un array, trebuie să folosim funcția SUMPRODUCT. Capacitatea funcției SUMPRODUCT de a gestiona array-uri fără a utiliza formule de array-uri oferă un avantaj față de limitările funcțiilor SUMIF și SUMIFS. Funcția SUMPRODUCT poate evalua și returna array-uri din condiții de interval – valoare. Utilizăm această funcționalitate pentru a rezolva perechi de criterii interval – criteriu.
(INT($B$3:$B$16)=E3) returnează {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
De asemenea, funcția SUMPRODUCT însumează valorile din tabloul său de argumente dacă există un singur argument. Cu toate acestea, ecuațiile condiționate returnează valori booleene. Așadar, acestea trebuie convertite în numere care să fie semnificative pentru funcția SUMPRODUCT. Din fericire, operatorul de produs (*) face acest lucru.
Înmulțind un tablou de valori cu un tablou de condiții, obținem valorile tabloului generate în funcție de nevoile noastre.
$C$3:$C$16*(INT($B$3:$B$16)=E3) returnează {1;2;3;4;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
În cele din urmă, SUMPRODUCT returnează rezultatele ca sumă după dată în Excel, ceea ce este ceea ce căutăm.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3))) returnează 10 pentru data de 7/11/2018.
Opțiune alternativă
O modalitate alternativă este utilizarea PivotTable care vă poate oferi mai multe opțiuni decât însumarea valorilor. Puteți găsi informații detaliate în How to group days without time by Pivot Table.
Vezi, de asemenea, articolele conexe How to calculate weighted average with SUMPRODUCT, How to SUM 2d ranges with SUMPRODUCT și How to sum values with OR operator using SUMPRODUCT with multiple criteria.
.