unidad ii: fórmulas y funciones

UNIDAD II: FÓRMULAS Y FUNCIONES

Facultad de IngenieríaUniversidad Autónoma de QuerétaroMa. Teresa García Ramírez

TABLAS DE DATOS

TABLAS DE DATOS Introducción Crear una Tabla Modificar los Datos de una Tabla Modificar la Estructura de una Tabla Estilo de una Tabla Ordenar una Tabla de Datos Filtrar una Tabla Funciones de Bases de Datos Crear un resumen de Datos Criterios de Filtrado

INTRODUCCIÓN Una tabla en Excel es un conjunto de datos

organizados en filas o registros, en la que la primera fila contiene las cabeceras de las columnas (los nombres de los campos), y las demás filas contienen los datos almacenados.

Es como una tabla de base de datos, donde cada fila es un registro de entrada, por tanto se pueden componer como máximo una lista con 255 campos y 65535 registros.

INTRODUCCIÓN Las tablas son muy útiles porque además

de almacenar información, incluyen una serie de operaciones que permiten analizar y administrar esos datos de forma sencilla. Ordenar los registros. Filtrar el contenido de la tabla por algún criterio. Utilizar fórmulas para la lista añadiendo algún

tipo de filtrado. Crear un resumen de los datos. Aplicar formatos a todos los datos.

CREAR TABLAS Para crear una tabla se selecciona el

rango de celdas (con datos o vacías) que se van a incluir en la tabla.

Seleccionar del menúinsertar->tablas,la opción Tabla.

Cuadro de Diálogo Crear Tabla

CREAR TABLA Si no se seleccionaron celdas previamente

se pueden seleccionar en este momento. Si en el rango seleccionado se incluyó la

fila de cabeceras (recomendado), se activa la casilla de verificación “La tabla tiene encabezados”.

Al cerrarse el cuadro de dialogo, aparece la pestaña de Diseño, correspondiente a las Herramientas de tabla.

CREAR TABLA

INTRODUCIR O MODIFICAR DATOS Para modificar o introducir nuevos datos en

la tabla se pueden teclear directamente los nuevos valores sobre las celdas, o bien utilizar un formulario de datos. Esta segunda opción viene muy bien si la lista es muy grande.

Un formulario de datos es un cuadro de dialogo que permite al usuario escribir o mostrar con facilidad una fila entera de datos (un registro).

INTRODUCIR O MODIFICAR DATOS Para abrir el formulario de datos,

se tiene que posicionar en la lista para que este se active, y pulsar en el icono Formulario. .

Si no está la opción activarlo desde Botón Office > Opciones de Excel > Personalizar, y Agregar el icono Formulario..., en la sección de Comandos que no están en la cinta de opciones.

INTRODUCIR O MODIFICAR DATOS

Icono de Formulario

MODIFICAR LA ESTRUCTURA DE LA TABLA Pulsando en el icono Cambiar tamaño de la

tabla, se puede seleccionar un nuevo rango de datos. Pero si la tabla contiene encabezados, estos deben permanecer en la misma posición, así que solo se pueden aumentar y disminuir filas.

Se pueden modificar directamente el rango de filas y columnas, estirando o encogiendo la tabla desde su esquina inferior derecha.

MODIFICAR LA ESTRUCTURA DE LA TABLA Se pueden insertar nuevas columnas y

renglones a la tabla. Se pueden eliminar columnas,

renglones o celdas de la tabla. Para eliminar la estructura de la tabla y

conservar los datos se selecciona de menú Diseño->Convertir en Rango.

ESTILOS DE TABLAS Existe una variedad de estilos para seleccionar. Se puede personalizar el estilo de la tabla,

seleccionando de Opciones de estilo de la tabla, y marcar o desmarcar otros aspectos, como que las columnas o filas aparezcan remarcadas con bandas, o se muestre un resaltado especial en la primera o última columna.

ORDENAR TABLA DE DATOS Se puede ordenar por un solo campo de datos o por

varios. En el caso de tablas solo se selecciona el campo a

ordenar y automáticamente considera todo el registro. Para ordenar por más de un criterio se solicita el

primer criterio de ordenación, posteriormente se selecciona agregar nivel para incluir los demás criterios de ordenación.

ORDENAR TABLA DE DATOS En ordenar el botón de opciones

permite distinguir entre mayúsculas y minúsculas, así como la orientación al momento de ordenar.

FILTRAR UNA TABLA Filtrar una tabla es seleccionar aquellos registros

que se correspondan con algún criterio fijado por el usuario.

Excel ofrece dos formas de filtrar una lista. Utilizando el Filtro (autofiltro). Utilizando filtros avanzados.

Para utilizar el autofiltro se utilizan las listas desplegables asociadas a las cabeceras de campos, se puede mostrar u ocultar el autofiltro en la pestaña Datos marcando o desmarcando el botón Filtro.

TIPOS DE FILTROS PARA TEXTO

TIPOS DE FILTROS En cualquier opción, de filtro de números o de

texto se muestra una ventana donde se pueden elegir dos condiciones de filtro:

Exigir que se cumpla una condición o las dos. Excel evaluará la condición elegida con la tabla, y si se cumple, mostrará la fila.

Para texto se usa el caracter ? para determinar que en esa posición habrá un caracter, sea cual sea, y el asterisco * para indicar que puede haber o no un grupo de caracteres.

TIPOS DE FILTROS Por ejemplo:

En el ejemplo de la imagen, solo se mostraran los registros cuyo Apellido tenga una ‘o’ en el segundo

caracter y no contenga la letra z.

FUNCIONES DE BASES DE DATOS Estas funciones se utilizan cuando se

requiere realizar cálculos sobre alguna columna pero añadiendo una condición de selección de las filas que entraran en el cálculo, es decir aplicando previamente un filtro.

Si se desea sumar solo las calificaciones aprobatorias del primer parcial, se utilizaría la función BDsuma.

FUNCIONES DE BASES DE DATOS Para utilizar las funciones de base de

datos es necesario definir los encabezados de nuestra tabla y crear los filtros, por ejemplo Área de

filtros o restricciones

Encabezados idénticos

Tabla

FUNCIONES DE BASE DE DATOS Todas las funciones de base de datos de

Excel llevan tres argumentos: El rango de datos que define la tabla o

base de datos. El nombre del campo que se utiliza en la

función, Un rango de criterios para indicar la

condición que deben cumplir las filas que serán utilizadas en el calculo de la función (el filtro).

FUNCIONES DE BASE DE DATOS Cómo se forman las condiciones?

Para formar las condiciones que se van a utilizar en el parámetro rango_criterios, se debe reservar una zona en nuestra hoja de calculo para dichas condiciones (preferiblemente antes de la base de datos).

En esta zona se escribe en una fila los nombres de los campos de la hoja de calculo.

Para realizar una operación en toda una columna de la base de datos, se introduce una línea en blanco debajo de los nombres de campo en el rango de criterios.

FUNCIONES DE BASE DE DATOS Rango de criterios, cómo se forman los filtros?

Si se quieren filtrar los registros cuyo nombre empiece con R*, en la zona de criterios debajo de la celda Nombre se escribe R*, esto equivale a definir la condición Nombre="R*".

Cuando la condición es una igualdad no es necesario poner el signo = delante del valor, se pone directamente el valor R*, ahora si se quieren los registros cuya calificación parcial 1 sea superior a 70 se debe escribir en la celda inferior a la cabecera Parcial1, >70 para formar la condición Parcial1 > 70.

FUNCIONES DE BASE DE DATOS Rango de criterios, cómo se forman los

filtros? Para combinar varias condiciones se emplean

los operadores Y y O. En un criterio de filtrado, si las condiciones están

escritas en la misma fila, estarán unidas por el operador Y, para que el registro aparezca se deben cumplir todas las condiciones. Por ejemplo parcial1 > 59 y parcial1 < 100, aparecen los registros cuya calificación esta comprendida entre 60 y 99.

FUNCIONES DE BASE DE DATOS

FUNCIONES DE BASE DE DATOS Rango de criterios, cómo se forman los

filtros? Para combinar varias condiciones se emplean

los operadores Y y O. En un criterio de filtrado, si las condiciones están

escritas en distintas filas, estarán 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 condición nombre="R*" O nombre=“B*", aparecen los registros cuyo nombre empieza con R o B.

FUNCIONES DE BASE DE DATOS

FUNCIONES DE BASE DE DATOS BDCONTARA(rango_datos;nombre_camp

o;rango_criterios) Cuenta las celdas que contienen un

valor (que no sea blanco) en una determinada columna (campo), considerando únicamente las filas que cumplan una determinada condición.

Ejemplo: =BDCONTAR(A6:I29,"Parcial1",A2:I3)

FUNCIONES DE BASE DE DATOS BDMAX(rango_datos;nombre_campo;ra

ngo_criterios) Devuelve el valor máximo encontrado

en una determinada columna (campo), considerando únicamente las filas que cumplan una determinada condición.

Ejemplo: =BDMAX(A6:I29,"Parcial1",A2:I3)

FUNCIONES DE BASE DE DATOS BDPRODUCTO(rango_datos;nombre_cam

po;rango_criterios) Devuelve el producto de los valores

encontrados en una determinada columna (campo), considerando únicamente las filas que cumplan una determinada condición.

Ejemplo: =BDPRODUCTO(A6:I29,"Parcial1",A2:I3)

FUNCIONES DE BASE DE DATOS BDPROMEDIO(rango_datos;nombre_campo;r

ango_criterios) Obtiene el promedio de los valores

encontrados en una determinada columna (campo), considerando únicamente las filas que cumplan una determinada condición

Ejemplo: =BDPROMEDIO(A6:I28,"Parcial1",A2:I3) =BDPROMEDIO(A6:I28,"Parcial1",A2:I4), ¿porqué

el resultado?

FUNCIONES DE BASE DE DATOS BDSUMA(rango_datos;nombre_campo;ra

ngo_criterios) Obtiene la suma de los valores

encontrados en una determinada columna (campo), considerando únicamente las filas que cumplan una determinada condición.

Ejemplo: =BDSUMA(A6:I28,"Parcial1",A2:I3)

FUNCIONES DE BASE DE DATOS BDEXTRAER(rango_datos;nombre_campo

;rango_criterios) Extrae de la base de datos un único valor

que contiene el campo indicado y la fila que cumple la condición establecida mediante el rango de criterios.

Ejemplo: =BDEXTRAER(A6:I28,"Parcial1",E2:E4) Si no encuentra el valor regresa un error.

FUNCIONES DE BASE DE DATOS BDVAR(rango_datos;nombre_campo;rango_criterios

) Calcula la varianza basándose en una muestra de los

valores contenidos en una determinada columna (campo), considerando únicamente las filas que cumplan una determinada condición.

BDDESVEST(rango_datos;nombre_campo;rango_criterios) Calcula la desviación estándar basándose en una

muestra de los valores contenidos en una determinada columna (campo), considerando únicamente las filas que cumplan una determinada condición.

HERRAMIENTAS DE TABLA Excel permite crear totales de manera

automática, solo hay que seleccionar en Herramientas de Tabla->Diseño->opciones de estilo marcar fila de totales y con ello se inserta al final de la tabla una fila donde se tienen las siguientes opciones.

EJERCICIOS Utilizando la función BDCONTAR calcula

la cantidad de ventas realizadas por los vendedores Jorge y María.

Usando BDExtraer, obtener la fecha de nacimiento de Mónica.

Utilizando la función BDDESVEST calcular el desvío estándar de las ventas de los sectores Alimentos y Electrónica.

EJERCICIOS Utilizando la función BDPROMEDIO

calcula el promedio de las ventas de los vendedores Juan y Luis.

Utilizando la función BDSUMA calcula la suma de ventas de los vendedores Juan y Ana

Utilizando la función BDDESVEST calcula la varianza de las ventas de los sectores Electrónica y Alimentos.

EJERCICIOS Utilizando la función BUSCARV obtiene el

concepto de masa corporal para los pesos indicados, inserta la función en la celda de buscar y cópiala hasta el final de la tabla.

Utilizando la función BDSUMA calcula la suma de ventas de los vendedores Juan y Ana

Utilizando la función BDDESVEST calcula la varianza de las ventas de los sectores Electrónica y Alimentos.

TABLAS DINÁMICAS

TABLAS DINÁMICAS Una tabla dinámica consiste en el resumen

de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que facilita la interpretación de dichos datos.

Es dinámica porque permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc...

CREAR TABLA DINÁMICA A partir de una colección de datos se

crea una tabla dinámica, seleccionando las información que se insertará en ésta.

CREAR TABLA DINÁMICA Seleccionar el rango de celdas

considerando los encabezados e indicar que se coloque en una nueva hoja de cálculo.

CREAR TABLAS DINÁMICAS Al seleccionar aceptar aparecen las

herramientas para tablas dinámicas.

CREAR TABLAS DINÁMICAS Al seleccionar todos los valores de la

colección de datos se tiene.

FILTROS EN TABLAS DINÁMICAS Otra característica útil de las tablas

dinámicas es permitir filtrar los resultados y así visualizar únicamente los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante.

Los campos principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una flecha indicando una lista desplegable.

FILTROS EN TABLAS DINÁMICAS Lista desplegable de filtros, con los

distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si se quieren ver o no.

MODIFICAR FÓRMULAS Por defecto, al crear una tabla dinámica,

Excel genera totales con SUMA, puede ser necesario modificar esas fórmulas por otras.

Para hacer esto se debe situar en cualquier celda de la zona que se quiera rectificar y hacer clic con el botón derecho del ratón, aparece un menú emergente con diferentes opciones, se debe escoger la opción Configuración de campo de valor...

MODIFICAR FÓRMULAS En esta ventana de diálogo se elige un

nombre, y la operación a realizar.

GRÁFICOS DINÁMICOS Se selecciona del menú insertar gráfico

dinámico y el manejo es igual que los gráficos usados en otras aplicaciones.