Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

EXERCICES POUR EXCEL 2007 Version 0.53 15 Juin 2013 1. REFERENCES ABSOLUES (Evo

EXERCICES POUR EXCEL 2007 Version 0.53 15 Juin 2013 1. REFERENCES ABSOLUES (Evolution d’un livret A) 2. REFERENCES ABSOLUES 3. TESTS LOGIQUES (EXERCICES) 4. FONCTIONS LOGIQUES IMBRIQUÉS 5. FONCTIONS LOGIQUES IMBRIQUÉS (calcul du poids idéal) 6. MISE EN FORME CONDITIONNELLE (MFC) 7. TCD : TABLEAU CROISE DYNAMIQUE 8. TCD ET DIVERS 9. PROTECTION DES FEUILLES DE CALCUL EXCEL 10.LES LISTES DEROULANTES DANS EXCEL 1 1. EVOLUTION D’UN LIVRET A ver 1.01 Objectif : Modéliser l’évolution d’un livret A sur une période de 18 ans. Capacités : Utilisation des références absolues et des références relatives. Méthode : On veut obtenir la feuille présentée dans l’image en bas à droite : Données initiales. Dans les cellules A1 à D5 entrez les textes et les valeurs indiqués à côté. Evolution des années : En B6, entrez : 2003 En B7, entrez : =B6+1 Recopier la cellule B7 vers le bas en utilisant la poignée en bas à droite de la cellule sur 18 cellules. Calcul des intérêts : La première année, année de dépôt, il n’y a pas d’intérêt, donc : En C6 entrer : 0 La deuxième année, le montant de l’intérêt est obtenu en multipliant le montant déposé sur le livret l’année antérieure par le taux. Donc, en C7 entrez : =D6*$B$2 Recopier la cellule C7 vers le bas en utilisant la poignée en bas à droite de la cellule sur 18 cellules. Note : Ici, se présente la difficulté de l’exercice ; si on prend comme formule en C7 =D6*B2 ; c’est à dire une expression où la cellule du taux est donnée en référence relative (B2) et non en référence absolue ($B$2), le résultat immédiat est exact mais la recopie de la formule vers le bas donnera des résultats aberrants. Essayez, avec et sans référence relative pour B2 ; examinez les formules obtenues en C8, C9, C10, etc. Conclure ! Calcul du montant sur le livret : La première année, année de dépôt, on a le montant initial, donc : En D6 entrer : =B3 Les années suivantes on a le montant de l’année antérieure augmenté des intérêts, donc : En D7 entrer : =D6+C7 Recopier la cellule D7 vers le bas. Terminé ! Vous pouvez faire varier dans ce modèle les données initiales (taux et montant initial) pour simuler d’autres cas (Plan Epargne Logement par ex.) Enregistrer votre Classeur sous le nom : table_retraites_LESTRELLIN.xls 2 Attention ! Ne vous étonnez pas d’avoir des résultats à zéro – tant que les calculs ne seront pas rentré dans leur intégralité vous n’aurez pas des résultats exacts ! 2. EXERCICE VALEURS RELATIVES ET ABSOLUES 1. Saisie des données 2. Mise en forme 3. Saisie des formules (directement ou par recopie). Voici la feuille de calcul initiale : Saisie des données :  En A1, saisissez COMMANDE.  En A3, saisissez Taux TVA.  En B3, saisissez 19,60%. Si nécessaire (par exemple si la case B3 affiche 20% ou 19,600%), modifiez le nombre de décimales : sur la barre d'outils de Mise en forme, activez le bouton "Réduire les décimales" (d'icône ,00 ,0) ou le bouton "Ajouter une décimale" (d'icône ,0 ,00). Dans la mesure où une seule case contient un pourcentage, on peut l'écrire directement (plutôt que de saisir le nombre, puis d'appliquer le format pourcentage). Saisissez les données des autres cellules. Tapez les prix simplement : 550, 200, 38, 12. On leur appliquera ultérieurement le format monétaire, après sélection de toutes les cellules concernées par ce format. 4. Mise en forme  Sélectionnez les cellules du titre A1:F1. Cliquez sur le bouton "Fusionner et centrer" (onglet Accueil, groupe Alignement) . Appliquez à la sélection la taille "14" et le style "Gras" .  Sélectionnez les deux cellules Taux TVA A3:B3. Appliquez une bordure quadrillage (sinon, il n'y a aucun quadrillage ; vérifiez-le après activation du bouton Aperçu avant impression) : sous l'onglet Accueil, dans le groupe Police, ouvrez le menu déroulant du bouton des bordures (à droite du bouton Souligné ) > "Toutes les bordures".  Sélectionnez les cellules d'étiquettes des colonnes A5:F5. Appliquez un alignement "Au centre" et le style "Gras" .  Sélectionnez les cellules A5:F10 et D10:F10 (Rappel : pour sélectionner plusieurs éléments simultanément, sélectionnez le premier, puis 3 appuyez sur Ctrl en sélectionnant les autres). Appliquez comme précédemment l'option de bordure "Toutes les bordures".  Sélectionnez les cellules de prix B6:B9, D6:F9 et F10. Appliquez-leur le format monétaire Euro après activation du menu déroulant de la zone "Format de nombre", dans le groupe "Nombre".  Sélectionnez les cellules de quantité C6:C9. Appliquez l'alignement "Au centre" . 5. Saisie des formules (directement ou par recopie). Calcul du Prix HT. Dans la cellule D6, saisissez =B6*C6. Validez. 6. Sélectionnez D6. Par cliqué-glissé, copiez la formule jusqu'en D9 (ou plus rapidement double- cliquez sur la poignée de D6). 7. B6 et C6 sont des références relatives. D6 a pour valeur le produit des deux cellules précédentes à gauche. Il en sera donc de même pour les cellules de la plage D7:D9. Calcul du Prix TTC. Le taux de TVA est affiché dans la cellule B3. 8. B3 précède de 3 colonnes toutes les cellules de Prix TTC. 9. En revanche, les nombres de lignes diffèrent (B3 est situé 3 lignes au-dessus de E6, 4 lignes au- dessus de E7, 5 lignes au-dessus de E8 et 6 lignes au-dessus de E9). 10.Pour préserver la valeur du taux de TVA, il faudra donc que la ligne de B3 soit saisie en référence absolue. 11.En F6, saisissez =D6*$B$3. En frappant sur la touche F4 du clavier, celle-ci affecte la référence absolue à la cellule. Une référence est dite absolue lorsque dans une formule copiée, une ou plusieurs cellules sont toujours les mêmes. Copiez cette formule jusqu'en F9 (ou plus rapidement double-cliquez sur la poignée de recopie de F9). Calcul du Total TTC. Dans la cellule F10, saisissez =E6+E7+E8+E9, ou plus rapidement =somme(E6:E9). Voici la feuille de calcul finale, que vous pouvez visualiser en cliquant sur le bouton "Aperçu avant impression" : 4 5 3. Tests LOGIQUES ver 1.00 Objectif : Utilisation de la fonction SI La syntaxe de la formule SI est la suivante : =SI(TEST LOGIQUE; VALEUR si vraie ; VALEUR si faux) Exercice 1 : Exercice avec la fonction SI Stocks Produits Prix Unitaire Quantité Disponible Etat Souris 10,00 € 20 à commander Clavier 19,00 € 120 ok Ecran plat 250,00 € 60 à commander Ecran cathodique 130,00 € 45 à commander Unité centrale DALL 800,00 € 113 ok Ordinateur Portable 999,00 € 51 à commander Exercice : 1) Mettre en forme le tableau ci-dessus 2) Trouver la formule avec la fonction SI : Si la quantité disponible est inférieure à 62, mettre un message d'alerte : "à commander", sinon "ok". Explications La Syntaxe de la fonction si est composée de 3 Arguments : 1. Le Test Logiques (une Question …) 2. La valeur Si vrai (ce qui va être fait si la réponse au Test est OUI) 3. La valeur Si Faux (ce qui va être fait si la réponse au Test est NON) Ce qui donne : =SI(Test_Logique ;valeur_si_vrai ;valeur_si_faux) Les points virgules (« ; ») sont des séparateurs ; le premier point virgule signifie « Alors » 6 Le deuxième point virgule signifie « Sinon » 7 Exercice 2 : Exercice avec la fonction SI Admis Éleves Notes Moyenne Etat Martin 9 9 non admis Duval 19 15 admis Lambert 20 16 admis Gabanou 5 7 non admis Berthier 15 11 admis Murviel 12 10 admis Exercice : 1) Mettre en forme le tableau ci-dessus 2) T rouver la formule avec la fonction SI : Si la moyenne est supérieure à 9, élève "admis" sinon "non admis". 8 4. LES TESTS LOGIQUES IMBRIQUÉS : Pour tester plusieurs conditions il est possible d'imbriquer plusieurs tests dans une séquence logique: Par exemple, appliquer deux taux de rabais en fonction de deux montants d'achat différents: (p. ex. à partir de 300€ d'achat vous appliquez un rabais de 5% et à partir de 500€ un rabais de 8%). La fonction contenant les deux tests logiques imbriqués aura la syntaxe suivante: A B C D E 1 Produit Prix de vente Quantité vendue Prix total Rabais? 2 Cahiers 2 500 1000.0 0 =SI(D2>500;D2*8%;SI(D2>300;D2*5%;"pas de rabais")) 3 Crayons 1 400 400.00 4 Bloc Notes 2 100 200.00 5 etc. =SI(Test_logique1;Valeur_si_vrai1;SI(Test_logique2;Valeur_si_vrai2;Valeur_si_faux)) NB: Il faudra prêter une attention particulière à l'ordre chronologique dans lequel vous imbriquez les tests, pour éviter qu'une condition ne soit pas déjà reprise au préalable par un autre test. Dans notre cas le fait de tester d'abord les montants >300 pour accorder le rabais de 5%, rendrait un deuxième test sur des montants dépassant 500€ inutile car ces valeurs auraient déjà été reprises par le premier test (500 étant plus grand que 300... La série de fonctions Si imbriquées donnera : =SI(D2>500;D2*8%;SI(D2>300;D2*5%;"pas de rabais")) 9 10 4. CALCUL DU POIDS IDÉAL ver 1.00 Objectif : Calcul du poids idéal d’une personne. Capacités : Utilisation des expressions logiques (Si alors sinon) imbriquées. uploads/Litterature/ e-des-exos-pour-excel.pdf