Hallo Konca,
eine Lösung mit Standard-Excel-Tabellenfunktionen ist meines Wissens nicht möglich. Es ginge mir einer benutzerdefinieten VBA-Function.
Allerdings leidet dabei wegen der vielen Rechenoperationen und der relativ langsamen Code-Verarbeitung die
Handhabbarkeit der Tabellen, so dass man während der Dateneingabe unter Extras-Optionen das Berechnen vorübergehend auf Manuell statt Auomatisch einstellen sollte.
Besser und insgesamt wesentlich übersichtlicher wäre es, wenn du, wie im anderen Forum vorgeschlagen, die Buchungen für alle Konten als eine lange Liste in eine Tabelle eingibst, am besten für das gesamte Jahr.
Die Auswertungen für die Kategorien und Konten kannst du dann mit Pivottabellen-Berichten machen. Das ist wesentlich einfacher als der Riesenformelaufwand den du in jedem Monatsblatt betreibst.
Ich hab hier mal eine kleine Beispieldatei angehängt. In der Datei sind auch ein paar kleine Makros enthalten, die automatisch den Datenbereich der Pivottabellen anpassen und die Pivottabellen aktualisieren
https://www.herber.de/bbs/user/42783.xls
Gruß
Franz
Beispiel für Formeln mit der benutzerdefinierten Funktion. Bitte darauf achten, dass in jedem Block ZEILE($A1) wieder mit $A1 beginnen muss.
A B C
Tabellenblattname: Mai
A B C
60 AFR Freizeitausgaben (Snacks,Zig etc.)
61 Datum Ein-/Ausgaben Bemerkung
62 01.05.07 20 GEZ
63 01.05.07 -50 Kino und Essen
Benutzte Formeln:
A62: =Eintrag($A$60;ZEILE($A1);1;$A$8:$D$48;$E$8:$H$48;$I$8:$L$48)
A63: =Eintrag($A$60;ZEILE($A2);1;$A$8:$D$48;$E$8:$H$48;$I$8:$L$48)
B62: =Eintrag($A$60;ZEILE($A1);2;$A$8:$D$48;$E$8:$H$48;$I$8:$L$48)
B63: =Eintrag($A$60;ZEILE($A2);2;$A$8:$D$48;$E$8:$H$48;$I$8:$L$48)
C62: =Eintrag($A$60;ZEILE($A1);3;$A$8:$D$48;$E$8:$H$48;$I$8:$L$48)
C63: =Eintrag($A$60;ZEILE($A2);3;$A$8:$D$48;$E$8:$H$48;$I$8:$L$48)
Function Eintrag(Zuordnung$, Zeile%, Spalte%, Bereich1 As Range, Bereich2 As Range, Bereich3 As Range) As Variant
'Liest die Konten aus bezüglich Zuweisung der Buchung zu einem bestimmten Kriterium
'Zuordnung = Begriff des Daten im Block berechnet werden sollen
'Zeile = Nummer der Zeile im Ausgabeblock
'Spalte = Nummer der Spalte im Ausgabe Block, 1 =Datum, 2 = Betrag, 3 = Bemerkung
'Bereich1 = Eingabebereich für Konto 1(Spalten Datum, Betrag, Bemerkung und Zuordnung)
'Bereich2 = Eingabebereich für Konto 2(Spalten Datum, Betrag, Bemerkung und Zuordnung)
'Bereich3 = Eingabebereich für Konto 2(Spalten Datum, Betrag, Bemerkung und Zuordnung)
Dim Daten(), I%, J%, K%, Bereich As Range, varData()
'Einlesen der Daten, für die die Zuordnung erfüllt ist
I = 0
Set Bereich = Bereich1
For J = 1 To Bereich.Rows.Count
If UCase(Bereich(J, 4)) = UCase(Zuordnung) Then
I = I + 1
ReDim Preserve Daten(1 To 3, 1 To I)
Daten(1, I) = Bereich(J, 1)
Daten(2, I) = Bereich(J, 2)
Daten(3, I) = Bereich(J, 3)
End If
Next
Set Bereich = Bereich2
For J = 1 To Bereich.Rows.Count
If UCase(Bereich(J, 4)) = UCase(Zuordnung) Then
I = I + 1
ReDim Preserve Daten(1 To 3, 1 To I)
Daten(1, I) = Bereich(J, 1)
Daten(2, I) = Bereich(J, 2)
Daten(3, I) = Bereich(J, 3)
End If
Next
Set Bereich = Bereich3
For J = 1 To Bereich.Rows.Count
If UCase(Bereich(J, 4)) = UCase(Zuordnung) Then
I = I + 1
ReDim Preserve Daten(1 To 3, 1 To I)
Daten(1, I) = Bereich(J, 1)
Daten(2, I) = Bereich(J, 2)
Daten(3, I) = Bereich(J, 3)
End If
Next
If I = 0 Then 'Keine Daten für Zuordnung gefunden
Eintrag = ""
Else
'Datenmatrix invertieren und nach Spalte 1 (Datum sortieren)
ReDim varData(1 To I, 1 To 3)
For J = 1 To I
For K = 1 To 3
varData(J, K) = Daten(K, J)
Next K
Next J
If I > 1 Then
Call QuickSort(1, UBound(varData, 1), varData())
End If
'Wert ermitteln
If Zeile > I Then
Eintrag = ""
Else
If varData(Zeile, Spalte) = "" And Spalte = 3 Then 'leere Bemerkung
Eintrag = ""
Else
Eintrag = varData(Zeile, Spalte)
End If
End If
End If
End Function
Private Sub QuickSort(lngUgrenze As Long, lngOgrenze As Long, varFeld)
Dim lngIndex1 As Long, lngIndex2 As Long, varElement As Variant, varSpeicher As Variant, intSpalte As Integer
lngIndex1 = lngUgrenze
lngIndex2 = lngOgrenze
varSpeicher = varFeld(((lngUgrenze + lngOgrenze) / 2) \ 1, 1) 'der Wert hinter dem Komma ist die Spalte die sortiert wird
Do
Do While varFeld(lngIndex1, 1)
lngIndex1 = lngIndex1 + 1
Loop
Do While varSpeicher
lngIndex2 = lngIndex2 - 1
Loop
If lngIndex1
For intSpalte = 1 To 3 '3 Spalten übertragen
varElement = varFeld(lngIndex1, intSpalte)
varFeld(lngIndex1, intSpalte) = varFeld(lngIndex2, intSpalte)
varFeld(lngIndex2, intSpalte) = varElement
Next
lngIndex1 = lngIndex1 + 1
lngIndex2 = lngIndex2 - 1
End If
Loop Until lngIndex1 > lngIndex2
If lngUgrenze
If lngIndex1
End Sub