Ut2 manual 2016

MICROSOFT OFFICE EXCEL AVANZADO

´

Unidad Temática 2: Trabajo de Datos con Excel M

UT2: Trabajo de datos con Excel 1

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Introducción Aun habiendo herramientas específicas para la gestión y manipulación de bases de datos, Excel nos permite trabajar con rangos de datos de una manera fiable y cómoda, por lo que se puede convertir en una buena herramienta para el trabajo con listas de datos, sin llegar a las pretensiones de una base de datos.

En Listas conoceremos la diferencia en cuanto a funcionamiento entre un rango de datos y una lista de datos creada a partir de un rango. Aprenderemos a ordenar listas y rangos de datos, así como a calcular subtotales en rangos de datos y aplicar filtros automáticos y filtros avanzados.

En Importar datos comprenderemos la existencia de distintas fuentes de datos externas y cómo tratarlas, así como el uso de herramientas para filtrar la importación de datos.

UT2: Trabajo de datos con Excel

2

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Listas.

Podríamos decir rápidamente que una lista es, simplemente, un montón de datos colocados unos debajo de otros.

Esta definición no es del todo exacta, pero nos acerca a la idea de lo que puede ser una lista en Excel.

Una lista de Excel proporciona características diseñadas para facilitar la administración y el análisis de grupos de datos relacionados en una hoja de cálculo de Excel. Si designamos un rango como una lista, podremos administrar y analizar los datos de la lista sin tener en cuenta los datos externos a la lista. Por ejemplo, podemos filtrar columnas, agregar una fila para totales, e incluso crear un informe de tabla dinámica utilizando únicamente los datos de la lista.

Podemos tener varias listas en una hoja de cálculo, lo que nos ofrece una gran flexibilidad para separar los datos en función de nuestras necesidades, en distintos conjuntos manejables.

¿Qué es una lista para Excel?

Para que Excel considere que una serie de filas forman una lista, hay que cumplir al menos cuatro reglas muy sencillas: -

La primera fila de la lista debe contener un título que identifique, de alguna forma, los datos de cada una de sus columnas.

-

En cada columna escribiremos datos relacionados. Lo que nunca hay que hacer es mezclar datos con distinto significado en la misma columna.

-

Tampoco conviene dejar celdas en blanco. Muchas funciones de Excel trabajan con la lista completa y los espacios en blanco pueden desorientar a los algoritmos que se encargan de ordenar, filtrar, contar, etc.

-

Procura que el rectángulo de celdas que delimita tu lista esté flanqueado por, al menos, una fila y una columna a cada lado. Así facilitamos a Excel la labor de reconocer qué datos pertenecen a la estructura.

UT2: Trabajo de datos con Excel

3

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Herramientas para las Listas.

Al crear una lista en Excel, las características de la lista y los elementos visuales diseñados para mejorar estas características permiten identificar y modificar fácilmente el contenido de la lista.

UT2: Trabajo de datos con Excel

4

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

CREACIÓN Y MANTENIMIENTO DE LISTAS Crear una lista.

Para crear una lista nos colocamos dentro del rango de celdas que ocupa la lista y usamos el menú Datos >> Lista >> Crear lista...

Excel identificará el rango que ocupa la lista (buscará hasta llegar a una fila y una columna vacios), y nos lo presentará en el cuadro de diálogo "Crear lista", desde el cual podemos indicar otro rango distinto así como especificar si la primera fila es la de encabezados:

Al pulsar Aceptar en el cuadro, la lista se crea:

UT2: Trabajo de datos con Excel

5

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Mantener la lista.

El mantenimiento de una lista consta de tres operaciones principales: inserción, modificación y borrado.

Al ser una lista un conjunto de filas y columnas de una hoja de cálculo, las operaciones de mantenimiento se pueden llevar a cabo siguiendo los procedimientos comunes de trabajo con Excel que ya conocemos.

No obstante, mantener una lista de esta forma puede resultar engorroso. Por ello, Excel nos proporciona algunas herramientas específicas que facilitan el trabajo con las listas, entre ellas el Formulario:

Lo vemos con un ejemplo ...

Veamos el procedimiento básico a seguir para crear y modificar listas.

UT2: Trabajo de datos con Excel

6

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

7

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

8

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

9

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

10

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

11

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

12

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

13

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

14

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

A tener en cuenta...

Si nos colocamos sobre una lista y usamos el menú Datos >> Lista >> Convertir a rango, podremos volver a convertir la lista en un rango normal.

UT2: Trabajo de datos con Excel

15

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Resuelve el reto: ¿Cómo haríamos para añadir un nuevo campo a nuestra lista?

UT2: Trabajo de datos con Excel

16

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

OPERACIONES BÁSICAS CON LISTAS DE DATOS Las operaciones que vamos a ver en este apartado son la ordenación y el cálculo de totales.

Estas operaciones podemos hacerlas con cualquier listado de datos de Excel que responda a la estructura básica de una lista, pero sin la necesidad de crearla como tal (siguiendo los pasos que vimos en el apartado anterior).

Por tanto, para realizar estas operaciones no es necesario crear una lista, sino que el listado de datos responda a una estructura bien definida.

La Ordenación.

Para ordenar un listado de datos lo único que tenemos que hacer es colocarnos dentro del listado y usar el menú Datos >> Ordenar...

Excel reconocerá el rango que conforma el listado de datos y lo seleccionará haciendo aparecer el cuadro de diálogo "Ordenar" para que indiquemos las opciones deseadas:

Resuelve el reto: ¿Cómo podemos usar los botones de la barra de herramientas Estándar para ordenar rápidamente un rango de datos por un campo determinado?

A tener en cuenta …

Al crear una lista a partir de un rango de datos, disponemos en los encabezados de columna de las "listas desplegables Autofiltro". En estas listas disponemos de opciones para ordenar rápidamente por el campo

UT2: Trabajo de datos con Excel

17

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

en cuestión.

Subtotales.

En aquellos rangos de datos que incluyan campos numéricos, este comando puede resultar muy útil.

La función Subtotales sirve para calcular de forma selectiva cantidades numéricas basadas en datos incluidos en los campos de un listado de datos.

Para calcular subtotales usamos el menú Datos >> Subtotales..., y trabajamos con el cuadro de diálogo "Subtotales":

UT2: Trabajo de datos con Excel

18

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Lo vemos con un ejemplo...

Veamos cómo usar la herramienta que nos permite calcular subtotales.

Puedes descargar el archivo Excel ( p e di do s . xl s ) que se encuentra tras este documento de la UT2 para practicar simultáneamente. Es el que se usa en las imágenes que siguen.

UT2: Trabajo de datos con Excel

19

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

20

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

21

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

22

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

23

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

24

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

25

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

26

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

27

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

28

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Vayamos un poco más allá…

Usa el mismo archivo de Excel usado en el ejemplo anterior e intenta calcular subtotales agrupando por más de un criterio.

Por ejemplo, ¿cómo podríamos obtener los subtotales de cada empleado en cada país en los que ha atendido un pedido?:

A tener en cuenta...

Al crear una lista a partir de un rango de datos, podemos usar el botón "Alternar fila total" de la barra de herramientas "Lista", con el cual podemos añadir una fila de totales al final de la lista y, mediante una lista desplegable, elegir el total a calcular.

UT2: Trabajo de datos con Excel

29

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

FILTRADO Con el filtro de datos estamos haciendo una elección selectiva de los elementos de una lista que estarán visibles en un momento determinado. Es decir, estamos seleccionando y viendo aquellas filas que nos interesan.

Para llevar a cabo estas tareas disponemos de dos herramientas: el Autofiltro y el Filtro Avanzado.

Autofiltro Si nos colocamos dentro del rango de datos y elegimos el menú Datos >> Filtro >> Autofiltro:

Excel reconocerá el rango de datos y en cada encabezado de columna añadirá una lista desplegable de Autofiltro:

Practiquemos con un ejemplo ...

Veamos cómo trabajar con los Autofiltros.

Use el fichero ejemplo empleados.xls (que se encuentra en la web del curso) para practicar este apartado:

UT2: Trabajo de datos con Excel

30

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

31

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

UT2: Trabajo de datos con Excel

Curso Excel Avanzado

32

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Un consulta a resolver: ¿Cómo se desactiva el filtro automático?

Para tener en cuenta ...

Al crear una lista a partir de un rango de datos, disponemos en los encabezados de columna de las "listas desplegables Autofiltro". En estas listas disponemos de opciones de Autofiltro (análogas a las vistas) para los campos de la lista creada.

Resolvamos este otro reto: Con el mismo fichero de ejemplo que se está usando, ¿Cómo podemos usar el Autofiltro para mostrar los datos de los representantes de ventas que son del Reino Unido?

UT2: Trabajo de datos con Excel

33

Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz

Curso Excel Avanzado

Filtro Avanzado.

Esta opción supone la máxima potencia que, de forma estándar, Excel pone en nuestras manos a la hora de filtrar listas de datos.

En términos generales, se trata de especificar los criterios de filtrado en un rango de celdas aparte (el rango de criterios) y, posteriormente, filtrar la lista de datos usando este rango de criterios.

Sigamos con este ejemplo: Veamos cómo trabajar con el filtro avanzado.

UT2: Trabajo de datos con Excel

34

Area de Gesti贸n Sanitaria Norte de C谩diz SAS Consejer铆a de Salud y Bienestar Social

UT2: Trabajo de datos con Excel

Formaci贸n Continuada

35

Area de Gesti贸n Sanitaria Norte de C谩diz SAS Consejer铆a de Salud y Bienestar Social

UT2: Trabajo de datos con Excel

Formaci贸n Continuada

36

Area de Gesti贸n Sanitaria Norte de C谩diz SAS Consejer铆a de Salud y Bienestar Social

UT2: Trabajo de datos con Excel

Formaci贸n Continuada

37

Area de Gesti贸n Sanitaria Norte de C谩diz SAS Consejer铆a de Salud y Bienestar Social

UT2: Trabajo de datos con Excel

Formaci贸n Continuada

38

Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social

Formación Continuada

Tenga en cuenta...

La Ayuda de Excel proporciona abundante información sobre el tema de filtrado de datos.

En la Tabla de contenidos, podemos acceder a Trabajar con datos >> Filtrar y ordenar datos y obtendremos información al respecto.

UT2: Trabajo de datos con Excel

39

Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social

Formación Continuada

Importar Datos.

Excel nos proporciona una serie de herramientas que nos permitirán disponer en nuestras hojas de cálculo de listas de datos procedentes de orígenes de datos externos (archivos de texto, bases de datos, etc.).

Para ello, haremos uso del menú Datos >> Obtener datos externos.

Sigamos el ejemplo: Veamos los procedimientos básicos para la importación de datos externos.

En este caso importaremos datos de una tabla de una base de datos Access.

Usa el archivo Access, situado en esta UT, para poder practicar lo indicado en estos pasos:

UT2: Trabajo de datos con Excel

40

Area de Gesti贸n Sanitaria Norte de C谩diz SAS Consejer铆a de Salud y Bienestar Social

UT2: Trabajo de datos con Excel

Formaci贸n Continuada

41

Area de Gesti贸n Sanitaria Norte de C谩diz SAS Consejer铆a de Salud y Bienestar Social

UT2: Trabajo de datos con Excel

Formaci贸n Continuada

42

Area de Gesti贸n Sanitaria Norte de C谩diz SAS Consejer铆a de Salud y Bienestar Social

UT2: Trabajo de datos con Excel

Formaci贸n Continuada

43

Area de Gesti贸n Sanitaria Norte de C谩diz SAS Consejer铆a de Salud y Bienestar Social

UT2: Trabajo de datos con Excel

Formaci贸n Continuada

44

Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social

Formación Continuada

Pulsamos en Siguiente.

Tenga en cuenta …

La Ayuda de Excel proporciona abundante información sobre el tema de importación de datos.

En la Tabla de contenidos, podemos acceder a Trabajar con datos >> Importar datos y obtendremos información al respecto.

UT2: Trabajo de datos con Excel

45

Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social

Formación Continuada

Algo más …

Los archivos de texto (.txt o .csv) son un buen método para pasar información entre distintas aplicaciones.

Excel nos permite importar datos procedentes de archivos de texto. No obstante, estos archivos de texto deben tener una estructura concreta y bien definida para evitar errores en la importación. ¿Cuáles son las características que deben tener los .txt para que la importación sea correcta? ¿Cómo se realizará la importación? Puedes ampliar información en este enlace de Microsoft Office Online.

UT2: Trabajo de datos con Excel

46