Imaginez pouvoir créer des formules dynamiques dans Google Sheets, qui s’adaptent automatiquement lorsque vous ajoutez ou supprimez des données. Eh bien, c’est possible grâce à la fonction FORMULEMATRICE !
La fonction FORMULEMATRICE est la fonction dédiée à la création de formules matricielles dans Google Sheets. Contrairement à Excel, Google Sheets ne dispose pas d’un moyen intégré de propager dynamiquement le résultat d’une formule matricielle vers le bas ou vers la droite. Vous pouvez utiliser la fonction FORMULEMATRICE pour obtenir un effet similaire, mais vous devez envelopper la formule en question avec cette fonction.
La fonction INDEX peut également être utilisée pour créer des formules matricielles, mais elle n’est pas aussi efficace que la fonction FORMULEMATRICE dans Google Sheets. Par conséquent, je recommande d’utiliser la fonction FORMULEMATRICE à moins que vous n’ayez une raison spécifique d’utiliser la fonction INDEX.
Syntaxe et arguments de la fonction FORMULEMATRICE
Voici la syntaxe de la fonction FORMULEMATRICE dans Google Sheets :
FORMULEMATRICE(formule_matricielle)
La fonction ne possède qu’un seul argument, à savoir la formule_matricielle.
Notes :
- L’argument formule_matricielle peut être une plage de cellules. Par exemple, =FORMULEMATRICE(A2:A10) renverra les valeurs des cellules A2 à A10.
- L’argument formule_matricielle peut être une expression mathématique. Par exemple, =FORMULEMATRICE(B2:B10*25) renverra les valeurs des cellules B2 à B10 multipliées par 25.
- L’argument formule_matricielle peut être une fonction qui renvoie un résultat supérieur à une seule cellule. Par exemple, =FORMULEMATRICE(FILTRER(Feuille1!B2:B10,Feuille1!B2:B10>5)*25) renverra les valeurs des cellules B2 à B10 qui sont supérieures à 5 multipliées par 25.
Exemples de formules matricielles dans Google Sheets
Dans l’exemple suivant, pour calculer le montant de chaque produit, j’ai utilisé la formule matricielle suivante dans la cellule E3 :
=FORMULEMATRICE(C3:C7*D3:D7)
Vous pouvez entrer la fonction FORMULEMATRICE dans Google Sheets de deux manières :
- Manuellement : Tapez =FORMULEMATRICE( , puis tapez la formule à étendre à l’intérieur et appuyez sur la touche Entrée.
- Automatiquement : Dans ce cas, vous devez taper la formule (qui doit commencer par le signe =) et appuyer sur Ctrl+Maj+Entrée (Windows) ou ⌘ + Maj + Entrée (Mac).
Dans l’exemple ci-dessus, si vous n’utilisez pas la fonction FORMULEMATRICE et utilisez une formule non matricielle, vous devez utiliser =C3*D3 dans la cellule E3 et remplir la formule vers le bas pour les lignes suivantes.
Pour remplir vers le bas, vous pouvez utiliser l’une des approches suivantes :
- Copiez la formule E3 et collez-la dans les cellules E4:E7. Pour ce faire, appuyez sur Ctrl+C (Windows) ou ⌘+C (Mac) dans la cellule E3, puis sélectionnez les cellules E4:E7 et appuyez sur Ctrl+V (Windows) ou ⌘+V (Mac).
- Sélectionnez les cellules E3:E7 et appuyez sur Ctrl+Entrée (Windows) ou ⌘+Entrée (Mac) pour remplir automatiquement la formule.
- Allez dans la cellule E3 et faites glisser la poignée de remplissage (un petit carré dans le coin inférieur droit de la cellule sélectionnée) vers le bas.
Voici quelques exemples supplémentaires de la fonction FORMULEMATRICE dans Google Sheets :
- =FORMULEMATRICE(GAUCHE(A2:A10,2)) : Renvoie les deux premiers caractères des textes des cellules A2:A10.
- =FORMULEMATRICE(TEXTE.DATE(ERREUR(FIN.MOIS(VAL.DATE(B1:B1000)),0))) : Convertit les dates de la plage B1:B1000 en dates de fin de mois.
- =FORMULEMATRICE(A1:A100& » « &B1:B100) : Combine les prénoms et les noms de famille dans la plage A1:B100.
5 choses à garder à l’esprit lors de l’utilisation de formules matricielles dans Google Sheets
Vous devez savoir quand utiliser la fonction FORMULEMATRICE dans Google Sheets pour obtenir un résultat sous forme de tableau. Les 5 points clés suivants vous aideront à comprendre comment l’utiliser et à résoudre les éventuelles erreurs qui peuvent survenir.
Liées aux performances
-
Assurez-vous que la formule matricielle dispose de suffisamment de cellules vides pour s’étendre. Si la formule matricielle n’a pas suffisamment de cellules vides, elle renverra une erreur #REF!.
-
Lorsque vous utilisez des plages ouvertes, placez la formule dans la première ligne (pour des données verticales) ou dans la première colonne (pour des données horizontales) de la plage. Cela permettra à la formule de s’étendre sur le bon nombre de cellules.
La formule matricielle suivante dans la cellule C3 de Google Sheets renvoie l’erreur « Le résultat n’a pas été étendu automatiquement, veuillez insérer plus de lignes (1) »:
=FORMULEMATRICE(LIGNE(A2:A))
N’entrez pas cette formule dans la ligne 3 ou toute autre ligne en dessous. Cela entraînera l’ajout de plusieurs lignes vides en bas de la feuille et affectera également les performances.
Liées à la fonctionnalité
- Toutes les utilisations de la fonction FORMULEMATRICE ne sont pas destinées à renvoyer des résultats sous forme de tableau. Dans certains cas, la fonction FORMULEMATRICE peut être utilisée pour étendre le résultat d’une autre fonction, comme la fonction ANNEE dans l’exemple suivant :
=FORMULEMATRICE(NB.SI(ANNEE(B1:B),2023))
Cette formule NB.SI renvoie le nombre de cellules de la plage de dates B1:B contenant l’année 2023. La fonction FORMULEMATRICE est utilisée pour étendre le résultat de la fonction ANNEE.
Voici un autre exemple. Dans ce cas, la fonction FORMULEMATRICE est utilisée pour étendre C2:C5>5, et non la fonction logique ET.
=FORMULEMATRICE(ET(C2:C5>5))
- Soyez conscient des fonctions et des expressions qui ne sont pas prises en charge par la fonction FORMULEMATRICE dans Google Sheets.
Comment les trouver ?
Voici une règle générale que je suis pour trouver les fonctions qui ne prennent pas en charge la fonction FORMULEMATRICE dans Google Sheets :
Si une fonction non basée sur des critères accepte un argument de plage et renvoie un résultat unique, alors la formule matricielle ne pourra peut-être pas l’étendre. Par exemple, SOMME, MAX, ESTDATE, ET, OU, etc.
Voici un exemple utilisant la fonction logique ET où la plage de cellules C2:C6 contient des valeurs booléennes VRAI ou FAUX.
=ET(C2:C6)
Cette formule renverra VRAI si toutes les valeurs booléennes de la plage sont VRAIES. Sinon, elle renverra FAUX.
Si une fonction basée sur des critères accepte un argument de plage et plusieurs critères d’une plage, alors elle peut être étendue. Exemples : NB.SI, SOMME.SI, RECHERCHEV, RECHERCHEX, SI, SI.CONDITIONS, etc.
Cependant, si vous utilisez plusieurs critères à partir de plusieurs plages, certaines fonctions renverront un résultat sous forme de tableau, tandis que d’autres ne le feront pas. Par exemple, la fonction SOMME.SI ne renverra pas un résultat sous forme de tableau, mais la fonction NB.SI le fera.
- Certaines fonctions peuvent s’étendre automatiquement aux cellules voisines sans utiliser la fonction FORMULEMATRICE. Ces fonctions comprennent SEQUENCE, INDEX, QUERY, FILTRER, TRIER, IMPORTRANGE, VSTOCKAGE, HSTOCKAGE, etc.
Vérifiez absolument : Guide des fonctions Google Sheets.
Les avantages de l’utilisation de la fonction FORMULEMATRICE dans Google Sheets
Voici les principaux avantages de l’utilisation de la fonction FORMULEMATRICE dans Google Sheets :
- Amélioration des performances des feuilles de calcul : Une formule matricielle peut remplacer des milliers de formules non matricielles, ce qui peut améliorer les performances des feuilles de calcul.
- Facilité de correction des formules : Si vous devez modifier une formule, vous pouvez le faire rapidement si vous utilisez une formule matricielle. Vous n’avez besoin de modifier la formule que dans une seule cellule.
- Élimination des erreurs : Les formules non matricielles sont sujettes aux erreurs lorsque la disposition de la feuille est modifiée. L’ajout de lignes ou de colonnes peut provoquer des cellules vides dans la ligne ou la colonne où la formule est appliquée. Une formule matricielle peut les remplir automatiquement.
- Nettoyage des données : Il est plus rapide de supprimer d’anciennes formules de votre feuille si vous avez des formules matricielles. Il vous suffit d’appuyer sur le bouton de suppression dans une ou deux cellules, en fonction du nombre de formules matricielles.
Comment utiliser la fonction FORMULEMATRICE avec RECHERCHEV, RECHERCHEX, NB.SI et SOMME.SI dans Google Sheets
Supposons que nous disposons d’une liste des 10 fruits les plus populaires et de leurs origines naturelles dans la plage A1:B11, où A2:A11 contient les noms des fruits et B2:B11 contient l’origine naturelle. Les cellules A1 et B1 sont réservées aux titres.
Les critères sont les noms des fruits « Fraise » et « Mangue » dans les cellules D2 et D3, respectivement. Comment renvoyer l’origine naturelle à l’aide de RECHERCHEV et RECHERCHEX ?
Voici la formule matricielle RECHERCHEV pour rechercher les noms des fruits dans la plage A2:A11 et renvoyer l’origine naturelle dans la plage B2:B11.
Syntaxe RECHERCHEV : RECHERCHEV(clé_recherche, plage, index, [est_ordonnée])
=FORMULEMATRICE(RECHERCHEV(D2:D3,A2:B11,2,FALSE))
Voici la formule matricielle RECHERCHEX correspondante dans Google Sheets.
Syntaxe RECHERCHEX : RECHERCHEX(clé_recherche, plage_recherche, plage_résultat, [valeur_absente], [mode_correspondance], [mode_recherche])
=FORMULEMATRICE(RECHERCHEX(D2:D3,A2:A11,B2:B11))
Vous aimerez peut-être : Différences clés entre RECHERCHEV et RECHERCHEX dans Google Sheets.
Dans la même plage de données, vous voulez trouver le nombre d’occurrences de « Amérique du Nord » et « Asie du Sud-Est » dans la plage B2:B11.
Si les critères sont dans les cellules D2:D3, la formule matricielle COUNTIF suivante renverra les résultats 1 et 3.
Syntaxe COUNTIF : COUNTIF(plage, critère)
=FORMULEMATRICE(COUNTIF(B2:B11,D2:D3))
Voici un autre exemple d’utilisation de la formule matricielle dans Google Sheets.
La combinaison suivante de FORMULEMATRICE et SOMME.SI renvoie la somme des valeurs de la colonne B pour les valeurs de la colonne A correspondant aux critères dans la plage D4:D6.
=FORMULEMATRICE(SOMME.SI(A1:A,D4:D6,B1:B))
Vous aimerez peut-être : Somme sur critères avec des valeurs multiples dans Google Sheets.
Ce que LAMBDA peut faire que FORMULEMATRICE ne peut pas
C’est un conseil supplémentaire. J’ai déjà expliqué que la fonction FORMULEMATRICE dans Google Sheets ne peut pas étendre toutes les formules. Par exemple, nous ne pouvons pas étendre la fonction MIN suivante à l’aide de la fonction FORMULEMATRICE dans Google Sheets.
=MIN(B2:E2)
Veuillez consulter la plage de cellules G2:G5 dans la capture d’écran ci-dessous pour les exemples. Nous pouvons l’étendre en utilisant BYROW, l’une des fonctions auxiliaires de LAMBDA.
=BYROW(B2:E5,LAMBDA(v,MIN(v)))
Conclusion
Apprendre la fonction FORMULEMATRICE est la première étape pour devenir compétent dans l’utilisation de Google Sheets. Les formules matricielles peuvent changer votre façon d’utiliser Google Sheets.
N’oubliez pas de ne conserver que les lignes et les colonnes que vous souhaitez dans votre feuille pour améliorer les performances de celle-ci. Suivez également mes instructions lorsque vous utilisez des plages ouvertes dans cette fonction.
