Somme en Fonction des Numéros de Semaines – Excel et Google Sheets

Ce tutoriel montre comment additionner des données correspondant à des numéros de semaine spécifiques dans Excel et Google Sheets.

 

Somme Si en Fonction de Numéros de Semaine

Pour additionner en fonction de numéros de semaine, nous allons utiliser la fonction SOMME.SI.ENS. Mais d’abord, nous devons ajouter une colonne d’aide contenant la fonction NO.SEMAINE.

La colonne d’aide Numéro de Semaine est calculée à l’aide de la fonction NO.SEMAINE :

=NO.SEMAINE(B3;1)

Ensuite, nous utiliserons la fonction SOMME.SI.ENS pour additionner toutes les ventes qui ont lieu pendant un numéro de semaine spécifique.

=SOMME.SI.ENS(D3:D9;C3:C9;F3)

Somme Si par Numéro de Semaine – Sans Colonne d’Aide

La méthode de la colonne auxiliaire est facile à suivre, mais vous pouvez également reproduire le calcul dans une formule unique en utilisant la fonction SOMMEPROD en combinaison avec la fonction NO.SEMAINE pour additionner le nombre total de ventes correspondant à un numéro de semaine spécifié.

=SOMMEPROD(--(NO.SEMAINE(B3:B9+0;1)=E3);C3:C9)

Dans cet exemple, nous pouvons utiliser la fonction SOMMEPROD pour effectuer des calculs complexes de type « somme si ». Parcourons l’exemple ci-dessus.

Voici notre formule finale :

=SOMMEPROD(--(NO.SEMAINE(B3:B9+0;1)=E3);C3:C9)

D’abord, la fonction SOMMEPROD liste le tableau des valeurs des plages de cellules :

=(--(({"1/3/2020" ; "1/6/2020" ; "1/9/2020" ; "1/12/2020" ; "1/15/2020" ; "1/18/2020" ; "1/21/2020"}+0;1)=1); {4 ; 9 ; 1 ; 7 ; 6 ; 2 ; 5})

Ensuite, la fonction NO.SEMAINE calcule le numéro de semaine de chacune des dates de vente.

La fonction NO.SEMAINE n’est pas conçue pour travailler avec des valeurs de tableau, nous devons donc ajouter zéro (« +0 ») pour que la fonction NO.SEMAINE traite les valeurs correctement.

=SOMMEPROD(--({1 ; 2 ; 2 ; 3 ; 3 ; 3 ; 4}=1); {4 ; 9 ; 1 ; 7 ; 6 ; 2 ; 5})

Les valeurs de Numéro de Semaine égales à 1 sont transformées en valeurs VRAIES.

=SOMMEPROD(--({VRAI ; FAUX ; FAUX ; FAUX ; FAUX ; FAUX ; FAUX ; FAUX}); {4 ; 9 ; 1 ; 7 ; 6 ; 2 ; 5})

Ensuite, les doubles tirets (–) convertissent les valeurs VRAIES et FAUX en 1 et 0 :

=SOMMEPROD({1 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0}, {4 ; 9 ; 1 ; 7 ; 6 ; 2 ; 5})

La fonction SOMMEPROD multiplie ensuite chaque paire d’entrées dans les tableaux pour produire un tableau de Nombre de Ventes dont le numéro de Semaine correspond à 1 :

=SOMMEPROD({4 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0})

Enfin, les chiffres du tableau sont additionnés :

=4

Cette formule est ensuite répétée pour les autres valeurs possibles de numéro de semaine.

Vous trouverez plus de détails sur l’utilisation des instructions booléennes et de la commande « — » dans une fonction SOMMEPROD ici.

Verrouillage des Références de Cellule

Pour faciliter la lecture de nos formules, nous avons présenté les formules sans les références de cellule verrouillées :

=SOMMEPROD(--(NO.SEMAINE(B3:B9+0;1)=E3);C3:C9)

Mais ces formules ne fonctionneront pas correctement lorsqu’elles seront copiées et collées ailleurs dans votre fichier. Vous devez plutôt utiliser des références de cellules verrouillées comme ceci :

=SOMMEPROD(--(NO.SEMAINE($B$3:$B$9+0;1)=E3);$C$3:$C$9)

Pour en savoir plus, lisez notre article sur le verrouillage des références de cellule.

Somme Si en Fonction de Numéros de Semaine dans Google Sheets

Ces formules fonctionnent exactement de la même manière dans Google Sheets et dans Excel.

Toutefois, la fonction NO.SEMAINE est plus souple dans Google Sheets que dans Excel, et accepte les entrées et sorties de type tableau. Par conséquent, l’opération {Tableau}+0 dans la formule NO.SEMAINE(B3:B9+0;1) n’est pas nécessaire.

La formule SOMMEPROD complète peut être écrite dans Google Sheets comme suit :

=SOMMEPROD(--(NO.SEMAINE($B$3:$B$9;1)=E3);$C$3:$C$9)