jueves, 19 de diciembre de 2013

Reparto equitativo con Excel.

En respuesta a un lector, plantearé una forma cómoda de realizar un reparto entre un número determinado previamente:

...en mi trabajo llegan diariamente cierto número de sobres que se reparten entre cierto número de trabajadores.
Por ejemplo 100 sobres para 10 ejecutivos, a veces más, a veces menos. La idea es que de la lista en Excel de lo que llegó, que está en la Columna A, excel pueda repartir de forma automática mediante una fórmula en la columna B a quien le toca proporcionalmente, por ejemplo si hay 5 ejecutivos y 20 sobres que Excel pueda asignarlo así:
Número de operaciones por ejecutivo = 4

Columna A Columna B (fórmula)
506010 Ejecutivo A Aquí Excel debería de saber que 4 es lo que corresponde al Ejecutivo A
506011 Ejecutivo A
506012 Ejecutivo A
506013 Ejecutivo A
506014 Ejecutivo B Aquí Excel debe saber que debe dar 4 a ejecutivo B.
506015 Ejecutivo B
506016 Ejecutivo B
506017 Ejecutivo B
506018 Ejecutivo C
... ...
506029 Ejecutivo D

Lo que he logrado es que Excel calcule cuantos sobres le tocan a cada ejecutivo, pero no he logrado que haga que la columna B de el resultado adecuado. Los nombres de los ejecutivos salen de otra lista donde apunto la gente disponible.



Tenemos el siguiente modelo, con algunos cálculos sencillos ya realizados:

Reparto equitativo con Excel.


En E3 tenemos un conteo directo sobre el número de sobres a repartir:
=CONTARA(A3:A26)
En E4 el númeo de personas entre los que se debe realizar la asignación/reparto:
=CONTARA(E9:E20)
Ambos cálculos se han realziado con la función CONTARA, que devuelve el número de celdas no vacías, esto es, con algún dato.
En E5 con la fórmula
=ENTERO(MULTIPLO.SUPERIOR(E3;E4)/E4)
conseguimos un reparto al alza entre el número de personas, es decir, nos aseguramos que nunca faltará una persona para asignarle un sobre...


Una vez visto el planteamiento, añadimos la sencilla fórmula que realizará el reparto, tomando los cálculos anteriores. En B3:B26 añadiremos la fórmula:
=ENTERO((FILA()-3)/$E$5)+1
con lo que conseguimos intervalos de cinco en cinco, como vemos en la imagen:

Reparto equitativo con Excel.



Para trabajar con nombres en lugar de números, aprovechándonos de la ordenación de las personas, podríamos aplicar la siguiente función:
=INDICE($E$9:$E$20;ENTERO((FILA()-3)/$E$5)+1)
para todo nuestro rango...

14 comentarios:

  1. Hola excelforo,
    Si la fórmula contara ... cuenta las celdas no vacías (números y textos),
    Mi consulta es como seria la fórmula para contar solo los texto...(en un rango de números y textos). Muchas gracias
    Sldos
    Patricia

    ResponderEliminar
    Respuestas
    1. Hola Patricia,
      podrías aplicar la siguiente fórmula matricial, suponiendo en B4:B10 los diferentes valores (números, errores, texto, numéricos...):
      =SUMA(SI(ESTEXTO(B4:B10);1;0))
      recuerda ejecutarla presionando Ctrl+Mayusc+Enter

      Un cordial Saludo

      Eliminar
  2. Buenas tardes si podrían ayudarme. Tengo en un cuadro de Excel. CI. Apellido,Nombre y Especialidad que lo traigo de otra hoja pero este lo pasa no ordenado y necesito ordenarlo por Cédula .ejemplo:

    V2592863 RAMOS, ROSA ELENA, DIBUJO
    V1823872 SEGUERA PINTO, NILDA, ARTE
    V1643772 ARIAS, KELLY JOSE, CULINARIAS
    V2592863 LEON CUBILLA, MIGLEDIS, CULINARIAS
    E2593765 CUBILLAS RAMOS, CARLOS, MUSICALES

    Debe estar ordenado por Cédula de manera ascendente, esto es de A a Z. Que tipo de formula podría utilizar sin necesidad de ordenar por Filtro cada cuadro.

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      la ordenación mediante fórmulas/funciones es algo complejo.. requiriéndo normalmente algún tipo de columna auxiliar de apoyo... además tendríaspor un lado el original y por otro el rango ordenado.
      Yo te recomendaría emplearas, si es lo que quieres construyas una tabla dinámica sobre el origen, y apliques la ordenación por CI (o el campo que quieras) en la tabla dinámica.

      De todas formas me apunto explicar un día (pronto) en el blog, la manera de ordenar mediante fórmulas...

      Un cordial saludo

      Eliminar
    2. Hola Carlos,
      echa un vistazo a esta entrada del blog..
      http://excelforo.blogspot.com.es/2013/12/ordenar-rango-de-celdas-de-texto-con.html
      ahí se da una explicación de cómo ordenar textos con fórmulas, en tu caso el CI
      Slds cordiales

      Eliminar
  3. tengo una base, que llega todos los meses, para 36 ejecutivos, los cuales estan repartidos en la region metropolitana de Chile, debo dividir 300 clientes para cada uno de ellos, siguiendo la prioridad de la ubicación de su sucursal, no alcanzo a resolver este problema :/

    ResponderEliminar
    Respuestas
    1. Hola Krallox,
      como estás?, un gusto saludarte igualmente.
      Lo primero que deberíamos es tener claro las condiciones del reparto
      ¿qué pasa si para cada ejecutivo no hay suficientes clientes en su zona?
      ¿qué pasa si para cada ejecutivo existe un exceso de clientes en su zona?...
      etc.
      Una vez marcadas las reglas podríamos comenzar a analizar cómo lograr el reparto.

      Un cordial saludo

      Eliminar
    2. Hola Ismael! muchas gracias por el apoyo ;), bueno te cuento que las condiciones del reparto es la cercania del cliente a la sucursal en donde opera el ejecutivo, por ende la prioridad es esa, y si no hubiesen suficientes clientes para el ejecutivo por zona, se recurre a la comuna mas cercana y asi sucesivamente hasta cubrir por completo la Regiòn. Lo mismo ocurre con los excesos, de ser asi, se le asignarian al ejecutivo carente de ellos, que se encuentre màs cercano, la verdad creo que he tratado de varias maneras y solo he logrado complicarme aun màs :P

      si se te ocurre algo, bienvenido sea =)

      Saludos de vuelta! =)

      Eliminar
    3. Hola!,
      pues efectivamente parece un problema este criterio, ya que en realidad parece no responder a ninguna regla real, ya que la cercanía podría no ser medible y habría que indicar manualmente qué región es más cercana con cuál.
      Diría que las posibilidades de desarrollo se ajustan a una personalización de región a región por número de ejecutivos
      :(
      Si no son muchas regiones intenta una a una analizando y comparando el número de ejecutivos en cada área
      Saludos

      Eliminar
  4. Ismael, que excelente información Gracias!!! ;D

    ResponderEliminar
  5. hola, yo tengo un problema... mensualmente llega una asignación con varios clientes que tienen impagados con cantidades variables... la pregunta es, ¿existe una fórmula para poder repartirlos entre los tres asesores que somos de manera proporcional los montos y número de clientes? Gracias por leerme. buenas tardes.

    ResponderEliminar
    Respuestas
    1. Hola!
      supongo el problema está en optar por una de las dos condiciones (o importes o clientes), ya que a priori parece no se puedan respetar ambos criterios al mismo tiempo...
      Para el reparto por clientes solo sigue las indicaciones de ete post.
      Para el reparto por importe habría que conocer la distribución de los datos y su estructura en la hoja de cálculo....

      Saludos

      Eliminar
  6. buenas noches, me gustaría saber quien me puede ayudar...este es mi caso:
    cada tanto me toca hacer la sumatoria de unos CDT (titulos de valores) que puede contener hasta 150 registros, luego esa sumatoria debo repartirla de manera equitativa entre 4 empleados es decir asignar un conjunto que contenga el nombre y el valor que le serán asignado a cada empleado...HELP

    ResponderEliminar
    Respuestas
    1. Hola Joan,
      para repartir equitativamente, salvo se me escape algo, bastaría dividir entre cuatro esa sumatoria, y asignárselo a cada empleado....

      Slds

      Eliminar