MAAN_Clase_03

Vista previa del material en texto

Estructura de la Clase
1. Motivación
2. ¿Qué son las fórmulas y qué son las funciones?
3. Funciones matemáticas y estadísticas
4. Funciones lógicas
5. Funciones condicionales
Estructura de la Clase
1. Motivación
2. ¿Qué son las fórmulas y qué son las funciones?
3. Funciones matemáticas y estadísticas
4. Funciones lógicas
5. Funciones condicionales
Motivación
En la primera clase dijimos que el objetivo detrás de analizar 
datos debería ser poder extraer conocimiento de los mismos.
Hasta ahora sólo vimos cómo manipular de manera básica datos 
(ingresarlos, moverlos, cortarlos, etc.), de ahora en adelante nos 
enfocaremos en técnicas para extraer conocimiento de los 
mismos.
Esto implica empezar a enfocarnos en operaciones de cómputo 
de información (recuerden que hablamos de tres tipos de 
operaciones asociadas a la información: transmisión, 
almacenamiento y cómputo).
Motivación
Cada tipo de dato que mencionamos (numérico, texto, fecha y 
booleano) tiene asociado un conjunto de operaciones (es 
básicamente lo que define a un tipo de dato, no sólo en Excel sino 
en cualquier herramienta de análisis de datos).
Conocer estas operaciones, saber cómo funcionan y sobre todo 
saber cuándo usarlas es lo que vamos a aprender en las próximas 
clases.
La forma de combinar estas operaciones de cómputo da lugar a 
los conceptos de fórmulas y funciones.
Estructura de la Clase
1. Motivación
2. ¿Qué son las fórmulas y qué son las funciones?
3. Funciones matemáticas y estadísticas
4. Funciones lógicas
5. Funciones condicionales
¿Qué son las fórmulas y qué son las funciones?
Fórmulas:
Las fórmulas en Excel son instrucciones que definen cómo se 
realiza una operación. Básicamente son expresiones que se 
pueden evaluar.
Más allá de las restricciones que las mismas operaciones imponen 
(por ejemplo, si uno está dividiendo, el denominador no puede 
ser cero), prácticamente no hay restricciones sobre qué 
operaciones y valores pueden tener las fórmulas.
Por ejemplo:
= (23+54*6) / 3
¿Qué son las fórmulas y qué son las funciones?
Funciones:
Las funciones son más interesantes.
¿Se acuerdan qué es una función en matemática?
¿Qué son las fórmulas y qué son las funciones?
Funciones:
En matemática decíamos que una función es una relación entre 
un conjunto de inputs y un conjunto de outputs posibles con la 
condición de que cada input (o elemento del dominio) esté 
asociado a un único output (o elemento de la imagen).
¿Qué son las fórmulas y qué son las funciones?
Funciones:
En matemática vimos también que las funciones tienen una 
notación que define parte de su comportamiento.
Para la primera función se lee así “La función llamada f toma 
como input un elemento del conjunto de los naturales y devuelve 
como output un elemento del conjunto de los enteros”.
¿Cómo se lee para g?
 
 
¿Qué son las fórmulas y qué son las funciones?
Funciones:
En computación es prácticamente lo mismo, las funciones 
representan un mapeo único de elementos del dominio a 
elementos de la imagen y a lo que en matemática se le llama 
notación en computación se le llama signatura.
Una signatura puede contener:
○ Nombre de la función.
○ Cuáles son los parámetros, y de que tipo se espera que sean.
○ Cuál es el valor que devuelve (retorno).
Sin embargo, las funciones en computación tienen permitidos 
más comportamientos, por ejemplo imprimir resultados parciales 
(ya vamos a ver esto al final del curso cuando veamos VBA).
¿Qué son las fórmulas y qué son las funciones?
Funciones:
Excel tiene implementado de fábrica cientos de funciones y en 
todas se especifican sus inputs, outputs y su nombre (o sea su 
signatura). Veamos el ejemplo de promedio:
○ El nombre de la función es “average”.
○ Tiene como output la media aritmética.
○ Los inputs pueden ser números, vectores de números, rangos de datos 
o referencias a celdas que contengan números.
¿Qué son las fórmulas y qué son las funciones?
Funciones: ¿Dónde más podemos consultar esta información?
¿Qué son las fórmulas y qué son las funciones?
¿Cómo se relacionan las fórmulas y las funciones?
Las fórmulas pueden estar compuestas por funciones.
Es en este punto en donde Excel adquiere valor por sobre una 
calculadora.
Excel trae de fábrica más de 400 funciones, las cuales están 
asociadas a distintas necesidades de uso. Por ejemplo tiene 
funciones matemáticas, estadísticas, financieras, lógicas, etc.
Usarlas es muy simple, pero siendo tantas uno puede perderse en 
qué hacen y cuándo usarlas.
Cómo construir fórmulas básicas
Para hacer:
Bajen el libro “barManolo.xlsx” que se encuentra en el campus 
virtual (el mismo tiene datos de más de dos décadas de ventas 
de distintos productos del “Bar de Manolo”):
Calculen para cada día la cantidad de platos que el restaurant 
vendió, estos valores deberían ser los que completen la columna 
“Cant. Platos Vendidos”.
Tiempo para la tarea 3 minutos
Cómo construir fórmulas básicas
Las referencias pueden apuntar a otros libros y hojas.
La forma más simple de hacerlo es, cuando uno está editando 
una fórmula, hacer clic sobre la celda o rango al que se quiere 
apuntar (moviéndonos como lo hacemos usualmente).
Otra opción (por el momento menos práctica) es escribir la 
dirección de la celda o rango de celdas que se quiere referenciar 
manualmente en la fórmula.
Cómo construir fórmulas básicas
Ejemplos:
Si uno está en la hoja 1 (Sheet1) y quiere hacer referencia a la 
celda A1 de la hoja 2 (Sheet2), la fórmula tendrá el siguiente 
aspecto:
= Sheet2!A1
Si uno está en el libro 1 (Book1) y quiere hacer referencia a la 
celda A1 del libro 2 (Book2, el cual debe estar abierto) que se 
encuentra en la hoja 1 (Sheet1), la fórmula tendrá el siguiente 
aspecto:
= [Book2]Sheet1!$A$1
Cómo construir fórmulas básicas
¿Cómo trabaja Excel las referencias a otros libros?
Si el libro al que se hace referencia está abierto, Excel incorporará 
inmediatamente cualquier cambio que se haga en este.
Si uno abre un libro que hace referencia a otro y ese otro está 
cerrado, Excel preguntará si uno quiere actualizar los valores de 
aquellas celdas que hacen referencia a otro libro o dejar los 
último valores observados, si ese libro ya no existe más, dejará el 
último valor que observó como el actual.
¿Antes vimos que las referencias a celdas de otros libros tenían el 
signo “$”, para qué sirve este signo?
Cómo construir fórmulas básicas
Referencias absolutas y referencias parciales:
Una referencia es absoluta cuando no apunta a una celda en base 
a la posición relativa que tiene ésta con respecto a la que se 
apunta, sino que apunta en base a su posición absoluta en la 
matriz de datos (por ejemplo: A1).
Por ejemplo:
= $A$1
Cómo construir fórmulas básicas
Referencias absolutas y referencias parciales:
La referencia siempre va a apuntar a la celda A1, aún cuando uno 
desplace, copie o autocomplete usando la celda que contiene 
dicha fórmula (¿Qué pasa si uno inserta columnas o filas?).
Un shortcut para transformar a una referencia en absoluta es 
apretar "F4" cuando uno ha ingresando una referencia como 
relativa (¿Qué pasa si aprieto F4 más de una vez?).
Cómo construir fórmulas básicas
Referencias absolutas y referencias parciales:
¿Por qué dos signos "$"?
Porque si uno pone sólo uno, la referencia pasa a ser parcial.
Una referencia parcial al ser desplazada tomará como absoluto el 
componente antecedido con $ y como relativo el que no lo está.
Por ejemplo:
= $A1
= A$1
¿Cómo se comporta cada una de esta fórmulas cuando se 
desplaza la celda que la contiene?
Cómo construir fórmulas básicas
Para hacer:
En base al archivo que hemos venido usando, calculen el ingreso 
que tuvo el restaurant por día. Noten que lo precios de cada plato 
se encuentran en la hoja de cálculo “Precios”.
Tiempo para la tarea 3 minutos
Estructura de la Clase
1. Motivación
2. ¿Qué son las fórmulas y qué son las funciones?
3. Funciones matemáticas y estadísticas
4. Funciones lógicas5. Funciones condicionales
Funciones matemáticas y estadísticas
Vamos a ver el caso de funciones matemáticas y estadísticas 
simples como manera de aprender a trabajar con funciones.
Para usar una función dentro de una fórmula hay que introducir 
su nombre en la fórmula y asignarle valores a los parámetros que 
requiera (lo cuales van dentro de paréntesis).
Funciones matemáticas y estadísticas
Cuando uno comienza a escribir el nombre de una función en 
Excel da indicios de qué funciones tienen como comienzo de 
nombre lo que uno lleva escribiendo.
Uno puede elegir la función deseada con las teclas direccionales y 
apretando "tab" o haciendo doble clic sobre ella.
También se puede escribir el nombre completo y abrir paréntesis 
para ingresar los valores de input.
Funciones matemáticas y estadísticas
Cuando el nombre de la función ya está escrito y el paréntesis 
está abierto (toda función tiene que tener siempre un juego de 
paréntesis detrás de su nombre en Excel), el mismo programa 
indica qué tipo de valores acepta como parámetros (input).
En el caso de "sum" toma números que pueden estar en rangos, a 
su vez estos se puede escribir directamente o pueden pasarse por 
referencia. Por ejemplo:
= sum(1, 2, 3, 4, 5)
= sum(A1, A2, A3, A4, A5)
= sum(A1:A10)
Funciones matemáticas y estadísticas
Estos son ejemplos de funciones simples que trabajan con celdas 
pero no con rangos.
Para redondear números (no el formato, sino el número interno):
= ROUND(number, numdigits)
= ROUNDUP(number, numdigits)
= ROUNDDOWN(number, numdigits)
Otras opciones para potencias y raíces:
= POWER(number, power)
= SQRT(number)
Otras operaciones:
= LOG(number, base)
= EXP(number)
= FACT(number)
Funciones matemáticas y estadísticas
Estos son ejemplos de funciones que trabajan con rangos o celdas 
únicas.
= SUM(number1, [number2], …)
= PRODUCT(number1, [number2], …)
= AVERAGE(number1, [number2], …)
= STDEV.P(number1, [number2], …
= MEDIAN(number1, [number2], …)
= MIN(number1, [number2], …)
= MAX(number1, [number2], …)
= COUNT(number1, [number2], …)
= COUNTA(number1, [number2], …)
= COUNTBLANK(number1, [number2], …)
Funciones matemáticas y estadísticas
Una muy útil es SUMPRODUCT, que hace el producto interno de 
dos o más arrays.
= SUMPRODUCT(array1, [array2], …)
¿Qué era el producto interno?
Dados dos vectores x = (x
1
,x
2
,x
3
,...,x
n
) e y = (y
1
,y
2
,y
3
,...,y
n
), el 
producto interno entre x e y es
x
1
y
1 
+ x
2
y
2 
+ x
3
y
3 
+ .... + x
n
y
n
Cuando trabajamos en Excel con SUMPRODUCT
○ Quiénes van a ser los parámetros? Dos vectores, definidos por un 
rango de celdas, del mismo tamaño y en la misma orientación.
○ Qué nos va a devolver? Un número.
Funciones matemáticas y estadísticas
Para hacer:
a) Si para obtener el total de platos vendidos y los ingresos no 
usó funciones, rehaga los cálculos usando “SUM” y 
“SUMPRODUCT”.
b) Utilizando fórmulas, responda estas preguntas:
¿En la historia del bar, cuántas unidades se vendieron de cada plato?
¿El día que más se vendieron boquerones, cuántos se vendieron?
¿Cuántos días no hubo ventas de guiso de lentejas?
¿Cuántas porciones de chipirones fritos se venden en promedio por día?
Tiempo para la tarea 5 minutos
Estructura de la Clase
1. Motivación
2. ¿Qué son las fórmulas y qué son las funciones?
3. Funciones matemáticas y estadísticas
4. Funciones lógicas
5. Funciones condicionales
Funciones lógicas
En computación en general y en Excel en particular es 
sumamente útil poder trabajar con expresiones lógicas, que 
permiten operar con predicados de verdad (TRUE) o falsedad 
(FALSE).
Excel tiene operadores lógicos, estos son:
Funciones lógicas
A su vez, cuando uno quiere combinar diferentes predicados de 
verdad, puede hacer uso de las funciones “AND”, “OR” y “NOT”, 
que tienen el siguiente comportamiento:
Funciones lógicas
Ejercicio:
1. Dar una fórmula que devuelva TRUE si un número es mayor o 
igual a 10, y FALSE en caso contrario.
2. Dar una fórmula que devuelva TRUE si un número es mayor o 
igual a 10 y menor o igual a 20, y FALSE en caso contrario.
3. Pensar una forma alternativa de expresar la condición pedida 
en 2.
4. Escribir ambos puntos en una hoja de Excel, que tomen el 
valor ingresado en A1 y devuelvan el valor booleano 
correspondiente.
Funciones lógicas
Para hacer:
Completen la columna “todo menos chipirones” con los valores 
lógicos TRUE o FALSE, siendo el valor TRUE sólo si se vendió en el 
día guiso de lenteja y boquerones pero no chipirones (y FALSE en 
caso contrario).
Tiempo para la tarea 4 minutos
Estructura de la Clase
1. Motivación
2. ¿Qué son las fórmulas y qué son las funciones?
3. Funciones matemáticas y estadísticas
4. Funciones lógicas
5. Funciones condicionales
Funciones condicionales
Las funciones condicionales tienen la particularidad de combinar 
un predicado lógico y una operación.
Hay dos grandes familias de funciones condicionales, que se 
definen en base a cómo el predicado lógico afecta al 
funcionamiento de la función.
A grandes rasgos el predicado lógico afecta el comportamiento de 
la función de una de las siguientes maneras:
a) Puede definir qué operaciones hará la función en base a si se 
cumple o no una condición.
b) Puede restringir el input de la función a observaciones que 
cumplan con una determinada condición.
Funciones condicionales
Funciones que definen un comportamiento diferente de la 
fórmula en base a si se cumple o no una condición.
La función que se destaca de este tipo es la función “if”, que toma 
una acción u otra en base a si se cumple o no una condición 
definida por el usuario.
= IF(logical_test, [value_if_true], [value_if_false])
En este caso la condición lógica no debe corresponderse con un 
texto.
A su vez, “value_if_true” y “value_if_false”, pueden ser fórmulas 
o funciones (incluso otras funciones if, cuando esto ocurre se 
llama ifs anidados).
Esta función es muy importante.
Funciones condicionales
Ejercicio:
Tenemos datos de alumnos de una escuela, el turno en el que se 
encuentran anotados, su edad y la nota promedio.
Se pide completar la columna D de la siguiente manera:
○ Si va de mañana, y tiene más de 13 años, el texto “Mna. y más de 13”
○ Si no, el texto “No Mna. y más de 13”
Funciones condicionales
¿Qué problema queremos resolver?
¿Qué funciones necesitamos utilizar?
¿Qué parámetros toma cada función y cómo se relacionan estas 
funciones entre sí?
¿Cuál es el rol de cada función en la fórmula final?
Funciones condicionales
Funciones que restringen el input de otra función a observaciones 
que cumplan con una única condición:
= COUNTIF(range, criteria)
= SUMIF(range, criteria, [sum_range])
= AVERAGEIF(range, criteria, [average_range])
También existen versiones de estas funciones que verifican si se 
cumple simultáneamente más de una condición:
= COUNTIFS(criteria_range1, criteria1, …)
= SUMIFS(sum_range, criteria_ range1, criteria1, …)
= AVERAGEIFS(average_range, criteria_ range1, criteria1, …)
El criterio lógico debe introducirse como texto (entre doble comillas, 
por ej: “>20”). Si no se pone un operador al frente, Excel asume que 
se está considerando igualdad (“20” y “=20” hacen lo mismo). Si se 
pone referencia a una celda también se considera igualdad.
Funciones condicionales
Ejercicio: Tomando nuevamente los mismos datos
se quiere calcular:
○ el promedio de nota de los alumnos que cursan de mañana 
(6.5)
○ el promedio de nota de los alumnos de menos de 15 años de 
la tarde (5.5)
Funciones condicionales
Ejercicio:
○ Calculen la cantidad total de platos que se vendieron los fines 
de semana.
○ Calculen cuántos días que caen en algún fin de semana no se 
vendieron platos.
○ Calculen el ingreso promedio los días de semana y los fines de 
semana, ¿cuál es mayor?.
○ En la columna “Más de 30 (Sí/No)”, armen una variable que 
indique si en el día correspondiente se vendieron más de 30 
platos.
Tiempo para la tarea 5 minutos
Mensajes deerror y fórmulas incorrectas
Cuando uno ingresa funciones en Excel puede que el programa no 
pueda efectuar bien los cálculos, en cuyo caso dará como 
resultado un error y dirá de qué tipo de error se trata 
comenzando con “#”.
Funciones y fórmulas
Una opción para encontrar la función que uno desea es utilizar la 
opción “insert function” que se encuentra en la barra de 
fórmulas. Allí se dividen a las funciones en familias, se ofrecen 
explicaciones de la misma y una vez elegida se abre un cuadro de 
diálogo que ayuda para ingresarla (también se puede buscar en 
Google).
Funciones y fórmulas
También en ribbon hay un pestañas de fórmulas.
Allí se encuentran se encuentra otro ingreso a “insert function”, 
opciones para ingresar fórmulas y opciones para auditar fórmulas 
(más adelante veremos las opciones para auditar fórmulas).
Uno puede pedirle aquí a Excel que recalcule toda la hoja de 
cálculo (un shortcut para esto es apretar “F9”).
Búsqueda de errores y evaluación de fórmulas
Una herramienta sumamente útil para ver cómo Excel está 
evaluando las fórmulas es “Evaluate Formula” que se encuentra 
en la pestaña “Formulas” de ribbon en el grupo “Formula 
Auditing”.
La herramienta muestra paso a paso cómo Excel va trabajando las 
operaciones que componen la fórmula (es muy útil!... Sobre todo 
cuando los fórmulas se "rompen").
Búsqueda de errores y evaluación de fórmulas
Para hacer:
Asignen como celda activa alguna de las celdas en donde 
ingresaron las fórmulas anteriores y usen “Evaluate Formula”, 
vean qué pasa.
Tiempo para la tarea 1 minutos