Evite BUSCARV utilizando el modelo de datos. Por lo tanto, tiene dos tablas que deben unirse con VLOOKUP antes de poder hacer una tabla dinámica. Si tiene Excel 2013 o una versión posterior en una PC con Windows, ahora puede hacerlo de manera simple y sencilla.
Supongamos que tiene un conjunto de datos con información sobre productos, clientes y ventas.
El departamento de TI se olvidó de poner el sector allí. Aquí hay una tabla de búsqueda que asigna un cliente a un sector. Es hora de una VLOOKUP, ¿verdad?
No es necesario realizar VLOOKUP para unir estos conjuntos de datos si tiene Excel 2013 o Excel 2016. Ambas versiones de Excel han incorporado el motor Power Pivot en el Excel principal. (También puede hacer esto usando el complemento Power Pivot para Excel 2010, pero hay algunos pasos adicionales).
Tanto en el conjunto de datos original como en la tabla de búsqueda, use Inicio, Formato como tabla. En la pestaña Herramientas de tabla, cambie el nombre de la tabla de Tabla1 a algo significativo. He usado datos y sectores.
Seleccione una celda en la tabla de datos. Elija Insertar, Pivot Table. A partir de Excel 2013, hay un cuadro adicional Agregar estos datos al modelo de datos que debe seleccionar antes de hacer clic en Aceptar.
Aparece la lista Campos de la tabla dinámica con los campos de la tabla de datos. Elija Ingresos. Dado que está utilizando el modelo de datos, aparece una nueva línea en la parte superior de la lista, que ofrece Activo o Todo. Haga clic en Todo.
Sorprendentemente, la lista Campos de tabla dinámica ofrece todas las demás tablas del libro. Esto es innovador. Aún no ha realizado una VLOOKUP. Expanda la tabla Sectores y elija Sector. Suceden dos cosas para advertirle que hay un problema.
Primero, la tabla dinámica aparece con el mismo número en todas las celdas.
Quizás la advertencia más sutil es que aparece un cuadro amarillo en la parte superior de la lista de Campos de tabla dinámica que indica que debe crear una relación. Elija Crear. (Si está en Excel 2010 o 2016, tenga suerte con la detección automática).
En el cuadro de diálogo Crear relación, tiene cuatro menús desplegables. Elija Datos en Tabla, Cliente en Columna (Extranjero) y Sectores en Tabla relacionada. Power Pivot completará automáticamente la columna correspondiente debajo de la columna relacionada (principal). Haga clic en Aceptar.
La tabla dinámica resultante es un mashup de los datos originales y la tabla de búsqueda. No se requieren VLOOKUP.
Ver video
- A partir de Excel 2013, el cuadro de diálogo Tabla dinámica ofrece el modelo de datos
- Esta es la palabra clave para Power Pivot Engine
- Para usar el modelo de datos, cree una tabla Ctrl + T de cada tabla en el libro de trabajo
- Construya una tabla dinámica a partir de la primera tabla
- En la Lista de campos de la tabla dinámica, cambie de Activo a Todo
- Elija un campo de la tabla de búsqueda
- Cree la relación o Detecte automáticamente
- La detección automática no estaba allí en 2013
- Gracias a Colin Michael y Alejandro Quiceno por sugerir Power Pivot en general.
Transcripción del video
Aprenda Excel del podcast, episodio 2014 - ¡Elimine VLOOKUP!
Podcasting este libro completo, haga clic en la "i" en la esquina superior derecha de la lista de reproducción.
Hola, bienvenido de nuevo al netcast, soy Bill Jelen, ¡esto en realidad se llama Eliminar VLOOKUP con el modelo de datos! Ahora me disculpo, esto es Excel 2013 y más reciente, si está de regreso en Excel 2010, debe descargar el complemento Power Pivot, que por supuesto es gratuito en 2010. Entonces, lo que tenemos aquí es que tenemos nuestro conjunto de datos principal, hay un campo Cliente aquí, y luego tengo una pequeña tabla que mapea el cliente al sector, necesito crear ingresos totales por sector, ¿verdad? Esta es una VLOOKUP, solo haz una VLOOKUP, pero bueno, gracias a Excel 2013, ¡no tenemos que hacer una VLOOKUP! Hice ambos en una tabla, y en Herramientas de tabla, Diseño, cambio el nombre de las tablas, llamo a ésta Sectores, y llamo a ésta Datos, para convertirla en una tabla, simplemente elija una celda, presione Ctrl + T. Entonces, si tenemos algunos títulos y números, cuando presionas Ctrl + T,preguntan "¿Dónde están los datos para su tabla?", Mi tabla tiene encabezados, y luego la llaman Tabla3, usted la llama de otra manera. Muy bien, así es como creé esas dos tablas, me voy a deshacer de esta tabla, está bien.
Entonces, para que este truco funcione, todos los datos deben vivir en tablas. Vamos a la pestaña Insertar, elegimos Tabla dinámica, y aquí abajo, en la parte inferior, agregamos estos datos al modelo de datos. Esto suena muy inocuo, ¿verdad? No hay nada como un punto intermitente que dice "¡Oye, te permitirá hacer cosas increíbles!" Y lo que están diciendo aquí, lo que están tratando de no decir es que … Ah, por cierto, cada copia de Excel 2013 tiene el motor Power Pivot detrás. Ya sabe, si está en Office 365, está pagando $ 10 al mes y quieren que pague $ 12 o $ 15 al mes para obtener Power Pivot, los dos o cinco dólares adicionales. Bueno, oye, shh, no lo digas, en realidad ya tienes la mayor parte de Power Pivot en Excel 2013. Muy bien, entonces hago clic en Aceptar, me toma un poco más de tiempo cargar el modelo de datos, está bien, pero está bien y ahora Aquí,en los campos de la tabla dinámica, tengo una lista de todos los campos. Entonces, quiero mostrar Ingresos, sin duda, pero lo que es diferente es aquí con Activo y Todo. Cuando elijo Todo, obtengo todas las tablas en el libro de trabajo. Muy bien, entonces voy a los Sectores y dije que quiero poner el sector en el área de Filas. Ahora, inicialmente, el informe va a ser incorrecto, vea los 6.7 millones hasta el final, y esta advertencia amarilla aquí dirá que tiene que crear una relación.y esta advertencia amarilla aquí dirá que tienes que crear una relación.y esta advertencia amarilla aquí dirá que tienes que crear una relación.
Muy bien ahora, en 2010 con Power Pivot, simplemente ofrecía AutoDetect, en 2013 eliminaron AutoDetect y en 2016 recuperaron AutoDetect, ¿de acuerdo? Debería mostrarte cómo se ve CREAR, pero cuando hago clic en este botón CREAR, oh sí, eso es todo, está bien, bien. Entonces, de nuestra primera tabla de Datos, tengo un campo llamado Cliente, de la tabla relacionada Sectores, tengo un campo llamado Cliente, y luego haces clic en Aceptar, está bien. Pero déjame mostrarte lo genial que es AutoDetect, si te encuentras en 2016, lo descubrieron, qué increíble es eso, ¿verdad? No necesita preocuparse por VLOOKUP, y la coma cae al final, si VLOOKUP le duele la cabeza, le encantará el modelo de datos. Tomó esas dos tablas, las unió, ya sabes, como lo haría Access, supongo, y creó una tabla dinámica, absolutamente increíble.Así que verifique el modelo de datos la próxima vez que tenga que hacer una BUSCARV entre dos tablas. Bueno, este y los otros 40 consejos están en el libro. Haga clic en esa "i" en la esquina superior derecha. Puedes comprar el libro, tener una referencia cruzada completa a toda esta serie de videos, todo agosto, todo septiembre, diablos, incluso podríamos trasladarlo a octubre para terminar todo.
Muy bien, recapitulación de hoy: a partir de Excel 2013, el cuadro de diálogo Tabla dinámica ofrece algo llamado Modelo de datos, es la palabra clave para el motor Power Pivot. Antes de crear sus tablas dinámicas, haga Ctrl + T para hacer una tabla de cada libro de trabajo, me tomé el tiempo extra para nombrar cada una. Cree una tabla dinámica a partir de la primera tabla y luego, en la lista de campos, suba a la parte superior y cambie de Activo a Todo. Elija un campo de la tabla de búsqueda, y luego le advertirá que tiene que crear una relación, o AutoDetect, en 2013, debe hacer clic en CREAR. Pero es qué, 4 clics para crearlo, 5 si cuentas el botón Aceptar, muy, muy fácil de hacer.
Muy bien, Colin, Michael y Alejandro Quiceno sugirieron Power Pivot en general para los libros, gracias a ellos, gracias por pasar, ¡nos vemos la próxima vez para otro netcast de!
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2014.xlsx
