Une utilisation de indirect()

Le problème suivant a été fourni par un lecteur du blog. Une entreprise doit suivre les activités de ses clients, avec un onglet par client. Chacun de ces clients peut passer au maximum deux commandes par jour. L’objectif est de regrouper dans un onglet de récapitulation la liste des commandes des clients.

Nous voyons dans l’écran ci-dessous la présentation de la récapitulation avec – en insert – une photo de l’onglet Lerouge.

La formule miracle, saisie en C4 et reproduite dans les colonnes C à E, est la suivante :

=SI($A4<>$A3;index(indirect(C$2&"!B:B");equiv($A4;indirect(C$2&"!A:A");0));
index (indirect(C$2&"!B:B");equiv($A4;indirect(C$2&"!A:A");0)
+equiv($A4;decaler(indirect(C$2&"!A1");
equiv($A4;indirect(C$2&"!A:A");0);0):indirect(C$2&"!A2000");0)))

Si vous avez bien compris les articles précédents sur les fonctions de recherche, vous n’aurez aucune difficulté à comprendre cette formule

Remarque – Vous pouvez noter en particulier que cette formule utilise 7 fois la fonction indirect() que nous avons étudiée il y a quatre jours…

rédigé par Hervé Thiriez | 0 commentaire(s)
14 avril 2011
Tutorial sur la fonction Indirect()

Pour terminer sur l’adressage dans Excel, après les fonctions recherche(), index() et equiv() puis, plus récemment, decaler(), nous étudions aujourd’hui la dernière des fonctions d’adressage, la fonction indirect().

Supposez qu’en B1, vous ayez récupéré (cf. formule reproduite en B8) un montant de dépense provenant d’un autre classeur. Vous souhaitez rendre cette recherche paramétrable, en vous gardant la possibilité de modifier le nom du classeur, celui de la feuille, ou l’adresse de la cellule concernée.

Cela se fait très bien en utilisant la solution proposée en B2 (cf. formule en B9), grâce à la fonction decaler(). L’argument de la fonction sert uniquement à reconstituer, caractère par caractère, la formule que nous avions en B1…

Remarque 1 – Si le classeur référencé dans la formule n’est pas ouvert, le résultat de la formule est le message d’erreur #REF !

Remarque 2 – Dans le cas ci-dessus, qu’il y ait $B$129 ou B129 en B6 revient strictement au même.

Syntaxe de la fonction

La syntaxe de la fonction est =decaler(référence[;A1]).

Si l’argument facultatif A1 est FAUX, la référence doit être de type L1C1.

S’il est VRAI ou absent, la référence doit être de type A1, c’est-à-dire une ou plusieurs lettres pour la colonne et un nombre pour la ligne.

rédigé par Hervé Thiriez | 0 commentaire(s)
10 avril 2011
Sécurisez-vous avec decaler() !

La fonction decaler() est particulièrement utile pour sécuriser les formules en les rendant plus flexibles. Nous en avons un excellent exemple ci-dessous avec le modèle de la Figure 1 : nous avons des valeurs en colonne A et nous calculons en colonnes B et C les pourcentages d’évolution d’une ligne à l’autre.

Formule de B2 : =A2/A1-1
Formule de C2 : =A2/decaler(A2;-1;0)-1

Cas de la suppression d’une ligne

Si par exemple nous détruisons la ligne 7, nous obtenons le résultat de la Figure 2. Pour la colonne C, aucun problème ! En revanche, la colonne B affiche un message d’erreur…

Cas de l’insertion d’une ligne

Si en revanche nous insérons une ligne au-dessus de la ligne 8, nous obtenons le résultat de la Figure 3.

Dès que l’on entre une valeur en A8 (cf. Figure 4), deux choses se passent : la colonne C reçoit automatiquement la bonne formule et elle affiche les bons résultats en C8 et C9.

Dans le cas de la colonne B, si l’on se contente de recopier la formule de B7 en B8, B8 affichera le bon résultat, mais B9 continuera à afficher le faux résultat 9,1% ! Pour éviter ce problème, il faut penser à recopier la formule de B7 jusqu’en B9…

En guise de conclusion…

La formule de la colonne C est plus longue et plus complexe, mais bien plus sûre que celle de la colonne B, que ce soit lors de la suppression ou lors de l’insertion d’une ligne.

De la même façon, quand on effectue la somme d’un bloc avec en ligne 1 des titres et en ligne 5 le total, la formule classique =somme(A2:A4) n’offre aucune élasticité : toute ligne insérée entre la ligne 1 et la ligne 2, ou entre la ligne 4 et la ligne 5 sera ignorée dans le total.

Si en revanche on utilise dans ce cas la formule =somme(A1:decaler(A5;-1;0)), on dispose alors d’une formule totalement élastique qui prend automatiquement en compte les valeurs de lignes insérées juste au-dessus de la ligne 2 ou de la ligne 5.

Comme quoi il faut parfois utiliser des formules plus lourdes pour bénéficier d’un taux de sécurité maximal !

rédigé par Hervé Thiriez | 0 commentaire(s)
05 avril 2011
Tutorial sur la fonction decaler()

Pour faire suite aux tutoriaux récents sur les trois fonctions fondamentales d'Excel, recherche(), index() et equiv(), il est approprié de jeter maintenant un coup d’œil sur la fonction decaler() qui a elle aussi une grande utilité.

La syntaxe est =decaler(référence;nb_lig;nb_col[;hauteur[;largeur]])

… où les crochets droits représentent des arguments facultatifs…

… et où hauteur et largeur sont le nombre de lignes et de colonnes à ramener…

La référence peut être la référence à une cellule ou à un bloc rectangulaire de cellules.

L’utilisation habituelle de la fonction decaler()

La syntaxe la plus courante est la formule =decaler(adresse;nb_lig;nb_col), grâce à laquelle on récupère le contenu de la cellule placée nb_lig en dessous et nb_col à droite de la cellule dont l’adresse est indiquée en premier argument.

Nous voyons une utilisation tout à fait classique de cette syntaxe dans les articles « Introduction à la fonction Decaler() » du 5 janvier 2007 et
« Paiement à 15 ou à 45 jours » du 9 janvier 2007, dont je vous conseille vivement la lecture.

Utiliser decaler() en deux dimensions

Il y a deux façons d’utiliser la fonction decaler() en deux dimensions :

● soit en prenant comme premier argument la référence à un bloc de cellules plutôt qu’à une cellule unique

● soit en utilisant les arguments facultatifs hauteur et largeur.

Ainsi, =decaler(A1:B3;2;3) renvoie la matrice D3:E5.

Si l’on entre cette formule normalement dans une cellule, on obtient le message #VALEUR !

Si l’on entre cette formule matriciellement (donc avec [Ctrl]-[Maj]-[Entrée]) dans une cellule, on obtient la valeur de D3.

Si enfin on l’entre matriciellement cette formule dans un bloc de trois lignes et deux colonnes, on récupère le contenu de la matrice A1:B3.

Maintenant si, dans un bloc de trois lignes et deux colonnes, vous entrez matriciellement la formule =decaler(A1;2;3;3;2), vous obtenez exactement le même résultat !

Donc, =decaler(A1:B3;2;3) et =decaler(A1;2;3;3;2) sont totalement équivalents, ce qui fait que je ne vois pas bien l’intérêt des deux derniers arguments, sauf peut-être dans une macro dans laquelle on n'a pas envie de s'embêter avec des lettres comme identificateurs de colonnes …

Appel aux lecteurs du blog !

Un lecteur pourra peut-être nous montrer une utilisation intelligente de la double double dimension, c’est-à-dire un exemple intéressant où le premier argument représente une matrice et où les deux arguments facultatifs sont utilisés (avec des valeurs différentes de 1 !)

rédigé par Hervé Thiriez | 6 commentaire(s)
01 avril 2011
L’heure et la date au pied levé

On peut à tout moment entrer dans une cellule la date du jour avec la combinaison [Ctrl]-; ou l’heure avec la combinaison [Ctrl]-:.

La date est bien celle du jour, mais à 0:00.

L’heure, quant à elle, ne tient pas compte des secondes : elle se situe automatiquement au début de la minute active.

Il se peut que vous souhaitiez disposer de la date avec l’heure, c’est-à-dire la valeur de =maintenant() au lieu de celle de la formule =aujourdhui().

De même, il se peut que vous souhaitiez avoir un tampon avec l’heure précise, à la seconde près, plutôt que l’heure arrondie au début de la minute.

Pour cela, il suffit de mettre dans votre classeur de macros personnelles les deux macros suivantes puis de leur associer les raccourcis [Ctrl]-m pour
« maintenant » et [Ctrl]-h pour l’heure.

Remarque – Il n’est hélas pas possible de leur accorder les raccourcis [Ctrl]-; ou [Ctrl]-: car les codes doivent être des lettres...

rédigé par Hervé Thiriez | 0 commentaire(s)
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 ?

rédigé par Hervé Thiriez | 2 commentaire(s)
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

rédigé par Hervé Thiriez | 0 commentai[tr][/tr]