INDEX-EQUIV : Astuces de pro sur Excel !

découvrez comment utiliser la fonction index éive comme un pro sur Excel bonjour à tous C'est alexexcelformation.fr et dans ce tutoriel je vais vous montrer quelques astuces incroyables qui vont vous permettre d'extraire des données de manière très précise dans excel en utilisant pour cela la fonction index éive nous verrons notamment comment extraire une donnée en fonction de plusieurs critères ou encore en toute fin de vidéo nous verrons comment effectuer des recherches en 3D c'est-à-dire en fonction d'une ligne et d'une colonne toujours en utilisant la fonction index kive alors vous trouverez très régulièrement de nouvelles vidéos sur la chaîne alors pensez à vous abonner et surtout à activer la clochette de notification afin d'être sûr de ne manquer aucune astuce et de devenir rapidement un [Musique] prodexcel comme d'habitude si vous voulez être sûr de bien comprendre tout ce que nous allons voir dans le cadre de cette vidéo vous pouvez télécharger le support de ce tutoriel ainsi que sont corriger en suivant le lien disponible dans la description de la vidéo VI ce qui vous permettra de réaliser les opérations en même temps que moi pour bien comprendre tout ce que nous allons voir dans cette vidéo donc pour illustrer ce tutoriel imaginons que nous gérions un petit magasin de livres et que nous souhaitions analyser les ventes de différents genres pour les différents mois de l'année voici donc un tableau de données que nous allons pouvoir utiliser qui reprend donc le nom des livres que nous voulons analyser le genre de ces dernier le mois pour lesquels nous allons vouloir les analyser et le nombre de ventes réalisé au cours de ce mois et donc pour cela nous allons utiliser la fonction index éive qui permet d'effectuer très rapidement des recherches au sein d'une base de données pour y extraire des informations en fonction d'un ou plusieurs critères comme nous allons le voir tout au long de ce tutoriel et donc pour commencer voyons comment fonctionne la fonction index equive laquelle est composée de deux fonctions tout d'abord la fonction index et ensuite nous verrons dans un deuxième temps la fonction éive donc cette fonction index permet de renvoyer la valeur d'une cellule spécifique au sein d'une plage de cellule ce qui sera particulièrement utile lorsque nous allons vouloir travailler avec de grandes quantités de données et que nous avons besoin d'extraire des informations précises et donc la syntaxe de cette fonction index est la suivante comme vous pouvez le constater elle est composée de trois arguments le premier argument correspond à la plage des cellules dans laquelle nous allons souhaiter effectuer la recherche de valeur ensuite nous avons l'argument numéro de ligne qui correspond au numéro de la ligne de la valeur que nous allons vouloir R voy et enfin le troème argument qui ici est saisi entre crochet étant donné qu'il est facultatif numéro de colonne correspond au numéro de la colonne de la valeur que nous allons vouloir enenvoyer si nous omettons cet argument la valeur par défaut sera donc 1 c'est-à-dire que celui-ci se trouvera dans la première colonne ainsi si nous souhaitons obtenir la valeur des ventes pour la troisième ligne de la colonne des ventes alors nous utiliserons la fonction index de la manière suivante tout d'abord en tant que matrice nous allons S sélectionner les cellules dans lequel se trouve l'information à extraire il s'agit ici de la plage des cellules dans lequell sont saisis le nombre de vente de chaque ouvrage c'est-à-dire les cellules D7 jusqu'à D16 ensuite en tant que deuxième argument nous allons venir saisir le numéro de la ligne que nous allons vouloir récupérer ici il s'agit de la troisème ligne nous n'avons pas besoin d'utiliser l'argument facultatif numéro de colonne étant donné qu'ici la plage des cellules n'est composée que d'une seule colonne ensuite nous validons en appuyant sur ent et la fonction index nous informe donc que la troisième valeur de la plage de cellule est 150 150 qui correspond au nombre de vente du livre Le Seigneur des Anneaux au cours du mois de mars ensuite pour effectuer la recherche nous allons combiner cette fonction index avec la fonction éive cette fonction éive qui permet donc de rechercher une valeur spécifique à l'intérieur d'une plage de cellule pour envoyer la position relative de cette valeur donc nous allons nous placer sur la ligne suivante et ici nous allons vouloir récupérer le numéro de la ligne dans la quelle se trouve la première occurrence qui correspond au mois de mars donc à l'intérieur de cette plage de cellule donc ici nous appelons la fonction éive et nous pouvons al constater que celle-ci attend trois arguments tout d'abord le premier argument valeur cherchée correspond à la valeur que nous allons souhaiter extraire donc il s'agit ici du mois de mars que nous pouvons soit venir saisir directement en tant que texte c'est-à-dire entre guillemets soit nous pouvons également venir sélectionner une cellule qui contient la valeur à extraire donc ici nous cliquons sur la cellule H8 pour insérer automatiquement la valeur de cette cellule en tant que premier argument le 2è argument tableau recherche correspond à la plage de cellule dans laquelle se trouve la valeur que nous allons vouloir rechercher ici comme nous l'avons vu il s'agit donc de récupérer la position du mois de mars à l'intérieur de notre liste de mois donc ici nous sélectionnons les cellules C7 jusqu'à C 166 ensuite le 3ème argument qui est encore une fois facultatif permet de définir le type de correspondance que nous voulons mettre en place ici il s'agit d'un argument très important étant donné que celui-ci va nous permettre d'effectuer une recherche exacte de la valeur donc ici qui correspond au mois de mars et lorsque cette valeur n'est pas disponible dans la plage alors la fonction nous renverra une erreur c'est très important pour éviter d'avoir des valeurs qui seraient approximatifes et donc des résultats qui pourraient être erronés voilà donc une fois que nous avons saisi le zéro nous pouvons fermer la parenthèse et valider et ici donc la fonction Iive nous informe que le mois de mars apparaît en 3e position dans la liste des mois voilà donc maintenant que nous connaissons et savons comment utiliser les fonctions index et equive nous allons pouvoir les combiner pour extraire des valeurs spécifiques à l'intérieur de notre plage de cellule par exemple si nous souhaitons trouver les ventes qui correspondent au livre Le Seigneur des Anneaux alors nous allons tout d'abord devoir trouver la position de celui-ci à l'intérieur de la liste de nos livres et utiliser cette position pour obtenir la valeur qui correspond à l'intérieur de la colonne des ventes donc pour cela nous nous plaçons sur la cellule G9 nous appelons tout d'abord la fonction index qui va donc nous permettre de définir la plage des cellule dans laquelle se trouve la valeur à extraire ici pour obtenir le nombre de vente nous sélectionnons une fois de plus les cellules D7 jusqu'à D16 à l'intérieur de cette fonction index en tant qu'argument numéro de ligne nous allons maintenant appeler la fonction equive pour obtenir la position du livre Le Seigneur des Anneaux donc une fois encore nous pourrions très bien saisir cette valeur entre guillemets étant donné qu'il s'agit d'un texte ou alors pour aller plus vite nous pouvons sélectionner directement la valeur de la cellule H9 dans laquelle a déjà été saisi donc le titre de ce livre ensuite nous passons au deuxième argument de la fonction index et ici il suffit de venir sélectionner la liste des livres nous faisons bien attention d'effectuer une recherche exacte afin de ne pas avoir des données qui seraient erronées et ensuite nous pouvons fermer les deux parenthèses donc la parenthèse de la fonction éive et celle de la fonction index ensuite nous validons et donc la fonction nous informe que 150 ouvrages du Seigneur des Anneaux ont été vendus au cours de l'année donc ici pendant le mois de mars si nous le souhaitons nous pouvons saisir directement un autre ouvrage dans la cellule H9 pour que lorsque nous validons le résultat de la fonction s'adapte automatiquement et nous informe que 130 ouvrages de Dune ont été vendus au cours de l'année donc là nous venons de voir l'usage le plus classique de cette fonction index éive qui permet effectivement d'effectuer des recherches au sein d'une base de données sans avoir à être limité par les limitations imposées par des fonctions comme les fonctions recherche V ou recherche h notamment le fait que la table doit impérativement être classée par ordre chronologique et qu'en plus la colonne dans laquelle nous allons vouloir effectuer des recherches doit forcément se trouver en première position et maintenant je vous propose de découvrir une méthode alternative pour utiliser cette fonction index éive cette méthode qui n'est utilisé que par les spécialistes d'Excel donc tout d'abord nous allons appeler la fonction index comme nous l'avons fait juste avant afin de récupérer des valeurs à l'intérieur de la colonne vente ensuite nous allons appeler la fonction éive sauf que ici pour valeur de recherche nous allons tout simplement saisir la valeur 1 et c'est dans l'argument tableau de recherche que nous allons mettre en place une matrice constituée de vrai ou de faux qui va déterminer quelles cellules sont à prendre en compte donc ici nous allons sélectionner les cellules que nous avons dans notre colonne geor et nous allons vouloir limiter la recherche uniquement au terme fiction ensuite nous fermons cette parenthèse et nous définissons bien entendu que nous voulons effectuer une recherche exacte avec la valeur zéro pour l'argument typ maintenant nous pouvons fermer les deux parenthèses et valider pour voir que ici nous avons une erreur et donc pour corriger cette erreur il va tout simplement suffire de multiplier le tableau de recherche par 1 pour remplacer les vrais par des 1 et les faux par des zé et donc ici nous avons exactement le même résultat que tout à l'heure à savoir que la formule nous informe que la première occurrence du terme fiction correspond à 120 ventes il s'agit donc du livre Le Petit Prince maintenant il faut savoir que si nous voulons cumuler cette recherche avec une deuxième recherche donc ici nous voyons que nous voulons les ventes de fiction du mois d'avril il suffit tout simplement de remplacer le X 1 par le deè critère de recherche que nous saisissons ici encore une fois entre parenthèses à savoir donc que nous allons chercher uniquement les mois qui correspondre à avril donc nous sélectionnons la plage C7 jusqu'à C16 et donc égal avril ici pas besoin de multiplier par 1 automatiquement Excel lorsqu'il va réaliser ses calculs va multiplier les plages et donc devrait seront égales à 1 tandis que un faux et un vrai ou deux faux seront égales à é comme nous pouvons le voir donc en utilisant l'outil d'évaluation des formules donc ici nous pouvons fermer et nous voyons que le résultat retourné par la formule est ici égal à 200 200 qui correspondent effectivement aux ventes de fiction donc c'est-à-dire au fil 1984 du mois d'avril et donc maintenant pour terminer découvrons encore une autre astuce avancée de la fonction index éive qui permet d'effectuer des recherches en 3D une recherche en 3D va donc être une recherche dans laquelle nous allons non seulement appliquer un filtre sur les colonnes comme nous venons de le faire mais également sur les lignes donc ici nous nous plaçons sur la cellule G14 et comme vous pouvez le voir nous allons vouloir obtenir le mois qui correspond aux ventes du livre Harry Potter donc nous appelons la fonction index comme nous nous avons l'habitude de le faire sauf que ici nous n'allons pas sélectionner uniquement les cellules qui correspondent à une colonne mais les cellules qui correspondent aux trois colonnes dans lesquelles potentiellement nous pourrions être amenés à obtenir le résultat c'est-à-dire soit nous voulons obtenir le genre soit le moi soit les ventes ensuite nous appelons la fonction éive une première fois afin de déterminer la ligne dans laquelle se trouve la valeur à récupérer donc comme nous avons l'habitude de le faire ici nous allons rechercher le livre Harry Potter au sein de la liste dé livres c'est-à-dire les cellules A7 jusqu'à A16 comme d'habitude il est question ici d'effectuer une recherche exacte nous utilisons donc la valeur éro pour le 3è argument et donc c'est là que réside l'astuce nous allons maintenant pouvoir utiliser le troème argument facultatif numéro de colonne afin de déterminer encore une fois ce numéro de colonne à partir de la fonction éive ici donc nous allons vouloir récupérer le moi à l'intérieur de la plage des cellules qui correspondent au titre de colleur c'est-à-dire les cellules B6 à6 genre mois vente nous effectuons une recherche exacte et ensuite nous pouvons fermer les deux parenthèses donc nous voyons que ici que le livre Harry Potter a été vendu au cours du mois d'avril mais si nous le souhaitons nous pouvons obtenir une autre information par exemple le genre Harry Potter c'est-à-dire ici la fantaisie ou alors le nombre de ventes réalisé ici 300 ventes pour la ligne donc Harry Potter nous pouvons également sélectionner un autre ouvrage par exemple la peste et donc soit 75 ventes correspondent à celui-ci et donc maintenant la dernière astuce que nous allons voir va être d'utiliser des plages dynamiques à la place de venir sélectionner directement des références de plage de cellule ce qui va permettre d'ajouter facilement des lignes à la suite de notre tableau qui seront prises en compte dans les calculs par exemple ici imaginons que nous ayons une nouvelle ligne qui correspond donc au Petit Prince qui est donc du genre de la fiction et cette ligne va correspondre aux ventes du mois de novembre mois durant lequel nous aurons V du 400 livres et donc si nous voulons connaître le nombre de ventes qui correspondent à la fiction du mois de novembre en modifiant tout simplement ici la valeur que nous utilisons en tant que deuxième critère par novembre nous allons être confrontés à une erreur de type na étant donné que si nous regardons la plage des cellules utilisées dans la formule nous voyons que celle-ci s'arrête juste au-dessus pour contourner ce problème c'est très simple il va tout simplement nous suffir de modifier le tableau afin d'en créer un tableau structuré donc pour cela nous sélectionnons l'une des cellules de ce tableau nous nous rendons dans le menu Insertion et nous cliquons sur tableau ici nous cochons bien l'option mon tableau comporte des en tête et nous validons en appuyant sur OK maintenant nous allons pouvoir modifier la plage des cellules utilisé ici pour obtenir le nombre de vente nous n'allons pas saisir les coordonnées des 7 jusqu'à des 166 mais nous allons cliquer sur l'entête vente afin de récupérer la colonne vente du tableau 1 pour obtenir le genre c'est pareil nous supprimons la référence B7 jusqu jusqu'à B16 et nous cliquons donc sur l'entê geor et pour finir donc nous allons effectuer la même opération pour obtenir la liste des mois maintenant nous validons en appuyant sur Entrée et le Petit Prince est bien pris en compte si nous ajoutons une nouvelle ligne donc par exemple d'une 2 qui serait donc un livre de fiction vendu au cours du mois de décembre et ici donc nous aurons vendu 200 ouvrages il va maintenant nous suffir de modifier la formule que nous avons dans la cellule G10 afin de récupérer uniquement les ventes de fiction du mois de décembre ensuite nous validons et le résultat est bien pris en compte dans la formule la plage de cellule est maintenant dynamique donc comme d'habitude vous pouvez télécharger le support de secours en suivant le lien que vous trouverez dans la description de la vidéo si la vidéo vous a plu n'oubliez pas de la liker et de la partager à vos collègues et amis sur l'ensemble de vos réseaux sociaux et vous pouvez également me laisser vos impressions dans l'espace des commentaires je lirai chacun d'entre eux et y répondrai avec plaisir évidemment si ce n'est pas encore fait abonnez-vous à la chaîne pour ne manquer aucune astuce c'est également très important pour m'aider à gagner en visibilité afin de pouvoir développer la chaîne et continuer de vous proposer des tas de nouvelles vidéos merci d'avoir suivi cette vidéo et à très bientôt pour un prochain tuto