Créer une séparation horizontale

Très fréquemment, un modèle comporte un grand nombre de lignes et l’on perd du temps à scroller comme un fou vers le haut ou vers le bas.

Si l’on sépare la fenêtre horizontalement en deux parties, cela augmente sensiblement le confort du travail.

Pour cela, vous avez le choix entre deux méthodes.

1. La commande « Fractionner » du menu Fenêtre.

Commencez pas vous placer dans une cellule au-dessus de laquelle vous souhaitez voir cette séparation horizontale apparaître (C4 dans l’exemple ci-dessous), puis utilisez la commande « Fractionner » du menu Fenêtre.

Vous constatez qu’Excel a créé à la fois une séparation horizontale et une séparation verticale, au-dessus et à gauche de la cellule active. Des ascenseurs permettent de se déplacer à volonté dans les quatre blocs ainsi créés.

2. Déplacer vous-même la barre de fractionnement

La barre de fractionnement horizontal, quand il n’y a pas encore eu de fractionnement, est logée au-dessus de l’ascenseur (à droite de la colonne « L », dans l'écran ci-contre). Il suffit de tirer cette barre vers le bas, ou de faire un double clic dessus, pour créer un fractionnement horizontal. Dans ce dernier cas, le fractionnement est créé au même endroit de l'écran que la dernière fois.

De la même façon, on fractionne verticalement en tirant à gauche, ou en activant d’un double clic, la barre de fractionnement placée à droite de l’ascenseur en bas de l’écran.

Une fois une barre placée, on la déplace comme on veut. On l’élimine en faisant un double clic dessus ou à l’aide de la commande « Supprimer le fractionnement » du menu Fenêtre.

Enfin, on peut figer les barres de fractionnement avec la commande « Figer les volets », auquel cas la partie figée perd ses ascenseurs. Voilà, vous pouvez jouer avec tout cela...

Contrôle de valeurs dans du texte

Désolé, encore six jours depuis mon dernier message. J'ai vraiment trop de travail ces temps-ci ! Rassurez-vous, je vais revenir bientôt au rythme d'un message tous les 3 à 4 jours :)

Un lecteur m’a récemment posé le problème suivant...

La colonne A reçoit des séries de valeurs numériques séparées par des points. Des valeurs numériques servent de titres à plusieurs colonnes placées à droite. Il faut placer « Oui » dans chaque colonne dont le nombre est présent dans la colonne A.

La formule de la cellule B2, qui a ensuite été recopiée dans le bloc B2:G4, est : =si(esterreur(trouve("."&B$1&".";"."&$A2&"."));"";"Oui").

La syntaxe de la fonction Trouve() est Trouve(texte1;texte2). Cette fonction renvoie la position où débute la première occurrence de la chaîne de caractères « texte1 » dans « texte2 ».

Ainsi, =trouve("dou";"roudoudou") renvoie 4.

Si la chaîne n’est pas trouvée, le message d’erreur « #VALEUR ! » est renvoyé.

L’astuce que nous avons utilisée dans notre formule est de remettre un point avant et après la chaîne, ainsi qu’avant et après le contenu de la cellule. En effet, si l’on n’avait pas pris cette précaution, on aurait par exemple cru trouver un « 3 » quand il y aurait eu un « 13 ».

Une fonction personnalisée

Voici déjà six jours que vous êtes sans nouvelle de moi. Mais ce message contient, à titre de compensation, un exemple particulièrement intéressant, notre première fonction personnalisée !

Dans mon dernier message, nous avons vu comment – à l’aide d’une formule compliquée – calculer un écart entre deux dates, exprimé en années, mois et jours.

En fait, quand on doit accomplir une tâche aussi complexe, il est préférable de créer à cet effet une fonction personnalisée, une fonction macro.

Voici la fonction personnalisée qui calcule l’écart exact entre deux âges :

Voici donc la façon dont nous avons utilisé cette fonction... La formule de B3, qui a été reproduite ensuite en C4, est la formule : =age_exact(B1;B2)

Je remercie au passage François Sermier (un autre rédacteur de La lettre d'Excel), qui m'a informé de l'existence de la fonction VB générale "DateSerial".

Calcul de votre âge exact

Avant de lire cette page, je vous conseille de lire mon message du 14 décembre, « La grand-mère de Bill Gates ». En effet, il ne sert à rien de se répéter et, si je le faisais, ce message serait trop long…

Supposons que vous souhaitiez connaître votre âge précis, c’est-à-dire le nombre d’années, de mois et de jours auxquels vous avez heureusement survécu. Dans l’exemple ci-dessous, je me suis pris comme cobaye en entrant ma propre date de naissance en B1 :

La formule miracle de la cellule B4, ne serait-ce que par sa longueur, est :

Je vous laisse le plaisir de décortiquer cette formule...

Notez que l’on tire parti dans la formule du fait que le mot « mois » est déjà au pluriel quand il est au singulier ; sinon, on devrait avoir une partie de la formule qui mette « mois » au singulier quand il n’y a qu’un mois…

Pour éviter ce problème avec les jours, je me suis contenté de mettre « j. » tout simplement. Vous pourrez toujours le justifier en expliquant que cela évite de devoir mettre une trop grande largeur à la colonne…

Vous pourrez aussi noter avec le 730 que j'ai simplifié, en ignorant les années bissextiles, la mise au pluriel des années.

Pour ceux d’entre vous qui apprécient ce blog et auront envie de m’envoyer un cadeau d’anniversaire, vous pourrez l'adresser à :

Hervé Thiriez – Groupe HEC – 78350 Jouy-en-Josas

Formule matricielle en cellule

Les formules matricielles, dans Excel, sont à la fois très puissantes et peu intuitives. On valide une formule matricielle, dans une ou dans plusieurs cellules d’un coup, en utilisant la combinaison [Ctrl]-[Maj]-[Entrée], ce qui les rend inaccessibles à ceux qui frappent sur leur clavier avec un seul doigt !

Prenons l’exemple ci-dessous, dans lequel nous avons des familles auxquelles appartiennent des produits et où, en C13, on veut calculer le chiffre d’affaires correspondant à une famille de produits donnée :

La solution classique revient à utiliser la colonne E, en entrant en E2 la formule =si(D2=$C$12;B2*C2;""), à la copier vers le bas et à récupérer le total en E11 dans C13.

La colonne E est parfaitement inutile si vous utilisez en C13 une formule matricielle : {=somme(si(D2:D10=C12;B2:B10*C2:C10;0))}

C’est-à-dire que vous entrez la formule sans les accolades, et que vous validez avec la combinaison [Ctrl]-[Maj]-[Entrée], qui ajoute de façon automatique les accolades.

En conclusion, quand on entre une formule matricielle, on demande à Excel de réaliser une boucle sur les éléments vectoriels (ou matriciels) indiqués, puis de réaliser l’opération demandée sur chacun de ces éléments, un par un.

Remarque – N’entrez surtout pas les accolades au clavier, sinon Excel prendrait cela pour du texte !

Formatage direct en K€ ou en M€

Trop souvent, dans les modèles, pour afficher une valeur en kilo-euros ou en méga-euros – je trouve les termes « keuros » ou « meuros » très laids – les utilisateurs d’Excel divisent les valeurs par 1.000 et 1 million, pour ensuite les re-multiplier par ces valeurs quand il leur faut récupérer le montant original.

Cela n’est pas efficace car, en divisant puis en re-multipliant par 1 million, on réduit la précision finale d’Excel. En outre, cette solution est très lourde.

La solution la plus efficace revient à créer un format personnalisé. Pour cela, il faut savoir que, dans un format personnalisé, tout espace placé hors de guillemets – sauf dans le cas précis des formats de type # ##0 – provoque une division par 1.000.

Dans le tableau ci-dessous, nous avons entré un montant en A1, qui a ensuite été recopié jusqu’en B3. Dans la colonne B, nous avons utilisé deux formats personnalisés...

Le format de B2 contient un espace avant les guillemets : # ##0,00 " K€"

Celui de B3 contient deux espaces avant les guillemets (cela ne se voit pas ici car "Blogger" me mange les espaces supplémentaires) : # ##0,00 " M€"

Notez au passage que, même sur les formats personnalisés, les boutons « Ajouter une décimale » et « Réduire les décimales » continuent à fonctionner.

Pourquoi est-ce l’espace qui joue ce rôle de division ?

Tout simplement car c’est le caractère de séparation des milliers dans les codes de formatage de l’Excel français. Si vous utilisez un Excel anglais, c’est le format #,##0.00," K$" que vous utiliserez donc pour les milliers de dollars.

Construire une table de fréquence

Supposons que vous souhaitiez construire dans la colonne D une table de fréquence des 20 valeurs (de 0 à 999) de la colonne A, c’est-à-dire savoir combien de valeurs sont inférieures ou égales à 250, comprises entre 250 et 500, entre 500 et 600,…

Pour obtenir les fréquences individuelles, il faut sélectionner le bloc D1:D6, saisir la formule fatidique =frequence($A$1:$A$20;C1:C6), puis la valider comme une formule matricielle en utilisant à cet effet la combinaison de touches [Ctrl]-[Maj]-[Entrée] !

Si en revanche vous souhaitez obtenir les fréquences cumulées, il faut sélectionner E1:E6, saisir la même formule, mais la valider en saisie multiple avec [Ctrl]-[Entrée]…

Une petite astuce enfin… Comment ai-je obtenu les libellés de l’axe des abscisses ? Tout simplement en sélectionnant cet axe, et en lui donnant le format numérique "<="0 !