EXERCICES EXCEL

1 - Somme (somme automatique)

L'entreprise SOMMES emploie 4 représentants. Chacun d'entre eux a effectué un chiffre d'affaires qu'il vous
communique. Présentez ces chiffres sous la forme d'un tableau qui fera apparaître les totaux annuels par
représentant et le total du chiffre d'affaires de chaque trimestre pour l'ensemble des 4 représentants.
M. MAXIME
87 200 € au 1er trimestre, 88 200 € au second trimestre, 75 700 € au troisième trimestre et 94 200 € au
quatrième trimestre.
M. LEGENDRE
1er trimestre : 66 200 €
2ème trimestre : 72 200 €
3ème trimestre : 70 200 €
4ème trimestre : 83 200 €
M. MARTINALE
Trimestre 1 : 104 200 €, trimestre 2 : 100 800 €, trimestre 3 : 103 100 €, trimestre 4 : 109 000 €
M. SERTIF
J'ai réalisé 89 200 € de chiffre d'affaires au premier trimestre, 80 200 € au second, 81 500 € au troisième et
86 900 € au
2 - Heures - (nommer des cellules)
L’entreprise HEURES vous demande de concevoir un tableau récapitulatif des heures effectuées par ses
salariés. Ce tableau devra faire figurer pour chaque salarié :
le nombre d'heures effectuées par jour.
 le total des heures effectuées dans la semaine.
 le nombre d'heures que le salarié aurait dû effectuer dans la semaine.(*)
 la différence entre nombre d'heures effectuées et le nombre des heures à effectuer.
NB : les heures effectuées doivent être saisies sous la forme de chiffres ordinaires et non à l'aide du format
"heures" d'Excel qui sera étudié dans d'autres exercices.
Lundi Botin 7 . Mardi Botin 9
Julard 9 Julard 10
Calista 7 Calista 7
Lomere 6 Lomere 7
Saliet 7 Saliet 9
Ouquetin 5 Ouquetin 4
.
Mercredi Botin 5 Jeudi Botin absent
Julard 9 Julard 7
Calista 7 Calista 7
Lomere 0 Lomere 7
Saliet 7 Saliet 7
Ouquetin 0 Ouquetin 6

Vendredi Botin 7
Julard 4
Calista 7
Lomere 4
Saliet 7
Ouquetin 2
L'horaire normal hebdomadaire de l'entreprise est de 35 heures.
(*) M. Lomere est à 4/5e de temps et Mme Ouquetin est à mi-temps. Pour automatiser cette colonne, écrivez
35 (horaire hebdomadaire normal) dans une cellule en dehors du tableau et utilisez-là pour vos calculs.
3 - Budget (4 opérations)
La société BUDGET possède les chiffres de ses charges de fonctionnement pour les années 2005 et 2006.
Elle souhaite établir à l’aide de ces chiffres une prévision des dépenses pour l’année 2007. Les chiffres sont
les suivants :
Année 2005 :
Achats de marchandises 940 605, autres achats 55 720.
Carburants 22 400, entretien & réparations 37 120, honoraires diverses 45 000, téléphone 28 950, électricité
31 800
Publicité 46 650, transports 7 850, impôts et taxes 8 650, salaires du personnel 540 700, charges de
personnel 237 500
Année 2006 :
Total des achats : 1039 300 (dont 15/16ème de marchandises, le reste pour les autres achats)
Carburants + 1 320 par rapport à 2005
Entretien réparation : + 3% par rapport à 2005
Honoraires divers + 1/5ème par rapport à 2005
Téléphone : - 170 par rapport à 2005
Electricité : 2/60ème de plus qu’en 2005
Publicité : +7% par rapport à 2005
Transport : +5% par rapport à 2005
Impôts et taxes : + 630 euros par rapport à 2005
Salaires du personnel : -1,5% par rapport à 2005
Charges de personnel : 44,2 % du montant des salaires
Année 2007 (prévisions)
Achats de marchandises 1/10ème de plus qu’en 2006
Autres achats : idem 2006
Carburants : chiffre de 2005 + 1/20ème
Entretien réparation : - 1% par rapport à 2006
Honoraires : changement d’expert comptable, 3000 € de moins qu’en 2006
Téléphone : retour au chiffre de 2005
Électricité : Même progression en euros que l’évolution entre 2005 et 2006
Publicité : le double de 2005
Impôts et taxes : même progression en pourcentage que l'évolution entre 2005 et 2006
Transport : + 443 € par rapport à 2005
Salaires du personnel : + 2,5 % par rapport à 2006
Charges de personnel : augmentation de 3% par rapport à 2006
Construire un tableau reprenant toutes ces sommes en construisant sur Excel les formules de calculs et les
totaux nécessaires.. Mettre en forme en utilisant toutes les fonctions connues. Prévoir une colonne entre
2005 et 2006 ainsi qu’entre 2006 et 2007, faisant apparaître le calcul de la variation des montants sous la
forme :
Exemple : chiffre de 2005 : 54 000 €, chiffre de 2006 : 81 000 € : évolution : 1,5
arrondir ces chiffres à 2 décimales sauf la colonne 2007 à arrondir à l'euro.
4 - Laporte (4 opérations-cellules nommées)
L’entreprise LAPORTE rémunère ses représentants en leur attribuant un salaire fixe et une commission
basée sur le chiffre d’affaires. En outre, chaque mois, elle partage entre eux une prime. Cette prime est
partagée proportionnellement au chiffre d’affaires mensuel réalisé par chacun d’eux. Les salaires fixes sont
les suivants :
LAPEYRE : 1 000 - MARTIN : 860 - GUINOT : 950
Chiffres d'affaire :
Juillet : Octobre :
LAPEYRE 25 225 LAPEYRE 33 000
MARTIN 38 720 MARTIN 62 020
58 58 275 GUINOT 44 825
Août : Novembre :
LAPEYRE 13 405 LAPEYRE 38 600
MARTIN 35 440 MARTIN 51 125
GUINOT 49 445 GUINOT 59 335
Septembre : Décembre :
LAPEYRE 16 570 LAPEYRE 34 650
MARTIN 32 240 MARTIN 44 105
GUINOT 51 880 GUINOT 57 340

La commission sur le chiffre d’affaires s’élève à 2%, la prime à partager chaque mois est de 2000 €
Concevoir un tableau par représentant permettant de connaître, pour chaque mois, le détail du salaire brut de
chacun. (fixe + commission + prime)
5 - Factor (fonction =si() )
L’entreprise FACTOR établit actuellement sa facturation à la main. Cette entreprise souhaite à présent
établir ses factures sur Excel. On vous demande de reproduire le modèle de facture ci-dessous sur Excel et
d'automatiser le plus possible les calculs.
 Construisez le modèle de la facture, avec toutes les formules de calcul demandées, sur la feuil1
 Renommez ensuite cet onglet "Modèle"
 Dupliquez cet onglet à chaque fois que vous aurez une facture à faire, de façon à conserver
l'original vierge.
Le numéro de facture, les colonnes "code article", "désignation", "quantité" et "prix unitaire" seront saisies,
les autres données seront le résultat d'un calcul effectué par Excel.
Il est à noter que les frais de port facturés aux clients s'élèvent à 100 Euros si le total des marchandises est
inférieur à 10 000 Euros, 50 Euros si le total des marchandises est compris entre 10000 et 50 000 Euros, et
gratuit si le total des marchandises dépasse 50 000 Euros (utilisez la fonction =SI() pour déterminer les frais
de port). La facture devra se présenter à peu près de la façon suivante :

Testez votre facture avec les bons de commande suivants (les TTC à trouver vous sont donnés pour
vérification) :
 Code article Désignation Quantité Prix unitaire
725 PORTE FENETRE 5 1782.00
532 FENETRE 60 X 120 8 812.00
125 PORTE D'ENTREE 1 2325.00
. TTC A TROUVER : 19145.45
Code article Désignation Quantité Prix unitaire
125 PORTE D'ENTREE 2 2325.00
. TTC A TROUVER : 5 124.86
Code article Désignation Quantité Prix unitaire
725 PORTE FENETRE 35 1782.00
544 FENETRE 80 X 140 50 812.00
125 PORTE D'ENTREE 7 2325.00
TTC A TROUVER : 128 355.32
6 - Volair (fonction =si() plus complexe )
Saisissez les données ci-dessous ou bien téléchargez le fichier de l’exercice.
Vous disposez des informations tarifaires dans les tableaux ci-dessous en ANNEXE 1 à saisir dans une feuille de
calcul d'un nouveau classeur.
Vous renommerez l'onglet de cette feuille : "Tarifs".
Vous construirez ensuite, sur une deuxième feuille du classeur, les deux fiches en ANNEXE2.
Vous renommerez l'onglet de cette feuille : "Devis".
Dans la fiche de renseignements, vous saisirez les données fournies par le client (exemple en ANNEXE3).
A l'aide de formules de calculs, vous exploiterez les informations contenues dans la fiche de renseignements et dans
la feuille "Tarifs" afin d'automatiser la fiche "Devis séjour". Toutes les données de la fiche "devis séjour" seront le
résultat de formules de calculs.
Une fois le document terminé, aucune information ne devra être saisie dans le devis.
NB : aucun montant ne devra être saisi dans les formules. Vous devrez systématiquement faire référence à la
cellule contenant le tarif correspondant dans le tableau concerné de la feuille "Tarifs".
ANNEXE 1 - Tarifs
Tarif des locations
Prix de la
Code Désignation
semaine
A Appartement « Garrigue » 560.00
B Appartement « Cigale » 640.00
Assurance Annulation
Code Désignation Prix
1 Pas d'assurance annulation -
2 Assurance annulation moins de 10 jours 5.00
3 Assurance annulation couverture totale 20.00
Remises
Code Désignation
0 0
1 2%
2 3%
3 5%
Demi-pension (prix de la semaine)
adultes 125.00
enfants 85.00
ANNEXE 2 - Fiche de renseignements et Devis
FICHE DE RENSEIGNEMENTS
 Type de location (A ou B)
Nombre d'adultes
Nombre d'enfants de moins de 12 ans
Supplément demi pension pour les adultes (OUI/NON)
Supplément demi pension pour les enfants (OUI/NON)
Type d'assurance annulation (1, 2 ou 3)
Code remise (0, 1, 2, ou 3)

DEVIS SEJOUR
Nombre d'adultes
Nombre d'enfants de moins de 12 ans
Prix de la location
Supplément demi pension adulte (prix unitaire x nb d'adultes)
Supplément demi pension enfants (prix unitaire x nb d'enfants)
Assurance annulation
Sous total
Remise
Total
ANNEXE 3 - Exemples de renseignements à saisir pour vérifier vos formules
client 1 Appartement B - 4 adultes et 2 enfants. 1/2 pension pour tous, assurance code 2, remise code 1
Appartement A - 2 adultes 1 enfant ½ pension pour les adultes uniquement, assurance code 3,
client 2
remise code 3
client 3 Appartement B - 6 adultes 3 enfants. ½ pension pour tous, assurance code 1, remise code 1

7 - Salaire (fonction =min() )

1/ Reproduisez les tableaux ci dessous

Travail à exécuter :
Faire un 1er tableau contenant les bases de calcul des cotisations selon le modèle ci-dessus. puis un second
tableau, toujours sur le même modèle, avec cette fois le calcul des cotisations.
AIDE POUR LES CALCULS
La maladie se calcule sur le salaire brut
La vieillesse : si le brut est supérieur au plafond la cotisation se calcule sur le plafond (2500 €) et dans le cas
contraire la cotisation se calcule sur le brut.
Assedic
Les cotisations Assedic se calculent sous forme de tranche.
Tranche A : si le brut est supérieur au plafond la cotisation se calcule sur le plafond (2500 €) et dans le cas
contraire la cotisation se calcule sur le brut.
Tranche B : Elle n'existe que si le salaire brut est supérieur au plafond (2500 €). La cotisation se calcule
alors sur la différence entre le salaire brut et la tranche A (Brut - Tranche A). Attention, la base tranche B ne
peut être supérieure à 3 fois le montant du plafond de la sécurité sociale (7500 €).
8 - Facturation - (Fonction Recherche)
1. Reproduisez sur la feuil1 la liste des clients (liste en ANNEXE 1)
2. Renommez l'onglet de la feuil1 "Clients"
3. Reproduisez sur la feuil2 le barème des frais de port (barème en ANNEXE 1)
4. Renommez l'onglet de la feuil2 "Frais de port"
5. Reproduisez sur la feuil3 la liste des articles (liste en ANNEXE 1)
6. Renommez l'onglet de la feuil3 "Catalogue"
7. Nommer le tableau contenant la liste des clients "clients", le tableau des frais de port "port", le
tableau des articles "catalogue"
8. Insérez une nouvelle feuille de calcul (menu "Insertion/ feuille")
9. Reproduisez sur la feuil4 (la nouvelle feuille) la facture (modèle en ANNEXE 2)
10. Renommez l'onglet de la feuil4 "Modèle facture"

11. concevez les formules de la facture de la façon suivante :

 A la place de "NOM" : saisir le nom d'un client
 A la place de "ADRESSE" : créez une fonction de recherche sur le NOM à partir de la liste des
clients
 idem pour le code postal et la ville
 Colonne "désignation" : créez une fonction de recherche sur le code article à partir du catalogue
produits
 Colonne "quantité" : saisie directe
 Colonne "prix unitaire" : créez une fonction de recherche sur le code article à partir du catalogue
produits
 Colonne "montants" : produit des prix par les quantités
 Ligne "remise" : créez une fonction recherche sur le total des marchandises à partir du barème de
remises
Pour élaborer les factures relatives aux bons de commandes ci-dessous, dupliquez la feuille "Modèle facture"
autant de fois que nécessaire de façon à préserver votre modèle.
12. Tester la facture à l'aide des cas proposés en ANNEXE 3
ANNEXE 1
Liste des principaux clients

Barème des frais de port

Le montant des frais de port diminue en fonction du total net des marchandises achetées (par exemple 50 €
de frais de port jusqu'à 100 € de marchandises achetées, puis 25 € de frais de port entre 1000 € à 2000 € de
 marchandises achetées, etc.)

Catalogue produits

ANNEXE 2

ANNEXE 3
Bon de commande du client EMILIE
Code article Désignation Quantité
7 5
14 5
 13 5
Bon de commande du client JEROME
Code article Désignation Quantité
1 1
2 1
3 1
4 1
Bon de commande du client FERNAND
Code article Désignation Quantité
1 1
2 1
Bon de commande du client KILIGE
Code article Désignation Quantité
1 10
2 10
8 10
11 10
9 - Devis (Fonctions = Et() & =Ou()
1/ Reproduisez la fiche de renseignements et le devis en ANNEXE 1 sur une feuille du classeur
2 / Construisez les formules de calculs du devis en fonction des informations saisies dans la fiche de
renseignements et des informations en ANNEXE 2
3/ Essayez d'utiliser les fonctions OU() et ET() combinées avec la fonction SI()
4/ Testez le devis avec les cas en ANNEXE 3
ANNEXE 1

ANNEXE 2
Conditions de vente
Remise 1 : 2% de remise pour les grossistes
Remise 2 : 5% de remise pour les grossistes si le total 1 est supérieur à 10 000 €
Escompte :
si le paiement s'effectue comptant
2% pour les détaillants
3% pour les grossistes
Frais de port :
il s'élèvent à 50 €. ils ne sont pas facturés dans l'un ou l'autre des deux cas suivants :
- si la vente est emportée
- si le total T.T.C. est supérieur à 15 000 €
ANNEXE 3
cas 1 Grossiste achetant 12000 € de marchandises, paiement comptant, livré
cas 2 Grossiste achetant 9000 € de marchandises, paiement comptant, emporté
cas 3 Détaillant achetant 25000 € de marchandises, paiement comptant, emporté
cas 4 Détaillant achetant 12000 € de marchandises, paiement différé, livré
cas 5 Grossiste achetant 12000 € de marchandises, paiement comptant, emporté
10 - Moyenne par élève - =moyenne()
A la veille d’un conseil de classe, les différents professeurs Professeur d’Anglais
vous remettent les informations suivantes : · BONUE Florian 11
Professeur de Français : · CORRY Jean marie 12
· DUBOIS Pierre 12 · CORTENE Mylène 13
· CORRY Jean marie 14 · DUBOIS Pierre 16
· GASTIEN Sylvie 13 · GASTIEN Sylvie 9
· BONUE Florian 8 · LOBERT Corinne 12
· CORTENE Mylène 11 · RAMOI Paul 14
· RAMOI Paul 10 · SIORNE Patrick 6
· LOBERT Corinne 7
· SIORNE Patrick 9
Professeur de Mathématiques Professeur d’Histoire/Géographie
· DUBOIS Pierre 15 · SIORNE Patrick 14
· CORRY Jean marie 6 · DUBOIS Pierre 10
· GASTIEN Sylvie 18 · CORRY Jean marie 9
· BONUE Florian 19 · CORTENE Mylène 18
· CORTENE Mylène 9 · GASTIEN Sylvie 15
· RAMOI Paul 14 · BONUE Florian 7
· LOBERT Corinne 11 · RAMOI Paul 13
· SIORNE Patrick 10 · LOBERT Corinne 13
Afin de faire ressortir la moyenne par élève et par matière, présenter ces informations sous forme de 2
tableaux (sur 2 feuilles de calcul différentes) à l’aide du tableur Excel.
Le 1er tableau fera apparaître les élèves par ordre alphabétique,
le second fera apparaître les élèves par ordre décroissant de leur moyenne.
Utiliser les fonctions de tri du tableur
11 – Vacancextra
L'association "VACANCEXTRA" gère deux centre de vacances. Ces deux centres accueillent durant l'année
des enfants de provenances diverses. En fin d'année, ces deux centres adressent un courrier au siège de
l'association pour rendre compte de la fréquentation enregistrée.
A l'aide de ces courriers, vous concevrez un tableau récapitulatif pour chaque centre ainsi qu'un troisième
tableau qui permettra d'obtenir la fréquentation totale de l'année pour les deux établissements.
Chaque tableau devra être conçu sur une feuille différente.
Ci dessous les lettres reçues :
12 – Novopra
La société NOVOPRA fabrique et commercialise des sièges de bureau de luxe.
Ces sièges sont fabriqués sur 2 sites de production situés à POITIERS et LIMOGES
En 2005, chaque usine a fait parvenir au siège de la société les chiffres suivants :
POITIERS :
Quantités produites en 2005
 Fauteuil cuir et bois LUXUS PARIS : 123 580 unités
 Fauteuil cuir et bois TYPO CONFORT 112 120 unités.
 Siège cuir PRESIDENT : 25 250 unités.
 Siège cuir AMBASSADEUR : 78 920 unités.
 Siège cuir SENATEUR : 67 430 unités.
 Siège tissu MINISTRE : 167 620 unités.
Prévisions 2006 :
 Augmentation de la production des fauteuils cuir et bois de 3%.
 Travaux sur chaîne de montage des sièges tissus : baisse de production de 15 %.
 Sièges cuir : 200 unités supplémentaires pour chaque siège.
LIMOGES
Quantités produites en 2005
 Fauteuil cuir et bois LUXUS PARIS : 166 820 unités.
 Siège cuir PRESIDENT : 33 120 unités.
 Fauteuil cuir et bois TYPO CONFORT 145 700 unités.
 Siège tissu MINISTRE : 198 310 unités.
 Siège cuir AMBASSADEUR : 65 420 unités.
 Siège cuir SENATEUR : 55 670 unités.
Prévisions 2006:
 Augmentation de la production du Fauteuil LUXUS PARIS de 1/16ème .
 La chaîne de siège tissu, rénovée en 2005 sera en mesure de produire davantage. Cette augmentation
est estimée à 1/5ème par rapport à 2005.
 Augmentation de la production des autres sièges cuir et bois de 6%.
 Sièges cuir : on envisage une stabilité de la production.
TRAVAIL A FAIRE
1) A l'aide du tableur EXCEL, présenter sous forme de tableau les résultats et prévisions des 2 usines.
(prévoir 1 tableau par feuille de calcul)..
2) Sur une troisième feuille, construire un tableau présentant le total des données des deux tableaux
précédents..(utiliser les fonctions de consolidation)
3) Sur une quatrième feuille, construire un tableau présentant la moyenne des productions et prévisions des
2 usines par type de siège fabriqué. (utiliser les fonctions de consolidation)
13 – Preta
La société PRETA commercialise des articles de prêt à porter.
On vous demande de concevoir un tableau, établi à partir des chiffres ci-dessous, faisant apparaître :
1- le total des achats par mois pour chaque article
2- le total des achats du trimestre par article
3- le total des achats par mois.
Vous utliserez, pour cela, les fonctions de consolidation.
Nombres d'articles achetés au cours du trimestre :
Achats au fournisseurs LESTETE
janvier février mars
jupes 125 100 80
 pantalons 44 95 45
caleçons . 50 .
chemises 150 90 110
Achats au fournisseurs BELHOM
janvier février mars
jupes 48 52 35
pantalons 25 35 30
gilets 10 25 25
costumes 18 11 12
Achats au fournisseurs CLASSIEU
janvier février mars
vestes 25 18 22
pantalons 14 12 25
pulls 50 62 55
chemises 14 40 40
14 - Valeur Ajoutée
Représenter graphiquement (sous forme d'histogramme et sous forme de secteurs) le tableau ci-dessous :
Valeurs ajoutées par branche en milliards d'euros en 2004 (extrait)
Source : Comptes nationaux - Insee

15 – Graphique
Vous souhaitez avoir une représentation graphique de vos ventes des années 2002 à 2005.
Elle souhaite obtenir les graphiques suivants :
Un graphique représentant l'ensemble des ventes sur cette période
Un graphique représentant l'évolution des ventes dans le Nord Est,
Un graphique représentant la répartition des ventes en 2002,
Un graphique représentant la répartition des ventes en 2005.
Vous trouverez ci-dessous, le tableau de données à partir duquel vous devez construire les graphiques.
Répartition des ventes

16 - Facture automatique
L'utilisation des listes déroulantes, cases à cocher, cases à option, sont décrites à l'aide de l'exemple ci-
dessous.
Accédez aux explications du manuel Excel niveau 3
Version XP - Version 2007
17 - Vins (Tableaux croisés dynamiques)
Un négociant en vin à regroupé dans une liste le chiffre d'affaires réalisé par mois, par région et par
appellation sur ces ventes de vin.
Il vous demande de construire les tableaux de statistiques suivants :
 Chiffre d'affaires, par mois et par région, pour chaque appellation
 Chiffre d'affaires par appellation et par région.
 Chiffre d'affaires par mois et par appellation pour chaque région.
 Modifiez le tableau 1 pour obtenir la répartition en pourcentage de votre chiffre d'affaires.

18 - Calculs d'échéances
Vous devez compléter la colonne "Date d'échéance". Utilisez les formules à votre disposition. Voir guide des
formules de dates.

19 - Calculs sur les heures

Vous devez compléter les colonnes "Heures effectuées" et "Différence" ainsi que les 3 totaux.
Pour fonctionner les formules sur les heures imposent :
1/ que les heures soient saisies au format HH:MM
2/ Que dans les options Excel (options avancées), la case "utiliser le calendrier depuis 1904" soit cochée
3/ que le format des cellules de total soit personnalisé sous la forme [hh]:mm