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

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.

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:

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

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 « Saisie » (pour Tableau des saisies) à la place de « Tableau3 ».

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.

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:

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:

Vous arriverez sur la fenêtre suivante:

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« :

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…

Choisissez l’onglet « Options« .

Puis choisissez « Liste« .

Pour la source, tapez « = » puis cliquez sur F3.

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.

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)

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

4. Modifiez le Nom et cliquez sur OK

5. Allez sur l’onglet « Saisie« 

6. Sélectionnez la colonne Produits

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.

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

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

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

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

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.

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:

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.

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:

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:

Allez ensuite dans le ruban:

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

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.

Cliquez sur Format puis appliquez les options suivantes:

Cliquez ensuite sur OK:

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

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:

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

Vous pouvez admirez le résultat:

Mettre le montant en Euros

Sélectionnez la colonne « Montant« .

Puis allez dans le ruban:

RUBAN: Accueil > Nombre > Liste déroulante « Standard » > Monétaire

Voici le résultat:

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)

Et voici le résultat final:

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.