Excel 2013 : Les formules et les fonctions avancées

Réaliser une recherche matricielle complète

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
La fonction RECHERCHEV est pratique, mais elle exige des tableaux avec une structure particulière. Passez outre ce problème avec le duo de fonctions INDEX et EQUIV, capable d'effectuer n'importe quelle recherche matricielle.
10:39

Transcription

Durant cette séquence, nous allons apprendre ensemble à réaliser une recherche matricielle complète dans Microsoft Excel. Pour y arriver, nous aurons besoin de deux fonctions, INDEX et EQUIV. Nous allons d'abord voir comment elles fonctionnent séparément, ensuite nous allons apprendre à les combiner. Commençons d'abord par INDEX. INDEX, si vous voulez, c'est le roi de la bataille navale. On va se concentrer sur la première ligne. Ce dont il a besoin, c'est d'un terrain de jeu, d'un numéro de ligne et d'un numéro de colonne. Par terrain de jeu, j'entends une plage. Nous allons donc sélectionner celle-ci, clic-gauche enfoncé, je déroule, je saisis tout, je lâche, point-virgule. Maintenant que la plage est sélectionnée, je dois lui indiquer un numéro de ligne, point-virgule, un numéro de colonne, c'est ce que je vais faire. Numéro de ligne, je lui indique la troisième, point-virgule, numéro de colonne, la première, parenthèse pour fermer. Ce que va faire INDEX, c'est qu'il va aller au croisement de ces deux informations pour m'afficher le résultat qu'il y trouve. Entrée. Ligne 3, Colonne 1, j'ai bien la lettre k. Ce qui va être extrêmement important à comprendre pour la suite, c'est que le chiffre que j'ai là, je ne suis pas obligé de l'écrire directement, je peux utiliser un calcul, par exemple. Là, j'ai écrit 3, j'aurais pu écrire 1 + 2. J'ai toujours la lettre k, pour Excel, c'est la même chose. Encore plus important, j'aurais pu utiliser une fonction, par exemple SOMME. Je sélectionne ces deux éléments, je ferme la parenthèse. J'ai fait la somme de ces deux cellules, donc 1 + 2, ça fait 3. Il me choisit toujours la troisième ligne. J'obtiens toujours k. Ici, comme fonction, nous n'allons pas utiliser SOMME, mais nous allons utiliser EQUIV. C'est la deuxième que nous allons voir ensemble. EQUIV, si vous voulez, c'est un super traducteur. On va prendre un exemple. Je vais écrire janvier, clic-gauche enfoncé, je déroule et j'ai tous les mois. À partir de là, je vais écrire =equiv, voilà. Il a besoin de trois choses, d'une valeur cherchée, d'un tableau de recherche et d'un type, ça correspond à une précision, nous allons voir. Valeur cherchée, je lui stipule évidemment ce que je recherche. Tableau de recherche, ce sera son terrain de jeu à lui, où est-ce qu'il doit travailler. Il va convertir cette information en nombre. INDEX, il ne fonctionne qu'avec des nombres. S'il a des mots, il ne fonctionne plus, c'est à ça que va servir EQUIV. Valeur cherchée, par exemple, je vais lui indiquer juillet. Juillet, INDEX ne peut pas l'interpréter. Point-virgule, tableau de recherche, je précise tous ces éléments, point-virgule. Là, ne cherchez pas à réfléchir en termes de précision, choisissez Correspondance exacte. Il vous suffit de double-cliquer dessus ou d'écrire 0, j'écris 0. Je ferme la parenthèse, j'ai tous mes éléments, je fais Entrée. Là, EQUIV a traduit, il me dit : écoute, juillet se trouve en septième position, c'est donc un chiffre, et INDEX comprend les chiffres. Moi, mon but, ça ne va pas être d'écrire juillet, mais de lui spécifier un endroit où je pourrais l'écrire plus facilement, vous allez voir. J'efface juillet et je vais choisir cette cellule. Et je vais pouvoir écrire juillet dedans. À quoi ça me sert ? N/A, normal, il ne trouve pas ce que je recherche, je ne le lui ai pas encore écrit. Jécris juillet, Entrée, ça fonctionne, il trouve le bon nombre. Mais le fait d'utiliser cette méthode, cela me permet de changer rapidement. J'aurais pu écrire août, il me dit : là, en huitième position. J'aurais pu écrire janvier, première position, etc. EQUIV traduit les informations, les mots, en chiffres et INDEX se nourrit de ces chiffres pour m'afficher la bonne info. Ça peut paraître un peu théorique pour le moment. On va prendre un cas très concret qu'on retrouve fréquemment en entreprise. En Feuil2, j'ai une gigantesque base de données, avec Prénom Nom, Matricule, Adresse, Code postal, Ville, Téléphone et Adresse mail. J'ai 10 000 éléments. Mon but va être de créer un outil de recherche. Je lui fournis le matricule, il me fournit le reste. On aurait pu se dire : on peut utiliser recherchev comme fonction, qui est beaucoup plus pratique. Le problème, avec recherchev, ça ne marche que si l'élément que vous lui fournissez se trouve en première colonne. Traduction, si vous lui donnez le prénom il peut vous retrouver tout le reste. Mais si l'information dont vous avez besoin se trouve avant, ça ne marche plus. Exemple, si vous lui donnez un matricule, il est incapable de vous retrouver le prénom. Les fonctions INDEX et EQUIV dépassent ces limitations. Vous pouvez lui donner n'importe quoi, pour retrouver n'importe quelle info. Nous allons voir comment. Ҫa va être très complexe, mais nous allons y aller étape par étape. Je vais créer mon outil de recherche en Feuil3. Ici je vais écrire Matricule. Et là, je vais créer mes éléments de recherche, Prénom, Nom, Adresse, Code postal, Ville, Téléphone, Adresse mail. Là, vous avez tout de même une restriction pour que cela fonctionne bien. Les mots que vous allez employer ici doivent strictement reprendre les intitulés. Si vous avez écrit Code postal, il ne faudra pas écrire Code. Si vous avez écrit Ville, il ne faudra pas écrire Ville d'origine, etc. Mon but donc, j'écris Matricule et là, il doit afficher automatiquement les infos qui s'y rapportent. Je vais commencer par écrire ici. Je commence par =index, j'ouvre la parenthèse, matrice, le terrain de jeu, numéro de ligne, numéro de colonne. Le terrain de jeu donc, je vais en Feuil2, je choisis toutes les colonnes, c'est là qu'il va devoir travailler. Attention, quand vous cliquez sur une autre feuille, quand vous êtes en train d'écrire une formule. Excel s'adapte en temps réel, donc il faut faire attention à ce que les références soient bonnes. Moi, mon tableau, par exemple, n'est pas en Feuil3, il est en Feuil2. Donc, pour écrire, je dois bien mettre point-virgule. Là comme ça, si je me déplace, Feuil3 et Feuil2, il l'indiquera après, mais ça, ça ne bougera plus. Continuons. J'ai mon terrain de jeu. Là, je dois lui préciser le numéro de ligne. Numéro de ligne, c'est le matricule qui va me le fournir. C'est ceci qui va me permettre de choisir la bonne ligne. Le matricule, j'ai besoin de le traduire en chiffres, je vais donc utiliser EQUIV. J'ouvre la parenthèse, valeur cherchée, c'est mon matricule. Où est-ce que je l'écris ? Ici. Point-virgule, Tableau de recherche, où se trouvent tous mes matricules ? IIs se trouvent dans cette colonne. Point-virgule, la précision, je ne réfléchis pas, 0, Correspondance exacte. Je ferme la parenthèse, EQUIV est terminée. Maintenant, je vais pouvoir écrire mon numéro de colonne. Point-virgule, pour mon numéro de colonne, je vais utiliser les éléments qui sont directement écrits ici. Par exemple, si à côté, il y a Prénom, Excel, de lui-même, va chercher ce qu'il y a dans la colonne Prénom etc. Je vais devoir utiliser à nouveau EQUIV. Je l'écris, j'ouvre la parenthèse, valeur cherchée, l'élément qu'il y a à côté, point-virgule, où est-ce qu'il doit le rechercher ? En Feuil2. Tous mes intitulés se trouvent ici. Je les sélectionne. Rappelez-vous qu'EQUIV fonctionne en colonnes, mais aussi en lignes. Je sélectionne donc toute ma ligne. Point-virgule, degré de correspondance exact, 0, je ne réfléchis pas. Parenthèse, EQUIV est terminé. Parenthèse, INDEX est terminé. J'ai une parenthèse noire, ça devrait être bon, Entrée. N/A, c'est normal, je ne lui ai pas indiqué de matricule, je vais le faire. J'en écris un et ça fonctionne, il me retrouve bien le bon prénom, avec 09. Je cherche 09, c'est bien Patricia. Mais je vais avoir un problème. Si j'étire directement la formule, clic-gauche enfoncé, je descends, je vais avoir des soucis, oui, regardez. On va avoir des problèmes avec les références relative et absolue. Là, le nom, je l'ai bien, mais je n'ai plus mon matricule, alors que pour Patricia, c'était bon. Autre problème que je vais avoir, mes intitulés dans mon autre feuille ne sont pas bien sélectionnés. Là, j'ai A1, H1, là j'ai A2, H2. Donc je n'ai plus mes intitulés. On va placer des dollars pour corriger tout ça. Je corrige ceci, je bloque le chiffre, je corrige mes intitulés, je place le $. Ceci devrait être la formule finale. J'appuie sur Entrée, clic-gauche enfoncé, je descends. Et là, Excel a automatiquement retrouvé toutes les informations dont j'ai besoin. Surtout, si je change de matricule, je mets une autre info, il s'adapte immédiatement. Vous avez sous les yeux l'un des outils de recherche les plus puissants que peut proposer Microsoft Excel. Le seul problème est sa complexité d'écriture. Toutefois, si vous vous entraînez, cela deviendra de plus en plus simple. N'hésitez pas à revisionner cette vidéo et à vous entraîner étape après étape. Vous savez désormais comment réaliser une recherche matricielle complète dans Microsoft Excel.

Excel 2013 : Les formules et les fonctions avancées

Créez des formules efficaces et exploitez au maximum les tableaux et les calculs. Redécouvrez les fonctions essentielles, exploitez les dates et gérez vos erreurs.

3h13 (42 vidéos)
Aucun commentaire n´est disponible actuellement
Spécial abonnés
Date de parution :16 févr. 2018
Mis à jour le:4 avr. 2018

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !