LPMGO Informatique Gestion.

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 1^er 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 2^nd tableau ( moment d'ordre 2 )
• Calculer le carré de la moyenne des nombres du 1^er tableau
• Faire la différence entre la moyenne obtenue avec la 2^nd 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 1^er 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