☰

Divida el contenido de las celdas en 'n' valores iguales repetidamente y redistribuya en celdas consecutivas

tag-icon • tag-icon microsoft-excel worksheet-function
Divida el contenido de las celdas en 'n' valores iguales repetidamente y redistribuya en celdas consecutivas

Lo que tengo:

n = 5
0, 0, 0, 0, 1000, 0, 0, 0, 0

Lo que quiero:

0, 0, 0, 0, 200, 200, 200, 200, 200, 0, 0...

Entonces, cuando vea un valor en la fila superior (1000), la fila inferiorigualmentedividir ese valornorteveces hasta que la suma de todas las divisiones sea igual al valor original.

Preferiría evitar el uso de VB si es posible.

EDITAR1:

¿Tiene la garantía de que cualquier valor que encuentre será divisible por n?

No. Sin embargo, una vez que la diferencia entre la suma y el valor original sea < 1, continúe.

¿Tiene la garantía de que habrá celdas adecuadas para n divisiones?

No.

¿Tiene la garantía de que habrá un solo valor distinto de cero?

No, habrá múltiples valores. Si se ve otro valor en la fila superior y la fila inferior no ha completado la división, la fila inferior sumará las divisiones antiguas y nuevas.

¿El valor distinto de cero estará siempre en la misma posición?

No. Puede aparecer en cualquier posición distinta a la primera.

EDITAR2:

Para ser transparente, estoy tratando de distribuir equitativamente los grandes costos en dólares entrenorteaños.

Quizás este sea un mejor ejemplo: Cronología de los insumos esperados y los resultados esperados

Tenga en cuenta que:

  • El proceso de dividir el Valor de Entrada debe ocurrir hasta que la suma de las respectivas divisiones sea igual al Valor de Entrada.

  • Si se ve otro Valor de entrada cuando aún no hemos terminado de dividir un Valor de entrada anterior, la fila de Salida continuará sumando las divisiones respectivas hasta que se cumpla la condición del primer punto para cada Valor de entrada.

Respuesta1

EDITAR: Si bien la fórmula anterior, innecesariamente complicada (veresta revisión) seguirá siendo útil, por ejemplo, si fuera necesario aplicar un factor de escala deslizante a las entradas, lo reemplacé en esta respuesta con la versión nueva, más simple y mucho más fácil de entender.

Aquí hay una solución que no es VBA y que no requiere filas/columnas auxiliares ni tablas adicionales:

Captura de pantalla de la hoja de trabajo

Ingrese la siguiente fórmula en matriz B3y luego rellénela hacia la derecha:

{=SUM(IFERROR(INDEX($2:$2,N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1))))/$A$5,0))}

Explicación:

La versión embellecida de la fórmula es la siguiente:

{=
SUM(
  IFERROR(
    INDEX(
      ($2:$2),
      N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)))
    )/$A$5,
    0
  )
)}

La fórmula es mucho más fácil de entender si se considera que para n = 5, el segundo argumento del primero INDEX()es efectivamente equivalente a:

COLUMN()-{0,1,2,3,4}


Básicamente funciona generando una A5matriz de compensaciones de longitud variable (basada en n, almacenada en mi ejemplo) para acceder al n-1 anterior, más los valores de entrada actuales.

Repasar la fórmula I3debería aclarar lo anterior:

  • (COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)→ {1,2,3,4,5}-1→{0,1,2,3,4}
  • COLUMN()-{0,1,2,3,4}→ {9}-{0,1,2,3,4}→{9,8,7,6,5}
  • N(IF(1,{9,8,7,6,5}))→ N({9,8,7,6,5})→{9,8,7,6,5}
  • (INDEX($2:$2,{9,8,7,6,5})/$A$5→ {100000,0,0,0,7}/5→{20000,0,0,0,1.4}
  • SUM(IFERROR({20000,0,0,0,1.4},0))→20001.40

INDEX($2:$2,N(IF(1,expression)))es un truco requerido * para forzar a Excel a devolver una matriz for expressionya que el segundo argumento de INDEX()se evalúa como un valor único de forma predeterminada. Usar just in conduciría aINDEX($2:$2,expression)I3

INDEX($2:$2,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1))→ INDEX($2:$2,9-(COLUMN($A$3:$E$3)-1))→ → →INDEX($2:$2,9-(1-1))INDEX($2:$2,9)100000

ya que dentro de una expresión que devuelve un solo valor, devuelve la columna de la primera celda del rango.COLUMN(multi-cell-range)

La IFERROR()función es necesaria en caso de que la fórmula exista en una celda cercana al lado izquierdo de la hoja, lo que resulta en acceder al encabezado de la fila o intentar acceder a una celda a la izquierda de la columna A.

Notas:

  • La fórmula embellecida realmente funciona si se ingresa.
  • Los corchetes ($2:$2)en la versión embellecida son necesarios para obligar a $2:$2permanecer en su propia línea.

* La explicación de exactamente por qué funciona el truco tendrá que esperar hasta que lo descubra por mí mismo primero ;-)

Respuesta2

Código:

Sub Divide(n As Integer)
Dim temp(), i As Integer, j As Integer, cnt As Integer, tmp

' Copy values from row to array
temp = Rows(Selection.Row).Value

' Search for the last cell. Use SpecialCells instead is the better variant.
For i = UBound(temp, 2) To LBound(temp, 2) Step -1
    If Not IsEmpty(temp(1, i)) Then
        cnt = i
        Exit For
    End If
Next i

' Perform main operation
For i = cnt To 1 Step -1
    If Rows(Selection.Row).Cells(1, i).Value <> 0 Then
        tmp = Rows(Selection.Row).Cells(1, i).Value
        Rows(Selection.Row).Cells(1, i).Delete Shift:=xlShiftToLeft
        For j = 1 To n
            Rows(Selection.Row).Cells(1, i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Rows(Selection.Row).Cells(1, i).Value = tmp / n
        Next
    End If
Next

End Sub

  • Sub acepta nvalor como parámetro.

  • Sub procesa la fila con la selección (esquina superior izquierda de).

  • Sub no marque, los valores en las celdas se pueden convertir a números para compararlos 0y dividirlos no no.

  • Otros errores tampoco se verifican.

  • Los valores desplazados se perderán mientras se muevan por encima del límite de columnas.

  • Y ninguna optimización en absoluto.

Utilice este código como idea.

Respuesta3

Aquí hay una solución que funciona sin la necesidad de usar VBA.

La única diferencia es que necesitarás agregar una tabla en algún lugar para ingresar cada costo individualmente.

Le recomiendo encarecidamente que haga esto, ya que simplifica mucho los cálculos y también agrega mucha claridad financieramente hablando: puede agregar fácilmente columnas para cosas como referencia de costo, descripción, tipo de servidor, etc. O incluso agregar cantidad y base. columnas de precios, si tiene varios artículos. Puede ampliar esto y producir informes interesantes, y es mucho más fácil que escribir VBA, en mi opinión.

Las celdas amarillas representan la entrada.

ingrese la descripción de la imagen aquí

  1. En C2, asigne un nombre a la celda _Npara tener una manera fácil de acceder a la variable 5 yearsen otro lugar
  2. Para sus costos (rango A5:D10), inserte una tabla que pueda nombrar Costspara referencias más sencillas.

Aquí están las fórmulas a usar y, si es necesario, arrastrar/copiar hacia la derecha (en azul en la captura de pantalla):

=IFERROR([@[Real Cost]]/_N;0)

=IF([@From]>0,[@From]+_N-1,"")

=SUMPRODUCT((B$13=Costs[From])*(Costs[Real Cost]))

=SUMPRODUCT((B$13>=Costs[From])*(B$13<=Costs[Until])*(Costs[Yearly Cost]))

información relacionada