TP4 Révision Excel [PDF]

Zitiervorschau

TP4: Révision

TP N°4: Révision

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

I. Sommaire Automatique sous Word Créer ce sommaire automatique avec MS/Word :

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

II. Publipostage sous Word 1. Dans Microsoft Excel, créer une source de données avec 16 enregistrements de votre choix contenant les informations personnelles des personnes dont on veut envoyer les courriers. Puis enregistrez le fichier sous le nom de « bd» dans votre dossier dans « Mes Documents ». (Champs : Nom, Prénom, CNE, CIN, Date de naissance, ville) 2. Ecrire le document principal puis enregistrer-le dans votre dossier dans « Mes Documents ».

Attestation délivrée à : Nom et Prénom : CNE : CNI : Date de naissance : Ville : Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

II. Fonctions avancées sous Excel 1. Créer un classeur Excel nommé « Atelier-Révision ». 2. Dans une feuille nommée « Cordonnées », construire ce tableau: NOM

ADRESSE

CODE POSTAL

VILLE

Nom1

Adresse1

CP1

Ville1

Nom2

Adresse2

CP2

Ville2

Nom3

Adresse3

CP3

Ville3

Nom4

Adresse4

CP4

Ville4

Nom5

Adresse5

CP5

Ville5

Nom6

Adresse6

CP6

Ville6

Nom7

Adresse7

CP7

Ville7

Nom8

Adresse8

CP8

Ville8

Nom9

Adresse9

CP9

Ville9

Nom10

Adresse10

CP10

Ville10

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

II. Fonctions avancées sous Excel 3.

Dans une deuxième feuille nommée « Produits » , construire ce tableau:

CODE

DESIGNATION

PU

1

Produit1

50,00 €

2

Produit2

25,00 €

3

Produit3

30,00 €

4

Produit4

12,00 €

5

Produit5

25,00 €

6

Produit6

10,00 €

7

Produit7

120,00 €

8

Produit8

56,00 €

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

II. Fonctions avancées sous Excel

4. Dans une troisième feuille nommée « Remise », construire ce tableau:

Montant

Taux

- €

0,00%

100,00 €

250,00 €

1 000,00 €

2 500,00 €

2,00%

7,00%

10,00%

12,00%

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

II. Fonctions avancées sous Excel 5. Dans une quatrième feuille nommé « Facture » , construire ce tableau:

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

II. Fonctions avancées sous Excel 6. Compléter les cellules de fond gris. Indications : Montant Total = Quantité * PU Total marchandise = Somme des Montants Total hors taxe = Total marchandise - ( Total marchandise* Remise) TVA à 10% = Total hors taxe * 10% Total TTC = TVA à 10% + Total hors taxe

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

II. Fonctions avancées sous Excel B3

=AUJOURDHUI()

F4

=RECHERCHEV(F3;Coordonnées!A2:D12;2;FAUX)

F5

=RECHERCHEV(F3;Coordonnées!A2:D12;3;FAUX)

F6

=RECHERCHEV(F3;Coordonnées!A2:D12;4;FAUX)

B9

=RECHERCHEV(A9;produits!A$2:C$9;2;FAUX)

B10

=RECHERCHEV(A10;produits!A$2:C$9;2;FAUX)

B11

=RECHERCHEV(A11;produits!A$2:C$9;2;FAUX)

B12

=RECHERCHEV(A12;produits!A$2:C$9;2;FAUX)

B13

=RECHERCHEV(A13;produits!A$2:C$9;2;FAUX)

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

II. Fonctions avancées sous Excel

D9

=RECHERCHEV(A9;produits!A$2:C$9;3;FAUX)

D10 =RECHERCHEV(A9;produits!A$2:C$9;3;FAUX) D11 =RECHERCHEV(A9;produits!A$2:C$9;3;FAUX) D12 =RECHERCHEV(A9;produits!A$2:C$9;3;FAUX) D13 =RECHERCHEV(A9;produits!A$2:C$9;3;FAUX)

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

II. Fonctions avancées sous Excel 7. Compter le nombre total des produits.

8. Compter le nombre des produits dont le prix unitaire est supérieur à 40,00€. 9.Calculer la quantité totale des produits dont le prix unitaire est supérieur à 40,00€.

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

III. Bases de données sous Excel Dans cet exercice, nous allons créer le relevé de notes de la classe et faire apparaître la moyenne des élèves, ce qui nous donnera ensuite l’admission de l’élève en classe supérieure. 1. Créer un classeur Excel nommé « Relevé-Notes». 2. Dans une feuille nommée « Elèves », construire ce tableau

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

III. Bases de données sous Excel

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

III. Bases de données sous Excel 1. Dans la colonne Nom des élèves, limiter la longueur du texte entre 2 et 8 caractères. 2. Dans la colonne CNE, limiter la longueur du texte à 8 caractères. 3. Dans la colonne Genre, créer une liste contenant les deux choix « Féminin » et « Masculin». 4. Dans la colonne Date de naissance, spécifier le type date comprise entre 96 et 2003. 5. Dans les colonnes modules, afficher deux chiffres après la virgule. 6. Calculer les moyennes des élèves. 7. Afficher les mentions des élèves (moyenne > 15 –> Très Bien , moyenne > 12 –> Bien, moyenne > = 10 –> Passable, sinon Non Admis ). =SI(G4>15;"très bien";SI(G4>12;"Bien";SI(G4>10;"Passable";" "))) 8. Appliquer le formatage conditionnel si l’élève est non admis.(Remplissage en rouge). 9. Calculer la moyenne la plus répandu dans cette classe. 10. Calculer la dispersion des moyennes dans cette classe. 11. Compter le nombre total d’élèves.

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

III. Bases de données sous Excel 12. Compter le nombre de filles et de garçons. 13. Compter le nombre des non admis dans la classe, le nombre d’élèves ayant obtenu la mention Bien et la mention très bien. 14. Calculer la moyenne max et min da la classe. 15. Chercher la mention de l’élève « Nom7 ». 16. Chercher le nom de l’élève ayant obtenu la première note. La fonction RECHERCHEV permet de rechercher des données dans un tableau en retournant des données toujours situées à droite de la première colonne. Pour autant, il est parfois comme dans cet exemple , nécessaire de récupérer des informations qui sont situées à la gauche de votre colonne pivot et cela, la fonction RECHERCHERV ne sait pas le faire. =DECALER(A5;EQUIV(MoyenneMax;plage-moyenne;0);0)

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

III. Bases de données sous Excel La fonction EQUIV recherche un élément spécifique dans une plage de cellules, puis renvoie la position relative de l’élément dans la plage. EQUIV(valeur_cherchée, matrice_recherche, [type]) • valeur_cherchée (Obligatoire): Valeur dont vous voulez l’équivalent dans matrice_recherche. • matrice_recherche (Obligatoire): Plage de cellules dans laquelle s’effectue la recherche. • type (facultatif ): Nombre -1, 0 ou 1. L’argument type indique comment Excel compare l’argument valeur_cherchée aux valeurs de l’argument matrice_recherche. Valeur par défaut de cet argument : 1.

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

III. Bases de données sous Excel

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

III. Bases de données sous Excel La fonction DECALER renvoie une référence à une plage qui correspond à un nombre déterminé de lignes et de colonnes d’une cellule ou plage de cellules. La référence qui est renvoyée peut être une cellule unique ou une plage de cellules. Vous pouvez spécifier le nombre de lignes et de colonnes à renvoyer. =DECALER(Ref, Lignes, Colonnes, [Hauteur], [Largeur]) Ref : Référence de départ (une cellule ou une plage de cellules adjacentes) à partir de laquelle le décalage sera fait (obligatoire). Lignes : Nombre de lignes vers le haut (négatif) ou vers la bas (positif) dont la cellule supérieure gauche de la référence de départ sera décalée (obligatoire). Colonnes : Nombre de colonnes vers la gauche (négatif) ou vers la droite (positif) dont la cellule supérieure gauche de la référence de départ sera décalée (obligatoire). Hauteur : Nombre de lignes de la référence retournée (optionnel). Si omis, prend la même valeur que la référence de départ. Largeur : Nombre de colonnes de la référence retournée (optionnel). Si omis, prend la même valeur que la référence de départ.

Mme. ZROURI Amira Ingénieur d’état en Informatique

TP4: Révision

III. Bases de données sous Excel 12. Chercher le nom de l’élève ayant obtenu la première note. 13. Trier la base de données par ordre alphabétique. 14. Trier la base de données par ordre croissant (moyenne). 15. Filtrer la base de données par filière.

Mme. ZROURI Amira Ingénieur d’état en Informatique