Resumen: Este es el primer tutorial de una serie diseñada para que conozca y se sienta cómodo usando Excel y sus funciones integradas de mash-up y análisis 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 capacidades de inteligencia empresarial de Excel, las tablas dinámicas, Power Pivot y Power View.
En estos tutoriales aprenderá a importar y explorar datos en Excel, crear y refinar un modelo de datos con Power Pivot y crear informes interactivos con Power View que puede publicar, proteger y compartir.
Los tutoriales de esta serie son los siguientes:
- Importar datos a Excel 2016 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.
Este tutorial utiliza datos que describen medallas olímpicas, países anfitriones y los diversos eventos olímpicos deportivos. Le recomendamos que siga los tutoriales en orden.
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 siguientes vínculos para descargar los archivos que usaremos durante esta serie de tutoriales. Descargue cada uno de los cuatro archivos en una ubicación a la que se pueda obtener acceso fácilmente, como Descargas o Mis documentos, o a una nueva carpeta que cree:
> Base de datos de Access de OlympicMedals.accdb
> OlympicSports.xlsx libro de Excel
> Population.xlsx libro de Excel
> DiscImage_table.xlsx libro de ExcelEn Excel, abra un libro vacío.
Haga clic en Obtener datos > de base de datos >> de base de datos de Microsoft Access. La cinta de opciones se ajusta dinámicamente en función del ancho del libro, por lo que los comandos de la cinta pueden tener un aspecto ligeramente diferente al de la pantalla siguiente.
Seleccione el archivo OlympicMedals.accdb que descargó y haga clic en Importar. Aparece la siguiente ventana Navegador, que muestra las tablas que se encuentran en la base de datos. Las tablas de la base de datos son similares a las hojas de cálculo o las tablas de Excel. Active la casilla Seleccionar varias tablas y seleccione todas las tablas. A continuación, haga clic en Cargar > en.
Aparece la ventana Importar datos.
Nota
Observe la casilla de verificación 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 simultáneamente. Un modelo de datos integra las tablas, lo que permite realizar análisis exhaustivos con tablas dinámicas, Power Pivot y Power View. Al importar tablas desde una base de datos, las relaciones existentes entre dichas 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 con el complemento Power Pivot. El modelo de datos se trata 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.
Una vez importados los datos, se crea una tabla dinámica con las tablas importadas.
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.
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 junto a ella. Busque el campo PaísrRegión_CON en la tabla Medallas expandida y arrástrelo hasta el área COLUMNAS. CON (NOC) son las siglas en inglés de Comités Olímpicos Nacionales, que es la unidad organizativa de un país o una 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 lugar 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 se expande la tabla Disciplinas, desplace el puntero sobre el campo Disciplina y aparecerá una flecha desplegable a la derecha del campo. Haz clic en el menú desplegable, haz clic en **(Seleccionar todo)**para quitar todas las selecciones y, a continuación, desplázate hacia abajo y selecciona 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.
La tabla dinámica es similar a la de la pantalla siguiente.
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 aún resaltados, presione Ctrl + T para darles formato de tabla. También puede dar formato de tabla a los datos desde la cinta de opciones seleccionando Formato DE INICIO > como tabla. Dado que los datos tienen encabezados, seleccione Mi tabla tiene encabezados en la ventana Crear tabla que aparece, tal como se muestra aquí.
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 identificar. También puede establecer relaciones entre tablas, lo que permite la exploración y el análisis en tablas dinámicas, Power Pivot y Power View.
Asigne un nombre a la tabla. En Propiedades de DISEÑO > DE TABLA, busque el campo Nombre de tabla y escriba Deportes. El libro es similar al de la pantalla siguiente.
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.
- 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 describió anteriormente en este tutorial, presione Ctrl + T para dar formato de tabla a los datos, o bien desde FORMATO HOME > como 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 Propiedades DE DISEÑO > DE TABLA busque el campo Nombre de 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.
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 superior deCampos de tabla dinámica, haga clic enTodo para ver la lista completa de tablas disponibles, como se muestra en la pantalla siguiente.
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 solicita que cree una relación, como puede verse en la pantalla siguiente.
Esta notificación se produce porque usó campos de una tabla que no forma parte del modelo de datos subyacente. Una manera de agregar una tabla al modelo de datos es crear una relación a 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 y no repetidos. En los datos de ejemplo, la tabla Disciplinas importada desde la base de datos contiene un campo con códigos de deportes, llamado IDDeporte. Esos mismos códigos de deportes están presentes en forma de campo en los datos de Excel que importamos. Vamos a crear la relación.Haga clic en CREAR... en el área resaltada de Campos de tabla dinámica para abrir el cuadro de diálogo Crear relación, tal como se muestra en la siguiente pantalla.
En Tabla, elija Tabla de modelo de datos: Disciplinas en la lista desplegable.
En Columna (extranjero), elija IDDeporte.
En Tabla relacionada, elija Tabla de modelo de datos: 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 todavía no tiene el aspecto correcto, debido a al orden de campos del área FILAS. Disciplina es una subcategoría de un determinado deporte, pero, debido a que acomodamos Disciplina por encima de Deporte en el área FILAS, no se encuentra organizada de forma correcta. La siguiente pantalla muestra esta 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.
En segundo plano, Excel está creando un modelo de datos que se puede usar en todo el libro, en cualquier tabla dinámica, gráfico dinámico, powerpivot 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, Extender relaciones del modelo de datos con Excel, Power Pivot**, y DAX**, deberá basarse en lo que ha aprendido aquí y seguir extendiendo el modelo de datos con un potente y visual complemento de Excel denominado Power Pivot. También aprenderá a calcular columnas en una tabla y a usar dicha columna calculada para que se pueda agregar una tabla no relacionada 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:
Tutorial: Ampliar relaciones del modelo de datos usando Excel, 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 se excluyen del modelo de datos.
C: Si convierte datos importados en tablas, se pueden incluir en el modelo de datos y estar disponibles para las 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 cuestionario
- Respuesta correcta: C
- Respuesta correcta: D
- Respuesta correcta: B
- Respuesta correcta: D
Nota
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