AW: Struckturaufrechnung mit SummeWenn?
16.08.2006 15:24:35
Mike28
Hier noch kurz die Beschreibung des Problems von meinem Vorgängerschreiber und den entsprechenden Lösungen.
Ich hoffe, damit kommt wer einen Schritt weiter, denn so langsam muss ich hier die Arbeit abschliessen können. Vielen Dank auf jedenfall für die Unterstützung.
Gruss
Mike
_____________________________________________________________________
Struckturaufrechnung mit SummeWenn?
--------------------------------------------------------------------------------
Hallo habe folgendes Problem: Ich möchte eine Struckturstückliste aus SAP aufrechnen und finde nicht die richtige Formel. Beispiel: In Spalte A ist die Strucktur in Spalte B ist der Preis in Spalte C ist die Aufrechnung des Preises nach der Strucktur (was ich mit einer Formel machen möchte) mit folgenden Kriterien: Die Aufrechnung des Preises in Spalte C für die Struckturstufe 2 beinhaltet alle Preise der Struckturstufe 3 bis zur nächsten Struckturstufe 2. Die Aufrechnung des Preises in Spalte C für die Struckturstufe 3 beinhaltet alle Preise der Strukturstufe 4 bis zu nächsten Struckturstufe 3. So gehts weiter mit allen weiteren Stufen. Ich habe die Ergebnisse in Spalte C manuell ausgerechnet. Wie kann ich das mit einer Formel in Spalte C machen die ich "runterkopieren möchte".
A B C
2 0 35
3 10 10
3 0 5
4 5 5
3 0 20
4 0 15
5 15 15
4 0 5
5 5 5
Ohne UDF wird das nix.
--------------------------------------------------------------------------------
Hallo Jürgen,
fordere gerade die Freunde der Matrixen heraus, aber ich denke, so ein Einzeller ohne selbstgeschriebene Funktion, wird ziemlich aufwendig werden.
Eine UDF sieht da etwa so aus:
Function GetStufe(rng As Range) As Double
Dim lngAkt As Long, lngMax As Long
lngAkt = rng.Row + 1
lngMax = Cells(Rows.Count, rng.Column).End(xlUp).Row
GetStufe = rng.Offset(0, 1).Value
For lngAkt = lngAkt To lngMax
If Cells(lngAkt, 1).Value = rng.Value Then
Exit
Function
End If
GetStufe = GetStufe + Cells(lngAkt, 2).Value
Next lngAkt
End
Function
Gruss
Markus
Ps.: WF, wenn Du ne Matrix lieferst, gebe ich einen aus. ;-)
Ohne UDF wird das nix.
Hallo Jürgen,
fordere gerade die Freunde der Matrixen heraus, aber ich denke, so ein Einzeller ohne selbstgeschriebene Funktion, wird ziemlich aufwendig werden.
Eine UDF sieht da etwa so aus:
Function GetStufe(rng As Range) As Double
Dim lngAkt As Long, lngMax As Long
lngAkt = rng.Row + 1
lngMax = Cells(Rows.Count, rng.Column).End(xlUp).Row
GetStufe = rng.Offset(0, 1).Value
For lngAkt = lngAkt To lngMax
If Cells(lngAkt, 1).Value = rng.Value Then
Exit
Function
End If
GetStufe = GetStufe + Cells(lngAkt, 2).Value
Next lngAkt
End
Function
Gruss
Markus
Ps.: WF, wenn Du ne Matrix lieferst, gebe ich einen aus. ;-)
Re: Ohne UDF wird das nix.
Hi Marcus,
schwierig aber doch nicht unmöglich oder? Verstehe wenig von deiner UDF (bin da kein Experte) werde mich da wohl mit beschäftigen müssen wie das so geht. Die Struktur hat natürlich noch etliche mehr Spalten und Informationen (10000 Datensätze mit 45 Spalten), so daß eine Matrixformel mit enormen Aufwand möglich ist ... die Datei wird riesig und die Rechenzeit geht in die Knie.
Gruß
Jürgen
PS: Nehme dein Angebot wahr wenn ich was finde.
Geht doch ;-)
Hallo Jürgen,
geht doch ohne UDF und Matrix mit ner einfachen Summen-Formel.
=SUMME(INDIREKT("B"&ZEILE(A1:$A10000)&":B"&WENN(ISTFEHLER(VERGLEICH(A1;A2:A$10000;0));10000;VERGLEICH(A1;A2:A$10000;0)+ZEILE())))
Naja, so einfach auch nicht, aber funktioniert.
Gruss
Markus
Geht doch ;-)
Hallo Jürgen,
geht doch ohne UDF und Matrix mit ner einfachen Summen-Formel.
=SUMME(INDIREKT("B"&ZEILE(A1:$A10000)&":B"&WENN(ISTFEHLER(VERGLEICH(A1;A2:A$10000;0));10000;VERGLEICH(A1;A2:A$10000;0)+ZEILE())))
Naja, so einfach auch nicht, aber funktioniert.
Gruss
Markus
Re: Geht doch ;-)
Hallo Marcus,
ja das war schon nicht schlecht (fast super) aber ... es geht noch nicht richtig:
Ich habe in die Formel noch hinter Vergleich eine -1 angefügt damit der nächste gefundene Vergleichswert nicht mit summiert wird. Das geht auch. Ein Fehler ist noch drin: siehe angefügte Tabelle. In Spalte C ist wie es die Formel macht ind Spalte D ist wie es sein soll. Un man sieht, daß der 4 Wert von Spalte C (20) falsch ist, weil die Formel bis zur nächsten Stufe 4 sumiert sucht und nicht erkennt, daß nur bis zur nächsten Stufe 2 sumiert werden darf, weil wenn eine kleiner Stufe als Zahl folgt also die 2 beginnt in der Stückliste ein neues Bauteil. Irgendwie muß noch in die Formel rein, daß nur alle größeren Stufen bis zur nächst kleineren summiert werden dürfen. Aber wie?
=SUMME(INDIREKT("B"&ZEILE(A2:$A10002)&":B"&WENN(ISTFEHLER(VERGLEICH(A2;A3:A$10002;0)-1);10000;VERGLEICH(A2;A3:A$10002;0)-1+ZEILE())))
A B C D
2 0 20 20
3 0 20 20
4 10 10 10
4 10 20 10
2 0 30 30
3 10 10 10
3 0 20 20
4 10 10 10
4 10 10 10
PS: Wenns klappt ... ich habe das Bier kalt gestellt!
Gruß
Jürgen
Geht vielleicht, aber so ist glaube ich...
Hallo Jürgen,
also mit ner UDF ist das recht einfach:
Option Explicit
Function GetWert(rng As Range) As Double
Dim lngAkt As Long
lngAkt = 1
With rng
GetWert = .Offset(0, 1)
While .Value < .Offset(lngAkt, 0) And .Offset(lngAkt, 0) <> ""
GetWert = GetWert + .Offset(lngAkt, 1)
lngAkt = lngAkt + 1
Wend
End With
End
Function
Gruss
Markus
Ps.: Komme auf das Angebot Bier noch zurück. ;-)
Re: Geht vielleicht, aber so ist glaube ich...
Ja supi Marcus,
wenn du mir sagst wie man die UDF einsetzt dann gibts Bier! Damit habe ich noch nie gearbeitet. Also was muß ich tun. Schon mal danke für die tolle Hilfe.
Gruß
Jürgen
Re: Geht vielleicht, aber so ist glaube ich...
Hallo Jürgen,
die
Function im Visual-Basic-Editor (Alt + F11) in ein Modul-Blatt anlegen.
Danach steht sie Dir im Tabellenblatt wie alle anderen Excel-Functionen zur Verfügung.
Eingebunden z.B. in Spalte D über =GetWert(A1)
Wenn's noch nicht reicht, schicke ich Dir auch gerne mal ein Muster.
Gruss
Markus
Geht alles supi ... aber
Hi Marcus ja klasse ...,
so doof bin ich gar nicht klappt doch alles und das mit richtigem Ergebnis. Supi ... aber ein Haken gibts noch: Wenn ich die UDF in meine Strukturstückliste "einbaue" und 10 verschiedene Spalten berechnen lasse kommt die Formel ca in 80.000 Zellen vor. Mein Rechner hört gar nicht mehr auf zu rechnen ... unendliche Wartezeit. Kann man das beschleunigen?
Gruß
Jürgen
PS: Bier geht klar.
Mach's mit Makro
Hallo Jürgen,
bei sovielen Daten bietet sich eher ein Makro an:
Option Explicit
Sub Setzen()
Dim lngAkt As Long, lngLast As Long
Dim intAkt As Integer
Dim arr As Variant
arr = Array(1, 4, 7) 'Spaltennummern ...
For intAkt = 0 To UBound(arr)
lngLast = Cells(Rows.Count, arr(intAkt)).End(xlUp).Row
lngAkt = 1
For lngAkt = 1 To lngLast
Cells(lngAkt, arr(intAkt) + 2) = GetWert(Cells(lngAkt, arr(intAkt)))
Next lngAkt
Next intAkt
End
Sub
Function GetWert(rng As Range) As Double
Dim lngAkt As Long
lngAkt = 1
With rng
GetWert = .Offset(0, 1)
While .Value < .Offset(lngAkt, 0) And .Offset(lngAkt, 0) <> ""
GetWert = GetWert + .Offset(lngAkt, 1)
lngAkt = lngAkt + 1
Wend
End With
End
Function
Gruss
Markus
Mach's mit Makro ... oder?
Danke...,
wird aber noch viel viel langsamer. Werd mich irgendwie durchbeißen. Thank you very much.
Gruß
Jürgen
Re: Mach's mit Makro ... oder?
Hallo Jürgen,
schick mir mal ein paar Testdaten, vielleicht gibt's doch ne andere Möglichkeit.
Gruss
Markus