exercices pour tableurpasseport.univ-lille1.fr/site/bureautique/ewsc/excel/Exo... · Web viewVous devez réaliser une feuille de suivi d’opérations bancaires. La mise en forme

Compléments de formation 3ème Edition Septembre 1999 Tableur Excel 97

Compléments de formation

© LD & W.S.C. - 11/05/23 - 07:09 - Page 1 sur 101 document.doc

Exercices de consolidationpour Tableur Excel 97

Compléments de formation [Exercice 11.2 :] 3ème Edition Septembre 1999 Tableur Excel 97

Page volontairement vide




Etape 1Paramétrer

l’écran,

saisir, sauvegarder

,imprimer des donné

es





Exercice 1.1Exercice 1.1 ::

Objectif : saisie de données dans une feuille Excel, enregistrer le classeur, modifier les données saisies, enregistrer sous un autre nom le classeur, imprimer.

Vous devez réaliser deux documents Excel. Le premier document est une liste d’élèves avec leurs notes de math, français et anglais. Le deuxième document est réalisé à partir du premier et comporte en plus les notes de sport.

Consignes :1. Saisissez les données ci-dessous :

2. Enregistrez le document sous le nom de notes_sans_sport.xls3. Imprimez.

4. Ajoutez dans le tableau les données concernant le sport :

5. Enregistrez le document sous le nom de notes_avec_sport.xls6. Imprimez







Etape 2Mise en

forme des

données






Objectif : Saisir - mettre en forme (Format/Cellule Bordure et motifs) - mettre en page - enregistrer et imprimer.

Vous devez réaliser une feuille de suivi d’appel téléphonique. La mise en forme est un critère important. N’hésitez pas à modifier les marges, l’échelle (dans mise en page), les largeurs de colonne et hauteurs de ligne afin d’obtenir un résultat papier satisfaisant (voir exemple ci-dessous).

nota : lors de l’aperçu avant impression, vous avez sans doute remarqué sur certaines lignes (encadrement) n’apparaissent pas. Ceci est normal, en effectuant

un Zoom cela change, mais surtout ce qui compte est le résultat papier.

Consignes :1. Saisissez le tableau page suivante.2. Ajoutez le titre « Suivi des appels téléphoniques » au-dessus du tableau.3. Enregistrez le sous le nom Suivi Appels.xls4. Imprimez.



OK Date Qui No téléphone Objet




Objectif : Saisir - mettre en forme (Format/Cellule Bordure et motifs) - mettre en page - enregistrer et imprimer.

Vous devez réaliser une feuille de suivi d’achat de livres. La mise en forme est un critère important. N’hésitez pas à modifier les marges, l’échelle (dans mise en page), les largeurs de colonne et hauteurs de ligne afin d’obtenir un résultat papier satisfaisant (voir exemple ci-dessous).

Consignes :1. Saisissez le tableau page suivante.2. Enregistrez le sous le nom Livres.xls3. Imprimez.



Liste des livres

Au 12/6/99

Acheté le Code Auteur Titre Commentaires




Objectif : mettre en forme (Format/Cellule Bordure et motifs) - mettre en page - enregistrer et imprimer.

Vous devez mettre en forme le document notes_avec_sport.xls réalisé page 4 (Exercice 1.1 :).

Consignes :1. Chargez le fichier notes_avec_sport.xls.2. Appliquez les mises en forme au tableau (vous pouvez utiliser la mise en forme

automatique).3. Mettez en page le tableau (augmentez éventuellement l’échelle)4. Enregistrez le sous le nom notes_avec_sport_2.xls5. Imprimez.






Etape 3Fonctions de calculs préprogramm

ées






Objectif : programmer des formules de calculs - enregistrer et imprimer.

A partir du document notes_avec_sport_2.xls, vous calculerez les moyennes de chaque élève.

Consignes :1. Ouvrez le fichier notes_avec_sport_2.xls.2. Programmez le calcul de la moyenne de chaque élève.3. Enregistrez le sous le nom notes_avec_sport_Moy.xls4. Imprimez.






Objectif : Saisir - mettre en forme et en page - - programmer des formules de calculs - enregistrer et imprimer.

Vous devez réaliser une feuille de suivi de poids de sportifs. La mise en forme est un critère important. N’hésitez pas à modifier les marges, l’échelle (dans mise en page), les largeurs de colonne et hauteurs de ligne afin d’obtenir un résultat papier satisfaisant (voir exemple ci-dessous).

Consignes :1. Saisissez le tableau page suivante.2. Programmez le calcul du poids moyen de chaque personne3. Enregistrez le sous le nom Poids.xls4. Imprimez.



Contrôle du poids

Au 7/7/89

Personnes Pierre Paul Jacques Yves Marc

poids moyen84,71 74,14 39,86 49,71 31,29

Date Poids en [kg] Poids en [kg] Poids en [kg] Poids en [kg] Poids en [kg]

1/1/88 85,00 75,00 40,00 50,00 30,00

4/2/89 86,00 74,00 40,00 50,00 30,00

1/3/89 85,00 73,00 37,00 45,00 38,00

3/4/89 84,00 74,00 40,00 50,00 30,00

6/5/89 83,00 73,00 42,00 53,00 31,00

31/5/89 84,00 75,00 40,00 50,00 30,00

5/7/89 86,00 75,00 40,00 50,00 30,00




Objectif : Saisir - mettre en forme et en page - utiliser ou modifier les formats de nombre - programmer des formules de calculs - enregistrer et imprimer.

Vous devez réaliser une feuille de suivi d’opérations bancaires. La mise en forme est un critère important. N’hésitez pas à modifier les marges, l’échelle (dans mise en page), les largeurs de colonne et hauteurs de ligne afin d’obtenir un résultat papier satisfaisant (voir exemple ci-dessous).

Consignes :1. Saisissez le tableau page suivante.2. Programmez les calculs (la colonne total correspond au total des valeurs de la

ligne)3. Appliquez les formats de nombre aux dates (créez le format personnalisé j/m/)

et aux valeurs monétaires (créer le format personnalisé 0,00" FF")4. Enregistrez le sous le nom Comptes.xls5. Imprimez.



Mouvements sur Comptes

Année 1999Compte 1 Compte 2 Compte 3 Total

1 250,00 FF 4 500,00 FF 3 250,00 FFJour Nature du mouvement N° Chq Montant Montant Montant

1/1/ Retrait CB -200,00 FF -200,00 FF3/1/ Chèque Docteur 12345 -152,00 FF -152,00 FF3/1/ Remise de chèque 550,00 FF 550,00 FF

10/1/ CB Supermarché -385,24 FF -385,24 FF11/1/ Encaissement 500,00 FF 500,00 FF15/1/ Chèque Supermarché 12346 -545,75 FF -545,75 FF20/1/ Retrait 2 500,00 FF 2 500,00 FF

Soldes des comptes 352,25 FF 7 500,00 FF 3 414,76 FF

Solde total 11 267,01 FF





Vous devez réaliser une feuille de conversions monétaires. Cette feuille comporte trois tableaux : la conversion devises en Francs Français vers devises étrangères, la conversion devises étrangères vers devises en Francs Français ainsi que le tableau des cours monétaires (ici datant de mars 1993). Nous vous conseillons d’enregistrer régulièrement votre travail (voir plus loin le nom du fichier à donner).

Avant cela vous allez réaliser un petit tableau de conversion Franc vers Euro (et Euro vers Franc).

Ce petit tableau vous permettra d’avoir une approche mathématique de la conversion monétaire (règle de trois par exemple).

Consignes :1. Saisissez le tableau de conversion Euro (ci-dessous) dans la feuille 1 du

classeur.

2. Dans la cellule C3, programmez le calcul :

Pour 1 € (Euro) j’ai 6,55957 Francs.

Pour 25 € j’ai donc 6,55957*25/1 Francs (règle de trois).Soit : =C2*B3/B2



De même pour la ligne suivante :Pour 1 € (Euro) j’ai 6,55957 Francs.

J’ai donc 100*1/6,55957 € pour 100 Francs (règle de trois).Soit : =C4*B2/C2

Vous obtenez le tableau suivant :

3. Modifiez les valeurs de saisie afin de bien vérifier les calculs.En B3 : saisissez 100, vous obtenez 655,957 FrancsEn D4 : saisissez 6559,57 vous obtenez 1000 €

4. Nommer l’onglet feuil1 en Conversion EuroPour cela un double clic sur l’onglet permet de modifier le nom de la feuille, ou bien clic-droit sur l’onglet, puis renommer.

5. En feuille 2, saisissez les tableaux de la page suivante (sauf les valeurs qui seront calculées).

6. Renommez la feuille en conversion devises7. Programmez les calculs (inspirez-vous la conversion euro).8. Mettez en forme les tableaux, afin de tout avoir sur une seule page.

9. Enregistrez le classeur sous le nom DEVISE.xls10. Imprimez.




Colonne calculée

Colonne calculée







Vous devez réaliser une feuille de calcul de taux d’alcoolémie. La formule de calcul du taux d’alcoolémie est bien sur théorique =Volume total d’alcool pur consommé/(le poids en Kg * coéf 0,7). Soit : Taux_alcool=Vol_alcool/(Poids*0,7)Afin de connaître son taux d’alcoolémie, il suffira de saisir les alcools consommés (nombre de verres, volume d’un verre, % d’alcool dans le liquide absorbé), seront calculés le volume de liquide absorbé, la quantité d’alcool pur dans ce volume consommé.Vous mettrez en forme le document comme ci-dessous :

Consignes :11.Saisissez le tableau de la page suivante en programmant les calculs.12.Attention : la saisie des volumes et poids devrons être suivi par une création d’un

format de nombre spécifique (Menu Format/Cellule/Onglet_Nombre puis catégorie personnalisée, saisissez 0" ml" pour le volume et 0" Kg" pour le poids.Vous venez de créer deux nouveaux formats de nombre à appliquer sur vos valeurs.

13. Enregistrez le fichier sous le nom de ALCOOL.XLS14. Imprimez



Taux d'alcoolémie 3,12

Données relatives à l'individuPoids en kg 85,00 kg

Quantité d'alcool consommée Teneur en alcool typiqueTotaux 1 550 ml 186 ml

Nb Boisson QtéTeneur alcool.

Vol. consommé

Alcool

2 Vin blanc 250 ml 11,0% 500 ml 55 ml1 Bière 400 ml 5,0% 400 ml 20 ml1 Xérès 50 ml 22,0% 50 ml 11 ml3 Eau de vie 20 ml 36,0% 60 ml 22 ml2 Cognac 20 ml 45,0% 40 ml 18 ml5 Champagne 100 ml 12,0% 500 ml 60 ml





Vous devez réaliser un calendrier (An 2000) afin de planifier des tâches.

Vous mettrez en forme le document comme ci-dessous :

Avant cela vous allez réaliser un petit tableau de calcul d’âge.

Ce petit tableau vous permettra d’avoir une approche de la gestion des dates sous Excel.

Consignes :1. Saisissez le tableau de calcul d’âge (ci-dessous) dans la feuille 3 du classeur que

vous renommerez (double clic sur l’onglet) Age.

Une date dans Excel est un nombre de jour depuis le 01/01/1900 (la valeur 1 étant le 01/01/1900).



2. Le calcul de l’âge :

Si nous effectuons la différence entre les deux dates, nous obtiendrons un nombre de jours (soit ici : 11535). Nous pouvons déduire l’age en divisant ce nombre par 365 (approximatif 1 an) : 11535/365 = 31,60273973. L’âge sera la partie entière de ce résultat, soit 31 ans.La formule Excel pour la cellule C2 sera : =ENT((B2-A2)/365)La fonction ENT() calcule la partie entière d’un nombre.

3. Programmez le calcul de l’âge.

4. Changez le format d’affichage des dates :Affichez le jour de naissance.

Utilisez un format personnalisé jjjj jj/mm/aa

Nota :

j affichera 1 au 31 sur un ou deux chiffresjj affichera 01 au 31 sur deux chiffres obligatoirementjjj affichera lun au dim trois premières lettresjjjj affichera lundi au dimanche le nom entier

m affichera 1 au 12 sur un ou deux chiffresmm affichera 01 au 12 sur deux chiffres obligatoirementmmm affichera jan au déc trois premières lettresmmmm affichera janvier au décembre le nom entier

aa affichera 00 au 99 sur deux chiffresaaaa affichera 1900 au 9999 sur quatre chiffres

5. En feuille 1, saisissez le calendrier de la page suivante en automatisant au maximum les saisies.

6. Renommez la feuille 1 : Calendrier 7. Enregistrez le fichier sous le nom de Calendrier.XLS8. Imprimez









Objectif : Saisir - mise en forme - programmer des formules de calculs - enregistrer et imprimer.

Vous devez réaliser le planning Année 2001 des congés suivant :

Consignes :1. Saisissez en feuille 1 (à renommer Janvier) le tableau page suivante (mois de

Janvier 2001) et programmez les calculs.

Information :

Pour afficher Utilisez ce code de formatMois sous la forme 1-12 mMois sous la forme 01-12 mmMois sous la forme de jan à déc. mmmMois sous la forme de janvier à décembre mmmmMois sous la forme de la première lettre du mois mmmmmJours sous la forme 1-31 jJours sous la forme 01-31 jjJours sous la forme dim à sam. jjjJours sous la forme dimanche à samedi jjjjAnnées sous la forme 00 à 99 aaAnnées sous la forme 1900 à 9999 aaaa

2. Faites de même pour les mois suivants (jusqu’en décembre). Utilisez la recopie de feuille de calcul.

3. Enregistrez le document sous le nom de PLANNIG.XLS4. Imprimez











Objectif : Saisir - mise en forme - utiliser des heures - programmer des formules de calculs - enregistrer et imprimer.

Vous devez réaliser le suivi des temps suivant :

Consignes :1. Saisissez le tableau. Pour les heures, saisissez hh:mm (ou hh correspond aux

heures et mm aux minutes)2. Programmez les calculs3. Enregistrez le document sous le nom de TACHES_TEMPS.XLS4. Imprimez






Objectif : Saisir - mise en forme - utiliser des heures - créer appliquer des formats heures - programmer des formules de calculs - enregistrer et imprimer.

Vous devez modifier le suivi des temps suivant :

Consignes :1. Ouvrez le fichier TACHES_TEMPS.XLS et enregistrez le sous le nom

TACHES_TEMPS2.XLS2. Ajoutez les deux colonnes Coéf et Durée pondérée.3. Programmez les calculs sachant que la durée pondérée est égale à la durée

multipliée par le Coéf.4. Créez et appliquez le format [h]:mm à la durée total afin d’afficher une valeur

supérieure à 23:59

Information :

Pour afficher Utilisez ce code de formatHeures sous la forme 0 à 23 hHeures sous la forme 00 à 23 hhMinutes sous la forme 0 à 59 mMinutes sous la forme 00 à 59 mmSecondes sous la forme 0 à 59 sSecondes sous la forme 00 à 59 ssHeure sous la forme 4 AM h AM/PMHeure sous la forme 4:36 pm h:mm am/pmHeure sous la forme 4:36:03 p h:mm:ss a/pTemps écoulé en heures, par exemple, 25.02 [h]:mmTemps écoulé en minutes, par exemple, 63:46 [mm]:ssTemps écoulé en secondes [ss]Fractions d'une seconde h:mm:ss.00

5. Enregistrez 6. Imprimez






Etape 4Formules de calcul

en références

relatives ou

absolues






Objectif : Saisir - programmer des formules de calculs en utilisant les références absolues - enregistrer et imprimer.

Vous devez réaliser les trois feuilles de calcul suivantes :

Feuil1 : tableau 1

Feuil2 : tableau 2

Feuil3 : tableau 3

Chaque tableau sera créé dans une feuille différente (que vous nommerez).

Consignes :1. Renommez la feuille 1 et saisissez le premier tableau en programmant les

calculs (la marge = prix_d’achat*%_de_marge). Veuillez à ne programmer qu’une formule que vous recopierez vers la droite (utilisez les références absolues).

2. Faites de même pour la feuille 2 (tableau 2) et pour la feuille 3 (tableau 3).Notes pour le tableau 3 : vous pouvez bien sur effectuer un copier-coller des données des tableaux précédents. Programmez une seule formule pour le calcul de la marge (que vous recopierez vers le bas et à droite). Utilisez les références semi-absolues ($A1 bloque uniquement le A. A$1 bloque uniquement le 1).

3. Enregistrez le fichier sous le nom de MARGES.XLS4. Imprimez éventuellement chaque tableau.







Objectif : Saisir - mise en forme - programmer des formules de calculs en utilisant les références absolues - enregistrer et imprimer.

Vous devez réaliser une table de multiplication de 1 à 10 à l’aide d’un nombre minimal de saisie de formule.

Table de Multiplication

x 0 1 2 3 4 5 6 7 8 9 100 0 0 0 0 0 0 0 0 0 0 01 0 1 2 3 4 5 6 7 8 9 102 0 2 4 6 8 10 12 14 16 18 203 0 3 6 9 12 15 18 21 24 27 304 0 4 8 12 16 20 24 28 32 36 405 0 5 10 15 20 25 30 35 40 45 506 0 6 12 18 24 30 36 42 48 54 607 0 7 14 21 28 35 42 49 56 63 708 0 8 16 24 32 40 48 56 64 72 809 0 9 18 27 36 45 54 63 72 81 90

10 0 10 20 30 40 50 60 70 80 90 100

Consignes :1. Saisissez le tableau ci-dessus en programmant les calculs. Les intersections de

chaque ligne et colonne correspondent au résultat de la multiplication de la valeur de la ligne par celle de la colonne. N’oubliez pas la mise en forme.

2. A partir d’une formule programmée, recopiez vers le bas et vers le haut celle-ci. Utilisez les références absolues et relatives.

3. Enregistrez le fichier sous le nom TABLE MULT.XLS4. Imprimez.







Etape 5Calcul

s conditionnels






Objectif : Saisir - mise en forme - programmer des formules de calculs en utilisant les fonctions conditionnelles - enregistrer et imprimer.

Vous devez gérer le suivi du concours de pétanque de votre village.

Consignes :1. Saisissez la grille de la page suivante dans la feuille 1 (à renommer

Pétanque1).2. Programmez les calculs (les cellules grisées sont celles dans lesquelles nous

saisirons les noms des joueurs et les scores de chaque match).Le gagnant d'une partie est celui dont le score est le plus élevé. Son nom s'affichera automatiquement dans la partie suivante (voir page suivante).Vous remarquerez que si on ne remplit aucun score, Excel (la fonction SI que vous avez mis en place) affichera un gagnant par défaut. Vous résoudrez ce problème dans les points suivants.

3. Dupliquez la feuille Pétanque1 dans une autre feuille (clic droit sur l’onglet de la feuille à dupliquer, puis Déplacer/Copier, activez créer une copie, validez). Renommer la nouvelle feuille en Pétanque2

Rappel de cours :

Dans Excel il est possible d’imbriquer les conditions SI.

=SI(Condition1 ; SI(Condition2 ; Valeur si vrai ; Valeur si faux) ; SI(Condition3 ; valeur si vrai ; valeur si faux))

SI() dans la valeur si vrai SI() dans la valeur si faux

Nota : le nombre d’imbrication est limité à 7 dans Excel

4. Dans notre exercice, si aucun score n'est saisi, la fonction SI affiche un gagnant par défaut. Il faut donc avant ce SI tester la présence de scores saisis.Le test peut être du style : SI scores saisis ALORS faire le test pour connaître le gagnant SINON ne rien afficherProblème : les scores sont saisis dans deux cellules différentes, le test doit vérifier la présence de valeurs saisies dans les deux cellules de scores. Pour cela, nous utiliserons la fonction ET

Rappel de cours :

Dans Excel il est possible de cumuler plusieurs conditions grâce à la fonction ET.

ET(Condition1 ; Condition2)

Le résultat est VRAI si Condition1 est VRAI ET Condition2 est VRAILe résultat est FAUX si Condition1 est VRAI ET Condition2 est FAUXLe résultat est FAUX si Condition1 est FAUX ET Condition2 est VRAILe résultat est FAUX si Condition1 est FAUX ET Condition2 est FAUX



Exemple

Nous voulons afficher en B4 le fait qu'il y a deux couleurs saisies en B1 et B2

Tableau final Tableau avec formule de calcul

<> signifie différent de"" signifie vide

5. Enregistrez votre travail sous le nom de PETANQUE99.XLS6. Imprimez.

Note : ne négligez pas la mise en forme.




Cette cellule affichera le nom du gagnant du premier match



Objectif : Saisir - mise en forme - programmer des formules de calculs en utilisant les fonctions conditionnelles - enregistrer et imprimer.

Vous devez réaliser une facture intégrant une remise automatique (variant suivant le total HT).Vous réaliserez cette facture en deux étapes :* réalisation de la facture sans taux de remise : feuille 1 du classeur* réalisation de la facture avec taux de remise : feuille 2 du classeur

Consignes :7. Saisissez la facture de la page suivante dans la feuille 1 (à renommer

FACTURE1). Programmez les calculs.8. Copier la facture en feuille 2 (clic droit sur l’onglet FACTURE1, puis

Déplacer/Copier, activez créer une copie, validez). Renommer la nouvelle feuille en FACTURE2.

9. Dans la feuille ainsi créée, insérez 3 lignes permettant d’intégrer une remise (par exemple Taux Remise, Montant Remise et Net HT).

10.Programmez les calculs de remise en respectant les règles décrites ci-dessous :Pour un montant total HT compris entre 2000 et 5000 F, le taux remise sera de 5%.Pour un montant total HT de plus de 5000 F, le taux de remise sera de 10%.Bien entendu aucune remise ne sera effectuée pour un montant total inférieur à 2000 F.

11. Enregistrez votre travail sous le nom de PC MAC.XLS12. Imprimez.




PC-MAC Vente1 rue de la Paix

75000 Paris

Mr William Saint-CricqGRETARue Tristan Derème65000 Tarbes

Paris le 12/1/96

N° Facture N° Client Commande N°6002912 275844 654

Désignation Qté P.U.HT P.T.HT

Boîte de 20 disquettes 3p1/2 HD 5 57,00 285,00

Barette V-RAM 512Ko MAC LC 2 143,00 286,00

Câble imprimante PC 4 42,00 168,00

Notre Numéro de TVA : FR42546547895 Total H.T. 739,00

Taux TVA 20,60%TVA 152,23

Net à payer 891,23

Réglé par Carte Bleue et Net d'escompteDate d'échéance 15/1/96

N° de téléphone du service comptabilité client : (1) 44 44 44 44




Objectif : Saisir - mise en forme - programmer des formules de calculs en utilisant les fonctions conditionnelles et les références absolues/relatives - enregistrer et imprimer.

Vous devez réaliser un comparatif avant achat. Pour cela vous mettez en place le tableau suivant :

Consignes :7. Saisissez le tableau page suivante et programmez les calculs. L’affichage des

prix dans chaque colonne fournisseur sera automatique en fonction du fournisseur de la ligne. La modification du fournisseur de la ligne entraînera automatiquement l’affichage du montant dans la colonne adéquat.

8. Enregistrez le document sous le nom de BUDGET.XLS9. Imprimez







Objectif : programmer des formules de calculs en utilisant les fonctions conditionnelles et gestion de dates- enregistrer et imprimer.

A partir du calendrier de l’Exercice 3.6 : (page 29), vous devez réaliser le calendrier perpétuel suivant :

Consignes :1. Ouvrez le fichier Calendrier.XLS de l’Exercice 3.6 : (page 29).2. Enregistrez le document sous le nom de CALEND_PERPET.XLS3. Allez sur la feuille Age4. Nous allons découvrir 3 fonctions de date :DATE() JOUR() MOIS() ANNEE()

La fonction DATE() :Cette fonction permet de reconstituer une date à l’aide de trois paramètres : année, mois et jour.Exemple :DATE(1999 ;11 ;26) affichera la date 26/11/1999DATE(1999 ;14 ;26) affichera la date 26/02/2000 (le mois 14 n’existant pas, Excel a compris qu’il fallait changer d’année).

La fonction JOUR() :Cette fonction extrait le jour d’une date.Exemple :JOUR(26/11/1999) affichera 26

La fonction MOIS() :© LD & W.S.C. - 11/05/23 - 07:09 - Page 56 sur 101 document.doc


Cette fonction extrait le mois d’une date.Exemple :MOIS(26/11/1999) affichera 11

La fonction ANNEE() :Cette fonction extrait l’année d’une date.Exemple :ANNEE(26/11/1999) affichera 1999

5. En feuille Age vous avez :

Nous allons afficher en A4 la date 23 ans, 2mois et 10 jours après la date de naissance, soit : A2 (02/03/1968) + 23 ans + 2 mois + 10 jours.Pour cela nous utiliserons les 4 fonctions présentées.

Si pour augmenter de 10 jours une date il suffit de faire une addition (voir Exercice 3.6 :), il ne sera pas aussi facile d’augmenter une date en mois ou en année (il est difficile de connaître le nombre de jours qui correspond à 2 mois et 23 ans : années bissextiles ?)

Afin d’effectuer les opérations désirées sur la date, il faut d’abord décomposer celle-ci en jour, mois, année. Puis appliquer les modifications à ces trois valeurs : jour+10, mois+2, année+23. Puis recomposer une date avec ces nouvelles valeurs.

6. Mise en pratique :

Modifiez le tableau comme ci-dessous :



7. Saisissez :- en A7 : =JOUR(A2)- en B7 : =MOIS(A2)- en C7 : =ANNEE(A2)

8. Nous pouvons maintenant appliquer notre calcul (jour+10, mois+2, année+23) :- en A10 : =A7+10- en B10 : =B7+2- en C10 : =C7+23

9. Nous allons maintenant recomposer la nouvelle date :- en A12 : =DATE(C10 ;B10 ;A10)

10.Effectuons cette opération en une cellule :© LD & W.S.C. - 11/05/23 - 07:09 - Page 58 sur 101 document.doc


- en B12 : =DATE(ANNEE(A2)+23 ;MOIS(A2)+2 ;JOUR(A2)+10)

11. Imprimez12. Revenez à la feuille Calendrier13.Modifiez le calendrier à fin que le premier de chaque mois soit lié avec le mois

précédent.14.Trouvez une solution afin que la fin de chaque mois soit gérée automatiquement.15. Imprimez16.Petit plus : à l’aide de la fonction JOURSEM() (cherchez dans l’aide en ligne

d’Excel) affichez des **** en face des dimanches.

17. Imprimez




Etape 6La

fonction

RECHERCHE






Objectif : Saisir - mise en forme - programmer des formules de calculs en utilisant la fonction recherche - enregistrer et imprimer.

Vous devez modifier la facture PC MAC.XLS réalisée précédemment (si tel n’est pas le cas, veuillez la réaliser en suivant les consignes de l’exercice correspondant) en ajoutant une feuille de suivi de stock, ainsi que l’affichage automatique des désignations et prix unitaires lors la saisie des références (dans la facture).

Consignes :1. Ouvrez le fichier PC MAC.XLS. Enregistrez le sous le nouveau nom (et sans

doute nouveau dossier) PC MAC2.XLS2. Ajoutez une feuille (à nommer STOCK) afin de saisir les stocks ci-dessous :

Etat des Stocks

Référence Désignation P.U.HT Qté Stock Stock mini A commanderR001 Boîte de 20 disquettes 3p1/2 HD 57,00 100 10R002 Barette V-RAM 256Ko MAC LC 75,00 10 2R003 Barette V-RAM 512Ko MAC LC 143,00 20 2R004 Câble imprimante PC 42,00 10 5R005 Boîte de 20 disquettes 3p1/2 DD 60,00 25 10R006 Clavier 102 Touches PC 112,00 5 10 Référence à commanderR007 Souris MicroSoft 2 170,00 14 20 Référence à commanderR008 Imprimante HP LaserJet 5 3800,00 8 2R009 Pc Pentium 75 / 16 Mo Multimédia 11450,00 15 2R010 Macintosh Performa 5200 11850,00 7 2

3. Ajoutez une dernière colonne où figurera le texte "Référence à commander" lorsque la quantité en stock est inférieure au stock mini.

Etat des Stocks

Référence Désignation P.U.HT Qté Stock Stock mini A commanderR001 Boîte de 20 disquettes 3p1/2 HD 57,00 100 10R002 Barette V-RAM 256Ko MAC LC 75,00 10 2R003 Barette V-RAM 512Ko MAC LC 143,00 20 2R004 Câble imprimante PC 42,00 10 5R005 Boîte de 20 disquettes 3p1/2 DD 60,00 25 10R006 Clavier 102 Touches PC 112,00 5 10 Référence à commanderR007 Souris MicroSoft 2 170,00 14 20 Référence à commanderR008 Imprimante HP LaserJet 5 3800,00 8 2R009 Pc Pentium 75 / 16 Mo Multimédia 11450,00 15 2R010 Macintosh Performa 5200 11850,00 7 2

4. Supprimez la feuille comportant la facture sans taux de remise (clic droit sur l’onglet FACTURE1)

5. Modifiez la facture (celle avec le taux de remise) comme indiquée page suivante. Programmez les calculs (n’oubliez pas l’affichage automatique de la désignation et du prix unitaire.

6. Enregistrez le document sous le nom de PC MAC2.XLS7. Imprimez




75000 Paris


Paris le 12/1/96


Référence Désignation Qté P.U.HT P.T.HT

R001 Boîte de 20 disquettes 3p1/2 HD 5 57,00 285,00

R003 Barette V-RAM 512Ko MAC LC 2 143,00 286,00

R006 Clavier 102 Touches PC 4 112,00 448,00

R008 Imprimante HP LaserJet 5 1 3800,00 3800,00


Taux Remise 5,00%Remise 240,95

Net H.T. 4578,05


Net à payer 5521,13






Etape 7Tri -

Protection de donné

es






Objectif : Protection des cellules - enregistrer et imprimer.

Vous devez modifier la facture PC MAC2.XLS réalisée précédemment (si tel n’est pas le cas, veuillez la réaliser en suivant les consignes de l’exercice correspondant) en protégeant les cellules.

Consignes :1. Ouvrez le fichier PC MAC2.XLS. Enregistrez le sous le nouveau nom (et sans

doute nouveau dossier) PC MAC3.XLS2. Déverrouillez les cellules de saisie dans la feuille FACTURE2.


75000 Paris


Paris le 12/1/96


Référence Désignation Qté P.U.HT P.T.HT

R001 Boîte de 20 disquettes 3p1/2 HD 5 57,00 285,00

R003 Barette V-RAM 512Ko MAC LC 2 143,00 286,00

R006 Clavier 102 Touches PC 4 112,00 448,00

R008 Imprimante HP LaserJet 5 1 3800,00 3800,00


Taux Remise 5,00%Remise 240,95

Net H.T. 4578,05


Net à payer 5521,13



N’oubliez pas de protéger la feuille (sans mot de passe SVP).

3. Enregistrez et imprimez


Zones de saisie






Etape 8Le

grapheur






Objectif : réalisation d’un graphique - enregistrer et imprimer.

Vous devez créer des graphique à partir du tableau Evolution du Chiffre d’affaire. Chaque graphique sera imprimé et intégré sous le tableau.

Consignes :1. Saisissez et mettez en forme le tableau ci-dessous :

2. Nommez la feuille SAFI et enregistrez le fichier sous le nom SAFI_graph.XLS



3. Réalisez le graphique histogramme :

4. Réalisez un graphique secteur pour le second trimestre :

5. Copiez la feuille SAFI (nommez la copie SAFI2) et transformez le premier graphique en courbes sur la feuille SAFI2 :

6. Enregistrez et imprimez.



La feuille SAFI :







Objectif : réalisation d’un graphique - enregistrer et imprimer.

Vous devez créer des graphique à partir du tableau contrôle de poids POIDS.XLS réalisée précédemment (si tel n’est pas le cas, veuillez la réaliser en suivant les consignes de l’exercice correspondant). Chaque graphique sera imprimé et intégré sous le tableau de contrôle de poids.

Consignes :1. Ouvrez le fichier POIDS.XLS. Enregistrez le sous le nouveau nom (et sans doute

nouveau dossier) POIDS2.XLS2. Réalisez le graphique courbe suivant l’évolution des différents poids de chaque

personne :

0,00

10,00

20,00

30,00

40,00

50,00

60,00

70,00

80,00

90,00

100,00

1/1/88 4/2/89 1/3/89 3/4/89 6/5/89 31/5/89 5/7/89

Pierre

Paul

Jacques

Yves

Marc

3. Réalisez un graphique courbe pour uniquement Pierre et Yves :

4. Enregistrez et imprimez.







Etape 9Feuilles liées

et consolidées






Objectif : réalisation de tableaux liés - enregistrer et imprimer.

Vous devez réaliser un récapitulatif de trois tableaux statistiques :

Tableau 1 Tableau 2

Tableau 3

Récapitulatif

Consignes :1. Saisissez chacun des tableaux dans une feuille différente (à nommer Tableau 1,

Tableau 2, Tableau 3)2. Créez le tableau Récapitulatif sur une dernière feuille.3. Enregistrez le fichier sous le nom RECAP_SEPT.XLS4. Imprimez.







Etape 10Liens OLE






Objectif : Importer de données d’Excel vers Word - enregistrer et imprimer.

Vous devez réaliser la note interne suivant (le tableau est à récupérer dans le fichier de l’Exercice 9.1 :) :

Consignes :1. Saisissez la note interne (page suivante).2. Insérez le tableau Récapitulatif (mise en forme modifiée) du fichier

RECAP_SEPT.XLS3. Enregistrez le fichier sous le nom Note_Récap.DOC4. Imprimez.



E n t r e p r i s e T r u c M u c h eZ o n e i n d u s t r i e l l e

6 5 0 0 0 T a r b e s

N o t e i n t e r n ed u 1 e r o c t o b r e 1 9 9 9

P é r i o d e d u 0 6 / 0 9 a u 2 4 / 0 9L u n M a r M e r J e u V e n

A n d r é 3 6 3 0 2 7 3 6 3 5L a u r e n t 3 5 2 4 3 3 4 1 3 6M a r i e 2 4 3 3 4 2 3 9 5 0P h i l i p p e 2 3 3 6 4 2 4 0 5 0




Objectif : Importer de données d’Excel vers Word avec liaison - enregistrer et imprimer.

Vous devez réaliser la note interne :

Consignes :1. Saisissez la note interne.2. Insérez avec liaison le tableau de calcul (fichier Excel à enregistrer sous le nom

BENE_TRUC_MUCHE.XLS

3. Enregistrez la note sous le nom Note_BENE.DOC4. Imprimez.



5. Modifiez la présentation du tableau dans la feuille BENE_TRUC_MUCHE.XLS

6. Imprimez.



Première version de la Note :


6 5 0 0 0 T a r b e s

N o t e i n t e r n eB é n é f i c e s a u 2 2 o c t o b r e 1 9 9 9

S e r v i c e s R e c e t t e s D é p e n s e s B é n é f i c e sP l a t e a u 1 5 0 1 3 2 1 8 T r o u 2 1 2 1 2 0 9 2 S o m m e t 1 8 0 1 7 5 5 T o t a u x 5 4 2 4 2 7 1 1 5



Seconde version de la Note :


6 5 0 0 0 T a r b e s

N o t e i n t e r n eB é n é f i c e s a u 2 2 o c t o b r e 1 9 9 9

S e r v i c e s R e c e t t e s D é p e n s e s B é n é f i c e sP l a t e a u 1 5 0 1 3 2 1 8 T r o u 2 1 2 1 2 0 9 2 S o m m e t 1 8 0 1 7 5 5 T o t a u x 5 4 2 4 2 7 1 1 5




Etape 11Divers - Synthès

es






Objectif : réalisation d’un bulletin de salaire - enregistrer et imprimer.

Vous devez réaliser le bulletin de salaire suivant :

Consignes :1. Saisissez le bulletin de salaire (voir page suivante)2. Programmation des calculs (voir pages suivantes)3. Mettez en forme (à soigner).4. Enregistrez le fichier sous le nom BULLETIN_TRUCMUCHE.XLS5. Imprimez.











Objectif : réalisation de bulletins de salaire liés - enregistrer et imprimer.

Vous devez réaliser les 12 bulletins de salaire à partir du fichier BULLETIN_TRUCMUCHE.XLS (page 90 Exercice 11.1 :). Vous réaliserez un tableau récapitulatif des charges patronales et salariales.

Consignes :1. Ouvrez le bulletin de salaire2. Enregistrez sous le nouveau nom : BULLETIN_TRUCMUCHE2.XLS3. Dupliquez et modifiez les bulletins de salaire (nommez chaque feuille par le

mois). Données à modifier. Saisissez les taux horaires suivants :

4. Créez la feuille récapitulative (voir page suivante).5. Imprimez chaque mois et les tableaux récapitulatifs.



Feuille Récapitulative :



Les douze bulletins de salaire :










Sommaire

ETAPE 1 PARAMÉTRER L’ÉCRAN, SAISIR, SAUVEGARDER, IMPRIMER DES DONNÉES 3

EXERCICE 1.1 : 3

ETAPE 2 MISE EN FORME DES DONNÉES 3

EXERCICE 2.1 : 3EXERCICE 2.2 : 3EXERCICE 2.3 : 3

ETAPE 3 FONCTIONS DE CALCULS PRÉPROGRAMMÉES 3

EXERCICE 3.1 : 3EXERCICE 3.2 : 3EXERCICE 3.3 : 3EXERCICE 3.4 : 3EXERCICE 3.5 : 3EXERCICE 3.6 : 3EXERCICE 3.7 : 3

ETAPE 4 FORMULES DE CALCUL EN RÉFÉRENCES RELATIVES OU ABSOLUES 3

EXERCICE 4.1 : 3EXERCICE 4.2 : 3

ETAPE 5 CALCULS CONDITIONNELS 3

EXERCICE 5.1 : 3EXERCICE 5.2 : 3EXERCICE 5.3 : 3

ETAPE 6 LA FONCTION RECHERCHE 3

EXERCICE 6.1 : 3

ETAPE 7 TRI - PROTECTION DE DONNÉES 3

EXERCICE 7.1 : 3

ETAPE 8 LE GRAPHEUR 3


ETAPE 9 FEUILLES LIÉES ET CONSOLIDÉES 3

EXERCICE 9.1 : 3



ETAPE 10 LIENS OLE 3


ETAPE 11 DIVERS - EVALUATION 3

EXERCICE 11.1 : 3


Documents

exercices pour tableurpasseport.univ-lille1.fr/site/bureautique/ewsc/excel/Exo... · Web viewVous devez réaliser une feuille de suivi d’opérations bancaires. La mise en forme