Excel - us

Excel

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

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

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

• Excel 2013 ha supuesto en este sentido una auténtica revolución a esta cómodasituación con la incorporación de varios complementos muy potentes: PowerPivot para tablas dinámicas y consultas elaboradas (lenguaje DAX) y PowerView junto a características muy novedosas como el análisis rápido, las escalasde tiempo y el relleno automático.

– Descargar Componente redistribuible del motor de base de datosde Microsoft Access 2010 (http://www.microsoft.com/es-es/download/details.aspx?id=13255)

-2-

http://www.microsoft.com/es-es/download/details.aspx?id=13255

Tablas Dinámicas en Excel

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

• Las tablas dinámicas nos permiten ver la misma información de muchasformas diferentes, transformando tablas muy extensas en una representaciónde 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íapreguntarnos: de entre los alumnos presentados, ¿cuál es el porcentaje desuspensos, aprobados, notables, sobresalientes y matrículas de honor?.

• Esta técnica consiste en agrupar por el campo nota (literal) y contar el númerode registros con el mismo valor representando el resultado con % del total depresentados.

-3-

Tablas Dinámicas en Excel

• Las tablas dinámicas se encuentran en dos sitios: entre los botones del grupoTablas de la ficha Insertar y en la ficha Inicio de la ventana de Power Pivot (clicsobre 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 enforma de gráfico:

-4-

Tablas Dinámicas en Power Pivot

• Las tablas dinámicas de Power Pivot presentan una serie de característicasavanzadas con respecto a las que generaríamos con la ficha Insertar. Por lo tanto apartir 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 sobreextensas hojas con muchos datos.

• También podemos afirmar que Power Pivot permite administrar y modificar elmodelo de datos.

• Power Pivot presenta tres características diferenciadoras en el tratamiento de tablasdinámicas:

– La posibilidad de que una tabla dinámica se base en un conjunto de tablasmúltiples (a diferencia de una sola tabla). Dichas tablas hay queinterrelacionarlas (como las de las bases de datos).

– La mayor capacidad para contener grandes volúmenes de datos (filas) dentro deuna 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 PowerPivot -> Administrar:

• Con Power Pivot tendremos un libro Excel para ver las consultas y una ventanadonde importamos y realizamos diferentes análisis de datos. Esto significa quecontaremos con todos los elementos propios de Excel, más otros nuevos queañade el complemento.

-6-

Relaciones entre tablas

• Una novedad muy importante es que existe la posibilidad de interrelacionar lastablas 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 yCLIENTES de CLIENTES.XLSX (desde la ventana de Power Pivot).

• No hay nada mejor para consumir poca memoria que las columnasinexistentes

• Si queremos crear un modelo eficiente, miraremos antes de importar cadacolumna y nos preguntaremos si contribuye positivamente al análisis quedeseamos realizar. Posteriormente, podremos agregar columnas nuevas si lasnecesitáramos.

-7-


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

-8-


• La tabla EMPLEADOS contiene datos referentes a unos empleados de unaorganización y CLIENTES las compras realizadas por clientes de unos grandesalmacenes.

• La cuestión es que desde la ventana de Power Pivot podemos establecer unainterrelación entre las dos tablas (en principio totalmente independientes)utilizando el campo (común aunque se llamen de distinta manera) dni (enEMPLEADOS) 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 essu fecha de nacimiento? ¿Cuál es su sueldo por hora?

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

-9-


• Si, desde la ficha Diseñar (de la ventana de Power Pivot) hacemos clic sobre elbotó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 CrearLógicamente el campo común que estable dicha relación será el dni deEMPLEADOS relacionado al cliente de CLIENTES:

-10-


• Ahora podemos escribir en una celda cualquiera de las columnas conencabezado Agregar columna de la tabla CLIENTES (no en la de EMPLEADOSya 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 tablainterrelacionada (para cada cliente o dni, su nombre).

-11-


=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 consultascon totales o tablas dinámicas.

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

-12-


• Para ello deberemos agrupar porempleado y sumar las horas totales.Posteriormente añadiremos lamedida que devuelve el producto deltotal de horas por sueldo_hora.

• Para ello nos falta una tabla queprecisamente tenemos en la hojaTRABAJAN_EN del libro con el queestamos trabajando.

• De nuevo esto nos demuestra laversatilidad y potencia de PowerPivot. Nos bastará con colocarnoscualquier celda de la tabla (la A1 porejemplo) y hacer clic en el botónAgregar a modelo de datos de la fichaPower Pivot (encenderemos Mi tabla

tiene encabezados):

-13-


• Ahora procedemos a calcular elnúmero total de horas que hatrabajado cada empleado.

• Esto se obtiene por una tabladinámica donde agrupamos porempleado y sumamos horas.

• Haremos clic en Tabla dinámica (enla ficha Inicio de la ventana de PowerPivot y escogemos la primera opción.Luego veremos las otras opciones).En el panel derecho tomando Tabla1(que es TRABAJAN_EN) empujamosdni_e a FILAS y horas a sumatorioVALORES:

-14-


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

-15-


• Para calcular el sueldo total del empleado deberemos multiplicar el total dehoras por el sueldo hora.

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

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

-16-


• Y escribimos la fórmula: • Con lo que tendremos:

-17-


• Esto también podríamos haberlo hecho con la función DAX: relatedTable. Adiferencia de la función related, devuelve una tabla (por la que debe ser utilizadacomo 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 enel lado muchos (de una relación uno a muchos), sino que puede estar en cualquierlado 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 lacolumna calculada de la primera fórmula).Ejemplo de la potencia del lenguaje DAX.

-18-


• Ahora podríamos llevar el sueldo del cliente (empleado) a la tabla CLIENTES ycontestar a la pregunta : ¿cuánto ganó el cliente? (muy interesante por temasde 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ántashoras ha trabajado el empleado en cada proyecto:

-20-


• Así, el empleado Corvina López,María de 44 horas 12 las empleó en laautomatización de descarga y 32 en elEstudio del cambio climático en laAntártida.

• Un ejemplo más interesante lotendríamos en el caso de que hubiesevarios registros (filas en la tablas) enlos que se repitiese el campo columna(proyecto) en este ejemplo.

• De esta manera tendríamos dosagrupamientos. De hecho en esteejemplo también podríamos arrastrarel proyecto después de nombre en elcampo FILAS y obtener el mismoresultado más compacto:

-21-


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

• La siguiente imagen muestra datos de la tabla indicada:

-22-


• 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 dosocasiones. 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 determinadafecha 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 lassegmentaciones.

• En efecto, quitamos la fecha del filtro y hacemos clic en crear segmentacionesde la ficha Analizar. Después bastará con que seleccionemos las fechasapuntadas (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 enParque María Luisa y Avda. Luis Montoto (observamos que los resultados seadaptan 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 datoshacemos 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ástipos que en Excel. En concreto podremos crear:

Tabla dinámica única: una tabla dinámica en blanco en la hojaactual 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 perocoloca el gráfico por encima de la tabla.

Dos gráficos horizontales: igual que la tabla y el gráfico enhorizontal.

Dos gráficos verticales: igual que la tabla y el gráfico en vertical.

Cuatro gráficos (puestos formando un cuadrado. De forma similar alos Cuadros de mando que veremos en el Tema VII).

-28-

Gráficos dinámicos con Power Pivot

• En ejemplos y ejercicios vistosanteriormente podemos plantearnosmostrar varios gráficos obtenidos apartir de tablas dinámicas, con el finde comparar resultados.

• Esta es una cuestión que se haresuelto en Power Pivot incorporandoal botón PivotTable de la ventanaprincipal otras opciones antes degenerar la tabla dinámica.

• Por ejemplo en la consulta última dela tabla replantes podemos escogerentre las opciones de PivotTable la degrá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 datosconocidos por diversos medios (prensa, libros, etc.); o que sencillamente hemosgenerado a partir de hechos o problemas conocidos por nosotros.

• Ya hemos visto el ejemplo de las tablas EMPLEADOS y TRABAJAN_EN quecontienen datos referentes a las horas trabajadas por diferentes empleados deuna 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 serde otra forma, nos facilita la importación/exportación de datos, de manera quepodremos disponer de ellos como si de datos propios se tratase (de hecho todoslos 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 muydiversas 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 dedatos.

• En general son datos provenientes de consultas que se han almacenado enarchivos de texto, separados por delimitadores como tabuladores, espacios,comas o puntos y comas (a este tipo de archivos se les llama CSV o Comma-Separated Values y son un tipo muy importante de archivos en muchasaplicaciones). El separador suele ser una coma, un ; (muchas veces espreferible 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 lacorrespondiente casilla de verificación), y que los distintos campos estánseparados por el mismo delimitador .

• Lo primero que haremos con un listado es ‘prepararlo’ en el sentido de añadirleuna cabecera (si no la tiene) y establecer el delimitador (en general será lacoma).

-34-


• 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 lahora de importarlo decir que el delimitador es un punto y coma): subaclientes.csv

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

-35-


• 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 librosExcel, podría intentar vincular las tablas mediante campos en común y realizarcá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 Pivotcuando 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 representatransacciones de ventas (sales) contiene casi tres millones y medio de registros!

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

-37-


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

-38-


• Suponga ahora que le solicitan que calcule la media del monto de ventas poraño.

• Tendremos que colocar en las filas el año (quitando el país) y añadir una nuevamedida (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.


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

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

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

-41-


• En InfoPath para poder disponer de formularios que lean de una base de datosAccess, es necesario realizar una conexión a la misma (pueden realizarse variasconexiones 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 quelee de bases de datos debe ser de plena confianza:

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

-42-


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

-43-


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

-44-


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

• La consulta es “horas totales para cada empleado” (hay que hacerla antes comoparte 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, conAccess no se permite modificar los datos a partir de un formulario de InfoPath.

-46-


• Haga los cambios pertinentes para obtener:

-47-


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

• Ya podremos realizar consultas haciendo clic en Vista previa al final de la fichaInicio.

• 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 Ejecutarconsulta.

-48-

Vistas

• Si ahora quisiéramos ver los proyectos en que participa un empleado notenemos 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” (sepuede 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 laconexión nueva):

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

• Observe que campos queryfields sólo tiene la conexión principal (por lo que aquí nohay 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 establecerpreviamente en las Propiedades de la Vista cuál es la predeterminada.

• Una vez publicado el formulario al hacer clic sobre él se abrirá Infopath Fillercon 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 sinoque sólo abre formularios.

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

-51-

Excel - us

Documents

Transcript of Excel - us