Plus de 100.000 visites déjà !

J’ai créé le blog « Monsieur Excel » il y a un peu moins de deux ans, en octobre 2005.

Le lectorat du blog a été en croissance régulière, et il a presque doublé en un an, passant aujourd’hui même le cap des 100.000 visites depuis sa création.

Je tiens à remercier les lecteurs réguliers – et a fortiori les abonnés, particuliers et entreprises – de leur fidélité. Plusieurs entreprises sont devenues des partenaires depuis, grâce au blog, et nous avons travaillé ensemble à la conception de nombreux modèles originaux.

Je rappelle qu’il est toujours possible de prendre un abonnement depuis le début du blog, ce qui vous permet de bénéficier de la collection complète des conseils, modèles, macros et add-ins fournis aux abonnés.

Je n’ai pour le moment pas encore parlé de la dernière version d’Excel, ayant pour habitude – un peu égoïste, je l’avoue – de laisser à d’autres le travail de debugging (déverminage, si l’on veut respecter le vocabulaire officiel) que Microsoft confie généreusement à ses clients. Rassurez-vous, cela ne durera pas éternellement et je compte vous parler un peu de cette version dans les mois qui viennent.

En attendant, j’espère que vous continuerez à trouver dans ces colonnes de quoi nourrir votre esprit et améliorer votre maîtrise d’Excel !

Organisation aléatoire de matches

Une fédération sportive doit organiser de façon aléatoire une série de rencontres, sachant que chaque concurrent possède cinq équipes. Avec huit concurrents, nous avons les équipes 1-5, 6-10, 11-15, 16-20, 21-25, 26-30, 31-35 et 36-40. Ces rencontres doivent être organisées de façon qu’une équipe ne rencontre que des équipes concurrentes, et jamais deux fois la même.

La macro ci-dessus résout le problème. Il y a 700 combinaisons possibles, que nous engendrons par trois boucles imbriquées. Dans la colonne C, nous avons entré une valeur aléatoire servant à obtenir un tri aléatoire des 700 combinaisons. Et voilà un programme permettant d’engendrer les 700 rencontres possibles dans un ordre aléatoire.

Nous avons représenté les 700 points dans un graphe XY, ce qui permet de vérifier visuellement la bonne couverture des résultats.

Remarque – Nous avons utilisé la fonction Rnd() du Visual Basic. Nous aurions aussi pu entrer la fonction alea() d’Excel dans les cellules de la colonne C.

Mieux gérer les liens hypertexte

Deux problèmes surviennent souvent quand plusieurs cellules d’une feuille contiennent des liens hypertexte, la difficulté d’éditer une cellule contenant un lien hypertexte et la nécessité éventuelle de supprimer une série de liens hypertexte.

Editer une cellule avec un lien hypertexte

Quand le pointeur se trouve sur une cellule avec un lien hypertexte, comme ci-dessous sur la cellule A2, l’identification détaillée du lien apparaît, ainsi qu’un conseil disant qu’il faut maintenir le bouton enfoncé pour sélectionner la cellule sans activer le lien :

Cette solution est possible, mais c’est certainement la plus longue...

Nous pouvons vous proposer deux solutions plus rapides, soit de faire tout simplement un [Ctrl]-clic dans la cellule, soit de cliquer en B2 puis d’utiliser la flèche gauche pour arriver en A2.

Remarque – Si vous utilisez le [Ctrl]-clic, il faut ensuite éditer directement dans la barre de formule, car le double clic pour l’édition directe dans la cellule ne marche alors plus.

Détruire une série de liens hypertexte

Vous vous récupérez parfois – par exemple en collant un tableau de cours boursiers en provenance d’Internet – une flopée de liens hypertexte totalement indésirables.

Pour les effacer d’un coup, une petite macro – que vous pourrez loger dans votre classeur de macros personnelles si le problème est récurrent – fait parfaitement l’affaire :

Sub DeleteHyperlinks()
Selection.Hyperlinks.Delete
End Sub

Si c’est tous les liens de la feuille que vous souhaitez ainsi détruire, il suffit de remplacer la commande ci-dessus par ActiveSheet.Hyperlinks.Delete.

Macro pour position de texte

Pour effectuer des consolidations, il me fallait récupérer des tableaux de ventes en provenance de différents classeurs.

Dans chacun, il fallait identifier le numéro de la ligne et de la colonne où l’on trouvait le mot « Ventes 2007 » qui était toujours placé dans le coin supérieur gauche du bloc de données à consolider.

J’ai pour cela créé une fonction macro pour récupérer la position du mot recherché.

En B1, j’ai utilisé la formule :
=ligne(indirect((Découvre("Ventes 2007"))))

En B2, j’ai utilisé la formule :
=colonne(indirect((Découvre("Ventes 2007"))))

Et voici la fonction macro Découvre :

Function Découvre(Texte)
Découvre = ActiveSheet.Cells.Find(What:=Texte, _
LookAt:=xlWhole, MatchCase:=True).Address
End Function

Récupération du nom de famille

Le problème suivant a été posé par un lecteur américain. Une liste contient des noms tels que celui en A1, c’est-à-dire formés d’un ou de plusieurs prénoms, suivis par un nom de famille unique.

Le but du jeu est d’obtenir le résultat affiché en A4 ou A6, c’est-à-dire avec le nom en premier, puis une virgule, puis la liste (éventuelle) des prénoms :

Si l’on peut utiliser deux formules intermédiaires, comme en A2 et A3, la solution n’est pas légère, mais demeure acceptable.

S’il faut obtenir le résultat final d’un seul coup, comme nous le faisons en A6, la formule devient franchement indigeste. Las abonnés au blog pourront au moins récupérer cette formule sans devoir la taper…

Nous vous laissons le plaisir d’analyser toutes ces formules pour bien en comprendre le fonctionnement !

Remarque 1 – On regrette parfois l’absence d’un dernier argument facultatif de la fonction Cherche() qui permettrait de demander à effectuer la recherche en partant de la fin !

Remarque 2 – Le problème est insoluble avec un nom de famille pouvant lui aussi comporter plusieurs mots. On ne peut alors savoir quand terminent les prénoms et quand débute le nom de famille.

Transformation de texte en date

Un lecteur me demandait récemment comment il pouvait entrer des dates sous la forme jjmmaaaa (comme en A1 ci-dessous) et récupérer dans une autre cellule la date correspondante.

J’ai pour cela entré en B1, puis recopié vers le bas, la formule :
=date(droite(A1;4);stxt(A1;3;2);gauche(A1;2))

Cela fonctionne très bien quand la date est entrée sous la forme de texte, comme c’est le cas dans la cellule A1.

Quand elle est entrée directement, comme je l’ai fait en A2 et A3 (dans ce dernier cas, la cellule a reçu le format personnalisé 00000000), on obtient un résultat erroné dans la colonne B car les deux caractères utilisés pour le mois et pour le quantième sont faux car le 0 à gauche, quand on saisit le nombre, est ignoré par Excel. On obtient alors le 61ème jour de février qui, en 2007, tombe le 2 avril !

Il faut donc absolument saisir cette date sous la forme de texte, par exemple en la faisant précéder d’une apostrophe, pour que la formule donne le bon résultat.

Une image dans un commentaire

Les commentaires peuvent être décorés de diverses façons, et même enrichis d’une image !

Pour obtenir le commentaire ci-dessous, nous avons d’abord entré son texte, puis appelé la commande « Modifier le commentaire » du menu contextuel obtenu en faisant un clic droit sur la cellule A1.

Nous avons alors exécuté la commande « Format de commentaire » suite à un clic droit sur la bordure du commentaire, sélectionné l’onglet « Couleurs et traits », puis déroulé le menu « Couleur » du bloc « Remplissage », puis activé la commande « Motifs et textures », puis choisi l’onglet « Image », et enfin sélectionné l’image de Titi.

Cela ressemble un peu à un parcours du combattant, mais cela fonctionne bien et l’image remplit automatiquement toute la surface du commentaire.

Je n’ai pas trouvé le moyen de séparer le texte de l’image, et je ne pense d'ailleurs pas que ce soit possible.

Comment donc ai-je pu faire pour isoler le texte de l’image ?

Eh bien, j’ai tout simplement triché, c’est-à-dire que j’ai sélectionné dans mon image non seulement le dessin de Titi mais aussi l’espace nécessaire pour loger le texte qui se trouvait dans le commentaire avant l'ajout de l'image.