1. Posez le problème
Pour illustrer notre propos, nous prendrons l’exemple d’un club de sport qui prête des articles à ses membres. Le responsable des prêts a créé le classeur Sports.xls, qui se compose de deux feuilles, contenant chacune
un tableau. La première, Prêts, mentionne les articles prêtés (voir écran 1). Vous constaterez la présence d’une colonne Numéro (numéro de prêt), contenant des chiffres en ordre croissant. Cette colonne n’est
pas obligatoire, son intérêt est de vous permettre, quand vous l’aurez constitué, de trier le tableau final dans le même ordre que la table des prêts. La seconde feuille, Clients, contient la liste des clients et leurs coordonnées
(voir écran 2). Pour que chaque article puisse être associé à son emprunteur, les feuilles Prêts et Clients contiennent chacune une colonne nommée COD_CLI, un code client de trois caractères.
Ainsi, dans la feuille Prêts, chaque article emprunté est suivi du code du client emprunteur. Nous allons vous montrer comment concevoir un tableau qui synthétise ces feuilles.
2. Saisissez les données
Lancez Excel et créez le classeur Sports.xls. Donnez aux deux premières feuilles de ce classeur les noms Prêts et Clients (pour changer le nom d’une feuille, double-cliquez sur son onglet, en
bas à gauche, tapez le nouveau nom et appuyez sur Entrée). Saisissez, dans les deux feuilles, les valeurs illustrées dans les écrans 1 et 2. Enfin, enregistrez et fermez le classeur Sports.xls.
3. Créez une requête et ajoutez les tables
Pour réaliser un troisième tableau combinant des éléments des deux autres, vous allez créer un nouveau classeur. Vous pourriez vous contenter de lancer une nouvelle feuille dans le même classeur, mais dans la gestion de données,
il est préférable de séparer le fichier contenant les informations initiales de celui qui réalise leur croisement. Et pour combiner les données tirées des tableaux, vous allez passer par le module Microsoft Query. Créez un nouveau
classeur vierge et déroulez le menu Données, Données externes, Créer une requête. Dans la fenêtre Choisir une source de données, désélectionnez l’option Utiliser l’Assistant…, sélectionnez Fichiers
Excel et cliquez sur OK. Le logiciel Microsoft Query démarre et ouvre une fenêtre. Parcourez votre disque dur afin de sélectionner le classeur Sports.xls et cliquez sur OK. Cliquez alors sur
Options, cochez les quatre cases affichées et validez par OK. Dans la liste Table, sélectionnez Prêts$ (et non Prêts$_) et cliquez sur Ajouter. Sélectionnez
Clients$ et cliquez de nouveau sur Ajouter (voir écran 3). Cliquez sur Fermer.
4. Reliez les deux tables
Dans la zone grisée, en haut de la fenêtre de Microsoft Query, vous voyez apparaître les deux tables que vous avez ajoutées : Prêts et Clients (ne tenez pas compte des caractères $).
Vous pouvez déplacer une table en faisant glisser son en-tête à la souris. Il faut établir un lien entre le code client (champ COD_CLI) de la table Prêts et le même champ de la table Clients. Pour cela, faites
glisser le mot COD_CLI de la table Prêts sur celui de la table Clients. Un trait apparaît pour symboliser ce lien (voir écran 4).
5. Spécifiez les colonnes à afficher
Vous voulez que, dans votre tableau de synthèse, s’affichent les colonnes Numéro du prêt, Article emprunté, Date, Code client, Nom et Ville du client. Dans la zone grisée de la fenêtre de Microsoft Query, cliquez deux
fois sur le mot Numéro de la table Prêts. La colonne Numéro et son contenu apparaissent dans la partie inférieure de la fenêtre. Les numéros ne sont pas classés, c’est normal. Faites de même pour ajouter
Article, Date, COD_CLI (celui de la table Prêts), Nom et Ville en choisissant toujours la table appropriée. Les colonnes s’affichent dans le volet inférieur de Microsoft Query (voir écran
5).
6. Copiez les résultats dans une feuille Excel
Dans la barre d’outils de Microsoft Query, cliquez sur le bouton Renvoyer les données. Cliquez éventuellement sur une cellule de la feuille Excel pour indiquer l’emplacement où les résultats doivent apparaître (si
vous ne le faites pas, le tableau de résultats commencera à la cellule A1). Enfin, cliquez sur OK. Le tableau s’affiche. Vous remarquerez que les dates sont au format jj/mm/aaaa hh:mm (par exemple
21/02/2006 00:00). Pour en changer le format, sélectionnez la colonne de dates, faites un clic droit, choisissez Format de cellules. Dans la liste Catégories, cliquez sur Date, choisissez un format
dans la liste Types et validez par OK (voir écran 6). Enregistrez le classeur sous le nom Résultats.xls.
7. Remettez le tableau en ordre
A présent, vous allez trier le tableau de synthèse dans le même ordre que la table des prêts. Pour cela, cliquez dans une cellule quelconque de la colonne A, là où se trouvent les numéros. Attention : une seule
cellule doit être sélectionnée. Puis, cliquez sur le bouton A-Z (Tri croissant) de la barre d’outils Standard. Votre tableau est prêt.
8. Ajoutez une condition…
Vous voulez maintenant limiter l’affichage aux seuls prêts consentis aux Parisiens. Pour cela, cliquez dans une cellule quelconque du tableau de résultats. La barre d’outils Données externes doit apparaître (sinon
déroulez le menu Affichage, Barres d’outils et cochez Données externes). Dans cette barre d’outils, cliquez sur le bouton Modifier la requête. La fenêtre de Microsoft Query réapparaît. Dans cette fenêtre,
déroulez le menu Affichage et cochez Critères. Un volet s’affiche, comportant trois lignes Champ, Valeur et Ou. Cliquez dans la cellule vide placée immédiatement à droite du mot Champ.
Un bouton apparaît à droite de cette cellule. Cliquez sur ce bouton et, dans la liste qui apparaît, choisissez Clients$Ville. Le mot Ville apparaît dans la cellule. Cliquez en dessous, dans la ligne Valeur,
saisissez Paris et appuyez sur Entrée. Dans le volet inférieur de Microsoft Query, seuls les clients de Paris sont maintenant visibles (voir écran 7). Vous pouvez, sur le même principe, ajouter d’autres
conditions en les plaçant dans les colonnes adjacentes.
Par exemple, pour n’afficher que les trois premiers prêts, choisissez, dans la colonne voisine, le champ Prêts$Numéro et la valeur <=3 (inférieur ou égal à 3). Toutes les conditions sont reliées par
ET. En d’autres termes, avec l’exemple ci-dessus, vous n’afficherez que les prêts consentis à des Parisiens ET portant un numéro au plus égal à 3. Pour récupérer ce nouveau tableau dans votre feuille Excel, cliquez simplement sur le bouton
Renvoyer les données de la barre d’outils. Le tableau est copié au même emplacement que celui que vous aviez choisi à l’étape 6.
9. … et supprimez-la
Vous voulez rétablir l’affichage de tous les prêts. Pour cela, cliquez dans une cellule quelconque du tableau de synthèse. Dans la barre d’outils Données externes, cliquez sur le bouton Modifier la
requête. Dans la fenêtre de Microsoft Query, déroulez le menu Critères, Supprimer tous les critères, puis cliquez sur le bouton Renvoyer les données.
10. Mettez à jour le tableau de résultats
Quand vous modifiez le contenu du classeur Sports.xls en changeant le contenu de l’une des tables, le classeur Résultats.xls n’est pas automatiquement mis à jour. Pour l’actualiser, rien de plus
simple : fermez le classeur Sports.xls. Puis, ouvrez Résultats.xls, cliquez dans le tableau de synthèse et, dans la barre d’outils Données externes, cliquez sur le bouton Actualiser tout. Si
une fenêtre de confirmation apparaît, cliquez sur OK. C’est fait.
