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...

51 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
    2. Gracias por su pronta respuesta, me ha sido muy util sin embargo la distribución sigue sin ser "equitativa"... ¿conoce alguna macro que pueda tomar en cuenta ambos criterios? o ¿para repartir por importe como seria el ejemplo de la fórmula a ocupar? ¡Gracias!

      Eliminar
    3. Hola,
      una macro a priori no te aportaría nada que no te devuelva una fórmula.
      Se trata de entender cuál es el criterio de reparto y construir el algoritmo.
      Por eso te indicaba en el comentario de septiembre que es importante conocer la distribución de datos y estructura de la hoja.
      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
  7. Hola buenas Noches.
    tengo un problema de asignación que me gustaría resolver: EL problema es el siguiente
    Me llegan sobres de 5 partes del país (cada parte esta definida como Región), y para cada región tengo varias personas que deben atender dichos sobres.
    Quisiera conocer un método automático para asignar según el siguiente ejemplo:
    Persona Región
    Pepito Antioquia
    Juanita Antioquia

    Sobre 1= Pepito
    Sobre 2 = Juanita
    Sobre 3 = Pepito
    sobbre 4 = Juanita

    es posible definir un código en Excel para este problema de asignación?

    De antemano agradezco toda la ayuda.

    ResponderEliminar
  8. Buenas
    Si me pueden ayudar con lo siguiente:
    Tengo varias ciudades que cuentan con varias Oficinas a las que hay que asignarle determinados proyectos.

    Se requiere que aleatoriamente sean escogidas las Oficinas para repartirles los proyectos.

    Por ejemplo en Cali hay 20 Oficinas, llegan 10 proyectos para distribuirlos a las Oficinas aleatoriamente. En el siguiente reparto se excluirán las que ya se hayan asignado hasta completar el ciclo de las 20

    ResponderEliminar
  9. columna A ciudades
    columna B cantidad oficinas

    ResponderEliminar
    Respuestas
    1. Hola,
      creo que yo haría obtendría una lista para cada una de las 20 oficinas de la ciudad según este (post, y luego me quedaría con los 10 más altos o más bajos.. como quieras
      Saludos

      Eliminar
    2. Gracias pero se necesita elegir al azar de la lista

      Eliminar
    3. correcto... al azar es como se desplegaría la elección...

      Eliminar
    4. Buenos días
      Ing. Ismael: Le agradecería proponerme un VBA para la solución:
      son 30 ciudades con un promedio de 12 oficinas a las que se les reparte aleatoriamente entre 5 a 10 proyectos, teniendo en cuenta que no se deben repetir e ir excluyendo las que ya se les haya asignado.
      Gracias

      Eliminar
    5. Hola David,
      para personalizaciones lee las Normas de uso del blog
      Saludos

      Eliminar
  10. Buenas tardes, quisiera saber como resolver un problema, en un estudio de 12 abogados divididos en 4 equipos de 3 cada uno, se dividen los casos que diariamente llegan, uno a cada uno de forma secuencial,pero CON LA EXCEPCIÓN de que por semana cada equipo (3 abogados) no reciben casos...
    Por mes cada equipo descansa una semana ... El problema es repartir la carga de forma equitativa advirtiendo que durante la semana de descanso a veces ingresan más casos generando desigualdad en la división de casos

    ResponderEliminar
    Respuestas
    1. Hola Fiorella,
      es algo complejo de elaborar, ya que son muchos condicionantes.
      Te puedo orientar diciéndote que busques el patrón de descanso a la hora de seleccionar equipos.
      Una vez tengas claro la distribución de equipos por semana, ya podrás repartir casos...

      Es probable que se requiera algo de programación :(

      Saludos

      Eliminar
  11. Hola excelforo.

    Quisiera hacer una fórmula para el siguiente problema.

    Mensualmente dos individuos aportan a un fondo una cantidad al inicio del mes, al final del mes, habrá que repartirse las cantidades obtenidas según lo aportado proporcionalmente.

    Alguien puede echarme una mano?

    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola Alejandro,
      la proporción en el caso tal cual lo planteas es la misma que la aportación, esto es, a cada uno le toca lo aportado

      Saludos

      Eliminar
  12. Hola. Quisiera la ayuda para una fórmula que reparta equitativamente una información. Por ejemplo tengo unas tulas que hay que contar,estas tienen montos diferentes y se deben repartir entre un núero de personas, en este caso 6 tulas que se reparten entre 3 personas. La tula AA01 tiene un monto de ¢37.000.000, la Tula AA02 un monto de ¢63.000.000. Es posible que la fórmula me diga cuales tulas asignar a cada uno, es decir ¨las tula AA01 y AA04 son del cajero 01, las tulas AA02 y AA06 son del cajero 02.

    AA01 $37.000.000 $6.000
    AA02 $63.000.000 $20.000
    AA03 $56.000.000 $12.000
    AA04 $33.000.000 $10.000
    AA05 $51.000.000 $16.000
    AA06 $48.000.000 $8.000

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      lo siento pero no comprendo el criterio de reparto :'(

      Si lo explicas de otra forma quizá pueda orientarte

      Saludos

      Eliminar
    2. Gracias. Lo que pasa es que todos los días hay que cargar una tulas con cierta cantidad de efectivo... Estas tulas las reparto entre una cantidad de personas. Se me pasa un excel con los numeros de tula y los montos de cada una (ejemplo la AA01 lleva 10.000.000 y la AA02 5.000.000, así varias tulas con montos diferentes). Lo que quiero es poser asignar las tulas de forma que todos tengan una cantidad igual de tulas y los montos los más equitativo posible. Por ejemplo si fueran 5 tulas para 2 personas, que la fórmula le asigne a uno 3 tulas y a otro 2 tulas y que tome en cuenta que los montos se asemejen tambien... Las 3 tulas suman 30.000.000 y las otras dos por ejemplo 28.000.000. Espero me entiendas mejor y me puedas ayudar. Gracias

      Eliminar
    3. bueno, aquí el principal problema sería incorporar a la fórmula esa doble condición (número de elementos o importes asociados).
      El primer caso se seguiría lo expuesto en el post... pero tener presente también los importes hace el asunto complejo.

      Le daré una vuelta a ver si se me ocurre alguna cosa sin programación

      Saludos

      Eliminar
  13. Respuestas
    1. Hola Excelforo
      disculpa las molestias, quisiera saber si has logrado algo con mi problema.Gracias

      Eliminar
    2. Hola,
      aun no he podido ponerme con esto... está en mi lista de pendientes
      ;-)

      Sdls

      Eliminar
  14. Hola Ismael, realice el paso a paso del post y me resulta perfecto, pero necesito resolver un incoveniente que aparecio luego de procesarlo, para mi ejercicio tengo mas de un producto por cliente y no se deben compartir los clientes

    ResponderEliminar
  15. compartir los clientes por ejecutivos ya que estoy preparando la base para llamados de dichos ejecutivos de call center

    ResponderEliminar
    Respuestas
    1. Hola
      entiendo necesitas un reparto pro doble criterio¿?¿?

      Podrías realizar el proceso en dos etapas...

      Habría que ver tu distribución de datos en la hoja

      Slds

      Eliminar
  16. Buenas. Quisiera saber si es posible repartir tomando en cuenta la cantidad y el monto de un determinado producto.Por ejemplo debo repartir unas tulas con efectivo para su descarga, cada tula tiene montos diferentes y los cajeros varian por día. Si por ejemplo tomo 6 tulas y las reparto entre 3 cajeros serían 2 para cada uno pero debo tomar en cuenta de que tengan igual monto o los más cercano posible... Las 6 tulas suman $120.000, entonces son 2 tulas con montos de $40.000 para cada uno. Espero se entienda y me puedan ayudar. Gracias

    ResponderEliminar
  17. Cantidad de cajeros
    Total Montos $120.000 6 Cantidad de efectivo x cajero $20.000
    Total Tulas 6 6 Cantidad de tulas x cajero 1
    Tulas Montos
    AA01 $20.000
    AA02 $15.000
    AA03 $10.000
    AA04 $30.000
    AA05 $25.000
    AA06 $20.000


    Distribucion por cajero

    Cajero 1 AA01 AA06 $40.000 AA01 + AA06
    Cajero 2 AA02 AA05 $40.000 AA02 + AA05
    Cajero 3 AA04 AA03 $40.000 AA03 + AA04
    Cajero 4
    Cajero 5
    Cajero 6
    Cajero 7

    ResponderEliminar
    Respuestas
    1. Hola Bryan
      no tengo una respuesta distinta a la comentada más arriba (Atrás en el tiempo)
      :'(

      Si te urge o es relevante para tu trabajo, te invito a leer las Normas de uso del blog

      Un cordial saludo!

      Eliminar
  18. Hola Ismael, espero puedas ayudarme. Te cuento, tengo que realizar la distribución de manera equitativa entre dos empleados una cantidad X de sobres, según la cantidad de cartas que tenga cada uno, la idea es que entre ambos puedan tener el 50% de sobres, pero dependiendo de cuántas cartas tiene cada uno de estos:

    Sobre N° Cartas %
    2001 8786 34%
    2002 5349 21%
    2003 3356 13%
    2004 3354 13%
    2005 1482 6%
    2006 1243 5%
    2007 1121 4%
    2008 1058 4%
    Total 25749 100%
    .... .... ...

    Tengo que repartir más de 60 sobres, todos con cantidades distintas

    Espero puedas ayudarme, gracias.

    ResponderEliminar
    Respuestas
    1. Hola Alberto,
      ¿no bastaría con aplicar el 50% a cada uno?
      Lo siento pero no entiendo el planteamiento... ¿?
      Slds y quedo atento

      Eliminar
    2. Hola Ismael, De hecho....de eso se trata, pero estoy buscando una fórmula que me simplique esto, pues tengo que repartir más de 1000 sobres entre estas 2 personas, y la idea es que al repartir estos sobres, ambos tengan casi la misma cantidad de cartas que se encuentran dentro de estos sobres. El problema es la cantidad de sobres. Por ejemplo puede que uno tenga 700 sobres y el otro 300, pero ambos tendrán casi la misma cantidad de cartas. No sé si ahora se entiende mi planteamiento o sigue difuso.

      Eliminar
    3. Hola Alberto,
      de verdad que lo siento, pero no termino de entenderlo :'(
      ¿Puedes enviarme un ejemplo a excelforo@gmail.com?
      Indica (a mano) cómo debería quedar
      Gracias

      Eliminar
  19. BUENAS TARDES
    TENGO 400.000 DE GANANCIAS POR VENTAS
    NECESITO REPARTIR PARA 2 PERSONAS DE ACURDO A UN MONTO DADO POR CADA UNO
    LA PRIMERA PERSONA APORTO 4.000.000 Y LA SEGUNDA 1.000.000
    COMO HACER PARA REPARTIR EN PARTES DE ACUERDO A SU APORTE?

    ResponderEliminar
    Respuestas
    1. Buenas tardes,
      calcula primero la proporción/ponderación o peso de cada uno respecto a sus aportes:
      uno: 4.000.000/5.000.000
      dos: 1.000.000/5.000.000
      y luego sobre esos porcentajes multiplicas las ganancias de 400.000

      Saludos

      Eliminar
  20. Buenas Tardes

    Tengo 10490 datos en excel que suman 5.500.000, quisiera rapartirlos en distintos porcentajes, hay una formula para hacerlo? muchas gracias

    21%
    15%
    14%
    15%
    14%
    21%

    ResponderEliminar
    Respuestas
    1. Hola
      no existe ninguna función predefinida en Excel... habría que estructurar una que siga los patrones de reparto.
      Probablemente emplear Solver sea una buena idea en este caso

      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.