Excel 2010 : 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
Si la fonction RECHERCHEV est pratique, elle exige des tableaux avec une structure précise. Passez outre ce problème avec le duo de fonctions INDEX et EQUIV, capable d'effectuer n'importe quelle recherche matricielle.
09:11

Transcription

Durant cette séquence, nous allons apprendre ensemble à réaliser une recherche matricielle complète. Pour cela, nous allons avoir besoin de deux fonctions : INDEX et EQUIV. Nous allons d'abord voir comment elles fonctionnent séparément, et puis ensuite, nous allons les combiner. Commençons par INDEX. INDEX si vous voulez, c'est le roi de la bataille navale. Nous allons nous concentrer sur la première ligne. Il a besoin pour fonctionner d'une matrice, d'un numéro de ligne et d'un numéro de colonne. « matrice », vous lui indiquez son terrain de jeu, sa plage. Nous, on va prendre cet exemple, je vais sélectionner tout le tableau. Clic gauche enfoncé, je déroule, je lâche. Point virgule, ici, je dois lui indiquer un nombre. Par exemple, je peux lui indiquer la troisième ligne. J'écris « 3 ». Point virgule, un numéro de colonne, je vais lui indiquer le « 1 », OK. INDEX, lui, va rechercher dans le tableau ce qu'il y a en troisième ligne, et à la première colonne. Parenthèse, Entrée. Troisième ligne, première colonne, c'est bien la lettre « k », il a bien fait son travail. La chose qui est extrêmement importante à comprendre est que là, j'ai indiqué le chiffre à la main, mais j'aurais très bien pu effectuer un calcul : « 1+2 » ça fonctionne toujours, j'obtiens toujours « k ». Ce nombre, j'aurais pu l'obtenir grâce à une fonction, « somme » de « B5 » et « B6 » je ferme la parenthèse, Entrée. La somme de «B5 » et de « B6 », ça fait « 3 ». Troisième ligne, première colonne, j'obtiens toujours la lettre « k ». C'est ce que nous allons faire, en fait. Pour indiquer des nombres à INDEX, plutôt que de les écrire directement, nous utiliserons des fonctions pour augmenter nos possibilités. Quelle fonction on va utiliser ? On utilisera EQUIV. EQUIV, comment il marche ? C'est une sorte de super traducteur, je vous montre. Je vais écrire « janvier », clic gauche enfoncé, je déroule, je suis en « décembre ». INDEX, pour fonctionner, il a besoin de nombres, mais, des fois, je n'ai pas accès à ces nombres, je n'ai que des mots, ce n'est pas grave, c'est là qu'EQUIV intervient. J'écris =equiv et lui, il a besoin de deux choses : que je lui indique une valeur à traduire, et l'endroit où se trouve cette valeur. Par exemple, je vais lui écrire « juillet », j'ouvre mes guillemets, j'écris « juillet », je ferme mes guillemets. Point virgule, je lui indique où est-ce qui doit trouver ce mot. Clic gauche enfoncé sur « janvier », je déroule, je lâche, point virgule. Là, il me demande le degré de précision que je souhaite. Ne vous embêtez pas, choisissez toujours Correspondance exacte. Vous pouvez soit double-cliquer, soit écrire « zéro », c'est pareil. Je double clique, ça me fait donc « zéro », je ferme la parenthèse. En gros, je lui ai dis : «trouve moi juillet dans cette colonne » Je fais Entrée. Là, il m'indique : « je l'ai trouvé, il est en septième position » Il m'indique le numéro « 7 » , il a traduit le mot en chiffres. Très important ici, « juillet », je l'ai écrit. Mais plutôt que de l'écrire, j'aurais pu sélectionner une cellule et le mot « juillet », l'écrire dedans. C'est ce que je vais faire. Valeur cherchée dans cette cellule forcément, il trouve rien, rien n'est écrit. Mais là, je vais écrire « juillet ». Il m'indique que « juillet » est en septième position. J'écris « août », huitième position, etc. EQUIV me sert donc de traducteur, et il va alimenter INDEX pour afficher la bonne information. Ça va être très complexe, mais on va y aller étape par étape. Je vais aller en « Feuil2 » pour un exemple typiquement professionnel. J'ai une gigantesque base de données avec « Prénom », « Nom », « Matricule », « Adresse », « Code postal », « Ville », « Téléphone », « Adresse mail ». J'ai 10 000 éléments. On aurait pu utiliser RECHERCHEV pour retrouver une information, mais RECHERCHEV ne fonctionne que si l'élément que j'indique se trouve en première colonne. En gros, si je lui indique le prénom, il peut me retrouver ce que je veux. Par contre, si je lui indique un matricule, il ne peut pas me retrouver le prénom. Bref, l'élément que je lui indique doit se trouver en première colonne. INDEX et EQUIV dépassent cette limitation. Je peux lui indiquer n'importe quel élément, il peut me retrouver n'importe quel élément. Inconvénient : la formule est complexe, mais c'est pas grave, on va la faire ensemble. Je vais aller en « Feuil3 ». Ici, je vais écrire « Matricule ». C'est ici que j'indiquerais donc le matricule. Et là, je vais lui indiquer tous les éléments qu'il doit me retrouver : « Prénom », « Nom », « Adresse », « Code postal », « Ville », « Téléphone, « Adresse Mail ». La restriction que vous avez, c'est que ces intitulés doivent être parfaitement identiques à ceux qui sont présents ici, tout le long. S'ils ne sont pas identiques, ça ne fonctionne pas. Commençons, c'est ici que je souhaite afficher les résultats, donc, je vais écrire ma première formule : =index. Je me concentre sur la première ligne « matrice », je lui indique son terrain de jeu. Son terrain de jeu, c'est toutes ces colonnes. C'est là où il va pouvoir effectuer ses recherches. Je reviens en arrière. Attention, quand vous faites des aller-retours d'une feuille à une autre, là, vous pouvez voir « Feuil3 », si je clique ici, il me met « Feuil2 » . Attention à l'endroit où vous vous trouvez. Donc nous, le tableau, il se trouve bien en « Feuil2 ». Point virgule, numéro de ligne. Je vais utiliser EQUIV comme traducteur. Mon numéro de ligne, en fait, va être obtenu grâce au matricule, c'est le matricule que je lui ai donné. On va commencer à écrire EQUIV. Où est-ce que je vais lui donner le matricule ? Le matricule, je vais lui donner ici, c'est ici que je vais l'écrire. C'est bon. Point virgule, « tableau_recherche », où se trouvent tous les matricules ? Ils se trouvent dans la feuille précédente, ici et dans cette colonne. L'endroit où je dois écrire le matricule, l'endroit où ils se trouvent tous point virgule, je mets une Correspondance exacte. je ne cherche pas à réfléchir. Je ferme la parenthèse. J'ai de quoi trouver le numéro de ligne, donc c'est grâce au matricule. Pour trouver la bonne colonne maintenant : point virgule, je vais m'aider des intitulés, je retourne en « Feuil3 ». En gros, je vais m'aider de ce qu'il y a écrit à côté. S'il trouve « Prénom », il va rechercher « Prénom » dans le tableau et me ressortir un prénom. Écrivons. EQUIV, j'ouvre la parenthèse, il doit chercher un élément. L'élément que je recherche, c'est celui-ci. Où est-ce qu'il doit le chercher ? Je reviens dans le tableau précédent. Il doit aller rechercher dans cette ligne. Souvenez-vous que EQUIV fonctionne en colonne et en ligne. Je lâche, je précise une Correspondance exacte. Je ferme la parenthèse, j'ai fini avec EQUIV, je referme l'autre parenthèse. J'ai fini avec INDEX. Entrée. Là, il ne trouve rien, c'est normal, je ne lui ai pas indiqué de matricule. Les matricules sont sous cette forme : « xxx- » et cinq caractères. Je vais écrire un matricule : « xxx-1234 », et on va mettre le nombre « 9 ». J'obtiens bien le prénom de la personne qui correspond au matricule, je vais vérifier : « xxx » avec des zéros et des neufs, c'est bien Patricia. Théoriquement, j'aurais juste besoin d'étirer la formule. Clic gauche enfoncé, mais ça ne marche pas, pourquoi ? Nous avons déjà eu ce problème. Je regarde, tout est bon. Si je regarde là, je vois que les choses se décalent. Je dois faire extrêmement attention aux références relatives et absolues. J'ai eu des décalages dans mes critères. Là, ça aurait dû rester sur le matricule, et surtout, j'ai sélectionné des choses dans ce tableau, ces intitulés. Je dois faire en sorte qu'ils ne bougent pas. Je retourne dans ma formule, ici, c'est donc « C2 », Je vais faire en sorte que le chiffre ne bouge pas. OK. Ensuite, dans mon deuxième EQUIV, là où il y avait les prénoms, mes intitulés sont ici. « Feuil2!A1:H1 », c'est la ligne que j'avais sélectionnée. Je vais faire en sorte qu'elle ne bouge pas. $ $ Entrée. Réessayons, clic gauche enfoncé et je descends. J'ai absolument toutes les informations dont j'ai besoin. J'écris un matricule au hasard. Entrée. J'ai toutes les informations nécessaires dans une base de données que j'ai déjà écrite. C'est l'une des combinaisons de fonction les plus complexes à maîtriser. Mais une fois que vous la maîtrisez, vous avez accès à l'un des outils de recherche les plus puissants de Microsoft Excel. Je vous invite à vous y entraîner dès que possible. Vous savez désormais comment effectuer une recherche matricielle complète sur Microsoft Excel.

Excel 2010 : 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.

2h55 (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 !