Cours informatique S4 2021 PDF

Séance 1 : samedi 03 avril 2021 INFORMATIQUE DE GESTION (S4) PROFESSEUR : ABAOUBIDA MY ABDELJAMIL Plan du cours Partie 1 : Traitement de texte (MS Word) Séance 1 Paragraphes Les styles Séance 2 Mise en page Les tableaux et Equations mathémat...

Séance 1 : samedi 03 avril 2021 INFORMATIQUE DE GESTION (S4) PROFESSEUR : ABAOUBIDA MY ABDELJAMIL Plan du cours Partie 1 : Traitement de texte (MS Word) Séance 1 Paragraphes Les styles Séance 2 Mise en page Les tableaux et Equations mathématiques Les références et bibliographie Table des matières, liste des figures, liste des tableaux et index 3 Plan du cours Partie 2 : Traitement des tableaux de données (MS Excel) Composition d’un classeur Excel Manipulation des cellules Liaison entre feuilles et entre classeurs Les fonctions (Si, RechercheV, RechercheH) Les graphiques Les tableaux croisés dynamiques (TCD) Créer et gérer des listes : Trier, Filtre, Sous-totaux… Champs d’application Excel et Algèbre linéaire Excel et Mathématiques financières Excel et Statistique Excel et Comptabilité Excel et Probabilité Excel et Microéconomie Introduction 1. Contextualisation Pourquoi cette matière pour un étudiant en sciences économiques et gestion ? 2. Mobilisation des concepts Informatique de gestion, de quoi parle-t-on ? 3. Informatique de gestion en pratique Comment procéder ? 1.Pourquoi cette matière pour un étudiant en sciences économiques et gestion ? Besoin d’établir certaines traces par rapport au cursus universitaire - Rédaction du mémoire de fin d’étude ; - Formulation d’un rapport de stage, d’une Demande; CV, PV, etc. - Résolution des exercices (Mathématiques financières, Probabilité, Recherche Opérationnelle, Statistique) - Exposé oral 7 1.Pourquoi cette matière pour un étudiant en sciences économiques et gestion ? L’insertion professionnelle Choix de Carrière - Entrepreneur, Manager ou Responsable administratif - Exemple de Traces : Business plan, Business plan, Tableaux de Brod, Rapport d’activités, schémas, organigrammes - Salarié - Exemple de Traces: Classification, Tri et Filtre des informations 8 Pour l’entreprise Objectif Création de la valeur Minimisation des coûts et Maximisation du profit Enjeu Être informée sur les nouvelles tendances de son environnement Préférences des consommateurs; Politique publique; Evolution du pouvoir d'achat … Big data Prise de décision : vitesse d’exécution ; choisir l’information pertinente Instrument Système d’information robuste, dynamique et stable Flux de données Flux interne Flux externe 9 Quoi ? L’informatique de gestion Choix pragmatique Traitement de texte (MS Word) Traitement de données (MS Excel) Présentation des résultats (PowerPoint) Nécessaires mais insuffisants Barème d’évaluation de la matière Elément Nature Note Pourcentage Contrôle Questions à choix.. /4 20% continu multiple QCM Travaux à rendre TAR.. /2 10% Examen final.. / 14 70% Contrôle continu Objet : QCM ou Travail à rendre QCM : il Consiste à répondre en ligne, à la fin de chaque séance, à cinq (5) questions à choix multiple TAR : Préparation à domicile d’un travail pratique sur Word ou Excel, l’enregister-sous NOM_PRENOM. Méthode d’envoi : Accéder à la plate-forme : CODE : abaoubida http://ecampus- fsjes.uca.ma/course/view.php?id=652  Envoyer un message vide à : [email protected] Partie 1 : Traitement de texte (MS Word) 14 Contextualisation : Revue électronique Faire une simulation en ligne Réussir la publication d’un article dépend de la maitrise des outils suivants sur MS Word I. La mise en forme d’un texte II. Les styles III. La mise en page IV. Insertion d'objets dans le corps du texte a. Les tableaux b. Les Equations mathématiques c. Les références et bibliographie d. Table des matières, liste des figures, liste des tableaux et index Contextualisation de MS Word La majorité des utilisateurs de traitement de texte passent leur temps à mettre en forme leur document ligne par ligne et page par page par méconnaissance des fonctionnalités de structuration de texte qui existent dans le logiciel MS Word Objectif Réussir la publication d’un article scientifique Saisir et organiser efficacement le contenu du document à produire, la mise en forme qui suivra prendra alors peu de temps. Recommandation Lorsque vous vous exercez ne regardez pas le clavier, n'y jetez même pas un coup d'œil! Regardez uniquement l'écran. La position de base peut être facilement trouvée sans regarder le clavier - Sentez les bosses sur le "F" et le "J".  2.Barre d’outils accès rapide 1.Barre de Titre 3.Onglets 4.Ruban 5.Fenêtre Édition 6.Barre de défilement 7.Barre d’état 8.Boutons 9.Zoom d’affichage 1. Barre de Titre : affiche le nom de fichier du document en cours d’édition et le nom du logiciel utilisé. Elle comporte également les boutons standard Réduire, Restaurer et Fermer. 2. Barre d’outils accès rapide : les commandes fréquemment utilisées, telles que Enregistrer, Annuler et Rétablir sont rassemblées ici. À la fin de la barre d’outils Accès rapide se trouve un menu déroulant d’où vous pouvez ajouter d’autres commandes couramment utilisées ou nécessaires. 3. Onglet [Fichier] : les commandes permettant d’agir sur le document proprement dit plutôt que sur son contenu, telles que Nouveau, Ouvrir, Enregistrer sous, Imprimer et Fermer. 4. Ruban : les commandes nécessaires à votre travail sont rassemblées ici. L’apparence du Ruban change en fonction de la taille de l’écran. Word compresse le Ruban en modifiant la disposition des contrôles pour s’adapter aux plus petits écrans. 5. Fenêtre Édition : affiche le contenu du document en cours de modification. 6. Barre de défilement : vous permet de modifier la position d’affichage du document en cours de modification. 7. Barre d’état : affiche des informations concernant le document en cours de modification. 8. Boutons d’affichage : vous permettent de modifier le mode d’affichage du document en cours de modification en fonction de vos besoins. 9. Zoom: vous permet de modifier les paramètres de zoom du document en cours de modification. I. La mise en forme d’un texte A. La notion de paragraphe La structure de base utilisée dans un traitement de texte est le paragraphe. Un paragraphe est un ensemble de caractères qui se termine par une marque de paragraphe « ¶ » et qui n'en contient pas d'autre (car la marque de fin de paragraphe fait partie du paragraphe). Cliquez sur le bouton ¶ du menu « Paragraphe » afin d'afficher les caractères non imprimables dont font partie les marques de paragraphe : cela vous permet de voir tous vos paragraphes et ainsi d’avoir une vision globale de votre document. Le menu paragraphe présente différents outils dont le symbole paragraphe. B. Aligner et justifier un texte Lorsque vous consulter un courrier vous remarquez que tous les paragraphes ne sont pas alignés de la même manière. En effet, l’alignement s’effectue par paragraphe. Voici quelques exemples des principales mise en forme et alignements des paragraphes : voir le document ci-dessous. Le 1er paragraphe à gauche a été aligné à droite grâce à la touche Le paragraphe à droite a été aligné à gauche grâce à la touche Le paragraphe « A Monsieur … » a été centré grâce à la touche Le paragraphe « Actuellement … » a été justifié grâce à la touche Remarque : Quand un paragraphe est justifié, le premier mot est aligné à gauche et le dernier mot est aligné à droite. Pour cela Word modifie l’espace entre les mots. C. Ajouter des retraits Pour créer un nouveau paragraphe avec une première ligne décalée ou définir le niveau hiérarchique des paragraphes, vous pouvez ajouter des retraits par rapport à la marge de droite ou de gauche. Sélectionner un ou plusieurs paragraphes puis cliquez dans l’onglet ACCUEIL puis PARAGRAPHE puis la touche AUGMENTER ou DIMINUER le retrait. REMARQUE Cela crée un décalage à droite ou à gauche qui correspond à une tabulation standard soit 1,25 cm. ATTENTION Un paragraphe ne peut être décalé vers la gauche que s’il y a déjà eu un retrait préalable vers la droite. De plus vous pouvez effectuer directement les retraits via la touche tabulation de votre clavier. MODIFIER LES MARGES AVEC LA REGLE A quoi sert la règle ? Elle permet de définir le retrait de gauche et de droite ainsi que le retrait de première ligne, et ce, indépendamment des alignements vus précédemment. Pour faire apparaître la règle il vous suffit de cliquer sur le bouton qui se trouve au-dessus de la barre de défilement vertical. Ce symbole qui se trouve sur la règle est, en réalité, l’association de 3 éléments pour modifier les retraits de gauche. Le premier triangle avec la pointe vers le bas sert à définir le retrait pour la première ligne. Le triangle avec la pointe vers le haut permet de définir le retrait des autres lignes, à l’exception de la première ligne. Et enfin le rectangle permet de définir le retrait de toutes les lignes d’un paragraphe. REMARQUE Lorsque vous appliquerez des retraits vous devrez sélectionner un ou plusieurs paragraphes puis ensuite modifier les retraits grâce à la règle. E. Utiliser les tabulations Les tabulations permettent de démarrer un texte à une position précise à partir de la marge de gauche quand on utilise la touche TAB, on crée une tabulation via le clavier mais on peut aussi les établir de façon automatique grâce à la règle de tabulation. les tabulations faites par la touche TAB, sont des tabulations par défaut, elles sont placés tous les 1,25 cm. Par exemple si vous appuyez deux fois sur la touche TAB votre texte commencera à 2,5cm de la marge de gauche. Dans les caractères non imprimables les tabulations sont indiquées par une petite flèche noire. Il existe trois types de tabulations qui se nomment « taquet de tabulation » : Ce bouton correspond à une tabulation à gauche (le texte s’écrit vers la gauche à partir de la tabulation). Ce bouton permet une tabulation à droite (le texte s’écrit vers la droite à partir de la tabulation). Ce bouton permet une tabulation centrée (le texte est centré par rapport à la tabulation). Informatique de gestion (S4) Séance 2 II. Notion de Style Qu’est-ce qu’un style ? C’est une combinaison de mises en forme de paragraphes et de caractères. II. Notion de Style Par exemple, dans un document si vous voulez que chacun de vos titres soient en « gras », police 12, et en Arial, alors il suffit de le programmer. Mais attention, vous pouvez le faire une fois votre texte écrit mais cela sera un peu plus fastidieux, l’idéal est d’utiliser les styles pendant la création de votre document. En effet, chacun des paragraphes utilisent un style. Définir les styles Pour bien utiliser les styles, vous devez les définir pour chaque paragraphe de votre document. Voici les styles prédéfinis par défaut : Au départ, vous devez cliquer sur les différents paragraphes et y appliquer un style que vous aurez choisi. L’essentiel est de définir le nom du style de chacun des paragraphes de votre document. Par exemple, lors de la rédaction, si vous souhaitez appliquer une police définie à chaque titre sans le faire à chaque fois manuellement c’est possible.  Séance 2 : MS Word 1.La mise en page d’un document Word Notion de Section Marges/Taille/Colonne En-tête/Pied de Page 2.Insertion d'objets dans le corps du texte Habiller une image avec un texte; Tableaux et Equation mathématique 3.Notes de bas de page Création d'un sommaire automatique Normes bibliographiques de L’American Psychological Association (APA) logiciel ZOTERO Rappel de quelques raccourcis utiles : RACCOURCIS CLAVIER WORD : LES ACTIONS COURANTES CTRL + C : copier le texte sélectionné CTRL + X : couper le texte sélectionné CTRL + V : coller du texte CTRL + H : rechercher et remplacer du texte CTRL + Z : annuler la dernière action CTRL + Y : rétablir la dernière action Ctrl + F : Ouvre la boite de dialogue Rechercher ALT + CTRL + I : aperçu avant impression CTRL + P : impression d’un document CTRL + K : permet de créer un lien hypertexte lorsqu’un texte est sélectionné Échap : stopper une action CRÉER ET ENREGISTRER UN DOCUMENT CTRL + N : créer un nouveau document CTRL + O : ouvrir un document CTRL + S : enregistrer un document CTRL + W : fermer un document III. La mise en page d’un document Word Voici la partie où l'on va pouvoir modifier l'apparence du document. Si vous avez déjà un texte structuré, la présentation de votre document vous prendra quelques minutes. Sinon elle pourra prendre plusieurs heures avec un résultat souvent loin d'être irréprochable et facilement modifiable. Notion de Section Tout comme le paragraphe est la base de la structuration de texte, la section est la base de la structuration de la mise en page : Word applique une mise en page à l'intérieur d'une section. Mais qu'est-ce qu'une section ? C'est un ensemble de paragraphes situés entre deux marques de saut de section. Pour changer explicitement de section il faut introduire un « saut de section ». Il en existe de deux sortes, le « saut de section continu » et le « saut de section/Page suivante ». Ces deux sauts de section se trouvent dans l'onglet «Mise en Page», menu «Mise en page», icône «Sauts de page». Marges/Taille/Colonne Le menu « Mise en page » de l'onglet « Mise en page » permet de modifier les nombreux attributs de la mise en page (Marges, orientation de la page, nombre de colonnes, numérotation des lignes,...), tout en précisant si les modifications doivent être appliquées à l'ensemble du document ou seulement à la section en cours.  En-tête/Pied de Page Les en-têtes et pied de page sont des textes courts qui ne font pas partie du texte principal et qui sont répétés en haut et en bas de chaque page lors de l’impression. Quelle informations à répéter ? Ils peuvent proposer toutes sortes d’informations : numéros de page, date et heure, titre du document, nom de la personne ou du service qui a rédigé le texte, ou encore le logo de la société, etc… Listes et puces permettent de mettre en avant des points particuliers : à utiliser avec parcimonie. Pensez à utiliser le correcteur orthographique et grammatical dans l’onglet «Révision». Application Création d'un sommaire automatique Une fois votre contenu hiérarchisé vous pouvez créer où bon vous semble un Sommaire correspondant à la hiérarchisation visible en MODE PLAN. Pour accéder au mode plan allez dans l’onglet « Affichage » puis cliquez sur «Plan». Ce mode vous permet de visualiser l’ensemble de votre document sous forme de plan. Pour la création du sommaire : Rendez-vous dans l'onglet « Référence », menu « Table des matières », icône « Table des matières ». Remarque importante le sommaire ainsi créé correspond au sommaire de votre document au moment où vous créez la table des matières. Le traitement de texte stocke en effet le sommaire dans un champ mais aussi un code appelé code de champ destiné à pouvoir actualiser le sommaire. Cette mise à jour n'est pas automatique. Si vous modifiez ou rajoutez des niveaux hiérarchiques dans le document, Il faudra penser à mettre à jour le sommaire avant d'imprimer le document en cliquant sur l'icône « mettre à jour la table » dans le menu utilisé précédemment. D'une manière générale tous les champs pouvant être affectés par des modifications du contenu du document doivent être mis à jour. Pour cela positionnez le curseur dans le corps du texte et sélectionner l'intégralité du document en utilisant le raccourci clavier Ctrl+A. Faites un clic-droit dans la sélection et choisissez « Mettre à jour les champs» dans le menu qui apparaît. Application INFORMATIQUE DE GESTION Séance 3 PROF. ABAOUBIDA MY ABDELJAMIL PLAN 1. Composition d’un classeur Excel 2. Manipulation des cellules (Mathématiques financières; Algèbre linéaire) 3. Liaison entre feuilles et entre classeurs 4. Les fonctions (SOMME, MOYENNE, Si, RechercheV, RechercheH, INDIRECT, INDEX,EQUIV, RANG…) (Comptabilité générale) 5. Les graphiques (Statistique; Probabilité) 6. Les tableaux croisés dynamiques (TCD) 7. Créer et gérer des listes : Trier, Filtre, Sous-totaux CHAMPS D’APPLICATION 1. Mathématiques financières 2. Algèbre linéaire 3. Comptabilité 4. Statistique 5. Probabilité 6. Microéconomie INTRODUCTION La plus grande force d’Excel, c’est son actualisation automatique des données. Pour ouvrir Excel, cliquez sur le bouton Démarrer puis cliquez sur EXCEL. Les classeurs sont les documents d’Excel. Ils sont constitués de feuilles de calcul. Présentation de l’interface Excel Menu Démarrer/application/Excel Double cliquer sur un fichier Excel = fichier dont l’extension est.xlsx Barre de Menu Barre d’outils Barre de formule Feuille de calcul Sélection des feuilles de calcul Chaque feuille est accessible par des onglets en bas du classeur. Les feuilles, elles, sont constituées de cases appelées « cellules », réparties sur 1048576 lignes et 16384 colonnes. Les colonnes sont numérotées avec des lettres, de A jusqu’aux lettres XFD. Chaque cellule porte le nom de l’intersection de la ligne et de la colonne où elle se trouve. La cellule C4, par exemple, se situe donc à l’intersection de la troisième colonne et de la quatrième ligne. La cellule : format 10 Cellule: format alphanumérique 11 Cellule: format numérique 12 BARRE DE FORMULE La barre de formule, située au-dessus de la feuille de calcul, permet de voir et de modifier le contenu de la cellule active (la cellule sélectionnée). En effet, si une cellule contient des calculs, elle n’affiche dans la feuille que le résultat. Seule la barre de formule permet de voir ce qu’elle contient réellement. Dans l’exemple la cellule A1 affiche le résultat d’une addition. La barre de formule, elle, affiche le calcul effectué. La zone située à gauche de la barre de formule, appelée « Zone Nom », affiche le nom de la cellule sélectionnée. I. SAISIR DES DONNEES Ayant passé le stade de la découverte et bien intégré, vous êtes prêt à mettre en pratique les premières manipulations 1.1. SELECTIONNER DES CELLULES Pour sélectionner une cellule, cliquez là. Elle devient la cellule active. La cellule est entourée d’un cadre. Le numéro de la ligne et de la lettre de la colonne sont affichés avec un fond orange. La « Zone Nom » à gauche de la barre de formule affiche les références de la cellule, par exemple A1. Toutes les données que vous allez maintenant saisir au clavier le seront dans la cellule active. Flèche Gauche Sélectionne la cellule de la colonne précédente. Flèche Droite Sélectionne la cellule de la colonne suivante. Flèche Haut Sélectionne la cellule de la ligne précédente. Flèche Bas Sélectionne la cellule de la ligne suivante. Début Sélectionne la première cellule de la ligne. CTRL + Début Sélectionne la cellule A1. CTRL + Fin Sélectionne la dernière cellule contenant les données. SELECTIONNER PLUSIEURS CELLULES La sélection de plusieurs cellules permet d’appliquer des modifications en une seule fois (suppression, mise en forme, etc.). On appelle cette sélection, une « plage de cellules » Cliquez la cellule en haut à gauche des cellules à sélectionner. Faites glisser jusqu’à la cellule en bas à droite. Toutes les cellules dans le rectangle délimité par la cellule en haut à gauche et en bas à droite sont sélectionnées. La cellule active reste avec un fond blanc. C’est cette cellule qui recevra les prochaines données tapées au clavier. SELECTIONNER DES PLAGES NON CONTIGUS Répétez les deux premières consignes de l’étape précédente, pour sélectionner la première plage. Maintenez enfoncé la touche CTRL. Sélectionner une autre plage. Relâcher la touche CTRL. SELECTIONNER DES LIGNES ET DES COLONNES Les deux plages sont sélectionnées en même temps, la cellule active reste avec un fond blanc Les lettres et les chiffres des colonnes et des lignes permettent de les sélectionner. Pour sélectionner une colonne ou une ligne cliquez sa lettre ou son chiffre. Pour sélectionner plusieurs colonne ou plusieurs ligne cliquez la lettre de la première colonne ou le chiffre de la première ligne, puis faite glisser jusqu’à la dernière colonne ou à la dernière ligne. Comme pour les cellules, vous pouvez sélectionner des colonnes et des lignes non contiguës : pour cela, cliquez la première colonne ou la première ligne, puis maintenez enfoncé la touche CTRL puis cliquez les autres colonnes ou les autres lignes. 1.2. SAISIR DES TEXTES ET DES NOMBRES Chaque cellule accepte un nombre ou un texte. Excel analyse ce que vous tapez pour déterminer le type de saisie. SAISIR UN TEXTE Cliquez la cellule qui doit contenir le texte. Tapez le texte au clavier. Appuyer sur la touche Entrée pour valider. Le texte est placé à gauche dans la cellule. Remarque Si le texte est trop long il déborde sur la cellule de droite. Si la cellule de droite est déjà occupée alors le texte est tronqué. SAISIR UN NOMBRE Cliquez la cellule qui doit contenir le nombre au clavier. Tapez le nombre au clavier. Le nombre est placé à droite dans la cellule Si la cellule est trop petite, des ##### remplacent le nombre. Attention Si le nombre est placé à gauche c’est qu’il contient des caractères que le logiciel ne reconnait pas (un point «. » à la place d’une virgule « , »). La saisie est alors considérée comme un texte et non un nombre.  2. REALISER DES CALCULS COMPRENDRE LES FORMULES ET LES FONCTIONS Les formules et les fonctions démontrent toute la puissance de votre tableur. Qu’est-ce qu’une formule ? Une formule est un ensemble d’opérations que l’on peut effectuer dans les cellules d’une feuille de calcul. Dès que vous tapez le signe égal dans une cellule, Excel considère que vous saisissez une formule. Eléments d’une formule Après le signe égal, une formule est composée de cinq types d’éléments : Opérateurs Valeurs numériques et chaînes de caractères Référence de cellules Parenthèses Fonctions Opérateurs : ils permettent d’appliquer des opérations entre des valeurs (addition, soustraction…etc. Valeurs numériques et chaînes de caractères : ce sont des valeurs littérales sous forme de nombre ou de caractères. Référence de cellules : elles désignent d’autres cellules contenant des valeurs numériques ou des chaînes de caractères. Parenthèses : elles contrôlent l’ordre d’évaluation des calculs. Fonctions : elles permettent d’effectuer des opérations plus sophistiquées que les opérateurs habituels. C’est l’ensemble de ces éléments qui constituent des formules. Références à une cellule C’est la référence la plus simple. Elle est composée de la lettre de la colonne et du numéro de ligne. Exemple D3 Référence à une plage de cellules Une plage de cellules est un ensemble de cellule délimité par la référence et la cellule en haut à gauche et de la cellule en bas à droite. Ces deux références sont séparées par deux points. Exemple : B2:D4  31/05/2021 INFORMATIQUE DE GESTION MS EXCEL PROF. ABAOUBIDA MY ABDELJAMIL 1 PLAN 1. Composition d’un classeur Excel 2. Manipulation des cellules (Mathématiques financières; Algèbre linéaire) 3. Liaison entre feuilles et entre classeurs 4. Les fonctions (SOMME, MOYENNE, Si, RechercheV, RechercheH, INDIRECT, INDEX,EQUIV, RANG…) (Comptabilité générale) 5. Les graphiques (Statistique; Probabilité) 6. Les tableaux croisés dynamiques (TCD) 7. Créer et gérer des listes : Trier, Filtre, Sous-totaux 2 CHAMPS D’APPLICATION 1. Mathématiques financières 2. Algèbre linéaire 3. Comptabilité 4. Statistique 5. Probabilité 6. Microéconomie 3 1  31/05/2021 INTRODUCTION La plus grande force d’Excel, c’est son actualisation automatique des données. 4 Pour ouvrir Excel, cliquez sur le bouton Démarrer puis cliquez sur EXCEL. Les classeurs sont les documents d’Excel. Ils sont constitués de feuilles de calcul. 5 Présentation de l’interface Excel Menu Démarrer/application/Excel Double cliquer sur un fichier Excel = fichier dont l’extension est.xlsx Barre de Menu Barre d’outils Barre de formule Feuille de calcul Sélection des feuilles de calcul 6 2  31/05/2021 Chaque feuille est accessible par des onglets en bas du classeur. Les feuilles, elles, sont constituées de cases appelées « cellules », réparties sur 1048576 lignes et 16384 colonnes. Les colonnes sont numérotées avec des lettres, de A jusqu’aux lettres XFD. 7 Chaque cellule porte le nom de l’intersection de la ligne et de la colonne où elle se trouve. La cellule C4, par exemple, se situe donc à l’intersection de la troisième colonne et de la quatrième ligne. 8 9 3  31/05/2021 La cellule : format 10 10 Cellule: format alphanumérique 11 11 Cellule: format numérique 12 12 4  31/05/2021 BARRE DE FORMULE La barre de formule, située au-dessus de la feuille de calcul, permet de voir et de modifier le contenu de la cellule active (la cellule sélectionnée). 13 En effet, si une cellule contient des calculs, elle n’affiche dans la feuille que le résultat. Seule la barre de formule permet de voir ce qu’elle contient réellement. 14 Dans l’exemple la cellule A1 affiche le résultat d’une addition. La barre de formule, elle, affiche le calcul effectué. La zone située à gauche de la barre de formule, appelée « Zone Nom », affiche le nom de la cellule sélectionnée. 15 5  31/05/2021 I. SAISIR DES DONNEES Ayant passé le stade de la découverte et bien intégré, vous êtes prêt à mettre en pratique les premières manipulations 16 1.1. SELECTIONNER DES CELLULES Pour sélectionner une cellule, cliquez là. Elle devient la cellule active. La cellule est entourée d’un cadre. Le numéro de la ligne et de la lettre de la colonne sont affichés avec un fond orange. 17 La « Zone Nom » à gauche de la barre de formule affiche les références de la cellule, par exemple A1. Toutes les données que vous allez maintenant saisir au clavier le seront dans la cellule active. 18 6  31/05/2021 Flèche Gauche Sélectionne la cellule de la colonne précédente. Flèche Droite Sélectionne la cellule de la colonne suivante. Flèche Haut Sélectionne la cellule de la ligne précédente. Flèche Bas Sélectionne la cellule de la ligne suivante. Début Sélectionne la première cellule de la ligne. CTRL + Début Sélectionne la cellule A1. CTRL + Fin Sélectionne la dernière cellule contenant les données. 19 SELECTIONNER PLUSIEURS CELLULES La sélection de plusieurs cellules permet d’appliquer des modifications en une seule fois (suppression, mise en forme, etc.). On appelle cette sélection, une « plage de cellules » 20 Cliquez la cellule en haut à gauche des cellules à sélectionner. Faites glisser jusqu’à la cellule en bas à droite. 21 7  31/05/2021 Toutes les cellules dans le rectangle délimité par la cellule en haut à gauche et en bas à droite sont sélectionnées. La cellule active reste avec un fond blanc. C’est cette cellule qui recevra les prochaines données tapées au clavier. 22 SELECTIONNER DES PLAGES NON CONTIGUS Répétez les deux premières consignes de l’étape précédente, pour sélectionner la première plage. Maintenez enfoncé la touche CTRL. Sélectionner une autre plage. Relâcher la touche CTRL. 23 SELECTIONNER DES LIGNES ET DES COLONNES Les deux plages sont sélectionnées en même temps, la cellule active reste avec un fond blanc Les lettres et les chiffres des colonnes et des lignes permettent de les sélectionner. Pour sélectionner une colonne ou une ligne cliquez sa lettre ou son chiffre. 24 8  31/05/2021 Pour sélectionner plusieurs colonne ou plusieurs ligne cliquez la lettre de la première colonne ou le chiffre de la première ligne, puis faite glisser jusqu’à la dernière colonne ou à la dernière ligne. 25 Comme pour les cellules, vous pouvez sélectionner des colonnes et des lignes non contiguës : pour cela, cliquez la première colonne ou la première ligne, puis maintenez enfoncé la touche CTRL puis cliquez les autres colonnes ou les autres lignes. 26 27 9  31/05/2021 1.2. SAISIR DES TEXTES ET DES NOMBRES Chaque cellule accepte un nombre ou un texte. Excel analyse ce que vous tapez pour déterminer le type de saisie. 28 SAISIR UN TEXTE Cliquez la cellule qui doit contenir le texte. Tapez le texte au clavier. Appuyer sur la touche Entrée pour valider. Le texte est placé à gauche dans la cellule. 29 Remarque Si le texte est trop long il déborde sur la cellule de droite. Si la cellule de droite est déjà occupée alors le texte est tronqué. 30 10  31/05/2021 SAISIR UN NOMBRE Cliquez la cellule qui doit contenir le nombre au clavier. Tapez le nombre au clavier. Le nombre est placé à droite dans la cellule Si la cellule est trop petite, des ##### remplacent le nombre. 31 Attention Si le nombre est placé à gauche c’est qu’il contient des caractères que le logiciel ne reconnait pas (un point «. » à la place d’une virgule « , »). La saisie est alors considérée comme un texte et non un nombre. 32 2. REALISER DES CALCULS COMPRENDRE LES FORMULES ET LES FONCTIONS Les formules et les fonctions démontrent toute la puissance de votre tableur. 33 11  31/05/2021 Qu’est-ce qu’une formule ? Une formule est un ensemble d’opérations que l’on peut effectuer dans les cellules d’une feuille de calcul. Dès que vous tapez le signe égal dans une cellule, Excel considère que vous saisissez une formule. 34 Eléments d’une formule Après le signe égal, une formule est composée de cinq types d’éléments : Opérateurs Valeurs numériques et chaînes de caractères Référence de cellules Parenthèses Fonctions 35 Opérateurs : ils permettent d’appliquer des opérations entre des valeurs (addition, soustraction…etc. Valeurs numériques et chaînes de caractères : ce sont des valeurs littérales sous forme de nombre ou de caractères. 36 12  31/05/2021 Référence de cellules : elles désignent d’autres cellules contenant des valeurs numériques ou des chaînes de caractères. Parenthèses : elles contrôlent l’ordre d’évaluation des calculs. Fonctions : elles permettent d’effectuer des opérations plus sophistiquées que les opérateurs habituels. C’est l’ensemble de ces éléments qui constituent des formules. 37 Références à une cellule C’est la référence la plus simple. Elle est composée de la lettre de la colonne et du numéro de ligne. Exemple D3 38 Référence à une plage de cellules Une plage de cellules est un ensemble de cellule délimité par la référence et la cellule en haut à gauche et de la cellule en bas à droite. Ces deux références sont séparées par deux points. Exemple : B2:D4 39 13  31/05/2021 Qu’est-ce qu’une fonction ? Une fonction est un mot-clé qui permet d’effectuer des calculs plus complexes qu’avec les quatre opérations habituelles (addition, soustraction, etc.). 40 SAISIR DES FORMULES Pour réaliser des calculs, vous devez saisir des formules. C’est dernières doivent comporter le signe égal pour distinguer les nombres et les textes. Elles contiennent des nombres et des références à d’autres cellules. 41 Cliquez la cellule qui doit recevoir la formule. Tapez le signe =. Tapez les nombres et / ou les références des cellules. Appuyez sur Entrée Le résultat s’affiche dans la cellule. La formule s’affiche dans la barre de formule. 42 14  31/05/2021 Qu’est-ce qu’une formule ? Nom de la cellule Formule de la cellule Résultat / évaluation de la formule 43 Syntaxe d’une formule Opérateur d’affectation 44 Syntaxe d’une formule Opérandes (données à traiter) Références aux cellules Plages cellules Constantes 45 15  31/05/2021 Syntaxe d’une formule (2) Opérateurs (instructions) Opérateurs de calcul Fonctions 46 Les formules - références Référence absolue = utilisation des coordonnées absolues par rapport à l’origine de la feuille : $lettre_de_colonne$numéro_de_ligne ex: $B$3 Référence relative = utilisation des coordonnées par rapport à la cellule qui contient la formule (cellule de référence) : la position relative est masquée, on visualise une référence absolue sans le signe $ , ex: A8. !!! Attention: lorsqu’on copie une cellule contenant une référence relative, c’est la position relative qui est copiée. Référence mixte = mélange d’une référence absolue et relative ex: B$3 Référence nommée = un nom est spécifiquement donné à une cellule et les formules peuvent faire référence directement à ce nom 47 Exemple Problème ? 48 16  31/05/2021 Exemple Formule en référence relative 49 Exemple On étend la cellule sur les autres cellules. 50 Exemple Erreur ! La référence concernant la taxe est aussi descendue de deux cellules. 51 17  31/05/2021 Exemple Étape 1 : On renomme la cellule pour la rendre « absolue » 52 Exemple = B2 + B2 * Taxe Étape 2 : On tape la formule correspondante au calcul. Étape 3 : On étend la cellule sur les autres cellules. Note : On aurait put utiliser la référence absolue $C$6 53 Application 1 On cherche à obtenir le tableau ci-dessous en ne saisissant qu’une seule formule 54 54 18  31/05/2021 Opérateurs classiques Arithmétiques : + Addition 3+4 ou A3 + B5 - Soustraction 2–1 ou A4 – B7 * Multiplication 3*4 ou A5 * B8 / Division 10 / 5 ou A1 / B7 ^ Puissance 2 ^ 3 (2*2*2) ou A1 ^ B7 55 Opérateurs classiques De comparaison : < et > Inférieur et Supérieur = Inférieur ou égal et Supérieur ou égal = et Égal et Différent (non égal) Exemple: A3 >= 100 56 Opérateurs classiques De texte : & Concaténation 57 19  31/05/2021 Les erreurs de calcul ##### : Entrer une date sous forme de XX mois XXXX et réduire la largeur de la colonne ! #DIV/0 : Diviser une valeur par la valeur d’une cellule vide (ou par 0) ! #NOM : Faire Somme( : xxxx) ! #NOMBRE : Faire 1000 1000 ! (dépassement de capacité) #VALEUR : Essayer de calculer la valeur absolue d’un argument de type texte ! 58/48 58 Excel et mathématiques financières 59 Escompte à Intérêts composés Définition et formules Lorsque l’échéance d’un effet de commerce dépasse un an le banquier utilise les intérêts composés pour l’escompter. La valeur actuelle d’un effet de commerce escompté au taux d’intérêt i, et dont l’échéance arrive après n années est de : Va  Cn  1  i  n L’escompte: E  Cn Va  Cn 1  1  i   n  60 20  31/05/2021 Escompte à Intérêts composés Exemple 1 Un effet de commerce de valeur nominal 25 000 dh payable dans 4 ans. Déterminer sa valeur actuelle et l’escompte à intérêts composés au taux annuel de 7% (la capitalisation est annuelle). 61 I-Escompte à Intérêts composés Réponses: Va  Cn  1  i   19072,38 dh n E  Cn Va  5927,62 dh 62 Sur Excel A B C D E F G H 1 Capital Taux N périodes Valeur Escompte d’intérêt actuelle 2 3 4 5 6 63 21  31/05/2021 Escompte à Intérêts composés Exemple 2 Quelle est la valeur actuelle d’un effet de commerce de valeur nominale 27 500 payable dans 3 ans et 4 mois sachant que le taux d’escompte est 9.4% par an et que la période de capitalisation est le trimestre? Calculer l’escompte retenu par le banquier? 64 Escompte à Intérêts composés Réponses Taux annuel : 9,4% alors le taux trimestriel est de 9,4%/4=2,35% Détermination de n: nous avons 3 ans et 4 mois et puisque la capitalisation est trimestrielle alors: n=(3*4)+1+(1/3)= 13 + 1/3 1 Va  Cn  1  i   27500 1  0,0235 n (13 ) 3  20175,67 dh E  Cn Va  7324,33 dh 65 65 Sur Excel A B C D E F G H I J 1 2 3 Capital Taux Taux Durée Période N Valeur Escompte d’intérêt d’intérêt de périodes actuelle 4 annuel trimestriel année Mois capitalisat ion 5 6 66 22  31/05/2021 II- Equivalence à Intérêts composés 1- Equivalence de deux capitaux Deux capitaux sont équivalents à intérêts composés si, escomptés à une date donnée, au même taux, ils ont la même valeur actuelle. Soient C1 et C2 : les valeurs nominales i : le taux de l’escompte n1 et n2 : le nombre d’années séparant la date de l’escompte et celle de l’échéance Va1 et Va2 : les valeurs actuelles Alors: Va1  Va2  Cn1 1  i   Cn2 1  i   n1  n2 67 67 Equivalence à Intérêts composés Exemple 3 Un effet de 12 500 dh échéant dans 3 ans doit être remplacé par un autre échéant dans 7 ans. Calculer la valeur nominal de l’effet de remplacement. Taux d’escompte 13%. 68 68 Equivalence à Intérêts composés Solution Va1  Va2  Cn1 1  i   Cn2 1  i   n1  n2 12500 1  0,13   C n2 1  0,13  3 7 Donc : C n2  20380 ,92 dh 69 69 23  31/05/2021 Sur Excel A B C D E F G H I J 1 2 3 Capital Taux Taux Durée Période N Valeur d’intérêt d’intérêt de périodes actuelle 4 annuel trimestriel année Mois capitalisat ion 5 C1 6 C2 70 71 Séance 4 : liaisons entre feuilles et classeurs Liaisons entre feuilles Liaisons entre classeurs Liaisons avec Word 72 24  31/05/2021 Manipulation des feuilles Excel Nommer une feuille Double-cliquez sur l’onglet. Le nom de l’onglet s’affiche en surbrillance. Ou bien faites un clic droit sur l’onglet > « Renommer ». Saisissez le nouveau nom de la feuille. 73 Couleur de l’onglet Pour choisir la couleur d’un onglet : clic droit sur l’onglet > Couleur d’onglet. Choisissez la couleur. Quand la feuille correspondante est sélectionnée, son nom est souligné de cette couleur. Sinon, l’arrière-plan de l’onglet revêt cette couleur. 74 Sélectionner des feuilles Pour sélectionner une ou plusieurs feuilles, on procède ainsi : Une feuille : clic sur l’onglet. L’onglet de la feuille sélectionnée devient blanc. Des feuilles adjacentes : clic sur l’onglet de la 1 ère ; Maj (Shift) + clic sur l’onglet de la dernière. 75 25  31/05/2021 Des feuilles non adjacentes : clic sur l’onglet de la 1 ère ; Ctrl + clic sur chaque autre onglet de feuille. Toutes les feuilles du classeur : clic droit sur un onglet > Sélectionner toutes les feuilles. 76 77 Cas1 Cas2 Cas3 Cas4 78 26  31/05/2021 On clique sur l'onglet de la première feuille, puis en maintenant la touche CTRL enfoncée, on sélectionne la seconde feuille et ainsi de suite. 79 A partir de là, tout ce qui sera saisi dans une feuille se reproduira sur la seconde, exactement au même endroit. Toute modification effectuée sur la première feuille se répercutera sur les autres feuilles de la sélection. 80 Remarque Une fois les feuilles déselectionnées, seule la feuille active sera concernée par d'éventuelles modifications ou saisies. 81 27  31/05/2021 Pour déselectionner une ou plusieurs feuilles, on procède comme indiqué précédemment pour la sélection, avec CTRL et en cliquant sur l'onglet de la feuille à enlever. 82 Liaisons entre feuilles Il est possible d'insérer sur une feuille des fonctions faisant référence à des cellules situées sur une autre feuille. 83 Exemple (Mathématiques financières) Equivalence de deux groupes de capitaux Deux groupes de capitaux sont équivalents à intérêts composés à une date donnée si, au même taux d’escompte, la somme des valeurs actuelles du premier groupe est égale à la somme des valeurs actuelles du deuxième groupe. V  V a1 a2 84 28  31/05/2021 On souhaite remplacer les cinq effets suivants: E1: 12 000 dh dans 1 ans; E2: 15 000 dh dans 3 ans; E3: 18 000 dh dans 4 ans. E4: 10 000 dh dans 5 ans; E5: 20 000 dh dans 6 ans; Par deux effets E’1: 30 000 dh dans 4 ans E’2 : C dh dans 6 ans Taux annuel d’escompte 11%. Calculer C. 85 85 Sur Excel G_eff1 G_eff2 A B C E I J A B C E I 1 1 2 2 3 Capital Taux Durée Valeur 3 Capital Taux Durée Valeur d’intérêt actuelle d’intérêt actuelle 4 annuel année 4 annuel année 5 E1 5 E’1 E2 E’2 E3 6 Somme 6 E4 E5 Somme A B C Résultat 1 Valeur actuelle C 2 Groupe d’effets 1 Groupe d’effets 2 86 87 29  31/05/2021 Liaison entre deux classeurs 88 Exemple 2 Liaison en deux classeurs On souhaite remplacer les cinq effets suivants: E1: 12 000 dh dans 1 ans; E2: 15 000 dh dans 3 ans; E3: 18 000 dh dans 4 ans. E4: 10 000 dh dans 5 ans; E5: 20 000 dh dans 6 ans; Par deux effets E’1: 30 000 dh dans 4 ans E’2 : 50000 dh Taux annuel d’escompte 10%. Déterminer l’échéance du E’2 89 89 Sur Excel Group1 Group2 A B C E I J A B C E I 1 1 2 2 3 Capital Taux Durée Valeur 3 Capital Taux Durée Valeur d’intérêt actuelle d’intérêt actuelle 4 annuel année 4 annuel année 5 E1 5 E’1 E2 E’2 E3 6 Somme 6 E2 E3 Somme Résultat A B C 1 Valeur actuelle Durée 2 Groupe d’effets 1 Groupe d’effets 2 90 30  31/05/2021 Liaisons Excel - Word Il est nécessaire de distinguer les objets liés et les objets incorporés. Incorporer des objets Lorsque vous incorporez un objet Excel, les informations contenues dans le fichier Word ne changent pas si vous modifiez le fichier source. 91 Les objets incorporés s'intègrent au fichier Word et, une fois insérés, ne font plus partie du fichier source. On incorpore des objets en les copiant ou en les insérant dans le document Word. 92 Exemple d’incorporation … 93 31  31/05/2021 Lier des objets Lorsqu'un objet est lié, les informations seront mises à jour si le fichier source (Excel) est modifié. Les données liées sont gardées dans le fichier source. Le fichier Word, ou le fichier Excel 94 Exemple de liaison … 95 Exemple 96 32  31/05/2021 Exemple 97 98 99 33  31/05/2021 Remarque sur la Mise à jour des données liées Par défaut, les objets liés sont automatiquement mis à jour: Word actualise les informations liées à chaque ouverture du fichier Word ou à chaque modification apportée au fichier Excel source si le fichier Word est également ouvert. Il est possible de modifier les paramètres des objets liés individuels pour empêcher leur mise à jour ou n'autoriser que les mises à jour manuelles. 100 101 Application (Mathématiques financières) 102 34  31/05/2021 Les Annuités Définition I –Valeur acquise II – Valeur actuelle 103 103 Rappel On appelle annuité, des sommes payables à intervalle de temps régulier L’intervalle de temps séparant le paiement de deux annuités est la période. La période peut être l’année, le semestre, le trimestre ou le mois. Le but des versement des annuités peut être de constituer un capital (dans ce cas on les appelle annuités de placement ou de capitalisation) ou de rembourser une dette (Dans ce cas on les appelle annuités de remboursement). 104 104 I- Valeur acquise Soient a : le montant de l’annuité constant i : le taux d’intérêt correspondant à la période retenue n : le nombre d’annuité An : la valeur acquise au moment du versement de la dernière annuité An a a a a a ………………. 0 1 2 3 n-1 n 105 35  31/05/2021 I- Valeur acquise Versement Valeur acquise An  a  a1  i   a1  i   a1  i   2 3 n-1 1 a(1+i)...  a1  i  n-2 n 1 2 a(1+i) n-3 3 a(1+i) ….. ….. An  a 1  i n 1 ….. ….. i n-1 a(1+i) n a 106 I- Valeur acquise Exemple 1 Calculer la valeur acquise, au moment du dernier versement, par une suite de 15 annuités de fin période d’un montant de 35 000 dh chacune. Taux : 10 % par an. 107 I- Valeur acquise Réponse n= 15 ans i=10% par an a= 35000 alors: An  a 1  i n 1  35000 1  0 ,1 15 1 i 0 ,1 A n  1. 112. 036 ,86 dh 108 108 36  31/05/2021 Sur Excel/ Sur Word 109 Emprunts indivis L’emprunt indivis se caractérise par le fait que l’emprunteur (particulier ou entreprise) s’adresse à un seul créancier. L’emprunt obligataire : emprunt par lequel l’emprunteur (grande entreprise ou l’Etat) recourt à une multitude de créancier. 110 Tableau d’amortissement d’un emprunt Une personne emprunte une somme C pour une durée égale à n périodes au taux d’intérêt i. Capital en début Intérêt de la Amortissemen Capital en fin de période Période Annuité (a) de période (CDP) période (I) t (M) (CFP) 1 C Ci M1 a1=C i + M1 C1= C - M1 2 C1 C1 i M2 a2=C1 i + M2 C2= C1 – M2 3 C2 C2 i M3 a3=C2 i + M3 C3= C2 – M3 …. …. n-1 Cn-2 Cn-2 i Mn-1 an-1=Cn-2 + Mn-1 Cn-1= Cn-2 – Mn-1 n Cn-1 Cn-1 i Mn an=Cn-1 + Mn Cn= 0 111 111 37  31/05/2021 Remboursement par annuités constantes Principe: La somme de l’intérêt de la période et de l’amortissement est constant: a = M + I i a C 1  1  i  n Exemple 1 Une personne emprunte 350 000 dh auprès d’une banque et s’engage à verser 8 annuités constantes, la première payable un an après la date du contrat. Etablir le tableau d’amortissement de l’emprunt sachant que le taux d’intérêt annuel est de 12 %. 112 112 Réponse i 0,12 a C  350000  70455,99 1  1  i  1  1  0,12 n 8 Capital en Capital en fin Intérêt de la Amortisseme Période début de Annuité (a) de période période (I) nt (M) période (CDP) (CFP) 1 350 000 42 000 28 456 70 456 321 544 2 321 544 38 585 31 871 70 456 289 673 3 289 673 34 761 35 695 70 456 253 978 4 253 978 30 477 39 979 70 456 213 999 5 213 999 25 680 44 776 70 456 169 223 6 169 223 20 307 50 149 70 456 119 074 7 119 074 14 289 56 167 70 456 62 907 8 62 907 7 549 62 907 70 456 0 113 Excel et Algèbre linéaire Calcul Matriciel Déterminant, Produit scalaire, Somme; Produit; Inverse; Co matrice Résolution des systèmes 114 38  31/05/2021 115 Fin 116 117 39  31/05/2021 Remboursement par amortissement constant Principe: L’amortissement M est constant C M n Exemple 2 Un emprunt de 300 000 dh est remboursable en 6 annuités, la 1ère est payable un an après la date du contrat. Sachant que l’amortissement est constant et que le taux est de 11.5% l’an. Construire le tableau d’amortissement de cet emprunt. 118 Réponse C 300000 M   50000 n 6 Capital en Capital en fin Intérêt de la Amortissemen Période début de Annuité (a) de période période (I) t (M) période (CDP) (CFP) 1 300 000 34 500 50 000 84 500 250 000 2 250 000 28 750 50 000 78 750 200 000 3 200 000 23 000 50 000 73 000 150 000 4 150 000 17 250 50 000 67 250 100 000 5 100 000 11 500 50 000 61 500 50 000 6 50 000 5 750 50 000 55 750 0 119 Application Un créancier accepte que son débiteur remplace les trois dettes: 15 000 dh dans 2 ans; 20 000 dh dans 3 ans; 35 000 dh dans 5 ans. par un versement unique de 70 800 dh. Taux annuel d’escompte est 10,5%. Déterminer l’échéance de ce paiement. 120 120 40  31/05/2021 Equivalence à Intérêts composés Solution 708001  0,105  150001  0,105  200001  0,105  350001  0,105 n 2 3 5 708001,105  12284,76  14823,24  21244,99 n 1,105n  0,682952 Alors, le paiement unique de 70800 dh sera effectué dans 3 ans, 9 mois et 25 jours. ln 0,682952 n donc : n  3,819214 ln1,105 121 Sur Excel A B C D E F G H I J 1 2 3 Capital Taux Taux Durée Période N Valeur d’intérêt d’intérêt de périodes actuelle 4 annuel trimestriel année Mois capitalisat ion 5 E1 E2 E3 6 Somme E 122 II- Equivalence à Intérêts composés Exemple 3: Un commerçant souhaite remplacer les trois dettes suivantes: 10 200 dh payable dans 1 an et 3 mois; 15 000 dh payable dans 2 ans et 7 mois; 22 500 dh payable dans 4 ans et 6 mois; par un effet unique dans 3 ans. Calculer le montant de ce paiement. Taux d’escompte annuel de 10,5 %. 123 123 41  31/05/2021 II- Equivalence à Intérêts composés Solution  7    2   3 Cn 1  0,105  102001  0,105  150001  0,105  12  3   1   12   6   4   225001  0,105  12  Alors la valeur de l ' effet unique est de : C n  47155 ,05 dh 124 124 Sur Excel A B C D E F G H I J 1 2 3 Capital Taux Taux Durée Période N Valeur d’intérêt d’intérêt de périodes actuelle 4 annuel trimestriel année Mois capitalisat ion 5 E1 E2 E3 6 Somme E 125 I- Valeur acquise Exemple 2 Combien faut-il verser à la fin de chaque semestre pendant 8 ans, pour constituer au moment du dernier versement, un capital de 450 000 dh. Taux : 4,5 % par semestre. 126 126 42  31/05/2021 I- Valeur acquise Réponse n=16 semestres i=4,5% par semestre An= 35000 alors: An  a 1  i n 1  a  A i i n 1  i n 1 0,045 a  19.806,92 dh 1  0,04516 1 127 127 Valeur acquise Remarque: capital acquise à une date postérieure au dernier versement Soient: a= 25000 dh i=10,5% par an Date du premier versement 31/12/2004 Date du dernier versement: 31/12/2014 TAF Calculer la valeur acquise le 31/12/2014 Calculer la valeur acquise le 31/05/2015 128 128 I- Valeur acquise Solution a= 25000 dh; i=10,5% par an; n=11 La valeur acquise le 31/12/2014: A11  25000 1  0 ,105 11  1  475. 966 ,51 dh 0 ,105 La valeur acquise le 31/05/2015: deux solutions possibles: A- Solution rationnelle: A 11 5   12   A11 1  5  0,105  496.790,04 dh 12 B- Solution commerciale: 5 A 5  A11 1,105 12  496.185,43 dh 11 12 129 129 43  31/05/2021 130 131 Application 1 Créer une formule pour calculer Combien un individu récupère-t-il à la fin d’un placement C dh pendant m mois au taux annuel de t%. A B C D E F G H 1 C M t VA 2 3 4 5 6 132 44  31/05/2021 Application 2 Créer une formule Une personne a placé un capital de 25000 dh pour une durée allant 27 mai N au 8 Août de la même année au taux annuel de 12%. Combien a-t-il récupéré à la fin de son placement? A B C D E F G H 1 C Date Date Taux VA début de fin d’intérêt 2 3 4 5 6 133 Application 3 Soient les trois placements suivants: Placement A: 17500 dh du 1/7/N au 5/11/N, à 9% l’an Placement B: 12000dh du 12/11/N, au 29/12/N, à 10,5% l’an Placement C : 27500 dh du 4/4/N au 12/10/N, à 8,5% l’an Calculer le taux moyen de placement de ces trois capitaux. A B C D E F G H 1 Placement Montant Date Début Date Fin Taux Taux moyen 2 A 3 B 4 C 5 6 134 Application 4 Un particulier remplace deux effets 15 000 DH échéant le 15 Août N et 30 500 DH échéant le 15 décembre N par un effet de 45931,01 DH échéant le 30 novembre N. Quel est le taux annuel d’actualisation ? A B C D E F G H 1 Effet Mon Echéan Date Durée taux tant ce d’actua en annuel lisation jours d’actua lisation 2 3 4 5 6 135 45  31/05/2021 Application 5 Un fournisseur accepte que son client remplace deux effets - 7500 dh dans 3 ans - 10200 dh dans 4ans par un versement unique de 15615,62 DH Déterminer l’échéance de ce paiement unique. Taux d’escompte 10 % A B C D E F G H 1 Effet Montant Durée Valeur Taux ex6s4 actuelle d’escompt e 2 A 7500 3 ? 10% 3 B 10200 4 4 5 C 15615,62 ? 6 136 46  31/05/2021 COURS EXCEL SEANCES 5 et 6 1 PLAN 1. Les fonctions (SOMME, SI, RECHERCHEV, RECHERCHEH, SOMMEPROD, INDIRECT, INDEX,EQUIV, RANG…) (Comptabilité générale) 2. Les graphiques (Statistique) 3. Les tableaux croisés dynamiques (TCD) 4. Créer et gérer des listes : Trier, Filtrer, Sous-totaux 2 La fonction SI La fonction SI effectue un test logique qui va retourner 2 valeurs (ET UNIQUEMENT 2) : VRAI ou FAUX 3 1  31/05/2021 La fonction SI se décompose en trois parties Le test Le résultat si le test est vrai Le résultat si le test est faux =SI (TEST ; réponse si VRAI ; réponse si FAUX) 4 Exemple 1 Une entreprise décide de donner à ses vendeurs une prime de 2000 DH pour un chiffre d'affaires inférieur à 20 000 DH et de 3000 DH au-delà. A B C 1 Vendeur Chiffre d’affaires Prime 2 Hassan 10500 3 Raja 20100 4 Mohamed 22400 5 Rachid 19500 6 Sana 33000 5 Exemple 2 Sur la base d’un résultat de match de football. Nous souhaitons afficher Gagné, Perdu ou Nul quand les équipes de la colonne A ont un score plus élevé que celui de l’équipe de la colonne D Pour la première cellule en E2, vous écrivez alors =SI(B2>C2; « Gagné »; « Perdu ») 6 2  31/05/2021 Exemple 3 Vous gérez les stocks et les commandes d’une entreprise. Vous ne pouvez pas honorer une commande si vous n’avez pas la totalité de la quantité demandée. Donc vous allez créer une fonction SI qui va vérifier que vous possédez la quantité commandée. Si votre stock est inférieur, vous ne pouvez livrer que la quantité que vous possédez. 7 Exemple 4 : Saisie Les mentions des élèves de façons automatique par la fonction SI 8 Exemple 5 : Etablissement automatique d’une facture En ce qui suit, on se propose d’automatiser la procédure consistant dans l’établissement d’une facture. Pour cela : Sur une feuille de calcul Excel créer le document suivant : 9 3  31/05/2021 10 Calculer le montant HT par article après remise (sur la plage E16 : E23). La remise (10%) s’accorde pour les montants dépassant 100 euros. Le montant ne s’affiche qu’au cas où la Quantité est saisie. Calculer les montants suivants : Le Montant Total HT dans E24 Le Montant Total TTC dans E26 11 Sur une autre feuille de calcul créer la base de données « Articles » dont vous avez un extrait dans l’écran suivant : 12 4  31/05/2021 Dans la feuille de calcul contenant la facture faire : Rattacher à la plage A16 : A23 une liste combinée déroulante contenant les références des produits existantes dans la base de données « Article ». Ecrire les formules de calcul permettant d’afficher la désignation et le prix unitaire une fois avoir sélectionnée la référence de l’article. Les calculs des différents montants doivent être faits automatiquement une fois avoir saisie la quantité de chaque produit. 13 14 15 5  31/05/2021 Rappel sur la fonction SI Application Une entreprise souhaite ventiler automatiquement ses factures en fonction du code du produit. Ouvrez le classeur et réalisez le tableau suivant dans une nouvelle feuille que vous renommez Ventilation 16 17 Dans E4, tapez la formule qui affiche le prix si le code coïncide avec les trois premières lettres du produit. Recopiez la formule de E4 vers la droite, puis vers le bas. Dans E12, tapez la formule qui calcul le total puis recopiez-la vers le bas. Enregistrez le classeur. 18 6  31/05/2021 TAR5 : Etablissement automatique d’une facture Objectif : Automatiser la procédure d’établissement d’une facture. 19 20 Calculer le montant HT par article après remise (sur la plage E16 : E23). La remise (10%) s’accorde pour les montants dépassant 100 euros. Le montant ne s’affiche qu’au cas où la Quantité est saisie. Calculer les montants suivants : Le Montant Total HT dans E24 Le Montant Total TTC dans E26 21 7  31/05/2021 Sur une autre feuille de calcul créer la base de données « Articles » dont vous avez un extrait dans l’écran suivant : 22 Dans la feuille de calcul contenant la facture faire : Rattacher à la plage A16 : A23 une liste combinée déroulante contenant les références des produits existantes dans la base de données « Article ». 23 24 8  31/05/2021 Ecrire les formules de calcul permettant d’afficher la désignation et le prix unitaire une fois avoir sélectionnée la référence de l’article. Les calculs des différents montants doivent être faits automatiquement une fois avoir saisie la quantité de chaque produit. 25 La fonction RECHERCHEV La fonction RECHERCHEV permet de rechercher des éléments dans une table ou une plage par ligne. La fonction RECHERCHEV cherche une valeur dans la première colonne d’un tableau puis elle affiche le contenu de la cellule qui se situe sur la même ligne que la valeur recherchée 26 Pour construire une fonction RECHERCHEV, il faut 4 paramètres =RECHERCHEV(Valeur_cherchée ; Table_matrice; No_index_col ; Valeur_proche ) 27 9  31/05/2021 APPLICATION : Fiche d’inventaire 28 Créer le tableau source et le tableau de destination dans la même feuille de calcul Pour tirer le meilleur parti de la fonction RECHERCHEV, il convient d’organiser vos données afin que la valeur que vous recherchez (ici Code) se trouve à gauche de la valeur de retour à rechercher (Article) 29 =RECHERCHEV(Valeur_cherchée ; Table_matrice; No_index_col ; Valeur_proche ) La Valeur_cherchée est la case correspondant au Code Article (I7) Dans Table_matrice, définir une plage de cellule qui contient les informations recherchées dans le fichier source. Le No_index_col correspond au n° de la colonne qui contient les données à afficher (ici les articles). La Valeur_proche est facultatif. FAUX indique que l'on recherche une correspondance exacte 30 10  31/05/2021 La fonction RECHERCHEH =RECHERCHEH(Valeur_cherchée ; Table_matrice; No_index_lign; Valeur_proche ) 31 Fonction SOMME.SI =SOMME.SI(plage;critère;[somme_plage]) plage (Obligatoire): Plage de cellules à calculer en fonction du critère. Les cellules de chaque plage doivent être des nombres ou des noms, des matrices ou des références contenant des nombres. Les valeurs vides ou textuelles ne sont pas prises en compte. critère (Obligatoire) : Critère, exprimé sous forme de nombre, d’expression, de référence de cellule, de texte ou de fonction qui définit les cellules à ajouter. Par exemple, l’argument critère peut être exprimé sous l’une des formes suivantes : 32, ">32", B5, "32", "pommes" ou AUJOURDHUI(). 32 Exemple 33 11  31/05/2021 34 La fonction NB.SI compte les cellules selon une condition : =NB.SI (plage1 ; critère) 35 Utilisez la fonction NB.SI pour calculer le nombre des M et des F : 36 12  31/05/2021 La fonction SOMMEPROD La formule =SOMMEPROD() Réalisation d'opérations conditionnelles comptant et additionnant en utilisant des conditions multiples. Les formules permettant de calculer le nombre de valeurs ou les sommes sont fondées sur deux conditions ou plus. Exemple : Compter le nombre de mois où un commercial fait une vente. Le tableau de départ pourrait se présenter sous cette forme : 37 Exemple 1 : Compter le nombre des ventes faites par Fouad sur le mois de Mars. C'est une formule conditionnelle qui emploie deux conditions. = SOMMEPROD((A2:A9="Fouad")*(B2:B9="Mars")*(plage1) *(plage2)) A B C A B C 1 NOM Mars Avril 1 NOM MOIS MONTANT 2 Ahmed NOMBRE 2 Ahmed Mars 100 3 Fouad 3 Fouad Mars 600 4 Khalid 4 Khalid Mars 400 6 Hassan 5 Fouad Mars 200 A B C 6 Hassan Avril 200 1 NOM Mars Avril 7 Fouad Avril 600 2 Ahmed MONTANT 8 Fouad Avril 700 3 Fouad 9 Fouad Avril 500 4 Khalid 6 Hassan 38 APPLICATION Le tableau suivant contient les chiffres d’affaires(CA) apportés par des commerciaux (hommes et femmes). 39 13  31/05/2021 1. Ouvrez un classeur et réalisez le tableau ci-dessus dans une nouvelle feuille que vous renommez Chiffres d’affaires. 2. Dans G4, tapez la formule qui calcule la somme des chiffres d’affaires apportés par les hommes. 3. Dans G5, tapez la formule qui calcule la somme des chiffres d’affaires apportés par les femmes. 4. Dans G6, tapez la formule qui compte le nombre de personnes ayant apporté un chiffre d’affaires inférieur à 3000. 5. Dans G7, tapez la formule qui compte le nombre d’hommes ayant apporté un chiffre d’affaires inférieur à 4000. 40 fonction Excel EQUIV La fonction Excel EQUIV recherche la position d'une valeur dans une plage de cellules. Utilisation : =EQUIV(valeur_recherchée; plage; type) 41 Déterminer la position de l’identifiant recherchée dans la liste Déterminer la position de la variable recherchée 42 14  31/05/2021 Sélectionnez la fonction EQUIV : Dans "Valeur_cherchée", entrez la valeur dont vous souhaitez obtenir la position. Dans "Tableau_recherche", entrez la plage de cellules dans laquelle la fonction recherchera la position de "Valeur_cherchée". Dans "Type", entrez 0 pour trouver la valeur exacte, 1 pour la valeur la plus élevée qui est inférieure ou égale à "Valeur_cherchée", -1 pour la plus petite valeur qui est supérieure ou égale à "Valeur_cherchée" (dans le doute, entrez 0). 43 Fonction INDEX La fonction Excel INDEX recherche une valeur dans un tableau en fonction de ses coordonnées. Utilisation : =INDEX(plage_de_cellules; no_ligne; no_colonne) 44 Sélectionnez la fonction INDEX : Dans "Matrice", entrez la plage de cellules dans laquelle la fonction effectuera la recherche. Dans "No_lig" et "No_col", entrez les numéros de ligne et de colonne. Ces numéros doivent correspondre aux lignes et colonnes de la plage de cellules "Matrice" (et non pas aux lignes et colonnes de la feuille Excel). 45 15  31/05/2021 Combinaison INDEX + EQUIV Si la fonction EQUIV renvoie le numéro de ligne : =INDEX(tableau; EQUIV(valeur_recherchée; plage_de_recherche; 0); no_colonne) Si la fonction EQUIV renvoie le numéro de colonne : =INDEX(tableau; no_ligne; EQUIV(valeur_recherchée; plage_de_recherche; 0)) 46 47 Sélectionnez pour commencer la fonction INDEX : Dans "Matrice", entrez la plage de cellules du tableau. Dans "No_lig", la fonction EQUIV qui calculera le numéro de ligne en fonction de la recherche. Dans "No_col", entrez la colonne du tableau qui contient les données à renvoyer (dans cet exemple, le but est d'afficher le numéro de l’étudiant, c'est donc la colonne 1). 48 16  31/05/2021 Statistique descriptive sur Excel 1. Les fonctions (SOMMEPROD, FREQUENCE, MOYENNE, ECARTTYPE…) 2. Les graphiques 3. Les tableaux croisés dynamiques (TCD) 4. Créer et gérer des listes : Trier, Filtre, Sous-totaux 49 L'objectif de la statistique est de collecter, d’analyser et d’interpréter de l'information (des ensembles d’observations) relative à un même phénomène et susceptible d’être caractérisée par un nombre. 50 A cette fin, le travail du statisticien comprend trois étapes : 1.La collecte des données ; 2.La présentation des données ; 3.Le résumé des données. 51 17  31/05/2021 1.La collecte des données Tout d’abord, la collecte des informations. Cette étape est essentielle et s’avère souvent complexe. Son bon déroulement suppose d’avoir répondu préalablement à trois questions : 52 La collecte des données - Quelles informations cherche-t-on à recueillir ? La réponse à cette question définit l’objet de la collecte. - Auprès de qui ces informations seront-elles recueillies ? La réponse à cette question définit le sujet de la collecte. -Comment ces informations seront-elles recueillies ? La réponse à cette question définit la méthode de la collecte. 53 1.La collecte des données Exemple Nous relevons les notes obtenues à deux épreuves test, en mathématiques et en français, d’une population composée de 15 étudiants extraits de trois classes. Voir la base de données l’objet? le sujet? la méthode? 54 18  31/05/2021 Exemple (suite) Nous pouvons construire un tableau de données à cinq colonnes reprenant l’ensemble de l’information obtenue. On écrit en ligne les individus (15) et en colonnes les variables étudiées (quatre : SEXE ; GROUPE ; note de FRANÇAIS ; note de MATHEMATIQUES). Nous obtenons le tableau suivant : 55 Variables Base de données qualitatives Variables quantitatives Observation Unités statistiques Lisibilité de la base ? Série statistique 56 2.La présentation des données Deuxième étape, la présentation des données. Une fois les données collectées, il importe «d’organiser» la statistique obtenue. Cette présentation prend la forme de tableaux et de graphiques. 57 19  31/05/2021 La présentation des données Pour améliorer la lisibilité de l’information, nous traitons variable par variable. Nous présenterons un tableau comportant deux colonnes, l’une indiquant les modalités de la variables, l’autre le nombre d’étudiants concernés Distribution 58 Analyse univariée sur Excel Fonction : fréquence Tableau Croisé Dynamique 59 Étiquettes de Nombre de lignes Identifiant3 F 47% G 53% Total général 100% 60 20  31/05/2021 Distribution de fréquences des étudiants par sexe 61 Distribution de fréquences des étudiants par groupe 62 Distributions conjointes et marginales Étiquettes de Total lignes F G général A 33% 7% 40% B 7% 27% 33% C 7% 20% 27% Total général