Excel ha già funzioni come il SUMIF e il SUMIFS per sommare i dati per gruppi. Tuttavia, non possono funzionare se si hanno valori data-ora combinati. Questo articolo mostra come sommare per data in Excel senza tempo usando le funzioni SUMPRODUCT e INT.
Sintassi
=SUMPRODUCT( intervallo di valori * ( INT( intervallo di data-ora ) = cella di data )
Passi
- Inizia con =SUMPRODUCT( funzione
- Tipo di selezionare l’intervallo che contiene valori $C$3:$C$16
- Utilizza l’asterisco per produrre i valori dell’intervallo con l’intervallo di condizioni e apri una parentesi *(
- Continua con INT($B$3:$B$16) per rimuovere la parte del tempo
- Inserisci condizione con un segno di uguale e intervallo di criteri =E3
- Tipo )) per chiudere la funzione e finire la formula
Come
Excel mantiene i valori di data e ora come numeri. Excel presume che la storia inizi dal 1 gennaio 1900 e accetta questa data come 1. Mentre i numeri interi rappresentano i giorni, il decimale rappresenta il tempo. Per esempio; 1/1/2018 è uguale a 43101, 12:00 è uguale a 0.5.
Possiamo usare la funzione INT per sbarazzarci dei valori di tempo o della parte decimale dei numeri. La funzione INT restituisce semplicemente la parte intera del numero da un valore numerico. Nel nostro caso;
INT($B$3:$B$16) restituisce {43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Tuttavia; per far funzionare la funzione INT su un array, dobbiamo usare la funzione SUMPRODUCT. La capacità della funzione SUMPRODUCT di gestire gli array senza usare formule di array fornisce un vantaggio rispetto alle limitazioni delle funzioni SUMIF e SUMIFS. La SUMPRODUCT può valutare e restituire array da condizioni di range – value. Usiamo questa funzionalità per risolvere le coppie intervallo – criterio.
(INT($B$3:$B$16)=E3) restituisce {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Inoltre, la funzione SUMPRODUCT somma i valori nel suo argomento array se c’è un solo argomento. Tuttavia, le equazioni condizionali restituiscono valori booleani. Quindi, devono essere convertiti in numeri significativi per la funzione SUMPRODUCT. Fortunatamente, l’operatore prodotto (*) fa questo.
Moltiplicando un array di valori con un array di condizioni, otteniamo i valori dell’array generati secondo le nostre esigenze.
$C$3:$C$16*(INT($B$3:$B$16)=E3) restituisce {1;2;3;4;0;0;0;0;0;0;0;0;0;0;0}
Infine, il SUMPRODUCT restituisce i risultati come somma per data in Excel che è quello che stiamo cercando.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) restituisce 10 per la data del 7/11/2018.
Opzione alternativa
Un modo alternativo è usare PivotTable che può darvi più opzioni che sommare valori. Puoi trovare informazioni dettagliate in Come raggruppare i giorni senza tempo con la tabella Pivot.
Vedi anche gli articoli correlati come calcolare la media ponderata con SUMPRODUCT, come sommare 2d intervalli con SUMPRODUCT, e come sommare i valori con l’operatore OR usando SUMPRODUCT con criteri multipli.