Monsieur Excel
Pour tout savoir faire sur Excel !

28 mars 2011

La bonne syntaxe des recherches…

Nous avons déjà, à de multiples reprises, souligné la mauvaise qualité de l’aide d’Excel. Aujourd’hui, nous nous intéressons aux syntaxes proposées par Excel quand on tape le nom d’une fonction suivi d’une parenthèse ouvrante. Nous le ferons par rapport aux trois fonctions de recherche étudiées depuis le 21 février.

Une remarque générale

Les crochets encadrent des arguments facultatifs. Là, Microsoft a toujours faux en écrivant « ; [argument] » là où la bonne syntaxe est « [;argument] » : en effet, si l’argument n’est pas utilisé, cela n’a aucun sens de mettre le « ; ». D’ailleurs, dans certains cas, ce « ; » isolé provoquera une erreur !

La notion erronée de « valeur proche »

La référence à « valeur_proche » en B5 est totalement erronée : quand cet argument est VRAI, ce ne sont pas les valeurs proches (en dessous ou au-dessus, de façon symétrique) qui sont acceptées, mais seulement les valeurs inférieures ou égales à la valeur cherchée !

Les trois syntaxes de la fonction index()

Excel propose deux syntaxes pour la fonction index(). Certes, on pourrait dire que la troisième (cf. B18) se trouve en prenant la syntaxe de B13 sans l’argument facultatif. Mais on pourra alors rétorquer que, dans ce cas, la syntaxe à droite de B10 représente une réponse unique et absolue.

Donc, en toute logique, il fallait proposer trois syntaxes sans élément facultatif – comme je le fais en B11, B15 et B18 – soit une seule, comme à la droite de B10. Mais en proposer précisément deux, comme le fait Microsoft, n’a aucun sens…

Vecteur, matrice, zone, tableau, référence ???

Sur le plan de la précision en matière de vecteurs, matrices..., Microsoft nous offre un grand n’importe quoi en utilisant ces cinq termes à peu près au hasard et – de toute façon – sans la moindre cohérence apparente.

Ainsi, en B9, apparaît « no_zone » qui signifie réellement « no_matrice ». On ne parle de « zone » nulle part ailleurs dans l’aide…

En B5, on parle de « table_matrice » qui pourtant ne diffère en rien de la
« matrice » référencée en B13.

En B20, on parle de « tableau_recherche ». L’utilisateur pourrait de bonne foi croire qu’une matrice peut être utilisée à cet endroit : s’il le fait, il obtient une erreur, car la fonction equiv() ne fonctionne qu’avec un vecteur et aucune autre forme de matrice, ou zone, ou tableau, ou référence…

En guise de conclusion…

Cela fait plus de 20 ans que j’ai indiqué ces problèmes à Microsoft.

Peut-être ces erreurs seront-elles enfin corrigées après ma mort ?

24 mars 2011

Les autres arguments d’equiv()

Nous avons vu il y a quatre jours comment utiliser equiv() avec « 0 » en troisième argument, pour trouver la position, dans le vecteur, de la première valeur identique à la valeur cherchée.

L’argument « +1 »

Quand on utilise comme troisième argument la valeur « +1 », ou quand il n’y a pas de troisième argument, equiv() renvoie la position de la dernière valeur inférieure ou égale à la valeur cherchée.

Cela suppose en général que le vecteur contienne des valeurs croissantes, mais il n’y a en fait aucune obligation en ce sens. Et il y a des applications concrètes où je l’utilise sur un vecteur non trié…

L’algorithme est alors le même que celui utilisé par recherche() ou par recherchev() sans quatrième argument, ou avec VRAI. On descend jusqu’à trouver la première valeur supérieure à la valeur cherchée, on remonte d’un cran…

L’argument « -1 »

Quand on utilise comme troisième argument la valeur « -1 », equiv() renvoie la position de la dernière valeur supérieure ou égale à la valeur cherchée.

Cela suppose en général que le vecteur contienne des valeurs décroissantes, mais il n’y a en fait aucune obligation en ce sens. Et il y a des applications concrètes où je l’utilise sur un vecteur non trié…

Correspondances avec recherche()

En fait, les fonction recherche() et recherchev() ou …h() sont équivalentes à un index d’equiv.

Si l’on appelle vect_1 le premier vecteur vertical et vect_2 le dernier vecteur vertical d’une matrice verticale ou carrée, les formules suivantes sont équivalentes :

D’une part :
=recherche(valeur;matrice)
=recherche(valeur;vect_1;vect_2)
=index(vect_2;equiv(valeur;vect_1))

D’autre part :
=recherchev(valeur;matrice;n;faux)
=index(vect_2;equiv(valeur;vect_1;faux))

… où n est égal à colonne(vect_2)-colonne(vect_1)+1

20 mars 2011

Découverte de la fonction equiv()

Après les tutoriaux récents sur les fonction recherche() et index(), il ne nous reste plus qu’à attaquer aujourd’hui la dernière des trois glorieuses, la fonction equiv(). Nous avons ajouté à notre tableau antérieur une colonne K où des valeurs aléatoires de 1 à 9 sont calculées à l’aide de la formule =ent(1+9*alea()).

Le but du jeu est, après avoir identifié en H9 la valeur cherchée (le 7), à trouver en H10 le numéro de la première ligne de la colonne K où l’on trouve la valeur cherchée.

Pour cela, la syntaxe de la fonction equiv() est =equiv(valeur;vecteur;0).

Ici, la formule utilisée est donc =equiv(H9;K:K;0).

L’algorithme de la fonction equiv avec l’argument final « 0 » est le même que celui du recherchev() avec FAUX : on descend le vecteur jusqu’au moment où l’on trouve la valeur cherchée. Dès qu’on la trouve, la fonction renvoie la position de cette valeur dans le vecteur. Si on ne la trouve pas, elle renvoie #N/A.

Remarque 1 – Il y a deux autres valeurs possibles pour le troisième argument, « +1 » (ou rien, ce qui revient au même) ou « -1 ». Nous en parlerons dans le prochain article.

Remarque 2 – Comme pour le recherchev() avec FAUX, l’ordre du vecteur n’a pas la moindre importance, il n’a donc aucun besoin d’être trié en valeurs croissantes.

Remarque 3 – Avec en H11 la formule =index(K:K;H10) on récupère la valeur cherchée, sauf bien entendu si H10 renvoie #N/A. Donc =equiv(valeur;vecteur;0) trouve la position de la valeur dans le vecteur, alors que =index(vecteur;position) trouve la valeur de la position dans le vecteur : ces deux fonctions sont étroitement liées !

16 mars 2011

Formations du mois de mai

Vous trouverez ci-dessous la liste des formations que je propose pour le mois de mai. Chacune de ces formations est limitée à huit participants, avec un ordinateur par personne. Vous pourrez télécharger la description de ces formations en cliquant sur leur intitulé :

- Modélisation avec Excel (2 jours) : les mercredi 11 et 18 mai 2011.

- Découverte de Visual Basic (2 jours) : les vendredi 13 et 20 mai 2011.

- La modélisation du risque (Crystal Ball) : le mardi 10 mai 2011.

Les séminaires Transition optimisée vers Office 2007 et Utilisation de Crystal Ball Pro ne sont pas proposés en inter lors de cette session.

Ces trois formations sont organisées en association avec la société EuroDécision.

Avec la formation Modélisation avec Excel, je garantis de transformer en deux jours tout utilisateur moyen d'Excel en « power user », c'est-à-dire à l'amener au niveau des 5% des meilleurs utilisateurs d'Excel.

Les cinq formations peuvent être animées en intra dans votre entreprise et – le cas échéant – personnalisées grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.

Références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.

14 mars 2011

L’argument « 0 » d'index()

Une particularité peu connue de la fonction index() est que l’on peut utiliser le
« 0 » à la place de n’importe lequel des trois arguments possibles, pour récupérer un vecteur ou une matrice.

Nous voyons ci-dessous trois exemples d’utilisation de cette fonctionnalité, les formules saisies dans chaque bloc étant entrées en commentaire du coin supérieur gauche du bloc. Toutes ces formules ont été saisies en matriciel pour chaque bloc, donc avec la combinaison [Ctrl]-[Maj]-[Entrée]. Le nom « Jules » correspond à l’ensemble des trois matrices à fond jaune.

En G1:I3, nous avons récupéré le contenu de toute la troisième matrice de Jules, avec donc un argument 0 pour la ligne et 0 pour la colonne. Comme cette troisième matrice ne comporte que deux lignes et deux colonnes, et que nous avons entré la formule pour un bloc de trois lignes et trois colonnes, il y a des #N/A dans le résidu…

Dans le bloc G5:H5, nous avons récupéré la seconde ligne de la troisième matrice.

Dans le bloc G7:G8, enfin, nous avons récupéré la troisième colonne de la seconde matrice.

Si, dans une cellule unique, nous entrons – sans validation matricielle ! – la formule =index(Jules;0;0;3), nous obtenons comme résultat #VALEUR !

Ceci dit, si nous cliquons alors dans la barre de formule, puis faisons [F9] pour évaluer la formule, la barre de formule affiche le résultat ={71.6;5.20}, ce qui prouve qu’il s’agit bien de la troisième matrice…

09 mars 2011

Tutorial sur la fonction index()

La fonction index(), à l’instar de la fonction recherche(), était une des 32 fonctions de Visicalc, l’ancêtre de tous les tableurs. C’est dire l’importance de cette fonction ! En fait, quand on considère les trois fonctions recherche (avec ses cousines H et V), index et equiv, on s’aperçoit que seules deux d’entre elles sont indispensables, la troisième pouvant être synthétisée à partir des deux autres.

Dans l’exemple ci-dessous, nous avons 75 valeurs tirées au hasard dans le bloc A1:E5, grâce à la formule =ent(100*alea()). En H1:H3, nous tirons des valeurs au hasard de 1 à 3 grâce à la formule =ent(1+3*alea()). Le but du jeu est de récupérer en H5:H7 la valeur de la colonne D en ligne H1, la valeur de la matrice A1:E5, en ligne H1 et colonne H2, et enfin la valeur en ligne H1, colonne H2, matrice H3 de la zone multiple encadrée sur fond jaune, nommée « Jules ».

Nous voyons dans la copie d’écran ci-dessus, en commentaire, les trois formules utilisées pour récupérer les valeurs demandées. Nous constatons que les trois syntaxes possibles de la fonction index() sont les suivantes :

● =index(vecteur; position)

● =index(matrice; n°lig; n°col)

● =index({mat}; n°lig; n°col; n°mat)

Remarque – La syntaxe ci-dessus est bien plus claire, et plus complète, que la syntaxe foireuse proposée par Excel quand on saisit « =index( » dans une cellule, à savoir :

● =index(matrice; no_lig; [no_col])

● =index(réf ; no_lig; [no_col]; [no_zone])

05 mars 2011

RechercheH et V avec « FAUX »

La grande différence entre l’exemple du fichier de personnel utilisé il y a quatre jours et l’exemple de frais postaux des 21 et 25 février est que, dans le cas du fichier de personnel, les valeurs de code intermédiaires correspondent à des erreurs. Si en effet vous entrez « Jules » comme prénom, au lieu de recevoir un message d’erreur, vous obtiendrez les résultats de Jacques Lerose. Ce n’était pas le cas pour les frais postaux où, par exemple, on pouvait avoir pour un tarif de 0,95 € tous les colis allant de 20 g. inclus à 50 g. exclus !

Si l’on voulait trouver le nom de famille en E15 à partir du prénom en E14, tout en signalant les erreurs éventuelles, il fallait utiliser une formule du type : =si(E14=recherche(E14;D3:D11);recherche(E14;D3:E11);"Erreur !")

Il fallut attendre de nombreuses années encore avant que, pour résoudre ce problème, une seconde syntaxe ne soit introduite à chacune de ces deux nouvelles fonctions : =recherchev(valeur;matrice;n°col;exact) et =rechercheh(valeur;matrice;n°lig;exact).

Si l’argument « exact » est absent ou VRAI, la fonction rechercheH ou V fonctionne selon le même algorithme que la fonction recherche(). Si en revanche ce quatrième argument est FAUX, un algorithme totalement différent est utilisé.

Voici ce qu’il donne pour rechercheV : on compare la valeur aux valeurs de la première colonne en commençant par le haut de la colonne ; et on descend, tant que l’on n’a pas trouvé la valeur cherchée. Si l’on parvient à la fin de la colonne sans l’avoir trouvée, la fonction renvoie #NA ! Sinon, elle s’arrête dès qu’elle trouve la valeur (à sa première occurrence, s’il y en a plusieurs) et renvoie alors le contenu de la colonne n°col de la matrice.

Remarque 1 – La conséquence immédiate de l’utilisation de rechercheV (resp. H) avec FAUX en quatrième argument est que la première colonne (resp. ligne) n’a plus besoin d’avoir des valeurs croissantes !

Remarque 2 – Compte tenu de ce nouvel algorithme, s’il y a plusieurs « Gilles » dans la liste, c’est toujours le premier que l’on obtiendra quand le quatrième argument sera FAUX. Alors que ce sera toujours le dernier avec VRAI ou sans quatrième argument…

01 mars 2011

RechercheH() et RechercheV()

A peu près à la même période que la seconde syntaxe de recherche(), les utilisateurs d’Excel ont eu droit à deux nouvelles fonctions, « cousines » de recherche() : rechercheH() et rechercheV().

Le premier objectif de ces deux fonctions était de résoudre le problème des utilisateurs qui ne comprenaient pas que c’était la dernière colonne ou ligne de la matrice qui fournissait le résultat.

Le second objectif était de ne plus faire dépendre la recherche de la forme de la matrice : rechercheV effectue toujours une recherche verticale et rechercheH toujours une recherche horizontale.

Les syntaxes de ces fonctions sont =recherchev(valeur;matrice;n°col) et =rechercheh(valeur;matrice;n°lig). Le troisième argument indique le n° (dans la matrice) de la colonne ou de la ligne qui fournira le résultat.

L’algorithme utilisé par ces fonctions est le même que celui de la fonction recherche(), présenté le 21 février dernier.

Nous voyons ci-dessus un exemple avec une liste de prénoms, de noms et de salaires. L’objectif est, après avoir saisi un prénom en E14, d’obtenir le nom de famille en E15 et le salaire en E16. Nous voyons en commentaire les formules qui ont été utilisées à cet effet.

Remarque 1 – Nous notons que la liste des prénoms respecte bien l’ordre alphabétique : c’est indispensable pour que les fonctions recherche() ou rechechev() fonctionnent bien dans cet exemple.

Remarque 2 – Les deux fonctions font référence à la même matrice D3:F11 : ce n’est pas gênant car le troisième article, en spécifiant la colonne concernée, corrige le tir…

Remarque 3 – Il se trouve qu’il y a deux « Gilles » dans cet exemple. Selon l’algorithme décrit dans l’article du 17 février, c’est toujours le dernier
« Gilles »
qui ressortira avec les formules indiquées ci-dessus.

Remarque 4 – Si, pour le salaire, nous avions par exemple utilisé la formule =recherchev(E15;E3:F11;2), cela n’aurait pas marché car la colonne E n’est pas rangée en ordre alphabétique.

Nous parlerons dans le prochain article du possible quatrième argument de ces deux fonctions, qui est arrivé quelques années plus tard encore…