Importer trier, filtrer une source de données (Excel)

Comme d’habitude, vous trouverez en fichiers joints les documents de travail de l’exercice ; c’est-à-dire :

– une archive au format ZIP

– le document, format Excel,

La présente page a été réalisée à partir d’une mise en situation sur Excel.

Vous trouverez, sur cet autre tuto, l’équivalent sur Calc.

Quelques différences de traitement significatives entre les deux tableurs nécessitent parfois une explication distincte, même si la logique de traitement générale de l’exercice est applicable quelle que soit la boutique fréquentée (Microsoft ou OpenOffice).

Voilà pourquoi, après une explication commune aux deux tableurs, vous trouverez, un peu plus bas, sur cette page, des renvois par lien hypertexte permettant aux utilisateurs de Calc de garder le fil de l’explication.

Récupérez l’archive ZIP sur votre ordinateur (clic droit du bouton de la souris et « Enregistrez la cible du lien sous.. ») afin de l’enregistrer, par exemple, dans votre dossier « Mes documents » (ou « Documents » sur Vista).

Une fois l’archive téléchargée, faîtes un clic droit de la souris sur ce fichier et décompressez par « Extraire »…

… afin de placer le contenu de l’archive…

… au niveau même de l’arborescence ; c’est-à-dire dans « Mes documents », (si tel était votre choix de départ).

Ouvrez le dossier « source_donnes » (désolé, il manque un « é »). A l’intérieur vous devriez trouver ces deux fichiers :

– un fichier au format texte (« Liste clients_modèle.txt ») ; il s’agit de votre document de travail.

– une image (« src_txt_xls.jpg ») ; c’est un modèle sous forme d’image que vous utiliserez, à un moment donné de l’exercice.

Un fichier texte pour un tableur ?

Comme vous l’aurez remarqué, le document de travail – je l’ai récupéré, sur un vieux bouquin d’exercices Excel – est au format « texte ». Il ne s’agit ni d’une feuille de calcul Excel, ni d’un document classique de type « tableur », quel que soit le format (.ods, par exemple)

Il est intéressant, en effet, de savoir que votre tableur est capable de récupérer des données saisies sur un simple traitement de texte du type « Bloc-notes » (anciennement appelé « Note pad ») ; ce dernier permettant de lire et saisir tous les caractères du clavier, sans aucune option de mise en forme.

Avant d’ouvrir ce document dans le tableur, nous allons donc l’éditer à l’aide du « Bloc-notes », afin d’en observer le contenu.

Pour lancer le Bloc-notes (si vous n’avez créé aucun raccourci), il est nécessaire de passer par le cheminement suivant :

– Menu « Démarrer »

– Tous les programmes

– Accessoires

– Bloc-notes

Après avoir ouvert le Bloc-notes, cliquez sur le menu « Fichier / Ouvrir »…

… afin de passer par « Mes documents/ source_donnes » pour ouvrir « Liste clients_modèles.txt » :

Comme vous le constatez, il s’agit bien d’un document texte.

Même ce n’est pas très lisible, on constate que ce fichier se présente comme une liste : code référence, nom d’entreprise, nom du représentant, adresse, etc.

Chaque élément étant séparé du suivant par un « espace » (blanc).

Fermez le Bloc-notes.

Nous allons éditer ce même fichier à l’aide d’un traitement de texte : Word ou OpenOffice Writer (c’est ce dernier qui a été pris comme exemple ci-dessous).

Une fois votre texte à l’écran, activez l’affichage des « caractères invisibles » (qui ne seront pas imprimés) en cliquant sur le bouton (1) ¶. Vous constatez que certains des espaces entre les mots, observés tout à l’heure sur le « Bloc-Notes », sont ici représentés par des symboles caractéristiques :

– Une petite flèche (2), laquelle apparaît sur votre traitement de texte (après avoir activé les caractères invisibles) lorsque vous appuyez sur la touche « Tabulation » du clavier (placée à gauche du « A »).

– Le « Pied de mouche » (3) (¶), autre caractère invisible, intégré, celui-ci, lorsque vous appuyez sur la touche « Entrée » du clavier, autrement appelé « Retour chariot ». Le bouton de la barre d’outils que nous avons activé tout à l’heure représente également un « pied de mouche » mais, en fait, ce symbole placé dans la barre d’outils permet d’activer « l’affichage de tous les caractères invisibles » (pied de mouche, tabulations, espaces, insécables, symbole de niveau de style, etc.). : Voir ce support à ce sujet.

Un petit point, centré en hauteur, sépare certains mots ; par exemple entre le prénom et le nom : « Alfred Futterkis ». Ce petit point représente le caractère invisible « espace » (non imprimable) qui est intégré au texte à chaque fois que l’on appuie sur la barre d’espace du clavier ; à ne pas confondre avec le signe de ponctuation « point », imprimable, et qui s’affiche aligné en bas du caractère et non centré en hauteur.

Les caractères « tabulation » et « pied de mouche » représentent des « séparateurs » qui seront utilisés pour intégrer la source de données (ce document texte) dans la structure tabulaire de notre feuille de calcul :

– chaque caractère « tabulation » délimite le champ d’une cellule,

– chaque caractère « pied de mouche » (touche « Entrée ») indique qu’il faut passer à la ligne.

Fermez votre traitement de texte, sans enregistrer.

Retournez sur le tuto Calc, pour la suite de l’exo, si vous êtes sur OpenOffice.

Importer une liste au format texte dans le tableur

Lancez Excel et passez par le menu « Fichier / Ouvrir… » :

Naviguez jusqu’à notre dossier « source_donnes », puis activez la liste « Type de fichiers » afin de sélectionner l‘option « Fichier texte »…

… cela devrait permettre d’afficher le fichier « source_donnes.txt ». Cliquez sur ce dernier, puis cliquez sur « Ouvrir » (ou double-clic sur le fichier direct).

Excel vous présente ensuite la boîte de dialogue suivante :

Cet « assistant d’importation » (ci-dessus) indique que le programme a identifié la présence de caractères séparateurs.
Cliquez directement sur le bouton « Suivant » afin de passer à la deuxième étape de l’assistant (ci-dessous).

A présent, il s’agit de confirmer que le caractère « Tabulation » sera utilisé pour délimiter les « champs », c’est-à-dire les cellules. Remarquez que notre document prend forme dans l’aperçu (partie inférieure de l’assistant) :

Cliquez sur « Suivant ».

La dernière étape permet de paramétrer le format appliqué aux données, colonne par colonne. Par exemple, il est possible de définir qu’une colonne comporte des dates.

Nous n’auront pas besoin de cette option, puisque le document sur lequel nous allons travailler ne comporte pas ce type d’information. Cliquez sur le bouton « Terminer » :

Excel a correctement intégré la source de données dans la feuille de calcul.

Insérer les champs d’en-tête

Cliquez sur l’en-tête de ligne 1 du bouton droit de la souris afin d’insérer une ligne :

Tapez ensuite, dans cette ligne 1, les en-têtes de colonnes suivant le modèle ci-dessous (vous pouvez également, pour plus de lisibilité, ouvrir le fichier joint « src_txt_xls.jpg », présent dans l’archive ; vous retrouverez en ligne A1 : I1 le contenu des en-têtes) :

Adapter la largeur des colonnes au contenu

Afin d’adapter automatiquement la largeur des colonnes à leur contenu, après avoir cliqué sur une en-tête de colonne (par exemple B, ci-dessous), activez l’option suivante :

Voici ce que devrait donner l’ajustement automatique de la largeur de colonne :

Si vous procédez ainsi pour toutes les colonnes, il est fort probable que la totalité de votre document ne s’affiche pas en entier à l’écran.

Dans ce cas, vous pouvez régler le zoom d’affichage pour vérifier l’allure générale du tableau :

Sélectionnez ensuite la première ligne afin de modifier les options de mise en forme afin de centrer en hauteur le texte dans la cellule :

Appliquez ensuite un fond tramé de couleur bleu :

« Enregistrer sous » une version au format Excel

Il est préférable de créer une version « tableur » du document, car pour l’heure il s’agit toujours d’un document texte (regardez la barre de titre de la fenêtre).

Pour cela, passez par le menu « Ficher / Enregistrer sous… » (voir support) :

Dans le champ « Type de fichier », choisissez, cette fois-ci, l’option « Classeur Microsoft Office Excel » :

Cliquez enfin sur le bouton « Enregistrer » :

Désormais, vous travaillez sur une variante au format « Excel » (.xls) du document « texte » (.txt) initial. Toutes les modifications apportées ne prendront effet que sur le fichier Excel actif :

Retournez sur le tuto Calc, pour la suite de l’exo, si vous êtes sur OpenOffice.

Figer les volets

Cliquez en A2 puis passez par le menu « Fenêtre / Figer les volets ».

Figer les volets ?

Il faut reconnaître que l’expression n’est pas d’une clarté exemplaire, justement.

Peut-être même que, lorsqu’il a fallu, pour la première fois, mettre du français sur cette procédure, le traducteur, arrivé au terme de sa pénible journée de labeur et, emporté par quelques sournoises inflexions à la somnolence, s’est, en quelque sorte, libéré en figeant les volets. Vrac.

Toujours est-il que les utilisateurs d’Excel, depuis la nuit des temps, sont spontanément capables de comprendre et de retenir à quoi correspondent ce genre de terminologies exotiques.

Alors, venons-en aux explications pratiques :

Une fois que vous avez « figé les volets » en A2, vous pouvez faire défiler votre feuille de calcul vers le bas (à l’aide de la molette de la souris ou de l’ascenseur) sans pour autant perdre de vue la ligne A1.

Ce qui offre l’avantage, dans notre cas, de garder à l’écran les en-têtes de colonnes tout en se donnant la possibilité de passer en revue chaque « enregistrement » (chaque ligne) de notre source de données, et ce jusqu’à la dernière ligne :

Insérer des données dans la liste à l’aide du formulaire (grille)

Imaginons qu’il soit nécessaire d’entrer de nouvelles données dans notre liste.

Excel permet, pour cela, de faire appel à une fonctionnalité assez pratique : le formulaire (ou la grille, sur les anciennes versions du programme).

Cliquez sur le menu « Données / Formulaire… » :

Voici en quoi consiste ce formulaire (ci-dessous).

Les en-têtes de colonnes (non modifiables) sont affichés en face de champs, dans lesquels apparaissent les données correspondant à chaque enregistrement.

Il est donc possible de corriger les données par l’intermédiaire de cette petite fenêtre, mais aussi, d’entrer une nouvelle référence : cliquez sur le bouton « Nouvelle » :

La fenêtre présente alors des champs vides :

Il suffit alors de taper les informations ; commençons par ce nouvel enregistrement de la liste, ci-dessous.

Quand vous avez fini, cliquez à nouveau sur le bouton « Nouvelle »

Récupérez le fichier « src_txt_xls.jpg » qui se trouve dans l’archive et, après l’avoir éventuellement imprimé, complétez votre saisie à partir de ce modèle :

Lorsque vous avez terminé la saisie de la dernière référence, cliquez sur le bouton « Fermer ».

Trier

Ces nouveaux enregistrements se sont inscrits en bas de la liste :

Comme vous l’avez peut-être constaté, avant nous y ajoutions ces nouveaux enregistrements, notre tableau était entièrement classé par ordre alphabétique de « référence client ».

Nous allons, maintenant, réorganiser le tableau afin que nos modifications s’intègrent de façon cohérente avec les autres données.

Passez par le menu « Données / Trier… »

Sur la boîte de dialogue, nous allons même modifier les critères de tri de l’ensemble du tableau :

– le premier critère était donc paramétré sur « REF Client » ; nous préférons que ce soit « Nom entreprise ». Pour cela, cliquez sur la liste et sélectionnez la bonne option :

– au cas où, sur notre liste, la même entreprise serait représentée par plusieurs personnes, il serait judicieux de définir, un deuxième niveau de tri : cliquez ici sur « Nom resp. »

Le mode de tri des données retenu est donc « par nom d’entreprise » puis « par représentants », chacun de ces critères étant « classé par ordre alphabétique croissant »,

Cliquez sur le bouton « OK » :

Voici le résultat ; vous constatez que les enregistrements effectués par l’intermédiaire du formulaire ont bien été intégrés dans la globalité du tableau (et nous constatons qu’il n’y a bien qu’un seul représentant par société) :

Retournez sur le tuto Calc, pour la suite de l’exo, si vous êtes sur OpenOffice.

Filtrer

Voyons, à présent une fonctionnalité complémentaire au tri, qui peut s’avérer très pratique, surtout si vous avez affaire à une liste importante de données : filtrer.

Passez par le menu « Données / Filtrer… » :

Après cela, ces boutons, avec une petite flèche, sont intégrés au niveau de chaque cellule d’en-tête de votre liste :

Il suffit alors de choisir l’un des en-têtes, par exemple « Ville », puis de sélectionner l’option désirée …

… afin d’afficher le tableau, selon le critère de filtrage suivant : « je ne veux voir que les entreprises berlinoises » :

Autrement dit vous avez à votre disposition au moins deux outils permettant d’organiser les données de votre tableau :

– le tri, qui conserve la totalité des données à l’écran, mais les organise selon différents critères,

– le filtre, qui présente une vue sélective des données.

Modifier rapidement la largeur de toutes les colonnes

Sachant que nous sommes engagés dans des logiques d’affichage sélectif, il serait peut-être possible d’optimiser l’affichage des colonnes – je veux parler de la largeur – car il n’est plus nécessaire que notre tableau prenne autant de place à l’écran.

Voici ce que je vous propose : cliquez sur la case vierge placée à l’intersection des entêtes de colonnes et des en-têtes de lignes.

Cela a pour effet de sélectionner l’intégralité de la feuille de calcul (voilà pourquoi à partir de la vue ci-dessous, notre document est tout bleu).

Réduisez ensuite l’une des colonnes.

Après avoir lâché la souris, toutes les colonnes de votre feuille s’adaptent à la largeur que vous venez de définir sur l’une d’entre-elles.

C’est la façon la plus simple pour modifier et uniformiser à la volée la largeur de toutes les colonnes du tableau :

Filtrages successifs

Reprenons un autre exemple : voyons combien d’entreprise (ou de personnes) parisiennes comporte notre source de données :

La feuille se réactualise en fonction de ce nouveau critère de tri « Ville ». Normal :

Nous voulons (ah, ça y est, je me mets à parler à votre place !) maintenant, modifier le critère de filtrage : ce ne sera plus « Ville », mais « Titre du responsable ».

Attention ! A moins que vous ne vouliez cumuler deux critères de filtrage, par exemple « je ne veux afficher que les directeurs des ventes parisiens », vous devez impérativement annuler le filtre actif avant de paramétrer le suivant.

Pour cela, rétablissez le critère « Tous » sur le champ « Ville » :

Puis indiquez votre nouveau filtrage sur le champ « Titre resp. » :

Rétablissez l’affichage sans aucun filtrage.

Observez que vous pouvez cumuler des options de tri (croissant ou décroissant) à votre filtre.

Si vous souhaitez revenir à un affichage « complet » des données, il est toujours possible d’ajuster la largeur des colonnes à leur contenu :

Retournez sur le tuto Calc, pour la suite de l’exo, si vous êtes sur OpenOffice.

Options de mise en page sur un tableau long

Réduisez la taille d’affichage écran par le zoom, afin que vous puissiez voir le tableau en entier :

Cliquez sur l’extrémité inférieure droite du tableau :

Puis, tout en maintenant le bouton de la souris enfoncé, remontez jusqu’à la cellule A1.

En fait ce n’est pas forcément la meilleure méthode pour sélectionner l’intégralité d’un long tableau, tel que celui-ci ; pour ma part je préfère utiliser les raccourcis claviers suivants :

– CTRL+bas

– CTRL+droit

– CTRL-MAJ+Origine (après avoir libéré les volets, sinon : MAJ+haut, en plus)

Je constate simplement, qu’en dépit de ces recommandations, vous préférez bien souvent vous débattre avec votre souris (ou pavé tactile). Alors comme je n’aime pas contrarier les gens avec de telles peccadilles, voici où j’en suis arrivé : vous voulez la souris ? Prenez donc la souris !

Permettez toutefois le conseil suivant : effectuez votre sélection depuis l’extérieur du tableau pour remonter vers l’intérieur. Cela vous évitera, par exemple, de visiter après un glisser-non contrôler, la cellule « IT65526 ».

Bref, après avoir sélectionné votre tableau, c’est toujours la même routine :

– définir la zone d’impression…

– options de mise en page.

Répéter la première ligne sur toutes les pages

Bien que cela ne soit pas un passage obligé, je vous propose d’avoir immédiatement un aperçu.

Vous savez certainement que cela est possible depuis cette boîte de dialogue « mise en page » en cliquant ici :

Il y a effectivement quelques ajustements à opérer.

Le tableau, en l’état, n’affiche que trois colonnes. Ce qui veut dire qu’il serait divisé, au mieux, sur deux pages en largeur. Sachant qu’en longueur, vu le nombre de lignes, on doit avoir aussi plusieurs pages, ce n’est même pas la peine de l’imprimer : il serait totalement illisible.

Inversement, il est possible de retourner sur les options de mise en page depuis l’aperçu. Il faut cliquer sur ce bouton « Page… » : n’hésitez pas à faire ces aller et retour d’une boîte de dialogue à l’autre, c’est très pratique.

On commence par modifier l’orientation : paysage.

Retour sur l’aperçu après avoir cliqué sur le bouton OK.

Hum… c’est mieux mais cela ne suffit pas : nous n’avons gagné qu’une seule colonne.

Retournons sur les options de mise en page :

Vous vous souvenez (voir exercice « primeur ») que vous pouvez ajuster la dimension de votre document au format d’impression. Nous l’avions fait en indiquant un pourcentage d’agrandissement (on peut évidemment réduire de la même façon).

Nous pouvons également demander au tableur d’adapter automatiquement la dimension du document au format du papier.

C’est ce que nous allons faire ici :

– vérifiez que vous êtes bien sur l’onglet « Page » (onglet « Classeur » sur OpenOffice)

– cliquez sur le bouton radio (rond) « Ajuster »

– tapez 1 dans le champ « page(s) en largeur » ; ce qui permettra d’imposer l’impression de la totalité des colonnes sur une seule page

– tapez 3 dans le champ « en hauteur ». Dans le doute vous pouvez indiquer une valeur plus importante : mieux vaut plus que pas assez. Si vous tapez, par exemple 5 pages en hauteur et que le contenu du tableau rentre sur 3 , Excel n’imprimera que 3 pages.

Cliquez sur le bouton « OK » afin de revenir sur l’aperçu.

Cela commence à prendre forme.

Oui, mais si on fait défiler la deuxième page (ci-dessous), on se rend compte que les en-têtes de colonnes n’y sont pas.

Pas très pratique. Il serait souhaitable que, sur le papier, nous ayons le même confort d’affichage que nous avait procuré le figer les volets à l’écran : garder en permanence les en-têtes de colonnes.

Pour cela, il va falloir fermer l’aperçu.

Puis cliquez sur le menu « Fichier / Mise en page ».

Notez que, cette fois-ci, je ne vous ai pas proposé de passer sur la boîte de dialogue « Mise en page » depuis l’aperçu. Cela tient au fait que le réglage que nous allons faire n’est pas disponible depuis l’aperçu.

Cliquez sur l’onglet « Feuille » puis sur le petit bouton placé à droite du champ « Ligne à répéter en haut » :

L’assistant se rétracte alors pour vous donner la main sur le document.

Cliquez sur l’en-tête de ligne 1

Revenez sur l’assistant en cliquant sur ce petit bouton :

Les références (en coordonnées absolues) de la ligne à répéter en haut de chaque page imprimées sont désormais insérées dans le champ.

Un petit retour sur l’aperçu permettra de vérifier cela.

Première page :

Dernière page :

Fermez l’aperçu.

Il faut maintenant mettre les bordures et comme elles doivent apparaître sur la totalité du tableau, le plus simple consistera à récupérer la zone d’impression…

… et d’appliquer « toutes les bordures » :

Encore un petit aperçu…

La bordure manquante à gauche n’est qu’un simple problème d’affichage écran ; cela arrive surtout lorsque l’on applique une réduction. Si vous avez un doute vous pouvez toujours n’imprimer que la première page, pour voir et éviter de gâcher encre et papier.

Il manque encore une chose : le pied de page, avec les champs :

– numéro de page et nombre de page

– la date

– le nom du fichier.

Tiens, je me rends compte que je ne vous ai jamais indiqué comment inséré ces champs sur Excel.

Oh mais ce n’est pas bien grave puisque c’est expliqué sur le support Calc !

Ce n’est pas pile poil les mêmes options d’interface que sur Excel, mais cela y ressemble beaucoup. Un petit effort d’adaptation : rien de tel pour se forger le caractère…

Dernière chose, avant de partir : quand vous aurez mis en place votre super pied à champs, n’oubliez pas d’enregistrer !