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

Lors du premier article, nous avons donc créer la structure de notre fichier Excel:

      • 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“.

Et nous avons également créé notre 3ème onglet: Tableaux de références (Adhérents et Produits).

Nous allons donc à présent passer à la deuxième partie qui consiste à créer notre table de saisies.

Comprendre le besoin

D’un point de vue fonctionnel, nous pourrons y entrer les achats des adhérents et il nous faudra donc remplir les éléments suivants:

      • Date: Date d’achat
      • Nom Prénom: Nom et prénom de l’adhérent. Cette cellule devra exister dans la table des Adhérents créée dans la partie précédente.
      • Produit: Produit acheté par l’adhérent. Cette cellule devra exister dans la table des Produits créée dans la partie précédente.
      • Qté: Quantité de produits achetés.
      • Banque: Les paiements dans les CE se font uniquement par chèque. Nous y retrouverons donc le nom de la banque. Attention, nous n’avons pas créé de table référence pour la Banque, ce qui signifie que le nom pourra varier selon la personne qui saisit: Crédit Agricole ou CA par exemple. Nous ne pourrons donc pas utiliser cette donnée dans notre synthèse. Cette information servira uniquement dans un souci de recherche.
      • n° Chèque: Correspond au N° de chèque des achats. L’adhérent pour faire plusieurs achats avec le même numéro de chèque.
      • Montant: Montant total de l’achat. Il sera calculé automatiquement en fonction de la quantité et du prix unitaire défini dans la table de référence Produits.
      • Année civile: Puisque un nombre maximum est défini par année civile, nous allons calculer l’année de la date d’achat pour récupérer cette information comme critère.
      • Qté Totale: Quantité totale achetée par l’adhérent pour le produit désigné et l’année civile correspondant à la date d’achat. Cette valeur sera calculée.
      • Qté Max: Nous récupérons ici la valeur de la quantité maximum définie dans la table Produits. Le but étant de spécifier à travers une couleur le fait que la quantité a été atteinte.

Cette proposition de colonnes peut être plus efficace mais elle répond aux besoins et reste simple dans la mise en œuvre.

Voici ce que nous pourrions donc avoir et que nous allons donc réaliser.

Saisie 01

Création de la table de saisie

Step1: Saisie des titres

Ouvrez le classeur réalisé lors de la première partie, puis positionnez-vous sur la feuille “Saisie” en cellule A1.

Saisissez les titres comme ci-dessous:

Saisie 02

Step2: Création de la table

Positionnez votre cellule en A1 puis dans le ruban:

RUBAN: Accueil > Style > Mettre sous forme de tableau

Puis choisissez le format désiré.

Saisie 03

Cochez la case “Mon tableau comporte des en-têtes” puis OK.

Saisie 04

Vous devriez avoir ceci:

Saisie 05

Nommons à présent ce tableau:

Positionnez votre cellule en A1.

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

Et écrivez “Saisie” (pour Tableau des saisies) à la place de “Tableau3”.

Saisie 06

Nous pouvons à présent commencer par remplir la date dans la première colonne. Pour l’exemple, nous remplirons 2 lignes.

Remarque: Pour mettre la date du jour, positionne-vous en A2, puis tapez sur CTRL + ;

Refaites la même chose en A3.

Saisie 07

Step3: Création de la liste déroulante Nom Prénom

La création de la liste déroulante va être réalisée en 2 étapes:

  1. Création d’un champ nommé
  2. Création de la liste déroulante.

Création d’un champ nommé

Un champ nommé est une plage de données à laquelle nous avons donné un nom de façon à utiliser ce nom à chaque fois que nous ferons appel à cette plage.

Allez sur l’onglet “References” et sélectionnez les deux valeurs de la colonne C, comme ci-dessous:

Saisie 08

Tapez CTRL + F3 (voir article sur les raccourcis pour les champs nommés) ou dans le ruban:

RUBAN: Formules > Noms définis > Gestionnaire de noms

Cliquez sur Nouveau:

Saisie 09

Vous arriverez sur la fenêtre suivante:

Saisie 10

Donnez un nom à votre Plage: Liste_Adherents

Dans la partie “Fait référence à“, vous devriez déjà avoir “=Adherents[Nom Prénom]” car vous aviez préalablement sélectionné toutes les cellules de la colonne “Nom Prénom“. Dans le cas contraire, tapez exactement la même chose.

Puis cliquez sur OK.

Nous venons de créer un Champ Nommé Liste_Adherents qui fait référence à l’ensemble des Nom Prénom de notre tableau des Adhérents. A chaque fois que nous ajouterons un adhérent, la liste se mettra à jour automatiquement. Et ce sera la même chose lorsque nous supprimerons une ligne de ce tableau car nous avons dit que le champ Liste_Adherent doit contenir toute la colonne “Nom Prénom” du tableau “Adherents” (=Adherents[Nom Prénom]).

Création de la liste déroulante

Revenez sur l’onglet “Saisie” et sélectionnez la colonne “Nom Prénom“:

Saisie 11

Allez ensuite dans le ruban pour créer une validation de données:

RUBAN: Données > Outils de données > Validation des données > Validation des données…

Saisie 12

Choisissez l’onglet “Options“.

Puis choisissez “Liste“.

Pour la source, tapez “=” puis cliquez sur F3.

Saisie 13

Sélectionnez la Liste_Adherents que vous venez de créer, puis cliquez sur OK deux fois.

Vous venez de créer une liste déroulante pour choisir l’adhérent.

Saisie 14

Step4: Création de la liste déroulante Produits

Nous allons à présent réaliser les mêmes manipulations que précédemment pour créer la liste déroulante des produits.

Séquence

1. Allez sur l’onglet “References

2. Sélectionnez la colonne “Produits” (en colonne G)

Saisie 15

3. Cliquez sur CTRL + F3 et cliquez sur Nouveau.

Saisie 16

4. Modifiez le Nom et cliquez sur OK

Saisie 17

5. Allez sur l’onglet “Saisie

6. Sélectionnez la colonne Produits

Saisie 18

7. Allez dans le ruban:

RUBAN: Données > Outils de données > Validation des données > Validation des données…

8. Choisissez Options, puis Liste puis tapez “=” dans Source et cliquez sur F3.

Saisie 19

9. Choisissez “Liste_Produits” que vous venez de créer, puis cliquez sur OK deux fois.

Saisie 20

10. Vérifiez en sélectionnant chaque cellule de la colonne Produits

Saisie 21

A présent, vous pouvez remplir les colonnes comme ci-dessous:

Saisie 22

Step5: Calcul du montant

Vous avez donc préparé toutes les données à saisir. Les colonnes qui suivent seront donc calculées.

Pour le montant, il va falloir réaliser le calcul suivant:

MONTANT = Quantité x Prix_Unitaire

Nous connaissons déjà la quantité puisqu’elle a été saisie dans la colonne D.

Le prix unitaire dépend du produit choisi.:

Saisie 23

Or, nous connaissons déjà le produit qui a été saisi en colonne C.

Nous devons donc récupérer la valeur dans le tableau Produits.

Positionnez-vous en cellule G2 et tapez la formule suivante:

=RECHERCHEV([@Produit];Produits;3;FAUX)

Cette formule recherche le Produit (Produit) de la ligne correspondante (@), dans la table Produits. La recherche se fait toujours dans la première colonne.

Et la formule renvoie la valeur correspondante de la 3ème colonne du tableau Produits.

Le dernier argument “FAUX” signifie que nous recherchons une valeur exacte et non la valeur la plus proche.

Une fois la formule saisie en G2, tapez sur ENTREE et vous remarquerez que la formule se répète dans toutes les cellules de la colonne. Ceci est dû au fait que nous utilisons un format Tableau.

Nous avons donc bien récupéré la valeur unitaire mais il faut à présent multiplier par la quantité.

Positionnez-vous à nouveau en cellule G2 et cliquez sur F2.

Rajouter à la fin *[@Qté]

La formule doit devenir:

=RECHERCHEV([@Produit];Produits;3;FAUX)*[@Qté]

Cliquez sur ENTREE et encore une fois la formule a été mise à jour dans toute la colonne.

Saisie 24

Step6: Calcul de l’année civile

L’année civile correspond à l’année de la date d’achat, saisie dans la première colonne.

Positionnez-vous en cellule H2 et tapez la formule suivante:

=ANNEE([@Date])

Puis tapez sur ENTREE pour obtenir le résultat suivant:

Saisie 25

Step7: Calcul de la quantité totale

Ce calcul est un peu plus compliqué puisqu’il s’agit de faire la somme des quantités pour le produit, l’adhérent et l’année civile spécifiées sur la même ligne.

Positionnez-vous en cellule I2 et tapez la formule suivante:

=SOMME.SI.ENS([Qté];[Nom Prénom];[@[Nom Prénom]];[Produit];[@Produit];[Année Civile];[@[Année Civile]])

Nous allons la décomposer pour bien comprendre.

Cette fonction calcule une somme selon plusieurs critères.

Rappel:

Lorsque vous voyez [Produit], cela correspond à la colonne complète nommée Produit.

Lorsque vous voyez [@Produit], cela correspond à la cellule de la colonne Produit et qui est sur la même ligne.

[Qté] est la plage sur laquelle il faut faire la somme.

[Nom Prénom] On recherche dans la colonne Nom Prénom

[@[Nom Prénom]] la valeur Nom Prénom de la ligne correspondante. Si nous avons des doubles [ ], c’est parce que le nom de la colonne contient des espaces.

[Produit] On recherche dans la colonne Produit

[@Produit] la valeur Produit de la ligne correspondante.

[Année Civile] On recherche dans la colonne Année Civile

[@[Année Civile]] la valeur Année Civile de la ligne correspondante.

Tapez sur ENTREE et la formule sera dupliquée dans toute la colonne.

Saisie 26

Step8: Calcul de la quantité Max

Tout comme pour le montant, nous allons récupérer la quantité maximum dans notre tableau produit en utilisant la formule suivante en J2:

=RECHERCHEV([@Produit];Produits;2;FAUX)

Et la formule renvoie la valeur correspondante de la 2nde colonne du tableau Produits.

Tapez sur ENTREE.

Nous approchons de la fin de la réalisation de notre tableau de saisies puisque toutes les cellules ont été créées comme nous le souhaitions:

Saisie 27

Step9: Surligner les lignes si quantité max atteinte

Nous souhaitons à présent surligner en rouge toutes les lignes de notre tableau si la quantité totale est supérieure à la quantité maximum autorisée.

Pour cela nous allons utiliser les mises en forme conditionnelles.

Sélectionnez votre tableau comme ci-dessous:

Saisie 28

Allez ensuite dans le ruban:

RUBAN: Accueil > Style > Mise en forme conditionnelle > Nouvelle règle

Saisie 29png

Sélectionnez “Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué

Ecrivez ensuite la formule suivante:

=$I2 > $J2

Cela signifie que pour chaque ligne, à chaque fois que la colonne I ($I) est supérieure à la colonne J ($J), il faudra appliquer le format que nous allons choisir.

Saisie 30

Cliquez sur Format puis appliquez les options suivantes:

Saisie 31

Cliquez ensuite sur OK:

Saisie 32

Pour tester, nous allons modifier les quantités (mettre 2 dans la cellule D2):

Saisie 33

Vous remarquez que la ligne complète est à présent en rouge.

Step10: Mise en forme

Nous allons à présent mettre en forme notre tableau:

      • Largeur des colonnes
      • Montant en Euros

Largeur des colonnes

Pour la largeur des colonnes, sélectionnez toutes les cellules de votre feuille en cliquant ici:

Saisie 34

Et double-cliquez ensuite sur le trait entre la colonne A et la colonne B:

Saisie 35

Vous pouvez admirez le résultat:

Saisie 36

Mettre le montant en Euros

Sélectionnez la colonne “Montant“.

Puis allez dans le ruban:

RUBAN: Accueil > Nombre > Liste déroulante “Standard” > Monétaire

Saisie 37

Voici le résultat:

Saisie 38

Step11: Finaliser

Pour finaliser, nous allons à présent supprimer le quadrillage comme nous avions fait sur l’onglet “References“.  Est-ce que vous vous souvenez ?

Bon je vais vous le repréciser.

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

Saisie 39

Et voici le résultat final:

Saisie 40

Conclusion

Nous venons de réaliser toute la partie saisie. Vous pouvez donc vous amuser à remplir les données afin que lors de la prochaine étape nous puissions créer une synthèse et peut être un petit tableau de bord.

Cela sera fait dans la prochaine étape et il n’en restera plus que deux: Créer la notice et surtout tester l’ensemble du classeur. Je vous donnerai quelques astuces sur les principaux tests à réaliser qui seront réutilisables pour tous vos prochains classeur.

La vidéo

Et comme c’est toujours bien de pouvoir visualiser directement ce que vous venez de faire, je vous propose une petite vidéo qui reprend l’ensemble des étapes.

One thought on “Création d’un tableau et du reporting associé – Partie 2

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *