46289422 Excel Access

7/23/2019 46289422 Excel Access

1/368

tabo

T

h

ie

rry

C

a

p

ro

n

AccessCroiser, extraire et analyser ses donnes

DExcel

7/23/2019 46289422 Excel Access

2/368

taboo

Pour que linformatique

soit un outil

et non un ennemi !

Access, l o Excel sarrte

Quand i l f au t cons igne r ch i f f r e set donnes, le premier rflexe est derecourir au classique tableur Excel (ouCalc). Cependant, par crainte de satta-quer aux concepts redouts des basesde donnes, ou par mconnaissance,

lutilisateur se prpare des lendemainsdifficiles. En effet, le tableur est vitedbord mesure que les besoins aug-mentent. Dans bien des cas, il suffit defaire jouer les fonctionnalits de base dedonnes dExcel ou mme de passer Access (ou Base) pour crer et grer sesdonnes dans une vritable base.

Thierry Capron est diplm de lESCP. Il aexerc diverses responsabilits opration-nelles en entreprise durant 15 ans (CBS,Arcelor Mittal, Point P). Depuis 10 ans, ilconsacre tout son temps la formation encomptabilit et bureautique auprs depublics varis (EDF GDF, Eurocopter, Ceram

Sophia Antipolis).

Tlchargez les fichiers des cas pratiques

sur www.editions-eyrolles.com

Conception:NordCompo

Pour un traitement plus performant de vos donnes !

> Construisez une base de donnes minimale grce aux listes dedonnes sous Excel et Calc

> Triez et filtrez des enregistrements dans vos listes de donnes

> Exploitez vos listes de donnes laide de la fonction Sous-totalet des tableaux croiss dynamiques

> Modlisez une base de donnes relationnelle prenne et volutivesous 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 manipulationsde chanes de caractres

> ditez et modifiez vos tats

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 sousOpenOffice.org.

7/23/2019 46289422 Excel Access

3/368


DExcel

7/23/2019 46289422 Excel Access

4/368

Chez le mme diteur

M. 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. TayLoret 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. roCHFeLdeT 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.

Ouvrages consacrs Excel et Access

Excel 2007 avanc.

P. moreaueT P. mori.

N12217, 2008, 240 pages.

Excel Missing Manual.

m. maCdoNaLd.

N12095, 2007, 818 pages.

Mmento Excel.m. GreyeT 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. morieT y. PiCoN.

N11490, 2004, 422 pages.

VBA pour Access 2003.

a. TayLoreT V. aNdersoN.

N11465, 2004, 636 pages.

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. GauTiereT m. berGame.

N11729, 2005, 14 pages.

OpenOffice.org 2.2 efficace Writer, Calc, Impress, Draw, Base.s. GauTier, C. Hardy, F. LabbeT miCHeL PiNquier.

N12166, 2007, 394 pages.

Ouvrages consacrs Calc et Base

Scuriser enfin son PCRflexes 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.

Collection Sans taboo

7/23/2019 46289422 Excel Access

5/368

santaboo

T h i e r r y C a p r o n


DExcel

7/23/2019 46289422 Excel Access

6/368

DITIONS EYROLLES61, bd Saint-Germain75240 Paris Cedex 05

www.editions-eyrolles.com

Le code de la proprit intellectuelle du 1erjuillet 1992 interdit en effet expressment la photocopie usage collectif sansautorisation 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 uvresnouvelles 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

7/23/2019 46289422 Excel Access

7/368

Btir une base de donnes relationnelle ne simprovise pas. louverturedes cours Access que nous donnions au Ceram Sophia Antipolis, nousdemandions aux tudiants si lun deux connaissait ce logiciel et ce quil enpensait. La meilleure rponse fut quAccess est un logiciel dlicat . Qui-

conque sest frott ce type de logiciel sans prparation, sans connais-sance et sans mthode particulire, apprciera la pertinence de la remarque.

Ces futurs managers ntaient pas particulirement ports sur linforma-tique et encore moins sur la bureautique. Pourtant, en deux jours, ilstaient capables de monter une base de donnes relationnelle simplepour grer leurs problmes quotidiens de planning, doccupation desalles, voire de trsorerie ou de gestion du personnel.

Ce livre reprend les outils pdagogiques dvelopps cette occasion ettests grandeur nature avec un excellent taux de russite. Dans la pre-mire partie notamment, il expose de faon dtaille les tonnantes listesde donnes, bases de donnes simplifies sous tableur, trs largementmconnues 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 formulesalambiques ? Vous devez suivre le planning doccupation des salles derunion ou celui des prochaines vacances ? Vous devez grer les membreset les cotisations de votre association ? Vous voulez suivre vos prts deDVD ? Vous souhaitez connatre linventaire de votre cave vin et engrer la consommation ?

Avant-propos

7/23/2019 46289422 Excel Access

8/368

DExcelAccess

VI

Que ce soit pour des besoins professionnels ou personnels, vous trou-verez dans cet ouvrage plusieurs applications de la mme notion de base

de donnes. Ce livre est un condens de lexprience accumule en for-mation sur ce thme pour des publics trs varis (tudiants, cadres, maisgalement employs en rorientation professionnelle...).

Nul besoin dtre un virtuose du clavier ou un champion des macrospour sapproprier cette mthode. tre laise avec son ordinateur, ma-triser lessentiel de Windows et des fonctions de base dun tableur suffi-ront amplement.

Quelles sont les caractristiques de cettemthode ?Structurer son approche dun logiciel de base de donnes conditionne 99 % la russite dun projet. Cest donc trs logiquement que nous met-trons ici laccent sur la mthodologie de travail plus que sur les techni-

ques pures qui, de toute faon, trouveront naturellement leur place entemps et en heure.

De plus, pour intresser le public le plus vaste possible, lensemble de nosexplications sont directement applicables par les utilisateurs de Micro-soft Office 2003 et 2007 (Access et Excel) ainsi que par ceuxdOpenOffice.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 certesdes comptences techniques, mais surtout et avant tout une mthode detravail prcise et une approche mthodologique sans faille. Aussi avons-nous ax notre dmonstration sur le droul dune mthodologie rigou-reuse et teste avec succs, inspire en partie de la mthode Merise.

Au plan des techniques proprement dites, Access et Base sont des logi-ciels extrmement vastes, touffus et complexes avec lesquels le risque deperdre de vue lessentiel est important. Aussi avons-nous fait le choix dene dvelopper que les techniques incontournables, celles quil est impos-sible 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 etCalc proposent en matire de liste de donnes le sujet est plus simple.
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

9/368

Avant-propos

VII

100 % compatible Microsoft Office (2003 et 2007) etOpenOffice.org 2.2

Cette mthode aborde les bases de donnes sous deux angles : lestableurs et les bases de donnes relationnelles. Nous nous devions demaintenir cette ouverture sur le plan des logiciels traits. Nous avonsainsi choisi de prsenter les solutions apportes par la dernire version deMicrosoft Office (Excel et Access 2007) car elle deviendra terme laplus rpandue, mais nous navons pas oubli les utilisateurs de la versionprcdente (Excel et Access 2003). Enfin, soucieux de fournir au lecteurune ouverture sur le monde du logiciel libre, nous traitons galementdOpenOffice.org 2.2 avec Calc et Base.Puisquil fallait bien choisir un angle de vue privilgi pour les explica-tions et les captures dcran, nous avons dcid daxer notre expos pardfaut sur Excel et Access 2007. chaque fois que cela a t ncessaire,nous avons spcifi les particularits des versions 2003 ouOpenOffice.org 2.2 dans des sections ou encadrs spcifiques.

Que trouverez-vous concrtement dans celivre ? la fin de ce livre, vous serez capable de concevoir, programmer etexploiter des listes de donnes sous tableur (chapitres 1 4) ou des basesde donnes relationnelles simples (chapitres 5 13). Ces deux parties,

indpendantes, sont articules chacune autour de leur cas pratique, vri-tables fils directeurs pdagogiques.

Les listes de donnes sous tableur (Excel et Calc)Les listes de donnes sous tableur sont, tort, trs largement mcon-nues. Pourtant, elles offrent des possibilits tonnantes et sont trs sim-ples dutilisation. Plus dune fois, il nous est arriv de conseiller nos

clients dadopter pour leurs statistiques commerciales une solution de cetype. Les retours de terrain, manant aussi bien des fonctionnels chargsde llaboration de ces listes de donnes que des oprationnels qui entaient destinataires, ont toujours t excellents en termes de fiabilit, derapidit et de finesse.

Le chapitre 1 balaye, un peu la manire dune bande-annonce, lesavantages dcisifs mais trop peu connus des listes de donnes par rapportaux feuilles de calcul traditionnelles.

RESSOURCE Les cas pratiques du livre sontdisponibles en tlchargement

Tous les cas pratiques de cet ouvrage, logiciel parlogiciel et chapitre par chapitre, sont disponiblesen tlchargement sur le site des ditions Eyrolles.B www.editions-eyrolles.com

7/23/2019 46289422 Excel Access

10/368

DExcelAccess

VIII

Le chapitre 2 traite des rgles de construction dune liste de donnes per-formante et fiable, celle qui vous fera gagner du temps. Il aborde gale-

ment les tonnantes analyses visuelles quautorise Excel 2007, notamment. lissue du chapitre 3, vous saurez tout des possibilits de tri et de slec-tion denregistrements dans une liste de donnes.

Enfin, dans le chapitre 4, la dcouverte et la mise en uvre des fonctionsde sous-total et de tableau crois dynamique vont vous permettre de ralisertoutes les analyses statistiques de votre liste de donnes, mettant ainsi dfi-nitivement au placard les bonnes vieilles fonctions Somme () et autres .

Les bases de donnes relationnelles (Access et Base)Access et Base sont des logiciels dlicats dutilisation quil est impossibledaborder comme un traitement de texte ou un tableur. Priorit est doncdonne lapproche mthodologique, aussi bien pour la prparation dutravail (la modlisation) que pour la programmation proprement dite. Leconcept cl de relation est particulirement dvelopp.

Le chapitre 5 est ax sur la dcouverte du vocabulaire et des principesfondamentaux qui prsident au fonctionnement des bases de donnesrelationnelles.

Les mthodes danalyse et de modlisation du modle traiter sontdtailles au chapitre 6. Cest une particularit importante de cettemthode que de traiter, en termes clairs et simples, tous les conceptsfondamentaux qui vous permettront coup sr de dfinir prcisment ledessein et lorganisation de votre base de donnes relationnelle.

Le chapitre 7 permet de se familiariser avec linterface logicielle et lesoprations essentielles (crer et ouvrir une base, grer les problmes descurit, crer une table et ses champs...).

Ltablissement des relations entre les tables, fondement du modle rela-tionnel dune base de donnes, fait lobjet du chapitre 8. Essentielleentre toutes, cette tape valide dfinitivement le travail de modlisationet 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 traversdes analyses quon peut raliser sur ses donnes. Les requtes servent desupport ces analyses. Vous saurez btir, manipuler et modifier unerequte danalyse des donnes simple aprs ltude du chapitre 10.

7/23/2019 46289422 Excel Access

11/368

Avant-propos

IX

Les chapitres 11 et 12 poussent les requtes dans leurs derniers retran-chements, 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 relation-nelles par les tats/rapports qui servent, comme leur nom lindique, visualiser et imprimer les tables ou les requtes de manire claire, lisibleet professionnelle.

Enfin, il nous a paru intressant dapporter en annexe des complmentsdinformation, tout dabord sur la mthode de modlisation Merise, puissur les droits et obligations du citoyen et du professionnel au regard de la

Commission Nationale Informatique et Libert.

RemerciementsEn comparant les preuves finales avec le tout premier jet de ma rdac-tion, je ne peux que mesurer lnorme travail de conseil, daccompagne-

ment 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 qutoutes celles et ceux que je ne connais pas et qui ont apport leur contri-bution ce livre.

Comment ne pas saluer aussi comme il convient la dream team duCeram Sophia Antipolis, Marc, Michel et Fred : Access et PCIE, a

forge des souvenirs...Merci aussi tous les anciens participants mes cours et sessions de for-mation. Ils mont rappel tous les jours que si une explication nest pascomprise cest quelle nest pas claire.

Enfin, toute ma reconnaissance Bernard qui a su voici maintenantquelques annes porter mon premier projet ddition et Marie-Pierrequi continue peut-tre contre vents et mares croire en dautres livres.

7/23/2019 46289422 Excel Access

12/368

7/23/2019 46289422 Excel Access

13/368

Table des matires

XI

1. PREMIERSPASVERSLESBASESDEDONNES ................... 1Un traitement de texte ne sait pas grer une base de donnes,mme trs simple 2

Le tableur et ses filtres : une solution plus performante 4Appliquer un filtre pour slectionner des enregistrements par-ticuliers 4Changer la slection opre par un filtre 6

Les listes de donnes sous tableur : un pas vers les bases dedonnes 8

Lexpos de notre cas pratique dtude des listes de donnes 8Notre mission 9Notre cahier des charges simplifi 9Une dfinition dune base de donnes 10

Pourquoi la solution tableur feuille-classeur ne convient pas 10

Le choix de lorganisation feuille-classeur idale 11Aucune solution feuille-classeur ne saura voluer 12

La solution : les listes de donnes sous tableur 13

La liste de donnes est souple et volutive 13La liste de donnes sadapte parfaitement au changement 13

Synthse : les avantages des listes de donnes sous tableur 15

2. BTIRUNELISTEDEDONNESEFFICACESOUSTABLEUR : STRUC-TURE, RGLESDEVALIDATIONETMISEENFORME ........... 17Dfinitions pralables 18

Les listes de donnes et bases de donnes :

lorganisation de linformation 18Les champs : la structure 18Les enregistrements : le contenu 19

Bien structurer une liste de donnes 19Rgle 1 : les noms de champ en premire ligne 20Rgle 2 : les enregistrements les uns au-dessous des autres 21Rgle 3 : ni ligne ni colonne vide 22Rgle 4 : pas de mise en forme inutile 23

Rgle 5 : pas de formule 23Les 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 26Limiter les entres aux donnes dune liste 27Crer une rgle de validation personnalise 28Lannulation des rgles de validation 29

Laide la saisie et les messages derreur 29Le dtail des rgles de validation conseilles pour notre caspratique 31

Les mises en forme conditionnelle de liste de donnes 32Les mises en forme conditionnelles spcifiques Excel 2007 32

Isoler un lment dans une srie 33Identifier les dix premiers lments dune srie 34Visualiser la place de chaque lment au sein de la srie 36

Les mises en forme conditionnelles dExcel 2003 et de Calc 37Synthse : btir une liste de donnes efficace sous tableur :structure, rgles de validation et mise en forme 39

3. TRIERETSLECTIONNERDESENREGISTREMENTSDANSLESLISTESDEDONNES.......................................41Comment accder aux fonctions de liste de donnes 42Les tris de liste de donnes 43

Trier sur un seul champ 44Trier selon un ordre personnalis 44

Crer une liste personnalise 45

Trier selon une liste personnalise 46Effectuer plusieurs tris successifs 47

Les filtres ou la slection denregistrements 49Les filtres simples 50

Appliquer un filtre sur un champ unique 50Appliquer un filtre sur plusieurs champs 51Dsactiver un filtre 51

Les filtres volus 52

7/23/2019 46289422 Excel Access

14/368

DExcelAccess

XII

Les filtres volus spcifiques Excel 2007 53Les autres filtres volus dExcel 2007 et 2003 et de Calc 55

Synthse : les tris et les slections denregistrements par filtre 57

4. EXPLOITERLESLISTESDEDONNES : LESSOUS-TOTAUXETLESTABLEAUXCROISSDYNAMIQUES.................................. 59Totaux et sous-totaux dans une liste de donnes 60

Insrer un niveau unique de sous-total 61Exploiter les sous-totaux de listes de donnes 64

Afficher la liste intgrale des enregistrements,

des sous-totaux et du total gnral 64Affiner lanalyse : dvelopper ou rduire chaque sous-total 64Supprimer les sous-totaux 66Imbriquer plusieurs niveaux de sous-totaux 66Moyenne, 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 72Exploiter un TCD 75

Faire varier la dimension filtre 76Modifier la structure du TCD 77

Actualiser un TCD 79Organiser ses TCD 80Le graphique crois dynamique 80

Les GCD sous Excel 2007 80

Les GCD version Excel 2003 84Synthse : les fonctions de sous-total et de tableau (graphique)crois dynamique sont les instruments indispensables danalysedune liste de donnes 87

5. INTRODUCTIONAUXBASESDEDONNESRELATIONNELLES 89Les limites des listes de donnes 90

Une liste de donnes est un cas particulier dune base

de donnes 90Une liste de donnes est une base de donnesaux performances limites 91

Les concepts fondamentaux des bases de donnes relationnelles 93

Les tables : regroupement des donnes dun mme domaine 93Les relations entre les tables 94Tables 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 100La mise en uvre pratique dune base de donnes relationnelle 100

La structure dun SGBDR et ses diffrents objets 101Les phases de la programmation dune base de donnesrelationnelle 103

Synthse : base de donnes relationnelle et mthode de travailavec un SGBDR 105

6. LAMODLISATIONDUNEBASEDEDONNES

RELATIONNELLE .........................................................107Prsentation de notre cas pratique de base de donnesrelationnelle 108Btir 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 111Caractriser les lments de la phrase cl 111viter les principaux piges du schma thorique 112

Ne pas crer dlment inutile 112Ne pas crer un schma thorique en boucle 114

Dduire le schma rel du schma thorique 114Dterminer dfinitivement les tables du schma rel 115Dterminer les champs et la cl primaire de chaque table 117tablir 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 121tape 3 : saisir les donnes 121tape 4 : programmer formulaires, requteset tats/rapports 122

Synthse : modlisation et mthode de programmation dunSGBDR 122

7. OPRATIONSESSENTIELLESSURUNEBASEDEDONNES ........................................125Crer une nouvelle base de donnes relationnelle 126Ouvrir une base de donnes relationnelle existante 128

Autoriser louverture de la base pour une session seulementsous Access 2007 130Autoriser dfinitivement louverture de toutes les bases dundossier sous Access 2007 131

Utiliser lcran daccueil dune base de donnes relationnelle 133

7/23/2019 46289422 Excel Access

15/368

7/23/2019 46289422 Excel Access

16/368

DExcelAccess

XIV

Saisir un formulaire ou directement dans la table 225Saisir des donnes avec un formulaire 225Saisir des donnes directement dans une table 226Modifier et supprimer des donnes 227

viter les difficults lies la saisie de donnes 228Il faut respecter les proprits du champ 228Il faut respecter le principe dintgrit rfrentielle 229

Synthse : les formulaires et la saisie des donnes 230

10. EXPLOITERLESDONNESAVECUNEREQUTESIMPLE .. 233

Quest-ce quune requte ? 234Plusieurs types de requtes 235Rflexions pralables la cration dune requte 236

Choisir les champs participant une requte 236Choisir le mode de cration de la requte 237

Crer une requte simple 237Crer une requte avec lassistant Access 238

Slectionner les champs de la requte (tape 1 de

lassistant) 238Choisir le type de requte (tape 2 de lassistant) 239Enregistrer la requte (tape 3 de lassistant) 240

Crer une requte en mode Cration/bauche 241Crer une nouvelle requte en mode Cration/bauche 242Insrer les tables participant la requte 242Insrer les champs participant la requte 243

Comprendre les rsultats dune requte en mode Contenu 244

Sexercer crer des requtes 246Faciliter la saisie des donnes avec une requte sous Access 248

Crer la requte source 248Crer le formulaire bas sur la requte 249

Synthse : exploiter les donnes avec une requte simple 252

11. ISOLERDESENREGISTREMENTSPARTICULIERSDANSUNEBASEDEDONNES .............................................. 255

Modes cration/bauche, assistant et SQL : sorienter 256Raliser des slections denregistrements 257

Crer une requte slection 257Crer une requte slection paramtre 258Combiner les critres de slection 260

Utiliser les critres de slection volus 262Identifier des enregistrements sans correspondance 262

Crer la requte de non-correspondance avec lassistant Access 263

Visualiser la requte de non-correspondance 267Le mode Cration de la requte de non-correspondance 268

Le mode SQL de la requte de non-correspondance 268Crer la requte de non-correspondance en mode SQL avecBase 270

Crer le code SQL de requte de non-correspondance 270Crer la requte de non-correspondance en mode SQL 271

Identifier les doublons dans une table 271Crer une requte de recherche de doublons avec lassistantAccess 273

Modifier une requte existante 275Crer une requte de recherche de doublons en mode SQL 278Synthse : rechercher des enregistrements particuliers dans unebase de donnes 279

12. EFFECTUERDESCALCULSETDESSYNTHSESAVECUNEREQUTE ....................................................283Prendre en compte une modification du cahier des charges 284

Identifier les modifications de la base suite au nouveau cahierdes charges 285Modifier la base de notre cas pratique 287

Crer des champs calculs 291Connatre la syntaxe dun champ calcul dans une requte 291Effectuer un calcul arithmtique 292Manipuler les chanes de caractres avec Access 297Effectuer un test conditionnel avec Access 298

Raliser des synthses avec une requte 301Crer une requte de synthse avec lassistant Access 301Crer une requte de synthse en mode Cration/bauche 303

Organiser son travail avec les requtes 304Grer lajout et la suppression de donnes 305Crer une unique requte avec tous les calculs 306

Synthse : effectuer des calculs et des synthses avec les requtes 307

13. DITERTABLESETREQUTESAVECDESTATS/RAPPORTSPROFESSIONNELS.......................................................309Crer un tat/rapport en trente secondes 311

Crer un tat simple avec lassistant Access 311Crer un rapport simple avec lassistant Base 316Sentraner la cration dtats/rapports 319

Crer un tat de synthse avec Access 319Dterminer les champs participant ltat 320

7/23/2019 46289422 Excel Access

17/368

Tabledesmatires

XV

Dterminer le niveau de regroupement 321Paramtrer le tri et les synthses effectuer et terminerlassistant 322Lanalyse critique de ltat obtenu 324

Modifier un tat existant avec Access 325Anatomie dun tat en mode Cration 326Manipuler les contrles 327

Slectionner un contrle 327Modifier une tiquette ou un contrle 327Dplacer un contrle 328

Modifier la taille dun contrle 329Supprimer un contrle inutile 329Arer la prsentation de ltat 330

Synthse : les tats et les rapports 331

A. COMPLMENTSSURLAMODLISATION ....................... 333Le schma thorique de lcole de parapente 334Exercice sur les couples de nombres cls 336

Prcisions concernant la boucle dans le schma thorique delcole de parapente 338

Le schma rel de lcole de parapente 339

B. DROITSETOBLIGATIONSENMATIREDEBASEDEDONNES341Vos obligations en tant que gestionnaire de base de donnes 341

La notion de donnes caractre personnel 342Dans quel cas faut-il dclarer un fichier de donnespersonnelles auprs de la CNIL ? 342

Vos droits en tant que citoyen face lutilisation de vos donnes

personnelles 343Le droit linformation 344Le droit dopposition 344Le droit daccs 344Le droit de rectification 345

Exercez vos droits 345

INDEX ......................................................................347

7/23/2019 46289422 Excel Access

18/368

chapitre1

7/23/2019 46289422 Excel Access

19/368

Premiers pasvers les bases de donnes

Grer une base de donnes nimpose pas ncessairementdutiliser un logiciel complexe comme Access ou Base. partir dun exemple trs simple, nous allons dcouvririci les avantages dcisifs des bases de donnes simplifiesque sont les listes de donnes sous tableur.

SOMMAIRE

B Le traitement de texte :une solution inadapte

B Le tableur et ses filtres :une solution plus performante

B Les listes de donnes soustableur : un pas vers les basesde donnes

MOTS-CLS

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

7/23/2019 46289422 Excel Access

20/368

DExcelAccess

2

Grer une base de donnes noblige pas ncessairement dutiliser un logi-ciel spcifique et complexe tel quAccess ou Base. Les tableurs Excel etCalc possdent en effet le mode Liste de donnes permettant, avec unminimum dinvestissement en temps, de crer et dexploiter une base dedonnes simple.

Lobjet de ce chapitre est de prsenter globalement ce mode, largementmconnu et donc sous-employ par la majorit des utilisateurs. Il sarti-cule autour de deux comparaisons, la premire entre traitement de texteet liste de donnes, la seconde entre utilisation classique du tableur etliste de donnes.

Un traitement de texte ne sait pas grerune base de donnes, mme trs simpleDans la palette des outils les plus utiliss en bureautique figure en tte deliste le traitement de texte. Nous allons voir ici pourquoi, mme pour un

problme trs simple, il ne peut apporter de solution efficace, et com-ment la liste de donnes sous tableur fait bien mieux en termes de facilitet de performances.

Rien ne vaut un exemple concret : dans une cole de commerce, nousdevons organiser la prsence des tudiants au cours de bases de donnes,car on sy bouscule. Huit dates sont programmes, chacune voyantchacun des quatre groupes dtudiants suivre une sance d1h30 dans laseule salle suffisamment spacieuse pour les accueillir tous.

Nous devons ainsi grer trois informations pour convoquer les bons tu-diants 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 detraitement de texte. Le premier rflexe (et pas le meilleur, nous verrons

pourquoi) peut donc tre de prendre Word ou Writer et de construire untableau 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 cestitres 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

Figure 11Un planning sous traitement de texte, trs

simple mais aux possibilits bien limites

7/23/2019 46289422 Excel Access

21/368

1Premierspasvers

lesbasesdedonnes

3

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 defaire 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 pour-rions le trier par date ou par crneau : Sous Word 2007, dans longlet Outils de tableau Disposition, cliquez

sur le bouton Trierdu groupe Donnes. Sous Word 2003, dans le menu Tableau, utilisez le bouton Trier.

Sous Writer, cliquez sur le bouton Trierdu menu Tableau.Voil, cest peu prs tout et ce nest pas grand chose... Imaginons parexemple que nous voulions distribuer tous les tudiants leur planninget uniquement leur planning. Tous les groupes tant prsents sur lemme tableau, et les traitements de texte ne sachant pas masquer tem-porairement certaines lignes, on ne pourra queffectuer des copier collerau sens premier du terme, cest--dire avec des ciseaux et de la colle...

Pas trs performant, non ? Ce problme se posera de la mme faonquand lintervenant du 17 novembre nous demandera son planning (etuniquement le sien).

Bref, notre tableau sous traitement de texte ne nous rendra gure de services.

JAIUNEQUESTION 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 portetrs bien. Alors ?Si cette faon de procder vous convient, continuez sans arrire pense ; vous trouverezmme dexcellents ouvrages qui vous y aideront. Ceci dit : Le module de mailing de Word a t dvelopp pour ceux qui ne souhaitent pas sini-

tier aux bases de donnes. Le fait que vous lisiez ces lignes prouve que vous nenfaites plus partie.

Les fonctions de mailing de Word et Writer restent bien au dessous, tant en ergo-nomie quen performance, de ce que vous pourrez faire avec Excel ou Access la finde 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 sup-rieures la moyenne des ventes, les employs de tel ge, etc.).

7/23/2019 46289422 Excel Access

22/368

DExcelAccess

4

Le tableur et ses filtres : une solution plusperformanteUn tableau, sous traitement de texte comme sous tableur, comporte deslignes et des colonnes. Rien nempche donc de transformer notre plan-ning de la figure 11 pour obtenir son quivalent tableur de la figure 12(ici, sous Excel 2007, les prsentations Excel 2003 et Calc sont quasi-ment 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 decolonnes : elles vont nous permettre deffectuer des slections denregis-trements particuliers, par exemple pour isoler le planning du groupe 2 oudu 17 novembre.

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

Appliquer un filtre pour slectionner desenregistrements particuliersLa fonction Filtre des tableurs (Excel ou Calc) permet disoler trs sim-plement certaines lignes (enregistrements) selon des critres entirementpersonnalisables.

Nous souhaitons par exemple isoler les lignes relatives au groupe 2. Cli-quons sur la flche ct de len-tte Groupes, en A1. Une liste drou-lante apparat (figure 13).

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 quavecExcel 2007 (dans longletAccueil, groupe Style, cliquez sur loutil Mettre sous formede tableau).

Excel 2003 : ces mmes flches sobtiennent par le menu Donnes, option Filtres puisFiltres automatiques. Pour les alternances de couleur, par contre, il faudra vousdbrouiller 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 12Un planning Excel bien plus performant

7/23/2019 46289422 Excel Access

23/368

1Premierspasvers

lesbasesdedonnes

5

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 Filtrestextuels, o nous retrouvons la liste de nos quatre groupes. Ils sont touscochs, ce qui signifie quils sont tous actuellement affichs. Si nous dco-chons les groupes 1, 3 et 4 pour ne conserver coch que le 2 et que nousvalidons, nous obtenons laffichage du groupe 2 uniquement (figure 14).

Observons laffichage de la figure 14 :

La flche ct de len-tte Groupes sest orne dune sorte d enton-noir. 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 deligne lextrme gauche de chaque ligne : les lignes 2 9 ont tempo-rairement disparu. De plus, Excel affiche ces numros de ligne enbleu pour bien nous rappeler quun filtre est actuellement en cours.

Figure 13Les possibilits de filtrepour la colonne (champ) Groupes

Figure 14Lapplication dun filtrenaffiche que certaines lignes.

EXCEL 2003 ET CALCAppliquer un filtre

Les botes de dialogue Excel 2003 et Calc diffrentlgrement de la figure 13, mais offrent avec uneergonomie semblable les mmes fonctionnalits,voir page 7.

EXCEL 2003 ET CALCReprer quun filtre est actif

Lentonnoir prsent sur la flche de la liste drou-lante dun filtre nest prsent que sous Excel 2007.Pour Excel 2003 et Calc, la flche change de cou-leur et devient bleue.

7/23/2019 46289422 Excel Access

24/368

DExcelAccess

6

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 parmgarde les deux filtres ensemble.

Nous voulons par exemple donner son planning notre intervenant du17 novembre. Il suffit deffectuer exactement le mme type doprationque prcdemment, cette fois-ci sur la colonne Crneau, sans oublierdannuler ventuellement tout filtre dj actif. Dans notre exercice, ilfaudra :1 Commencer par annuler un filtre actif. Sous Excel 2007, cliquez sur

la liste droulante du filtre appliqu la colonne, slectionnezloption Slectionner toutdans la zone au dessous de Filtres textuels ;lentonnoir correspondant disparat et les lignes masques rapparais-sent pour afficher tous les champs. Pour Excel 2003 et Calc, cochezrespectivement les options Toutou Tous.

2 Activer la slection denregistrement sur le champ Dates. Pour cela,cliquez sur la liste droulante du filtre Dates. Excel 2007 a prvu unregroupement par mois comme le montre la figure 15, mais cela necorrespond pas ce que nous cherchons faire. Il suffit alors douvrirle mois de novembre en cliquant sur la croix ct du mois pourslectionner ce fameux 17 novembre (figure 16), puis de validerpour obtenir ce que nous souhaitions (figure 17).

Figure 15Les filtres dExcel 2007 regroupent les dates par mois

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

7/23/2019 46289422 Excel Access

25/368

1Premierspasverslesbasesdedonnes

7

Remarquez sur la figure 17 que seules les lignes 5, 13, 21 et 29, corres-pondant la date du 17 octobre, sont affiches, et que les autres sonttemporairement masques. Cest le principe mme du filtre qui se con-tente de masquer sans jamais supprimer.

Dans cette manipulation, nous avons bien pris soin dannuler le filtre surla colonne Groupe avant dappliquer celui sur la Date. Que ce serait-ilpass 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 aurionsobtenu pour ce 17 novembre et pour le groupe 2 (figure 18) : la ligneunique correspondant au groupe 2 et au 17 novembre.

Remarquez galement sur cette figure 18 que licne de filtre (lenton-

noir) apparat la fois sur les colonnes Groupes et Dates, preuve que desfiltres sont appliqus sur ces deux colonnes.

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

Figure 17

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

EXCEL 2003 ET CALC Appliquer un filtre sur des dates

Excel 2003 et Calc noffrent pas la possibilit, comme Excel 2007, de slectionner un moisparticulier. Il sagit alors simplement de slectionner dans la liste de choix du filtre correspon-dant la date souhaite (figures 19 et 110).

Figure 19 Le choix dun critrede filtre de date sous Excel 2003

Figure 110 Le choix dun critre defiltre de date sous Calc

Figure 18 Deux filtres appliqussimultanment se cumulent.
http://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

26/368

DExcelAccess

8

Les listes de donnes sous tableur : un pasvers les bases de donnesLe mode Liste de donnes des tableurs est celui que nous venons dutiliserdans lexercice prcdent, un peu comme monsieur Jourdain faisait de laprose, sans le savoir. Il soppose dans sa construction et dans son utilisationau mode dutilisation traditionnel de ces logiciels, avec des feuilles de cal-culs (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 lieudclater linformation dans diffrents feuilles et classeurs, de toutregrouper sur une seule feuille en un seul tableau.

Voyons les avantages de ces listes de donnes par rapport lutilisationclassique du tableur. Il nest pas ncessaire dexprimenter les manipula-tions prsentes dans cette section, elles ne vous sont proposes qu titredillustration. Leur tude dtaille au travers dun cas pratique feralobjet des chapitres 2 4.

Lexpos de notre cas pratique dtude des listes dedonnesImaginons que nous venons dintgrer en tant que contrleur de gestioncette 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 jeunesamateurs de grimpe sportive, en indoor et en comptition. Son fonda-teur, Eddy K., fou de prises et de grattons, est un jeune patron fonceuret, surtout, a ce quil est convenu dappeler le flair des affaires et le sensdu business. Comme il a eu le talent de sassocier avec Karim L., ing-nieur tonnamment djant mais totalement gnial, leur petite structurea trs vite su profiter du boom de cette spcialit sportive en alliant pro-duits innovants et image forte. Sa principale richesse est linvention de

Karim L. : une fibre textile synthtique qui combine excellence des qua-lits physiques (rsistance et lasticit) et capacit tre teinte de millestonnantes 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 de10 millions deuros cette anne, et envisage de souvrir lEurope lanprochain, particulirement dans les pays nordiques.

RAPPEL Feuilles de calcul et classeurs

Un classeur Excel ou Calc permet, comme un clas-seur rel, de regrouper des feuilles de calcul trai-tant du mme sujet pour un classement plus clairet un accs plus rapide linformation. On peutpar exemple regrouper dans un mme classeur lesinformations 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 oumme dautres classeurs. Ces manipulations parailleurs trs pratiques et performantes, un peu troplongues pour tre dveloppes ici, sont dtaillesdans nombres douvrages sur les tableurs, dont PCtrucs, du mme auteur, aux ditions Leduc.S oules ouvrages consacrs Excel et Calc dans la col-lection TSoft aux ditions Eyrolles.

s

7/23/2019 46289422 Excel Access

27/368

1Premierspasverslesbasesdedonnes

9

Notre mission

Ce tableau est-il idyllique ? Eh non... Comme bien souvent en pareil

cas, lintendance na pas aussi bien suivi. Notamment en matire de suivides performances commerciales de ses reprsentants. Conscient delenjeu, Eddy K. nous a confi, comme premire tche, la mise en placedes 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.

On na pas de temps perdre !

Notre cahier des charges simplifi

Tout travail sur base de donnes, aussi simple soit-il, ncessite systmati-quement une rflexion pralable. Autant sous traitement de texte ousous tableur on peut toujours au dernier moment ajouter des paragraphes

ou dplacer des colonnes, autant en matire de bases de donnes ce nesera pas toujours possible. En clair, il faut dfinitivement perdre sesrflexes comme je commence programmer comme a et puis on verrabien plus tard comment a volue je pourrais toujours rajouter une oudeux colonnes on va pas se prendre la tte . Tout simplement parce quedans certains cas, il sera impossible de faire voluer dans le bon sens untravail mal n et quil faudra alors tout reprendre zro. Prendre le tempsde rflchir avant et de poser sur papier clairement le contexte, les con-

traintes, les informations et le but recherch est fondamental, le tempsqui 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 ventuellenouvelle activit dexport. Nous commercialisons trois gammes de produits : les cordes

dattache, les sangles et les mousquetons. Le dveloppement dunegamme 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 peutplus clair. Il veut, chaque lundi, le chiffre daffaires de chaque repr-

s
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

28/368

DExcelAccess

10

sentant pour chaque zone et pour chaque gamme de produits,priode par priode. Pour le reste, il ne veut rien entendre de noscontraintes 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 confierpar la suite.

Une dfinition dune base de donnesNotre cas pratique nous amne runir un ensemble dinformations (Repr-sentant, Zone, Produit et Vente) de faon suivre nos statistiques commer-ciales. Nous pouvons donc avancer la dfinition suivante : une base dedonnes 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, notredfinition est valide. Par exemple, pour un systme de gestion comptable,elle peut se personnaliser ainsi : ensemble des informations relatives auxclients, aux produits, aux commandes et aux livraisons, organises de faon pouvoir suivre, par client, par commande et par livraison, les facturescorrespondantes et les rglements effectus . Rajouter des fonctionnalitscomplmentaires, par exemple suivre les impays et les relances, pourraamener 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 neconvient pasDans notre cas pratique, le premier rflexe que nous pourrions avoir (vrai dire celui quauraient 99 % des utilisateurs de tableur) serait douvrirExcel ou Calc et de nous lancer dans la cration de classeurs avec des

tableaux complexes remplis de formules pour suivre nos statistiques.

es

7/23/2019 46289422 Excel Access

29/368

1Premierspasver

slesbasesdedonne

11

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).

Cette solution tableur feuille-classeur oblige disperser linformationdans de multiples feuilles de calcul qui, mme si elles sont toutes basessur le mme schma, sont toutes indpendantes les unes des autres. Ceciprsente au moins les deux dfauts rdhibitoires suivants :

Elle oblige un choix dorganisation des donnes qui ne pourra tremodifi.

Elle ne pourra pas suivre lvolution de la structure et des besoins denotre entreprise.

Pourquoi ?

Le choix de lorganisation feuille-classeur idale

Dans notre tude de cas, nous avons prcdemment choisi lorganisationde la figure 111. bien y rflchir, il existe beaucoup dautres possibi-lits dorganisation de nos donnes, par exemple celle de la figure 113,avec un classeur par reprsentant, une feuille par zone, les priodes et lesproduits en lignes et colonnes. Chaque solution potentielle privilgie unpoint 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.

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

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

Figure 113 Une autre organisationdes donnes par multiplication

de feuilles identiques

ss
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

30/368

DExcelAcce

12

Par contre, si nous prfrons visualiser les ventes par produit etpriode pour un reprsentant et une zone donns, il vaudra mieuxopter pour la figure 113.

Nous en sommes donc rduits un choix dans lequel, objectivement,aucune solution ne simpose par rapport une autre, dautant que nousne serons jamais certains davoir bien compris ce quEddy avaitdemand, ni mme quil ne changera pas davis...

Aucune solution feuille-classeur ne saura voluer

La vie de lentreprise nest pas un long fleuve tranquille, et la vrit dun

jour nest pas ncessairement celle du lendemain. Pour ce qui nous con-cerne, il est tout--fait possible par exemple que la dimension Produitprenne progressivement un aspect prpondrant, auquel cas aucune denos deux possibilits de dpart ne conviendra plus.

Et quand bien mme ce cas ne se produirait pas (ce qui est bien impro-bable, convenez-en), larrive prvisible de nouveaux produits ( com-mencer par les baudriers) va nous obliger reprendre tous nos tableaux.

Trs concrtement, pour ajouter un nouveau produit dans notre baseavec une solution feuille-classeur selon le schma de la figure 111(avec les produits en ligne), il va nous falloir, pour chacune des feuilles dechaque classeur, insrer la ligne Baudriers et reprendre toutes les for-mules de totalisation. Avec quatre reprsentants (quatre classeurs) et unhistorique 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 tableur

Les donnes figurant dans les lignes et les colonnes dun tableau sont les seules treexploitables 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. Etcest encore plus vrai si vous les stockez dans dautres classeurs, ou mme dans dautresdossiers ! Donc autant viter tant que possible de disperser ses informations, par exempleen les groupant dans le mme tableau...

TECHNIQUE Intervertir lignes et colonnesdun tableau Excel ou Calc

Dans notre cas pratique, si, pour des raisons deprsentation, nous dcidons dintervertir lignes etcolonnes, 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 loptionTranspos.

Attention quand mme, cette manipulationdplace contenus et format des cellules. Il vous

faudra donc certainement reparamtrer intgrale-ment formats et encadrements qui auront tchambouls par la transposition

es
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

31/368

1Premierspasver

slesbasesdedonn

13

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 soustableur, dont les principaux avantages sont la souplesse, lvolutivit etles performances. Les quelques exemples qui suivent vont certainementvous en convaincre.

La liste de donnes est souple et volutive

Avec les listes de donnes, nous naurons plus besoin de nous poser laquestion : quest-ce que je vais mettre en ligne, en colonne, en

feuille... . Au contraire, nous allons entrer linformation dans un seultableau comme nous le verrons partir du chapitre suivant et, par uncoup de baguette magique, nous pourrons la prsenter exactementcomme nous le souhaitons.

Nous pourrons par exemple fournir Eddy ltat suivant (figure 114) quisynthtise 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.

La liste de donnes sadapte parfaitement au changement

Imaginons maintenant quun nouveau reprsentant intgre lentreprise.Avec une solution feuille-classeur traditionnelle, il y aurait plus oumoins de travail selon loption choisie :

Figure 114 Les ventes par produit et zone,

toutes priodes et reprsentants confondus

Figure 115Zoom par reprsentant et produit pour la zone Nord. Seule Sophiea vendu des Cordes. Nous retrouvons bien les 3 930 de cordespour le Nord de la figure 114.

OBSERVONS Le filtre de la figure 115

En haut de cet tat, ct de lintitul Nord dans le champ Zone, apparat notre entonnoir defiltre. Il suffira de cliquer dessus pour voir apparatre les trois zones et de slectionner celle deson 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

cess
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

32/368

DExcelAcc

14

Avec une organisation du type de la figure 111 ou 113 (un classeurpar reprsentant), il suffirait alors de rajouter un autre classeur pource reprsentant. On se serait alors relativement bien sorti daffaire.

Encore que... Imaginons que dans ce cas nous ayons cr un classeurde consolidation des ventes, tous reprsentants confondus. Lajoutdun nouveau classeur correspondant ce nouveau reprsentant nousaurait oblig modifier toutes les formules de consolidation en yintgrant le nouveau reprsentant

Si, par contre, nous avions choisi dintgrer la dimension Reprsen-tant en ligne ou en colonne de nos feuilles de calcul, la seule solutionaurait alors t de prendre notre courage deux mains et dinsrer

dans tous les classeurs et dans toutes leurs feuilles soit une ligne, soitune colonne pour le nouveau reprsentant... Bien videmment, ilaurait galement fallu reprendre toutes les formules...

Ces deux solutions ne sont pas satisfaisantes : elles sont toutes deux trslourdes et difficilement adaptatives.

Par contre, en mode liste de donnes, il suffit de saisir le nom du nou-veau reprsentant pour quautomatiquement il soit pris en compte dans

les tats avec bien videmment toutes les formules mises jour. Lafigure 117 en fournit la preuve.

PRODUCTIVIT 30 secondes maximumpour tous ces tats

Cerise sur le gteau, les listes de donnes segrent trs simplement et trs rapidement. Avecune conomie de temps et de moyens remar-quable. titre dillustration, tous les tats desfigures 114, 115, 116 et 117 ont t pro-

gramms en 30 secondes chrono. Qui dit mieux ?

Figure 117Lintgration dun nouvel lment est automa-

tique en mode liste de donnes.
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

33/368

7/23/2019 46289422 Excel Access

34/368

chapitre2

7/23/2019 46289422 Excel Access

35/368

Btir une liste de donnes efficacesous tableur : structure, rgles devalidation et mise en forme

Nous allons ici apprendre construire une liste de donnesscurise sous tableur et comment une mise en formespcifique permet une analyse efficace.

SOMMAIRE

B Dfinitions pralables

B Bien structurerune liste de donnes

B Les rgles de validationdune liste de donnes

B Les mise en formeconditionnelle de listesde donnes

MOTS-CLS

B Liste de donneset base de donnes

B Champ et enregistrementB Les cinq rgles

dune liste de donnesB Rgles de validation,

message daide la saisieet message derreur

BMise en forme conditionnelle :isoler un lment, visualiserles 10 premiers lments,barre de donnes

B Doublons

Access
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

36/368

DExcelA

18

Ce chapitre dbute vritablement ltude des bases de donnes simpli-fies que sont les listes de donnes sous tableur. Elle se poursuivra aucours des chapitres 3 et 4.

Aprs quelques prcisions terminologiques, nous dcouvrirons dans cechapitre les cinq rgles rgissant la structure de ces listes de donnes. Nousverrons ensuite comment le paramtrage de rgles de validation permetdassurer la conformit des donnes, simplifie le travail des oprateurs etacclre les traitements. Enfin, nous apprendrons isoler par quelquesastuces visuelles et graphiques certains enregistrements parmi dautres.

Dfinitions pralablesLes bases de donnes utilisent un vocabulaire simple mais spcifique. Ilest 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. Dansle chapitre prcdent, nous avons dfini une base de donnes comme unensemble dinformations organises dans un but dfini . La complexit delorganisation des donnes traiter va dterminer si nous avons affaire unevritable base de donnes ou une simple liste de donnes.

Nous verrons dans les chapitres consacrs Access et Base, partir de lapage 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 acti-vits encadres par des animateurs ; la base de donnes correspondantedevra au moins comprendre trois lments individualiss : Membres,Activits et Animateurs.

Une liste de donnes est galement une base de donnes, mais une basede donnes simplifie en ce sens quelle nest compose que dun seul l-ment, en loccurrence un tableau, comme notre planning du chapitre 1ou notre cas pratique de matriel dalpinisme. Les tableurs comme Excelou Calc possdent des fonctions intgres trs efficaces ddies ceslistes de donnes. Cest leur tude que nous dmarrons ici.

Les champs : la structureLes champs dune base de donnes sont en fait les informations qui yseront listes. Ils dfinissent en quelque sorte la structure de la base. Si

forme
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

37/368

2Btirunelistededonnesefficacesoustableur:structure,

rglesdevalid

ationetmiseen

19

on reprsente la base de donnes sous la forme dun tableau, les champssont en gnral lists en premire ligne, en en-tte de colonne. Parexemple, 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, onpeut en ajouter ou en supprimer volont. Toutefois, une fois que leschamps ncessaires ont t crs, en dautres termes une fois que lastructure de la base est arrte, le nombre et le nom des champs nontplus tre modifis. Dans notre planning dcole de commerce, les troischamps Groupe, Date et Crneau sont ncessaires et suffisants, inutileden ajouter mais interdiction den enlever.

Les enregistrements : le contenuLes champs dune base de donnes dfinissent sa structure, les enregis-trements son contenu. Ainsi, notre exemple de planning du chapitre 1comprenait trente-deux enregistrements correspondant toutes les com-binaisons groupe/date/crneau. Si nous considrons un annuaire souslangle base de donnes, les diffrents abonns au tlphone en consti-

tuent les enregistrements.Dans une base, le nombre denregistrements (tout comme le nombre dechamps) nest heureusement pas limit, cest mme tout lintrt. Parcontre, autant le nombre de champs est stable une fois quil est arrt,autant le nombre denregistrements est lui constamment variable. Parexemple, la taille de lannuaire des abonns au tlphone augmente enfonction des nouveaux abonns et diminue en fonction des rsiliations.

Bien structurer une liste de donnesNous reprenons ici ltude du cas pratique de liste de donnes de matrieldalpinisme. La figure 21 reprsente la liste de donnes correspondante.

Cette liste de donnes se prsente bien comme un tableau classique, avecles 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 nomsde champs sont les slecteurs de filtres, ceux que nous avons utiliss auchapitre 1 pour afficher ou masquer certains enregistrements.

La structure de cette liste de donnes, simplissime, rpond cinq rglesncessaires et suffisantes. Dtaillons-les.

EXEMPLES Dautres champspour dautres bases de donnes

Dans le cas dun annuaire, on peut imaginer que les

champs de la base de donnes seront le nom delabonn, sa ville, son adresse et videmment sonnumro de tlphone, et pourquoi pas son e-mail.Pour un fichier de prospects commerciaux, le nomde lentreprise, le nom du contact, ses coordon-nes, les produits qui peuvent lintresser, les diff-rents contacts que nous avons eu avec lui, peuventconstituer les diffrents champs de la base. Etpourquoi pas la date laquelle nous devons lerecontacter.

La structure des champs dune base de donnes estdonc fonction des buts que lon assigne cette base.

OUPS Les slecteurs de filtrenapparaissent pas

Ne vous inquitez pas si les slecteurs de filtrenapparaissent pas automatiquement sur votrecran, tout dpend du logiciel que vous utilisez.Leur fonctionnement est dtaill au chapitre 3.

TLCHARGER Cette liste de donnessous tableur est disponible

en tlchargement

Lidal serait que vous testiez toutes les manipula-tions des chapitres 2 4. Pour vous faciliter latche, la liste de donnes de notre exemple est dis-ponible en tlchargement sur la fiche ouvrage surle site www.editions-eyrolles.com. Mais vouspouvez aussi la saisir manuellement sur votretableur favori, Excel ou Calc. Voire mme crerdirectement votre propre liste de donnes.

Access

7/23/2019 46289422 Excel Access

38/368

DExcelA

20

Rgle 1 : les noms de champ en premire ligneNous avions dtermin quatre champs grer : le Reprsentant, laZone, le Produit et la Priode. Nous les retrouvons en premire ligne dela 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 desventes ralises entre deux dates.

Nous remarquons galement deux colonnes supplmentaires, correspon-dant 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 sixchamps, 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 quedes caractres alphabtiques.

Figure 21La liste de donnes de notre cas pratique :

un tableau classique, les Champs en en-ttede colonne et les Enregistrements

les uns au-dessous des autres

nforme

7/23/2019 46289422 Excel Access

39/368

2Btirunelistededonnesefficacesoustableur:structure,

rglesdevalidationetmiseen

21

Zone : ce champ naccepte pour le moment que les valeurs Nord , Sud ou RP pour Rgion parisienne ; il ne contient donc aussique des caractres alphabtiques.

Produit : ce champ naccepte pour le moment que les valeurs Cordes , Sangles ou Mousquetons , et ne contient donc ga-lement 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 lensemblede nos donnes selon lordre chronologique de saisie. Mais il va galementnous 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 par-lera de la vente n 8. Cest plus simple, plus prcis et plus rapide.

Rgle 2 : les enregistrements les uns au-dessous desautresIl suffit de saisir, partir de la ligne 2, les enregistrements de la liste dedonnes, cest--dire son contenu, les uns au-dessous des autres, sans seposer de question.

Actuellement, notre liste contient trente enregistrements, de la ligne 2 la ligne 31. Nous pourrions en ajouter satit. Si les ventes augmen-taient, tant en nombre quen volume, il suffirait de les saisir la suite desautres, 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 pourraaccepter toutes les nouvelles ventes de notre socit.

PRODUCTIVIT Numroter / renumroter les lignes dune liste de donnes

Pour 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.

INFO Nombre de lignes disponiblessur une feuille de calcul

Une feuille de calcul peut comporter jusqu1 048 576 lignes pour Excel 2007, et 65 536 seule-ment pour Excel 2003 et Calc. Cela dit, les ali-menter toutes risquera de poser de srieuxproblmes de temps de recalcul, sans compter les

maux de tte...

JEMESUISTROMP Jai fait une erreur de saisiedans la liste de donnes...

Si jamais vous faites une erreur de saisie dans uneliste de donnes, il suffit de la rectifier commevous le feriez dans une feuille de calcul classique.

Si jamais vous oubliez de saisir une vente, vouspouvez soit : simplement lajouter en bas de la liste de don-

nes, avec linconvnient que son numro chro-nologique ne correspondra pas la ralit ;

insrer une ligne au bon endroit, avec lavan-tage que le numro sera correct mais aveclinconvnient quil faudra alors renumroterles lignes, comme expliqu dans lapart Pro-ductivit ci-contre.

Access
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

40/368

DExcel

22

Rgle 3 : ni ligne ni colonne videSi, par mgarde, notre liste de donnes contient soit une ligne soit unecolonne intgralement vierge, le tableur risque de ne travailler que sur

une partie de la liste de donnes et doublier en quelque sorte tout ce quiaura t saisi de lautre ct de cette dangereuse ligne ou colonne vierge.

Prenons lexemple ( ne pas suivre) de la figure 22. Cette liste de don-nes comprend une ligne vide, la 18. Quand nous utiliserons les fonc-tions de liste de donnes, Excel ou Calc risquent de ne travailler que surune partie de la liste, soit de A1 F17, soit de A19 F28, en omettantdonc une partie de la liste.

TRUC Utiliser et faciliter le mode de saisie assiste des tableurs

Quand 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 furet mesure de notre saisie, des entres dj effectues.Concrtement, si nous nous plaons la cellule D32 du tableau de la figure 21 et quenous saisissons S , le tableur va automatiquement nous proposer Sangles parceque 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 distinc-tion. Do perte de temps et gain dnervement. Sauf si nous choisissons la terminologieAttacheou Rappel simplement, auquel cas taper simplement A ou R suffira fairele distingo et acclrer sensiblement notre saisie !

Penser donc, quand cest possible, ne pas avoir dans le mme champ deux entres avecla mme initiale. La suggestion automatique des entres de la liste de donnes fonction-nera bien plus efficacement.

CONDITIONNEL Risque ?

Excel ou Calc risquent selon le cas de ne tra-

vailler que sur une partie de la liste... . Le terme risquent est un peu vague. Et tonnamentimpossible clarifier. Les cas de figure sont tropnombreux pour tre tous lists, dans certains cas ilny aura pas de problme et dans dautres il y enaura. Dans ces conditions, le principe de prcau-tion simpose : ninsrer ni ligne ni colonne viergedans une liste de donnes !

Figure 22Une liste de donnes non conforme

(avec une ligne vierge)

enforme
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

41/368

2Bt

irunelistededonnesefficacesou

stableur:structure,

rglesdevalidationetmisee

23

ceci il existe un remde imparable : crer une liste de donnes com-pacte, 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 ra-liser une mise en page sophistique de la liste de donnes. En effet, ellene sert qu collecter linformation, et jamais elle ne sera imprime entant que telle ; nous ne lutiliserons quau travers de sorties et de docu-ments comme ceux que nous avons dj vu et dautres que nous appren-drons crer. Contentons-nous simplement de faire quelque chose de

propre et de lisible, ce sera largement suffisant...

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 lemontant total des ventes. Et encore plus dinsrer des lignes entre les

ventes de chaque reprsentant pour en calculer le total. Les fonctions defiltres (page 50), de sous-total (page 60) et de tableau crois dynamique(page 71) le feront en deux temps trois mouvements et sans aucun risquederreur comme le montre la figure 23.

La liste de donnes ne contient donc que les donnes, sans aucune for-mule de totalisation.

EXCEL 2007 Des listes de donneslgamment quadrilles en un clic

Excel 2007 permet quand mme sans se fatiguer decrer des surlignages comme dans la figure 21. Leregard y suit plus facilement les lignes, inutile desen priver ! Dans longlet Accueil, groupe Style,cliquez sur Mettre sous forme de tableau.

Figure 23Une liste de donnes (brute et sans formule)permet de gnrer automatiquement toutessortes de tableaux danalyse.

Access
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

42/368

DExcel

24

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

Les rgles de validation dune liste dedonnesTout comme on ne remplit pas le rservoir dune voiture diesel avec delessence 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.Par exemple, dans notre exercice, nous savons quil nexiste que quatrereprsentants, Hlne, Sophie, Jean et Pierre. Faire une faute dortho-graphe la saisie sur un nom, par exemple Sofie ou mme Helene , sans accent, aura pour effet de crer un reprsentant suppl-mentaire quExcel et Calc traiteront comme un reprsentant partentire, diffrent, sans faire le lien avec Sophie ou Hlne. Ce seraencore pire si on saisit dans le champ Reprsentantle nom dun produit,ou encore si on saisit la date de la vente dans la colonne Montant. Danstous les cas, le rsultat sera inexploitable.

Lide gnrale des rgles de validation est de rendre impossible ces erreurs desaisie et donc de garantir la conformit des donnes avec ce qui est attenduafin dassurer le bon fonctionnement de la liste de donnes, de mnager lesnerfs des oprateurs de saisie (qui savent ds lors que toute erreur grossire desaisie est impossible) et donc au bout du compte de simplifier le travail.

Attention : les rgles de validation ninterdisent bien videmment que leserreurs de conformit de donnes par rapport des rgles. Elles vontinterdire par exemple de saisir un nombre la place dun texte, de saisirle nom dun reprsentant qui nexiste pas, etc. mais certainement pas leserreurs de saisie non logiques comme par exemple daffecter Pierre unevente de Jean. Do la ncessit, malgr la prsence de toutes les rglesde validation possibles et imaginables, de procder une contrle minu-tieux de lexactitude de la saisie (et non plus de leur simple cohrence) !

Grce aux rgles de validation, vous allez pouvoir dfinir, pour chaquechamp 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.

enforme

L d i l d d l d
http://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdfhttp://app/CahiersTitres.pdf

7/23/2019 46289422 Excel Access

43/368

2Bt

irunelistededonnesefficacesou

stableur:structure,

rglesdevalidationetmise

25

Le mode opratoire gnral pour accder au paramtrage des rgles devalidation 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 denregistre-ment 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 dumenu Donnes avec Excel 2003.

LoptionValidit

du menuDonnes

avec Calc.

Le paramtrage des rgles de validation proprement ditesCette section va nous guider dans la mise en uvre effective des rglesde validation dans notre liste de donnes. La figure 24 dtaille la botede dialogue de validation des donnes sous Excel 2003 et 2007. Lesmanipulations Calc sont trs proches.

Le tableau ci dessous reprend lessentiel des options proposes par Excel2007 :

TRUC Slectionner une colonnesauf son en-tte

Pour slectionner une colonne sans son en-tte, ilfaut cliquer sur len-tte de colonne (la lettreA, B,C...) puis dslectionner la premire cellule parCtrl + Clic.

MTHODOLOGIE Quand paramtrerles rgles de validation ?

Les rgles de validation dune liste de donnessous tableur, la diffrence dAccess ou de Base,ne sappliquent quaux nouvelles entres, et non

aux donnes dj prsentes dans la liste. Il estdonc ncessaire de les paramtrer ds le dbut deson travail, avant mme toute saisie.

Figure 24La bote de dialogue Validation des donnes.

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 Aucun contrle nest effectu. Option par dfaut.

Nombre entier * Limite la saisie un nombre entier (avec possibilit de dfinir des bornes).

Dcimal * Limite la saisie un nombre dcimal (avec possibilit de dfinir des bor-nes), sans toutefois pouvoir dfinir le nombre de dcimales autorises.

Liste * Limite la saisie aux valeurs dune liste.

elAccess

Tableau 2 1 Principales possibilits de contrle de validit des entres dans une liste de donnes (Excel) (suite)

7/23/2019 46289422 Excel Access

44/368

DExce

26

* Ces options sont dtailles ci-dessous.

Limiter les entres un nombre entier ou dcimal

Pour limiter la saisie dun champ des valeurs numriques, slectionnez la

colonne correspondante (sauf son en-tte) et appelez la bote de dialogueValidation des donnes comme dcrit ci-dessus, slectionnez longletOptions et paramtrez les critres souhaits. Le paramtrage de la bote dedialogue de la figure 25 permet par exemple de contrler lentre denombres entiers positifs infrieurs gal 12 000. Il est possible de choisirdautres conditions (suprieur, infrieur, gal, diffrent de ...).

Date Limite la saisie aux entres sous forme de date (JJ/MM/AA ou autre) avec

la possibilit de fixer des bornes.

Heure Limite la saisie aux entres sous forme dheure (MM:SS ou autre) avec lapossibilit de fixer des bornes.

Longueur du texte Permet de limiter la longueur dun champ texte pour viter les listes dedonnes trop volumineuses (limiter un champ Prnom 10 caractresinterdira de saisir Jean-Christophe).

Personnalis * Pour limiter les entres la valeur dune formule comme expos ci dessus.

Ignorer si vide Si coch, permet de ne pas appliquer la rgle de validation quand la cel-

lule est vide. Comme les rgles de validation sappliquent la colonneentire, il faut bien videmment cocher cette case systmatiquement(sinon toutes les cellules vides en attente de saisie seront rejetes).

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

Figure 25Seuls les entiers comprisentre 0 et 12 000 seront accepts.

eenforme

Limiter les entres aux donnes dune liste

7/23/2019 46289422 Excel Access

45/368

2Btirunelistededonnesefficacesou

stableur:structure,

rglesdevali

dationetmise

27

Limiter les entres aux donnes d une liste

Loption Liste de la liste droulante de la bote de dialogue Validation desdonnes va permettre de limiter les entres dun champ aux valeurs dune

liste cre manuellement. Par exemple, dans no

Documents

46289422 Excel Access