Exercices Excel

Exercice 1 – Fonctions Somme et Moyenne 1. Créer un nouveau classeur Face à Face 2. Réaliser le bilan des ventes de vêtements pour le 4ème trimestre 2001 à l’aide des données concernant les ventes des secteurs Hommes, Femmes, Enfants et Divers On demande de calculer le chiffre d’affaires total pour chaque mois, le chiffre d’affaires total par rayon, le chiffre d’affaires moyen par mois et par rayon ainsi que le pourcentage du chiffre d’affaires par mois pour chaque rayon et sur le trimestre. Catégories

Octobre

Novembre

Décembre

Hommes

25890

19536

6589

Femmes

56598

49651

15422

Enfants

36588

14799

12548

Divers

5690

5410

3258

3. Centrer les titres des colonnes en police taille 14 et gras, ajouter un titre " Bilan " en taille 24, centrez-le sur 8 colonnes (2 manières différentes)

4. Les pourcentages obtenus ainsi que les moyennes vous sont présentés avec trop de décimales. Utilisez les fonctions d’arrondis appropriées afin d’obtenir un résultat " lisible "

5. Tester diverses mises en forme.

Exercice 2 Fonction OU, ET et NB.Si Pour une étude, un professeur a fait remplir un questionnaire à ses étudiants. Ce dernier comprend les champs nom, prénom, sexe, année. D’autres champs sont venus compléter sa base. Ils sont relatifs au fait que les étudiants possèdent ou non un ordinateur, ont ou n’ont pas connexion à Internet. De plus, ils ont dû évaluer leur niveau de connaissance dans les logiciels Word, Excel et Access. Les critères de référencement étaient : Aucune, Faible, Bonne, Moyenne et Très bonne. Les réponses des étudiants sont dans le fichier Exercice2.xls On vous demande de faire apparaître :

1. Une étoile à côté des personnes ne possédant pas d’ordinateur. 2. De faire apparaître FAUX ou VRAI pour les personnes qui possèdent un ordinateur et une 3. 4. 5.

connexion à Internet De faire apparaître FAUX ou VRAI pour les personnes qui possèdent un ordinateur ou qui sont de sexe féminin. De faire apparaître FAUX ou VRAI pour les personnes qui ont une bonne connaissance en Word et une connaissance moyenne en Excel. De faire apparaître FAUX ou VRAI pour les personnes qui ont une bonne connaissance en Word ou une connaissance moyenne en Excel.

6. De calculer le nombre de personne(s) qui ne possède(nt) pas d’ordinateur.

Exercice 3 Fonction NB.Si et Si Dans le cadre d’un établissement scolaire, la direction vous demande de rédiger en Excel un " bulletin ". Chaque étudiant a subi 5 examens dans des branches dont les poids respectifs sont 3, 2, 5, 1.5 et 9. Chaque cote est encodée sur 20.

Nom

Poids

3

2

5

1,5

9

Prénom

Cote 1

Cote 2

Cote 3

Cote 4

Cote 5

Durand

Charles

12

7

10

10

10

Delor

Anne

17

18

14

19

12

Tahon

Carl

8

14

17

5

20

Moore

Roger

12

10

11

18

14

Maroit

Virginie

19

13

15

20

19

Charles

Louis

18

8

14

5

6

Amand

Adam

11

10

11

14

11

Il vous est demandé :

1. de calculer la moyenne pondérée de chaque étudiant, 2. de calculer la moyenne de la classe, 3. d’indiquer dans une colonne l’appréciation suivante : • • • • •

si l’étudiant à un ou plusieurs échecs ou que la moyenne de cet étudiant est en dessous de 12 : Echec, si l’étudiant à une moyenne comprise entre 12 et 14 : Satisfaction, si l’étudiant à une moyenne comprise entre 14 et 16 : Distinction, si l’étudiant à moyenne comprise entre 16 et 18 : Grande distinction, si l’étudiant à une moyenne comprise entre 18 et 20 : La plus grande distinction.

1. de trier les données par ordre alphabétique. 2. de représenter les résultats obtenus sur un graphique approprié 3. de sauvegarder ce graphique sur une nouvelle page appelée Graphique.

Exercice 4 Nb.si, Somme.si et Mise en forme conditionnelle Dans le cadre de la maîtrise des coûts, votre directeur général vous demande de réaliser le relevé récapitulatif des photocopies par service. Vous disposez du relevé mensuel suivant : Relevé mensuel N° service

Nombre de photocopies

41

125

43

154

42

78

45

42

43

65

41

14

42

87

45

69

44

137

43

198

41

12

44

29

Pour chaque demande de photocopies, vous disposez donc du numéro du service ainsi que du nombre de photocopies effectuées. On vous demande :

1. 2. 3. 4. 5. 6. 7.

de créer un classeur Photocopies, d’indiquer les données dans une feuille intitulée " Relevé ", de " créer " une feuille " Bilan ", dans la feuille " Bilan " vous indiquerez, pour chaque service, le nombre de demandes, le nombre total de photocopies réalisées ainsi que le pourcentage global de photocopie du mois, de faire apparaître en rouge le pourcentage le plus élevé, de faire apparaître en bleu le pourcentage le moins élevé, de représenter sur un graphique de type " Secteurs ", le pourcentage de photocopies demandées par chaque service,

8. de placer le graphique dans une feuille intitulée " Graphique ".

Exercice 5 Somme.si et RechercheV Le Directeur de la Société " Cuisine " vous demande de préparer un " document " permettant de réaliser un certain nombre de calculs suite à la participation de la société au Salon " Batirama ". Cette société est spécialisée dans la fabrication et la vente de cuisines équipées. Vous disposez des deux tableaux suivants. Le premier vous renseigne sur les commissions accordées en fonction du type de produit vendu ; le second constitue le relevé des ventes par catégorie réalisées par les 4 vendeurs. Catégorie

Type

% commission

1

Meubles de cuisine

10

2

Electroménager

5

3

Décoration

15

4

Autres

10

Ventes de la journée Vendeur

Catégorie

Montant

Luc

2

6800

Marc

1

23500

Luc

1

45600

Etienne

3

2500

Marc

2

13400

Luc

4

4500

Jacques

1

43500

Jacques

2

34000

Luc

3

17000

Marc

4

21000

Etienne

1

32700

Jacques

3

2100

Le Directeur vous demande :

1. de calculer le total des ventes effectuées chaque jour pour chacun des vendeurs, 2. de calculer le montant des commissions dues à chacun des vendeurs, 3. de calculer le montant global des ventes réalisées pour chaque type de produit Remarque : si l’appellation du type de produit change, cette appellation doit également changer dans le tableau ci-dessous.

Vendeur

Commission totale

Luc Marc Jacques Etienne Type de produit

Ventes totales

Exercice 6 Vous devez vous occuper de la gestion du stock du magasin " Au bel Article ". Vous connaissez, pour chaque article, son numéro, le stock existant ainsi que son prix. Pour chaque article, en fin de semaine, on note la quantité vendue (si on a en a vendu). La liste des prix, les ventes par semaine, l’état du stock et les chiffres d’affaires sont sur des feuilles séparées appelées respectivement : ListePrix, Ventes, Stock et CA. On vous demande de :

1. mettre à jour le stock, 2. de calculer le chiffre d’affaires pour chaque article, 3. de calculer le chiffre d’affaires total de la semaine, 4. d’afficher en rouge gras les stocks nuls dès qu’ils le sont

Ci-dessous, les données sont en grisé. Liste des prix n° article

Prix

1

221

2

325

3

158

4

758

5

451

6

89

7

129

8

430

9

700

10

599

Ventes Semaine 1

Semaine 2

Semaine 3

Semaine 4

n° article

quantité

n° article

quantité

n° article

quantité

n° article

quantité

1

3

1

1

1

5

1

7

2

2

2

4

3

2

2

2

3

2

3

5

5

8

3

9

5

8

4

7

6

7

4

3

6

3

5

3

7

3

9

1

7

5

7

2

10

7

8

8

8

2

9

7

10

12 Stock début semaine

Semaine 1

Semaine 2

Semaine 3

Semaine 4

Semaine 5

Article

Stock

Stock

Stock

Stock

Stock

1

20

2

18

3

21

4

30

5

19

6

20

7

16

8

25

9

18

10

19 Chiffre d'affaires

Article 1 2 3

Semaine 1

Semaine 2

Semaine 3

Semaine 4

Total

… 10

Exercice récapitulatif Créer un nouveau classeur au nom explicite, mettre les données sur une (ou plusieurs) feuille(s) et les résultats sur d'autres feuilles. Tester différents types de graphiques et choisir les plus appropriés à décrire vos résultats.

En attendant un système plus performant, le système de gestion des vols de la compagnie OLAIR se fait par feuille de calcul sous EXCEL. Le classeur comprend 3 feuilles: a. Vols : c’est la liste des vols disponibles

N° Vol

Type d'appareil

Liaison

Capacité

Prix de Date la départ place

Heure départ

LP1 Airbus A300 Londres Paris 20 142 18-déc 15:00 LP2 Airbus A300 Londres Paris 20 175 25-déc 15:00 LP3 Airbus A360 Paris Londres 20 133 12-déc 19:00 NYP1 BOEING 747 New york Paris 35 717 20-déc 7:45 NYP2 BOEING 747 New york Paris 35 783 27-déc 7:45 NYP3 BOEING 747 New York Paris 50 750 2-janv 15:15 PL1 Airbus A300 Paris Londres 20 142 18-déc 12:45 PL2 Airbus A300 Paris Londres 20 200 25-déc 12:45 PNY1 BOEING 747 Paris New york 35 800 19-déc 7:45 PNY2 BOEING 747 Paris New york 35 700 26-déc 7:45 PR1 Airbus A320 Paris Rome 25 163 15-déc 12:45 PR2 Airbus A320 Paris Rome 25 163 22-déc 12:45 RP1 Airbus A320 Rome Paris 25 170 15-déc 18:00 RP2 Airbus A320 Rome Paris 25 170 22-déc 18:00 b. Remises : c’est la liste des remises applicables suivant le type du passager (si un client achète plusieurs places, le même taux de remise est appliqué à toutes les places) Code

Désignation

Taux

1 Abonnement annuel

22%

2

20%

Abonnement mensuel

3 Abonnement groupe

10%

4 Carte famille

12%

5 Employé de l'agence

100%

6 Enfant -10 ans

10%

7 Personne +90 ans

90%

8 Club

10%

9 Club d'or

15%

10 Poids > 120 Kg -20% c. Passagers : c’est la feuille principale qui permet d’enregistrer les réservations Nom du client

N° Vol

Type client

Nbre de places

DESIR

PL1

1

10

VARLET

LP2

10

1

DETHIER

PNY1

5

10

MERLIN

PL1

2

2

HUBAUT

PNY2

3

3

SERVAIS

LP2

2

2

SIMPSON

RP2

8

3

BLANCHARD

RP2

2

4

On demande: •

• • • • •

•

pour chaque réservation de la feuille "Passagers", de calculer le montant brut à payer, d'afficher la remise en %, de calculer le montant net et d'afficher le type d'avion de mettre en rouge la facture la plus élevée et en vert la remise totale la plus élevée pour chaque vol de la feuille "Vols" d'indiquer le nombre de places restantes de déterminer, pour chaque catégorie de passagers de la feuille "Remises", le nombre de réservations sur une nouvelle feuille "Avions", de faire le bilan en terme de nombre de passagers et chiffre d'affaire net total pour chaque type d'avion de tracer un histogramme reprenant la remise totale de chaque client avec en abscisse son nom Les résultats doivent se trouver sur les feuilles précisées dans les questions précédentes et le graphique sur une feuille séparée "Graphique". Soigner la mise en page