Plantilla de personal

Centro de Investigación Operativa - UMH

    Objetivos

    Enunciado

    Solución


    Objetivos

    Utilizar y operar con funciones y datos del tipo fecha

    Utilizar funciones de conversión de datos.

    Utilizar y calcular datos del tipo porcentual.

    Utilizar la función CONTAR.SI para contar el número de elementos que cumplen una condición.

    Utilizar la función FRECUENCIA para calcular la frecuencia de aparición de datos dentro de un rango y saber introducir fórmulas que proporcionan resultados matriciales.

    Enunciado

    La empresa de ventas de móviles Moviphone dispone de un nuevo comercio en la ciudad de Elche. Los empleados que trabajan en la empresa realizan el trabajo según la sección en la que estén contratados: A: Administración, V: Ventas y T: Técnicos.

    Los datos de los empleados de la nueva oficina son los siguientes

    El responsable de la oficina desea conocer la distribución de la plantilla (nº de empleados) por

    1. sección
    2. grupo de edad.

    Atendiendo a los siguientes grupos de edad: Hasta 25 años, de 26 a 35, de 36 a 45, de 46 a 60 y más de 60 años.

    Para calcular la edad de una persona se realizará a partir de la fecha de nacimiento tomando por defecto la fecha que tenga el ordenador configurada como la de hoy. Si el resultado es, por ejemplo, que una persona tiene 30 años y 6 meses, se asume que la persona tiene 30 años.

    En ambos casos, se desea conocer además el porcentaje respecto al número total de empleados.

    Solución

    El aspecto inicial de la hoja de cálculo es el siguiente

     

    1.- Distribución por sección

    Para contar el número de empleados, podemos hacer uso de la función CONTAR.SI, con lo cual en I5 tendremos

    =CONTAR.SI(C4:C17;G5)

    esto es, contamos el nºtotal de celdas del rango C4:C17 que pertenecen a la sección Administración, o lo que es lo mismo cuyo valor coincide con el contenido en G5, en este caso 'A'.

    Para calcular el nºempleados del resto de secciones se utiliza la misma función pero tomando la sección correspondiente. Si copiamos la fórmula de I5 en I6 y I7, tendremos previamente que fijar las filas de las celdas del rango del que contamos los valores. Con lo cual, la fórmula final en I5 es

    =CONTAR.SI(C$4:C$17;G5)

    Ahora ya podemos copiar la fórmula en I6,I7.

    Para calcular el total de empleados en I9, sumamos los valores I5:I7.

    =SUMA(I5:I7)

    y copiamos la fórmula en J9.

    Finalmente, calculamos los valores del porcentaje. Cuando se calculan porcentajes hay que tener en cuenta cuál es el formato que tiene la celda. Si es del tipo número o general, el porcentaje se calcula multiplicando el nºelementos/nºtotal por 100.

    Si la celda es del tipo porcentaje, o aparece el símbolo '%' escrito en la fórmula no hay que multiplicar por 100.

    Así en el primer caso tendremos en J5

    =I5/I$9*100

    y copiamos en J6 y j7. El resultado es el mostrado en la figura

    En el segundo caso, elegimos primero el formato del tipo porcentaje. Para ello, seleccionamos las celdas y pulsamos el botón Estilo porcentual de la barra de herramientas Formato y el número de decimales lo establecemos pulsando , o bien, elegimos en el menú Formato la opción Porcentaje especificando el número de decimales, por ejemplo 2 decimales. La fórmula en J5 es

    =I5/I$9

    y aspecto final de la distribución por secciones es

     

    2.- Distribución por grupos de edad

    Calculamos primero la edad de cada empleado. Introducimos en I1 la fecha que el ordenador tiene configurada como la de hoy.

    =HOY()

    Para calcular la edad de una persona calculamos la diferencia en días entre la fecha de hoy y la fecha de nacimiento y dividimos entre el nº de días de un año estándar (365). Así introducimos en E4 la fórmula

    =(I$1-D4)/365

    y copiamos en E5:E17.

    ¿Qué ocurre con los resultados de la fórmula?

    Puede ocurrir que al operar sobre datos del tipo fecha, el resultado de la operación lo proporcione en formato fecha. Para modificarlo en el menú Formato elegimos la opción General o Número.

    Por otro lado, hay que tener en cuenta que aunque en una celda podemos especificar el número de decimales que se muestran, internamente si el valor que contiene la celda tiene decimales se trabajará con este valor. Por ejemplo, si una celda contiene el valor 25,6 y obligamos a que no se muestren las cifras decimales se mostrará en la celda un 26, realizando un redondeo. Sin embargo, si hacemos referencia al valor contenido en la celda para futuras operaciones, se toma el valor con decimales, es decir, 25,6.

    Para conseguir que un número no tenga cifras decimales pueden utilizarse diversas funciones de conversión como ENTERO,TRUNCAR,REDONDEAR.

    La función ENTERO(Número) redondea un número hasta el entero inferior más próximo. Número es el número real que desea redondear al entero inferior más próximo. Así, ENTERO(8,9) es igual a 8 y ENTERO(-8,9) es igual a –9.

    Aplicándo esta función al ejemplo, modificamos E4

    =ENTERO((I$1-D4)/365)

    y copiamos en E5:E17.

     

    Para calcular el nº personas por grupos de edad utilizamos la función FRECUENCIA. La sintaxis es la siguiente

    FRECUENCIA(datos;grupos)

    Esta función calcula la frecuencia con que se repiten los valores de un rango y devuelve un matriz vertical de números. Por ejemplo, se puede utilizar la función para contar el número de los resultados que se encuentran dentro de un rango. Debe introducirse como una fórmula de matrices debido a que FRECUENCIA devuelve una matriz.

    Observaciones

     

     

    En este ejercicio, los datos son los de las edades de las personas (E4:E17) y los grupos son los indicados en H14:H17. Los pasos para introducir la función FRECUENCIA son:

    1.- Seleccionar las celdas en las que va el resultado de la función FRECUENCIA, que siempre será un número más que el nºceldas de los grupos.

    2.- Introducimos la función

    =FRECUENCIA(E4:E17;H14:H17)

    3.- En lugar de pulsar INTRO. Pulsamos la combinación de teclas Ctrl+Shift+Intro. Automáticamente la fórmula aparece escrita entre llaves.

    Hay que tener en cuenta que el paso 3 es imprescindible para el correcto funcionamiento de la función y que no es equivalente escribir directamente las llaves en la celda. Con lo cual, si no se ha realizado alguno de los pasos 1-3 hay que borrar la fórmula completa y empezar en el paso 1.

    En Video en formato Flash o Video en formato Windows Media Player se puede ver un vídeo en el que se muestran los pasos necesarios para introducir la función.

    Para calcular los valores correspondientes al Total y al Porcentaje se utilizan fórmulas similares al caso de la distribución por secciones pero cogiendo las celdas correspondientes.

    Así, para calcular el Total introducimos en I20

    =SUMA(I14:I18)

    y copiamos en J20.

    Para el Porcentaje, aplicamos el formato Porcentual con 2 cifras decimales a las celdas J15:J18,J20. Introducimos en J14

    =I14/I$20

    y copiamos en J15:J18.

    El resultado final de la hoja de cálculo es el siguiente

    plantillapersonal.zip