exercices pour excel 2007

EXERCICES POUR EXCEL 2007 Version 0.53 15 Juin 2013
REFERENCES ABSOLUES (Evolution d'un livret A) 1. REFERENCES ABSOLUES Tests LOGIQUES (EXERCICES)
FONCTIONS LOGIQUES IMBRIQUÉS
FONCTIONS LOGIQUES IMBRIQUÉS (calcul du poids idéal)
Mise en Forme Conditionnelle (MFC)
TCD : TABLEAU CROISE DYNAMIQUE
TCD et DIVERS
PROTECTION DES FEUILLES DE CALCUL EXCEL
LES LISTES DEROULANTES DANS EXCEL 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
1. 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[pic],0) ou le bouton "Ajouter une décimale" (d'icône
,0[pic],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) [pic].
Appliquez à la sélection la taille "14" et le style "Gras" [pic].
. 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 [pic] du bouton des
bordures (à droite du bouton Souligné [pic]) > "Toutes les bordures".
. Sélectionnez les cellules d'étiquettes des colonnes A5:F5. Appliquez
un alignement "Au centre" [pic] et le style "Gras" [pic].
. Sélectionnez les cellules A5:F10 et D10:F10
(Rappel : pour sélectionner plusieurs éléments simultanément,
sélectionnez le premier, puis 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" [pic].
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" : 2. 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 |Quantité Disponible|Etat | |
| |Unitaire | | | |
|Souris |10,00 E |20 |à commander | |
|Clavier |19,00 E |120 |ok | |
|Ecran plat |250,00 E |60 |à commander | |
|Ecran cathodique |130,00 E |45 |à commander | |
|Unité centrale DALL|800,00 E |113 |ok | |
|Ordinateur Portable|999,00 E |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 »
Le deuxième point virgule signifie « Sinon »
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) Trouver la formule avec la fonction SI : | |
|Si la moyenne est supérieure à 9, élève "admis" sinon "non admis". | 4. Les tests logiques IMBRIQUÉS : Pour tester plusieurs conditions il est possible d'imbriquer plusieurs
tests dans une séquence logique: Par exemple, appliqu