Introduction aux tableurs

1. Introduction aux tableurs Utilité, 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 * >> Le cours *(sauf le jour de l’examen)

6. Sommaire • Applications concrètes • A la maison • En entreprise • Terminologie • Les 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. Sommaire • Applications concrètes • Terminologie • Le logiciel • Le fichier • L’espace de travail • Les fonctions

10. Terminologie : menu du logiciel 4 3 5 • Ruban • Onglets • Barre d’outils Accès rapide • Titre du fichier • Autres commandes 2 1

11. Terminologie : menu du logiciel • Ruban • Il s’agit du menu principal. Le ruban change selon l’onglet sélectionné, et affiche les principales fonctionnalités • 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. • Barre d’outils Accès rapide • Personnalisable, cette barre permet de créer des raccourcis vers des actions (annuler, imprimer, nouveau fichier, etc. …) • 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…) • 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 = classeur

13. Terminologie : l’espace de travail • Cellule • Plage de cellules • Feuille • Nom de la sélection • Barre de formule • Entête de colonne • Entête de ligne 4 5 6 7 2 1 3

14. Terminologie : l’espace de travail • 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) • 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. • Feuille • Une feuille de calcul est identifiée par un nom (qui apparait en bas de l’espace de travail) • 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 • 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 • Entête de colonne • Permet de sélectionner toute une colonne en cliquant dessus • Entête de ligne • Permet de sélectionner toute une ligne en cliquant dessus

15. Sommaire • Applications concrètes • Terminologie • Les fonctions

16. 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 :

17. 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.

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ée retournerCel(Index_Col ; n°_de_ligne); } retourner faux } Pour aller plus loin Un 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) Algorithme faux ! Cela sert juste à faire comprendre ce qu’il se passe.

20. Sommaire • Applications concrètes • Terminologie • Les fonctions • Les fonctions (avancé)

21. Les fonctions imbriquées • Il 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%) 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’’OUclasse = ‘‘L2’’) Il faut maintenant organiser tout ceci, et le traduire en ‘Excel’… Age < 24ET(OU(classe=‘‘L1’’ ; classe = ‘‘ L2’’) ET(AGE<24 ; OU(classe=‘‘L1’’ ; classe = ‘‘L2’’)) SI(ET(AGE<24 ; OU(classe= ‘‘L1’’ ; classe = ‘‘L2’’)) ; 10% ; 0%)

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é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

24. 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

25. Sommaire • Applications concrètes • Terminologie • Les fonctions • Les fonctions (avancé) • Les fonctions financières

26. Les fonctions financières • Quelques fonctions à retenir

27. 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.

28. Sommaire • Applications concrètes • Terminologie • Les 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 statistiques • Quelques fonctions à comprendre

31. Les fonctions statistiques La 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. Sommaire • Applications concrètes • Terminologie • Les fonctions • Les fonctions financières • Les fonctions statistiques • Les fonctions matricielles

34. 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

35. 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)

36. Les fonctions matricielles • Une 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 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 cellulesElle retourne donc l’effectif de chaqueclasse(+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 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

42. Sommaire • Applications concrètes • Terminologie • Les 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

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 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.

46. Sommaire • Applications concrètes • Terminologie • Les fonctions • Les bases de données

47. 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

48. 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 :

49. 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