Cours Excel 2010
Leçon 1 :
• Les bases (bases, texte) • Les bases (mise en forme du texte) • Les bases (alignements, fusion de cellules, couleurs) • Les bases (déplacer/copier du contenu) • Les bases (enregistrement, impression, feuilles) • Les bases (exercice à télécharger)
Leçon 2 :
• Les tableaux (bordures) • Les tableaux (couleurs de fond, modèles de tableau) • Les tableaux (exercice à télécharger)
• Format de cellule et mise en forme
Leçon 4 :
• Insertion d'objets (formes) • Insertion d'objets (WordArt et images) • Insertion d'objets (SmartArt)
Leçon 5 :
• La recopie incrémentée • La recopie incrémentée (exercice à télécharger)
Leçon 6 :
• Formules de calculs, fonctions SOMME et MOYENNE • Fonction SI, recopie de formules • Opérateurs de comparaison • Fonctions et calculs (exercice à télécharger)
Leçon 7 :
• Insertion de graphiques • Insertion de graphiques sparkline
Leçon 8 :
• Les mises en forme conditionnelles • Les mises en forme conditionnelles sur mesure • Les mises en forme conditionnelles (exemples 1 à 3) • Les mises en forme conditionnelles (exemples 4 à 6) • Les mises en forme conditionnelles (exemples 7 à 10)
Leçon 9 :
• Mise en page • Liste déroulante • Listes déroulantes multiples
Leçon 10 :
• Utilisation d'une base de données • Utilisation d'une base de données (suite)
Cours Excel : les bases
!" # $
" % & "
’ % ( &
’ % ) & &
( * + * ( ,
- * ) . *
/ 0
( * + * ) . / 12
3 4
" 5
6
" 7 ! 8 8 * $
" 9 + % :: & * 2
+ % :: & % :; & < =
> 7 % :: &
3 4 ? " 5
La recopie incrémentée permet souvent un gain de temps considérable, en vous évitant de répéter maintes fois les mêmes opérations. Taper 1 copier selectionner en bas a droite de la derniere cellule, « Incrémenter une série » La recopie incrémentée s’applique aussi aux jours, mois, dates, heures, etc.
2 " 7 @ " 7 @ A 4 @ + @ + @ < @ !B [email protected] " @ "
Fusion de cellules
’
< 9 " 2 9
Attention 9
C
Déplacer une cellule
<
3 2 ? 2 D 7
.
Copier des cellules
< " ) "
6 8 0 2
68
Reproduire une mise en forme
" 3
<
Retour à la ligne
3 5 ,
Excercice 1
Enregistrer un classeur
3 >
’ 7 E 8
’
• XLSX : C'est le format par défaut des versions d'Excel 2007 et plus (recommandé)
• XLSM : Si votre classeur contient des macros, c'est l'extension qu'il vous faut (c'est également un format pour les versions d'Excel 2007 et plus)
• XLS : Si votre fichier doit pouvoir être utilisé sur des versions d'Excel inférieures à 2007, il faudra utiliser XLS (cela peut engendrer entre autres des pertes de mises en formes)
Imprimer
" % ) & % 0 &
8 ? " 2 % 0 & 8
Modèles de documents
" % ) & % 6 &
5 2 7 > > !+ $
Les feuilles du classeur
3 2 *
9
• Insérer de nouvelles feuilles
• Supprimer la feuille sélectionnée
• Renommer la feuille sélectionnée (vous pouvez aussi double-cliquer sur l'onglet pour renommer la feuille)
• Changer l’ordre des feuilles (vous pouvez aussi cliquer et faire glisser l'onglet pour le déplacer)
• Copier la feuille sélectionnée
• Changer la couleur de l'onglet
• Masquer/afficher une feuille • Etc.
Sélection de cellules
F ! 2 $
3 7
3 % " , & 9
3 % " &
Cours Excel : les tableaux (bordures)
3 2 2
1èreméthode : le bouton
<
2
8 5 2
2e méthode :)
< % ) &
" % G & *
3e méthode: le bouton tracer manuel
"
" 2 "8 2
2
Couleur et style des traits
5 2 ! ( $
2 % G &
> 2 % ) &
Cours Excel : les tableaux (couleurs, modèles)
Couleur de fond
6 7 ? 8 <
! 4 $ * % ) & % H &
’ - I # 8 % A & % + 7 &
7 2 ! % " & $
Les modèles de tableau
A 2
< 2
" % A 2 & 5
Exercice 2
Cours Excel : format de cellule et mise en forme
Format de cellule
< % ) &
# > 8
L’onglet « Nombre »
3 2 2 % 6 2 & 2 9
3 5 2 % A & % " 2 & 9
<
# % # &
2 ! $
L’onglet « Alignement »
4
! $
L’onglet « Police »
0
Les onglets « Bordures » et « Remplissage »
0 8 2 8 4 ! ? $
Cours Excel : insertion d'objets
Les formes
8 % 0 & % ) &
’
8 % ) &
’ 9 # 9
0 2 5 !A $
Les manipulations
3 9
’ 8 ! 8 7
2 8 $
3 4 2
#8 2 % &
| H | 9 | !" | , | $ |
WordArt
3 8 % 0 &
% J & 5
5 8 % ) &
’ 2 8
7 8
Insérer une image
3 8 % 0 & % 0 &
’ 8 % 1 0 &
’ D 2 8
! 2 $
2 2 ! $
K K
SmartArt
3 < 8 % 0 & % < &
" 5 5 9
Cours Excel : la recopie incrémentée
Exemple avec une fonction
La cellule sélectionnée contient la somme des chiffres encadrés.
En procédant à la recopie de la somme:
Vous obtenez 2 autres chiffres qui représentent la somme de leur colonne de chiffres respective :
Recopier une mise en forme
Exemple de recopie de mise en forme avec les 2 lignes suivantes.
Sélectionnez et recopiez simplement les lignes.
Exercice 5
Cours Excel : formules de calculs et fonctions
Structure des formules de calcul
| = |
"
| ( ) |
7
’ >
| = |
#7
| ( ) |
7
’ 2 * F L ’ 5 7
! $
F L 9
Introduction sur les fonctions
6
0
Fonction SOMME
< E " 8 % ) & %
< &
< 2 !
2 C$
Fonction MOYENNE
0 8 % ) & " *
8
< E 5 % 0 & 9
H % A1M 66 & 9
5 1N ’ > 8
< 5
Cours Excel : fonction SI, recopie de formules
Fonction SI
" % 0 & <0
# 2 % 1 & (O % 6 &
’ test logique % 1 & % 6 &
• Test logique : D39>10 (dans ce cas, on demande si D39 est plus grand que 10)
• Si c’est vrai, la fonction affiche « Oui ».
• Si c’est faux, la fonction affiche « Non ».
|
Remarque 9 | 8 | ! | 9 K1 K$ |
P % 1 &
A
’ ! 9 8 2 Q
($
A 8 (O ! 8 % 1 & % 6 & >
$ 8 7
8 (O (O
# 7 8
3 % (O & % D;& 9
H
1 8 5 2 7 D; 8
5 K K D;
| $ |
P Q Q
| A46 | $A$46 | ! | A$46 |
08 K K Q $
7 9
P 2 D;9
| A46 | Seuil |
7 9
D; Cours Excel : opérateurs de comparaison
| > |
# 8 % &
2
<0 8 % + & 9
D'autres fonctions (Cf fiche)
;Cours Excel : insertion de graphiques
’ 2 ?
< 2 8 % 0 &
’
’
3 ! $ 5 % A 5 &
5
8 5
8 % # & 4 4 9
2
0 2 !
$ RS
T 8 % A & 2 2 8 % ) &
Modifier la source du graphique
3 % < &
A ! $ Cours Excel : insertion de graphiques sparkline
P U ! U
2 8 =O(O$
3 5
< ’
’8 % " & Cours Excel : mises en forme conditionnelles
" 8 ! 2 A)"$
" ? A)" K > 8 K
< 2 A)" % < & 9
! :O8OOO$
’ :O8OOO
A)" % (O & (O
2
2
8 V 9
Cours Excel : mises en forme conditionnelles sur mesure
3 GF
’8 2 A)" 2 GF
< 2 % 6 7 & 9
< 5 7
| = |
"7 ! G($
| =$B$8 | ! | $ |
K K $
| =B1=$B$8 |
A)" ! ) $
| =B1=$B$8 |
’7 2
! 8 $
3 7 K K Q $
3 % W 7 & % A 7 &
| $ |
Q
" *
’ 9
3 GF
Cours Excel : mises en forme conditionnelles
Exemples de mises en forme conditionnelles
A)"
1. Afficher "payé" dans une cellule lorsque la date de paiement est saisie
="IMPAYE"
| ="PAYE" |
H 8
A)" (
2. Afficher les retards de paiement en fonction de la date du jour
W . PX1PH#BP0 A)" 8 5
A)" =
3. Afficher les doublons
" A)" % 2 & 9
#
A)"
4. Identifier les personnes absentes d'un tableau (ou en double)
3 2 2 6G <0
’ 6G <0 2 8 - 2 < O
’
| <1 |
2 > ?>1
A)" D
5. Colorer une ligne sur deux
3 A1#
" 8 ! =$ 2 O
2 ( RA1#!’0W6 [email protected]=$RO
’ A)"
| +COLONNE() |
3 RA1#!’0W6 !$,"1’166 [email protected]=$RO
| 6. Rechercher un mot dans un texte et colorer la ligne | A)" : |
| =CHERCHE($B$9;$D2;1) |
’
< GL #
A)" ;
7. Mettre en forme les weekends
’ X1PH< A
R1P!X1PH< A! ($R(@X1PH< A! ($RY$
# 8 ! % ) &$
R# + !"[email protected]#:@($
A)" Y
8. Quadriller automatiquement un tableau
’ A)" 8 5 G 8
A)" F
9. Colorer la dernière valeur d'une colonne
’ A)" 7 " . 6G ’
R +!6G ’!"Z(9"($R6G ’!"9"[email protected]"([SKK$
A)" L
Cours Excel : mise en page
3 % ) & % 0 &
< 5 8
< 4 8
0
Définir une zone d'impression
’ 2 8 4
1P< 31P \ 8 % & ! $ 8 2 2
3 " % \ 0 & % # &
3 > 8 8 2 > 9
% 0 8 2 &
Cours Excel : liste déroulante
# /
3 8 % # &
<8 8 % ) & % 1 & % 3 H 2 & % # &
P
Liste déroulante
" % 0 & % \ &
< % ) V &
% 3 & ! $
% " & ! $
% 6 2 & 2 2
’ >
’ ;
Obtenir la valeur texte à partir du numéro
6 ] M U ; : !
2 $
3 2 06# ^
% A & % 6 _ & ! 8
> % ) V &$
? 9
Cours Excel : listes déroulantes "validation"
< 8 % # &
% &
# % ’ & ! $
2 - 9
Fonctions Excel
| Date et heure : | |
| AUJOURDHUILogique : | (afficher la date du jour) |
| SI | (effectuer des actions en fonction d'une condition) |
| SI imbriqués Math et trigo : | (imbriquer plusieurs conditions SI) |
| ALEA | (générer aléatoirement une valeur) |
| ARRONDI | (arrondir une valeur) |
| (arrondir une valeur vers le bas) | |
| (arrondir une valeur vers le haut) | |
| RACINE | (effectuer la racine carrée) |
| SOMME | (calculer la somme d'une série de valeurs) |
| (calculer la somme à partir des valeurs qui remplissent la condition) |
Recherches et référence :
| CHOISIR | (afficher une valeur ou effectuer une action en fonction d'un choix) |
| EQUIV | (renvoyer la position d'une valeur dans une liste) |
| INDEX | (renvoyer une valeur en fonction d'un n° de colonne et d'un n° de ligne) |
| INDEX + EQUIV | (semblable à RECHERCHEH/RECHERCHEV sans la contrainte de la première ligne/colonne) |
| RECHERCHEH | (renvoyer une valeur d'un tableau en fonction d'une valeur se trouvant dans la première ligne) |
| RECHERCHEVStatistiques : | (renvoyer une valeur d'un tableau en fonction d'une valeur se trouvant dans la première colonne) |
(renvoyer la plus grande valeur)
(renvoyer la plus petite valeur)
| MOYENNE | (effectuer la moyenne d'une série de valeurs) |
| NB | (calculer le nombre de cellules contenant des nombres) |
|
(calculer le nombre de cellules contenant une valeur déterminée) | |
| (calculer le nombre de cellules vides) | |
| NBVAL | (calculer le nombre de cellules non vides) |
| PREVISION | (extrapoler à l'aide de données connues) |
| RANGTexte : | (donner un classement à une valeur en fonction d'une série de valeurs) |
| CONCATENER | (assembler des valeurs, les unes après les autres) |
| DROITE | (extraire des caractères en partant de la droite) |
| GAUCHE | (extraire des caractères en partant de la gauche) |
| MAJUSCULE | (convertir en majuscules) |
| MINUSCULE | (convertir en minuscules) |
| NOMPROPRE | (convertir la première lettre de chaque mot en majuscules et les autres lettres en minuscules) |
| STXT | (extraire des caractères d'une chaîne) |
Fonction Excel : AUJOURDHUI
’ PX1PH#BP0 ! $ "
La date du jour
% PX1PH#BP0!$ & 2 ! 7 $
9 2 9
| =votre_date-AUJOURDHUI() |
9
2 2 2 9
A 2 2 V
2 2 " *
Fonction Excel : SI
’ <0 7
9
* % 3 & =( ‘ !* (; $ = ‘
2 "(( "(=
3 "(( ! $ % & 5
# . (;
< 7
9
Fonction Excel : SI (imbriqués)
’ <0 > 2 " 7
6 > 7
0 7 <0 ! > $
6 > 7 . (; <
" % _ _ & <0 ! $
P > % & -
6 6 . ;O <
) 2 9
0 ! $ .
. Fonction Excel : ARRONDI
’ HH16#0 ^
< HH16#0
• Dans « Nombre », entrez le nom de la cellule à arrondir.
• Dans « No_chiffres », entrez le nombre de décimales souhaitées.
" 1N
’
3
Fonction Excel :
’ HH16#0 06) 2
< HH16#0 06)
• Dans « Nombre », entrez le nom de la cellule à arrondir.
• Dans « No_chiffres », entrez le nombre de décimales souhaitées.
" 1N
’
3
Fonction Excel :
’ HH16#0 <P3 2
< HH16#0 <P3
• Dans « Nombre », entrez le nom de la cellule à arrondir.
• Dans « No_chiffres », entrez le nombre de décimales souhaitées.
" 1N
’
3
Fonction Excel : SOMME
" <1AA 2 " V % < &
P 2
< 5 5
’
Fonction Excel :
’ <1AA <0 7
9
6 !
$ 3 "L <1AA <0
• Dans la case « Critère », entrez le critère "M".
• Dans la case « Plage », entrez la plage de données dans laquelle le critère sera vérifié.
• Dans la case « Somme_plage » entrez la plage de données qui sera utilisée pour l’obtention de la somme.
" 1N
# ( F 7 KAK
< "(
"F
9 ( 7 "( =
7 "=
6 2
6 > ? 7 KAK KA K
9
Fonction Excel : RECHERCHEH
’ H "B H"B B 7 2
>
3 9
6 .
0 . <
• Dans « Valeur_cherchée », nous entrons B6 qui correspond à Séverine.
• Dans « Tableau », nous définissons une plage de cellule qui contient le tableau.
• Dans « No_index_lig », nous devons entrez le n° de la ligne qui contient les données à afficher (ici les âges). Le n° de ligne est celui du tableau sélectionné.
• « Valeur_proche » est facultatif, mais permet soit de chercher la valeur exacte de « Valeur_cherchée » (en entrant : FAUX), soit de trouver la valeur la plus proche de « Valeur_cherchée » (en entrant : VRAI ou en laissant vide).
1 2 . <
A G; .
Fonction Excel : RECHERCHEV
’ H "B H"B 7 2
>
3 9
6
0 F
• Dans « Valeur_cherchée », nous entrons E2 qui correspond à 8.
• Dans « Table_matrice », nous définissons une plage de cellule qui contient le tableau.
• Dans « No_index_col », nous devons entrez le n° de la colonne qui contient les données à afficher (ici les prénoms). Le n° de colonne est celui du tableau sélectionné.
• « Valeur_proche » est facultatif, mais permet soit de chercher la valeur exacte de « Valeur_cherchée » (en entrant : FAUX), soit de trouver la valeur la plus proche de « Valeur_cherchée » (en entrant : VRAI ou en laissant vide).
1 2 F
A =
| Fonction Excel : MAX | |||
| ’ A ^ 1epossibilité : | 2 | ||
| < |
A ^ | % 6 2 ( & | 1N |
’
2epossibilité :
< A ^ ! O$
1N
’
’ = 2 2 Fonction Excel : MIN
’ A06 2
1epossibilité :
< A06 % 6 2 ( & 1N
’
2epossibilité :
< A06 ! O$
1N
’
Fonction Excel : MOYENNE
| ’ A1M 66 1epossibilité : | 7 5 | 2 | ||
| < | A1M 66 | % 6 | 2 ( & | 1N |
’ 5
2epossibilité :
< A1M 66 ! O$
1N
’ 5
’ = 2 2 Fonction Excel :
’ 6G <0 2 7
< 6G <0 7
# 2 % & 9
" 1N
’ 2 % & 9
Fonction Excel : NBVAL
’ 6G ’ 2
< 6G ’ 4 4
2
O
’ 2 9
Fonction Excel : PREVISION
’ 3H 0<016
3 9
6 =OO: =OOL
• Dans « X », nous indiquons 2005 car nous recherchons le chiffre d’affaire de cette année-là.
• Dans « Y_connus », entrez la plage de données contenant les chiffres d’affaires.
• Dans « X_connus », entrez la plage de données contenant les années.
7 2 ! $
Fonction Excel : RANG
’ H 6W
P 9
6 2 (=
< H 6W
• Indiquez dans « Nombre » le nombre à classer.
• Indiquez dans « Référence » la plage de cellule contenant tous les scores réalisés (pensez aux $ pour faciliter la recopie).
• N’indiquez rien (ou 0) dans « Ordre » pour un ordre décroissant, entrez une valeur différente de 0 pour un ordre croissant.
" 1N
7
H 9 > 0 ; ’ Y
2 Fonction Excel : NOMPROPRE
’ 61A3H13H 7
< 61A3H13H
’ 9
Fonction Excel : DROITE
W . #H10+ 2 7
- 7
3 9
’ G(
6 7
3 #H10+ 9
• Indiquez dans « Texte » la cellule contenant la chaîne de caractère.
• Dans « No_car » indiquez le nombre de caractères à extraire depuis la droite (ici, 2). Cliquez sur OK.
’ 7 9
Fonction Excel : GAUCHE
W . W P"B 2 7
- 7
3 9
’ G(
6 D
3 W P"B 9
• Indiquez dans « Texte » la cellule contenant la chaîne de caractère.
• Dans « No_car » indiquez le nombre de caractères à extraire (ici, 4). Cliquez sur OK.
’ D 7 9
Fonction Excel : MAJUSCULE
’ A XP<"P’ - 7
< A XP<"P’
’ 9
Fonction Excel : MINUSCULE
La fonction MINUSCULE transforme une chaîne de caractères en minuscules.
Sélectionnez la fonction MINUSCULE puis entrez simplement la cellule du texte à convertir en minuscules.
Le résultat :
Fonction Excel : EQUIV
’ TP0
• Dans « Valeur_cherchée » entrez la valeur dont vous souhaitez obtenir la position.
• Dans « Tableau_recherche » entrez la plage de cellules dans laquelle la fonction recherchera la position de « Valeur_cherchée ».
• Dans « Type » entrez 0 pour trouver la valeur exacte, 1 pour la valeur la plus élevée qui est inférieure ou égale à « Valeur_cherchée », -1 pour la plus petite valeur qui est supérieure ou égale à « Valeur_cherchée ».
’ 9
Fonction Excel : INDEX
’ 06# ^ 2
• Indiquez dans « Matrice » la plage de cellules dans laquelle la fonction effectuera la recherche.
• Dans « No_lig » indiquez le n° de la ligne et dans « No_col » le n° de la colonne. Ces numéros doivent correspondre aux colonnes et lignes de la plage de cellules « Matrice ».
’ 9
