Transcript excel - Miniature

Introduction aux tableurs
Utilité, logiciels, fonctionnement…
[email protected]
1
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, …)
2
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
3
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
4
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 *
>> Le cours
*(sauf le jour de l’examen)
5
Sommaire

Applications concrètes
◦ A la maison
◦ En entreprise
Terminologie
 Les fonctions

6
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
7
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
8
Sommaire
Applications concrètes
 Terminologie

◦ Le logiciel
◦ Le fichier
◦ L’espace de travail

Les fonctions
9
Terminologie : menu du logiciel
4
3
5
2
1
1.
2.
3.
4.
5.
Ruban
Onglets
Barre d’outils Accès rapide
Titre du fichier
Autres commandes
10
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.
11
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 = classeur

Classeur
Feuille 1
Feuille 2
Feuille 3
…
Cellule
A1
Cellule
B1
…
Cellule
A1
Cellule
B1
…
Cellule
A1
Cellule
B1
…
Cellule
A2
Cellule
B2
…
Cellule
A2
Cellule
B2
…
Cellule
A2
Cellule
B2
…
…
…
…
…
…
…
…
…
…
12
Terminologie : l’espace de travail
4
1.
5
6
2.
7
2
1
3.
4.
5.
6.
7.
Cellule
Plage de cellules
Feuille
Nom de la sélection
Barre de formule
Entête de colonne
Entête de ligne
3
13
Terminologie : l’espace de travail
Cellule
1.
◦
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)
Plage de cellules
2.
◦
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.
Feuille
3.
◦
Une feuille de calcul est identifiée par un nom (qui apparait en bas de l’espace de travail)
Nom de la sélection
4.
◦
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
Barre de formule
5.
◦
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
Entête de colonne
6.
◦
Permet de sélectionner toute une colonne en cliquant dessus
Entête de ligne
7.
◦
Permet de sélectionner toute une ligne en cliquant dessus
14
Sommaire
Applications concrètes
 Terminologie
 Les fonctions

15
Les fonctions : introduction

Elles 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 :

16
Les fonctions
SI(condition ; valeur_si_vrai ; valeur_si_faux)
◦ 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.
17
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;
}
18
Les fonctions : dissection approximative
RechercheV(Valeur_cherchée ; Matrice ; Index_Col; [Proche]) {
Pour chaque ligne de Matrice {
Pour aller
plus loin
si Cel(Col_1 ; n°_de_ligne) = Valeur_Cherchée
Un paramètre entre crochet signifie, par convention qu’il est facultatif.
retourner
Cel(Index_Col
Tous les paramètres optionnels
se trouvent
obligatoirement en ;finn°_de_ligne);
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)
retourner faux
}
19
Sommaire
Applications concrètes
 Terminologie
 Les fonctions

◦ Les fonctions (avancé)
20
Les fonctions imbriquées

Il arrive que l’on ne connaisse pas l’un
des paramètres d’une fonction…
SI(Age < 24 et classe=L1 ou 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’’))
Comment traduire tout ça ..?
SI(ET(AGE<24 ; OU(classe= ‘‘L1’’ ; classe = ‘‘L2’’)) ; 10% ; 0%)
21
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.
22
Les fonctions imbriquées

Il 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
23
Les fonctions imbriquées

Imbriquer 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
24
Sommaire
Applications concrètes
 Terminologie
 Les fonctions

◦ Les fonctions (avancé)
◦ Les fonctions financières
25
Les fonctions financières

Quelques 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 ?
26
Les fonctions financières

Pour 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.
27
Sommaire
Applications concrètes
 Terminologie
 Les fonctions

◦ Les fonctions (avancé)
◦ Les fonctions financières
◦ Les fonctions statistiques
28
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
29
Les fonctions statistiques

Quelques fonctions à comprendre
Nom
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
30
Les fonctions statistiques
La fonction FREQUENCE
FREQUENCE(VALEURS ; Matrice_dintervalles)

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 ?
31
Les fonctions statistiques
Tous…
32
Sommaire
Applications concrètes
 Terminologie
 Les fonctions

◦ Les fonctions financières
◦ Les fonctions statistiques
◦ Les fonctions matricielles
33
Les fonctions matricielles

Une 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
34
Les fonctions matricielles
Nous avons vu qu’une fonction avait une
valeur de retour
 Nous 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)
35
Les fonctions matricielles
Une valeur de retour
 X valeurs de retour

1 cellule
X cellules
Une cellule = une valeur
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

36
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
37
Les fonctions matricielles

Retour sur la fonction FREQUENCE()
38
Les fonctions statistiques
Tous…
39
Les fonctions statistiques

Il s’agit d’une fonction matricielle, par
opposition à celles vues jusqu’alors
◦ Elle ne s’utilise pas sur une cellule, mais une
plage de cellules
Elle 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 :
FREQUENCE(PLAGE_DE_VALEURS ; {10 ; 20 ; 30} )
◦ Retournera l’effectif des classes :
]-inf;10] ]10; 20] ]20; 30] ]30; +inf[
40
Les fonctions statistiques

Protocole
◦ 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
41
Sommaire
Applications concrètes
 Terminologie
 Les fonctions

◦
◦
◦
◦
Les fonctions (avancé)
Les fonctions financières
Les fonctions statistiques
Les fonctions statistiques II
42
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…
43
Les fonctions statistiques
Nom
TENDANCE(p1 ; p2 ; p3)
CROISSANCE(p1 ; p2 ; p3)
Pourquoi ?
[matricielle]
Estime l’évolution d’une série à tendance linéaire p1 sur les périodes p3
(valeurs connues sur les périodes p2)
[matricielle]
Estime l’évolution exponentielle d’une série p1 sur les périodes p3 (valeurs
connues sur les périodes p2)
[matricielle]
DROITEREG(p1 ; p2 ; p3 ; p4) 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}
[matricielle]
LOGREG(p1 ; p2 ; p3 ; p4)
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}
44
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 logarithmique

Pour 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.
45
Sommaire
Applications concrètes
 Terminologie
 Les fonctions
 Les bases de données

46
Les bases

Une 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
47
Manipuler une base de données


Excel dispose d’un ensemble de fonctions pour
manipuler les bases de données.
Elles sont toutes de la forme :
Nom
BD________(p1;p2;p3)
Pourquoi ?
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.
48
Comment faire

Nommer 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
49