Une utilisation de SommeProd()

Nous avons une liste de personnes, dont les noms se trouvent en colonne A, avec leur type de poste (colonne B) et leur département (colonne C).

Nous souhaitons compter le nombre de personnes correspondant à chaque combinaison possible de poste et de département :

Une belle formule pour atteindre cet objectif est la formule suivante, saisie en G2 puis recopiée vers le bas : =sommeprod(--($B$2:$B$11=E2);--($C$2:$C$11 =F2))

On peut se demander à quoi cela peut bien servir d’avoir deux signes "-" consécutifs et non pas un signe "+" qui, mathématiquement, est équivalent. Ce qui est vrai en mathématique n’est pas forcément vrai en Excel ! Voyons pourquoi…

Quand on évalue $B$2:$B$11=E2, on obtient : {FAUX;FAUX;VRAI;FAUX;FAUX;VRAI;VRAI;FAUX;VRAI;VRAI}.

Quand on évalue -($B$2:$B$11=E2), on obtient : {0;0;-1;0;0;-1;-1;0;-1;-1}.

Et enfin, --($B$2:$B$11=E2) donne : {0;0;1;0;0;1;1;0;1;1} qui, multiplié par le vecteur {1;0;0;1;1;0;0;1;1;1} engendré par le second argument, donne le bon résultat ! CQFD…

Une petite histoire…

Un professeur de mathématiques expliquait à ses élèves : « Moins par Moins, cela donne Plus, mais Plus par Plus ne donnera jamais Moins. ».

Une voix sarcastique s’élève du fond de la classe : « Cause toujours – Tu m’intéresses ! »

L’add-in « Workbook Navigation »

Nous avons vu dans la dernière rubrique comment naviguer efficacement entre les feuilles d’un classeur.

L’add-in Workbook Navigation, une réalisation sympathique de Shailesh Shah, vous propose un certain nombre de fonctionnalités, dont en particulier – ce qui n’est pas une surprise, vu son nom – une commande facilitant la navigation entre classeurs et entre feuilles :

La barre d’outils de cet add-in affiche le nom et le chemin complet du classeur actif, puis une série de commandes dont en particulier la commande « Activer » (celle qui est inversée dans la barre d’outils ci-dessus) qui liste les noms de toutes les feuilles du classeur actif.

Voici ce que l’on peut obtenir suite à un clic dans le nom du classeur actuel actif :

Les abonnés au blog recevront en prime – lors de leur livraison bimestrielle – cet add-in tout à fait intéressant.

Navigation entre les feuilles

Quand un classeur comporte un nombre important de feuilles, il est parfois difficile de naviguer efficacement d’une feuille à l’autre, dans la mesure où le bas de l’écran Excel ne peut afficher les noms que d’un nombre limité de feuilles.

Cela est encore plus vrai quand les noms de ces feuilles sont longs, ce qu’il m’arrive fréquemment de constater dans les modèles de mes clients.

Pour afficher rapidement la liste des noms des feuilles, il faut faire un clic droit (appel de menu contextuel) dans l’icône la plus à gauche de la barre des noms de feuilles, celle que l’on distingue sous le coin inférieur gauche de la fenêtre, dans l’image ci-dessous.

On obtient alors la liste des feuilles et, si celle-ci ne tient pas intégralement dans la fenêtre – comme c’est ici le cas – on obtient la suite en activant la dernière commande, « Plus de feuilles… ».

On active une feuille en sélectionnant dans la fenêtre la commande qui porte son nom.

Remarque – Une façon rapide de naviguer entre des feuilles voisines consiste aussi à utiliser les raccourcis [Ctrl]-[PgUp] et [Ctrl]-[PgDn] pour atteindre respectivement la feuille précédente et la feuille suivante.

Un minimum qui ignore les « 0 »

Une lectrice m’a demandé récemment comment l’on pouvait obtenir le minimum d’une colonne en ignorant les valeurs nulles.

En effet, cette colonne est alimentée par une base de données extérieures, les valeurs originales ne sont jamais nulles mais – quand elles ne sont pas renseignées dans la base – la formule d’importation renvoie la valeur 0.

Bien entendu, chaque fois que je rencontre un problème de ce genre, j’imagine aussitôt une formule matricielle, c’est-à-dire validée avec [Ctrl]-[Maj]-[Entrée].

La première solution à laquelle j’ai pensé n’a pas marché. La formule matricielle était :
=min((A1:A10>0)*(A1:A10))

En revanche, la seconde solution était la bonne, grâce à une seconde formule matricielle :
=min(si(A1:A10>0;A1:A10;10^6))

Remarque 1 – Le dernier argument peut être n’importe quelle valeur supérieure aux valeurs usuelles de la colonne.

Remarque 2 – La présentation de la cellule C1 a été obtenue grâce au format personnalisé "Minimum : "0.

Jours ouvrables : formule simplifiée

Il y a quatre jours, nous avons présenté une formule permettant d’obtenir une liste de jours ouvrables, avec un espace pour séparer les semaines les unes des autres.

Cette formule fonctionnait très bien, mais n’était ni simple ni compacte.

On peut aboutir exactement au même résultat visuel en combinant une formule bien plus légère et un format conditionnel. L’astuce consiste à créer une formule qui saute automatiquement un jour du week-end et à masquer par format conditionnel le jour du week-end qui reste…

La formule à entrer en B3, puis à reproduire vers le bas, est :
=B2+1+(joursem(B2)=7)

Le format conditionnel de cette cellule est visible dans la copie d’écran ci-dessus. Nous avons simplement choisi une police blanche pour les cellules sélectionnées afin de les rendre invisibles.

Remarque – Un bénéfice indirect de cette solution est qu’elle se base sur une seule cellule de départ (en gras) alors que la formule précédente exigeait deux cellules de départ (en gras).

Une formule pour jours ouvrables

Vous souhaitez, à partir d’une date donnée, entrer une formule qui génère automatiquement une suite de jours ouvrables, avec une cellule vide pour chaque week-end :
Nous partons des deux dates marquées en gras. La formule miracle – entrée en A4 et reproduite ensuite vers le bas – est la formule suivante :
=choisir(1+(joursem(n(A3))=6)+(A3<>"");A2+3;1+A3;"")

Le premier argument de la fonction choisir() est :
■ 1 quand la cellule précédente est vide
■ 2 si la cellule précédente est un jour autre que le vendredi
■ 3 quand la cellule précédente tombe un vendredi

Remarque – Notons la présence de la fonction n() qui permet d’éviter une erreur de calcul en renvoyant 0 quand son argument correspond à une cellule contenant un texte vide. Elle nous permet d’économiser un si()…

Le re-engineering de vos modèles

Dans les deux derniers articles, nous avons vu une macro de 18 instructions, puis son remplacement par une macro de deux instructions seulement. On peut donc faire de sérieuses économies de place mémoire et de temps calcul avec un reengineering des macros.

Une bonne part de mon activité de consultant revient à prendre des modèles existants et à les modifier – d’une part pour les rendre plus flexibles, plus sûrs et plus sexy – d’autre part pour les rendre plus performants, grâce à une dimension réduite et un temps de calcul accéléré.

La réduction de la taille d’un classeur ne résulte pas seulement dans une économie de place et une facilité de transport améliorée : il faut savoir que, quand la taille d’un classeur Excel dépasse les 20 Mo, on ne peut plus avoir totalement confiance dans sa capacité à bien effectuer tous les calculs nécessaires sans erreur. Dans certains cas, par exemple, le lancement du calcul par [F9] ne marche plus ; parfois même, le « calcul forcé » obtenu normalement avec la combinaison de touches [Ctrl]-[Alt]-[F9] refuse lui aussi de fonctionner…

Réduction de taille de classeur

Le tableau ci-dessous illustre, pour quelques exemples précis que j’ai vécus en tant que consultant, la taille du classeur original et celle du classeur après re-engineering.

Remarque – Dans les trois premiers cas, le modèle amélioré faisait tout ce que faisait le modèle original, plus de nouvelles fonctionnalités.

Une autre possibilité de gain en efficacité est celui de sanofi-aventis où j’ai pu remplacer 150 classeurs par un classeur unique. J’ai eu un autre cas du même genre avec la CCIP, où un grand nombre de classeurs a été remplacé par un classeur unique.

Gain en vitesse de calcul

Bien entendu, une réduction significative de la taille d’un classeur aboutit aussitôt à une amélioration de son temps de calcul.

Mais il y a aussi des techniques spécifiques pour réduire de façon significative le temps de calcul d’un classeur, même sans modification sensible de sa taille. Je suis ainsi souvent arrivé à économiser 50% du temps de calcul des modèles que j’avais améliorés.

J’ai rencontré aussi des cas extrêmes : pour un modèle de Lilly France dont le temps de calcul était de 1’30", je suis descendu à moins d’une seconde de temps de calcul, soit une économie de plus de 99% du temps de calcul. Il faut dire que, dans ce modèle, il y avait une macro effectuant un traitement compliqué, que j’ai pu remplacer par une formule directe !