Comment faire un calcul (tel que RECHERCHEV) pour chaque élément qui a été Alt + Entrée dans une cellule.
Regarder la vidéo
- Un visualiseur télécharge des données à partir d'un système où chaque élément est séparé par Alt + Entrée
- Bill: Pourquoi fais-tu ça? Viewer: C'est ainsi que j'hérite des données. Je veux poursuivre dans cette voie.
- Bill: Que voulez-vous faire avec les 40% de valeurs qui ne figurent pas dans le tableau? Visionneuse: pas de réponse
- Bill: Il existe un moyen compliqué de résoudre ce problème si vous disposez des derniers outils Power Query.
- Au lieu de cela, une macro VBA pour le résoudre - la macro devrait fonctionner jusqu'à Excel 2007
- Au lieu de rechercher VLOOKUP, effectuez une série de Find & Replace with VBA
Transcription vidéo
Apprenez Excel de, Podcast Episode 2150: RECHERCHEV Chaque Alt + valeur entrée dans chaque cellule.
Hey. Bienvenue à nouveau sur le netcast. Je suis Bill Jelen. Aujourd'hui, une des questions les plus bizarres. Quelqu'un a dit, hé, je veux faire une RECHERCHEV pour chaque valeur de la cellule, et lorsque j'ai ouvert le fichier Excel, les données ont été ALT + Entrées. Donc, il y a 4 éléments dans cet ordre et ils sont tous séparés par ALT + ENTRÉE, puis seulement 2 ici et 6 ici et ainsi de suite.
Je suis retourné voir la personne qui a envoyé cela. J'étais comme, eh bien, c'est une très mauvaise façon de stocker ces données. Pourquoi fais-tu ça? Et il était comme, je suis comme si je ne le fais pas. C'est ainsi que les données sont téléchargées. J'ai dit, est-ce que je peux le diviser en lignes séparées? Non, vous devez le garder de cette façon.
Bien. Donc, il n'y a pas de bon moyen de faire une RECHERCHEV pour chaque élément individuel, et demain, sur l'épisode de demain, 2151, je vais vous montrer comment nous pouvons utiliser une toute nouvelle fonctionnalité dans Power Query pour le faire, mais vous auriez d'avoir Office 365 pour avoir cela.
Donc, aujourd'hui, je veux utiliser une méthode qui va tout le chemin en arrière, et ce que j'ai fait ici, c'est que j'ai créé une nouvelle feuille de calcul avec le LOOKUPTABLE, voici donc les éléments. J'ai également remarqué qu'il y a tout un tas de choses, environ 40% des choses ici, ne sont pas dans le LOOKUPTABLE. J'ai dit, que voulez-vous faire là-bas, et pas de réponse à cette question, alors je vais simplement les laisser tels quels si je ne trouve pas de correspondance.
D'accord, donc, ce que j'ai ici, c'est que j'ai une feuille appelée LOOKUPTABLE et vous verrez que mon fichier est actuellement stocké au format xlsx et je vais utiliser une macro VBA. Pour utiliser une macro VBA, vous ne pouvez pas l'avoir en tant que xlsx. C'est contre les règles. Donc, vous devez SAVE AS et enregistrer ceci est xlsm. FILE, SAVE AS et changez-le de WORKBOOK en un MACRO-ENABLED WORKBOOK XLSM ou un BINARY WORKBOOK - l'un ou l'autre fonctionnera - très bien, et cliquez sur ENREGISTRER.
Très bien, nous sommes maintenant autorisés à exécuter des macros. ALT + F11 pour accéder à l'enregistreur de macros. Vous commencez avec ce grand écran gris. INSERT, MODULE, et voici notre module, et voici le code. Donc, je l'ai appelé ReplaceInPlace et je définis une feuille de calcul. C'est le LookupTable. Vous y mettriez votre vrai nom de feuille de calcul de table de recherche, puis ma table de recherche commence dans la colonne A, qui est la colonne 1. Donc, je vais à la toute dernière ligne de la colonne 1, appuyez sur la touche FIN et la flèche vers le haut, ou , bien sûr, la flèche CONTROL + UP ferait la même chose, déterminer de quelle ligne il s'agit, puis nous allons passer de chaque ligne de 2 à FinalRow. Pourquoi 2? Eh bien, parce que les en-têtes sont dans la ligne 1. Je veux donc remplacer, en commençant à la ligne 2 jusqu'à la dernière ligne, et donc, pour chaque ligne de 2 à FinalRow, FromValue est ce que 's dans la colonne A et ToValue est ce qui se trouve dans la colonne B.
Maintenant, si, pour une raison quelconque, vos données étaient en J et K, alors ce J serait la 10e colonne, donc vous mettez un 10 là, et K serait la 11e colonne, puis, dans la sélection, nous allons remplacer la FromValue à la ToValue. C'est vraiment important ici. xlPart, xlPart - et c'est un L, pas un nombre 1 - xlPart qui dit que cela nous permettra de remplacer une partie de la cellule car ces numéros de pièce sont tous séparés par un caractère de saut de ligne. Même si vous ne pouvez pas le voir, il est là. Donc, cela devrait nous permettre de ne pas mettre à jour accidentellement la mauvaise chose, puis xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Bien. Voilà donc notre petite macro ici. Essayons. Nous prendrons ces données, et je ne veux rien détruire, donc je vais simplement prendre les données originales et les copier vers la droite. Bien. Donc, nous avons notre sélection là-bas. En fait, je vais commencer à partir de ce point. CONTROL + BACKSPACE, puis ALT + F8 pour obtenir une liste de toutes les macros. Voilà notre REPLACEINPLACE. Je clique sur EXÉCUTER, et partout où il trouve un élément dans la LOOKUPTABLE, il remplace ce numéro d'élément par l'élément, faisant apparemment une RECHERCHEV, bien que nous ne le résolvions pas du tout avec une RECHERCHEV
Bien. Alors, hé, le tout nouveau livre qui est sorti - Power Excel With, l'édition 2017, 617 Excel Mysteries Solved - toutes sortes de nouveaux conseils.
Résumé du jour: le spectateur télécharge des données à partir d'un système où chaque élément est séparé par ALT + ENTRÉE, puis doit effectuer une RECHERCHEV à chaque élément, et, vous savez, pourquoi je fais cela; alors, la personne a dit, je ne le fais pas mais je dois le garder ainsi; et puis 40% des valeurs ne sont pas dans le tableau, eh bien, pas de réponse; donc je suppose qu'ils vont ajouter ces éléments au tableau; maintenant, demain, nous allons parler de la façon de résoudre ce problème avec Power Query, mais, aujourd'hui, cette macro fonctionnera depuis le début dans toutes les versions Windows d'Excel, en remontant au moins à Excel 2007; donc, au lieu d'un VLOOKUP, juste une série de recherche et de remplacement par VBA.
Et bien Salut. Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2150.xlsm
