Eine Zahl in Summenkomponenten aufteilen

585
Sarah22

Gibt es eine Möglichkeit, dass ich eine Nummer angeben kann und Excel die Komponenten, aus denen sich diese Nummer zusammensetzt, aus einer begrenzten Teilmenge berechnet (ohne die angegebene Anzahl zu überschreiten)?

zB Teilmenge: (1,25, 2,5,, 5,10,20)

und eine Zahl (25) gegeben, würde die Excel 20 + 5 (oder 20,5) schreiben

17 -> 10 + 5 + 1,25 (nächste Kombination, die 17 nicht überschreitet)

35 → 20 + 10 + 5 = 35

36 → 20 + 10 + 5 = 35

37 → 20 + 10 + 5 + 1,25 = 36,25

38 → 20 + 10 + 5 + 2,5 = 37,5

39 → 20 + 10 + 5 + 2,5 + 1,25 = 38,75

und so weiter

0
Möchten Sie dies mit Zellformeln, VBA oder einer der beiden tun? Robyn vor 7 Jahren 0
@Robyn gut, wenn Sie es mit einer Zellenformel schaffen könnten, würde es sicherlich die Frage beantworten und sehr beeindruckend sein barlop vor 7 Jahren 1
Es würde wahrscheinlich VBA erfordern, und an dieser Stelle machen sie nicht gern ganze Übungen für Menschen. Da es sich um VBA handelt, ist dies eher ein Programmierproblem, aber wiederum macht es die Schwester oder der größere Bruder, stackoverflow, nicht gerne für Menschen. Daher wäre es besser, in einem anderen Forum wie https://www.excelforum.com/excel-programming-vba-macros/ nachzufragen. barlop vor 7 Jahren 0
Es lohnt sich auch darüber nachzudenken, warum Sie diese Frage stellen. Woher kommt die Frage? Wenn Sie versuchen, Excel zu lernen ... vielleicht VBA, dann nehme ich an, dass dies eine Schleife-Iteration erforderlich macht. Und wenn Sie versuchen, das zu lernen, gibt es einfachere Möglichkeiten. Also eher eine progressive Übung das baut dein Wissen auf, wäre sinnvoll. Wie ein VBA-Makro, um die Zahlen 1 bis 5 anzuzeigen. barlop vor 7 Jahren 0
Es gibt viel Zweideutigkeit. Können sich die Faktoren wiederholen? Ihre Untermenge sind alle Faktoren voneinander. Es gibt viele Möglichkeiten, sie zu verwenden. Gibt es eine andere Regel wie die wenigsten möglichen Faktoren? Wie müssen 60 ausgedrückt werden? fixer1234 vor 7 Jahren 0

1 Antwort auf die Frage

2
JSmart523

Das hat Spaß gemacht!

Erstellen Sie an einer beliebigen Stelle in Ihrer Arbeitsmappe (ich schlage eine andere Registerkarte vor) eine Komponententabelle mit einer Null wie folgt vor: Komponententabelle Beginnen Sie einfach mit dem Wort "Components", geben Sie dann Ihre Zahlen ein und wählen Sie "Insert Table" aus der Multifunktionsleiste. Stellen Sie sicher, dass Sie die Null einschließen und den Tabellennamen wie hervorgehoben ändern. Diese Komponententabelle muss in aufsteigender Reihenfolge sein, aber die Erstellung der Tabelle bedeutet, dass Sie diese praktische kleine Sortier- / Filter-Schaltfläche erhalten. :-)

Erstellen Sie dann an einer anderen Stelle in Ihrer Arbeitsmappe die Haupttabelle. Haupttabelle mit einer Eingangsnummernspalte, 10 Komponentenspalten und einer Komponentensummenspalte Fügen Sie nur die erste Spalte und alle Spaltenüberschriften hinzu, und wählen Sie dann erneut Tabelle einfügen. Ich habe das Zahlenformat des Normal-Styles General;General;so bearbeitet, dass Werte von Null als leer angezeigt werden.

Säulen:

Nummer - Ihre Nummer

C1 - =VLOOKUP([@Number],Components,1)

C2 bis C10 - =VLOOKUP([@Number]-SUM(OFFSET([@C1],0,0,1,COLUMN()-COLUMN([@C1]))),Components,1)

CSum - =SUM([@[C1]:[C10]])

[@ColumnName]Bezieht sich innerhalb einer Tabelle auf die Zelle "ColumnName" in der aktuellen Zeile. Daher wird in meiner Version eine Formel von COLUMN([@C1])3 zu 3 ausgewertet, da dies in Spalte C steht.

Also für die hervorgehobene Zelle im obigen Bild die Formel von

=VLOOKUP([@Number]-SUM(OFFSET([@C1],0,0,1,COLUMN()-COLUMN([@C1]))),Components,1)

löst sich auf

=VLOOKUP(**35**-SUM(OFFSET(C4,0,0,1,**6** - **3**)),Components,1)

löst sich auf

=VLOOKUP(35-SUM(**C4:E4**),Components,1)

löst sich auf

=VLOOKUP(35-**35**,Components,1)

Daher sucht VLOOKUP in Components nach der größten Zahl, die dem ersten Argument (0) entspricht, und gibt ein Ergebnis von 0 zurück. Dies wird gemäß der benutzerdefinierten Formatierung von angezeigt, Custom;Custom;sodass Null als leer angezeigt wird .

(Nun, shucks. Das "**" ist natürlich nicht wörtlich Teil dessen, worauf sich die Formel auflöst, sondern mein gescheiterter Versuch, die geänderten Teile fett hervorzuheben. Wenn jemand weiß, wie ich den Effekt erzielen kann, den ich gedreht habe für, bitte beheben Sie es.)

Verwandte Probleme