Monsieur Excel
Pour tout savoir faire sur Excel !

27 juin 2011

Utilisation d’un nom relatif

Avec l’article publié il y a quatre jours, vous vous êtes peut-être dit : « C’est bien joli le concept de nom relatif, mais à quoi cela peut-il bien servir ? ».

Nous en voyons un exemple ci-dessous. Nous avons des ventes en colonne B et nous aimerions bien avoir, en colonne D, les ventes de la veille. Certes, il suffirait pour cela d’entrer en D3 la formule relative =B2 pour faire le travail. Mais cela n’est pas une formule très parlante…

Pour la rendre plus explicite, il serait sympathique d’utiliser des noms. Nous avons attribué le nom Ventes à la colonne B. La formule à entrer en D3 est alors =Ventes préc !

Remarque 1 – Cela bien sûr en utilisant le nom préc défini dans l'article précédent...

N’est-ce pas bien plus joli ?

Pourquoi cela fonctionne-t-il ? Tout simplement parce que, à l’instar du « : » qui, dans Excel, signifie « jusqu’à » et du « ; » qui signifie « et » ; il y a un troisième caractère codé : l’espace qui, dans Excel, signifie « intersection ». Notre nouvelle formule signifie donc « intersection de la colonne Ventes avec la ligne précédente ».

Vous pouvez aisément vérifier d’ailleurs que =B2 donne exactement le même résultat que =B:B 2:2 !

C’est pour cela que vous n’avez pas le droit d’utiliser d’espace dans un nom Excel, ainsi d’ailleurs que dans plusieurs autres fonctionnalités d’Excel !

Remarque 2 – Notre formule apporte un autre avantage. Si vous êtes amené à insérer une ligne, par exemple entre la ligne 9 et la ligne 10, la formule classique sera erronée dans la cellule B11 (ex-B10) alors que la nôtre fonctionnera toujours :)

23 juin 2011

Création d’un nom relatif

Par défaut, un nom est toujours défini en absolu, c’est-à-dire que les coordonnées de(s) cellule(s) concernée(s) par ce nom reçoivent automatiquement des dollars. Nous l’avons vu dans l’article publié il y a quatre jours, où le nom « Albert » était associé à la référence =Data!$A$1:$C$2; Data!$B$6:$C$7; Data!$D$13:$E$15, avec des dollars partout !

Et pourtant, rien ne vous empêche de définir un nom avec des coordonnées relatives !

Prenons l’exemple ci-dessous. Nous sélectionnons la ligne 6 et nous appelons la définition de nom en entrant le nom « préc », comme vous le voyez dans la copie d’écran ci-dessous :

Ensuite, dans la zone « Fait référence à : », remplacez le =Noms!$6:$6 (absolu) actuel par =Noms!5:5 (relatif) et validez. Et voilà, vous venez de remplacer la définition absolue faisant référence à la ligne 6 par une définition relative faisant référence à la ligne précédente.

Pour vérifier, utilisez le menu déroulant de la zone « Nom », à gauche de la barre de formules, et sélectionnez « préc » : Excel sélectionne alors immédiatement la ligne juste au-dessus de la ligne active !

Remarque 1 – Si vous faites cela lorsque la cellule active se trouve dans la première ligne, Excel sélectionne alors la toute dernière ligne de la feuille !

Remarque 2 – La définition d’un nom peut aussi être mixte, c’est-à-dire comporter à la fois des coordonnées absolues (avec le « $ ») et des coordonnées relatives (sans le « $ »)…

19 juin 2011

Définition d’un nom

Dans Excel, les noms ont de multiples usages. C’est pourquoi il est bon de savoir aussi les définir de façon efficace.

La méthode traditionnelle pour nommer une cellule ou une zone – qui peut être multiple, c’est-à-dire constituée de plusieurs blocs – revient à sélectionner tout d’abord cette zone, à activer l’onglet « Formules », à utiliser le bouton « Définir un nom », puis la commande « Définir un nom », à entrer le nom en question et enfin à valider.

Tout cela est bien long avec ses cinq étapes. On arrive au même résultat bien plus rapidement en sélectionnant la zone à nommer, en cliquant dans la zone
« Nom » (à gauche de la barre de formules), en entrant le nom, puis en validant par [Entrée].

C’est ce que nous avons fait ci-dessous en sélectionnant le bloc multiple A1:C2, B6:C7 et D13:E15, puis en entrant le nom Albert dans la zone « Nom ». Dès que l’on valide ce nom, il est associé à l’ensemble des trois blocs.

Comment vérifier à quoi un nom est associé ?

Il suffit, la cellule activée étant n’importe où dans le même classeur, d’activer le menu déroulant à droite de la zone « Nom », représenté par la flèche basse, et de sélectionner le nom voulu pour qu’Excel active la feuille voulue et la zone nommée. On peut aussi, mais c’est un peu moins rapide, passer par [F5] ou par le raccourci [Ctrl]-t pour afficher la liste des noms et choisir le nom à atteindre.

Un nom identifie donc une zone précise d’une feuille donnée. On le vérifie aisément en passant par la commande « Gestionnaire de noms ». Quand on sélectionne le nom « Albert », on constate qu’il fait référence à =Data!$A$1:$C$2; Data!$B$6:$C$7; Data!$D$13:$E$15. C’est bien pratique quand on récupère un classeur venu d’ailleurs et que l’on veut vérifier la définition des noms.

Dans notre cas, nous constatons que le nom Albert fait référence à un bloc de trois matrices, l’ordre de ces matrices étant respectivement $A$1:$C$2 puis $B$6:$C$7 et enfin $D$13:$E$15. Cet ordre est particulièrement utile à connaître par exemple pour utiliser une formule telle que =index(Albert;n°lig;n°col;n°mat).

15 juin 2011

Comptage des trous dans une série

Le problème suivant provident d’un lecteur du blog qui travaille dans une société financière.

On dispose, pour une série d’actions cotées en bourse, des cours – jour par jour – entre deux dates. Le but de l’opération est de calculer le nombre de valeurs manquantes entre le premier et le dernier cours cité.

Dans l’exemple ci-dessus, pour l’action A, il faut ainsi découvrir qu’entre la première cotation le 30 aoüt et la dernière le 10 septembre, il manque deux cotations.

Pour cela, nous avons calculé la position de la première valeur, celle de la dernière, et le nombre de valeurs entre ces deux positions.

Voici les formules des cellules F2 à F5 :

F2 : =min(si(estnum(B2:B19);ligne(B2:B19);10^6))

F3 : =equiv(10^6;B:B)

F4 : =nb(index(B:B;F2):index(B:B;F3))

F5 : =F3-F2+1-F4

Remarque – La formule de F2 est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée]

11 juin 2011

Désactiver le copier/coller

Parfois, vous aimeriez bien empêcher l’utilisateur d’un de vos classeurs de copier une partie de son contenu pour le coller ailleurs, dans un de ses classeurs par exemple.

Pour cela, il vous suffit d’écrire, au niveau du workbook (le classeur), la macro d’une ligne suivante :

Cette macro est toute bête, elle se contente de vider le contenu du Presse-papiers dès que vous désactivez la fenêtre du classeur.

Astucieux, n’est-ce pas ?

Mais cela n’empêchera pas l’utilisateur de votre classeur, s’il est astucieux lui aussi, d’envoyer dans le classeur de son choix une copie de la feuille active grâce à la commande « Déplacer ou copier… » qui apparaît dès que l’on fait un clic droit sur n’importe quel onglet du classeur actif.

Ou encore, il peut, à partir d'un autre classeur, écrire en relatif une formule faisant référence à la cellule A1 de la feuille active de votre classeur et la recopier partout dans sa feuille pour obtenir une copie du contenu de cette feuille...

Pour bien vous protéger, il vous faudra donc aller plus loin encore…

07 juin 2011

La propriété « Text » d’une cellule

Nous avons écrit une toute petite macro, activée par le bouton ci-dessous, qui affiche le contenu de la cellule B1. Voici cette macro :

Sub Affiche()

MsgBox "Contenu de la cellule B1 : " & Range("B1").Text

End Sub

La particularité de cette macro est qu’elle utilise une propriété peu connue du
« range ». Il s’agit de la propriété « Text » qui renvoie non seulement le contenu de la formule mais aussi son format numérique, s’il s’agit d’une valeur.

C’est autrement plus léger que si la macro devait spécifier l’affichage au format « € » dans le msgbox…

Remarque 1 – Cette propriété est en read-only : on peut la consulter, mais on ne peut pas la modifier…

Remarque 2 – La propriété « Text » ne fonctionne que si l’objet du range est une cellule unique.

03 juin 2011

Déplacement avec le bouton droit

Quand un bloc est sélectionné et que le curseur passe sur l’un des quatre segments délimitant la sélection, il prend la forme du curseur de déplacement, la croix fine avec ses quatre flèches.

Si l’on déplace alors le bloc en maintenant le bouton droit de la souris enfoncé, à la place du bouton gauche, on obtient l’écran suivant dès que l’on lâche le bouton (ici, avec le résultat en H2) :

On constate donc que l’on a sélectionné le bloc D14:E16, que le résultat se logera en H2:I4, et que – selon l’option que nous aurons choisie – nous pourrons le déplacer (Placer ici), le coller (Copier ici), ne coller que les valeurs (Copier ici les valeurs seules), …

Avec l’option « Créer un lien », on mettra en H2 la formule =D14, qui sera alors reproduite dans le reste du bloc.

L’option « Créer un lien hypertexte » met dans le bloc H2:I4 un lien hypertexte vers D14, seule la cellule H2 montrant de façon visible la présence de ce lien.

Je vous laisse expérimenter avec les quatre commandes du second bloc, dont l’intérêt pratique ne me semble pas évident…

Remarque 1 – Si vous « déplacez » le bloc en le remettant ensuite à sa place originale, vous pouvez – en sélectionnant alors l’option « Copier ici les valeurs seules » – remplacer le bloc par ses valeurs. C’est une façon rapide d’effectuer l’équivalent d’un [Ctrl]-c sur le bloc lui-même, suivi d’un collage spécial des valeurs…

Remarque 2 – Dans les cinq commandes contenant le verbe « copier », il faudrait utiliser le terme « Coller » à la place de « Copier ». Une fois de plus, Microsoft a oublié (ou fait l’économie) de faire relire tout cela par quelqu’un de compétent :(