Sélection de lignes avec des mots

Le problème auquel nous nous attaquons aujourd’hui est original. On dispose dans une zone appelée « Mots » (ici, le bloc A14:A16) d’une liste de mots que l’on recherche. On souhaite, dans la base en colonnes A et B, n’afficher que les lignes dans lesquels au moins un des éléments de la liste « Mots » est présent.

Nous voulons donc que les lignes contenant dans la colonne A ou la colonne B l’un des trois mots de notre liste soient les seules qui demeurent visibles. Voici le résultat souhaité :

La macro permettant d’atteindre notre objectif n’est pas trop compliquée. Notez en particulier l’utilisation du « With » qui simplifie ensuite les références puisqu’il suffit alors de débuter par un point pour identifier le bloc des colonnes A et B.

Si on me cherche, on me trouve...

Nous avons vu récemment comment fonctionnait la fonction Cherche(), en notant d’ailleurs qu’elle ne faisait aucunement la distinction entre les majuscules et les minuscules.

En fait, Excel dispose d’une fonction tout à fait similaire qui – elle – fait bien la différence entre les majuscules et les minuscules. Nous pouvons le voir dans les lignes 6 et 7 du tableau suivant qui complète celui de mon dernier message :

Cette fonction est horriblement mal nommée : Trouve(). Il est en effet totalement ridicule d’appeler Cherche() et Trouve() deux fonctions qui – à un détail près – jouent exactement le même rôle !

La syntaxe de la fonction Trouve() est exactement la même que celle de la fonction Cherche(), avec un troisième argument facultatif identifiant la position à partir de laquelle on souhaite débuter la recherche.

Ceci dit, pour une fois, ce n’est pas la traduction en français de la fonction qui est en cause, car en anglais on utilise Search() et Find(), qui soulèvent tout à fait le même problème.

M’enfin, comme dirait Gaston Lagaffe, on ne peut pas refaire le monde...

Découverte de la fonction Cherche

Il y a quatre jours, nous avons utilisé la fonction Cherche(), sans avoir le temps de vous la présenter sérieusement. Nous l’avions déjà utilisée, sans plus la commenter, dans nos messages du 2 et du 18 décembre 2005.

La syntaxe de la fonction est la suivante : =cherche(texte_1;texte_2[;position])

Remarque 1 – Nous utilisons ici la convention informatique usuelle selon laquelle les arguments facultatifs sont présentés entre crochets.

La fonction trouve la position du premier endroit où l’on trouve la chaîne de caractères texte_1 dans la chaîne texte_2. Quand le troisième argument est présent, il indique à partir de quelle position dans texte_2 on démarre la recherche de texte_1.

Nous voyons ci-dessus comment cette fonction nous sert à identifier dans le texte de la cellule A1 la position du premier « e » - en cellule A3 – puis du second – en cellule A5. En A4, nous trouvons la position du premier « e » à partir de la onzième position. Les formules de A3:A5 ont été reproduites en B3:B5.

Remarque 2 – Notons au passage que la fonction Cherche() ne fait pas la différence entre les majuscules et les minuscules. En revanche, elle est tout à fait sensible aux lettres accentuées, comme c’est aussi le cas pour les fonctions Recherche() et Equiv().

Moyenne de notes alphabétiques

Dans de nombreuses institutions d’enseignement supérieur, les élèves reçoivent des notes alphabétiques, ce qui ne simplifie pas le calcul de moyennes.

Supposons par exemple que les notes possibles soient A, B, C, D et F correspondant respectivement à des valeurs de 4, 3, 2, 1 et 0.

On souhaite effectuer, pour une série de matières sans pondération, la moyenne de ces notes :

En C1, nous avons entré la formule =5-cherche(B1;"ABCDF") qui a été ensuite recopiée en dessous. Cela nous permet de calculer en C11 la simple moyenne de C1:C9.

En B12, nous avons entré une formule matricielle, donc validée avec [Ctrl]-[Alt]-[Entrée], qui nous permet de calculer directement le même résultat sans avoir besoin de la colonne C, la formule étant : =moyenne(5-cherche(B1:B9; "ABCDF")).

Mais pourquoi passer par une formule matricielle quand on peut faire à la fois plus rapide et plus simple ?

La meilleure solution, utilisée en B13, est la formule : =sommeprod(5-cherche(B1:B9;"ABCDF"))/nbval(B1:B9) !

Des crochets pour “Evaluate”...

Pour faire suite à notre présentation de la fonction « Evaluate » du VBA il y a quatre jours, nous allons apprendre encore une nouveauté aujourd’hui...

En fait, cette fonction possède un « raccourci » tout à fait original, les crochets... Testez par exemple la macro suivante sur l’exemple de notre dernier message, en cliquant sur [F8] chaque fois que vous voulez avancer d’une ligne dans l’exécution de la macro :

Conseil - Pour suivre l'évolution dans la macro tout en en observant l'effet sur la feuille de calcul, réduisez la fenêtre du VBA. Vous aurez alors "le beurre et l'argent du beurre".

Vous constaterez que la macro sélectionne d’abord le bloc B1 :B5, puis en affiche la valeur moyenne...

Là, pour le coup, [average(B1:B5)] est bien plus rapide à taper que application.average(B1:B5) !

Evaluate, une fonction originale

Les programmeurs ignorent souvent la fonction Evaluate, une fonction originale du VBA. Prenons l’exemple ci-dessous, dans lequel cinq valeurs aléatoires sont tirées et où l’on affiche leur moyenne :


La moyenne est calculée directement en B7 grâce à la formule =moyenne(B1:B5). Mais on l’obtient aussi dans une fenêtre de message grâce à la macro suivante :

Sub Moyenne()
MsgBox "Moyenne : " & Evaluate("average(B1:B5)")
End Sub

Certes, nous aurions pu remplacer :
Evaluate("average(B1:B5)")
par :
Application.average(B1:B5)
mais n’est-ce pas tellement plus original avec la fonction Evaluate ?

Créer une transposition dynamique

Reprenons l’exemple présenté le 31 juillet, dans lequel nous présentions le collage spécial avec transposition.

Le collage spécial que nous avions présenté, qu’il s’agisse du collage spécial d’un ensemble de valeurs, ou du collage des valeurs d’un ensemble de formules, est une photo à un instant donné du tableau original. Si l’on modifie une valeur du tableau original, sa correspondante dans le tableau transposé n’est pas actualisée.

Supposons que nous souhaitions obtenir un tableau transposé lié de façon dynamique au tableau original, afin d’obtenir par exemple le résultat suivant :

Il faut dans ce cas entrer en A9:G14 la formule suivante :
=index($A$1:$F$7;colonne();ligne()-8)

Il ne reste alors plus qu’à effacer la formule de A9 qui, autrement, afficherait un « 0 » tout à fait inesthétique...

Remarque – Notons au passage que le bouton « Coller avec liaison », placé en bas du dialogue « Collage spécial », se grise dès que l’on coche la case
« Transposé ». On ne peut donc pas coller avec liaison tout en transposant, ce qui rend indispensable l’utilisation d’une formule telle que ci-dessus.