¿Alguna vez ha usado BUSCARV para mover una columna de una tabla a otra tabla? Ahora que Excel tiene un modelo de datos integrado, BUSCARV ha quedado obsoleta. Se puede crear una relación entre dos tablas de datos basada en los datos que coincidan entre ellas. Después, se pueden crear hojas de Power View y generar tablas dinámicas y otros informes con campos de cada tabla, incluso si las tablas son de orígenes diferentes. Por ejemplo, si tiene datos de ventas de clientes, podría interesarle importar y relacionar datos de inteligencia de tiempo para analizar patrones de venta por año y mes.
Todas las tablas de un libro se muestran en la tabla dinámica y las listas de campos de Power View.
Al importar tablas relacionadas desde una base de datos relacional, Excel casi siempre puede crear esas relaciones en el modelo de datos que genera en segundo plano. En todos los demás casos, se deberán crear las relaciones manualmente.
-
Asegúrese de que el libro contiene al menos dos tablas y que cada una tiene una columna que se pueda asignar a una columna de otra tabla.
-
Siga uno de estos procedimientos: Dar formato de tabla a los datos o Importar datos externos como una tabla en una nueva hoja de cálculo.
-
Asigne un nombre significativo a cada tabla: en Herramientas de tabla, haga clic en Diseño > Nombre de tabla y escriba un nombre.
-
Compruebe que la columna de una de las tablas tenga valores de datos únicos sin duplicados. Excel solo puede crear la relación si una columna contiene valores únicos.
Por ejemplo, para relacionar las ventas de clientes con la inteligencia de tiempo, ambas tablas deben incluir fechas en el mismo formato (por ejemplo, 01/01/2012) y al menos una tabla (inteligencia de tiempo) debe enumerar cada fecha una sola vez en la columna.
-
Haga clic en Datos> Relaciones.
Si Relaciones está atenuado, significa que la hoja de cálculo contiene una sola tabla.
-
En el cuadro Administrar relaciones, haga clic en Nueva.
-
En el cuadro Crear relación, haga clic en la flecha abajo de Tabla y seleccione una tabla en la lista. En una relación uno a varios, esta tabla debe estar en el lado de varios. En nuestro ejemplo de cliente e inteligencia de tiempo, elegirá la tabla de ventas de clientes primero, puesto que es probable que se produzcan varias ventas en cualquier día dado.
-
En Columna (externa), seleccione la columna que contiene los datos que se relacionan con Columna relacionada (principal). Por ejemplo, si tuviera una columna de fecha en ambas tablas, ahora elegiría esa columna.
-
En Tabla relacionada, seleccione una tabla que tenga al menos una columna de datos relacionada con la tabla recién seleccionada en Tabla.
-
En Columna relacionada (principal), seleccione una columna que tenga valores únicos que coincidan con los valores seleccionados para Columna.
-
Haga clic en Aceptar.
Más información acerca de las relaciones entre tablas en Excel
Notas acerca de las relaciones
-
Sabrá si existe una relación cuando arrastre campos de diferentes tablas a la lista de campos de la tabla dinámica. Si no se le solicita que cree una relación, Excel ya tiene información de la relación que necesita para relacionar los datos.
-
Crear relaciones es similar a utilizar VLOOKUP: necesita columnas que contienen datos coincidentes para que Excel pueda hacer referencias cruzadas de las filas en una tabla con las de otra tabla. En el ejemplo de inteligencia horaria, la tabla customer necesitaría tener valores de datos que también existieran en la tabla de inteligencia horaria.
-
En un modelo de datos, las relaciones de tabla pueden ser una a una (cada pasajero tiene una tarjeta de embarque) o una a varias (cada vuelo tiene varios pasajeros), pero no varias a varias. Las relaciones varias a varias provocan errores de dependencia circular, tal como "Se detectó una dependencia circular". Este error se producirá si realiza una conexión directa entre dos tablas de tipo varias a varias o conexiones indirectas (una cadena de relaciones de tabla que sean una a varias dentro de cada relación, pero varias a varias cuando se visualizan de una punta a otra). Para obtener más información acerca de las relaciones, consulte Relaciones entre tablas en un modelo de datos.
-
Los tipos de datos de las dos columnas deben ser compatibles. Consulte Tipos de datos en modelos de datos de Excel para obtener más información.
-
Hay otras maneras de crear relaciones que podrían ser más intuitivas, especialmente si duda de qué columnas desea usar. Consulte Crear una relación en la vista Diagrama de Power Pivot.
Ejemplo: relación de datos de inteligencia horaria a datos de vuelo aéreo
Puede obtener información sobre las relaciones de tabla y la inteligencia horaria mediante datos gratuitos en Microsoft Azure Marketplace. Algunos de estos conjuntos de datos son de gran tamaño y necesitan una conexión a Internet rápida para completar la descarga de datos dentro de un periodo de tiempo razonable.
-
Inicie el complemento Power Pivot in Microsoft Excel y abra la ventana de Power Pivot.
-
Haga clic en Obtener datos externos > Desde servicio de datos > Desde Microsoft Azure Marketplace. Se abre la página principal de Microsoft Azure Marketplace en el asistente para importar tablas.
-
En Precio, haga clic en Gratis.
-
En Categoría, haga clic en Ciencia y estadística.
-
Busque DateStream y haga clic en Registrarse.
-
Especifique su cuenta Microsoft y haga clic en Iniciar sesión. Una vista previa de los datos debería aparecer en la ventana.
-
Desplácese hasta el final de la página y haga clic en Seleccionar consulta.
-
Haga clic en Siguiente.
-
Elija BasicCalendarUS y haga clic en Finalizar para importar los datos. Con una conexión a Internet de alta velocidad, la importación debería tardar aproximadamente un minuto. Cuando haya terminado, debería aparecer un informe de estado de 73.414 filas transferidas. Haga clic en Cerrar.
-
Haga clic en Obtener datos externos > Desde servicio de tabla > Desde Microsoft Azure Marketplace para importar un segundo conjunto de datos.
-
En Tipo, haga clic en Datos.
-
En Precio, haga clic en Gratis.
-
Busque US Air Carrier Flight Delays y haga clic en Seleccionar.
-
Desplácese hasta el final de la página y haga clic en Seleccionar consulta.
-
Haga clic en Siguiente.
-
Haga clic en Finalizar para importar los datos. Con una conexión a Internet de alta velocidad, la importación debería demorar unos 15 minutos. Cuando finalice, debería aparecer un informe de estado de 2.427.284 filas transferidas. Haga clic en Cerrar. Ahora debería tener dos tablas en el modelo de datos. Para relacionarlas, se necesitarán columnas compatibles en cada tabla.
-
Observe que el objeto DateKey en BasicCalendarUS tiene el formato 1/1/2012 12:00:00 AM. La tabla On_Time_Performance también contiene una columna datetime, FlightDate, cuyos valores se especifican en el mismo formato: 1/1/2012 12:00:00 AM. Las dos columnas contienen datos coincidentes, del mismo tipo, y al menos una de las columnas (DateKey) contiene solo valores únicos. En los pasos subsiguientes, usará estas columnas para relacionar las tablas.
-
En la ventana de Power Pivot, haga clic en Tabla dinámica para crear una tabla dinámica en una hoja de cálculo nueva o existente.
-
En Lista de campos, expanda On_Time_Performance y haga clic en ArrDelayMinutes para agregarlo a las áreas Valores. En la tabla dinámica, debería ver la cantidad total de tiempo que los vuelos se han demorado, medida en minutos.
-
Expanda BasicCalendarUS y haga clic en MonthInCalendar para agregarlo al área Filas.
-
Observe que la tabla dinámica ahora enumera meses, pero la suma total de minutos sigue siguen la misma para cada mes. Para repetir, los valores idénticos indican que se necesita una relación.
-
En Lista de campos, en “Puede que se necesite una relación entre tablas”, haga clic en Crear.
-
En Tablas relacionadas, seleccione On_Time_Performance y en Columna relacionada (principal) seleccione FlightDate.
-
En Tabla, seleccione BasicCalendarUS y en Columna (externa) elija DateKey. Haga clic en Aceptar para crear la relación.
-
Observe que la suma de minutos demorados ahora varía para cada mes.
-
En BasicCalendarUS, arrastre YearKey al área Filas, encima de MonthInCalendar.
Ahora puede fragmentar las demoras de llegadas por año y mes u otros valores del calendario.
: De manera predeterminada, los meses se enumeran en orden alfabético. Mediante el complemento Power Pivot, se puede cambiar el ordenamiento de modo que los meses aparezcan en orden cronológico.
-
Asegúrese de que la tabla BasicCalendarUS esté abierta en la ventana de Power Pivot.
-
En la tabla Inicio, haga clic en Ordenar por columna.
-
En Ordenar, seleccione MonthInCalendar
-
En Por, seleccione MonthOfYear.
La tabla dinámica ahora ordena cada combinación de mes y año (October 2011, November 2011) según el número de mes dentro de un año (10, 11). Cambiar el criterio de ordenación es fácil porque la fuente DateStream proporciona todas las columnas necesarias para que funcione este escenario. Si usa otra tabla de inteligencia horaria, el paso será diferente.
"Puede que se necesiten relaciones entre tablas"
A medida que agregue campos a una tabla dinámica, se le irá notificando si se requiere una relación de tablas para poder interpretar los campos seleccionados en la tabla dinámica.
Aunque Excel puede indicarle cuándo se necesita una relación, no puede indicar qué tablas y columnas usar, ni si una relación de tabla es incluso posible. Siga estos pasos para obtener las respuestas que necesita.
Paso 1: Determinar las tablas que se deben especificar en la relación
Si el modelo contiene tan solo unas pocas tablas, puede que sea evidente al instante cuáles se deben usar. Pero, para modelos más grandes, es probable que necesite ayuda. Un método es usar la vista Diagrama en el Power Pivotcomplemento. La vista Diagrama proporciona una representación visual de todas las tablas del modelo de datos. Con ella, podrá determinar rápidamente qué tablas van aparte del resto del modelo.
: Es posible crear relaciones ambiguas que no son válidas cuando se usan en una tabla dinámica o un informe Power View. Supongamos que todas las tablas están relacionadas de alguna forma con otras tablas del modelo, pero cuando intenta combinar campos de distintas tablas, recibe el mensaje “Puede que se necesite una relación entre tablas”. La causa más probable es que se ha topado con una relación de varias a varias. Si sigue la cadena de relaciones de tablas que se conectan a las tablas que desee usar, es probable que descubra que hay dos o más relaciones de tablas de una a varias. No hay solución fácil para cada situación, pero puede intentar crear columnas calculadas para consolidar las columnas que desee usar en una tabla.
Paso 2: Buscar columnas que se pueden usar para crear una ruta de acceso de una tabla a otra
Cuando haya identificado qué tabla está desconectada del resto del modelo, revise sus columnas para determinar si otra columna, en otro lugar del modelo, contiene valores coincidentes.
Por ejemplo, supongamos que tiene un modelo con ventas de producto por territorio y que posteriormente importa datos demográficos para ver si hay una correlación entre las ventas y las tendencias demográficas de cada territorio. Dado que los datos demográficos provienien de otro origen de datos, inicialmente sus tablas estarán aisladas del resto del modelo. Para integrar los datos demográficos con el resto del modelo, deberá buscar una columna en una de las tablas demográficas que corresponda a una que ya esté usando. Por ejemplo, si los datos demográficos se organizan por región y los datos de ventas especifican en qué región se ha producido la venta, podría relacionar los dos conjuntos de datos si busca una columna común, tal como Provincia, Código de área, o Región, para proporcionar la búsqueda.
Además de valores coincidentes, hay requisitos adicionales en la creación de una relación:
-
Los valores de datos en la columna de búsqueda deben ser únicos. Es decir, la columna no puede contener duplicados. En un modelo de datos, las cadenas nulas y vacías son equivalentes a un objeto en blanco, que es un valor de datos distinto. Esto significa que no puede tener varios objetos nulos en la columna de búsqueda.
-
Los tipos de datos de la columna de origen y la columna de búsqueda deben ser compatibles. Para obtener más información sobre los tipos de datos, vea Tipos de datos en los modelos de datos.
Para obtener más información acerca de las relaciones de tablas, vea Relaciones entre tablas en un modelo de datos.
