Exercice Excel calcul d’itérations intérêts composés

Notions : progression géométrique : références relatives, recopie.

Le club investissement d'une grande école vient de prendre son bénéfice. Et il est
coquet ! Le club envisage de prêter cette somme à l'association des élèves pour financer
un voyage promo sur la station spatiale. L’association rémunérera annuellement cette
somme à un taux d’intérêts composés T.

Dans un emprunt les intérêts se calculent à l'échéance de chaque période, ici annuelle.
Si le prêt est à t%, avec un capital c, les intérêts au bout d'un an se montent à c x T ; le
montant dû par l’emprunteur est c+cxT

somme qui devient le nouveau capital prêté pour l'année suivante. Cannée = Cannée-1 ^
(1+T)
On demande d'établir le tableau de l'accroissement du montant dû) pour 12 ans.

Exercice Excel calcul de la 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

Solution :
1er 2ème 3ème 4ème
trimestre trimestre trimestre trimestre Totaux
MAXIME 87 200 88 200 75 700 94 200 345 300
LEGENDRE 66 200 72 200 70 200 83 200 291 800
MARTINALE104 200 100 800 103 100 109 000 417 100
SERTIF 89 200 80 200 81 500 86 900 337 800
Totaux 346 800 341 400 330 500 373 300 1 392 000
Formule en F2 : =somme(B2:E2) - Cette formule est à recopier sur les lignes suivantes.
Formule en B6 : =somme(B2:B5) - Cette formule est à recopier sur les colonnes de
droite

Exercice Excel consolidation de tableaux contenant

des données différentes
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

Exercice Excel calculs financiers pour compte

bancaire
Dans ce premier exercice simple et utile vous serez guidé pas à pas pour construire la
feuille de calcul. Excel est parfaitement adapté à ce type de calcul de gestion.
Notions : fonctions SOMME et SI, mise en page, recopie de formules avec références
relatives.
Afin de tenir à jour un compte bancaire, on se propose d'utiliser un tableur. Les
différentes opérations sont mises dans un tableau, à raison d'une opération par ligne,
avec leur date et leur montant.
 1/ entrée des opérations bancaires.
Dans un premier temps nous allons entrer les différentes opérations, leur date dans la
colonne A et leur libellé dans la colonne B. Le montant de l'opération est placé en
colonne C s'il s'agit d'une opération débitrice, en colonne D s'il s'agit d'une opération
créditrice.

Les totaux seront calculés par une formule de sommation, comme nous le verrons dans
l'étape suivante.

2/ calculs de débit et de crédit.

Plaçons nous dans la cellule correspondant au total du débit, en C16.

Une formule commence toujours par un signe =. Excel possède une fonction prédéfinie
SOMME. Elle s'utilise comme toutes les autres fonctions, sous forme =SOMME(ref:ref).
Pour entrer la formule nous pouvons frapper directement le texte "=SOMME(" ou bien
cliquer sur l'icône de l'outil de sommation, puis sélectionner la plage de cellules à
sommer C5:C15.

Pour le calcul du total du crédit, nous allons recopier cette formule (en C15), qui est en
références relatives), à droite (en D15).

3/ calculs du solde final.

Nous avons maintenant les totaux des débits et crédits, il ne nous reste plus qu'à
calculer le nouveau solde du compte. La formule de calcul du nouveau solde en ligne 17
fait la différence entre les totaux des opérations de débit et de crédit. Évidemment si le
solde est positif il figure en colonne Crédit, et en colonne Débit s'il est négatif : dans ces
cellules la valeur à afficher est conditionnée par son signe, nous utiliserons la fonction
SI.

Ainsi en C17 nous avons =SI(C16>D16;C16-D16;"") et en D17 la formule

complémentaire.

4/ libellé final.
En B17 nous plaçons le libellé "solde débiteur" ou "solde créditeur", suivant le cas.
Quelle est donc la formule =SI(… à employer ?

5/ présentation.
Pour une meilleure lisibilité du tableau, nous pouvons préciser le format d'affichage des
valeurs dans les colonnes débit et crédit, aussi mettre en gras le solde final et en
italique le solde initial.

Enfin nous gagnons aussi en lisibilité à encadrer les plages de cellules pour mettre en
valeur la structure du tableau.

6/ sauvegarde et impression.
Maintenant que nos calculs sont effectifs, nous désirons les conserver.

 Au préalable nous aurons mis en page la feuille de calcul : menu Fichier, ligne Mise en
page.
 Sur disque nous sauvegardons la feuille de calcul, menu Fichier, ligne Enregistrer sous,
en positionnant l'enregistrement sur la disquette de sauvegarde des documents.
 Sur papier nous imprimons le tableau : menu Fichier, ligne Imprimer.
7/ modifications.
 Modifions une valeur et observons les totaux et solde. Ceux-ci sont automatiquement
remis à jour. De même supprimons ou ajoutons une ligne d'opération bancaire (pour
insérer : menu Fichier, ligne Insérer). Tout est alors recalculé.

Exercice Excel fonction si calcul d'une facture

simple
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

Formule en F9, F10, F11, etc... : =D9*E9

Formule en F16 : =somme(F10:F15)
Formule en F17 : =F16*10%
Formule en F18 : =F16-F17
 Formule en F19 : =SI(F16<10000;100;SI(F16<50000;50;0))
Formule en F20 : =somme(F18:F19)
Formule en F21 : =F20*19.6%
Formule en F22 : =somme(F20:F21)

Exercice Excel calcul du pourcentages et

proportions
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

GUINOT 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)

Exercice Excel fonction RECHERCHE() facturation a

partir de données
 Reproduisez sur la feuil1 la liste des clients (liste en ANNEXE 1)
 Renommez l'onglet de la feuil1 "Clients"
 Reproduisez sur la feuil2 le barème des frais de port (barème en ANNEXE 1)
 Renommez l'onglet de la feuil2 "Frais de port"
 Reproduisez sur la feuil3 la liste des articles (liste en ANNEXE 1)
 Renommez l'onglet de la feuil3 "Catalogue"
 Nommer le tableau contenant la liste des clients "clients", le tableau des frais de port
"port", le tableau des articles "catalogue"
 Insérez une nouvelle feuille de calcul (menu "Insertion/ feuille")
 Reproduisez sur la feuil4 (la nouvelle feuille) la facture (modèle en ANNEXE 2)
 Renommez l'onglet de la feuil4 "Modèle facture"
 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.

 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 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

Exercice Excel 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 enANNEXE 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
Appartement B - 4 adultes et 2 enfants. 1/2 pension pour tous, assurance code 2,
client 1 remise code 1
Appartement A - 2 adultes 1 enfant ½ pension pour les adultes uniquement,
client 2 assurance code 3, remise code 3
Appartement B - 6 adultes 3 enfants. ½ pension pour tous, assurance code 1,
client 3 remise code 1