1

1 UNIDAD 3 – MANEJO DE DATOS

1.1 DATOS ORDENAR

Es una herramienta fundamental en el manejo de bases de datos, ya que nos permite clasificar en forma ascendente o descendente por un campo especial la información que se tiene.

- Ordenar una lista por una sola columna Permite ordenar la información de una lista para facilitar la búsqueda de algún dato, para llevar a cabo la ordenación por un campo no se debe seleccionar la columna donde se encuentran el dato, basta con ubicarse en cualquier dato de la columna que contiene en campo.

Ubicarse en una celda perteneciente a la columna por la cual desea llevar a cabo la ordenación.

Clic en la ficha Datos

Ubicarse en el grupo de iconos Ordenar y Filtrar

Clic en los botones: Orden Ascendente u Orden Descendente según el caso. Nota: Si la columna contiene entradas numéricas y de tipo texto, Excel en una ordenación ascendente, sitúa todas las entradas numéricas antes que las de tipo texto. La ordenación descendente es la inversa a la anterior. Las celdas en blanco quedarán siempre al final.

- Ordenar una lista por dos o más columnas (Máximo 64)

Ubicarse en una celda de la base de datos a ordenar.

Clic en la ficha Datos.

Ubicarse en el grupo de iconos Ordenar y Filtrar

Clic en el ícono

Aparece la siguiente caja de diálogo:

En la opción Mis datos tienen encabezados, hacer clic en la casilla de verificación, si la Base de datos tiene títulos para que éstos no se incluyan en la ordenación.

Hacer clic en la lista desplegable de la opción Ordenar por y seleccione el campo principal por el que se desea llevar a cabo la ordenación, en este caso Ciudad.

Indique el Criterio de ordenación deseada, ya sea Ascendente o Descendente.

Haga clic en el botón Agregar nivel, luego

2

Hacer clic en la lista desplegable de la opción Luego por y seleccione el campo que se requiera como segundo criterio de ordenación, en este caso Sexo. Indique el tipo de ordenación.

Repita los pasos anteriores para el tercer criterio de ordenación

Haga clic en Aceptar.

Siguiendo todo el procedimiento anterior la Base de Datos debe quedar ordenada de la siguiente forma:

1.2 APLICA FILTROS

Los filtros permiten visualizar las filas que cumplen unas determinadas condiciones, mientras que oculta las demás. Para poder filtrar una lista, ésta debe poseer títulos en las columnas. Las filas filtradas se pueden copiar en otra hoja de cálculo del libro de trabajo actual, si se desea.

Ejemplo: Digitar esta base de datos.

Y ordenar los datos en forma

ascendente, teniendo en cuenta los

siguientes criterios:

Primer criterio: Ciudad

Segundo criterio: Sexo

Tercer criterio: Estado Civil

3

Para activar los filtros se debe ubicar en un dato de la base de datos, hacer clic en la ficha Datos y clic en el icono Filtro Automáticamente en cada campo de la primera fila de la base de datos aparecen los filtros, así:

Cada filtro contiene las opciones para realizar la búsqueda de los datos que cumplan con alguna condición. Ejemplo: Se requiere filtrar a todos los empleados que viven en Medellín, se debe: - Desplegar el filtro de Ciudad - Desactivar la opción Seleccionar todo - Activar Medellín y aceptar. Automáticamente, Excel mostrará solamente los registros que cumplen con la condición, ocultando los demás.

Al realizar un filtro, Excel, cambia el icono del filtro, para que se identifique la columna por la cual esta filtrada la información.

Para eliminiar el filtro se debe desplegar las opciones de la columna por la cual se filtró y elgir la opción Borrar filtro de …

a) Filtros de texto

Excel permite personalizar los filtros de acuerdo al tipo de datos que se tengan digitados en la base de datos. Para los textos, Excel cuenta con las siguientes opciones:

4

Al seleccionar alguna de las opciones que ofrece Excel, aparece un cuadro de texto para realizar la personalización del filtro. Ejemplo: Se desean filtrar todas las ciudades cuyo nombre comienza por la letra B. Se debe desplegar el filtro de Ciudad, hacer clic en Filtros de Texto y elegir la opción Comienza por…

En el cuadro de diálogo que aparece se debe digitar la letra por la cual se desea realizar el filtro, en este caso B y hacer clic en aceptar. Excel, solo mostrará las ciudades cuyo nombre comiencen por la letra B, ocultando las demás ciudades.

b) Filtros de número Se utiliza para personalizar filtros en columnas que contengan valores numéricos.

Ejemplo: Filtrar todas las personas cuya edad es mayor o igual a 40 años. Desplegar el filtro de la columna Edad, hacer clic en la opción Filtros de número y elegir la opción Mayor o igual que… Automáticamente se activa un cuadro de diálogo, donde se debe digitar el valor por el cual se desea realizar el filtro, en este caso 40. Excel mostrará los registros que cumplan con la condición, ocultando los registros que no lo cumplan.

5

1.3 SUBTOTALES

Los subtotales constituyen una manera rápida y sencilla de resumir datos de una Base de Datos. No es necesario crear fórmulas. Excel crea la fórmula, inserta las filas correspondientes al subtotal y al total. En Excel, para trabajar los subtotales primero se debe ordenar la Base de Datos por el campo que se desee resumir la información. Realiza el siguiente Ejercicio1: Crear un resumen con los subtotales por Empresas en orden descendente, sumando las ganancias de cada empresa debajo de la columna Ganancia, de forma que pueda ver los totales parciales:

La base de datos debe estar ordenada por el campo Empresa en forma descendente (debido a que se requieren los subtotales por este campo), como lo muestra la tabla anterior.

Ubicarse en un dato de la base de Datos

Clic en la ficha Datos

Clic en el icono Subtotal

En la opción Para cada cambio en, se debe seleccionar EMPRESA (el campo por el cual se ordenó la Base de Datos):

En la opción Usar función, se debe seleccionar la función que se requiere, en este caso SUMA

En la opción Agregar subtotal a, se debe activar lo que se pide sumar, en este caso GANANCIA

Clic en Aceptar.

6

Observe que han aparecido los subtotales bajo la columna de Ganancia agrupados por Empresas. A la izquierda, aparecen unos signos que controlan el nivel de desglose del subtotal. Puede aumentar o disminuir el

nivel del subtotal haciendo clic en los signos + y – o, bien, en los números que aparecen sobre estos signos,

como se observa en la siguiente figura: - Ejercicio2: Agregar el Promedio de los Gastos, aparte del total anterior de la Ganancia. Realizar los siguientes pasos:

Ubicarse en la tabla que contiene los subtotales

Clic en Datos

Clic en el icono Subtotales

En la opción Usar función, seleccionar la función Promedio

En la opción Agregar subtotal a, active la casilla GASTOS

Desactive la casilla Reemplazar subtotales actuales (si no la desactiva, se perderán los subtotales anteriores)

Clic en Aceptar Si desea desaparecer los subtotales, debe activar la opción Subtotales del Menú Datos, y hacer clic en el botón Quitar todos. Nota: Para realizar subtotales por otro campo, por ejemplo, por Países, deberá primero ordenar la base de datos por esa columna.

1.4 APLICA FUNCIONES CON BASES DE DATOS Estas funciones, permiten realizar cálculos matemáticos y estadísticos en registros seleccionados de la base de datos. Cada una de las funciones denominadas BD, usan tres argumentos: base_de_datos, nombre_de_campo y criterios. - Base_de datos: Es el rango de celdas que compone toda la base de datos. - Nombre_de_campo: Es el campo que indica qué se va a hallar. Este campo puede ser el título de la

columna encerrado entre comillas dobles, como por ejemplo “Edad” o se puede asignar un número que represente la posición de la columna en la base de datos, 1 para la primera columna, 2 para la segunda columna y así sucesivamente.

7

- Criterios: Son los datos que Indican de quién se va a hallar. Es el rango de celdas que contiene el título de

la columna y el dato, correspondientes al criterio. El criterio debe copiarse en una columna después de la base de datos.

a) Función BdSuma

Suma los números de una columna de una lista o base de datos que concuerden con las condiciones especificadas Sintaxis: =BDSUMA(base_de_datos;nombre_de_campo;criterios)

b) Función BdPromedio Calcula el promedio de los valores de una columna, lista o base de datos que cumplen las condiciones especificadas. Sintaxis: =BDPROMEDIO(base_de_datos;nombre_de_campo;criterios)

c) Función BdMax Devuelve el valor máximo dentro de un campo o columna de una base de datos, los registros que coincidan con los criterios especificados. Sintaxis: =BDMAX(base_de_datos;nombre_de_campo;criterios)

d) Función BdMin

Devuelve el valor mínimo de una columna en una lista o base de datos que concuerde con las condiciones especificadas Sintaxis: =BDMIN(base_de_datos;nombre_de_campo;criterios)

e) Función BdContar Cuenta las celdas que contiene un número en una columna de una lista o base de datos y que concuerdan con los criterios especificados. Sintaxis: =BDCONTAR(base_de_datos;nombre_de_campo;criterios)

f) Función BdContara

Cuenta el número de celdas que no están en blanco dentro de los registros que cumplan con los criterios especificados. Sintaxis: =BDCONTARA(base_de_datos;nombre_de_campo;criterios)

g) Función BdExtraer

Extrae un único valor de la columna de una lista de datos que concuerde con las condiciones especificadas Sintaxis =BDEXTRAER(base_de_datos;nombre_de_campo;criterios)

Observaciones:

Si ningún registro coincide con los criterios, BDEXTRAER, devuelve el valor de error #¡VALOR!

Si más de un registro coincide con los criterios, BDEXTRAER, devuelve el valor de error #¡VALOR!

Ejercicio1: Digitar la siguiente tabla y hallar las Ventas totales de David Bisbal

8

Pasos:

1. Sacar el criterio aparte, haciéndose la pregunta ¿De quién se va a hallar? a) Para sacar el criterio se deben seleccionar los datos de la Base de datos y dar la orden de copiar, en este caso: Siempre el título de la columna (Artista) y el dato correspondiente (David Bisbal) b) Dar la orden de pegar, dejando una columna vacía después de la base de datos.

2. Digitar la función que se requiera, en este caso =Bdsuma 3. Siguiendo la sintaxis de la fórmula, se debe seleccionar toda la base de datos 4. Para el nombre del campo se debe hacer la siguiente pregunta ¿Qué se va a hallar?

Para este nombre de campo se digita el nombre de la columna o se asigna un número, para este caso se piden hallar las “ventas” o se digita el número 3 que es la columna donde están las ventas.

Ejercicio2: Hallar el Artista que sólo vendió en el mes de Abril

Pasos:

1. Para el criterio hacer la pregunta ¿De quién se va a hallar? En este caso el criterio será:

E

17 Mes

18 Abril

2. Digitar la función que se requiera, en este caso =Bdextraer 3. Para el nombre del campo, hacer la pregunta ¿Qué se va a hallar?

En este caso el nombre del campo será: “Artista” o digitar el número 1

4. La fórmula será: =Bdextraer(A1:D13;1;E17:E18)

1.5 VALIDACIÓN DE DATOS

9

Permite personalizar datos de la hoja de cálculo, con el objetivo de que el usuario ingrese sólo los datos que desee. Se pueden validar cualquier valor, números enteros, decimales, crear una lista, fechas, horas, dar la longitud de un texto, entre otras.

Realizar el siguiente ejercicio: Validar las cantidades para que sólo permita ingresar números enteros entre 1 y 50.

1) Seleccione el rango de celdas que desea validar. Desde A2 hasta A7 (A2:A7) 2) Clic en la ficha Datos. 3) Del grupo de Iconos Herramientas de Datos, hacer clic en el

icono Validación de Datos. 4) Clic en la opción Validación de datos… 5) En la ficha Configuración, determine qué criterio usará para validar Ejemplo: Números enteros.

6) Entre: Determine aquí cuál será el rango (mayor, menor, entre

otros)

7) Mínimo y Máximo: escriba números enteros como límites

8) En la ficha Mensaje de entrada: escriba un comentario que

aparecerá cuando los usuarios se ubiquen en las celdas

validadas

9) En la ficha Mensaje de Error: Escriba un mensaje que

aparecerá cuando el dato que se digite no se encuentra entre el

rango que se estableció como límite para la validación.

- VALIDACIÓN A PARTIR DE UNA LISTA Permite utilizar una lista de valores para crear un cuadro desplegable que muestre los datos que se están ingresando. Realizar el siguiente ejercicio: Crear una lista con seis ciudades

1) Seleccionar el rango de celdas donde se creará la lista

2) Clic en la ficha datos

3) Del grupo de Iconos Herramientas de Datos, hacer clic en el

icono Validación de Datos.

4) En la opción permitir del cuadro de diálogo, elegir Lista

5) En la opción Origen, digitar los elementos de la lista, separando

cada uno con coma o punto y coma

6) Clic en Aceptar.

10

NOTA: En cada celda del rango seleccionado, aparecerá una lista desplegable donde sólo se puede elegir una opción, como lo muestra la figura:

1.6 FORMATO CONDICIONAL

Permite cambiar el formato a unas celdas determinadas que cumplan con una condición específica. También se pueden aplicar formatos a unos valores, barras de datos, escalas de color conjuntos de iconos entre otros. - Realizar el siguiente ejercicio, resaltando reglas de celdas:

Aplicar a la ciudad de Cali, relleno verde con texto verde oscuro

a) Seleccione las celdas a las que se desea aplicar el formato.

(A2:A12)

b) Clic en la ficha Inicio – Grupo de Iconos Estilos, clic en el Icono

Formato condicional

c) Al hacer clic en este icono aparece la siguiente lista desplegable, con todas las opciones que se pueden

trabajar. Clic en la opción Es igual a

d) Digitar Cali y seleccionar

e) El formato quedará como lo indica la muestra:

- Para borrar reglas:

Seleccione el área trabajada

Clic en la ficha Inicio – Grupo de Iconos Estilos, clic en el Icono Formato condicional

Ubicarse en la opción Borrar reglas.

Seleccionar Borrar reglas de las celdas seleccionadas o Borrar reglas de toda la hoja. - Para crear nuevas reglas: Al ejercicio anterior agregar color rojo a las Ciudades de Medellin, color azul a Bogota y color amarillo a Pereira.

a) Seleccione las celdas a las que se desea aplicar el formato (A2:A12) b) Clic en la ficha Inicio – Grupo de Iconos Estilos, clic en el Icono Formato condicional c) De la lista desplegable que aparece, hacer clic en la opción Administrar reglas… d) Aparece la siguiente caja de dialogo, en la cual se debe hacer clic en el botón Nueva Regla

11

Al hacer clic en este botón aparece la siguiente caja de dialogo

1.7 BUSCAR OBJETIVO

Esta opción se utiliza para modificar el resultado de una fórmula, modificando el contenido de una celda cualquiera, ya sea sumando o restando el valor que se requiera para conseguir el resultado esperado. - Realizar el siguiente ejercicio: El presupuesto del 2010 para todas las escuelas debe ser $ 500.000 (en este momento está en $ 410.000), para esto se puede modificar el presupuesto de refrigerios de la Escuela1.

Objetivo a alcanzar:

$500.000

Celda que me va a

ayudar a lograr el

objetivo

Hacer clic en esta

opción

Seleccionar estas

opciones

Digitar el texto

Medellin

Clic en este botón y

aplicar los formatos

que se requieren

12

- Pasos para realizar el ejercicio:

1. Ubicarse en la celda que contiene la fórmula que se desea modificar, en este caso E5

2. Clic en la ficha Datos – Grupo de íconos Herramientas de datos – Clic en ícono Análisis Y si – Clic en Buscar objetivo

3. En la siguiente caja de diálogo en las opciones:

a. Definir la celda: E5 b. Con el valor: 500000 c. Para cambiar la celda: $E$2

4. Clic en Aceptar

5. Si existe una solución, debe aparecer el siguiente mensaje:

1.8 AUDITORÍA DE FÓRMULAS Permite rastrear en una celda activa que contenga fórmulas, las celdas que la preceden, las celdas dependientes de ella o rastrear un posible error dentro de una fórmula. Las herramientas de auditoría son las siguientes: Ejercicio: Digitar el siguiente cuadro

Rastrear Precedentes: Sólo se aplica a fórmulas y señala las celdas que intervienen para obtener un resultado.

13

Ejemplo: Se requiere saber que celdas intervienen para obtener el resultado 19490.

- Clic en la celda que contiene la fórmula, D6 - Clic en la ficha Fórmulas. - Clic en el ícono Auditoría de fórmulas - Clic en la opción Rastrear Precedentes.

Rastrear dependientes: No se aplica a fórmulas, se debe ubicar en cualquier dato y señala los resultados que dependen del valor donde se esté ubicado. Ejemplo: Se requiere saber qué resultados dependen del valor de la celda C3

- Clic en una celda C3 - Clic en la ficha Fórmulas. - Clic en el ícono Auditoría de fórmulas - Clic en la opción Rastrear Dependientes.

Rastrear error: Permite detectar o definir la posible celda que está generando un error en la celda activa. Ejemplo: Se requiere saber que celdas intervienen en la celda C8, en la cual aparece un error.

- Clic en una celda que muestra el error, C8 - Clic en la ficha Fórmulas. - Clic en el ícono Auditoría de fórmulas - Clic en la opción Comprobación de Errores - Clic en la opción Rastrear Error.

Quitar todas las flechas: Cuando desee anular las flechas que aparecen, basta con que active este comando.

Evaluar fórmula: Permite hacer un seguimiento paso a paso de una fórmula. - Clic en la celda que contiene la fórmula a evaluar. - Clic en la ficha Fórmulas. - Clic en el ícono Auditoría de fórmulas - Clic en la opción Evaluar fórmula.

Mostrar fórmula: Permite mostrar todas las fórmulas en lugar de resultados. - Clic en la ficha Fórmulas. - Clic en el ícono Auditoría de fórmulas - Clic en la opción Mostrar fórmulas 1.9 REALIZA GRÁFICOS

Los datos numéricos que se encuentran en la hoja de cálculo, pueden visualizarse a través de gráficos que trae incorporados la hoja electrónica Excel. A continuación veremos el diseño de un gráfico de columnas.

a) Elementos de un Gráfico

14

Para crear un gráfico rápido se deben seleccionar los datos a graficar, incluyendo los títulos, y presionar la tecla F11. Automáticamente, Excel crea el grafico en una hoja nueva y activa 3 cintas nuevas: Diseño, Presentación y Formato.

Ficha Diseño En esta ficha encontramos los siguientes iconos:

Permite cambiar el tipo de gráfico.

Permite cambiar los datos del eje. Es decir, los datos que se colocaron en el eje X se cambian para el eje Y, y viceversa.

Permite cambiar el diseño del gráfico.

-

10.000

20.000

30.000

40.000

50.000

60.000

70.000

80.000

90.000

Juan Pedro Luis Jorge

(P

eso

s)

Nombres

APORTES

ENERO

FEBRERO

Series

Valores Eje X

Eje X

Título del Eje X

Valores Eje Z

Leyenda

Área del gráfico

Planos laterales

Título del gráfico

Título del Eje Z

Eje Z

15

Permite modificar el estilo visual (colores) del grafico.

Mueve el grafico a una hoja existente del libro o a una nueva hoja.

Ficha Presentación En esta ficha encontramos los siguientes iconos:

Agrega o elimina los títulos del gráfico

Inserta o elimina los títulos de las etiquetas de cada eje.

Permite eliminar o cambiar de posición la leyenda del gráfico.

Permite agregar o quitar las etiquetas de los datos, con el fin de mostrar el valor de cada etiqueta del eje.

Inserta la tabla de datos al gráfico.

Ficha Formato En esta ficha encontramos los siguientes iconos:

Cambia el estilo visual a las líneas del gráfico.

Agrega un color de relleno sólido, degradado, imagen o textura a la forma seleccionada.

Especifica el color, ancho y estilo de la línea del contorno de la forma seleccionada.

16

Cambia el ancho y alto de la forma seleccionada.

b) Agregar datos a un gráfico

Seleccione los datos de la columna que desea agregar, incluyendo su título.

Presionar las teclas Ctrl + C, para dar la orden de Copiar

Ubicarse en el Gráfico y presionar las teclas Ctrl + V, para dar la orden de pegar

c) Como elaborar un gráfico cuando todos los datos son numéricos

1. Seleccionar los datos que serán el eje Y (Desde B1 hasta D6) 2. Presionar la tecla F11 (cambiar el tipo de gráfico a columnas – Ficha Diseño) 3. Desde la Ficha Diseño, hacer clic en el icono

4. De la caja de diálogo que aparece, clic en el botón Editar

5. De la caja que aparece, ubicarse en el cuadro de texto, pasar a la hoja que contiene la información y seleccionar los datos que serán el Eje X (Desde A2 hasta A6).

17

6. El gráfico quedará así:

0

20

40

60

80

100

120

140

1997 1998 1999 2001 2002

CA

NTI

DA

DES

MODELOS

CONCESIONARIO CLARI

MAZDA

RENAULT

CHEVROLET