Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
MICROSOFT OFFICE EXCEL Nivel Medio
´
Unidad Temática 2: Trabajo de Datos con Excel M
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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.
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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
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
7
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
8
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
9
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
10
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
11
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
12
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
13
Area de Gesti贸n Sanitaria Norte de C谩diz SAS Consejer铆a de Salud y Bienestar Social
Formaci贸n Continuada
A tener en cuenta...
UT2: Trabajo de datos con Excel
14
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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.
Resuelve el reto: ¿Cómo haríamos para añadir un nuevo campo a nuestra lista?
UT2: Trabajo de datos con Excel
15
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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 en cuestión.
UT2: Trabajo de datos con Excel
16
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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
17
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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 . x l 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
18
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
19
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
20
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
21
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
22
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
23
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
24
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
25
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
26
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
27
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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
28
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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
29
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
30
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
31
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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
32
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
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
33
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
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
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
38
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
39
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
40
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
UT2: Trabajo de datos con Excel
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
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
44
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
45