Tableur avance EXCEL

Communauté d’Agglomération de l’Aéroport du Bourget Drancy - Le Bourget - Dugny

Les ateliers informatiques

TABLEUR

avec Excel 2007

Atelier 3 : Tableur avancé

Edition 2010-2011

Médiathèques de la Communauté d’Agglomération de l’Aéroport du Bourget

Atelier TABLEUR 3 2 Edition 2010-2011

I. Les liens hypertexte (hyperliens) 3

A. Créer des hyperliens vers des sites internet 3

B. Créer des hyperliens vers une autre feuille du même classeur 4

C. Créer un hyperlien vers une adresse mél 4

II. Les formats de cellule 4

Format personnalisés 4

III. La mise en forme conditionnelle 5

A. Mettre en place une mise en forme conditionnelle 5

B. Supprimer ou modifier des conditions ou des règles de mise en forme 7

IV. Les formules 9

A. Rappels 9

1. Créer des formules 9 2. Priorité des opérations dans la formule 9 3. Syntaxe 9

B. Les références absolues 10

V. Les liaisons entre feuilles et classeurs 11

A. Créer des liens 11

B. Les liaisons entre classeurs 12

VI. Les bases de données 12

A. Créer une base de données 13

B. Les points à respecter 13

C. Insérer ou modifier des noms de champs 14

D. Trier les données 14

E. Filtrer les données 16

1. Filtres automatiques 16

2. Filtres avancés (élaborés) 18 a) Filtrer les données sur place 18 b) Extraire les données vers un autre emplacement 20



Cet atelier a pour objectif d’aborder quelques notions et fonctions plus avancées du tableur Excel.

I. Les liens hypertexte (hyperliens)

Excel permet d’insérer, dans ses feuilles de calcul, différents types de liens permettant d’accéder

rapidement à des pages internet, des feuilles de calcul ou des adresses de messagerie électronique.

A. Créer des hyperliens vers des sites internet

Excel (à partir de la version 97) s'est ouvert à Internet en permettant de lier des adresses de sites ou

de pages internet à une feuille de calcul. Ceci lance automatiquement le navigateur installé et fait

une requête pour la page web liée.

En activant le bouton , dans l’onglet insertion vous ouvrez la boîte de dialogue Insérer un lien

hypertexte.

Elle permet de créer un lien vers un fichier ou une page Web existant(e), Dans notre exemple nous

avons créé un lien vers la page d'accueil du Portail des Médiathèques.

Voici le résultat après avoir confirmé notre lien.

Le lien apparaît en bleu souligné.



B. Créer des hyperliens vers une autre feuille du même classeur

Pour créer un lien vers une autre feuille d'un même classeur il faut choisir l'option Emplacement

dans ce document à gauche dans le menu après Insertion de lien. Choisissez Référence de cellule

pour indiquer la feuille et la cellule que vous aimeriez lier.

C. Créer un hyperlien vers une adresse mél

Pour de créer un lien hypertexte vers une adresse de messagerie électronique, il faut choisir adresse

de messagerie. Il est possible de préciser l’objet du message.

Voici le résultat du lien vers un mél :

Cette fonction ne fonctionne que si vous disposez d’un logiciel de messagerie activé et paramétré sur

votre ordinateur. Elle ne fonctionne pas si vous n’accédez à votre boîte mél que par le biais du site

internet de l’hébergeur de celle-ci (webmail).

II. Les formats de cellule

Format personnalisés

Excel possède de nombreux formats prédéfinis pour l’affichage de certaines données comme les

dates, les n° de tel, etc. Si l’affichage par défaut ne vous convient pas vous pouvez en choisir un autre

parmi les formats prédéfinis ou créer vos propres formats personnalisés.

Onglet Accueil – Cellules ► Format ► Format de cellule

Les formats disponibles sont classés par rubrique (colonne de gauche).

Choisissez un format proche de celui qu’on veut obtenir puis cliquez dans la catégorie personnalisée

en bas de liste. Modifiez votre format comme désiré. Un aperçu est disponible au-dessus de la case

de saisie si vous avez sélectionné auparavant une cellule.

Ex : date Le 02/01/2010 : jjjj j mmmm aaaa = samedi 2 janvier 2010

Code

format affichage

Code

format affichage

Code

format affichage

j 2 m 1

jj 02 mm 01 aa 10

jjj sam mmm janv

jjjj samedi mmmm janvier aaaa 2010

Les formats de n° de téléphone, de n° de sécurité sociale, et de code postal se trouvent dans la

catégorie Spécial.



III. La mise en forme conditionnelle

Une option très pratique de l’onglet Accueil est la mise en forme conditionnelle. Elle permet de faire

adopter un format particulier à des chiffres, des cellules dès qu'ils remplissent une condition

déterminée.

A. Mettre en place une mise en forme conditionnelle

Pour activer une mise en forme conditionnelle il faut d'abord sélectionner les cellules concernées et

aller dans l’onglet Accueil ► Style ► Mise en forme conditionnelle

La liste de menus suivante apparaît :

Vous pouvez définir des mises en forme en fonction de valeurs contenues dans des cellules ou par

rapport à des formules.

Ce menu permet aussi de gérer et de supprimer les règles de mise en forme conditionnelles

appliquées au document.

Pour les Règles de mise en surbrillance des cellules et Règles des valeurs plus/moins élevées, une

liste de conditions (ou règles) apparaît (ex : Supérieur à…).



Une fois la condition choisie, cette boîte de dialogue apparaît :

Elle permet de définir une valeur de référence pour laquelle la condition est vraie (1ère case). Puis de

choisir parmi les formats de mise en forme proposés (2ème case : menu déroulant).

Si aucun format prédéfini ne vous convient, il est possible de créer votre propre mise en forme en

choisissant : format personnalisé…

La boîte de dialogue Format de cellule s'ouvre et vous pouvez procéder à la mise en forme de la

Police, des Bordures et du Remplissage à votre convenance.

Il est possible de définir plusieurs conditions (ou règles) de mise en formes se rapportant aux

valeurs contenues dans une même plage de cellules. Il suffit de répéter la manipulation autant de

fois que de conditions souhaitées.

Les menus Barres de données, Nuances de couleurs et Jeux d’icônes offres d’autres formats de

mises en forme prédéfinis.



B. Supprimer ou modifier des conditions ou des règles de mise en forme

Pour supprimer les règles : aller dans l’onglet Accueil ► Mise en forme conditionnelle ► Effacer les

règles.

Effacer les règles de la feuille entière : supprime toutes les mises en forme conditionnelles

de la feuille.

Effacer les règles des cellules sélectionnées : supprime toutes les mises en forme

conditionnelles des cellules sélectionnées au préalable.

Pour modifier ou ne supprimer que certaines règles (ou conditions) : aller dans l’onglet Accueil ►

Mise en forme conditionnelle ► Gérer les règles.

Le gestionnaire des règles de mise en forme conditionnelle s’ouvre.

Dans le premier menu déroulant vous pouvez choisir afficher les règles de mise en forme pour :

- Sélection actuelle : les cellules sélectionnées,

- Cette feuille de calcul : toute la feuille Excel qui contient le tableau,

- Feuille : une autre feuille du classeur Excel.



Voici des exemples avec une mise en forme en fonction des valeurs contenues dans

des cellules :

Condition : Signaler par une mise en forme particulière si le Stock disponible d'un produit

descend en-dessous de 100 unités, s’il est supérieur à 1000 ou bien s’il se situe entre ses

deux valeurs.

Colonnes Stock disponible après la mise en forme conditionnelle :

Dans la colonne E (règle de surbrillance des cellules ► Format personnalisé) :

40 = rouge gras, 1500 = vert gras et 90 = rouge gras.

Dans la colonne F (règle de surbrillance des cellules) :

40 = remplissage rouge clair avec texte rouge foncé, 1500 = remplissage vert clair avec texte

vert foncé vert, 850 = remplissage jaune clair avec texte jaune foncé et 90 = remplissage

rouge clair avec texte rouge foncé.

Dans la colonne G (jeux d’icônes) :

40 = flèche rouge vers le bas, 1500 = flèche verte vers le haut, 850 = flèche jaune remontante

et 90 = Flèche rouge vers le bas.

Dans la colonne H (nuances de couleurs) :

40 = remplissage rouge foncé, 1500 = remplissage vert foncé, 850 = remplissage vert clair et

90 = remplissage rouge foncé.



IV. Les formules

A. Rappels

1. Créer des formules

Sélectionnez la cellule qui va contenir le résultat.

Pour indiquer à Excel qu'il s'agit d'une formule, il faut commencer la saisie par

le signe = .

Dans une formule vous pouvez utiliser des références de cellules ou des constantes (chiffres).

L'utilisation de références rend les formules dynamiques, c’est-à-dire que le résultat se met à

jour automatiquement dès qu'on change le contenu des cellules représentées dans la

formule.

L'utilisation de références permet aussi la recopie de formules pour le calcul d'autres

données.

2. Priorité des opérations dans la formule

Excel calcule les opérations dans l'ordre de priorité décroissant (des plus prioritaires aux moins

prioritaires) suivant :

les opérations entre parenthèses

l'exponentiation

la multiplication et la division

l'addition et la soustraction

Pour valider la formule, taper sur la touche Entrée.

3. Syntaxe

Toujours commencer par = indique à Excel qu’on va saisir une formule.

( ) indique à Excel le calcul à faire en priorité.

: pour sélectionner une plage de cellules contigües

ex : SOMME(A3:A5) = A3 + A4 + A5

"" pour insérer une chaîne de texte dans une formule.

; pour séparer les arguments d’une formule.

ex : fonction SI

syntaxe : SI(test logique;valeur si vrai;valeur si faux)

SI(A10<101; "Cadre dans le budget";"Dépasse le budget")



Référence

ABSOLUE

B. Les références absolues

Excel permet de recopier facilement des formules. En recopiant la formule, Excel tient compte du

déplacement effectué et adapte les références de la formule pour tenir compte des nouvelles

données.

Parfois, il est nécessaire qu'Excel ne modifie pas une ou plusieurs références contenues dans la

formule. Dans ce cas, il faut transformer les références en références absolues.

Exemple : Une table pour calculer les montants équivalents en USD (Dollar américain) à partir de

montants en EUROS.

Le taux de change se trouve dans une cellule unique C4 (1,3718).

Pour le calcul des montants en USD correspondants il suffit de multiplier chaque

montant en EURO par le taux de change.

Pour recopier la formule il sera nécessaire de transformer la référence C4 (taux de change) en

référence absolue.

Pour le faire, cliquez dans la formule sur la référence C4 et utilisez la touche de Fonction F4. Ce qui

donne $C$4.

La touche F4 transforme d'abord une référence relative en référence absolue. En appuyant encore

sur la touche F4, la référence se transforme ensuite en références mixtes (colonnes ou lignes

absolues) et finalement de nouveau en référence relative.

Voici les différentes possibilités :

C4 = référence relative

$C$4 = Référence absolue (Constante)

C$4 = Référence mixte (ligne constante)

$C4 = Référence mixte (colonne constante)

Références

RELATIVES



V. Les liaisons entre feuilles et classeurs

Une des particularités d'une feuille de calcul est celle de pouvoir insérer dans une cellule une

référence qui pointe vers une donnée située dans une autre feuille ou dans un autre classeur

(référence externe).

Il est également possible d'intégrer dans une formule une référence externe. On parle alors de liens

ou liaisons entre feuilles ou classeurs.

Ces liens permettent de relier un certain nombre de documents (feuilles de calcul, graphiques, bases

de données, etc.).

A. Créer des liens

En fait, chaque fois que vous créez une formule en y insérant des références, vous établissez des liens

entre différentes cellules. Mais on ne parle pas de liens en tant que tels, car les différentes

références se trouvent sur la même feuille.

Lorsque vous commencez une formule (avec le signe =) et que vous pointez sur une cellule se situant

dans une autre feuille (en sélectionnant la feuille avec l'onglet), le lien est crée automatiquement.

La différence constatée dans la formule, est l'apparition du nom de la feuille qui à été liée (feuille

source) à côté de la référence.

Exemple : =B9*Taux de change!$B$4.

Cette formule est un lien entre deux feuilles d’un même classeur Excel.

Dans ce classeur il y a 2 feuilles : Table de change et Taux de change.

Dans la feuille Taux de change il y a un tableau contenant le taux dans la cellule B4.

Dans la feuille Table de change nous avons crée une formule en utilisant directement le taux

contenu dans la cellule B4 (transformée en référence absolue $B$4) qui se trouve dans la feuille Taux

de change.



L'image suivante illustre la formule.

La feuille Taux de change est dans ce cas la feuille source et la feuille Table de change est la feuille

destination.

B. Les liaisons entre classeurs

Vous pouvez également lier des données qui se trouvent dans des classeurs différents. Le principe

pour la création d'une liaison entre classeurs et le même que pour les feuilles. Vous pouvez voir les

liaisons actives de votre fichier dans Connexions existantes... dans l’onglet Données.

Cette commande ouvre une boîte de dialogue qui permet d'ouvrir le fichier source, de modifier la

source de la liaison ou de la mettre à jour.

Faites attention lorsque vous déplacez un classeur Excel contenant des liaisons sur un autre

support, pour que les liaisons ne soient pas perdues, il faudra également déplacer tous les fichiers

sources correspondants.

VI. Les bases de données

Excel n’est pas seulement un tableur, il peut être également utilisé pour créer et gérer des bases de données ou Tables (listes d’adresses de clients, des inventaires de produits etc.). Il est capable de gérer et organiser des masses considérables de données car les limites ne sont déterminées que par la largeur et la longueur des feuilles de calcul.



A. Créer une base de données

La création d'une base de données sous Excel est très simple, il suffit de créer un tableau en insérant

les données en lignes et colonnes.

Chaque colonne contiendra les données se référant au même critère (ex : tous les noms dans la

même colonne, tous les prénoms dans la colonne suivante etc.).

Plus les données sont détaillées, plus la base de données pourra être exploitée ultérieurement.

Voici un tableau contenant une liste d'adresses :

Titre Nom Prénom Rue Code Postal Ville

Monsieur Depardieu Gérard Rue des Mousquetaires 30 8000 Versailles

Madame Roberts Julia Av. Des jolies filles 10 1203 Beverly Hills

Madame Bullock Sandra Blvd du Net 23 5000 Cyberia

Monsieur Pitt Brad Rue des Singes 12 6500 Los Angeles

Pour utiliser Excel comme base de données il suffit de respecter quelques critères lors de la saisie des

données.

B. Les points à respecter

Libellés des colonnes (Noms de champs).

La première ligne contient pour chaque colonne les noms de champs (Titre, Nom, Prénom,

etc.) Excel utilise la première ligne du tableau comme libellé pour chaque colonne.

Chaque ligne du tableau représente un enregistrement.

A partir de la deuxième ligne du tableau, chaque ligne doit contenir les différentes données

par rapport à une même entité. Ex : Nom, Prénom, Adresse etc. pour une personne. Ces

lignes représentent les enregistrements.

Chaque cellule (Champ) contient les données relatives aux colonnes.

Le contenu de chaque cellule d’une base de données contient les données respectives de

chaque entité par rapport aux libellés (Noms de champ) des différentes colonnes.

Pour créer votre base de données, vous pouvez aussi copier des listes de données en provenance

de Word :

Copier les données dans le document Word puis les coller dans une feuille Excel. Cela fonctionne très

bien si les champs de la liste dans le document Word sont séparés par des tabulations.



Ex :

Madame Bellucci Monica 100 Rue de la Beauté 2365 Rome

Madame Bullock Sandra 23 Blvd du Net 5000 Cyberia

Monsieur Depardieu Gérard 30 Rue des Mousquetaires 8000 Versailles

Monsieur Di Caprio Léonardo 1912 Rue du Port 8500 Titania

Monsieur Hanks Tom 1 Chemin de la course 6200 Forest

Madame Moore Demi 10 Rue des Juges 4000 New York

Il ne reste plus qu’à ajouter la ligne contenant les noms de champs (colonnes), et à ajuster la taille

des colonnes pour obtenir le tableau précédent.

Une fois les données organisées par colonne, et si la base de données comporte beaucoup

d’enregistrements (lignes), Excel offre la possibilité de vérifier que celle-ci ne comporte pas de

doublons. Sélectionnez le tableau entier puis, dans l’onglet Données, choisissez Supprimer les

doublons.

C. Insérer ou modifier des noms de champs

Pour insérer un nouveau Nom de champs il suffit simplement d'insérer une colonne supplémentaire

à l'endroit voulu et d'y saisir le nom. La modification d'un nom de champs se fait par simple

modification du nom dans la cellule.

D. Trier les données

Avec Excel il est très facile de trier toutes les données par rapport à n'importe quelle colonne.

Pour trier par rapport à une colonne déterminée il suffit de cliquer sur le titre de la colonne et de

cliquer sur les boutons (tri croissant) (tri décroissant) dans l’onglet Données.

Attention ! Ne sélectionnez pas une partie du tableau sinon vous risquez de mélanger les données.

Excel vous avertit dans le cas où vous auriez sélectionné une plage de données.

http://www.excel-online.net/excel2003/bd.html#Trier



Vous pouvez aussi utiliser, de la même façon, la commande Trier dans l’onglet Données.

Après avoir sélectionné une cellule du tableau ou tout le tableau cliquez sur Données…/Trier...

Excel sélectionne par défaut tout le tableau sauf la première ligne qui contient les titres des

colonnes.

Dans la boîte de dialogue Trier il est possible de trier d'abord par rapport à un champ principal

(exemple Code Postal) et ensuite Ajouter un niveau de tri supplémentaire.

Ex : base triée par Code Postal puis dans chaque code postal, par Nom (niveau de tri supplémentaire)

puis dans chaque Nom, par prénom (niveau de tri supplémentaire).

Ceci s'avère très utile lorsque la base de données contient plusieurs fois le même Code Postal et le

même Nom.

Le choix entre du plus petit au plus grand ou du plus grand au plus petit permet de trier les données

numériques par ordre croissant ou décroissant.

Le choix entre de A à Z ou de Z à A permet de trier les données de type texte par ordre alphabétique

ou inverse.

Dans les options de tri, vous pouvez aussi demander de trier de gauche vers la droite en fonction de

l'orientation de votre tableau.



E. Filtrer les données

1. Filtres automatiques

Excel permet de filtrer facilement les données, c’est-à-dire de n’afficher temporairement qu’une

partie des données selon les critères de votre choix, avec possibilité d’afficher ensuite une autre

sélection ou tout le tableau.

Cliquez dans une cellule de la base de données et choisissez dans l‘onglet Données/Trier et filtrer la

commande Filtrer.

De nouveaux boutons apparaissent alors dans la partie droite des cellules contenant les noms de

champs (titres des colonnes).

Cliquez sur un de ces boutons. Une liste déroulante

s’affiche, qui répertorie les différentes valeurs présentes

dans la colonne en question.

Pour faire apparaître uniquement les données recherchées

il suffit alors de cocher sur la valeur correspondante dans

cette liste.

Par exemple : pour faire figurer sur la liste seulement les

dames, cliquer sur Madame dans la liste du Filtre sur la

colonne Titre.

Les lignes contenant les enregistrements des Messieurs sont

alors masquées.



Notez la coloration des numéros des lignes en bleu ainsi qu’un changement dans l’aspect du bouton du filtre.

Pour revenir à l’affichage initial du tableau, il suffit de cliquer une nouvelle fois sur

Cela désactive la fonction filtrer, donc tous les filtres appliqués au tableau.

Il est possible d’appliquer plusieurs filtres successifs, pour cela il suffit de cliquer sur le bouton filtre

d’un autre champ.

Ex : Pour n’afficher que les dames dont le nom commence par la lettre B.

Cliquer sur le bouton filtre, choisir le menu filtres textuels puis filtre personnalisé…



Ce filtre s’ajoute au premier et donne l’affichage suivant :

Pour annuler seulement un filtre sur plusieurs activer : cliquer sur le bouton du filtre à annuler puis

Effacer le filtre de…

2. Filtres avancés (élaborés)

Il est très facile de filtrer des données à l'aide des Filtres automatiques. Cependant il se peut que le

nombre de critères pour filtrer les données soit trop important pour obtenir les filtres voulus avec les

Filtres automatiques, il faudra donc recourir aux filtres avancés.

a) Filtrer les données sur place

Pour utiliser un filtre avancé il faut d'abord créer une zone de critères personnalisée.

Il s'agit simplement de reproduire sur une partie libre de la feuille les Noms de champs en prévoyant

en-dessous une ou plusieurs lignes de contraintes. Dans les lignes de contraintes, insérer les critères

ou contraintes pour filtrer la liste des données.

Dans l'exemple suivant nous aimerions filtrer notre liste selon les critères :

Titre = Monsieur; Nom commençants par D ou P ou S.



Ceci nous donne la zone de critères suivante :

Dés que la zone de critères est définie et qu'elle contient les critères voulus, cliquer dans une cellule

du tableau puis sur la commande avancé dans l’onglet Données/Trier et filtrer.

La base de données est sélectionnée automatiquement et une boîte de dialogue s’ouvre :

La rubrique Plages : contient la sélection de la base de

données.

Il faut maintenant cliquer dans la case Zone de critères et

sélectionner sur la feuille toute la Zone de critères.

Cliquer sur l'icône de réduction pour réduire la boîte de

dialogue afin de mieux accéder aux données :

Sélectionner la zone de critères en faisant glisser la souris puis valider avec la touche Entrée. La Zone

de critères contient maintenant les références des cellules sélectionnées.

En laissant l'option Filtrer la liste sur place les lignes ne correspondant pas aux critères de filtrages

seront masquées comme avec le filtre automatique.



b) Extraire les données vers un autre emplacement

A l'aide du filtre avancé il est aussi possible d'extraire les données filtrées et de les copier sur un

autre emplacement de la feuille.

A cet effet il faudra prévoir aussi, en plus de la zone de critères, une zone de destination (ou

d'extraction).

Comme pour la zone de critères il suffit de copier les noms de champs correspondant aux rubriques

qu'on aimerait extraire.

Par exemple :

Nous aimerions extraire de notre liste tous les noms et prénoms des Messieurs et les copier ailleurs

sur la feuille.

La procédure est similaire à celle du filtrage sur place. Après avoir sélectionné la zone de critères il

faudra choisir l'option Copier vers un autre emplacement et sélectionner la zone d'extraction.

Les données filtrées seront copiées dans la zone de destination selon les critères choisis :

Pour finir, sachez que vous pouvez trouver beaucoup de trucs, astuces, réponses à vos questions sur

Excel grâce à un moteur de recherche comme Google, Bing…

Education

Tableur avance EXCEL