Fonctionnalités avancées dans Excel

1. Différentes parties d’une formule Excel

Une formule peut contenir tout ou partie des éléments suivants : des fonctions,
des références, des opérateurs et des constantes.

1. Fonctions : la fonction PI() renvoie la valeur pi : 3,142...

2. Références : A2 renvoie la valeur de la cellule A2.
3. Constantes : valeurs numériques ou de texte entrées directement dans une formule (par
exemple, 2).
4. Opérateurs : l’opérateur ^ (accent circonflexe) élève un nombre à une puissance et
l’opérateur * (astérisque) le multiplie.
2. Syntaxe d’une fonction Excel
L’exemple suivant de la fonction ROUND arrondissant un nombre dans la cellule A10
illustre la syntaxe d’une fonction.

1. Structure : La structure d’une fonction débute par un signe égal (=), suivi du nom de la
fonction, d’une parenthèse ouvrante, des arguments de la fonction séparés par des points-
virgules et d’une parenthèse fermante.
2. Nom de la fonction : Pour obtenir la liste des fonctions disponibles, cliquez sur une
cellule et appuyez sur Maj+F3 pour lancer la boîte de dialogue Insérer une fonction .
3. Arguments : Les arguments peuvent être des nombres, du texte, des valeurs logiques
telles que TRUE ou FALSE, des tableaux, des valeurs d’erreur telles que #N/A ou des
références de cellule. L’argument que vous désignez doit produire une valeur valide pour cet
argument. Les arguments peuvent également être des constantes, des formules ou d’autres
fonctions.
4. Info-bulle d’argument : Une info-bulle contient la syntaxe et les arguments, et s’affiche
lorsque vous tapez la fonction. Par exemple, tapez =ARRONDI( et l’info-bulle s’affiche. Les
info-bulles s’affichent uniquement pour les fonctions intégrées.
3. Les opérateurs dans les fonctions Excel :
Les opérateurs indiquent le type de calcul que vous voulez effectuer sur les éléments d’une
formule.
Il existe quatre types différents d’opérateurs de calcul :
arithmétique, comparaison, concaténation de texte et référence.
 Opérateurs arithmétiques
Opérateur arithmétique Signification Exemple

+ (signe plus) Addition =3+3

– (signe moins) Soustraction =3–3

Négation =-3

* (astérisque) Multiplication =3*3

 Opérateur arithmétique Signification Exemple

/ (barre oblique) Division =3/3

% (signe pourcentage) Pourcentage +3096

^ (signe insertion) Exposant =3^3

 Opérateurs de comparaison
Vous pouvez comparer deux valeurs avec les opérateurs ci-dessous. Le résultat obtenu
est une valeur logique : VRAI ou FAUX.
Opérateur de comparaison Signification Exemple

= (signe égal) Égal à =A1=B1

> (signe supérieur à) Supérieur à =A1>B1

< (signe inférieur à) Inférieur à =A1<B1

>= (signe supérieur ou égal à) Supérieur ou égal à =A1>=B1

<= (signe inférieur ou égal à) Inférieur ou égal à =A1<=B1

<> (signe différent) Différent de =A1<>B1

 Opérateur de concaténation de texte

Utilisez le signe & pour concaténer (joindre) une ou plusieurs chaînes de texte afin
d’obtenir un seul élément de texte.
Opérateur de texte Signification Exemple

& (et commercial) Lie, ou concatène, deux valeurs pour produire une =A1 & B1
valeur de texte continu

 Opérateurs de référence
Pour référencier une cellule ou une plage de cellules

4. Utilisation de références dans les formules Excel

Une référence identifie une cellule ou une plage de cellules d’une feuille de calcul et indique
à Excel où trouver les valeurs ou les données à utiliser dans une formule.
Par défaut, Excel utilise le style de référence A1 qui fait référence aux colonnes contenant
des lettres et aux lignes contenant des numéros. Ces lettres et numéros sont appelés en-têtes
de ligne et de colonne. Pour faire référence à une cellule, entrez la lettre de la colonne suivie
du numéro de la ligne. Par exemple, B2 fait référence à la cellule située à l’intersection de la
colonne B et de la ligne 2.
Pour faire référence à Utilisez

La cellule de la colonne A et de la ligne 10 A10

La plage de cellules de la colonne A et des lignes 10 à 20 A10:A20

La plage de cellules de la ligne 15 et des colonnes B à E B15:E15

Toutes les cellules de la ligne 5 5:5

Toutes les cellules de la colonne H H:H

Toutes les cellules des colonnes H à J H:J

La plage de cellules des colonnes A à E et des lignes 10 à 20 A10:E20

5. Les erreurs dans les formules :

6. Liaison entre feuilles de calcul :

On veut consolider les ventes et les achats des deux usines (usine1,usine2) dans une feuille
nommée synthese
Usine1 Usine2

Synthese

=SOMME(Usine1!C2;Usine2!C2)

7. Utilisation de listes de données :

Tout tableau contenant une série d’informations, peut être utilisé comme une base de
données afin d’en extraire des informations et des statistiques. Les informations pourront être
extraites à l’aide des fonctions de recherche ou bien à l’aide de filtres ou de formules de
calcul appropriées. La seule contrainte, pour pouvoir utiliser les filtres, est de donner un
format différent à la première ligne du tableau. Cette ligne sera ainsi reconnue
automatiquement comme contenant les noms de champs (ou en-têtes de colonnes).

Pour faciliter la gestion de vos données, mettez automatiquement en forme votre tableau en
choisissant un des formats proposés :
 Sélectionnez une cellule de votre tableau,
 Dans le ruban "Accueil" utilisez l’outil correspondant.
  Mettre la liste à jour
Pour ajouter ou supprimer des fiches supplémentaires dans une liste, vous pouvez
procéder de deux manières :
• Saisir directement une nouvelle fiche directement dans le tableau selon la
méthode classique de saisie sur Excel.
• Saisir par l’intermédiaire d’un outil Formulaire.

L’onglet Formulaire n’est pas présent par défaut dans Excel 2007. Il faut l’ajouter, pour cela :
 Cliquez sur la barre d’outils Accès rapide
 Cliquez sur autres commandes
 Cherchez l'icône "Formulaire"
 Cliquez dessus puis sur le bouton "Ajouter".
 Filtrer la liste en choisissant des critères
Vous obtenez à droite de chaque nom de champ une flèche ouvrant un menu
déroulant permettant de choisir des critères de tri ou de sélection des données :
8. Les Macro commandes :
Certaines opérations longues et répétitives que vous pourriez avoir à faire sur une feuille
de calcul peuvent être automatisées. Cette automatisation passe par la construction de
macro commandes. Une fois ces macros commandes réalisées, vous demanderez à Excel
de les exécuter à chaque fois que nécessaire.
« Une macro commande est constitué par l'enregistrement d'une séquence d'actions
habituellement réalisées au clavier ou à la souris. Pour construire la macro commande
vous allez demander à Excel de mémoriser un certain nombre d'actions ».
Nous désirons trier la liste des employés précédemment saisie en fonctions de différents
critères (alphabétique, salaire). Ces différents tris sont longs et fastidieux, pour cela il est
utile d’utiliser des macros commandes que nous déclencherons au moment voulu.
Nous allons maintenant effectuer toutes les actions qui seront ensuite exécutées
automatiquement par la macro.
Exemple :
Trier l’ensemble des employés par ordre alphabétique selon le nom
 Cliquer sur le menu AFFICHAGE, puis sur option Macros

 Choisissez un nom de la macro, puis filtrer selon le nom

 Une fois que vous avez finir l’opération de tri, arrêter l’enregistrement
 9. Recherche dans un tableau :

Utilisez la fonction RECHERCHEV pour rechercher une valeur dans une table.
RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;[valeur_proche])

Argument Description

valeur_cherchée (obligatoire) Valeur que vous voulez rechercher. La valeur que vous
voulez rechercher doit apparaître dans la première colonne
de la plage de cellules que vous spécifiez dans
l’argument table_matrice.
Par exemple, si l’argument table_matrice englobe les
cellules de la plage B2:D7, l’argument valeur recherchée
doit se trouver dans la colonne B.

table_matrice (obligatoire) Plage de cellules dans laquelle la fonction RECHERCHEV

recherche l’argument valeur_cherchée .
La première colonne de la plage de cellules doit contenir
l’argument valeur_cherchée.
La plage de cellules doit également contenir la valeur de
renvoi que vous recherchez.

no_index_col (obligatoire) Numéro de colonne (la colonne la plus à gauche

du table_matrice correspondant à 1) qui contient la valeur
de renvoi.

valeur_proche (facultatif) Représente une valeur logique indiquant si vous souhaitez

que la fonction RECHERCHEV recherche une
correspondance exacte ou approximative :
 Correspondance approximative : 1/VRAI suppose
que la première colonne de la table est triée par ordre
alphabétique ou numérique et recherche dès lors la
valeur la plus proche. Cette méthode est appliquée par
défaut si vous n’en spécifiez pas d’autre. Par exemple,
=RECHERCHEV(90,A1:B100,2,VRAI).
 Correspondance exacte : 0/FAUX recherche la valeur
exacte dans la première colonne. Par exemple,
=RECHERCHEV(« Smith »,A1:B100,2,FAUX).
 10. Validation de la saisie :
Excel dispose d’un outil de validation de la saisie qui permet de placer des contraintes
(éventuellement allant jusqu’à l’interdiction de toute saisie) sur des cellules « stratégiques »
de votre feuille, en affichant le cas échéant un message d’alerte, fondé sur des conditions ou
le résultat de formules.
 [Link] ne forme conditionnelle :
La mise en forme conditionnelle est une puissante fonction proposée par Excel. Comme son
nom l'indique, elle consiste à appliquer une mise en forme particulière à une sélection en
fonction d'une ou plusieurs conditions.

12. Tableaux croisés dynamiques (TCD) :

Un tableau croisé dynamique permet de combiner et de comparer des données, pour mieux
les analyser.
Croisé : toute donnée dépend des étiquettes de sa ligne et de sa colonne.
Dynamique : un tableau croisé dynamique est évolutif, facilement modifiable. Il permet
d’examiner les données sous des angles différents.

« Le tableau croisé dynamique (ou TCD) permet de synthétiser les données d'une base de
données de façon à obtenir les "croisements" de données souhaités ».
  La création d’un tableau croisé dynamique s’effectue à partir de colonnes de données
 Ces données sources doivent être de même nature au sein d’une même colonne.
 Les colonnes ne doivent contenir ni filtre, ni sous-totaux.
 La première ligne de chaque colonne doit contenir une étiquette, qui correspondra à un
nom de champ dans le tableau croisé dynamique.
Création d’un TCD :

Pour ajouter un TCD, sélectionnez la base de données (avec les en-têtes) et cliquez sur
"Tableau croisé dynamique" depuis l'onglet "Insertion" :

Pour insérer le TCD dans une nouvelle feuille du classeur, laissez les valeurs par défaut et
validez :
Un TCD vide est alors créé :

Cochez ensuite les champs à intégrer au TCD (dans ce cas, tous les champs) :
 [Link] graphiques :

Pour insérer un graphique à partir d’un tableau, commencez par sélectionner le tableau
contenant les données en entier :

A partir de l'onglet "Insertion", vous pouvez ensuite choisir le modèle de graphique souhaité :
Si vous n'êtes pas sûr du type de modèle souhaité, cliquez sur "Graphiques recommandés"
pour afficher des suggestions et des aperçus de différents modèles de graphiques qui peuvent
convenir aux données sélectionnées :

Et validez votre choix :

  Personnaliser Le Graphique

Vous pouvez changer le style de graphique si nécessaire depuis l'onglet "Création" (après
avoir sélectionné le graphique) :

Depuis ce même onglet, vous pouvez également modifier la palette de couleurs du graphique

Vous pouvez aussi déplacer et redimensionner le graphique comme s'il s'agissait d'une
simple forme :
  Personnaliser Les Eléments Du Graphique

Vous pouvez personnaliser chaque élément du graphique en le sélectionnant et en le

modifiant comme s'il s'agissait d'une forme ou d'une zone de texte.

Par exemple, le titre :

Autre exemple, la colonne de la dernière série :

 Ajouter Des Eléments Au Graphique

Vous pouvez ajouter ou retirer certains éléments du graphique en cliquant sur le + à droite du
graphique :