Embed Size (px)
Citation preview
2
Présentation Un tableur…
◦ Est un outil de calcul puissant et complexe◦ Propose de nombreux outils que l’on peut classifier◦ Permet de gérer et traiter des données
Un tel outil nécessite une certaine rigueur dans les termes utilisés, et une bonne compréhension de son fonctionnement global
De nombreuses suites logicielles incorporent un tel outil (Microsoft Office, LibreOffice, StarOffice, OpenOffice, …)
3
Kit de survie
Pour Excel ou pour le reste, vous ne saurez sans doute jamais TOUT ce que l’on attend de vous
Vous devez donc savoir vous débrouiller avec les moyens du bord
Un petit kit de survie semble approprié pour ne pas mourir dans d’atroces souffrances le jour de l’examen
4
Kit de survie : le bon sens paysan
Vous êtes doté de réflexion et de libre arbitre (au-delà de toute considération philosophique)
Lorsque vous testez une formule, des indices peuvent vous indiquer son degré de pertinence :◦ L’affichage d’une date au lieu d’un pourcentage
◦ Un stock négatif
◦ Un moyenne supérieure à la note maximale
◦ …
Réfléchissez et n’appliquez pas bêtement.◦ Réfléchissez aux causes probables d’un résultat incohérent, sans vous
énerver, et de façon objective : vous pouvez vous être trompé, la série étudiée peut contenir des valeurs « hors normes »
◦ Contrôlez en modifiant des valeurs de la population que vous testez par exemple
◦ Plusieurs formules mènent au même résultat, essayez en deux quand vous le jugez nécessaire, et que vous le pouvez :
Moyenne(A1:A3) SOMME(A1:A3) / 3 (A1+ A2 + A3) / 3
5
Kit de survie : les outils Vous serez rarement confronté à un problème pour
lequel aucune aide ne vous est accordée
Excel regorge d’aides en tous genres◦ Touche F1 : Aide globale de Excel
◦ Les petits boutons ou liens « Afficher l’aide »
◦ La fenêtre d’insertion d’une fonction vous offre un moteur de recherche si vous avez oublié, ou ne connaissez carrément pas la fonction dont vous avez besoin
◦ Les messages d’erreur lorsque votre saisie est erronée
Internet est plus vaste que Facebook et Gmail ! *◦ Les moteurs de recherche sont efficaces
◦ Des sites et forum spécialisés existent www.siteduzero.com www.wikipedia.org www.ilemaths.net www.excel-pratique.com …
Des gens qui savent◦ Le professeur n’est pas là pour juger, mais pour faire comprendre
◦ Des power points extraordinaires *
*(sauf le jour de l’examen)
>> Le cours
6
SommaireApplications concrètes
◦A la maison◦En entreprise
TerminologieLes fonctions
7
Applications concrètes : à la maison Gérer ses comptes, planifier un budget, etc… Faire des statistiques sur son équipe de foot
préférée Sauvegarder les numéros de ses contacts
◦ Avec l’apparition des Smartphones cela tend à disparaitre
Gérer une médiathèque
8
Applications concrètes : en entreprise
Certaines entreprises, y compris d’envergure internationale, utilisent des tableurs dans des domaines variés :◦ Comptabilité◦ Gestion de stock◦ Tableaux de bords◦ Gestion de projet◦ …
Il n’est cependant pas réaliste de gérer la comptabilité d’une multinationale via tableur, le volume des données, et la complexité des traitements doivent rester mesurés. Le meilleur moyen de déterminer un volume d’activité pouvant être traité par tableur reste l’expérimentation
9
SommaireApplications concrètesTerminologie
◦Le logiciel◦Le fichier◦L’espace de travail
Les fonctions
10
Terminologie : menu du logiciel
1. Ruban2. Onglets3. Barre d’outils Accès rapide4. Titre du fichier5. Autres commandes
1
2
3 45
11
Terminologie : menu du logiciel
1. Ruban◦ Il s’agit du menu principal. Le ruban change selon l’onglet
sélectionné, et affiche les principales fonctionnalités
2. Onglets◦ Ils regroupent les outils du logiciel par catégories. L’onglet
‘Fichier’ est un peu particulier, et permet de gérer le fonctionnement du logiciel, la sauvegarde, l’impression, ou le partage du fichier mail.
3. Barre d’outils Accès rapide◦ Personnalisable, cette barre permet de créer des raccourcis vers
des actions (annuler, imprimer, nouveau fichier, etc. …)
4. Titre du fichier◦ Affiche le nom du fichier, ce qui permet d’être sûr de ne pas
travailler sur un fichier similaire (correction/énoncé, ou bilan 2011/bilan 2012, etc…)
5. Autres commandes◦ Les 3 boutons du haut ont le comportement connu, mais leurs
miniatures situés dessous permettent de réduire/ agrandir, et fermer le document courant, au lieu du logiciel entier.
12
Terminologie : le fichier tableur
Un fichier de type tableur, également appelé classeur, est composé d’une ou plusieurs feuilles.
Les feuilles sont d’immenses matrices de cellules.
fichier tableur = classeurClasseur
Feuille 1
Cellule A1
Cellule A2
…
Cellule B1
Cellule B2
…
…
…
…
Feuille 2
Cellule A1
Cellule A2
…
Cellule B1
Cellule B2
…
…
…
…
Feuille 3
Cellule A1
Cellule A2
…
Cellule B1
Cellule B2
…
…
…
…
…
13
Terminologie : l’espace de travail
1. Cellule2. Plage de cellules3. Feuille4. Nom de la sélection5. Barre de formule6. Entête de colonne7. Entête de ligne
4 5
6
7
2
1
3
14
Terminologie : l’espace de travail
1. Cellule◦ Champ pouvant contenir du texte, des nombres, dates, pourcentages, etc…
Une cellule peut aussi contenir des formules de calculs. Une cellule est identifiée par un index de ligne, (nombre) et un index de colonne (lettre)
2. Plage de cellules◦ Matrice de cellules. Une plage de cellules est identifiée par un binôme
C1:C2, où C1 est la cellule haut gauche, et C2 la cellule bas droit.
3. Feuille◦ Une feuille de calcul est identifiée par un nom (qui apparait en bas de
l’espace de travail)
4. Nom de la sélection◦ Permet de nommer un cellule ou plage de cellules sélectionnées. Permet
aussi de sélectionner un cellule ou plage de cellule précédemment nommées
5. Barre de formule◦ Affiche le contenu littéral de la cellule (et non pas le résultat du calcul). Il est
possible de modifier le contenu depuis cette barre ou directement dans la cellule
6. Entête de colonne◦ Permet de sélectionner toute une colonne en cliquant dessus
7. Entête de ligne◦ Permet de sélectionner toute une ligne en cliquant dessus
15
SommaireApplications concrètesTerminologieLes fonctions
16
Les fonctions : introductionElles permettent d’effectuer des
calculs plus complexes que les opérations arithmétiques de base (+ - * / %)◦ Amortissement, recherche de valeur, partie entière, vrai/faux,
opérations ensemblistes, etc…
Le tableur dispose de la liste complète des fonctions qu’il gère
Elles suivent toutes le même format :
17
Les fonctions
◦ Nom de la fonction Cela identifie la fonction qui va être appelée.
Chaque fonction a un nom, et un comportement qui lui sont propres.
◦ Paramètres Ils permettent de …paramétrer… le comportement
de la fonction. Le type et le nombre de paramètre d’une fonction dépend de celle-ci.On ne peut pas les déduire, il faut les connaitre, ou consulter la documentation
◦ Valeur de retour Chaque fonction produit un résultat,
éventuellement dépendant des paramètres. On appelle ce résultat valeur de retour.
SI(condition ; valeur_si_vrai ; valeur_si_faux)
18
Les fonctions : dissection approximative
SI(condition ; valeur_si_vrai ; valeur_si_faux) {Analyse et test de la condition;Si condition = vrai
retourner valeur_si_vrai;Sinon
retourner valeur_si_faux;}
Algorithme
faux !
Cela sert juste à
faire
comprendre ce
qu’il
se passe.
19
Les fonctions : dissection approximative
RechercheV(Valeur_cherchée ; Matrice ; Index_Col; [Proche]) {
Pour chaque ligne de Matrice {si Cel(Col_1 ; n°_de_ligne) =
Valeur_Cherchéeretourner Cel(Index_Col ;
n°_de_ligne); }retourner faux
}Algorithme
faux !
Cela sert juste à
faire
comprendre ce
qu’il
se passe.
Pour aller plus loinUn paramètre entre crochet signifie, par convention qu’il est facultatif. Tous les paramètres optionnels se trouvent obligatoirement en fin de liste.Une valeur par défaut lui est attribuée automatiquement s’il n’est pas renseigné. On représente généralement cela de la façon suivante :
Fonction(… ; … ; [opt1] = xxx ; [opt2] = xxx)
20
SommaireApplications concrètesTerminologieLes fonctions
◦Les fonctions (avancé)
21
Les fonctions imbriquéesIl arrive que l’on ne connaisse
pas l’un des paramètres d’une fonction…
Comment traduire tout ça ..?
SI(Age < 24 et classe=L1 ou L2; 10% ; 0%)
SI(ET(AGE<24 ; OU(classe= ‘‘L1’’ ; classe = ‘‘L2’’)) ; 10% ; 0%)
Ce concept est trop compliqué pour être exprimé en un paramètre simple. Il faut alors le décomposer en plusieurs concepts simples (sur une feuille ou de tête) :
Age < 24 ET (classe= ‘‘L1’’ OU classe = ‘‘L2’’)Il faut maintenant organiser tout ceci, et le traduire en ‘Excel’…
Age < 24 ET (OU(classe= ‘‘L1’’ ; classe = ‘‘ L2’’) ET(AGE<24 ; OU(classe= ‘‘L1’’ ; classe = ‘‘L2’’))
22
Les fonctions imbriquées
Pour aller plus loin◦ Comment savoir s’il s’agit d’un « paramètre compliqué » ?
Un tableur ne comprendra un paramètre que s’il s’agit d’une opération arithmétique simple ( <, >, =, <>, vrai, faux, « Texte », Nombre, date, etc…)
◦ Pourtant on met des fonctions à la place d’opérations arithmétiques simples… Il faut garder à l’esprit que le tableur va résoudre les fonctions
en commençant par les plus imbriquées. Hors, la valeur de retour d’une fonction est nécessairement une opération arithmétique simple, il remplace donc durant la résolution la fonction par la valeur qu’elle retourne.
◦ Peut on imbriquer n’importe comment ? Non. Il faut s’assurer que le type de retour corresponde au type
du paramètre et que cela ait un sens. C’est la seule et unique contrainte.
Il est donc possible d’imbriquer autant de fonction que nécessaire, sur n’importe quels paramètres.
23
Les fonctions imbriquéesIl existe 2 façons de traiter ce genre de
problème :◦A l’arrache
On écrit la fonction comme si on connaissait tous les paramètres, et on décompose les paramètres l’un après l’autre (diapo précédente), quand le problème se présente
◦ Intelligemment On regarde au préalable tous les paramètres dont
on a besoin, et on les pré-calcule les uns après les autres (diapo précédente)
Les deux méthodes ont des avantages et inconvénient, libre à chacun de leur usage
24
Les fonctions imbriquéesImbriquer deux (ou plusieurs) fonctions
revient à calculer au préalable les paramètre compliqués dans des cellules intermédiaire, puis appeler la fonction « mère » en utilisant en paramètre les références à ces cellules.
Pour aller plus loin◦ Pourquoi imbriquer alors ?
Cela permet de ne pas polluer notre feuille avec des calculs intermédiaires qui n’ont pas de sens ni d’utilité. Si un calcul intermédiaire est utilisé à de nombreuses reprises, il est judicieux de le calculer dans une cellule de référence
25
SommaireApplications concrètesTerminologieLes fonctions
◦Les fonctions (avancé)◦Les fonctions financières
26
Les fonctions financièresQuelques fonctions à retenir
Nom Pourquoi ?
VA() Combien j’ai au début ?
VC() Combien j’aurai à la fin ?
VPM() Combien j’économise tous les mois ?
NPM() Pendant combien de temps ?
TAUX()
PRINCPER() Combien je rembourse ce mois-ci ?
INTPER() Combien je paye d’intérêt ce mois-ci ?
27
Les fonctions financièresPour ne pas se rater…
◦ Il convient de détecter le type de valeur que vous voulez trouver (ce qui vous donnera la formule à utiliser)
◦ Assurez-vous que les taux, npm, et vpm soient exprimés dans la même unité de temps (et que cette unité soit cohérente avec les données de l’exercice…)
Pour aller plus loin◦ Comment trouver l’unité de temps adéquate ?
Repérez dans l’énoncé toutes les indications temporelles C’est la période de rémunération qui fait foi (généralement mensuelle
quand elle n’est pas explicitée) Homogénéisez les taux, npm, et vpm dans cette unité Le résultat de votre fonction sera exprimé sur la même période, vous
devrez peut-être le ramener sur la période demandée Exemple : Pour une rémunération mensuelle à un taux X, en combien d’années remboursez vous un prêt à raison de Y€ par mois ? La période de référence pour le calcul est le mois. Il faudra donc diviser par 12 le npm pour obtenir un nombre d’années.
28
SommaireApplications concrètesTerminologieLes fonctions
◦Les fonctions (avancé)◦Les fonctions financières◦Les fonctions statistiques
29
Les fonctions statistiques
Un tableur met à disposition un ensemble de fonctions statistiques simples permettant d’éviter des calculs certaines pourraient même être calculées à la main :
MOYENNE(A1:A5) SOMME(A1:A5) / 5
30
Les fonctions statistiquesQuelques fonctions à
comprendreNom Pourquoi ?
MOYENNE calcule la moyenne d'une série de valeurs
MIN renvoie la plus petite valeur d'une série
MAX renvoie la plus grande valeur d'une série
RANG calcule le classement d'une valeur parmi une série de valeurs
MEDIANE renvoie le nombre qui se trouve au milieu d'une série de nombres
ECARTYPE évalue l'écart type en se basant sur un échantillon de valeurs
ECARTYPEP évalue l'écart type en se basant sur la population complète des valeurs
NBVAL compte les cellules non vides
NB compte uniquement les cellules avec nombre
NB.SI compte les cellules répondant à un critère
31
Les fonctions statistiquesLa fonction FREQUENCE
Retourne les effectifs de chaque classe (i.e. chaque intervalle) pour les valeurs données
Problème :◦On donne une matrice d’intervalles et
non un seul intervalle. Quel effectif retourne alors la fonction ?
FREQUENCE(VALEURS ; Matrice_dintervalles)
32
Les fonctions statistiques
Tous…
33
SommaireApplications concrètesTerminologieLes fonctions
◦Les fonctions financières◦Les fonctions statistiques◦Les fonctions matricielles
34
Les fonctions matriciellesUne fonction est dite matricielle
lorsqu’elle effectue des calculs de types matriciels◦Multiplier deux plages de cellules entre
elles◦Inverser ou transposer une matrice◦…
Pour les appliquer dans Excel, au lieu d’appuyer sur entrée, il faut appuyer sur ctrl + maj + entrée
35
Les fonctions matriciellesNous avons vu qu’une fonction
avait une valeur de retourNous avons vu que cette valeur
avait un « type simple »
Et pourtant◦Une fonction matricielle peut
retourner un tableau de valeurs (chacune d’elles sera de type simple)
36
Les fonctions matriciellesUne valeur de retour 1 cellule X valeurs de retour X cellules
Pour appliquer une fonction, il faut habituellement sélectionner une cellule.
Pour appliquer une fonction retournant un tableau de valeur, il faut sélectionner autant de cellules que n’en contient le tableau de retour
Une cellule = une valeur
37
Les fonctions matricielles
Pour aller plus loin◦ Toutes les cellules qui entrent en jeu dans une
formule matricielle sont liées, et il n’est pas possible de modifier la formule pour une seule de ces cellules
◦ http://www.excelabo.net/pasapas/matricielles
38
Les fonctions matricielles
Retour sur la fonction FREQUENCE()
39
Les fonctions statistiques
Tous…
40
Les fonctions statistiquesIl s’agit d’une fonction
matricielle, par opposition à celles vues jusqu’alors◦Elle ne s’utilise pas sur une cellule,
mais une plage de cellulesElle retourne donc l’effectif de chaque classe (+1) passée en second paramètre, sur autant de cellules qu’il n’y a de classes :
◦Retournera l’effectif des classes :]-inf;10] ]10; 20] ]20; 30] ]30; +inf[
FREQUENCE(PLAGE_DE_VALEURS ; {10 ; 20 ; 30} )
41
Les fonctions statistiquesProtocole
◦ On sélectionne n+1 cellules contigües d’une colonne(n=nombre d’items de la matrice d’intervalles)
◦ On commence à saisir la formule : =FREQUENCE(A1:A40;…
◦ On renseigne la borne sup de chaque intervalle/classe sous forme d’une matrice sans valider :=FREQUENCE(A1:A40;{10;20;30})
◦ On applique cette fonction matricielle sur la …matrice… sélectionné en appuyant sur :ctrl + maj + entrée
42
SommaireApplications concrètesTerminologieLes fonctions
◦Les fonctions (avancé)◦Les fonctions financières◦Les fonctions statistiques◦Les fonctions statistiques II
43
Rappels◦ Fiche pratique
Retours sur des notions importantes :http://qotsaupload.free.fr/seconde/statdesc/cours1.PDF
◦ Tendance Évolution globale de la série (souvent en version
anglophone : trend)C’est une notion assez naturelle : légère augmentation, forte diminution, stagnation, etc…
◦ Croissance Évolution d’une série sur une période (généralement en
pourcent) Si la croissance est stable sur chaque période, la série statistique étudiée est
de forme linéaire (évolution stable). Si l’évolution change (augmentation de la croissance, augmentation de la
décroissance, etc…), la série statistique est de forme exponentielle ou logarithmique
Les statistiques sont des estimations…
44
Les fonctions statistiques
Nom Pourquoi ?
TENDANCE(p1 ; p2 ; p3)[matricielle] Estime l’évolution d’une série à tendance linéaire p1 sur les périodes p3 (valeurs connues sur les périodes p2)
CROISSANCE(p1 ; p2 ; p3)[matricielle] Estime l’évolution exponentielle d’une série p1 sur les périodes p3 (valeurs connues sur les périodes p2)
DROITEREG(p1 ; p2 ; p3 ; p4) [matricielle] Calcule une droite affine (y=mx+b) représentant au mieux les valeurs p1 sur les périodes p2. La fonction retourne une matrice de la forme {m;b}
LOGREG(p1 ; p2 ; p3 ; p4)[matricielle] Calcule une courbe exponentielle (y=bm^x) représentant au mieux les valeurs p1 sur les périodes p2. La fonction retourne une matrice de la forme {m;b}
45
Comment appliquer ces fonctions ?Les fonctions TENDANCE() et DROITEREG()
sont utilisées sur les séries à tendance linéaire. ◦ On les utilise aussi lorsque l’on arrive pas
vraiment à déterminer la tendance de la série
Les fonction CROISSANCE() et LOGREG() sont utilisées lorsque l’évolution est à tendance exponentielle ou logarithmiquePour aller plus loin◦ Un bon moyen de déterminer la tendance d’une série est de
la représenter sous forme d’un graphe.
◦ Dans certains cas où l’on n’arrive pas vraiment à déterminer la forme de la croissance, dans le cas de l’évolution de la population mondiale par exemple, on peut appliquer les deux méthodes, et travailler sur les résultats des deux séries ainsi produites.
46
SommaireApplications concrètesTerminologieLes fonctionsLes bases de données
47
Les basesUne base de données est une agrégation
d’informations organisées◦ Sous forme de tableaux (90% du temps)
On parle de champs pour faire référence aux colonnes de ces tableaux
On parle d’enregistrements pour faire référence aux lignes
48
Manipuler une base de donnéesExcel dispose d’un ensemble de fonctions
pour manipuler les bases de données.Elles sont toutes de la forme :
Nom Pourquoi ?
BD________(p1;p2;p3) La fonction retourne un résultat (dépendant de la fonction utilisée) portant sur le champ p2 de la base p1 selon un ou des critères définis par p3
Autre fonction du TD :
SOUS.TOTAL() Retourne le sous total de la formule spécifiée en argument p1, appliquée sur le tableau p2. Le résultat retourné tient compte des filtres appliqués sur le tableau.
49
Comment faireNommer la plage
de cellule qui servira de base
Définir une zone pour les critères
Appliquer les formules souhaitées sur votre BD, en définissant et utilisant les critères nécessaires
