Excel a déjà des fonctions comme le SUMIF et le SUMIFS pour additionner des données par groupes. Cependant ; elles ne peuvent pas fonctionner si vous avez des valeurs date-heure combinées. Cet article montre Comment faire une somme par date dans Excel sans temps en utilisant les fonctions SUMPRODUCT et INT.
Syntaxe
=SUMPRODUCT( plage de valeurs * ( INT( plage de date-heure ) = cellule de date ) )
Etapes
- Débuter avec la fonction =SUMPRODUCT(
- Type de sélection de la plage qui contient les valeurs $C$3 :$C$16
- Utiliser l’astérisque pour produire des valeurs de plage avec la plage de condition et ouvrir une parenthèse *(
- Continuer avec INT($B$3 :$B$16) pour supprimer la partie temps
- Entrer la condition avec un signe égal et la plage de critères =E3
- Type )) pour fermer la fonction et terminer la formule
Comment
Excel conserve les valeurs de date et d’heure sous forme de nombres. Excel suppose que l’historique commence le 1er janvier 1900 et accepte cette date comme 1. Alors que les nombres entiers représentent les jours, la décimale représente le temps. Par exemple ; 1/1/2018 est égal à 43101, 12:00 est égal à 0,5.
Nous pouvons utiliser la fonction INT pour nous débarrasser des valeurs de temps ou de la partie décimale des nombres. La fonction INT renvoie simplement la partie entière du nombre à partir d’une valeur numérique. Dans notre cas;
INT($B$3:$B$16) renvoie {43292;43292;43292;43292;43293;43293;43293;43293;43293;43293;43293;43294;43294}
Cependant ; pour que la fonction INT fonctionne sur un tableau, nous devons utiliser la fonction SUMPRODUCT. La capacité de la fonction SUMPRODUCT à traiter les tableaux sans utiliser de formules de tableau offre un avantage par rapport aux limites des fonctions SUMIF et SUMIFS. La fonction SUMPRODUCT peut évaluer et renvoyer des tableaux à partir de conditions de plage de valeurs. Nous utilisons cette fonctionnalité pour résoudre les paires plage – critères.
(INT($B$3:$B$16)=E3) renvoie {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
De plus, la fonction SUMPRODUCT additionne les valeurs de son tableau d’arguments s’il n’y a qu’un seul argument. Cependant, les équations conditionnelles renvoient des valeurs booléennes. Elles doivent donc être converties en nombres significatifs pour la fonction SUMPRODUCT. Heureusement, l’opérateur produit (*) le fait.
En multipliant un tableau de valeurs avec un tableau de conditions, nous obtenons les valeurs du tableau générées selon nos besoins.
C$3:$C$16*(INT($B$3:$B$16)=E3) renvoie {1;2;3;4;0;0;0;0;0;0;0;0;0;0}
Enfin, le SUMPRODUCT renvoie les résultats sous forme de somme par date dans Excel, ce qui est ce que nous recherchons.
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) renvoie 10 pour la date du 7/11/2018.
Option alternative
Une autre façon est d’utiliser PivotTable qui peut vous donner plus d’options que la somme des valeurs. Vous pouvez trouver des informations détaillées dans Comment regrouper les jours sans heure par tableau croisé dynamique.
Voir aussi les articles connexes comment calculer une moyenne pondérée avec SUMPRODUCT, comment faire la SOMME de 2d plages avec SUMPRODUCT, et comment additionner des valeurs avec l’opérateur OR en utilisant SUMPRODUCT avec plusieurs critères.
.