Organisation et gestion de vos données sous Excel

Stockez et organisez vos données sous Excel Une approche systématique, structurée et simplifiée

Publié par PolyKromy Consulting Inc., Vancouver, BC, Canada

PolyKromy Consulting Inc. a apporté la plus grande attention à la conception de ce livre afin de vous fournir une information complète et fiable. Cependant, PolyKromy Consulting Inc. n’assume de responsabilités, ni pour son utilisation, ni pour les contrefaçons de brevets ou atteintes aux droits de tierces personnes qui pourraient résulter de cette utilisation.

Copyright © 2013, PolyKromy Consulting Inc. Tous droits réservés

1e édition Gaëtan Mourmant www.XLerateur.com

Stockez et organisez vos données sous Excel Une approche systématique, structurée et simplifiée

Introduction

L’utilisation d’Excel – un tableur - comme outil de stockage et de gestion de données devrait être une aberration logique et théorique, et pourtant c’est une situation extrêmement fréquente dans la vie réelle… En fait, au travers de mon expérience, je me suis rendu compte que cette situation conduisait à plusieurs défis que j’adresse dans cet ouvrage. Rappelons qu’à l’origine, Excel n’était pas fait pour stocker des données, mais pour faire des calculs ! Cependant, les usages l’ont très rapidement transformé en outil de stockage de données (une ou plusieurs tables/listes/tableaux de données), et les récents développements d’Excel permettant plus d’un million de lignes ou encore l’utilisation de solutions Cloud Computing (Office 2013, Office 365), vont encore accélérer ce mouvement. J’en suis donc venu à considérer qu’Excel, dans de nombreuses situations, est un très bon outil de stockage de données, À CONDITION de respecter un certain nombre de règles, et dans les cas suivants : -

Nombre limité d’utilisateurs.

-

Nombre limité de tables et relativement faible complexité des relations entre ces tables.

Aussi, si vous voulez instaurer une paix durable entre le département des systèmes d’informations et les power-users ou développeurs Excel (vous ?), voici quelques règles de bon sens qui sont très utiles : -

Présenter l’application Excel comme une solution temporaire, une sorte de prototype, qui sera développé en produit complet lorsque les ressources seront disponibles.

-

Faire comprendre que les tables sont structurées suivant les règles que vous allez lire, et que de fait, l’intégration dans un système plus complet ou une solution de plus grande échelle sera grandement facilitée.

-

Reconnaître les limites d’Excel indiquées plus haut et donc reconnaître la pertinence d’une solution de plus long terme et à plus grande échelle (Access, Oracle, MySQL, etc.).

Je présente dans cet ouvrage une sélection de règles d’or et de fonctionnalités pour stocker, organiser et gérer vos données sous Excel. Le cours se décompose en deux parties. Dans la première partie, nous étudions les règles de base indispensables à connaître pour bien gérer et organiser ses données sous Excel. Dans la deuxième partie, nous nous basons sur ces notions pour construire un outil de facturation. L’outil est suffisamment simple pour pouvoir être facilement adapté à vos besoins. Nous traitons de la problématique des formulaires de saisie associés à une table ou une application. Les fichiers et les liens utilisés dans ce cours sont disponibles ici : www.xlerateur.com/stocker Finalement, je tiens à remercier Céline Brien, Louise Whitty, Ngoc Tran Pham, Justine Sautrey et Dominique Handelsman pour leur soutien durant tout le processus de création de cet ouvrage. C’est un plaisir de travailler avec vous !

Partie 1 Règles d’or et notions de base

Liste de contrôle des règles d’or :

11

Liste de contrôle des règles d’or : Cette liste vous permet de vérifier rapidement que les règles d’or ont bien été respectées :      

1 2 3 4 5 6

Une feuille = une table de données et rien d’autre Première ligne : uniquement les titres de colonnes Pas de cellules vides dans les titres de colonnes. Pas de cellules fusionnées Pas de doublons dans les titres de colonnes Une clef primaire dans la première colonne    

6.1 6.2 6.3 6.4

Ajout manuel Ajout automatique de la clef en VBA Pas de cellule vide pour la clef primaire Mise en forme conditionnelle pour les cellules vides

 7 Pas de lignes et de colonnes vides  8 Pas de totaux, sous-totaux et calculs intermédiaires  9 Utiliser les filtres automatiques et figer les volets  9.1 Filtres automatiques  9.2 Figer les volets

 10 Structurer vos tables  10.1 Une seule colonne pour une même dimension  10.2 Redondance des données

 11 Données numériques et calculs à droite  11.1 Position des données numériques  11.2 Dépendances de gauche vers droite  11.3 N’utiliser qu’une seule formule par colonne

     

12 Utiliser les tableaux d’Excel 13 Utiliser l’outil de validation des données 14 Retravailler des bases de données mal formatées 15 Dynamiser vos noms 16 SOMME.SI, INDEX et EQUIV 17 Création de listes en cascades

Une feuille = une table de données et rien d’autre

12

1 Une feuille = une table de données et rien d’autre Il est indispensable que chaque table occupe une feuille, et rien qu’une feuille. En effet, avoir deux tables sur la même feuille de calcul est potentiellement très dangereux et l’organisation des données n’est pas idéale.

Par exemple, vous courez le risque d’effacer par inadvertance des lignes appartenant aux deux tables :

Important : Les tableaux sont très efficaces pour saisir des données directement. Voici la procédure pour les mettre en place (voir aussi le point 12) : -

2003 : menu Données, puis Liste.

-

2007/2010/2013 : positionnez-vous sur votre plage de données, puis dans l’onglet Accueil, choisir Mettre sous forme de tableau.

Première ligne : uniquement les titres de colonnes

13

2 Première ligne : uniquement les titres de colonnes La première ligne de la feuille contient toujours et uniquement les titres et doit se trouver en ligne 1. De la même manière, on évitera toute colonne vide à gauche de la table.

Si vous voulez ajouter des boutons, des grands titres ou d’autres informations, augmentez la hauteur de la première ligne et ajoutez des zones de textes ou des boutons (outils de dessin) pour ajouter des fonctionnalités au fichier. On se trouve alors sur un autre plan que les cellules et ceci fonctionne correctement :

Vous pouvez aussi ajouter un cadre blanc ou de couleur en dessous des boutons pour améliorer le design de l’ensemble. Par défaut, les zones de texte et les boutons vont s’ajuster avec la largeur et la hauteur des lignes et des colonnes. Vous pouvez modifier ceci en se positionnant sur le bord du bouton ou de la zone de texte, puis en faisant un clic droit, choisir Format de la forme, Propriétés, et cocher : Ne pas déplacer ou dimensionner avec les cellules.

Première ligne : uniquement les titres de colonnes

14

Pas de cellules vides dans les titres de colonnes.

15

3 Pas de cellules vides dans les titres de colonnes. En effet, vous perdez l’information concernant le contenu de la colonne et en plus, vous ne pourrez pas utiliser correctement les tableaux croisés dynamiques.

Figure 1: Les titres des colonnes ne doivent pas être vide, ici 2003

Note : pour les versions supérieures ou égales à Excel 2007, une colonne vide d’un tableau est remplacée par « Colonne » suivi du numéro de colonne :

Figure 2: Pas de titres de colonnes vides, ici pour les versions supérieures ou égales à 2007

Pas de cellules fusionnées

16

4 Pas de cellules fusionnées Un corollaire de cette règle est de ne JAMAIS utiliser de cellules fusionnées, que ce soit pour les titres de colonnes ou à l’intérieur de la table.

Note : avec les tableaux de la version 2007 et suivantes, il est de toutes façons impossible de fusionner deux cellules.

Pas de doublons dans les titres de colonnes

17

5 Pas de doublons dans les titres de colonnes Afin de garantir que chaque colonne désigne un élément spécifique, il faut se garder d’utiliser le même nom de colonnes deux fois.

Note : avec la mise sous forme de tableau, les doublons sont automatiquement identifiés et un numéro leur est accolé, ici Adresse et Adresse2 :

Une clef primaire dans la première colonne

18

6 Une clef primaire dans la première colonne Une clef primaire est un numéro unique attribué à chaque enregistrement (ici, chaque ligne). Pour implanter cette clef, il y a plusieurs possibilités :

6.1 Ajout manuel C’est la solution la plus simple, vous ajoutez simplement un numéro de manière manuelle, en vous assurant que ce numéro est unique. Par exemple, si vous avez numéroté des formulaires papier, vous pouvez reprendre ce numéro pour ensuite facilement retrouver le formulaire utilisé.


19

6.2 Ajout automatique de la clef en VBA On peut créer un bouton sur la feuille : -

Sous Excel 2007/2010/2013, Onglet développeur1, puis Insérer, Boutons.

-

Sous Excel 2003, affichez la barre d’outils Formulaires.

Pour chaque nouvel enregistrement, on ajoute automatiquement une nouvelle clef en utilisant le code VBA suivant. Ce code est simplifié pour comprendre l’essence de ce qui se passe.

1

Si l’onglet Développeur n’est pas présent en 2007/2010/2013, voici comment l'afficher : - Excel 2007 : bouton Office (en haut à gauche) cliquez sur Excel Options (en bas à droite), puis cochez Afficher l’onglet Développeur dans le ruban. - Excel 2010 : Menu Fichier, Options, choisissez Personnaliser le ruban, puis cochez Développeur (cadre de droite).


20

Sub nouvelle_ligne() ActiveSheet.Range("A" & ligne_premiere_cellule_vide).Value = Application.WorksheetFunction.Max(ActiveSheet.Range("A2:A" & ligne_premiere_cellule_vide - 1)) + 1 End Sub

Function ligne_premiere_cellule_vide() ligne_premiere_cellule_vide = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row + 1 'Gestion d'un tableau vide : If ActiveSheet.Range("A" & ligne_premiere_cellule_vide - 1) = "" Then ligne_premiere_cellule_vide = ligne_premiere_cellule_vide - 1 'cas d'un tableau vide. End Function

En résumé, dans ce code, on repère la première cellule vide de la première colonne, en partant de la dernière cellule de la feuille et en remontant vers le haut. A l’emplacement de cette cellule, on va ajouter un nouvel enregistrement reprenant le maximum de toute la colonne plus une unité. Si jamais vous utilisez des tables liées (on est ici à la limite d’une solution Excel), il ne faudra pas oublier de supprimer les enregistrements liés dans d’autres tables. Par exemple, si vous supprimez un client, il faut aussi supprimer les factures de ce client.


21

6.3 Pas de cellule vide pour la clef primaire Dans une colonne contenant une clef primaire, toutes les cellules doivent impérativement contenir un nombre. Autrement dit, chaque ligne doit avoir une clef unique. Ceci permettra d’effectuer un comptage du nombre de lignes (fonction nombre dans les tableaux croisés dynamiques), de définir un nom dynamique (voir plus loin) ou encore de lier des tables.


22

6.4 Mise en forme conditionnelle pour les cellules vides On sélectionnera la colonne contenant la clef primaire, puis on choisira la mise en forme conditionnelle suivante (2007-2013, onglet Accueil ; 2003, Menu Format) :

De fait, une cellule non vide sera immédiatement mise en évidence :

Pas de lignes et de colonnes vides

23

7 Pas de lignes et de colonnes vides En laissant une ligne ou une colonne vide, vous risquez de ne travailler que sur une partie de la table. Excel va automatiquement reconnaitre les dimensions de la table en fonction de ces lignes et colonnes vides. De fait, un tri sur une partie de la table aurait un effet dramatique sur la cohérence de vos données.

Note : avec la mise sous forme de tableau, ceci est rendu impossible.

Pas de totaux, sous-totaux et calculs intermédiaires

24

8 Pas de totaux, sous-totaux et calculs intermédiaires En ajoutant des totaux et calculs intermédiaires dans la feuille contenant les données, vous courez le risque que ceux-ci soient comptabilisés dans des tableaux croisés dynamiques ou dans d’autres calculs effectués sur des colonnes entières.

Note : encore une fois, en utilisant la mise sous forme de table (Excel 2007 et plus), ce problème disparait. Les totaux de tableaux ne sont pas pris en compte dans les tableaux croisés dynamiques, et il est impossible d’ajouter des sous-totaux à un tableau.

Utiliser les filtres automatiques et figer les volets

25

9 Utiliser les filtres automatiques et figer les volets 9.1 Filtres automatiques L’utilisation des filtres automatiques permet de travailler directement sur toute la table de données. Ceci est particulièrement utile pour filtrer les données (évidemment), mais aussi pour créer des tableaux croisés dynamiques ou encore pour trier les données par ordre alphabétique. Rappel : aller dans l’onglet (ou le menu) Données, puis choisir Filtres automatiques

9.2 Figer les volets Une option très pratique consiste à figer les volets, ce qui permet de toujours garder la ligne de titre en haut de l’écran, quelle que soit la ligne sur laquelle on se trouve. -

Excel 2003 : Fenêtre – Figer les volets.

-

Excel 2007 et plus : Onglet Affichage – Figer les volets.

Utiliser les filtres automatiques et figer les volets

26

Note : la mise sous forme de tableau sous 2010 et plus permet de gérer les filtres et ne demande plus de figer les volets. Dès qu’on dépasse la première page, les titres de colonnes (A, B, C) se transforment en titre de colonnes du tableau, incluant aussi les possibilités de filtres.

Structurer vos tables

27

10 Structurer vos tables 10.1 Une seule dimension

colonne

pour

une

même

Un exemple classique est la création de nouvelles colonnes pour chaque nouveau mois ou pour chaque société. Ceci indique en général un problème dans la structure des données et surtout, cela va complexifier les calculs et les comparaisons. Dans de tels cas, il vaut mieux restructurer la table pour mettre la date dans une seule colonne (sauf cas particulier). Ici, on créera une seule colonne Société, dans laquelle on reprendra chacune des sociétés.

Ce qui nous donnera :

Pour calculer les statistiques, on utilisera ensuite des tableaux croisés dynamiques. Ce genre de problème arrive fréquemment avec une colonne (ou une feuille par mois), il vaut mieux alors restructurer le fichier dans une seule table avec une colonne date reprenant tous les mois.

Structurer vos tables

28

10.2 Redondance des données La normalisation, dénormalisation2 d’un ensemble de tables permet de réduire la redondance des données en créant plusieurs plus petites tables au lieu d’une seule très grosse. Par exemple, si dans une table de factures, on répète les informations d’un même client (société, nom, prénom) pour chacune de ses factures, on prend le risque d’avoir des informations fausses au bout d’un moment. De fait, on va choisir d’avoir une table Factures, une table Clients, et une clef NoClient que l’on réutilisera dans la table Factures pour identifier le client. Ainsi, l’information n’est stockée qu’à un seul endroit, ce qui réduit les risques d’erreur et la taille du fichier. Cependant, ceci complexifiera la maintenance de l’application. Il y a donc une analyse à faire sur la structure de la table et sur la manière d’agencer ces informations : dans une seule table ou dans des tables séparées. Dans le second cas, et en fonction de la taille et de la complexité de l’application, vous aurez besoin : -

soit de connaissances plus avancées (INDEX/EQUIV) – voir la suite du cours ;

-

soit d’envisager l’utilisation d’un logiciel de gestion de bases de données (type Access ou Oracle).

2

sur

Excel

Voir ici pour une explication plus complète

https://secure.wikimedia.org/wikipedia/fr/wiki/Forme_normale_%28bases_de _donn%C3%A9es_relationnelles%29

Données numériques et calculs à droite

29

11 Données numériques et calculs à droite 11.1 Position des données numériques De manière générale, on met les données numériques et les calculs à droite de la table de données. Ceci permet de localiser rapidement les calculs, mais aussi de respecter une certaine logique dans la lecture des informations. De plus, lors de la construction d’un tableau croisé dynamique, cela permet de pré-trier les données pour séparer ce qui va en lignes/colonnes (données non numériques) de ce qui va généralement en zone de calculs (données numériques).

Évidemment, si vos données sont déjà ordonnées suivant une autre logique, il peut être préférable de garder la logique précédente, notamment lorsque ces données sont importées.

11.2 Dépendances des calculs : de gauche à droite De manière assez logique, on essaiera aussi de faire en sorte que l’enchaînement des formules se fasse de gauche à droite. Ceci permet d’améliorer la vitesse d’exécution ainsi que la lisibilité des formules (et donc d’éviter des erreurs). Par exemple, ici, la cellule E2 sera calculée à partir de la cellule D2 et C2. La cellule F2 est calculée à partir de E2, et finalement, G2 est déterminée par F2.

Données numériques et calculs à droite

11.3 N’utiliser colonne

qu’une

seule

formule

30

par

Si la base est bien structurée, il est fortement recommandé de n’utiliser qu’une seule formule pour toute la colonne ; formule qui sera recopiée sur toute la colonne de la table. Cela permet entre autres, d’éviter des corruptions de données.

En d’autres termes, utiliser une formule faisant référence à la ligne du dessus ou la ligne du dessous est fortement déconseillé.

Utiliser les tableaux d’Excel

31

12 Utiliser les tableaux d’Excel Au fur et à mesure des versions d’Excel, l’utilisation des tableaux (appelés Liste sous 2003) a été considérablement améliorée et enrichie. Les tableaux s’avèrent ainsi très efficaces pour saisir des données directement. Voici comment les mettre en place : -

2003 : menu Données, puis Liste.

-

2007/2010/2013 : positionnez-vous sur votre plage de données, puis dans l’onglet Accueil, choisir Mettre sous forme de tableau.

Rappelons et illustrons certains avantages et limites de l’utilisation des tableaux

12.1 Avantages des tableaux -

-

L’ajout de nouvelles lignes au tableau permet de recopier automatiquement : o le format o les formules L’ajout de texte immédiatement à droite ou en dessous du tableau permet son extension automatique.


-

-

-

32

La référence à un tableau est dynamique ; elle s’ajuste avec les nouveaux éléments. Si un tableau croisé dynamique est basé sur un tableau, celui-ci conserve cette référence par la suite (Excel 2007 et suivants uniquement). Si vous utilisez un tableau pour définir un nom, ce nom s’ajustera automatiquement avec l’ajout de nouveaux items. Vous pouvez donc y faire référence (Excel 2007 et suivants uniquement). Vous pouvez aussi utiliser des formules structurées, pour cela, assurez-vous que (1) le format d’enregistrement est .xlsx ou .xlsm dans les options et (2) dans Options – Formules, la case est cochée :

Une formule structurée permet de faire référence à des zones spécifiques du tableau, notamment via les spécificateurs suivants (source : http://silkyroad.developpez.com/excel/tableau/) : 

[#Tout] fait référence au tableau complet (en-têtes et ligne de totaux compris).



[#Données] fait référence aux données, sans les entêtes ni la ligne de totaux.



[#En-tête] fait référence aux données d'en-tête.


33



[#Totaux] fait référence à la ligne de totaux.



[#Cette ligne] fait référence à la même ligne que celle qui contient la formule (est souvent utilisée dans les colonnes calculées).

On obtient ainsi ce type de formules : =SOMME.SI(Tableau6[Sexe];L2;Tableau6[Salaire])

12.2 Limites des tableaux -

-

-

Les tableaux sous Excel 2007 et suivants présentent des problèmes de compatibilité descendante avec Excel 2003, notamment sur les formules structurées. Donc, si votre fichier va être utilisé sous Excel 2003 : o vous pouvez toujours utiliser les tableaux (listes sous Excel 2003), mais… o il est conseillé d’utiliser les noms dynamiques (voir section 14, p. 37), en effet, les formules structurées des tableaux ne fonctionnent pas sous Excel 2003. Faire référence à des cellules d’un tableau peut générer des formules structurées assez longues, parfois difficiles à comprendre, même si globalement plus logiques et explicites. Les tableaux ne permettent pas le partage des fichiers et la modification simultanée sur un réseau.

Utiliser l’outil de validation des données

34

13 Utiliser l’outil de validation des données La validation des données sous Excel est un outil très puissant qui permet de vérifier, par exemple, que des dates ont bien été saisies, que les valeurs rentrées appartiennent à une liste, ou encore qu’une valeur se trouve entre un maximum et un minimum. Faire un cours complet sur cette fonctionnalité prendrait une journée, tant elle est puissante.

13.1 Création de listes Voici un exemple permettant d’ajouter une liste à une colonne. Tout d’abord, définissez un nom, ici type_emploi (sélectionnez la plage, puis dans la zone de nom située à gauche de la zone de formule, tapez type_emploi, validez par Entrée) qui fera référence à votre liste de valeurs.

Revenez ensuite dans votre table de données, sélectionnez la colonne du Poste et choisissez Données – Validation, puis dans le menu déroulant, Liste. Finalement, ajouter = type_emploi dans la zone « Source » :


35

Il ne reste plus qu’à utiliser la zone de liste déroulante disponible lorsqu’on se trouve sur la cellule :

Alternativement, on peut utiliser les noms dynamiques présentés un peu plus loin, notamment si on travaille avec des versions antérieures à 2003. Note : les utilisateurs ne connaissant pas cette technique peuvent être tentés de faire référence à une colonne entière pour la création de la liste déroulante. Bien que ce soit une solution qui fonctionne, je ne la recommande pas, car elle n’est


36

pas très utile pour rechercher des éléments dans la liste qui devient très longue (version 2007 et précédente). De plus, si vous utilisez des formulaires dynamiques (comme FormXL Pro), cela rend cette utilisation plus lente. On préférera donc les solutions présentées ci-dessus.

13.2 Dates, heure, nombres, etc. Grâce aux options de validation, vous pouvez…  Définir des intervalles pour des nombres entiers ou décimaux.  Définir la saisie à partir d’une liste.  Définir des intervalles de dates ou d’heure  Valider des nombres pour un code postal (par ex. d’une longueur de 4 ou 5)  Utiliser des formules personnalisées qui, par exemple, permettent de valider des saisies en fonction du contenu d’autres cellules.

Retravailler des données mal formatées

37

14 Retravailler des données mal formatées Il vaut mieux évidemment suivre les règles précédentes pour éviter que les données soient mal formatées ou mal saisies (notamment tout ce qui concerne la validation des données), cependant lorsque ce n’est pas possible, voici quelques solutions.

14.1 Connaitre et combiner les fonctions de texte Dans le cas où vous travaillez sur des bases de données mal formatées, les fonctions suivantes sont très utiles à connaitre :  GAUCHE qui permet de "renvoyer des caractères situés à

l’extrême gauche d’une chaîne de caractères" (aide d’Excel). =GAUCHE("EXCEL" ;3) retournera EXC

 DROITE, la même chose, mais à droite =DROITE("EXCEL" ;3) retournera CEL  STXT qui permet de "renvoyer un nombre donné de caractères extraits d’une chaîne de texte à partir de la position que vous avez spécifiée, en fonction du nombre de caractères spécifiés" (aide d’Excel). =STXT("EXCEL";2;3) retournera CEL  NBCAR permet de renvoyer le nombre de caractères. = NBCAR("EXCEL") retournera 5  TROUVE permet de renvoyer la position d’une chaîne de caractères dans une autre chaîne. Très utile lorsque cette fonction est combinée avec DROITE, GAUCHE ou STXT. Par exemple, on pourra utiliser une formule suivante =GAUCHE(A1;TROUVE("-";A1;1)-1) pour retourner tout ce qui se trouve à gauche d’un tiret.  NOMPROPRE permet de mettre en majuscule la première lettre de tous les mots d’une chaîne, et en minuscule les autres lettres. Très pratique pour transforme Jean DUPONT en Jean Dupont.  MAJUSCULE et MINUSCULE assez évident, cela permet de convertir en minuscule ou en majuscule. Ca peut être très utile pour l’uniformisation de fichiers.


38

14.2 Convertir du texte Dans Données – Convertir, vous avez la possibilité de convertir du texte, soit en fonction d’un séparateur (ici le « ; »), soit en fonction d’un nombre de caractères prédéfini.

14.3 Dédoublonner Depuis Excel 2007, on peut utiliser un outil permettant de supprimer les doublons, aller dans Données et choisir Supprimer les doublons :

Il reste ensuite à choisir sur quelles colonnes appliquer le dédoublonnage.


Pour Excel 2003 (et versions suivantes), vous pouvez aussi allez dans Données – Filtres, Filtres élaborées pour ensuite choisir une extraction sans doublons:

Avec l’outil de validation, vous pouvez aussi entourer les données non valides en rouge :

Ce qui donnera ceci :

39

Dynamisez vos noms

40

15 Dynamisez vos noms Un nom dynamique est un nom comprenant une formule permettant à la plage de se dimensionner automatiquement. Par exemple, vous pouvez utiliser un nom pour définir les dimensions d’une table, ou encore pour définir une liste. L’ajout de nouveaux éléments sera pris en compte automatiquement. Les améliorations apportées aux tables (Excel 2007-2013) rendent cette fonctionnalité moins importante. En effet, après avoir formaté votre tableau, vous pouvez ajouter un nom « normal » et celui-ci étant lié au tableau, il se mettra automatiquement à jour. Cependant, dans les cas où vous devez conserver une compatibilité avec Excel 2003, les noms dynamiques sont utiles. Pour insérer un nom : Excel 2003 : Insertion, Noms, Définir ; Excel 2007-2013 : onglet Formules, Gestionnaire de noms. Un nom dynamique est défini de la manière suivante :

Une fois ces noms définis, vous pouvez les utiliser pour construire un tableau croisé dynamique ou vos formules, les intégrer dans du code VBA ou encore pour gérer un formulaire. Pour approfondir ce sujet, vous pouvez aussi aller à cette adresse : http://www.polykromy.com/html/decaler.html

SOMME.SI

41

16 SOMME.SI Dans cette section, on se base sur le tableau suivant : En se basant sur la section précédente, on a créé un nom dynamique pour chacune des colonnes.

SOMME.SI

42

On peut ensuite utiliser ces noms pour des calculs sur la table de données. Par exemple, pour récupérer la somme des salaires : =SOMME(Base_Colonne_Salaire) Ou encore la somme des salaires en fonction du poste saisi en cellule A4, par exemple Secrétaire. =SOMME.SI(Base_Colonne_Poste;A4;Base_Colonne_Salaire)

Sous 2007/2010/2013, vous pouvez faire référence directement au tableau : =SOMME(Tableau1[Salaire]) =SOMME.SI(Tableau1[Poste];'Quelques calculs'!A4;Tableau1[Salaire])

Ne pas utiliser la fonction RECHERCHEV

43

17 Ne pas utiliser la fonction RECHERCHEV Bien que très souvent utilisé, cette fonction présente certains désavantages qui en font une fonction particulièrement dangereuse. Si vous ne connaissez pas cette fonction, vous pouvez tout de suite passer à la section suivante, ca ne sert à rien de l’apprendre, car la combinaison de fonction INDEX/EQUIV est beaucoup plus puissante et moins dangereuse ! ;-) Je plaisante à moitié, car vous risquez de récupérer des fichiers ayant cette formule, donc elle reste utile à connaitre ! La fonction RECHERCHEV permet de renvoyer la valeur d’une cellule situé dans la xème colonne (ou lignes pour RECHERCHEH) d’un tableau de recherche. Par exemple, vous pouvez rechercher un numéro de client dans une table et renvoyer la valeur situé dans la troisième colonne de ce tableau. Les dangers de cette fonction sont les suivants : -

Tout d’abord, cette fonction va présenter un bug majeur si on ajoute des colonnes à l’intérieur de la plage de recherche. En effet, comme on utilise un décalage de colonnes, ce décalage va rester le même et donc entrainer de gros problèmes. En plus, vous n’aurez pas forcément une valeur d’erreur, mais une valeur fausse comme résultat de votre recherche.

-

De plus, la fonction RECHERCHEV ne permet pas de travailler avec des tableaux à double entrée, donc on lui préférera la combinaison INDEX/EQUIV qui permet de le faire.

-

Vous ne pouvez pas non plus faire une recherche renvoyant les valeurs situés à gauche de la colonne de recherche.

-

Finalement, vous ne pouvez pas bouger la colonne de recherche sans risquer de rendre la formule inexacte.

INDEX/EQUIV

44

18 INDEX/EQUIV 18.1 Calculs à partie de INDEX/EQUIV On peut utiliser la combinaison INDEX/EQUIV pour rechercher le nom correspondant à un numéro d’employé.

=INDEX(Base_Colonne_Nom;EQUIV(B7;Base_Colonne_No;0)) Qui se décompose de cette manière :  EQUIV(B7;Base_Colonne_No;0) renvoie le numéro de la ligne du client No 7 (B7) dans la colonne clients, soit donc la valeur 3.  =INDEX(Base_Colonne_Nom;EQUIV(B7;Base_Colonne_N o;0)) correspond en fait à =INDEX(Base_Colonne_Nom;3) et renvoie la valeur de la cellule se trouvant dans la troisième ligne de la plage Base_Colonne_Nom, soit donc Cadieux. Une alternative si vous n’utilisez qu’Excel 2007/2010/2013 consiste à utiliser les formules structurées, c'est-à-dire liées au tableau. Ceci nous donne :

INDEX/EQUIV

45

18.2 Une construction invulnérable Lors de la construction de tels outils, il peut être pertinent de faire une boucle entre les titres de la table de données et le titre dans la feuille de reporting (ici en A11, la colonne Nom. Ceci nous donnerait :

Ainsi, à partir de l’exemple ci-dessus, on peut en B11 et C11, faire référence au titre de la colonne Nom dans la table de données. De fait, on peut modifier la formule EQUIV pour faire une recherche de la cellule A11 dans la première ligne de la table pour renvoyer la colonne correspondante. =INDEX(Tableau1[#Tout];EQUIV(B7;Tableau1[[#Tout];[No]];0);EQ UIV(A11;Tableau1[#En-têtes];0)) Ou pour 2003 avec des noms dynamiques:

INDEX/EQUIV

46

=INDEX(Base_tout;EQUIV(B7;Base_Colonne_No;0)+1;EQUIV('Que lques calculs'!A11;Base_Titre;0)) Ainsi, en changeant la valeur de B11 pour refléter le titre d’une colonne de la table (par exemple, Prénom), on renverra automatiquement les valeurs de cette table. Un très gros avantage de cette approche est qu’un changement de nom dans la table se reflétera instantanément dans les autres feuilles. Dans cet autre exemple basé sur le même principe (INDEX/EQUIV), on peut rechercher dans la feuille Clients, les informations du client pour renseigner une table Factures. On reviendra en détail sur ce type d’utilisation dans la partie suivante.

Création de listes en cascades

47

19 Création de listes en cascades La création de listes en cascade permet de gérer une liste déroulante qui se modifie en fonction du choix d’une autre liste déroulante. Par exemple, vous voulez choisir une marque de voitures pour ensuite accéder aux modèles correspondants, ou encore vous voulez choisir un type d’aliment pour ensuite aller à la liste des aliments correspondants : Choix des fruits :

Choix des légumes :

Ces listes dynamiques en cascade se créent de la manière suivante : 1/ Créez trois listes dans une feuille séparée. La première liste va avoir les items suivants : Fruits et légumes. Puis créez deux autres listes pour les fruits et les légumes. 2/ Nommez chacune des listes (à gauche de la zone de formule, vous avez la zone de nom), idéalement après avoir défini un tableau par liste. Par exemple, la liste des fruits est nommée


48

Fruits et la liste des légumes est nommée Légumes. Ça nous donne ceci:

3/ Dans une nouvelle feuille, par exemple en A2, choisissez la première liste, via Données – Validation – Liste et en mettant la référence correspondante (ici Type_Aliments). Donc, vous aurez une liste avec le choix Fruits, Légumes.

4/ Dans une autre cellule qui va recevoir le choix du produit, choisissez Données – Validation – Liste, et tapez =INDIRECT(A2). La fonction INDIRECT va renvoyer la plage nommée correspondant à A1, donc la plage Fruits ou la plage Légumes.


49

Et voilà, en choisissant Fruits ou Légumes en A2, le menu déroulant en C2 va se mettre automatiquement à jour.


50

Partie 2 Construction d’une application de facturation


52

Introduction

53

Introduction Dans cette partie, nous allons voir en détail comment construire une application de facturation. Nous nous intéressons tout d’abord à la structure des tables clients et factures et notamment à l’utilisation des fonctions INDEX/EQUIV pour établir la liaison entre les tables, puis nous construisons les différentes fonctionnalités de l’application (tables produits et détails des factures, les boutons de navigation, la facture en soi et certaines mises en forme conditionnelles). Nous finalisons ensuite l’application en ajoutant un formulaire de saisie pour chacune des tables. Le détail des fichiers est disponible ici : www.xlerateur.com/stocker

Ajout des tables

54

1 Ajout des tables On va ici traiter un problème simplifié permettant de « connecter » deux tables ensemble. Pour cela, on va avoir une clef dans chacune des tables : le no de facture et le no de client (voir page suivante). Dans la table facture, on va reprendre le no de client, de façon à identifier la facture correspondant au bon client :

Ainsi, le client no 1 a deux factures, la facture 1 et 3.

Ajout des tables

La table Clients sur la feuille Clients

La table Factures

Les tables Produits et Détail des factures

55

Ajout des noms dynamiques

56

2 Ajout des noms dynamiques Si vous travaillez avec les versions 2000 à 2013 d’Excel, il est utile de définir les noms dynamiques pour chacune des tables (voir la section 15 de la première partie).

3 Validation des données pour ne choisir que des numéros existants En utilisant la validation des données, on s’assure de n’utiliser que des numéros déjà existants (no de clients, de factures, etc) dans les tables liées. Par exemple, dans la feuille facture, le No Client a une validation sous forme de liste.

INDEX et EQUIV pour gérer les liaisons

57

4 INDEX et EQUIV pour gérer les liaisons 4.1 Pour Excel 2000 à 2013 Finalement, afin de connecter les deux tables, on utilise la formule suivante :

=INDEX(Clients_Colonne_Société;EQUIV($D2;Clients_Colonne_No_ client;0);1) se décompose de cette manière : 

EQUIV($D2;Clients_Colonne_No_client;0) renvoie le numéro de la ligne du client No 1 ($D2) dans la colonne Clients, soit donc la valeur 1.



=INDEX(Clients_Colonne_Société;EQUIV($D2;Clients_Colon ne_No_client;0);1) correspond en fait à =INDEX(Clients_Colonne_Société;1;1) et renvoie la valeur de la cellule se trouvant dans la première ligne, première colonne de la plage Clients_Colonne_Société, soit donc Société A.



Le dernier 1 est facultatif. Il permet d’indiquer la colonne.

De fait, cette formule renvoie le nom de la société en fonction du numéro client saisi.

4.2 Uniquement sur 2007/2010/2013 SI vous travaillez uniquement sur 2007/2010/2013, vous pouvez aussi utiliser l’alternative suivante utilisant les formules structurées : =INDEX(Tableau1[Société];EQUIV([@[No Client]];Tableau1[No client];0))

INDEX et EQUIV pour gérer les liaisons

58

Avec Tableau1 se référant au tableau de la table Clients. Évidemment, Tableau1 peut être renommé en Tableau_Client, à partir de l’onglet Création, puis Nom du tableau.

Et la formule se trouve automatiquement transformée en : =INDEX(TableauClients[Société];EQUIV([@[No Client]];TableauClients[No client];0))

On peut ensuite faire de même avec le tableau des produits. IMPORTANT : Il faut garder en tête que la suppression d’une ligne dans la table Client va entraîner l’apparition de #N/A dans la table facture. On peut gérer cette situation de deux manières : 1/ Soit en supprimant les lignes correspondantes dans factures. La meilleure option. 2/ Soit en gérant ceci avec une formule du type =SI(ESTNA(…)) La première option est de loin la meilleure, sinon on prend le risque de recréer un client avec un numéro de client déjà existant dans la table facture, ce qui aboutirait à une corruption de la validité des données.

Deux autres tables : produits et détail des factures.

59

5 Deux autres tables : produits et détail des factures. Nous ajoutons ici une table simple reprenant la liste des produits et leur description.

Nous ajoutons aussi une table reprenant le détail des factures, à savoir, le détail de la quantité de produits achetés.

Cette table est un peu plus complexe, car nous utilisons deux clefs : -

Le numéro de produit.

-

Le no de facture, qui est répété pour chaque ligne du détail de la facture.

Même si ce n’est pas obligatoire, nous pouvons reprendre via des formules INDEX/EQUIV, des informations complémentaires reliées à ces clefs : la date de la facture et les informations produits. Ceci permet d’ajouter une vérification de plus concernant le choix de la facture et du produit. De plus, ça va simplifier certaines formules par la suite, notamment lors de l’édition de la facture du client. Les formules sont les suivantes.

Deux autres tables : produits et détail des factures.

60

5.1 Pour 2000 à 2010 avec des noms dynamiques Pour la colonne Date facture, cellule C2 : =INDEX(Factures_Colonne_Date;EQUIV(B2;Factures_Colonne_No;0)) Pour la colonne Intitulé du produit, cellule E2 : =INDEX(Produits_Colonne_Intitule_du_produit;EQUIV(D2;Produits _Colonne_No_Produit;0)) Pour la colonne Intitulé du produit, cellule E3 : =INDEX(Produits_Colonne_Description_du_produit;EQUIV(D2;Prod uits_Colonne_No_Produit;0)) Pour la colonne Total, cellule I2 : =H2*G2

5.2 Pour 2007/2010/2013 avec les formules structurées Pour une application fonctionnant uniquement sous 2007-2013, on aurait les formules suivantes : Pour la colonne Date facture, cellule C2 : =INDEX(TableauFactures[Date];EQUIV([@[No Facture]];TableauFactures[No];0)) Pour la colonne Intitulé du produit, cellule E2 : =INDEX(TableauProduits[Intitulé du Produit]];TableauProduits[No Produit];0))

produit];EQUIV([@[No

Pour la colonne Intitulé du produit, cellule E3 : =INDEX(TableauProduits[Description du produit]; EQUIV([@[No Produit]];TableauProduits[No Produit];0)) Pour la colonne Total, cellule I2 : =[@[Prix unitaire]]*[@Quantité]

Ajout des boutons Nouveau

61

6 Ajout des boutons Nouveau Dans chacune des feuilles, on peut ajouter les boutons "Nouveau" permettant d’ajouter une nouvelle ligne (c.-à-d. un nouvel enregistrement) On se base ici sur le code décrit dans la première partie.

7 Deux nouveaux boutons dans la feuille Factures On ajoute aussi deux nouveaux boutons permettant de gérer l’ajout d’une nouvelle facture à partir du client sélectionné ou encore l’ajout du détail de la facture à partir de la facture sélectionnée. o

Feuille Clients, bouton Nouveau : pour créer une nouvelle facture pour un client sélectionné.

o

Feuille Factures, bouton Ajouter des détails à la facture sélectionnée : ceci va créer un nouvel enregistrement dans la feuille Détail factures.

Le code est structuré sensiblement de la même manière :

Deux nouveaux boutons dans la feuille Factures

62

Option Explicit '********************************************************* ' On va ajouter une ligne dans Factures et lui attribuer ' le numéro du client sélectionné. '********************************************************* Sub Nouvelle_facture() Dim lg_no_client As Long Dim lg_ligne_nouvelle_ligne As Long 'Est-on dans la table? If ActiveCell.Row = 1 Or ActiveCell.Row >= fct_ligne_premiere_cellule_vide Then MsgBox "Vous êtes en dehors de la table, déplacez-vous sur une ligne contenant un client.", vbOKOnly Exit Sub End If 'on met en mémoire le numéro du client lg_no_client = Cells(ActiveCell.Row, Range("Clients_Titre_No_client").Column).Value 'Afin d'augmenter la sécurité de l'application, on a aussi renommé les feuilles dans VBE 'on peut donc taper directement le nom de la feuille dans VBE : Factures.Select 'on met en mémoire le numéro de ligne qui sera ajouté lg_ligne_nouvelle_ligne = fct_ligne_premiere_cellule_vide 'on ajoute la nouvelle ligne Call nouvelle_ligne 'aucun paramètres, car par défaut, on se trouve sur la feuille active 'on complète le numéro du client Factures.Cells(lg_ligne_nouvelle_ligne, Range("Factures_Titre_No_Client").Column).Value = lg_no_client Factures.Cells(lg_ligne_nouvelle_ligne, 2).Select End Sub

La feuille facture récapitulative.

63

8 La feuille facture récapitulative. 8.1 Description de la feuille Cette feuille se base sur l’ensemble des tables pour créer une facture prête à être imprimée. Pour cela, il suffit juste de rentrer le numéro de la facture et c’est tout. La formule la plus complexe concerne les colonnes intermédiaires de calculs (sur la droite), et notamment les formules DECALER (en G21) pour permettre de calculer les différentes lignes. Une alternative en une seule formule matricielle existe aussi, mais elle est un peu plus compliquée à comprendre et pas nécessaire ici.

La feuille facture récapitulative.

64

8.2 Ajout des boutons de navigation à partir de Factures et Détail Factures Une procédure assez simple a aussi été ajoutée pour permettre, à partir des feuilles Factures et Détails factures, d’éditer directement la facture correspondant à la ligne de la sélection active. Il s’agit des boutons nommés : Editer la facture sélectionnée :

'*********************************************************** ' Cette procédure appelle la procédure selection_facture ' C'est utile si on veut appeler une procédure à partir d'un bouton. '*********************************************************** Sub selection_facture_a_partir_de_detail_factures() Call selection_facture(Range("Detail_factures_Titre_No_Facture").Column) End Sub '*********************************************************** ' Cette procédure sélectionne la feuille Formulaire Facture ' et change le numéro de facture en fonction de la ligne active '*********************************************************** Sub selection_facture(Optional i_colonne_de_reference As Integer) 'on utilise optional pour pouvoir lancer cette procédure à partir d'un bouton, comme dans la feuille Factures Dim lg_no_facture As Long Dim lg_ligne_nouvelle_ligne As Long If i_colonne_de_reference = 0 Then i_colonne_de_reference = 1 'Est-on dans la table ? If ActiveCell.Row = 1 Or ActiveCell.Row >= fct_ligne_premiere_cellule_vide Then MsgBox "Vous êtes en dehors de la table, déplacez-vous sur une ligne contenant une facture.", vbOKOnly Exit Sub End If 'on met en mémoire le no de la facture de la ligne de la cellule active lg_no_facture = Cells(ActiveCell.Row, i_colonne_de_reference).Value 'on sélectionne la feuille contenant le formulaire Formulaire_factures.Select 'Formulaire_factures est le nom de la feuille en VBE 'on met à jour le no de la facture dans ce formulaire. Formulaire_factures.Range("form_facture_no_facture").Value = lg_no_facture End Sub

Mise en forme conditionnelle sur le détail des factures

65

9 Mise en forme conditionnelle sur le détail des factures Dans cette feuille, on veut changer la couleur de ligne pour chaque changement de facture. Pour cela, on va ajouter la mise en forme conditionnelle suivante :

Cette mise en forme conditionnelle s’applique donc à la plage correspondant au tableau. À chaque fois que la cellule du dessus (colonne H) est différente de la cellule active, on formatte le fond en grisé.

Création des boutons de navigation

66

10 Création des boutons de navigation La procédure est très simple, faire un clic droit sur le bouton et choisir Lien hypertexte…

Puis, choisir la feuille sur laquelle on veut se positionner.

La plupart des boutons de navigation se retrouvent dans l'onglet Menu. Des boutons de navigation ont aussi été ajoutés dans les autres onglets, particulièrement pour permettre un retour à la feuille Menu et avoir ainsi une navigation fluide.

Formulaire de saisie

67

11 Formulaire de saisie Suivant la complexité de l’application, la volonté d’arriver à un résultat professionnel, ou encore le niveau des utilisateurs, il est parfois utile de créer des formulaires sous Excel. Ceci permet aux utilisateurs de rentrer plus facilement leurs données et de diminuer les risques d’erreur de saisie. Je propose ici de parcourir plusieurs solutions. La première solution me semble la plus efficace dans la majorité des cas et se base sur le logiciel FormXL Pro disponible ici (www.xlerateur.com/fxlg). C’est un logiciel qui nous a demandé plusieurs mois de développement et dont nous sommes particulièrement fiers ;-) Avec ce livre, vous bénéficiez automatiquement d’une réduction de 20 euros sur le prix du logiciel en vous rendant à cette adresse : www.xlerateur.com/fxlg.


68

11.1 Première solution : vous possédez FormXL Pro sous 2007/2010/2013 Il suffit simplement d’utiliser FormXL Pro pour remplir les informations de la table. Ca nous donne ceci, en un clic en appuyant sur le bouton d’affichage du formulaire :

On notera que les validations (listes, dates, etc.) sont automatiquement prises en compte dans le formulaire, de même que les formules. Avec FormXL Pro, vous avez aussi accès à un outil de recherche dynamique et instantané. De plus, en utilisant le bouton « Ajouter le formulaire au fichier Excel », vous pouvez rendre le fichier indépendant de FormXL Pro pour le diffuser à vos collaborateurs. Cette solution fonctionnera aussi sur Excel 2003.

Avec ce livre, vous bénéficiez gratuitement de 20 € de réduction sur l’achat de FormXL Pro (voir aussi en dernière page) : www.xlerateur.com/fxlg


11.2 Deuxième solution : personnalisation

simple,

mais

69

sans

Une autre solution consiste à utiliser l’outil de formulaire disponible directement dans Excel :

Celui-ci est disponible sous 2003 directement dans Données – Formulaire…

Sous 2007/2010/2013, il faut l’ajouter à la barre d’accès rapide (via les options d’Excel) ou bien l’appeler par macro.


70

Voici le code pour l’appeler via une macro : Sub appel_formulaire_excel() ActiveSheet.ShowDataForm End Sub On notera que cette solution ne permet pas de gérer les dates, les listes déroulantes ou les validations de données. De plus, l’interface est un peu vieille. On lui préférera FormXL Pro ;-)

11.3 Troisième solution – Le formulaire sur une feuille de données On peut aussi créer un formulaire de saisie sur une feuille de données. Pour cela, je vous invite à consulter la série de vidéos disponible ici : http://www.polykromy.com/blog/excel-base-de-donnees.htm

La mise en place prend plus de temps que les solutions ci-dessous, mais elle est gratuite et ne nécessite que peu de connaissances en VBA. La maintenance est un peu plus compliquée et demande ensuite une bonne compréhension de ce qui a été fait.


71

11.4 Quatrième solution – Userform en VBA Cette solution consiste à développer une solution complètement personnalisable en créant manuellement le formulaire. C’est utile si vous avez un besoin de personnalisation très important. Par exemple, vous voulez ajouter des boutons radio, des images, des onglets, la gestion de plusieurs tables sur un même formulaire, etc. Cette solution demande cependant un très bon niveau de maîtrise du VBA. Je vous recommande deux CD de formation à ce sujet. Le premier est une introduction à VBA dans lequel je décris en profondeur comment gérer les userforms et les lier à une table de données : http://www.polykromy.com/html/excelvba.html Le deuxième va beaucoup plus loin que le premier et permet de créer dynamiquement le formulaire en VBA. Par exemple, l’ajout ou la suppression de nouvelles colonnes est géré automatiquement sans avoir besoin de modifier le formulaire : http://www.xlerateur.com/vbaavance-1/ Le logiciel FormXL Pro est d’ailleurs basé sur ce cours, mais en poussant beaucoup plus loin le concept et l’automatisation.


72

Conclusion


74


75

Tout d’abord, je vous remercie de m’avoir lu jusqu’à la fin. En respectant ces procédures et règles d’or, vous pourrez construire des tables/listes/tableaux de données faciles à utiliser, rigoureux et solides. Malgré les nombreux tests et le soin particulier apporté au développement de ce cours, il est toujours possible que des erreurs persistent. Si tel est le cas, n’hésitez pas à m’en faire part, je vous en serai grandement reconnaissant. De même, si vous avez des commentaires, suggestions ou des questions, n’hésitez pas : [email protected] À bientôt Gaëtan Mourmant


76


77

SORTEZ DE L'ENFER DE VOS FEUILLES DE CALCULS : CREEZ EN 1 CLIC VOS FORMULAIRES EXCEL Bénéficiez de 20 € de réduction en vous rendant à cette adresse : www.xlerateur.com/fxlg Le moyen le plus rapide et le plus simple pour ajouter des formulaires automatisés et dynamiques à vos fichiers. Intégré directement à Excel dans les onglets Interface esthétique, moderne et élégante Version internationale directement intégrée Idéal pour les débutants, mais aussi pour les experts : Pas besoin de connaître le VBA pour créer et implanter les formulaires L'accès au code VBA est ouvert pour une personnalisation complète Excel 2007, 2010 et 2013 Mises à jour automatisées Satisfait ou remboursé !

Organisation et gestion de vos données sous Excel

Recommend Documents