# Formula para conciliar saldos



## magoba (Jul 14, 2011)

Hola a Todos
  Si me pueden ayudar con este problema tengo reportes mensuales los cuales constan de 11 columnas y un
numero variables de filas de 100 a 2500, la columna A es el numero de empleado la B el nombre y las columnas E y F
las aportaciones o retiros mensuales que pueden ser maximo 3 al mes. La formula que solicito tiene que buscar en la columna A si el numero aparece una vez poner la cantidad de la columna E o F si aparece 2 o 3 veces sumarlas, las formulas irian en M y N Ejemplo
      A__      B __         E__           F__         M__            N     
     25   juan_      300.00   __ 0.00   ___  300.00     ___ 0.00
     44   pedro     100.00   __ 50.00
     44   pedro     150.00    __ 0.0    ____ 250.00    __ 50.00
     55   ana__       100.00  __  25.00
     55   ana__       100.00   ____ 0.00  
     55   ana__       100.00   __ 25.00   __ 300.00   __  50.00
     80   many_       50.00   ___  0.0    _____  50.00    ___  0.0

     Muchas gracias por la ayuda


----------



## cgcamal (Jul 15, 2011)

Hola magoba,

Seguro hay soluciones más cortas, pero esta de abajo parece hacer lo que necesitás.

En M2 (y copiar hacia abajo):

```
=SI(FRECUENCIA($A2:$A$2500,A2)=1,SI(CONTAR.SI(A:A,A2)=1,E2,SUMA(INDIRECTO(
DIRECCION(FILA(E2)-CONTAR.SI(A:A,A2)+1,5)&":"&DIRECCION(FILA(E2),5)))),"")
```
 Y similarmente en N2 (y copiar hacia abajo):

```
=SI(FRECUENCIA($A2:$A$2500,A2)=1,SI(CONTAR.SI(A:A,A2)=1,F2,SUMA(INDIRECTO(
DIRECCION(FILA(E2)-CONTAR.SI(A:A,A2)+1,6)&":"&DIRECCION(FILA(E2),6)))),"")
```
Espero ayude,

Saludos cordiales


----------



## magoba (Jul 15, 2011)

Cesar C muchas gracias por tu ayuda, algo estoy copiando mal pues no funciona debidamente la formula estoy adjuntando la tabla para mejor comprension

 ******** language="JavaScript" ************************************************************************>Libro1ABCDEF1ClaveNombreAportaciones del Periodo de la CuentaRetiros del Periodo de la CuentaRendimientos del Periodo de la CuentaSaldo Final de la Cuenta2881GUILLERMO$661,427.08-$826,552.49$165,125.41$0.003994HIGINIO$395,696.24-$403,617.72$7,921.48$0.0041141VICENTE LUIS$4,314.64$0.00$1,246.12$5,560.7551141VICENTE LUIS$745,000.00$0.00$39,483.15$784,483.1461161FLORENCIO$1,702.76$0.00$491.78$2,194.5371188JUAN JOSE$1,027.18-$1,167.07$139.89$0.0081274JORGE MIGUEL$7,890,203.40-$5,429,668.94$660,464.13$3,120,998.5891300EMILIO$1,285,991.71$0.00$83,695.08$1,369,686.78101300EMILIO$300,000.00$0.00$18,423.07$318,423.06111357RAFAEL$566,592.48-$688,007.44$121,414.96$0.00121357RAFAEL$106,955.11-$111,864.92$4,909.81$0.00131357RAFAEL$59.05-$62.84$3.79$0.00141377HUMBERTO$1,477,816.78$0.00$404,536.75$1,882,353.52151388JORGE$1,269,016.24-$1,499,485.32$242,571.07$12,101.99161388JORGE$52.37$0.00$3.94$56.30171403PEDRO FCO$658,343.32-$777,854.29$144,032.78$24,521.81Hoja1


----------



## sailepaty (Jul 15, 2011)

Otra opción, 

M2 y copia hacia abajo y la aderecha.

=SI($A2<>$A3,SUMAR.SI(INDICE($A$1:$A2,MAX(FILA()-2,1)):$A2,$A2,INDICE(E$1:E2,MAX(FILA()-2,1)):E2),"")

Saludos


----------



## magoba (Jul 15, 2011)

Muchas gracias Sailepaty fijate que cuando son 3 veces las que aparece un dato nada mas suma los 2 ultimos tal vez con la tabla que adjunte se pueda modififar la formula para que sume los 3 eventos de la misma columna.
Cesar estoy tratando de modificar la formula que mandaste, de lo que me doy cuenta es que cuando aparece una sola vez el dato en la columna A te pone la cantidad correcta pero cuando aparece 2 o 3 veces te pone la cantidad de la columna de la izquierda estoy tratando de ver como modificarlas para  que sumen de la misma columna las 2 o 3 veces en que aparezca el evento

Muchas gracias a los dos por esta gran ayuda


----------



## sailepaty (Jul 15, 2011)

A ver así,

=SI($A2<>$A3,SUMAR.SI(INDICE(A:A,MAX(FILA()-2,1)):$A2,$A2,INDICE(E:E,MAX(FILA()-2,1)):E2),"")

Saludos


----------



## magoba (Jul 15, 2011)

Cesar ya encontre la manera con tu formula, esta fué cambiando el 5 por un 6 en la primer formula estoy trabajando en la siguiente.

  Les agradesco mucho su ayuda pues no tenia idea del uso de estas funciones tan interesantes las estaré  practicando

   Gracias nuevamente


----------



## magoba (Jul 15, 2011)

Sailepaty  con esta segunda fórmula trabajó perfectamente, solucionado el problema  muy amable de tu parte,   Gracias


----------



## cgcamal (Jul 15, 2011)

magoba said:


> Cesar ya encontre la manera con tu formula, esta fué cambiando el 5 por un 6 en la primer formula estoy trabajando en la siguiente.
> 
> Les agradesco mucho su ayuda pues no tenia idea del uso de estas funciones tan interesantes las estaré  practicando
> 
> Gracias nuevamente


Que bueno saber que te funcionó magoba, por nada.

Saludos cordiales


----------



## magoba (Jul 15, 2011)

Gracias Cesar que gran ayuda saludos


----------



## magoba (Jul 14, 2011)

Hola a Todos
  Si me pueden ayudar con este problema tengo reportes mensuales los cuales constan de 11 columnas y un
numero variables de filas de 100 a 2500, la columna A es el numero de empleado la B el nombre y las columnas E y F
las aportaciones o retiros mensuales que pueden ser maximo 3 al mes. La formula que solicito tiene que buscar en la columna A si el numero aparece una vez poner la cantidad de la columna E o F si aparece 2 o 3 veces sumarlas, las formulas irian en M y N Ejemplo
      A__      B __         E__           F__         M__            N     
     25   juan_      300.00   __ 0.00   ___  300.00     ___ 0.00
     44   pedro     100.00   __ 50.00
     44   pedro     150.00    __ 0.0    ____ 250.00    __ 50.00
     55   ana__       100.00  __  25.00
     55   ana__       100.00   ____ 0.00  
     55   ana__       100.00   __ 25.00   __ 300.00   __  50.00
     80   many_       50.00   ___  0.0    _____  50.00    ___  0.0

     Muchas gracias por la ayuda


----------



## Greg Truby (Jul 15, 2011)

sailepaty said:


> =SI($A2<>$A3,SUMAR.SI(INDICE(A:A,MAX(FILA()-2,1)):$A2,$A2,INDICE(E:E,MAX(FILA()-2,1)):E2),"")


 
Hola a todos. El uso de Sailepaty de ...*MAX(Row()-2*... me tuvo curioso. Si lo hice bien, si hay más de tres filas con el mismo código, eso va a fallar. ¿Podría alguien confirmar si tengo razón? Hice una prueba con cinco filas de Rafael y me rinda una cifra diferente que la fórmula de César y de mi.

Para que sepan, la fórmula que puse para esto fue:

• =IF(A2=A3,"",SUM(OFFSET($A$1,MATCH(A2,A$1:A2,0)-1,4,COUNTIF(A$1:A2,A2))))

Sería necesario hacer un análisis pero puede ser que la siguiente sea menos cara para calcular debido a la eliminación de COUNTIF()

• =IF(A2=A3,"",SUM(OFFSET($A$1,MATCH(A2,A$1:A2,0)-1,4,ROW()-MATCH(A2,A$1:A2,0)+1)))

Tal vez sea bien obvio a todo los participantes en esta hilera, pero para cualquier lector futuro, favor noten que las fórmulas presentadas aquí exigen que los datos estén sorteados por columna A.


----------



## magoba (Jul 15, 2011)

Gracias Greg no noté la diferencia pues en el caso de mi tabla son 3 aportaciones o retiros por mes por eso me funcionó la salpaty pero checo con tu formula y te informo los resultados

   Muchas gracias por tu observación


----------



## sailepaty (Jul 16, 2011)

Greg Truby said:


> Hola a todos. El uso de Sailepaty de ...*MAX(Row()-2*... me tuvo curioso. Si lo hice bien, si hay más de tres filas con el mismo código, eso va a fallar.


 
Efectivamente Greg si existen mas de 3 registros por empleado la formula que propuse va a fallar. Sin embargo la propuse de esa forma basándome en la premisa de Magoba.



magoba said:


> Tengo reportes mensuales los cuales constan de 11 columnas y un numero variables de filas de 100 a 2500, la columna A es el numero de empleado la B el nombre y las columnas E y F las aportaciones o retiros mensuales que *pueden ser maximo 3 al mes*.


 
Ahora si existe la posibilidad de que existan mas de tres registros me inclinaría por esta opción y evitar la volatilidad de OFFSET. 


=SI(A2<>A3,SUMA(INDICE($E$2:E2,COINCIDIR(A2,$A$2:A2,0)):E2),"")

Saludos


----------



## Greg Truby (Jul 16, 2011)

Sí, Sailepaty, de hecho no fue hasta el último post de Magoba donde nos recordó de eso que noté que él nos había dicho eso en su primer post. Ya cuando eso, me di cuenta que seguro que usted la hizo asi a propósito. Y también gracias por recordarme que Offset() también es volátil. Siempre me recuerde sobre Indirect(), but siempre se me olvida de Offset().


----------



## magoba (Jul 16, 2011)

Gracias Sailepaty,  usarè tu ultima fórmula pues uno nunca sabe si se puedan nesecitar mas retiros, el límite que tenemos ahora es máximo tres pero pues
podría cambiar en un futuro y pues ya estaré preparado.

   Les agradesco a todos esta camaraderia y deseo de ayudar.

    Un abrazo a todos,  gracias


----------

