Excel – Fonctions avancées

LA FONCTION LOGIQUE « SI »
Fichier utilisé : livres_données.xls

Notions nouvelles : Fonction SI

Ouvrez le classeur livres_données.xls dans votre dossier de travail et ouvrez-le.

Nous allons calculer dans toutes les cellules de données du Tableau 3 le nombre de livres publiés par million
d’habitants seulement si un chiffre de population apparaît dans le tableau 2.

Pour cela, nous allons utiliser la fonction SI.

Cliquez dans la cellule C21 puis sur le bouton insérez une fonction.

Choisissez la catégorie de fonction : logique, et sélectionnez Si comme nom de fonction.

Cliquez / OK

Champ Test logique :

Ce champ doit contenir une condition. Dans notre exemple, nous chercherons si la cellule concernant le Japon en
1975 est vide. Cela se traduit par l’expression : C12="" (C12 est la cellule concernant la population du Japon en
1975 ; les deux guillemets signifient "vide").

Champ valeur si vrai

On indique ici l'opération à effectuer si la condition est remplie. Dans notre exemple, si la cellule concernant la
population du Japon en 1975 est vide, on n'affichera rien dans la cellule C21. Tapez dans ce champ l'expression :
"".

Champ valeur si faux

On indique ici l'opération à effectuer si la condition n'est pas remplie. Dans notre exemple, si la cellule concernant
la population du Japon en 1975 n'est pas vide, on divise le nombre de livres publiés au Japon en 1975 par la
population du Japon la même année. Tapez dans ce champ l'expression : C3/C12.

Cliquez / OK

Recopiez la formule vers la droite puis vers le bas.

Vous remarquerez que le résultat n'est pas très satisfaisant, puisque le nombre 0 apparaît lorsqu'il manque le
nombre de livres publiés (Tableau 1).

Nous allons corriger ce défaut dans le tableau 4.

Dans les cellules de données de tableau 4, introduisez une formule qui affiche le résultat du tableau 3 quand ce
résultat est différent de 0.

Recopiez la mise en forme du tableau 1 pour les tableaux 3 et 4 si nécessaire.

Enregistrez votre travail et passez à l’exercice suivant.

1 Daniel Fleury, Philippe Rincon

LA FONCTION « RECHERCHEV »
Fichier créé : dépenses.xls
Nouvelles fonctions :

Table de recherche ; formule matricielle ; fonction logique SI ; protection ; liaisons

Ouvrez un nouveau classeur et sauvegardez le sur votre répertoire de travail sous le nom dépenses.

Utilisation d’une table de recherche.

1. Nommez la première feuille oct.2004

Dans cette feuille, créez un état de vos dépenses en informatique en vous conformant au modèle ci-dessous.

Indications :

Titre du tableau : centré sur plusieurs colonnes, police Arial, 24 points, rouge.
Titres des colonnes : texte en gras, centrage vertical et horizontal dans les cellules, fond jaune
Encadrement comme sur le modèle.
Données des colonnes de prix : format monétaire €.

A B C D E
1
Dépenses en informatique
2 Réf. articles quantité prix prix
unitaire HT total HT
3
4
5
6
7
8
9
10 Total
hors taxe
11 Remise
12 TVA 19,60 %

13 Total TTC

Au lieu de saisir les données « nom des articles » et « prix » dans cette feuille, vous les insérerez à l’aide de la
fonction RECHERCHEV. Vous éviterez ainsi saisie répétitive et erreurs.

L’utilisation de cette fonction suppose l’établissement préalable d’une table de recherche (appelée table_matrice)
que nous allons créer maintenant.

2. Création de la table de recherche

Nommez la deuxième feuille Références puis déplacez-la (en la faisant glisser) de façon à la mettre en première
position dans le classeur.

2 Daniel Fleury, Philippe Rincon

 Créez dans cette feuille la liste suivante afin de stocker les données de facturation (articles référencés et prix).

A B C
1 référence nom de l’article prix unitaire HT
2 5P12 imprimante laser brother 376,00
3 5P22 cartouche laser jet HP67098R 46,00
4 5P16 câble imprimante 14,00
5 9L78 Excel pour Windows XP 99,00
6 9L80 Powerpoint pour Windows XP 89,00
7 8A49 Thunderbyte antivirus 90,00
8 1M46 clavier Cherry 102 touches 40,00
9 1M72 Disque dur 200 Go FUJITSU 172,00

Attribuez le nom articles à la plage A2:C9.

Table de recherche (ou table matrice)

Lorsque vous utiliserez la fonction RECHERCHEV, vous utiliserez la plage de cellules nommée
articles comme table de recherche (désignée par Excel sous le terme table_matrice).
Dans une table de recherche, les éléments de la première colonne (ici, le code référence) sont utilisés
comme clés de recherche (désignés sous le terme valeur_cherchée).
Aux éléments de la première colonne (valeur principale) de la table sont associées les valeurs figurant
sur la même ligne. Exemple : à la référence 5P12 sont associés le texte : « imprimante laser brother »
et le prix : 1880,00 figurant sur la même ligne.
La fonction RECHERCHEV
La fonction RECHERCHEV cherche si le contenu de la cellule désignée dans la formule se trouve
dans la première colonne de la table. Si c’est le cas, il est possible reporter tout ou partie des valeurs
qui lui sont associées.
Syntaxe : =RECHERCHEV(valeur_cherchée;table_matrice;n°_index_col;valeur_proche)

3. Calcul des dépenses

Dans la feuille oct.2004, en suivant la Procédure décrite ci-dessous, faites un état de vos dépenses pour le mois
d’octobre, soit :

2 imprimantes (réf. 5P12)

3 cartouches (réf. 5P22)

2 câbles (réf. 5P16)
Procédure :
1. Dans la colonne A, tapez les nos de référence de ces trois articles, dans la colonne C le nombre d’unités.

2. cliquez dans la cellule B3

3. Cliquez / Bouton : fx (insérer une fonction)/ Recherche & Matrices / RECHERCHEV
L’argument valeur_cherchée précise la valeur que vous voulez comparer à celles de la première colonne de
votre table de recherche.
Ici, il s’agit de la référence d’un article. Cliquez sur la cellule contenant cette référence. A3 s’affiche.

3 Daniel Fleury, Philippe Rincon

 Cela signifie que vous recherchez si le contenu de la cellule A3 de la feuille oct.2004 figure dans la première
colonne de la table de recherche (articles).

L’argument table_matrice spécifie la plage de cellules utilisée comme table de recherche (l’endroit où sont
définies toutes les valeurs utilisées).

Ici, il s’agit de articles, nom que vous avez attribué à la plage A2:C9 dans la feuille Références.

L’argument n°_index_colonne indique à quel endroit de la table de recherche se trouve la valeur associée
que l’on souhaite faire figurer (seule la colonne sera indiquée puisque les valeurs associées figurent sur la
même ligne que la valeur principale).
Ici, il s’agit de 2 (deuxième colonne de la table) qui contient les noms des articles.
L’argument valeur_proche indique le niveau de comparaison souhaité. On indique FAUX si l’on ne
souhaite pas utiliser les valeurs proches. On indique VRAI si l’on souhaite étendre la recherche également
aux valeurs proches.
Ici, tapez FAUX pour préciser qu’on ne souhaite utiliser que des valeurs absolument identiques.
4. Vous devez obtenir la formule : = RECHERCHEV($A3;articles ;2 ;faux)

5. Vérifiez le résultat.

6. Copiez la formule vers le bas (jusqu’à la cellule B9). Un message d’erreur s’affiche dans certaines cellules. Essayez
de comprendre pourquoi et comment y remédier ?
Solution :

- redéfinissez le bloc articles de la feuille références en y incluant une ligne supplémentaire (vierge). articles doit
maintenant se référer au bloc A2:C10 ; tapez 0 dans la première colonne de la nouvelle ligne de ce bloc ;

Le message d’erreur qui s’affichait auparavant (#N/A) ne figure plus. En effet, aucun achat n’étant effectué, la
valeur cherchée est égale à 0 (rien). Les valeurs qui lui sont associées figurent sur la ligne 10 de la table de référence.
Elles sont égales à 0 (rien).

NB: Lorsqu’une formule fait référence à une cellule contenant la valeur #N/A, elle renvoie la valeur d’erreur
#N/A.

7. Procédez de la même façon dans la colonne D pour afficher le prix unitaire des articles.

Formule matricielle et fonction SI.

4. Utilisation d’une formule matricielle pour calculer les prix totaux

Pour calculer le prix total HT, plutôt que de saisir la formule « prix unitaire * quantité » et de la copier vous allez le
calculer en une seule fois pour tous les articles en multipliant la colonne prix unitaire par la colonne quantité.
Sélectionnez la plage E3:E9 puis entrez la formule :

=plage quantité * plage prix unitaire HT (par exemple =C3:C9*D3:D9)

en appuyant sur les touches Ctrl +Maj + Entrée une fois la saisie de la formule terminée.

Cette formule multiplie les éléments des deux colonnes ligne par ligne.

Introduisez le calcul du total hors taxes en utilisant le bouton Somme automatique.

5. Utilisation de la fonction logique SI pour calculer la remise

Remise de 10% à partir de 5 00€ d’achat hors taxe.

Cliquez dans la cellule E11

Cliquez / bouton : Assistant fonction » / Logique / SI

Syntaxe : =SI(test_logique;valeur_si_vrai;valeur_si_faux)

4 Daniel Fleury, Philippe Rincon

 Chaque argument est séparé par des points-virgules (;).

test_logique indique la valeur ou l’expression sur laquelle porte le test et dont le résultat peut être VRAI ou
FAUX.
Ici, le test porte sur le nombre affiché dans la dans la cellule E10.
Condition : E10 supérieur ou égal à 500.
valeur_si_vrai valeur ou expression affichée si la réponse au test est VRAI.
Ici, si la réponse au test est VRAI, il faut calculer la remise de 10% de la somme figurant dans la cellule E10
(Exemple : E10*0,1).
valeur_si_faux valeur ou expression affichée si la réponse au test est FAUX.
Ici, si la réponse au test est FAUX le résultat sera égal à 0.
Vous devez obtenir la formule : =SI(E10>=500;E10*0,1;0)
Vérifiez le résultat.

Introduisez la formule de calcul de la TVA à partir du total hors taxe.

Introduisez la formule de calcul du montant TTC en tenant compte du total hors taxe, de la remise et de la
TVA.

Vérifiez, « à la main », que les calculs sont corrects.

6. Protection d’une feuille.

Si vous êtes satisfait(e) du résultat d’ensemble, protégez votre feuille après avoir déverrouillé les cellules devant
pouvoir être modifiées (plages A3:A9 et C3:C9).

1. Sélectionnez les plages A3:A9 et C3:C9 puis déverrouillez-les de la façon suivante :

Clic droit / Format de cellule / Protection, puis décochez la case « verrouillée ».

2. Protégez la feuille : Clic droit sur le nom de la feuille / Protéger la feuille (sans mot de passe)
Vérifiez le résultat. Vous constatez que seules les données de détail (références et prix unitaires) peuvent être
modifiées.

7. Copie d’une feuille dans une autre

Copiez la feuille oct.2004 dans deux nouvelles feuilles que vous nommerez nov.2004 et déc.2004. Si nécessaire,
placez-les dans l’ordre chronologique après oct.2004.

Saisie des données de novembre et décembre

Sélectionnez les deux nouvelles feuilles : cliquez sur l’onglet nov.2004, et en maintenant le doigt appuyé sur la
touche Maj cliquez sur l’onglet déc.2004.

Effacez les données contenues dans les colonnes « Quantité » et « Réf. » . Celles-ci se sont effacées des deux feuilles
sélectionnées.
Enregistrez.

Établissez le bilan de vos achats en informatique pour les mois de novembre et décembre 2004 :

Vous achetez en novembre :

1 PowerPoint
1 Excel
Vous achetez en décembre :
2 Thunderbyte antivirus
1 Disque dur
2 claviers

5 Daniel Fleury, Philippe Rincon

 Enregistrez votre document.

8. Liens entre feuilles (bilan)

Construisez dans une nouvelle feuille (que vous appellerez 4e trim 2004) un tableau faisant l’état des dépenses en
informatique du dernier trimestre 2004.

Titre principal (en A1) : Dépenses en Informatique (octobre à décembre 2004) ;

En-têtes de colonnes (de B2 à E2) : HT, remise, TVA, TTC.

En-têtes de lignes (de A3 à A7) : octobre, novembre, décembre, Total, Moyenne mensuelle ;

Insertion des totaux mensuels :

- cliquez dans la cellule devant contenir le total HT pour le mois d’octobre ;

- tapez = ;

- activez la feuille oct. 2004 ;

- cliquez sur la cellule contenant le total HT ;

- cliquez sur la touche ENTER (la feuille 4e trim. 2004 redevient active).
Répétez la même opération pour les autres données mensuelles.

Sur la ligne Total, introduisez les formules correspondant aux totaux trimestriels au moyen du bouton Somme
automatique.

Introduisez les moyennes mensuelles au moyen de la fonction MOYENNE.

Mise en forme du tableau :

- formats monétaires, 2 décimales ;

- encadrement, titre et en-têtes par format automatique de votre choix.

9. Graphique
Placez dans même feuille un histogramme 3D de l’évolution des dépenses TTC sur les trois derniers mois. Ôtez si
nécessaire les décimales de l’axe des y.

10. Liaison avec Word

Nous allons copier dans un document Word le bilan du trimestre et créer une liaison, de façon que Word puisse
mettre à jour les informations chaque fois qu’elles sont modifiées dans le classeur Excel d’origine.

Sélectionnez le graphique, copiez-le (il est alors placé dans le presse-papiers), activez Word et collez-le au moyen de
la commande :

Cliquez / Édition / Collage spécial...

Revenez dans Excel. Dans la feuille oct. 2004, remplacez 2 imprimantes par 10. Regardez si le graphique s’est
modifié dans le document Word. Si ce n’est pas le cas, c’est que l’option de collage avec liaison n’était pas activée au
moment du collage. Dans ce cas, effacez le graphique du document Word et recommencez en collant avec liaison.

Procédez de même pour le tableau.

Donnez un titre à ce bilan.

Placez votre (vos) nom(s) et prénom(s) dans l’en-tête, alignés à gauche (l’un au dessous de l’autre si vous êtes deux).
Insérez dans le pied de page :
- à gauche le nom du fichier et sa localisation (chemin) :
- alignés à droite, la date et l’heure.

6 Daniel Fleury, Philippe Rincon

 Correction de l’orthographe. Aperçu avant impression.

Enregistrez sous le nom dépenses + votre prénom.xls dans votre dossier de travail.

EXCEL – DOCUMENT LONG

Fichier créé : PLAN+votre prénom.xls (feuille Table + feuille bilan + feuille graphiques)
Fichier utilisé : liste_ens.doc ; FLUX.xls
Nouvelles notions :
Synthèse de données en créant un plan : création automatique, création manuelle
Conversion de Word vers Excel
Filtre automatique
Travailler et organiser les données dans un document long ; figer les titres des lignes et des colonnes, ajuster la
largeur des colonnes, utiliser le zoom, mise en page, etc…

Soit :
une composante de l’Université : le Département de 1er cycle LSH ;
4 disciplines enseignées (Informatique, mathématiques et statistiques pour LSH, Sociologie) extraites de la
table générale des enseignements ;
chaque discipline organise plusieurs enseignements ;
chaque enseignement est identifié par un code ;
des étudiants qui choisissent certains de ces enseignements dans le cadre de leur cursus.
Il conviendra, à partir du flux des inscrits par diplôme et par enseignement, de dresser un tableau de
synthèse organisé en niveaux.

Conversion de texte en tableau et filtre automatique.

1. Liste des enseignements. Conversion de paragraphes Word en données Excel.

Ouvrez le fichier Word « liste_ens.doc », sélectionnez et copiez la liste des enseignements.

Activez Excel et collez la liste dans la 1re feuille de votre classeur Excel en A1. Nommez Table cette feuille.
Sélectionnez la 1re colonne et cliquez sur Convertir dans le menu Données.

Suivez les instructions affichées à l'écran pour indiquer comment diviser le texte en colonnes (code UFR en A, code
enseignement en B, code semestre en C et intitulé en D).
Attention de bien sélectionner l’option de format de données pour chaque colonne sélectionnée (l’en-tête de
colonne sous Aperçu de données affiche le format appliqué). C’est ainsi que vous devrez définir un format texte
pour la colonne « semestre ».

Enregistrez votre fichier dans votre dossier de travail sous le nom PLAN+votre prénom.xls

2. Liste des enseignements. Utilisation d’un filtre.

Vous souhaitez isoler les enseignements du 1er semestre, du 2e semestre etc…et/ou les enseignements organisés par
le Département des sports ?. Vous pouvez utiliser un filtre automatique :
Insérez une ligne au-dessus de la liste et créez les étiquettes des colonnes : code UFR (colonne A), code ens.
(colonne B), sem. (colonne C) et intitulé (colonne D) ;
Pointez une cellule dans la liste à filtrer et utilisez la commande Données / Filtre / Filtre automatique ;
Cliquez sur la flèche à droite de l’étiquette « semestre » et sélectionnez une valeur, par exemple 3 correspondant aux
enseignements qui sont répétés ;
Cliquez sur la flèche à droite de l’étiquette « code UFR » et sélectionnez la valeur 67 (Département des sports).

7 Daniel Fleury, Philippe Rincon

 Cette liste doublement filtrée n’affiche plus que les enseignements répétés (1er et 2e sem.) et organisés par le
Département des sports.
Vous remarquerez que les lignes correspondant aux autres enseignements sont masquées.
Notez également que les flèches des filtres sont bleu dans les colonnes où des valeurs ont été sélectionnées.

3. Complétez le tableau « bilan »

Ouvrez le fichier FLUX.xls. Copiez son contenu dans votre classeur Plan en utilisant la commande « Déplacer ou
copier une feuille » du menu édition

Nommez cette deuxième feuille Bilan.

Attention : ne saisissez pas les données situées dans les plages grisées. Ces données doivent être calculées et
figurent dans le tableau de présentation (affiché page Erreur ! Signet non défini.) uniquement pour vous
permettre de vérifier vos résultats.

Complétez le tableau sans travailler la mise en forme (pour le moment) mais en respectant le positionnement des
données en lignes et en colonnes.
NB : Afin de faciliter la saisie des données et accroître votre confort de travail vous devez très rapidement (quelques
clics suffisent) :
- OBLIGATOIREMENT figer les titres des lignes et des colonnes ;
- Ajuster les colonnes (ajustement automatique) ;
- Utiliser le zoom et/ou modifier la taille de la police, etc…

Utilisez la fonction RECHERCHEV avec 4e argument FAUX pour afficher les intitulés et les codes semestre des
enseignements.(colonnes B et C).

Vous devrez vous-même définir votre table de recherche, que vous nommerez « codes », à partir de la liste dans la
feuille « Table ».

Utilisez la fonction SOMME pour calculer le nombre des inscrits par enseignement (colonne D) ainsi que par
discipline et par diplôme (lignes 13, 17, 22, 37).

Mode plan.

4. Création d’un plan pour faire la synthèse des données.

En créant un plan dans une feuille de calcul, vous pouvez hiérarchiser vos informations de façon à afficher ou
masquer autant de niveaux de données que vous le souhaitez.

On peut attribuer des niveaux de plan à des lignes mais aussi à des colonnes.
Pour créer un niveau on regroupe des données auxquelles on associe une ligne (ou une colonne) de synthèse. Dans
le tableau bilan, par exemple, il faudra grouper les lignes 4 à 12 (données de détail) et leur associer la ligne de
synthèse 13 (discipline à laquelle sont rattachés ces enseignements).

Les lignes et les colonnes de synthèse sont toujours placées à proximité immédiate des données de détails auxquelles
elles sont associées (ligne au-dessus ou au-dessous du groupe de données de détails ; colonne située à gauche ou à
droite du groupe de données de détail). Le choix de leur place dépend de l’option retenue dans Données / Grouper et
créer un plan / Options...

Excel peut créer automatiquement un plan et faire la synthèse des lignes et des colonnes de données de détail si
vous avez utilisé des formules telles que SOMME.

Si les lignes et les colonnes de données de synthèse contiennent des valeurs plutôt que des formules ou bien si elles ne
sont pas placées de manière logique, il est préférable de créer vous-même un plan.

A. Création automatique du plan dans la feuille Bilan

1. Cliquez sur n’importe quelle cellule de la feuille Bilan.

8 Daniel Fleury, Philippe Rincon

 2. Dans le menu Données, cliquez sur Grouper et créer un plan, puis sur Plan automatique. Les symboles du plan
apparaissent à côté des groupes sur votre écran. Utilisez-les pour afficher ou masquer des données de détail. Faites
des essais.
NB : si les symboles n’apparaissent pas, vérifiez que l’option d’affichage « Symboles du plan » est active (menu Outils / Options / onglet : Affichage).

3. Vérifiez le résultat. Qu’observez-vous ?

B. Création manuelle du plan

Supprimez le plan de la feuille Bilan à l’aide de la commande Données / Grouper et créer un plan / Effacer le plan.

Créez vous-même le plan. Indications :

Dans ce tableau, il y aura deux niveaux de synthèse en ligne :

un niveau comprenant les sous-totaux par discipline (lignes 13, 17, 22 et 27) et les données de détail qui leur
sont associées (lignes 4 à 12, 14 à 16, 18 à 21 et 23 à 36) ;
un niveau comprenant les totaux généraux (ligne 38) et les données de détail qui leur sont associées (lignes
4 à 37).
et un niveau de synthèse en colonne comprenant :
pour le code enseignement, les données de détail (intitulé et semestre)
pour le nombre d’inscrits (colonne D : synthèse), les données de détail (colonnes E à AC).
Procédure :

1. Sélectionnez les lignes ou les colonnes contenant les données de détail.

Cliquez / Données / Grouper et créer un plan / Grouper.

2. Répétez la même Procédure pour les lignes et les colonnes de détail jusqu’à ce que vous ayez créé tous les
niveaux souhaités dans votre plan.

C. Mise en forme du tableau “ bilan ”.

Selon votre goût.

5. Graphiques.
1. Créez un diagramme sectoriel représentant la répartition des inscrits en Informatique par enseignement et
insérez-le en tant qu’objet dans une nouvelle feuille de calcul (et non dans un feuille graphique) que vous nommerez
graphiques. Mettez en valeur l’enseignement IF203 en excentrant le secteur et en jouant sur la mise en forme de
l’étiquette de donnée.
er
NB : modifiez éventuellement l’angle du 1 secteur afin que les étiquettes ne se chevauchent (Format de série de données / option).

2. Copiez le graphique et modifiez les références de façon à représenter la répartition des inscrits en Informatique
par Diplôme.
NB : supprimez les étiquettes relatives aux secteurs non significatifs (0%).

3. Sélectionnez les 2 objets graphiques et regroupez les à l’aide la barre d’outils dessin. Faites la mise en
page à votre goût.

9 Daniel Fleury, Philippe Rincon

 Tables des matières
La fonction logique « SI » .......................................................................................................................... 1
la fonction « RECHERCHEV »................................................................................................................. 2
1. Nommez la première feuille oct.2004 ........................................................................................................................................ 2
2. Création de la table de recherche ................................................................................................................................................ 2
3. Calcul des dépenses ....................................................................................................................................................................... 3
4. Utilisation d’une formule matricielle pour calculer les prix totaux ........................................................................................ 4
5. Utilisation de la fonction logique SI pour calculer la remise .................................................................................................. 4
6. Protection d’une feuille. ................................................................................................................................................................ 5
7. Copie d’une feuille dans une autre .............................................................................................................................................. 5
8. Liens entre feuilles (bilan) ............................................................................................................................................................ 6
9. Graphique ....................................................................................................................................................................................... 6
10. Liaison avec Word ......................................................................................................................................................................... 6
Excel – document long .............................................................................................................................. 7
1. Liste des enseignements. Conversion de paragraphes Word en données Excel. ................................................................ 7
2. Liste des enseignements. Utilisation d’un filtre. ....................................................................................................................... 7
3. Complétez le tableau « bilan »...................................................................................................................................................... 8
4. Création d’un plan pour faire la synthèse des données. .......................................................................................................... 8
5. Graphiques. .................................................................................................................................................................................... 9

10 Daniel Fleury, Philippe Rincon