TABLEUR - UE8 DCG
MISSION 4: GÉRER LES DONNÉES DANS UN TABLEAU
Avant de commencer la mission, vous devez maitriser les notions abordées dans le chapitre : "Organiser les données" (clé primaire, table, ...) et dans la fiche outil n°9 "Gestion de liste" OU les 3 vidéos ci-dessous :
​
​
​
​
​
​
​
​
​
​
​
Pour vérifier votre acquisition des concepts présentés, répondez au questionnaire suivant :
​
RECHERCHEV (Découverte)
LE 4ème paramètre de RECHERCHEV
Le double RECHERCHEV
Avant de commencer, reprenez votre classeur de la mission 3.
​
Si vous avez perdu votre classeur, téléchargez le tableur suivant :
​
CONTEXTE : Madame DAVAR, votre directrice, souhaite que vous repreniez la feuille "FACTURE". En effet, toutes les cellules ne sont pas automatisées. Aujourd'hui, quand un commercial saisit les produits de la commande, il doit aller chercher les références dans la feuille "PRODUITS" et les copier.
​
​
TRAVAIL A FAIRE
1) Dans la feuille FACTURE, créer une liste déroulante dans la plage de cellule A15:A19. Expliquer l'intérêt de cet outil.
​
2) Toujours dans la même feuille, automatiser les cellules C15:C19 (Désignation du produit) et D7 (Nom et prénom du client), D8 (Adresse), D9 (Code Postal et Ville).
​
3) Entrainez-vous sur la formule RECHERCHEV et RECHERCHEH à l'aide du questionnaire ci-dessous. Si besoin, téléchargez les feuilles ici :
​
​
​
​
CONTEXTE : Mme.DAVAR souhaite mettre en place un système de remise selon le montant de la facture. Elle vous communique le tableau suivant :
​
​
​
​
​
​
​
​
​
​
Par exemple, pour 160€ de commande, le client bénéficie d'un taux de remise de 4%. Soit une facture finale de 158,4€.
​
​
4) Importer le tableau de remise (disponible ici : ). Modifier ensuite votre feuille de calcul "FACTURE" pour obtenir l'ergonomie suivante :
​
​
​
​
​
​
5) Automatiser les cellules E22 à l'aide d'une fonction RECHERCHEV(). Automatiser également E23 et E25.
DEMONSTRATION
INDEX ET EQUIV
Pour faire des recherches dans un classeur, RECHERCHEV() et RECHERCHEH() sont des fonctions utiles mais limitées. Nous allons maintenant étudier les fonctions INDEX() et EQUIV()
​
Avant de continuer, prenez le temps de voir ces deux vidéos ou de revoir la fiche outil n°9
​
​
​
​
​
​
​
​
​
​
Pour vérifier votre acquisition des concepts présentés, répondez au questionnaire suivant :
TRAVAIL A FAIRE
6) Entrainez-vous sur les fonctions INDEX() et EQUIV() avec le quiz ci-dessous.
​
​
​
​
​
​​
7) Dans la feuille "FACTURE", automatiser les cellules C15:C19 (Désignation du produit) et D7 (Nom et prénom du client), D8 (Adresse), D9 (Code Postal et Ville) à l'aide des fonctions INDEX() et EQUIV().
Avant de continuer, vous allez devoir vous entrainer sur les fonctions RECHERCHEV, RECHERCHEH, INDEX et EQUIV sur papier.
​
Si vous en ressentez le besoin, un entrainement supplémentaire est disponible ci-dessous. Vous trouverez également les feuilles du dossier d'exercice papier.
​
​
APPROFONDIR N°4 :
​
Téléchargez le fichier GestionListes.xlsx
Le fichier contient 7 feuilles permettant d'approfondir votre compréhension des formules RECHERCHEV, RECHERCHEH, INDEX et EQUIV
​
Suivez attentivement les consignes écrites en haut de chaque fiche.
Avant de commencer la mission, vous devez maitriser les notions abordées dans :
​
-
La fiche outil n°9 "Gestion de liste" (SOMME.SI, NB.SI) OU les 4 vidéos ci-dessous :
​
Un classeur de démonstration est disponible ici :
SOMME.SI
NB.SI
SOMME AVEC PLUSIEURS CRITERES :
SOMME.SI.ENS
COMPTER LES LIGNES SELON PLUSIEURS CRITERES
NB.SI.ENS
Pour vérifier votre compréhension de SOMME.SI(), NB.SI(), SOMME.SI.ENS() et NB.SI.ENS(), veuillez répondre au questionnaire suivant :
CONTEXTE :
​
Madame DAVAR vous demande de synthétiser les informations relatives aux ventes. L'objectif est de favoriser la prise de décision à partir de statistiques clés.
Avant de poursuivre la mission, si vous en avez besoin, vous pouvez télécharger le classeur corrigé ici :
TRAVAIL A FAIRE
8) Dans le même classeur, importez la feuille SYNTHESE.txt et VENTES_MAJ.csv.
​
9) Dans la feuille SYNTHESE, à l'aide des fonctions de votre choix, automatisez B2, C2, D2, B8, C8 et D8. Votre recopie doit se faire sans erreur. Vous devez obtenir le tableau suivant :
​
​
10) Entrainez-vous sur les fonctions NB.SI() et SOMME.SI() avec le quiz ci-dessous. Le classeur est disponible ici :
​
​
​
​
​
​
​
​
​
11) Pour chaque commercial, Madame DAVAR souhaite connaître le nombre factures, le montant total et moyen. TOUJOURS DANS LA FEUILLE SYNTHESE :
(a) Importer le tableau suivant :
(b) Automatiser le nombre de commandes, le total, la moyenne et le total par mois à l'aide de formules.
NB : Un exemple du tableau à obtenir est disponible ci-dessous.
​​
12) Les entretiens d'évaluation approchant, Madame DAVAR souhaite 3 représentations graphiques des performances de ses commerciaux : 1) Nombre de commandes, 2) Total des ventes) 3) Total des ventes par rapport à l'objectif de 2800€. Dans la feuille SYNTHESE, réaliser les 3 graphiques demandés par Madame DAVAR.
NB : Un exemple du tableau et des graphiques à obtenir sont disponibles ci-dessous.
Pour réaliser vos graphique, aidez-vous de la fiche outil n°11 (Insérer un graphique)
APPROFONDIR N°5 :
​
Téléchargez le fichier SyntheseDonnees.xlsx
Le fichier contient 5 feuilles permettant d'approfondir votre compréhension des formules SOMME.SI, NB.SI, NB.SI.ENS et SOMME.SI.ENS
​
Suivez attentivement les consignes écrites en haut de chaque fiche.