Fórmulas en Excel para el cálculo de ausentismo en archivo de asistencia

Ya en la entrada anterior sobre cómo hacer un archivo de asistencia en Excel explicamos cómo elaborar nuestra tabla para el control de ausentismo diario. En esta entrada explicaremos que fórmulas utilizar para hacer un cálculo automático del % de ausentismo diario y % de ausentismo mes.

Las fórmulas que utilizaremos son algo complejas pero procuraremos explicar bien su funcionamiento.

Les recordamos que para insertar una formula en Excel, deben posicionarse sobre la celda donde quieren insertar la formula (en la celda que aparecerá el resultado) luego pueden ir al menú Fórmula y luego hacer click en el primer ícono que dice Insertar Función, cada grupo de formulas se encuentra dentro de una categoría, la que aparece por defecto es Usadas recientemente, pero si desplegamos podemos ver el resto de las categorías como: Financieras, estadísticas etc …      Ver Excel para principiantes

Fórmulas que utilizaremos:

CONTAR.SI (se encuentra dentro de la categoría Estadísticas): Esta fórmula cuenta las celdas que coincidan con la condición dada dentro de un Rango. En nuestro caso la condición dada son las letras que le asignamos a cada caso en la leyenda de la tabla, Ejemplo AI para inasistencias, SSO para reposos, esta fórmula nos contará la cantidad de veces que aparezca AI dentro del rango que seleccionemos, el rango es todo el espacio que tenemos en la tabla para colocar la información.

Contarsi

SUMA (se encuentra dentro de la categoría Matemáticas y Trigonometría): Esta fórmula suma todos los números que se encuentren en el rango de celdas que seleccionemos.

Suma

Fórmulas hechas a mano: Utilizaremos otras fórmulas para sumar, restar o dividir pero estas las haremos de forma manual sumando o dividiendo por nombre de Celda. Ejemplo:  = C1 + C2

Recuerden que el nombre de la celda viene dado por la letra de la columna y el número de fila, en este caso C1 significa columna C fila 1.

Hagamos el archivo con fórmulas.

Hasta ahora nuestro archivo de asistencia se debe ver así:     Ver  Como hacer un Archivo de asistencia en Excel

Asistencia Lista

Vamos a agregar otras columnas para colocar las fórmulas, Agregamos al final de los días del mes una columna para contar las inasistencias (todas justificadas o no) y otra columna para contar la cantidad de días que el ejecutivo debió asistir. Sugerimos ver la entrada  Medición de Ausentismo  para entender este cálculo.

Se debe ver así: en esta imagen también mostramos como se ve después de que le hicimos click en insertar función.

insertar contar si

Nos paramos sobre la celda donde queremos el resultado para insertar la función ahora debemos seleccionar el rango, Es decir de donde a donde vamos a contar los AI. Sombreamos las celdas que componen el rango:

Ubicamos el cursor sobre el campo Rango dentro del cuadro de dialogo y hacemos click, luego seleccionamos o sombreamos las celdas que deseamos como rango, en este caso es desde la J3 hasta la AL3

seleccion de rango

Luego seleccionamos el criterio: Aquí hay varios puntos importantes.

  • Por defecto el cursor se queda en el campo de rango dentro del cuadro de dialogo, deben hacer clic sobre el espacio de criterio antes de hacer la selección.
  • Seleccionamos el criterio que deseamos contar en nuestro cuadro de leyenda, en esta caso estamos contando los AI. (podemos hacer click sobre la celda donde está escrito AI o poner el valor de la celda a mano)
  • Luego de seleccionar el criterio (AI) en este caso es la celda E28 la bloqueamos (bloquemos la celda para que no se corra cuando corramos la fórmula) apretando  F4 en el teclado.

Debe verse así:

seleccion de criterio

Hacemos click en aceptar y ya tenemos lista nuestra fórmula para contar los AI, pero solo nos está contando los AI, y también debemos contar los SSO (reposos) PR (Permisos) y todo lo que sea inasistencia.

contar listo

Para hacer eso modificamos la formula a mano de una forma sencilla.

Nos paramos sobre la barra de función en la imagen se muestra cual es, y copiamos la formula sin copiar el signo = (Igual)

barra de funcion 1

Luego agregamos un signo + para sumar las formulas y al lado del signo pegamos lo que copiamos

barra de funcion 2

Fíjense que ambas fórmulas son iguales, hasta ahora esta fórmula nos está contando la cantidad de AI que hay en el rango de J3 hasta AL3 y el resultado lo está sumando con la cantidad de AI que hay desde J3 hasta AL3. Pero no queremos que sume la cuenta de AI con la Cuenta de AI sino que Sume la Cuenta de AI con la Cuenta de SSO más la cuenta de PR y así sucesivamente.

Por lo que debemos cambiar el criterio en las formulas que peguemos como lo muestra la siguiente imagen.

barra de funcion 3

Fíjense que hemos cambiado E28 por E30 en la segunda fórmula o la que pegamos, asignamos E30 por que es donde se encuentra el criterio de SSO en nuestra Leyenda. Con esto nos sumara los AI + los SSO.  También dense cuenta que no borramos los signos $, ya que estos son los que mantienen bloqueada la celda.

Para sumar el resto de los criterios de inasistencia solo tenemos que repetir el procedimiento, copiar la formula, agregar el signo + y cambiar el criterio en la que peguemos, al apretar la tecla ENTER se actualiza el resultado.

Se verá así cuando lo hagan.

barra de funcion 4

Una vez que ya hemos contado todos los criterios de inasistencias solo tenemos que copiar esa fórmula y pegarla en el resto de las celdas. (un resultado por cada fila)

Para esto, nos paramos sobre la celda que tiene la formula, la copiamos, luego seleccionamos donde queremos pegarla y hacemos click derecho en el mouse y le decimos pegar

pegar formula

pegar formula 2

Ahora nos está contando las inasistencias de todo el mes  de cada ejecutivo, en la próxima entrada veremos cómo contar los días que debió laborar y luego como calcular el ausentismo.

Con este archivo se nos irá actualizando el ausentismo del mes día a día.

Síguenos en facebook.com/gestiondecallcenter haciendo clic en Me Gusta y entérate de nuevas publicaciones

Puede que te interese Leer sobre:

 

Etiquetado con: , , ,
Publicado en: Preparación en Excel 2007