Comment écrire une formule Excel qui recherchera une valeur sur une feuille différente en fonction du produit sélectionné. Comment extraire des données d'une feuille de calcul différente pour chaque produit.
Regarder la vidéo
- Rhonda de Cincinnati: Comment rechercher à la fois la ligne et la feuille de calcul?
- Utilisez la colonne Date pour déterminer quelle feuille utiliser
- Étape 1: Créez une RECHERCHEV régulière et utilisez FORMULATEXT pour voir à quoi devrait ressembler la référence
- Étape 2: Utilisez la concaténation et la fonction TEXT pour créer une référence qui ressemble à la référence de tableau de tableau dans la formule
- Étape 3: Créez votre RECHERCHEV, mais pour le tableau de table, utilisez INDIRECT (résultats de l'étape 2)
- Étape 4: Copiez la formule de l'étape 2 (sans le signe égal) et collez-la dans la formule de l'étape 3
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2173: recherchez la feuille et la ligne.
Salut, bon retour sur le netcast, je suis Bill Jelen. J'étais à Cincinnati la semaine dernière et Rhonda à Cincinnati avait cette excellente question. Rhonda doit rechercher ce produit, mais la table de recherche est différente, selon le mois. Vous voyez, nous avons différents tableaux de recherche ici pour janvier à avril, et probablement pour les autres mois également. Bien.
Je vais donc utiliser INDIRECT pour résoudre ce problème, mais avant de faire INDIRECT, je trouve toujours plus facile de simplement faire une recherche directe. Ainsi, nous pouvons voir à quoi va ressembler le formulaire. Nous recherchons donc A1 dans ce tableau en janvier, et nous voulons que la septième colonne, virgule FALSE, toutes les VLOOKUP se terminent par FALSE. (= RECHERCHEV (A2, 'janvier 2018'! A1: G13,7, FALSE)). Bien.
Et bien, c'est la bonne réponse. Ce qui m'intéresse vraiment, c'est d'obtenir le texte de la formule. Cela me montre donc à quoi ressemblera la formule. Et toute l'astuce ici est que j'essaie de créer une colonne Helper qui ressemblera exactement à cette référence, non? Donc cette partie - juste cette partie juste là. Bien. Donc, cette colonne Helper doit ressembler à cette chose. Et la première chose que je veux faire, c'est que nous allons utiliser la fonction TEXT de la date - la fonction TEXT de la date - pour obtenir mmm, espace, aaaa - donc, "mmm aaaa" comme ça - - qui devrait renvoyer, pour chacune des cellules, quel mois nous recherchons. Maintenant, je dois envelopper cela dans des apostrophes. S'il n'y avait pas eu de nom d'espace là-dedans, je n'aurais pas besoin des apostrophes, mais j'en ai. Donc nous allons concacténer d'avance,l'apostrophe, c'est donc une citation - une apostrophe, une citation - une esperluette, puis ici, une autre citation, une apostrophe et un point d'exclamation, A1: G13, citation de fermeture, esperluette là.
Bien. Alors maintenant, ce que nous avons réussi ici dans la colonne Helper, c'est que nous avons construit quelque chose qui ressemble exactement au tableau de table dans la RECHERCHEV. Bien. Donc, notre réponse, alors, ça va être = VLOOKUP de cette cellule, A2, virgule, puis quand nous arriverons au tableau de table, nous allons utiliser l'INDIRECT. INDIRECT est cette fonction sympa qui dit: "Hé, voici une cellule qui ressemble à une référence de cellule, et je veux que vous alliez à F2, prenez l'élément qui ressemble à une référence de cellule, puis utilisez ce qui se trouve dans cette référence de cellule comme la réponse, "virgule, 7, virgule, FALSE," comme ça. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)) Très bien, alors maintenant, à la volée, nous choisissons un table de recherche différente et renvoyer les valeurs selon que c'est avril ou quoi.
Bien. Alors prenons ce 24/04, je le changerai en 17/02/2018, comme ça, et nous devrions voir que 403 changera en 203 - parfait. Ça fonctionne. Bien. Maintenant, nous n'avons pas besoin de ces deux colonnes ici, bien sûr, et vraiment, si vous y réfléchissez, nous n'avons pas besoin de toute cette colonne. Nous pourrions prendre tout cela, sauf pour le signe égal, Ctrl + C pour le copier, puis là où nous avons D2, il suffit de coller, comme ça. Parfait. Double-cliquez pour abattre cela et vous en débarrasser. Voilà notre réponse. Très bien, nous allons utiliser notre texte de formule ici, juste pour jeter un coup d'œil à cette réponse finale.
Je dois vous dire que si je devais construire cette formule à partir de rien, je ne le ferais pas. Je ne pourrais pas le faire. Je vais tout gâcher, c'est sûr. C'est pourquoi je le construis toujours par étapes - je détermine à quoi va ressembler la formule, puis je concentre la colonne Helper qui sera utilisée dans l'INDIRECT, puis enfin, peut-être ici à la fin, remets tout en place.
Hé, de nombreux conseils comme celui-ci dans le livre Power Excel avec. Ceci est l'édition 2017 avec 617 mystère Excel résolu. Cliquez sur ce "I" dans le coin supérieur droit pour plus d'informations.
D'accord, récapitulation de cet épisode: Rhonda de Cincinnati - comment rechercher à la fois la ligne et la feuille de calcul. J'utilise la colonne Date pour déterminer quelle feuille utiliser; je construis donc une RECHERCHEV régulière et j'utilise le texte de la formule pour voir à quoi devrait ressembler la référence; puis créez quelque chose qui ressemble à cette référence en utilisant la fonction de texte pour convertir la date en mois et année; utilisez Concactenation pour créer quelque chose qui ressemble à la référence; puis, lorsque vous créez votre RECHERCHEV pour le deuxième argument, le tableau de table, utilisez INDIRECT; puis pointez sur les résultats de l'étape 2; puis la quatrième étape facultative là, copiez la formule de l'étape 2, sans le signe égal, et collez-la dans la formule de l'étape 3, de sorte que vous vous retrouvez avec une seule formule.
Eh bien, je tiens à remercier Rhonda d'être venue à mon séminaire à Cincinnati, et je tiens à vous remercier d'être passé. Je vous verrai la prochaine fois pour une autre diffusion sur Internet.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2173.xlsm
