Création d’un tableau et du reporting associé – Partie 1

Excel offre de très nombreuses possibilités. Mais d’expérience, ce que nous trouvons le plus en entreprise est l’utilisation de tableaux utilisés sous forme de listes. Et ce que chacun souhaite est ensuite d’avoir la possibilité de réaliser une synthèse, voir un tableau de bord.

Je vous propose donc à travers plusieurs articles comment réaliser cela simplement, pas à pas. Nous utiliserons que des notions de base de façon à rendre l’exercice facile à reproduire pour tout le monde. Pour notre file rouge, j’ai choisi de prendre un cas concret d’entreprise puisqu’il s’agit de la vente aux salariés des produits proposés par le CE (Comité d’Entreprise).

 

La recette va se décomposer en 5 parties:

• Partie 1: Création des tableaux listant les données de référence (produits proposés, adhérents).
• Partie 2: Création du tableau de saisies.
• Partie 3: Création de la synthèse.
• Partie 4: Tester le fichier Excel. Il est essentiel de tester car il faut s’assurer du comportement de chaque action. Lors de l’utilisation, j’imagine que vous ne vous amuserez pas à recalculer les montants. Vous ferez confiance à votre fichier. Il faut donc s’assurer de cela dès le départ !
• Partie 5: Rédaction de la notice. Lorsque vous serez en congés, ou absents, ou… la personne qui s’occupera du fichier aura besoin de savoir comment il fonctionne. Il est donc essentiel pour tout classeur Excel d’y ajouter une notice.

 

Les deux derniers points sont souvent oubliés, pour ne pas dire toujours. C’est ce qui fait que les gens n’ont pas toujours  confiance en Excel mais plutôt en d’autres logiciels qui eux ont été testés et sont souvent accompagnés de notice !

 

Dans ce premier article, nous allons commencer par créer la structure de notre fichier Excel puis nous réaliserons nos tableaux de référence.

 

La Structure

Comme toute recette de cuisine, nous allons commencer par préparer notre plan. Pour cela, ouvrez un nouveau classeur Excel (CTRL + N).

Nous allons renommer nos onglets, et en rajouter (Shift + F11). Je vous propose la structure suivante:

• Onglet 1: Tableau de saisies. Vous pouvez donc renommer la feuille « Saisies » en faisant un clic-droit sur le nom de l’onglet, puis renommer (ou en cliquant deux fois sur l’onglet).
• Onglet 2: Synthèse. Renommez la feuille « Synthese« .
• Onglet 3: Tableaux de références. Renommez la feuille « References« .
• Onglet 4: Notice. Renommez la feuille « Notice« .

 

Vous devriez avoir un fichier qui ressemble à ceci:

 

Et n’oubliez pas de sauvegarder régulièrement (CTRL + S).

 

Partie 1: Les données de références

Dans notre cas, nous avons identifié deux types de données de référence:

1. La liste des adhérents
2. La liste des produits proposés.

 

L’objectif est de lister l’ensemble des adhérents avec leur nom, prénom et une colonne qui contiendrait une valeur unique (code employé par exemple) de façon à retrouver facilement la personne. En général, la colonne « Nom Prénom » répond à ce problème car dans une entreprise, il est quasiment impossible d’avoir deux personnes qui s’appellent exactement pareil. Si c’est le cas, nous utiliserons un autre code.

Ces listes de référence vont nous permettre:

• de créer une liste déroulante ;
• de vérifier que les valeurs entrées existent (important pour notre synthèse). En effet, si je saisie une fois « Ticket Gaumont » et la seconde fois « Gaumont », je retrouverai dans ma synthèse deux types de produits différents: Ticket Gaumont et Gaumont. Et la synthèse sera donc plus compliquée. Nous pouvons dire à travers cette vérification que nous ajouterons une contrainte de saisie.
• de faire des calculs. Par exemple en connaissant le tarif unitaire d’un produit, nous pourrons calculer le prix en fonction de la quantité.
• de faire des contrôles. Dans notre cas, nous décidons par exemple de limiter le nombre de places de cinéma à 10 par personne et par an. Lorsque la personne va saisir une quantité, nous pourrons automatiquement savoir si cela est accepté ou non en surlignant en rouge par exemple si la quantité annuelle est dépassée.
• de ne pas saisir à chaque fois les mêmes informations. Le prix du ticket de cinéma est noté une et une seule fois dans un tableau.

 

Finalement, vous êtes en train de créer une base de données sans même vous en rendre compte:

 

Liste des adhérents

 

Step1: Ecrire les titres des colonnes sur la ligne 1

 

Allez sur l’onglet que nous avons nommé « References », puis saisissez les informations comme ci-dessous:

 

 

Nous avons choisi ces titres mais si besoin, vous pouvez en rajouter ou en supprimer.

 

Step2: Mettre au format tableau

L’avantage pour nous du format tableau sera multiple:

1. Les formules seront enregistrées pour toute une colonne ;
2. Meilleure lisibilité au niveau des couleurs (alternance de la couleur une ligne sur deux)
3. Meilleure lisibilité dans les formules qui reprennent le titre de chaque colonne.

 

Séquence

Aller sur la cellule A1.

RUBAN: Accueil > Style > Mettre sous forme de tableau

Puis choisissez le format désiré.

Cochez la case « Mon tableau comporte des en-têtes » puis OK.

 

Vous devriez avoir ceci:

 

Nommons à présent ce tableau:

Positionnez votre cellule en A1.

RUBAN: Outils de tableau > Création > Propriétés > Nom du tableau:

Et écrivez « Adherents » (pour Tableau des adhérents) à la place de « Tableau1 ».

 

Step3: Mise en forme

Pour mettre en forme, nous allons commencer par remplir une première ligne:

 

Je n’ai pas rempli la colonne « Nom Prénom » car je veux qu’elle se calcule directement en fonction des deux premières. Nous le ferons à l’étape suivante.

Remarquons que la largeur des colonnes doit être revue pour une meilleure lisibilité.

 

Enfin, nous avons un problème avec le numéro de téléphone qui est considéré comme un nombre.

Pour y remédier, nous allons sélectionner la colonne téléphone:

 

Séquence

Positionner le curseur comme ci-dessous et vous verrez apparaître une petite flèche noire. Cliquez ensuite plusieurs fois jusqu’à la sélection de la colonne mais sans le titre:

 

Choisir directement

RUBAN: Accueil > Nombre > Liste déroulante

 

Puis choisir Texte:

 

Pour changer le format de cellule, vous pouvez également le faire de deux autres méthodes:

1. Faire ensuite CTRL+SHIFT+1 pour changer le format de cellule.

 

1. vous pouvez également faire un clic-droit puis choisir Format de Cellule.

 

Puis choisir le format Texte:

 

Une fois le format modifié, ressaisissez votre numéro de téléphone en entier:

 

L’icône vert dans le coin en haut à gauche spécifie qu’il y a peut être une erreur. En effet, pour Excel, nous avons rentré un nombre au format texte. Il nous informe qu’il y a donc peut être une erreur de format. Vous pouvez ignorer.

 

Step4: Ajout formule

Il ne nous reste plus qu’à créer formule pour avoir « Nom Prénom ».

La question est la suivante: comment voulons-nous voir le résultat ?

Pour notre exemple, voici ce que je propose:

NOM, Prénom

Donc Nom en majuscule et Prénom avec la première lettre en Majuscule, le reste en minuscule. Les deux parties seront séparées par une virgule.

Voici la formule qu’il faut donc écrire en C2:

 

=MAJUSCULE([@Nom]) & « ,  » & NOMPROPRE([@Prénom])

 

Vous pouvez voir [@Nom]. Il ‘agit de la valeur de la colonne « Nom » et qui se situe sur la même ligne (le @ signifie même adresse).

La remarque est la même pour le prénom.

Le « & » signifie que nous concaténons les valeurs.

 

Et puisque nous avons créé un tableau (Step 2), la formule sera enregistrée pour cette colonne. Si vous ajouté un nouvel adhérent, en commençant par écrire le nom en cellule A3, automatiquement la colonne C se calculera.

 

 

Remarquez également que j’ai écrit les noms et prénoms en minuscule et ma colonne C a été mise à jour comme je le souhaitais.

 

 

Liste des Produits

Nous allons présent construire notre deuxième tableau de référence: Les produits proposés par le CE.

 

Pour tout tableau, il faudra respecter les mêmes étapes que précédemment, à savoir:

• Step1: Saisir les titres
• Step2: Créer le tableau
• Step3: Mise en forme
• Step4: Ajout des formules

Toutefois, ici nous n’aurons pas de formule à appliquer et donc nous n’aurons que 3 étapes.

 

Step1: Saisir les titres

 

Positionnez-vous en cellule G1 et tapez les titres comme ci-dessous:

 

 

Step2: Créer le tableau

Positionnez-vous sur la cellule G1 et allez dans le ruban.

RUBAN: Accueil > Style > Mettre sous forme de tableau

 

Ensuite, choisissez le format de tableau qui vous plaît.

Renommez ensuite le nom du tableau (remplacez « Tableau2 » par « Produits »):

RUBAN: Outils de tableau > Création > Propriétés > Nom du tableau:

 

 

Step3: Mise en forme

Pour mettre en forme, nous allons commencer par remplir une première ligne comme tout à l’heure:

 

Après modification des deux premières colonnes:

 

Et donc pour le Prix Unitaire, nous souhaitons avoir 2 chiffres après la virgule.

Pour modifier cela, sélectionnez d’abord la colonne complète comme nous avions fait pour la tableau précédent:

 

Rappel:

 

Puis allez dans le ruban:

RUBAN: Accueil > Nombre > Liste déroulante

Puis choisir Nombre:

 

Conclusion

 

Pour finaliser, ajoutez une nouvelle ligne pour vérifier le fonctionnement en commençant à écrire en cellule G3.

 

Et pour rendre la lecture plus agréable, vous pouvez enlever le quadrillage en allant dans le ruban:

RUBAN: Affichage > Afficher > Quadrillage (décocher)

 

Votre onglet « Reference » est terminé. Voici le résultat:

 

Vidéo

Et voici la vidéo qui reprend pas à pas l’ensemble des étapes:

 

La prochaine étape aura pour objectif de créer l’onglet des « Saisies ».