En el post de hoy conseguiremos, mediante fórmulas y también con programación, conseguir rellenar unas celdas con un valor parcial hasta conseguir que su suma acumulada llegue a una cantidad total, pero sin rebasarla.
La cuestión planteada por el usuario:
Comenzaremos exponiendo la solución con fórmulas, partiendo de dos datos:
1-celda D1: valor total a alcanzar (en el ejemplo 16,3)
2-celda E1: valor parcial para completar x celdas (en el ejemplo 1.5)
La fórmula buscada, añadida en B1 y luego arrastrada hacia abajo hasta B14 (en realidad tantas celdas como necesitemos hasta conseguir alguna celda vacía...):
=SI($E$1*FILA()
Con este condicional comparamos el valor parcial acumulado con el total a alcanzar, fila a fila.
Mientras no superemos el valor Total retornaremos el dato de E1 (parcial), y solo cuando superemos dicha cantidad, y por diferencias:
$E$1-($E$1*FILA()-$D$1)
completaremos el valor. Tal como pedía el lector.
Un fórmula relativamente sencilla de aplicar.. con el único inconveniente que tendremos que ser nosotros los que controlemos hasta donde debemos arrastrarla...
Para evitar el inconveniente anterior propondré una solución alternativa con macros.
Así pues abrimos la ventana de código de nuestro módulo estándar y añadimos el siguiente código:
Al ejecutar nuestra macro nos abrirá dos InputBox pidiendonos que seleccionemos las celdas con los valores 'Parcial' y 'Total', completando hacia abajo, desde la celda 'Parcial' con la serie requerida.
La cuestión planteada por el usuario:
| [...]Tengo en una columna el valor de la cifra acumulada de una sumatoria de valores iguales, conozco el valor de este número también y quiero que el resto de celdas se rellene automáticamente hasta llegar a sumar el valor acumulado y si la última cifra no puede ser igual al valor de la serie, entonces debe ajustarse hasta conseguir que la sumatoria sea igual al acumulado. Algo así: 9 (acumulado) 2 2 2 2 1 (último valor ajustado para que la suma sea igual a 9) (Los valores de 9 el acumulado y el primer 2 de la serie ya se conocen) Otro ejemplo 16.3 1.5 1.5 1.5 1.5 1.5 1.5 1.5 1.5 1.5 1.5 1.3 (16.3 y 1.5 son valores que ya se conocen)[...] |
Comenzaremos exponiendo la solución con fórmulas, partiendo de dos datos:
1-celda D1: valor total a alcanzar (en el ejemplo 16,3)
2-celda E1: valor parcial para completar x celdas (en el ejemplo 1.5)
La fórmula buscada, añadida en B1 y luego arrastrada hacia abajo hasta B14 (en realidad tantas celdas como necesitemos hasta conseguir alguna celda vacía...):
=SI($E$1*FILA()
Con este condicional comparamos el valor parcial acumulado con el total a alcanzar, fila a fila.
Mientras no superemos el valor Total retornaremos el dato de E1 (parcial), y solo cuando superemos dicha cantidad, y por diferencias:
$E$1-($E$1*FILA()-$D$1)
completaremos el valor. Tal como pedía el lector.
Un fórmula relativamente sencilla de aplicar.. con el único inconveniente que tendremos que ser nosotros los que controlemos hasta donde debemos arrastrarla...
Para evitar el inconveniente anterior propondré una solución alternativa con macros.
Así pues abrimos la ventana de código de nuestro módulo estándar y añadimos el siguiente código:
Sub Repeticion()
Dim Total As Range, Parcial As Range
'seleccionas las celdas con los valores Total y Parcial...
Set Total = Application.InputBox("Valor a alcanzar", "Excelforo", Type:=8)
Set Parcial = Application.InputBox("Selecciona dónde se encuentra el valor parcial", "Excelforo", Type:=8)
Dim valor As Double, Tot As Double
valor = Parcial.Value
Tot = Total.Value
Dim Acum As Double
Acum = Parcial.Value
'comenzamos el inicio del bucle para completar la secuencia de valores
Parcial.Select
Do
Acum = Acum + valor 'acumulamos valores parciales
ActiveCell.Offset(1, 0).Select 'bajamos una celda abajo...
'controlamos el último valor de la secuencia
If Acum >= Tot Then
ActiveCell.Value = valor - (Acum - Tot)
Else
ActiveCell.Value = valor
End If
Loop Until Acum >= Tot 'salimos del bucle al superar el Total
End Sub
Al ejecutar nuestra macro nos abrirá dos InputBox pidiendonos que seleccionemos las celdas con los valores 'Parcial' y 'Total', completando hacia abajo, desde la celda 'Parcial' con la serie requerida.
This post first appeared on EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDA, please read the originial post: here
