1
EXCEL
EJERCICIO 30
OTRAS FUNCIONES DE EXCEL; CONTAR Y SUMAR; K.ESIMO MAYOR Y
MENOR
El Excel (y cualquier programa de hoja de cálculo) cuenta con numerosísimas fun-ciones, muchas de las cuales son desconocidas para la mayoría de usuarios o, si se co-nocen, apenas se usan. Es imposible abarcarlas todas en un solo curso (y aún más, en una sola evaluación), pero no está de más echar un vistazo a algunas de las más útiles.
Es lo que haremos en este ejercicio y el siguiente, mostrando un ejemplo de aplica-ción de cada una de ellas.
FUNCIONES DE CUENTA (CATEGORÍA: ESTADÍSTICAS)
Ya hemos visto antes la función CONTAR (ejercicio 5). Dicha función únicamente cuenta los valores numéricos del rango seleccionado. Es una utilidad muy limitada, por lo que dicha función ha de complementarse con otras del mismo estilo:
CONTARA
Cuenta todos los valores de un rango, no sólo los números. No cuenta, en cambio, las celdas en blanco del rango seleccionado.
EJEMPLO-ACTIVIDAD
Suponiendo que tenemos una lista de alumnos y calificaciones con el siguiente formato:
ALUMNO A
5,6 7,3
ALUMNO B
2,8
ALUMNO C
9,2 7,5
Nº de notas 5
2
Ahora, partamos de la siguiente lista de alumnos y calificaciones:
Notas grupo A
8,25 No presentado
6,8 5,3 Convalidado
9,5
Nº de alumnos 6
Dado que el rango que contiene las notas incluye tanto números como texto, será necesario usar la función CONTARA:
PROCEDIMIENTO
Es el mismo en ambas funciones.
Abre un nuevo libro de Excel y llámalo 30ex Funciones varias.xlsx. Llama a la pri-mera hoja Funciones de cuenta. En dicha hoja, realiza todas las actividades relativas a las funciones de cuenta (CONTAR, CONTARA, CONTAR.BLANCO, CONTAR.SI, CON-TAR.SI.CONJUNTO).
Sitúate en la celda de resultado. Activa el asistente para funciones y, en la catego-ría Estadísitcas, haz clic en CONTAR o CONTARA según el caso.
En el primer argumento (Valor1) selecciona el rango cuyos elementos quieras con-tar. Si no hay más rangos, acepta.
CONTAR.BLANCO
3
EJEMPLO-ACTIVIDAD
Supongamos que tenemos un registro en el que vamos apuntando los pedidos re-cibidos de un determinado artículo:
Artículo
F-321
Nº pedido
Fecha recepción
2 4-3-2012
13 14-3-2012
17
22 25-3-2012
24
Nº de pedidos pendientes
Usa la función CONTAR.BLANCO para obtener el nº de pedidos realizados y no re-cibidos. El procedimiento es el mismo que para CONTAR y CONTARA.
Ahora añadiremos un nivel de complicación. Tenemos el mismo registro de antes pero con una columna más que muestra el nº de unidades recibidas del artículo en cada pedido. En los casos en que el pedido sea defectuoso y se haya devuelto, en dicha columna aparecerá DEVUELTO. En algunos pedidos ya recibidos, todavía no se ha in-troducido el nº de unidades.
Artículo
F-321
Nº pedido
Fecha recepción
Nº unidades
2 4-3-2012 230
13 14-3-2012 DEVUELTO
17 17-3-2012
22 25-3-2012 320
24 2-4-2012
Nº de pedidos recibidos y no devueltos
Para obener lo que nos piden, tendremos que combinar las funciones CONTAR y
4
CONTAR.SI
Sólo cuenta los valores de un rango que cumplen una determinada condición.
EJEMPLO-ACTIVIDAD
En una lista de trabajadores y su antigüedad en la empresa:
Trabajador
Años de antigüedad
García, José Mª 17
Santos, Antonia 8
Gomila, Juana 12
González, Arturo 5
Trabajadores con más de 10 años de antigüedad
Utiliza la función CONTAR.SI para obtener el nº de trabajadores con más de 10 años de antigüedad en la empresa.
PROCEDIMIENTO-EXPLICACIÓN
La función tiene dos argumentos:
- Rango: el rango cuyos valores queremos contar.
- Criterio: es la condición que han de cumplir las celdas a contar. El formato se muestra con unos ejemplos:
o “=ENTREGADO”: cuenta las celdas en las que aparezca esta palabra
o “>10”: cuenta las celdas en las que el valor sea superior a 10
o “<=12-4-2012”: cuenta las celdas en las que la fecha sea el 12 de abril de 2012 o una fecha anterior.
CONTAR.SI.CONJUNTO
Se emplea cuando se necesita introducir más de un criterio o condición de cuenta.
EJEMPLO-ACTIVIDAD
5
Fecha
Nº de espectadores
1-3-2012 83
2-3-2012 103
3-3-2012 97
4-3-2012 72
Más de 90 esp antes del 4
marzo
Utiliza la función CONTAR.SI.CONJUNTO para obtener el nº de veces que asistieron más de 90 espectadores antes del día 4 de marzo.
También sirve esta función para contar valores repetidos en dos listas.
En la siguiente tabla se muestran las calificaciones obtenidas por dos alumnos en tres módulos distintos del ciclo de Administración y Finanzas:
MÓDULO 1
MÓDULO 2
MÓDULO 3
Vidal, Santiago 5 2 8
Oliver, Petra 3 7 6
Nº de módulos aprobados por am-bos alumnos
Utiliza la función CONTAR.SI.CONJUNTO para obtener el nº de módulos que han aprobado ambos alumnos.
PROCEDIMIENTO-EXPLICACIÓN
La función tiene la siguiente estructura de argumentos:
- Rango 1: rango de celdas en el que ha de cumplirse (o no) la primera condi-ción (en nuestro caso, las celdas con las fechas, salvo la de encabezado, claro) - Condición 1: con el mismo formato visto en la función CONTAR.SI (en nuestro
caso, la condición es que la fecha sea anterior al 4 de marzo)
- Rango 2: rango en que ha de cumplirse (o no) la 2ª condición (en nuestro ca-so, las celdas con las cifras de espectadores)
- Condición 2: en el ejemplo, la 2ª condición es que el nº de espectadores sea superior a 90.
6
FUNCIONES DE SUMA (CATEGORÍA: MATEMÁTICAS)
SUMAPRODUCTO
Multiplica, celda por celda, los valores de los rangos seleccionados (hasta un máximo de 255 rangos) y suma los resultados.
EJEMPLO-ACTIVIDAD
En el inventario de un almacén figura la referencia del artículo, el nº de uniddes del mismo en el almacén y su precio unitario. Queremos calcular el valor total de todas las mercancías almacenadas.
REFERENCIA
Nº UNIDADES
PRECIO UNITARIO
F-33 205 506 €
B-301 625 254 €
C-88 140 180 €
H-23 256 355 €
J-310 320 420 €
VALOR TOTAL MERCANCÍAS
Utiliza la función SUMAPRODUCTO para obtener el valor total de las mercancías.
PROCEDIMIENTO-EXPLICACIÓN
Los argumentos de esta función serán los rangos cuyas celdas queramos multiplicar (para luego sumar los resultados).
P, ej, =SUMAPRODUCTO(A1:A20;B1:B20) multiplica A1 por B1, A2 por B2 y así has-ta la fila 20 y, luego, suma los resulhas-tados de esas multiplicaciones.
Obviamente, también podríamos hacerlo en dos pasos: multiplicar fila a fila y luego sumar. Pero para eso existen las funciones: para abreviar operaciones.
SUMAR.SI
Es semejante a CONTAR.SI sólo que en esta ocasión no contamos celdas sino que sumamos sus valores. Sin embargo, esta función es más flexible ya que permite obte-ner la condición de un rango distinto al que estamos sumando.
EJEMPLO-ACTIVIDAD
7
REFERENCIA CATEGORÍA
UNIDADES
A13 CONSERVAS 230
B18 BEBIDAS 415
D45 CONSERVAS 218
A22 POSTRES 525
CONSERVAS (nº uddes.)
Usa la función SUMAR.SI para sumar sólo las unidades de los artículos de categoría CONSERVAS.
Procedimiento-explicación
La función consta de 3 argumentos:
- Rango: es el rango de celdas al que se refiere la condición (en nuestro caso, las celdas de la columna CATEGORÍA
- Criterio: la condición que ha de cumplirse en el rango anterior (en nuestro caso, que se trate de la categoría CONSERVAS). El formato de la condición es el mismo ya explicado para la función CONTAR.SI
- Rango_suma: el rango de celdas a sumar (en el ejemplo, las celdas de la co-lumna UNIDADES)
SUMAR.SI.CONJUNTO
Se usa para lo mismo que SUMAR.SI, pero cuando ha de cumplirse más de una condición.
EJEMPLO-ACTIVIDAD
Dada la siguiente relación:
REFERENCIA
CATEGORÍA
PRECIO UNITARIO UNIDADES
A13 CONSERVAS 55 € 230
B18 BEBIDAS 40 € 415
D45 CONSERVAS 36 € 218
A22 POSTRES 22 € 525
F50 APERITIVOS 35 € 612
H32 CONSERVAS 12 € 120
8
Usa la función SUMAR.SI.CONJUNTO para obtener el total de unidades de artículos de categoría CONSERVAS cuyo precio unitario sea superior a 30 euros.
Procedimiento-explicación
En esta función, al contrario que en SUMAR.SI, el rango a sumar es el primer argumen-to:
- Rango_suma: rango cuyas celdas se quieren sumar (en nuestro ejemplo, las celdas de la columna UNIDADES)
- Rango_criterios1: el rango al que se refiere la primera condición. Los siguien-tes argumentos van apareciendo a medida que introducimos el que les pre-cede (en nuestro caso, las celdas de la columna CATEGORÍA
- Criterio1: condición que se ha de cumplir en el rango_criterios1 (en nuestro caso, que la categoría sea CONSERVAS)
- Rango_criterios2: el rango a que se refiere la segunda condición (en el ejem-plo, las celdas de la columna PRECIO UNITARIO)
- Criterio2: condición que se ha de cumplir en el rango_criterios2 (en el ejerci-cio, que el precio unitario sea superior a 30 euros.
- Etc.
FUNCIONES K.ESIMO MAYOR y K.ESIMO MENOR(CATEGORÍA: ESTADÍSTICAS)
Estas funciones de nombre tan extraño son un complemento muy útil a las funcio-nes MAX y MIN ya vistas en ejercicios anteriores.
Como es lógico, en un análisis estadístico no siempre nos interesará sólo el valor máximo o el mínimo de una lista. Es probable que lo que queramos es obtener el 4º, 5º o 6º valor más alto o más bajo. O bien la suma de los 3 valores más altos, por poner un ejemplo.
Nota: el nombre de la función se explica porque la función puede mostrar el vi-gésimo, trivi-gésimo, etc mayor o menor valor de un rango (la K sustituye al vig, trig, etc). Aunque, claro, como decíamos, también puede darnos el segundo, tercer o cuarto valor más alto o más bajo.
EJEMPLO-ACTIVIDAD
9
ALUMNO/A NOTA
A 5,5
B 4
C 8
D 6,5
E 9,25
2ª nota más alta
2ª nota más baja
Promedio de las 3 notas más altas
Procedimiento-explicación
Los argumentos de la función son:
- Matriz: rango en el cual queremos buscar el k mayor o menor valor.
- K: si buscarmos el 2º mayor o menor valor, K será 2; si el 3º, será 3, y así su-cesivamente.
En el caso del promedio de las 3 notas más altas, tendríamos que anidar 3 funcio-nes K.ESIMO.MAYOR como argumentos (cada una, un argumento) de la función PRO-MEDIO1.
ACTIVIDAD
Vuelve a descargar de la web el archivo 16ex Plantillas. Cambia el nombre a 30ex Análisis estadístico.
En la hoja Datos personales, ordena la lista por departamento y, a continuación, borra el contenido de las siguientes filas, que representan empleados dados de baja definitiva en la empresa: 10, 23, 27, 32 y 35.
Luego, emplea las funciones de cuenta para obtener los siguientes conceptos (en algunos casos hay que combinar funciones):
- El nº de trabajadores de los departamentos de COMPRAS Y ALMACÉN y CON-TAB. Y TES.
- El nº de trabajadores dados de baja definitiva en la empresa.
- El nº total de trabajadores que la empresa ha tenido, incluyendo los dados de baja.
- El nº de trabajadores del grupo de cotización nº 4
1
10
- El nº de trabajadores dados de alta con posterioridad al 10 de diciembre de 2011 que pertenezcan al departamento de COMPRAS Y ALMACÉN.
- El nº de trabajadores que o bien pertenezcan al departamento de RRHH o al grupo de cotización 6.
A continuación, en la hoja Datos económicos, elimina, en primer lugar, la colum-na G (Retención practicada). Luego,utiliza las funciones de suma vistas para obtener los siguientes conceptos:
- El total de retención por IRPF practicada (en un solo paso)
- La suma de los salarios base de los empleados cuyo primer apellido empieza por B.
- La suma de las cotizaciones a la SS inferiores a 80 euros practicadas a em-pleados cuyo sueldo base sea superior a 800 euros.
- La suma total pagada por plus de transporte, menos el total pagado por ese concepto a los empleados que cobren un plus de antigüedad menor a 10 eu-ros
Finalmente, en la misma hoja Datos económicos utiliza las funciones K.ESIMO.MAYOR y K.ESIMO.MENOR para obtener:
- Los dos salarios base más altos. - El 2º y el 4º salario líquido más bajo
- La suma de la 3ª, la 4ª y la 6ª cotizaciones a la SS más altas practicadas.
