[PDF] & Travaux Pratiques et Examens Sous EXCEL Avec solutions

View - Download & Travaux Pratiques et Examens Sous EXCEL Avec solutions

Previous PDF

Next PDF

1 Travaux Pratiqueset ExamensSousEXCELAvec solutions 2 3

Travaux Pratiques sur Excel-Atelier n°1Calcul avec des formules simples, en utilisant des fonctions et la Miseen formed'un tableau1°/ Saisir le tableau suivant dans la feuille de calculfeuil1:Tableau récapitulatif de ventes de Matériels Informatiques de l'année 2010MoisQuantitéPrix UnitaireTotal HTTVA (20%)Total TTC1Janvier20200002Février25250003Mars4070004Avril1060005Mai1550006Juin50800007Juillet3025008Août5120009Septembre603500010Octobre504000011Novembre504000012décembre70280002°/ Calculer pour les douze mois:Total HT:HT= Q*PUTVA:TVA=HT* ttva (on suppose que le taux de tva est 20%)Total TTC:TTC=HT+TVA

4

3°/ Copier le tableau de la feuil1 vers la feuil2. Effacer les calculs effectués.Recalculer HT, TVA et TTC en utilisant des fonctions et le tauxde TVA commeréférence absolue (on suppose par exemple que le taux de TVA est situé dans lacellule de référence absolue $B$1)

4°/ Copier le tableau de lafeuil2vers lafeuil3:Recalculer HT, TVA et TTC en utilisant des fonctions etCompléter letableau enrajoutant 4 lignes permettant de :Calculer la somme Annuelle: Quantité, Prix unitaire, Total HT, TVA etTTCCalculer la Moyenne: Quantité, Prix unitaire, Total HT, TVA et TTCCalculer le Minimum : Quantité, Prix unitaire, Total HT, TVA et TTCCalculer le Maximum : Quantité, Prix unitaire, Total HT, TVA et TTC

5

5°/ Mettre en forme les tableaux et Enregistrer votre classeur: Atelier_1.xls

6

Travaux Pratiques sur Excel-Atelier n°2La fonction Si() simple, Si() imbriquée,Et(), ou(), somme.si() et nb.si()Partie1:1°/ Saisir le tableau suivant dans la feuil1 :Notes des étudiants (semestre 1)NomNote1Note2Note3MoyenneObservationAhmed41210Amine101310Mostafa171312Nour5912salim453Rabii9,59,59,5Meilleure noteMauvaise note2°/ Calculer la moyenne, la meilleure et la mauvaise note.

3°/ Afficher l"observation selon la moyenne calculée (utilisation de la fonction si ()simple) :Si la moyenne est <10afficherajournéSinonafficherAdmis

4°/ Copier le tableau de la feuille1 vers la feuille2.

7

Afficher l"observation selon les conditions suivantes: (utilisation des fonctions siimbriquées)Si Moyenne est <5afficherajournéSi lamoyenne est >=5 et <10afficherrattrapageSinonafficheradmis

5°/ Copier le tableau de la feuille2 vers la feuille3.Afficher l"observation selon les conditions suivantes: (utilisation des fonctions siimbriquées)Si la moyenne est>=0et<5afficherajournéSi la moyenne est >=5et<10afficherrattrapageSi la moyenne est >=10et<12afficherpassableSi la moyenne est >=12et<14afficherAssez BienSi la moyenne est >=14et<16afficherBienSinonafficherTrès Bien

8

6°/ Copier le tableau de la feuille1vers la feuille4.Afficher l"observation (utilisez uniquement l"observation admis, rattrapage et ajourné)selon les conditions :Si la moyenne est <5afficherajournéSi la moyenne est >=5 et <10afficherrattrapageSinonafficherAdmisN.B. La mention "admis" peut être affectée aux étudiants si toutes les notes sont>=9.5 et la moyenne<10 (Utiliser la fonction Si() avec et-ou)

7°/ Dans la feuille 2: Utiliser la fonction NB.SI () pour afficherle nombre d'étudiants qui ne sont pas admis.le nombre d'étudiants qui ont un rattrapage.le nombre d'étudiants qui sont admis.

9

8°/Mettre en forme et enregistrer votre classeur: Atelier_2.xlsPartie2 (complément):(La fonctionSOMME.SI())On considère le tableau des Prévisions mensuelles.Prévisions mensuellesRéférenceQuantitéPrix UnitaireHTPromotiontaux de remiseHT avec remisea00110250oui15%b00220400oui25%c00330560non10%d00440900non5%e00550300oui30%1°/Calculer le prix hors taxe HT (formule simple)Calculer le prix hors taxe HT selon la promotion.

Utilisation de la fonction Somme.si ()

10

Afficher le Total HT de tousles produits dont la promotion est égale à ouiAfficher le Total HT de tous les produits dont la promotion est égale à non

2°/Mettre en forme et enregistrer votre classeur: Atelier_2_complement.xlsTravaux Pratiques sur Excel-Atelier n°3Manipulation des filtres automatiques et élaborésPartie1:Liste des étudiantsCodeNomNote1Note2Note3MoyenneObservation100Ahmed412108,67rattrapage200Amine10131011,00admis300Mostafa17131214,00admis400Nour59128,67rattrapage500salim4534,00ajourné600Rabii9,59,59,59,50rattrapage1°/Utilisation du Filtre Automatique :Afficher des données selon des critères (Code, Nom,....Moyenne etc.)1.Afficher les 3 premiers étudiants de la liste.2.Afficher les 3 derniers étudiants de la liste.3.Afficher les étudiants dont les codes sont compris entre 300 et 500.4.Afficher les étudiants dont les noms commencent par A et se terminentpar e5.Afficher les étudiants qui ont un rattrapage (avec deuxmanières).6.Afficher les étudiants qui ont un rattrapage et dont les codes sontsupérieurs ou égaux à 400.2°/Utilisation du Filtre Elaboré ou Avancé:

11

1.Afficher les étudiants dont les codes sont inférieurs ou égaux à 200.2.Afficher les étudiants dont les codes sont compris entre 300 et 500.

3.Afficher les étudiants qui ont une note égale 3.

4.Afficher les étudiants dont les codes sont compris entre 300 et 500 et qui ontune note égale 3.

5.Afficher les étudiants qui ont toutes les notes quisont égales à 9.5.6.Afficher les étudiants dont les noms commencent par A.

12

7.Afficher les étudiants dont les noms se terminent par e.8.Afficher les étudiants dont les noms commencent par A et se terminent par e.Première ManièreDeuxièmeManière9.Afficher les étudiants qui ont un rattrapage avec deux manières.

10.Afficher les étudiants qui ont un rattrapage et dont les codes sont supérieurs ouégaux 400.

11.Afficher les étudiants dont la moyenne dépasse 9. (Utiliser lafonctionmoyenne)

12.Afficher les étudiants dont la moyenne est comprise entre 9et 12(Utiliser lafonctionmoyenne)

13 14

13.Afficher les étudiantsqui ont obtenu lameilleure moyenne. (Utilise r lafonctionmoyenne et max)

14.Afficher les étudiants quiont obtenu la mauvaise moyenne. (Utiliser lafonctionmoyenne et min)

Partie2(Complément): La fonction BDSOMME()=BDSOMME(éte ndue de l a list e de donnée s; numé ro de l a col onne àadditionner; étendue de la zone de critères)Cettefonction affiche le total d'un certain champ numérique selon les critèresdemandés.Liste des employés

1.Afficher les employés qui ont le meilleur salaire.2.Afficher la somme des salaires des employés.3.Afficher la somme des salaires des employés de sexe Féminin

Zone 14

Zone 13

15

4.Afficher la somme dessalaires des employés de sexe Masculin et dont lacatégorie est supérieure ou égale à 3.5.Afficher les employés dont le salaire est compris entre 5000 DHS et 10000 DHS6.Afficher les employés dont le salaire n"est pas compris entre 5000 DHS et 10000DHS7.Afficher les employés de sexe M ou F et dont le salaire dépasse 10.

16

Travaux Pratiques sur Excel-Atelier n°4Manipulation de recherche: RechercheV() et RechercheH()I.Utilisation de la fonction Recherche VerticaleOn considère le tableau contenantla liste de produitsde la forme suivante:Liste de produitsréférencedésignationPrixttvaprod1Unités250020%prod2Ecrans100020%prod3Claviers25014%prod4Souris607%prod5Caméras15020%1°/Donner les formules permettant de:1.Afficher la désignation, le prix et le taux de tva2.Calculer le montant TTC de chaque ligne de commandeCommandesréférencedésignationdatequantitéPrixTauxMontant TTCprod301/12/0095prod405/12/20092prod510/12/200910prod113/12/200925prod516/12/200914

17

2°/Donner la formule permettant decalculer le Total TTC de chaque produit:référencedésignationTotal TTCprod1Unitésprod2Ecransprod3Claviersprod4Sourisprod5Caméras

18

II.Utilisation de lafonction Recherche horizontaleOn considère le tableau contenant la liste de produits dont la structure est la suivante:Liste de produitsrefprod1prod2prod3prod4prod5designationUnitésEcransClaviersSourisCamérasprix2500100025060150ttva20%20%14%7%20%1°/Donner les formules permettant de:1.Afficher la désignation, le prix et le taux de tva2.Calculer le montant TTC de chaque ligne de commandeCommandes de produitsréférencedésignationdatequantitéPrixTauxMontant TTCprod301/12/0095prod405/12/20092prod510/12/200910prod113/12/200925prod516/12/200914

19

Travaux Pratiques sur Excel-Atelier n°5Tableaux croisés dynamiquesObjectifs:Un rapport de table croisé dynamique donne la possibilité d'examiner etd'analyser les données d'un tableau selon plusieurs points de vue.Il offre aussi la possibilité de créer des représentations graphiques dynamiquesqui dépendent de l'analyse effectuée.On considère le tableau suivant:Exportations en Textile et Habillement (en tonnes) du MarocVers l'union européenne pour les années 2001, 2002 et 2003

N.B. Les données du tableau ci-dessus sont aléatoires. Elles ne représentent paslaréalité.Travail à faire :1. Créer un tableau croisé représentant le total en tonnes des exportations réalisées parproduit et par pays durant les trois années.-Cliquez sur le menuDonnées\Rapport du tableau croisédynamique...)-Suivez les étapes.2.A partir de cette présentation afficher, dans des feuilles séparées pour chaqueannée, les exportations par produit et par pays.3. Modifier le tableau initial et actualiser les données.4. Donner la représentation graphique en histogramme et analyser là selon les produitset les années.5. Représenter en secteurs les exportations du "textile" sur les trois années.6. Ajouter un champ calculé permettant d'afficher la somme des exportations dechaque produit sur l'ensemble de pays.

20

Exemple d'application :Présentation des Produits par FilialeProduitAnnéeFiliale CasaFiliale RabatFiliale KénitraChemise200247 800,0015 700,0030 000,00Tee-Schort200259 400,0010 200,0034 000,00Pantalon200280 200,0013 000,0027 000,00Chemise200363 200,0024 500,0039 540,00Tee-Schort200370 000,0016 400,0028 200,00Pantalon200385 700,0015 300,0030 500,00Chemise200478 600,0028 900,0045 000,00Tee-Schort200476 300,0018 600,0031 500,00Pantalon200495 100,0018 400,0033 800,00Travail à faire :1. Créer un tableau croisé représentant le total des ventes réalisées par produit et parfiliale durant les 3 années.2. A partir de cette présentation afficher, dans desfeuilles séparées pour chaqueannée, le bilan des ventes par produit et par filiale.3. Modifier le tableau initial et actualiser les données.4. Donner la représentation graphique en histogramme et analyser là selon produitset années.5. Représenteren secteurs les ventes du produit "Chemise" sur les trois années.6. Ajouter un champ calculé permettant d'afficher la somme des ventes de chaqueproduit sur l'ensemble de filiale.

21

Travaux Pratiques sur Excel-Atelier n°6Représentation graphiqueinteractiveEléments Nécessaires:Utilisation de fonction:Index()Utilisation d'une liste déroulanteCréation du graphique interactifBilans mensuelsRubriquesJanFévMarsAvrilMaiJuinJuilletAoûtAppels12001250130015001700175018001600Ventes100120130150160170185140Chiffresd'affaires4500054000585006750072000765008325063000Charges2000020000200002500030000300003000030000Résultats25000340003850042500420004650053250330001.Représenter en histogramme les ventes mensuelles2.Représenter en secteurs les charges mensuelles

22
23

Exercice 1:La prime mensuelle des salariés d"une entreprise est calculée enfonction des nombres d"enfants de ces salariés. Les informations disponibles sur lessalariés sont résumées dans le tableau suivant:

a-Donner la formule qu"il faut appliquer à la cellule D2 pour calculer les primesmensuelles sachant que:-pour les salariés n"ayant pas d"enfants: Prime = 0-pour les salariés ayant 1 ou 2 enfants: Prime= 50 par enfant-pour les salariés ayant 3 enfantsou plus:Prime= 100 parenfant.b-Donner la formule permettant de calculer la prime totale des salariés de sexeM ou Fc-Donner la formule permettant de calculer le nombre des salariés de sexe Fou Md-Donner les formules permettant de calculer le nombre des salariés qui n"ontpas d"enfants ainsi que la prime totale.e-Donner la formule permettant de faire une augmentation des primes selon lesconditions suivantes:SexeF(DH)SexeM(DH)0200250>=1 et <=2500700Plus de 36001000Formule=prime1+augmentation

24
e-/

Exercice 2:

1°/Donner la formule qu"il faut appliquer à la cellule E2 pour calculer le salairesachant que:Salaire= (Nombre d"heures × Indemnité d"ancienneté) + Prime aukilométrage

25

SolutionE2=D2*SI(B2<2;$H$2;SI(B2<=4;$H$3;$H$4))+SI(C2<500;0;SI(C2<=1000;500;750))Exercice3:Il s'agit de calculer le montant que doit payer chaque client ayant passé un nombre denuitées dans un hôtel. Pour cela on a résumé les informations nécessaires dans letableau suivant:

a-Donner la formulequ'il faut appliquer à la cellule E2 pour calculer le montantsachant que:Montant=(Nombre de nuitées x Pension) x (1-taux_de_remise)Le taux de remise est définie par :-Si nombre de nuitées est strictement inférieur à 5alorstaux_de_remise=0-Si nombre de nuitées est entre 5 et 10alorstaux_de_remise=10%-Sinontaux_de_remise=20%Exercice 4:

1°/Donner la formule qu"il faut appliquer à la cellule E2 pour calculer le salairesachant que:Salaire= (Nombre d"heures × Indemnité d"ancienneté) + Prime aukilométrage

26

SolutionE2=D2*SI(B2<2;$H$2;SI(B2<=4;$H$3;$H$4))+SI(C2<500;0;SI(C2<=1000;500;750))2°/Donner la zone de critères pour définir un filtre élaboré permettant d"afficher leschauffeurs ayant une ancienneté n"est pas comprise entre 2 et 4 et qui ont réalisé unvolume horaire dépassant 150 heures.Façon1AnciennetéNombred'heures<2>150>4>150Façon2AnciennetéAnciennetéNombred'heures<2>150>4>1503°/Donner la formule qui permet de calculer la somme des salaires des chauffeursdont le kilométrage parcouru est compris entre 1000 et 2000 KM.Façon1:=SOMME(E2:E8)-SOMME.SI(C2:C8;"<1000";E2:E8)-SOMME.SI(C2:C8;">2000";E2:E8)Façon2:zone de critèresKilométrageKilométrage>=1000<=2000et=BDSOMME (A1:E8;E1;B28:C29)NB:La plage A1:E8 base de données (tableau principal)E1 ou le mot"Salaire»La plage B28:C29 désigne la zone de critères

27

Exercice 5:

1°/Donner la formule qu"il faut appliquer à la celluleG3permettant de déduire lenom de chaque vendeur.Solution=RECHERCHEV (F3;$A$3:$D$8;2)2°/Donner la formule qu"il faut appliquer à la celluleB14pour calculer la sommedes quantités (Total ventes) réalisé par chaque vendeur.Solution=SOMME.SI ($F$3:$F$10; A14; $I$3:$I$10)3°/Donner la formule qu"il faut appliquer à la celluleG14pour calculer la sommedes quantités vendues par région (Total quantités).=SOMME.SI ($H$3:$H$10; F14; $I$3:$I$10)4°/Donner la formule qu"il faut appliquer à la celluleC14pour calculer le salaireréel d"un vendeur sachant que:Salaire réel= salaire de base + prime de ventesPrime de ventes est calculé selon les conditions suivantes:Sitotal ventes<500 alorsPrime de ventes= 200Sitotal ventesest comprise entre 500 et 1000alorsPrime de ventes=500SinonPrime de ventes=700 sistatut ="A»sinonPrime de ventes=600Solution:Salaire réel=Salaire réel = salaire de base + prime de ventesFaçon1:=D3+SI (B14<500;200;SI(B14<=1000;500;SI(C3="A";700;600)))Façon2:

28

=RECHERCHEV (A14;$A$3:$D$8;4) ++SI (B14<500;200;SI(B14<=1000;500;SI (RECHERCHEV (A14;$A$3:$D$8;3)="A";700;600)))Exercice6:Pour gérerles ventes du mois décembre 2011 de quatre produits: Crayons, Gommes,feutres et papiers, ondécide de mettre en placetroistableaux.Tableau n°1: Comprendlaliste de produits (Référence, Désignation et Prix).Tableaun°2: Présente les ventes qui sont réalisées en mois décembre 2011.Tableau n°3: Comprend un récapitulatif sur les ventes qui sont réalisées pour chaqueproduit.Les informations disponibles sont résumées de la façon suivante:

1°/Donner lesformulesqu"il faut appliquer à la celluleG3 et H3pourdéduireladésignation et le prix de chaque produitqui sontsituésdans letableau n°1.G3==RECHERCHEV(F3;$A$3:$C$6;2)H3==RECHERCHEV(F3;$A$3:$C$6;3)2°/Donner la formule qu"il faut affecter à la celluleJ3pourcalculerle montant HTde chaque commande.J3=H3*I3OuJ3=PRODUIT(H3;I3)3°/Donner lesformulesqu"il faut affecter à la celluleM3 etN3pourcalculerlaquantité total commandée et le total HT pour chaque produit.M3==SOMME.SI($G$3:$G$9;"="&L3;$I$3:$I$9)N3==SOMME.SI ($G$3:$G$9;"="&L3;$J$3:$J$9)Exercice7:

29

Pour chaque vendeur la prime et la commission est un pourcentage du chiffred"affairesCAselon la région. Son revenu total (Rev.TOT) est la somme des deuxéléments.Les informations disponibles sur les vendeurs sont résumées dans letableausuivant:

a)Donner la formule qu"il faut appliquer à la celluleD2pour calculer la Prime,la formule qu"il faut appliquer à la celluleE2pour calculer la commission(COM) et la formule qu"il faut appliquer à la celluleF2pour calculer lerevenu total(Rev.TOT).Prime=CA x taux de la primeetCommission=CA x taux de lacommissionRev.TOT=Prime + CommissionD2=C2*SI(B2="nord";$B$11;SI(B2="sud";$B$12;$B$13))ouD2=C2*SI(B2="nord";20%;SI(B2="sud";25%;15%))E2=C2*SI(B2="nord";$C$11;SI(B2="sud";$C$12;$C$13))ouD2=C2*SI(B2="nord";5%;SI(B2="sud";4%;3,50%))F2= D2+E2ou =somme (D2; E2)b)Donner la formulepermettantd"ajouter une prime additionnelle de 3000 DHSau revenu total (Rev.TOT)deSAMIR si sonCA>= 20000.=F2+SI(ET(A2="Samir";C2>20000);3000;0)c)Donner la formule qui permet de calculer le total des chiffres d"affaires desvendeurs de la régionNORD.=SOMME.SI ($B$2:$B$9;"nord";$C$2:$C$9)d)Donner la zone de critères pour définir un filtre élaboré permettant d"afficherles vendeurs de la régionNORDouSUDqui ont unchiffre d"affairesCAcompris entre50000et120000.RégionCACA

30

Nord>=50000<=120000Sud>=50000<=120000e)Donner la zone de critères pour définir un filtre élaboré permettant d"afficherles vendeurs qui ontunchiffre d"affairesCA>=Moyenne du CA.Exercice8:Le salaire net de chaque vendeur est la somme de la prime qui est un pourcentage dusalaire de base selon la ville et l"indemnité par sexe.Les informations disponibles sur les vendeurs sont résumées dans letableausuivant:

1.Donner lesformulesqu"il faut appliquerauxcellulesE2et F2pour calculerrespectivement laPrimeet leSalaire Net.Prime=Salaire de basex taux de la primeetSalaire Net=Salaire de base +Prime +Indemnité par sexe2.Donner lesformulesqui permettentde calculer le total dessalaires Netdesvendeurs desexeMasculin(M)et de sexeFéminin(F).3.Donner la formule qui permet de calculer le total dessalaires Netdes vendeursde lavilleCasablancaet de sexeMasculin(M).4.Donner la zone de critères pour définir un filtre élaboré permettant d"afficherles vendeurs de lavilleRabatetFesqui ont unsalaire Netcompris entre4000et6000.5.Donner la zone de critères pour définir un filtre élaboré permettant d"afficherles vendeurs de sexemasculin(M)de la villeCasablancaqui ont lemeilleursalaire Net.

quotesdbs_dbs3.pdfusesText_6

[PDF] examen externe histoire

[PDF] examen fin d'études secondaires luxembourg 2017 resultats

[PDF] examen fin d'études secondaires luxembourg 2018

[PDF] examen fonction publique 2016

[PDF] examen fonction publique 2017

[PDF] examen fonction publique québec 2015

[PDF] examen gestion de production corrigé

[PDF] examen gestion de projet pdf

[PDF] examen grh pdf

[PDF] examen habileté ? comprendre une loi et un règlement

[PDF] examen habiletés professionnelles personnel technique

[PDF] examen habiletés professionnelles pour le personnel professionnel

[PDF] examen histoire des sciences st

[PDF] examen histoire des sciences usthb

[PDF] examen html corrigé pdf