LPro MGC Excel: Sujets
Travail préparatoire
Exercices complémentaires sur Excel
Travail préparatoire
- Facturette : bases de calcul (32:11)
- Mise en forme de tableau (14:33)
- Facturette : formules avancées (39:35)
- Facturette : jouer à la marchande (28:11)
Programme
- Principes du tableur
- Maillon faible du système : calcul de la moyenne.
- Notion de fonctions
- Mise en place d'une facturation
- Début de formules et format L1C1
- Nom de plages
- Créer une facturette : code, désignation, PU, Qte, Total (en ligne 1)
- Ajouter 4 articles (en tenant compte de votre thématique du S1)
- Créer une cellule total en E11
- Gérer la TVA et le TTC
- Mettre en forme la facturette
- Créer une facturette : code, désignation, PU, Qte, Total (en ligne 1)
- Ajouter 4 articles (en tenant compte de votre thématique du S1)
- Créer une cellule total en E11
- Gérer la TVA et le TTC
- Mettre en forme la facturette
- Fonction rechercheV [B2=RECHERCHEV(Ref;tarif;2;0)]
- Fonction sierreur [B2=SIERREUR(RECHERCHEV(Ref;tarif;2;0);"Produit inexistant")]
- Formatage
conditionnel (sur la colonne B Si {Mise en forme conditionnelle -
Nouvelle règle de mise en forme - Appliquer une mise en forme
uniquement aux cellules qui contiennent - texte spécifique - Contenant -
"Produit inexistant" - Format... [Remplissage rouge - Police couleur
blanc gras]} - Finalisation du fichier [B2=SI(Ref="";"";SIERREUR(RECHERCHEV(Ref;tarif;2;0);"Produit inexistant"))]
A faire
- Créer une structure de factures avec des produits de votre thématique (A choisir)
- Refaire les formules de la séance avec votre sujet.
Fichier Clients
Travail préparatoire
- Clients : introduction (8:08)
- Clients : calculs sans mise en forme (19:55)
- Clients : mise en forme de tableaux (7:10)
- Clients : Tableaux Croisées Dynamiques TCD (26:33)
- Extraction du domaine d'un courriel (16:52)
Programme
- Principe du fichier client
- Création des champs DPT et REGION
- Comment calculer le département
- Traitement des étrangers dans REGION
- Gestion de l'affichage des tableaux
- Formules de gestion du temps
- Approche de segmentation d'un ensemble
- Filtres et volets
- Notion de tableaux Croisés Dynamiques
- Gestion de la segmentation
- Filtres sur TCD
- Extraction du domaine d'un courriel
A faire
- Créer une feuille Clients dans la facturette
- Vérifier que les 10 clients disposent des champs utiles et nécessaires pour un fichier clients actuel.
- Compléter votre fichier Excel avec au moins une "date de naissance" et un mail.
- Faire les calcul appropriés
- Intégrer un champ "domaine" de l'opérateur de courriel
- Faire au moins 3 tableaux croisés dynamiques pertinents
Travail préparatoire
- Graphiques : principes de base et axe secondaire (15:21)
- Publipostage : Microsoft Office (24:36)
- Base des macro commandes (28:08)
- Sécurité des macro commandes (8:07)
- http://www.brest.me/cours-en-videos/videos-excel-detail-2.html
Programme
- Publipostage
- Graphique
- Macro
- Liste déroulante
- Valeur cible
Vous devez établir un calendrier universel comme indiqué
dans le fichier PDF ci-dessous.
Le calendrier est sur 13 mois et peut démarrer n’importe
quel mois de l’année.
Pour vous aider, les paramètres d’année et de mois de départ
sont positionnés dans une feuille de paramètres.
Dans cette feuille de paramètres, vous indiquerez aussi les
jours fériés de l’année en vous servant des paramètres ci-dessous.
Jour férié | Date |
Jour de l'an | 1/1 |
Pâques | * |
Lundi de Pâques | = Pâques + 1 j |
Fête du Travail | 1/5 |
Fête de la Victoire | 8/5 |
Ascension | = Pâques + 39 j |
Pentecôte | = Pâques + 49 j |
Lundi de Pentecôte | = Pentecôte + 1 j |
Fête nationale | 14/7 |
Assomption | 15/8 |
Armistice | 11/11 |
Noël | 25/12 |
* Le plus gros problème est de calculer le jour de Pâques.
La formule est connue et est dans https://fr.wikipedia.org/wiki/Calcul_de_la_date_de_P%C3%A2ques.
Par contre pour la transformer en formule Excel c’est
relativement complexe !
Heureusement il y a eu des « olympiades » pour
trouver la formule la plus courte avec Excel pour obtenir le jour de Pâques.
https://groups.google.com/g/microsoft.public.excel.worksheet.functions/c/Io--GevuiX0/m/BaTgEuQQv4cJ.
Vous utiliserez la formule qui vous convient en faisant
attention au fait que les formules sont en anglais.
Si vous n’êtes pas un habitué du Visual Basic, vous pourrez
vous faire aider par le site https://fr.excel-translator.de/translator/.
Comme toujours, vous vérifierez que le jour de Pâques est
correct pour quelques années.
Dans la petite colonne à gauche de la date, indiquer W si c’est
un samedi ou un dimanche et F si la date est un férié.
Création du calendrier
En respectant les consignes ci-dessus, créer un calendrier
universel permettant de visualiser les Week-ends et les fériés
Coloriage
En utilisant le formatage conditionnel, colorier les 2 cases
liées au week-end pour le mois courant.
Sélectionner la plage (ex. : A3:B33) et appliquer le
menu « Formatage conditionnel | Gérer les règles ».
Créer une règle de type « Utiliser une formule pour
déterminer pour quelles cellules le format sera appliqué ».
Pour la formule concernant les Week-ends, mettre « =SI($A3=Code_Week;1;0) ».
Attention A3 sera variable en fonction de la plage de cellule sélectionner.
Prendre la première cellule en haut à gauche de la plage.
Dans format écrire en blanc sur fond gris, et valider. La
plage doit être « =$A$3:$A$33 » pour le mois de janvier.
Les 3 cas possibles sont notés dans la copie d’écran
ci-dessous.
Explication de la formule.
Le test conditionnel permet de renvoyer vrai (1) ou faux
(2).
Il suffit de tester si la cellule courante contient « F »
ou « W » ou son équivalent en qualité de variable.
Il ne faut pas bloquer la ligne 3, de façon à pouvoir incrémenter
la règle sur l’ensemble des cellules du mois. Par contre il faut bloquer les
colonnes pour rester dans la première colonne de la plage sélectionnée.
Si le résultat est vrai, alors la ligne des 2 cellules est coloriée,
sinon elle reste normale.