Licence Professionnelle
Management et Gestion des Organisations
Spécialité Entrepreneuriat, Repreneuriat et Intrapreneuriat
Année 2019-2020 - Dates des Cours :
- Je 20.02.2020 - 13h30-17h00
- Ve 21.02.2020 - 13h30-17h00
- Ve 06.03.2020 - 13h30-17h00
- Me 11.03.2020 - 13h30-17h00
- Ve 27.03.2020 - 13h30-17h00 - RÉVISION EXCEL
- Ve 03.04.2020 - 13h30-17h00 - PARTIEL sur EXCEL
Cliquez sur le lien suivant pour avoir accès au cours TD du vendredi 27 mars 2020
Lien vers le bloc-notes lppme-2020
Objectifs Cours Outils informatiques de Gestion
- Savoir utiliser le tableur « EXCEL »
- Savoir utiliser les fonctions financières « EXCEL »
- Savoir utiliser le solveur « EXCEL »
- Savoir faire des tableaux croisés dynamiques avec « EXCEL » ou équivalent
Quelques utilisations du solveur d'excel
Pour utiliser le solveur il faut ajouter des compléments
(XLSTAT ou Analysis ToolPak) qui permettent de réaliser
de nombreux calculs statistiques
-
Sur Windows : « Fichier », « Options »,
« Compléments » puis « Atteindre » ...
-
Sur Mac : « Outils », « Compléments Excel » puis « Solveur »
Techniques EXCEL à connaître
- Les bases, la mise en page, les formats de cellule
- Utiliser un fichier CSV, Format UTF-8
- Savoir utiliser les Fonctions Financières
- Savoir utiliser les fonctions de Manipulations de texte
- Permuter le nom et le prénom inscrit dans une cellule
- Convertir les Minuscules et Majuscules
- Savoir faire un menu Rechercher sous EXCEL avec liste déroulante
- Savoir utiliser les Fonctions Rechercher
- Faire des Graphiques, Courbes 3D
- Créer des tableaux croisés
- Savoir utiliser le solveur
Feuilles de calcul
Rappels
- Utiliser une adresse relative B2 ou absolue du type $B$2, $B2 ou B$2
- Donner un nom à une case
- Sélectionner un ensemble de cellules juxtaposées avec :
- Sélectionner un ensemble de cellules non juxtaposées avec ,
Créer une table de multiplication ( de 1 à 9 ) sous EXCEL
- Mettre une formule a priori dans la case qui reçoit l'opération 1x1
- Propager vers la droite
- Sélectionner la ligne des cases obtenues
- Propager vers le bas
- Si le résultat obtenu n'est pas satisfaisant, reprendre la formule :
- Ajouter un $ devant une lettre et un autre $ devant un nombre
- Recommencer la propagation
- Indice : il y a un $ pour un opérande et un autre pour l'autre opérande de la formule
Les Tableurs, les Formules, les Fonctions
Établir un tableau d'amortissement sous EXCEL
- Faire un tableau d'amortissement d'un emprunt de 3000 € sur 3 ans en paramétrant le taux d'intérêt
Établir un tableau d'amortissement avec différé sous EXCEL
- Faire un tableau d'amortissement d'un emprumpt de 100000 € sur 10 ans avec un différé de 2 ans
- Paramétrer le taux d'intérêt
Calculer la valeur capitalisée d'un placement sous EXCEL
- Donner des noms aux cellules; exemple TAUX pour $B$3
Statistiques sur les sommes de nombres impairs sous EXCEL
Manipulation de texte
Permuter le nom et le prénom inscrit dans une cellule sous EXCEL
- Mettre le nom de famille en majuscule
- Que se passe-til si on met juste Jean sans le nom de famille ?
- Créer la case C2 avec le formule =B2& " " ( on ajoute un espace )
- Utiliser dans les formules des lignes 3 à 6, la référence B2 par une référence vers C2
- Enlever alors l'espace en trop dans la cellule B6 à l'aide d'un SI dans la cellule D8
Créer une liste déroulante sous EXCEL
Faire un menu Rechercher sous EXCEL
Lecture d'un fichier CSV avec EXCEL ou LibreOffice
- Mettre des É majuscules à Émile et à Élu
- Prendre toujours Format UTF-8
- Utiliser le séparateur ; (qui ne devra pas faire partir du contenu d'un champ)
- La première ligne contient les noms des champs
Les données sont séparées avec des points-virgules ( Comma-separated values )
- Faire un double click sur le fichier
- Si aucune application n'est associée, faire « Ouvrir avec... »
- Si le résultat n'est pas satisfaisant, lancer EXCEL puis faire F)ichier O)uvrir
- Si le résultat n'est pas satisfaisant, ouvrir avec LibreOffice
et enregistrer en XLSX, type Classeur Office Open XML
Exercice 1
Sachant que c'est le caractère - sépare l'année de début et l'année de fin,
créer deux colonnes DEB et FIN. Puis par programmation et propagation, remplir
les champs de ces colonnes. Pour cela utiliser les fonctions : GAUCHE et DROITE;
éventuellement utiliser les fonctions CHERCHE et NBCAR afin de vérifier
si on bien le format DDDD-FFFF sur 9 caractères avec un « - » en 5è position.
Créer deux autres colonnes et copier les valeurs des colonnes DEB et FIN,
puis supprimer les colonnes DEB, FIN et Dates. Donner des noms aux nouvelles
colonnes
Exercice 2
Sachant que c'est le premier espace qui sépare le Prénom du Nom,
créer deux colonnes PRENOM et NOM. Puis par programmation et propagation,
remplir les champs des ces colonnes, le NOM en majuscule.
Pour cela utiliser les fonctions : CHERCHE, NBCAR, DROITE, GAUCHE,
MAJUSCULE et éventuellement SUPPRESPACE.
Exercice 3
Créer une colonne avec le NOM en majuscule suivi du Prénom.
Pour cela , on utilisera les techniques de l'exercice précédent
ainsi que l'opérateur & de concaténation.
Exercice 4
Compter le nombre d'années de présidence de chaque président
arrondie à l'unité supérieure... (faire un « SI » pour traiter
le cas où le nombre d'année est nulle)
Donner de manière automatique, le nom du président ayant eu la présidence
la plus longue (MAX) ... que se passe-t-il si deux présidents sont
dans ce cas là ?
Exercice 5
Créer un liste déroulante composée des métiers suivants :
- Avocat
- Journaliste
- Haut Fonctionnaire
- Militaire
- Industriel
- Professeur
- Ingénieur Polytechnicien
Puis une case donnant le nombre de présidents ayant le métier d'origine sélectionné
Pour vérifier les résultats obtenus, se référer à :
Tableaux Croisés Dynamiques
Comment effectuer une régression linéaire avec Excel
Pour ajouter les fonctionnalités « Analyse de Corrélation »
et « Régression Linéaire », faire « Fichier »,
« Options », « Compléments »
et sélectionner Analysis Toolpak
puis cliquer sur « Atteindre » et ajouter les macros complémentaires
On peut alors les utiliser en faisant « Données », « Utilitaires d'Analyse »...
Voir aussi :
Les filtres dans Excel
Calculer l'écart-type d'une distribution sous EXCEL
- Faire un tableau initiale donnant les 36 résultats du lancé de 2 dés
On va calculer l'écart type ( racine carré de la variance ) de 2 manières suivantes :

-
La première formule calcule la racine carrée de la moyenne des carrés
de la différence entre un lancé et la moyenne de tous les lancés
-
La seconde formule calcule la racine carrée de la différence
entre la moyenne des carrés des lancés et le carré de la moyenne de tous les lancés
Pour cela, on va d'une part :
- Faire le tableau des écarts entre chaque nombre du 1er tableau et la moyenne
- Faire le tableau des carrés des écarts précédents
- Faire la moyenne des carrés obtenus pour obtenir la variance
- Calculer la racine carré pour obtenir l'écart type
Puis d'autre part :
- Faire un tableau avec les 36 carrés des résultats du lanc de dés
- Calculer la moyenne des carrés du 2nd tableau ( moment d'ordre 2 )
- Calculer le carré de la moyenne des nombres du 1er tableau
- Faire la différence entre la moyenne obtenue avec la 2nd tableau et le carré précédent
- Calculer la racine carré de la variance obtenue pour obtenir l'écart type
Indice : La variance est égale à 5,8333333
- Dessiner l'histogramme des données du 1er tableau
Compléments
Appliquer une mise en forme selon les données d’un tableau
Pour mieux visualiser les valeurs d’un tableau, vous pouvez appliquer une
mise en forme conditionnelle. Rendez-vous dans l’onglet Accueil du ruban,
puis Mise en forme conditionnelle et choisissez les règles de votre choix.
Vous pouvez également afficher des icônes selon les données du tableau.
Toujours afficher la première ligne / la première colonne sur Excel
Sur Excel, vous pouvez figer les volets : cela permet de toujours affiche
la première ligne ou la première colonne. Vous pouvez également choisir
de figer plusieurs lignes ou plusieurs colonnes ! Pour ce faire, sélectionnez
la première cellule non-figée. Par exemple, si vous souhaitez figer la ligne 1
et la colonne A, sélectionnez la cellule B2. Dans l’onglet Affichage,
choisissez Figer les volets et le tour est joué.
Faire un calcul matriciel sur Excel (résultat sur plusieurs cellules)
Dans certains cas, le résultat d’un calcul Excel ne peut être affiché sur
une seule cellule. On parle alors de calcul matriciel. C’est notamment le cas
de la fonction TRANSPOSE, qui permet de transposer une matrice. Pour ces
fonctions matricielles, vous ne pouvez pas taper ENTER pour valider la formule. Vous devez sélectionner la plage de sortie, saisir votre formule, puis taper CTRL+SHIFT+ENTER.
De cette façon, le résultat de votre calcul matriciel sera affiché
sur l’ensemble des cellules sélectionnées.
Ajouter des compléments à Microsoft Excel
Liens EXCEL