Pierre Fauconnier

Office365 offre quelques fonctions exclusives. Entendez par là qu'elles ne sont disponibles sur votre Excel 2016 QUE si vous possédez une licence Office365.

Parmi celles-ci, MAX.SI.ENS() et MIN.SI.ENS qui fonctionnent comme SOMME.SI.ENS (j'en parle dans ce billet et aussi dans celui-ci)

Exemple

Soit à rechercher le montant le plus élevé des ventes pour un pays donné (par exemple, la Belqique). MAX.SI.ENS() vient à bout de ce problème très facilement (je répète: Exclusivité 365!!).



Bien entendu, outre son exclusivité 365, cette fonction souffre des mêmes limitations que les autres fonctions .ENS:

• Critérisation uniquement sur la valeur partielle ou totale des cellules de la colonne du critère;
• Obligation de reconstituer le critère lorsque l'on utilise les jokers pour la recherche partielle (voir ce billet);
• Obligation de reconstituer le critère lorsque l'on utilise les opérateurs de comparaison (voir ce billet);
• Toutes les conditions exprimées doivent être rencontrées: une condition non remplie suffit à elle seule à exclure la ligne du calcul.

Et si on n'a pas Office 365? On fait COMMENT??

On utilise une formule matricielle... Non, ne vous sauvez pas! Il ne s'agit pas de matrices comme lors de vos cours de maths, il s'agit d'une syntaxe et d'une validation particulière de formule pour dire à Excel de travailler en boucle sur les lignes d'un tableau ou d'une table de données.

Et à nouveau, comme pour d'autres problèmes, c'est l'arithmétique booléenne qui nous vient en aide



Hum... Pierre, tu peux développer un peu le fonctionnement de cette fonction?

Et puis déjà, c'est quoi ces accolades de part et d'autre de la formule?

Les accolades, elles sont ajoutées par Excel lorsque l'on valide la formule avec CTRL+SHIFT+ENTER (il ne faut pas les saisir vous-même!). C'est cette validation particulière qui précise à Excel qu'il doit travailler en matricielle, c'est-à-dire en boucle ^(*)

Vous vous souvenez? Dans un précédent billet, je vous disais que les valeurs booléennes VRAI et FAUX étaient transtypées en valeurs numériques lors de leur utilisation dans des opérations arithmétiques (transtypage = modification du type de la donnée, par exemple dans ce cas-ci, de valeur logique ou booléenne vers valeur numérique).

Si nous développons le calcul, nous voyons que nous pouvons, ligne par ligne, tester que le pays de la ligne du tableau correspond au pays choisi, ce qui renvoie VRAI ou FAUX. VRAI et faux sont transtypées en valeurs numériques lorsqu'on multiplie ces valeurs logiques par les montants, de sorte que, 1 étant neutre et 0 étant absorbant pour la multiplication, on se retrouve avec des montants ou des 0, selon que la condition est rencontrée ou pas...

Dès lors, la valeur la plus grande de la colonne Montant si pays choisi correspond bien à la meilleure vente pour le pays choisi...




La validation matricielle (Ctrl+Shift+Enter) permet de se passer de ces (parfois nombreuses) colonnes supplémentaires pour récupérer le résultat répondant aux x conditions attendues, en effectuant les calculs ligne par ligne et en tenant compte des résultats intermédiaires pour extraire, dans notre exemple, le maximum conditionnel.



La formule du maximum matriciel permet donc de se passer de MAX.SI.ENS trop limitatif à mon goût, et peut-être aussi à celui de l'oncle Anatole... Ou simplement de disposer d'une solution parce que MAX.SI.ENS n'est pas disponible dans la version d'Excel utilisée...




^(*) Travailler en boucle signifie qu'Excel va évaluer la formule pour chaque ligne du tableau et garder les résultats de chaque ligne pour les évaluer plus tard au sein d'une matrice

Ah oui, pour l'oncle Anatole, ça ne résout pas tous les cas... Bon, ben... On en parle dans un prochain billet?