Monsieur Excel
Pour tout savoir faire sur Excel !

28 janvier 2016

Rechercher/Remplacer (c)

Dans les deux articles précédents, nous avons vu un certain nombre de choses relatives à la commande Rechercher/Remplacer d’Excel. Dans ce troisième et dernier article, nous vous fournissons divers renseignements importants pour tirer le maximum de cette commande.

Recherche d’un joker, soit « ? » soit « * »

Comment donc faire pour rechercher précisément les occurrences de l’un des deux jokers, soit « ? » qui est censé remplacer un caractère, soit « * » qui est censé remplacer une chaîne de caratères de longueur indéterminée ?

Heureusement pour vous, Microsoft a prévu cette éventualité !
Il vous faut tout simplement utiliser, selon le cas,  la recherche de « ~? » ou de « ~* ».

Remarque 1 – Cela ne résout pas le problème soulevé dans le premier article de cette série, qui est que les jokers marchent parfaitement bien dans le champ « Rechercher », mais pas dans le champ « Remplacer », où ils sont pris à la lettre.

Les options de la commande « Remplacer »

Quand on utilise la commande « Remplacer », on voit un bouton « Options » qui apparaît dans la fenêtre. Dès que l’on clique dans ce bouton, on obtient la première copie d’écran ci-dessous.


Cela nous offre de multiples possibilités. Si l’on déroule un des menus déroulants « Format », on obtient la seconde copie d’écran. Cela signifie qu’il est possible d’appliquer la commande à n’importe quelle caractéristique de formatage : Nombre, Alignement, Police, Bordure, Remplisage ou Protection.
Nous en voyons un exemple d’application ci-dessous…

Changement de couleur avec le champ « Remplacer par » vide…

Supposons par exemple que nous souhaitions remplacer, dans une sélection, ou dans l’onglet actif, ou dans un ensemble d’onglets sélectionné, toutes les cellules contenant un remplissage jaune par un remplissage bleu ciel.

Pour cela, il suffit de sélectionner ce sur quoi l’on veut agir, puis de demander via la commande « Format » d’effectuer le remplacement demandé.

Remarque 2 – Dans une telle situation, il est important de laisser les champs « Rechercher » et « Remplacer par » vides. Sinon, on retomberait dans le problème soulevé en Remarque 1.

Quand on effectue ce changement de remplissage, il n’affecte que les cellules dont le remplissage était naturellement jaune. Si par exemple certaines cellules avaient un remplissage jaune par la faute d’un format conditionnel, ce remplissage ne serait – assez logiquement – pas affecté.





23 janvier 2016

Rechercher/Remplacer (b)

Dans l’article précédent, je disais que les commandes « Rechercher » et « Remplacer » étaient fréquemment sous-utilisées. Nous avions donné en particulier l’exemple du remplacement de « = » par « $$ » pour déplacer ou coller un bloc sans que les coordonnées des cellules ne s’adaptent.

Aujourd’hui, nous verrons d’autres utilisations de ces deux commandes.

Recherche sur plusieurs onglets

On peut facilement sélectionner plusieurs onglets en utilisant les raccourcis [Ctrl]-clic (pour ajouter ou ôter un onglet) et [Maj]-clic pour sélectionner une série d’onglets.

Quand on utilise alors les commandes « Rechercher » ou « Remplacer », l’opération s’effectue sur l’ensemble des onglets ainsi sélectionnés.

Recherche des cellules avec des liens externes

Quand la commande « Modifier les liens » du bloc « Connexions » de l’onglet « Données » n’est pas grisée, cela signifie que le classeur est lié à d’autres classeurs.

Dans la pratique, on tombe parfois sur de tels liens qui ont été créés par erreur. Si vous héritez un jour d’un classeur dans lequel il y a des liens inappropriés, vous pouvez avoir envie d’identifier rapidement les cellules avec ce genre de lien.

Pour cela, il suffit de sélectionner l’ensemble des onglets potentiellement coupables, et de rechercher « [ ». Dans toute formule de lien externe, il y a en effet un crochet ouvrant avant le nom du classeur référencé.

Réduction de la taille du classeur et/ou du temps de calcul

Quand, dans un onglet, il y a de nombreuses répétitions de formules lourdes, cela peut engendrer une taille de classeur vraiment excessive.

Si ce classeur n’alimente, par des liens externes, aucun autre classeur, vous disposez d’une solution simple. Dans cet onglet, remplacez « = » par « $$ »avant de lancer l’enregistrement du classeur. Il vous suffira alors de réaliser l’opération inverse lors de l’ouverture du classer.

Pour automatiser cette tâche, il suffit d’enregistrer ces deux opérations et de les lier à des macros qui s’exécuteront automatiquement à l’enregistrement et à l’ouverture du classeur.

Il m’est déjà arrivé, quand un onglet était très « lourd » à calculer et que ses résultats n’impactaient aucun autre onglet, de créer une macro à exécuter dès que l’on quittait l’onglet et qui remplaçait « = » par « $$ ». Une autre macro réalisait l’opération inverse dès que l’onglet était activé… Les gains de temps de calcul et de place mémoire que l’on peut ainsi obtenir sont parfois impressionnants !

Il m’est aussi arrivé, quand un onglet était très « lourd » à calculer et que ses résultats n’impactaient aucun autre onglet, d’écrire deux macros. La première, quand on quittait l’onglet, détruisait tout les formules lourdes reproduites dans plein de cellules. La seconde, quand on activait l’onglet, recréait ces formules…

17 janvier 2016

Rechercher/Remplacer (a)

Deux des commandes les plus sous-utilisées d’Excel sont les commandes « Rechercher » et « Remplacer ». Pour ma part, je m’en sers assez souvent…

Ces commandes sont accessibles dans le ruban « Accueil », onglet « Edition », via le menu déroulant « Rechercher et sélectionner ».

Mais elles sont bien plus directement accessibles encore via les raccourcis [Ctrl]-f (pensez à Find) pour « Chercher » et [Ctrl]-h pour « Remplacer ».

Quand seule une cellule est sélectionnée, la commande s’applique à l’ensemble de l’onglet. Si au moins deux cellules sont sélectionnées, la commande s’applique seulement aux cellules sélectionnées.

Astuce – Quand je souhaite que la commande ne s’applique qu’à la cellule active, je sélectionne en même temps une cellule vide – avant d’exécuter la commande ! – pour éviter que la commande ne s’applique à l’onglet entier.

Les jokers de « Rechercher » et « Remplacer »

Ces deux commandes vous permettent d’utiliser des jokers : « ? » pour un caractère et « * » pour une série de caractères de longueur indéterminée.

Ainsi, en entrant « ???? », on cherchera tout contenu avec exactement quatre caractères. De façon plus complexe, en entrant « *ab*f?? », on cherchera tout contenu avec n’importe quoi d’abord, pui « ab » puis, plus tard, un « f » suivi de deux caractères exactement.

Remarque 1 – Faites bien attention car, si vous cherchez une chaîne d’une certaine longueur dans des cellules contenant des nombres, ce sont les nombres saisis qui comptent en non les nombres affichés. En effet, le formatage peut modifier le nombre de chiffres affichés.

Le piège des jokers

Il y a un bug dans la commande « Remplacer par : ».

Si par exemple on demande à Excel de remplacer « *ab?c* » par « *ht* », on suppose que l’on va remplacer toute chaîne de caractères contenant n’importe quoi suivi de « ab », puis un caractère puis « » puis n’importe quoi par le premier n’importe quoi, suivi de « ht » puis le second n’importe quoi.

En fait, il n’en est rien : le résultat final sera littéralement « *ht* » ! Cela signifie que les « * » (ou les « ? ») sont pris à la lettre dans le champ « Remplacer par : » !

Remarque 2 – Dans les bugs, on fait en général la distinction entre les bugs « de programmation » et les bugs « de conception ». Dans le premier cas, le programmeur s’est « planté ». Dans le second cas, c’est le cahier des charges qu’on lui a fourni qui est foireux. Dans le cas ci-dessus, je pense qu’il s’agit d’un bug de conception.

Utilité de la commande Rechercher/Remplacer

Il y a de nombreux usages possible de la commande « Remplacer » pour modifier des formules après un déplacement ou une copie. Nous allons en voir un exemple ci-dessous.

On peut par exemple ajouter des « $ » à certain endroits dans des formules pour que, après un déplacement ou une copie, certaines coordonnées ne soient pas modifiées par Excel. Il suffit alors, après le déplacement ou la copie, d’ôter par un Rechercher/Remplacer les « $ » excédentaires.

Une excellente technique, si l’on veut déplacer ou copier/coller un bloc de cellules sans que les coordonnées des cellules pointées par les formules ne se modifient, consiste à remplacer avant cette opération, dans le bloc original, le signe « = » par « $$ ». Une fois le transfert terminé, on remplacera partout dans l’onglet « $$ » par « = ».

                                                                                                                                                                   

12 janvier 2016

Serpents et échelles (g)

Un peu de retard... 

Désolé pour le retard, il a fallu digérer les fêtes...

Ceci dit, la série d'articles sur les serpents et échelles avait été publiée au rythme d'un article tous les 5 jours, ce qui devrait compenser...

La dernière questions des Serpents et échelles

Et voilà ! 

Nous devons repartir du modèle Serpents et échelles (e) puisque le second joueur repart de la case n°1. Nous reprenons ce modèle en utilisant les colonnes K à M de la façon suivante…


NDLR - L'éditeur tronquait les formules que j'avais indiquées initialement en réinterprétant mes signes "<" et ">". Il m'a hélas fallu passer par la copie d'écran ci-dessus pour résoudre le problème.

L’idée est que le compteur en colonne M passe à 1 quand on a été insensibilisé à la morsure du serpent.

La formule de L2 calcule la position où l’on arrive en évitant la morsure dans le cas où l’on est revenu plus bas que le point de départ et que l’antidote n’a pas encore été utilisé.


Nous voyons ci-dessus les résultats obtenus. Le tableau en bas à droite indique un résultat moyen de 42,15% ; nous sommes donc clairement dans la réponse (a) du QCM.

En guise de conclusion

Et voilà ! Nous avons utilisé Excel pour brillamment répondre à toutes les questions.

Comme j’adore les énigmes mathématiques et logiques, j’ai souvent utilisé Excel pour les résoudre, au point que j’envisage un jour – quand j’aurai du temps !!! – de publier un livre sur la résolution d’énigmes à l’aide d’Excel.

Il faudrait qu’il m’arrive quelque chose et que je sois alité durant un mois pour cela. Je ne me le souhaite pas vraiment :)

03 janvier 2016

Serpents et échelles (f)

Renouvellement de MVP Excel

L'année 2016 commence pour moi par une bonne nouvelle : j'ai été renouvelé par Microsoft comme MVP Excel, ce qui fait ma huitième consécration consécutive.

Serpent et échelles : la question n°4 

Cette fois-ci, nous cherchons à trouver sur quelle case il fait faire démarrer le joueur n°2 pour que ses chances de gagner soient les mêmes que celles du joueur n°1.

La solution que j’ai choisie est celle qui miminise les modifications à effectuer depuis le modèle de la question précédente.

J’ai entré en A5 le numéro de la cellule où débute le joueur n°2.

La formule de K1 est changée en =index($C$1:$C$34;J1+A5).

Le reste du modèle est inchangé…


Pour que vous puissiez voir la disctribution des résultats sur trois tirages consécutifs, j’ai mis en R3997:U4000 un tableau synthétisant les résultats de trois simulations pour chacun des cas. On constate aisément qu’il faut démarrer avec le joueur n°2 placé au départ sur la case 6 pour égaliser les chances.

Remarque – On aurait aussi pu faire une table avec trois colonnes de plus, une pour chaque position possible de démarrage pour le joueur n°2. Cela aurait eu l’avantage que l’on aurait comparé les quatre solutions possibles sur les mêmes tirages aléatoires. L’inconvénient est que cela aurait demandé un peu plus de travail, et aurait encore ralenti le fonctionnement du modèle.

La dernière question…

Voici enfin la dernière question posée lors de cet examen…

Pour égaliser les chances entre les deux joueurs, au lieu de faire démarrer le joueur n°2 sur une case avancée, on envisage de le rendre insensible à la première morsure de serpent qu’il subira.

Si l’on fait cela, quelle sera alors la probabilité que le joueur n°1 gagne ?

  • 42,5%
  • 46,5%
  • 49,5%
  • 52,5%