Monsieur Excel
Pour tout savoir faire sur Excel !

25 février 2011

La seconde syntaxe de recherche()

La fonction recherche() originale posait des problèmes à de nombreux utilisateurs d’Excel. Certains avaient du mal à comprendre que c’était dans la colonne la plus à droite, ou la ligne la plus basse, de la matrice que l’on prenait le résultat. D’autres se plaignaient que – avec son algorithme – on était obligé de trouver le résultat à droite (matrice verticale) ou en bas (matrice horizontale) : ils voulaient pouvoir trouver un résultat à gauche, ou au-dessus…

Donc, une bonne dizaine d’années plus tard, Microsoft a ajouté une seconde syntaxe à cette fonction : =recherche(valeur;vecteur de recherche;vecteur de résultat).

Dans l’exemple ci-dessous, par rapport à celui présenté il y a quatre jours, nous avons interverti les blocs A2:A10 et B2:B10, en mettant les tarifs avant les poids.

La formule de la cellule B13 montre l’application directe de la nouvelle syntaxe aux deux vecteurs de la matrice verticale. Le nouvel algorithme est le suivant : on identifie dans le vecteur de recherche la position de la dernière valeur inférieure ou égale à la valeur cherchée (cf. tracé en bleu) ; ensuite, on note la position de cette valeur puis, dans le vecteur de résultat, on identifie (cf. tracé en rouge) la valeur ayant cette position.

Rien n’empêche alors, si l’on a l’esprit un peu tordu – serait-ce donc mon cas ? – de faire comme en B13, où nous effectuons la recherche dans le vecteur vertical B3:B10 et trouvons le résultat dans le vecteur horizontal A17:H17 !

Cela marche parfaitement bien, mais ce n’est pas vraiment conseillé pour la lisibilité de vos modèles :)

21 février 2011

Tutorial sur la fonction recherche()

Nous avions déjà présenté la fonction recherche() dans une série d’articles débutant le 18 mars 2006, donc dans les six premiers mois de ce blog. C’est dire l’importance de cette fonction, qui était une des 32 fonctions de Visicalc (contre environ 347 pour Excel 2007), l’ancêtre de tous les tableurs, né en 1979.

Il est temps de vous proposer un nouveau tutorial pour cette fonction fondamentale. Nous reprenons le même exemple, actualisé au tarif postal actuel. Vous devez poster une lettre de 116 grammes, en utilisant le tarif en A2:B10.

Certes, vous pourriez utiliser en B13 la formule suivante :

Cela peut fonctionner, mais cette solution présente trois inconvénients :

● on ne peut pas imbriquer plus de 7 fonctions ;

● si l’on crée une nouvelle catégorie, par exemple pour 160 grammes, la formule doit être modifiée : elle n’est donc pas flexible ;

● une formule comme cela est particulièrement indigeste : elle est donc difficilement auditable.

La solution est d’utiliser la formule affichée en commentaire de la cellule A13. Mais on peut aussi utiliser la formule en A14 qui, elle, fait référence au tableau transposé en A16:B17. La syntaxe de la fonction recherche() est donc : =recherche(valeur;matrice). Nous verrons, dans un autre article, qu’une seconde syntaxe – ajoutée des années plus tard – est aussi possible.

L’algorithme de la fonction recherche()

L’algorithme utilisé par la fonction recherche() est illustré par le tracé en traits fléchés, en bleu. Excel compare 116 gr. à 0 gr., puis à 20 gr., puis à 50 gr., jusqu’à buter sur une valeur supérieure à la valeur de référence (dans notre cas, nous butons sur 250 gr.). Quand elle trouve cette première valeur supérieure, elle remonte d’une ligne puis par complètement à droite dans cette ligne, jusqu’à la fin de la matrice de référence.

Si la matrice est horizontale, Excel va vers la droite jusqu’à rencontrer la première valeur supérieure à la valeur de référence puis revient alors une colonne à gauche et descend jusqu’en bas de la matrice pour trouver le résultat.

Remarque 1 – Si la matrice est carrée, elle est considérée comme « verticale ».

Remarque 2 – Cet algorithme suppose que la première colonne (matrice verticale) ou la première ligne (matrice horizontale) est constituée de valeurs croissantes.

Remarque 3 – Attention ! Si vous insérez des colonnes à l'intérieur de la matrice, une matrice « verticale » peut devenir « horizontale », auquel cas les formules de recherche ne donneront plus les bons résultats...

16 février 2011

Des conseils parfois améliorables…

Je suis parfois surpris, en consultant les réponses données sur des sites d’assistance pour Excel, de voir quelles sont les réponses proposées aux lecteurs…

Dans l’exemple ci-dessous, vous avez dans le bloc B2:G8 des « 1 » qui apparaissent à divers endroits. L’objectif est de retrouver dans le bloc J2:L6 les numéros d’identification correspondant aux colonnes dans lesquelles on trouver le premier « 1 », puis le second, et enfin le troisième.

La solution proposée consiste à créer une colonne intermédiaire (la colonne I) dans laquelle on regroupe les identifiants de toutes les colonnes contenant un « 1 », grâce à la formule suivante, entrée en I2 et reproduite vers le bas :

=si(B2=1;B$1;"")&si(C2=1;C$1;"")&si(D2=1;D$1;"")&si(E2=1;E$1;"")&si(F2=1;F$1;"")&si(G2=1;G$1;"")

Les formules des cellules des colonnes J à L sont reproduites dans la copie d’écran ci-dessus.

Tout cela me semble hélas bien compliqué…

Je peux vous proposer de détruire la colonne I ci-dessus et d’entrer dans la nouvelle cellule I2, en la recopiant ensuite en I2:K6, la formule :

=si(nb($B2:$G2)>colonne()-9;H2+equiv(1;decaler($A2;0;H2+1):$G2;0);"")

Remarque 1 – Plus besoin d’une colonne intermédiaire comme la colonne I de la première solution ! En outre, avantage non négligeable, il y a la même formule dans tout le bloc I2:K6 !

Remarque 2 – Notez l'utilisation de la fonction decaler() pour déplacer le point de départ du vecteur de recherche : vous avez là une fonctionnalité vraiment très puissante !

12 février 2011

Indexer une sélection d’une base

Le problème à résoudre aujourd’hui est un problème auquel de nombreux développeurs Excel ont été confrontés. Il s’agit, après avoir sélectionné certains enregistrements grâce à un filtre dans une base Excel, de numéroter les résultats de la sélection.

Dans l’exemple ci-dessous, nous avons sélectionné dans une base tous les garçons (colonne A) et nous voulons créer (colonne H) des numéros d’ordre pour les lignes retenues par la sélection :

La solution que nous vous proposons ci-dessous est adaptée d’une solution proposée par un développeur VBA anglais, Pascal Daulton.

Elle fonctionne pour la base sur laquelle vous avez un filtre actif, dans la feuille active :

08 février 2011

Finance 3.1 – Un an après…

Dans mon article du 8 novembre 2009, je vous annonçais la création de la société Finance 3.1, une société de conseil spécialisée dans la finance d'entreprise et dans le contrôle de gestion, autour de l’utilisation d’Excel.

A l’époque, certaines personnes m’avaient exprimé leur scepticisme devant l’idée selon laquelle il pouvait être intéressant de créer une société de conseil mettant en exergue sa maîtrise d’Excel.

Finance 3.1 vient de clôturer sa première année complète avec un chiffre d’affaires en 2010 double de celui prévu dans le business plan initial. Elle a été une des lauréates du Réseau Entreprendre en 2010 (34 lauréats sur 300 candidats). L’effectif de la société est passé de deux personnes fin 2009 à quatre personnes fin 2010.

Nous avons eu l’honneur d’aider un certain nombre de clients durant l’année, dont des interventions importantes auprès de trois entreprises du CAC 40 : Electricité de France, France Telecom et BNP Paribas. Nous profitons de cette occasion pour remercier ces clients, et tous les autres aussi, de la confiance qu’ils nous ont témoignée !

Une intervention notable…

Chez un de nos clients, une consolidation devait être réalisée de façon régulière à partir d’une quarantaine de fichiers Excel. La solution utilisée avant notre arrivée était une application contenant 400 pages de code Visual Basic (!!!) et dont le temps de traitement était de 4 heures en moyenne.

Après que nous ayons effectué un re-engineering total de l’application, le temps de traitement est tombé à moins de 5 minutes (50 fois moins), avec un code Visual Basic de moins de 8 pages (50 fois moins). En outre, la comparaison avec les deux dernières consolidations n’a montré qu’un écart entre les deux jeux de résultat ; l’erreur qui a été découverte, après analyse de cet écart, provenait de l’ancienne version !

Remarque – On peut noter qu’avec 400 pages de code Visual Basic, l’ancienne application était de fait quasiment impossible à auditer. Elle contenait d’ailleurs cette erreur qui n’avait jamais été décelée.

Cet exemple prouve que – avec une grande maîtrise d’Excel – on peut obtenir en conseil des résultats que même de grands cabinets de conseil ne seraient pas en mesure d’obtenir. Il y a donc un sens à fournir un service de conseil en finance ou en contrôle de gestion reposant sur une forte expertise Excel :)

03 février 2011

Un calcul d’écart de temps

Le problème à résoudre est le suivant : vous disposez en colonne A d’une date de début et en colonne B de données concernant une heure de début et une heure de fin de travail, éventuellement le lendemain, sachant que la période de travail ne peut dépasser 18 heures.

L’objectif est de calculer le temps écoulé entre les deux heures indiquées.

On pourrait certes obtenir le résultat sans macro, mais au prix soit de colonnes supplémentaires pour des calculs intermédiaires, soit de formules particulièrement indigestes.

La formule en C2, reproduite vers le bas, est : =heures(B2).

La fonction macro ci-dessus est l’œuvre de David Smart, un expert renommé d’Excel.