La pregunta de hoy de Quentin, quien estuvo en mi seminario de Atlanta Power Excel. Quentin tiene que generar las mismas 7 preguntas de encuesta para cada uno de los más de 1000 clientes en Excel.
Como puede ver en esta figura, los clientes están en A. Las preguntas a repetir están en la columna D.
Podría resolver esto con VBA o fórmulas, pero esta es la semana de Power Query a las, así que voy a usar un truco genial en Power Query.
Si desea una fila en blanco entre cada encuesta, agregue un número de secuencia y agregue el número 7 después de la última pregunta.
Presione Ctrl + T en ambos conjuntos de datos. Nombra el segundo conjunto de datos con un nombre que puedas recordar, como Preguntas o Encuesta.
Del segundo conjunto de datos, use Datos, De la tabla.
Se abre el editor de Power Query. En la pestaña Inicio, elija el menú desplegable Cerrar y cargar y elija Cerrar y cargar en…. En el siguiente cuadro de diálogo, elija Solo crear una conexión.
Ahora está de vuelta en Excel. Elija cualquier celda de la tabla de clientes en la columna A. Datos, de la tabla. Una vez que se abre el Editor de consultas, haga clic en la pestaña Agregar columna en la cinta y luego elija Columna personalizada. La fórmula es =#"Questions"(incluido el # y las comillas).
Aparece una nueva columna en el editor con el valor Table repetido en cada fila. Haga clic en el icono Expandir en el encabezado de la columna.
Elija ambos campos en la tabla. En la pestaña Inicio, elija Cerrar y cargar.
Aparecerá una nueva hoja de trabajo con las 7 preguntas repetidas para cada uno de los más de 1000 clientes.
Ver video
Transcripción del video
Aprenda Excel del episodio 2205 del podcast: explosión de la encuesta de préstamos.
Oye, bienvenido de nuevo a Netcast, soy Bill Jelen. Ahora, ayer en el episodio 2204, fue Kaylee de Nashville quien tuvo que hacer una explosión VLOOKUP: para cada elemento aquí en la columna D, teníamos un montón de elementos coincidentes en la columna G y necesitábamos explotarlos. Entonces, si el Palacio C tuviera 8 elementos, obtendríamos 8 filas.
Ahora, hoy, tenemos a Quentin. Ahora, Quentin estuvo en mi seminario en Atlanta, pero en realidad es de Florida, y Quentin tiene casi 1000 clientes aquí, bueno, más de 1000 clientes, en la columna A, y para cada cliente, necesita crear esta encuesta. - esta encuesta de 1, 2, 3, 4, 5, 6 preguntas. Y lo que voy a hacer aquí es agregar un número de secuencia solo con los números del 1 al 7, de esa manera, puedo crear una bonita fila en blanco en el medio. Voy a convertir estos dos conjuntos de datos en una tabla; por lo tanto, estamos tratando de expandir estas 7 filas para cada uno de estos 1000 clientes. Ese es el objetivo.
Ahora, puedo hacer esto con VPA; Puedo hacer esto con fórmulas; pero es una especie de "Semana de Power Query" aquí, estamos en una carrera de este es nuestro tercer ejemplo de Power Query seguido, así que voy a usar Power Query. Voy a convertir este de la izquierda en una mesa. Voy a tener mucho cuidado de nombrar esto no Tabla 1. Le voy a dar un nombre. Tendremos que reutilizar ese nombre más tarde, así que lo llamaré Preguntas, así. Y luego esta será la Tabla 2, pero voy a cambiarle el nombre a Clientes, no es tan importante que cambie el nombre de este porque es el segundo que tiene que tener el nombre. Entonces, vamos a elegir esto; Datos; y vamos a decir From Table / Range. Obtener y transformar datos: esto se conoce como Power Query. Está integrado en Excel 2016. Si tiene 2010 o 2013, en Windows,ni una Mac, ni iOS, ni Android, puede descargar Power Query gratis de Microsoft.
Entonces, vamos a obtener datos de Tabla / Rango; aquí está nuestra mesa: no vamos a hacer nada con ella, solo Cerrar y Cargar; Cerrar y cargar en; solo crear una conexión; bien, y mira, el nombre de esa consulta es Preguntas. Utiliza el mismo nombre que aquí. Y luego volvemos a este, y, Data; De tabla / rango; entonces, hay una lista de nuestros 1000 o más clientes.
Hola, aquí está un saludo para Miguel Escobar, mi amigo, quien es el coautor de M Is For (DATA) MONKEY). Pondré un enlace a eso en el video, un gran libro sobre Power Query, que me ayudó con esto. Vamos a poner una nueva columna personalizada, y la fórmula de la columna personalizada es la siguiente: = # "el nombre de la consulta". Nunca me hubiera dado cuenta de eso sin Miguel, así que gracias a Miguel por eso.
Y cuando hago clic en Aceptar, sí, no parece que haya funcionado, solo tenemos mesa, mesa, mesa, pero eso es exactamente lo que tuvimos ayer con Kaylee y la venta de entradas. Y todo lo que tengo que hacer es expandir esto, y de hecho voy a decir que probablemente no necesito la Secuencia… bueno, pongámosla por si acaso. Podemos sacarlo después de verlo. En este momento, tenemos 1000 filas y ahora tenemos 7000 filas, hermoso. Ahora puedo ver que está apareciendo en Sequence, así que no lo necesito. Haré clic derecho y eliminaré solo esa columna. Y luego puedo volver a casa; Cerrar y cargar; y ¡BAM !: ahora deberíamos tener más de 7000 filas con 6 preguntas y un espacio en blanco para cada cliente. Quentin estaba encantado con eso en el seminario. Un truco genial: evita VBA, evita un montón de fórmulas usando Index,y cosas así, una gran manera de hacerlo.
Pero, oye, hoy, déjame enviarte con M Is For (DATA) MONKEY. Ken Puls y Miguel Escobar escribieron el mejor libro sobre Power Query. Amo ese libro; en 2 horas te convertirás en un profesional con ese libro.
Muy bien, termine hoy: Quentin necesita generar una encuesta idéntica para 1000 clientes diferentes. Hay 6, 7 u 8 preguntas para cada cliente. Ahora, podríamos hacer esto con VBA o macro, pero, como estamos en una ejecución de Power Query aquí, hagamos una Power Query. Agregué una pregunta en blanco adicional a las Preguntas; Agregué un Número de secuencia para asegurarme de que el espacio en blanco permanezca allí; convertir a los clientes en una mesa; convertir las preguntas en una tabla; Es muy importante que nombre Preguntas algo que pueda recordar. Yo llamé a la mía "Preguntas". Agregue las preguntas a Power Query, solo como conexión; y luego, cuando agregue los clientes a Power Query, cree una nueva columna personalizada donde la fórmula sea: # "el nombre de la primera consulta" y luego expanda esa columna en el editor de Power Query; Cerrar &Vuelva a cargar en la hoja de cálculo y listo. Un truco asombroso, me encanta Power Query, lo mejor que le ha pasado a Excel en 20 años.
Quiero agradecer a Quentin por participar en mi seminario. Ha estado en mi seminario un par de veces antes, un gran tipo. Quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo de Excel
Para descargar el archivo de Excel: préstamo-encuesta-explosión.xlsx
Power Query sigue sorprendiéndome. Consulte el libro M es para Data Monkey para obtener más información sobre 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:
"Puedes hacer cualquier cosa con AGGREGATE excepto entenderlo".
Liam Bastick
