Tableur

Excel
Exercice 1 :
Saisie des données

a) Entrer le tableau suivant dans une feuille de calcul vierge (police Arial 12) :
Nom Prénom N° de tél. Réf Produit Quantité Prix unitaire
Legros Georges 0386623012 123456 3 122,60
Duprez Suzanne 0386681243 789654 45 89
Martin Guillaume 0245784786 482395 12 122,53
Guyot Bernard 0245632145 456912 78 456,25

b) Ajouter une colonne à gauche (clic droit sur A puis insertion), la titrer (en A1) Numéro client et
y créer une suite de 1 à 5.
c) Insérer, au dessus de la ligne 1, une ligne permettant d’inscrire le titre suivant : Récapitulatif
commandes. Mettre ce titre en taille 16.
d) Insérer entre les lignes Legros et Duprez la ligne suivante :
Duval Marc 0386650125 778954 145 7896,20

e) Supprimer la ligne Martin.

f) Inverser les colonnes Quantité et Prix unitaire.
g) Nommez l’onglet du classeur « exo 1 » (en bas à gauche) et sauvegarder le fichier sous le
nom td1.xls.

Exercice 2 :
Saisie automatique

a) Entrer le tableau ci-contre dans une seconde feuille de calcul du

même classeur que vous nommerez « exo2 ».
janvier Février
1980 150
1981
b) Effectuer les manipulations suivantes en n’utilisant le clavier que pour les touches ECHAP,
MAJ et CTRL.
c) Compléter horizontalement la liste des mois (jusqu’à août). Compléter verticalement la liste
des années (jusqu’à 1990).
d) Recopier vers le bas la cellule B2 (jusqu’à la ligne 12), copier la zone B2 :B12 en colonne C
(C2 :C12).
e) A l’aide d’un collage spécial (à partir de la même zone B2:B12), ajouter 150 à toutes les cel-
lules de la zone C2:C12.
f) Recopier vers la droite ces deux colonnes de façon à ce qu’il y ait une incrémentation automa-
tique des valeurs par pas de 150.
g) Sélectionner la colonne A et la ligne 1 ; leur appliquer une mise en forme Gras.
h) Utiliser pour les noms de mois la police Times New Roman 12.
i) Centrer les indications de la colonne A.
j) Ajuster la largeur des cellules, puis fixer à 8 la largeur de la colonne A.
k) Copier l’ensemble du tableau obtenu à droite en inversant la présentation (les lignes devien-
nent des colonnes et inversement). Ajuster la largeur des colonnes.
l) Appliquer un contour en bordure double autour du tableau et un quadrillage en bordure simple
pour l’intérieur (Format – Cellule).

15
Exercice 3 :
Formules de calcul

Dans une nouvelle feuille de votre fichier, nommée « exo 3 », reproduire le modèle de facture ci-
dessous. Ne pas tenir compte de la mise en forme (encadrement, trame, …). Par contre, les cel-
lules de la colonne de droite doivent être construites grâce à des formules excel.

Prix unitaire
Titre de l'ouvrage quantité Prix de la ligne HT
HT
Les Misérables 42,00 € 10 420,00 €
Les aventures de Tom Sa-
wyer 59,90 € 5 299,50 €
Catalogue 9,50 € 70 665,00 €
0,00 €
0,00 €
0,00 €
Total HT : chose encadrée
Total TTC (le taux de TVA est à 20,6 %) : chose encadrée

Exercice 4 :
Formules de calcul

a) Ouvrir le fichier nommé « facture.xls ».

b) Ecrire les formules requises dans les cellules colorées en jaune.
c) Améliorer la présentation afin que le résultat ressemble au modèle ci-dessus :

Exercice 5 :
Adresses relatives adresses absolues

a) Ouvrir la feuille « exo 3 » du fichier « td1.xls » dans laquelle vous aviez effectué l’exercice 3
du TP précédent.
b) Insérer une ligne au dessus de votre tableau.
c) Dans la ligne 1, insérer le taux de TVA de la façon suivante : Taux de TVA 20,6 %
d) A droite de la colonne « prix de la ligne HT », ajouter une nouvelle colonne intitulée « prix de
la ligne TTC.
e) Pour la première ligne (les misérables), insérer dans la cellule adéquate, la formule permettant
de calculer le prix de la ligne TTC. Attention, il convient dans cette formule de faire référence au
taux de TVA affiché en cellule B1.
f) Compléter la colonne par un recopiage à l’aide de la souris (curseur +). Constater le pro-
blème.
g) Comparer les formules de la cellule D4 et E4. Quelle erreur constate-t-on ?
h) En cellule E3, corriger dans la formule l’expression B1 par $B$1.
i) Recopier cette cellule dans le reste de la colonne E du tableau.
j) Comparer à nouveau les formules de la cellule D4 et E4, et constater la différence entre réfé-
rence relative (B1) et référence absolue ($B$1).
k) Modifier la formule du total TTC (normalement située en D11), de façon à ce qu’elle fasse ré-
férence au taux de TVA mentionné en B1.
l) Changer le contenu de la cellule B1 : nouveau taux de TVA à 19,6 %. Est-il nécessaire de
modifier de nouvelles formules pour obtenir des résultats justes ?
m) Enregistrer puis fermer ce fichier.

16
Exercice 6 :
Formules de calcul

a) Rouvrir le fichier de l’exercice 4 (« facture.xls » fait).

b) Modifier le tableau en ajoutant une colonne pour le prix TTC à droite de la colonne Prix de la
ligne HT.
c) Trouver la formule de la première cellule, puis recopier cette formule dans toute la colonne.
d) Enregistrer et fermer.

Exercice 7 :
Formules de calcul

a) Ouvrir l’exercice « Exo07.xls »

b) Sans modifier la couleur de fond des cellules, arranger la mise en forme pour obtenir une
feuille de paye ressemblant au document reproduit ci-contre. La largeur des colonnes vous est
donnée dans le tableau ci-dessous :

A B C D E F G
3 17 21 8 11 14 12

c) Remplir la cellule F17 par l’expression « + 10 % » qui doit apparaître telle quelle.
d) Remplir par des formules toutes les cellules à fond bleu. Il est rappelé qu’une journée de tra-
vail comprend 7 heures. Les indemnités de nourriture et d’entretien représentent 6 euros par
jours.
e) A quoi correspondent les cellules à fond jaune ?
f) Enregistrer sous le nom « bulletin de paye »..

17
 Mois de
BULLETIN DE PAIE : MARS
à conserver sans limitation de durée

Employeur : Salarié :
MARTIN Gi-
Nom : DUPONT Odette Nom : sèle
Adresse : 12 rue du Pont Adresse : 4 Place de l'Eglise
63400 ESTIVEL 63 400 ESTIVEL
Qualité Assistante Maternelle Agréée
n° URSSAF : 6 302 241 670 418 N° SS 2 61 06 44 345 084

Nombre de jours de garde : 18

Nombre d'heures de garde réelles : 126

Salaire brut :
Salaire de base : 126 heures à 8.00 € 1 008.00 €
Indemnité compensatrice d'absence : 12 heures à 4.00 € 48.00 €
Indemnité de congés payés : 105.60 €
total : 1 161.60 €

Cotisations salariales :
Contribution Sociale Généralisée : 7.50% de 95 % du salaire brut 82.76 €
Remboursement de la Dette Sociale : 0.50% de 95 % du salaire brut 5.52 €
Sécurité Sociale : 7.40% du salaire brut 85.96 €
ASSEDIC : 3.01% du salaire brut 34.96 €
IRCEM : 2.75% du salaire brut 31.94 €
total : 241.15 €

Salaire à verser :
Salaire net : 920.45 €
Indemnité de nourriture et entretien : 108.00 €
Rappel sur rémunération : 0.00 €
Trop perçu sur rémunération : 0.00 €
net à payer
: 1 028.45 €

date : signature de l'employeur

le 31 Mars 2004

18
Exercice 8 :
Formules de calcul – Moyennes - Formats conditionnels - Fonction SI

a) Ouvrir le fichier Exo08.xls

b) Pour la première ligne, placer les contenus écrits à la verticale (format – cellule – alignement)
c) Pour les colonnes B, C et D, appliquer un format conditionnel qui affiche les notes égales à 20
en vert et en caractères gras.
d) Compléter par des formules les cellules B24, E2 et F2.
e) Sélectionner simultanément les plages E3 :F22 et B24 :F24 puis appliquer à ces cellules un
format de nombre à une seule décimale.
f) Pour ces deux dernières plages de cellules (les deux dernières colonnes), appliquer un format
conditionnel : si la note est inférieure à 10, elle devra être écrite en rouge et en caractères gras.
g) Recopier ces formules dans les cellules appropriées.
h) Dans la colonne G, qui sera intitulée « résultat », vous insérerez à l’aide de la fonction SI, le
résultat « Admis », si la moyenne pondérée est supérieure à 10 et « Refusé » dans le cas con-
traire.
i) A l’aide de la mise en forme conditionnelle, vous écrirez en gras et en couleur les résultats
« Refusé » dans la colonne G.
j) Trier les élèves par ordre de rang.
k) Avec la commande mise en page, placer ce tableau à l’horizontal sur la page, et insérer en en-
tête le titre : Résultat des examens 2004.

Exercice 9 :
Fonctions SI – Moyenne - Compteur

a) Ouvrir le fichier Exo09.xls (qui contient la liste des biens d'une agence immobilière) et se re-
porter au modèle ci-dessous (incomplet) pour effectuer toutes les modifications. Entre autre :
b) Effacer le tableau inférieur (qui n'est que la copie du tableau supérieur).
c) Supprimer du tableau les deux premiers appartements qui ont été retirés de la vente.
d) Déplacer les colonnes.
e) Insérer quelques lignes en haut et ajouter le titre
f) Trier sur le type
g) Ajouter le calcul du nombre de biens en vente ainsi que leur prix moyen
h) Ajouter à droite une colonne permettant de calculer le montant de la commission de vente, à
savoir 5 % du prix si celui-ci est inférieur à 700 000 €, 3 % au delà (utiliser une fonction Si pour
effectuer le calcul).
i) Appliquer au tableau le format automatique Liste 1.
Détail des biens en vente
Nombre de biens en vente à compléter
Type Adresse Prix Commission Prix moyen à compléter
appartement 2, rue Guisarde 685 000 € 34 250,00 €
appartement 5 passage Lepic 157 000 € à compléter
appartement 30 Bd Poissonnière 586 940 €
appartement 15 rue Mademoiselle 250 000 €
appartement 27 rue Mademoiselle 260 000 €

j) Supprimer le quadrillage et faire un aperçu avant impression

k) Sauvegarder sous Appart.xls

19
Exercice 10 :
Formules de calcul et fonctions logiques

a) Ouvrir le fichier Exo10.xls

b) En utilisant la fonction Si, calculer les salaires des différents représentants en fonction des
données ci-dessous. La rémunération mensuelle des représentants de l’entreprise X comprend 3
éléments :
- Un fixe de 1200 €
- Une indemnité (Ind.) de frais d’un montant de 315 € si le représentant travaillle en
zone 1 et de 250 € s’il travaille en zone 2.
- Une commission basée sur le chiffre d’affaires (CA) mensuel du représentant et
son ancienneté (Anc.) :
5 % du CA si l’ancienneté est supérieure à 10 ans
3 % du CA dans le cas contraire.
En cas de difficulté, écrivez pour l’indemnité d’abord, puis pour la commission, la réponse aux 3
questions suivantes sur une feuille séparée :
1 Que doit-on tester ?
2 Quel est le résultat si la réponse au test est vraie ?
3 quel est le résultat dans le cas contraire ?
Si vous ne vous êtes pas trompé, le total des salaires doit être égal à 24 322 €

Exercice 11 :
Fonctions logiques ET SI OU

a) Ouvrir le fichier Logique.xls, sur la feuille « Exercice 11 ».

b) A l’aide des fonctions logiques d’Excel, nous allons construire le tableau suivant.
Chiffre
Représentant Ville Ancienneté d'affaires Intéressement
(CA)
M. Dupont Toulouse 10 135000 13500
Mme Lenoir Toulouse 5 45000 2250
Mlle Leblanc Bordeaux 12 120000 12000

TOTAL 27750
Ce tableau a pour but de calculer l’intéressement des vendeurs d’une société, en respectant les
conditions ci-dessous.

Condition 1 :
La ville où travaille le représentant est Toulouse et l’ancienneté est strictement supérieure à 7 ans
(la condition 1 est donc vraie si les deux conditions ci-dessus sont vraies simultanément).

Condition 2 :
Le CA mensuel est supérieur ou égal à 100 000 et l’ancienneté est supérieure ou égale à 6 ans.
(la condition 2 est donc vraie si les deux conditions intervenant dans sa définition sont vraies).
Si l’une au moins des deux conditions parmi la une et la deux est vérifiée (condition 1 ou condi-
tion 2), alors l’intéressement perçu comme salaire par un représentant est égal à 10 % du CA
mensuel. Sinon, l’intéressement est 5 % du CA mensuel.
Le contenu des cellules de la colonne « intéressement » doit se calculer automatiquement. Il est
possible de le calculer directement grâce à une formule qui utilise les fonctions SI et ET du logiciel
Excel. En cas de difficulté, utiliser successivement les colonnes « condition 1 », « condition 2 » et
« % de l’intéressement ».

20
 Exercice 12 :
Formules de calcul et fonctions logiques

a) Ouvrir le fichier Logique.xls à la feuille « exercice 12 », qui présente les résultats de 3 matchs
de football de la 34ème journée du championnat de France de Football de division 1 en saison
1998-99.
b) Programmer les cellules de la colonne D à l’aide des instructions suivantes
c) Pour calculer le nombre de points après le match, appliquer le barème suivant

:Une victoire vaut 3 points.
Un match nul vaut un
Une défaite vaut 0 point.
point.

En fait il n’est nécessaire de programmer que les deux premières cellules de la colonne.
d) Dans la dernière colonne doit s’afficher « maintien » ou « ? ». En effet, parmi les 6 équipes, se
maintiennent en D1 toutes celles dont le nombre de points après les matchs de la 34ème journée
est supérieur au minimum suivant : Min (zone de cellules qui contiennent les nombres de points).
Utiliser des $ pour les coordonnées de cette zone de cellules. Ne pas oublier les guillemets autour
de « maintien » et « ? ».

Nombre Nombre
Buts mar- de points de poins Maintien
Equipes
qués avant le après le en D1 ?
match match
Monaco 1 59 62 maintien
Lorient 0 35 35 ?
Metz 1 36 39 maintien
Le Havre 0 35 35 ?
Auxerre 2 34 37 maintien
Rennes 0 59 59 maintien

21