TECHNIQUES EXCEL POUR LA GESTION GEOFFROY MÉNARD, AGROÉCONOMISTE, CETAB+

Combinaison de textes ..................................................................................................................... 1

Figer les volets .................................................................................................................................. 1

Travailler sur plusieurs onglets en même temps .............................................................................. 2

Fonction SIERREUR ........................................................................................................................... 3

Noms de cellules et de plage ............................................................................................................ 3

Fonctions NB.SI ................................................................................................................................. 4

Fonctions SOMME.SI, SOMME.SI.ENS et MOYENNE.SI ..................................................................... 5

Fonctions RECHERCHE (RECHERCHEV. RECHERCHEH) ...................................................................... 6

Validation des données .................................................................................................................... 8

Formattage en tableau ................................................................................................................... 11

Tableaux croisés dynamiques ......................................................................................................... 12

Formattage conditionnel ................................................................................................................ 17

COMBINAISON DE TEXTES

On peut combiner des textes et des nombres avec le &

Par exemple, on pourra afficher un amalgame de valeurs complexes comme ceci :

FIGER LES VOLETS

Figer les « volets » permet de sélectionner quelques lignes d’entête et/ou quelques colonnes à gauche qui resteront

toujours affichées quand on défile dans la feuille.

Pour l’activer, on sélectionne la première case qu’on veut laisser défiler et on fait Affichage > Figer les volets. (Par exemple

si on veut figer 2 lignes et 2 colonnes on sélectionnera C3)

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

2/19

TRAVAILLER SUR PLUSIEURS ONGLETS EN MÊME TEMPS

Il est possible de sélectionner plusieurs feuilles (onglets) et de modifier les mêmes cases (mêmes coordonnées) sur toutes

les feuilles sélectionnées en même temps. Très utile quand, par exemple, on a un même tableau qui se répète sur plusieurs

feuilles, auxquels on doit apporter le même changement.

Pour sélectionner plusieurs feuilles, on tient CTRL et on clique sur de nouvelles feuilles pour les ajouter à la sélection. Ou,

tenir SHIFT et aller chercher une feuille plus loin pour sélectionner toutes les feuilles entre celles sélectionnées. Gare aux

modifications accidentelles quand on a plusieurs feuilles sélectionnées par inadvertance!

On peut aussi aller chercher les données de plusieurs feuilles, par exemple pour faire des sommes.

Par exemple, j’utilise une feuille de temps comme suit, identique dans chaque onglet. Avec la formule

=SOMME('employé 1:employé 3'!C4)

Insérée en C4 et copiée dans tout le reste du tableau, le tableau du total des employés fera la somme de la cellule pour

toutes les feuilles entre la feuille employé1 et la feuille employé 3.

Truc : si vous ajoutez de nouvelles feuilles ENTRE la première et la dernière, elles seront incluses dans les formules de

somme.

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

3/19

FONCTION SIERREUR

Utilité : Éviter d’afficher des messages d’erreur, par exemple lors qu’il y a division par 0

Exemple :

On peut utiliser SIERREUR pour qu’il retourne une case vide au lieu d’une erreur. La syntaxe de cette formule est simple :

SIERREUR(opération; valeur à retourner en cas d’erreur)

NOMS DE CELLULES ET DE PLAGE

Utilité : faciliter le travail avec les données

Fonction : affecter un nom à une cellule ou un ensemble de cellule, qu’on pourra utiliser n’importe où pour y référer au lieu

d’utiliser leurs coordonnées.

Les noms de cellules sont particulièrement pratiques pour les données importantes qui seront reprises dans plusieurs

feuilles à la fois.

On peut ouvrir le gestionnaire de noms (Formules > Gestionnaire de noms) pour voir l’ensemble des noms utilisés dans le

classeur.

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

4/19

https://support.office.com/fr-fr/article/D%C3%A9finir-et-utiliser-des-noms-dans-les-formules-4d0f13ac-53b7-422e-afd2-

abd7ff379c64

FONCTIONS NB.SI

Compte le nombre d’occurance d’un texte ou d’un nombre.

On peut utiliser un test de condition en le mettant entre guillemets, et on peut utiliser le wildcard *

Par exemple, on veut compter le nombre d’occurrence de « champ tunnel » dans ce plan des cultures :

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

5/19

Si on remplace « tunnel » par l’étoile, il comptera toutes les cases qui commencent par "champ ".

FONCTIONS SOMME.SI, SOMME.SI.ENS ET MOYENNE.SI

Ces fonctions permettent d’additionner, à l’intérieur d’une plage, les cellules qui correspondent à certains critères.

Par exemple, j’ai une feuille de temps de travail formattée comme suit :

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

6/19

Je veux savoir le total de temps (somme de la colonne C) que l’employé Mario Martin (condition de la colonne B) consacre à

l’entretien (condition de la colonne E. Je dois utiliser la formule SOMME.SI.ENS dont la syntaxe est la suivante :

SOMME.SI.ENS(plage de la somme, plage du 1er critère, 1er critère, plage du 2e critère, 2e critère, ...)

La formule sera la suivante :

=SOMME.SI.ENS(C:C;B:B;"Mario Martin";E:E;"Entretien")

Documentation sur office.com :

https://support.office.com/fr-fr/article/Fonction-SOMME-SI-169b8c99-c05c-4483-a712-1697a653039b

https://support.office.com/fr-fr/article/SOMME-SI-ENS-SOMME-SI-ENS-fonction-c9e748f5-7ea7-455d-9406-611cebce642b

FONCTIONS RECHERCHE (RECHERCHEV. RECHERCHEH)

Cette fonction sert à rechercher dans un tableau de plusieurs colonnes et plusieurs lignes, la cellule d’une certaine colonne

vis-à-vis la cellule d’une autre colonne.

Par exemple, j’ai un onglet avec ma liste de clients :

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

7/19

Je veux que, dans une autre feuille, je sélectionne le nom de l’entreprise dans une case et les cases voisines aillent

automatiquement chercher le contact, l’adresse et le téléphone correspondant. J’utilisera la fonction RECHERCHEV, dont la

syntaxe est la suivante :

RECHERCHEV (valeur à chercher; plage dans laquelle chercher; colonne de la valeur à retourner;faux)

Donc dans mon autre onglet j’ai la formule suivante :

La formule recherche le terme en I2 (premier argument), soit « Distri-bio » dans la plage des colonnes A à D dans la feuille

ListeClients (deuxième argument); il la trouve à la ligne 2 et retourne le texte de la ligne deuxième colonne (troisième

argument). À noter, le FAUX à la fin sert à désactiver le paramètre « recherche approximative » qui fonctionne mal avec les

données texte.

En appliquant la même formule mais avec les numéros de colonne 3 et 4 pour les cellules Adresse et Téléphone, les trois

champ seront remplis automatiquement selon le client.

Fonctionne très bien combiné avec des listes de validation. Dans cet exemple, on applique une validation de données à la

case de l’entreprise client (I2) et la liste de choix est la colonne des Entreprises de la feuille ListeClients.

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

8/19

VALIDATION DES DONNÉES

Utilité : Faciliter et contrôler l’entrée de données

Onglet Données > Validation des données

On peut s’en servir pour forcer une fourchette de valeur (ex : nombre entre 0 et 1) ou un certain type de données (ex : une

date) dans une cellule. Il peut alors afficher un message d’erreur personnalisé

On peut aussi s’en servir pour offrir des listes déroulantes de choix à saisir.

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

9/19

Une fonctionnalité très pratique est de pouvoir aller chercher une liste dans une feuille de travail au lieu de l’écrire dans les

paramètres des cellules à valider.

Par exemple

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

10/19

On peut ensuite facilement modifier la liste d’option.

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

11/19

On peut aussi combiner ça en affectant un nom de plage à notre liste :

FORMATTAGE EN TABLEAU

Utilité : faciliter le travail de données

Excel permet de « formatter » une plage de cellules en tableau. Cela a quelques effets intéressants :

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

12/19

• Active automatiquement des filtres et tris aux entêtes

• Remplace les entêtes de colonnes Excel (A, B, C etc) par les noms d’entêtes de notre tableau quand on défile vers le

bas :

• Quand on entre une formule dans la première ligne du tableau, la formule sera copiée automatiquement dans

toutes les lignes du tableau

• Les noms des colonnes sont utilisés dans les formules :

Les tableaux ont plusieurs autres fonctions, pour les apprendre visitez la page sur office.com :

https://support.office.com/fr-fr/article/Pr%C3%A9sentation-des-tableaux-Excel-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

TABLEAUX CROISÉS DYNAMIQUES

Utilité : analyse de données

Fonction : assemble automatiquement des tableaux, à partir de données brutes, selon les paramètres établis par

l’utilisateur.

Les données sources doivent être formatées ainsi : chaque ligne correspond à une entrée, et les colonnes sont des

variables. L’entête de la colonne est le nom de la variable, et dans les cases correspondantes, dans chaque ligne, il y a une

valeur pour cette variable.

Exemple :

Dans cet exemple, chaque ligne correspond à la saisie d’une tâche, et à chaque saisie de tâche, ses variables peuvent

prendre différente valeurs :

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

13/19

La variable Nom de l’employé peut prendre les valeurs Mario Martin, Rodrigo Perez ou Guy Gagnon; les valeurs de la

variable Heures de travail sont des nombres, la variable Culture peut prendre les valeurs Laitures, Carottes, etc.

Le tableau croisé dynamique généré calcule le nombre d’apparition des termes ou la somme des nombres, selon les

variables que l’on choisit d’utiliser.

Par exemple, on choisit de présenter, dans notre tableau d’analyse :

• les Activités en lignes ;

• les Cultures en colonnes ;

• la somme des heures de main d’œuvre comme valeur mesurée

Excel génère ce tableau automatiquement à partir des données sources, en compilant pour chaque case, la somme des

valeurs qui comprennent les variables choisies dans la colonne et la ligne correspondante. Par exemple, en additionnant,

dans les données sources les valeurs de la case « heures travaillées » de toutes les lignes qui comprenaient à la fois le terme

« Conditionnement » dans la colonne « Activités » et le terme « Autres cultures » dans la colonne « Cultures », on obtient

12.

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

14/19

Pour créer un tableau croisé dynamique, on place le curseur dans la feuille des données sources et on va dans Insertion /

Tableau croisé dynamique (bouton complètement à gauche)

Une boîte de dialogue apparait qui nous demande de confirmer la source de données (la feuille entière sera sélectionnée

par défaut) et si on veut créer un tableau dans un nouvel onglet ou l’onglet actuel. Le plus simple est de laisser l’option

Nouvel onglet et de faire Ok.

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

15/19

Excel va alors créer un tableau croisé dynamique vide. Il faut spécifier quelles variables on veut analyser. En se servant du

de la liste des champs à droite :

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

16/19

On peut faire un glisser-déposer (drag&drop) des variables que l’on veut analyser dans les cases correspondantes. Par

exemple, pour notre premier exemple en haut :

Note : Il faut bien faire attention à ce qui est compté dans la variable choisir pour les VALEURS. Excel peut parfois compter

le NOMBRE de variables heures au lieu de leur SOMME, il rapportera alors le nombre de tâches saisies plutôt que la somme

des heures qui sont entrées dans la case!!

On peut aisément choisir d’analyser d’autres paramètres, par exemple, nous voulons savoir les tracteurs ont été utilisés

combien de temps, et par qui :

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

17/19

Dans cet exemple, on a choisi d’utiliser les noms d’employés comme ligne, les équipements comme colonnes et les heures

d’utilisation de la machinerie comme variable.

Les tableaux croisés dynamiques sont très puissants. On peut par exemple choisir d’utiliser plusieurs variables comme

lignes, il va alors grouper la deuxième variable choisir, par la première. Par exemple, on décide d’ajouter les cultures

comme variable utilisée pour les lignes, après la variable équipement :

Expérimentez avec les tableaux croisés dynamiques, vous saisirez rapidement la puissance et la flexibilité de l’outil.

L’important est que les données sources soient formatées pour voir être traitées en TCD.

Il est intéressant de savoir que les tableaux de résultats des formulaires google se prêtent très bien à l’analyse par tableau

croisé dynamique. Google spreadsheet est également capable de faire des tableaux croisés dynamiques. Ces derniers sont

appelés « Pivot Tables » dans les interfaces anglais. Les données des classeurs google peuvent également être exportés et

travaillés sur Excel. Les formulaires google et les classeurs peuvent être aisément remplis sur des téléphones intelligents.

L’application google spreadsheet permet également de travailler sur des classeurs hors-ligne, et le classeur se synchronise

avec le nuage une fois la connexion internet réétablie.

Autres ressources

• Créer un tableau croisé dynamique pour analyser des données de feuille de calcul (Microsoft.com)

• Les tableaux croisés dynamiques (Guide du Cégep du Vieux Montréal)

• How to Structure Source Data for a Pivot Table & Unpivot www.excelcampus.com/modeling/structure-pivot-table-

source-data

FORMATTAGE CONDITIONNEL

Le formatage conditionnel permet d’appliquer une mise en forme à des cellules quand elles répondent à certaines

conditions.

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

18/19

Une des fonctionnalités pratiques est d’appliquer un gradient de couleur en fonction des valeurs des cellules. Ceci permet

de rapidement mettre en évidence les chiffres les plus élevés et/ou les plus faibles.

Exemple

La fonction la plus puissante du formatage conditionnel est probablement d’utiliser des fonctions pour formatter les

cellules.

Par exemple, je pourrais appliquer un formatage conditionnel à la cellule J15 dans le tableau suivant pour apparaitre en

rouge si la somme des lignes n’égale pas la somme des colonnes :

Pour utiliser cette fonction il faut bien comprendre l’utilisation des tests de condition dans excel.

Une expression avec un opérateur booléen (=, >, < ou <>) retourne soit VRAI ou FAUX. Ces tests n’ont pas besoin d’être

imbriqués dans un SI(). Par exemple, essayez d’entrer la formule =1>0 dans une cellule, vous verrez que le résultat est VRAI

car 1>0 retourne VRAI. C’est pour cette raison que dans l’exemple ci-haut il n’y a pas de SI dans la formule de mise en forme

conditionnelle donnée en exemple ci-haut – SI ne retourne pas VRAI ou FAUX, elle retourne l’argument 2 si l’argument 1

retourne VRAI et l’argument 3 si l’argument 1 retourne FAUX.

Un autre exemple d’application : dans une facture, on veut éviter que la date ne soit omise alors on encadre la cellule de la

date en rouge tant qu’elle est vide :

Introduction aux tableaux croisés dynamiques Geoffroy Ménard, agr., CETAB+

19/19

Ou encore, on veut mettre en évidence dans une feuille de temps que si une opération culturalle est sélectionnée comme

catégorie d’activité, il faut identifier une culture :

AUTEUR

Geoffroy Ménard, agroéconomiste

CETAB+

819-758-6401, poste 2780

geoffroy.menard@cetab.org