Asignacion de Personas a Centros por Codigo Postal

Tengo que distribuir un determinado número de Personas (por ejemplo 1000) de forma individual entre una serie de Centros, en función de sus respectivos Códigos Postales. El número de Personas y Centros por Código Postal no siempre va a ser el mismo y la distribución debe de ser lo mas equilibrada posible entre los Centros ubicados en un mismo Código Postal. Sin embargo pueden ser muy dispares el número de Personas por Centro de un Código Postal y otro.
Por ejemplo:
En el Código 28001 tengo 100 Personas y 3 Centros (C1, C2 y C3), con lo cual a C1 y a C2 lo tocarían 33 Personas a cada uno y al C3 34 Personas.
En el Código 28002 tengo 200 Personas y 2 Centros (C4 y C5), con lo cual le tocarían 100 Personas a cada uno de los Centros.
Y por ultimo en el Código Postal 28003 tengo 700 Personas y 3 Centros (C6, C7 y C8) con lo cual C6 y C7 tienen 233 Personas y el C8  234 Personas.
Trabajo con 2 Hojas: una de Personas, Códigos Postales y Centro (esta columna es la que tengo que llenar con el nombre del Centro) y otra de Centros y Códigos Postales.
"Hoja Personas"
PERSONAS     CPOSTAL    CENTRO
Persona1       28001
Persona2       28001
...
Persona201   28002
....
Persona1000 28003
"Hoja Centros"
CENTROS       CPOSTAL
Centro1         28001
Centro2         28001
...
Centro8         28003
Necesito poner en la columna Centro de la Hoja Personas el Centro asignado.
No sé si será posible hacer lo que necesito de una forma un poco automatica, ya que hasta ahora lo vengo haciendo de una forma manual.
Gracias por anticipado.

1 respuesta

Respuesta
1
Suponiendo que los nombres verdaderos de los centros no son los mostrados
-Centro1, Centro2, ...-
¿Podemos darle una identificación secuencial a esos centros en la
hoja Centros?
Algi así como:
Id centros cpostal
1 Centro1 28001
2 Centro2 28001
...
8 Centro8 28003
De esta forma seria mas facil
Muentras tanto te sugiero lo siguiente
Crear una hoja para los datos deducidos necesarios
algo como lo siguiente
cpostal CantPersonas CantCentros aCadaCentro masXtra
28001 100                           3              33                   1
28002 200                           2             100                  0
28003 700 3              233                 1
28004   54                           3              18                   0
28005     0 1               0                    0
y luego en la hoja Personas
incluir una columna D para calcular el ordinal del centro que corresponde a cada fila
primer centro de la hoja Centros : 1
segundo centro de la hoja Centros :2
etc
este valor se puede calcular con
=SI(RESIDUO(CONTAR.SI($B$2:B2;B2);BUSCARV(B2;datos;4;0))=1;SI(CONTAR.SI($B$2:B2;B2)
Luego la columna CENTROS la consigues con
=DESREF(Centros.$A$2;D2-1;0)
(Me equivoqué, y estuve trabajando con otra hoja de cálculo -OOCalc- y no lo hice en excel, pero todas las funciones de uno deben existir en el otro, espero que sean idénticas las fórmulas; en caso contrario deben haber su equivalente con otro nombre)
Hola csarxex
Ante todo muchas gracias por tu respuesta.
Creo haber hecho bien todo lo que me dices
- creé columna Id en CENTROS
- creé hoja DATOS con las 5 columnas que me indicas
- incluí columna D en hoja PERSONAS.
El problema lo tengo en esta columna D con la formula =SI(RESIDUO(CONTAR.SI($B$2:B2;B2);BUSCARV(B2;datos;4;0))=1;SI(CONTAR.SI($B$2:B2;B2)   
me dice que le falta un parentesis de apertura o de cierre, puse uno al final y me sigue dando error.
En cuanto a la funcion DESREF, para adaptarla a excel creo que tendré qeu sustituir el "centros.$a$2" por "centros!$a$2" es decir : =DESREF(centros!$A$2;D2-1;0)
Gracias de nuevo por tu inestimable ayuda
marca54
Lo hice en excel y me quedó lo siguiente:
Tuve cambiar el titulo de la columna D porque excel da error al sumarle 1 a un texto
Le puse un 0 (cero) en lugar del titulo
ColA colB colC colD
Persona cpostal centro 0
Fila1
ColA: Persona1
ColB: 28001
colC: =desref(Centros!$A$2,D2-1,0)
colD: si(residuo(contar.si($B$2:B2,B2),buscarv(b2,datos,4,0))=1,si(contar.si($b$2:b2,b2)<buscarv(b2,datos,2,0),d1+1,d1),d1)
Fila2
Persona2
28001
=desref(Centros!$a$2,d3-1,0) =si(residuo(contar.si($b$2:b3,b3),buscarv(b3,datos,4,0))=1,si(contar.si($b$2:b3,b3)<buscarv(b3,datos,2,0),d2+1,d2),D2)
HOJA1:
<table style="border-collapse: collapse; width: 642pt;" border="0" cellspacing="0" cellpadding="0" width="854">
<col style="width: 69pt;" width="92"></col><col style="width: 180pt;" width="240"></col><col style="width: 161pt;" width="214"></col><col style="width: 110pt;" width="146"></col>
<tbody>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17"><a name="RANGE!A1:E10">cpostal</a></td>
<td class="xl22" style="width: 180pt;" width="240">CantPersonas</td>
<td class="xl22" style="width: 161pt;" width="214">CantCentros</td>
<td class="xl22" style="width: 110pt;" width="146">aCadaCentro</td>
<td class="xl22" style="width: 122pt;" width="162">masXtra</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">28001</td>
<td class="xl22">=CONTAR.SI(Personas!B1:B1055,A2)</td>
<td class="xl22">=CONTAR.SI(Centros!B1:B13,A2)</td>
<td class="xl22">=ENTERO(B2/C2)</td>
<td class="xl22">=RESIDUO(B2,C2)</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">28002</td>
<td class="xl22">=CONTAR.SI(Personas!B2:B1055,A3)</td>
<td class="xl22">=CONTAR.SI(Centros!B2:B14,A3)</td>
<td class="xl22">=ENTERO(B3/C3)</td>
<td class="xl22">=RESIDUO(B3,C3)</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">28003</td>
<td class="xl22">=CONTAR.SI(Personas!B3:B1056,A4)</td>
<td class="xl22">=CONTAR.SI(Centros!B3:B15,A4)</td>
<td class="xl22">=ENTERO(B4/C4)</td>
<td class="xl22">=RESIDUO(B4,C4)</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">28004</td>
<td class="xl22">=CONTAR.SI(Personas!B4:B1057,A5)</td>
<td class="xl22">=CONTAR.SI(Centros!B4:B16,A5)</td>
<td class="xl22">=ENTERO(B5/C5)</td>
<td class="xl22">=RESIDUO(B5,C5)</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">28005</td>
<td class="xl22">=CONTAR.SI(Personas!B5:B1058,A6)</td>
<td class="xl22">=CONTAR.SI(Centros!B5:B17,A6)</td>
<td class="xl22">=ENTERO(B6/C6)</td>
<td class="xl22">=RESIDUO(B6,C6)</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">28006</td>
<td class="xl22">=CONTAR.SI(Personas!B6:B1059,A7)</td>
<td class="xl22">=CONTAR.SI(Centros!B6:B18,A7)</td>
<td class="xl22">=ENTERO(B7/C7)</td>
<td class="xl22">=RESIDUO(B7,C7)</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">28007</td>
<td class="xl22">=CONTAR.SI(Personas!B7:B1060,A8)</td>
<td class="xl22">=CONTAR.SI(Centros!B7:B19,A8)</td>
<td class="xl22">=ENTERO(B8/C8)</td>
<td class="xl22">=RESIDUO(B8,C8)</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">28008</td>
<td class="xl22">=CONTAR.SI(Personas!B8:B1061,A9)</td>
<td class="xl22">=CONTAR.SI(Centros!B8:B20,A9)</td>
<td class="xl22">=ENTERO(B9/C9)</td>
<td class="xl22">=RESIDUO(B9,C9)</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">28009</td>
<td class="xl22">=CONTAR.SI(Personas!B9:B1062,A10)</td>
<td class="xl22">=CONTAR.SI(Centros!B9:B21,A10)</td>
<td class="xl22">=ENTERO(B10/C10)</td>
<td class="xl22">=RESIDUO(B10,C10)</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17"> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17"> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17"> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl23" style="height: 12.75pt; width: 69pt;" width="92" height="17">cpostal</td>
<td>CantPersonas</td>
<td>CantCentros</td>
<td>aCadaCentro</td>
<td>masXtra</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl22" style="height: 12.75pt;" height="17">28001</td>
<td>100</td>
<td>3</td>
<td>33</td>
<td>1</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl22" style="height: 12.75pt;" height="17">28002</td>
<td>200</td>
<td>2</td>
<td>100</td>
<td>0</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl22" style="height: 12.75pt;" height="17">28003</td>
<td>700</td>
<td>3</td>
<td>233</td>
<td>1</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl22" style="height: 12.75pt;" height="17">28004</td>
<td>54</td>
<td>3</td>
<td>18</td>
<td>0</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl22" style="height: 12.75pt;" height="17">28005</td>
<td>0</td>
<td>1</td>
<td>0</td>
<td>0</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl22" style="height: 12.75pt;" height="17">28006</td>
<td>0</td>
<td>1</td>
<td>0</td>
<td>0</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl22" style="height: 12.75pt;" height="17">28007</td>
<td>0</td>
<td>1</td>
<td>0</td>
<td>0</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl22" style="height: 12.75pt;" height="17">28008</td>
<td>0</td>
<td>1</td>
<td>0</td>
<td>0</td>
</tr>
<tr style="height: 12.75pt;" height="17">
<td class="xl22" style="height: 12.75pt;" height="17">28009</td>
<td>0</td>
<td>1</td>
<td>0</td>
<td>0
</td>
</tr>
</tbody>
</table>

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas

Contar Registros Únicos de Subtotal (autofiltro)

A tod@s. Estoy desesperado. Tengo una hoja con muchos datos agrupados en columnas con encabezados. A los encabezados les aplico Autofiltro. Mi problema al que no encuentro solución es que una vez realizada la selección del AUTOFILTRO necesito CONTAR...
1 respuesta

Buscar el valor de una celda en una fila de otra hoja, avanzar 7 celdas y copiar en otra celda

Para todos. Me podrían, por favor, ¿Ayudar a solucionar lo siguiente? Debo buscar en la fila 1 de la Hoja2 el valor contenido en E2 de la Hoja1. Una vez hallado, avanzar 7 celdas a la derecha sobre la misma fila y el valor de esa celda copiarlo en O2...
1 respuesta

Saber si existe un valor en la matriz

Tengo una matriz (en realidad son varias filas sólo con fechas) y deseo obtener "verdadero" o "falso" si al colocar una fecha por en una celda, esa fecha esta en alguna de las celdas anteriores. Aclaro que la fecha buscada puede estar repetida n...
1 respuesta

Contar celdas buscando por el mes de la fecha

Y gracias de antemano por la ayuda que me podáis prestar. Tengo una hoja de excel en la que una de las columnas contiene fechas como: 01-ene-2010 12-ene-2010 16-ene-2010 25-feb-2010 26-feb-2010 16-mar-2010 18-abril-2010 ... ... Estoy intentando...
2 respuestas

Como puedo combinar las fórmulas de Buscarv con sumar.si

Tengo una pregunta en relación a la pregunta en el titulo ya contestada perfectamente....
1 respuesta

Sumar celdas con contenido

Tengo una hoja de excel donde voy anotando datos de clientes. En la primera columna aparece el código del cliente, y a lo mejor hasta tres columnas más abajo no aparece el siguiente. Lo que quisiera es poder sumar sólo las celdas en las que aparece...
1 respuesta

Contar.si con 2 condiciones

Tengo un problema que ahora te cuento, lo sabría hacer con una macro pero me interesa resolverlo con una función de Excel. Te cuento: Tenemos Columna ColumnaB A 1 B 2 C 3 A 0 B 2 C 3 A 1 Lo que quiero hacer es: Contar los registros de la Columna...
2 respuestas

Copiar rango de celdas según condición VBA

Necesito de su ayuda en lo siguiente, quiero copiar un rango de celdas y pegarlas en otra ubicación solo si cumple una condición, por ejemplo tengo una columna digamos B:B la cual tiene valores como nombres de personas en cada celda por ejemplo b1 =...
2 respuestas