Office: (Office 365) Mehrere Tabellen in einer zusammenfassen

Hallo,
ich möchte die Kontoauszüge von 4 unterschiedlichen Bankkonten (Tabelle 1-4) in einer Gesamttabelle per Formel kombinieren. Mein Ansatz: In der Gesamttabelle schreibe ich einfach ein fortlaufendes Datum. Dann mache ich für jedes Konto eine Spalte wo der Saldo zum jeweiligen Tag per xverweis aus Tabelle 1-4 in die Gesamttabelle reingeholt wird.

Das Problem dabei: Zu vielen Tagen gibt es gar keine Kontobewegung und keinen Saldo. Hier müsste also der "jüngste verfügbare Saldo vor dem Stichtag" eingefügt werden. An anderen Tagen gibt es mehrere Kontobewegungen. Hier würde ich dann den Saldo, der sich aus der letzten Bewegung ergibt, benötigen.

Ich suche bewusst eine Formellösung, weil ich die noch am wahrscheinlichsten nachvollziehen und anpassen kann. Mir ist klar, dass es per "Power Query", "Power Pivot" etc. vermutlich "easy" lösbar ist. Nur habe ich mich da leider noch nicht eingearbeitet :-(

Über jeden Tipp freut sich der
Frank

 

Hallo Frank,

Daten über Formeln zusammenfassen ist eher keine so gute Idee. Wäre doch eine gute Gelegenheit, sich mit PowerQuery zu beschäftigen.

 

Hola,
Xverweis() wobei das Suchkriterium Maxwenns() oder Minwenns() ist.
Mehr kann man dazu aktuell nicht sagen.
Gruß,
steve1da

 

Hallo,
vielen Dank für die Antworten. Ja, PowerQuery steht auf der ToDo-Liste. Ich habe es nun mit Maxwenns() versucht, bin aber mit der Formel gescheitert. Deshalb habe ich jetzt einmal eine Bespieldatei gemacht und dort die "Wunschwerte" eingetragen, die herauskommen sollen. Meine Formelversuche sind nicht da, weil es ohnehin nicht funktioniert hat... .
Viele Grüße
Frank

 

Moin,

diese Formel für B4 zum runterziehen:

=XVERWEIS(1;(H:H<=A4)*(H:H<>"");J:J;;;-1)

Für Spalte C, D und E die roten Teile entsprechend auf die Konten anpassen.

 

Moin!
Nur eine Anmerkung zum sehr guten Formelvorschlag:
Prinzipiell verarbeitet der XVERWEIS() problemlos ganze Spalten, da er nur den UsedRange berücksichtigt.
Anders sieht das imo aus, wenn Wahrheitswerte über ganze Spalten ermittelt werden.
Folgendes rechnet erheblich schneller:
(wobei die Endzeile 23 auch dynamisch errechnet werden könnte)
=XVERWEIS(1;(H$4:H$23<=A4)*(H$4:H$23<>"");J$4:J$23;;;-1)

Besser:
Man wandelt die Kontentabellen in Tabellenobjekte mittels Strg+t um und benutzt (dynamische!) strukturierte Verweise:
=XVERWEIS(WAHR;Tabelle2[[Datum ]]<=A4;Tabelle2[Saldo];;;-1)

Gruß Ralf

 

Moin,

wow, genial, macht genau was sie soll!
Falls du noch Lust hast: Ich verstehe die Idee so, dass in den Datumsspalten durch das (H:H<=A4) alle Datumswerte kleiner gleich dem Wert in Spalte A ausgewählt werden. Die Ausgewählten erhalten dann von XL den Wahrheitswert 1. Diesen multiplizierst du dann mit dem Wahrheitswert (H:H<>"") falls die betreffende Zelle nicht leer ist. Für XL steht dann da wahr*wahr und das ergibt 1?
Wenn ich mir die Formel im Dialogfeld "Funktion einfügen" anschaue, werden mir ja die Funktionsargumente gezeigt. Dort taucht deine "-1" vom Formelende jedoch gar nicht auf. Nach der Rückgabematrix J:J gibt es dort nur noch "Wenn_nicht_gefunden" und "Vergleichsmodus". Diese beiden Felder sind aber leer, weil du ja ;;; benutzt. In der Hilfe finde ich noch den "Suchmodus", der aber im Dialogfeld gar nicht aufgeführt ist. Dein Trick ist also, dass du "rückwärts" suchst um den jüngsten Wert bei mehreren Treffern zu erwischen?
Viele Grüße
Frank

 

Hi,

dann lösche mal die -1!

 

Hallo,

das stimmt nicht. Du musst nur im Dialogfeld hinunterscrollen, um auch noch das letzte Parameterfeld der Funktion XVERWEIS zu sehen!

 

ah, sorry, ich Trottel habe den Scrollbalken übersehen. Meine Formeln haben meistens weniger Paramerter....
Viele Grüße
Frank

 

Hallo nochmal,
ich habe festgestellt, dass ich in meiner Beispieldatei einen Fehler gemacht habe: Die Datumswerte bei zwei Konton sind absteigend - ich hatte sie bei allen 4 Konten aufsteigend dargestellt. Nun habe ich die Beispieldatei noch einmal entsprechend modifiziert und diverse Versuche unternommen, die Formel

entsprechend zu modifizieren. Leider bin ich gescheitert.

Es soll genau das selbe herauskommen, wie die Formel zurückgibt, wenn die Datumswerte absteigend sortiert sind. Bei mehreren Buchungen am selben Datum soll entsprechend der oberste Wert (also die kleinste Zeilennummer) geliefert werden. Deshalb habe ich den Suchmodus von -1 auf 1 gesetzt. Funktioniert aber genauso wenig wie meine anderen Versuche. Könntet ihr noch einmal schauen, wie die Synthax für absteigende Datumswerte lauten müsste? (Ergänztes Beispielsheet wo ich es bei Konto 4 probiere).

Natürlich könnte ich die Datumswerte auch jeweils manuell umsortieren. Das würde jedoch wieder andere Komplikationen hervorrufen.
Vielen Dank
Frank

 

Hi,

bei mir funktioniert es mit
=XVERWEIS(1;(X:X<=A4)*(X:X<>"");Z:Z;;1)

Es funktioniert natürlich nicht, wenn man an mehreren Stellen gleichzeitig ändert!

 

Hallo Edgar,
vielen Dank - funktioniert perfekt.
Irgendwie habe ich da noch einen Denk-Knoten weil ich erwartet habe, dass die selben Zahlen rauskommen, wenn Konto 4 nur anders sortiert ist. Ich schlafe nochmal drüber und vielleicht verstehe ich es morgen...
Viele Grüße
Frank

 

Hi,

Dein Fehler:
=XVERWEIS(1;(X:X>=A4)*(X:X<>"");Z:Z;;1)

 

Moin,

das war's! Nochmals herzlichen Dank für deine Mühe - jetzt läuft's perfekt!

ich habe diese automatischen Tabellen nicht im Einsatz. Das ist wie eine "Anfahrhilfe am Berg" - ein bisschen Hilfe und ein bisschen nervige Entmündigung. Aber dein Argument ist natürlich schlagend - es macht das Sheet in diesem Fall deutlich schneller. Da muss ich meine Vorurteile wohl mal überdenken. Vielen Dank für den Tipp!

Viele Grüße
Frank