Monsieur Excel
Pour tout savoir faire sur Excel !

08 février 2016

Quelques raccourcis précieux…

Quand je travaille chez mes clients, je suis toujours surpris de constater que des cadres utilisant Excel de façon quotidienne – et ce souvent depuis des années – ignorent encore certains raccourcis pourtant particulièrement utiles. Voici une récapitulation (non exhaustive, forcément) de raccourcis précieux.

Couper, Copier et Coller

Notre première série de raccourcis est un grand fondamental, et pas seulement pour Excel : [Ctrl]-x pour Couper, [Ctrl]-c pour Copier et [Ctrl]-v pour Coller. Là, heureusement, rares sont ceux qui ne les connaissent pas !                      

Déplacement dans un bloc

A l’opposé, le raccourci suivant est inconnu de la majorité des utilisateurs : [Ctrl]-[Maj]-flèche, quand la cellule active est à l’intérieur d’un bloc, sélectionne toutes les cellules de la cellule active à celle placée à la fin du bloc dans le sens de la flèche.

Sélection de tout un bloc

Le raccourci suivant (au moins l’un des deux) est connu de la plupart des utilisateurs : [Ctrl]-* ou [Ctrl]-a pour sélectionner tout le bloc auquel appartient la cellule active.

Remarque – Il y a là un encouragement à concevoir votre modèle avec des blocs bien séparés les uns des autres. Si certains de vos blocs se touchent, ce raccourci ne pourra plus sélectionner un bloc sans l’autre !


Par défaut, quand on construit une formule, les références sont relatives (sans « $ »). Rappelez-vous que les américains nous ont imposé (dans Excel) que le « $ » soit le symbole d’une valeur immuable… Lors de la construction d’une formule, la touche [F4] transforme les coordonnées actives en – successivement – ligne bloquée, colonne bloquée, tout bloqué, tout relatif.

Remarque – Dans l’édition d’une formule, la touche [F4] vous permet, si nécessaire, de « dollariser » d’un coup plusieurs arguments ou même la formule entière.

Sommation rapide

Encore un raccourci utile mais peu connu : [Alt]-= joue exactement le même rôle que la touche S. L’intérêt est que, quand on développe un modèle rapidement avec les deux mains sur le clavier, ce raccourci est plus rapide que de devoir quitter le clavier, prendre la souris et cliquer sur la touche S.

Ce que peu d’utilisateurs en revanche savent, à ce sujet, est que l’on peut d’un coup d’un seul, avec le raccourci-clavier ou la touche, faire à la fois les totaux à droite et les totaux en bas pour un bloc, à condition bien entendu d’avoir aussi auparavant sélectionné la colonne vide à droite et la ligne vide en bas.

Collage spécial en accès direct

Encore un raccourci peu connu ! Quand vous avez copié un bloc, vous pouvez ensuite sélectionner une destination puis appeler directement la commande « Collage spécial » en faisant  [Ctrl]-[Alt]-v.

Affichage/Fermeture des filtres en direct

Encore un raccourci peu connu ! Pour afficher ou faire disparaître les filtres, il suffit de faire [Ctrl]-L.

Notez que le L est en majuscules, il vous faut donc trois doigts, ou alors un doigt à cheval sur [Ctrl] et [Maj]…   

Edition rapide d’une cellule

Tout le monde connaît le couble clic sur une cellule pour en afficher la formule et éventuellement l’éditer. Mais il est possible d’obtenir le même résultat avec [F2], ce quoi est plus pratique si vous avez déjà les deux mains sur le clavier.

Remplissage automatique

Vous avez sélectionné un bloc vertical ou un bloc horizontal dont la première cellule contient la formule ou valeur originale. Pour recopier cette cellule vers le bas, faites [Ctrl]-b. Pour recopier cette cellule vers la droite, faites [Ctrl]-d.

Si maintenant deux cellules au début du bloc contiennent des valeurs, la recopie continuera la série avec des valeurs de même écart…

Saisie multiple

Vous avez sélectionné un vecteur horizontal ou vertical, ou même une matrice. Vous entrez une valeur, ou une formule. Si vous validez avec [Ctrl]-[Maj]-[Entrée], la formule est entrée dans l’ensemble du bloc. Cela revient au même résultat qu’un copier suivi d’un collage spécial des seules formules…


02 février 2016

Liste des onglets avec liens

Nous découvrons aujourd’hui un petit utilitaire bien pratique proposé par Jeff Robson, d’Access Analytics, et un peu modifié par mes soins.

Le but est de disposer dans un classeur d’une liste des onglets du classeur, chaque nom d’onglet ayant un lien hypertexte vous permettant d’y aller directement.

Commençons par ajouter un onglet baptisé « Plan » et entrons « Onglets du classeur » en A1. C’est plus logique de placer cet onglet en tête du classeur, mais rien ne vous y oblige.

Faites un clic droit dans le nom de cet onglet et activez la commande « Visualiser le code ». Il ne vous reste plus qu’à entrer le code ci-dessous pour que tout soit prêt. Quittez alors VBA et, dans le classeur, cliquez sur le nom d’un autre onglet, puis sur celui de l’onglet « Plan ».

Et le tour est joué !

Vous avez maintenant la liste des onglets du classeur, avec des liens hypertexte…





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…

Formule de K2 : =sierreur(index($C$1:$C$34;J2+L1);34)
Formule de L2 : =si(M1=1;K2;SI(K2
Formule de M1 : 0
Formule de M2 : =M1+1*(K2

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%