Sesion1.Funciones

"A veces creemos que lo que hemos logrado es sólo una gota en el océano, pero sin ella el océano estaría incompleto" Madre Teresa de Calcuta

Funciones Objetivo Aplicar las funciones para consistencia de datos, funciones condicionales

Contenido Función esblanco, eserror, eslogico, estexto, esnumero, esref esr ef Funciones Estadísticas. Contar, Contara, Contar.blanco, Contar.Si, Sumar.Si, Uso de los operadores lógicos And y OR

Funciones Instituto de Educación Superior Tecnológico Privado

1. Función esblanco, eserror, eslogico,

…

Puede utilizar las funciones esblanco y esnod para consistenciar los datos. Existen 9 funciones para hojas de cálculo que se utilizan para comprobar el tipo de un valor o referencia. Cada una de estas funciones, comprueba el tipo del argumento valor y devuelve VERDADERO o FALSO dependiendo del resultado. ESBLANCO devuelve el valor lógico VERDADERO si valor es una referencia a una celda vacía, de lo l o contrario devuelve FALSO. Sintaxis.

ESBLANCO(valor) ESERR(valor), ESERROR(valor), ESLOGICO(valor), ESNOD(valor), ESNOTEXTO(valor), ESNOTEXTO(valor), ESNUMERO(valor), ESREF(valor), ESTEXTO(valor).

Valor Es el valor que se desea probar. Puede ser el valor de una celda vacía (en blanco), de error, lógico, de texto, numérico, de referencia o un nombre que se refiera a alguno de los anteriores.

Función

Devuelve VERDADER VERDADERO O si

ESBLANCO

Valor se refiere a una celda vacía.

ESERR

Valor se refiere a cualquier valor de error con excepción de #N/A.

ESERROR

Valor se refiere a uno de los valores de error (#N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!).

ESLOGICO

Valor se refiere a un valor lógico.

ESNOD

Valor se refiere al valor de error #N/A (el valor no está disponible).

ESNOTEXTO

Valor se refiere a cualquier elemento que no sea texto. (Tenga presente que esta función devuelve VERDADERO VERDADERO incluso si valor se refiere a una celda en blanco.)

ESNUMERO

Valor se refiere a un número.

ESREF

Valor se refiere a una referencia.

ESTEXTO

Valor se refiere a texto.

Página: 2

Microsoft Excel Avanzado

Utilizaremos su hoja de cálculo “Ferretería el Maestrito SRL”

En el campo valor de venta (columna E) se tiene una fórmula =A10*D10 (Cantidad*precio unitario) Cuando no se utiliza todas las filas de detalle la columna E, muestra S/. 0.00 En las filas de detalle no utilizadas, como se muestra en la gráfica. Para corregir este problema de estética utilizaremos la función esblanco

Página: 3


Si la celda A10 está vacía

Multiplica cantidad * precio unitario

=SI(ESBLANCO(A14);"";A14*D14) Muestra vacio

2. Funciones estadísticas 2.1 CONTAR.BLANCO(rango) Cuenta el número de celdas en blanco dentro de un rango.

Observaciones Las celdas que contienen fórmulas que devuelven " " (texto vacío) también se cuentan, en cambio las celdas que contienen el valor 0 no se cuentan.

Contar la cantidad de celdas en blanco que existe la tabla

CONTAR.BLANCO(A5:D9) es igual a 2

2.2 COMBINAT(número;tamaño) Devuelve el número de combinaciones para un número determinado de elementos. Use COMBINAT para determinar el número total de grupos posibles para un número determinado de elementos.

Observaciones Los argumentos numéricos se truncan a números enteros. Si uno de los argumentos es un valor no numérico, COMBINAT devuelve el valor de error #¿NOMBRE?

Página: 4

Microsoft Excel Avanzado Si el argumento número < 0, el argumento tamaño < 0 o número < tamaño, COMBINAT devuelve el valor de error #¡NUM! Una combinación es cualquier conjunto o subconjunto de objetos, independientemente de su orden interno. Las combinaciones son distintas de las permutaciones, en las que el orden interno es importante. El número de combinaciones es el siguiente, donde el argumento número = n y el argumento tamaño = k:

Ejemplo Supongamos que desee saber cuántos equipos de dos personas podría formar con ocho candidatos. COMBINAT(8; 2) es igual a 28 equipos.

2.3 Función CONTAR(ref1, ref2,...) Cuenta los números que hay en la lista de argumentos. Use CONTAR para obtener el número de entradas en un campo numérico de un rango o de una matriz de números. Donde: ref1; ref2. ..son entre 1 y 30 argumentos que pueden contener o hacer referencia a distintos tipos de datos, pero sólo se cuentan los números. Ejemplos Si A3 contiene “Ventas”, A4 contiene 8112190, A6 contiene 19, A7 contiene 22.24 y A8 contiene “#¡DIV/0!”, entonces: =CONTAR(A6:A7) =CONTAR(A4:A7) =CONTAR(A2,A6:A9,“Doce”,5) =CONTAR(A1:A9,,”2”) es una representación textual de un Número

Es igual a 2 Es igual a 3 Es igual a 3 Es igual a 5

Las funciones estadísticas ejecutan análisis estadísticos de rangos de datos. Por ejemplo, una función estadística puede proporcionar información estadística acerca de una línea recta trazada como resultado de un conjunto de valores, como la pendiente de la línea y la intersección "y" o acerca de los puntos reales que componen la línea. Ahora describiremos las funciones más importantes.

Página: 5


2.4 CONTAR.SI(rango;criterio) Cuenta las celdas, dentro del rango, que no están en blanco y que cumplen con el criterio especificado. Rango, es el rango dentro del cual desea contar el número de celdas que no están en blanco. Criterio, es el criterio en forma de número, expresión o texto, que determina las celdas que se van a contar. Por ejemplo, el argumento criterio puede expresarse como 32; "32"; ">32" o "manzanas".

De la siguiente tabla calcular la cantidad de alumnos provenientes de la policía nacional y la cantidad de civiles.

2.5 SUMAR.SI(rango;criterio;rango_suma) Suma las celdas en el rango que coinciden con el argumento criterio. Rango, es el rango de celdas que desea evaluar. Criterio, es el criterio en forma de número, expresión o texto, que determina qué celdas se van a sumar. Por ejemplo, el argumento criterio puede expresarse como 32; "32"; ">32"; "manzanas". Rango_suma, son las celdas que se van a sumar. Las celdas contenidas en rango_suma se suman sólo si las celdas correspondientes del rango coinciden con el criterio. Si rango_suma se omite, se suman las celdas contenidas en el argumento rango.

Página: 6


De la siguiente lista calcular el total de importe obtenido por los viajes realizados a Lima, Tacna, Trujillo, Cuzco. Mostrar los resultados por cada destino.

2.6 SUBTOTALES(núm_función;ref1) Devuelve un subtotal en una lista o base de datos. Generalmente es más fácil crear una lista con subtotales utilizando el comando Subtotales del menú Datos. Una vez creada la lista de subtotales, puede cambiarse modificando la fórmula SUBTOTALES. Núm_función, es un número de 1 a 11 que indica qué función debe ser utilizada para calcular los subtotales dentro de una lista. 1-PROMEDIO, 2-CONTAR, 3-CONTARA, 4-MAX, 5-MIN,6PRODUCTO, 7-DESVEST, 8-DESVESTP, 9-SUMA, 10-VAR, 11-VARP Ref1 es el rango o referencia para el cual desea calcular los subtotales.

Observaciones Si hay otros subtotales dentro de ref1 (o subtotales anidados), estos subtotales anidados se pasarán por alto para no repetir los cálculos. La función SUBTOTALES pasa por alto las filas ocultas. Esto es importante cuando sólo desea obtener el subtotal de los datos visibles que resulta de una lista filtrada.

Página: 7


Si alguna de las referencias es una referencia 3D, SUBTOTAL devolverá el valor de error #¡VALOR!.

Cálculos estadísticos referentes al campo importe

2.7 MODA(número1;número2; ...) Devuelve el valor que se repite con más frecuencia en una matriz o rango de datos. Al igual que MEDIANA, MODA es una medida de posición. Número1;número2; ..., son entre 1 y 30 argumentos cuya moda desea calcular. También puede usar una matriz individual o una referencia matricial en lugar de argumentos separados por punto y coma.

Observaciones Los argumentos deben ser números, nombres, matrices o referencias que contengan números.

Página: 8

Microsoft Excel Avanzado Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se pasan por alto; sin embargo, se incluirán las celdas cuyo valor sea 0. Si el conjunto de datos no contiene puntos de datos duplicados, MODA devuelve el valor de error #N/A. En un conjunto de valores, la moda es el valor que se repite con mayor frecuencia; la mediana es el valor central y la media es el valor promedio. Ninguna de estas medidas de la tendencia central tomada individualmente proporciona una imagen completa de los datos. Supongamos que los datos están agrupados en tres áreas, la mitad de las cuales es un valor bajo que se repite y la otra mitad consiste en dos valores elevados. Tanto PROMEDIO como MEDIANA devolverán un valor situado en una zona central relativamente vacía, y MODA devolverá el valor bajo dominante.

Calcular la moda de un conjunto de edades.

=Moda(A4:A12)

3. Función Si, si anidado Pueden utilizarse las funciones lógicas para ver si una condición es cierta o falsa o, para comprobar varias condiciones.

Página: 9


a.FALSO( ) Devuelve el valor lógico FALSO.

b.VERDADERO( ) Devuelve el valor lógico VERDADERO.

c. NO(valor_lógico) Invierte el valor lógico del argumento. Use NO cuando desee asegurarse de que un valor no sea igual a otro valor específico.

Ejemplos NO(FALSO) es igual a VERDADERO; NO(1+1=2) es igual a FALSO

d.SI(prueba_lógica;valor_si_verdadero;valor_si_falso) La función SI es la prueba lógica más utilizada. Tiene tres argumentos entre paréntesis y separados por comas: La comparación lógica. Es una expresión lógica Expresión si es VERDAD. Valor de la celda a usar cuando la comparación es VERDAD Expresión si es FALSA. Valor de la celda a usar cuando la comparación es FALSA

Sintaxis:

=SI(comparación lógica, expresión si es VERDAD, expresión si es FALSA) Un valor puede ser un número, texto entre comillas, una celda de referencia, una fórmula u otra prueba lógica.

Tabla de operadores lógicos Operador Lógico

=

Significado Igual a

Ejemplo =SI(E8=C8,"Igual,"No igual") Cuando las dos celdas son iguales, se muestra la palabra

Página: 10


"Igual". Cuando las dos celdas no son iguales, se muestra la frase "No igual".

<

Menor que

=SI(F4
>

Mayor que

=SI(C6>100,C6,100) Si C6 es mayor que 100, mostrar C6. De los contrario mostrar 100.

<=

Menor que o igual a

=SI((B5<=10,B5,"Máximo") Si B5 es mayor que, o igual a 10, mostrar B5. De lo contrario mostrar la palabra "Máximo".

>=

Mayor que o igual a

=SI((MAX(B4:E8)>=SUMA(B4:E8)/2,MáX(B4:E8), SUMA(B4:E8)/2) Si el valor más grande en el rango, es mayor que o igual a la mitad de la suma del rango, entonces mostrar el valor más grande. De lo contrario mostrar la mitad de la suma del rango. (Estas declaraciones pueden ofrecer un aspecto realmente desordenado!)

<>

No igual a

=SI((B8<>D6,SI((B8<10,10,B8),D6) Si B8 no es igual a D6, verificar para ver si B8 es menor que 10. Mostrar 10 si lo es y B8 si no lo es. De lo contrario, mostrar D6, el que en este caso será igual a B8.

Aplicación de la función SI utilizando como comparación lógica un dato texto A su hoja de cálculo anterior adicionar la columna bonif1.

Página: 11


Bonif.1. Se calculará según el siguiente criterio. Si el empleado está afiliado a una AFP, se le asigna 15% del sueldo básico Si el empleado está afiliado a una SNP, se le asigna 10% del sueldo básico

Si el empleado esta afiliado a una AFP

=SI(J4="AFP",15%*K4, 10%*K4) Si es verdad se asigna 15% del sueldo básico

Si es falso se asigna 10% del sueldo básico

Aplicación de la función SI utilizando como comparación lógica un dato numérico (rango de números) A su hoja de cálculo anterior adicionar la columna bonif2.

Bonif.2. Se calculará según el siguiente criterio.

Página: 12

Microsoft Excel Avanzado Si el empleado tiene un sueldo básico menor a 1,500, asignarle una bonif2 de 10% del sueldo básico

Si sueldo básico menor a 1500

=SI(K4<1500,10%*K4, 0 ) Si es verdad se asigna 10% del sueldo básico

Si es falso se asigna 0

Aplicación de la función SI utilizando como comparación lógica un dato fecha A su hoja de cálculo anterior adicionar la columna bonif3.

Bonif.3. Se calculará según el siguiente criterio. Si el empleado ingreso antes del año 1970, asignarle una bonif3 de 10% del sueldo básico

Página: 13


Si año de ingreso es menor al año 1970

=SI(AÑO(D4)<1970, 10% * K4, 0 ) Si es verdad se asigna 10% del sueldo básico

Si es falso se asigna 0

e. =Si anidado Puede anidar hasta 7 declaraciones Y SI... para crear pruebas complejas. Por ejemplo, para mostrar una letra de calificación en la celda vecina a Calificación Promedio, podría usar 4 declaraciones anidadas en una sola fórmula:

=SI(Gradomediano>89,"A",SI(Gradomediano>79,"B",SI(Gradomediano> 69,"C",SI(Gradomediano>59,"D","F"))))

Aplicación de la función SI anidado utilizando rangos de números. A su hoja de cálculo anterior adicionar la columna Grado

Página: 14


Asignar un grado al empleado (A,B,C,D,E) según la siguiente tabla. Rango

Grado

1

a

2000

E

2001

a

3000

D

3001

a

4000

C

4001

a

5000

B

5000

a

más

A

Si sueldo básico es mayor a 5000

=SI(K4>5000, "A" ,SI(K4>4000,"B",SI(K4>3000,"C",SI(K4>2000,"D","E")))) Si es verdadero asigna “A”

Si es falso vuelve a preguntar, y así sucesivamente

Aplicación de la función SI anidado utilizando rangos de fechas A su hoja de cálculo anterior adicionar la columna Bonif.4

Página: 15


Asignar un porcentaje del sueldo básico, según la siguiente tabla. Fecha de ingreso

Bonif. 4

1960

a

1980

25%

1981

a

1990

20%

1991

a

2000

15%

2001

a

2008

10%

Si fecha de ingreso fue después del año 2000

=SI(AÑO(D5)>2000,10%*K5,SI(AÑO(D5)>1990,15%*K5,SI(AÑO(D5)>1980,20%*K5,25%*K5))) Si es verdadero, asignar 10% del sueldo básico

Si es falso vuelve a preguntar

E. Operador lógico Y Esta función suele utilizarse conjuntamente con la función Si. Nos permite realizar en lugar de una pregunta varias. Y sólo se realizará el argumento situado en la parte verdadero del Si en el momento que todas las respuestas sean verdaderas. Sintaxis

Y(Pregunta 1; pregunta 2; pregunta 3;...) Página: 16

Microsoft Excel Avanzado Ejemplo: En la celda A1, introduciremos la edad y en la A2 la estatura de la persona medida en centímetros. En la celda A3 aparecerá el texto "Puede pasar" si la edad es mayor de 16 años y mide más de 150. En el caso que alguna de las dos condiciones no se cumplan, aparecerá el texto "NO puede pasar". =SI(Y(A1>16;B1>150);"Puede pasar";"NO puede pasar") Observa que toda la función Y(...) se escribe dentro del primer argumento de la función Si.

Aplicación de la función Y. A su hoja de cálculo anterior adicionar la columna Bonif.5

Asignar una bonificación de 2% del sueldo básico a los empleados que cumplan las siguientes condiciones: Pertenecen a la Gerencia Están afiliados a una AFP Su sueldo básico es menor que 3,000 Si no cumplen esta condiciones asignar 5% del sueldo

Página: 17


Si gerencia es igual a G1 y está afiliado a una AFP y su sueldo básico es menor 3,000

=SI(Y(H4="G1";J4="AFP" ; K4<3000) ; 2%*K4 ; 5%*K4) Si es verdad, asignar 2% del sb

Si es falso, asignar 5% del sb

Donde: sb. Sueldo básico

Crear un cuadro de proyección de cuentas por cobrar. Ejemplo. Al señor Carlos Arna se le hizo un préstamo de 20,000.00 el cual pagará en 3 cuotas de 6,666.67. El préstamo se realizó el primero de febrero del 2000, en el cuadro proyectado deberá aparecer de marzo a mayo los montos que nos debe. Diseño del cuadro

Fórmulas a utilizar.

Página: 18


F. Operador lógico O Esta función también se suele utilizar conjuntamente con la función Si. Con ella también podremos realizar varias preguntas dentro del Si y la parte que está en el argumento reservado para cuando la pregunta es verdadera, sólo se realizará en el caso que cualquiera de las respuestas a las preguntas dentro de la O sea verdadera. Sintaxis:

O(Pregunta 1; pregunta 2; pregunta 3;...) Ejemplo: Utilizaremos el mismo ejemplo anterior pero dejaremos pasar si la persona es mayor de 16 años o mide más de 150. De esta manera con que se cumpla una de las dos aparecerá el texto "Puede pasar". El único caso que aparecerá "NO puede pasar", será cuando las dos preguntas no se cumplan. =SI(O(A1>16;B1>150);"Puede pasar";"NO puede pasar")

Aplicación de la función O. A su hoja de cálculo anterior adicionar la columna Bonif.6

Asignar una bonificación de 2% del sueldo básico a los empleados que cumplan una las siguientes condiciones: Pertenecen a la Gerencia Están afiliados a una AFP Su sueldo básico es menor que 3,000 Si no cumplen al menos una de estas condiciones asignar 5% del sueldo básico

Página: 19


Si gerencia es igual a G1 ó está afiliado a una AFP ó su sueldo básico es menor 3,000

'=SI(O(H4="G1";J4="AFP";K4<3000);2%*K4;5%*K4) Si es verdad, asignar 2% del sb

Donde: sb. Sueldo básico

Página: 20

Si es falso, asignar 5% del sb

Sesion1.Funciones

Recommend Documents