Exercices Excel sur les différents calculs
Rédigé par GC Team, Publié le 14 Janvier 2011, Mise à jour le Mercredi, 14 Décembre 2022 20:37![](/images/thumb/140/default.png)
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 + c x T
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 trimestre | 2ème trimestre | 3ème trimestre | 4ème trimestre | Totaux | |
MAXIME | 87 200 | 88 200 | 75 700 | 94 200 | 345 300 |
LEGENDRE | 66 200 | 72 200 | 70 200 | 83 200 | 291 800 |
MARTINALE | 104 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
|
janvier
|
février
|
mars
|
|
jupes
|
48
|
52
|
35
|
pantalons
|
25
|
35
|
30
|
gilets
|
10
|
25
|
25
|
costumes
|
18
|
11
|
12
|
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
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
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
Code |
Désignation |
Prix de la 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
|
client 2
|
Appartement A - 2 adultes 1 enfant ½ pension pour les adultes uniquement, assurance code 3, remise code 3
|
client 3
|
Appartement B - 6 adultes 3 enfants. ½ pension pour tous, assurance code 1, remise code 1
|
Plutôt que d'écrire les montants dans la formule, il est préférable faire référence aux cellules contenant ces montants, qui se trouvent dans la feuille de calcul où vous avez construit les tableaux de tarif. De cette façon, en cas de changement tarifaire, il ne sera pas nécessaire de modifier les formules de calcul.