MICROSOFT Excel 2003 User Manual [fr]

IIII

Auteur : Nathalie JAILLIARD

2/56 EXCEL PERFECTIONNEMENT 13/03/11

I. MISE EN FORME ELABOREE DU DOCUMENT ...................................................................................... 3

a. Insertion de cellules, colonne, insertion de ligne ........................................................................................ 3

b. Notion de fusion des colonnes .................................................................................................................... 5

c. Format des nombres .................................................................................................................................... 5

d. Format personnalisé .................................................................................................................................... 5

e. Format mise en forme conditionnelle :........................................................................................................ 7

f. Validation des données ............................................................................................................................... 7

CREATION DE LA LISTE .................................................................................................................... 8

MISE EN FORMAT DE VALIDATION : ............................................................................................. 8

II. FONCTIONS ................................................................................................................................................ 12

a. Références dans les Cellules ..................................................................................................................... 12

La référence A1 : ............................................................................................................................... 12

La référence L1C1 : .......................................................................................................................... 12

On parle aussi de références relative , absolue ou mixte . ................................................................. 12

b. Définition d'une fonction........................................................................................................................... 13

Fonction somme ................................................................................................................................ 14

Fonction MOYENNE (x;y...) : .......................................................................................................... 15

Fonction AUJOURD'HUI (): ............................................................................................................ 15

Fonction NBVAL(valeur1;valeur2;...) : ............................................................................................ 15

FonctionNB.VIDE(plage) : ............................................................................................................... 16

Fonction SI(X>Y ; « objectif atteint » ; »retard sur l’objectif ») : ................................................... 16

Fonction SOMME.SI (plage ;critère ;somme_plage) : ..................................................................... 17

Fonction CELLULE(type info, référence) : ...................................................................................... 19

Fonction JOUR360(date_début,date_fin,unité) : .............................................................................. 20

c. Calculer en nommant des plages ............................................................................................................... 21

III. LES LIAISONS ............................................................................................................................................ 23

a. Rappel : ......................................................................................................................................................... 23

b. Les liaisons : ................................................................................................................................................. 23

Références 3D ....................................................................................................................................... 24

Consolidation ........................................................................................................................................ 25

Consolidation automatique .................................................................................................................... 26

Création de Plan .................................................................................................................................... 28

a. Création ......................................................................................................................................................... 29

b. vocabulaire .................................................................................................................................................... 34

c. Modification .................................................................................................................................................. 35

Bouton assistant du tableau croisé dynamique ............................................................................ 35

"options" :.............................................................................................................................................. 35

CHAMPS: ............................................................................................................................................. 37

Mise en forme automatique : ................................................................................................................. 38

d. Création d'un tableau croisé dynamique à partir de plusieurs feuilles : ........................................................ 39

VI. FICHIERS ............................................................................................................................................. 45

a. Qu’est-ce qu’une base de données ? ...................................................................................................... 45

c. Modification dans la base de données : ................................................................................................. 46

e. Savoir faire un remplacement ................................................................................................................ 49

f. Savoir faire un tri .................................................................................................................................... 50

g. Faire un filtre simple ............................................................................................................................... 51

i. calcul simple dans la base de données : ................................................................................................. 54

j. calcul plus compliqués dans la base de données : fonctions Base de données .................................... 54

VII. VALEUR CIBLE .................................................................................................................................. 56

ère

étape : .............................................................................................................................................. 39

ème

étape : ............................................................................................................................................. 40

ème

étape ............................................................................................................................................... 40

ème

étape ............................................................................................................................................... 41

Création d'un élément calculé dans un rapport de tableau ou graphique croisé dynamique .................. 43

Création de la liste ............................................................................................................................... 45

Savoir faire une recherche dans la base de données......................................................................... 48

Recherche élaborée : filtre élaboré .................................................................................................... 52

3/56 EXCEL PERFECTIONNEMENT 13/03/11

MISE EN FORME ELABOREE DU DOCUMENT

a. Insertion de cellules, colonne, insertion de ligne

Voici notre exemple : un tableau de commande de fournitures :

Pour ajouter les colonnes Prix HT , TVA Se positionner à l’endroit où l’on veut insérer et sélectionner la colonne en cliquant sur la lettre correspondante (ici le D)

Et décaler la souris vers la droite en restant appuyé sur le click gauche le nombre de colonne à insérer (ici 2)

Choisir INSERTION dans le menu puis COLONNES ou CTRL+  ou cliquer droit et choisir insertion Si une boite de dialogue apparaît, choisir décaler vers la droite, ou bas, ligne entière ou colonne entière Validez par OK

4/56 EXCEL PERFECTIONNEMENT 13/03/11

POUR INSERER UNE LIGNE : Sélectionner 1 ou plusieurs lignes Choisissez INSERTION LIGNES OU appuyez sur CTRL+  (touche CTRL et touche plus)

POUR INSERER DES CELLULES : Sélectionnez les cellules , par exemple de A3à F3 : (cliquez gauche sur la cellule A3, restez appuyé et glissez la souris jusque la cellule F3, lâchez, mais laissez la souris sur cette sélection, vous devez obtenir ceci :

Puis cliquez droit pour obtenir « insérer » Ou dans le menu choisir « insertion » puis cellules Ou appuyez sur CTRL+ 

Cliquez sur le choix qui vous intéresse : dans notre cas « décaler les cellules vers le bas » Les cellules ou les lignes ou les colonnes s’insèrent avant la sélection.

SUPPRESSION DE CELLULES OU DE LIGNES/COLONNES : Pour supprimer ce sera identique, mais il faudra choisir supprimer, Il faudra faire la sélection de ce que l’on veut supprimer, puis en cliquant droit sur la souris ou en choisissant dans le menu « supprimer »,

Ou en appuyant sur CTRL+  (touche CTRL et touche moins) on obtient cette boite de dialogue. Il suffira de choisir et de faire OK.

5/56 EXCEL PERFECTIONNEMENT 13/03/11

b. Notion de fusion des colonnes

Pour centrer le titre : « Commande des fournitures » dans notre exemple, il faut :

- saisir dans la cellule le plus à gauche ce titre

- Sélectionner la zone par rapport à laquelle doit se faire le centrage (les cellules concernées)

(ATTENTION les autres cellules doivent être vides)

- Puis cliquez sur le bouton

- OU FORMAT Cellule PUIS ALIGNEMENT cochez « fusionnez les cellules » et OK

Les 3 cellules sélectionnées se transforment en 1 cellule. (Attention à ne pas utiliser si l’on veut effectuer du tri et/ou des calculs)

On obtient ceci :

On peut centrer un titre, sans fusionner les cellules, il faudra alors choisir dans le menu :

FORMAT CELLULE puis ALIGNEMENT, puis « centrer sur plusieurs colonnes » et OK

c. Format des nombres

Lorsqu'on a un tableau avec des nombres devant servir à des calculs, il faut mettre en forme les cellules sinon par défaut c'est "standard". Si on ne le fait pas, lors de calculs, il peut se produire des erreurs. Dans notre exemple, on formatera les colonnes de prix en "nombre" avec 2 décimales et la colonne quantité sans décimale. Faire "Format" "cellules" "nombre" et choisir le nombre de décimales

PUIS

On peut aussi choisir "monétaire" à la place de nombre

d. Format personnalisé

On peut aussi personnaliser son format : On ira dans « FORMAT » « cellules » « Nombres »

6/56 EXCEL PERFECTIONNEMENT 13/03/11

ère

ème

Voici la table ci-dessous qui permet de comprendre le code utilisé par Excel pour personnaliser son format

Table :

Code Signification Exemple # Représente un chiffre 24 s’écrira en format ## : 24 0 Représente un chiffre remplacé, s’il fait défaut, par un 0 24 s’écrira en format 000 : 024 ; Sépare la représentation des nombres positifs, négatifs et

nuls

La 1

ème

=nul

= positifs , 2

=négatifs et

[vert] Les crochets entourent la couleur d’affichage ? Représente un chiffre du diviseur ou du dividende dans un

fraction J ou JJ Représente le jour de la semaine en chiffre 1, 2, 3 …. JJJ Représente le jour de la semaine abrégé en 3 lettres LUN, MAR, MER … M ou

Représente le mois en chiffre 01, 02, 03 … MM MMM Représente le mois en abrégé en 3 lettres Jan, fév, mar, avr … MMMM Représente le mois entier Janvier, février… AA Représente l’année sur 2 chiffres 01, 02, AAAA Représente l’année sur 4 chiffres 2001, 2002 …

Admettons que le format monétaire en euro n'existe pas. Nous voulons que le nombre 10.50 saisi devienne à l'affichage : 10,50 €

Il faudra alors saisir dans "type" ceci :# ##0,00\ € selon les codes de la table ci-dessus, on obtiendra alors 10,50 €

De la même manière pour les dates, il est préférable de saisir les dates en mettant un format "date". On peut s’amuser à créer un format spécifique ou "personnalisé" dans lequel on impose sa manière d'afficher, par exemple : Mettre Janvier, février …. Dans le tableau sous forme de date : 01/01/2006 pour janvier, 01/02/2006 pour février…etc Quand on sélectionnera ces cellules en format personnalisé, (mettre en format « mmmm ») , les dates s’afficheront Janvier en toutes lettres :

devient .

7/56 EXCEL PERFECTIONNEMENT 13/03/11

e. Format mise en forme conditionnelle :

Ce menu permet de conditionner la mise en forme. On veut faire ressortir certaines cellules en fonction de leur contenu. Imaginons, dans notre exemple, que l'on veut faire ressortir les montants supérieurs à 750 000 € en faisant apparaître la cellule en couleur rose :

Sélectionnez les cellules du tableau

Cliquez sur "Menu" "FORMAT" "MISE EN FORME CONDITIONNELLE"

Dans la condition 1, vous voyez 3 zones de critères :

• La première : choisissez si vous allez comparer la valeur de la cellule ou plutôt

prendre en compte sa formule. Dans notre cas, ce sera la valeur résultat ("valeur de la cellule est")

• Choisir le critère : supérieur ou égal, ou bien égal, ...etc

• Choisir la valeur

Enfin, choisir comment doit apparaître les cellules correspondant à ces critères, ici de couleur rose. On clique sur "Format" "Motif" et choisir la couleur rose :

On obtient ceci :

f. Validation des données

Dans une base de données, il est très utile parfois de diriger la saisie pour éviter d’avoir des erreurs, et afin de permettre une recherche efficace.

8/56 EXCEL PERFECTIONNEMENT 13/03/11

Dans notre exemple, il ne faut pas d’erreur dans la liste des régions,

on va donc créer une liste pré-définie Il faut commencer par créer la liste, puis mettre le format de validation sur les cellules concernées:

1) CREATION DE LA LISTE

Qui doit se trouver obligatoirement dans la même feuille de calcul : saisir cette liste en colonne H, indiquez sur

ère

la 1

ligne REGIONS, et juste à la cellule en-dessous commencez à saisir les noms de régions comme suit :

2) MISE EN FORMAT DE VALIDATION :

Se placer sur la cellule qui doit être formatée, ici sur B2 Cliquez dans le menu déroulant sur « Donnees » « validation »

On obtient cette boite de dialogue :

9/56 EXCEL PERFECTIONNEMENT 13/03/11

a) OPTIONS

Dans « Critères de validation » on choisit « liste » et dans "source" on inscrit la zone de sélection où se trouve

les régions ou on va sélectionner cette zone en cliquant sur le cigare

On obtient alors ceci

Il suffit de sélectionner à l'aide de la souris la liste créée auparavant :

puis cliquez à nouveau sur le cigare : et vous obtenez ceci :

b) Messages de saisie

10/56 EXCEL PERFECTIONNEMENT 13/03/11

on peut choisir de mettre un message qui apparaîtra dés qu’on passe sur la cellule comme celui-ci, allez dans l'onglet "message de saisie"

On peut aussi ne rien mettre. Dans notre exemple, nous voulons aider la personne à saisir les régions, on inscrira le message suivant : "Veuillez choisir une région dans la liste suivante" avec un titre :

voilà ce que donne le message quand la cellule est sélectionnée :

c) Alerte d’erreur :

On a aussi la possibilité de mettre un message qui apparaîtra en cas de saisie erronée dans l'onglet "Alerte d'erreur

On met un titre, le message d'erreur , et on choisit dans "style" si on veut :

un arrêt de la saisie

un avertissement

une information:

"arrêt" :

11/56 EXCEL PERFECTIONNEMENT 13/03/11 la saisie est bloquée jusqu'à ce que la personne ait bien saisi le bon nom

On obtient ceci lorsque on saisir une erreur :

"avertissement" :

message d'alerte vous demandant si vous voulez continuer à saisir, si oui, il laisse la mauvaise saisie puis vous permet de passer à la cellule suivante, si non, vous pouvez corrigez, si annuler, vous revenez en arrière

"informations" :

message d'information et prend en compte la saisie même si elle est mauvaise

12/56 EXCEL PERFECTIONNEMENT 13/03/11

II.

FONCTIONS

a. Références dans les Cellules

Pour effectuer un calcul de base, on utilise les signes suivants : = , + , * , -

Excel se repère dans la feuille de calcul à l’aide de cellule qui, chacune ont une référence. 2 manières d’écrire les références B8 ou L(8)c selon si on a coché dans le menu « Outil » « option » onglet

« général » style de référence L1C1

1) La référence A1 :

Par défaut, Excel utilise le style de référence A1 qui étiquette les colonnes par des lettres (de A à IV, pour couvrir 256 colonnes) et les lignes par des numéros (de 1 à 65536). Dans notre exemple la cellule sélectionnée est D4

2) La référence L1C1 :

Vous pouvez aussi utiliser un style de référence dans lequel des lignes et des colonnes de la feuille de calcul sont numérotées. Le style L1C1 permet de calculer des positions de ligne et de colonne dans des macros. Dans ce cas les colonnes sont numérotées de 1 à 256 et les lignes de 1 à 65536 Dans notre exemple la cellule sélectionnée est L4C4

On parle aussi de références relative , absolue ou mixte

En fonction du calcul à effectuer, on pourra utiliser 3 types de référence :

référence relative : c'est-à-dire des références relatives à la position de la formule

exemple B8 correspond à la cellule de la colonne B et 8

ou L(8)C correspond à 8

si on a mis un nombre en B8 (exemple 5) et sur la cellule C9 on met la formule suivante =B8 il apparaîtra en référence L1C1 : = L(-1)C(-1)

Car on a décalé d'une ligne et d'une colonne (moins une ligne, moins une colonne)

Si on insère une ligne dans le calcul, ces références décalent d’autant

ème

ligne dans la colonne actuelle,

ème

ligne

13/56 EXCEL PERFECTIONNEMENT 13/03/11

On voit dans la ligne de commande =L(-2)C(-1) On a décalé de 2 lignes et le résultat est toujours exact, puisque dans les références relatives , Excel prend en compte le décalage (ce ne sera pas le cas dans la référence absolue)

Référence absolue : est à se servir quand on souhaite faire référence à des cellules situées toujours au même emplacement : $B$8 ou L8C1

Par exemple pour une paie, on prend toujours le salaire brut qui sera situé au même endroit

834.01 est toujours en F23, on indiquera alors dans la cellule de base des charges (en D27) la commande =$F$23

Pour mettre les $ automatiquement dans les références absolues , taper la formule Puis se positionner en modification de cellule (sélectionner la cellule ou partie de la cellule) puis appuyez sur F4

Validez par ou 

Référence mixte : combine les références relatives et absolue

Par exemple $B8 ou L8C(1) : on dit que la partie de la colonne est absolue, donc ne bouge pas, par contre la ligne peut évoluer.

b. Définition d'une fonction

Une fonction est une formule prédéfinie vous permettant d’exécuter des calculs plus ou moins complexes.

Il existe plus de 200 fonctions réparties en différentes catégories : mathématiques, trigonométriques, financières, statistiques, textes..

La fonction est composée d’un nom et d’un couple de parenthèses qui indiquera les cellules concernées :

= SOMME (…….)

Se placer dans la cellule devant recevoir la fonction (où le résultat doit apparaître), et saisissez = soit vous connaissez la fonction et la taper directement, soit vous vous aidez de l’assistant en cliquant en haut à gauche sous le menu sur la flèche et vous choisissez

14/56 EXCEL PERFECTIONNEMENT 13/03/11

1) Fonction somme

Cette fonction étant courante, elle possède un icône prévu sans mettre =

Si vous passez par l'icône

Vous pouvez alors sélectionner la zone à calculer s'il n'y a pas de nombre dans les cellules juxtaposées de la formule on a =SOMME()

il faudra alors sélectionner à l'aide de la souris la zone à calculer

sinon Excel affiche ceci :

Vous voyez que A6:A12 est surligné, vous pouvez changer la sélection en re-sélectionnant à l'aide de la souris, Sinon faire ENTER

Si vous passez par l'assistant :

Taper = Choisir la fonction SOMME Puis Suivez l’assistant.

La référence, sera dans Nombre 1, et si vous avez tout sélectionné, pas besoin de remplir nombre 2. On a la possibilité d’ajouter plusieurs zones de références dans la fonction Somme. Dés qu’on aura rempli la zone 1 et 2, il nous proposera une 3

Important : dans la zone de référence , si on veut dire :

nombre1 + nombre2 + nombre3 : alors on mettra deux point (:) entre le premier et le dernier nombre (cela signifie jusqu'à)

nombre1 : nombre3

nombre1 + nombre 3 : on mettra un point-virgule (;) (cela signifie et)

nombre1 ; nombre3

ème

zone, et ainsi de suite.

15/56 EXCEL PERFECTIONNEMENT 13/03/11

2) Fonction MOYENNE (x;y...) :

calcule la moyenne : somme des arguments divisée par le nombre des arguments

3) Fonction AUJOURD'HUI ():

renvoie la date du jour

4) Fonction NBVAL(valeur1;valeur2;...) :

Compte le nombre de cellules qui ne sont pas vides et les valeurs comprises dans la liste des arguments. Utilisez NBVAL pour compter le nombre de cellules contenant des données dans une plage ou une matrice

NBVAL(A1:A7)

égale 6

16/56 EXCEL PERFECTIONNEMENT 13/03/11

5) FonctionNB.VIDE(plage) :

compte combien de cellules sont vides

Exemple

Supposons que, dans la feuille de calcul précédente, la cellule B3 contienne la formule suivante :

SI(C3<30;"";C3),

qui renvoie " " (texte vide).

NB.VIDE(B2:C5)

égale 2

6) Fonction SI(X>Y ; « objectif atteint » ; »retard sur l’objectif ») :

Fonction SI(X>Y ; « objectif atteint » ; »retard sur l’objectif ») :

si la valeur X est supérieure à la valeur Y alors Objectif atteint, sinon retard

Dans notre exemple ci-dessous nous ajoutons une colonne conditions

Dans la première ligne; inscrire = puis sélectionnez la fonction SI L'assistant ci-dessous apparaît, il ne vous reste plus qu'à remplir :

faites ENTER, puis recopiez cette cellule sur celles du dessous, vous obtenez ceci :

17/56 EXCEL PERFECTIONNEMENT 13/03/11

7) Fonction SOMME.SI (plage ;critère ;somme_plage) :

Additionne des cellules spécifiées si elles répondent à un critère donné.

plage représente la plage de cellules qui va être comparée, utilisée pour faire notre critère. critère représente le critère, sous forme de nombre, d'expression ou de texte, définissant les

cellules à additionner. Par exemple, l'argument critère peut être exprimé sous une des formes suivantes : « 32 », « >32 », « pommes ». Pas de formules imbriquées.

somme_plage représente les cellules à additionner. Si l'argument somme_plage est omis, ce

sont les cellules de l'argument plage qui sont additionnées.

=SOMME.SI(A3:A16;"stylo *";F3:F16) : on veut savoir le total de tous les stylos , on

mettra « stylo » avec un espace derrière et un caractère générique * afin que excel prenne en compte tous les mots commençant par stylo.

Mettre comme critère : stylo*

+ 39 hidden pages