Google Sheets propose une méthode unique pour appliquer une condition OU (logic) à la fonction SOMME.SI. La méthode des constantes de tableau que vous connaissez peut-être dans Excel ne fonctionnera pas ici.
Par exemple, dans Excel, vous pouvez utiliser la formule suivante pour faire la somme des quantités livrées si la date de livraison est le 21 octobre 2023 et que le statut de la livraison est « envoyé » ou « livré » :
=SOMME(SOMME.SI(C2:C14;A2:A14;DATE(2023;10;21);D2:D14;{"envoyé";"livré"}))Voici la syntaxe de la fonction SOMME.SI :
SOMME.SI(plage_somme, plage_critère1, critère1, [plage_critère2, ...], [critère2, ...])Dans cette formule :
- C2:C14 est la plage de somme.
- A2:A14 est la plage de critère 1.
- D2:D14 est la plage de critère 2.
- Les critères respectifs sont DATE(2023,10,21) et {« envoyé », »livré »}.
Mais cette condition avec la logique OU ne fonctionnera pas dans Google Sheets.
Pour effectuer une somme avec une condition OU, vous devez utiliser la fonction REGEXMATCH ou l’une des fonctions auxiliaires de lambda.
Note pour mes fidèles lecteurs : J’ai déjà abordé ce sujet dans l’un de mes précédents tutoriels, mais dans ce tutoriel, je me suis concentré sur la fonction REGEXMATCH, et non sur la fonction SOMME.SI.
Données d’exemple
Nous avons utilisé les données d’exemple suivantes dans l’exemple de la formule Excel ci-dessus. Nous utiliserons les mêmes données dans Google Sheets pour montrer comment la condition logique OU diffère entre Google Sheets et Excel.
Condition OU avec REGEXMATCH
Cette approche offre plusieurs avantages. Vous n’avez pas besoin d’utiliser des caractères génériques pour les correspondances partielles séparément, le cas échéant.
De plus, vous pouvez rendre la fonction SOMME.SI avec une condition OU sensible à la casse.
Laissez-moi vous expliquer la logique derrière l’utilisation de REGEXMATCH dans SOMME.SI, puis nous écrirons la formule.
Dans le problème précédent, spécifier le critère dans la colonne de date est simple car nous n’avons pas besoin d’appliquer le test logique OU.
Le défi se pose dans la colonne de statut. Nous devons tester deux conditions : « envoyé » et « livré ».
Référez-vous à la capture d’écran ci-dessous pour la formule de tableau ARRAYFORMULA REGEXMATCH dans la cellule E2 :
=ARRAYFORMULA(REGEXMATCH(D2:D14;"envoyé|livré"))Cette formule renverra un tableau de valeurs booléennes.
Voici la syntaxe de la formule REGEXMATCH :
REGEXMATCH(texte; expression_régulière)Si la valeur dans la cellule contient « envoyé » ou « livré », l’élément correspondant dans le tableau sera VRAI, indiquant une correspondance. Si la cellule ne contient aucun de ces mots, l’élément du tableau sera FAUX, indiquant qu’il n’y a pas de correspondance.
Dans ce cas, les critères sont sensibles à la casse et c’est une correspondance partielle. Si vous souhaitez une correspondance exacte, sans caractère générique, remplacez l’expression régulière « envoyé|livré » par « ^envoyé$|^livré$ ».
Si vous souhaitez qu’elle soit insensible à la casse mais requiert une correspondance exacte, sans caractère générique, utilisez « (?i)^envoyé$|^livré$ ».
Maintenant, voici la formule SOMME.SI en utilisant la logique OU dans Google Sheets :
=SOMME(SOMME.SI(C2:C14;A2:A14;DATE(2023;10;21);E2:E14;VRAI))Nous avons remplacé la colonne de statut, initialement dans la plage de critères de D2:D14, par le résultat de la formule REGEXMATCH.
Note : Vous n’avez pas besoin d’utiliser la colonne auxiliaire E2:E14. Dans SOMME.SI, remplacez cette plage par la formule REGEXMATCH elle-même.
Condition OU avec Lambda
Utiliser MAP ou BYROW lambda avec SOMME.SI est une autre façon de gérer la condition OU dans Google Sheets.
Voici un exemple d’utilisation de MAP selon les données d’exemple ci-dessus :
=SOMME(MAP({"envoyé","livré"};LAMBDA(ligne; SOMME.SI(C2:C14;A2:A14;DATE(2023;10;21);D2:D14;ligne))))Cette formule fonctionne comme suit :
Dans cette formule, nous utilisons la fonction MAP pour itérer sur le tableau {« envoyé », »livré »} et appliquons la fonction lambda à chaque élément de ce tableau.
La fonction lambda, définie comme LAMBDA(ligne; SOMME.SI(C2:C14; A2:A14; DATE(2023, 10, 21); D2:D14; ligne)), est une partie cruciale de la formule.
La ligne sert d’argument passé à la fonction lambda, représentant soit « envoyé » soit « livré ».
La fonction MAP exécute essentiellement la fonction lambda deux fois, une fois avec « envoyé » comme argument de ligne, et une fois avec « livré » comme argument de ligne, donnant ainsi deux valeurs.
La fonction SOMME agrège les valeurs renvoyées, nous donnant la somme finale des résultats.
Correspondance générique
Pour utiliser des correspondances partielles dans la condition OU de SOMME.SI, utilisez le caractère générique astérisque (*) comme suit :
Par exemple, pour correspondre à n’importe quel mot contenant « envoyé » ou « livré », utilisez les caractères génériques comme suit :
{"*envoyé*","*livré*"}Le caractère générique astérisque (*) correspond à n’importe quelle séquence de caractères, y compris zéro caractère.
Dans ce cas, les caractères génériques correspondront à n’importe quel mot qui contient la chaîne « envoyé » ou « livré », indépendamment des autres caractères qui peuvent être présents dans le mot.
Note : Cette formule n’est pas sensible à la casse.
Alternatives à SOMME.SI avec condition OU dans Google Sheets
Si vous recherchez une solution parfaite pour remplacer SOMME.SI avec des critères OU, utilisez QUERY.
La fonction QUERY peut être utilisée pour effectuer diverses tâches, y compris SOMME.SI avec des critères OU. Il existe plusieurs façons de le faire, en utilisant soit l’opérateur logique OU, soit les fonctions de comparaison de chaînes Matches ou Contains.
- Requête avec l’opérateur logique OU (sensible à la casse et correspondance exacte) :
=QUERY(A2:D14;"select sum(C) where A=date '2023-10-21' and D='envoyé' or D='livré' label sum(C)''")- Requête avec la fonction de comparaison de chaîne Matches (sensible à la casse et correspondance exacte) :
=QUERY(A2:D14;"select sum(C) where A=date '2023-10-21' and D matches 'envoyé|livré' label sum(C)''")- Requête avec la fonction de comparaison de chaîne Contains (sensible à la casse et correspondance partielle) :
=QUERY(A2:D14;"select sum(C) where A=date '2023-10-21' and D contains 'envoyé' or D contains 'livré' label sum(C)''")Notes :
- Le comportement par défaut des fonctions de comparaison de chaînes comme Matches et Contains est sensible à la casse. Pour les rendre insensibles à la casse, convertissez les chaînes en minuscules en utilisant la fonction Lower avant de les comparer.
- La fonction de comparaison de chaînes Contains correspond à n’importe quelle chaîne contenant la sous-chaîne spécifiée, indépendamment de sa position dans la chaîne.
Conclusion
Vous avez peut-être remarqué que j’ai codé les critères dans toutes les formules SOMME.SI avec des conditions OU ci-dessus. Vous pouvez utiliser des références de cellules à la place. Veuillez consulter les fonctions correspondantes dans mon guide des fonctions pour en savoir plus sur l’utilisation des critères.
J’espère que vous avez apprécié ce tutoriel. Merci de votre attention !
