Introduction aux tableurs Utilité, logiciels, fonctionnement… 1 renaud.angles@redraiden.com

1

Introduction aux tableursUtilité, logiciels, fonctionnement…

renaud.angles@redraiden.com

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