Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Master Génie Industriel – Tableur 1 Unité Tableur et Bases de données http://la

Master Génie Industriel – Tableur 1 Unité Tableur et Bases de données http://lagis-vi.univ-lille1.fr/~lo/ens/gi/#TBD Tableur – Utiliser Excel Olivier Losson Master Génie Industriel : http://master-gi.univ-lille1.fr Spécialités Production-Maintenance et Informatique Industrielle Master Génie Industriel – Tableur 2 Plan du cours Introduction Historique Types de tableur et fonctionnalités d'un tableur 1 – Objets classeur, feuille et cellule 2 – Formules, références et fonctions 3 – Graphes 4 – Outils d'analyse Valeur cible Solveur 5 – Traitements de données Filtres automatique et élaboré Tris, sous-totaux, tableaux croisés dynamiques 6 – Formulaires Références Master Génie Industriel – Tableur 3 Introduction (1) Historique 1981 : VisiCalc (http://www.bricklin.com/history/vcexecutable.htm, 27ko) 1982 : Multiplan (255 lignes x 65 colonnes = 16 000 cellules) 1983 : Lotus 1-2-3 (plages nommées, macros) 1985 : Excel pour Mac, puis Windows en 1987 (v. 2.0) (GUI) 1990 : Excel v. 3.0 (classeur, dessin 3D) 1993 : Excel v. 5.0 (classeur multipages, VB), devient leader du marché 1997 : Excel97 (v. 8.0) (validation des données, feuilles utilisateur, 65 536 lignes x 256 colonnes = 16,7 . 106 cellules) 2007 : Excel 2007 (v. 12) (106 lignes x 16 384 colonnes = 17,1 . 109 cellules), changement d'extensions : xls → xlsx 2013 : Excel 2013 (v. 15) (travail mobile, partagé et/ou en ligne) 2016 : Excel 2016 (v.16) (cartes 3D, requêtes dynamiques sur BdD) Master Génie Industriel – Tableur 4 Introduction (2) Types de tableurs Libres ou open-source Calc de la suite OpenOffice ou LibreOffice Gnumeric Kspread (Linux) Propriétaires Excel (Microsoft) Quattro Pro (Corel) Internet Google Spreadsheet EtherCalc Comparaison https://en.wikipedia.org/wiki/Comparison_of_spreadsheet_software Choix d'Excel 94 % du marché (2010), langage VBA Master Génie Industriel – Tableur 5 Introduction (3) Fonctionnalités d'un tableur (ang. Spreadsheet) Manipulation élémentaires de données numériques Stockage (classeur, feuille, cellule) Calculs (adresse, formule, fonction) Fonctionnalités de haut niveau Tri de données Représentation graphique Filtrage (sélection selon critère) Résolution de problèmes (solveur) Synthèse de résultats (tableau croisé dynamique, consolidation) Automatisation et interaction avec l'utilisateur (formulaire, macro VBA) Un tableur est inadapté pour traiter des données Volumineuses Liées et/ou de structure complexe Mises à jour fréquemment Destinées à produire des rapports → Utiliser un SGBD Master Génie Industriel – Tableur 6 Objets Excel Classeur (ang. Workbook) Ensemble de feuilles Correspond à un fichier .xls(x) Feuille (ang. Worksheet) de calcul : tableau rectangulaire de cellules graphique : uniquement 1 graphique Cellule (ang. Cell) Contenant indivisible de donnée (éventuellement calculée) Plage (ang. Range) : ensemble de cellules généralement (mais pas nécessairement) contiguës. Exemples : Sélection rectangulaire Cellule active Ligne ou colonne Ensemble de cellules disjointes désignées individuellement Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 7 Objets Excel Classeur Cellule (active, plage de) Feuilles Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 8 Objet Cellule Propriétés Adresse Concaténation de la colonne (A, B, .. IU, IV) et de la ligne (1, 2, …, 65 536) Notation alternative : LyyCxx Exemple : B3 correspond à L3C2 Valeur (contenu) Numérique : entier, réel, date Texte (jusqu'à 65 000 caractères) Booléen (VRAI ou FAUX) Formule (expression calculée) Objets (lien hypertexte, OLE, …) Commentaire Style Format (notamment numérique) Alignement, police, bordure, fond Protection (verrouillée ?) Mise en forme conditionnelle Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 9 Formules (1) Généralités Introduite par = Expression formée de : opérateurs (math., & pour concaténer) opérandes : constantes, références, fonctions Résultat Valeur (ou erreur), affichée La formule source est dans la barre de formule Référence à une cellule (ex. B1) Absolue (ex. $B$1) : recopiée telle quelle Relative (ex. B1) : recopiée relativement Mixte (ex. B$1 ou $B1) Nommée (ex. rayon, en référence à $B$1) Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 10 Formules (2) Référence à une plage de cellules Cellule active Par son nom (nommée par Insertion/nom) Plage rectangulaire Référence des coins Haut G. et Bas D. Ex. B2:C4 ou $B$2:$C$4 Liste de cellules ou plages Séparer leurs références par le caractère « ; » Ex. B2:C4 équivaut à B2;C2;B3:C4 Préfixer (si besoin) du nom de feuille (séparateur « ! ») Ex. Feuil1!$B$2:$C$4 Ligne/colonne/feuille entière Ligne 2 entière : $2:$2 Colonne B entière : $B:$B Feuille Feuil1 entière : Feuil1!$1:$65536 Repérer les antécédents/dépendants Menu Outils/Audit de formules Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 11 Fonctions Généralités Toute fonction retourne une valeur typée nombre(s), chaîne, booléen, référence, ... Syntaxe NomFonction(paramètre1; paramètre2; …) Exemple =DATE(1901; 1; 1) → 01/01/1901 ou 367 Quelques fonctions (cf. http://www.excel-pratique.com/fr/fonctions.php) ALEA() Ex. =ALEA()*(4-2)+2 → Nombre aléatoire  [2,4[ SI(test_logique; valeur_si_vrai; valeur_si_faux) Ex. =SI(A1<=1; "1 ou moins"; "Plus de 1") → "Plus de 1" FREQUENCE(tableau_données; matrice_intervalles) Ex. =FREQUENCE(A1:B3; {1;3}) → {1;4;1} (occurrences pour ]-,1], ]1,3], ]3,+[) DECALER (réf; lignes; colonnes; hauteur; largeur) Ex. =SOMME(DECALER(A2:B3; -1; 0; 2; 1)) → 4,8 (somme des valeurs de A1:A2) Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 12 Graphes Généralités Nombreux types et sous-types, qui peuvent être combinés Données source : Étiquettes de l'axe des abscisses en première colonne Une série de données par colonne Exemple Graphe combiné courbe-histo 2 séries de données nom : B1, valeurs : B2:B7 nom : C1, valeurs : C2:C7 Étiquettes X : A2:A7 Quadrillage Étiquette Courbe de Axe Y secondaire de donnée régression secondaire Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 13 Outils d'analyses de simulation (1) Utilité : trouver une valeur optimale pour une formule (celle de la cellule cible) en respectant des contraintes sur les valeurs des cellules référencées Valeur cible La cellule à modifier doit contenir un nombre constant (pas une formule) Ex. Calcul de remise Ex. Résolution d'équation : x3-5x+2=0 (racines : 2, ±2-1) Formules Données Résultat Formules Données Résultat Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 14 Outils d'analyses de simulation (2) Solveur Limite de la valeur cible : recherche d'une valeur unique en fonction de la valeur d'une cellule unique → utiliser le solveur sinon Installation (si besoin) : Outils/Macros complémentaires Ex. Résolution d'équation : x3-5x+2=0 sous contrainte x>1 Outils/Utilitaires d'analyses Analyse de corrélation, variance, covariance Histogramme, rang (analyse de position), échantillonnage Tests statistiques Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 15 Principe N'afficher que les lignes correspondant à certains critères Opère sur un tableau (plage rectangulaire) avec en-têtes de colonnes Filtre automatique (Données/Filtrer) Champs (en-têtes) Activation du filtre Tableau filtré Traitement de données : Filtres (1) Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 16 Filtre élaboré (cf. http://philippetulliez.developpez.com/tutoriels/advancedfilter/) Plus de possibilités que le filtre automatique Critères plus complexes (combinés, champs calculés, ...) Filtrage sur place ou exportation du résultat vers autre feuille/classeur Utilise un tableau de données avec les en-têtes de colonnes en première ligne une zone de critères (en général au-dessus des données) utilisant les en-têtes Ex. Traitement de données : Filtres (2) Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 17 Filtre élaboré (cf. http://philippetulliez.developpez.com/tutoriels/advancedfilter/) Plus de possibilités que le filtre automatique Critères plus complexes (combinés, champs calculés, ...) Filtrage sur place ou exportation du résultat vers autre feuille/classeur Utilise un tableau de données avec les en-têtes de colonnes en première ligne une zone de critères (en général au-dessus des données) utilisant les en-têtes Ex. Traitement de données : Filtres (3) Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 18 Filtre élaboré Critères combinés Sur la même ligne : ET logique Sur des lignes différentes : OU logique Traitement de données : Filtres (4) Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 19 Filtre élaboré Critères calculés Le nom de champ est un alias quelconque (différent de tout en-tête du tableau) Le critère est une formule qui doit retourner VRAI ou FAUX Le test logique doit porter sur une|des cellule|s de la première ligne de données Ex. Traitement de données : Filtres (5) =NBCAR(B6)>=8 =J6<MOYENNE($J$6:$J$23) Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 20 Sous-totaux Calculer des stats. pour chaque groupe de données Ex.  Tri par Option puis par Nom  Ajout de sous-totaux Traitement de données : Tri et Sous-totaux Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 21 Limites de Sous-totaux Ne permet de créer un groupe que sur 1 champ Présentation peu synthétique si beaucoup de données Tableau croisé dynamique Tableau à double entrée (groupes sur 2 champs) synthétisant les données Dynamique : change automatiquement avec les données source Ex. Traitement de données : Tableaux croisés dynamiques Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 22 Principe Conception d'interfaces simples à base d'objets graphiques (contrôles) Types de contrôles : Contrôles paramétrés (cf. Propriétés) → ne nécessitent pas de code VBA Ex. Formulaires (1) Étiquette Zone d'édition Zone de groupe Bouton Cases d'option Listes Défiler/compter Propriétés Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 23 Ex. utilisant INDEX(matrice; no_lig; no_col) Formulaires (2) Objets Formules Graphes Analyse Données Formulaires Master Génie Industriel – Tableur 24 Cours en ligne http://www.commentcamarche.net/contents/excel-tableur-1992167239 http://www.coursbardon-microsoftoffice.fr/excel2007/index.htm http://excel.developpez.com/cours/ https://fr.wikiversity.org/wiki/Tableur_EXCEL https://user.oc-static.com/pdf/200966-analysez-des-donnees-avec-excel.pdf http://www.excel-pratique.com/fr/ Pratique https://fr.wikibooks.org/wiki/Microsoft_Excel/Raccourcis_clavier http://www.andypope.info/tips.htm http://peltiertech.com/Excel/index.html uploads/Industriel/ www-cours-gratuit-com-id-10777.pdf