¿Puede Excel VLOOKUP devolver todos los resultados y unirlos con una coma en el medio?
Ver video
- El objetivo es concatenar todas las respuestas de texto de una VLOOKUP
- Método de Bill: use una función de VBA llamada GetAll
- Lista única usando Eliminar duplicados
- El método de Mike:
- Lista única con filtro avanzado
- Función TEXTJOIN agregada en Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Debido a la función SI, la fórmula requiere Ctrl + Shift + Enter cada vez que edita la fórmula
- ¡Alt AQOR Enter volverá a ejecutar el filtro avanzado!
Transcripción del video
Episodio 183: Únete a todos los partidos de VLOOKUP
Bill Jelen: Oye, bienvenido. Es hora de otro Podcast de Dueling Excel. Soy Bill Jelen de, y me acompañará Mike Girvin para Excel Is Fun. Este es nuestro Episodio 183: Únete a todas las partidas BUSCARV.
(Música)
Muy bien, la pregunta de hoy de Matt. ¿VLOOKUP puede devolver todos los resultados y unirlos con un espacio de coma entre cada uno? Por ejemplo, 109876, que son estos dos aquí, ¿puede devolver el espacio de coma de aceite bajo? Comprobado el 12/12. Y claro que si hubiera más, devolvería más. Muy bien, entonces mi solución aquí será usar algo de VBA. Muy bien, asegúrese de que esté guardado como xlsm o no podrá ejecutar VBA o xlsb, pero no xlsx; xlsx es el único archivo que no puede ejecutar VBA. Presionaremos Alt + F11, nos aseguraremos de estar en Dual183 o cualquiera que sea el nombre de su libro de trabajo. Inserte el módulo en el módulo en blanco y vamos a pegar este código, de acuerdo.
Echemos un vistazo a esta función GetAll, y aquí está el número de identificación que estamos buscando y luego el rango que queremos buscar. Y comenzamos, vamos a devolver una variable llamada GetAll, por lo que comenzamos con un espacio en blanco. Para cada celda de mi rango, si el valor de la celda es lo que estamos buscando, entonces vamos a tomar GetAll = GetAll & "" y luego Cell.Offset (0 filas, 1 columna), en otras palabras, el valor eso es justo al lado de ese número de identificación porque en VBA, aquí está el número de identificación. Si encontramos el número de identificación coincidente, queremos pasar 1 columna. Ahora, ¿qué pasaría si quisieras pasar 2 columnas o 3 columnas? Bueno, entonces cambia estas 0 filas y 1 columna para que sean un 2. De acuerdo, también verifica si, no ponemos un espacio de coma si es el primero.Entonces, si la variable GetAll es actualmente "", entonces no pondremos el espacio de coma, ¿de acuerdo?
Entonces, ahora que tenemos esta función aquí, observe lo fácil que es resolver el problema de Matt. Vamos a venir aquí y tomar sus ID, Ctrl + C y pegar Ctrl + V así. Datos, Eliminar duplicados, haga clic en Aceptar. Entonces, hay una lista única de ID y luego queremos decir = getall y estamos buscando ese valor en la coma E2. Mirando a través de este rango aquí, presionaré F4. F4 funciona como una función normal. Y de nuevo apartando la pregunta de Matt, haga doble clic para eliminarla. Funcionará.
Y solo intentemos, intentemos algo loco aquí. Hagamos una Frase 1 y coloquemos un montón de ellas como la Frase 1 a la 10. Firmaremos todas estas en 109999. Pegue y luego pegue aquí. Copie esa fórmula, edite la fórmula para que llegue hasta el final, por supuesto. Sip. Y devolverá todas esas frases. Muy bien, esa es mi solución, VBA, una pequeña función allí. Mike, veamos qué tienes.
Mike Girvin: Gracias. GetAll, esa es una función de VBA increíble. Muy bien, voy a pasar a la hoja de aquí. Ya lo he convertido a una tabla de Excel para que cuando agreguemos los registros a continuación, con suerte, las cosas se actualizarán.
Ahora lo primero que voy a hacer es esto en dos partes. Podría hacer una fórmula aquí para extraer una lista única, pero quiero ver otra opción: el filtro avanzado tiene una opción de extracción de lista única y se puede actualizar. Voy a resaltar solo los datos de la columna de ID, a Filtro avanzado o usaré el teclado Alt, A, Q. Ahora, lista de filtros en el lugar, de ninguna manera. Quiero Copiarlo en otra ubicación. Tiene solo la columna A y porque es una tabla de Excel que se expandirá más adelante. No tengo ningún criterio, quiero copiarlo en D1 y verificar solo los registros únicos. Haga clic en Aceptar.
Ahora, voy a venir aquí, todos los comentarios ingresan y voy a usar una función que solo funciona en Excel 2016 Office 365: = función TEXTJOIN. Esta función por sí sola vale la pena obtener la última versión de Excel. Esta es una tarea tan común que la gente quiere hacer, unir muchas cosas. Ahora nuestro delimitador en ",", y lo mejor de esta función es que podemos decirle que ignore las celdas vacías. Ahora, puedo poner VERDADERO, 1 o Dejarlo, Omitirlo. Entonces, voy a dejarlo, omitirlo. Y aquí es donde necesitamos nuestro texto. Usaremos la función SI para filtrar y obtener solo los elementos que queremos. Voy a decir que mire toda esta columna aquí: Nombre de la tabla y luego en () el nombre del campo, ¿alguno de ustedes = a esta referencia de celda relativa, esa es la prueba lógica. Si tuviera que hacer clic en esto y presionar la tecla F9 para evaluar,Podrías ver que ahora solo tenemos 2 VERDADEROS, Ctrl + Z ahora escribo una coma y con la matriz de Verdades y Fallas, ahora puedo darle los elementos para elegir. Así que ahora, seleccionaremos solo los elementos que tienen un VERDADERO aquí de este rango. Comma y quiero asegurarnos de poner “” - que aparecerá como una celda vacía con respecto al segundo argumento en TEXTJOIN.
Ahora, voy a cerrar paréntesis y ahora la función SI creará esa cadena de Verdades y Falso, los elementos reales de este rango se recogerán si lo ve Verdadero y todos los demás elementos tendrán esa celda vacía. ¿Y adivina qué? TEXTJOIN ignorará por completo todas esas celdas vacías y devolverá solo los elementos que coincidan con este ID, y luego lo unirá con ese delimitador. Ahora bien, esta es definitivamente una fórmula de matriz que requiere la pulsación de tecla especial Ctrol + Shift + Enter. El argumento de prueba lógica contiene nuestra operación de matriz y ese argumento no puede calcular esta operación de matriz correctamente a menos que usemos el teclado Ctrl + Shift + Enter. Ahora voy a cerrar paréntesis. En realidad, podríamos probar 1 aquí mismo en el Texto 1 si F9 todo esto, podríamos ver que obtenemos los 2 elementos, el resto de esas celdas vacías serán ignoradas. Ctrl + Z. Ahora deja's ingrese esto en la celda con Ctrl + Shift + Enter. Inmediatamente busque la barra de fórmulas. Esas llaves son Excel que le dice que entendió y calculó esto como una fórmula de matriz. Ahora puedo hacer doble clic y enviarlo. Eso se ve bien.
Voy a ir a la última celda y presionar F2 para verificar que todos los rangos se vean correctamente. Ahora lo que no quiero hacer es que no quiero presionar Enter porque esa fórmula después de ponerla en modo de edición solo se calculará correctamente si usamos Ctrl + Shift + Enter; o, debido a que ya ingresamos la fórmula, podemos usar la tecla Esc para volver a lo que esté en la celda antes de ponerla en modo de edición.
Ahora, probemos esto. Voy a hacer clic en la última celda aquí abajo y presionar Tab y luego escribir una nueva ID, Tab, Tab. Otro registro nuevo, Tab, y ya puedo ver que no tuve suficiente trabajo aquí. Yo soy, vamos a poner - Perfecto y luego Enter. Ahora, esto no se actualizará automáticamente como si tuviéramos un montón de fórmulas en las que estamos contando elementos únicos y luego extrayendo elementos únicos, pero no hay problema. Ver este. Podemos actualizar esta lista de registros únicos porque usamos Filtro avanzado y no importa desde qué celda comience, ya que cuando se invoca el Filtro avanzado, memoriza el rango de extracción y los rangos que estaba mirando originalmente. Puede hacer clic en Filtro avanzado o usar el teclado Alt + A + Q. Tenemos que seleccionar Copiar a otra ubicación, pero mire eso.Se recordó por completo y se expandió a A13 debido a la función de tabla de Excel. Recordó la gama de extractos. Tengo que verificar solo los registros únicos, pero haga clic en Aceptar.
Ahora, tengo que venir y copiar esta fórmula. Y ahí lo tiene, utilizando Filtro avanzado y la increíble función TEXTJOIN con, en la operación Array para obtener solo los elementos que coinciden. Muy bien, regrese a.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Oh, oye, quiero agradecer a todos por pasar. Nos vemos la próxima vez para otro podcast de Dueling Excel de y Excel Is Fun.
Descargar archivo
Descarga el archivo de muestra aquí: Duel183.xlsm
