2212120664 Excel

Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

Thierry Capron

sans tabooCroiser, extraire et analyser ses donnes

AccessDExcel

Pour que

linformatique soit un outil et non un ennemi !

sans tabooPour un traitement plus performant de vos donnes !> Construisez une base de donnes minimale grce aux listes de donnes sous Excel et Calc > Triez et filtrez des enregistrements dans vos listes de donnes > Exploitez vos listes de donnes laide de la fonction Sous-total et des tableaux croiss dynamiques > Modlisez une base de donnes relationnelle prenne et volutive sous Access et Base > Concevez les tables et leurs diffrents champs > Crez une table par importation de fichiers Excel et Calc > tablissez les relations entre les tables matres et esclaves > Btissez vos formulaires et alimentez votre base > Apprenez crer et modifier des requtes de slection > Identifiez les doublons et les enregistrements sans correspondance > Effectuez des calculs, tests conditionnels et manipulations de chanes de caractres > ditez et modifiez vos tats

Access, l o Excel sarrteCe document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

Quand il faut consigner chiffres et donnes, le premier rflexe est de recourir au classique tableur Excel (ou Calc). Cependant, par crainte de sattaquer aux concepts redouts des bases de donnes, ou par mconnaissance, lutilisateur se prpare des lendemains difficiles. En effet, le tableur est vite dbord mesure que les besoins augmentent. Dans bien des cas, il suffit de faire jouer les fonctionnalits de base de donnes dExcel ou mme de passer Access (ou Base) pour crer et grer ses donnes dans une vritable base.

Thierry Capron est diplm de lESCP Il a . exerc diverses responsabilits oprationnelles en entreprise durant 15 ans (CBS, Arcelor Mittal, Point P). Depuis 10 ans, il consacre tout son temps la formation en comptabilit et bureautique auprs de publics varis (EDF GDF, Eurocopter, Ceram Sophia Antipolis).Tlchargez les fichiers des cas pratiques sur www.editions-eyrolles.com

qui sadresse cet ouvrage ?> Aux associations, TPE et PME dont la base de donnes atteint une masse critique ingrable avec des feuilles de calcul Excel ou Calc. > Aux utilisateurs dExcel et de Calc qui se sentent limits par lorganisation en feuilles et classeurs. > Aux dbutants en base de donnes qui veulent partir du bon pied aussi bien sous MS-Office que sous OpenOffice.org.

Conception : Nord Compo


AccessCroiser, extraire et analyser ses donnes

DExcel

Ouvrages consacrs Excel et AccessExcel 2007 avanc. P. moreau eT P. mori. N12217, 2008, 240 pages. Excel Missing Manual. m. maCdoNaLd. N12095, 2007, 818 pages. Mmento Excel. m. Grey eT m. berGame. N11756, 2005, 14 pages. Excel 2003 Programmation VBA. d. daVid. N11622, 2005, 272 pages. Access 2007 Missing Manual. m. maCdoNaLd. N12096, 2007, 740 pages. Access 2003. P. mori eT y. PiCoN. N11490, 2004, 422 pages. VBA pour Access 2003. a. TayLor eT V. aNdersoN. N11465, 2004, 636 pages.

Ouvrages consacrs Calc et BaseCe document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

OpenOffice.org 2 Calc. s. GauTier, avec la contribution de J.-m. THomas. N11667, 2006, 220 pages. OpenOffice.org Calc 2 Initiation. P. moreau. N12035, 2006, 210 pages. OpenOffice.org Calc 2 avanc. P. moreau. N12036, 2006, 186 pages.

Mmento Calc OpenOffice.org 2. s. GauTier eT m. berGame. N11729, 2005, 14 pages. OpenOffice.org 2.2 efficace Writer, Calc, Impress, Draw, Base. s. GauTier, C. Hardy, F. Labb eT miCHeL PiNquier. N12166, 2007, 394 pages.

Collection Sans taboo Scuriser enfin son PC Rflexes et techniques contre les virus, spams, phishing, vols et pertes de donnes. P. LeGaNd. N12005, 2006, 400 pages . Ciel Compta. N. CrouzeT. paratre. Mac OS X Tiger. Laboratoire SUPINFO des technologies Apple. N12204, 2007, 292 pages. Mac OS X Leopard. Laboratoire SUPINFO des technologies Apple. paratre.

Chez le mme diteurM. Nebra. Russir son site web avec XHTML et CSS, 2e dition. N12307, 2008, 316 pages. P. Labbe. Photoshop CS3. N12121, 2008, 530 pages. C. Gmy. Gimp 2.4. Dessin et retouche photo, 2e dition. N12152, 2008, 402 pages. A. aNdrieu. Russir son rfrencement web. N12264, 2008, 302 pages. i. TayLor et b. JeLeN. Analyse marketing et reporting avec Excel. N12251, 2008, 234 pages. V. messaGer roTa. Gestion de projet. Vers les mthodes agiles. N12165, 2007, 252 pages. a. FerNaNdez-Toro. Management de la scurit de linformation. Implmentation ISO 27001. N12218, 2007, 256 pages. M. Grey. Mmento Firefox et Thunderbird. N11780, 2006, 14 pages. C. boNNiN. Mmento SQL. Bases relationnelles. N06258, 1998, 14 pages. C. souTou. Apprendre SQL avec MySQL. Avec 40 exercices corrigs. N11915, 2006, 398 pages. C. souTou De UML SQL. Conception de bases de donnes. N11098, 2002, 500 pages. H. Tardieu, a. roCHFeLd eT r. CoLLeTTi. La mthode Merise. Principes et outils. N52473, 2000, 352 pages (ditions dOrganisation). D. dioNisi. Lessentiel sur Merise. N09046, 1998, 272 pages. P. roques. UML 2 par la pratique. tude de cas et exercices corrigs. N12322, 2008, 368 pages. F. VaLLe. UML pour les dcideurs. N11621, 2005, 300 pages.


Thierry Capron

Croiser, extraire et analyser ses donnes

AccessDExcel

sans taboo

DITIONS EYROLLES 61, bd Saint-Germain 75240 Paris Cedex 05 www.editions-eyrolles.com


Le code de la proprit intellectuelle du 1er juillet 1992 interdit en effet expressment la photocopie usage collectif sans autorisation des ayants droit. Or, cette pratique sest gnralise notamment dans les tablissements denseignement, provoquant une baisse brutale des achats de livres, au point que la possibilit mme pour les auteurs de crer des uvres nouvelles et de les faire diter correctement est aujourdhui menace. En application de la loi du 11 mars 1957, il est interdit de reproduire intgralement ou partiellement le prsent ouvrage, sur quelque support que ce soit, sans autorisation de lditeur ou du Centre Franais dExploitation du Droit de Copie, 20, rue des Grands-Augustins, 75006 Paris. Groupe Eyrolles, 2008, ISBN : 978-2-212-12066-0

Avant-proposCe document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

Btir une base de donnes relationnelle ne simprovise pas. louverture des cours Access que nous donnions au Ceram Sophia Antipolis, nous demandions aux tudiants si lun deux connaissait ce logiciel et ce quil en pensait. La meilleure rponse fut quAccess est un logiciel dlicat . Quiconque sest frott ce type de logiciel sans prparation, sans connaissance et sans mthode particulire, apprciera la pertinence de la remarque. Ces futurs managers ntaient pas particulirement ports sur linformatique et encore moins sur la bureautique. Pourtant, en deux jours, ils taient capables de monter une base de donnes relationnelle simple pour grer leurs problmes quotidiens de planning, doccupation de salles, voire de trsorerie ou de gestion du personnel. Ce livre reprend les outils pdagogiques dvelopps cette occasion et tests grandeur nature avec un excellent taux de russite. Dans la premire partie notamment, il expose de faon dtaille les tonnantes listes de donnes, bases de donnes simplifies sous tableur, trs largement mconnues et sous-utilises pour tre complet sur le sujet.

qui sadresse ce livre ?Vous grez pniblement les statistiques commerciales de votre entreprise grand renfort de classeurs, de feuilles de calcul et de formules alambiques ? Vous devez suivre le planning doccupation des salles de runion ou celui des prochaines vacances ? Vous devez grer les membres et les cotisations de votre association ? Vous voulez suivre vos prts de DVD ? Vous souhaitez connatre linventaire de votre cave vin et en grer la consommation ?

Groupe Eyrolles, 2008

DExcel Access

Que ce soit pour des besoins professionnels ou personnels, vous trouverez dans cet ouvrage plusieurs applications de la mme notion de base de donnes. Ce livre est un condens de lexprience accumule en formation sur ce thme pour des publics trs varis (tudiants, cadres, mais galement employs en rorientation professionnelle...). Nul besoin dtre un virtuose du clavier ou un champion des macros pour sapproprier cette mthode. tre laise avec son ordinateur, matriser lessentiel de Windows et des fonctions de base dun tableur suffiront amplement.


Quelles sont les caractristiques de cette mthode ?Structurer son approche dun logiciel de base de donnes conditionne 99 % la russite dun projet. Cest donc trs logiquement que nous mettrons ici laccent sur la mthodologie de travail plus que sur les techniques pures qui, de toute faon, trouveront naturellement leur place en temps et en heure. De plus, pour intresser le public le plus vaste possible, lensemble de nos explications sont directement applicables par les utilisateurs de Microsoft Office 2003 et 2007 (Access et Excel) ainsi que par ceux dOpenOffice.org 2.2 (Base et Calc).

Priorit la mthode de travailAvec Access et Base, et dans une moindre mesure avec Excel et Calc, concevoir, programmer et exploiter une base de donnes ncessite certes des comptences techniques, mais surtout et avant tout une mthode de travail prcise et une approche mthodologique sans faille. Aussi avonsnous ax notre dmonstration sur le droul dune mthodologie rigoureuse et teste avec succs, inspire en partie de la mthode Merise. Au plan des techniques proprement dites, Access et Base sont des logiciels extrmement vastes, touffus et complexes avec lesquels le risque de perdre de vue lessentiel est important. Aussi avons-nous fait le choix de ne dvelopper que les techniques incontournables, celles quil est impossible de ne pas connatre. Heureusement, les choses sont bien faites : elles suffisent rsoudre la plupart des cas qui se prsenteront vous ! Toutefois, nous avons pu prsenter la quasi-intgralit de ce quExcel et Calc proposent en matire de liste de donnes le sujet est plus simple.

VI


100 % compatible Microsoft Office (2003 et 2007) et OpenOffice.org 2.2Cette mthode aborde les bases de donnes sous deux angles : les tableurs et les bases de donnes relationnelles. Nous nous devions de maintenir cette ouverture sur le plan des logiciels traits. Nous avons ainsi choisi de prsenter les solutions apportes par la dernire version de Microsoft Office (Excel et Access 2007) car elle deviendra terme la plus rpandue, mais nous navons pas oubli les utilisateurs de la version prcdente (Excel et Access 2003). Enfin, soucieux de fournir au lecteur une ouverture sur le monde du logiciel libre, nous traitons galement dOpenOffice.org 2.2 avec Calc et Base.Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

Puisquil fallait bien choisir un angle de vue privilgi pour les explications et les captures dcran, nous avons dcid daxer notre expos par dfaut sur Excel et Access 2007. chaque fois que cela a t ncessaire, nous avons spcifi les particularits des versions 2003 ou OpenOffice.org 2.2 dans des sections ou encadrs spcifiques.

Que trouverez-vous concrtement dans ce livre ? la fin de ce livre, vous serez capable de concevoir, programmer et exploiter des listes de donnes sous tableur (chapitres 1 4) ou des bases de donnes relationnelles simples (chapitres 5 13). Ces deux parties, indpendantes, sont articules chacune autour de leur cas pratique, vritables fils directeurs pdagogiques.

RESSOURCE Les cas pratiques du livre sontdisponibles en tlchargementTous les cas pratiques de cet ouvrage, logiciel par logiciel et chapitre par chapitre, sont disponibles en tlchargement sur le site des ditions Eyrolles. B www.editions-eyrolles.com

Les listes de donnes sous tableur (Excel et Calc)Les listes de donnes sous tableur sont, tort, trs largement mconnues. Pourtant, elles offrent des possibilits tonnantes et sont trs simples dutilisation. Plus dune fois, il nous est arriv de conseiller nos clients dadopter pour leurs statistiques commerciales une solution de ce type. Les retours de terrain, manant aussi bien des fonctionnels chargs de llaboration de ces listes de donnes que des oprationnels qui en taient destinataires, ont toujours t excellents en termes de fiabilit, de rapidit et de finesse. Le chapitre 1 balaye, un peu la manire dune bande-annonce, les avantages dcisifs mais trop peu connus des listes de donnes par rapport aux feuilles de calcul traditionnelles.


VII

Avant-propos

DExcel Access

Le chapitre 2 traite des rgles de construction dune liste de donnes performante et fiable, celle qui vous fera gagner du temps. Il aborde galement les tonnantes analyses visuelles quautorise Excel 2007, notamment. lissue du chapitre 3, vous saurez tout des possibilits de tri et de slection denregistrements dans une liste de donnes. Enfin, dans le chapitre 4, la dcouverte et la mise en uvre des fonctions de sous-total et de tableau crois dynamique vont vous permettre de raliser toutes les analyses statistiques de votre liste de donnes, mettant ainsi dfinitivement au placard les bonnes vieilles fonctions Somme () et autres .

Les bases de donnes relationnelles (Access et Base)Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

Access et Base sont des logiciels dlicats dutilisation quil est impossible daborder comme un traitement de texte ou un tableur. Priorit est donc donne lapproche mthodologique, aussi bien pour la prparation du travail (la modlisation) que pour la programmation proprement dite. Le concept cl de relation est particulirement dvelopp. Le chapitre 5 est ax sur la dcouverte du vocabulaire et des principes fondamentaux qui prsident au fonctionnement des bases de donnes relationnelles. Les mthodes danalyse et de modlisation du modle traiter sont dtailles au chapitre 6. Cest une particularit importante de cette mthode que de traiter, en termes clairs et simples, tous les concepts fondamentaux qui vous permettront coup sr de dfinir prcisment le dessein et lorganisation de votre base de donnes relationnelle. Le chapitre 7 permet de se familiariser avec linterface logicielle et les oprations essentielles (crer et ouvrir une base, grer les problmes de scurit, crer une table et ses champs...). Ltablissement des relations entre les tables, fondement du modle relationnel dune base de donnes, fait lobjet du chapitre 8. Essentielle entre toutes, cette tape valide dfinitivement le travail de modlisation et de programmation ralis en amont. Le chapitre 9 traite des mthodes dentre des donnes dans une base, par saisie directe (dans la table ou via un formulaire) ou par importation partir de fichiers existants. Une base de donnes relationnelle ne trouve de justification quau travers des analyses quon peut raliser sur ses donnes. Les requtes servent de support ces analyses. Vous saurez btir, manipuler et modifier une requte danalyse des donnes simple aprs ltude du chapitre 10.

VIII


Les chapitres 11 et 12 poussent les requtes dans leurs derniers retranchements, pour effectuer par exemple des slections denregistrements multicritres ou encore des calculs, un peu la manire dun tableur. Le chapitre 13, quant lui, clt lanalyse des bases de donnes relationnelles par les tats/rapports qui servent, comme leur nom lindique, visualiser et imprimer les tables ou les requtes de manire claire, lisible et professionnelle. Enfin, il nous a paru intressant dapporter en annexe des complments dinformation, tout dabord sur la mthode de modlisation Merise, puis sur les droits et obligations du citoyen et du professionnel au regard de la Commission Nationale Informatique et Libert.Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

RemerciementsEn comparant les preuves finales avec le tout premier jet de ma rdaction, je ne peux que mesurer lnorme travail de conseil, daccompagnement et de structuration qua ralis toute lquipe ditoriale Eyrolles. Sans compter une patience, une disponibilit et une bonne humeur toute preuve. Un norme bravo et toute ma reconnaissance Sandrine, Sophie, Matthieu et Aurlie avec qui jai travaill en direct, ainsi qu toutes celles et ceux que je ne connais pas et qui ont apport leur contribution ce livre. Comment ne pas saluer aussi comme il convient la dream team du Ceram Sophia Antipolis, Marc, Michel et Fred : Access et PCIE, a forge des souvenirs... Merci aussi tous les anciens participants mes cours et sessions de formation. Ils mont rappel tous les jours que si une explication nest pas comprise cest quelle nest pas claire. Enfin, toute ma reconnaissance Bernard qui a su voici maintenant quelques annes porter mon premier projet ddition et Marie-Pierre qui continue peut-tre contre vents et mares croire en dautres livres.


IX

Avant-propos


Table des matires1. PREMIERS PAS VERS LES BASES DE DONNES ................... 1 Un traitement de texte ne sait pas grer une base de donnes, mme trs simple 2 Le tableur et ses filtres : une solution plus performante 4 Appliquer un filtre pour slectionner des enregistrements particuliers 4 Changer la slection opre par un filtre 6 Les listes de donnes sous tableur : un pas vers les bases de donnes 8 Lexpos de notre cas pratique dtude des listes de donnes 8 Notre mission 9 Notre cahier des charges simplifi 9 Une dfinition dune base de donnes 10 Pourquoi la solution tableur feuille-classeur ne convient pas 10 Le choix de lorganisation feuille-classeur idale 11 Aucune solution feuille-classeur ne saura voluer 12 La solution : les listes de donnes sous tableur 13 La liste de donnes est souple et volutive 13 La liste de donnes sadapte parfaitement au changement 13 Synthse : les avantages des listes de donnes sous tableur 15 2. BTIR UNE LISTE DE DONNES EFFICACE SOUS TABLEUR : STRUCTURE, RGLES DE VALIDATION ET MISE EN FORME ........... 17 Dfinitions pralables 18 Les listes de donnes et bases de donnes : lorganisation de linformation 18 Les champs : la structure 18 Les enregistrements : le contenu 19 Bien structurer une liste de donnes 19 Rgle 1 : les noms de champ en premire ligne 20 Rgle 2 : les enregistrements les uns au-dessous des autres 21 Rgle 3 : ni ligne ni colonne vide 22 Rgle 4 : pas de mise en forme inutile 23 Groupe Eyrolles, 2007


Rgle 5 : pas de formule 23 Les rgles de validation dune liste de donnes 24 Le paramtrage des rgles de validation proprement dites 25 Limiter les entres un nombre entier ou dcimal 26 Limiter les entres aux donnes dune liste 27 Crer une rgle de validation personnalise 28 Lannulation des rgles de validation 29 Laide la saisie et les messages derreur 29 Le dtail des rgles de validation conseilles pour notre cas pratique 31 Les mises en forme conditionnelle de liste de donnes 32 Les mises en forme conditionnelles spcifiques Excel 2007 32 Isoler un lment dans une srie 33 Identifier les dix premiers lments dune srie 34 Visualiser la place de chaque lment au sein de la srie 36 Les mises en forme conditionnelles dExcel 2003 et de Calc 37 Synthse : btir une liste de donnes efficace sous tableur : structure, rgles de validation et mise en forme 39 3. TRIER ET SLECTIONNER DES ENREGISTREMENTS DANS LES LISTES DE DONNES .......................................41 Comment accder aux fonctions de liste de donnes 42 Les tris de liste de donnes 43 Trier sur un seul champ 44 Trier selon un ordre personnalis 44 Crer une liste personnalise 45 Trier selon une liste personnalise 46 Effectuer plusieurs tris successifs 47 Les filtres ou la slection denregistrements 49 Les filtres simples 50 Appliquer un filtre sur un champ unique 50 Appliquer un filtre sur plusieurs champs 51 Dsactiver un filtre 51 Les filtres volus 52

XI

DExcel Access

Les filtres volus spcifiques Excel 2007 53 Les autres filtres volus dExcel 2007 et 2003 et de Calc 55 Synthse : les tris et les slections denregistrements par filtre 57 4. EXPLOITER LES LISTES DE DONNES : LES SOUS-TOTAUX ET LES TABLEAUX CROISS DYNAMIQUES .................................. 59 Totaux et sous-totaux dans une liste de donnes 60 Insrer un niveau unique de sous-total 61 Exploiter les sous-totaux de listes de donnes 64 Afficher la liste intgrale des enregistrements, des sous-totaux et du total gnral 64 Affiner lanalyse : dvelopper ou rduire chaque sous-total 64 Supprimer les sous-totaux 66 Imbriquer plusieurs niveaux de sous-totaux 66 Moyenne, nombre, variance et autres carts types 68 Des tats danalyse entirement paramtrables : les tableaux (graphiques) croiss dynamiques 71 Crer un tableau crois dynamique avec lassistant Crer un TCD 72 Exploiter un TCD 75 Faire varier la dimension filtre 76 Modifier la structure du TCD 77 Actualiser un TCD 79 Organiser ses TCD 80 Le graphique crois dynamique 80 Les GCD sous Excel 2007 80 Les GCD version Excel 2003 84 Synthse : les fonctions de sous-total et de tableau (graphique) crois dynamique sont les instruments indispensables danalyse dune liste de donnes 87 5. INTRODUCTION AUX BASES DE DONNES RELATIONNELLES 89 Les limites des listes de donnes 90 Une liste de donnes est un cas particulier dune base de donnes 90 Une liste de donnes est une base de donnes aux performances limites 91 Les concepts fondamentaux des bases de donnes relationnelles 93 Les tables : regroupement des donnes dun mme domaine 93 Les relations entre les tables 94 Tables matres et tables esclaves 97 La cl primaire : identifiant unique dun lment de table 97

La mthode dlaboration dune base de donnes relationnelle 100 La phase de modlisation 100 La mise en uvre pratique dune base de donnes relationnelle 100 La structure dun SGBDR et ses diffrents objets 101 Les phases de la programmation dune base de donnes relationnelle 103 Synthse : base de donnes relationnelle et mthode de travail avec un SGBDR 105 6. LA MODLISATION DUNE BASE DE DONNES RELATIONNELLE .........................................................107 Prsentation de notre cas pratique de base de donnes relationnelle 108 Btir le schma thorique de la base de donnes relationnelle 109 Dterminer les lments du schma thorique avec la phrase cl 110 Individualiser les lments de la phrase cl 111 Caractriser les lments de la phrase cl 111 viter les principaux piges du schma thorique 112 Ne pas crer dlment inutile 112 Ne pas crer un schma thorique en boucle 114 Dduire le schma rel du schma thorique 114 Dterminer dfinitivement les tables du schma rel 115 Dterminer les champs et la cl primaire de chaque table 117 tablir les relations entre les tables 118 Les principales tapes de la programmation dAccess et de Base 120 tape 1 : programmer les tables 120 tape 2 : tablir les relations 121 tape 3 : saisir les donnes 121 tape 4 : programmer formulaires, requtes et tats/rapports 122 Synthse : modlisation et mthode de programmation dun SGBDR 122 7. OPRATIONS ESSENTIELLESSUR UNE BASE DE DONNES ........................................125


Crer une nouvelle base de donnes relationnelle 126 Ouvrir une base de donnes relationnelle existante 128 Autoriser louverture de la base pour une session seulement sous Access 2007 130 Autoriser dfinitivement louverture de toutes les bases dun dossier sous Access 2007 131 Utiliser lcran daccueil dune base de donnes relationnelle 133 Groupe Eyrolles, 2007

XII

Crer une table 134 Les caractristiques des champs dune table 135 Utiliser des noms de champs pertinents 135 Dfinir une cl primaire efficace 136 Faciliter la saisie avec un masque de saisie 137 Interdire labsence de saisie 137 Crer une nouvelle table 138 Crer les champs dune table 139 Mthode gnrale de cration dun champ 140 Crer le champ cl primaire numrotation automatique 141 Crer un champ numrotation automatique 141 Dclarer un champ cl primaire 142 Crer un champ ordinaire 143 Crer un champ de type Texte 144 Crer un champ de type Date 145 Crer un masque de saisie sous Access 145 Crer un champ de type tlphone 148 Crer une table par importation de donnes 150 Caractristiques des donnes importer 150 Importer des donnes externes dans une table Access 150 Importer dautres objets Access 153 Importer des donnes et des programmes sous Base 155 Modifier la table rsultante aprs importation 156 Manipuler les objets dune base de donnes 156 Comprendre lutilit des deux modes daffichage dun objet 156 Ouvrir un objet directement dans le mode adquat 158 Basculer dun mode daffichage lautre 158 Fermer, supprimer et copier un objet 159 Synthse : oprations essentielles sur une base de donnes 160 8. TABLIR LES RELATIONS ENTRE LES TABLES .................. 163 Crer toutes les tables de notre cas pratique 164 Crer la table des Guides 167 Crer la table des Activites 168 Crer la table des Inscriptions 168 Crer la table des BureauxGuides avec Base 169 tablir les relations par liste de choix avec Access 170 Paramtrer une liste de choix manuelle avec Access 171 Utiliser lassistant Liste de choix manuelle 171 Vrifier et scuriser la liste de choix avec Access 173 Assigner une valeur par dfaut dans une liste de choix 174


Paramtrer une liste de choix par rapport une table avec Access 175 Utiliser lassistant Liste de choix par rapport une table 176 Paramtrer la fentre des relations avec Access 182 Anatomie de la fentre des relations avec Access 183 Paramtrer lintgrit rfrentielle des relations avec Access 183 tablir les relations avec Base 185 Fonctionnement de la fentre des relations avec Base 185 Paramtrer une relation dans la fentre des relations avec Base 187 Rsoudre les difficults lies au paramtrage des relations 189 Des donnes ont t saisies avant le paramtrage des relations 189 Il manque une cl primaire pour une table 190 Une table contient une cl primaire inattendue 190 Il nexiste pas de liste de choix entre la table esclave et sa table matre (Access) 191 Synthse : le verdict des relations 192 9. CRER UN FORMULAIRE ET SAISIR LES DONNES ............195 quoi sert un formulaire ? 196 Crer un formulaire de saisie simple 198 Crer un formulaire avec lassistant 198 Dterminer la table et les champs du formulaire 200 Choisir laspect du formulaire 202 Le formulaire en mode Contenu 203 Modifier un formulaire 205 Modifier ltiquette dun contrle de formulaire 209 Dplacer et redimensionner un contrle dun formulaire 209 Crer un formulaire avec liste de choix sous Base 210 Insrer la liste de choix 211 Insrer ltiquette de la liste de choix 214 Aller plus loin avec les formulaires 215 Crer un formulaire pour agir sur deux tables la fois 215 Crer un menu douverture pour nophyte avec Access 218 Crer le formulaire de menu 218 Automatiser louverture dun formulaire 221 Saisir et modifier les donnes 222 Les spcificits de la saisie des donnes dans une base de donnes relationnelle 222 Les contraintes dintgrit rfrentielle 222 Les donnes sont enregistres immdiatement 223 Saisir, modifier et supprimer des donnes 225


XIII

Table des matires

DExcel Access

Saisir un formulaire ou directement dans la table 225 Saisir des donnes avec un formulaire 225 Saisir des donnes directement dans une table 226 Modifier et supprimer des donnes 227 viter les difficults lies la saisie de donnes 228 Il faut respecter les proprits du champ 228 Il faut respecter le principe dintgrit rfrentielle 229 Synthse : les formulaires et la saisie des donnes 230 10. EXPLOITER LES DONNES AVEC UNE REQUTE SIMPLE .. 233 Quest-ce quune requte ? 234 Plusieurs types de requtes 235 Rflexions pralables la cration dune requte 236 Choisir les champs participant une requte 236 Choisir le mode de cration de la requte 237 Crer une requte simple 237 Crer une requte avec lassistant Access 238 Slectionner les champs de la requte (tape 1 de lassistant) 238 Choisir le type de requte (tape 2 de lassistant) 239 Enregistrer la requte (tape 3 de lassistant) 240 Crer une requte en mode Cration/bauche 241 Crer une nouvelle requte en mode Cration/bauche 242 Insrer les tables participant la requte 242 Insrer les champs participant la requte 243 Comprendre les rsultats dune requte en mode Contenu 244 Sexercer crer des requtes 246 Faciliter la saisie des donnes avec une requte sous Access 248 Crer la requte source 248 Crer le formulaire bas sur la requte 249 Synthse : exploiter les donnes avec une requte simple 252 11. ISOLER DES ENREGISTREMENTS PARTICULIERS DANS UNE BASE DE DONNES .............................................. 255 Modes cration/bauche, assistant et SQL : sorienter 256 Raliser des slections denregistrements 257 Crer une requte slection 257 Crer une requte slection paramtre 258 Combiner les critres de slection 260 Utiliser les critres de slection volus 262 Identifier des enregistrements sans correspondance 262 Crer la requte de non-correspondance avec lassistant Access 263


Visualiser la requte de non-correspondance 267 Le mode Cration de la requte de non-correspondance 268 Le mode SQL de la requte de non-correspondance 268 Crer la requte de non-correspondance en mode SQL avec Base 270 Crer le code SQL de requte de non-correspondance 270 Crer la requte de non-correspondance en mode SQL 271 Identifier les doublons dans une table 271 Crer une requte de recherche de doublons avec lassistant Access 273 Modifier une requte existante 275 Crer une requte de recherche de doublons en mode SQL 278 Synthse : rechercher des enregistrements particuliers dans une base de donnes 279 12. EFFECTUER DES CALCULS ET DES SYNTHSES AVEC UNE REQUTE ....................................................283 Prendre en compte une modification du cahier des charges 284 Identifier les modifications de la base suite au nouveau cahier des charges 285 Modifier la base de notre cas pratique 287 Crer des champs calculs 291 Connatre la syntaxe dun champ calcul dans une requte 291 Effectuer un calcul arithmtique 292 Manipuler les chanes de caractres avec Access 297 Effectuer un test conditionnel avec Access 298 Raliser des synthses avec une requte 301 Crer une requte de synthse avec lassistant Access 301 Crer une requte de synthse en mode Cration/bauche 303 Organiser son travail avec les requtes 304 Grer lajout et la suppression de donnes 305 Crer une unique requte avec tous les calculs 306 Synthse : effectuer des calculs et des synthses avec les requtes 307 13. DITER TABLES ET REQUTES AVEC DES TATS/RAPPORTS PROFESSIONNELS ....................................................... 309 Crer un tat/rapport en trente secondes 311 Crer un tat simple avec lassistant Access 311 Crer un rapport simple avec lassistant Base 316 Sentraner la cration dtats/rapports 319 Crer un tat de synthse avec Access 319 Dterminer les champs participant ltat 320 Groupe Eyrolles, 2007

XIV


Dterminer le niveau de regroupement 321 Paramtrer le tri et les synthses effectuer et terminer lassistant 322 Lanalyse critique de ltat obtenu 324 Modifier un tat existant avec Access 325 Anatomie dun tat en mode Cration 326 Manipuler les contrles 327 Slectionner un contrle 327 Modifier une tiquette ou un contrle 327 Dplacer un contrle 328 Modifier la taille dun contrle 329 Supprimer un contrle inutile 329 Arer la prsentation de ltat 330 Synthse : les tats et les rapports 331 A. COMPLMENTS SUR LA MODLISATION ....................... 333 Le schma thorique de lcole de parapente 334 Exercice sur les couples de nombres cls 336 Prcisions concernant la boucle dans le schma thorique de lcole de parapente 338

Le schma rel de lcole de parapente 339

B. DROITS ET OBLIGATIONS EN MATIRE DE BASE DE DONNES341 Vos obligations en tant que gestionnaire de base de donnes 341 La notion de donnes caractre personnel 342 Dans quel cas faut-il dclarer un fichier de donnes personnelles auprs de la CNIL ? 342 Vos droits en tant que citoyen face lutilisation de vos donnes personnelles 343 Le droit linformation 344 Le droit dopposition 344 Le droit daccs 344 Le droit de rectification 345 Exercez vos droits 345 INDEX ......................................................................347


XV

Table des matires


chapitre


1

Premiers pas vers les bases de donnesCe document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

SOMMAIRE

B Le traitement de texte :

Grer une base de donnes nimpose pas ncessairement dutiliser un logiciel complexe comme Access ou Base. partir dun exemple trs simple, nous allons dcouvrir ici les avantages dcisifs des bases de donnes simplifies que sont les listes de donnes sous tableur.

une solution inadapte

B Le tableur et ses filtres :une solution plus performante

B Les listes de donnes soustableur : un pas vers les bases de donnes MOTS-CLS

B Liste de donnes sous tableur B Filtre de liste de donnes B Base de donnes B Cahier des charges simplifi


DExcel Access

Grer une base de donnes noblige pas ncessairement dutiliser un logiciel spcifique et complexe tel quAccess ou Base. Les tableurs Excel et Calc possdent en effet le mode Liste de donnes permettant, avec un minimum dinvestissement en temps, de crer et dexploiter une base de donnes simple. Lobjet de ce chapitre est de prsenter globalement ce mode, largement mconnu et donc sous-employ par la majorit des utilisateurs. Il sarticule autour de deux comparaisons, la premire entre traitement de texte et liste de donnes, la seconde entre utilisation classique du tableur et liste de donnes.


Un traitement de texte ne sait pas grer une base de donnes, mme trs simpleDans la palette des outils les plus utiliss en bureautique figure en tte de liste le traitement de texte. Nous allons voir ici pourquoi, mme pour un problme trs simple, il ne peut apporter de solution efficace, et comment la liste de donnes sous tableur fait bien mieux en termes de facilit et de performances. Rien ne vaut un exemple concret : dans une cole de commerce, nous devons organiser la prsence des tudiants au cours de bases de donnes, car on sy bouscule. Huit dates sont programmes, chacune voyant chacun des quatre groupes dtudiants suivre une sance d1h30 dans la seule salle suffisamment spacieuse pour les accueillir tous. Nous devons ainsi grer trois informations pour convoquer les bons tudiants le bon jour, sachant que le cours aura toujours lieu au mme endroit : le groupe dtudiants concern (qui ?) ; le jour (quand ?) ; et enfin le crneau horaire (prcision sur le quand ?). Ce planning peut parfaitement se prsenter sous forme de tableau de traitement de texte. Le premier rflexe (et pas le meilleur, nous verrons pourquoi) peut donc tre de prendre Word ou Writer et de construire un tableau texte comme celui de la figure 11. Sa structure est on ne peut plus simple : Dabord trois colonnes correspondant aux informations fournir aux tudiants : le Groupe, la Date, le Crneau horaire, chacun de ces titres tant saisi en en-tte de chaque colonne. Ensuite, les uns au dessous des autres, les diffrentes combinaisons groupes/jour/crneau. En considrant quil y a quatre groupes et huit Groupe Eyrolles, 2008

Figure 11

Un planning sous traitement de texte, trs simple mais aux possibilits bien limites

2

sances par groupe, notre tableau doit comporter 8 4 = 32 lignes, en plus de celle den-tte. Difficile de faire plus simple, peut-tre. Mais en parallle difficile de faire plus inoprant. Que pouvons-nous en effet faire de ce tableau, part le punaiser lentre de chaque salle ? On peut bien sr le trier. Il est actuellement tri par groupe, nous pourrions le trier par date ou par crneau : Sous Word 2007, dans longlet Outils de tableau Disposition, cliquez sur le bouton Trier du groupe Donnes. Sous Word 2003, dans le menu Tableau, utilisez le bouton Trier. Sous Writer, cliquez sur le bouton Trier du menu Tableau.Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

Voil, cest peu prs tout et ce nest pas grand chose... Imaginons par exemple que nous voulions distribuer tous les tudiants leur planning et uniquement leur planning. Tous les groupes tant prsents sur le mme tableau, et les traitements de texte ne sachant pas masquer temporairement certaines lignes, on ne pourra queffectuer des copier coller au sens premier du terme, cest--dire avec des ciseaux et de la colle... Pas trs performant, non ? Ce problme se posera de la mme faon quand lintervenant du 17 novembre nous demandera son planning (et uniquement le sien). Bref, notre tableau sous traitement de texte ne nous rendra gure de services.JAI UNE QUESTION Et les fonctions de mailing de Word ?Vous affirmez que Word est disqualifi pour grer les bases de donnes. Pourtant je gre personnellement les mailings de mon entreprise entirement sous Word et je men porte trs bien. Alors ? Si cette faon de procder vous convient, continuez sans arrire pense ; vous trouverez mme dexcellents ouvrages qui vous y aideront. Ceci dit : Le module de mailing de Word a t dvelopp pour ceux qui ne souhaitent pas sinitier aux bases de donnes. Le fait que vous lisiez ces lignes prouve que vous nen faites plus partie. Les fonctions de mailing de Word et Writer restent bien au dessous, tant en ergonomie quen performance, de ce que vous pourrez faire avec Excel ou Access la fin de ce livre, surtout en ce qui concerne les slections de donnes (les abonns de telle ville, les clients de tel dpartement, les produits de telle gamme, les ventes suprieures la moyenne des ventes, les employs de tel ge, etc.).


3

1 Premiers pas vers les bases de donnes

DExcel Access

Le tableur et ses filtres : une solution plus performanteUn tableau, sous traitement de texte comme sous tableur, comporte des lignes et des colonnes. Rien nempche donc de transformer notre planning de la figure 11 pour obtenir son quivalent tableur de la figure 12 (ici, sous Excel 2007, les prsentations Excel 2003 et Calc sont quasiment identiques), soit en le recrant intgralement, soit, plus facilement, en procdant par copier/coller. Remarquez combien sa prsentation est plus attractive. Et surtout, observez les flches apparues ct de chacun des trois en-ttes de colonnes : elles vont nous permettre deffectuer des slections denregistrements particuliers, par exemple pour isoler le planning du groupe 2 ou du 17 novembre.TECHNIQUE Obtenir lapparence de la figure 12 Excel 2007 : ces bandes alternativement grises et blanches (en fait bleues et blanches) et ces flches ct de chacun des en-ttes de colonne ne sobtiennent simplement quavec Excel 2007 (dans longlet Accueil, groupe Style, cliquez sur loutil Mettre sous forme de tableau). Excel 2003 : ces mmes flches sobtiennent par le menu Donnes, option Filtres puis Filtres automatiques. Pour les alternances de couleur, par contre, il faudra vous dbrouiller la main... Pour Calc, le menu Donnes, options Filtres puis Autofiltre fait apparatre les flches. Pour les lignes de couleurs alternes, il faudra aussi se dbrouiller la main...


Figure 12

Un planning Excel bien plus performant

Maintenant, en quoi ce tableau sous tableur est-il plus performant que son petit frre sous traitement de texte ? Nous allons lexprimenter en quelques clics !

Appliquer un filtre pour slectionner des enregistrements particuliersLa fonction Filtre des tableurs (Excel ou Calc) permet disoler trs simplement certaines lignes (enregistrements) selon des critres entirement personnalisables. Nous souhaitons par exemple isoler les lignes relatives au groupe 2. Cliquons sur la flche ct de len-tte Groupes, en A1. Une liste droulante apparat (figure 13).

4



Figure 13

Les possibilits de filtre pour la colonne (champ) Groupes

Le haut de la bote de dialogue de la figure 13 permet deffectuer des tris, mais le plus intressant ici se situe plus bas, au-dessous de la zone Filtres textuels, o nous retrouvons la liste de nos quatre groupes. Ils sont tous cochs, ce qui signifie quils sont tous actuellement affichs. Si nous dcochons les groupes 1, 3 et 4 pour ne conserver coch que le 2 et que nous validons, nous obtenons laffichage du groupe 2 uniquement (figure 14).

EXCEL 2003 ET CALCAppliquer un filtreLes botes de dialogue Excel 2003 et Calc diffrent lgrement de la figure 13, mais offrent avec une ergonomie semblable les mmes fonctionnalits, voir page 7.

Figure 14

Lapplication dun filtre naffiche que certaines lignes.

Observons laffichage de la figure 14 : La flche ct de len-tte Groupes sest orne dune sorte d entonnoir. Il signifie quun filtre est appliqu sur cette colonne. Excel naffiche que le groupe 2 parce quil a masqu les lignes des autres groupes. Cest vident si on observe les intituls de numros de ligne lextrme gauche de chaque ligne : les lignes 2 9 ont temporairement disparu. De plus, Excel affiche ces numros de ligne en bleu pour bien nous rappeler quun filtre est actuellement en cours.

EXCEL 2003 ET CALCReprer quun filtre est actifLentonnoir prsent sur la flche de la liste droulante dun filtre nest prsent que sous Excel 2007. Pour Excel 2003 et Calc, la flche change de couleur et devient bleue.


5


DExcel Access

Changer la slection opre par un filtreAppliquer un nouveau filtre alors quun autre filtre est dj appliqu recle un pige ; il faut faire attention dans ce cas ne pas appliquer par mgarde les deux filtres ensemble. Nous voulons par exemple donner son planning notre intervenant du 17 novembre. Il suffit deffectuer exactement le mme type dopration que prcdemment, cette fois-ci sur la colonne Crneau, sans oublier dannuler ventuellement tout filtre dj actif. Dans notre exercice, il faudra : 1 Commencer par annuler un filtre actif. Sous Excel 2007, cliquez sur la liste droulante du filtre appliqu la colonne, slectionnez loption Slectionner tout dans la zone au dessous de Filtres textuels ; lentonnoir correspondant disparat et les lignes masques rapparaissent pour afficher tous les champs. Pour Excel 2003 et Calc, cochez respectivement les options Tout ou Tous. 2 Activer la slection denregistrement sur le champ Dates. Pour cela, cliquez sur la liste droulante du filtre Dates. Excel 2007 a prvu un regroupement par mois comme le montre la figure 15, mais cela ne correspond pas ce que nous cherchons faire. Il suffit alors douvrir le mois de novembre en cliquant sur la croix ct du mois pour slectionner ce fameux 17 novembre (figure 16), puis de valider pour obtenir ce que nous souhaitions (figure 17).


Figure 15

Figure 16

Les filtres dExcel 2007 regroupent les dates par mois

Le choix dun filtre sur une date particulire (ici, le 17 novembre)

6


Figure 17

Le filtre sur une date particulire (ici le 17 novembre) est appliqu.

Remarquez sur la figure 17 que seules les lignes 5, 13, 21 et 29, correspondant la date du 17 octobre, sont affiches, et que les autres sont temporairement masques. Cest le principe mme du filtre qui se contente de masquer sans jamais supprimer. Dans cette manipulation, nous avons bien pris soin dannuler le filtre sur la colonne Groupe avant dappliquer celui sur la Date. Que ce serait-il pass si les deux filtres avaient t appliqus en mme temps ? Cest trs simple : quand diffrents filtres sexcutent en mme temps, leurs effets se cumulent. Dans notre exemple, voici ce que nous aurions obtenu pour ce 17 novembre et pour le groupe 2 (figure 18) : la ligne unique correspondant au groupe 2 et au 17 novembre. Remarquez galement sur cette figure 18 que licne de filtre (lentonnoir) apparat la fois sur les colonnes Groupes et Dates, preuve que des filtres sont appliqus sur ces deux colonnes.EXCEL 2003 ET CALC Appliquer un filtre sur des datesExcel 2003 et Calc noffrent pas la possibilit, comme Excel 2007, de slectionner un mois particulier. Il sagit alors simplement de slectionner dans la liste de choix du filtre correspondant la date souhaite (figures 19 et 110).


Figure 18 Deux filtres appliqus

simultanment se cumulent.

Figure 19 Le choix dun critre

Figure 110 Le choix dun critre de

de filtre de date sous Excel 2003

filtre de date sous Calc

Les filtres offrent bien dautres possibilits, nous les tudierons en dtail pages 71 et suivantes.


7


DExcel Access

Les listes de donnes sous tableur : un pas vers les bases de donnesRAPPEL Feuilles de calcul et classeursUn classeur Excel ou Calc permet, comme un classeur rel, de regrouper des feuilles de calcul traitant du mme sujet pour un classement plus clair et un accs plus rapide linformation. On peut par exemple regrouper dans un mme classeur les informations relatives un client, un produit, ou un thme particulier. Il est possible deffectuer, dans une feuille de calcul, des calculs par rapport des cellules dautres feuilles du mme classeur ou mme dautres classeurs. Ces manipulations par ailleurs trs pratiques et performantes, un peu trop longues pour tre dveloppes ici, sont dtailles dans nombres douvrages sur les tableurs, dont PC trucs, du mme auteur, aux ditions Leduc.S ou les ouvrages consacrs Excel et Calc dans la collection TSoft aux ditions Eyrolles.

Le mode Liste de donnes des tableurs est celui que nous venons dutiliser dans lexercice prcdent, un peu comme monsieur Jourdain faisait de la prose, sans le savoir. Il soppose dans sa construction et dans son utilisation au mode dutilisation traditionnel de ces logiciels, avec des feuilles de calculs (composes de lignes, de colonnes et de formules dans tous les sens) regroupes en classeurs que vous pratiquez coup sr. Par exprience, trs peu daficionados du tableur connaissent et utilisent les tableurs en mode liste de donnes. Leur principe de base est, au lieu dclater linformation dans diffrents feuilles et classeurs, de tout regrouper sur une seule feuille en un seul tableau. Voyons les avantages de ces listes de donnes par rapport lutilisation classique du tableur. Il nest pas ncessaire dexprimenter les manipulations prsentes dans cette section, elles ne vous sont proposes qu titre dillustration. Leur tude dtaille au travers dun cas pratique fera lobjet des chapitres 2 4.


Lexpos de notre cas pratique dtude des listes de donnesImaginons que nous venons dintgrer en tant que contrleur de gestion cette socit de fabrication de matriel dalpinisme, ct dAnnecy. Pleine dides et de projets, cette dernire a acquis en quelques annes une notorit tout fait remarquable, en particulier auprs des jeunes amateurs de grimpe sportive, en indoor et en comptition. Son fondateur, Eddy K., fou de prises et de grattons, est un jeune patron fonceur et, surtout, a ce quil est convenu dappeler le flair des affaires et le sens du business. Comme il a eu le talent de sassocier avec Karim L., ingnieur tonnamment djant mais totalement gnial, leur petite structure a trs vite su profiter du boom de cette spcialit sportive en alliant produits innovants et image forte. Sa principale richesse est linvention de Karim L. : une fibre textile synthtique qui combine excellence des qualits physiques (rsistance et lasticit) et capacit tre teinte de milles tonnantes faons, toutes plus fun les unes que les autres. Il en possde, son nom propre, le brevet mondial. Aprs quatre ans dexistence, lentreprise prvoit un chiffre daffaires de 10 millions deuros cette anne, et envisage de souvrir lEurope lan prochain, particulirement dans les pays nordiques.

8


Notre missionCe tableau est-il idyllique ? Eh non... Comme bien souvent en pareil cas, lintendance na pas aussi bien suivi. Notamment en matire de suivi des performances commerciales de ses reprsentants. Conscient de lenjeu, Eddy K. nous a confi, comme premire tche, la mise en place des statistiques commerciales de lentreprise. Nous sommes donc dans un schma trs classique : une socit dynamique ; une croissance rapide ; une intendance qui na pas suivi ; des besoins vitaux de statistiques simples, fiables et efficaces.Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

On na pas de temps perdre !

Notre cahier des charges simplifiTout travail sur base de donnes, aussi simple soit-il, ncessite systmatiquement une rflexion pralable. Autant sous traitement de texte ou sous tableur on peut toujours au dernier moment ajouter des paragraphes ou dplacer des colonnes, autant en matire de bases de donnes ce ne sera pas toujours possible. En clair, il faut dfinitivement perdre ses rflexes comme je commence programmer comme a et puis on verra bien plus tard comment a volue je pourrais toujours rajouter une ou deux colonnes on va pas se prendre la tte . Tout simplement parce que dans certains cas, il sera impossible de faire voluer dans le bon sens un travail mal n et quil faudra alors tout reprendre zro. Prendre le temps de rflchir avant et de poser sur papier clairement le contexte, les contraintes, les informations et le but recherch est fondamental, le temps qui semble perdu sera conomis par la suite au moins au centuple. Nous avons ainsi runi les informations suivantes : Notre entreprise compte actuellement quatre reprsentants (Hlne, Sylvie, Pierre et Jean). Ce nombre est appel crotre rapidement. Ils interviennent indiffremment dans trois zones gographiques : la rgion Sud, la rgion parisienne et le Nord. Ce dcoupage sera amen changer en fonction de lvolution des marchs et dune ventuelle nouvelle activit dexport. Nous commercialisons trois gammes de produits : les cordes dattache, les sangles et les mousquetons. Le dveloppement dune gamme de baudriers est envisage pour le dbut de lanne prochaine, les tests ont dj dmarr. Dans ce contexte, le 24 du mois dernier, Eddy K. a t on ne peut plus clair. Il veut, chaque lundi, le chiffre daffaires de chaque repr Groupe Eyrolles, 2008

9


DExcel Access

sentant pour chaque zone et pour chaque gamme de produits, priode par priode. Pour le reste, il ne veut rien entendre de nos contraintes informatiques. nous de nous dbrouiller. Nous avons ici traiter une information selon quatre axes danalyse (quatre dimensions) : le reprsentant : la zone ; la gamme de produits (que nous appellerons plus simplement produit) ; et enfin la priode. De plus, linformation fournie devra bien videmment tre : fiable, cest bien le moins que lon puisse demander ; disponible rapidement : des statistiques mensuelles exactes mais avec trois mois de retard ne servent pas grand chose ; volutive et conomique : il nest pas question de passer tout notre temps grer ces statistiques, Eddy a dautres projets nous confier par la suite.


Une dfinition dune base de donnesNotre cas pratique nous amne runir un ensemble dinformations (Reprsentant, Zone, Produit et Vente) de faon suivre nos statistiques commerciales. Nous pouvons donc avancer la dfinition suivante : une base de donnes est un ensemble dinformations organise dans un but dfini . Les exemples de base de donnes abondent : carnet dadresses, annuaire, fichier des prospects dune entreprise, systme de gestion comptable avec gestion des facturations et des encaissements... Pour chacun deux, notre dfinition est valide. Par exemple, pour un systme de gestion comptable, elle peut se personnaliser ainsi : ensemble des informations relatives aux clients, aux produits, aux commandes et aux livraisons, organises de faon pouvoir suivre, par client, par commande et par livraison, les factures correspondantes et les rglements effectus . Rajouter des fonctionnalits complmentaires, par exemple suivre les impays et les relances, pourra amener ajouter la base de donnes des informations complmentaires, ici en lespce les dlais de rglements thoriques accords aux clients.

Pourquoi la solution tableur feuille-classeur ne convient pasDans notre cas pratique, le premier rflexe que nous pourrions avoir ( vrai dire celui quauraient 99 % des utilisateurs de tableur) serait douvrir Excel ou Calc et de nous lancer dans la cration de classeurs avec des tableaux complexes remplis de formules pour suivre nos statistiques. 10 Groupe Eyrolles, 2008

Figure 111

Une structure dorganisation traditionnelle (une feuille par priode, un classeur par reprsentant)


Nous pouvons par exemple choisir une organisation de nos donnes selon le schma de la figure 111 : un classeur par reprsentant ; autant de feuilles que de priodes ; les zones en colonnes et les produits en ligne de chaque feuille (figure 112).

Figure 112

La feuille de calcul (Zone en colonne et Produit en ligne) correspondant lorganisation feuille-classeur de la figure 111

Cette solution tableur feuille-classeur oblige disperser linformation dans de multiples feuilles de calcul qui, mme si elles sont toutes bases sur le mme schma, sont toutes indpendantes les unes des autres. Ceci prsente au moins les deux dfauts rdhibitoires suivants : Elle oblige un choix dorganisation des donnes qui ne pourra tre modifi. Elle ne pourra pas suivre lvolution de la structure et des besoins de notre entreprise. Pourquoi ?

Le choix de lorganisation feuille-classeur idaleDans notre tude de cas, nous avons prcdemment choisi lorganisation de la figure 111. bien y rflchir, il existe beaucoup dautres possibilits dorganisation de nos donnes, par exemple celle de la figure 113, avec un classeur par reprsentant, une feuille par zone, les priodes et les produits en lignes et colonnes. Chaque solution potentielle privilgie un point de vue et comporte avantages et inconvnients : La figure 111 offre une vision claire des ventes par produit et par zone pour un reprsentant et une priode donns. Groupe Eyrolles, 2008

Figure 113 Une autre organisation

des donnes par multiplication de feuilles identiques

11


DExcel Access

TECHNIQUE Intervertir lignes et colonnesdun tableau Excel ou CalcDans notre cas pratique, si, pour des raisons de prsentation, nous dcidons dintervertir lignes et colonnes, il faut procder ainsi : 1. Slectionner le tableau. 2. Le copier par Ctrl + C. 3. Se placer sur une feuille vierge, et via un clic droit choisir Collage spcial, puis loption Transpos. Attention quand mme, cette manipulation dplace contenus et format des cellules. Il vous faudra donc certainement reparamtrer intgralement formats et encadrements qui auront t chambouls par la transposition

Par contre, si nous prfrons visualiser les ventes par produit et priode pour un reprsentant et une zone donns, il vaudra mieux opter pour la figure 113. Nous en sommes donc rduits un choix dans lequel, objectivement, aucune solution ne simpose par rapport une autre, dautant que nous ne serons jamais certains davoir bien compris ce quEddy avait demand, ni mme quil ne changera pas davis...

Aucune solution feuille-classeur ne saura voluerLa vie de lentreprise nest pas un long fleuve tranquille, et la vrit dun jour nest pas ncessairement celle du lendemain. Pour ce qui nous concerne, il est tout--fait possible par exemple que la dimension Produit prenne progressivement un aspect prpondrant, auquel cas aucune de nos deux possibilits de dpart ne conviendra plus. Et quand bien mme ce cas ne se produirait pas (ce qui est bien improbable, convenez-en), larrive prvisible de nouveaux produits ( commencer par les baudriers) va nous obliger reprendre tous nos tableaux. Trs concrtement, pour ajouter un nouveau produit dans notre base avec une solution feuille-classeur selon le schma de la figure 111 (avec les produits en ligne), il va nous falloir, pour chacune des feuilles de chaque classeur, insrer la ligne Baudriers et reprendre toutes les formules de totalisation. Avec quatre reprsentants (quatre classeurs) et un historique de vingt-quatre priodes (cela ne fait jamais que deux ans...), nous aurons 4 classeurs 24 feuilles = 96 feuilles modifier, avec chaque fois une ligne insrer et toutes les formules entrer et vrifier. Franchement, cela vous dit ?MTHODE Le degr daccessibilit des informations dun tableurLes donnes figurant dans les lignes et les colonnes dun tableau sont les seules tre exploitables du premier coup dil (figure 112). Ds quelles figurent sur une autre feuille, elles sont ncessairement moins disponibles, car disperses en fait sur plusieurs crans et donc non visualisables en mme temps. Et cest encore plus vrai si vous les stockez dans dautres classeurs, ou mme dans dautres dossiers ! Donc autant viter tant que possible de disperser ses informations, par exemple en les groupant dans le mme tableau...


12


La solution : les listes de donnes sous tableurNous venons de constater les limites de lutilisation traditionnelle du tableur. Lalternative consiste employer des listes de donnes sous tableur, dont les principaux avantages sont la souplesse, lvolutivit et les performances. Les quelques exemples qui suivent vont certainement vous en convaincre.

La liste de donnes est souple et volutiveAvec les listes de donnes, nous naurons plus besoin de nous poser la question : quest-ce que je vais mettre en ligne, en colonne, en feuille... . Au contraire, nous allons entrer linformation dans un seul tableau comme nous le verrons partir du chapitre suivant et, par un coup de baguette magique, nous pourrons la prsenter exactement comme nous le souhaitons. Nous pourrons par exemple fournir Eddy ltat suivant (figure 114) qui synthtise pour tous les reprsentants les ventes par produit et par zone. Saperoit-il de la faiblesse des ventes dans le Nord de la France ? En deux clics, nous lui fournirons lanalyse de la figure 115.Figure 115 Figure 114 Les ventes par produit et zone,


toutes priodes et reprsentants confondus

Zoom par reprsentant et produit pour la zone Nord. Seule Sophie a vendu des Cordes. Nous retrouvons bien les 3 930 de cordes pour le Nord de la figure 114.

OBSERVONS Le filtre de la figure 115En haut de cet tat, ct de lintitul Nord dans le champ Zone, apparat notre entonnoir de filtre. Il suffira de cliquer dessus pour voir apparatre les trois zones et de slectionner celle de son choix (la figure 116 donne la mme vision, mais cette fois-ci pour le Sud).

Figure 116 Le mme tat que la figure 115, mais pour la zone Sud

La liste de donnes sadapte parfaitement au changementImaginons maintenant quun nouveau reprsentant intgre lentreprise. Avec une solution feuille-classeur traditionnelle, il y aurait plus ou moins de travail selon loption choisie : Groupe Eyrolles, 2008

13


DExcel Access


PRODUCTIVIT 30 secondes maximumpour tous ces tatsCerise sur le gteau, les listes de donnes se grent trs simplement et trs rapidement. Avec une conomie de temps et de moyens remarquable. titre dillustration, tous les tats des figures 114, 115, 116 et 117 ont t programms en 30 secondes chrono. Qui dit mieux ?

Avec une organisation du type de la figure 111 ou 113 (un classeur par reprsentant), il suffirait alors de rajouter un autre classeur pour ce reprsentant. On se serait alors relativement bien sorti daffaire. Encore que... Imaginons que dans ce cas nous ayons cr un classeur de consolidation des ventes, tous reprsentants confondus. Lajout dun nouveau classeur correspondant ce nouveau reprsentant nous aurait oblig modifier toutes les formules de consolidation en y intgrant le nouveau reprsentant Si, par contre, nous avions choisi dintgrer la dimension Reprsentant en ligne ou en colonne de nos feuilles de calcul, la seule solution aurait alors t de prendre notre courage deux mains et dinsrer dans tous les classeurs et dans toutes leurs feuilles soit une ligne, soit une colonne pour le nouveau reprsentant... Bien videmment, il aurait galement fallu reprendre toutes les formules... Ces deux solutions ne sont pas satisfaisantes : elles sont toutes deux trs lourdes et difficilement adaptatives. Par contre, en mode liste de donnes, il suffit de saisir le nom du nouveau reprsentant pour quautomatiquement il soit pris en compte dans les tats avec bien videmment toutes les formules mises jour. La figure 117 en fournit la preuve.

Figure 117

Lintgration dun nouvel lment est automatique en mode liste de donnes.

14


Synthse : les avantages des listes de donnes sous tableurUne base de donnes peut se dfinir comme un ensemble dinformations organises dans un but dfini. Il est possible de traiter une base de donnes simple comme celle de notre cas pratique de matriel dalpinisme sous ce mode particulier des tableurs que sont les listes de donnes. Il prsente, par rapport une organisation traditionnelle des donnes sous forme de feuilles et de classeurs, des avantages dcisifs, en termes de simplicit, de fiabilit, dadaptabilit et de performance. Nous allons en dtailler le fonctionnement au cours des chapitres 2 4, avant daborder ltude des bases de donnes plus complexes que sont les bases de donnes relationnelles partir du chapitre 5.



15



chapitre


2

Btir une liste de donnes efficace sous tableur : structure, rgles de validation et mise en formeCe document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

SOMMAIRE

B Dfinitions pralables

Nous allons ici apprendre construire une liste de donnes scurise sous tableur et comment une mise en forme spcifique permet une analyse efficace.

B Bien structurerune liste de donnes

B Les rgles de validationdune liste de donnes

B Les mise en formeconditionnelle de listes de donnes MOTS-CLS

B Liste de donneset base de donnes

B Champ et enregistrement B Les cinq rglesdune liste de donnes

B Rgles de validation,message daide la saisie et message derreur B Mise en forme conditionnelle : isoler un lment, visualiser les 10 premiers lments, barre de donnes B Doublons


DExcel Access

Ce chapitre dbute vritablement ltude des bases de donnes simplifies que sont les listes de donnes sous tableur. Elle se poursuivra au cours des chapitres 3 et 4. Aprs quelques prcisions terminologiques, nous dcouvrirons dans ce chapitre les cinq rgles rgissant la structure de ces listes de donnes. Nous verrons ensuite comment le paramtrage de rgles de validation permet dassurer la conformit des donnes, simplifie le travail des oprateurs et acclre les traitements. Enfin, nous apprendrons isoler par quelques astuces visuelles et graphiques certains enregistrements parmi dautres.

Dfinitions pralablesCe document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

Les bases de donnes utilisent un vocabulaire simple mais spcifique. Il est ncessaire de le matriser. Voyons-en ici les principaux termes.

Les listes de donnes et bases de donnes : lorganisation de linformationUne liste de donnes sous tableur est une base de donnes simplifie. Dans le chapitre prcdent, nous avons dfini une base de donnes comme un ensemble dinformations organises dans un but dfini . La complexit de lorganisation des donnes traiter va dterminer si nous avons affaire une vritable base de donnes ou une simple liste de donnes. Nous verrons dans les chapitres consacrs Access et Base, partir de la page 89, quune vritable base de donnes, pour autant que ce mot ait un sens, se compose de plusieurs lments dpendants les uns des autres. Par exemple, dans une association, les membres sinscrivent des activits encadres par des animateurs ; la base de donnes correspondante devra au moins comprendre trois lments individualiss : Membres, Activits et Animateurs. Une liste de donnes est galement une base de donnes, mais une base de donnes simplifie en ce sens quelle nest compose que dun seul lment, en loccurrence un tableau, comme notre planning du chapitre 1 ou notre cas pratique de matriel dalpinisme. Les tableurs comme Excel ou Calc possdent des fonctions intgres trs efficaces ddies ces listes de donnes. Cest leur tude que nous dmarrons ici.

Les champs : la structureLes champs dune base de donnes sont en fait les informations qui y seront listes. Ils dfinissent en quelque sorte la structure de la base. Si 18 Groupe Eyrolles, 2008

on reprsente la base de donnes sous la forme dun tableau, les champs sont en gnral lists en premire ligne, en en-tte de colonne. Par exemple, notre cas pratique de planning dcole de commerce contenait trois champs : le groupe, la date et le crneau. Une base de donnes comprend autant de champs que lon souhaite, on peut en ajouter ou en supprimer volont. Toutefois, une fois que les champs ncessaires ont t crs, en dautres termes une fois que la structure de la base est arrte, le nombre et le nom des champs nont plus tre modifis. Dans notre planning dcole de commerce, les trois champs Groupe, Date et Crneau sont ncessaires et suffisants, inutile den ajouter mais interdiction den enlever.Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

EXEMPLES Dautres champs pour dautres bases de donnesDans le cas dun annuaire, on peut imaginer que les champs de la base de donnes seront le nom de labonn, sa ville, son adresse et videmment son numro de tlphone, et pourquoi pas son e-mail. Pour un fichier de prospects commerciaux, le nom de lentreprise, le nom du contact, ses coordonnes, les produits qui peuvent lintresser, les diffrents contacts que nous avons eu avec lui, peuvent constituer les diffrents champs de la base. Et pourquoi pas la date laquelle nous devons le recontacter. La structure des champs dune base de donnes est donc fonction des buts que lon assigne cette base.

Les enregistrements : le contenuLes champs dune base de donnes dfinissent sa structure, les enregistrements son contenu. Ainsi, notre exemple de planning du chapitre 1 comprenait trente-deux enregistrements correspondant toutes les combinaisons groupe/date/crneau. Si nous considrons un annuaire sous langle base de donnes, les diffrents abonns au tlphone en constituent les enregistrements. Dans une base, le nombre denregistrements (tout comme le nombre de champs) nest heureusement pas limit, cest mme tout lintrt. Par contre, autant le nombre de champs est stable une fois quil est arrt, autant le nombre denregistrements est lui constamment variable. Par exemple, la taille de lannuaire des abonns au tlphone augmente en fonction des nouveaux abonns et diminue en fonction des rsiliations.

OUPS Les slecteurs de filtrenapparaissent pasNe vous inquitez pas si les slecteurs de filtre napparaissent pas automatiquement sur votre cran, tout dpend du logiciel que vous utilisez. Leur fonctionnement est dtaill au chapitre 3.

Bien structurer une liste de donnesNous reprenons ici ltude du cas pratique de liste de donnes de matriel dalpinisme. La figure 21 reprsente la liste de donnes correspondante. Cette liste de donnes se prsente bien comme un tableau classique, avec les noms de champ en premire ligne et les diffrents enregistrements les uns au-dessous des autres. Sur la figure 21, les flches qui apparaissent dans les cellules des noms de champs sont les slecteurs de filtres, ceux que nous avons utiliss au chapitre 1 pour afficher ou masquer certains enregistrements. La structure de cette liste de donnes, simplissime, rpond cinq rgles ncessaires et suffisantes. Dtaillons-les.

TLCHARGER Cette liste de donnessous tableur est disponible en tlchargementLidal serait que vous testiez toutes les manipulations des chapitres 2 4. Pour vous faciliter la tche, la liste de donnes de notre exemple est disponible en tlchargement sur la fiche ouvrage sur le site www.editions-eyrolles.com. Mais vous pouvez aussi la saisir manuellement sur votre tableur favori, Excel ou Calc. Voire mme crer directement votre propre liste de donnes.


19

2 Btir une liste de donnes efficace sous tableur : structure, rgles de validation et mise en forme


DExcel Access

Figure 21

La liste de donnes de notre cas pratique : un tableau classique, les Champs en en-tte de colonne et les Enregistrements les uns au-dessous des autres

Rgle 1 : les noms de champ en premire ligneNous avions dtermin quatre champs grer : le Reprsentant, la Zone, le Produit et la Priode. Nous les retrouvons en premire ligne de la figure 21 : Reprsentant, Zone et Produit sont respectivement en colonne B, C et D. Le champ Priode sest transform en Date, colonne E. Ce champ est plus dtaill et autorisera plus danalyses, comme lextraction des ventes ralises entre deux dates. Nous remarquons galement deux colonnes supplmentaires, correspondant deux champs : Le montant de chaque vente, celui que nous aurions saisi dans les cellules de nos tableaux, se trouve tout fait droite en colonne F. Et le champ Numro qui sert mettre un peu dordre dans notre liste de donnes. Notre liste de donnes complte de la figure 21 se compose donc de six champs, correspondant chacun une colonne : Numro : ce champ numrote les enregistrements par ordre de saisie, et ne contient que des nombres entiers de 1 N. Reprsentant : ce champ naccepte pour le moment que les valeurs Jean , Sophie , Pierre ou Hlne , et ne contient donc que des caractres alphabtiques. 20 Groupe Eyrolles, 2008

Zone : ce champ naccepte pour le moment que les valeurs Nord , Sud ou RP pour Rgion parisienne ; il ne contient donc aussi que des caractres alphabtiques. Produit : ce champ naccepte pour le moment que les valeurs Cordes , Sangles ou Mousquetons , et ne contient donc galement que des caractres alphabtiques. Date : ce champ est de format JJ-MMMM-AA. Montant : ce dernier champ est de type numrique sans dcimales, au format . Il est important de porter son attention sur le champ Numro, car il joue un rle essentiel. Nous lavons cr pour pouvoir rapidement trier lensemble de nos donnes selon lordre chronologique de saisie. Mais il va galement nous servir nous y retrouver clairement dans notre liste ; par exemple, plutt que de parler de la vente de cordes de 2 571 du 31 janvier, on parlera de la vente n 8. Cest plus simple, plus prcis et plus rapide.


Rgle 2 : les enregistrements les uns au-dessous des autresIl suffit de saisir, partir de la ligne 2, les enregistrements de la liste de donnes, cest--dire son contenu, les uns au-dessous des autres, sans se poser de question. Actuellement, notre liste contient trente enregistrements, de la ligne 2 la ligne 31. Nous pourrions en ajouter satit. Si les ventes augmentaient, tant en nombre quen volume, il suffirait de les saisir la suite des autres, sans autre limitation que le nombre de lignes disponibles dans une feuille de calcul, ce qui laisse tout de mme une certaine marge. Nous pouvons donc assurer Eddy que notre liste de donnes pourra accepter toutes les nouvelles ventes de notre socit.PRODUCTIVIT Numroter / renumroter les lignes dune liste de donnesPour numroter ou renumroter les cellules de A1 A100 : 1. Saisissez 1 en A1puis saisir 2 en A2. 2. Slectionnez les deux cellules A1 et A2. 3. Recopiez par la poigne de recopie jusquen cellule A100. Le tableau va alors alimenter les cellules vides en ajoutant la valeur 1 correspondant lincrment entre 1 et 2 de A1 et de A2. Vous pouvez changer cet incrment : Si vous saisissez 0 en A1 et 2 en A2, vous obtiendrez la srie suivante : 0, 2, 4, 6, 8... avec un incrment de 2. Si vous saisissez 1 en A1 et 5 en A2, vous obtiendrez cette fois 1, 5, 9, 13, 17, soit un incrment de 4. Si vous saisissez 0 en A1 et 5 en A2, vous obtiendrez enfin 0, 5, 10, 15, 20, soit un incrment de 5. Groupe Eyrolles, 2008

INFO Nombre de lignes disponiblessur une feuille de calculUne feuille de calcul peut comporter jusqu 1 048 576 lignes pour Excel 2007, et 65 536 seulement pour Excel 2003 et Calc. Cela dit, les alimenter toutes risquera de poser de srieux problmes de temps de recalcul, sans compter les maux de tte...

JE ME SUIS TROMP Jai fait une erreur de saisiedans la liste de donnes...Si jamais vous faites une erreur de saisie dans une liste de donnes, il suffit de la rectifier comme vous le feriez dans une feuille de calcul classique. Si jamais vous oubliez de saisir une vente, vous pouvez soit : simplement lajouter en bas de la liste de donnes, avec linconvnient que son numro chronologique ne correspondra pas la ralit ; insrer une ligne au bon endroit, avec lavantage que le numro sera correct mais avec linconvnient quil faudra alors renumroter les lignes, comme expliqu dans lapart Productivit ci-contre.

21


DExcel Access

TRUC Utiliser et faciliter le mode de saisie assiste des tableursQuand on saisit des informations dans un tableur, celui-ci, prvenant, regarde toujours au-dessus dans la mme colonne sil ne pourrait pas nous aider en nous suggrant, au fur et mesure de notre saisie, des entres dj effectues. Concrtement, si nous nous plaons la cellule D32 du tableau de la figure 21 et que nous saisissons S , le tableur va automatiquement nous proposer Sangles parce que cest la seule entre existante de la colonne commenant par S. Si nous avions grer deux types de cordes, les Cordes dattache et les Cordes de rappel, il faudrait saisir Cordes d ou Cordes de pour que le tableur puisse faire la distinction. Do perte de temps et gain dnervement. Sauf si nous choisissons la terminologie Attache ou Rappel simplement, auquel cas taper simplement A ou R suffira faire le distingo et acclrer sensiblement notre saisie ! Penser donc, quand cest possible, ne pas avoir dans le mme champ deux entres avec la mme initiale. La suggestion automatique des entres de la liste de donnes fonctionnera bien plus efficacement.


Rgle 3 : ni ligne ni colonne videCONDITIONNEL Risque ? Excel ou Calc risquent selon le cas de ne travailler que sur une partie de la liste... . Le terme risquent est un peu vague. Et tonnament impossible clarifier. Les cas de figure sont trop nombreux pour tre tous lists, dans certains cas il ny aura pas de problme et dans dautres il y en aura. Dans ces conditions, le principe de prcaution simpose : ninsrer ni ligne ni colonne vierge dans une liste de donnes !

Si, par mgarde, notre liste de donnes contient soit une ligne soit une colonne intgralement vierge, le tableur risque de ne travailler que sur une partie de la liste de donnes et doublier en quelque sorte tout ce qui aura t saisi de lautre ct de cette dangereuse ligne ou colonne vierge. Prenons lexemple ( ne pas suivre) de la figure 22. Cette liste de donnes comprend une ligne vide, la 18. Quand nous utiliserons les fonctions de liste de donnes, Excel ou Calc risquent de ne travailler que sur une partie de la liste, soit de A1 F17, soit de A19 F28, en omettant donc une partie de la liste.

Figure 22

Une liste de donnes non conforme (avec une ligne vierge)

22


ceci il existe un remde imparable : crer une liste de donnes compacte, sans ligne ni colonne vierge, comme sur notre figure 22.

Rgle 4 : pas de mise en forme inutileInutile de se casser la tte insrer des quadrillages, ou chercher raliser une mise en page sophistique de la liste de donnes. En effet, elle ne sert qu collecter linformation, et jamais elle ne sera imprime en tant que telle ; nous ne lutiliserons quau travers de sorties et de documents comme ceux que nous avons dj vu et dautres que nous apprendrons crer. Contentons-nous simplement de faire quelque chose de propre et de lisible, ce sera largement suffisant...Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

EXCEL 2007 Des listes de donnes lgamment quadrilles en un clicExcel 2007 permet quand mme sans se fatiguer de crer des surlignages comme dans la figure 21. Le regard y suit plus facilement les lignes, inutile de sen priver ! Dans longlet Accueil, groupe Style, cliquez sur Mettre sous forme de tableau.

Rgle 5 : pas de formuleTravailler avec des listes de donnes oblige perdre pas mal de rflexes. commencer par lhabitude dinsrer des formules un peu partout. Inutile donc dinsrer la ligne Total en bas de tableau pour calculer le montant total des ventes. Et encore plus dinsrer des lignes entre les ventes de chaque reprsentant pour en calculer le total. Les fonctions de filtres (page 50), de sous-total (page 60) et de tableau crois dynamique (page 71) le feront en deux temps trois mouvements et sans aucun risque derreur comme le montre la figure 23.

Figure 23

Une liste de donnes (brute et sans formule) permet de gnrer automatiquement toutes sortes de tableaux danalyse.

La liste de donnes ne contient donc que les donnes, sans aucune formule de totalisation. Groupe Eyrolles, 2008

23


DExcel Access

Nous connaissons maintenant les rgles de base pour construire une liste de donnes. Il est donc temps daborder ltude des rgles de validation pour sassurer de la conformit des donnes au moment de leur saisie.

Les rgles de validation dune liste de donnesTout comme on ne remplit pas le rservoir dune voiture diesel avec de lessence sans plomb, il est essentiel, dans une liste de donnes, de valider les entres au moment de leur saisie afin dviter pas mal de dysfonctionnements.Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

Par exemple, dans notre exercice, nous savons quil nexiste que quatre reprsentants, Hlne, Sophie, Jean et Pierre. Faire une faute dorthographe la saisie sur un nom, par exemple Sofie ou mme Helene , sans accent, aura pour effet de crer un reprsentant supplmentaire quExcel et Calc traiteront comme un reprsentant part entire, diffrent, sans faire le lien avec Sophie ou Hlne. Ce sera encore pire si on saisit dans le champ Reprsentant le nom dun produit, ou encore si on saisit la date de la vente dans la colonne Montant. Dans tous les cas, le rsultat sera inexploitable. Lide gnrale des rgles de validation est de rendre impossible ces erreurs de saisie et donc de garantir la conformit des donnes avec ce qui est attendu afin dassurer le bon fonctionnement de la liste de donnes, de mnager les nerfs des oprateurs de saisie (qui savent ds lors que toute erreur grossire de saisie est impossible) et donc au bout du compte de simplifier le travail. Attention : les rgles de validation ninterdisent bien videmment que les erreurs de conformit de donnes par rapport des rgles. Elles vont interdire par exemple de saisir un nombre la place dun texte, de saisir le nom dun reprsentant qui nexiste pas, etc. mais certainement pas les erreurs de saisie non logiques comme par exemple daffecter Pierre une vente de Jean. Do la ncessit, malgr la prsence de toutes les rgles de validation possibles et imaginables, de procder une contrle minutieux de lexactitude de la saisie (et non plus de leur simple cohrence) ! Grce aux rgles de validation, vous allez pouvoir dfinir, pour chaque champ dont vous voulez contrler lentre : Les rgles de validation proprement dites, cest--dire les critres qui rendront la saisie valide ou non. Les messages daide la saisie pour aider les oprateurs dans leur saisie. Les messages et les actions effectuer en cas de non conformit avec les rgles de validation. 24 Groupe Eyrolles, 2008

Le mode opratoire gnral pour accder au paramtrage des rgles de validation est le suivant : 1 Slectionnez la zone sur laquelle la rgle devra tre active. Cette zone correspond en gnral la colonne entire du champ considr, lexclusion de son en-tte qui contient non une entre denregistrement mais le nom du champ. 2 Appelez la bote de dialogue Validation des donnes par : Le bouton Validation des donnes de longlet Donnes, groupe Outils de donnes sous Excel 2007. Loption Validation du menu Donnes avec Excel 2003. Loption Validit du menu Donnes avec Calc.Ce document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

TRUC Slectionner une colonnesauf son en-ttePour slectionner une colonne sans son en-tte, il faut cliquer sur len-tte de colonne (la lettre A, B, C...) puis dslectionner la premire cellule par Ctrl + Clic.

Le paramtrage des rgles de validation proprement ditesCette section va nous guider dans la mise en uvre effective des rgles de validation dans notre liste de donnes. La figure 24 dtaille la bote de dialogue de validation des donnes sous Excel 2003 et 2007. Les manipulations Calc sont trs proches.MTHODOLOGIE Quand paramtrerles rgles de validation ?Les rgles de validation dune liste de donnes sous tableur, la diffrence dAccess ou de Base, ne sappliquent quaux nouvelles entres, et non aux donnes dj prsentes dans la liste. Il est donc ncessaire de les paramtrer ds le dbut de son travail, avant mme toute saisie.

Figure 24

La bote de dialogue Validation des donnes.

Le tableau ci dessous reprend lessentiel des options proposes par Excel 2007 :Tableau 21 Principales possibilits de contrle de validit des entres dans une liste de donnes (Excel) Nom de loption de la bote de dialogue Validation des donnes Effet

Tout Nombre entier * Dcimal * Liste * Groupe Eyrolles, 2008

Aucun contrle nest effectu. Option par dfaut. Limite la saisie un nombre entier (avec possibilit de dfinir des bornes). Limite la saisie un nombre dcimal (avec possibilit de dfinir des bornes), sans toutefois pouvoir dfinir le nombre de dcimales autorises. Limite la saisie aux valeurs dune liste.

25


DExcel Access

Tableau 21 Principales possibilits de contrle de validit des entres dans une liste de donnes (Excel) (suite) Nom de loption de la bote de dialogue Validation des donnes Effet

Date Heure Longueur du texte

Limite la saisie aux entres sous forme de date (JJ/MM/AA ou autre) avec la possibilit de fixer des bornes. Limite la saisie aux entres sous forme dheure (MM:SS ou autre) avec la possibilit de fixer des bornes. Permet de limiter la longueur dun champ texte pour viter les listes de donnes trop volumineuses (limiter un champ Prnom 10 caractres interdira de saisir Jean-Christophe). Pour limiter les entres la valeur dune formule comme expos ci dessus. Si coch, permet de ne pas appliquer la rgle de validation quand la cellule est vide. Comme les rgles de validation sappliquent la colonne entire, il faut bien videmment cocher cette case systmatiquement (sinon toutes les cellules vides en attente de saisie seront rejetes).

Personnalis * Ignorer si videCe document est la proprit exclusive de bagi alfred ([email protected]) - 26 Octobre 2009 12:30

* Ces options sont dtailles ci-dessous.

Limiter les entres un nombre entier ou dcimalPour limiter la saisie dun champ des valeurs numriques, slectionnez la colonne correspondante (sauf son en-tte) et appelez la bote de dialogue Validation des donnes comme dcrit ci-dessus, slectionnez longlet Options et paramtrez les critres souhaits. Le paramtrage de la bote de dialogue de la figure 25 permet par exemple de contrler lentre de nombres entiers positifs infrieurs gal 12 000. Il est possible de choisir dautres conditions (suprieur, infrieur, gal, diffrent de ...).

Figure 25

Seuls les entiers compris entre 0 et 12 000 seront accepts.

26


Limiter les entres aux donnes dune listeLoption Liste de la liste droulante de la bote de dialogue Validation des donnes va permettre de limiter les entres dun champ aux valeurs dune liste cre manuellement. Par exemple, dans notre cas pratique, pour contrler strictement la saisie dHlne, Sophie, Jean ou Pierre dans notre liste de donnes. La figure 26 illustre le paramtrage reproduire pour cela 1 Saisissez, dans un espace rserv de la feuille de calcul, les entres autorises : les noms de nos quatre reprsentants. Le mieux est de les saisir en bloc, bien lcart de la liste de donnes, par exemple dans notre cas pratique de H1 N1, en prenant bien garde laisser au moins une colonne libre entre cette zone des entres autorises et la liste de donnes en elle-mme pour que les donnes ne se mlangent pas. Une fois la liste cre, appelez la bote de dialogue Validation des donnes. 2 Choisissez Liste dans la liste de choix Autoriser (et laissez les cases Ignorer si vide et Liste droulante dans la cellule coches). 3 Dsignez dans la zone Source les cellules o vous avez saisi prcdemment les entres autorises (de H1 H4 dans notre cas pratique pour le champ Reprsentant). 4 Validez, cest termin.


Figure 26 Le paramtrage dune liste de validation (de H1 H4) pour le champ Reprsentant

(la colonne B correspondant ce champ, lexclusion de la cellule B1, est slectionne)

Il est maintenant impossible loprateur de saisir une valeur absente de la liste paramtre ci-dessus.


27


DExcel Access

Crer une rgle de validation personnaliseIl est galement possible de limiter les saisies dans un champ, non par rapport une liste, mais par rapport une rgle. Par exemple il peut tre intressant dinterdire dantidater des enregistrements dans le futur, cest--dire que si le jour de saisie est le 12 mai il faut empcher la saisie du 14 mai.TECHNIQUE La fonction Maintenant()Excel et Calc possdent de nombreuses fonctions dont beaucoup sortent des strictes fonctions de calcul. Comme cette fonction Maintenant() qui rappelle la date et lheure ( la seconde prs) de la saisie. Son quivalent Aujourdhui() ne rappelle que la

Documents

2212120664 Excel