Macro - Excel (vin -1)

Cet exercice est mis en situation sur Excel. L’équivalent Calc est disponible ici.

Comme d’habitude, vous avez donc récupéré le document joint, afin de l’enregistrer sur votre ordinateur, par exemple dans le dossier « Mes documents ».

Voici à quoi ressemble ce fichier : des références de vins – totalement fantaisistes - classées par vignobles...

… une feuille de calcul par vignoble. Toutes ces feuilles étant présentées de façon rigoureusement identiques (avec les mêmes en-têtes de colonnes).

Corriger plusieurs feuilles de calcul en même temps

Imaginons qu’il soit nécessaire de corriger le document de façon, par exemple, à supprimer la colonne A sur toutes les feuilles.

Plutôt que de corriger les feuilles de calcul, les une après les autres, il sera plus rapide d’apporter la modification en une seule fois, après avoir créer un groupe de travail.

Pour cela,cliquez sur la première feuille, puis garder la touche MAJ (Shift) du clavier enfoncée et cliquez sur la dernière feuille :

Cliquez ensuite sur la colonne A de la première feuille et, sur le menu contextuel, choisissez l’option « Supprimer » :

La suppression de la colonne s’effectue normalement sur la feuille « Alsace », la première du groupe, celle qui est affichée à l’écran.

Et si l’on clique sur un autre onglet...

… on peut constater que la même correction s’est également produite pour toutes les autres feuilles du groupe.

Vous avez peut-être constaté (ci-dessus, l’avant-dernière vue) que le groupe de travail (sélection de plusieurs feuilles) s’est immédiatement désactivé en cliquant individuellement sur l’un des onglet du groupe.

Vous devez donc à nouveau constituer votre groupe de travail (retournez sur l’explication si nécessaire), car nous n’avons pas fini d’apporter des corrections.

Le contenu de la colonne « I » (Appréciation) doit être supprimé sur toutes les feuilles. Nous apporterons nos appréciations, plus loin, lorsque nous aurons rassemblé toutes les informations sur une seule feuille. Ce sera l’occasion de découvrir quelques nouvelles fonctionnalités du tableur.

Pour l’instant, vous pouvez constater que ces petites vignettes ont été générées à partir de caractères spéciaux (voir support).

Sélectionnez à partir de la ligne 2, plusieurs lignes de la colonne I sur la feuille « Alsace » et effectuez un clic droit afin de choisir l’option « Effacer le contenu ».

Ce qui permet de supprimer le contenu tout en conservant la mise en forme (Police Wingdings) ; non seulement sur la première feuille du groupe, « Alsace »...

… mais également sur toutes les autres feuilles sélectionnées :

Copier une feuille sur un autre classeur

Le but du jeu consiste maintenant à récupérer les informations du classeur actif (« Vin_détails ») afin de rassembler tout cela sur une seule feuille de calcul.

Avant de débuter les manipulations, il faut savoir que ce qui va être présenté n’est pas forcément la méthode la plus rigoureuse pour arriver au résultat souhaité. On peut considérer, en effet, que ce genre d’opération nécessite plutôt l’usage d’un logiciel de base de données (Access, ou Base) au lieu d’un tableur.

Une autre solution pourrait consister, tout en restant sur nos tableurs, à faire usage de macros, afin d’automatiser un certain nombre de tâches (ce qui ne résoudrait pas tous les problèmes pour autant). Néanmoins l’utilisation de macros, telles que celles qui seraient nécessaires à cet exercice, représente au moins deux écueils :

– cela demande un effort d’apprentissage, assimilable à de la programmation informatique, nettement plus élevé que le contenu général de cet atelier,

– les langages sur lesquels reposent les macros d’Excel et Calc ne sont pas compatibles.

Nous essayerons donc de réaliser notre synthèse, par copier-coller, sur notre tableur sans effectuer de macro. Outre le fait que cela nous donnera l’occasion de découvrir quelques manipulations intéressantes à connaître de façon plus générale, nous pourrons au moins insister sur les limites du copier-coller dans un tableur lorsqu’il s’agit de traiter ce type d’opération.

Créez un nouveau document :

Un fois que ce nouveau document s’affiche à l’écran, enregistrez-le immédiatement en tant « Vin_global », au même niveau d’arborescence que le premier document actif (« Vin-détails »).

Afin de bien clarifier la situation, mieux vaut préciser que nous avons désormais deux documents actifs à l’écran. Vous pouvez le vérifier en passant par le menu « Fenêtre » :

Au cours des étapes suivantes de l’exercice, nous aurons besoin de passer en permanence d’une fenêtre à l’autre. Il va donc falloir être extrêmement attentif aux indices permettant d’identifier sur quel document vous vous trouvez.

Rappelons que parmi ces indices, vous avez la barre de titre de la fenêtre, ainsi que la barre de tâches de Windows, laquelle permet, de façon plus générale, de vérifier la présence des fenêtres ouvertes. En cliquant sur un bouton, vous affichez la fenêtre :

De cette façon, affichez à nouveau le document « Vin_détails ».

Normalement vous devriez avoir encore, sur ce classeur, votre groupe de travail (toutes les feuilles sélectionnées).

Voici une autre façon de désactiver ce groupe de travail : faîtes un clic droit sur les onglets et sélectionnez l’option « Dissocier les feuilles » :

Faîtes ensuite un nouveau clic droit sur l’onglet « Alsace » et sélectionnez l’option « Déplacer ou copier » :

Il va falloir copier cette feuille dans l’autre document actif. Cliquez sur la liste « Dans le classeur » de la boîte de dialogue et sélectionnez « Vin_global »

Cliquez ensuite sur la case « Créer une copie » :

Affichez le classeur « Vin_global » afin de vérifier que la copie s’est correctement opérée

Vous constatez que la feuille, sur cet autre classeur, a conservé le nom d’origine (« Alsace »)

Faîtes un clic droit sur cet onglet afin de renommer cette feuille – dans le classeur « Vin_global » - en tant que « Synthèse » :

Coller avec liaison

En cliquant sur l’une des cellules collées, dans le classeur « Vin_global », on se rend compte que le tableur a créé ce document exactement comme si l’on avait effectué un « Enregistrer sous... ».

Les indications qui apparaissent dans le document « cible » après notre copier-coller, c’est à dire la feuille « Synthèse » (de « Vin_global ») ne font nullement référence au document « source » (la feuille « Alsace » du classeur « Vin_détails » )

Il n’y a plus aucune relation entre ces deux documents. Si, par exemple, on apporte une modification dans l’un des deux classeurs, la correction ne se répercutera pas dans l’autre.

Voici comment conserver la liaison :

– Activez le document source - « Vin_détails » et sélectionnez sur la feuille « Alsace » le groupe de cellules A2 : G4.

– Copiez (par exemple, à l’aide du bouton de la barre d’outils)

– Affichez le document cible, « Vin_global »...

– Sur la feuille « Synthèse », sélectionnez également le groupe de cellules A2 : G4...

– Passez par le menu « Édition / Collage spécial... »

– Sur la boîte de dialogue cliquez sur le bouton « Coller avec liaison »

Désormais, les deux feuilles sont liées. Il suffit d’observer la barre de formule pour le constater.

Afin de vérifier la liaison, nous allons faire l’expérience suivante :

– retournez sur le classeur « Vin_détails » et modifiez le contenu d’une des cellules de la feuille « Alsace » :

Si vous retournez sur l’autre classeur - « Vin_global » - vous pouvez constater que la modification s’est automatiquement reportée.

La liaison entre plusieurs classeurs par collage spécial peut donc être, dans certains cas, une solution intéressante. Mais le premier constat qui s’impose c’est qu’il est impératif, pour que cela fonctionne, que les documents soient localisés l’un par rapport à l’autre dans les mêmes conditions qu’au moment de leur liaison. Il suffit de déplacer l’un des classeurs à un autre niveau d’arborescence pour rompre la liaison ; voilà pourquoi il est préférable de les enregistrer dans le même dossier.

Avant de copier les autres feuilles du classeur « Vin_détails » dans la feuille « Synthèse », il est préférable de procéder à la mise en forme des cellules « cibles ».

Pour cela, sur « Vin_global », sélectionnez le groupe de cellules A2 : H2 et cliquez sur l’outil « Duplication de mise en forme » (le pinceau)...

… appliquez ensuite le pinceau sur les lignes suivantes.

Après cela, retournez sur l’autre classeur, « Vin_détails » et cliquez sur l’onglet « Bordeaux ».

Sélectionnez le groupe de cellule A2 : G8 et copiez.

C’est volontairement que nous avons pris une ligne supplémentaire (en 8).

Retournez sur le classeur « Vin_global », placez-vous en A5 et recommencez le collage spécial avec liaison.

Vous constatez que la dernière ligne (ici en 11) s’est remplie avec des zéros.

Retournez sur le classeur « Vin_détails » et apportez, en ligne 8, de nouvelles informations...

De retour sur le classeur « Vin_global » on constate là encore que la modification s’est immédiatement répercutée de la feuille source vers la feuille cible.

C’est parfait, me direz-vous.

Oui mais, le problème, c’est qu’il ne faut pas en déduire pour autant que toute modification sur le fichier source se répercutera dans les mêmes conditions sur le fichier cible.

Ne pas modifier la structure des données

Prêtons-nous à une petite expérience :

Activez, sur le classeur « Vin_détails », l’onglet « Alsace » et faîtes un clic droit sur l’en-tête de ligne 3 afin d’insérer une nouvelle ligne :

Insérez une quatrième référence de vin sur votre feuille.

Enregistrez et fermez.

En fait il n’est pas indispensable que le fichier source soit ouvert pour actualiser le fichier cible. Afin de vérifier l’état de la liaison et forcer l’actualisation des données sur la cible, il est même indispensable que le fichier source soit fermé.

Retournez sur le classeur cible de la liaison (« Vin_global ») : alors que la source comportait quatre références de vin d’Alsace, la cible n’en comporte toujours que trois.

Voici comment vérifier l’état de la liaison :

Sur la boîte de dialogue, cliquez sur le bouton « Vérifier l’état »

C’est à ce moment, si le fichier source est ouvert, qu’il est demandé de le fermer afin de vérifier la liaison.

Dans notre cas, comme nous l’avions fermé, on nous indique que tout est OK, côté liaison.

Et même après avoir cliqué sur le bouton « Mettre à jour les valeurs » notre fichier cible, n’affiche imperturbablement que trois références de vin d’Alsace, alors que la source en comporte quatre.

Après la précédente remarque concernant la nécessité de pas déplacer les classeurs liés, un deuxième constat s’impose : sous peine de rompre la cohérence de la liaison, il faut évitez de modifier la structure des données du fichier source.

Ces réserves étant prises en compte, vous pouvez procéder à la récupération des autres feuilles « source ».

Vous constaterez ainsi qu’il est possible de lier les données après un classique « coller » (à partir d’Excel 2001) .

Pour cela, cliquez sur cette petite case et cochez l’option « Lier les cellules »

Vous devriez, normalement arriver à ce résultat, après avoir collé votre dernière feuille source.

Sous-totaux

Nous allons maintenant activer une fonctionnalité du tableur permettant de présenter sous une forme « plus rassemblée » les informations.

En général, les sous-totaux – puisqu’il s’agit de cela - sont prévus pour recalculer des valeurs numériques par sous-catégories. Cette fonctionnalité permet également – sous certaines conditions - de présenter les données dans une logique assez proche des filtres (voir support).

Cliquez sur n’importe cellule du tableau, puis passez par le menu « Données / Sous-totaux... »

Sur la boîte de dialogue, il va falloir indiquer un certain nombre de critères :

– Commencez par vérifier que l’option « Vignoble » est bien sélectionnée dans le champ « A chaque changement de ».

– Comme je l’ai déjà indiqué, les sous-totaux sont associés à une fonction (contrairement aux filtres). Le plus souvent, on additionne les sous-totaux. Dans le cas de notre tableau, l’addition des valeurs numériques disponibles ne correspond, en fait, à pas grand-chose d’intéressant. J’ai plutôt choisi de calculer le prix moyen des produits (vins) par catégories (vignobles) ; pour cela, dans le champ « Fonction » je sélectionne « Moyenne ».

– Il faut, maintenant préciser sur quelle valeur (colonne) du tableau le tableur doit faire l’opération. Je choisis donc, sur ce troisième champ « Ajouter un sous-total » l’option « Prix ». Si vous travaillez sur un tableau qui ne comporte pas de valeur numérique à traiter, il ne sera pas possible d’effectuer un sous-total. Mieux vaut, dans ce cas, utiliser la fonctionnalité « Filtre » du tableur pour regrouper vos catégories, tel que nous le verrons par la suite.

– Cliquez enfin sur le bouton « OK ».

Voici à quoi ressemble une feuille de calcul avec des sous-totaux.

Tout d’abord, le tableur a intercalé des lignes pour inscrire les valeurs ; observez, par exemple en ligne 5, le prix moyen des vins du vignoble « Alsace » :

Dans l’angle supérieur gauche, vous constatez qu’un système d’étiquetage est apparu.

Cela correspond au trois niveau d’affichage disponibles sur notre tableau :

– Le niveau 3 (par défaut) présente l’intégralité du tableau.

– Si vous cliquez sur 2, vous n’affichez que les sous-totaux.

Les petits symboles + et -, selon une convention que l’on retrouve souvent en informatique, permettent de développer (+) ou rétracter (-) une arborescence.

Par exemple, si, tout en conservant l’affichage en position « niveau 2 »...

...vous cliquez sur le + placé en face de Bourgogne, vous ne dévoilerez que le contenu de cette catégorie.

– En activant le niveau 1...

… vous n’obtiendrez que la moyenne générale appliquée à toutes les catégories.

Cliquez à nouveau sur le niveau 3 afin de tout développer, puis éventuellement rétracter sur l’une ou l’autre des catégories.

C’est tout pour l’instant : enregistrez, fermez.

La suite de cet exercice se trouve ici.