Cours Excel

Support de cours EXCEL 2000

1

CRC2

Support de Cours

EXCEL 2000 Janvier 2003


TABLE DES MATIERES FILTRER UNE BASE DE DONNEES 1. FILTRE AUTOMATIQUE 1.1 Réaliser un filtre simple 4 1.2 Réaliser un filtre sur plusieurs colonnes 4 1.3 Afficher tous les enregistrements 5 1.4 Réaliser un filtre personnalisé 5 1.5 Supprimer le filtre automatique 6 2. FILTRE ELABORE 2.1 Créer la zone de critères 7 2.2 Exemple de condition ET 7 2.3 Exemple de condition OU 8 2.4 Exemple de condition ET OU 9 3. LES FONCTIONS BASE DE DONNEES 3.1 Créer la zone de critères 10 3.2 Exemple de base de données 11 LA MISE EN PAGE SOUS EXCEL 1. LA MISE EN PAGE 12 2. MARGES 12 3. EN TETE / PIED DE PAGE 13 4. L’ONGLET FEUILLE 14 LES CALCULS SOUS EXCEL 1. LES FORMULES DE CALCULS AVEC OPERATEURS

1.1 Les opérateurs 16 1.2 Ordre de calcul des opérateurs 16 2. LES FONCTIONS DE CALCUL 2.1 Le bouton « somme automatique » 16 2.2 L’assistant fonction 17 2.3 Modification d’une formule 19 2.4 Calcul automatique 21 3. LES REFERENCES RELATIVES, ET ABSOLUES 3.1 Avantage de cette fonctionnalité 24 3.2 Présentation concrète de cette fonction 25 4. LES NOMS 4.1 Intérêt des noms 30 4.2 Attribution d’un nom 30 4.3 Utilisation des noms dans un calcul 32

2


3

UTILISATIONS DES FONCTIONS 33 LA MISE EN FORME D’UN TABLEAU 1. AJUSTER LATAILLE DES COLONNES 37 2. MODIFIER LA TAILLE, LA POLICE ET LE STYLE DES CARACTERES 38 3. FORMATS DES NOMBRES 39 4. BORDURES 42 5. COLORATIONS DES CELLULES 44 FORMAT PERSONNALISE 1. EXPLICATION DES PRINCIPAUX FORMATS 46 2. SUPPRESSION D’UN FORMAT PERSONNALISE 48 LES GRAPHIQUES SOUS EXCEL 1. INTRODUCTION SUR LES GRAPHIQUES 50 2. LES DIFFERENTS TYPES DE GRAPHIQUES 60 CREATION D’UNE LISTE 1. DEFINITION 70 2. CREER UNE LISTE DANS EXCEL 70 3. TRIER LA LISTE 76 L’IMPRESSION SOUS EXCEL 1. DEFINIR UNE ZONE D’IMPRESSION SOUS EXCEL 80 2. L’APERCU AVANT L’IMPRESSION SOUS EXCEL 82


F I L T R E R U N E B A S E D E D O N N E E S

1. FILTRE AUTOMATIQUE Le filtre automatique constitue le moyen le plus rapide de sélectionner uniquement les éléments

que vous souhaitez visualiser dans une liste.

1.1 Réaliser un filtre simple La méthode la plus simple est le

filtre automatique, Données / Filtre / Filtre automatique. De

petites flèches s’affichent en bas

à droite des cellules de la ligne

de titre, comme dans l’exemple

de la figure suivante.

En cliquant sur une de ces

flèches, vous constaterez que la

liste des entrées de la colonne y

est disponible. Sélectionnez une

de ces entrées. A l’écran ne

sont maintenant visibles que les lignes contenant cette entrée. Pour vous indiquer que vous

avez appliqué un filtre, la flèche devient bleue ainsi que les numéros de lignes. Les lignes qui

ne correspondent pas à ce fournisseur sont masquées. Vous pouvez appliquer un autre filtre

par-dessus le précédent en cliquant sur la flèche d’une autre colonne.

Vous pouvez également choisir dans la liste des entrées, Vides et Non vides, 10 premiers et

Personnalisé.

10 premiers ouvrent la boîte de dialogue suivante :

− Haut affiche les premiers, Bas affiche les derniers.

− Sélectionnez le nombre que vous souhaitez afficher.

− Sélectionnez Éléments ou Pourcentage afin d’afficher le nombre d’éléments ou le

pourcentage dans la liste.

1.2 Réaliser un filtre sur plusieurs colonnes Pour rechercher des valeurs spécifiques dans les lignes d’une liste à l’aide d’un ou de deux

critères de comparaison pour la même colonne, pointez sur Filtrer dans le menu Données,

cliquez sur Filtre automatique, sur la flèche située dans la colonne qui contient les données à

comparer, puis sur Personnalisé. 1. Pour effectuer la comparaison en fonction d’un critère, dans la première zone située sous

Afficher les lignes dans lesquelles, cliquez sur l’opérateur de comparaison à utiliser,

puis tapez la valeur du critère de comparaison dans la zone située à droite de l’opérateur

de comparaison.

2. Pour afficher des lignes qui remplissent deux conditions, tapez la valeur et l’opérateur de

comparaison souhaités, puis cliquez sur l’option Et. Dans les zones correspondant au

4


second opérateur de comparaison et à la seconde valeur, tapez l’opérateur et la valeur

souhaités.

3. Pour afficher des lignes qui remplissent uniquement l’une des deux conditions, tapez la

valeur et l’opérateur de comparaison souhaités, puis cliquez sur l’option Ou. Dans les

zones correspondant au second opérateur de comparaison et à la seconde valeur, tapez

l’opérateur et la valeur souhaités.

Options de filtre automatique

Pour Cliquez sur

Afficher toutes les lignes (Tous)

Afficher toutes les lignes conformes à la limite supérieure ou inférieure spécifiée, soit par élément, soit par pourcentage (par exemple, les 10 meilleurs pourcentages de ventes)

(10 premiers)

Appliquer deux valeurs de critère dans la colonne en cours ou utiliser des opérateurs de comparaison autres que ET (l’opérateur par défaut) (Personnalisé)

Afficher uniquement les lignes qui contiennent une cellule vide dans la colonne (Vides)

Afficher uniquement les lignes qui contiennent une valeur dans la colonne (Non vides)

Remarque : Les options (Vides) et (Non vides) ne sont disponibles que si la colonne à filtrer

contient une cellule vide.

1.3 Afficher les enregistrements

Pour afficher de nouveau tous les enregistrements, Données/Filtre/Afficher tout.

Dans le cas où vous effectuez des filtres automatiques régulièrement, il est conseillé d’ajouter

les commande Filtre automatique et Afficher tout (situé dans la catégorie Données) sur une

barre d’outils et créer une image à Afficher tout

1.4 Réaliser un filtre personnalisé Personnalisé ouvre la boîte de dialogue suivante :

Sélectionnez les opérateurs dans les

zones situées à gauche, et tapez ou

sélectionnez les valeurs dans les

zones situées à droite.

5


6

Cliquez sur Et pour combiner deux critères (seules les lignes répondant aux 2 critères seront

affichées). Cliquez sur Ou pour utiliser l’un des deux critères (une ligne sera affichée si elle

répond à l’un des 2 critères).

1.5 Supprimer le filtre automatique

• Pour supprimer un filtre d’une colonne d’une liste, cliquez sur la flèche située à droite de

la colonne, puis cliquez sur (Tous).

• Pour supprimer les filtres appliqués à toutes les colonnes de la liste, pointez dans le menu

Données sur Filtre, puis cliquez sur Afficher tout.

• Pour supprimer les flèches de filtre d’une liste, pointez dans le menu Données sur Filtre,

puis sur Filtre automatique.

.


2. FILTRE ELABORE CONSEIL : savoir utiliser les filtres automatiques pour lire cette partie. BUT : filtrer les informations d’une base de données suivant un ou plusieurs critères pour afficher ou exporter (sur une autre feuille de calcul) le résultat voulu. 2.1 Créer la zone de critères Il faut prévoir un espace vide (lignes et colonnes) à côté de la liste de données pour commencer. Dans une ligne vide, il faut taper les noms des champs en faisant attention d’utiliser les mêmes mots et styles. Maintenant dans les lignes du dessous, il faut indiquer les combinaisons qui sont les suivantes :

OU : les critères sont saisis sur plusieurs lignes ET : les critères sont saisis sur plusieurs colonnes OU & ET : les critères sont saisis sur plusieurs lignes et colonnes

2.2 Exemple de condition OU On veut afficher 3 critères d’une base de données soit : 18, 19,22 ans dans l’exemple ci-dessous :

7

Base de données entrée des critères 18 ou 19 ou 22. Ensuite, il suffit d’aller cliquer sur « Données-Filtres-Filtre élaboré… »

Ici on filtre la liste sur place… L’action de copier vers un autre emplacement correspond à insérer le résultat sur une autre feuille ou un autre document.


Le champ « plages » correspond à la base de données dans son intégralité, soit pour cet exemple les cellules : A1 : B7. Le champ « Zone de critères : » correspond à l’entrée des critères dans son intégralité, soit pour cet exemple les cellules : G1 : G4.

Après validation par « ok », la base de données est filtrée pour n’afficher que les critères voulus :

Résultat dans la base de données. (affichage des numéros de lignes concernées en bleu). Pour revenir à l’affichage complet de la base de donnée, il suffit de cliquer sur « Données –Filtre –Afficher tout ».

2.3 Exemple de condition ET On veut afficher les personnes de la base de données ayant un age compris entre 20 et 30 ans.

Base de données entrée des critères : >20 et <30.

8


Ensuite, le processus reste le même qu’auparavant, il suffit de choisir les plages et la zone de critères avec la souris.

Il faut cliquer sur ce bouton pour pouvoir sélectionner un champ avec la souris. Le champ « plages » correspond à la base de données dans son intégralité, soit pour cet exemple les cellules : A1 : B7. Le champ « Zone de critères : » correspond à l’entrée des critères dans son intégralité, soit pour cet exemple les cellules : G1 : H2.

Affichage du résultat. 2.4 Exemple de condition ET OU Nous utilisons toujours la même base de données et nous aimerions afficher les personnes

ayant 18, 19 ou 22 ans et les personnes ayant un age différent de 18 ans.

OPERATEUR DE COMPARAISON

A noter que l’on peut utiliser les opérateurs de comparaison suivants : = : égal < : inférieur > : supérieur <= : inférieur ou égal >= : supérieur ou égal <> : différent de.

La procédure d’affichage de résultats reste la même :

9


3. LES FONCTIONS BASE DE DONNEES

Excel fournit 12 fonctions de feuille de calcul pour analyser des données stockées dans des listes

ou des bases de données. Chacune de ces fonctions, regroupées sous l’appellation fonctions de

base de données, utilise trois arguments : base de données, champ et critères. Ces arguments

font référence aux plages de feuille de calcul utilisées par la fonction.

3.1 Créer la zone de critères

• Syntaxe : Bd fonction (base de données ; champ ; critères)

• Base de données : représente la plage de cellules constituant la liste ou la base de

données.

• Champ : indique la colonne utilisée dans la fonction. Les colonnes de données de la liste

doivent comporter une étiquette d’identification sur la première ligne. Vous pouvez

spécifier l’argument champ sous forme de texte en mettant l’étiquette de colonne entre

guillemets, par exemple, « Age » ou « Rendement » dans la liste d’exemples ci-dessous,

ou sous forme d’un nombre représentant la position de la colonne dans la liste : 1 pour la

première colonne (Arbre dans l’exemple ci-dessous), 2 pour la deuxième colonne

(Hauteur) et ainsi de suite.

• Critères : est une référence à une plage de cellules qui spécifie les conditions de la

fonction. Celle-ci renvoie les informations de la liste correspondant aux conditions

spécifiées dans la plage de critères. Cette plage comprend une copie de l’étiquette de

colonne de la liste pour la colonne dont vous voulez que la fonction effectue la synthèse.

Les références de critères peuvent être tapées sous forme d’une plage de cellules, telle

que A1 :F2 dans l’exemple de base de données ci-dessous, ou sous forme d’un nom

représentant la plage, « Critères », par exemple.

ExemplesL’illustration suivante montre une base de données concernant un petit verger. Chaque

enregistrement contient des informations relatives à un arbre. La plage A5 :E11 est nommée

Base de données et la plage A1 :F3, Critères.

10


11

3.2 Exemple de fonctions de base de données

• BDNB (Base de données;"Age";A1:F2) égale 1. Cette fonction recherche dans les

enregistrements les pommiers qui ont une taille comprise entre 10 et 16, puis compte

dans les enregistrements trouvés le nombre de champs Age contenant des nombres.

• BDNBVAL (Base de données;"Bénéfice";A1:F2) égale 1. Cette fonction recherche dans

les enregistrements les pommiers qui ont une taille comprise entre 10 et 16, puis compte

dans les enregistrements trouvés le nombre de champs Bénéfice qui ne sont pas vides.

• BDMAX (Base de données;"Bénéfice";A1:A3) égale 1050,00 F, ce qui correspond au

bénéfice maximal généré par les pommiers et les poiriers.

• BDMIN (Base de données;"Bénéfice";A1:B2) égale 750,00 F, ce qui correspond au

bénéfice minimal généré par les pommiers dont la taille est supérieure à 10.

• BDSOMME (Base de données;"Bénéfice";A1:A2) égale 2250,00 F, ce qui correspond au

bénéfice total généré par les pommiers.

• BDSOMME (Base de données;"Bénéfice";A1:F2) égale 750,00 F, ce qui correspond au

bénéfice total généré par les pommiers dont la taille est comprise entre 10 et 16.

• BDPRODUIT (Base de données;"Rendement";A1:F2) égale 10, ce qui correspond au

produit des rendements des pommiers dont la taille est comprise entre 10 et 16.

• BDMOYENNE (Base de données;"Rendement";A1:B2) égale 12, ce qui correspond au

rendement moyen des pommiers dont la taille est supérieure à 10.

BDMOYENNE (Base de données;3;Base de données) égale 13, ce qui correspond à l'âge moyen de tous les arbres figurant dans la base de données


LA MISE EN PAGE SOUS EXCEL

Les options de la mise en page permettent d’améliorer la présentation du classeur. On peut changer les marges des feuilles, les affichages de titres et plusieurs autres options qui seront décrites plus bas.

1. MISE EN PAGE

Les principales options pour la mise en page se trouvent dans Fichier/mise en page… On obtient la fenêtre suivante :

L’onglet Page nous permet de choisir l’orientation de la pages. On peut la réduire ou l’agrandir ou l’ajuster pour que les pages Excel tiennent sur un nombre défini de pages papier en largeur et en hauteur. C’est aussi sous cet onglet qu’on sélectionne le format du papier et la qualité d’impression en pixel par pouces (ppp). On peut définir les propriétés de numérotation de pages automatiquement (débutera a 1) ou en choisissant le numéro de la première page pour ajouter des feuilles a une impression déjà effectuée. Le bouton Imprimer nous renvoi vers le menu du même nom, ainsi qu’Aperçu, et Options vers les propriétés de l’imprimante.

2. LES MARGES

L’onglet Marges sert à définir celles-ci. On retrouve à nouveau les 3 boutons (Imprimer, Aperçu et Options) de l’onglet page qui ont ici les mêmes fonctions de redirection. On peut définir le centrage de la feuille Excel sur la page à imprimer de deux façons : Horizontalement ou verticalement

12


3. EN TETE / PIED DE PAGE

C’est dans l’onglet en-tête/pied de page qu’on entre les informations qui doivent apparaître sur le haut et sur le bas de toutes les feuilles Excel du classeur.

Pour personnaliser ces emplacements en rajoutant par exemple la date ou l’heure de la création du document, on sélectionne En-tête personnalisé ou Pied de page personnalisé.

13


4. L’ONGLET FEUILLE Le dernier onglet Feuille :

Zone d’impression :

Nous permet de définir la partie du document à imprimer en la sélectionnant après un clic sur

l’icône .

Titres à imprimer :

Les lignes ou les colonnes contenant le titre pour qu’ils apparaissent automatiquement sur chaque page. Impression : On définit ici l’apparence du document à l’impression : présence du quadrillage, impression en noir et blanc, qualité de l’impression (brouillon), présence des en-têtes de ligne et de colonne (A, B, C… 1, 2, 3…) et les commentaires. Ordre des pages : Lorsque le document ne tient pas sur une feuille, on définit l’ordre d’impression des pages en cochant une des 2 options.

Impression

14

Excel dispose de plusieurs outils pour faciliter l'impression. Notamment l’outil « aperçus » qui en facilite la mise en page. Bien entendu, il n’est pas obligatoire de passer par cet outil pour imprimer le document. Mais son utilisation est conseillée.


Première méthode d’impression (méthode rapide):

On utilise directement la fenêtre impression en cliquant sur Fichier/Imprimer… ou bien le raccourci clavier Ctrl+P

Il faut vérifier en (1) que l’imprimante sélectionnée est bien celle indiquée. En cochant la case (2), on peut imprimer plus tard sur un autre type d'imprimante que celle utilisée pour créer le document. La partie (3) permet de sélectionner la partie que l’on veut imprimer. En effet, il y a trois possibilités : choisir la zone à imprimer directement sur le documents, imprimer la feuille seule, ou encore, imprimer le classeur entier. Ce tableau permet aussi de choisir le nombre de copies à imprimer et l’organisation de l’impression (4).On peut en effet demander que toutes les pages une soit imprimées puis la deux … (1 1 1 2 2 2 3 3 3…) ou d’imprimer les feuilles dans l’ordre pour ne pas avoir a trier ensuite les pages imprimées (fonction copies assemblées : 1 2 3 1 2 3 …). Dans la partie (5), on peut choisir d’imprimer tout le classeur, ou seulement un groupe de pages de celui-ci (ex : de la page 3 à 5). L’aperçu (6) permet de visualiser le résultat obtenu. On peut aussi avoir l’aperçu en cliquant sur Fichier/aperçu avant l’impression.

15


L E S C A L C U L S S O U S E X C E L

1. LES FORMULES DE CALCULS AVEC OPERATEURS

1.1 Les opérateurs Il existe 6 opérateurs arithmétiques afin d’effectuer les calculs de base :

• + (signe plus) ....................................Addition

• - (signe moins)..................................Soustraction

• * (astérisque) ....................................Multiplication

• / (barre oblique) ................................Division

• ^ (signe insertion exposant)..............Exposant

• % (signe pourcentage) .....................Pourcentage

1.2 Ordre de calcul des opérateurs L’ordre de calcul respecte les priorités de calculs établis en mathématique (les multiplications et les

divisions doivent être réalisées avant les additions et les soustractions).

Exemple : 5*2 + 9/3 = 10 + 3 =13

2. LES FONCTIONS DE CALCUL 2.1 Le bouton « somme automatique »

Afin d’obtenir rapidement le résultat d’une somme sur plusieurs cellules d’une même ligne ou d’une

même colonne il vous suffit de sélectionner la cellule contiguë à la dernière cellule de la ligne ou de la

colonne dans laquelle le résultat doit s’afficher puis d’appuyer sur la case « somme automatique » .

Validez en appuyant sur la touche Entrée.

Si les cellules sélectionnées ne vous conviennent pas double-cliquez sur la case contenant la formule

puis modifiez la sélection à l’aide du point d’ancrage (situé dans l’angle inférieur droit de la cellule la

plus basse ou la plus à droite de la sélection initiale) ou sélectionnez manuellement les cellules

désirées.

Vous pouvez également modifier-les

cellules en entrant les valeurs de la

formule manuellement.

16

Point d’ancrage.


Coller une fonction Pour coller une fonction dans une cellule placez-vous dans la cellule en question puis sélectionnez

une fonction en cliquant sur.

Choisissez alors la fonction désirée dans les différentes catégories.

Excel vous indique la façon dont vous devez remplir les champs de la formule.

Puis validez par OK.

2.2 L'assistant Fonction Hormis la fonction somme insérable à partir du bouton de la barre d'outils d'Excel, les autres fonctions

de calcul d’Excel restent méconnues de bon nombre d'utilisateurs.

Et cependant depuis la version 5 d'Excel, il est beaucoup plus aisé de mettre en œuvre une fonction

de calcul, et ce sans avoir à maîtriser la syntaxe de ces fonctions, sur le bout des doigts.

On a ici recours au bouton présent à la fois sur la barre d'outils d'Excel et sur la ligne d'édition des

formules lors de l'édition d'une cellule. Il suffit de cliquer sur ce bouton pour faire apparaître la boite de

dialogue de l'Assistant Fonction. Cette première étape vous permet de choisir la fonction de calcul

d'Excel que vous voulez mettre en œuvre. Pour vous aider à trouver votre bonheur parmi les

centaines de fonctions disponibles sous Excel, la liste de gauche vous permet de choisir une

catégorie dont les fonctions affiliées apparaissent dans la fenêtre de droite.

La catégorie sélectionnée par défaut, à savoir "Les dernières utilisées" regroupe les dernières

fonctions utilisées et celles censées être les plus courantes.

Même si vous n'avez jamais fait appel à cet assistant, vous trouverez dans cette catégorie les

fonctions de gestion les plus courantes.

La syntaxe exacte de la fonction incluant ses arguments est indiquée en gras en dessous chaque

argument et séparé par un ";" L'argument "Nombre" ou "Valeur" représente généralement un nombre

saisi ou encore le contenu d'une cellule.

17


Après avoir cliqué sur la fonction de votre choix dans la fenêtre de droite pour la sélectionner, cliquez

sur le bouton Suivant (un double clic sur la fonction aboutit au même résultat), l'écran suivant vous

permet de saisir les arguments nécessaires à cette fonction.

Si vous vous êtes trompé dans le choix de votre fonction vous pouvez revenir à l'écran précédent au

moyen du bouton Précédent.

Prenons à titre d'exemple la fonction "SI", incluant trois champs permettant de saisir les 3 arguments

de cette fonction (si vous désirez faire référence à une cellule ou à une plage de cellules, vous pouvez

sélectionner les cellules voulues directement en faisant glisser votre pointeur sur les cellules

concernées les coordonnées de ces cellules s'affichent alors dans le champ où est positionné votre

curseur texte) si la fenêtre de l'Assistant masque les cellules concernées, faites-la glisser par

l'intermédiaire de sa barre de titre. En haut à gauche de cette fenêtre apparaissent des informations

relatives au fonctionnement de cette fonction qui vous indiquent en outre à quoi correspondent les

différents arguments.

La zone d'affichage Valeur affiche le résultat actuel de votre formule, tandis que la ou les zones

d'affichage grisées situées à droite des champs de saisie des arguments affichent les valeurs des

arguments sélectionnés, le bouton situé à gauche des arguments vous permet d'appeler à nouveau

l'Assistant fonction.

Après avoir cliqué sur le bouton Fin, le résultat final apparaît dans la ligne d'édition des formules où il

peut alors être directement modifié.

Un exemple très pratique :

Après avoir réalisé un tableau, toutes les lignes peuvent ne pas être remplies.

Mais la formule inclus dans son calcul ces mêmes lignes.

Il est possible de faire disparaître les chiffres parasites, ici en G7, 8, 9 et 10.

Il suffit donc de modifier la formule du haut comme suit.

18


Explications : vous dites à Excel, SI (la cellule A3 est différente de rien ; alors calcule la somme de B3

à F3 et ajoute le signe "Kg" ; sinon ne fait rien). Et vous tirer votre formule, les chiffres parasites

disparaissent.

2.3 Modification d’une formule Pour modifier une formule déjà insérée dans un tableau Excel (comme sur l’exemple ci-dessous) :

19


Cliquez sur la cellule contenant la formule à modifier,

Cliquez sur la cellule

Dans la barre de formule, où il y avait auparavant une somme (l’addition des résultats des deux

colonnes) vous souhaitez maintenant insérer une formule de soustraction afin d’obtenir un autre

résultat dans ce tableau.

Modifiez la formule en remplaçant l’opération « addition » par l’opération « soustraction » (en

respectant les valeurs des nombres) :

Appuyez sur ENTRÉE.

Barre de formule

Cellule du résultat

20


Cette modification de formule est valable pour toutes les opérations de calcul utilisables dans Excel,

elle permet d’obtenir des tableaux semblables mais ayant des calculs différents afin de montrer

plusieurs résultats si besoin il y a.

Et donc le résultat change dans la cellule de la fonction quand la modification est validée en appuyant

sur la touche « Entrée », le nouveau résultat du calcul s’affiche,

2.4 Calcul automatique C’est un paramétrage d’Excel qui permet de choisir la façon d’obtenir les résultats des opérations de

calcul, soit de manière automatique soit manuelle.

Pour paramétrer les calculs sous Excel utilisez les options suivantes :

Dans le menu Outils, cliquez sur Options, puis sur l'onglet Calcul, sous Mode de calcul, sélectionnez

une option.

CALCUL AUTOMATIQUE

21


CALCUL MANUEL

Après avoir inséré une formule de calcul dans une case, remplissez les champs : le résultat s’affiche automatiquement

Si vous sélectionnez une option autre que Automatique, appuyez sur F9 pour calculer toutes les

feuilles de calcul dans tous les classeurs ouverts.

Pour calculer uniquement la feuille de calcul active, appuyez sur MAJ+F9.

Donc suivant l’option choisie (automatique ou manuelle) le résultat d’un calcul s’obtient de deux

façons différentes :

1 : Calcul automatique

Si vous modifiez la valeur d’une cellule incluse

dans une fonction et que vous validez avec

« entré » le résultat du calcul tient compte de

cette modification.

2 : Calcul manuel

22


Si vous modifiez la valeur d’une cellule incluse dans une fonction et que vous validez avec « entré » le

résultat du calcul ne tient pas compte de cette modification.

Vous devez appuyez sur la touche F9 pour que le calcul s’effectue.

23


3. LES REFERENCES RELATIVES ET ABSOLUES

3.1 Avantage de cette fonctionnalité Rappel : Excel permet de gagner énormément de temps, par la recopie automatique d’une formule de

calcul, sur un certain nombre de colonnes ou de lignes. Ces formules de calcul, faisant appel à des «cases », celles ci sont repérées par leurs coordonnées ; une correspondant à leur emplacement en colonne, et l’autre à leur positionnement en ligne. Exemple : A7 (colonne A , ligne 7)

La fonctionnalité d’adressage, offre trois possibilités : • Lorsque l’adressage est relatif (par défaut) si on recopie une formule de

calcul, (une addition par exemple) vers la droite, par rapport à celle d’origine, alors, les coordonnées de ces variables, voient-elles aussi, leurs valeurs «se décaler » vers la droite. Ce principe est très intéressant lorsque l’on veut calculer des totaux

répétitifs, de ligne ou de colonnes :

Exemple. Total des cases «A1» à «A8» apparaît dans la case «A9».

Recopié vers la droite, cela donne « B1» à « B8 » apparaît dans

la case « B9 », et ainsi de suite.(Le A est remplacé par le B).

• L’adressage «mixte ».

Dans ce cas, seule, une des deux coordonnées (colonne ou ligne) est

imposée à la formule de calcul.

Cette deuxième possibilité est intéressante, dans le cas ou seule une des

deux adresses est à modifier, lors de la recopie de formule.

• L’adressage absolu permet, toujours dans le cas d’une recopie de formule,

de figer le nom des coordonnées utilisées. Ce «blocage » sera intéressant lorsqu’une formule fera toujours appel à la même «case », donc la même valeur. Dans ce cas, l’opérateur saisi une seule fois cette valeur, et fera appel a celle ci de façon «systématique», évitant ainsi les erreurs, et les corrections à répétition, après recopie.

24


3.2 Présentation concrète de cette fonction.

• Exemple d’adressage relatif Dans le tableau ci dessous, nous avons recopié la formule de la case «B6», dans les cases «C6» et «D6», par un simple « glissé, copié », soit une seule manipulation. Lors de cette recopie de la case « B6 » à « C6 », les coordonnées de la formule de calcul initial, à savoir « = SOMME(B3;B5) », ont été remplacée automatiquement, par «=SOMME(C3;C5)». De même, dans la case « D6 », les coordonnées de la formule de calcul initial, à savoir « = SOMME(B3 ;B5) », ont été remplacée automatiquement, par «=SOMME(D3;D5)».

25


• L’adressage Mixte

Exemple d’erreur de calcul sans adressage Mixte. Dans le tableau ci dessous, nous avons recopié la formule de la case « B9 », dans les cases « C9 » et « D9 », par un simple « glissé, copié », soit une seule manipulation. Lors de cette recopie de la case « B9 » à « C9 », les coordonnées de la formule de calcul initial, à savoir «=SOMME(B2*C5)», on été remplacée automatiquement, par «=SOMME(C2*D5) ».En effet, en adressage relatif, lors d’une recopie vers la droite, seule la cordonnée correspondant à l’adresse colonne change. De même en « D9 », les coordonnées de la formule de calcul initial, à savoir «=SOMME(B2*C5)», ont été remplacées automatiquement, par «=SOMME(D2*E5) », car décalage de deux cases vers la droite. Par contre, lors de la recopie vers le bas, de la zone « B9 » en « B10 », la formule initiale, à savoir toujours «=SOMME(B2*C5)», devient «=SOMME(B3*C6)».Ici, seule l’adresse ligne change. (La recopie de cette dernière vers la droite, donnera les résultats suivant, à savoir en « C10 » « =SOMME(C3*D6) », et en « C11», «=SOMME(D3*E6) », comme expliquer ci dessus.) Résultats : Dans ce cas, à part les résultats présentés en « B9 » et « B10 », tous les autres sont faux (En rouge dans le tableau).

26


Exemple de solution de calcul, cette fois ci en adressage Mixte. Après avoir crée la formule normale en case « B9 », à savoir « =SOMME(B2*C5) », nous avons simplement ajouté le signe « $ » devant les coordonnées colonne, de la case « C5 ».La formule ainsi obtenue, dans la case « B9 », est : « =SOMME(B2*$C5) ». Nous avons ensuite recopié cette formule, de la case « B9 », vers les cases « C9 » et « D9 », par un simple « glissé, copié », soit une seule manipulation. Premier avantage : Lors de cette recopie en « C9 », les coordonnées de la formule de calcul initial, à savoir «=SOMME(B2*$C5)», ont été remplacée automatiquement, par «=SOMME(C2*$C5) ». Seule les coordonnées « B2 » (en adressage relatif) contrairement à « $C5 » (qui lui est en adressage mixte) ont changés, lors de la recopie vers la droite. De même en « D9 », les coordonnées de la formule de calcul initial, à savoir «=SOMME(B2*$C5)», ont été remplacées automatiquement, par «=SOMME(D2*$C5) », car décalage de deux cases vers la droite. Là encore, « $C5 » n’a pas changé, l’adresse colonne «C» étant «bloqué» par le signe «$» placé juste devant. Deuxième avantage : Lors de la deuxième recopie, vers le bas, de la case « B9 » en « B10 », les coordonnées de la formule de calcul initial, à savoir «=SOMME(B2*$C5)», ont été remplacées automatiquement, par «=SOMME(B3*$C6) ». Dans ce cas, « B2 » étant en adressage relatif, il devient « B3 », tandis que « $C5 » devient « $C6 ». En effet, seule l’adresse colonne « C » est « imposée » par le signe « $ », et non l’adresse ligne 5, qui change en 6, prenant ainsi en compte, dans le tableau, le bon taux de commission.

27


• L’adressage absolu

Autre exemple d’erreur de calcul sans adressage absolu. Dans le tableau ci dessous, nous avons recopié la formule de la case «B8», dans les cases «C8», «D8», «B9», «C9» et «D9» par un simple « glissé, copié ». Lors de cette recopie de la case «B8» vers les cases «C8» et «D8», les coordonnées colonnes se sont mises à jour de façon automatique. La formule de calcul initial, étant «=SOMME(B2*C5)», la recopie vers la droite à fait apparaître la formule suivante : «=SOMME(C2*D5)», en case «C8», et «=SOMME(D2*E5)» en case «D8», modification normale, en adressage relatif, comme vu précédemment. De même la recopie de la case «B8» en «B9» fera apparaître une modification automatique de la coordonnée ligne, cette fois, en passant de la formule initiale «=SOMME(B2*C5)» a la formule recopiée en B9 «=SOMME(B3*C6)». L’explication pour les formules obtenues en case «C9» et «D9» est la même que le premier point ci dessus. Dans ce cas précis, a part pour la formule initiale, tous les autres résultats sont faux, car ils font appel a des cases, n‘ayant pas de donnée.

28


Exemple de solution de calcul, cette fois ci en adressage absolu. Toujours dans le même tableau, ci dessous, nous avons recopié la formule de la case « B8 », dans les cases « C8 », « D8 », « B9 », « C9 » et « D9 » par un simple « glissé, copié ». Auparavant, nous avons ajouter à la formule initiale de cette case B8, a savoir «=SOMME(B2*C5) », un signe « $ » devant les deux coordonnées (colonnes et lignes) de la case « C5». La formule ainsi obtenue est : «=SOMME(B2*$C$5)» De cette façon, dans cette formule, la case «C5» est en adressage absolu. On pourra donc recopier celle ci dans tous les sens, ces coordonnées ne seront pas modifiées. Seule la première partie de la formule, (faisant appel au chiffre d’affaires) se mettra à jour automatiquement, en fonction de la variable calculée. Ainsi, si «B8» est égale a «=SOMME(B2*$C$5)», la copie de cette formule en case «C8» donnera le résultat suivant : «=SOMME(C2*$C$5)»et en case «C9» le résulta escompté, a savoir «=SOMME(D2*$C$5)» La recopie vers le bas, modifiera elle, la valeur de la cordonnée « ligne ». En effet, «=SOMME(B2*$C$5)» se transformera en «=SOMME(B3*$C$5)», et ainsi de suite.

29


4. LES NOMS

4.1 Intérêt des noms Nommer une cellule ou un groupe de cellules contiguës est une manière de rendre plus limpide, plus explicite, la manipulation des données. Ainsi, ce type de formule :

=SOMME(Totaux)

se comprend plus facilement que :

=SOMME(C1 :C10)

Le nom donné est conservé en cas de déplacement de la cellule ou du groupe ou du groupe de cellules.

4.2 Attribution d’un nom

Pour nommer une cellule ou un groupe de cellules, il faut d’abord cliquer sur la cellule ou

sélectionner le groupe :

s

Sélection d’un groupe de cellule

30


Placer ensuite le curseur dans la case en haut à gauche de l’application, comme suit :

et saisissez le nom souhaité :

Règles d’attribution des noms : Un nom doit obligatoirement commencer par une lettre ou un caractère de soulignement ( _ ). Pour le

reste, il peut contenir chiffres, lettres, majuscules, minuscules (sachant qu’Excel ne fera pas la

différence entre elles : Total = TOTAL), etc., mais ni espace ni caractère spéciaux. En outre, sa

longueur ne doit pas excéder 253 caractères.

31


Il est également possible de nommer un groupe de cellule en passant par le menu

Insertion Nom Définir :

Dans la boîte de dialogue, tapez un nom, ajoutez-le, puis validez :

4.3 Utilisation des noms dans un calcul Une fois les cellules ou le groupe de cellules nommés, vous pouvez utiliser le nom dans les formules :

=SOMME(Totaux)

=SOMME(Totaux) * 19, 6 / 100

Et plusieurs noms dans une même formule :

= SOMME(Totaux) / SOMME(totauxQuantité)

32


UTILISATION DES FONCTIONS

Objectif : permettre d’effectuer des calculs sans avoir à taper des formules longues et compliquées. Saisir une feuille de calcul Excel puis y entrer toutes les valeurs qui nécessiteront un résultat. (voir l’exemple ci-dessous)

2 . Cliquez sur ce bouton pour faire apparaître la boîte

de dialogue coller une fonction.

1. Cliquez sur la cellule oû l’on souhaite saisir la fonction .

Cliquez sur «tous» lorsque vous ignorez la catégorie qui contient la fonction recherchée.

Cliquez à cet endroit pour voir afficher la fonction que vous souhaitez réaliser.

33


Sélectionnez dans la rubrique Nom de la fonction Le choix puis validez par OK. Alors une boîte de dialogue qui comprend le nécessaire aux calculs apparaît (notez que le résultat de notre exemple, est donc 7500 car (B6 : B8) ) :

Cette zone permet de saisir les nombres nécessaires aux calculs. Il est nécessaire de déplacer la fenêtre ci-dessus afin de pouvoir sélectionner la cellule correspondant au nombre 1. Remarque : lors de la sélection de la cellule, cette dernière devient active.

34


Cliquez dans la fenêtre ci-dessus pour la préparation du deuxième nombre. Il est nécessaire de sélectionner la deuxième cellule de la feuille EXCEL qui en outre est indispensable au calcul. A ce stade, suivre la même procédure pour prendre en compte tous les chiffres qui vont composer l’opération.

Après avoir sélectionné toutes les cellules indispensables aux calculs, Validez en cliquant sur le bouton OK le résultat apparaît dans la cellule. En ce qui concerne notre exemple (voir 1), le résultat correspond à TOTAL DEPENSES.

35


36


LA MISE EN FORME D’UN TABLEAU

1. AJUSTER LA TAILLE D’UNE COLONNE Pour Modifier la largeur d’une colonne, il existe deux possibilités : l’ajustement automatique et la redimension de la largeur de colonne. Redimensionner la largeur d’une colonne :

Pour affecter une taille à une colonne, il suffit de la sélectionner puis de passer par le clic droit (à gauche) ou par le menu format (ci-dessus) pour fixer une taille.

Pour redimensionner la taille d’une colonne, il suffit de se placer avec la souris sur la bordure droite de son en-tête puis de la faire glisser (en maintenant le bouton gauche enfoncé).

Il suffit ensuite de donner une valeur à la sélection.

37


Mise en forme automatique :

Pour Ajuster la taille d’une colonne à son texte, il suffit de double cliquer sur la bordure droite de son en-tête. Pour ajuster toutes les colonnes à leur contenu, il suffit de sectionner toutes les colonnes (Control + A ou menu Edition puis Sélectionner tout) et de double cliquer sur n’importe quelle bordure d’un en-tête.

En passant par le menu Format, Colonne puis Ajustement automatique, vous pouvez aussi redimensionner la ou les colonnes sélectionnées.

Pour ne Redimensionner la largeur que de certaines colonnes, il suffit de les sélectionner (en cliquant sur les en-têtes des colonnes tout en maintenant la touche Control) puis de les redimensionner via un en-tête ou le menu contextuel du clic droit.

2. MODIFIER LA TAILLE, LA POLICE ET LE STYLE DES CARACTERES Vérifiez que les options de Mise en Forme sont activées en cliquant avec le bouton droit de la souris sur la barre d’outils en haut de votre écran. Cliquez sur Mise en Forme si l’option est désactivée.

Sélectionnez votre texte, des cellules, des lignes ou des colonnes. Cliquez ensuite sur le bouton ci-dessus pour choisir puis modifiez la police.

De la même façon, après avoir sélectionné une zone de texte, cliquez sur le bouton de la taille de Police pour modifier celle-ci

Pour modifier le style de caractères, il suffit de sélectionner la zone de texte à modifier puis d’appliquer le style de caractères adéquat (gras, italique, souligné).

38


Il est aussi possible de paramétrer les textes des cellules à modifier via le menu Format puis Cellules (ou le clic droit puis Format de Cellule). Se rendre ensuite dans l’onglet Police.

D’autres paramétrages sont possibles via la fenêtre de Format de Cellules, comme la couleur de la Police ou la mise en exposant ou en indice du texte. 3. FORMATS DES NOMBRES

• Avantage de cette fonction Cette fonction à de multiples avantages, comme par exemple :

Soigner la présentation d’un document, en faisant apparaître automatiquement l’unité monétaire ou bien encore, en faisant apparaître en couleur les nombres négatifs. Eviter les erreurs de saisie, de par le pré formatage de cellules, sous forme de date, heure, N° de téléphone, Nombre de décimale, code postal, et même de façon personnalisée, évitant l’intégration par erreur, de nombre trop long.

Affecter à une cellule, une fonction spécifique, tel que l’intégration de texte, étant alors

considérée comme tel dans le document

• Accès à cette fonction de présentation.

Nous avons accès à cette fonction lors de la création ou en modification d’un document, après avoir sélectionné la ou les cellules désirées :

39


Soit par le clic droit de la souris, ligne format de cellule …

Soit, comme le montre l’écran ci dessous ;

Par le menu de haut d’écran, option « Format » puis le sous menu « Cellule »,

Ou directement par les touches de raccourcis « ALT » + « T »…….. puis « C », Ou bien encore par la touche directe « Ctrl » + « 1 ».

40


Ou bien enfin, par l’icône de raccourci de la barre de tache, comme montre l’écran ci dessous.

L’écran ainsi obtenu, permet :

• De formater la ou les cellules du document, et ce de par le choix de l’une des catégories présentes ci dessous, à savoir « standard », « Nombre », « Monétaire », etc. …..

• D’affiner ce « formatage », en choisissant pour chaque Catégorie, le « Type » exact de présentation désirée.

Le tableau ci-dessous nous permettra d’apprécier un échantillon parmi les nombreux paramétrages possibles.

41


Différentes

"Catégories" disponibles

Exemple de "Type" de modification Sans aucune présentation

Avec présentation de

ce "Type"

Standard Non proposé 0Nombre Utilise le séparateur des milliers/ nombre négatif en rouge/avec trois décimales -2145.362 -2 145.362

Monétaire Utilise trois décimales/nombre négatif en rouge/avec unité monétaire -2222.22 -2 222.220 €Comptabilité (idem Monétaire, mais en plus aligne les décimal par colonne) -2222.22 -2 222.220 €

Date Format utilisé JJ/MM/AA 10103 01/01/03Heure Format utilisé hh:mn 1230 12:30

Pourcentage

Utilise deux décimal, et converti en pourcentage 1.2 1.20%

Fraction Impossibilité de saisir 1/4 en standard, sauf en texte, d'où calcul impossible 01-avr 1/4Scientifique Utilisation de la présentation scientifique avec trois décimales 2.36666 2.367E+00

texte Même présentation à l'écran, mais le nombre sera considéré comme un texte 123 123Spécial exemple de saisie d'un numéro de téléphone 326364656 03 26 36 46 56

Personnalisée exemple de saisie d'une somme d'argent en négatif avec deux décimales -2222.22222 -2 222.22 €

4. BORDURES

1) La Barre d'outils :

Le bouton permet d'appliquer quelques bordures à votre plage. Sélectionnez votre plage puis,

si la bordure souhaitée est celle dessinée sur le bouton, cliquez sur celui-ci, dans le cas contraire,

cliquez sur le petit triangle qui vous proposera d'autres bordures Vous utiliserez souvent la boîte de

dialogue vu le peu de bordures proposées.

42


2) Boîte de dialogue

Clic droit: Format de cellule puis choissiez l’onglet Bordure

Ou Cliquez sur Format puis sur Cellule. (Ctrl+1)

• Sélectionnez ensuite le Style de votre bordure en cliquant sur

la zone Style.

• Sélectionnez, éventuellement, votre couleur dans la liste

Couleur.

• Cliquez sur le bouton correspondant à la bordure à appliquer.

Les boutons Aucune, Contour, Intérieur vous permettent

d'appliquer rapidement plusieurs bordures.

Vous constatez que vous avez ici beaucoup plus de choix de

bordures et que vous pouvez également appliquer les 2 diagonales.

3) Exemple :

Pour pouvoir modifier ou ajouter une

bordure il faut sélectionner toutes les

cellules souhaitées et cliquez avec le

bouton droit de la souris puis sélectionnez

Format de cellule ou utilisez le

bouton .

Ensuite sélectionner l’onglet Bordure et

choisissez le résultat souhaité.

43


5. COLORATION DES CELLULES

1) La Barre d'outils :

Le bouton permet d'appliquer la couleur de remplissage d’une cellule.

2) Boîte de dialogue

Clic droit: Format de cellule puis choisissez l’onglet Motifs

Ou Cliquez sur Format puis sur Cellule. (Ctrl+1)

• Sélectionnez votre couleur de remplissage.

3) Exemple

Pour coloriser une cellule il suffit de sélectionner celle-ci et de cliquer sur le bouton couleur de

remplissage ou faire un clic droit avec la souris et choisissez Format de cellule ou Cliquez

sur Format puis sur Cellule. (Ctrl+1) puis Motifs et sélectionnez l’effet désire.

44

Support de cours EXCEL 2000 Support de cours EXCEL 2000

45

45


FORMAT PERSONNALISE Accès au format de cellule personnalisé

Une fois la cellule remplie, il y a deux façons d’accéder au format de cellule personnalisé :

1. Sélectionner la cellule (clic gauche) puis cliquer sur le menu Format puis Cellule (ou appuyer sur CTRL + 1)

2. Faire un clic droit sur la cellule puis cliquer sur Format de cellule

Ensuite , cliquer sur l’onglet Nombre puis dans le champ Catégorie , cliquer sur Personnalisée Dans le champ Type : Cliquer sur le format voulu. Remarque : Dans la fenêtre de Format de cellule, possibilité de voir l’aperçu de la transformation.

1. EXPLICATION DES PRINCIPAUX FORMATS

• Format « positionnement » : Sélectionner @, sa position dans la formule déterminera la position du nombre dans la cellule

Remarque : Si @ est le seul symbole dans la formule, le nombre sera positionné complètement à gauche

46


• Format « milliers » : # ##0 Sert à découper un nombre par série de 3 chiffres Ex : 12954 devient 12 954

• Format « virgule » : 0,00 Sert à insérer deux zéros après la virgule (on peut insérer le nombre de zéros que l’on veut, ex 0,0000) Ex : 12 devient 12,00 • Format couleur pour nombre positif et négatif Ex : Changement de couleur du nombre en vert (si positif) et en rouge (si négatif). Taper [vert]standard ;[rouge]-standard

Remarque : En tapant [vert]standard , le contenu de la cellule sera toujours vert. • Format « Exposant » Transformer un nombre sous forme de notation scientifique Ex : En tapant 125 et en appliquant le format 0,00 E+00, on obtient : 1,25 E+03 • Format « Date » : =maintenant() correspond à jj/mm/aaaa hh :mm =aujourdhui() correspond à jj/mm/aaaa Pour les dates : J affiche le jour sous la forme 1

47


JJ 01 JJJ Mar JJJJ Mardi Remarque : le premier jour de la semaine est dimanche. M affiche le mois sous la forme 1 MM 01 MMM Jan MMMM Janvier AA affiche l’année sous la forme 95 AAAA 1995 • Format « Heure »Transformation automatique de l’heure au format américain

AM (matin) ou PM (après-midi) en sélectionnant h :mm :ss AM/PM Ex : 13 :52 :42 se transformera en 1 :52 :42 PM 2. SUPPRESSION D’UN FORMAT PERSONNALISE Premièrement : Sélectionner la cellule, cliquer sur le menu Format puis Cellule. Ensuite : Cliquer sur l’onglet Nombre puis sur la catégorie Personnalisée. Sélectionner le format à supprimer puis cliquer sur le bouton Supprimer Valider en cliquant sur OK

48


LES GRAPHIQUES SOUS EXCEL

1. Introduction sur les graphiques

1.1. Création d'un graphique

1.2. Modification sur les graphiques

2. Les Différents types de graphique

2.1. L’histogramme

2.2. Graphique à barres

2.3. Courbes

2.4. Secteurs

2.5. Nuages de points

2.6. Aires

2.7. Anneaux

2.8. Radar

2.9. Surface

2.10. Bulle

2.11. Boursier

2.12. Cône, Cylindre et pyramide

2.13. Types personnalisés

49


1. INTRODUCTION SUR LES GRAPHIQUES

Afin de mettre en pratique les différents graphiques que l’on peut trouver sous Excel, nous allons

nous appuyer sur un tableau que nous pourrons considérer comme notre base de données. Ce

tableau est un exemple qui nous présente le Chiffre d’affaire de différents commerciaux sur une

période d’un mois.

NB : Attention les différentes colonnes doivent être exempte de toute fusion pour éviter les conflits lors de la création des graphiques.

1.1 Création d’un graphique

Il faut tout d’abord sélectionner les colonnes qui contiennent les valeurs que l’on souhaite voir

figurer en abscisse et en ordonnée. Par exemple : nous voulons voir apparaître le chiffre d’affaires par

commercial afin de comparer leurs résultats.

Pour cela nous allons sélectionner les colonnes qui nous intéressent (nom du commercial et

chiffre d’affaire total)

Haut du document

50


Il existe deux méthodes afin d’insérer un graphique dans la feuille :

• Par le menu Insertion puis Graphique (Alt+I+G) :

• Par la barre des taches standard puis l’icône suivant :

Le menu suivant apparaît donc :

Haut du document

51


Dans un premier temps le graphique sélectionné sera celui par défaut (histogramme groupé), il

sera simplement utilisé afin de décrire les différents paramètres que l’on peut modifier sur un

graphique

NB : L’ensemble des modifications que nous allons décrire ci-dessous sont valables pour les

différents types de graphiques.

En cliquant directement sur terminer (nous reviendrons plus tard sur les étapes 2 à 4), le

graphique suivant apparaît :

6

5

42

3

71

1.2 Modifications sur les graphiques

Afin de modifier les paramètres détaillés ci-dessus, nous allons détailler tous ces points un par un :

• le titre

Pour commencer sélectionnez-le

Un simple clique permet de modifier le texte tandis qu’un double clique ouvre la fenêtre Format du titre

du graphique :

Haut du document

52


- Motifs :

Choix des Motifs et textures

Couleur de l’aire où se trouve le titre

Sélection du type de bordure

- Police :

Choix d’Arrière-plan

Choix de la Couleur

Choix des Attributs

Choix du Soulignement

Choix du Style

Choix de la Taille

Choix de la Police

Haut du document

53


- Alignement :

Choix de l’alignement du texte

Choix de l’Orientation du texte

• Format de l’axe :

Un double clique sur l’axe des ordonnées ou des abscisses ouvre la fenêtre format de l’axe :

- Motifs :

Choix du type des graduations

Emplacement des étiquettes de graduation

Choix du type de ligne

Haut du document

54


Echelle :

Choix des options

Coupure de l’axe des ordonnées

- Police (voir section précédente)

- Nombre :

Choix de la Catégorie Et son aperçu

- Alignement (voir section précédente)

Haut du document

55


• Format de série de données : - Motifs (voir section précédente)

-Sélection de l’axe

Choix de l’axe principal ou secondaire

- Barre d’erreur Y :

Réglage de la marge d’erreur

Choix de la barre d’erreur

Haut du document

56


- Etiquettes de données :

Choix des étiquettes de données

- Ordre des séries :

Permet d’ordonner les séries dans le cas où elles sont plusieurs.

Haut du document

57


- Options :

Attribue une couleur par donnée en abscisse

Détermine la largeur de l’intervalle et la superposition

• Format de zone de traçage : - Motifs (voir section précédente)

• Format de quadrillage - Motifs (voir section précédente)

-Echelle :

Diverses options de l’échelle

Unité d’affichage

Echelle de l’axe des ordonnées

Haut du document

58


• Format de légende

- Motifs (voir section précédente)

- Police (voir section précédente)

-Emplacement :

Emplacement de la légende

• Format de la zone de graphique

- Motifs (voir section précédente) - Police (voir section précédente) -Propriétés

Options relatives à la sécurité

Propriété du positionnement

Haut du document

59


2. LES DIFFERENTS TYPES DE GRAPHIQUE

Afin D’étudier ces différents types de graphique, nous allons les détailler un par un en nous

appuyant à notre tableau de départ.

2.1 L’histogramme

Description : Un graphique en histogrammes affiche les changements de données pendant une

période déterminée ou effectue des comparaisons entre des éléments.

Exemples

Haut du document

60


2.2 Graphique à barres

Description : Identiques à l’histogramme mais avec une orientation horizontale

Exemple :

2.3 Courbes

Description : Un graphique en courbes représente les tendances à intervalles régulières, il en

existe trois types : sans points, avec points et en 3D. Exemples:

Haut du document

2.4 Secteurs

61


Description : Un graphique en secteurs affiche la taille d’un élément dans son ensemble. Ce

type de graphique représente toujours une seule série de donnés.

Exemples:

Afin de rendre les secteurs de petite taille plus lisible, vous pouvez les en un seul, puis

décomposer cet élément dans un autre graphique plus petit (en secteurs ou en barres) que vous

placerez à côté du graphique principal.

Exemple :

Haut du document

62


2.5 Nuages de points

Description : Ce type de graphique vous permettent de mettre en relation deux valeurs et de

tracer ou non des courbes reliant les points obtenus.

Exemples:

2.6 Aires

Description : Dans notre exemple ce graphique nous permet de mettre en évidence, pour

chaque vendeur, le chiffre d’affaire pour chaque appareil, ce qui permet de les comparer facilement.

Exemples :

Haut du document

2.7 Anneaux

Description : Comme un graphique en secteurs, un graphique en anneaux affiche la relation qui

existe entre un tout et certaines de ses parties, mais il peut contenir plusieurs séries de données.

Chaque anneau du graphique représente une série de données.

Haut du document

63


Exemples :

2.8 Radar

Description :Dans un graphique en radar, chaque catégorie a son propre axe des

ordonnées émanant du point central. Les lignes relient toutes les valeurs de la même série. Exemples

Graphique en radar

0

200

400

600

800Derat

Periot

Poiret

Geraut

Fota

Ferair

Téléphone sx205

Téléphone 154-42

Photocopieur sf-4586

Fax 2501-51

Fax 1245-45

Haut du document

64


2.9 Surface Description : le graphique en surface permet d'obtenir une association de deux séries de

données. Les couleurs indiquent les zones appartenant à la même plage de valeurs. Exemple :

Der

at

Per

iot

Poi

ret

Ger

aut

Fota

Fera

irTéléphone

sx205

0100

200300

400

500

600

700

chiffre d'appareil

vendeurs

Graphique en surface

600-700500-600400-500300-400200-300100-2000-100

2.10 Bulle

Description : Il a les mêmes propriétés que le nuage de point, mis à part qu’une troisième valeur

est représentée par la taille des bulles.

Graphique en bulle

Periot

Derat

Poiret

Fota

Ferair

Geraut

-500

50100150200250300350400

chiff

re d

'affa

ire

Téléphone sx205

Exemple :

Haut du document

65


2.11 Boursier

Description : le graphique Max-Min-Clôture est souvent utilisé pour illustrer les cours de la

Bourse. Ce graphique est également adapté à la représentation de données scientifiques, par

exemple pour indiquer les changements de température. Pour créer ce type de graphique boursier

parmi d'autres, vous devez organiser vos données dans le bon ordre.

Un graphique boursier mesurant le volume comporte deux axes des ordonnées : l'un pour les

colonnes représentant le volume, l'autre pour les cours des valeurs boursières. Le volume peut être

indiqué dans un graphique Max-Min-Clôture ou Ouverture-Max-Min-Clôture.

Exemple :

250300350

Graphique en cylindreTéléphone sx205

graphique boursier

0

100

200

300

400

500

600

700

Derat Periot Poiret Geraut Fota Ferair

vendeurs

chiff

re d

'affa

ire

020040060080010001200140016001800

nombre d'heures travaillées

chiff

re d

'affa

ire to

tal Photocopieur

sf-4586Chiffre d'affaire total (en €)Objectif Chiffre d'affaire (en €)Nombre d'heures travaillées ce mois

2.12 Cône, Cylindre et pyramide

Description : Les marques de données coniques, cylindriques et pyramidales peuvent modifier

considérablement l'apparence des histogrammes et des graphiques en barres 3D.

Haut du document

66


Exemples :

Haut du document

0

50

100

150

200

250

300

350

chiffre d'affaires

Derat Periot Poiret Geraut Fota Ferair

vendeurs

Graphique en côneTéléphone sx205

050

100150200250300350

chiffre d'affaires

Derat Poiret Fota

vendeurs

Graphique en PyramideTéléphone sx205

2.13 Types personnalisés

67


Description : Il existe d’autres types de graphique dit personnalisés. Ces graphiques sont fondés

sur les types de graphiques standard mais contiennent une mise en forme et des options

supplémentaires, telles qu'une légende, un quadrillage, des étiquettes de données, un axe

secondaire, des couleurs, des motifs, des remplissages et des choix de placement pour plusieurs

éléments graphiques. Ces types de graphiques se trouvent sous l'onglet Personnalisé de la boîte de

dialogue Options du graphique.

Exemple :

Haut du document

68


CRÉATION D’UNE L ISTE

Excel peut gérer les données sous forme de base de données, dont les fonctions vont de la saisie de données à l’aide d’un masque de saisie à diverses possibilités de filtrage des informations, le tri ou le calcul de sous totaux à partir de données.

Excel peut se révéler utile dans les cas suivants :

• Vous avez déjà entré les données à exploiter dans Excel et vous pouvez les transformer en

base de données.

• Vous connaissez parfaitement Excel, ne maîtrisez pas suffisamment Access et n’avez pas le

temps d’apprendre à le manipuler.

• Les données à traiter ne sont ni trop volumineuses ni trop complexes.

1. Définition

2. Créer une liste dans Excel

2.1. Format de la liste

2.2. Organisation des listes

2.3. Définir la liste

2.4. Saisir les enregistrements

2.5. Rechercher un enregistrement

2.5.1. Critères de recherche

2.5.2. La recherche

2.5.3. Recherche d'une valeur dans une liste à l'aide de l'Assistant Recherche

2.6. Modifier une liste

3. Trier la liste

3.1. Ordre de tri

3.2. Le tri par colonnes

3.3. Trier à l’aide de la commande Données/Trier

3.3.1. Tri de lignes en fonction du contenu de 2 colonnes ou plus

3.3.2. Tri de colonnes en fonction du contenu des lignes

3.3.3. Tri de mois, jours de la semaine ou de listes personnalisées

69


1. Définition

Une liste est un ensemble de feuilles de calcul contenant des données similaires, tel qu’une base de

données de factures ou une série de noms de clients et de numéros de téléphone. Une liste peut être

utilisée comme une base de données dans laquelle les lignes sont les enregistrements et les

colonnes, des champs. Un champ accueille toujours le même type d’enregistrement ; Ainsi le champ

Rue est-il composé de toutes les adresses de la liste.

La première ligne d’une liste contient des étiquettes correspondant aux colonnes.

champs

ChampsEnregistrements d’une ligne

2. Créer une liste dans Excel

La gestion d’une liste, la saisie, la modification ou la suppression des enregistrements peuvent se

faire soit directement dans le tableau, soit à l’aide d’un masque de saisie. Ce dernier est un outil

fort utile, qui vous permet de mieux gérer votre travail.

Vous devez tout d’abord définir une liste, c’est à dire déterminer la plage de cellules qui

accueillera la base de données. Vous pourrez ensuite entrer les différents enregistrements.

2.1. Format de la liste

• Utilisez des étiquettes de colonnes mises en forme : Créez des étiquettes de colonnes dans la première ligne de la liste. Excel utilise les étiquettes afin de créer des rapports et de rechercher et d'organiser des données. Pour les étiquettes de colonnes, utilisez un style de police, d'alignement, de format, de motif, de bordure ou de mise en majuscules différent du format que vous affectez aux données de la liste. Mettez en forme les cellules au format texte avant de taper les étiquettes de colonnes.

retour au menu

70


• Utiliser des bordures de cellules : Lorsque vous voulez séparer les étiquettes des données, utilisez les bordures des cellules (et non pas des lignes vides ou en pointillés) pour insérer des lignes sous les étiquettes.

• Éviter les lignes et les colonnes vides : Évitez de placer des lignes et des colonnes vides dans la liste, de sorte que Excel puisse plus facilement détecter et sélectionner la liste.

• Ne pas insérer des espaces à gauche et à droite : Les espaces supplémentaires au début ou à la fin d'une cellule affectent le tri et la recherche. Plutôt que d'insérer des espaces, mettez le texte en retrait à l'intérieur de la cellule.

• Étendre les formats de liste et les formules : Lorsque vous ajoutez de nouvelles lignes de données à la fin d'une liste, Excel utilise des formats de liste et des formules adéquats. Pour ce faire, trois des cinq cellules précédentes doivent utiliser le même format ou la même formule.

• Activation ou désactivation des formats et des formules étendus

Quand vous activez les formules et les formats étendus, Excel met automatiquement en forme les nouvelles données que vous saisissez à la fin d'une liste en accord avec les lignes précédentes, et copie automatiquement les formules qui se répètent dans chaque ligne. Pour être étendus, les formats et les formules doivent apparaître dans au moins trois des cinq lignes de la liste qui précèdent la nouvelle ligne.

Dans le menu Outils, cliquez sur Options, puis sur l'onglet Modifier.

Pour appliquer automatiquement aux

nouveaux éléments que vous ajoutez à la fin d'une liste la même mise en forme que le

reste de la liste, activez la case à cocher Étendre les formules et formats de liste. Pour

empêcher la mise en forme automatique, désactivez la case à cocher.

retour au menu

71


2.2. Organisation des listes

• Utiliser une seule liste par feuille de calcul Évitez d'avoir plusieurs listes dans une feuille de calcul. Certaines fonctions de gestion de liste, telles que le filtrage, ne peuvent être utilisées que sur une liste à la fois.

• Placer des éléments identiques dans une colonne Créez la liste de façon à ce que les mêmes éléments de chaque ligne se retrouvent dans la même colonne.

• Maintenir la liste séparée Laissez au moins une colonne et une ligne vides entre la liste et les autres données sur la feuille de calcul. Excel peut alors plus facilement détecter et sélectionner la liste lorsque vous triez, filtrez ou insérez des sous totaux automatiques.

• Placer les données importantes au-dessus ou en dessous de la liste Évitez de placer des données importantes à gauche ou à droite de la liste. Ces données risquent d'être masquées lorsque vous filtrez la liste.

• Afficher les lignes et les colonnes Vérifiez que les lignes ou les colonnes masquées sont affichées avant de modifier la liste. Lorsque des lignes ou des colonnes ne sont pas affichées, des données peuvent être supprimées par erreur.

2.3. Définir la liste

• Entrez dans la première ligne de la liste les intitulés de colonnes, ou de champs,

puis sélectionnez une cellule dans la liste à créer.

• Activez la commande Données/Grille. Pour créer la base de donnée, Excel doit

pouvoir reconnaître les étiquettes de colonnes. Il affiche donc un message d’erreur.

• Cliquez sur le bouton OK.

retour au menu

72


Le programme affiche donc le masque de

saisie Feuil 1 vous permettant de saisir les

informations relatives au premier

enregistrement.

2.4. Saisir les enregistrements

• Par défaut, le point d’insertion se trouve dans la première zone de saisie (ici, Code

Fournisseur). Saisissez l’entrée correspondante (ici, INFO).

• Appuyez sur la touche Tab ou cliquez sur la zone de saisie suivante pour y placer

le curseur (ici, Société) puis entrez les données (ici, Gigatoc).

• Répétez la procédure pour les autres zones de saisie jusqu’à ce que

l’enregistrement soit complet.

Première zone de saisie &

Ajouter un nouvel enregistrement

Bouton Critères pour effectuer les recherches par exemple

• Validez en appuyant sur la touche Entrée ou cliquez sur le bouton Nouvelle pour

ouvrir un nouvel enregistrement.

• Procédez de même pour les autres enregistrements. Après avoir complété le

dernier, cliquez sur le bouton Fermer.

retour au menu

73


2.5. Rechercher un enregistrement

Si vous gérez des listes très importantes (modification, suppression d’enregistrement), vous

pouvez avoir besoin de rechercher des enregistrements sur la base de critères définis.

2.5.1. Critères de recherche

Le masque de saisie vous propose les critères de recherche suivants :

Enregistrements présentant la même entrée

dans un champ

Rechercher tous les fournisseurs de Paris, c’est-

à-dire tous les enregistrements dont l’entrée du

champ Localité est Paris.

Enregistrements présentant des similitudes.

Rechercher tous les fournisseurs dont la localité commence par la lettre « N », c’est-à-dire tous les enregistrements qui remplissent le critère de recherche « H* » dans le champ Localité. Le caractère générique « ? » remplace n’importe quel nombre de signes dans une chaîne de caractères.

Ex : p?rt trouve « port » et « part ».

Enregistrements présentant des similitudes de comparaison.

Rechercher tous les fournisseurs dont le code

postal est supérieur ou égal à 78000, c’est-à-dire

tous les enregistrements répondant au

critère :>=78000 dans le champ Code Postal. Les

opérateurs de comparaison sont :

<, >, =, >=, =< .

2.5.2. La recherche

Procédez comme indiqué ci-après pour rechercher des enregistrements à l’aide du

masque de saisie, sur la base de critères définis.

Dans notre exemple, nous recherchons tous les fournisseurs d’articles d’informatique,

c’est-à-dire tous les enregistrements présentant l’entrée INFO dans le champ Code

Fournisseur.

retour au menu

74


Sélectionnez la liste en cliquant sur n’importe quelle cellule puis ouvrez le masque de saisie en

activant la commande Données/Grille.

Cliquez sur le bouton Critères.

Entrez les critères de recherche dans les zones de saisie correspondantes (ici, INFO dans la

zone de saisie Code Fournisseur).

Pour afficher les enregistrements trouvés, cliquez sur les boutons Précédente ou Suivante (ici,

Suivante)

Entrez les critères de recherche Affichez les enregistrements trouvés

2.5.3. Recherche d'une valeur dans une liste à l'aide de l'Assistant Recherche

L'Assistant Recherche est un complément. Si la commande Rechercher ne s'affiche

pas dans le sous-menu Assistant du menu Outils, vous devez installer et charger le

complément.

• Cliquez sur une cellule de la liste.

• Dans le menu Outils, pointez sur Assistant, puis cliquez sur Rechercher.

• Suivez les instructions de l'Assistant.

2.6. Modifier une liste

Comme pour la création, la modification d’une liste, c’est-à-dire la modification ou la

suppression des enregistrements qui la composent, peut se faire directement dans la liste ou

à l’aide du masque de saisie. Si la liste comporte un grand nombre d’enregistrements et/ou

de champs, nous vous conseillons d’avoir recours au masque de saisie.

retour au menu

75


Procédez comme suit pour modifier ou supprimer les enregistrements :

• Sélectionnez la liste en cliquant sur n’importe quelle cellule puis activez la

commande Données/Grille.

• L’enregistrement affiché par défaut est le premier de la liste. Activez l’enregistrement

que vous souhaitez modifier.

• Pour modifier un enregistrement, cliquez dans les zones de saisie de votre choix et

entrez les modifications. Pour supprimer un enregistrement, cliquez sur le bouton

homonyme.

• Cliquez sur le bouton Fermer pour quitter la grille.

3. Trier une liste

Dans Excel, vous pouvez trier vos listes par colonnes ou par lignes. Cette fonction offre une plus

grande clarté, permet une recherche plus rapide et une interprétation plus efficace des données.

3.1. L’ordre de tri

Microsoft Excel utilise des ordres de tri spécifiques pour organiser des données en fonction de la valeur, et non pas du format, de ces données.

Pour un tri dans l'ordre croissant, Excel utilise l'ordre suivant. Pour un tri dans l'ordre décroissant, l’ordre de tri est inversé, sauf pour les cellules vides toujours placées à la fin.

• Chiffres Les chiffres sont triés du plus petit nombre négatif au plus grand nombre positif.

• Tri alphanumérique Lorsque vous triez du texte alphanumérique, Excel trie de gauche à droite, caractère par caractère. Ainsi, une cellule contenant par exemple le texte « A100 », sera placée après la cellule contenant l'entrée « A1 » et avant une cellule contenant l'entrée « A11 ».

Les textes courants et ceux contenant des chiffres sont triés dans l'ordre suivant :

0 1 2 3 4 5 6 7 8 9 (espace) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Les apostrophes (') et les traits d'union (-) sont ignorés, avec une exception

toutefois : lorsque deux chaînes de texte ne se différencient que par la présence

d'un trait d'union, la chaîne qui renferme celui-ci est classée en dernier.

• Valeurs logiques Dans les valeurs logiques, FAUX est placée avant VRAI.

76


• Valeurs d'erreur Toutes les valeurs d'erreur sont équivalentes.

• Espaces Les espaces sont toujours placés en dernier.

3.2. Le tri par colonnes

Le tri par colonnes consiste à trier tous les enregistrements d’une première colonne (ici, tri

croissant), puis d’une deuxième colonne (ici, tri croissant) puis d’une troisième colonne (ici, tri

décroissant).

Dans la barre d’outils Standard, vous disposez des boutons Tri croissant et Tri décroissant.

Boutons Tri croissant & tri décroissant

Procédez ainsi pour trier une liste par colonnes à l’aide des boutons Tri croissant et Tri décroissant :

• Sélectionnez la colonne à trier ou l’une des cellules qui la composent

• puis cliquez sur le bouton de tri de votre choix (ici, bouton Tri décroissant).

Tri décroissant de la colonne A

Fenêtre de la commande Données/Trier

3.3. Trier à l’aide de la commande Données/Trier

Les boutons de tri de la barre d’outils Standard ne vous permettent de trier les données que sur

la base d’une colonne.

retour au menu

77


3.3.1. Tri de lignes en fonction du contenu de deux colonnes ou plus

Pour fonder le tri sur 2 ou 3 colonnes, vous devez activer la commande

Données/Trier.

Dans notre exemple, nous trions la liste selon les critères suivants : Première colonne

Code Fournisseur, deuxième colonne Société, troisième colonne Contact.

• Cliquez sur l’une des cellules de la liste puis sélectionnez la commande

Données/Trier.

• Définissez le premier critère de tri : Dans la liste déroulante Trier par,

sélectionnez l’entrée de votre choix (ici, Code Fournisseur). Activez l’option de

tri (ici, Décroissant).

• Procédez de même pour le second critère de tri : Dans la liste déroulante Puis

par (ici, Société). Activez de nouveau l’option de tri (ici, Croissant).

• Cliquez sur le bouton OK.

La liste est triée

3.3.2. Tri de colonnes en fonction du contenu des lignes

• Cliquez sur une cellule dans la liste que vous voulez trier.

• Dans le menu Données, cliquez sur Trier.

• Cliquez sur Options.

• Sous Trier, cliquez sur De la gauche vers la droite, puis sur OK.

retour au menu

78


• Dans les zones Trier par et Puis par, cliquez sur les lignes que vous voulez

trier.

3.3.3. Tri de mois, de jours de la semaine ou de listes personnalisées

• Sélectionnez une cellule ou une plage dans la liste que vous voulez trier.

• Dans le menu Données, cliquez sur Trier.

• Cliquez sur Options.

• Sous Première clé de l'ordre de tri, cliquez sur l'ordre de tri personnalisé souhaité, puis sur OK.

• Cliquez sur toutes les autres options de tri désirées.

Remarques :

L'ordre de tri personnalisé s'applique uniquement à la colonne spécifiée dans la zone Trier par. Pour trier plusieurs colonnes en utilisant un ordre de tri personnalisé, triez chaque colonne séparément. Par exemple, pour trier les colonnes A et B, dans cet ordre, triez d'abord la colonne B, puis spécifiez l'ordre de tri personnalisé à l'aide de la boîte de dialogue Options de

tri. Ensuite, triez la liste dans la colonne A.

Si vous voulez organiser une liste selon un ordre spécifique, par exemple, des données de type organisationnel, vous pouvez les trier à l'aide d'une liste personnalisée pour l'ordre de tri personnalisé.

retour au menu

79


Impress ion sous Excel

1. Définir une zone d’impression

Afin d’expliquer comment définir une zone d’impression sous Excel, nous allons nous appuyer sur une feuille qui nous servira d’exemple. Pour imprimer cette feuille, il existe plusieurs méthodes qui vont permettre de définir une zone d’impression. 1.1 Zone d’impression par défaut

80


Une zone d’impression par défaut à comme dimension 56 lignes par 7 colonnes. Ceci

correspond à une page avec des marges en haut et en bas de 2.5 cm, et des marges à gauche et à

droite de 2 cm.

1.2 Définition de zone d’impression

Il faut tout d’abord sélectionner la zone que l’on veut imprimer, ensuite cliquer sur Fichier, Zone d’impression puis sur Définir. La zone sélectionnée sera alors la zone imprimée.

81


2. L’aperçu avant impression L’aperçu avant impression est possible grâce à deux manières différentes :

Le bouton aperçu avant impression qui se trouve dans la barre des tâches standard.

Par le menu Fichier, Aperçu avant impression. Lors de l’aperçu avant impression la barre d’outil suivante, se charge :

Suiv. (n’est accessible que pour les classeurs contenant plusieurs pages) Permet de passer à la page suivante. Préc. (n’est accessible que pour les classeurs contenant plusieurs pages) Permet de passer à la page précédente.

Zoom Passe de l’un à l’autre des niveaux de zoom (deux niveaux). Imprimer… Ouvre la boîte de dialogue d’impression. Page… Ouvre la boîte de dialogue de la mise en page. (voir section suivante pour plus de détail). Marges Masque ou affiche les marges, ce qui permet ensuite de les modifier à volonté. Aperçu des sauts de page Cliquée, cette option, fait apparaître les pages dans la fenêtre générale d’Excel. Fermer Ferme l’aperçu. Aide Lance l’aide.

82

Documents

Cours Excel