1 FUNCIONES Y CONDICIONALES EN EXCEL

TEMA: FUNCIONES Y CONDICIONALES EN EXCEL         DURACIÓN: 2 HORAS

LOGRO: IDENTIFICAR LA  APLICABILIDAD EN EL DESARROLLO DE PROCESOS ORGANIZACIONALES

INDICADORES DE LOGRO

·         Aprender el manejo del programa Excel como herramienta de trabajo

·         Utilizar el programa Excel como herramienta matemática, estadística y contable

·         Aprender a utilizar el programa Excel como una base de datos relacional.

CONSOLIDACIÓN DE CONCEPTOS

FÓRMULAS: Todos sabemos que Excel permite introducir en las celdas al menos dos tipos de información:

Por un lado, los valores constantes. Estos son números o cadenas de texto que permanecen invariables independientemente del contenido de otras celdas.

Por otro lado, las fórmulas. Éstas son combinaciones de operadores y operadores que arrojan un resultado, el cual puede llegar a cambiar si cambia el contenido de otras celdas.

Tipos de operadores

Los operadores son los elementos con los que se realizan cálculos en las fórmulas. Éstos pueden ser:

• Constantes (numéricas, de texto o de fecha, dependiendo de la fórmula)
• Referencias a celdas
• Referencias a rangos
• Fórmulas

La gran ventaja de utilizar referencias de celdas como operadores de fórmulas, es que su resultado se actualiza automáticamente cuando cambia el contenido de la celda que se ha utilizado como operando. En muchas ocasiones, los cálculos se complican al utilizar fórmulas como operadores de fórmulas, y los menos atrevidos prefieren colocar la fórmula en una celda y luego utilizar una referencia a ésta última como operando de la fórmula definitiva

.

TIPOS DE OPERADORES

Operadores aritméticos      Para ejecutar las operaciones matemáticas básicas como suma, resta o multiplicación, combinar números y generar resultados numéricos, utilice los siguientes operadores aritméticos.

Operador aritmético	Significado (Ejemplo)
+ (signo más)	Suma (3+3)
- (signo menos)	Resta (3-1) Negación (-1)
* (asterisco)	Multiplicación (3*3)
/ (barra oblicua)	División (3/3)
% (signo de porcentaje)	Porcentaje (20%)
^ (acento circunflejo)	Exponenciación (3^2)

Operadores de comparación      Se pueden comparar dos valores con los siguientes operadores. Cuando se comparan dos valores utilizando estos operadores, el resultado es un valor lógico: VERDADERO o FALSO.

Operador de comparación	Significado (Ejemplo)
= (signo igual)	Igual a (A1=B1)
>(signo mayor que)	Mayor que (A1>B1)
<(signo menor que)	Menor que (A1<B1)
>= (signo igual o mayor que)	Igual o mayor que (A1>=B1)
<= (signo igual o menor que)	Igual o menor que (A1<=B1)
<>(signo distinto de)	Distinto de (A1<>B1)

Operador de concatenación de texto      Utilice el signo (&) para unir o encadenar una o varias cadenas de texto con el fin de generar un solo elemento de texto.

Operador de texto & ("y" comercial)Conecta o encadena dos valores para generar un valor de texto continuo ("Viento"&"norte")

Operadores de referencia      Combinan rangos de celdas para los cálculos con los siguientes operadores.

Operador de referencia	Significado (Ejemplo)
: (dos puntos)	Operador de rango que genera una referencia a todas las celdas entre dos referencias, éstas incluidas (B5:B15)
, (coma)	Operador de unión que combina varias referencias en una sola (SUMA(B5:B15,D5:D15))
(espacio)	Operador de intersección que genera una referencia a celdas comunes a las dos referencias (B7:D7 C6:C8)

El orden de precedencia de las operaciones aritméticas en Excel se resume en lasiguiente tabla:

PRECEDENCIA DE OPERADORES ARITMÉTICOS

¿Cuál es el resultado de 6-4/2? Si crees que la respuesta es 1, sigue leyendo. ¿Cuál es el resultado de 12/3+2*2-1? Si crees que la respuesta es 11, ésta es tu sección. Aunque puede resultar innecesario, no está de más recordar que las operaciones de una fórmula  NO se resuelven de izquierda a derecha. Es un error muy común, ya que en Occidente leemos de este modo. Las operaciones matemáticas tienen una jerarquía, un orden de precedencia, así que algunas se resuelven antes que otras.

El orden de precedencia de las operaciones aritméticas en Excel se resume en la siguiente tabla:

1º        Las operaciones entre paréntesis, de adentro hacia fuera     

2º        La exponenciación (^).   

3º        La multiplicación (*) y división (/), de izquierda a derecha   

4º        La suma (+) y resta (–), de izquierda a derecha   

5º        La concatenación (&)      

Aquí tienes algunos ejemplos confeccionados con idénticas sucesiones de operadores y operadores, alterando tan sólo la situación de los paréntesis. Trata ahora de unir “cada ovejacon su pareja”.

1          12/3+2*2-1 3,8         A

2          (12/3)+2*(2-1) 2,4    B

3          12/(3+2)*2-1 7          C

4          (12/3+2)*2-1 11        D

5          12/(3+2*(2-1)) 6        E

La conclusión es que siempre debemos utilizar el paréntesis, aunque a primera vista pueda parecer redundante. De este modo, nos aseguramos de que la fórmula se comporta exactamente tal y como nosotros habíamos previsto. Si manejamos millones, una precaución tan básica como inofensiva puede evitarnos la cárcel.

Además, hay una ventaja añadida: al recorrer la fórmula en la barra de fórmulas, a medida que introducimos paréntesis o desplazamos el punto de inserción, podemos ver cómo se resaltan los paréntesis emparejados, y su nivel de anidamiento se muestra con un código de colores. Si Microsoft se ha tomado tantas molestias, será por algo.  Resultado del ejemplo anterior: 1C;2E;3A;4D;5B

Uso de paréntesis

Para cambiar el orden de evaluación, escriba entre paréntesis la parte de la fórmula que se calculará en primer lugar. Por ejemplo, la siguiente fórmula da un resultado de 11 porque Excel calcula la multiplicación antes que la suma. La fórmula multiplica 2 por 3 y, a continuación, suma 5 al resultado.                        =5+2*3

Por el contrario, si se utilizan paréntesis para cambiar la sintaxis, Excel sumará 5 y 2 y, a continuación, multiplica el resultado por 3, con lo que se obtiene 21.         =(5+2)*3

En el siguiente ejemplo, los paréntesis que rodean la primera parte de la fórmula indican a Excel que calcule B4+25 primero y después divida el resultado por la suma de los valores de las celdas D5, E5 y F5.=(B4+25)/SUMA(D5:F5)

Constantes en las fórmulas

Una constante es un valor que no se calcula. Por ejemplo, la fecha 9-10-2008, el número 210 y el texto "Ganancias trimestrales" son constantes. Una expresión, o un valor obtenido como resultado de una expresión, no es una constante. Si utiliza valores constantes en la fórmula en vez de referencias a celdas (por ejemplo, =30+70+110), el resultado cambia sólo si modifica la fórmula.

EDICIÓN INTELIGENTE DE FÓRMULAS

Uno de los problemas a los que nos enfrentamos a la hora de interpretar y retocar fórmulas es que no sabemos a ciencia cierta cuáles son las celdas que participan en ella. Pero no hay que preocuparse: existe una técnica que nos permite a) conocer perfectamente cuáles son las celdas que participan en una fórmula y b) modificar esas referencias con un golpe de ratón. Si editamos una fórmula con referencias a otras celdas de la hoja activa, éstas se resaltan con un código de colores.

TALLER

EJERCICIO 1

1)    Realizar los puntos que se detallan a continuación.

IVA:  23%                 

VENDEDOR	FECHA	MODELO	CANTIDAD	PRECIO UNITARIO	DESCUENTO	TOTAL
ANA	15/07/2010	SANDALIA	3	35.000
MARÍA	04/07/2010	BOTAS	4	85.000
CECILIA	05/07/2010	DEPORTIVO	8	120.000
RICARDO	12/07/2010	BOTAS	4	80.000
LUIS	01/07/2010	SANDALIA	9	35.000
ALEXANDER	15/07/2010	SANDALIA	7	35.000
MARIO	08/07/2010	DEPORTIVO	5	120.000
FERNANDO	09/07/2010	BOTAS	3	80.000
LUCIA	10/07/2010	DEPORTIVO	3	120.000
ALEJANDRA	11/07/2010	SANDALIA	1	35.000

2) Insertar una columna entre Descuento y Total rotulada como Importe del IVA ycalcularla para cada venta efectuada. Se debe considerar el precio con el descuentocorrespondiente y el porcentaje de IVA definido en la parte superior de la planilla

3) Crea otros datos de ventas con fechas posteriores al 15 del mes, con los mismos vendedores

4).La columna Total debe mostrar el resultado de aplicarle al precio, el descuento e IVA correspondientes para todas las unidades vendidas. Al final de esta columna totalizar.

DECISIONES CON LA FUNCIÓN =SI

Esta función juega un papel muy importante para la toma de decisiones, imagínate si lo haces manual sin esta función, podrías tardar mucho tiempo en resolver tus problemas que lleven decisiones, por ejemplo si una persona llega puntual a su trabajo se le da el bono de puntualidad, pero si llega tarde no se le da, por lo tanto tendrías que usar 2 formulas, una para la puntualidad y otra para lo contrario, entonces la función SI me evita estar cambiando la formula una y otra vez.

1 NOMBRE	RESULTADO
2 GABY	=SI(A2=”GABY”;”ES LINDA”;”ES FEA”)
3 ALEJANDRA	=SI(A3=”GABY”;”ES LINDA”;”ES FEA”)
4 MARIA	=SI(A4=”GABY”;”ES LINDA”;”ES FEA”)
5 ROSA	=SI(A5=”GABY”;”ES LINDA”;”ES FEA”)
6 LORENA	=SI(A6=”GABY”;”ES LINDA”;”ES FEA”)

=SI(A2=”GABY”;”ES LINDA”;”ES FEA”)

AZUL equivale a la condición

MORADO equivale si se cumple la condición

ROJO equivale si no se cumple la condición

LAS COMILLAS indican el texto o requisito que se debe buscar y cumplir para ejecutar la condición

La función dice:

Si la celda A2 es igual a GABY, entonces que escriba en la celda ESLINDA, si no que escriba ES FEA.EL RESULTADO SERIA EL SIGUIENTE EN EXCEL

1 NOMBRE	RESULTADO
2 GABY	ES LINDA
3 ALEJANDRA	ES FEA
4 MARÍA	ES FEA
5 ROSA		ES FEA
6 LORENA		ES FEA

La mayoría de las veces la condición siempre permanece

Otro ejemplo para entender esta función vamos a realizar una pequeña planilla donde tendremos por cada registro o fila el nombre, y 3 notas para cada persona. En la cuarta columna calcularemos el Promedio de estas tres notas

La quinta columna nos servirá para indicar la situación del alumno, la cual será aprobado en el caso de que su promedio sea mayor o igual a 4.0, o reprobado en el caso de que sea menor que 4.

Para estimar la situación de cada alumno procederemos de la siguiente manera: nos situamos en la celda donde vamos a calcular la situación del primer alumno (celda F2) y presionamos el piloto de funciones . Aparecerá la ventana Insertar función. En ella seleccionaremos la categoría Lógicas y luego seleccionaremos la función SI. En la parte inferior del panel aparece una breve reseña de la función:
SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Comprueba si se cumple una condición y devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como FALSO.

Presionamos Aceptar y pasamos a la siguiente ventana donde indicaremos los argumentos de la función. Para nuestro caso debemos evaluar si el promedio es mayor o igual que 4, por lo tanto en el argumento prueba_lógica colocamos el nombre de la celda donde está el promedio (podemos seleccionarla con el puntero del mouse), y a continuacion escribimos mayor o igual que 4 de la forma: E2>=4.  En el argumento Valor_si_verdadero colocamos el valor que deseamos para el caso que nuestra condición sea verdadera, que sería Aprobado.

En el argumento Valor_si_falso colocamos el valor que deseamos para el caso que nuestra condición sea falsa, que sería Reprobado.

Aceptamos, y luego autocompletamos para el resto de los registros.

Hay que hacer notar que si seleccionamos la celda donde hemos aplicado la función si, veremos que la forma de escribir la formula manualmente es:
=si(E2>=4;"Aprobado","Reprobado")

TALLER

Ejercicio

EJERCICIO 1

De acuerdo a los datos de la tabla 1 realizar los siguientes cálculos:

VENDEDOR	FECHA	MODELO	CANTIDAD	PRECIO UNITARIO	DESCUENTO	TOTAL
ANA	15/07/2010	SANDALIA	3	35.000
MARÍA	04/07/2010	BOTAS	4	85.000

5) Calcular la columna Descuento, teniendo en cuenta que el modelo “Sandalia” tendrá undescuento sobre el precio unitario del 10%, mientas que para el resto será del 5%.

6) Al final de la planilla de ventas, en una columna rotulada como Puntos, se deben mostrarlos puntos generados por vendedor en cada venta realizada. Para esto se debe tener en cuenta que las ventas que superan las 2 unidades, generan 2 puntos; mientras que aquellas que no superan esta cantidad generan sólo 1 punto.