funciones basicas de excel

Fundamentación

Una de las grandes herramientas que existen en Excel son las

funciones, que permiten simplificar el trabajo al momento de buscar

resultados de diversos cálculos y operaciones extremadamente útiles, que

completan todo el poder de la hoja de cálculo.

Esta Unidad proporciona información sobre las funciones básicas y

funciones que utilizan criterios lógicos de Excel, mostrando su sintaxis e

incluyendo ejemplos de cómo se deben utilizar.

Capacidad

El participante al final de esta unidad estará en capacidad de utilizar funciones

básicas y con criterios lógicos en cálculos matemáticos y estadísticos simples

y condicionales.

Contenidos

INTRODUCCIÓN A LAS FUNCIONES DE EXCEL

o Definición y sintaxis.

o Funciones básicas: SUMA, PROMEDIO, MIN, MAX, CONTAR,

CONTARA, CONTAR.BLANCO, ENTERO, REDONDEAR, TRUNCAR,

HOY(), AHORA()

UNIDAD

UNIDAD

3 Guía Didáctica

P á g i n a | 2

o Funciones con un criterio lógico: CONTAR.SI, SUMAR.SI,

PROMEDIO.SI

o Funciones con múltiples criterios lógicos:

CONTAR.SI.CONJUNTO, SUMAR.SI.CONJUNTO,

PROMEDIO.SI.CONJUNTO

o Función SUMAPRODUCTO

Actividades

Las actividades a desarrollar en la Unidad son:

Actividad 01: Obtiene cálculos utilizando funciones básicas de Excel.

Actividad 02: Utiliza funciones matemáticas y estadísticas con criterios

lógicos.

Estrategias Metodológicas

Para desarrollar la presente unidad llevaremos a cabo dos tipos de

actividades:

1. Lectura Inicial de Contenidos, la realizaremos de manera ordenada,

presenta los aspectos teóricos. Si se presenta alguna duda se puede

realizar lecturas sobre puntos específicos en la sección de Material

Complementario o en las direcciones electrónicas de tus sílabos para esta

Unidad 3.

2. Actividades a Desarrollar en la Unidad, se encuentran en el apartado de

Actividades, donde constan: las consignas o indicaciones.

Recuerde que el Aula Virtual de la UCV, se empleará como medio de

comunicación entre el docente y los estudiantes, para efectos de mensajería

interna, foros, evaluaciones virtuales, acciones de tutoría académica y

comunicación de resultados de evaluación de los aprendizajes.

P á g i n a | 3

Evaluación

En esta Unidad Ud., será evaluado en:

1. El uso de funciones básicas en la obtención de resultados matemáticos y

estadísticos simples.

2. El uso de funciones matemáticas y estadísticas que utilizan uno o más

criterios lógicos.

Es importante que Ud., esté en constante comunicación con su docente

Tutor, ya sea para informar de dificultades, avances de las tareas, etc.

Recuerde que será calificado de acuerdo a las rúbricas de evaluación de cada

actividad; por ejemplo: se le evaluará por la entrega oportuna de sus

actividades.

Material Complementario

A fin de ampliar y profundizar conocimientos te recomendamos las lecturas

que se encuentran en la sección de Material Complementario:

Material Complementario Unidad 03:Uso de otras funciones básicas

Videos:

Funciones básicas 1

Funciones básicas 2

Funciones con criterio lógico

.

Estructura de Contenidos:

INTRODUCCIÓN A LAS FUNCIONES DE EXCEL o Definición y sintaxis.

o Funciones básicas: SUMA, PROMEDIO, MIN, MAX, CONTAR,

CONTARA, CONTAR.BLANCO, ENTERO, REDONDEAR, TRUNCAR,

HOY(), AHORA()

o Funciones con un criterio lógico: CONTAR.SI, SUMAR.SI,

PROMEDIO.SI

o Funciones con múltiples criterios lógicos:

CONTAR.SI.CONJUNTO, SUMAR.SI.CONJUNTO,

PROMEDIO.SI.CONJUNTO

o Función SUMAPRODUCTO

UNIDAD

3 INTRODUCCIÓN A LAS FUNCIONES DE EXCEL

P á g i n a | 5

Introducción

Estimado participante:

En esta Unidad aprenderá acerca del uso de las funciones de Excel, aplicación

de formatos condicionales avanzados y protección de elementos de la hoja de

cálculo.

Excel 2010 proporciona muchas funciones predeterminadas agrupadas en

diferentes categorías que permiten realizar cálculos de diferentes tipos,

facilitando la construcción de aplicaciones que involucran el desarrollo de

cálculos sencillos y complejos.

Veremos la utilización de formatos condicionales avanzados, los cuales están

basados en una fórmula o criterio lógico, los cuales determinan la aplicación

de dichos formatos.

Finalmente, se aplicará protección a la hoja de cálculo o parte de ella, de tal

manera que no pueda ser modificada sin la autorización correspondiente. Te

deseo éxitos en el desarrollo de esta Unidad.

P á g i n a | 6

INTRODUCCIÓN A LAS FUNCIONES DE EXCEL

Definición de función

Una función es una fórmula predefinida por Excel 2010 (o por el usuario), que

opera con uno o más argumentos (valores) para devolver un resultado. Estas

funciones están agrupadas por categorías y su elección es sencilla, según el

método que se emplee.

Sintaxis:

Todas las funciones de Excel, tienen una misma estructura de ingreso a la

hoja de cálculo, lo único que cambia son los argumentos utilizados por ellas;

por eso se tiene como sintaxis general:

Nombre_de_la_Función (arg1; arg2;....; argN)

Dónde:

Nombre_de_la_Función: es un nombre abreviado que identifica a la

función.

(arg; arg2,....; argN): son los argumentos requeridos para proveer

un resultado.

Separador de grupo: puede ser “;” o “,” (debe tener presente, ya

que puede devolverle un Error como resultado).

INGRESAR UNA FUNCIÓN EN LA HOJA DE CÁLCULO

Método 1: Escribiendo la función

Este método consiste en “digitar la función y sus argumentos” con los

datos de la aplicación, teniendo en cuenta la sintaxis de la función que estás

empleando. ¡No olvide!, que cada función tiene argumentos diferentes.

Para explicar adecuadamente este método, desarrolle el siguiente ejercicio:

1

Separador de grupo

2

P á g i n a | 7

Ejercicio: Se tiene las notas de los alumnos de una institución educativa, y

se pide Calcular el puntaje total obtenido por cada uno de ellos.

A continuación se detallan los pasos a realizar:

Sitúe el cursor en la celda donde escribirá la función. Para nuestro

ejemplo: celda F2

Escriba la función según su sintaxis. Ejemplo: =SUMA(B2:E2)

Importante:

Observa que al escribir el nombre de la función, iniciando con el

signo “=”, el programa te localiza rápidamente en una lista de

funciones, la que desea utilizar; para ingresar sus argumentos.

l

Al escribir la función y sus argumentos, debe seguir exactamente la

sintaxis que le muestra en ese instante el Excel:

!

P á g i n a | 8

Seleccione las celdas correspondientes B2:E2, cierra paréntesis.

Pulse la tecla Enter para ver el resultado.

Ahora, debe copiar la función obtenida, verticalmente desde el

controlador de relleno.

Importante:

¡MUY BIEN!: Lo ha logrado, ya tiene los resultados deseados,

este método es rápido, pero requiere de mucha práctica de su

parte, ya que debe conocer conceptualmente sus argumentos,

antes de ingresarlos. Le recomiendo que lo use, cuando logres

mucha destreza en el uso de funciones. Ahora veremos el método

2.

Método 2: Usando el asistente “Insertar función”

Este método consiste en “insertar la función y sus argumentos” a través

del botón Insertar función, para luego obtener los resultados.

Para explicar adecuadamente este método, desarrolla el siguiente ejercicio:

Ejercicio: Ahora usando el mismo cuadro de datos, debe calcular la

máxima nota de cada alumno. Previamente ingrese el texto “MAXIMA

NOTA” en la celda G1.

Sitúa el cursor en la celda donde se insertarás la función. Ejemplo: celda

G2

!

P á g i n a | 9

Haz clic en el botón Insertar función que está en la Barra de fórmulas

En el cuadro de diálogo “Insertar función”, seleccione la categoría y la

función a usar:

Ejemplo: Categoría: Estadísticas y Función: Max.

Haz clic en el botón Aceptar

Ingresa los argumentos solicitados. Puedes escribir el rango de celdas o

seleccionarlo desde el mismo cuadro de datos (con arrastre). Ejemplo:

Número1: B2:F2.

P á g i n a | 10

Ahora haz clic en el botón Aceptar, enseguida copia la fórmula hacia

abajo.

Importante:

¡LO LOGRASTE!: Como debe haberse dado cuenta, este método

es muy sencillo, ya que solo se ingresas o selecciona los

argumentos; después de seleccionar la función. Cada argumento

describe su uso al ser seleccionado. Le recomiendo que lo use, si

está recién aprendiendo a trabajar con funciones. Ahora veremos

el método 3.

Método 3: Uso del botón Autosuma

Es otro método que también puedes usar, consiste en utilizar el botón

Autosuma, que contiene básicamente a las funciones más usuales del

programa.

!

P á g i n a | 11

Para explicar adecuadamente este método, desarrolla el siguiente ejercicio:

Ejercicio: Siguiendo con el mismo ejercicio, ahora debes calcular la mínima

nota de cada alumno. Previamente ingresa el texto “MINIMA NOTA” en la

celda H1.

Haga clic en la celda donde se insertará la función. Ejemplo: celda

H2

Seleccione la ficha Inicio, y del grupo Modificar elija la opción:

Autosuma

Seleccione el nombre de la función a usar. Ejemplo: Min

Seleccione el rango de datos que se vas a usar (hazlo con

arrastre).

Ejemplo: B2:E2

Ahora pulsa Enter para obtener los resultados y copia la función

verticalmente.

P á g i n a | 12

Importante:

¡MUY BIEN…!: Es un método fácil que nos ayudará a utilizar las

funciones en forma rápida. Le recomiendo que lo uses, es muy

efectivo para un aprendizaje sencillo. Ahora veremos el método

4.

Método 4: Uso de la Biblioteca de Funciones

Este método es un resumen del método 2, ya que las funciones están

agrupadas por categorías en el grupo Biblioteca de funciones; para que las

puedas seleccionar según el cálculo a realizar.

Para explicar este método, continuará desarrollando el siguiente ejercicio:

!

P á g i n a | 13

Ejercicio: Ahora debe calcular el promedio de cada alumno. Previamente

ingrese el texto “PROMEDIO NOTAS” en la celda I1.

Ubique el cursor en la celda donde insertará la función. Ejemplo: Celda

I2

En la ficha Fórmulas y en el grupo Biblioteca de Funciones, localiza

la categoría y función: Promedio.

Ingrese los argumentos requeridos. Seleccione el rango de datos, con

arrastre.

Haga clic en el botón Aceptar y copia la función.

P á g i n a | 14

NOTA:

¿QUE LE PARECIO?: Este método también es muy fácil de

aprender. Puede usarlo como una nueva alternativa en el

manejo de funciones.

A continuación, verá con más detalle el uso de las funciones

en el desarrollo de las siguientes aplicaciones agrupadas por

categoría: ESTADISTICAS, MATEMATICAS Y

TRIGONOMETRICAS, FECHA Y HORA, etc.

FUNCIONES ESTADÍSTICAS

a) FUNCIÓN PROMEDIO: Devuelve el promedio de un rango numérico de

datos seleccionado.

Sintaxis:

=PROMEDIO (Número1; Número2;...)

Para explicar el uso de esta función, emplea el siguiente ejercicio:

Ejercicio: Se tiene las ventas realizadas en el primer trimestre del

presente año por los vendedores de la empresa TOLIMA´S S.A. Se pide

obtener el promedio de ventas por vendedor. El método de funciones

a usar quedará a su criterio según lo aprendido anteriormente.

3

P á g i n a | 15

Calculando el Promedio del Vendedor

Para este caso usará el método 3: Botón Autosuma

Haz clic en la celda E2, para obtener el Promedio Vendedor.

Como es una función básica, puede usar rápidamente el botón

Autosuma (ficha Inicio):

Ahora debe seleccionar (con arrastre) los argumentos que serán

promediados: B2:D2

P á g i n a | 16

Pulse la tecla Enter para obtener el Promedio Vendedor. Ahora

puede copiar la función al resto de vendedores (arrastre desde el

controlador de relleno).

Importante:

¡BUEN LOGRO!, ya utilizaste la función Promedio para obtener el

Promedio de las ventas de cada vendedor, pues aproxima los

resultados a dos decimales. Ahora verá el uso de la función MIN.

b) FUNCIÓN MIN: Devuelve el valor mínimo de un rango de valores.

Sintaxis:

=MIN (Número1; Número2;...)

Ejercicio: Ahora debe calcular la menor venta de cada vendedor.

Para este ejercicio, tambien emplea el método 3: Botón Autosuma, por

ser una función básica.

Previamente debe ingresar en la celda F1, el texto: MINIMA VENTA.

!

P á g i n a | 17

Seleccione la celda F2, para obtener la Mínima Venta por

vendedor.

Haga clic en el botón Autosuma y elige la función MIN:

Ahora seleccione (con arrastre) el rango de datos donde se buscará

el valor mínimo: B2:D2

Pulse la tecla Enter, para obtener el resultado. Puede copiar la

función insertada al resto de vendedores:

P á g i n a | 18

Importante:

¡MUY BIEN!, haz usado la función MIN, para obtener en un rango

de datos numéricos el valor más bajo, ahora veremos la función

MAX.

c) FUNCION MAX: Devuelve el valor máximo de un rango de valores.

Sintaxis:

=MAX (Número1; Número2;...)

Ejercicio: También en el mismo cuadro de datos que estás trabajando

deberá calcular la venta mayor de cada vendedor.

Previamente debe ingresar en la celda G1, el texto “MAXIMA VENTA” y

podrá usar también el botón Autosuma para obtener el resultado.

!

P á g i n a | 19

Seleccione la celda G2, para obtener la Máxima Venta por

vendedor.

Haz clic en el botón Autosuma y elige la función MAX:

Ahora seleccione (con arrastre) el rango de datos donde se buscará

el valor máximo: B2:D2.

Pulse la tecla Enter, para obtener el resultado. Puede copiar la función

insertada al resto de vendedores:

P á g i n a | 20

Importante:

¡FÁCIL VERDAD!, ha trabajado con la función MAX, para obtener

en un rango de datos numéricos el valor más ALTO, ahora verá la

función CONTAR.

d) FUNCIÓN CONTAR: Devuelve la cantidad de datos numéricos de un

rango.

Sintaxis:

=CONTAR (Valor1; Valor2;...)

Ejercicio: Usando la misma aplicación anterior, debe calcular el Nº de

ventas hechas en el trimestre.

Previamente, debe ingresar en la celda C10, el texto “Nº Ventas

hechas en el Trimestre” y podrá usar también el botón Insertar

función para obtener el resultado.

Seleccione la celda D10, para que inserte la función CONTAR.

!

P á g i n a | 21

Seleccione la ficha Fórmulas y haga un clic en el botón Insertar

función de la Biblioteca de funciones. Elija la categoría:

Estadísticas y la función CONTAR.

Luego, debe ingresar el argumento del conteo: Seleccione el rango

de datos donde se hará el conteo.

P á g i n a | 22

Pulse la tecla Enter para obtener el resultado.

Importante:

¡IMPORTANTE! Debe tener siempre en cuenta que esta función

solo se aplica sobre rango de datos numéricos. Ahora veremos la

función CONTARA.

e) FUNCIÓN CONTARA: Devuelve la cantidad de datos de un rango de

celdas llenas.

Sintaxis:

=CONTARA (Valor1; Valor2;...)

!

P á g i n a | 23

Ejercicio: Debe calcular el Nº de vendedores.

Previamente, debe ingresar en la celda C12, el texto “Nº

Vendedores” y podrá usar también el botón Insertar función para

obtener el resultado.

Selecciona la celda D12, para que insertes la función CONTARA.

P á g i n a | 24



Estadísticas y la función CONTARA.

Luego, debe ingresar el argumento del conteo: Seleccione el rango

de datos donde se hará el conteo.


P á g i n a | 25

Importante:

¡IMPORTANTE! Debe tener presente que esta función a diferencia

de contar, realiza el conteo sobre celdas que contengan cualquier

tipo de datos. Ahora veremos la función CONTAR.BLANCO.

f) FUNCIÓN CONTAR.BLANCO: Devuelve la cantidad de celdas en

blanco en un rango de celdas.

Sintaxis:

=CONTAR.BLANCO (Rango)

Ejercicio: Calcule el Nº de ventas no realizadas en el trimestre.

Previamente, debe ingresar en la celda C14, el texto “Nº Ventas no

hechas en el trimestre”, podrá usar también el botón Insertar

función para obtener el resultado.

!

P á g i n a | 26

Seleccione la celda D14, para insertar la función CONTAR.BLANCO



Estadísticas y la función CONTAR.BLANCO.

P á g i n a | 27

A continuación, debe ingresar el argumento del conteo: Seleccione

el rango de datos donde se hará el conteo. Para este ejemplo:

Todos los trimestres.


P á g i n a | 28

Importante:

¡IMPORTANTE! Debe tener presente que esta función solo cuenta celdas

vacías, para obtener su resultado.

Ha terminado con el análisis de las principales funciones estadísticas,

pasara ahora estudiar las funciones MATEMATICAS Y

TRIGONOMETRICAS del Excel.

FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS

a) FUNCIÓN SUMA: Devuelve la suma de todos los números de un rango de

celdas.

Sintaxis:

=SUMA (Número1; Número2;...)

!

4

P á g i n a | 29

Ejercicio 1: Se tiene información sobre el stock y las unidades vendidas

de los productos de una distribuidora de bebidas y se pide calcular el total

de unidades vendidas de Enero a Abril.

Como es una función muy básica, podemos escribir la función y sus

argumentos para obtener el resultado.

Seleccione la celda D10

Escriba la función y sus argumentos: = SUMA (D3:G8) y pulse

Enter.

Habrás obtenido el resultado:

P á g i n a | 30

Importante:

¡IMPORTANTE! Como debe haberse dado cuenta, hemos obtenido

el resultado escribiendo la función SUMA y sus argumentos, este

método para este caso es sencillo ya que sus argumentos son

fáciles de entender. Ahora veremos la función Entero.

b) FUNCIÓN ENTERO: Redondea un número hasta el entero inferior más

próximo.

Sintaxis

=ENTERO (Número)

Ejercicio: Calcular el valor entero del promedio de las 3 notas.

Recuerde que ya tenemos calculado el promedio de las notas, para ello

use la función Promedio.

Ahora debe editar la función Promedio para obtener el Entero del

Promedio:

Haga doble clic sobre la celda donde se calculó el promedio (F3)

!

P á g i n a | 31

Haga clic después del signo = y escriba exactamente lo que se

muestra a continuación con rojo:

=ENTERO(PROMEDIO (C3:E3))

Pulsa la tecla Enter y verás el resultado:

Finalmente, copie verticalmente la función obtenida para hallar el

promedio entero del resto de alumnos.

Importante:

Observación: Este ejercicio, muestra el valor entero en la misma

celda donde está el promedio o valor original. Ahora verá cómo al

mismo resultado no le aplicamos Entero, sino la función

REDONDEAR, notará la diferencia.

!

P á g i n a | 32

c) FUNCIÓN REDONDEAR: Redondea un número al número de decimales

especificado.

Sintaxis

=REDONDEAR (Número; núm_decimales)

Ejercicio: Ahora redondee el mismo promedio de las 3 notas a 2

decimales.

“Recuerde que al igual que en el caso anterior ya tiene calculado el

promedio de las notas, con la función Promedio”.

Ahora, debe editar la función Promedio para obtener el Promedio

Redondeado a 2 decimales:

Haga doble clic sobre la celda donde se calculó el promedio (G3)

Haga clic después del signo = y escriba exactamente lo que se

muestra a continuación con color rojo:

=REDONDEAR (PROMEDIO (C3:E3), 2)

P á g i n a | 33

Pulsa la tecla Enter y verás el resultado:

Finalmente, copie verticalmente la función obtenida para hallar el

promedio redondeado del resto de alumnos.

Importante:

Observación: Este ejercicio, muestra el valor REDONDEADO en la

misma celda donde está el promedio o valor original. Puede

comparar con el anterior promedio obtenido a través de la

función ENTERO, es diferente ya que este último promedio

considera 2 decimales del valor original, mientras que el anterior

solo la parte entera del promedio. También puede usar la función

TRUNCAR para ver la diferencia entre ENTERO, REDONDEAR Y

TRUNCAR.

!

P á g i n a | 34

d) FUNCIÓN TRUNCAR: Trunca un número a un entero, suprimiendo la

parte fraccionaria de dicho número.

Sintaxis

=TRUNCAR (Número; núm_decimales)

Ejercicio: Ahora trunca el mismo promedio de las 3 notas a 2 decimales.

“Recuerde que al igual que en el caso anterior ya tiene calculado el

promedio de las notas, con la función Promedio”.

Ahora, debe editar la función Promedio para obtener el Promedio Truncado

a 2 decimales:

Haga doble clic sobre la celda donde se calculó el promedio (G3)

Haga clic después del signo = y escribe exactamente lo que se

muestra a continuación con color rojo:

=TRUNCAR(PROMEDIO (C3:E3); 2)

Pulse la tecla Enter y verá el resultado:

P á g i n a | 35

Finalmente, copia verticalmente la función obtenida para hallar el

promedio redondeado del resto de alumnos.

Importante:

Observación: Aparentemente usando truncar y redondear es lo

mismo, pero no es así, ya que REDONDEAR muestra con 2

decimales el resultado redondeado: 14.33, y tiene en cuenta si

en el tercer decimal es igual o superior a 5, el último digito

decimal aumentaría a 4, es decir quedaría 14.34; mientras que

en TRUNCAR no se considera el valor de tercer decimal. Pruebe

ingresando otras notas y observe los resultados.

FUNCIONES DE FECHA Y HORA

Estas funciones son muy usuales cuando se realizan cálculos con fecha, por

ejemplo: Tiempos de Servicio, días de mora, etc. Es por ello que se explicará

su uso:

!

5

P á g i n a | 36

a) FUNCIÓN HOY(): Muestra la fecha que tiene su PC. Observa que esta

función no tiene argumentos.

Sintaxis:

=HOY ()

b) FUNCIÓN AHORA (): Muestra la fecha y hora que tiene la PC. Observa

que esta función no tiene argumentos.

Sintaxis:

=AHORA ()

Ejercicio: Insertar la fecha actual y la hora del sistema.

Calculando la fecha actual:

Seleccione la celda C8, para obtener la fecha actual

Escriba la función: =HOY () y pulsa la tecla Enter, verás que ya

tiene la fecha actual del sistema.

Proceso muy similar seguirás para obtener la hora actual:

Haga clic en la celda C10, y escribe la función: =AHORA () y pulsa

la tecla Enter, habrás obtenido la fecha y hora actual.

P á g i n a | 37

Pero si desea mostrar solo la hora actual, cambie el

formato a la celda C10 (donde apareció el resultado):

Seleccione la celda donde harás el cambio de formato:

C10

En la ficha Inicio, selecciona el formato de Número

Elija el nuevo formato: Hora

Importante:

¡MUY BIEN!: Lo has logrado, pero debemos practicar más,

recuerda la práctica nos hace diestros.

Visualiza los siguientes videos para complementar tus conocimientos: Funciones básicas 01(ver carpeta Videos)

Funciones básicas 02(ver carpeta (Videos)

!

P á g i n a | 38

FUNCIONES CON CRITERIO LÓGICO

Como se comentó líneas arriba, estas funciones ya utilizan criterios o

condiciones para obtener sus resultados, por ello se le llaman con criterio

lógico. ¡Empecemos!

a) FUNCIÓN CONTAR.SI: Cuenta las celdas no vacías dentro de un rango,

pero solo aquellas que cumplen el criterio especificado por ti.

Sintaxis:

=CONTAR.SI (rango, criterio)

Ejercicio: Ingrese los siguientes datos y determina el número de

empleados del Departamento de Ventas.

Calculando el Nº de empleados del Departamento de Ventas

“Aquí el criterio del conteo es que sean del Departamento de Ventas”

Seleccione la celda E17

Puede usar la biblioteca de funciones para insertar la función, para

lo cual debe seguir la secuencia mostrada en la siguiente figura:

6

P á g i n a | 39

Ahora, ingrese los argumentos solicitados, para obtener el

resultado:

Importante:

¡IMPORTANTE! Puede observar que el criterio es un texto:

“Ventas”, el cual debe ser leído en el rango de los departamentos

(B2:B15)

Haga clic en el botón Aceptar. El resultado será:

!

P á g i n a | 40

Calcular el Nº de empleados que tienen un sueldo superior a 1500

soles

“Aquí el criterio del conteo es que su sueldo sean mayor a 1500”


Puede escribir también la función, así

=CONTAR.SI(F2:F15,">1500")

P á g i n a | 41

Calcular el Nº de empleados que tienen fecha de ingreso a partir del

año 2005

“Aquí el criterio del conteo es que su fecha de ingreso sea mayor o

igual al 1 de enero del 2005”


Puede escribir también la función, así:

=CONTAR.SI(C2:C15,">=1/1/2005")

P á g i n a | 42

b) FUNCIÓN SUMAR.SI: Esta función, suma las celdas en un rango de

datos, pero solo aquellas que coinciden con el criterio establecido.

Sintaxis:

=SUMAR.SI (rango, criterio, rango_suma)

Ejercicio: Este ejercicio, explicará claramente el uso de esta función.

Se tiene el mismo cuadro anterior, pero ahora se requiere conocer el

Sueldo total de todos los empleados del Departamento de

Contabilidad.

“El criterio a usar para obtener el sueldo total de los empleados, es que

solo sean del departamento de Contabilidad”

Haga clic en la celda F21


lo cual siga la secuencia mostrada en la siguiente figura:

P á g i n a | 43

Ahora debe ingresar los argumentos solicitados, para obtener el

resultado:

Importante:

¡IMPORTANTE! Ahora observe que el criterio es una un texto:

“Contabilidad”, el cual debe ser leído en el rango de los cursos

(B2:B15), mientras que el rango_suma es el rango de las notas

(F2:F15) que será sumado al ser evaluado el criterio.

Haz clic en el botón Aceptar. El resultado completo será:

!

P á g i n a | 44

c) FUNCIÓN PROMEDIO.SI: Devuelve el promedio de todas las celdas de

un rango que cumplen un criterio que tú determines.

Sintaxis:

=PROMEDIO.SI (rango, criterio, rango_promedio)

Ejercicio: Para explicar esta función se requiere calcular el promedio de

sueldos de los empleados de que están afiliados a la AFP Profuturo.

“El criterio a usar para obtener el promedio de sueldos de los empleados

es que solo sean de la AFP PROFUTURO”



lo cual debe seguir la secuencia mostrada en la siguiente figura:

P á g i n a | 45


resultado:

Importante:

¡IMPORTANTE! Ahora observe que el criterio es un texto:

“Profuturo”, el cual debe ser leído en el rango de las AFPs

(D2:D15), mientras que el rango_promedio es el rango de los

sueldos (F2:F15) que serán promediadas al ser evaluado el

criterio.

Haz clic en el botón Aceptar. El resultado completo será:

!

P á g i n a | 46

Importante:

MUY FÁCIL ¡VERDAD! Las funciones con criterio lógico tienen un

análisis muy sencillo, espero lo vuelva a practicar, ya que

generalmente la información a obtener siempre está

condicionada.

FUNCIONES CON MÚLTIPLES CRITERIOS LÓGICOS

a) FUNCIÓN CONTAR.SI.CONJUNTO: Realiza el conteo de las celdas que

cumplen los criterios especificados, dentro del rango.

Sintaxis:

=CONTAR.SI.CONJUNTO (rango1, criterio1, rango2, criterio2…)

Para ello explicaremos esta función sobre el mismo ejercicio que venimos

trabajando. Copie el cuadro de datos a la hoja 2.

!

7

P á g i n a | 47

Ejercicio: Determine el número de empleados del Departamento de

ventas con sueldos menores a 1500 soles.


Use la biblioteca de funciones para insertar la función, para lo cual

siga la secuencia mostrada en la siguiente figura:

P á g i n a | 48


resultado:

Importante:

¡INTERPRETACIÓN! Ahora observa que el Rango_criterios1

(B2:B15), es el rango de los departamentos, Criterio1: “Ventas;

Rango_criterios2 es el rango de los sueldos (F2:F15); y Criterio2:

<1500.


!

P á g i n a | 49

Importante: SENCILLO Y FÁCIL. Se dio cuenta, ha utilizado dos criterios para contar,

cada criterio se establece en su propio rango de datos.

b) FUNCIÓN SUMAR.SI.CONJUNTO: Esta función suma las celdas de un

rango de valores, pero solo a aquellos que cumplen con varios criterios

que tú especifiques.

Sintaxis:

=SUMAR.SI.CONJUNTO(rango_suma, rango1_criterio, criterio1, rango2_criterio,

criterio2…)

Ejercicio: Determine el sueldo total de los empleados afiliados a la AFP

Profuturo y que tienen fecha de ingreso a partir del año 2006.

!

P á g i n a | 50

Haga clic en la celda F18 (combinar celdas con F19)

Use la biblioteca de funciones para insertar la función, siga la

secuencia mostrada en la siguiente figura:


resultado:

P á g i n a | 51

Importante: ¡INTERPRETACIÓN! Ahora observe que el Rango_suma (F2:F15), es el

rango de los sueldos (ellos serán sumados según los criterios

especificados), Rango_criterios1 es el rango de las AFPs (D2:D15) y

como Criterio1 se indica el nombre de la AFP que será evaluada, en este

caso Profuturo y por último; Rango_criterios2 es el rango de las fechas

de ingreso (C2:C15), en el que se ingresa 1/1/2006, es decir; todos los

que ingresaron a partir del año 2006.


!

P á g i n a | 52

c) FUNCIÓN PROMEDIO.SI.CONJUNTO

Devuelve el promedio (media aritmética) de todas las celdas que cumplen

múltiples criterios que tú especificaste

Sintaxis:

=PROMEDIO.SI.CONJUNTO (rango_promedio, rango_criterio1, criterio1,

rango_criterio2, criterio2…)

Ejercicio: Determina el sueldo promedio de los empleados

P á g i n a | 53

Haga clic en la celda F20 (combinar celdas con F21)

Use la biblioteca de funciones para insertar la función, para lo cual

debe seguir la secuencia mostrada en la siguiente figura:

P á g i n a | 54


resultado:

Importante:

¡INTERPRETACIÓN! Ahora observe que el Rango_promedio

(F2:F15), es el rango de los sueldos que serán promediados,

Rango_criterios1 es el rango de las AFPs (D2:D15) y como Criterio1 se

indica el nombre de la AFP que será evaluada, en este caso Integra y por

último; Rango_criterios2 es el rango de número de hijos (E2:E15), en el

que se indica si tiene hijos, es decir: >0.


!

P á g i n a | 55

Importante: ¡EXCELENTE! Es muy sencillo trabajar con este tipo de funciones ya que

el uso de criterios o condiciones hacen que los resultados sean

selectivos.

Ahora veremos la función Frecuencia muy importante en la tabulación de

un muestreo de datos.

d) FUNCIÓN SUMAPRODUCTO: Esta función, multiplica los componentes

correspondientes de las matrices (rangos de datos) suministrados y

devuelven la suma de esos productos.

Sintaxis:

=SUMAPRODUCTO (matriz1; matriz2; matriz3;...)

Ejercicio: Calcula la venta total del siguiente cuadro de ventas.

!

P á g i n a | 56

Ingrese los datos y la función SUMAPRODUCTO, como se indica, en la

celda B15.

Importante:

INTERPRETACIÓN! En este caso se utiliza dos rangos: cantidades y

precios unitarios. Cada cantidad se multiplica por su precio unitario

correspondiente, al final esos productos se suman dando como resultado

la venta total.

Ejercicio: Calcule el interés simple total generado por tres capitales

depositados a plazo fijo.

La fórmula del Interés Simple = Capital * Tasa * Plazo

Ingrese los siguientes datos y función según se muestra a continuación.

!

P á g i n a | 57

Importante:

INTERPRETACIÓN! En este caso se utiliza tres rangos: capitales, tasas y

plazos. Cada capital se multiplica por su tasa y plazo correspondiente, al

final esos productos se suman dando como resultado el interés total.

Visualiza los siguientes videos para complementar tus conocimientos: Funciones con criterios lógicos (ver carpeta Videos)

!

P á g i n a | 58

Práctica Desarrollada

Teniendo como información las calificaciones de un grupo de alumnos

correspondiente a la primera unidad, calcule el promedio ponderado según la

siguiente fórmula:

Ingresa los siguientes datos:

Lo primero que debe calcular es el Peso Total. En la celda F2, ingrese la

función =SUMA(B2:E2) o puede hacer uso del botón Autosuma y presionar

Enter.

Para calcular el Promedio del primer alumno, según se indica en la fórmula,

debe sumar la multiplicación de cada calificación por su peso correspondiente,

luego dividir dicha suma entre el Peso Total. Como dicha fórmula debe

copiarse hacia abajo, para obtener los promedios del resto de alumnos, las

referencias de cada uno de los pesos y el peso total, deben ser absolutas.

Entonces, la fórmula a ingresar en la celda F4, sería:

= (B4*$B$2+C4*$C$2+D4*$D$2+E4*$E$2)/$F$2

P á g i n a | 59

Pero, como puede observar la fórmula contenida dentro del paréntesis puede

ser sustituida por la función SUMAPRODUCTO, así:

=SUMAPRODUCTO (B4:E4,$B$2:$E$2)/$F$2

En donde el rango de los pesos y la referencia del peso total son absolutos.

A continuación se muestra el resultado:

P á g i n a | 60

AUTOEVALUACIÓN:

Ahora demuestre lo aprendido:

1. Responda V (Verdadero) o F (Falso), marca con “X”: V

F

a. PROMEDIO, es una función Matemática y Trigonométrica ( )

( )

b. Todas las funciones tienen argumentos ( )

( )

c. CONTAR, cuenta cualquier dato del rango especificado ( )

( )

2. Es una función que puede promediar valores numéricos

condicionados:

a. CONTAR.SI

b. PROMEDIO

c. SUMAR.SI

d. PROMEDIO.SI

3. Es una función que permite sumar valores numéricos según uno o

más criterios lógicos:

a. SUMA

b. SUMAR.SI

c. SUMAR.SI.CONJUNTO

Bibliografía Dodge, M. (2007). El Libro de Microsoft Excel 2007. España: Editorial ANAYA

MULTIMEDIA.

Walkenbach, J. (2008). La biblia de Excel 2007. España: Editorial AMAYA MULTIMEDIA.

P á g i n a | 61

Práctica de Reforzamiento

Con la presente práctica, el alumno podrá reforzar sus conocimientos en el

uso de funciones básicas y con criterios lógicos en cálculos matemáticos y

estadísticos simples y condicionales.

ACTIVIDAD 01: FUNCIONES MATEMATICAS Y ESTADISTICAS

A. CASO 01: FUNCION SUMA, PROMEDIO, MIN, MAX

Ejercicio:

Del reporte de ventas anual de tres productos, determina los cálculos que

se indican en la hoja de cálculo.

CONSIGNA:

1) Calcular el TOTAL MENSUAL.

Situarse en la celda E4 e inserte la función SUMA con los

argumentos solicitados: =SUMA (B4:D4). Copie la función

obtenida hasta la celda E15.

2) Calcular el PROMEDIO MENSUAL.

Situarse en la celda F4 e inserte la función PROMEDIO con

los argumentos solicitados: =PROMEDIO (B4:D4). Copie la

función obtenida hasta la celda F15.

3) Calcular el TOTAL ANUAL POR PRODUCTO.

Situarse en la celda B16 e inserte la función SUMA con los

argumentos solicitados: =SUMA (B4:B15). Copie la función

obtenida hasta la celda D16.

4) Calcular el TOTAL ANUAL POR PRODUCTO.

UNIDAD

3 Actividad de Aprendizaje

P á g i n a | 62

Situarse en la celda B17 e inserte la función PROMEDIO con

los argumentos solicitados: =PROMEDIO (B4:B15). Copie la

función obtenida hasta la celda D17.

5) Calcular el MINIMO ANUAL POR PRODUCTO.

Situarse en la celda B18 e inserte la función MIN con los

argumentos solicitados: =MIN (B4:B15). Copie la función


6) Calcular el MAXIMO ANUAL POR PRODUCTO.

Situarse en la celda B19 e inserte la función MIN con los

argumentos solicitados: =MIN (B4:B15). Copie la función


7) Calcular la VENTA TOTAL TRIMESTRE 1.


argumentos solicitados: =SUMA (E4:E6).










11) Calcular la VENTA ANUAL DE LOS 3 PRODUCTOS.

Situarse en la celda G21 e inserte la función SUMA con los

argumentos solicitados: =SUMA (B16:D16).

12) Calcular la VENTA PROMEDIO DE LOS 3 PRODUCTOS.

Situarse en la celda G22 e inserte la función PROMEDIO con

los argumentos solicitados: =PROMEDIO (B17:D17).

13) Calcular la VENTA MINIMA DE TORNILLOS Y PERNOS.

Situarse en la celda H24 e inserte la función MIN con los

argumentos solicitados: =MIN (B4:B15, D4:D15).

14) Calcular la VENTA MAXIMA DE LOS TRIMESTRES 1 Y 3.

Situarse en la celda H25 e inserte la función MAX con los

argumentos solicitados: =MAX (B4:D6, D10:D12).

P á g i n a | 63

B. CASO 02: FUNCION CONTARA, CONTAR,CONTAR.BLANCO

Ejercicio:

A continuación se muestra una relación de alumnos de Computación II con

sus calificaciones correspondientes. Determine los cálculos que se piden

en la parte inferior.

CONSIGNA: A. Calcular el NRO DE ALUMNOS MATRICULADOS.

Situarse en la celda B21 e inserte la función CONTARA con

los argumentos solicitados: =CONTARA (A5:A19).

B. Calcular el NRO DE CALIFICACIONES REGISTRADAS.

Situarse en la celda B22 e inserte la función CONTAR con

los argumentos solicitados: =CONTAR (B5:E19).

C. Calcular el NRO DE CALIFICACIONES NO REGISTRADAS.

Situarse en la celda B23 e inserte la función

CONTAR.BLANCO con los argumentos solicitados:

=CONTAR.BLANCO (B5:E19).

RUBRICA DE EVALUACIÓN

CRITERIOS Deficiente

(0) Regular

(2.5) Bueno

(5)

CASO 01: Crea resúmenes estadísticos

utilizando funciones matemáticas y estadísticas.

No crea nada.

Aplica las

funciones

estadísticas y matemáticas, sin comprender los argumentos solicitados por dicha función.

Aplica y

comprende

correctamente el uso de los argumentos en las funciones estadísticas y matemáticas.

CASO 02: Crea resúmenes estadísticos utilizando funciones matemáticas y estadísticas

No crea nada.

Aplica las funciones estadísticas y matemáticas, sin

comprender los argumentos solicitados por dicha función.

Aplica y comprende correctamente el uso de los

argumentos en las funciones estadísticas y matemáticas.

TOTAL ACTIVIDAD 1 0 puntos 5 puntos 10 puntos

P á g i n a | 64

ACTIVIDAD 02: FUNCIONES MATEMATICAS Y ESTADISTICAS

CONDICIONALES

A. CASO 01: FUNCION CONTAR.SI, SUMAR.SI, PROMEDIO.SI

Ejercicio:

A continuación se muestra la relación de participantes que han

desarrollado un Taller de capacitación, en un determinado Turno y Lugar.

Se desea elaborar cuadros estadísticos para analizar dicha capacitación

(Ver cuadros en la hoja de calculo).

CONSIGNA:

1. Calcular el NRO PARTICIPANTES DE SEXO MASCULINO.

Situarse en la celda D30 e inserte la función CONTAR.SI con

los argumentos solicitados: =CONTAR.SI (D7:D26,”M”).

2. Calcular el NRO PARTICIPANTES DE SEXO FEMENINO.

Situarse en la celda D31 e inserte la función CONTAR.SI

con los argumentos solicitados: =CONTAR.SI (D7:D26,”F”).

3. Calcular el TOTAL PRECIO DE BIJOUTERIA.

Situarse en la celda I30 e inserte la función SUMAR.SI con

los argumentos solicitados:

=SUMAR.SI (E7:E26,” BIJOUTERIA”, F7:F26)

4. Calcular el TOTAL PRECIO DE CARPINTERIA.

Situarse en la celda I31 e inserte la función SUMAR.SI con

los argumentos solicitados:

=SUMAR.SI (E7:E26,” CARPINTERIA”, F7:F26)

5. Calcular la EDAD PROMEDIO DE LOS PARTICIPANTES MUJERES.

Situarse en la celda D34 e inserte la función PROMEDIO.SI

con los argumentos solicitados:

=PROMEDIO.SI (D7:D26,” F”, C7:C26)

6. Calcular el PRECIO PROMEDIO DEL TURNO MAÑANA.

Situarse en la celda D35 e inserte la función PROMEDIO.SI

con los argumentos solicitados:

=PROMEDIO.SI (G7:G26,” MAÑANA”, F7:F26)

P á g i n a | 65

B. CASO 02: FUNCION CONTAR.SI.CONJUNTO, PROMEDIO.SI.CONJUNTO

Ejercicio:

De la lista de clientes morosos, determine el número de clientes y deuda

promedio, según los criterios indicados.

CONSIGNA: 1. Calcular el NRO CLIENTES DE SEXO FEMENINO DE TRUJILLO.

Situarse en la celda E18 e inserte la función

CONTAR.SI.CONJUNTO con los argumentos solicitados:

=CONTAR.SI.CONJUNTO(C2:C16,”F”,B2:B16,”TRUJILLO”)

2. Calcular el NRO CLIENTES DE LAREDO CON DEUDAS MAYORES A

3000


CONTAR.SI.CONJUNTO con los argumentos solicitados:

=CONTAR.SI.CONJUNTO(B2:B16,”LAREDO”,D2:D16,”>3000”)

3. Calcular la DEUDA PROMEDIO DE LOS CLIENTES DE SEXO

MASCULINO QUE VIVEN EN TRUJILLO


PROMEDIO.SI.CONJUNTO con los argumentos solicitados:

=PROMEDIO.SI.CONJUNTO(D2:D16,C2:C16,”M”,B2:B16,”TRUJILLO”)

C. CASO 03: FUNCION SUMAR.SI.CONJUNTO

Ejercicio:

El cuadro de la izquierda, muestra el reporte de ventas de dos vendedores

de una tienda comercial. Se pide hallar las ventas totales y promedio,

según ciertos criterios condicionales.

CONSIGNA:

1. Calcular la VENTA TOTAL DE JUAN REYES EN

ELECTRODOMESTICOS.


SUMAR.SI.CONJUNTO con los argumentos solicitados:

=SUMAR.SI.CONJUNTO(D2:D22,B2:B22,”JUAN

REYES”,C2:C16,”Electrodomésticos”)

P á g i n a | 66

2. Calcular la VENTA TOTAL DE LUIS CASTRO EN COMPUTACION CON

IMPORTES SUPERIORES A 200.


SUMAR.SI.CONJUNTO con los argumentos solicitados:

=SUMAR.SI.CONJUNTO(D2:D22,B2:B22,”LUIS

CASTRO”,C2:C16,”Computación”,D2:D22,”>200”)

RUBRICA DE EVALUACIÓN

CRITERIOS Deficiente

(0) Regular

(1.5) Bueno

(3)

CASO 01:

Crea resúmenes estadísticos utilizando funciones

matemáticas y estadísticas condicionales.

No crea nada.

Crea aplicaciones

matemáticas y estadísticas sin condiciones

Aplica correctamente las funciones estadísticas y

matemáticas condicionales.

CASO 02: Crea resúmenes estadísticos utilizando funciones

matemáticas y estadísticas condicionales.

No crea nada.

Crea aplicaciones matemáticas y estadísticas sin condiciones

Aplica correctamente las funciones estadísticas y matemáticas condicionales.

CASO 03: Crea resúmenes estadísticos

utilizando funciones matemáticas y estadísticas condicionales.

No crea nada

Crea aplicaciones matemáticas y estadísticas sin condiciones

Aplica correctamente las funciones estadísticas y matemáticas condicionales.

TOTAL ACTIVIDAD 2 0 puntos 4.5 puntos 9 puntos

PUNTUALIDAD UNIDAD 03 1 punto

http://www.ucv.edu.pe/cis/

[email protected]

funciones basicas de excel

Documents