Excel •

Una de las peculiaridades de Excel que lo hacen muy interesante como Sistema Informacional es su enorme éxito.

•

Es quizás la aplicación de productividad (en el sentido más general) más famosa y extendida de Office.

•

No sólo se manejan millones de tablas Excel por innumerables empresas en el mundo sino que muchos Sistemas de Información exportan sus datos a tablas Excel o Excel puede importar tablas de los mismos.

•

Excel 2013 ha supuesto en este sentido una auténtica revolución a esta cómoda situación con la incorporación de varios complementos muy potentes: Power Pivot para tablas dinámicas y consultas elaboradas (lenguaje DAX) y Power View junto a características muy novedosas como el análisis rápido, las escalas de tiempo y el relleno automático. – Descargar Componente redistribuible del motor de base de datos de Microsoft Access 2010 (http://www.microsoft.com/eses/download/details.aspx?id=13255) -2-

Tablas Dinámicas en Excel •

Es una de las peculiaridades de Excel que no tienen otras aplicaciones de hojas de cálculo (algunas como Zoho Reports la han incorporado ya).

•

Las tablas dinámicas nos permiten ver la misma información de muchas formas diferentes, transformando tablas muy extensas en una representación de datos con sentido.

•

Se corresponden con las operaciones con totales de las bases de datos.

•

Si, por ejemplo, partimos de un libro con calificaciones de alumnos, cabría preguntarnos: de entre los alumnos presentados, ¿cuál es el porcentaje de suspensos, aprobados, notables, sobresalientes y matrículas de honor?.

•

Esta técnica consiste en agrupar por el campo nota (literal) y contar el número de registros con el mismo valor representando el resultado con % del total de presentados. -3-

Tablas Dinámicas en Excel •

Las tablas dinámicas se encuentran en dos sitios: entre los botones del grupo Tablas de la ficha Insertar y en la ficha Inicio de la ventana de Power Pivot (clic sobre Administrar en el grupo Modelo de datos de la ficha Power Pivot).

•

Los gráficos dinámicos no son más que tablas dinámicas representadas en forma de gráfico:

-4-

Tablas Dinámicas en Power Pivot •

Las tablas dinámicas de Power Pivot presentan una serie de características avanzadas con respecto a las que generaríamos con la ficha Insertar. Por lo tanto a partir de ahora nos vamos a referir sólo a tablas dinámicas con Power Pivot.

•

Power Pivot, entre otras cosas, permite realizar de forma muy rápida cálculos sobre extensas hojas con muchos datos.

•

También podemos afirmar que Power Pivot permite administrar y modificar el modelo de datos.

•

Power Pivot presenta tres características diferenciadoras en el tratamiento de tablas dinámicas:

– La posibilidad de que una tabla dinámica se base en un conjunto de tablas múltiples (a diferencia de una sola tabla). Dichas tablas hay que interrelacionarlas (como las de las bases de datos). – La mayor capacidad para contener grandes volúmenes de datos (filas) dentro de una misma hoja de cálculo de Excel. – La utilización de un lenguaje muy potente de fórmulas llamado DAX. -5-

Power Pivot para Excel •

Descargamos de la web TRABAJAN_EN_ALUMNOS.XLSX y hacemos Power Pivot -> Administrar:

•

Con Power Pivot tendremos un libro Excel para ver las consultas y una ventana donde importamos y realizamos diferentes análisis de datos. Esto significa que contaremos con todos los elementos propios de Excel, más otros nuevos que añade el complemento. -6-

Relaciones entre tablas •

Una novedad muy importante es que existe la posibilidad de interrelacionar las tablas procedentes de muy diversas fuentes (donde el formato no tiene por qué ser de tabla). El resultado se conoce como MODELO DE DATOS.

•

Aquí importaremos las tablas EMPLEADOS de EMPLEADOS.XLSX y CLIENTES de CLIENTES.XLSX (desde la ventana de Power Pivot).

•

No hay nada mejor para consumir poca memoria que las columnas inexistentes

•

Si queremos crear un modelo eficiente, miraremos antes de importar cada columna y nos preguntaremos si contribuye positivamente al análisis que deseamos realizar. Posteriormente, podremos agregar columnas nuevas si las necesitáramos.

-7-

Relaciones entre tablas

•

Así, sería conveniente en la segunda tabla prescindir de la columna OTRA INFORMACIÓN (no vamos a utilizarla y los valores están duplicados). -8-

Relaciones entre tablas •

La tabla EMPLEADOS contiene datos referentes a unos empleados de una organización y CLIENTES las compras realizadas por clientes de unos grandes almacenes.

•

La cuestión es que desde la ventana de Power Pivot podemos establecer una interrelación entre las dos tablas (en principio totalmente independientes) utilizando el campo (común aunque se llamen de distinta manera) dni (en EMPLEADOS) y cliente (en CLIENTES).

•

Después de establecer la interrelación nos podremos plantear las consultas: ¿Cómo se llama el cliente (empleado) que ha comprado un artículo? ¿cuál es su fecha de nacimiento? ¿Cuál es su sueldo por hora?

•

Estas preguntas suponen implícitamente que existe al menos una relación entre las dos tablas (ya que de otro modo sería inviable realizarlas).

-9-

Relaciones entre tablas •

Si, desde la ficha Diseñar (de la ventana de Power Pivot) hacemos clic sobre el botón Administrar relaciones obtendremos la ventana de diálogo:

•

De donde concluimos que no existe ninguna relación entre las dos tablas.

•

Para llevar a cabo las consultas debemos crear una relación con el botón Crear Lógicamente el campo común que estable dicha relación será el dni de EMPLEADOS relacionado al cliente de CLIENTES:

-10-

Relaciones entre tablas •

Ahora podemos escribir en una celda cualquiera de las columnas con encabezado Agregar columna de la tabla CLIENTES (no en la de EMPLEADOS ya que habría varias respuesta por fila y problemas de representación del dato) las fórmulas:

(Escriba en la caja fx. Observe que un asistente le ayuda a rellenar la fórmula. Cierre el paréntesis final) =related(EMPLEADOS[empleado]) Para la consulta ¿Cómo se llama el cliente que ha comprado un artículo? •

related es la función DAX que nos devuelve el valor del campo en la tabla interrelacionada (para cada cliente o dni, su nombre).

-11-

Relaciones entre tablas =related(EMPLEADOS[fecha_nac]) Para ¿cuándo nació? y =related(EMPLEADOS[sueldo_hora]) Para la consulta ¿Cuánto gana a la hora el empleado?

•

Establecer interrelaciones resulta más interesante aún para el caso de consultas con totales o tablas dinámicas.

•

Un ejemplo lo tenemos en la consulta: ¿cuánto ganó el empleado?

-12-

Relaciones entre tablas •

Para ello deberemos agrupar por empleado y sumar las horas totales. Posteriormente añadiremos la medida que devuelve el producto del total de horas por sueldo_hora.

•

Para ello nos falta una tabla que precisamente tenemos en la hoja TRABAJAN_EN del libro con el que estamos trabajando.

•

De nuevo esto nos demuestra la versatilidad y potencia de Power Pivot. Nos bastará con colocarnos cualquier celda de la tabla (la A1 por ejemplo) y hacer clic en el botón Agregar a modelo de datos de la ficha Power Pivot (encenderemos Mi tabla

tiene encabezados):

-13-

Relaciones entre tablas •

Ahora procedemos a calcular el número total de horas que ha trabajado cada empleado.

•

Esto se obtiene por una tabla dinámica donde agrupamos por empleado y sumamos horas.

•

Haremos clic en Tabla dinámica (en la ficha Inicio de la ventana de Power Pivot y escogemos la primera opción. Luego veremos las otras opciones). En el panel derecho tomando Tabla1 (que es TRABAJAN_EN) empujamos dni_e a FILAS y horas a sumatorio VALORES:

-14-

Relaciones entre tablas •

Si interrelacionamos TRABAJAN_EN y EMPLEADOS podemos empujar nombre en vez de dni_e y nos encontraremos con los nombres de los empleados frente al número total de horas trabajadas (después de establecer la interrelación haga clic en Actualizar todo de la ficha Power Pivot):

-15-

Relaciones entre tablas •

Para calcular el sueldo total del empleado deberemos multiplicar el total de horas por el sueldo hora.

•

Lo primero será añadir el campo sueldo_hora, para lo que arrastramos dicho campo a sumatorio VALORES (como es único el sumatorio es el valor mismo).

•

Ahora agregamos un campo calculado a la tabla dinámica:

-16-

Relaciones entre tablas •

Y escribimos la fórmula:

•

Con lo que tendremos:

-17-

Relaciones entre tablas •

Esto también podríamos haberlo hecho con la función DAX: relatedTable. A diferencia de la función related, devuelve una tabla (por la que debe ser utilizada como argumento de otra función) con las filas relacionadas con la actual.

•

Ahora la fórmula en la correspondiente columna calculada no tiene por qué estar en el lado muchos (de una relación uno a muchos), sino que puede estar en cualquier lado ya que al devolver una tabla y ser el argumento de una función que devolverá un valor, no habrá problemas de representación del dato.

•

Con todo ello podríamos calcular el sueldo del empleado en EMPLEADOS como:

=sumx(relatedtable(TRABAJAN_EN);TRABAJAN_EN[horas]) Con lo que obtendremos el número total de horas trabajadas por el empleado. Y:

=[total de horas]*[sueldo_hora ] Con lo que obtendremos el sueldo del empleado (se ha llamado total de horas a la columna calculada de la primera fórmula).Ejemplo de la potencia del lenguaje DAX. -18-

Relaciones entre tablas •

Ahora podríamos llevar el sueldo del cliente (empleado) a la tabla CLIENTES y contestar a la pregunta : ¿cuánto ganó el cliente? (muy interesante por temas de morosidad si se niega a pagar un artículo financiado):

-19-

Columnas en tablas dinámicas •

Si arrastramos a COLUMNAS el campo proyectos podríamos conocer cuántas horas ha trabajado el empleado en cada proyecto:

-20-

Columnas en tablas dinámicas •

Así, el empleado Corvina López, María de 44 horas 12 las empleó en la automatización de descarga y 32 en el Estudio del cambio climático en la Antártida.

•

Un ejemplo más interesante lo tendríamos en el caso de que hubiese varios registros (filas en la tablas) en los que se repitiese el campo columna (proyecto) en este ejemplo.

•

De esta manera tendríamos dos agrupamientos. De hecho en este ejemplo también podríamos arrastrar el proyecto después de nombre en el campo FILAS y obtener el mismo resultado más compacto: -21-

Columnas en tablas dinámicas •

Otro ejemplo es la tabla REPLANTES importada de la base Arbolado urbano de Sevilla.accdb (descárguela de la Web). Los campos son la fecha, el árbol, el lugar y el número de árboles replantados para cada árbol.

•

La siguiente imagen muestra datos de la tabla indicada:

-22-

Columnas en tablas dinámicas •

Si ahora nos planteamos el número de replantes en cada sitio:

•

Observamos que el COCO PLUMOSO se ha replantado en el mismo sitio en dos ocasiones. Esto significa que tenemos dos agrupaciones: por árbol y por sitio. -23-

Filtros en tablas dinámicas •

Si ahora nos planteamos el número de replantes en cada sitio y en determinada fecha podremos utilizar el campo FILTRO y arrastrar hasta el mismo la fecha.

•

Así para los replantes en cada sitio del 1-1-81 al 1-6-81:

-24-

Filtros en las Tablas Dinámicas •

Esto mismo (de manera mucho más precisa y cómoda ) se consigue con las segmentaciones.

•

En efecto, quitamos la fecha del filtro y hacemos clic en crear segmentaciones de la ficha Analizar. Después bastará con que seleccionemos las fechas apuntadas (dejando presionada la tecla CTRL):

-25-

Filtros en las Tablas Dinámicas •

El filtro se puede aplicar utilizando diferentes campos.

•

Por ejemplo, supongamos que queremos filtrar además por lugar: sólo en Parque María Luisa y Avda. Luis Montoto (observamos que los resultados se adaptan inmediatamente a los filtros que escojamos):

-26-

Escalas de tiempo •

Cuando se trata de fechas es más adecuado utilizar escalas de tiempo.

•

En efecto si volvemos atrás y en vez de crear una segmentación de datos hacemos clic en crear escala de tiempo podremos con el ratón manipular qué escala de tiempo nos interesa filtrar:

•

Escogeremos visualizar las fechas por días. -27-

Las Tablas Dinámicas con Power Pivot •

Con Power Pivot las tablas y gráficos dinámicos pueden ser de muchos más tipos que en Excel. En concreto podremos crear:

 Tabla dinámica única: una tabla dinámica en blanco en la hoja actual o en una nueva.  Gráfico dinámico único: un gráfico dinámico en blanco.

 Tabla y gráfico dinámicos en horizontal uno al lado de otro. Estarán en blanco y son independientes aunque las segmentaciones (filtros) de datos serán las mismas para ambos.  Tabla y gráfico dinámicos en vertical. Igual que el anterior pero coloca el gráfico por encima de la tabla.  Dos gráficos horizontales: igual que la tabla y el gráfico en horizontal.

 Dos gráficos verticales: igual que la tabla y el gráfico en vertical.  Cuatro gráficos (puestos formando un cuadrado. De forma similar a los Cuadros de mando que veremos en el Tema VII). -28-

Gráficos dinámicos con Power Pivot •

En ejemplos y ejercicios vistos anteriormente podemos plantearnos mostrar varios gráficos obtenidos a partir de tablas dinámicas, con el fin de comparar resultados.

•

Esta es una cuestión que se ha resuelto en Power Pivot incorporando al botón PivotTable de la ventana principal otras opciones antes de generar la tabla dinámica.

•

Por ejemplo en la consulta última de la tabla replantes podemos escoger entre las opciones de PivotTable la de gráfico dinámico:

-29-

Gráficos dinámicos con Power Pivot •

Por último obtener cuatro gráficos según se muestra en la imagen:

•

La escala de tiempo se refiere al segundo gráfico. -30-

Informes en Excel a partir de datos importados de otras fuentes

Por último, en esta sección veremos cómo resulta muy potente y fácil importar datos de otras fuentes a la ventana de Power Pivot para generar informes a partir de los mismos.

El origen de los datos en Excel •

Los datos pueden tener su origen en transcripciones que realizamos de datos conocidos por diversos medios (prensa, libros, etc.); o que sencillamente hemos generado a partir de hechos o problemas conocidos por nosotros.

•

Ya hemos visto el ejemplo de las tablas EMPLEADOS y TRABAJAN_EN que contienen datos referentes a las horas trabajadas por diferentes empleados de una pequeña organización, y varias hojas resúmenes con resultados globales.

•

Sin embargo, vivimos en el siglo de la Información y Excel, como no podría ser de otra forma, nos facilita la importación/exportación de datos, de manera que podremos disponer de ellos como si de datos propios se tratase (de hecho todos los datos utilizados hasta ahora se han importado de tablas en Access).

-32-

Importando datos con Power Pivot •

Power Pivot es, sobre todo, un entorno para importar y relacionar datos de muy diversas fuentes (y elaborar informes con los mismos):

-33-

Importando listas desde archivos de texto •

Los listados en archivos de texto son una manera muy extendida de almacén de datos.

•

En general son datos provenientes de consultas que se han almacenado en archivos de texto, separados por delimitadores como tabuladores, espacios, comas o puntos y comas (a este tipo de archivos se les llama CSV o CommaSeparated Values y son un tipo muy importante de archivos en muchas aplicaciones). El separador suele ser una coma, un ; (muchas veces es preferible a la coma ya que ésta se confunde con la coma decimal).

•

Excel asume que la primera fila es la cabecera (aunque hay que activar la correspondiente casilla de verificación), y que los distintos campos están separados por el mismo delimitador .

•

Lo primero que haremos con un listado es ‘prepararlo’ en el sentido de añadirle una cabecera (si no la tiene) y establecer el delimitador (en general será la coma). -34-

Importando listas desde archivos de texto •

La siguiente imagen muestra un lista obtenida al importar el archivo csv (habremos de buscarlo en el disco, como con las bases de datos Access, y a la hora de importarlo decir que el delimitador es un punto y coma): suba clientes.csv

•

Procedemos de forma similar a la vista para Access y desde la ventana principal de Power Pivot hacemos obtener datos externos -> de texto y escoger la opción de archivos CSV (dese cuenta que hemos tenido que cambiar la primera fila). -35-

Importando listas desde archivos de texto •

Usaremos la primera fila como cabecera:

•

Con estos datos podremos realizar diferentes operaciones de manipulación (suponga que añade datos de archivos de texto, bases de datos y de otros libros Excel, podría intentar vincular las tablas mediante campos en común y realizar cálculos sorprendentes entre tablas que, en principio, no están relacionadas).

-36-

Tablas con millones de registros •

Por último, es interesante que estudiemos el comportamiento de Power Pivot cuando tiene que manejar tablas con millones de registros.

•

Para ello nos bajaremos de la web Power PivotDAXSamples.exe el archivo Excel: Contoso Sample DAX Formulas.xlsx

•

Si abrimos la ventana Power Pivot observaremos que la tabla que representa transacciones de ventas (sales) contiene casi tres millones y medio de registros!

•

Por otro lado, la tabla FactInventory, que almacena todas las transacciones de inventario, son más de ocho millones de registros! -37-

Tablas con millones de registros •

Observe que la hoja SalesBycoubtry contiene una tabla dinámica que resume el monto en ventas agrupando por países y por año con una fragmentación vertical del medio por el que se ha efectuado la venta.

-38-

Tablas con millones de registros •

Suponga ahora que le solicitan que calcule la media del monto de ventas por año.

•

Tendremos que colocar en las filas el año (quitando el país) y añadir una nueva medida (el formato de la celda es personalizado escogiendo el adecuado):

-39-

Informes en InfoPath

La aplicación para generar formularios de Office es InfoPath. En esta sección veremos cómo podemos crear formularios a partir de plantillas diseñadas para cargar los datos de bases de datos. Dichos formularios pueden importarse posteriormente a un modelo Power Pivot.

Informes en InfoPath •

Podemos utilizar InfoPath para acceder a datos almacenados en una base de datos Access. Esto permitirá obtener diferentes vistas externas (formularios) de los datos de una base de datos.

•

Desde InfoPath no es posible modificar o añadir datos a una base de datos Access. Para ello tendremos que utilizar los formularios que vimos en su momento en Access.

•

En concreto, Vamos a utilizar dos vistas “HORAS TOTALES PARA CADA EMPLEADO” y “NÚMERO DE PROYECTOS ASIGNADOS A CADA EMPLEADO” para visualizar algunos datos relacionados con los empleados de una base para una pequeña organización.accdb (descargada de la web de la asignatura).

-41-

Informes en InfoPath •

En InfoPath para poder disponer de formularios que lean de una base de datos Access, es necesario realizar una conexión a la misma (pueden realizarse varias conexiones a una misma base de datos o a varias bases de datos).

•

Un detalle importante (una vez importada la consulta de la base de datos) es que, por razones de seguridad, la plantilla en InfoPath correspondiente al formulario que lee de bases de datos debe ser de plena confianza:

•

No hace falta firmarlo (aunque puede hacerse y crear una firma con crear certificado…).

-42-

Informes en InfoPath •

A la hora de crear la plantilla no escogeremos el modelo formulario en blanco sino Plantilla de formulario avanzado -> Bases de datos:

-43-

Informes en InfoPath •

Al hacer doble clic en Base de datos, lo primero que se nos solicitará es una base de datos para realizar la conexión:

-44-

Informes en InfoPath •

Antes de importar la consulta (vista externa) de la base de datos hemos de realizarla.

•

La consulta es “horas totales para cada empleado” (hay que hacerla antes como parte del trabajo, si no la tiene ya hecha).

-45-

La plantilla pequeña organización •

De tal manera que al finalizar nuestra plantilla tendrá el aspecto:

•

Borramos el botón de acción Nuevo registro ya que, según hemos dicho, con Access no se permite modificar los datos a partir de un formulario de InfoPath.

-46-

La plantilla pequeña organización •

Haga los cambios pertinentes para obtener:

-47-

La plantilla pequeña organización •

Por defecto, los campos para consultas (queryFields) se añaden en una sección opcional, mientras que el resultado de las consultas (dataFields) se incorporan en una tabla extensible.

•

Ya podremos realizar consultas haciendo clic en Vista previa al final de la ficha Inicio.

•

Hagamos la consulta “horas trabajadas por el empleado Núñez Murcia, Leonardo” (si no introduce ningún dato se mostrarán todos los registros).

•

Después de escribir correctamente el nombre completo, haga clic en Ejecutar consulta.

-48-

Vistas •

Si ahora quisiéramos ver los proyectos en que participa un empleado no tenemos que hacer una plantilla nueva. Tan sólo crearemos una nueva vista:

-49-

Vistas •

A la nueva vista la llamamos “número de proyectos asignados a cada empleado” (se puede renombrar la vista anterior como “datos personales empleados”).

•

Aquí importaremos la consulta “número de proyectos asignados a cada empleado”. El aspecto puede ser algo parecido a (despliegue la pestaña campos y escoja la conexión nueva):

•

No olvide desplegar la pestaña para que aparezcan los Datafields de esta segunda consulta y poderlos arrastrar al panel central.

•

Observe que campos queryfields sólo tiene la conexión principal (por lo que aquí no hay botón de Ejecutar consulta. Esta se ejecuta directamente al activar la vista). -50-

Vistas •

Si publicamos el formulario (Publicar en la ficha Inicio) podremos establecer previamente en las Propiedades de la Vista cuál es la predeterminada.

•

Una vez publicado el formulario al hacer clic sobre él se abrirá Infopath Filler con el que sólo podremos rellenar los datos en los controles correspondientes. A diferencia de InfoPath Designer la aplicación Filler no diseña plantillas sino que sólo abre formularios.

•

Establecer como predeterminada esta segunda vista. De esta manera al abrirse el formulario será la vista por defecto.

-51-