Monsieur Excel
Pour tout savoir faire sur Excel !

23 mars 2017

Quelle est ma version d’Excel ?

Parfois, nous avons besoin de savoir quelle version d’Excel nous utilisons, surtout quand nous utilisons un ordinateur non familier.

Une solution consiste à utiliser le menu Fichier, commande Aide. Nous obtenons alors le tableau représenté dans la partie droite de la copie d’écran ci-dessous.


Une autre solution revient à utiliser la macro ci-dessous, que nous avons associé (cf. copie d’écran) au bouton macro placé dans le coin supérieur gauche qui engendre le dialogue affiché en dessous et à droite du bouton :

Voici la macro en question :

Sub MyVersion()
    MsgBox "Version : " & Application.Version & vbLf & "Build : " & Application.Build
End Sub

Remarque – On peut d’ailleurs se demander pourquoi le « build » ne correspond pas exactement à ce qui est indiqué par la commande « Aide » du menu « Fichier ».

14 mars 2017

D’autres articles sur equiv()

La fonction equiv() est une véritable mine d’or et peut être utilisée pour résoudre un grand nombre de problèmes concrets. Voici quelques articles du blog qui vous permettront d’en savoir plus sur les possibilités offertes par cette fonction géniale.

Liste dynamique sans répétition
Le 16 juin 2013.

Dans cet exemple, la fonction equiv() sert à constituer de façon dynamique  une liste sans répétition de toutes les valeurs différentes observées dans un vecteur.

Un equiv() sur une matrice (a)

La fonction equiv() ne peut utiliser qu’un vecteur comme premier argument. Comment faut-il procéder quand on souhaite trouver une valeur dans une matrice au lieu d’un vecteur ?

La série d’articles suivante répond à cette question délicate. Depuis le 27 janvier 2014, avec Un élément dans une matrice jusqu’au 16 février 2014 avec Un equiv() sur une matrice (d) .

Recherche sur >255 caractères
Le 28 février 2015.

Récupérer une valeur d'un TCD
Le 15 novembre 2005.

Calcul du nombre de modalités
Le 6 octobre 2016.

Nous utilisons là la fonction equiv(), en association avec la fonction frequence(), pour calculer – sans formule matricielle ! – le nombre de modalités (de valeurs différentes) dans un vecteur.

Une utilisation originale d’equiv()

Comme je l’ai indiqué dans le premier article du 27 février, le premier de cette petite série, Excel suppose – quand on utilise les troisièmes arguments +1 et -1, que les valeurs sont respectivement en ordre croissant et décroissant.  Mais ce n’est pas une obligation !

Ainsi, =equiv(10^6;D:D;1) trouve la position de la dernière valeur entrée en colonne D, sous réserve que toutes les valeurs soient inférieures à 10^6. Sinon, il suffit d’augmenter ce nombre…

De même, =equiv(-10^6;D:D;-1) trouve la position de la dernière valeur entrée en colonne D, sous réserve que toutes les valeurs soient supérieures à -10^6.

06 mars 2017

Les trois arguments d’equiv()

Dans l’article précédent, nous avons présenté la fonction equiv(). Dans celui-ci, nous analysons ce qu’elle donne avec chacun de des trois arguments.

L’argument 0

Quand le troisième argument est 0, Excel renvoie la position dans le vecteur de recherche de la première occurrence de la valeur cherchée. Dans notre exemple, la formule de B16, reproduite sous forme de texte dans la cellule B17, dit bien que c’est en ligne 6 que l’on trouve le premier 7.

Remarque – On peut se demander pourquoi Microsoft a choisi de prendre par défaut le « 1 » comme troisième argument alors que c’est le « 0 » qui est de loi le plus utile.

L’argument 1

Quand le troisième argument est 1, Excel renvoie la position de la dernière valeur inférieure ou égale à la valeur cherchée. Cela suppose en théorie que le vecteur dans lequel on effectue la recherche soit trié en ordre croissant, mais il n’y a là aucune obligation. Dans notre exemple, la formule de D16, reproduite sous forme de texte dans la cellule D17, dit bien que c’est en ligne 9 que l’on trouve la dernière valeur inférieure ou égale à 7.


Si l’on ajoute les valeurs de la colonne D, on peut constater que l’equiv() avec un 1 en troisième argument donne exactement le même résultat qu’un simple recherche(), avec la formule reproduite en D18.

L’argument -1

Quand le troisième argument est 1, Excel renvoie la position de la dernière valeur supérieure ou égale à la valeur cherchée. Cela suppose en théorie que le vecteur dans lequel on effectue la recherche soit trié en ordre décroissant, mais il n’y a là aucune obligation. Dans notre exemple, la formule de G16, reproduite sous forme de texte dans la cellule G17, dit bien que c’est en ligne 9 que l’on trouve la dernière valeur inférieure ou égale à 7.

On ne peut hélas pas, dans ce cas, trouver un équivalent avec la fonction recherche() car celle-ci ne sait pas gérer une liste de valeurs décroissantes. Mais on peut par exemple utiliser à cet effet la formule matricielle suivante : =max(si(F1:F13>=G15;ligne(G1:G13);0)). Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

27 février 2017

La fonction equiv() : aide en ligne

La fonction equiv() est une des trois fonctions de recherche d’Excel : elle est particulièrement utile. Rappelons-en le principe :
=equiv(valeur_cherchée;vecteur;code)

La valeur cherchée est un texte ou une valeur numérique.
Quand le code est 0, equiv() renvoie la position de la première occurrence de la valeur cherchée dans le vecteur.
Quand le code est 1, equiv() renvoie la position de la dernière occurrence inférieure ou égale à la valeur cherchée dans le vecteur. Cela suppose en général que le vecteur soit classé en ordre croissant.
Quand le code est -1, equiv() renvoie la position de la dernière occurrence supérieure ou égale à la valeur cherchée dans le vecteur. Cela suppose en général que le vecteur soit classé en ordre décroissant.
Quand le code est absent, Excel se comporte comme s’il était égal à 1.


Là où l’aide d’Excel a tout faux…

Comme on le voit dans la photo n°1, Excel appelle le second argument tableau_recherche, ce qui laisse entendre qu’il s’agit d’une matrice. Ceci est renforcé par la photo n°2, où Excel parle clairement d’une « matrice de valeurs ou la référence à une matrice ». Le problème, c’est que si vous entrez une matrice (c’est-à-dire au moins deux lignes et deux colonnes), le résultat de votre equiv() devient un déprimant #NA!

Là où l’aide d’Excel vous induit en erreur…

Dans le bloc D4:D8, nous avons entré les formules reproduites en E4:E8. Dans mon modèle, la formule de D1, ainsi que celles des colonnes A et B, est : =ent(1+10*alea()).

Chaque fois que je lance un recalcul avec [F9], je constate que D5 et D6 affichent toujours le même résultat, idem pour D7 et D8.

D5:D6 montrent bien que, quand le troisième argument est absent, le code absent prend la valeur 1.

Mais on voit en D7:D8 que, quand il y a le « ; » sans code, le code absent prend alors la valeur 0 !

21 février 2017

Factorielle : une illusion d’optique !

Le problème suivant a été soulevé par le MVP Liam Bastik.

La formule de C4 est : =B4*C3
La formule de D4 est : =droite(C4;15)*1
La formule de E4 est : =droite(C4;19)*1

La question qui se pose est la suivante. Pourquoi, à partir de la ligne 24, obtient-on des résultats qui ont l’air tout à fait incohérents ?


Pour vous mettre sur la voie, calculons donc la valeur de C23 (avec [F9] dans la barre de formule) : nous obtenons 51090942171709400000.

Calculons à présent celle de C24 : 1,12400072777761E+21.

Le problème vient donc du passage à la notation exponentielle.

Le MVP Jan Karel Pieterse a suggéré pour D4 la formule =droite(texte(C4;"0");15).

Mais on peut encore raccourcir sa formule en éliminant les guillemets…

La solution la plus courte est donc : =droite(texte(C4;0);15)

16 février 2017

Position de la dernière occurrence

Dans les deux articles précédents, nous avons vu comment trouver la première occurrence, puis la seconde, puis la troisième.. d’une valeur ou d’un texte dans une table.

Pour trouver la position de la dernière occurrence, c’est beaucoup plus simple. Nous utilisons en D12 la formule suivante : =max(ligne(A2:A21)*(A2:A21=D1)).

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Notons que, si aucune occurrence n’a été trouvée, la cellule affichera « Ligne 0 », ce qui n’est pas particulièrement joli.

Ce serait plus joli d’afficher alors « Aucune ».


Beaucoup d’utilisateurs d’Excel vont alors construire une formule compliquée avec un si(), ce qui les amènera à écrire deux fois la formule.

Pour notre part, nous n’allons pas alourdir notre formule. Ce problème est résolu de façon bien plus esthétique avec le format personnalisé "Ligne "0;;"Aucune".

En effet, l’ordre des formats personnalisés est Positif;Négatif;Nul;Texte. Avec notre format, nous masquons les nombres négatifs et affichons « Aucune » quand la cellule vaut 0.

Remarque – De nombreuses formules de ce type peuvent être allégées de façon significative en ayant recours aux formats personnalisés !

10 février 2017

La énième occurrence, en direct

Dans l’article précédent, nous avons vu comment trouver la première occurrence, puis la seconde, puis la troisième.. à partir des positions des occurrences précédentes.

Parfois, on souhaite accéder directement à la troisième occurrence, ou à la septième, sans pour autant passer par les occurrences précédentes. Nous allons voir aujourd’hui comment atteindre cet objectif. Vous pouvez voir ce que cela donne dans le bloc D5:E10.

Nous entrons en colonne E le numéro de l’occurrence souhaitée et obtenons en colonne D le numéro de la ligne concernée. Mais ici chacune des formules de D5 à F10 est indépendante et autonome.


La formule de D5 est :
=sierreur(petite.valeur(si($A$1:$A$21=$D$1;ligne($A$1:$A$21));E7);"")
Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Pour comprendre cette formule, il suffit de voir que :
si($A$1:$A$21=$D$1;ligne($A$1:$A$21))
Donne le résultat suivant :
{FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;
FAUX;15;FAUX;FAUX;18;FAUX;FAUX;21}

On cherche alors la énième plus petite valeur et, comme les FAUX ne comptent pas dans cette recherche, on trouve bien la énième occurrence de la valeur recherchée.