Tableur
Excel
Exercice 1 :
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 .
Exercice 2 :
| janvier | Février |
| 1980 | 150 |
| 1981 |
a) Entrer le tableau ci-contre dans une seconde feuille de calcul du même classeur que vous nommerez « exo2 ».
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 cellules 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).
Exercice 3 :
Dans une nouvelle feuille de votre fichier, nommée « exo 3 », reproduire le modèle de facture cidessous. Ne pas tenir compte de la mise en forme (encadrement, trame, …). Par contre, les cellules de la colonne de droite doivent être construites grâce à des formules excel.
| Titre de l'ouvrage | Prix unitaire HT | quantité | Prix de la ligne HT |
| Les Misérables | 42,00 € | 10 | 420,00 € |
| Les aventures de Tom Sawyer | 59,90 € | 5 | 299,50 € |
| Catalogue | 9,50 € | 70 | 665,00 € |
| 0,00 € | |||
| 0,00 € | |||
| 0,00 € |
| chose encadrée |
Total HT : chose encadrée Total TTC (le taux de TVA est à 20,6 %) :
Exercice 4 :
a) Ouvrir le fichier nommé « ».
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 :
a) Ouvrir la feuille « exo 3 » du fichier « » dans laquelle vous aviez effectué l’exercice 3 du TP précédent.
b) Insérer une ligne au dessus de votre tableau.
| Taux de TVA | 20,6 % |
c) Dans la ligne 1, insérer le taux de TVA de la façon suivante :
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.
Exercice 6 :
a) Rouvrir le fichier de l’exercice 4 (« » 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 :
a) Ouvrir l’exercice « »
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 »..
| MARS |
Mois de
BULLETIN DE PAIE : à conserver sans limitation de durée
|
|
| 18 |
| 126 |
Nombre de jours de garde : Nombre d'heures de garde réelles :
| Salaire brut : Salaire de base : Indemnité compensatrice d'absence : |
| heures à heures à | 8.00 € 4.00 € | ||
| 1 008.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 : Rappel sur rémunération : | 108.00 € 0.00 € |
| Trop perçu sur rémunération : | 0.00 € 1 028.45 € |
| net à payer : |
date : signature de l'employeur le 31 Mars 2004
Exercice 8 :
a) Ouvrir le fichier
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 contraire.
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 entête le titre : Résultat des examens 2004.
Exercice 9 :
a) Ouvrir le fichier (qui contient la liste des biens d'une agence immobilière) et se reporter 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 |
| 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 € |
Prix moyen à compléter
j) Supprimer le quadrillage et faire un aperçu avant impression
k) Sauvegarder sous
Exercice 10 :
a) Ouvrir le fichier
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 :
a) Ouvrir le fichier , sur la feuille « Exercice 11 ».
b) A l’aide des fonctions logiques d’Excel, nous allons construire le tableau suivant.
| Représentant | Ville | Ancienneté | Chiffre d'affaires (CA) | Intéressement |
| 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 condition 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 ».
Exercice 12 :
a) Ouvrir le fichier à 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 « ? ».
| Equipes | Buts marqués | Nombre de points avant le match | Nombre de poins après le match | Maintien en D1 ? |
| 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 |
