janvier
2011
Un article de Pierre Fauconnier
Lors d’une de mes formations EXCEL, je discutais avec des participants lorsque j’entendis l’un d’eux affirmer que le ou exclusif (xor) était impossible à formuler en Excel.
En effet, les fonctions ET – qui renvoie VRAI lorsque toutes les conditions sont remplies – et OU – qui renvoie VRAI lorsqu’au moins une condition est remplie- sont proposées par Excel, mais pas XOU ou autre appellation qui renverrait VRAI lorsqu’une et une seule condition est remplie.
Il est pourtant possible de formuler le ou exclusif en Excel, avec un brin d’astuce.
Démonstration
Si l’on veut que toutes les conditions soient remplies, l’utilisation de ET() s’impose, comme dans l’illustration suivante.
Nous avons VRAI en F2 car les conditions mentionnées comme paramètres de la fonction ET() sont toutes rencontrées.
Une seule condition non remplie amène Excel à renvoyer FAUX comme résultat de la fonction ET().
Lors de l’utilisation de OU(), il faut que au moins une condition soit remplie, comme dans l’illustration suivante ou seule la zone correspond à la condition mentionnée en paramètres.
Mais OU() renvoie également VRAI lorsque plusieurs conditions, voire toutes, sont remplies, comme ci-dessous ou la zone et le mois correspondent aux critères.
Mais pas de XOU pour le OU EXCLUSIF. Comment faire si je veux vérifier qu’une et une seule condition soit remplie? Excel ne nous fournit pas – encore – la fonction du OU exclusif. Il va donc falloir un peu d’astuce pour réaliser cela.
Pour expliquer la démarche, je vais construire un petit tableau de résultat pour la première ligne du tableau. En G1 :I1, je place les critères. Je peux alors renvoyer VRAI ou FAUX en ligne 2, en postulant simplement les égalités (forme courte du SI() lorsque la valeur renvoyée doit être une valeur logique).
J’obtiens donc VRAI partout lorsque les trois conditions sont remplies, ce qui correspond à la fonction ET(). Pour tester cela, je dois juste savoir que VRAI et FAUX sont convertis en valeurs numériques par Excel lorsque je les utilise au sein d’opérations mathématiques. VRAI vaut 1 et FAUX vaut 0.
Je peux donc écrire ET() sans utiliser la fonction, en multipliant les valeurs de G2:I2, puisque 1 et 0 sont respectivement neutre et absorbant pour la multiplication. Tester le ET() revient donc à tester que la multiplication renvoie 1.
Je teste le OU() selon un système similaire, puisuqe 0 est neutre pour l’addition. J’additionne les valeurs logiques et je teste que la somme est supérieure à 0.
Fort logiquement, je teste le OU exclusif en additionnant les valeurs logiques et en vérifiant que la somme renvoie 1.
Je pourrai donc tester de la même façon si X conditions sont remplies, en plaçant à droite de l’égalité le X souhaité.
Cette façon de pratiquer prend toute sa valeur lors de calculs sur l’ensemble du tableau. Si je souhaite la somme des montants pour les lignes qui répondent à une seule condition, je pourrai bien entendu réaliser la somme des valeurs reprises dans la colonne du OU exclusif.
Bien sûr, SOMMEPROD() me permet cela sans devoir construire les colonnes intermédiaires (J’ai déjà abordé SOMMEPROD() dans ce billet).
Je calculerai la somme des montants des lignes qui remplissent toutes les conditions comme ceci
Si j’utilise l’addition et que je teste si la somme est positive non nulle, je calculerai la somme des montants des lignes qui répondent au moins à une condition.
Il me suffit de vérifier que l’addition des valeurs logiques renvoie 1 pour récupérer la somme des montants pour les lignes qui répondent à une et une seule condition.
CQFD…
Connaissiez-vous SOMMEPROD(). Souhaitez-vous un tutoriel sur cette fonction?
Articles récents
- Formation EXCEL: Analyse des données sans formules grâce aux tableaux croisés dynamiques
- Access : Sélectionner une valeur selon l’une ou l’autre de ses propriétés
- Références structurées: absolues ou relatives?
- Excel 2013, un relooking commercial ou une révolution copernicienne?
- Outook sur Exchange mutualisé