Kaley de Nashville está trabajando en una hoja de cálculo de venta de boletos. Para cada evento, elige un plan de venta de entradas. Ese plan de venta de entradas podría indicar entre 4 y 16 tipos de entradas para el evento. Kaley quiere una fórmula que vaya a la tabla de búsqueda y devuelva * todas * las coincidencias, insertando nuevas filas según corresponda.
Si bien no tengo una VLOOKUP que pueda resolver esto, las nuevas herramientas de Power Query integradas en Excel 2016 pueden resolverlo.
Nota
Si tiene la versión de Windows de Excel 2010 o Excel 2013, puede descargar Power Query gratis de Microsoft. Desafortunadamente, Power Query aún no está disponible para Excel para Android, Excel para ios o Excel para Mac.
Para ilustrar el objetivo: Mike McCann and the Mechanics aparece en el Allen Theatre con el plan de entradas C. Dado que hay cuatro filas coincidentes en la tabla de búsqueda, Kaley quiere cuatro filas que digan Mike McCann and the Mechanics, cada una con una coincidencia diferente de la tabla de búsqueda.
Seleccione una celda de la tabla original. Presione Ctrl + T para marcar esos datos como una tabla. En la pestaña Herramientas de tabla, cambie el nombre de la tabla de Table1 a Shows. Repita para la tabla de búsqueda, llamándola Tickets.
Seleccione una celda en la tabla Muestra. En la pestaña Datos, elija De tabla / rango.
Una vez que se abra el editor de Power Query, abra el menú desplegable Cerrar y cargar y elija Cerrar y cargar en….
En el cuadro de diálogo Importar datos, elija Crear solo una conexión.
Vaya a la mesa Tickets. Repita los pasos para crear solo una conexión a tickets. Debería ver ambas conexiones en el panel Consultas:
Seleccione cualquier celda en blanco. Elija Datos, Obtener datos, Combinar consultas, Combinar.
Hay seis pasos en el cuadro de diálogo Fusionar. El 3 y el 4 no me parecen intuitivos.
- Elija Programas en el menú desplegable superior
- Elija Entradas en el segundo menú desplegable.
- Haga clic en el encabezado de Ticket Plan en la parte superior para seleccionar esa columna como clave externa en la tabla Shows.
- Haga clic en el encabezado de Plan de boletos en la parte inferior para seleccionar esa columna como el campo clave en la tabla de búsqueda.
- Abra el tipo Unión y elija Interior (solo filas coincidentes).
- Haga clic en Aceptar
Los resultados son inicialmente decepcionantes. Verá todos los campos de la tabla 1 y una columna que dice Tabla, Tabla, Tabla.
Haga clic en el icono Expandir en la parte superior de la columna Entradas.
Anule la selección de Ticket Plan ya que ya tiene ese campo. El campo restante se llamará Tickets. Tipo de boleto a menos que desmarque Usar nombre original como prefijo.
¡Éxito! Cada fila de cada programa se divide en varias filas.
No estoy particularmente satisfecho con la clasificación de los datos. La clasificación por fecha hace que los tipos de entradas se ordenen de forma extraña.
Ver video
En el caso de hoy, el video se filmó después de que se escribió el artículo. Sugiero agregar una columna de secuencia a los Tipos de entradas para controlar el orden de clasificación.
Transcripción del video
Aprenda Excel del Podcast, Episodio 2204: Devolver todas las BUSCARV.
Oye, bienvenido de nuevo al netcast, soy Bill Jelen. Pregunta de hoy de Nashville Music City. Estuve allí en Nashville, alguien es responsable de programar la carga de boletos en un sistema de venta de boletos y esto es lo que tenemos: Tenemos una lista de eventos, próximos eventos, tenemos la fecha, el lugar y un plan de boletos. Entonces, aunque algo se lleva a cabo en el Palace, podría haber diferentes planes de boletos, como, tal vez el piso esté configurado, ya sabes, con asientos o tal vez solo sea una sala de pie, ¿verdad?
Entonces, dependiendo del tipo de plan de boletos, tienes que venir aquí a la tabla de búsqueda y encontrar todos los eventos coincidentes, y esencialmente vamos a hacer lo que yo llamo una explosión de BUSCARV. Entonces, si hay algo en Hannah C, irán a Hannah C y si hay 1, 2, 3, 4, 5, 6, 7 elementos en Hannah C, tendremos para devolver siete filas, lo que significa que tendrá que insertar seis filas más y copiar esos datos. Bien.
Ahora, no vamos a hacer esto con una VLOOKUP en absoluto, pero entiendes el concepto: estamos haciendo una VLOOKUP y estamos devolviendo todas las respuestas como nuevas filas. Muy bien, voy a tomar estas dos tablas y convertirlas en una tabla real con Ctrl + T. Los primeros se llamaron Tabla 1 - nombre horrible, llamémoslo Eventos o Shows, llamémoslo Shows, así - y el segundo, ahora, oye, esto es lo que aprendí porque practiqué esto - tenemos que tener un campo de secuencia aquí. Entonces = FILA (A1), haga doble clic, cópielo y luego copie y pegue valores especiales. Bien. Ahora hacemos que eso lo convertirá en una tabla: Ctrl + T, y lo llamaremos Tickets.
Bien. Entonces tenemos espectáculos, tenemos entradas. Voy a ir a la pestaña Datos, y estoy aquí en la cosa de espectáculos, quiero decir que quiero obtener mis datos de una tabla o rango, esto es Power Query, por cierto. Si está de regreso en Excel 2010 o 2013, puede descargarlo gratis de Microsoft, descargue la herramienta Power Query. Si está en una Mac, iOS o Android, lo siento, no hay Power Query para usted. Muy bien, desde una Mesa o Gama … encuentra a alguien que tenga una-- encuentra un amigo que tenga una-- PC con Windows y haz que lo configure. Bien. Aquí hay una tabla, no vamos a hacer nada con esto, simplemente Cerrar y Cargar, Cerrar y Cargar en, y luego decir "Solo Crear Conexión", perfecto. Vamos a pasar aquí a nuestra segunda tabla: obtener datos, de una tabla o rango, no vamos a hacer nada con esta, cerrar y cargar,Cerrar y cargar en, "Solo crear conexión", Aceptar. Entonces, lo que tenemos ahora es que tenemos una conexión con la primera tabla y una conexión con la segunda tabla. No vamos a fusionar estos dos, lo que esencialmente es como hacer BUSCARV, o una base de datos conjunta, supongo, es realmente lo que es. Combine Queries, vamos a Fusionar. Bien.
Ahora, siete cosas que tienes que hacer en este cuadro de diálogo, y es un poco confuso, elegiremos Shows como la primera tabla; elija Tickets como la segunda mesa; elija qué campo tienen en común, y esto puede ser varios campos (puede controlar y hacer clic), pero en este caso solo hay un plan de boletos; y luego Ticket Plan; y luego cambiaremos el tipo de combinación a una combinación interna con "solo las filas coincidentes". Bien. Ahora, hace clic en Aceptar y cree que todo su problema se resolverá, pero está aplastado porque aquí están todos los datos de A, no han insertado ninguna fila nueva en absoluto, y aquí, solo un campo estúpido y aburrido llamado Tickets que solo tiene Mesa, Mesa, Mesa, ja.
Pero, afortunadamente, en la parte superior hay un ícono Expandir, y vamos a expandir eso. No necesito tomar un plan, ya lo tengo. Tipo de ticket y secuencia. No quiero que se llame Tickets.TicketType, que es lo que Power Query quiere hacer, así que desmarco esta casilla. Bien. Ahora mismo tenemos 17 filas de datos; cuando hago clic en Aceptar, ¡BAM! Ahí está la explosión. Entonces, Michael Seeley y Starlighter's aparecen con todos los diferentes tipos de boletos, así. Muy bien, y ver que estos tipos de boletos aparecen en secuencia, eso es genial. Pero Michael Seeley no es el próximo espectáculo, el próximo espectáculo es el 5 de junio. Entonces, cuando trato de ordenar esto por Fecha, esto me vuelve loco, no puedo explicarlo. Ordenar por fecha, y Mike Man and the Mechanics llega a 65, pero luego las entradas están arruinadas. Ellos'Estás en la secuencia incorrecta, y por eso tuve que hacer esta secuencia, se siente así. Puedo ordenar por secuencia. Así que ahora, 6, 5, hermoso, y luego, dentro de eso, los boletos son correctos. Y de hecho, en este punto, ya no necesitamos esta columna. Entonces puedo hacer clic derecho y eliminar, y luego Cerrar y Cargar (esta vez voy a Cerrar y Cargar, no Cerrar y Cargar a) y tenemos nuestro resultado. Bien.
Así que pasamos de una lista de eventos a toda esta gran lista, pero aquí está la parte increíble: arruiné esto, Mike Man and Mechanics no es Palace B, es Palace C. Así que vuelvo al original en la parte superior derecha -esquina de mano para más información sobre el libro.
Bien. Temas de este episodio: Kaley en Nashville necesita hacer una BUSCARV para devolver todas las coincidencias, generalmente insertando nuevas filas. Y es una base de datos de tickets, ¿de acuerdo? Así que voy a llamar a esto una explosión VLOOKUP porque cada programa explotará en hasta 16 filas. Vamos a usar Power Query para resolver esto, y he aprendido que la fecha aparecerá en la secuencia incorrecta a menos que agreguemos un campo de secuencia al tipo de ticket. Convierta ambos conjuntos en una tabla con Ctrl + T; nómbrelos como Espectáculos y Entradas; y luego de cada tabla, Obtener datos, De la tabla, Cerrar y cargar, para Solo crear una conexión; repita para la otra mesa; luego Datos, Obtener datos, Combinar consultas, Combinar; y luego ese cuadro de diálogo, es bastante confuso para mí: elija Eventos, elija Entradas, haga clic en Tipo de entrada en ambos, cambie la unión a una unión interna,haga clic en Aceptar, y luego obtendrá ese resultado horriblemente decepcionante en el que solo es una columna que dice Tabla, Tabla, Tabla, Tabla; haga clic en el icono Expandir en la parte superior de eso; elija el campo Ticket Sequence; no prefijo con el nombre de la tabla; y puede ordenar por fecha, ordenar por secuencia; Cerrar y cargar en la hoja de cálculo. Lo bueno es que si cambian los datos subyacentes, simplemente actualice y obtendrá los resultados.
Ahora, oye, para descargar el libro de trabajo utilizado en el video de hoy, visita la URL en la descripción de YouTube. También hay una lista de los próximos seminarios. Me encantaría verte en uno de mis seminarios de Power Excel en vivo.
Quiero agradecer a Kaley por presentarse en Nashville y darme esa gran pregunta. Te quiero por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo de Excel
Para descargar el archivo de Excel: return-all-vlookups.xlsx
Power Query sigue sorprendiéndome. Esta es la segunda de una serie de tres días donde la respuesta es Power Query:
- Martes: convierte una columna de fecha / hora en solo fecha
- Hoy: Devolver todas las VLOOKUP
- Jueves: cree una encuesta para cada uno de los 1100 elementos
Tengo una lista de reproducción de YouTube completa de cosas que terminé resolviendo con Power Query.
Pensamiento del día de Excel
Les he pedido a mis amigos de Excel Master sus consejos sobre Excel. Pensamiento de hoy para reflexionar:
"¡En caso de duda, utilice la función REDONDEAR!"
Mike Girvin
