Comprendre l'équivalent de "IN" avec Excel et la fonction SI
La fonction SI dans Excel permet d'évaluer une condition et de renvoyer une valeur si cette condition est vraie, et une autre valeur si elle est fausse. Sa syntaxe de base est la suivante :
=SI(condition; valeur_si_vrai; valeur_si_faux)
Le défi est qu'Excel n'a pas de fonction "IN" native, comme on peut en trouver dans certains langages de programmation (SQL par exemple). L'objectif est donc de simuler ce comportement pour vérifier si une valeur se trouve dans une liste de valeurs possibles.
Les méthodes pour simuler "IN" dans Excel
Plusieurs approches sont possibles pour simuler l'opérateur "IN" dans Excel en combinaison avec la fonction SI. Nous allons explorer les plus courantes :
- Utilisation de la fonction OU (OR)
- Utilisation de la fonction RECHERCHEV (VLOOKUP) ou EQUIV (MATCH)
- Utilisation de la fonction SOMMEPROD (SUMPRODUCT) et -- (double négation)
Méthode 1 : Utiliser la fonction OU (OR) avec SI
La fonction OU (OR) renvoie VRAI si au moins une des conditions qu'elle évalue est VRAIE. Elle est parfaite pour tester si une valeur correspond à l'une des valeurs d'une liste. Combinée à la fonction SI, elle permet de renvoyer un résultat en fonction de cette appartenance.
Exemple pratique avec OU et SI
Imaginons que vous ayez une colonne A contenant des noms de fruits et que vous souhaitiez vérifier si un fruit est soit "Pomme", soit "Banane", soit "Orange".
La formule serait la suivante :
=SI(OU(A1="Pomme";A1="Banane";A1="Orange");"Oui";"Non")
Explication :
OU(A1="Pomme";A1="Banane";A1="Orange"): Cette partie évalue si la valeur en A1 est égale à "Pomme", "Banane" ou "Orange". Si au moins une de ces conditions est vraie, la fonction OU renvoie VRAI.SI( ... ;"Oui";"Non"): La fonction SI utilise le résultat de la fonction OU. Si la fonction OU renvoie VRAI (c'est-à-dire, si A1 contient "Pomme", "Banane" ou "Orange"), alors la fonction SI renvoie "Oui". Sinon, elle renvoie "Non".
Avantages et inconvénients de la méthode OU
Avantages :
- Simple à comprendre et à mettre en œuvre pour un petit nombre de valeurs.
- Ne nécessite pas de table de référence externe.
Inconvénients :
- Peut devenir fastidieux et difficile à gérer si la liste des valeurs possibles est longue. La formule devient très longue et difficile à lire.
- Difficile à maintenir : si vous devez ajouter ou supprimer une valeur dans la liste, vous devez modifier la formule.
Méthode 2 : Utiliser RECHERCHEV (VLOOKUP) ou EQUIV (MATCH) avec SI
Une autre approche consiste à utiliser les fonctions RECHERCHEV (VLOOKUP) ou EQUIV (MATCH) pour rechercher la valeur dans une table de référence. Si la valeur est trouvée, cela signifie qu'elle appartient à la liste. Ces fonctions sont ensuite combinées avec la fonction SI.
Exemple pratique avec RECHERCHEV et SI
Supposons que vous ayez une liste de codes produits valides dans une plage de cellules, par exemple, de D1 à D10. Vous souhaitez vérifier si le code produit en A1 figure dans cette liste.
La formule serait la suivante :
=SI(ESTNUM(RECHERCHEV(A1;D1:D10;1;FAUX));"Oui";"Non")
Explication :
RECHERCHEV(A1;D1:D10;1;FAUX): Cette partie recherche la valeur de A1 dans la plage D1:D10. L'argumentFAUXassure une correspondance exacte.ESTNUM(...): La fonction RECHERCHEV renvoie une erreur (#N/A) si elle ne trouve pas la valeur. La fonction ESTNUM vérifie si le résultat de RECHERCHEV est un nombre (c'est-à-dire, si la valeur a été trouvée). Elle renvoie VRAI si c'est un nombre, et FAUX sinon.SI( ... ;"Oui";"Non"): La fonction SI utilise le résultat de ESTNUM. Si ESTNUM renvoie VRAI (c'est-à-dire, si la valeur a été trouvée), alors la fonction SI renvoie "Oui". Sinon, elle renvoie "Non".
Exemple pratique avec EQUIV et SI
La fonction EQUIV (MATCH) renvoie la position d'une valeur dans une plage. On peut l'utiliser de la même manière :
=SI(ESTNUM(EQUIV(A1;D1:D10;0));"Oui";"Non")
Explication :
EQUIV(A1;D1:D10;0): Cette partie recherche la valeur de A1 dans la plage D1:D10. L'argument0assure une correspondance exacte.ESTNUM(...): Comme avec RECHERCHEV, EQUIV renvoie une erreur (#N/A) si elle ne trouve pas la valeur. La fonction ESTNUM vérifie si le résultat de EQUIV est un nombre (c'est-à-dire, si la valeur a été trouvée). Elle renvoie VRAI si c'est un nombre, et FAUX sinon.SI( ... ;"Oui";"Non"): Identique à l'exemple avec RECHERCHEV.
Avantages et inconvénients de la méthode RECHERCHEV/EQUIV
Avantages :
- Plus facile à gérer qu'avec OU si la liste des valeurs est longue, car la liste est stockée dans une plage de cellules séparée.
- Plus facile à maintenir : pour ajouter ou supprimer une valeur, il suffit de modifier la plage de cellules contenant la liste.
Inconvénients :
- Nécessite une table de référence externe.
- Peut être moins performant qu'avec OU si la table de référence est très grande.
Méthode 3 : Utiliser SOMMEPROD (SUMPRODUCT) et -- (double négation) avec SI
La fonction SOMMEPROD (SUMPRODUCT) peut être utilisée pour compter le nombre d'occurrences d'une valeur dans une plage. La double négation (--) convertit les valeurs booléennes (VRAI/FAUX) en valeurs numériques (1/0). Combinée à la fonction SI, cette méthode permet de simuler l'opérateur "IN".
Exemple pratique avec SOMMEPROD et SI
Reprenons l'exemple de la liste de codes produits valides dans la plage de cellules D1:D10. La formule serait la suivante :
=SI(SOMMEPROD(--(A1=D1:D10))>0;"Oui";"Non")
Explication :
A1=D1:D10: Cette partie compare la valeur de A1 à chaque cellule de la plage D1:D10. Elle renvoie un tableau de valeurs booléennes (VRAI ou FAUX).--(A1=D1:D10): La double négation convertit les valeurs booléennes en valeurs numériques. VRAI devient 1, et FAUX devient 0.SOMMEPROD(...): La fonction SOMMEPROD additionne les valeurs numériques résultantes. Si la valeur de A1 est présente dans la plage D1:D10, la somme sera supérieure à 0. Sinon, la somme sera égale à 0.SI( ... >0 ;"Oui";"Non"): La fonction SI vérifie si la somme est supérieure à 0. Si c'est le cas (c'est-à-dire, si la valeur a été trouvée), alors la fonction SI renvoie "Oui". Sinon, elle renvoie "Non".
Avantages et inconvénients de la méthode SOMMEPROD
Avantages :
- Relativement simple à comprendre et à mettre en œuvre.
- Peut être plus performant que RECHERCHEV/EQUIV dans certains cas.
Inconvénients :
- Moins intuitive que les autres méthodes.
- Peut être moins performant que OU si la liste est très courte.
Choisir la bonne méthode en fonction de vos besoins
Le choix de la méthode dépend de plusieurs facteurs, notamment :
- La longueur de la liste des valeurs possibles : Pour une petite liste, la méthode OU est la plus simple. Pour une liste plus longue, RECHERCHEV/EQUIV ou SOMMEPROD sont plus appropriées.
- La nécessité de maintenir la liste des valeurs : Si la liste des valeurs est susceptible de changer fréquemment, RECHERCHEV/EQUIV est la meilleure option, car elle permet de modifier la liste facilement en modifiant la plage de cellules.
- La performance : Pour une liste très courte, la méthode OU peut être la plus rapide. Pour une liste plus longue, SOMMEPROD peut être plus performant que RECHERCHEV/EQUIV dans certains cas, mais cela dépend de la taille de la plage et de la complexité des données.
Bonnes pratiques et erreurs à éviter
- Utiliser des références absolues ($) pour les plages de cellules : Lorsque vous utilisez RECHERCHEV/EQUIV ou SOMMEPROD, assurez-vous d'utiliser des références absolues pour les plages de cellules contenant la liste des valeurs possibles. Cela évitera que les références ne se décalent lorsque vous copiez la formule vers d'autres cellules.
- Vérifier les types de données : Assurez-vous que les types de données de la valeur à rechercher et de la liste des valeurs possibles sont cohérents. Par exemple, si vous recherchez un nombre, assurez-vous que la liste des valeurs possibles contient également des nombres, et non du texte.
- Gérer les erreurs : Les fonctions RECHERCHEV et EQUIV renvoient une erreur (#N/A) si la valeur n'est pas trouvée. Utilisez la fonction
SIERREURpour gérer ces erreurs et afficher un message plus convivial. - Commenter vos formules : Si vos formules sont complexes, ajoutez des commentaires pour expliquer leur fonctionnement. Cela facilitera la maintenance et la compréhension de vos feuilles de calcul.
Conclusion
Bien qu'Excel ne dispose pas d'une fonction "IN" native, il est possible de simuler ce comportement en combinant la fonction SI avec d'autres fonctions telles que OU, RECHERCHEV, EQUIV ou SOMMEPROD. Le choix de la méthode dépend de la longueur de la liste des valeurs possibles, de la nécessité de maintenir la liste et des considérations de performance. En maîtrisant ces techniques, vous pourrez créer des formules Excel plus puissantes et flexibles, capables de réaliser des tests d'appartenance complexes et d'automatiser vos tâches.