Téléchargez l'exemple de classeur
Ce didacticiel montrera comment utiliser les fonctions SUMPRODUCT et SUMIFS pour additionner des données qui répondent à certains critères sur plusieurs feuilles dans Excel et Google Sheets..
Somme régulière sur plusieurs feuilles
Parfois, vos données peuvent s'étendre sur plusieurs feuilles de calcul dans un fichier Excel. Ceci est courant pour les données collectées périodiquement. Chaque feuille d'un classeur peut contenir des données pour une période définie. Nous voulons une formule qui additionne les données contenues dans deux ou plusieurs feuilles.
La fonction SUM vous permet de additionner facilement des données sur plusieurs feuilles à l'aide d'un Référence 3D:
| 1 | =SOMME(Feuille1:Feuille2!A1) |
Cependant, cela n'est pas possible avec la fonction SUMIFS. Au lieu de cela, nous devons utiliser une formule plus compliquée.
Somme si sur plusieurs feuilles
Cet exemple résumera le Nombre de livraisons prévues pour chaque Client sur plusieurs feuilles de calcul, chacune contenant des données relatives à un mois différent, en utilisant les fonctions SUMIFS, SUMPRODUCT et INDIRECT :
| 1 | =SUMPRODUCT(SUMIFS(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Parcourons cette formule.
Étape 1 : Créez une formule SUMIFS pour 1 feuille d'entrée uniquement :
Nous utilisons la fonction SUMIFS pour additionner les Nombre de livraisons prévues par Client pour une seule fiche de données d'entrée :
| 1 | =SUMIFS(D3:D7,C3:C7,H3) |
Étape 2 : ajouter une référence de feuille à la formule
Nous gardons le même résultat de formule, mais nous spécifions que les données d'entrée sont dans la feuille appelée 'Étape 2'
| 1 | =SUMIFS('Etape 2'!D3:D7,'Etape 2'!C3:C7,H3) |
Étape 3 : Imbriquer dans une fonction SUMPRODUCT
Pour préparer la formule pour effectuer des calculs SUMIFS sur plusieurs feuilles, puis pour additionner les résultats, nous ajoutons une fonction SOMMEPROD autour de la formule
| 1 | =SUMPRODUCT(SUMIFS('Etape 3'!D3:D7,'Etape 3'!C3:C7,H3)) |
L'utilisation de la fonction SUMIFS sur une feuille donne une valeur unique. Sur plusieurs feuilles, la fonction SUMIFS génère un tableau de valeurs (une pour chaque feuille de calcul). Nous utilisons la fonction SOMMEPROD pour additionner les valeurs de ce tableau.
Étape 4 : remplacez la référence de feuille par une liste de noms de feuilles
Nous souhaitons remplacer le Nom de la feuille partie de la formule avec une liste de données contenant les valeurs : Jan, fév, Mar, et avr. Cette liste est stockée dans les cellules F3:F6.
La fonction INDIRECT pour s'assurer que la liste de texte montrant Noms des feuilles est traité comme faisant partie d'une référence de cellule valide dans la fonction SUMIFS.
| 1 | =SUMPRODUCT(SUMIFS(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Dans cette formule, la référence de plage précédemment écrite :
| 1 | 'Étape 3'!D3:D7 |
Est remplacé par :
| 1 | INDIRECT("'"&F3:F6&"'!"&"D3:D7") |
Les guillemets rendent la formule difficile à lire, donc ici elle est affichée avec des espaces ajoutés :
| 1 | INDIRECT ( " ' " & F3:F6 & " ' ! " & "D3:D7" ) |
L'utilisation de cette façon de référencer une liste de cellules nous permet également de résumer les données de plusieurs feuilles qui ne suivent pas un style de liste numérique. Une référence 3D standard nécessiterait que les noms de feuilles soient dans le style : Input1, Input2, Input3, etc., mais l'exemple ci-dessus vous permet d'utiliser une liste de n'importe quel Noms des feuilles et de les faire référencer dans une cellule séparée.
Verrouillage des références de cellule
Pour rendre nos formules plus faciles à lire, nous avons montré les formules sans références de cellules verrouillées :
| 1 | =SUMPRODUCT(SUMIFS(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Mais ces formules ne fonctionneront pas correctement si elles sont copiées et collées ailleurs dans votre fichier. Au lieu de cela, vous devez utiliser des références de cellules verrouillées comme ceci :
| 1 | =SUMPRODUCT(SUMIFS(INDIRECT("'"&$F$3:$F$6&"'!"&"D3:D7"),INDIRECT("'"&$F$3:$F$6&"'!"& "C3:C7"),H3)) |
Lisez notre article sur le verrouillage des références de cellules pour en savoir plus.
Somme si sur plusieurs feuilles dans Google Sheets
L'utilisation de la fonction INDIRECT pour référencer une liste de feuilles dans une fonction SUMPRODUCT et SUMIFS n'est actuellement pas possible dans Google Sheets.
Au lieu de cela, des calculs SUMIFS séparés peuvent être effectués pour chaque feuille d'entrée et les résultats additionnés :
| 1234 | =SUMIFS(Jan!D3:D7,Jan!C3:C7,H3)+SUMIFS(Fév!D3:D7,Fév!C3:C7,H3)+SUMIFS(Mar!D3:D7,Mar!C3:C7,H3)+SUMIFS(Avr!D3:D7,Avr!C3:C7,H3) |
