Organisation générale
L’organisation
des données est la seconde étape de la constitution d’un modèle Excel, pour
assurer une séparation rigoureuse entre :
1.
Données d'entrée : les informations qui seront
utilisées et manipulées pour obtenir la solution souhaitée ;
2.
Calculs intermédiaires : les opérations réalisées sur les
données d'entrée ;
3.
Présentation des résultats : les documents produits pour afficher
ou imprimer les résultats souhaités, tableaux, graphiques...
Le principe
à respecter absolument est de réellement créer 3 parties distinctes pour ne
jamais mélanger sur un même espace : données, calculs et résultats ;
pas de calculs dans une zone de saisie (sauf aux fins de contrôle de cette
saisie), ni de calcul complexe dans les tableaux de présentation des
résultats.
Selon la
dimension du modèle, sa complexité, on pourra répartir ces 3 parties sur un
nombre variable d'onglets. Une bonne règle à suivre est d'associer au minimum
un onglet à chaque partie principale, et plus si nécessaire.
Il faut
naturellement rester flexible : pour les problèmes simples, il n’est pas
interdit de constituer un modèle mono-onglet… mais en respectant cette règle de
non mélange. Excel offre suffisamment de lignes et de colonnes pour créer une
bonne ségrégation. L’utilisation de la couleur, de cadres, de liens hypertextes
pour passer d’une zone à l’autre… facilitera la création d’une feuille de
calcul clairement organisée.
Ne pas
oublier non plus un onglet de titrage et d'index, ainsi qu'un onglet de
présentation / explication générale du modèle. Dans les cas plus complexes, un
onglet - feuille de contrôle peut faire sens.
Données d’entrée
Les données
d’entrée se décomposent en 4 catégories :
1.
Données
saisies,
2.
Données
intégrées,
3.
Paramètres
du modèle,
4.
Tables
de recherche et de validation.
Données saisies
Les informations
qui seront entrées manuellement par les utilisateurs dans la feuille de calcul.
Il est
essentiel de donner à ces derniers les meilleures conditions pour assurer la
qualité de la saisie.
Un onglet
minimum sera réservé aux saisies de données. Pour un petit nombre
d’informations à saisir, on pourra aisément créer des écrans de saisie clairs
et faciles d’usage, regroupés sur un même onglet.
Pour les
saisies par accumulation (données historiques), l’utilisation de la fonction
tableau est souvent utile, permettant d’associer des formules de contrôle
automatiquement dupliquées. Si les saisies doivent faire l’objet de
consultations avec utilisation des filtres, préférer l’usage d’un tableau par
onglet, ce qui évitera de masquer les lignes d’un tableau avec les filtres
appliqués sur un autre.
Distinguer
libellés et zones de saisie par des moyens visuels est une bonne
pratique : couleur de fond et/ou de la police de caractère, en respectant
quelques règles simples :
1.
Rester
cohérent à l’intérieur du modèle (et ensuite de modèle en modèle) dans le choix
des couleurs de libellés et de couleurs de saisie ;
2.
Éviter
de peinturlurer votre modèle Excel : choisir des couleurs sobres et
discrètes.
L’organisation
de la saisie doit respecter la structure des documents source : un
document se lit (dans les pays latins et anglo-saxons) de gauche à droite et de
haut en bas… l’ordre de saisie s’adaptera strictement à l’ordre de lecture, ce
qui accélère et fiabilise la saisie.
Pour
limiter les risques d’erreurs :
1.
Ne
pas hésiter à prévoir des aides à la saisie (validation de données, listes de
choix) ;
2.
Indiquer
clairement la nature des données à saisir : unités monétaires et de
mesure ;
3.
Intégrer
des fonctions d’aide au contrôle : affichage de libellé pour une saisie de
code, calculs de cohérence dans la saisie de données numériques…
4.
Verrouiller
les zones non destinées à la saisie
On pourra
associer aux zones de saisie des manipulations de formatage simples destinées à
une utilisation ultérieure :
1.
Calcul
des jours, mois et année à partir d’une date ;
2.
Séparation
d’un code complexe en sous codes simples ;
3.
Créations
de codes complexes à partir de saisies distinctes pour la création de clés de
recherche ;
4.
Création
automatique de libellés abrégés…
Données intégrées
Les données
importées, manuellement ou automatiquement, de sources externes :
1.
En
provenance d’autres feuilles de calcul Excel, intégrées par copier-coller
manuel (ou mécanisé par une macro-commande), par formules utilisant des liens
externes ou par consolidation ;
2.
Fichiers
de transferts provenant de divers logiciels (fonction d’importation) ;
3.
Tables
issues de bases de données de l’organisation ;
4.
Tables
en provenance d’Internet ;
5.
Résultats
de requêtes SQL ou Power-Query sur bases de données.
Importer
des données, c’est connecter votre modèle Excel au monde extérieur, aux autres
sources d’information existant dans votre environnement, avec tous les
avantages en résultant : réutiliser un travail déjà produit en amont,
éviter les saisies fastidieuses et sources d’erreurs, se donner un moyen
d’analyse des informations… C’est pourquoi il faut préférer chaque fois que
possible une intégration automatique (par requête SQL ou Power-Query) ou
mécanisée (import de données, Copier-Coller)
à une saisie manuelle.
Intérêt des requêtes
La
meilleure solution reste l’intégration par requête (SQL ou mieux Power-Query)
qui permettra d’insérer des données correspondant exactement au besoin :
·
Par
les champs insérés : choix des champs dans les tables de la base de
données source, création de champs calculés, manipulation du contenu des
champs…
·
Par
des instructions de mise en forme pour réduire les retraitements ultérieurs
dans Excel : format de nombre, de date…
·
Par
les possibilités de connexion entre les tables de la base de données :
pour accéder au libellé correspondant à un code, pour mettre en relation
plusieurs informations (ex : commandes, livraisons, factures) …
·
Par
les possibilités de sélection selon des critères définis
·
Par
la mise en place de tris à plusieurs niveaux pour présenter le résultat de la
requête dans un ordre adéquat…
On peut
naturellement faire tout cela dans Excel également… Mais la requête une fois
paramétrée réalisera l’opération automatiquement… à chaque actualisation.
Actualisation des données importées
Les
requêtes délivrent toute leur puissance par le paramétrage de la mise à jour… À
la demande ou automatisée : à l’ouverture du modèle Excel, périodique
toutes les n minutes…
On peut
imaginer une requête récupérant des informations sur le système de caisse d’un
magasin pour actualiser les statistiques de vente automatiquement toutes les
minutes et suivre en quasi temps réel l’évolution du chiffre d’affaire… ou la
production d’un atelier… Sans qu’il soit nécessaire d’écrire une seule ligne de
programme, et sans aucune intervention de l’utilisateur du modèle.
Les
requêtes sont très utiles quand il faut intégrer des informations provenant de
nombreuses sources, par exemple pour la mise à jour d’un tableau de bord. Il
est facile de paramétrer une multitude de requêtes qui mettront automatiquement
à jour une batterie d’indicateurs sans qu’il soit nécessaire d’intervenir
manuellement.
Clarté du modèle
Chaque
requête produit un tableau Excel. Il est facile d’organiser ces tableaux, dans
un ou plusieurs onglets pour constituer un ensemble compréhensible :
·
De
préférence un onglet par source de données ;
·
Les
tables alignées de gauche à droite en haut de la feuille de calcul, tous les
entêtes sur une même ligne ;
·
Réserver
deux ou trois lignes au-dessus pour identifier source et nature du contenu par
un titre et des commentaires courts (pour plus de détails, on pourra
ensuite accéder aux paramètres de la requête) ;
·
Les
requêtes produisant directement des agrégats avec un nombre petit et fixe de
lignes peuvent être superposées ; on peut toutefois préférer calculer les
agrégats par la ligne de totaux ou un tableau croisé dynamiques, ce qui permet
de visualiser au besoin la justification de cet agrégat d’un simple clic ;
·
On
peut aussi demander à la requête de produire directement un tableau croisé
dynamique plutôt qu’un tableau… : à étudier selon l’usage ultérieur des
données.
Il n’est
pas nécessaire de tenir compte de contraintes de mise en page pour ces
onglets : ils ne sont aucunement destinés à l’impression. Il faut par
contre prévoir que les requêtes peuvent être modifiées et donc prévoir une
possibilité d’extension en nombre de colonnes pour chaque requête. C’est
d’autant plus important dans la phase de développement, tant que le modèle
n’est pas figé ; il est éventuellement plus facile de supprimer des
colonnes, en phase finale, dont on peut s’assurer qu’elles ne seront pas
utilisées.
Si l’onglet
occupe un nombre important de colonnes (plusieurs pages écran), prévoir une
colonne de liens hypertextes en colonne A pour accéder directement à la partie
de l’onglet que l’on souhaite vérifier. Cela fera gagner du temps.
Prévoir
également un onglet de sommaire fournissant une vue d’ensemble des connexions
créées.
Dans les
cas de relations complexes entre tables, un diagramme relationnel pour chaque
source pourra être utile dans l’avenir pour une compréhension rapide.
Paramètres
du modèle
Réserver un
onglet regroupant tous les paramètres utilisés dans le modèle, de préférence
dans un tableau comprenant les informations suivantes :
1.
Nom
du paramètre : qui sera utilisé pour le nommage de la valeur du paramètre
et utilisé dans les formules ; préférer un nom court, et respecter une
règle de nommage à la fois simple et parlante ; utiliser une racine
commune et un indice pour identifier des paramètres liés à une période ou un
numéro de série…
2.
Libellé
complet du paramètre : pour identifier clairement le paramètre ;
3.
Source
de l’information : pour justifier aisément l’origine de la valeur
attribuée ;
4.
Unité
de mesure ou monétaire utilisée : indispensable ;
5.
Valeur
du paramètre : sur une colonne si une seule valeur ; le cas échéant,
on peut utiliser plusieurs colonnes : mini – médian - maxi…, début – fin…
Tables
de recherche et de validation
Ces tables
seront utilisées en saisie pour la validation par listes de données et pour
associer des libellés à des codes saisis ou importés. Pour chaque table,
prévoir les colonnes qui seront nécessaires à la fois pour l’utilisation de ces
tables : code, libellé, valeur, unité… Utiliser un tableau pour le
stockage des tables, ce qui permettra d’ajouter facilement des valeurs sans
modifier les formules de recherche.
Poser les
tables de gauche à droite d’un onglet dédié aux tables.
Prévoir une
liste de liens en colonne A, qui servira d’index et d’accès direct, ainsi
qu’une ligne de titre et une ligne de commentaire en haut d’onglet. Figer les
volets pour maintenir visibles lignes de titre et entêtes de table et colonne
d’index – liens hypertexte.