Cómo hacer un cálculo (como BUSCARV) para cada elemento que ha sido Alt + Ingresado en una celda.
Ver video
- Un espectador descarga datos de un sistema donde cada elemento está separado por Alt + Enter
- Bill: ¿Por qué haces esto? Visor: Así es como heredo los datos. Yo quiero mantenerlo así.
- Bill: ¿Qué quieres hacer con el 40% de los valores que no están en la tabla? Visor: Sin respuesta
- Bill: Hay una forma complicada de resolver esto si tiene las últimas herramientas de Power Query.
- En su lugar, una macro de VBA para resolverlo: la macro debería funcionar hasta Excel 2007
- En lugar de hacer BUSCARV, haga una serie de Buscar y reemplazar con VBA
Transcripción del video
Aprenda Excel de, Podcast Episodio 2150: BUSCARV Cada Alt + Valor ingresado en cada celda.
Oye. Bienvenido de nuevo al netcast. Soy Bill Jelen. Hoy, una de las preguntas más extrañas. Alguien dijo, hey, quiero hacer una VLOOKUP para cada valor en la celda, y cuando abrí el archivo de Excel, los datos fueron ALT + Ingresados. Entonces, hay 4 elementos en este orden y todos están separados por ALT + ENTER, y luego solo 2 aquí y 6 aquí y así sucesivamente.
Volví a la persona que envió esto. Pensé, bueno, esta es una forma muy mala de almacenar estos datos. ¿Por qué estás haciendo esto? Y él estaba como, soy como si no lo estuviera haciendo. Esta es la forma en que se descargan los datos. Dije, ¿está bien si lo divido en filas separadas? No, debes mantenerlo de esta manera.
Bien. Por lo tanto, no hay una buena manera de hacer una BUSCARV para cada elemento individual, y mañana, en el episodio de mañana, 2151, les mostraré cómo podemos usar una función nueva en Power Query para hacer esto, pero tendrían tener Office 365 para tener eso.
Entonces, hoy, quiero usar un método que se remontará, y lo que hice aquí es que creé una nueva hoja de trabajo con LOOKUPTABLE, así que estos son los elementos. También noté que hay un montón de cosas, alrededor del 40% de las cosas aquí, no están en LOOKUPTABLE. Dije, ¿qué quieres hacer allí, y no hay respuesta a esa pregunta, así que los dejaré como están si no encuentro una coincidencia?
Muy bien, lo que tengo aquí es que tengo una hoja llamada LOOKUPTABLE y verá que mi archivo ahora mismo está almacenado como xlsx y voy a usar una macro VBA. Para usar una macro de VBA, no puede tenerla como xlsx. Va en contra de las reglas. Por lo tanto, debe GUARDAR COMO y guardar esto es xlsm. ARCHIVO, GUARDAR COMO y cámbielo de LIBRO DE TRABAJO a un LIBRO DE TRABAJO XLSM CON MACRO HABILITADO, o un LIBRO DE TRABAJO BINARIO - cualquiera de ellos funcionará - bien, y haga clic en GUARDAR.
Muy bien, ahora podemos ejecutar macros. ALT + F11 para acceder a la grabadora de macros. Empiezas con esta gran pantalla gris. INSERT, MODULE, y ahí está nuestro módulo, y aquí está el código. Entonces, lo llamé ReplaceInPlace y defino una hoja de trabajo. Esa es la LookupTable. Pondría el nombre de la hoja de trabajo de la tabla de búsqueda real allí, y luego mi tabla de búsqueda comienza en la columna A, que es la columna 1. Entonces, voy a la última fila de la columna 1, presiono la tecla FIN y la flecha ARRIBA , por supuesto, CONTROL + flecha ARRIBA haría lo mismo, averiguar qué fila es, y luego vamos a ir de cada fila de 2 a FinalRow. ¿Por qué 2? Bueno, porque los encabezados están en la fila 1. Entonces quiero reemplazar, comenzando en la fila 2 hasta la última fila, y así, para cada fila desde 2 hasta FinalRow, el FromValue es lo que 's en la columna A y ToValue es lo que está en la columna B.
Ahora bien, si, por alguna razón, sus datos estaban en J y K, entonces esta J sería la décima columna, por lo que pondría un 10 allí, y K sería la undécima columna, y luego, en la Selección, reemplazaremos el FromValue al ToValue. Esto es realmente importante aquí. xlPart, xlPart - y eso es una L, no un número 1 - xlPart que dice que nos permitirá reemplazar parte de la celda porque esos números de parte están todos separados por un carácter de salto de línea. Aunque no puedas verlo, está ahí. Entonces, eso debería permitirnos no actualizar accidentalmente lo incorrecto, y luego xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Bien. Entonces, esta es nuestra pequeña macro aquí. Vamos a intentarlo. Tomaremos estos datos, y no quiero destruir nada, así que solo tomaré los datos originales y los copiaré a la derecha. Bien. Entonces, tenemos nuestra selección allí. De hecho, voy a empezar desde este punto. CONTROL + RETROCESO y luego ALT + F8 para obtener una lista de todas las macros. Ahí está nuestro REPLACEINPLACE. Hago clic en EJECUTAR, y dondequiera que encuentre un elemento en BUSCAR, reemplazó ese número de elemento con el elemento, aparentemente haciendo una BUSCARV, aunque no lo estamos resolviendo con una BUSCARV en absoluto.
Bien. Entonces, oye, el nuevo libro que salió - Power Excel With, la edición 2017, 617 Excel Mysteries Solved - todo tipo de nuevos consejos geniales allí.
Resumen de hoy: el espectador descarga datos de un sistema en el que cada elemento está separado por ALT + ENTRAR, y luego necesita hacer una BUSCARV en cada elemento y, ya sabes, ¿por qué estoy haciendo esto? entonces, la persona dijo, no lo voy a hacer pero tengo que mantenerlo así; y luego el 40% de los valores no están en la tabla, bueno, no hay respuesta; así que supongo que van a agregar esos elementos a la tabla; ahora, mañana, vamos a hablar sobre cómo resolver esto con Power Query, pero, hoy, esta macro funcionará en todas las versiones de Excel para Windows, volviendo al menos a Excel 2007; entonces, en lugar de VLOOKUP, solo una serie de buscar y reemplazar con VBA.
Este hola. Quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2150.xlsm
