Création d’un graphe élastique

Nous avons vu, dans la dernière rubrique, comment l'on pouvait créer un nom dynamique. Nous allons voir aujourd’hui comment créer un graphe élastique, c’est-à-dire s’adaptant automatiquement au nombre des données présentes.

Commençons par sélectionner le bloc de données en A1:A10 puis créons un graphe, en sélectionnant un simple histogramme. Bien entendu, ce graphe n’est pas élastique : il ne s’adapte pas aux données puisqu’il continue à afficher 10 valeurs même si l’on ajoute ou détruit des données.

Pour rendre le graphe dynamique, sélectionnons-le (cf. copie d’écran) puis, dans la barre de formule, remplaçons $A$1:$A$10 par le nom dynamique Valeurs défini dans la rubrique précédente, et enfin validons par [Entrée].

Le graphe est à présent dynamique, ce que nous vérifions aisément en détruisant les trois dernières valeurs, puis en retirant la formule de A7 jusqu’en A13.

Remarque 1 – Nous notons, si nous sélectionnons la série dans le graphe, que Feuil1!valeurs est devenu Class.xls!Valeurs, c’est-à-dire qu’Excel a remplacé le nom de la feuille par celui du classeur, ce qui est approprié, dans la mesure où un nom est normalement défini au niveau du classeur.

Remarque 2 – Si nous n’avions pas gardé « Feuil1 ! » quand nous avons remplacé $A$1:$A$10 par « Valeurs », Excel aurait refusé notre modification.

Remarque 3 – Ce message est le 201ème publié dans ce blog. C'est une sorte d'anniversaire...

Création d’un nom dynamique

Une des clefs pour la création de modèles professionnels est l’utilisation de noms dynamiques, c’est-à-dire de noms qui s’adaptent automatiquement aux données présentes. Si l’on enlève ou ajoute des données, le nom s’adapte alors immédiatement…

Ici, nous avons utilisé les formules représentées dans le cartouche. Nous souhaitons créer un nom qui identifie les données de la colonne A, soit actuellement A1:A10. Mais nous voulons que ce nom soit dynamique : si l’on reproduit la formule de la cellule A10 en A11:A12, il faut que le nom identifie alors le bloc A1:A12… ; si l’on efface le bas de la colonne pour ne garder que les cellules A1:A7, il faut que le nom ne représente plus que ces sept cellules.

Pour cela, passez par la commande Insertion – Nom – Définir, donnez le noms « Valeurs » et dans la zone « Fait référence à : », entrez : =Feuil1!$A$1:decaler(Feuil1!$A$1;Feuil1!$B$1-1;0).

Comment vérifier que le nom est bien dynamique ?

Jusqu’à Excel 2000, la solution était simple… Il suffisait d’entrer dans une cellule la formule =somme(valeurs), puis de faire un double clic dans la cellule pour l’éditer : Excel sélectionnait alors exactement le bloc contenant les valeurs.

Malheureusement, pour les versions postérieures, cela ne marche plus. La moins mauvaise solution que je connaisse revient à entrer =nb(valeurs) dans une cellule, ce qui permet de vérifier au moins le nombre des valeurs incluses dans le nom…

Remarque – Notre nom dynamique suppose que, dans la zone à nommer, il n’y ait aucune cellule vierge ou contenant du texte. Si nous avions utilisé nbval(), cela aurait résolu le problème des cellules contenant du texte mais, dans un cas tel que celui-ci, on ne souhaite de toute façon pas qu’il y ait du texte dans la zone nommée.

La barre d'outils « Formulaires »

Supposons que vous vouliez insérer dans une feuille de calcul Excel 7 un bouton macro ou une zone de liste déroulante. Si vous débutez avec Excel 7, vous risquez de passer un certain temps avant de trouver la procédure adaptée. Voici ce qu’il faut faire…

Cliquez sur le bouton Microsoft Office puis, en bas de la fenêtre qui surgit alors, sur le bouton « Options Excel ».

Dans la fenêtre qui vient de s’ouvrir, cochez l’option « Afficher l’onglet Développeur dans le ruban » et validez par OK.

L’onglet Développeur s’ajoute alors à la droite de la liste des onglets. Le voici, en version US :

Il suffit de cliquer sur le bouton « Insert » pour avoir accès aux outils des barres d’outils « Formulaires » et « Boîte à outils Contrôles » (appelés maintenant « Contrôles ActiveX) des versions antérieures d’Excel.

Généralisation de notre macro

Dans les deux derniers messages, nous avons tout d’abord créé une première macro en l’enregistrant, puis nous avons simplifié cette macro enregistrée.

Aujourd’hui, nous présentons la version « finale » de cette macro dans laquelle nous avons rendu la macro totalement flexible. Cela signifie que, quelque soit le nombre de lignes et de colonnes du bloc, la macro continuera à fonctionner.

Dans les deux versions antérieures, la macro ne fonctionnait bien que si le bloc initial comportait exactement six lignes et sept colonnes. C’était en effet le cas du bloc à partir duquel la macro initiale avait été enregistrée.

Voici le code de la macro finale, totalement flexible :

Sub Totaux_fin()
'
' Totaux Macro
' Macro modifiée le 16/11/2007 par Hervé Thiriez
'
Dim nb_Lig As Integer
Dim nb_Col As Integer
Selection.CurrentRegion.Select
nb_Lig = Selection.Rows.Count
nb_Col = Selection.Columns.Count
Selection.Cells(nb_Lig + 1, 1).Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & nb_Lig - 1 & "]C:R[-1]C)"
ActiveCell.AutoFill Destination:=ActiveCell.Range(Cells(1, 1), Cells(1, nb_Col)), _
Type:=xlFillDefault
ActiveCell.Cells(1 - nb_Lig, nb_Col).Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=SUM(RC[-" & nb_Lig & "]:RC[-1])"
ActiveCell.Offset(1, 0).AutoFill Destination:=ActiveCell.Range("A2:A" & nb_Lig)
End Sub

Ce sont les variables nb_Lig et nb_Col qui permettent la flexibilité de la macro.

Remarque – Grâce à ces deux variables, nous pouvons nous passer totalement des end(xlDown), end(xlUp) et end(xlToRight) antérieurs…

Simplifier une macro enregistrée

Même si l’on ne fait aucune bêtise quand on enregistre une macro, Excel génère du code « bavard » que l’on peut significativement simplifier.

Ainsi, les quatre premières lignes :
Selection.CurrentRegion.Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

... peuvent être ramenées à une instruction unique :
Selection.CurrentRegion.End(xlDown).End(xlDown).Offset(1, 0).Select

Après toutes les simplifications utiles, nous parvenons à un code nettement plus compact :

Certes, nous pourrions encore compacter un peu, mais cela demande un peu plus d’aisance avec le VBA, et parvenir à :

On économise ainsi deux instructions, mais au prix d’une lisibilité un peu moins bonne. Ce n’est donc pas une bonne affaire...

Dans un modèle, en règle générale, la clarté est préférable à la compacité !

Notre première macro enregistrée

Pour notre première expérience d’enregistrement de macro, constituons un petit tableau en B2:H7 dont nous chercherons à créer par macro les totaux horizontaux et verticaux. Le curseur étant dans le tableau, en D5, nous utilisons la commande Outils – Macro – Nouvelle macro, nous décidons de nommer la macro « Totaux » et de l’enregistrer dans ce classeur :

Dès que la barre d’outils « Arrêter l’enregistrement » dont le nom est d’ailleurs illisible apparaît, cliquons dans le second bouton, « Références relatives », pour que la macro n’enregistre que les adresses relatives.

Ensuite, réalisons les étapes suivantes :
– [Ctrl]-* pour sélectionner le bloc ;
– [Ctrl]-{bas}, [Ctrl]-{bas} et {bas} pour aller en B8 ;
– saisie de « Total », puis {droite} et le bouton de sommation puis [Entrée] pour obtenir la première somme ;
– recopie incrémentée de C8 en G8 ;
– [Ctrl]-{haut}, [Ctrl]-{droite} et {droite} pour aller en I2 ;
– saisie de « Total », puis {bas} et le bouton de sommation puis [Entrée] pour obtenir la première somme ;
– recopie incrémentée de I3 en I8.

Faisons [Alt]-[F11] pour ouvrir l’éditeur Visual Basic et voir le code ainsi généré :

Si nous effaçons la ligne et la colonne de totaux et exécutons la macro, nous constatons qu’elle fonctionne parfaitement bien.

Dans le prochain message, nous verrons comment simplifier ce code.

Arrondi au quart d’heure supérieur

Pour des calculs d’heures supplémentaires, une entreprise désire calculer le temps passé entre deux heures données, arrondi au quart d’heure supérieur.

Nous voyons dans le tableau ci-dessous trois exemples avec à chaque fois l’heure de début de la tâche, l’heure de fin et le temps passé, arrondi au quart d’heure supérieur.

La formule de B3 est la suivante :

=arrondi.sup((B2-B1+si(B1>B2;1;0))*4*24;0)/4/24

Cette formule est rendue plus compliquée par la présence du si() qui permet de gérer le cas de la colonne D, où l’heure de début se situe avant minuit et l’heure de fin dans la matinée.

Nous avons divisé par 4 et 24 car, pour une date dans Excel, la valeur 1 représente un jour : il faut donc diviser par 24 pour obtenir une heure et par 4 pour le quart d’heure.

Remarque 1 – Bien évidemment, nous pourrions remplacer à la fois « 4*24 » et « 4/24 » par « 96 ». On gagnerait ainsi en compacité, au prix hélas de la lisibilité…

Remarque 2 – Il suffit de remplacer arrondi.sup() par arrondi() si l’on souhaite obtenir le quart d’heure le plus proche, au risque de provoquer une grève…