Traduire une formule en anglais

Les commentaires sont une fonctionnalité précieuse d’Excel ; ils mériteraient à mon avis d’être utilisés plus fréquemment qu’ils ne le sont usuellement.

Un commentaire s’ouvre dans une cellule par la commande « Commentaire » du menu Insertion. Un raccourci-clavier peu documenté joue le même rôle, [Maj]-[F2]. Dès l’appel du commentaire, un commentaire apparaît, prêt à être édité, avec le nom de l’utilisateur :


Les commentaires sont intéressants car, dès que l’on passe dessus avec le curseur, on peut consulter une information circonstanciée.

Mais ils peuvent présenter d’autres avantages encore…

Par exemple, je me trouve souvent dans la situation où je fais une formation à la modélisation avec Excel à un groupe d’étudiants de MBA de diverses nationalités. Quand je crée une formule, avec mon Excel en français, ils me demandent fréquemment comment cette formule s’écrirait en anglais. J’ai donc écrit une macro que j’ai enregistrée dans mon classeur de macros personnelles, pour afficher automatiquement la formule en anglais :

Sub Formule()
ActiveCell.AddComment
ActiveCell.Comment.Text Text:=ActiveCell.Formula
End Sub

Comme je lui ai associé le raccourci [Ctrl]-k, il suffit que je tape ce raccourci pour que la formule apparaisse aussitôt à l’écran en anglais.

Remarque – La formule apparaît en anglais car c’est la langue naturelle du VBA.

Colorer des valeurs d’un graphe

Vous souhaitez, dans un graphe comme le premier graphe ci-dessous, colorer toutes les valeurs dépassant le seuil de 70, pour obtenir le résultat reproduit dans le second graphe ci-dessous.

Cela s’obtient aisément avec la macro ci-dessous :

Tri de plusieurs lignes à la fois

Un lecteur m’a demandé récemment s’il était possible de trier d’un seul coup plusieurs lignes en ordre croissant, en les traitant indépendamment les unes des autres.

Il souhaitait obtenir le résultat du tableau des lignes 8 à 11, en partant du tableau des lignes 2 à 5. Cela revient en fait à trier la ligne 2, puis la 3, puis la 4, et enfin la 5. Ce qui est certes fastidieux...

Une solution consiste à créer le tableau du bas en entrant dans la cellule A8 une formule que l’on recopie dans tout le tableau : =petite.valeur($A2:$G2; colonne()).

Si l’on veut que ce tableau remplace le tableau du haut, il suffit alors de sélectionner A8:G11, de copier et de faire un collage spécial des valeurs en A2. Il ne reste ensuite plus qu’à effacer le tableau du bas.

Remarque 1 – Bien entendu, cette solution sera d’autant plus intéressante que le nombre de lignes du bloc sera élevé.

Remarque 2 – La solution consistant à utiliser la formule présente un autre avantage, s’il n’est pas nécessaire d’écraser le tableau initial : c’est que le tri ainsi effectué est un tri dynamique, alors que celui effectué grâce à la commande « Trier » du menu Données est un tri ponctuel.

Date & Heure : autres fonctions

Dans la dernière rubrique, nous avons passé en revue les fonctions normales de date et heure d’Excel. Aujourd’hui, nous nous intéressons aux fonctions de date et heure ajoutées par l’utilitaire d’analyse. Le tableau ci-dessous illustre leur utilisation :

Leur syntaxe est la suivante :

=fin.mois(date_départ;mois)

=fraction.annee(date_début;date_fin[;base])

=nb.jours.ouvres(date_départ;date_fin[;jours_fériés])

=no.semaine(numéro_de_série[;méthode])

=serie.jour.ouvre(date_départ;nb_jours[;jours_fériés])

Nous avons vu le 6 août comment fonctionnait l’argument facultatif [;jours_fériés].

Remarque 1 – L’aide d’Excel pour ces fonctions est largement incomplète. Quand on clique sur le bouton « Aide sur cette fonction », on obtient un écran vierge !

Remarque 2 – L’argument facultatif [;base] fonctionne de la façon suivante : rien ou 0 pour le comptage 30/360 US, 1 pour le comptage réel, 2 pour le réel/360, 3 pour le réel/365 et 4 pour le 30/360 européen.

Les fonctions de date et heure

Puisque nous avons parlé des jours fériés dans les trois dernières rubriques, nous pouvons continuer à explorer la gestion du temps dans Excel en présentant les fonctions de date et heure.

Le tableau ci-dessus illustre l'utilisation de la totalité des fonctions de date et heure permanentes d’Excel, c’est-à-dire en ignorant celles ajoutées par l’utilitaire d’analyse.

Si vous n’êtes pas familier de la gestion de la date et de l’heure dans Excel, lisez donc notre rubrique du 14 décembre 2005 avant de continuer la lecture de cet article.

La colonne B affiche les formules utilisées en colonne A. Nous avons aussi mis en commentaire de la cellule A9 la véritable valeur de cette cellule, le contenu actuel de la cellule étant dû au format « jjj jj/mm/aaaa hh:mm:ss ».

Remarque 1 – On note en A4 que dateval() ignore l’heure en argument, et en A14 que tempsval() ignore la date en argument.

Remarque 2 – La fonction jours360() possède un troisième argument facultatif dont vous pourrez voir le mode d'emploi dans l'aide d'Excel.

Macro pour ôter les jours fériés

Dans la dernière rubrique, nous avons vu comment générer une séquence de jours ouvrés à partir de n’importe quel jour initial, grâce à l’option « Série » de la commande « Remplissage » du menu Edition.

Hélas, s’il y a quelque part une liste de jours fériés, ceux-ci ne peuvent pas être éliminés simplement de la séquence ainsi créée. Il faut pour cela une macro !

Celle que je vous propose ci-dessous part de la cellule active. Si cette cellule correspond à un jour férié, la liste des jours fériés portant le nom « Col », elle est détruite. On passe ensuite à la cellule suivante, tant que celle-ci n’est pas vide.

Remarque 1 – Le nom de la fonction « CountIf » est précédé du mot-clef
« Application » qui indique au Visual Basic qu’il s’agit d’une fonction propre à l’application, c’est-à-dire à Excel.

Remarque 2 – Notons aussi l’utilisation de Range(״Col״) pour identifier dans la fonction le vecteur des jours fériés.

Créer une suite de jours ouvrés

Nous avons vu il y a quatre jours comment calculer le nombre de jours ouvrés entre deux dates. Quand on s’intéresse aux jours ouvrés, on a parfois le besoin de créer dans la feuille de calcul une séquence de jours ouvrés.

Prenons l’exemple ci-dessous. Nous désirons créer une série de 25 jours ouvrés consécutifs démarrant le 20 août 2007. Nous entrons cette date en A1 et nous appelons l’option « Série » de la commande « Remplissage » du menu Edition, puis nous sélectionnons « Jour ouvré » dans le bloc « Unité de temps » :

Il n’est pas nécessaire de modifier les autres réglages par défaut de cette fenêtre, qui nous conviennent tous. Dès que nous validons par « OK », nous obtenons la liste des 25 jours ouvrés consécutifs, allant jusqu’au 21 septembre 2007.

Remarque 1 – Il est un peu plus rapide de sélectionner – en tirant vers le bas avec le bouton droit de la souris – la zone A1:A25. En effet, quand on lâche le bouton de la souris, la commandé « Série » apparaît alors aussitôt en bas du menu contextuel : cela évite d’avoir à passer par le menu Edition et par la commande « Remplissage ».

Remarque 2 – Cette solution ne tient évidemment pas compte des éventuels jours fériés. Nous verrons dans la prochaine rubrique comment résoudre ce problème.

Calcul du nombre de jours ouvrés

Pour calculer le nombre de jours ouvrés entre deux dates, vous avez une fonction de l’utilitaire d’analyse qui est particulièrement précieuse, la fonction nb.jours.ouvres().

Nous vous rappelons que, pour qu’elle soit reconnue par Excel, il faut que l’utilitaire d’analyse soit actif. Vous pouvez relire à cet égard la rubrique du 25 févier 2007, où je montre comment tester la présence en mémoire de cet add-in fourni avec Excel.

Si vous voulez l’activer, il suffit pour cela d'utiliser la commande « Macros complémentaires » du menu Outils et de cocher l’utilitaire d’analyse. Quand cet utilitaire est en mémoire, vous avez accès à de nombreuses nouvelles commandes – dont nous reparlerons plus tard – ainsi qu’à de multiples nouvelles fonctions, dont la fonction nb.jours.ouvres().

Sa syntaxe est nb.jours.ouvres(date1;date2[;liste]) où – pour respecter la convention en informatique – nous indiquons entre crochets ce qui est facultatif.

Nous voyons ci-dessus son utilisation. Nous avons entré en colonne D une liste de jours fériés, avec pour info le jour de la semaine correspondant en colonne E. Les cellules B3 et B4 calculent le nombre de jours ouvrés – c’est-à-dire week-end exclus – quand on tient compte des jours fériés (en B3) ou non (en B4).

Remarque – On obtient le message d’erreur #VALEUR! dès qu’une cellule de la liste en troisième argument contient une valeur non numérique, par exemple un texte.