Excel, bestimmte Daten aus einer großen Excel Tabelle nach mehreren Kriterien filtern?
Hallo zusammen,
ich mache gerade meine Ausbildung im Bereich der Bauindustrie.
Nun habe ich folgendes Problem:
Ich möchte ein Deponietagebuch in Excel automatisieren.
Auf der Deponie wird in unregelmäßigen Abständen von verschiedenen Baustellen Material angeliefert.
Es gibt folgende Möglichkeiten was auf der Deponie passieren kann:
- Es wird nichts angeliefert
- Es wird von einer Baustelle eine Fuhre mit einer Art von Material angeliefert
- Es werden von einer Baustelle mehrere Fuhren mit verschieden Arten von Material angeliefert.
- Es werden von Mehreren Baustellen mehrere Fuhren von einer Art von Material angeliefert
- Es werden von mehreren Baustellen mehrere Fuhren mit verschiedenen Arten von Material angeliefert.
Am Ende sollte es so oder so ähnlich sein:
- Wenn nichts angeliefert wird erscheint das Datum mit dem Vermerk das nichts geliefert wurde
- Wenn eine Fuhre mit einer Art Materialien angeliefert wird, das Datum mit der Fuhre und weiteren Infos angezeigt wird( siehe Abbildung 2 )
- Wenn von einer Baustelle mehrere Fuhren mit mehreren Arten von Material angeliefert wird von jeder der verschiedenen Art an Material eine Fuhre angezeigt wird.
- Wenn von mehreren Baustellen mehrere Fuhren von Material angeliefert werden pro Baustelle und pro verschiedener Art von Material jeweils eine Fuhre angezeigt wird.
Auf dem Bild ist die Excel abgebildet aus der ich meine Informationen habe. Das Material X wird am 09.01.2025 von Baustelle X mit der Kostenstelle X zur Deponie X gefahren.
Und auf diesem Bild ist dann eben die Excel abgebildet wo die Anlieferung von Material auf die Deponie AB automatisch eingetragen werden soll.
Nochmal ein paar Infos zum allgemeinen Verständnis:
- Die Liste aus der ich meine Informationen habe ist real mehrere 10tausend Zeilen groß. Es gibt c.a 100 verschiedene Deponien.
- Die Kostenstelle bleibt pro Baustelle immer gleich, genau so wie die Bezeichnung für die Deponien.
Ich weis selbst, dass das Problem hier die absolute Königsklasse in Excel erfordert.
Ich selbst arbeitete auf Excel auch sehr viel und gut, bin bei dem Problem jedoch an meine Grenzen gestoßen, wo es darum ging das ja an einem Tag auch von mehreren Baustellen verschiedene Arten von Material geliefert werden können. Also letztendlich habe ich es geschafft wenn in der Theorie immer nur an einem Tag von einer Baustelle eine Art von Material geliefert werden würde.
Falls jemand noch irgendwelche weiteren Infos zum Verständnis benötigt dann werde ich zügig und ausführlich antworten.
Ich bin für jede Hilfe dankbar!
Liebe Grüße
Ich möchte ein Deponietagebuch in Excel automatisieren.
- In Form eines Soll/Ist-Abgleiches ?
- Wenn ja, für welche Zeiträume (tgl./wchtl./mtl.) ?
- Mit Soll/Ist-Abgleich kenne ich mich nicht aus
- mtl.
Warum soll bei "Nichtlieferung" ein Datum stehen, wenn keine Lieferung erwartet wird? Oder werden alle "Produkte" bestellt und sollen geliefert werden?
Weil auch bei "nicht Lieferung" die Zeile benötigt wird um andere Vermerke per Hand noch hinzuzufügen(zb. wird auf der Deponie ja auch gearbeitet wenn nichts geliefert wird)
wie immer die wichtigste Frage? Welche Excel Version kommt zum Einsatz 2019 oder > 2019, bei 2019 kann ich dir jetzt schon sagen wird es sehr mühsam.
Moin, ja ich habe 2021 :) bin grad dabei deine Lösung auf mein Problem anzuwenden, ich gebe dir Bescheid ob e am Ende geklappt hat! schon mal vielen dank
1 Antwort
30.01.25 11:00 Uhr ich habe die Erklärung nochmal etwas verfeinert.
In der Hoffnung, dass bei dir mindestens Excel 2021 zum Einsatz kommt (ich glaube Zeilenwahl war auch schon Bestandteil von Excel 2021) habe ich eigentlich eine recht schnelle Lösung gefunden:
Hier mal die Formeln aufgeschlüsselt:
A15:
- in dem ersten Prüfkriterium [A14="Datum"] schaue ich ob in der Zelle darüber Datum steht, ist dies der Fall handelt es sich logischer Weise um den Spaltenkopf, somit soll der 1. Eintrag der 1.1. des in G1 definierten Jahres sein [DATUM(G1;1;1)].
- sollte das Datum der Zelle oben drüber (dieses Prüfkriterium kommt natürlich erst ab der 2. Zeile zum tragen) in der anderen Tabelle (bei mir Logbericht) mehr als 1x vorkommen [ZÄHLENWENNS(Logbericht[Datum];A14)>1]. Dann soll die Anzahl gezählt werden[SUMME(($A$14:$A14=A14)*1)]. Jetzt wird die Anzahl in der oberen Tabelle mit der in der unteren Tabelle verglichen bis 1 Zeile über dem aktuellen Eintrag ist der in der oberen Tabelle größer, wird der gleiche Tag wie oben drüber ausgegeben, [A14] ist die Anzahl identisch wird der darauffolgende Tag ausgegeben [A14+1].
- die [1;A14+1] am Ende ist für den Fall da, dass keine der beiden Bedingungen zuvor wahr ist, dann wird der nächste Tag ausgegeben. Findet an allen Tagen mit nur einem Eintrag Anwendung.
B15:
- das kannst du theoretisch ignorieren, dies brauchte ich nur, da in der Excel Webversion das Datumsformat TTTT nicht zur Verfügung steht, daher musste ich es über die Formel lösen.
C15:
- Hier definiere ich in der Filter-Funktion als erstes mal die Ausgabespalte der oberen Tabelle, dies soll nämlich, die sein, die den gleichen Spaltenkopf hat wie die eigenen Spalte [INDIREKT("Logbericht["&C$14&"]")]. sprich indirekt macht nichts anderes als diesen String zusammenzusetzen Logbericht[+Jahr+"]" und übergibt den Inhalt von diesem Bezug an den Filter. Im Anschluss Filtere ich nur jene Daten heraus, die das gleiche Datum haben wie das Datum der aktuellen Zeile [[@Datum]=Logbericht[Datum]] Das @ steht dabei in einer intelligenten Tabelle für die gleiche Zeile wie die, in der die Formel steht, wird nichts gefunden wird der entsprechende Text ["keine Lieferung"]ausgegeben.
- Je nach dem wie viele Einträge oben in der Liste mit dem gleichen Datum vorhanden sind, ist es also möglich das zwischen 1 und X Zeilen ausgegeben werden würden, in eine Zelle passt natürlich nur ein Eintrag (andernfalls erhält man die Fehlermeldung #Überlauf, sofern in der in irgend einer der Zellen darunter, die benötigt werden würden, schon Daten stehen, da wir hier in jeder Zeile einen Eintrag haben, also immer sobald mindestens 2 Daten mit gleichen Datum in der oberen Tabelle gefunden werden. Also zähle ich Mittels der gleichen Formel wie vorhin schon, mit dem einzigen Unterschied, diesmal bis zur aktuellen Zelle, nicht bis eine Zelle drüber, wie viele Einträge mit dem gleichen Datum vorhanden sind, ist dies der erste Eintrag, wird über Zeilenwahl [=ZEILENWAHL(Filter(...);ZÄHLENWENNS($A$15:$A15;$A15))] auch die erste Zeile ausgegeben. Es werden auf diese Weise also nacheinander alle Zeilen abgearbeitet egal wie viele es sind.
Im Prinzip war das schon die ganze Magie also eigentlich nix sonderlich kompliziertes die Formel in D15 und E15 ist prinzipiell komplett identisch einfach Copy and Paste. Da sich die passende Bezugsspalte über indirekt dynamisch ermittelt wird. ACHTUNG: die Spalten müssen in den Tabellen die identischen Namen haben, änderst du hier also etwas laufen die Formeln ins Leere und werden #Bezug ausgeben.
Ich würde mich über Rückmeldung freuen, ob dir noch was fehlt oder noch etwas unklar ist. Du kannst das gerne auf Herz und Nieren testen ob ich auf die Schnelle irgendwas übersehen habe.
LG
was ist denn der Fehler?
Du musst natürlich die passende Tabelle angeben, Logbericht war ja nur mein Name am besten in jeden Zellbezug, den ich gesetzt habe reinklicken und durch deinen ersetzten, dann solltest du da schonmal keine Fehler mehr drin haben. Ein anderes Tabellenblatt sollte nach meiner Erfahrung keinen Unterschied machen, da du ja über Logbericht[Spaltenkopfname] kein Tabellenballt ansprichst sondern direkt die Tabelle.
Wenn du willst kannst du deine Tabelle auch mal einkürzen und anonymisieren und hier als Downloadlink teilen, dann kann ich mal schauen ob ich den Fehler finde.
okay also es klappt leider noch nicht. Kann es sein das wenn der Logbericht auf einem anderen Tabellenblatt steht das dann so nicht funktioniert?