PRODUIRE UNE FEUILLE DE CALCUL(EXCEL)
I-GENERALITES SUR EXCEL
1- Identifier les parties de l’interface d’un tableur
Un tableur est
un logiciel d’application qui permet de :
·
Créer
et manipuler des tableaux
·
Calculer
des données
·
Présenter
des données sous forme de graphique
Il existe de
nombreux logiciels tableur parmi lesquels on peut citer :
·
Microsoft
Excel de la suite Microsoft Office
·
Quattro
pro de la suite Corel WordPerfect Office
·
Open
calc de la
suite Open Office
Dans un
logiciel tableur, on utilise généralement des
feuilles de calcul. Une feuille de calcul est un tableau électronique formé
de plusieurs lignes et de plusieurs colonnes qui contient des éléments qui
aideront à présenter les données et à effectuer les calculs de manière
efficace.
·
Une ligne est une suite horizontale des cases,
identifiée par un chiffre. Dans Excel, les lignes sont numérotées de 1 à
1 048 576.
·
Une colonne est une suite verticale de cases,
identifiées par une lettre. Dans Excel, les colonnes sont numérotées de A…Z,
AA……AZ, BA…ZZ, AAA…XFD
·
Une cellule est la case d’une feuille de calcul à l’intersection d’une
ligne et d’une colonne. Une cellule est active
lorsque sa bordure apparait en gras, la rendant aisément identifiable.
Pour rendre une cellule active, il
suffit de placer le pointeur de la souris sur la cellule souhaitée, puis
cliquer avec le bouton gauche de la souris.
·
La référence d’une cellule indique son emplacement dans
la feuille de calcul. Elle est constituée d’une lettre désignant la colonne et
d’un chiffre désignant la ligne.
Exemple : A2 (l’intersection de la ligne 2 et la
colonne A)
Dans Excel,
lorsqu’une cellule est active sa référence s’affiche dans la zone de référence.
Un classeur est un ensemble de feuille de
calcul.
2-Demarrer Excel
Pour
démarrer Microsoft Excel sous Windows 10:
·
Cliquer
sur le bouton Démarrer
·
Cliquer
sur Toutes les applications
La liste des applications apparait,
classée par ordre alphabétique. Faire défiler jusqu’à la lettre E.
·
Cliquer
sur Excel, puis sur Nouveau classeur.
Une feuille de
calcul vierge apparait à l’écran
Feuille de calcul d’Excel
3-Description de la fenêtre d’Excel
Les différents
éléments de la fenêtre d’Excel sont
·
La barre des titres : Le titre du classeur enregistré
s’affiche dans la barre des titres. Elle comporte également :
- A l’extrême gauche les boutons de
fermetureT, d’agrandissement + et de réduction
- A l’extrême droit le bouton d’office et
les boutons de barre d’outils à accès rapide (sauvegarde annulation tec…)
Le
ruban : Le ruban
d’Excel 2013 est un bandeau constitué des onglets
qui parcourent toute la largeur de la fenêtre. Lorsqu’on clique sur un onglet,
- les boutons de commande apparaissent.
Elles permettent de sélectionner les commandes les plus fréquemment utilisées.
-on voit aussi apparaitre pour cet
onglet des galeries accessibles via
une flèche déroulante (lanceur
des boîtes de dialogues).
La barre des formules : Elle affiche le contenu de la cellule active.
·
La zone de référence : Elle affiche la référence de la
cellule active (E3)
·
La barre d’état : Elle donne des informations concernant
la tâche qu’on effectue.
·
Les barres de défilement : Si la qualité des données excède la
capacité d’affichage de l’écran, on peut faire défiler la feuille dans le sens
vertical ou dans le sens horizontal.
·
Les onglets : Les feuilles de
calcul d’un classeur sont accessibles à partir de certains boutons appelés onglets, situés en bas de page,
au-dessus de la barre d’état (exemple : feuill1).
4- gestion des données
4.1-La saisie des données
Les types des données à saisir dans une cellule sont :
·
Texte (étiquettes) : Lycée de Batcham ; Année 2017/2018
·
Valeurs : 125,45 ; 01/06/2011 ;
10 :30 ; 40% ; 10 €
·
Formules : =D3+D4 ;=Somme (B2 :B14) ; = 420* 15
Pour
saisir une donnée dans une cellule :
·
Cliquez sur la cellule,
·
Saisissez votre donnée,
Appuyez sur la touche Entrée
pour valider la saisie.
Entrée
NB : En
cas d’erreur de frappe, on appuie sur la touche : Backspace
du clavier pour supprimer les données incorrectes, puis on recommence.
Pour conserver
les données saisies dans la cellule, et passer à la cellule suivante, on appuie
sur la touche Entrée du
clavier.
4.2-Modifier le contenu d’une cellule
Pour modifier le contenu d’une cellule :
·
Cliquez sur la cellule à modifier
·
Cliquez directement dans la barre de formule et
saisissez les modifications.
·
Appuyez sur Entrée pour valider la
modification
Ou
bien
·
Double cliquez sur la cellule à modifier et
saisissez les modifications
4.3-Sélectionner des cellules
Avant d’effectuer une
tâche sur Excel, il faut d’abord sélectionner les cellules avec lesquelles on
souhaite travailler. Les cellules sélectionnées apparaissent en surbrillance
(ou vidéo inverse à l’écran).
·
Sélectionner
une cellule
On place le pointeur de la souris sur la cellule à sélectionner, puis on
clique avec le bouton gauche de la souris.
·
Sélectionner une plage des cellules
Pour sélectionner une plage (ensemble des cellules adjacentes) des
cellules : cliquez sur la première cellule, puis glissez la souris vers la
dernière cellule.
Exemple d’une plage de cellules (B1 : D5)
·
Sélectionner deux plages différentes de
cellules
Pour
sélectionner deux plages différentes : Sélectionnez la première plage de
cellules puis maintenez la touche Ctrl enfoncée et sélectionnez la
deuxième plage de cellules.
·
Sélectionner deux plages différentes de
cellules
Pour
sélectionner deux plages différentes : Sélectionnez la première plage de
cellules puis maintenez la touche Ctrl enfoncée et sélectionnez la
deuxième plage de cellules.
·
Sélectionner une ligne ou une colonne.
Pour
sélectionner une ligne ou une colonne entière :
Ligne entière : pointer le curseur de la souris sur le numéro de la ligne,
lorsqu’il prend la forme d’une flèche horizontale, cliquez.
Colonne entière : pointer le curseur de la souris sur le numéro de la
colonne, lorsqu’il prend la forme d’une flèche verticale, cliquez.
4.4-Effacer le contenu d’une cellule
Pour effacer le contenu d’une cellule :
·
Sélectionnez-la (es) cellule(s) à effacer.
·
Appuyez sur la touche « Suppr»
OU Cliquez droit sur la cellule, puis supprimer.
4.5-Copier
le contenu d’une cellule
Pour copier le contenu d’une cellule :
·
Sélectionnez-la (es) cellule(s) à copier.
·
Dans le menu Edition cliquez sur Copier.
·
Activez-la (es) cellule(s) de destination
·
Dans le menu Edition cliquez sur Coller
4.6-Déplacer le contenu d’une cellule
Pour déplacer le contenu d’une cellule :
·
Sélectionnez la (es) cellule(s) à déplacer.
·
Dans le menu Edition cliquez sur Couper.
·
Activez la cellule de destination
·
Dans le menu Edition cliquez sur Coller
4.7-Recopie
incrémentée et répétitive
Sélectionnez une cellule de données
·
Placez le pointeur sur le petit carré noir au coin
inférieur droit de la cellule
·
Gardez le doigt sur le bouton gauche, puis déplacez
le pointeur vers le sens souhaité
Le
contenu de la cellule va se recopier (incrémentée) dans les cellules
sélectionnées.
4.8-Annuler
la dernière modification
Pour
annuler la dernière modification :
·
Placer
le pointeur de la souris sur le bouton
sur
la barre d’outils à accès rapide, puis Cliquer avec le bouton gauche de la
souris.
·
Pour
rétablir, cliquer sur 
avec le bouton gauche de la souris.
5 : la gestion des lignes et colonnes
5.1- Insérer une ligne ou une colonne
Pour insérer une ligne au-dessus de la
ligne sélectionnée :
·
Sous l’onglet Accueil, dans le groupe cellule,
cliquez sur la petite flèche de commande Insérer puis sélectionnez insérer
des lignes dans la feuille OU cliquez droit sur la cellule sélectionnée,
puis cliquez sur Insérer une ligne
au-dessus.
Pour insérer une colonne à gauche de la colonne sélectionnée
:
·
Sous l’onglet Accueil, dans le groupe cellule,
cliquez sur la petite flèche decommandeInsérerpuis sélectionnez insérer
des colonnes dans la feuilles OU cliquez droit sur
la colonne sélectionnée, puis cliquez sur Insérer
une colonne à gauche.
5.2-Modifier la largeur d’une colonne
Pour modifier la largeur d’une colonne :
·
Sélectionnez-la ou les colonnes à modifier
·
Sous l’onglet Accueil, dans le groupe cellule,
cliquez sur Format puis Largeur de colonne
·
Tapez le nombre de la largeur souhaitée et valider
par OK.
5.3-Modifier
la hauteur d’une ligne
Pour modifier la hauteur d’une ligne :
·
Sélectionnez-la ou les lignes à modifier
·
Sous l’onglet Accueil, dans le groupe cellule,
cliquez sur Format puis Hauteur
de ligne
·
Tapez le nombre de la hauteur souhaitée et valider
par OK.
5.4-
Supprimer une ligne ou une colonne
Pour supprimer une ligne ou une colonne :
·
Sélectionnez la ligne ou la colonne à supprimer.
·
Sous l’onglet Accueil, dans le groupe cellule
cliquez sur Supprimer OU
cliquez droit sur la cellule sélectionnée, puis Supprimer.
5.5- Figer les lignes et les colonnes
Excel fige les lignes situées au-dessus et les
colonnes situées à gauche de la cellule sélectionnée.
·
Pour sélectionner une cellule, placez le pointeur
de la souris sur la cellule choisie, puis cliquez avec le bouton gauche.
·
Sous l’onglet Affichage,
cliquez sur figer les volets
·
Placez enfin le pointeur sur figer les volets, puis cliquer avec le bouton gauche de la souris.
Les
colonnes situées à gauche de la ligne verticale se trouvent alors figées. Elles
restent gelées à ’’
l’écran’’ lorsque vous faites défiler la feuille de calcul.
5.6-
Formater une colonne ou une ligne
Pour formater la largeur d’une colonne
·
Sélectionnez la colonne à formater
·
Sous l’onglet Accueil,
cliquez sur Format
·
Cliquez sur Largeur
de colonne.
Une boite de dialogue apparait.
Tapez
la valeur de la largeur de la colonne.
6: gestion des feuilles de calcul
6.1- Insérer une feuille
Pour insérer une nouvelle feuille dans
un classeur :
·
Cliquez droit sur l’onglet d’une feuille, sélectionnez
Insertion puis cliquez surFeuillepuis sur ok
6.2- Renommer
une feuille
Pour renommer une feuille :
·
Cliquez droit sur l’onglet de la feuille, sélectionnez
Renommer
·
Tapez le nouveau nom
6.3-
Déplacer une feuille
Pour
modifier l’ordre des onglets d’une feuille :
·
Cliquez sur l’onglet désiré et glisser-le à l’endroit souhaité.
6.4- Copier une feuille
Pour
copier une feuille :
·
Cliquez droit sur l’onglet de la feuille à copier
·
Cliquez copier puis cochez créer une copie
6.5- Supprimer une feuille
Pour supprimer
une feuille :
·
Cliquez droit sur l’onglet d’une feuille puis sélectionnez Supprimer
6.6- Colorer l’onglet d’une feuille
Pour
colorer l’onglet d’une feuille :
·
Cliquez droit sur l’onglet d’une feuille,
·
Sélectionnez Couleur d’onglet puis choisissez la couleur souhaitée
6.7- Fractionner une feuille de calcul
Ø Fractionner une feuille de calcul verticalement
·
Placez en premier lieu le pointeur sur la ligne qui sera la ligne de
séparation
·
Sous l’onglet Affichage,
cliquez sur fractionner
La feuille
de calcul se trouve divisée horizontalement en deux sections.
Les lignes situées en dessous de la ligne de séparation se trouvent
alors figées. Elles restent ‘’gelées ‘’ à l’écran lorsque vous faites
défiler la feuille de calcul.
Pour
faire défiler les lignes situées au-dessus de la ligne de séparation, placez le
pointeur sur ▼ ou
▲ puis cliquez sur le bouton gauche.
Pour
faire défiler les lignes situées en dessous de la ligne horizontale, placez le
pointeur de la souris sur ▲ ou ▼, puis cliquez avec le bouton
gauche de la souris.
Ø Fractionner une feuille de calcul horizontalement
·
Placez en premier lieu le pointeur sur la colonne qui sera la colonne de
séparation
·
Sous l’onglet Affichage,
cliquez sur fractionner
Les
colonnes situées à gauche de la colonne de séparation trouvent alors figées.
Elles restent ‘’gelées’’ à l’écran
lorsque vous faites défiler la feuille de calcul.
La
feuille de calcul se trouve divisée verticalement en deux sections.
Pour
faire défiler les colonnes à gauche de la colonne de séparation, placez le
pointeur sur◄ ou
► puis cliquez sur le bouton gauche.
Pour faire
défiler les lignes situées en dessous de la ligne horizontale, placez le
pointeur de la souris sur ► ou ◄, puis cliquez avec le bouton
gauche de la souris.
6.8
-Passer d’une feuille de calcul à une autre
·
Cliquez sur les onglets de séparation situés en bas de la feuille et à
l’extrême droite.
II-MISE EN FORME D’UNE FEUILLE DE CALCUL
1-Mise en forme des cellules
Pour améliorer la
présentation et l’apparence du texte d’un tableau :
· Sélectionnez la ou les cellules
souhaitées.
· Sous l’onglet Accueil, Cliquez
sur le groupe Police, on obtient le
tableau suivant :
Fig.53
Gras, italique et souligné
·
Sélectionner la ou les cellules à modifier
·
Sous l’onglet Accueil,
Cliquez su G pour le gras, I pour
l’italique et S pour le
souligné
Police
et taille de caractère
·
Sélectionner la ou les cellules à modifier
·
Sous l’onglet Accueil,
choisissez une police et une taille
Ajout
des bordures
·
Sélectionner la ou les cellules à modifier
·
Sous l’onglet Accueil,
choisissez un type de bordure 
OU
Pour sélectionner un style de bordure,
·
Cliquer sur Bordure,
·
Choisir Contour pour une bordure extérieure
ou Intérieur pour une bordure intérieure.
Ensuite choisir un contour parmi les 8 contours proposés ;
·
Cliquer sur OK
Modifier
les couleurs
·
Sélectionner la ou les cellules à colorier
·
Sous l’onglet Accueil,
choisissez une couleur de remplissage 
Fusionner
et centrer
·
Sélectionner la ou les cellules à fusionner
·
Cliquez sur Accueil
·
Dans le groupe Alignement,
cliquez sur Fusionner
OU
·
Cliquer sur Alignement,
·
Cocher Fusion des cellules
Excel avertit que la fusion des cellules va permettre uniquement de
conserver la valeur de la cellule supérieure gauche, les autres valeurs seront
ignorées.
·
Cliquer sur OK
Aligner
le texte à gauche, au centre et à droite
·
Cliquez sur 
pour la gauche
·
Cliquez sur 
pour le centre
·
Cliquez sur 
pour la droite
Renvoyer
automatiquement à la ligne
·
Cliquez sur Accueil
·
Cliquez sur Renvoi automatique à
la ligne
Aligner
le texte en haut, au centre et en bas
·
Cliquez sur Accueil
·
Dans le groupe Alignement
·
Cliquez sur haut, centre,
bas 
2-
Orientation du texte
·
Cliquer sur Alignement
·
Saisir un angle d’inclinaison
·
Cliquer sur OK.
Renvoi automatique à la
ligne
·
Cliquer sur Alignement
·
Cocher Renvoyer automatique à la ligne
Couleur
·
Cliquer sur Remplissage pour choisir la
couleur du motif
III-FORMULES ET FONCTIONS
1-Les
formules
Les
formules sont des équations qui effectuent des
calculs sur les valeurs d’une feuille de calcul. Elles commencent par un
signe égal (=). Une formule peut également contenir les éléments suivants :
des valeurs, des références, des opérateurs et des parenthèses.
Exemples : = 60+10-8
avec (+ l’addition et – la soustraction)
= (14+A3)*D6 avec (*
: multiplication)
= (B2-C4)^2 avec (^ :
exposant)
1.1-La
saisie d’une formule
Pour faire l’addition de 100
et 300, par exemple :
-Saisissez 100 dans la
cellule A2
-Saisissez 300 dans la
cellule A3
-Saisissez la formule =
A2+A3 dans la cellule A4
-Appuyez sur la touche Entrée
1.2-Recopie
d’une formule
Pour recopier une formule dans les autres cellules
d’une ligne :
·
Saisir la formule dans la cellule A4 par
exemple, puis Entrée
·
Sélectionner les cellules B4, C4, C5 ….
·
Dans le groupe Edition, cliquez sur
Remplissage à droite
La formule est recopiée
1.3-References des cellules (adressage)
Pour recopier une formule ou une
fonction, on utilise les options copier / coller Ou la recopie
incrémentée. La recopie incrémentée
est utilisée si la formule ou la fonction se trouve dans les cellules
adjacentes de la même ligne ou de la même colonne.
Ces
méthodes sont très utiles et très pratiques mais elles peuvent générer des
problèmes. On constate que, pendant que la lettre varie de A à ZZZ en mouvement
de translation horizontal, le chiffre varie aussi de 1 à 1 048 576…. en mouvement de translation vertical.
Par exemple, Si on copie la formule =A1+A2 de
la cellule A3 et on la colle dans la celluleB7, elle devient
- dans la cellule B7 plutôt la formule =B5+B6(Notez que la lettre change ainsi que les
chiffres)
-et dans la cellule C3,
on trouvera =C1+C2. Dans tous
les cas ce n’est plus la formule saisie en A3.
1.3.1-Référence relative
Les
références d’une cellule sont dites relatives lorsqu’elles dépendent de la position de la cellule
qui contient la formule. Dans le cas de l’exemple précédant, les références (A1,
A2) – (B5, B6) - (C1, C2) s’appellent des références relatives respectivement aux cellules A3, B7, C3(elles
changent).
1.3.2- Référence absolue
Une
référence absolue est une adresse exacte (fixe) d’une cellule, quelle
que soit la position de la cellule qui contient la formule.
Pour
rendre une référence absolue, on place un signe $ aussi bien devant la lettre que devant le chiffre : =$A$1. Lors de la recopie, cette
adresse sera fixe.
1.3.3 Référence semi-relative
Une référence est semi-relative lorsqu’un seul
élément varie (soit la lettre, soit le chiffre). Dans ce cas on fixe l’élément
qui ne doit pas varier avec $.
Exemple: A$1 et $A1
sont semi-relatives
Résumé :
|
Référence
relative Exemple : N38 |
·
N varie en cas de
translation horizontale de la formule qui contient la référence ·
38 varie en cas de
translation verticale de la formule qui contient la référence |
|
Référence
semi-relative Exemple : $N38 |
·
Seul 38 varie en cas de
translation verticale de la formule |
|
Référence
semi-relative Exemple : N$38 |
·
Seul N varie en cas de
translation horizontale de la formule |
|
Référence
absolue Exemple : $N$38 |
·
La référence n’est pas
modifiée en cas de recopie. |
2- Les fonctions
Les
fonctions sont des formules prédéfinies qui effectuent des calculs en utilisant
des valeurs particulières appelées arguments.
2.1- Syntaxe d'une fonction
· =Nom de la fonction
(arguments1 ; arguments2 ;…)
Les arguments peuvent être des nombres,
du texte, des valeurs logiques, des Références et des
fonctions. Excel dispose de nombreuses fonctions réparties en catégories :
Math&trigo,
Date &heure, Statistique, Texte, …
2.2-Catégories
Fonctions
Math&trigo
- Somme (nombre1 ; nombre2 ;…)
- Produit (nombre1 ; nombre2 ;…)
- Pi ()
- Puissance (nombre ; puissance)
- Racine (nombre)
- Cos (nombre)
-Sin (nombre)…
Date
&heure
-Aujourd’hui()
-Maintenant () …
Statistique
- Max (nombre1 ; nombre2 ; …)
- Min (nombre1 ; nombre2 ; …)
- MOYENNE (cellule1 ; cellule2 ; …)
-ECARTYPE (cellule 1 :cellule2)
-VAR.P(cellule 1 : cellule 2)
Texte
- Majuscule (texte)
–Minuscule (texte)…
2.3-La saisie d’une fonction
Pour calculer
la somme des nombres suivants : 20,10,25, 45
-Saisissez chaque nombre
dans une cellule (par exemple dans B2, B5)
-Dans la cellule résultat
(B8 par exemple), saisissez la fonction suivante :
= Somme (B2 ;B3 ;B4 ;B5) ou =Somme (B2 : B5) ou = Somme
(20 ;10 ;25 ;45)
Remarques :
i) Les deux points (:) dans
la fonction signifient que la sélection va de la cellule B2 jusqu'à B5.
ii) Le bouton somme automatique
permet de faire la somme des valeurs de la même ligne ou de la même colonne.
iii) Ne laissez aucun espace
entre le nom de la fonction et la première parenthèse telle que :
=Somme (...). Elle ne donnera pas de résultat.
2.4-Quelques fonctions prédéfinies avancées
·
Fonction RANG ()
Elle permet de
trouver une classification.
Syntaxe :
=RANG
(nombre ; référence ; [ordre])
Ordre peut prendre la valeur 0 pour Décroissant et
la valeur 1 pour croissant
Exemple :
=RANG (E3 ;
E$3 : E$8 ;0)
·
Fonction SI ()
Cette fonction permet
d’établir des comparaisons logiques entre une valeur et le résultat attendu.
Une instruction SI peut avoir deux résultats. Le premier résultat est appliqué
si la comparaison est vérifiée, sinon le deuxième résultat est appliqué.
Syntaxe :
SI (test_logique ;
[valeur_si_vrai] ;[valeur_si_faux])
Exemple :
SI (C2= " oui " ;1 ;2) indique SI (C2= " oui ",
renvoyer 1, sinon renvoyer un 2)
·
Fonction NB.SI ()
Cette fonction permet
de compter le nombre de cellules qui répondent à un critère ; par exemple
pour compter le nombre de fois où le nom d’une ville apparait dans une liste de
client.
Syntaxe :
=NB.SI (plage ;
critère)
Exemples : =NB.SI
(A2 : A5 ; " Londres "
=NB.SI (A2 :
A5 ; A4)
·
Fonction SOMME.SI ()
Cette fonction est
utilisée pour calculer la somme des valeurs d’une plage qui répond aux critères
spécifiés.
Syntaxe :
=SOMME.SI
(plage ; critère ; [somme_plage])
Remarques :
• Dans une meme formule, on peut utiliser plusieurs fonctions
predefinies.
Exemple :
= Somme (moyenne (A1 :
A10) ; moyenne (C1 : C10))/NB(B1 : B5)
• Si la fonction SI doit choisir entre plus que deux valeurs, on
peut utiliser la meme fonction
comme argument de la premiere.
Exemple :
= SI(A2 < 9 ; "Redouble" ;
SI (A2 <10 ; " Racheté " ; " Passe "))
3-Somme sur plusieurs feuilles de
calcul
La somme peut
être réalisée sur plusieurs feuilles de calcul de la façon suivante :
soient quatre feuilles de calcul Feuil1, Feuil2,Feuil3,Feuil4.
La cellule B2 dans la feuille de calcul Feuil4 va réaliser le calcul des
valeurs sommées. Les cellules B2 des trois autres feuilles de calcul seront
alimentées en valeurs. Pour simplifier, prenons 5,10 et 15.
1-Erire la formule= SOMME ( dans la cellule B2 de la feuille de
calcul Feuil4.
2-Cliquer avec le pointeur de la souris sur la
cellule B2 dans la première feuille de calcul Feuil1.
3-Saisissez ensuite un signe « + »
4-Aller dans la Feuil2 et cliquer sur B2, puis
appuyer le signe « + ».
5-Repeter le processus avec la troisième feuille de
calcul Feuil3.
6-Fermer ensuite la parenthèse.
Remarque : Une variante
sera de procéder ainsi qu’il suit : les cellules B2 des trois feuilles de
calcul Feuil1, Feuil2 et Feuil3 sont remplies des valeurs 5,10 et 15.
Sélectionner directement toutes les feuilles de calcul. Ecrivez, dans la
cellule B4 de la feuille de calcul SOMME, la formule =SOMME (Feuil1 :Feuil3! B2). La somme de ces feuilles de calcul
indépendantes sera effectuée automatiquement.
4-Tableau
croisé dynamique
Le
tableau croise dynamique qui permet de composer rapidement un tableau synthèse
provenant d'une masse de données. Comme le nom l'indique, ce tableau permet
d'avoir le sommaire d'une ou de plusieurs variables a
la fois. De plus, ce tableau est dynamique, cela veut dire qu'on peut ajouter,
retirer et modifier la présentation du tableau.
Créer un tableau croisé dynamique
1-Sélectionner
les cellules à partir desquelles vous voulez créer un tableau croisé dynamique
2-Sélectionner
insertion/tableau croisé dynamique.
3-sélectionner
Nouvelle feuille de calcul, puis OK.
4-Pour ajouter
un champ à votre tableau croisé dynamique, sélectionner les champs qui vont
faire partie du tableau et les glisser vers le zone cible (valeurs, colonnes,
lignes, filtres).
5-Protéger
des cellules
·
Cliquer sur Protection pour verrouiller ou
masquer les cellules
NB.
Pour protéger une
cellule ou un classeur, allez dans le menu Révision
et choisir Protéger la feuille ou le
classeur.
6-Trier et
filtrer
Pour trier un tableau,
·
Sélectionner
le tableau
·
Dans
le menu Données, choisir Trier
·
Choisir
un critère de tri (par ordre alphabétique, par valeur)
·
Valider
Pour appliquer
un filtre
·
Sélectionner le tableau
·
Dans le menu Données, choisir Filtre. De petites flèches apparaissent à droites des
cellules titres, cliquer sur la flèche duchamp de critère et cocher un critère.
·
Valider.
7-Mise en
forme conditionnelle
La mise en
forme conditionnelle facilite la mise en évidence de certaines valeurs ou
l’identification de cellules particulières. Elle modifie l’apparence d’une
plage de cellules en fonction d’une condition (ou d’un critère). Vous pouvez
utiliser la mise en forme conditionnelle pour mettre en surbrillance des
cellules qui contiennent des valeurs correspondantes à une condition.
Appliquer une mise en forme
conditionnelle à du texte
1-Sélectionnez la plage de cellules, le tableau ou
l’ensemble de la feuille où vous voulez appliquer une mise en forme
conditionnelle.
2-Sous l’onglet Accueil, cliquez sur Mise
en forme conditionnelle, pointez sur Règles de mise en surbrillance
des cellules, puis cliquez sur Texte qui contient.
3-Dans la zone à côté du contenant, tapez le texte
que vous voulez mettre en surbrillance, puis cliquez sur OK.
IV- IMPRIMER UN DOCUMENT
1-Définir une zone d’impression
Une zone d’impression est une ou plusieurs plages de
cellules que vous désignez pour imprimer lorsque vous ne voulez pas imprimer la
feuille de calcul entière. Une feuille de calcul peut avoir plusieurs zones
d’impression. Chaque zone d’impression s’imprime sur une page distincte.
Pour définir une zone d’impression dans
une feuille de calcul :
·
Dans
la feuille de calcul, sélectionnez les cellules que vous voulez définir comme
zone d’impression.
·
Sous
l’onglet Mise en page, cliquez sur Zone d’impression, puis sur Définir.
2- Aperçu avant impression
·
Pour visualiser le document tel qu’il
apparaîtra à l’impression :
·
Ouvrez le document à visualiser
·
Cliquez sur Fichier,
puis sur Imprimer. La fenêtre suivante s’affiche :
Fig.54
On voit à droite
de cette fenêtre, un aperçu de la page.Utilisez les
boutons de la barre d'outils suivante pour parcourir la page ou effectuer des
ajustements avant d'imprimer.
2- Paramétrer le document
Pour imprimer un document,
indiquez le nombre de copies et le nombre des pages à imprimer en utilisant les
zones suivantes :
_ La zone Etendue de page
:
_ Tout : pour
imprimer toutes les pages d’un document.
_ Pages en cours : pour
imprimer la page sélectionnée.
_ Pages : pour
imprimer quelques pages (pour imprimer par exemple lespages 1, 4, 5, 6, 7,8 et
12 tapez 1 ; 4-8 ; 12)
_ La zone Copies : entrez le nombre de copies à imprimer.
·
Cliquez sur OK.
3-Imprimer un document
·
Cliquez sur cliquez sur OK
EXERCICES
CONTROLE DE CONNAISSANCES :
1-Définir :
feuille de calcul, classeur.
2-Citer
02 tableurs.
3-Quels
sont les rôles d’un tableur ?
4- Comment appelle-t-on l’intersection d’une ligne et
d’une colonne d’un tableur ?
5- Comment
peut-on rendre une cellule active sous Excel ?
6- La référence d’une cellule est « B5 ».Que signifient cette indication ?
EXERCICE I: Le tableau suivant présente
l'état de salaires des employés d'une société.
|
Questions : |
|||||||||
|
1. On désire remplir les colonnes F,G,H
et I du tableau ci-dessus .Quelles
formules Excel doit-on utiliser pour : |
|||||||||
|
a) Remplir la colonne "Salaire imposable "
sachant que : Salaire imposable = Nbre heures * Prix heure |
|||||||||
|
|
|||||||||
|
b) Remplir la colonne "Impôt" sachant que
: Impôt = 15% * Salaire imposable. |
|||||||||
|
c) Remplir la colonne "Salaire net"
sachant que : |
|||||||||
|
Salaire net = Salaire imposable + prime - retenu -
Impôt |
|||||||||
|
d) Calculer les totaux en b2…h2 |
|||||||||
|
2. On voudrait connaitre le pourcentage chaque
salaire par rapport à la masse salariale totale. Pour cela, On a
introduit en i2 la formule :+h2/h8*100 et
on trouve 11,5222269. |
|||||||||
|
a) Quelle est la procédure à utiliser pour trouver
un résultat avec deux chiffres après la virgule. |
|||||||||
|
b) En voulant faire la recopie en bas pour trouver
les autres pourcentages, on trouve des Résultats
erronés, comment rectifier la formule en h2. Expliquer. |
|||||||||
EXERCICE II:
Questions :
|
1. On désire remplir les colonnes E, F et G du
tableau ci-dessus. Quelles formules Excel doit-on utiliser pour : |
||||||||||||||||||||||||||||||||||||||||
a)Trier le tableau en ordre croissant selon la durée de
location. |
|
|||||||||||||||||||||||||||||||||||||||
EXERCICE
III : On donne le tableau suivant :
|
|
A |
|
B |
C |
D |
E |
F |
G |
G |
|
1 |
|
|
|
|
|
|
|
|
|
|
2 |
|
|
Noms des
commerciaux |
Quantités vendues |
Prix |
Montant |
Prime (20% du
montant) |
Rang |
Mention |
|
3 |
|
|
ITALEN |
150 |
20050 |
|
|
|
|
|
4 |
|
|
BELKA |
220 |
19090 |
|
|
|
|
|
5 |
|
|
DIEUDONNE |
300 |
27000 |
|
|
|
|
|
6 |
|
|
PATRICK |
404 |
15900 |
|
|
|
|
|
7 |
|
|
TOTAL |
|
|
|
|
|
|
Questions :
1-Quelle est la formule qu’on doit introduire en E3 pour calculer le
montant de ITALEN ?
2-Comment obtenir rapidement les montants en E4, E5, E6
3- Quelle est la formule qu’on doit introduire en F3 pour calculer le
montant de la prime de ITALEN ?
4- Comment obtenir rapidement les montants en F4, F5, F6
5- En G3 on trouve 4: que signifie ce
résultat ?Quelle est la formule utilisée ?
6-Quelle formule doit-on Insérer en H3 la formule pour trouver la mention
de chaque commercial
EXERCICE IV:
L’élève Ali marque des points quand il répond aux
questions posées dans les différentes matières de sa classe. Les points sont
regroupés dans le tableau suivant :
|
|
A |
B |
C |
|
1 |
MATIERES |
POINTS |
|
2 |
MATHS |
5 |
|
3 |
PHYS |
2 |
|
4 |
CHIM |
3 |
|
5 |
INFO |
4 |
|
6 |
HIST |
1 |
|
7 |
GEO |
3 |
|
8 |
MATHS |
2 |
|
9 |
MATHS |
3 |
|
10 |
HIST |
3 |
|
11 |
MATHS |
1 |
|
12 |
GEO |
3 |
|
13 |
ECM |
4 |
|
14 |
MATHS |
2 |
|
15 |
ECM |
6 |
|
16 |
MATHS |
1 |
|
18 |
MATIERES |
NOMBRE TOTAL DE POINTS OBTENUS |
NOMBRE DE BONNES REPONSES |
|
19 |
MATHS |
|
|
|
20 |
PHYS |
|
|
|
21 |
CHIM |
|
|
|
22 |
INFO |
|
|
|
23 |
HIST |
|
|
|
24 |
GEO |
|
|
|
25 |
ECM |
|
|
Questions :
1- Donner en
c17 la formule qui permet de calculer les totaux des points d’Ali
2-En
introduisant en C19 la formule =NB.SI (A2 : A16 ; A19), on
trouve 6
Quelle est la
signification de ce résultat ?
3-Quelle est
la formule qu’on devra introduire en B19 pour calculer automatiquement le total
des points en MATHS ?
EXERCICE V:
On donne
le tableau suivant :
|
|
A B C D E F G |
||||||
|
1 |
Facture N° |
||||||
|
2 |
|
|
|
|
Code client |
5000 |
|
|
3 |
TVA |
25% |
|
|
|
|
|
|
4 |
Produit |
Catégorie |
Quantité |
Prix unitaire (FCFA) |
Total HT |
Remise |
Montant à payer |
|
5 |
clavier |
A |
40 |
3500 |
|
|
|
|
6 |
Souris |
A |
52 |
1500 |
|
|
|
|
7 |
Haut-parleur |
B |
22 |
500 |
|
|
|
|
8 |
Webcam |
C |
9 |
32000 |
|
|
|
|
9 |
Appareil photo |
C |
4 |
70000 |
|
|
|
|
11 |
|
|
Total facture |
|
|||
Questions :
1- Quelle est la
formule Excel qu’on devra introduire en E5 pour calculer le prix HT de
clavier ?
2-Quelle
technique doit-on utiliser pour calculer rapidement les autres prix en E6,
E7, E8 et E9 ?
3-On
donne :
Remise = 10%*total HT Si catégorie «A »
Remise = 7%*total HT Si catégorie «B»
Remise = 5%*total HT Si catégorie «C »
Ecrire la formule Excel qu’on doit utiliser pour
calculer les remises selon les catégories des produits.
4- pour calculer le montant à payer :
Montant
à payer=total HT+ (total HT*TVA) - remise
On a introduit. En G5 :+
E5+E5*B3-F5 pour trouver 161000 qui est juste. Mais en faisant la
recopie vers le bas on trouve un résultat erroné (VALEUR !)
Corriger cette formule.
5-Quelle est la procédure pour représenter sous forme histogramme le prix unitaire de chaque produit.
EXERCICE VI:
Les tableaux suivants
représentent les productions des entreprises dans trois régions :
TRAVAIL A FAIRE
1)
On introduit en D9 la formule Excel =SI (B9=A$3;26*C9;SI
( B9=A$4;13*C9;32*C9)). Que fait cette formule ?
2) Quelle est la formule Excel
pour calculer le total des nombres d'employés et celui des productions.
3) Donner les formules pour remplir la colonne Performance, sachant qu'elle est :
● «
Excellente » si Production >1000
● « Acceptable
» si 500<=Production <=1000
● «
Insuffisante » si Production<500
4) Quelle est la procédure pour trier le Tableau 2 en ordre croissant selon la
colonne Région.
5) Comment utiliser la mise en forme
conditionnelle pour colorer en Vert les
cellules de la colonne Performance dont
la valeur est « Excellente » et
en Rouge celles dont la valeur
est « Insuffisante ».
6) Quelle est la formule pour
déterminer sous le tableau 2, le nombre des entreprises ayant une production
>1000.
7) Comment déterminer la somme de
production de la région de l’Ouest.
8) Quelle est la procédure
pour représenter un graphique sous forme d'histogramme de forme «
pyramides 3D », dans une nouvelle feuille, la colonne "Région" en fonction de la colonne "Nombre d'employé". En
ajoutant votre nom et prénom en texte libre sur le graphique.
EXERCICES
CONTROLE DE CONNAISSANCES :
1-. Une feuille de calcul est un tableau électronique formé de plusieurs
lignes et de plusieurs colonnes qui contient des éléments qui aideront à
présenter les données et à effectuer les calculs de manière efficace
-Un classeur est un ensemble de
feuille de calcul.
2-
Excel, Quattro, Open calc
3-Créer et manipuler des
tableaux, Calculer des données, Présenter des données sous forme de graphique
4- cellule
5- Il suffit de cliquer sur cette cellule.
6- Colonne B, 5eme ligne
EXERCICE I:
|
1 a) En F2 : =PRODUIT(B2 :C2) b).En G2 : +F2*15/100 |
||||||||
|
c) En H2 :+F2+D2-E2-G2 |
||||||||
|
d) En B8 : =SOMME (B2 :B7),puis Recopie à droite |
||||||||
|
2. ).Cliquer sur Nombre dans le menu Accueil, puis sur Nombre |
||||||||
|
Choisir 2 comme nombre de décimales. |
||||||||
|
b) La formule juste est :+H2/$H$8 |
|
|||||||
Pour recopier une formule ou une fonction,
on utilise les options copier / coller Ou la recopie incrémentée. La recopie incrémentée est utilisée si
la formule ou la fonction se trouve dans les cellules adjacentes de la même
ligne ou de la même colonne.
Ces méthodes sont très utiles et très pratiques mais elles peuvent
générer des problèmes. On constate que, pendant que la lettre varie de A à ZZZ
en mouvement de translation horizontal, le chiffre varie aussi de 1 à
1 048 576…. en mouvement de translation
vertical.
Par exemple, Si on copie la
formule =A1+A2 de la cellule A3 et on la colle dans la celluleB7,
elle devient
- dans la cellule B7
plutôt la formule =B5+B6(Notez que la lettre change ainsi que les
chiffres)
-et dans la
cellule C3, on trouvera =C1+C2. Dans tous les cas ce n’est plus la formule saisie en A3.
Pour rendre une référence absolue, on place un signe $ aussi bien devant la lettre que
devant le chiffre : =$A$1. Lors
de la recopie, cette adresse sera fixe.
EXERCICE II:
|
1. a) En D3 :=SI(C<5 ;C2*1500 ;C2*1200) b) En
F3 : +D*25/100 En E3 : +D3+E3 |
|||||||
|
a)Sélectionner le tableau Dans l’onglet. Données , choisir Trier Choisir la colonne B3 Choisir Du
plus petit au plus grand
Sélectionner Dans l’onglet. Données,
choisir Filtrer Cliquez sur
Avancé Introduire une zone de critère Cliquez sur
Ok |
|
||||||
EXERCICE III:
1- +c3*d3
2- Sélectionner les cellules E4 ;E5 ;E6 ; puis Recopier en bas
3- +e3*20/100
4- Sélectionner les cellules F4 ;
F5 ; F6 ; puis Recopier en bas
5-
Ce montant occupe la 4eme position dans un classement par ordre
décroissant. La formule est =RANG (E3 ; E3 :E6 ;
0)
6- =SI(E3>5000000;"bien";"passable")
EXERCICE IV:
1-
=SOMME (b2 :b16)
2. 6 représente les nombres de fois que Ali donne une
bonne réponse en maths.
3 =SOMME.SI (A2:A16;A19;B2:B16)
EXERCICE V:
1. =+C5*D5
2. Sélectionner les cellules
E6 ;E7 ;E9 ; puis Recopier en bas
3. =SI(B5="a";E5*10/100;SI(B5="b";E5*7/100;E5*5/100))
4. =E5+E5*$B$3 -F5
EXERCICE VI:
1-Calcule la production
par employé de chaque région
2-Nombre total des
employés : =SOMME (C9 :C15)
Nombre total de de la production: =SOMME (D9 :D15)
3-=SI(D9>1000;"excellente";SI(D9<500;"Insuffisante";"Acceptable"))
4.
Sélectionner le tableau 2
-Aller dans le menu Données Cliquer Trier/Puis,
choisir trier par Région
5. -Sélectionnez la plage de cellules E9 :E15
-Sous l’onglet Accueil, cliquez sur
Mise en forme conditionnelle, pointez sur Règles de mise en
surbrillance des cellules/cliquez sur Texte qui contient/Dans la zone à
côté du contenant, tapez le texte que vous voulez mettre en surbrillance, puis
cliquez sur OK.
-remplir excellente et cliquer sur Remplissage
vert
-remplir insuffisante et cliquer
sur Remplissage rouge
6. =NB.SI (D9:D15;
">1000")
7. C’est la somme de
production de la région de l’Ouest.
8-Aller
dans le menu Insertion /Cliquer sur Barre/Choisir pyramidal
et valider