☰

Zellinhalte wiederholt in n gleiche Werte aufteilen und in aufeinanderfolgende Zellen neu verteilen

tag-icon • tag-icon microsoft-excel worksheet-function
Zellinhalte wiederholt in n gleiche Werte aufteilen und in aufeinanderfolgende Zellen neu verteilen

Was ich habe:

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

Was ich möchte:

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

Wenn Sie also einen Wert in der oberen Zeile sehen (1000), wird die untere ZeilegleichmäßigTeilen Sie diesen Wert aufNmal, bis die Summe aller Teilungen dem ursprünglichen Wert entspricht.

Ich würde die Verwendung von VB nach Möglichkeit vermeiden.

BEARBEITEN1:

Ist garantiert, dass alle gefundenen Werte gleichmäßig durch n teilbar sind?

Nein. Sobald jedoch die Differenz zwischen der Summe und dem ursprünglichen Wert < 1 ist, fahren Sie fort.

Ist garantiert, dass für n Unterteilungen ausreichend Zellen vorhanden sind?

NEIN.

Ist garantiert, dass es nur einen Wert ungleich Null gibt?

Nein, es wird mehrere Werte geben. Wenn in der oberen Zeile ein anderer Wert angezeigt wird und die untere Zeile noch nicht vollständig aufgeteilt ist, werden in der unteren Zeile die alten und neuen Aufteilungen zusammengerechnet.

Steht der von Null verschiedene Wert immer an der gleichen Position?

Nein. Es kann an jeder anderen Position als der ersten erscheinen.

EDIT2:

Um transparent zu sein, versuche ich, hohe Kosten gleichmäßig zu verteilen aufNJahre.

Vielleicht ist dies ein besseres Beispiel: Zeitplan für erwarteten Input und erwarteten Output

Beachten Sie, dass:

  • Der Vorgang des Aufteilens des Eingabewerts muss so lange erfolgen, bis die Summe der jeweiligen Aufteilungen dem Eingabewert entspricht.

  • Wenn ein anderer Eingabewert angezeigt wird, während wir mit der Aufteilung eines vorherigen Eingabewerts noch nicht fertig sind, wird die Ausgabezeile fortgesetzt, indem die jeweiligen Aufteilungen summiert werden, bis die Bedingung im ersten Punkt für jeden Eingabewert erfüllt ist.

Antwort1

EDIT: Während die vorherige, unnötig komplizierte Formel (siehediese Überarbeitung) weiterhin nützlich sein wird, wenn beispielsweise ein gleitender Skalierungsfaktor auf die Eingaben angewendet werden müsste. Ich habe es in dieser Antwort durch die neue, einfachere und viel leichter verständliche Version ersetzt.

Hier ist eine Nicht-VBA-Lösung, die keine Hilfszeilen/-spalten oder zusätzlichen Tabellen erfordert:

Screenshot des Arbeitsblattes

Tragen Sie in das Array folgende Formel ein B3und füllen Sie diese anschließend nach rechts aus:

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

Erläuterung:

Die verschönerte Version der Formel lautet wie folgt:

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

Die Formel ist wesentlich einfacher zu verstehen, wenn man bedenkt, dass für n = 5 das zweite Argument des ersten INDEX()effektiv äquivalent ist zu:

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


Es funktioniert grundsätzlich durch die Generierung eines Arrays von Offsets variabler Länge (basierend auf n, A5in meinem Beispiel gespeichert) um auf die vorherigen n-1 plus die aktuellen Eingabewerte zuzugreifen.

Das Durchgehen der Formel I3sollte das obige klarer machen:

  • (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)))ist ein erforderlicher Hack * , um Excel zu zwingen, ein Array für zurückzugeben, expressionda das zweite Argument von INDEX()standardmäßig als einzelner Wert ausgewertet wird. Die Verwendung von nur in würde zuINDEX($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

da innerhalb eines Ausdrucks, der einen einzelnen Wert zurückgibt, die Spalte der ersten Zelle des Bereichs zurückgegeben wird.COLUMN(multi-cell-range)

Die IFERROR()Funktion wird benötigt, wenn sich die Formel in einer Zelle nahe der linken Seite des Blattes befindet und entweder auf die Zeilenüberschrift zugreift oder versucht wird, auf eine Zelle links der Spalte zuzugreifen A.

Anmerkungen:

  • Die verschönerte Formel funktioniert tatsächlich, wenn sie eingegeben wird.
  • Die Klammern ($2:$2)in der verschönerten Version sind erforderlich, um zu erzwingen, $2:$2dass das in einer eigenen Zeile bleibt.

* Die Erklärung, warum der Hack genau funktioniert, muss warten, bis ich es selbst herausgefunden habe ;-)

Antwort2

Code:

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 akzeptiert nWert als Parameter.

  • Sub verarbeitet die Zeile mit der (linken oberen Ecke der) Auswahl in.

  • Unter „Nicht aktivieren“ wird geprüft, ob die Werte in den Zellen zum Vergleichen 0und Dividieren durch in Zahlen umgewandelt werden können noder nicht.

  • Auf andere Fehler wird ebenfalls nicht geprüft.

  • Beim Überschreiten des Spaltenlimits gehen verschobene Werte verloren.

  • Und überhaupt keine Optimierung.

Verwenden Sie diesen Code als Idee.

Antwort3

Hier ist eine funktionierende Lösung, für die VBA nicht erforderlich ist.

Der einzige Unterschied besteht darin, dass Sie irgendwo eine Tabelle hinzufügen müssen, um alle Kosten einzeln einzugeben.

Ich empfehle Ihnen dringend, dies zu tun, da es die Berechnungen erheblich vereinfacht und auch finanziell gesehen viel Klarheit schafft: Sie können problemlos Spalten für Dinge wie Kostenreferenz, Beschreibung, Servertyp usw. hinzufügen. Oder sogar Mengen- und Grundpreisspalten hinzufügen, wenn Sie mehrere Artikel haben. Sie können dies erweitern und interessante Berichte erstellen, und es ist meiner Meinung nach viel einfacher, als VBA zu schreiben.

Die gelben Zellen stellen die Eingabe dar.

Bildbeschreibung hier eingeben

  1. Benennen Sie in C2 die Zelle, um an anderer Stelle _Neinfach auf die Variable zugreifen zu können5 years
  2. Fügen Sie für Ihre Kosten (Bereich A5:D10) eine Tabelle ein, die Sie Costszur einfacheren Bezugnahme benennen können

Hier die Formeln, die Du verwenden und ggf. nach rechts (im Screenshot blau) ziehen/kopieren musst:

=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]))

verwandte Informationen