Ya conocemos una de las funciones especiales: la sumatoria (SUMA). Pero Excel tiene muchas más. Funciones para hacer cálculos tan inverosímiles como los logaritmos, las funciones trigonométricas o las fórmulas estadísticas. Aquí nos ocuparemos de todo eso.
De todos modos, veamos algunas de las funciones más importantes.
ââ La condicional
No es el nombre de una chacarera, sino de una de las funciones especiales. Veámosla trabajar en un ejemplo.
Un colegio ha organizado un festival de teatro. La entrada vale $ 3, pero sólo para los mayores de 12 años. Los chicos de hasta 12 no pagan. Hay una lista provisoria de asistentes, con sus edades.
La gente del colegio quiere ir teniendo una idea de la recaudación. Es fácil: basta agregar una columna a la tabla anterior poniendo $ 3 para los mayores de 12 años y 0, para los demás. El trabajo de revisar la lista e ingresar un 3 o un 0 (según el caso) puede hacerse automáticamente con la función condicional.
La forma en que trabaja la función es simple. Resolvamos el ejercicio:
ΠCopiamos los datos de la planilla anterior.
• En la celda C2, para obtener el valor de la entrada para el primer asistente, tenemos que escribir lo siguiente:
=SI(B2>12;3;0)
Ž Para completar la planilla, tenemos que extender la función escrita en C2, hasta C7. Para ello podemos usar el puntero de estirar.
• Finalmente, en C8 escribimos una sumatoria para calcular el total.
Lo que la función expresa es que, si la edad (B2) es mayor que 12, entonces el valor de la entrada es 3. Si no, es cero. ¿Se entiende?
ÙÙ Comentarios
Esta función resuelve situaciones de "o este o el otro". Siempre hay tres datos a tener en cuenta:
* Los dos valores que pueden tener la función (en este caso 3 y 0). * La condición que decide cuál es el valor a considerar.
Estos tres valores son los que van dentro de los paréntesis de la función y en el orden en que los pusimos antes:
Œ La condición.
• El valor que debe tomar la función cuando la condición se cumple.
Ž El valor que debe tomar la función cuando la condición no se cumple.
En Excel, cuando una función tiene más de un valor entre los paréntesis, hay que separarlos de alguna manera. El separador que usamos nosotros fue el punto y coma. Pero podrían utilizarse otros separadores, dependiendo de la configuración de Excel y/o de Windows.
ÙÙ Un caso mas complejo
Los dos "valores" de la función condicional no tienen por qué ser fijos. Pueden ser fórmulas y, por lo tanto, variables. Como en el siguiente caso:
La planilla muestra la actividad del equipo de ventas de “Grandes Tiendas La Condicional”. Cada vendedor recibe una comisión del 5% sobre el volumen de ventas. Pero si este volumen supera los $ 5.000, la comisión se eleva al 8%. Estamos, de nuevo, ante un caso a resolver por la función condicional: la comisión puede tomar un valor u otro según la condición de haber vendido más de $ 5.000.
Para escribir la función, los tres datos a considerar son: * Volumen de ventas mayor a $ 5.000 (condición).
* Comisión igual al 8% de las ventas (valor a considerar si la n se cumple).
* Comisión igual al 5% de las ventas (valor a considerar si la condición no se cumple).
Esta función se llama condicional, justamente, porque el valor que toma , la función, depende de una condición
Si una celda muestra
#¿VALOR? es porque Excel está tratando de hacer una operación matemática con celdas cuyo contenido es texto
De nuevo, estos tres datos, en ese orden, y escritos como fórmulas de Excel, son los que figurarán entre los paréntesis de la función condicional. Refiri
planilla de ejemplo, el importe de la comisión se calcula con la fórmula:
=Sl(B2>5000;B2*8%;*B2*5%)
Siendo:
* B2>5000, la condición.
* B2*8%, la comisión que recibe el vendedor si la condición se cumple.
* B2*5%, la comisión que recibe el vendedor si la condición no se cumple. Entonces:
ΠCopiamos la planilla tal como se ve en el ejemplo.
• Escribimos en C2 la función condicional que explicamos antes.
Ž Extendemos esta fórmula al resto de la columna.
La planilla terminada la tenemos en la figura de la derecha.
ââ La función de búsqueda en tablas
La función condicional tiene una limitación: sirve para elegir entre "esto" y "lo otro". Es decir, solamente dos posibilidades. ¿Cómo habríamos hecho en el caso del festival de teatro si hubiera habido tres precios para la entrada? Por ejemplo, $ 2 para los menores, $ 3 para los mayores y gratis para jubilados.
Podríamos haber aplicado combinaciones de la función condicional. De acuerdo, pero no nos compliquemos. Para este caso hay una función
búsqueda en tablas. La aplicamos al siguiente ejemplo:
El problema es éste: tenemos la lista de la planilla a la izquierda y queremos completarla con los sueldos que le corresponde a cada uno, según su
Es muy fácil resolver esto "a mano". Necesitamos la ayuda de un amigo de confianza a quien le damos la segunda tabla. Nosotros, por nuestra parte, comenzamos a leer la primera lista. Encontramos que Pablo tiene categoría 3. Entonces preguntamos con voz fuerte y clara: Es lo mismo poner
"¿Categoría 3?" Nuestro amigo consulta su tabla, recorre la primera columna hasta localizar la Categoría 3, corre luego su vista hasta la segunda columna para encontrar el sueldo que le corresponde y canta, a su vez, "¡700!".
Repetimos el procedimiento con Marcelo, y así sucesivamente hasta completar toda la lista. Pero este mecanismo no es muy práctico.
ÙÙ Usando la función adecuada
Si solamente hubiera dos categorías, podríamos usar la función condicional: si es de tal categoría va tal sueldo, si no el otro. Pero como hay muchas categorías necesitamos otra función: la función de búsqueda en tablas.
Se trata, en realidad, de una familia de funciones cuyo miembro más conocido es =BUSCARV. La V viene de vertical, por el formato de la tabla, por columnas. Existe la función =BUSCARH para el caso, menos frecuente, de tablas horizontales, por filas. Tiene varias aplicaciones. Vamos a ellas.
ÙÙ Primer caso: variante tradicional
Para resolver el problema de la planilla de ejemplo, tenemos que escribir, en la celda C2, esta función:
=BUSCARV(B2;A11:B14;2)
Tal como está presentado el ejemplo, luego de escribir la función en C2 (para Pablo), tenemos que copiar la columna abajo para aplicarla al resto de la gente. Como en la serie de fórmulas que se obtendrán, la tabla es siempre la misma, tenemos que fijar sus coordenadas con signos $. Quedaría así:
=BUSCARV(B2;A$11:B$14;2)
ÙÙ Cómo funciona
Todo lo que hacía nuestro amigo para ayudarnos a completar la lista, lo hace esta
Efectivamente, nosotros le pasábamos el valor de la categoría de Pablo (contenido en B2), él lo buscaba en su tabla (A11:B14) y nos cantaba el valor que se encontraba en la segunda columna de la tabla (por eso el 2).
En otras palabras:
* B2 es lo que se llama el valor de entrada. Es el dato con que entramos en la tabla.
* A11:B14 es la tabla donde se hace la búsqueda. Tenemos que incluir todas las columnas (la de categoría, por donde se entra, y la de sueldo, por donde se sale). En cambio, excluimos la fila de títulos.
* 2 es la posición (contando desde la izquierda) de la columna que contiene el dato que finalmente entregará la función.
Si este último parámetro no quedara del todo claro, se entenderá mejor en el segundo caso.
Podría ocurrir que el valor de entrada no estuviera entre los valores de la primera columna. Entonces hay tres posibilidades:
* Que el valor de entrada sea menor al primer valor de la tabla. En este caso, la función devuelve un mensaje de error.
* Que el valor de entrada sea mayor al último valor de la tabla. En este caso, la función devuelve el valor que acompaña al último valor. En el ejemplo anterior para una categoría de 5 obtendríamos un sueldo de 1200.
* Que el valor de entrada esté comprendido entre dos valores de la tabla. En este devuelve el valor correspondiente al menor de los dos. En el ejemplo anterior para una categoría de 2,5, obtendríamos un sueldo de 600.
ÙÙ Segundo caso: búsqueda en tablas de múltiples columnas
Esta nueva planilla es una versión más completa de la planilla anterior. Pretendemos que la planilla asigne automáticamente el sueldo y la categoría laboral. Ambos valores dependen de la categoría.
La función a escribir en C2 es la misma del caso anterior. Pero en D2 tenemos que poner:
=BUSCARV(B2;A11:C14;3)
El último parámetro es un 3, porque lo que queremos traer (la categoría laboral) figura en la tercera columna de la tabla.
Hasta aquí, la función de búsqueda en tablas según su uso "de manual". Lo que sigue son dos ejemplos especiales.
Supongamos el caso de una hipotética empresa que decide pagar a sus empleados un premio especial de fin de año. El importe del premio varía según el sueldo y la empleado, tal como se
indica en la planilla de la derecha.
Esto es lo que se llama una tabla de doble entrada: sueldo y antigüedad. La tabla dice que, por ejemplo, un empleado cuyo sueldo fuera de $ 1.500 y su antigüedad de 10
años, recibiría un premio de $1.200. Queremos una función que realice la búsqueda en esta tabla de doble entrada. Que nos diga, dados el sueldo y la antigüedad de un empleado, cuál es el premio que le corresponde.
La primera columna de la tabla debe estar ordenada en forma creciente (de menor a mayor). En caso contrario la función produce resultados extraños.
Aunque las planillas de cálculo no tienen funciones de búsqueda en tablas de doble entrada, puede resolverse el problema combinando dos funciones simples.
En primer lugar, tenemos que hacer una ligera modificación en la tabla. La modificación consiste en insertar una fila debajo de las antigüedades y encima de la tabla propiamente dicha. Luego, completamos esta fila con los números del dos al seis. Tantos números como columnas de datos haya, comenzando en 2. Hechas estas modificaciones, la función que tenemos que escribir en D2 para resolver el problema es:
=BUSCARV(B2;A14:F17;BUSCARH(C2;B12:F13;2)
Fórmula que, obviamente, tenemos que extender hacia abajo para aplicarla a toda la lista de gente; pero antes de copiar la fórmula, tenemos que fijar las coordenadas de las filas con los signos $ ya que la consulta se hace en la misma tabla para todos los integrantes de la lista.
La primera parte de la función es una BUSCARV que trabaja como ya vimos: toma el sueldo que está en B2 y lo busca en la primera columna de la tabla A14:F17. Una vez que encuentra este sueldo, trae el valor del premio que está en ... ¿en dónde? Si la antigüedad fuera menor que cinco, el premio sería el que está en la segunda columna; si la antigüedad fuera de entre cinco y diez, el premio lo encontraría en la tercera columna, y así sucesivamente.
Es decir que el “corrimiento" que debe hacer la función para encontrar el valor correcto depende de la antigüedad y está dado por la tabla formada por las dos primeras filas, B12:F13. La función BUSCARH encuentra ese corrimiento: toma el 2), lo busca en la tabla B2:F13 y trae el valor que está inmediatamente por debajo de la antigüedad buscada. Ese valor es el corrimiento que debe tomar la función BUSCARV.
ÙÙ Cuarto caso: ¿Existe este valor?
Problema: encontrar una función que nos diga si un cierto valor pertenece o no a una lista dada.
Solución: la función de búsqueda en tablas (prescindimos esta vez de la ayuda de nuestros amigos).
Efectivamente, sea el caso que vemos en la siguiente planilla.
Queremos escribir algo en la celda B2 que nos diga si el dato escrito en B1
pertenece o no a la lista escrita en D1:D6. Ese algo es:
=BUSCARV(B1;D1:D6;1)=B1
La explicación es la siguiente.
El tercer parámetro de la función es un 1. Eso quiere decir que la columna de entrada es la misma que la de salida. De modo que si el valor buscado se encuentra, devuelve ese mismo valor. Si no se encuentra, devuelve el anterior. En la expresión se compara el valor devuelto por la función con el propio valor de entrada. Si éste se encuentra, ambos valores coinciden y como resultado de la comparación aparecerá la palabra VERDADERO. Si no se encuentra, los valores serán diferentes y aparecerá la palabra FALSO.
ââ Funciones de fechas
En el repertorio de funciones provistas por Excel figuran también las denominadas "funciones de fecha”. Son, obviamente, funciones que permiten hacer cálculos basados en el calendario: vencimientos, edades, días de la semana, etc. Vamos a ellas.
La más simple de las funciones de fecha es la función =HOY(). Si escribimos esta función en la celda A1 de una planilla nueva lo que Excel nos entrega es la fecha de hoy. Por ejemplo, 27/06/98.
Las funciones de fecha pueden formar parte de otros cálculos. Por
=A1+30
da como resultado otra fecha. La fecha correspondiente a 30 días a partir de hoy. Este es el tipo de cálculos que Excel puede hacer con fechas. En los cálculos, las fechas representan "cantidad de días". De la misma forma, restando dos fechas entre sí se obtiene la cantidad de días que median entre ambas.
Por ejemplo, Pedro nació el 4 de julio de 1970. Si escribimos la fórmula:
=HOY()-FECHA(70;7;4)
El resultado es 10.220. Pedro lleva vividos 10.220 días. La función FECHA permite poner en una fórmula una fecha cualquiera. Entre los paréntesis se escriben año,
mes y día, convenientemente separados.
Los tres términos de la función FECHA aparecen en el texto separados por punto y coma. Esto depende del tipo de puntuación establecida en Excel. Según la configuración que se tenga, podría ir coma como separador.
También se podría hacer esta cuenta:
=FECHA(70;7;4)+10000
El resultado es el 19 de noviembre de 1997. Ese fue el cumpleaños día número diez mil de Pedro.
ÙÙ Un ejemplo
Dejemos los asuntos personales y apliquemos las funciones de fecha a un ejemplo. La siguiente planilla es una lista del
personal temporario de una empresa con las fechas de comienzo y fin de sus tareas.
Si una celda muestra #¿NOMBRE? Es porque escribimos mal el nombre de un función formateada como fecha, lo que pongamos después ya no aparecerá como número. Hay que ponerle formato “General”
Queremos calcular los honorarios de cada uno, sabiendo que se les paga $ 25 por día.
La columna D debe calcular la diferencia entre las fechas indicadas en las columnas B y C. Entonces:
ΠEscribimos la planilla tal como se ve en la figura.
• En el casillero D3 escribimos la fórmula =(C3-B3+1)*25.
• Extendemos (usando el puntero de estirar) esta fórmula a lo largo de toda la lista. El resultado lo podemos ver en la planilla de la izquierda.
Según las propiedades de las funciones de fecha, la diferencia entre las fechas escritas en B3 y C3, es la cantidad de días que median entre ambas. Para conocer los días trabajados, a esa diferencia hay que sumarle un día más, ya que los empleados trabajaron desde la primera de las fechas hasta la segunda, inclusive. Finalmente, el número final se multiplica por 25, que es el precio de la jornada de trabajo. Todo eso hace la fórmula que escribimos antes.
ÙÙ El día de la semana
Función DIASEM(). Esta función devuelve un número que indica el
correspondiente a una fecha cualquiera. Hay varias formas de usarla. Por ejemplo:
=DIASEM("4-7-70")
En esta variante, el único argumento de la función, es la fecha cuyo día de la semana queremos averiguar.
DIASEM() devuelve un número entre 1 (domingo) y 7 (sábado). En este caso devuelve 7, es decir sábado. La función DIASEM() puede llevar un segundo argumento. Si escribimos =DIASEM(“4-7-70”;2), el número devuelto va de 1 (lunes) a 7 (domingo). En este caso al sábado le corresponde 6.