Supportnet / Forum / Tabellenkalkulation

Hallo zusammen, heute habe ich euch mal eine richig harte Nuss zu knacken. Schaut euch am besten die Excel Liste einmal an. Hier ein Screenshot: [url=http://www.bollenase.de/supportnet.jpg] Screenshot[/url] Eine PI stellt einen gewissen Guthabenstand dar. Dieser wird nach und nach durch KL´s abgebaut. Unter umständen wird er auch wieder durch X1 erhöht. Die Zuordnung welche KL nun mit welcher PF verrechnet wird stellt die Spalte Check Nummer dar. Nun soll in der Spalte P berechnet werden ob das Guthaben aufgebraucht ist oder nicht. Dazu soll er sich in der Spalte B6 das Guthaben (PF) herraussuchen, im Feld Check Nummer sich die dazu abgebuchten Rechnungen (KL) raussuchen (auch die erhöhungen X1) und mir dann schreiben vorhanden oder aufgebraucht. Ist das möglich? Vielen Dank mfg rebuss

so, im moment kann ich dir erst mal nur anbieten, es mit 2 Formeln zu machen!! Ich probiere natürlich noch weiter!!!

So, in Spalte F:
=WENN(B6="pi";E6;WENN(D6=D5;WENN(B6="kl";E6+F5;F5-E6)))
In Spalte G:
=wenn(F6=0;"Aufgebraucht";"vorhanden")

So long
Hope it helps

Michael

Hi rebuss !
Das wird wohl nur mit einem Makro machbar sein, weil man ja nie weiß, wieviele Zeilen zum einelnen PI zu berücksichtigen sind.
Stehen die Daten immer so schön untereinander, oder könnte auch hinter der letzten Eintragung zur CheckNr 6666 wieder eine zur CheckNr 1111 auftauchen ?
Um einen Schritt weiterzugehen :
Wenn die Reihenfolge nicht gewahrt bleibt - soll das Makro dann in einem neu sortieren ?
Wenn Dir sonst niemand einfacher helfen kann, mail mich mal an und beantworte dabei obige Fragen. Ich bin z.Zt. nicht regelmäßig im supportnet.
Gruß
Teddy

schliesse mich zum teil Teddy an, aber wenn es dir reicht, dass Excel dir hinschreib, wenn es verbraucht ist --> "verbraucht" und ansonsten die werte, dann funzt diese Formel (zumindest bei mir *ggg*)

=WENN(B6="pi";E6;WENN(D6=D5;WENN(UND(B6="kl";E6+F5=0);"verbraucht";WENN(B6="x1";F5-E6;F5+E6))))

Gruß
Michael

Hi Teddy,

nein die Daten stehen nicht immer so schön untereinander. Die Guthabennummer in der Spalte CheckNr. stehen immer durcheinander drin.

Eine Sortierung sollte nicht erfolgen.

Vielen Dank

mfg

rebuss

@Milka84: Danke für deine Formel. Diese werde ich natürlich auch ausprobieren.

jetzt trau ich mich auch mal 2 Praktikertipps:

Wie wäre es denn mit SUMMEWENN?
Guthaben (mit einem Minuszeichen) und Abbuchungen müßten eigentlich mit dieser Funktion summiert werden können. Je nachdem,ob das Ergebnis positiv oder negativ ist, kannst du dann über eine WENN-Funktion den entsprechenden Kommentar ausgeben.

Der Screenshot zeigt, das du mit Autofiltern arbeitest. Wenn du mit dem Autofilter die entsprechenden Datensätze filterst und über den Spaltenüberschriften z.B. mit der Funktion TEILSUMME die Summe bildest, müßte es eigentlich auch klappen.

Hope it klapp´s!
CU
Klaus

@milka: ich hatte im Hinblick auf die Überschrift schon förmlich darauf gewartet, dass du dich auf diesen Thread melden würdest und wie immer bei solchen "harten Nüssen" auch eine Klasse-Lösung hast. <binwiedermalbegeistert> ;-)

Also ich weiß jetzt nicht, ob ich das so richtig verstanden habe. Ich verstehe das so, pro checknumber gibt es einen Etat (PI), der aufgebraucht (KL) werden kann, jedoch auch aufgestockt (X1) werden kann.
Die Formel:
=SUMMEWENN($E$6:$L$100;E6;$L$6:$L$100)
liefert, wenn Du sie in P6 schreibst und nach unten ziehst, den Saldo bezogen auf die Checknumber. Der Bereich ist erstmal bis Zeile 100 festgelegt.
Man könnte auch bei direkt untereinander vorkommenden Checknummern in P7 folgende Formel schreiben:
=WENN(ODER(E7="";E6=E7);"";SUMMEWENN($E$6:$L$100;E7;$L$6:$L$100))
Natürlich nach unten ziehen, jetzt würde bei aufeinanderfolgenden identischen Checknummern nur in der ersten Zeile der Saldo erscheinen.
Wenn hier nur vorhanden oder aufgebraucht erscheinen soll, kann man ja mit einer zusätzlichen Wennbedingung arbeiten.

CU Aliba

@aliba: sorry, ich wollte dich natürlich nicht beleidigen.
Auch deine Tipps haben mir schon sehr oft geholfen oder mich in Erstaunen versetzt.

Cu
KLaus

danke Klaus, bin immer erfreut über positive Resonanz auf meine Beiträge!!!

Hi Klaus, habe mich in keinster Weise angesprochen oder gar beleidigt gefühlt.

CU Aliba

Hallo rebuss,

ich hätte da ein vorläufiges Makro anzubieten. Wenn die Check-Nummern sortiert wären könnte es so laufen:

Sub Addieren()
Dim SUM As Long
Dim j, i As Long

j = 1
i = Cells(Rows.Count, 1).End(xlUp).Row

For n = 6 To i
  If Cells(n, 3) = Cells(n + 1, 3) Then
    j = j + 1
    SUM = SUM + Cells(n, 4).Value
  Else
    SUM = SUM + Cells(n, 4).Value
    Cells(n - j + 1, 7).FormulaR1C1 = SUM
    j = 1
  End If
Next
End Sub

Wenn die Check Nummer nicht sortiert sind, vielleicht so:

Sub Addieren_2()
Dim SUM As Long
Dim i As Long
Dim m, n As Long

Dim CheckNr As Long

i = Cells(Rows.Count, 1).End(xlUp).Row

For n = 6 To i
  CheckNr = Cells(n, 3).Value
  SUM = 0
  For m = 6 To i
    If Cells(m, 3).Value = CheckNr Then
      SUM = SUM + Cells(m, 4).Value
      Cells(m, 7).FormulaR1C1 = SUM
      If SUM = 0 Then
        Cells(m, 7).FormulaR1C1 = CheckNr & " aufgebraucht"
      End If
    End If
  Next
  
Next

End Sub

Du musst auf jeden Fall noch die Spaltennummer anpassen an Deine Tabelle.
Darauf kann man jetzt Erweiterungen einführen, je nach belieben.

Gruß
Günter

Wau,

ihr seid einfach klasse. Nun habe ich soviele Tipps das ich fast nicht mehr weiss welchen ich benutzen soll. Die Formel von Milka habe ich probiert. Irgendwie scheint diese nicht zu funktionieren. Welche eine gute Lösung zu sein scheint ist die Summewenn Formel von Aliba. Sehr einfach gehalten und deshalb auch übersichtlich. Ich denke das ich diese benutzen werde (natürlich auch erst nach einem Test des Makros. Natürlich auch dir danke)

Allerdings noch eine kleine Frage zur Summewenn Formel. In meiner Original Excel Tabelle habe ich noch einen Document Type stehen (KK) stehen der nicht in die Berechnung der Summewenn Formel miteinbezogen werden soll. (Dieser hat einen positiven Betrag.) Auch die gleiche Check Nummer wie das Etat. Kann ich denn irgendwie ausschliesen?

Also sozusagen: Rechne mir alles zusammen das die gleiche Check Nummer hat ausgenommen den Document Type KK.

Vielen Dank

mfg

Kay Hensle

dann ich nochmal: wenn du das per Autofilter machst, kannst du das ganz einfach realisieren.
Und mit der Funktion TEILERGEBNIS über der Spaltenüberschrift wird dir dann direkt das Ergebnis angezeigt.

CU
Klaus

Hi Rebuss,

probier mal diese Formel:

=WENN(ODER(E6=E5;E6="");"";SUMMEWENN($E$6:$L$100;E6;$L$6:$L$100)-SUMMENPRODUKT(($B$6:$B$100="KK")*($E$6:$E$100=E6)*$L$6:$L$100))

Die KK-Einträge werden jetzt abgezogen. Wenn gleiche Check-Nummern untereinander stehen wird nur beim ersten Eintrag der Saldo angezeigt, ansonsten, wenn die Checknummer wieder auftaucht.

CU Aliba

Für alle Mitleser hier die Makro-Lösung :
----------------------
Sub Makro1()
Dim xlastrow As Integer
Dim xsumme As Double
Dim i1 As Integer
Dim i2 As Integer
Dim CheckNr As String
With Range("B1")
xlastrow = .SpecialCells(xlCellTypeLastCell).Row
End With
For i1 = 6 To xlastrow
If Cells(i1, 2) = "PI" Then
xsumme = 0
CheckNr = Cells(i1, 4)
For i2 = 6 To xlastrow
If Cells(i2, 5) = CheckNr Then
xsumme = xsumme + Cells(i2, 12).Value
End If
Next i2
If xsumme >= 0 Then
Cells(i1, 16) = "aufgebraucht"
Else
Cells(i1, 16) = " "
End If
End If
Next i1
End Sub
-------------------
Gruß
Teddy

hi alle

wow die ganze exel elite fast an borad fehlen ja nicht mehr viele das sind die klasse threads(ich kann kein englich).

gruss nighty

• Excel-Formel
• Excel Formel wenn formel mit 2 sverweisen
• Excel Formel: Verknüpfung von Arbeitsblätter durch Formel
• Excel Formel
• Excel Formel