Si vous souhaitez élargir les résultats d’une formule matricielle SUMIFS, vous devrez utiliser une fonction Lambda dans Google Sheets.
La fonction SOMME.SI peut élargir les résultats en utilisant la fonction FORMULEMATRICIELLE, mais pas la fonction SOMMESI. Une des limitations de SOMME.SI est qu’elle ne peut gérer qu’une seule plage de critères.
Cependant, dans SOMME.SI, nous pouvons combiner des critères et des plages de critères en tant que solution alternative à la formule matricielle SOMMESI. Cette approche ne fonctionne pas toujours, notamment lorsqu’il s’agit de plages de dates en tant que critères.
Une autre solution de contournement consiste à utiliser la fonction REQUÊTE. Cette méthode peut également présenter certaines limitations similaires à celles de SOMME.SI. Je vais couvrir toutes ces méthodes et astuces dans ce tutoriel.
Formule matricielle SOMMESI pour des résultats élargis dans Google Sheets
Cet exemple démontre l’utilisation de critères de plage de dates dans une formule matricielle SOMMESI dans Google Sheets.
Voici la syntaxe de SOMMESI à titre de référence :
SOMMESI(plage_somme; plage_critère1; critère1; [plage_critère2; critère2; …]).
Prenons l’exemple où vous disposez des dates de livraison de différents produits dans la colonne A, des quantités dans la colonne B et des confirmations de livraison dans la colonne C. Une marque « x » dans la colonne C signifie que la livraison de ce produit a été confirmée par le fournisseur.
Dans ce scénario, vous souhaitez calculer le total des livraisons confirmées entre le 01/11/2023 et le 05/11/2023. Les critères pour ces deux dates sont dans les cellules D2 et E2, et un autre critère, « x », est dans la cellule F2.
De plus, il existe un autre ensemble de critères dans les cellules D3:F3, qui sont les dates du 06/11/2023 et du 10/11/2023 dans les cellules D3:E3 et « x » dans la cellule F3.
Voici la formule SOMMESI non matricielle dans la cellule G2 :
=SOMMESI(B2:B14;A2:A14; »>= »&D2;A2:A14; »<= »&E2;C2:C14;F2).
Pour élargir les résultats dans les cellules, nous devons utiliser une formule matricielle SOMMESI.
Dans Microsoft 365 Excel, la formule s’étend en raison de son comportement de tableau dynamique. Vous devez simplement spécifier la plage de critères dans la formule comme suit :
=SOMMESI(B2:B14;A2:A14; »>= »&D2:D3;A2:A14; »<= »&E2:E3;C2:C14;F2:F3).
Cela ne fonctionne pas avec SOMMESI dans Google Sheets. Voici comment utiliser une formule matricielle SOMMESI pour élargir ses résultats dans Google Sheets.
SOMMESI avec MAP Lambda pour élargir les résultats du tableau
Tout d’abord, permettez-moi d’expliquer comment convertir facilement une formule SOMMESI non matricielle en une formule matricielle SOMMESI en utilisant la fonction MAP lambda dans Google Sheets :
Vous devez spécifier les plages de critères individuellement dans MAP, les nommer dans la fonction Lambda et remplacer les critères existants dans SOMMESI par les noms attribués.
Les plages de critères dans ce cas sont D2:D3, E2:E3 et F2:F3.
Spécifiez-les avec MAP comme suit :
=MAP(D2:D3;E2:E3;F2:F3; LAMBDA(critère1; critère2; critère3; formule_sommersi)).
Remplacez formule_sommersi par la formule non matricielle et dans cette formule, remplacez D2 par critère1, E2 par critère2 et F2 par critère3.
Voici la formule matricielle SOMMESI résultante pour élargir les résultats :
=MAP(D2:D3;E2:E3;F2:F3; LAMBDA(critère1; critère2; critère3; SOMMESI(B2:B14; A2:A14; »>= »&critère1; A2:A14; »<= »&critère2; C2:C14; critère3))).
Anatomie de la formule
Analysons les composants de la formule pour vous aider à comprendre la logique :
Syntaxe de la fonction MAP :
MAP(tableau1; [tableau2, …]; LAMBDA([nom, …]; expression_formule)).
Dans notre formule, les arguments sont les suivants :
- tableau1 : D2:D3
- tableau2 : E2:E3
- tableau3 : F2:F3
Dans la fonction LAMBDA :
- nom(s) : critère1, critère2, critère3 (correspondant à tableau1, tableau2 et tableau3)
- expression_formule : SOMMESI(B2:B14; A2:A14; »>= »&critère1; A2:A14; »<= »&critère2; C2:C14; critère3)
La fonction MAP applique la fonction LAMBDA pour chaque ensemble de critère1, critère2 et critère3 fourni dans D2:D3, E2:E3 et F2:F3 respectivement.
Cela donne une liste de sommes basée sur ces différents ensembles de critères.
Alternatives à la formule matricielle SOMMESI
La formule matricielle SOMMESI ci-dessus n’est pas facile à remplacer par des formules classiques. Par conséquent, je recommande de s’y tenir.
Cependant, dans certains scénarios, vous pouvez envisager d’utiliser d’autres formules en alternative à la formule matricielle SOMMESI. Voici quelques exemples.
SOMME.SI (critères dans une seule colonne)
Si vous souhaitez appliquer plusieurs critères dans une seule colonne, vous pouvez utiliser SOMME.SI, qui a la capacité d’élargir les résultats.
Voici la syntaxe de SOMME.SI à titre de référence :
SOMME.SI(plage; critère; [plage_somme]).
Problème :
Additionner les ventes au T1 (E3) et T2 (E4) lorsque les trimestres sont spécifiés dans A2:A et les montants des ventes sont spécifiés dans C2:C.
Solution :
=FORMULEMATRICIELLE(SOMME.SI(A2:A9;E3:E4;C2:C9)).
SOMME.SI (critères dans plusieurs colonnes)
Cette fois-ci, nous avons des noms de mois (en texte) dans la colonne A, des noms de fruits dans la colonne B et des montants de ventes dans la colonne C.
L’objectif est de calculer la somme des ventes pour les fruits Apple et Orange en janvier séparément.
Les critères sont définis comme suit :
- E3 et E4 contiennent « January »
- F3 et F4 contiennent « Apple » et « Orange »
Formule :
=FORMULEMATRICIELLE(SOMME.SI(A2:A&B2:B;E3:E4&F3:F4;C2:C)).
Étant donné que SOMME.SI peut gérer des critères dans une seule colonne, nous avons combiné des critères dans deux colonnes avec leurs plages respectives.
Cependant, il est essentiel de noter que cette alternative basée sur SOMME.SI peut ne pas fonctionner dans tous les scénarios, notamment lorsqu’il s’agit de critères impliquant des comparaisons.
Voici l’alternative MAP + SOMMESI :
=MAP(E3:E4;F3:F4;lambda(ensembleCritères1,ensembleCritères2; somme.si(C2:C;A2:A;ensembleCritères1;B2:B;ensembleCritères2))).
Utilisation de la fonction REQUÊTE comme alternative à SOMMESI pour élargir les résultats de la matrice
REQUÊTE est l’une des meilleures fonctions pour la manipulation et la visualisation des données.
La sortie de la REQUÊTE peut être structurée, ce qui la rend utile pour créer des tableaux croisés dynamiques et des graphiques basés sur des formules.
Voici un exemple de résolution du problème d’expansion de la formule matricielle SOMMESI à l’aide de la formule REQUÊTE. Nous utiliserons les données des fruits comme exemple ici.
Formule :
=REQUÊTE(A2:C; »Sélectionnez A,B, SUM(C) where A Matches ‘January’ and B matches ‘Apple|Orange’ group by A,B label SUM(C) » »).
Veuillez noter que cette formule est sensible à la casse et que j’ai spécifié les critères en tant que valeurs codées en dur.
Conclusion
Dans certains scénarios, vous voudrez peut-être utiliser la fonction FORMULEMATRICIELLE avec SOMMESI, non pas pour élargir les résultats, mais pour élargir une fonction utilisée à l’intérieur, notamment pour les fonctions liées aux dates.
Voici un exemple :
=FORMULEMATRICIELLE(SOMMESI(C2:C;YEAR(A2:A);F2;B2:B;E2).
Dans l’exemple ci-dessus, vous pouvez voir la fonction de date YEAR. Comme il s’agit d’une fonction non matricielle, vous devez utiliser FORMULEMATRICIELLE avec SOMMESI.
En fait, la FORMULEMATRICIELLE est utilisée pour prendre en charge la fonction YEAR. Cela signifie que vous pouvez également utiliser la formule ci-dessus de la manière suivante :
=SOMMESI(C2:C;FORMULEMATRICIELLE(YEAR(A2:A);F2;B2:B;E2).
