Curso de Excel 2007

1

TEMA 1: Tablas de Datos (II)

Contenido

TEMA 1: TABLAS DE DATOS (II) ........................................................................................................................ 1

CONTENIDO ..................................................................................................................................................... 1

UTILIZAR FILTROS AVANZADOS. ...................................................................................................................... 2

Cmo utilizar el filtro avanzado ...................................................................................................................... 2

EJERCICIO PASO A PASO. TRABAJAR CON TABLAS. ............................................................................................................ 4

FUNCIONES DE BASE DE DATOS ....................................................................................................................... 5

CREAR UN RESUMEN DE DATOS: SUBTOTALES ................................................................................................ 7

EJERCICIOS ....................................................................................................................................................... 8

EJERCICIO 1: LIBROS. ................................................................................................................................................ 8

CUESTIONARIO ................................................................................................................................................ 8

Curso de Excel 2007

2

Utilizar Filtros avanzados.

Si queremos filtrar los registros de la lista por una condicin ms compleja, utilizaremos el cuadro de

dilogo Filtro avanzado. Previamente deberemos tener en la hoja de clculo, unas filas donde

indicaremos los criterios del filtrado.

Para abrir el cuadro de dilogo Filtro avanzado,

pulsaremos en en la seccin Ordenar y

filtrar.

Rango de la lista: Aqu especificamos los registros de

la lista a los que queremos aplicar el filtro.

Rango de criterios: Aqu seleccionamos la fila donde

se encuentran los criterios de filtrado (la zona de

criterios).

Tambin podemos optar por guardar el resultado del

filtrado en otro lugar, seleccionando la opcin Copiar

a otro lugar, en este caso rellenaremos el campo

Copiar a: con el rango de celdas que recibirn el resultado del filtrado.

Si marcamos la casilla Slo registros nicos, las repeticiones de registros (filas con exactamente los

mismos valores) desaparecern.

Para volver a visualizar todos los registros de la lista, acceder al men Datos - Filtro - Mostrar todo.

Cmo utilizar el filtro avanzado

Para formar las condiciones que vayamos a utilizar en el parmetro Rango de criterios, debemos

reservar una zona en nuestra hoja de clculo para dichas condiciones, zona que llamaremos zona de

criterios.

En esta zona tendremos que tener en una fila los encabezados de los campos de la lista que

intervienen en la condicin, (lo mejor es copiarlos de la lista) y debajo indicaremos las condiciones.

Por ejemplo, si queremos filtrar los registros de nombre Rosa, en la zona de criterios debajo de la

celda Nombre escribimos Rosa, esto equivale a definir la condicin Nombre="Rosa".

Cuando la condicin es una igualdad no es necesario poner el signo = delante del valor, ponemos

directamente el valor Rosa, ahora si quisiramos los registros cuyo cdigo sea superior a 3

Curso de Excel 2007

3

deberamos escribir en la celda inferior a la cabecera CODIGO, >3 para formar la condicin Codigo >

3.

Para combinar varias condiciones se emplean los operadores Y y O.

En un criterio de filtrado, si las condiciones estn escritas en la misma fila, estarn unidas por el

operador Y, para que el registro aparezca se deben cumplir todas las condiciones. Por ejemplo precio

> 100 y precio < 1200, aparecen los registros cuyo precio est comprendido entre 101 y 1199.

En un criterio de filtrado, si las condiciones estn escritas en distintas filas, estarn unidas por el

operador O, el registro aparecer en el resultado del filtrado si cumple al menos una de las

condiciones. Por ejemplo, con la condicin nombre="Rosa" O nombre="Ana", aparecen los registros

cuyo nombre es Rosa o Ana, aparecern todas las Ana y todas las Rosa.

Ejemplos:

Si en la zona de criterios tenemos:

Se filtrarn los registros de nombre Rosa.

Si en la zona de criterios tenemos:

Se filtrarn los registros de nombre Rosa y que adems tengan un cdigo mayor que 3.

Si en la zona de criterios tenemos:

Se filtrarn los registros de nombre Rosa O cdigo mayor que 3, es decir los de nombre Ana y los que

tengan un cdigo mayor que 3 aunque no se llamen Ana.

Con un poco de prctica puedes llegar a formar condiciones realmente complejas.

Curso de Excel 2007

4

Ejercicio paso a paso. Trabajar con tablas. 1. Si no tienes abierto Excel2007, brelo para realizar el ejercicio.

Primero crearemos la lista.

2. Empieza un nuevo libro de trabajo.

3. Confecciona una fila con los campos que vemos en la imagen.

4. Selecciona el rango A1:H1 (las celdas que has rellenado).

5. Selecciona la pestaa Insertar.

6. Pulsa sobre el botn Tabla.

Se abre un cuadro de dilogo Crear Tabla con el rango seleccionado.

7. Marca la casilla La tabla tiene encabezados.

8. Pulsa Aceptar.

Ahora vamos a rellenarla con informacin de tus Amig@s o informacin ficticia.

9. Ha aparecido una fila en blanco. En ella introduce los datos de tu primer amigo.

10. En la ltima celda, pulsa la tecla TAB para ir a una nueva fila donde introducir los datos del

segundo amig@.

Vamos a introducir el resto de datos utilizando un formulario.

11. Selecciona del icono Formulario... de la barra de acceso rpido. Si no lo habas aadido, mira

cmo hacerlo en el apartado de formularios. Aparece un formulario con los campos de la

segunda fila.

12. Pulsa Nuevo. Aparecen los campos en blanco para que los rellenes con los datos de tu tercer

amig@.

13. Rellena los campos con los datos de tu tercer amig@ pasando de un campo a otro con la

tecla de tabulacin.

14. Pulsa Intro. Estas en un nuevo registro.

15. Rellena el Nombre y el 1er apellido.

16. Pulsa Restaurar. Los valores introducidos desaparecen, vuelves a empezar con el registro en

blanco.

17. Repite los pasos 13 y 14 para introducir una lista de amig@s, repite algn apellido en varios

registros.

18. Pulsa Cerrar para terminar de introducir nuevos registros.

Vamos a ordenar la lista por el primer apellido.

Curso de Excel 2007

5

19. Posicinate en la columna del primer apellido. (Columna B)

20. Pulsa sobre . (El primer botn ordena ascendentemente de la A a la Z, el segundo

hace lo contrario, de la Z a la A).

Observa como ahora tus amigos estn por orden alfabtico de apellido.

Vamos a filtrar la lista de contactos por el apellido que hemos repetido varias veces. Utilizaremos

el Filtro.

Si los campos de la cabecera tienen una flecha al lado derecho pasa al paso 23.

21. Sitate en la pestaa Datos.

22. Pulsa el botn Filtro.

23. Despliega la lista del primer apellido y selecciona slo el apellido que ms se repita.

Observa como ahora slo aparecen los amigos con ese apellido.

24. Despliega otra vez la lista del primer apellido y selecciona la opcin (Todas).

Observa como ahora aparecen todos los registros.

25. Por ltimo cierra el libro de trabajo y guarda el libro en la carpeta Mis documentos del disco

duro, con el nombre de Lista_amigos.

Funciones de base de datos

En el tema de funciones omitimos el apartado de funciones dedicadas a bases de datos, pero ahora

vamos a explicar cada una de esas funciones ya que se aplican a tablas de datos.

Estas funciones se utilizan cuando queremos realizar clculos sobre alguna columna pero aadiendo

una condicin de seleccin de las filas que entrarn en el clculo, es decir aplicando previamente un

filtro.

Por ejemplo si tenemos una columna con el beneficio obtenido por nuestros automviles (ver figura

ms abajo) y queremos saber cunto ha sido el beneficio de los Ford, no podemos utilizar la funcin

suma porque sumara todos los automviles, en este caso lo podramos conseguir con la funcin de

base de datos BDSUMA incluye la condicin de filtrado automvil="Ford"

Para explicar las funciones de Base de datos que nos ofrece Excel, utilizaremos la hoja:

Curso de Excel 2007

6

En esta hoja tenemos una lista con los automviles de la empresa, con los datos de plazas, aos,

rentabilidad y beneficio obtenido.

Nota: Las filas 1 a 4 se utilizan para definir los filtros.

Estas son todas las funciones de base de datos ofrecidas por Excel. Todas ellas llevan tres

argumentos: el rango de datos que define la tabla o base de datos, el nombre del campo que se

utiliza en la funcin, un rango de criterios para indicar la condicin que deben cumplir las filas que

sern utilizadas en el clculo de la funcin (el filtro).

Funcin Descripcin

BDCONTAR(datos;campo;criterios) Cuenta las celdas que contienen un nmero

BDCONTARA(datos;campo;criterios) Cuenta las celdas que contienen un valor

BDMAX(datos;campo;criterios) Obtiene el valor mximo

BDMIN(datos;campo;criterios) Obtiene el valor mnimo

BDPRODUCTO(datos;campo;criterios) Obtiene el producto de los valores indicados

BDPROMEDIO(datos;campo;criterios) Obtiene el promedio de los valores indicados

BDSUMA(datos;campo;criterios) Obtiene la suma de los valores indicados

BDEXTRAER(datos;campo;criterios) Obtiene un valor de un campo en una fila que cumpla un criterio de seleccin

BDVAR(datos;campo;criterios) Calcula la varianza sobre una muestra de valores

BDVARP(datos;campo;criterios) Calcula la varianza sobre todos los valores de un campo

BDDESVEST(datos;campo;criterios) Calcula la desviacin estndar sobre una muestra de valores

BDDESVESTP(datos;campo;criterios) Calcula la desviacin estndar sobre todos los valores de un campo

Curso de Excel 2007

7

Crear un resumen de datos: Subtotales

Cuando hablamos de crear un resumen de los datos de una tabla nos estamos refiriendo a crear

subtotales agrupando los registros por alguno de los campos de la lista.

Por ejemplo si tenemos una lista de nios con los campos nombre, direccin, localidad y edad;

podemos obtener un resumen de la edad media de los nios por localidad.

Otro ejemplo, el que te enseamos abajo, disponemos de una lista de vehculos clasificados por

marca y modelo; y queremos averiguar el coste total de cada marca.

Para agregar los subtotales automticamente debemos situarnos sobre una celda cualquiera de la

lista y marcar la opcin Fila de totales en las Opciones de estilo de tabla, en la pestaa Diseo.

Al seleccionar una celda de la fila de totales, aparece una pestaa con una lista de las funciones que

podemos usar para calcular el total de esa columna.

Curso de Excel 2007

8

Se puede mejorar el resumen y los subtotales de la tabla utilizando los esquemas que ya vimos, o las

tablas dinmicas, que veremos en el tema siguiente.

Ejercicios

Si no tienes abierto Excel, brelo para realizar los ejercicios planteados a continuacin.

Ejercicio 1: Libros. 1 Abrir el libro de trabajo Libros de la carpeta de ejercicios del curso.

2 Crear un filtro avanzado para filtrar los libros que cuesten menos de $16.

3 Guardar los cambios realizados y cerrar el libro.

Cuestionario

Solo una respuesta es vlida por pregunta. Haz clic en la respuesta que consideres correcta.

1. Cules de las siguientes funciones son vlidas para trabajar con tablas.

a) BDCONTAR, BDEXTRAER.

b) BDMAX, BDPRODUCTO y BDPROMEDIO.

Curso de Excel 2007

9

c) Ambas son correctas.

d) Ninguna de las anteriores.

2. A qu nos referimos al hablar de resumen en una tabla Excel.

a) Escoger los puntos ms importantes de la tabla.

b) Crear subtotales.

c) Ambas son correctas.

d) Ninguna de las anteriores.

3. Para crear un resumen de la tabla de datos.

a) Acceder al men Herramientas - Resumen.

b) Acceder al men Herramientas - Subtotales.

c) Marcar la opcin Fila de totales.

d) Ninguna de las anteriores.

Respuestas: 1 c), 2 b), 3 c)