Excel pour les nuls- Formation Gratuite


Cette formation Excel pour les nuls est destinée à ceux qui ne connaissent rien en Excel et qui veulent débuter à l’utiliser, et aussi à ceux qui connaissent un peu en Excel mais qui cherchent plus d’informations sur l’utilisation de quelques outils fournis par ce tableur.

Vous trouverez dans cette formation Excel pour les nuls une liste des cours très intéressants qui vont vous guider à bien utiliser les éléments essentiels du programme Microsoft Excel. (Vous trouverez la liste après cette présentation, en bas !! )

Les cours sont utiles et très simples à suivre, ils vont vous permettre d’apprendre à utiliser des outils Excel à travers des explications faciles fournissant des méthodes et des techniques variées ainsi que des trucs et des astuces différents et importants. Les explications sont accompagnées d’images illustratives et parfois des vidéos très courtes.

En plus; et ce qui est important ; la formation est gratuite, il vous suffit tout simplement d’être connectés pour suivre ses cours.

Excel pour les nuls


Comment faire pour suivre ces cours ?

Les cours sont numérotés et mis en ordre croissant, le titre de chaque cours est accompagné d’une petite description qui vous donnera une idée générale sur le contenu du cours et de ce que vous allez apprendre, et à la fin de cette description vous aurez un lien sur lequel vous allez cliquer pour aller à la page contenant le cours. Vous pouvez aussi cliquer sur l’image accompagnant la description pour vous rediriger vers la page du cours souhaité.

Vous avez le choix de suivre les cours par ordre ou bien choisir celui qui vous intéresse, mais si vous êtes nuls en Excel et vous ne savez pas d’où commencer, le mieux est de suivre l’ordre des cours.

Posez vos questions !

Vous pourriez me contacter si vous avez des questions sur les cours de cette formation, soit à travers le formulaire de contact (voir dans le volet de droite) ou via mes pages sociales. Vous pouvez également laisser votre commentaire tout en bas de cet page.

Vous pouvez aussi vous inscrire à la liste d’abonnées pour être informés des mises à jour de cette liste des cours. Cliquez pour cela sur ce bouton :



Enfin, et avant de vous laisser commencer votre première leçon, je vous souhaite de passer de bons moments à travers cette formation et j’espère qu’elle vous rendra le service souhaité.

La liste des cours de la formation Excel pour les nuls

Cours 1 : Explication des éléments essentiels de l’interface Excel

Dans cette première leçon, vous allez découvrir les éléments essentiels dont se compose l’interface Excel. Parmi ces éléments vous allez trouver le ruban, le menu Fichier, le backstage et la barre d’outils d’accès rapide.

Note: à la fin de cet article, vous trouverez un cours gratuit à télécharger.

Commencez alors votre premier cours en suivant ce lien : Explication des éléments essentiels de l’interface Excel.

Explication des éléments essentiels de l’interface Excel


Cours 2 : Créez votre premier tableau : saisie, modification et enregistrement des données.

Dans ce cours très simple, vous allez apprendre à créer un petit tableau en commençant par créer les titres de ses colonnes, puis vous remplirez ses lignes en saisissant vos données. Vous allez aussi savoir comment créer votre premier fichier Excel contenant votre tableau créé.

Cours 3 : Comment ouvrir un classeur Excel ?

Un fichier Excel est nommé «Classeur», il enregistre vos tableaux créés et vos données y saisies. Dans le cours précédent, vous avez vu comment créer et enregistrer un classeur Excel. Dans ce cours vous allez apprendre à ouvrir votre classeur Excel de plusieurs façons (+ astuces).

Voici le lien du cours: Comment ouvrir un classeur Excel ?

Comment ouvrir un classeur Excel ?

Cours 4 : Les bordures : Ajouter, modifier, supprimer et colorier

Des techniques différentes sont expliquées dans ce cours pour vous permettre de savoir comment ajouter des bordures à votre tableau, chose que vous n’avez pas faite lors de la création de votre premier tableau que vous avez vu dans le Cours 2.

Découvrez donc comment ajouter des bordures à votre tableau ou à une partie de lui. Découvrez aussi comment mettre en forme ces bordures en leur appliquant un style ou une couleur personnalisée. Et à la fin de ce cours découvrez comment ajouter ces bordures en les traçant à la main.

Cours 5 : Mise en forme du tableau

Dans cette leçon vous allez voir comment mettre en forme votre tableau, en choisissant une taille d’écrirture du texte, des cellules ou des entêtes de votre tableau, en donnant aussi une couleur personnalisée à ces entêtes ou à l’arrière-plan du tableau.

En plus et à travers ce cours vous allez voir comment mettre en forme une partie du texte de vos cellules, comment aligner le contenu de votre tableau (à gauche, à droite, centrer….), comment mettre le texte de la cellule dans une position verticale ou inclinée.

A la fin du cours vous allez découvrir deux outils importants : l’outil fusionner les cellules et l’outil Format de cellule.

Suivez alors le lien suivant : Mise en forme du tableau

 Mise en forme du tableau

Cours 6 : Appliquer une mise en forme rapide de tableau

Excel vous fournit des styles de tableau prédéfinis qui vous permettent de mettre en forme rapidement votre tableau si vous ne voulez pas passer par les étapes expliquées dans le Cours 5.

Ce cours va vous faire découvrir les styles proposés par Excel et va vous montrer ; à travers 3 méthodes différentes ; comment appliquer un style pour votre tableau. En plus, vous allez apprendre à créer votre propre style et l’appliquer à votre tableau, le modifier si vous le souhaitez ou même le supprimer définitivement.

Le lien du cours est le suivant : Appliquer une mise en forme rapide de tableau

 Appliquer une mise en forme rapide de tableau



Cours 7 : Que gagnez-vous en utilisant l'outil Mettre sous forme de tableau ?

Dans ce cours vous allez découvrir les points avantageux de l’outil Mettre sous forme de tableau que vous avez vu dans le Cours 6, comme la mise en forme automatique de chaque ligne ajoutée à votre tableau, l’utilisation des fonctions les plus utilisées (les fonctions SOMME, MOYENNE, MAX, MIN …) par un simple clic et aussi la mise à jour automatique des calculs.

Découvrez donc ces points utiles en suivant le lien du cours ici : Que gagnez-vous en utilisant l'outil Mettre sous forme de tableau ?

 Que gagnez-vous en utilisant l'outil Mettre sous forme de tableau ?

Cours 8 : Les Colonnes : Afficher-Masquer-Insérer-Supprimer

Les colonnes dans Excel sont numérotées par ordre alphabétique de A à Z puis, AA,AB,AC et BA,BB,BC … jusqu’à la dernière colonne qui est XFD. Excel compte 16 384 colonnes.

Dans ce cours donc, vous allez apprendre comment masquer des colonnes (3 méthodes), comment les afficher (4 méthodes), comment insérer des colonnes (2 méthodes) et enfin comment les supprimer (2 méthodes).

Cours 9 : Comment insérer et supprimer des lignes dans Excel ?

Ce cours explique et comme l’indique son titre comment insérer des nouvelles lignes dans votre tableau et comment en supprimer d’autres. Vous allez donc voir 3 méthodes pour insérer une ou plusieurs lignes et 3 méthodes pour supprimer une ou plusieurs lignes.

Cours 10 : Comment masquer et afficher des lignes ?

Dans ce cours vous allez découvrir 3 méthodes vous expliquant comment masquer des lignes de votre tableau Excel et 4 méthodes vous montrant comment afficher une ou plusieurs lignes.

Voici le lien du cours : Comment masquer et afficher des lignes ?



Cours 11 : Comment Insérer et Supprimer une ou plusieurs feuilles Excel ?

Le but de ce cours est de vous montrer comment insérer une feuille de calcul Excel (vous allez voir 3 méthodes) ou insérer plusieurs feuilles. Ainsi, il va vous montrer comment supprimer une feuille de calcul ou plusieurs feuilles de calcul (adjacentes et non-adjacentes).

Cours 12 : Comment renommer une feuille Excel et colorer son onglet ?

Vous remarquez que les feuilles Excel sont nommées par défaut : Feuil1, Feuil2…. Cette nomination vous pouvez la personnaliser et donner à vos feuilles de calcul des noms significatifs. Pour cela, ce cours vous fournit 3 méthodes pour renommer une feuille Excel.

Vous pouvez aussi définir des couleurs différentes de votre choix pour les onglets de vos feuilles de calcul ce qui vous permet de les faire distinguer à l’aide de ses couleurs. Ce cours vous présente donc 2 méthodes pour exécuter cette tâche.

Cours 13 : Comment sélectionner des feuilles Excel ?

Pour effectuer quelques opérations sur les feuilles, comme par exemple les copier, les déplacer ou créer un tableau qui va se répéter sur plusieurs feuilles, on doit tout d’abord les sélectionner. Parfois, on a besoin de sélectionner plusieurs feuilles adjacentes ou non-adjacentes, ou bien même sélectionner toutes les feuilles du classeur Excel.

Ce cours donc va vous montrer différentes manières de sélectionner vos feuilles de calcul. En plus, vous allez découvrir comment résoudre quelques problèmes qui surviennent lorsqu’on fait masquer des onglets des feuilles.

Cours 14 : Déplacer et copier des feuilles Excel : Trucs et Astuces

Dans ce cours vous allez apprendre à :

  • Déplacer et copier des feuilles Excel dans le même classeur.
  • Déplacer et copier des feuilles Excel dans un classeur existant.
  • Déplacer ou copier une feuille dans un nouveau classeur.
Et ceci en procédant de différentes manières et en découvrant des trucs et des astuces très importants.

Cours 15 : 23 techniques pour sélectionner des cellules dans Excel

Sélectionner une cellule pour y saisir du texte ou mettre en forme son contenu est une opération très facile, il vous suffit tout simplement de cliquer sur elle. Cependant, sélectionner la dernière cellule d’une ligne ou d’une colonne d’un grand tableau ou bien se positionner dans la dernière cellule de la feuille peut vous gêner si vous ne savez pas comment.

Dans ce cours, vous avez entre vos mains 23 techniques vous permettant de savoir comment :
  • Sélectionner la première ou la dernière cellule d’une ligne ou d’une colonne
  • Sélectionner la première et la dernière cellule de la feuille de calcul.
  • Sélectionner toutes les cellules d’une ligne ou d’une colonne d’un tableau.
  • Sélectionner tout le tableau.
  • Sélectionner des cellules non adjacentes.

Cours 16 : Comment sélectionner une ligne ou une colonne entière et toutes les cellules de la feuille ?

Dans ce cours vous allez apprendre à savoir comment :
  • Sélectionner une colonne entière.
  • Sélectionner une ligne entière.
  • Sélectionnez des colonnes adjacentes.
  • Sélectionnez des colonnes non-adjacentes.
  • Sélectionnez des lignes adjacentes.
  • Sélectionnez des lignes non-adjacentes.
  • Sélectionnez toutes les cellules de la feuille de calcul.

Cours 17 : Comment sélectionner le contenu d’une cellule ?

Sélectionner une cellule, vous l’avez vu dans le Cours n° 15. Or le problème qui survient pour quelques utilisateurs c’est qu’ils ne parviennent pas à modifier le texte saisi dans la cellule même si la cellule est sélectionnée.

Le but de ce cours n° 17 est de vous montrer comment sélectionner ce que contient la cellule pour le modifier ou pour corriger les fautes de frappes… etc. Vous allez voir alors 4 techniques pour ce faire.


Cours 18 : Comment couper-copier-coller facilement des cellules ?

Ce cours vous donne en détail des explications sur l’utilisation des opérations : couper-copier-coller. Il va vous faire découvrir comment utiliser les boutons de ces opérations, comment effectuer un copier/coller ou un couper/coller à l’aide du clavier, et enfin comment utiliser l’outil Presse-papiers d’Excel.

Vous pouvez suivre le cours à partir de ce lien : Comment couper-copier-coller facilement des cellules?


Cours 19 : 3 techniques pour coller des valeurs

Excel vous fournit dans ces dernières versions plusieurs options de collage : par exemple, si vous avez copié un texte et vous le collez dans un nouvel emplacement, vous pouvez choisir entre copier le texte tel qu’il est (avec sa mise en forme) ou bien copier seulement le texte et lui appliquer la mise en forme du nouvel emplacement….

Dans ce cours, vous allez découvrir ce nouvel outil : Options de collage, et vous allez voir comment appliquer l’une de ces options qui est le collage des valeurs en procédant de trois façons.

Voici donc le lien du cours : 3 techniques pour coller des valeurs


Cours 20 : Comment copier une mise en forme : 5 Méthodes

Lorsque vous créez un tableau et vous lui appliquez une mise en forme (comme vous l’avez vu dans le Cours n°5) et que vous aimez créer un autre tableau et lui appliquer la même mise en forme, cela sans doute va vous demander du temps et d’efforts. Cependant et avec cette option de copie de mise en forme que vous allez découvrir dans ce cours, vous allez mettre en forme votre deuxième tableau, tel que le premier, très rapidement et sans toucher à ses données.

Suivez donc les 5 méthodes de copie de mise en forme ici : Comment copier une mise en forme : 5 Méthodes.



Cours 21 : Comment coller avec liaison ?

Dans ce cours, je vais vous montrer comment utiliser l’outil Coller avec liaison. Un outil très important qui vous permet de copier vos données qui se trouvent dans un tableau vers un autre tableau et de garder une liaison entre ces deux données, c’est-à-dire que lorsque vous effectuez une modification sur les premières données, automatiquement les deuxièmes données prendront en compte cette modification et se mettent à jour. Pas besoin de vous intervenir pour les modifier elles aussi.

Ce qui est génial également dans l’utilisation de cet outil, c’est que lorsque vous déplacez vos données d’origines soit dans la même feuille ou dans une autre feuille, Excel conservera intactes les données collées dans le deuxième tableau.

Je vais vous présenter alors 3 façons d’utiliser cet outil, et voici le lien du cours pour les découvrir :




Associez INDEX et EQUIV pour effectuer mieux vos recherches

Ajourd’hui vous allez voir dans cette formation Excel, comment utiliser conjointement les deux fonctions INDEX et EQUIV pour effectuer vos recherches mieux que lorsque vous utilisiez la fonction RECHERCHEV.
INDEX EQUIV


Comme nous l’avons vu dans l’article "Comment utiliser la fonction RECHERCHEV ?" la fonction RECHERCHEV nous permet de renvoyer la valeur qui correspond à une valeur cherchée dans un tableau.
Et si nous voulons l’appliquer ici pour rechercher le prix de l’ampoule « LED E27 », nous procédons comme suit :

Exemple de RECHERCHEV


Cette même valeur retournée pourra être renvoyée aussi en utilisant conjointement les deux fonctions INDEX et EQUIV.
Beaucoup d’utilisateurs d’Excel préfèrent l’utilisation de ces deux fonctions au lieu de la fonction RECHERCHEV pour de nombreuses raisons.
Tout d’abord veuillez jeter un coup d’œil sur l'utilisation de ces deux fonctions INDEX et EQUIV, puis découvrons ensemble comment faire des recherches en les associant toutes les deux.

La fonction INDEX

La fonction INDEX permet de renvoyer une valeur qui se trouve dans une cellule, d’un tableau ou d’une plage de cellules, à l ‘intersection d’une ligne et d’une colonne spécifiées.
La fonction INDEX a deux syntaxes :
INDEX(matrice, no_lig, [no_col]) et INDEX(réf, no_lig, [no_col], [no_zone]).
Je vous invite à découvrir en détails comment utiliser la fonction INDEX à partir de cet article : La fonction INDEX


La fonction EQUIV

La fonction EQUIV renvoie la position d’un élément recherché dans une plage de cellules.
Voici sa syntaxe : EQUIV(valeur_cherchée, matrice_recherche, [type])
Découvrez plus d’infos sur cette fonction en suivant ce lien : La fonction EQUIV

Pourquoi INDEX + EQUIV est considérée meilleure que RECHERCHEV ?

Insérer ou supprimer des colonnes sans affecter au résultat renvoyé :

Le problème de la fonction RECHERCHEV qui se pose lors de l’insertion ou de la suppression d’une ou de plusieurs colonnes c’est qu’elle retourne des valeurs incorrectes.
Prenez l’exemple suivant:
En utilisant la fonction RECHERCHEV pour trouver le prix de l’ampoule « LED E27 » ; nous avons déterminé pour le numéro d’index de colonne la valeur 3.
Or, si nous insérons une colonne par exemple avant la deuxième colonne « Puiss en Watt », cette dernière et la colonne Prix seront décalées vers la droite, et la colonne « Puiss en Watt » deviendra la troisième colonne de la table_matrice
RECHERCHEV renvoie donc 4 au lieu de 8.

RECHERCHEV renvoie erreur à cause de colonne insérée


Nous serons alors obligés de modifier notre formule pour régler le problème.
Mais imaginez combien de temps et d’efforts vous allez fournir si vous êtes devant un grand tableau de plusieurs colonnes et de milliers de données !!

Avec INDEX+EQUIV vous pouvez insérer ou supprimer des colonnes sans affecter à vos résultats et sans avoir besoin d’intervenir pour modifier la formule.
En reprenant l’exemple précédent, voici comment va dérouler le travail des deux fonctions INDEX et EQUIV associées :
  • La valeur (le prix) qui correspond à la valeur cherchée « LED E27 » se trouve à l’intersection de la ligne n°6 et de la colonne n°3 de la plage de cellules A2:C7.
  • Si nous utilisons la fonction INDEX, nous aurons l’écriture suivante =INDEX(A2:C7;6;3)
Simple exemple avec INDEX


Et pour rendre le jeu dynamique, nous allons utiliser la fonction EQUIV pour renvoyer le numéro de la ligne et de la colonne utilisées dans la fonction INDEX.
Voici ce que nous allons faire :

Renvoyer le numéro de la ligne :

  • La valeur 8 correspond à la valeur cherchée « LED E27 » et toutes les deux se trouvent dans la même ligne : Ligne n°6
  • Alors la formule à utiliser pour renvoyer ce chiffre est la suivante : =EQUIV(F1;A2:A7;0)

Renvoyer le numéro de la colonne :

  • La colonne contenant la valeur 8 est la colonne « Prix », nous utiliserons donc la fonction EQUIV de la façon suivante pour nous renvoyer le numéro de cette colonne : =EQUIV(C1;A1:C1;0) 

Simple exemple avec EQUIV


  • Intégrant maintenant EQUIV dans INDEX :
Utilisation de INDEX & EQUIV



Insérez ou supprimez des colonnes et remarquez ce qui surviendra:

Insérer supprimer colonne dans INDEX et EQUIV


Copiez vos formules sans problèmes

Souvenez-vous de cet exemple vu dans l’article Comment utiliser la fonction RECHECHEV ?, où le copier-coller de la fonction RECHECHEV impose qu’on doit modifier « manuellement » le no_col_index qui est déterminé comme valeur figée?!

En utilisant INDEX+EQUIV, vous n’aurez pas ce genre de difficulté.
Voyons donc comment nous allons procéder :
  • La cellule G3 va afficher le montant réalisé par le vendeur spécifié dans la cellule G1, dans notre exemple c’est "Nom 1".
  • La colonne affichant les montants réalisés est placée en 4ème position dans la matrice A2:D22 qu’on a nommée « Table1 ».
Tableau exemple

    • Et pour utiliser la fonction EQUIV qui va renvoyer cette position, nous écrivons :

=EQUIV($F3;$A$1:$D$1;0)
    • F3 contient le nom de l’intitulé de la colonne « Mt réalisé » tel qu’il est écrit dans le tableau. Et nous avons figé la colonne F. (vous allez voir pourquoi !)
    • $A$1:$D$1 : référence absolue de la plage des intitulés des colonnes du tableau.
  • Quant à la valeur « Nom 1 », elle se trouve dans la 1ère ligne.
    • Alors la fonction EQUIV s’écrira comme suit pour retourner la position de cette ligne :

=EQUIV($G$1;$A$2:$A$22;0)

Nous allons maintenant intégrer les deux formules dans la fonction INDEX, et voici ce que nous obtiendrons :
=INDEX(Table1; EQUIV($G$1;$A$2:$A$22;0); EQUIV($F3;$A$1:$D$1;0))

Excel renvoie alors le montant 1156,00 Euros.

Exemple INDEX & EQUIV


En copiant cette formule sur la cellule G4, Excel affiche « Paris » et c’est le bon résultat souhaité.

Copie de INDEX EQUIV


Vous remarquez donc que toutes les références restent inchangées dans les deux formules, sauf F3 qui est incrémentée après la copie et est devenue F4 pour s’adapter à ce que nous recherchons, c’est-à-dire le nom de la ville.

De droite à gauche

En utilisant la fonction RECHERCHEV, la valeur cherchée doit se trouver dans la colonne la plus à gauche de la table_matrice (plage de recherche).
Avec INDEX+EQUIV cette condition n’est pas obligatoire.
Voici le tableau utilisé dans l’exemple précédent, où nous avons déplacé la colonne "Nom" en deuxième position dans la plage de cellules A2:D22 (Table1).
Et voici la formule utilisée :
=INDEX(Table1; EQUIV($G$1;$B$2:$B$22;0); EQUIV($F3;$A$1:$D$1;0))

De droite à gauche dans INDEX et EQUIV

Excel renvoie donc le bon résultat.

INDEX+EQUIV avec plusieurs critères

Exemple 1

Le tableau suivant contient des notes de quelques étudiants selon les matières étudiées :

Tableau de notes


Nous voulons trouver la note d’une matière spécifiée pour un étudiant sélectionné en utilisant les deux fonctions INDEX et EQUIV :
  • Supposons que la cellule H1 affiche le nom de l’étudiant cherché, et la cellule H2 contient le nom de la matière dont on veut trouver la note.
  • Nous avons donc créé une liste de choix dans H1 pour afficher tous les noms des étudiants, et une autre liste dans H2 pour afficher toutes les matières.
    • Pour ce faire, sélectionnez la cellule H1 et cliquez sur Validation des données sous l’onglet Données.
    • Sélectionnez Liste sous Autorise, puis cliquez dans la zone Source et sélectionnez les noms des étudiants, et cliquez sur OK pour valider.
Créer une liste de choix

    • Procédez ainsi pour créer la liste des matières.
  • Sélectionnez H4 et tapez la formule suivante :

=INDEX($B$2:$E$6;EQUIV($H$2;$A$2:$A$6;0);EQUIV($H$1;$B$1:$E$1;0))
  • Validez par Entrée.
  • Voici le résultat obtenu :
INDEX EQUIV avec deux conditions



Exemple 2

Le tableau suivant liste plusieurs types d’ampoules avec leurs puissances en Watt et prix:

Prix des ampoules
Prix des ampoules


Ce que nous souhaitons dans cet exemple c’est chercher le prix d’une ampoule selon sa puissance en Watt:
Par exemple, nous allons procéder en utilisant conjointement INDEX et EQUIV pour trouver le prix de l’ampoule "Halogène (230V) 1000W".
  • L’ampoule "Halogène (230V) 1000W" se trouve donc dans la ligne n°4.
  • Et son prix correspondant se trouve dans la colonne Prix.
Déterminer une ligne du tableau

  • En utilisant la fonction INDEX, nous écrivons : =INDEX(C2:C15;4)
  • Vous remarquez que nous avons sélectionné uniquement la colonne Prix, c’est pour cela que n’avons pas déterminé le numéro de colonne.
  • Intégrant maintenant EQUIV :
  • La formule s’écrira de cette façon :

=INDEX(C2:C15;EQUIV(F1&F2;A2:A15&B2:B15;0))
  • Validez ensuite par Ctrl+Shift+Entrée pour obtenir le résultat escompté :
INDEX EQUIV plusieurs critères



Pour renvoyer donc le numéro de la ligne 4, Nous avons effectué la recherche des deux valeurs Halogène (230V) et 1000W concaténées dans les deux plages de cellules concaténées également A2:A15&B2:B15.
C’est-à-dire que la fonction EQUIV dans ce cas va chercher la valeur résultante de la concaténation F1&F2 qui est « Halogène (230V)1000 » dans le nouveau tableau obtenu de la concaténation des deux plages A2:A15&B2:B15 et qui sera affiché de cette façon:

Données concaténées


Bien entendu, la valeur « Halogène (230V)1000 » se trouve en 4ème ligne.

Voyons à présent comment utiliser la même formule mais en évitant de la rendre matricielle. ( Vous ne serez pas obligés de combiner Ctrl+Shift+Entrée pour la valider)
Copiez la formule suivante dans la cellule F4 :
=INDEX(C2:C15;EQUIV(F1&F2;INDEX(A2:A15&B2:B15;0);0))
Validez alors par Entrée:

INDEX EQUIV plusieurs critères non matricielle



À la place de A2:A15&B2:B15 nous avons intégré une autre fonction INDEX pour nous renvoyer toutes les valeurs de ces deux plages concaténées en spécifiant 0 pour l’argument no_lig. (reportez vous à l’article La fonction INDEX pour plus de détails sur cette astuce).

La fonction EQUIV

Dans l’article suivant de cette formation Excel, vous allez découvrir l’utilité de la fonction EQUIV et comment l’utiliser.
La fonction EQUIV

Présentation de La fonction EQUIV

La fonction EQUIV (Match en anglais) renvoie la position d’un élément recherché dans une plage de cellules.

Syntaxe de la fonction EQUIV :

EQUIV(valeur_cherchée, matrice_recherche, [type])

Les trois arguments de la fonction EQUIV  sont donc :
  • valeur_cherchée : c’est la valeur dont on cherche la position dans une plage de cellules. Elle peut être de type texte, numérique ou logique.
  • matrice_recherche : est la référence de la plage de cellules dans laquelle on cherche la position de l’élément recherché.
  • Type : c’est un nombre -1, 0 ou 1 :
    • Type = 1 : La fonction EQUIV recherche la valeur la plus élevée qui est inférieure ou égale à la valeur_cherchée. Dans ce cas la matrice_recherche doit être triée en ordre croissant.
    • Type= 0 : La fonction EQUIV retourne la valeur exacte correspondant à la valeur_cherchée.
    • Type= -1 : La fonction EQUIV recherche la valeur la plus petite qui est supérieure ou égale à la valeur_cherchée. La matrice_recherche doit être triée en ordre décroissant.



Exemples d’utilisation de la fonction EQUIV

Le tableau suivant sera utilisé dans les exemples qui suivent :

Exemple 1 : chercher la position exacte d’un élément verticalement

  • Pour trouver la position relative à la valeur « Fluo (230V) » dans la plage de cellules A2:A7, tapez la formule suivante : =EQUIV("fluo (230v)";A2:A7;0)
  • Excel renvoie le numéro 4
Fonction EQUIV -recherche dans une colonne-

Note : la valeur cherchée est mise entre guillemets car elle est de type texte. Vous pouvez également référencer une cellule contenant cette valeur cherchée, dans ce cas vous n’aurez pas besoin d’utiliser les guillemets.


Cellule référencée dans l'argument valeur cherchée de la fonction EQUIV

L’argument Type est égal à "0" à fin de trouver la position exacte de la valeur cherchée « fluo (230v) ».

Exemple 2: chercher la position exacte d’un élément horizontalement

  • Pour trouver la position relative à la valeur « Néon (230V) » dans la plage de cellules (Ligne) A6:C6, tapez la formule suivante : =EQUIV("Néon (230V)";A6:C6;0)
  • Excel affiche le numéro 1
Fonction EQUIV -recherche dans une ligne-

Exemple 3: Utilisation de l’argument Type=1 dans la fonction EQUIV

Dans la colonne « Puis. En Watt », nous voulons chercher la position de la valeur « 75 ».
Et puisque cette valeur n’existe pas dans la plage de cellules B2:B7, nous allons dès lors chercher la position d’une valeur proche d’elle mais inférieure à 75.
Dans ce cas nous allons définir pour l’argument Type la valeur 1.
  • Pour ce faire, veuillez tout d’abord mettre en ordre croissant votre tableau pour obtenir le bon résultat.
  • Puis tapez la formule suivante :  =EQUIV(75;B2:B7;1)
  • Excel vous renvoie 5 qui représente la position de la valeur 50, cette dernière est la valeur la plus élevée et inférieure à 75.
La fonction EQUIV argument Type égal à 1

Exemple 4 : Utilisation de l’argument Type = -1 dans la fonction EQUIV

Dans l’exemple suivant, nous allons demander à Excel de nous renvoyer la position de la valeur 35 dans la plage de cellules B2:B7, ou bien la position d’une autre valeur qui existe dans cette plage et qui est supérieure à 35  au cas où Excel ne trouve pas la valeur cherchée.
  • La première des choses à faire donc est de mettre en ordre décroissant le tableau de données, puis d’utiliser la fonction EQUIV en définissant pour l’argument Type la valeur -1 :
  • Voici la formule à utiliser :
=EQUIV(35;B2:B7;-1)
  • Le résultat renvoyé est 3.
La fonction EQUIV argument Type égal à -1

Vous remarquez que trois valeurs qui sont supérieures à 35 : vous avez 40, 50 et 300. La plus petite de ces valeurs est 40 et sa position dans la plage de cellules B2:B7 est donc 3.

Note : Si la fonction EQUIV ne trouve pas de valeur équivalente, elle renvoie la valeur d’erreur #N/A.

La fonction INDEX

L’article présent de la formation Excel vous permet de découvrir le rôle de la fonction INDEX et comment l’utiliser à travers 6 exemples.
La fonction INDEX

Présentation de la fonction INDEX

La fonction INDEX permet de renvoyer une valeur qui se trouve dans une cellule, d’un tableau ou d’une plage de cellules, à l ‘intersection d’une ligne et d’une colonne spécifiées.

Syntaxes de la fonction INDEX

Excel fournit deux syntaxes de la fonction INDEX :

Forme matricielle et référentielle de la fonction INDEX

  • La première syntaxe :

INDEX(matrice, no_lig, [no_col]) : c’est la syntaxe de la forme matricielle de la fonction INDEX, et c’est la plus utilisée. Cette forme permet de renvoyer une valeur d’une plage de cellules précisée.

Comme vous le voyez, trois arguments qui composent la fonction INDEX :
  1. matrice : est le tableau ou la plage de cellules contenant la valeur à renvoyer.
  2. no_lig : le numéro de la ligne du tableau ou de la plage de cellules où se trouve la valeur à renvoyer.
  3. no_col : le numéro de la colonne du tableau ou de la plage de cellules contenant la valeur à renvoyer.

  • La deuxième syntaxe

INDEX(réf, no_lig, [no_col], [no_zone]): c’est la syntaxe de la forme référentielle qui permet de renvoyer une valeur parmi plusieurs plages de cellules spécifiées.

Pour cette forme référentielle, vous remarquez qu’il existe 4 arguments :
  • réf : indique la référence de la plage de cellules sélectionnée ou les références des plages de cellules spécifiées.
  • no_lig et no_col : sont déjà définis dans la première syntaxe.
  • no_zone : est le numéro d’ordre de la plage de cellules mentionnée dans l’argument réf par rapport aux autres plages de cellules référencées dans le même argument et qui contient la valeur à renvoyer. Par exemple 1 indique à Excel de renvoyer la valeur se trouvant dans la première plage de cellules et à l’intersection de la ligne et de la colonne spécifiées par les deux arguments no_lig et no_col .


Exemples d’utilisation de la fonction INDEX

Les exemples traités ci-dessous seront appliqués sur le tableau suivant concernant les prix des différents types d’ampoules:

Prix des ampoules
Liste des prix des ampoules


Exemple 1 : Un tableau de plusieurs lignes et colonnes

  • Pour trouver la valeur se trouvant dans la 3ème ligne et la deuxième colonne de la plage de cellules A2:C7, voici la formule utilisée: =INDEX(A2:C7;3;2)
  • Excel renvoie 300
Insérer la fonction INDEX


Exemple 2 : Un tableau de plusieurs lignes et d’une seule colonne

  • Pour trouver la valeur se trouvant dans la 4ème ligne de la plage de cellules A2:A7, tapez : =INDEX(A2:A7;4)
  • Dans ce cas et puisque la plage de cellules A2:A7 ne contient qu’une seule colonne, la spécification du numéro de colonne n’est pas obligatoire.
  • La fonction INDEX renvoie « Fluo (230V) »
INDEX- Tableau d'une seule colonne


Exemple 3 : Un tableau de plusieurs colonnes et d’une seule ligne

  • Pour afficher la valeur se trouvant dans la 3ème colonne du tableau A2:C2, saisissez : =INDEX(A2:C2;;3)
  • Ce tableau ou cette plage de cellules spécifiée se compose d’une seule ligne, nous avons donc laissé l’argument no_lig vide.
  • La valeur renvoyée est « 1 »
Fonction INDEX - Tableau avec une seule ligne



Note :
Si vous tapez un numéro de ligne (de colonne) qui se trouve en dehors de la plage sélectionnée, Excel renvoie une erreur REF !.

Fonction INDEX - Erreur REF


Exemple 4 : l’argument no_lig égal à 0

  • Lorsque l’argument no_lig est égal à 0, la fonction INDEX renvoie toutes les valeurs de la colonne spécifiée.
  • Dans l’exemple suivant, sélectionnez la plage de cellules E2:E7 puis tapez dans la barre de formule la formule suivante : =INDEX($A$2:$A$7;0)
  • Ensuite validez par Ctrl+Maj+Entrée et non par Entrée pour créer une formule matricielle.
no_lig égal à 0 dans la fonction INDEX



Cette formule aura plus d’importance quand vous l’utiliserez pour créer une liste déroulante de valeurs.
  • Pour cet effet, sélectionnez une cellule de votre feuille de calcul et cliquez sous l’onglet Données sur Validation de données.
L'outil Validation de données

  • Sous Autoriser, sélectionnez Liste et dans la zone Source saisissez la formule précédente =INDEX($A$2:$A$7;0).
Créer une liste dans Validation de données

  • Cliquez sur OK pour sortir de cette fenêtre.
  • Une flèche apparaît à côté de la cellule, cliquez sur cette flèche pour faire afficher la liste des valeurs se trouvant dans la colonne spécifiée par la fonction INDEX. (ici c’est la colonne Type)
Liste déroulante Excel

Exemple 5 : l’argument no_col égal à 0


Même principe aussi, sauf que dans ce cas, se sont toutes les valeurs de la ligne spécifiée par la fonction INDEX qui seront renvoyées.
  • Sélectionnez à présent la plage de cellules E2:G2 puis tapez dans la barre de formule : =INDEX($A$2:$C$7;3;0)
  • Validez par Ctrl+Maj+Entrée.
  • Excel affiche les valeurs contenues dans la troisième ligne de la plage de cellules A2 :C7.
no_col égal à 0 dans la fonction INDEX


Et comme vous l’avez fait pour créer une liste déroulante dans l’exemple précédent, suivez les mêmes étapes et collez cette fois =INDEX($A$2:$C$7;3;0) dans la zone Source.

Liste créée en utilisant la fonction INDEX


Exemple 6 : Utilisation de la forme référentielle de la fonction INDEX

Cette forme est peu utilisée par les utilisateurs d’Excel, cependant on va voir comment elle fonctionne :
  • Dans cet exemple, nous avons 4 tableaux de notes pour 4 étudiants. Notre but est d’utiliser une seule fonction INDEX incluant les références de ces 4 tableaux pour trouver la note de la matière C++ pour l’étudiant 4.
4 plages de cellules


  • Sélectionnez alors une cellule et collez la formule suivante :

=INDEX((A4:B8;E4:F8;A13:B17;E13:F17);3;2;4)

  • Remarquez que dans l’argument réf, nous avons écrit les références des 4 plages de cellules séparées par des points-virgules et que nous les avons mises entre parenthèses.
  • La matière C++ se trouve dans la troisième ligne dans toutes les plages de cellules.
  • La note correspondante à cette matière se trouve dans la deuxième colonne.
  • L’argument no_zone est égal à 4. En effet, la plage de cellules contenant les notes de l’étudiant E13:F17 est  placée en quatrième position dans l’argument réf.
  • La fonction INDEX renvoie donc la note 13.
Fonction INDEX référentielle


Note : les plages de cellules spécifiées dans l’argument réf de la fonction INDEX doivent exister dans la même feuille, si non, INDEX renvoie l’erreur #CNUM!

Vous pouvez rendre cette formule dynamique, par exemple :
  • Choisissez deux cellules, une pour afficher le numéro de la zone et l’autre pour afficher le numéro de la matière.
  • Puis saisissez les références de ces deux cellules dans les deux arguments no_zone et no_lig comme suit :
Fonction INDEX dynamique

  • Il ne vous reste maintenant qu’à taper le numéro de la plage de cellules et le numéro de la matière pour que la fonction INDEX vous renvoie le résultat correspondant. Regardez l'illustration suivante :
Exemple Fonction INDEX dynamique