Sheets 
Agenda 
Forms 
Apps Script 
Gmail 
Chat 
Meet 
Contacts 
Chrome 
Sites 
Looker Studio 
Slides 
Docs 
Drive 
AppSheet 
Android 
Chromebook 
Equipements Google 
Google Cloud Platform 
Google Photos 
Maps 
Youtube 
Google Sheets : comment utiliser ARRAYFORMULA avec des conditions ET() ou bien OU() ?
ARRAYFORMULA en quelques mots Selon le support Google, “la fonction ARRAYFORMULA() permet d’afficher des valeurs issues d’une formule de tableau sur plusieurs lignes et/ou colonnes” (#3093275). On parle aussi de tableau de valeurs par exemple, […]
Ce que vous allez découvrir
- ARRAYFORMULA en quelques mots
- Arrayformula avec ET() et OU()
Google Sheets : comment utiliser ARRAYFORMULA avec des conditions ET() ou bien OU() ?
ARRAYFORMULA en quelques mots
Selon le support Google, “la fonction ARRAYFORMULA() permet d’afficher des valeurs issues d’une formule de tableau sur plusieurs lignes et/ou colonnes” (#3093275).
On parle aussi de tableau de valeurs par exemple, toujours selon le support Google, “un tableau composé de lignes et de colonnes contenant des valeurs. […] Toutes les fonctions qui acceptent une plage de cellules (A1:B6, par exemple) comme paramètre d’entrée acceptent également un tableau de valeurs en lieu et place de cette plage.” (#6208276).
On emploie aussi le terme de formule matricielle.
Voici un tableau de valeurs contenant des prix et des quantités et nous souhaitons calculer le chiffre d’affaires, soit le produit du prix unitaire et de la quantité vendue.
Ce tableau sera régulièrement incrémenté avec de nouvelles valeurs. Pour éviter de recopier la formule à chaque fois, nous allons donc encapsuler la formule B2*C2 à l’intérieur d’un ARRAYFORMULA().
La formule en D2 est :
=ArrayFormula(SI(ESTVIDE(A2:A);;B2:B*C2:C)).
Comme vous pouvez le constater avec les lignes 7, 8 et 9, quand il manque un prix et/ou une quantité, alors le CA est calculé malgré tout et affiche donc la valeur zéro.
Le 0€ exprime une information en comptabilité et ici il faudrait ne rien écrire car il n’y a pas eu de vente. Alors pour pallier cet inconvénient, nous serions tenté de faire appel à un OU() pour vérifier si la colonne prix unitaire ou quantité est vide et d’écrire la formule suivante :
=ArrayFormula(SI(OU(ESTVIDE(B2:B);ESTVIDE(C2:C));;B2:B*C2:C))... mais nous n’avons pas le résultat escompté :
En fait, cela fonctionne, mais il faudrait que l’ensemble des cellules des plages B2:B et C2:C ne soit pas vide, ce qui n’a aucun sens.
Arrayformula avec ET() et OU()
Nous constatons donc que ET() et OU() ne sont pas compatibles avec ArrayFormula(). Alors comment obtenir un résultat équivalent ?
Allons un peu plus dans le détail ; le résultat de la fonction ESTVIDE() est VRAI si la cellule est vide ou FAUX si il y a une valeur dans la cellule.
Ce sont des valeurs booléennes : VRAI égal 1 et FAUX égal à 0.
VRAI + VRAI = 2, VRAI + FAUX = 1 et FAUX + FAUX = 0 ; le + équivaut à un OU()
VRAI * VRAI = 1, VRAI * FAUX = 0 et FAUX * FAUX = 0 ; le * équivaut à un ET()
En F2, la formule est : =ArrayFormula(ESTVIDE(B2:B15))
En G2, la formule est : =ArrayFormula(ESTVIDE(C2:C15))
En H2, la formule est :=ArrayFormula(F2:F15+G2:G15
En I2, la formule est : =ArrayFormula(F2:F15*G2:G15)
Nous venons donc de créer deux matrices en colonne H c’est le résultat d’un OU et en colonne I celui d’un ET.
Si on observe bien ces matrices, celle qui répond parfaitement à notre problématique est la matrice OU en colonne H : si le résultat est égal à zéro, alors il faut effectuer l’opération B * C pour obtenir la valeur du chiffre d’affaires, sinon il ne faut rien faire.
La formule en D2 est donc :
=ArrayFormula(SI((ESTVIDE(B2:B)+ESTVIDE(C2:C))>0;;B2:B*C2:C)).
N’hésitez pas à nous contacter pour vous accompagner dans votre formation ou pour des projets spécifiques autour des outils Google.
Besoin d'un peu plus d'aide sur Sheets ?
Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !
Découvrir nos formations Sheets- Articles connexes
- Plus de l'auteur
3
Note du cours
(0)
(0)
(1)
(0)
(0)
Bonjour, intéressant, mais malheureusement, les images ne s’affichent pas.