Tutorial: importar datos en Excel y crear un modelo de datos
Excel para Office 365 Excel 2019 Excel 2016 Excel 2013
Nota: Nos gustaría proporcionarle el contenido de ayuda actual lo más rápido posible en su idioma. Esta página se ha traducido mediante un sistema automático y es posible que contenga imprecisiones o errores gramaticales. Nuestro objetivo es que este contenido sea útil para usted. ¿Puede informarnos sobre si la información le ha sido útil al final de la página? Aquí tiene el artículo en inglés para que pueda consultarlo fácilmente.
Resumen: Este es el primer tutorial de una serie destinada a que conozca y se sienta cómodo con el uso de Excel y sus funciones integradas de análisis y mezcla de datos. Estos tutoriales permiten generar y refinar un libro de Excel desde cero, crear un modelo de datos y hacer increíbles informes interactivos con Power View. Los tutoriales están diseñados para demostrar las características y las capacidades de inteligencia empresarial de Excel, las tablas dinámicas, Power Pivot y Power View.
Nota: En este artículo se describen modelos de datos en Excel 2013. Sin embargo, las mismas características de Power Pivot y modelado de datos que se incluyen en Excel 2013 también son válidas para Excel 2016.
En estos tutoriales se aprende a importar y explorar datos en Excel, crear y refinar un modelo de datos con Power Pivot y hacer informes interactivos con Power View que pueda publicar, proteger y compartir.
Los tutoriales de esta serie son los siguientes:
Importar datos en Excel 2013 y crear un modelo de datos
Ampliar las relaciones del modelo de datos con Excel, Power Pivot y DAX
Crear informes de Power View basados en mapas
Incorporar datos de Internet y establecer valores predeterminados para los informes de Power View
Ayuda de Power Pivot
Crear informes de Power View sorprendentes - Parte 2
En este tutorial, empezará con un libro de Excel en blanco.
Las secciones de este tutorial son los siguientes:
Importar datos desde una base de datos
Importar datos de una hoja de cálculo
Importar datos con la función copiar y pegar
Crear una relación entre datos importados
Control y evaluación
Al final de este tutorial hay una evaluación que puede realizar para probar su aprendizaje.
Esta serie de tutorial usa datos que describen medallas olímpica, países de hospedaje y diversos acontecimientos deportivos Olímpicos. Le sugerimos que examine cada tutorial por orden. Asimismo, los tutoriales usan Excel 2013 con Power Pivot habilitado. Para obtener más información sobre Excel 2013, haga clic aquí. Para obtener instrucciones sobre cómo habilitar Power Pivot, haga clic aquí.
Importar datos desde una base de datos
Empezaremos este tutorial con un libro en blanco. El objetivo de esta sección es conectarse a un origen de datos externo e importar datos en Excel para realizar un análisis más exhaustivo.
Empecemos por descargar algunos datos de Internet. Los datos describen medallas olímpicas y son parte de una base de datos de Microsoft Access.
Haga clic en los vínculos siguientes para descargar archivos que usamos durante esta serie de tutoriales. Descargue cada uno de los cuatro archivos a una ubicación de fácil acceso, como descargas o Mis documentos, o a una nueva carpeta que cree:
base de
datos de Access > OlympicMedals. accdb
libro de Excel > OlympicSports. xlsx
libro de Excel > Population. xlsxlibro de Excel GT DiscImage_table. xlsx
En Excel, abra un libro vacío.
Haga clic en GT de datos para obtener GT de datos externos de Access. La cinta se ajusta dinámicamente en función del ancho del libro, por lo que los comandos de la cinta de opciones pueden tener un aspecto ligeramente distinto al de las pantallas siguientes. La primera pantalla muestra la cinta de opciones cuando un libro es amplio, la segunda imagen muestra un libro cuyo tamaño se ha cambiado para que ocupe solo una parte de la pantalla.
Importar datos desde Access Importar datos desde Access con una cinta de opciones pequeña
Seleccione el archivo OlympicMedals. accdb que ha descargado y haga clic en abrir. Aparece la siguiente ventana Seleccionar tabla, que muestra las tablas que se encuentran en la base de datos. Las tablas de una base de datos son similares a las hojas de cálculo o las tablas de Excel. Active la casilla Habilitar selección de varias tablas y seleccione todas las tablas. A continuación, haga clic en Aceptar.
Seleccione la ventana de tablas
Aparece la ventana Importar datos.
Nota: Observe la casilla situada en la parte inferior de la ventana que le permite Agregar estos datos al modelo de datos, que se muestra en la pantalla siguiente. Un modelo de datos se crea automáticamente al importar o trabajar con dos o más tablas a la vez. Un modelo de datos integra las tablas, lo que permite realizar análisis extensivos mediante el uso de tablas dinámicas, Power Pivot y Power View. Al importar tablas de una base de datos, las relaciones de base de datos existentes entre esas tablas se usan para crear el modelo de datos en Excel. El modelo de datos es transparente en Excel, pero puede verlo y modificarlo directamente mediante el complemento Power Pivot. El modelo de datos se describe con más detalle más adelante en este tutorial.
Seleccione la opción Informe de tabla dinámica, que importa las tablas a Excel y prepara una tabla dinámica para analizar las tablas importadas y haga clic en Aceptar.
Ventana Importar datos
Una vez importados los datos, se crea una tabla dinámica con las tablas importadas.
Tabla dinámica en blanco
Con los datos importados en Excel y el modelo de datos creado automáticamente, ya estará listo para explorar los datos.
Explorar datos con una tabla dinámica
Explorar datos importados es sencillo mediante una tabla dinámica. En una tabla dinámica, arrastre los campos (parecidos a las columnas de Excel) de las tablas (como las tablas que acaba de importar de la base de datos de Access) a distintas áreas de la tabla dinámica para ajustar el modo en que se presentan los datos. Una tabla dinámica tiene cuatro áreas: FILTROS, COLUMNAS, FILAS y VALORES.
Las cuatro áreas de Campos de tabla dinámica
Puede que se requieran varias pruebas para determinar a qué área se debe arrastrar un campo. Puede arrastrar todos los campos que quiera a las tablas hasta que la tabla dinámica presente los datos del modo en que desea verlos. No dude en probar arrastrar campos a diferentes áreas de la tabla dinámica; los datos subyacentes no se ven afectados cuando arrastra campos a una tabla dinámica.
Vamos a explorar los datos de medallas olímpicas en la tabla dinámica, comenzando por los medallistas olímpicos organizados por disciplina, tipo de medalla y país o región del atleta.
En campos de tabla dinámica, expanda la tabla medallas haciendo clic en la flecha que hay junto a ella. Busque el campo NOC_CountryRegion en la tabla medallas expandidas y arrástrelo hasta el área columnas. NOC significa para comités olímpicos nacionales, que es la unidad organizativa de un país o región.
A continuación, desde la tabla Disciplinas, arrastre Disciplina hasta el área FILAS.
Vamos a filtrar Disciplinas para mostrar sólo cinco deportes: tiro con arco, saltos, esgrima, patinaje artístico y patinaje de velocidad. Esto se puede hacer desde dentro del área Campos de tabla dinámica, o desde el filtro Etiquetas de fila de la propia tabla dinámica.
Haga clic en cualquier parte de la tabla dinámica para asegurarse de que la tabla dinámica de Excel esté seleccionada. En la lista Campos de tabla dinámica, donde la tabla Disciplinas está expandida, pase el cursor sobre el campo Disciplina para que aparezca una flecha desplegable en la parte derecha. Haga clic en el menú desplegable, luego en (Seleccionar todo)para eliminar todas las selecciones y, a continuación, desplácese hacia abajo y seleccione tiro con arco, saltos, esgrima, patinaje artístico y patinaje de velocidad. Haga clic en Aceptar.
O bien, en la sección Etiquetas de fila de la tabla dinámica, haga clic en el menú desplegable que se encuentra al lado de Etiquetas de fila, haga clic en (Seleccionar todo) para eliminar todas las selecciones y, a continuación, desplácese hacia abajo y seleccione tiro con arco, saltos, esgrima, patinaje artístico y patinaje de velocidad. Haga clic en Aceptar.
En Campos de tabla dinámica, en la tabla Medallas, arrastre Medalla al área VALORES. Puesto que los valores deben ser numéricos, Excel cambia automáticamente Medalla a Recuento de medallas.
En la tabla Medallas, seleccione Medalla nuevamente y arrástrela al área FILTROS.
Vamos a filtrar la tabla dinámica con el fin de que muestre solo los países o las regiones con más de 90 medallas. Para ello, proceda de la siguiente manera.
En la tabla dinámica, haga clic en la lista desplegable situada a la derecha de Etiquetas de columna.
Seleccione Filtros de valor y luego Mayor que…
Escriba 90 en el último campo (a la derecha). Haga clic en Aceptar.
Ventana Filtro de valor
La tabla dinámica es similar a la de la pantalla siguiente.
Tabla dinámica actualizada
Con poco trabajo, ahora dispone de una tabla dinámica básica que incluye campos de tres tablas distintas. Lo que ha facilitado tanto esta tarea fueron las relaciones ya existentes entre las tablas. Dado que existían relaciones de tabla en la base de datos de origen y que importó todas las tablas en una sola operación, Excel pudo volver a crear esas relaciones en el modelo de datos.
Sin embargo, ¿qué sucede si los datos provienen de orígenes distintos o se importan más adelante? Normalmente, puede crear relaciones con datos nuevos según las columnas coincidentes. En el paso siguiente, importará tablas adicionales y aprenderá cómo crear relaciones nuevas.
Importar datos de una hoja de cálculo
Ahora vamos a importar datos desde otro origen, esta vez desde un libro existente, y, a continuación, especificar las relaciones entre nuestros datos existentes y los datos nuevos. Las relaciones le permiten analizar colecciones de datos en Excel y crear visualizaciones interesantes e inmersivas de los datos que importa.
Empecemos creando una hoja de cálculo en blanco, para luego importar datos de un libro de Excel.
Inserte una nueva hoja de cálculo de Excel y nómbrela Deportes.
Vaya a la carpeta que contiene los archivos de datos de muestra descargados y abra OlympicSports.xlsx.
Seleccione y copie los datos en Hoja1. Si selecciona una celda con datos, como, por ejemplo, la celda A1, puede presionar Ctrl + A para seleccionar todos los datos adyacentes. Cierre el libro OlympicSports.xlsx.
En la hoja de cálculo Deportes, coloque el cursor en la celda A1 y pegue los datos.
Con los datos aun resaltado, presione Ctrl + T para dar formato a los datos como una tabla. También puede dar formato a los datos como una tabla de la cinta de opciones si selecciona Inicio GT dar formato como tabla. Dado que los datos tienen encabezados, seleccione la tabla tiene encabezados en la ventana crear tabla que aparece, como se muestra aquí.
Ventana Crear tabla
Aplicar formato a los datos como una tabla tiene muchas ventajas. Puede asignar un nombre a una tabla, lo que hace que sea fácil de identificar. También puede establecer relaciones entre tablas, lo que permite explorar y analizar en tablas dinámicas, Power Pivot y Power View.
Asigne un nombre a la tabla. En herramientas de tabla GT diseñar propiedades de >, busque el campo nombre de tabla y escriba deportes. El libro es similar a la pantalla siguiente.
Nombrar una tabla en Excel
Guarde el libro.
Importar datos con la función copiar y pegar
Ahora que hemos importado datos de un libro de Excel, vamos a importar datos de una tabla de una página web o de cualquier otra fuente de la que podamos copiar y pegar en Excel. En los pasos siguientes, agregará las ciudades anfitrionas de los Juegos Olímpicos de una tabla.
Inserte una nueva hoja de cálculo de Excel y asígnele el nombre Anfitriones.
Seleccione y copie la siguiente tabla, incluidos los encabezados de tabla.
Ciudad
PaísRegión_CON
Código Alfa 2
Edición
Temporada
Melbourne / Estocolmo
AUS
AS
1956
Verano
Sydney
AUS
AS
2000
Verano
Innsbruck
AUT
AT
1964
Invierno
Innsbruck
AUT
AT
1976
Invierno
Antwerp
BEL
BE
1920
Verano
Antwerp
BEL
BE
1920
Invierno
Montreal
CAN
CA
1976
Verano
Lake Placid
CAN
CA
1980
Invierno
Calgary
CAN
CA
1988
Invierno
St. Moritz
SUI
SZ
1928
Invierno
St. Moritz
SUI
SZ
1948
Invierno
Beijing
CHN
CH
2008
Verano
Berlín
GER
GM
1936
Verano
Garmisch-Partenkirchen
GER
GM
1936
Invierno
Barcelona
ESP
SP
1992
Verano
Helsinki
FIN
FI
1952
Verano
París
FRA
FR
1900
Verano
París
FRA
FR
1924
Verano
Chamonix
FRA
FR
1924
Invierno
Grenoble
FRA
FR
1968
Invierno
Albertville
FRA
FR
1992
Invierno
Londres
GBR
Reino Unido
1908
Verano
Londres
GBR
Reino Unido
1908
Invierno
Londres
GBR
Reino Unido
1948
Verano
Munich
GER
DE
1972
Verano
Atenas
GRC
GR
2004
Verano
Cortina d’Ampezzo
ITA
IT
1956
Invierno
Roma
ITA
IT
1960
Verano
Turín
ITA
IT
2006
Invierno
Tokio
JPN
JA
1964
Verano
Sapporo
JPN
JA
1972
Invierno
Nagano
JPN
JA
1998
Invierno
Seúl
KOR
KS
1988
Verano
México
MEX
MX
1968
Verano
Ámsterdam
NED
NL
1928
Verano
Oslo
NOR
NO
1952
Invierno
Lillehammer
NOR
NO
1994
Invierno
Estocolmo
SWE
SW
1912
Verano
St Louis
USA
US
1904
Verano
Los Ángeles
USA
US
1932
Verano
Lake Placid
USA
US
1932
Invierno
Squaw Valley
USA
US
1960
Invierno
Moscú
URS
RU
1980
Verano
Los Ángeles
USA
US
1984
Verano
Atlanta
USA
US
1996
Verano
Salt Lake City
USA
US
2002
Invierno
Sarajevo
YUG
YU
1984
Invierno
En Excel, coloque el cursor en la celda A1 de la hoja de cálculo Anfitriones y pegue los datos.
Aplique formato de tabla a los datos. Como se ha descrito anteriormente en este tutorial, para dar formato de tabla a los datos, debe presionar Ctrl + T o hacer clic en INICIO > Dar formato de tabla. Dado que los datos tienen encabezados, seleccione Mi tabla tiene encabezados en la ventana Crear tabla que aparece.
Asigne un nombre a la tabla. En HERRAMIENTAS DE TABLA > DISEÑO > Propiedades, busque el campo Nombre de la tabla y escriba Anfitriones.
Seleccione la columna Edición y, desde la pestaña INICIO, dele el formato Número con 0 posiciones decimales.
Guarde el libro. El libro es similar al de la pantalla siguiente.
Tabla Host
Ahora que tiene un libro de Excel con tablas, puede crear relaciones entre ellas. La creación de relaciones entre tablas le permite mezclar los datos de las dos tablas.
Crear una relación entre datos importados
Puede comenzar inmediatamente usando campos de las tablas importadas en la tabla dinámica. Si Excel no puede determina cómo incorporar un campo a una tabla dinámica, se debe establecer una relación con el modelo de datos existente. En los pasos siguientes, aprenderá a crear una relación entre los datos importados de orígenes diferentes.
En Hoja1, en la parte superiordelos campos de tabla dinámica, haga clicentodos para ver la lista completa de tablas disponibles, como se muestra en la siguiente pantalla.
Haga clic en Todas en Campos de tabla dinámica para mostrar todas las tablas disponibles.
Desplácese por la lista para ver las tablas nuevas que acaba de agregar.
ExPanda deportes y seleccione deporte para agregarlo a la tabla dinámica. Observe que Excel le pide que cree una relación, tal y como se muestra en la siguiente pantalla.
Solicitud de CREAR… relación en Campos de tabla dinámica
Esta notificación se produce porque usó campos de una tabla que no forma parte del modelo de datos subyacente. Una forma de agregar una tabla al modelo de datos es crear una relación con una tabla que ya esté en el modelo de datos. Para crear la relación, una de las tablas debe tener una columna de valores únicos, no repetidos. En los datos de ejemplo, la tabla disciplinas importada de la base de datos contiene un campo con códigos deportivos, denominado iddeporte. Estos mismos códigos deportivos están presentes como un campo en los datos de Excel que importamos. Vamos a crear la relación.
Haga clic en crear… en el área campos resaltados de la tabla dinámica para abrir el cuadro de diálogo crear relación, como se muestra en la siguiente pantalla.
Ventana Crear relación
En Tabla, elija Disciplinas en la lista desplegable.
En Columna (extranjero), elija IDDeporte.
En Tabla relacionada, elija Deportes.
En Columna relacionada (principal), elija IDDeporte.
Haga clic en Aceptar.
La tabla dinámica cambia para reflejar la nueva relación. Sin embargo, la tabla dinámica no se ve bien aún, debido al orden de los campos en el área filas. La disciplina es una subcategoría de un deporte dado, pero dado que organizó una disciplina por encima del deporte en el área filas, no está organizada correctamente. En la siguiente pantalla se muestra el orden no deseado.
Tabla dinámica con ordenación no deseada
En el área FILAS, mueva Deporte arriba de Disciplina. Así es mucho mejor, y la tabla dinámica muestra los datos de la forma en que desea, tal como se muestra en la pantalla siguiente.
Tabla dinámica con ordenación corregida
En segundo plano, Excel está generando un modelo de datos que se puede usar en el libro, en cualquier tabla dinámica, gráfico dinámico, Power Pivot o cualquier informe de Power View. Las relaciones de tabla son la base de un modelo de datos y lo que determina las rutas de navegación y cálculo.
En el siguiente tutorial, Ampliar relaciones del modelo de datos usando Excel 2013, Power Pivot y DAX, hará lo que aprendió aquí y realizará los pasos para ampliar el modelo de datos con un complemento sofisticado y visual de Excel llamado Power Pivot. También sabrá cómo calcular una columna de una tabla y usar esa columna calculada para que una tabla no relacionada pueda agregarse al modelo de datos.
Control y evaluación
Revise lo que ha aprendido
Ahora tiene un libro de Excel que incluye datos de acceso de una tabla dinámica en varias tablas, de las cuales muchas importó por separado. Aprendió a importar desde una base de datos y desde otro libro de Excel, y a copiar los datos y pegarlos en Excel.
Para que los datos funcionen juntos, tuvo que crear una relación de tabla que Excel usó para correlacionar las filas. También ha aprendido que tener columnas en una tabla que estén correlacionadas a datos de otra tabla es fundamental para crear relaciones y buscar filas relacionadas.
Ahora está listo para realizar el siguiente tutorial de la serie. He aquí un vínculo:
Ampliar relaciones del modelo de datos usando Excel 2013, Power Pivot y DAX
Evaluación
¿Desea saber qué tan bien recuerda lo que aprendió? Aquí tiene la oportunidad de hacerlo. La siguiente evaluación resalta las características, las capacidades o los requisitos que ha aprendido en este tutorial. En la parte inferior de la página, encontrará las respuestas. ¡Buena suerte!
Pregunta 1: ¿Por qué es importante convertir datos importados en tablas?
A: No es necesario, ya que todos los datos importados se convierten en tablas de forma automática.
B: Si los datos importados se convierten en tablas, se eliminan del modelo de datos. Solo están disponibles en tablas dinámicas, Power Pivot y Power View cuando están excluidos del modelo de datos.
C: Si los datos importados se convierten en tablas, se pueden incluir en el modelo de datos y puede dejarse disponibles para tablas dinámicas, Power Pivot y Power View.
D: Los datos importados no se pueden convertir en tablas.
Pregunta 2: ¿Cuál de las siguientes fuentes de datos puede importar a Excel e incluir en el modelo de datos?
A: Bases de datos de Access y muchas otras bases de datos también.
B: Archivos de Excel existentes.
C: Todo lo que se pueda copiar y pegar en Excel y dar formato de tabla, incluidas tablas de datos de sitios web, documentos o cualquier otro elemento que se pueda pegar en Excel.
D: Todas las respuestas anteriores
Pregunta 3: En una tabla dinámica, ¿qué ocurre cuando reordena los campos de las cuatro áreas de Campos de tabla dinámica?
A: Nada. No se pueden reordenar los campos una vez que se colocan en las áreas de Campos de tabla dinámica.
B: El formato de la tabla dinámica cambia para reflejar la distribución, pero los datos subyacentes no se ven afectados.
C: El formato de la tabla dinámica cambia para reflejar la distribución, y todos los datos subyacentes cambian de forma permanente.
D: Los datos subyacentes cambian y pasan a ser conjuntos de datos nuevos.
Pregunta 4: Al crear una relación entre tablas, ¿qué es lo que se requiere?
A: Ninguna tabla puede tener una columna que contenga valores únicos no repetidos.
B: Una tabla no debe ser parte del libro de Excel.
C: Las columnas no se deben convertir en tablas.
D: Ninguna de las afirmaciones anteriores es correcta.
Respuestas de la evaluación
Respuesta correcta: C
Respuesta correcta: D
Respuesta correcta: B
Respuesta correcta: D
Notas: Los datos y las imágenes de este tutorial se basan en lo siguiente:
Olympics Dataset de Guardian News & Media Ltd.
Imágenes de banderas cortesía de CIA Factbook (cia.gov)
Datos de población cortesía del Banco mundial (worldbank.org)
Pictogramas de deportes olímpicos por Thadius856 y Parutakupiu
