Apprendre Excel 2019 pour débutant
Master Génie Industriel:
Spécialités Production-Maintenance et Informatique Industrielle
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
Introduction (1)
Historique
1981 : VisiCalc (, 27ko)
1982 : Multiplan (255 lignesx65 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 lignesx256 colonnes = 16,7 . 106cellules) 2007 : Excel 2007 (v. 12) (106lignesx16 384 colonnes = 17,1 . 109cellules), 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)
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
Choix d'Excel
94 % du marché (2010), langage VBA
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
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 graphiqueCellule (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
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
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 relativementMixte (ex. B$1 ou $B1)
Nommée (ex. rayon, en référence à $B$1)
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
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.) 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)
Graphes
Graphes
Généralités
Nombreux types et sous-types, qui peuvent être combinésDonnées source :
Étiquettes de l'axe des abscisses en première colonne Une série de données par colonne
Exemple Quadrillagesecondaire Étiquettede donnée Courbe de régression secondaireAxe Y
Graphe combiné courbe-histo Étiquettes X : A2:A7
2 séries de données nom : B1, valeurs : B2:B7 nom : C1, valeurs : C2:C7
Analyse
Outils d'analyses de simulation (1) Utilité : trouver une valeur optimale
Données Formules Résultat
pour une formule (celle de la cellule cible) en respectant des contraintes sur les valeurs des cellules référencées Valeur cible
Analyse
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
Analyse de corrélation, variance, covariance Histogramme, rang (analyse de position), échantillonnage Tests statistiques
Traitement de données : Filtres (1)
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 (2)
Filtre élaboré(cf.)
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
Traitement de données : Filtres (3)
Filtre élaboré(cf.)
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 (4)
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 (5)
Filtre élaboré
Critères calculés
Traitement de données : Tri et Sous-totaux
Traitement de données : Tableaux croisés dynamiques
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
Formules Données Formulaires
Formulaires (1)
Principe
Formules Données Formulaires
Formulaires (2)
Références
Cours en ligne
Pratique
Annexe - Pratique
Saisie de données
Éviter le formatage automatique : précéder la valeur d'une apostrophe (« ' » ) Texte d'une cellule sur plusieurs lignes : utiliser Alt+Entrée
Sélection à la souris
Grande plage : clic HG, puis Shift+BD
Étendre plage : Shift et/ou Ctrl
Ligne/colonne complète : en-tête
Plusieurs lignes/colonnes complètes : Shift et/ou Ctlr+en-tête
Document complet : bouton Sélectionner tout