Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 12:08:33
Zelle/Spalte bestimmten Wert in Kopfzeile zuordnen
16.04.2024 12:01:17
Dynamische Liste generiert aus mehreren Vorgagen
16.04.2024 11:48:22
Blatt in HTML exportieren wenn Checkbox aktiv ist
16.04.2024 11:43:37
Mehrere Druckbereiche dynamisch festlegen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1716to1720
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1716to1720
1716to1720
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Tabellenabgleich

Tabellenabgleich
25.10.2019 11:11:45
Nick
Hallo in die Runde,
Ich bräuchte mal wieder eure Hilfe. Ich möchte gerne zwei Tabellen vergleichen. In diesem Fall die Tabellen „Planung“ und „Fehlerliste“ und Unterschiede bei betroffenen Datensätzen sichtbar machen.
Zu diesem Zweck habe ich mir folgendes Vorgehen überlegt: Wenn ein Artikel in der Datei Fehlerliste steht, wird dieser Artikel zunächst anhand der Artikelnummer und des Datums in der Datei Planung gesucht. In der Datei Planung hat jede Artikelnummer mehrere Datensätze für einen Tag, weil die Zwischenschritte des Arbeitsprozesses angezeigt werden. Anschließend soll aus der Datei Fehlerliste der Beginn und das Ende des Arbeitsprozesses mit dem Beginn und dem Ende des Arbeitsprozesses in der Datei Planung verglichen werden und alle Zeilen, die zwischen dem identischen Beginn und dem identischen Ende liegen, sollen in der Spalte Prüfung in der Datei Planung durch das Wort „Prüfen“ gekennzeichnet werden. Dies soll allerdings nur in den Zeilen geschehen, in welchen ein Z in der Spalte Verfügbarkeit steht. Zeilen, in denen ein Y in der Spalte Verfügbarkeit steht, sollen nicht markiert werden. Beispielhaft habe ich den Artikel 1111 am 18.09.19 so gekennzeichnet, wie es am Ende aussehen sollte.
Wenn jemand Lust und Zeit hat sich damit auseinanderzusetzen, würde ich mich sehr freuen. Meine Suche nach verwendbaren Schnipseln für mein Anliegen und meine bisherigen Versuche sind bisher sehr ernüchternd verlaufen. Vielen Dank im Voraus!
Die Tabellen sind unter folgenden Links zu finden:
https://www.herber.de/bbs/user/132742.xlsx
https://www.herber.de/bbs/user/132744.xlsx
LG Nick

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: verständl. wäre eine(!) Datei mit Tabellen ...
25.10.2019 13:13:28
neopa
Hallo Nick,
... anstelle Deiner zwei Dateien, die durch die Forumssoftware andere Dateinamen erhalten und wo Deine Blattnamen nicht mit Deinen Angaben vergleichbar sind. Also kopiere die beiden Blätter in eine Datei, benennen die Blattnamen eindeutig und stell die Datei neu ein. Dann sehen wir weiter.
Gruß Werner
.. , - ...
AW: Tabellenabgleich
25.10.2019 13:40:30
ChrisL
Hi Nick
https://www.herber.de/bbs/user/132747.xlsx
Ich habe versucht, die Anforderung zu verstehen. Leider ohne Erfolg d.h. das Resultat stimmt nicht überein und ich habe auch nach längerem Vergleich nicht verstanden, wie dein Musterresultat entstanden ist.
Dennoch schicke ich dir Power Query Lösung, da u.a. die Typenkonvertierung (Text-Datum) gelöst ist und vermutlich nur noch die richtigen Vergleichskriterien hinterlegt werden müssen.
Power Query Tutorials gibt es auf Youtube. Die Datenverbindungen können auch zu externen Datenquellen gemacht werden.
Ein Teil des Verständnisproblems ist, dass Datensätze zwischen Beginn und Ende liegen müssen, aber die Werte Texte sind.
cu
Chris
Anzeige
AW: nachgefragt ...
25.10.2019 13:53:32
neopa
Hallo Chris,
... ich wollte mich hier auch an einer PQ-Lösung versuchen, war aber auch darüber "abgestorben" dass ich die Zielstellung vom TE nicht nicht nachvollziehen konnte.
Hab aber noch eine Frage zu folgenden thread: https://www.herber.de/forum/archiv/1716to1720/t1719619.htm#1719679. Dort hab ich eine einfache PQ-Lösung erstellt. Diese hat momentan jedoch noch den Nachteil, das das gewünschte Ergebnis in einem neuem Tabellenblatt erstellt wird. Eine Lösung, wo letztendlich das neue Archiv das alte überschreibt, ist aber sehr wahrscheinlich die Zielstellung des TE.
Wüsstest Du da eine Lösung hierfür mit PQ. Aus formeltechnischer Sicht betrachtet, wird da so etwas wie ein Zirkelbezug notwendig. VBA-mäßig sicherlich keine Hürde, aber wie würde das mit PQ realisierbar sein?
Gruß Werner
.. , - ...
Anzeige
AW: nachgefragt ...
25.10.2019 15:13:22
ChrisL
Hi Werner
(Ich bin nicht sicher, ob der Link stimmt.)
Query = Abfrage
Gemäss meinem Verständnis soll/darf eine Abfrage die Ursprungsdaten grundsätzlich nicht verändern, sondern nur auswerten. Ich glaube darum nicht, dass Quelle und Ziel identisch sein können.
Ausnahmen sind Löschabfragen, aber diese habe ich in PQ noch nicht gefunden. Selbst wenn, würde ich diese i.d.R. nicht verwenden sondern eine Selektion vornehmen.
Meine Aussage basiert auf meinem Allgemeinwissen zu Abfragen (u.a. aufgrund Erfahrungen aus Access). Eine fundierte Wissensgrundlage dazu habe ich nicht.
cu
Chris
Anzeige
oT: hierzu ...
25.10.2019 15:52:35
neopa
Hallo Chris,
... der Link ist verweist schon auf den von mir gemeinten thread.
Danke für Deine Aussage, die somit meine geäußerte Vermutung zumindest momentan stützt.
Und noch kurz zu Deiner gestrigen PQ-Lösung hier: https://www.herber.de/forum/archiv/1716to1720/t1719066.htm
Ich konnte diese eben gut nachvollziehen. Mal sehen ob ich diese am WE noch ohne "spicken" nachstellen kann. Da würde ich dann auch gleich anstreben, die "phys." Hilfstabellen wegfallen zu lassen, in dem ich anstelle dessen nur eine Verbindung erzeuge.
Danke also auch dafür und ich wünsche Dir ein schönes WE.
Gruß Werner
.. , - ...
Anzeige
oT: passt
25.10.2019 18:47:18
ChrisL
Hi Werner
Danke für die Rückmeldung.
Dass man eine Verbindung herstellen kann, ohne gleich ein Zwischenergebnis zu speichern, ist mir bekannt. Wenn du es jedoch schaffst, die Hilfsabfrage (Max) ohne Zwischenspeicherung zu integrieren, wäre ich ebenfalls an der Lösung interessiert. Es hat mich bisher nie gestört, weil man die Tabellenblätter auch einfach ausblenden kann, aber der direkte Weg wäre natürlich eleganter.
Zur Fragestellung von vorher (können Abfragen die Quelldaten modifizieren?): Unsere Vermutung wird m.E. auch durch Wiki gestützt:
https://de.wikipedia.org/wiki/Abfragesprache
Implizit durch Aussagen wie:
Das Ergebnis einer Abfrage (Query) ist eine Teilmenge des zugrundeliegenden Informationsbestandes. Man spricht daher auch von einer Filterung der Daten.
https://de.wikipedia.org/wiki/SQL
Für PQ hilft m.E. ein rudimentäres Verständnis von SQL. SELECT (filtern), ORDER BY (sortieren), JOIN (hinzufügen), GROUP BY (aggregieren)
Viel Spass beim Tüfteln
cu
Chris
Anzeige
AW: oT: nun ...
26.10.2019 09:21:35
neopa
Hallo Chris,
... es ist mir zwar gelungen einen klein wenig modifizierten Weg zur Lösung aufstellen (u.a. auch ohne Hilfstabellenabspeicherung). Aber mit meinen rudimentären Kenntnissen zu PQ ist es mir momentan noch nicht gelungen ohne Abfragen-Zwischenspeicherung auszukommen. Doch ich denke, dass dies möglicherweise über eine benutzerdefinierte Spalte bzw. benutzerdefinierte Funktion möglich sein sollte.
Aber was noch nicht ist, kann ja noch werden.
Gruß Werner
.. , - ...
Neue Datei + neue Erklärung
26.10.2019 12:04:09
Nick
Guten Morgen,
Das war natürlich nicht so schlau von mir. Beide Dateien in einer wäre natürlich einfacher, das habe ich mal korrigiert und die neue Datei ist unter folgendem Link zu finden:
https://www.herber.de/bbs/user/132767.xlsx
Und ja, die Anforderung ist irgendwie komplex formuliert. Ich versuche es mal anhand des Beispiels 1111 am 18.09.2019 zu erklären.
In Tabelle1 beginnt der Weg des Artikels 1111 am 18.09.2019 im Lager an Position AA. Über AA geht es weiter an die Positionen DD - EE - BB - FF - GG - BBA. Da auf dem Weg zwischen AA und BB ein Fehler aufgetreten ist, ist dieser Abschnitt in Tabelle2 erfasst. Sofern der Fehler direkt aufgefallen wäre, wäre auf dem betroffenen Abschnitt in Tabelle1 ein "Y" in der Spalte "Verfügbarkeit". Da allerdings ein "Z" in der Spalte "Verfügbarkeit" hinterlegt ist, ist der Fehler erst im Nachhinein bemerkt worden und in Tabelle2 erfasst worden. Deswegen muss der Abschnitt AA - DD - EE - BB manuell geprüft werden, daher das "Prüfen" in der Spalte "Prüfung" der Tabelle1.
Ich hoffe, dass ich es so verständlicher machen konnte.
Die Lösung über die PowerQuery schaue ich mir nachher an und gebe nochmal konkrete Rückmeldung dazu. Auf jeden Fall ein Dank an dieser Stelle dafür.
LG Nick
Anzeige
AW: Neue Datei + neue Erklärung
27.10.2019 07:34:29
ChrisL
Hi Nick
Eine PQ-Lösung sollte zwar machbar sein, aber wird doch etwas komplizierter wie angenommen.
(ein Index vergeben und Datensätze zwischen Fehlerbeginn und Ende selektieren)
Wenn du etwas Zeit in PQ investieren möchtest, versuche ich mal eine neue Musterlösung zu erstellen.
Ansonsten kann ich dir auch eine Formel (WENN, ZÄHLENWENNS) anbieten. Die Spalten mit dem Datum musst du allerdings vorher angleichen. Die Daten in Tabelle1 sind bereits chronologisch sortiert?
cu
Chris
AW: dafür gibt es wohl auch weitere Gründe ...
27.10.2019 13:59:38
neopa
Hallo Chris, hallo Nick,
... dass eine PQ-Lösung hier zumindest für mich (bin auch nur Einsteiger in PQ) als nicht einfach zu bezeichnen ist.
Ich hatte zu einer vertretbaren Lösungsvorschlag auch mehrere Anläufe genommen, aber eine 100% Realisierung der Zielvorgabe ist mir nicht gelungen.
Ich habe aber etwas zusammengestrickt, was einerseits die "Probleme" der vorhandenen Daten aufzeigt (sieh mit PQ erstellte Prüfanmerkungen in Tabelle 2) und die Ergebnis-Prüfanmerkungen in Tabelle1 zumindest für den jeweiligen "Start" und oder "Ende" kennzeichnet.
Vielleicht ist mein Vorschlag hier: https://www.herber.de/bbs/user/132791.xlsx
aber schon ausreichend.
An Verbesserungen meines Lösungsvorschlags bin ich natürlich jederzeit interessiert.
Gruß Werner
.. , - ...
Anzeige
AW: dafür gibt es wohl auch weitere Gründe ...
28.10.2019 09:51:50
ChrisL
Hi
Hier eine neue PQ-Variante:
https://www.herber.de/bbs/user/132805.xlsx
1. Es gibt m.E. immer noch Inkonsistenzen in den Musterdaten z.B.
Fehler 18.9.20, 2222, 2, OO - BB
Soweit ich sehe, hat der Artikel die Arbeitsstation "OO" gar nie durchlaufen.
2. Vorgehen
- Index (Zeilennummer) für Planung vergeben
- Auf Tabelle Fehler, Beginn und Ende in eine Indexnummer umwandeln
- Fehler-Index an Planung hinzufügen
- Spalte mit Bedingung einfügen
#"Hinzugefügte bedingte Spalte" = Table.AddColumn(#"Erweiterte FehlerBereinigt", "Prüfung", each if ([Index] >= [#"FehlerBereinigt.Beginn.Index"] and [Index]
3. Der Vorschlag funktioniert, wenn es pro Tag/Artikel nur 1 Fehler gibt.
Mittels Aggregation der Fehler (min./max. Index) könnte man Fehler, die sich überschneiden noch mit berücksichtigen
funktioniert mit Aggregat:
Prozess A-B-C-D-E
Fehler1 A-C
Fehler2 B-D
funktioniert nicht:
Prozess A-B-C-D-E
Fehler1 A-B
Fehler2 D-E
cu
Chris
Anzeige
AW: dafür gibt es wohl auch weitere Gründe ...
28.10.2019 11:01:23
Nick
Guten Tag,
die Daten sind an einigen Stellen inkonsistent, das stimmt. Leider ist das kein Fehler meinerseits und die Daten werden tatsächlich so geliefert.
Das Ergebnis, welches die PQ-Lösung von Werner ausgibt, sieht schon ganz gut und damit könnte ich auch eventuell arbeiten. Allerdings habe ich bisher noch nie PQs gearbeitet und bin absoluter Neuling. Ich muss mal schauen, wie ich die Abfrage mit neuen Daten füttern kann, bzw. ob das überhaupt geht und was als Ergebnis geliefert wird. Das Gleiche gilt für die Lösung von Chris. Dass es nicht zu 100% funktioniert ist jetzt nicht tragisch. Natürlich wäre es besser, aber auch so würde viel Arbeit erspart werden. Für das Problem mehrerer Fehler an einem Tag müsste ich mich mir dann was einfallen lassen.
Aber mal eine andere Frage: Ist mein Ansatz zur Lösung des Problems der Richtige? Unterm Strich sollen am Ende die Unterschiede zwischen den Tabellen sichtbar gemacht werden. Das muss nicht zwangsweise über einen Prüfvermerk und meinen Vorschlag geschehen. Ich meine, vielleicht denke ich zu umständlich und es gibt eine intelligentere Lösung.
LG Nick
Anzeige
AW: dafür gibt es wohl auch weitere Gründe ...
28.10.2019 11:36:45
ChrisL
Hi Nick
Danke für die Rückmeldung.
Mit inkonsistenten Inputdaten wirst du natürlich nie ein konsistentes Ergebnis erhalten. Insofern wäre mir nicht ganz wohl bei einer solchen Datengrundlage.
Ist die Selektion der betroffenen Datensätze erst einmal erfolgt, dann kannst du das Ergebnis in x-beliebigen Varianten ausgeben (gefiltert, sortiert und dargestellt nach eigenem Gusto). Die Ausgabe ist somit aus meiner Sicht OK.
Eher Fragezeichen habe ich eher bei den Inputdaten. Einerseits die bereits angesprochenen Inkonsistenzen. Andererseits finde ich die Aufteilung des Arbeitsprozesses auf mehrere Datensätze etwas schwierig, zumal das Ergebnis von der Sortierung der Daten abhängt. Die zündende Idee fehlt mir allerdings im Moment ebenfalls (ich denke in Richtung, Arbeitsprozess AA-BB-usw. in Nummern umwandeln und als Muster-Prozess hinterlegen).
cu
Chris
AW: hierzu ...
28.10.2019 12:03:55
neopa
Hallo Chris,
... Dein Vorschlag mit INDEXieren der Zeilennummer kommt der Zielvorgabe näher als mein Versuch.
Aber schau Dir mal die Anzahl Deiner Datensätze im Vergleich zum Ist an. In der Abfrage "PlaningMatchFehler" müsste deshalb vor dem letzten Schritt noch
= Table.Distinct(#"Hinzugefügte bedingte Spalte", {"Index", "Datum", "Artikelnummer", "Beginn", "Ende"}) eingefügt werden.
Es bleibt dann noch das Problem mit den Inkonsistenzen in der Tabelle2 ("Fehler"). Möglicherweise liegen diese an ungeprüften Beispieldaten des TE. Ich hatte diese in meiner PQ-Datei mittels PQ dort aufgezeigt. Und in Tabelle 1 hatte ich dazu diese jeweils für den entweder vorhanden/übereinstimmenden Beginn- bzw. Ende-Station gelistet.
Du könntest das mE noch mit einer einer ergänzenden OR-Formel-Definition in Deinem vorletzten Schritt noch integrieren. Oder?
Gruß Werner
.. , - ...
AW: hierzu ...
28.10.2019 12:50:33
ChrisL
hi Werner
Ja die Bedingung könnte man noch mit OR ergänzen.
Vielleicht werden die Inkonsistenzen dadurch etwas weniger, aber es bleibt inkonsistent. Insofern tue ich mich mit der Ausgangslage etwas schwer.
cu
Chris
Verarbeitung inkosistenter Daten
28.10.2019 14:07:23
Nick
Hallo nochmal,
die Daten sind, obwohl sie inkonsistent sind, korrekt. Das habe ich mehrmals geprüft und wird auch zukünftig weiterhin vorkommen. Das Problem wird ab jetzt erstmal einfach so gehandhabt, wie es Werner aufgezeigt hat. Wenn der Beginn bzw. das Ende nicht vorhanden sind, soll für alle betroffenen Tagessätze eines Artikels "Fehler: Beginn nicht vorhanden" bzw. "Fehler: Ende nicht vorhanden" als Prüfvermerk ausgegeben werden.
Jetzt muss ich erstmal lernen, wie ich mit der PQ umzugehen habe, um sie mit meinen Daten zu füttern und so die Lösung von euch beiden zu kombinieren. Ist es denn überhaupt möglich die PQ mit neuen Daten zu befüllen und zu verwenden? Wenn ich bspw. die Fehlerliste mit anderen Werten befüllen und aktualisieren möchte, bekomme ich die Fehlermeldung, dass die Konvertierung in "Number" nicht möglich war.
LG Nick
AW: zu Deiner letzteren Fragestellung
28.10.2019 14:32:25
neopa
Hallo Nick,
... es ist normalerweise ein Vorteil von PQ, dass nach Erweiterung und oder Änderung der auszuwertenden Daten es nur eines Mausklicks auf das Datenaktualisierungsymbols bedarf.
Neue Daten sollten dazu natürlich die gleiche Datenstruktur und Formate besitzen, wie sie zur Basisauswertung vorlagen. Solltest Du neue Daten einkopieren, dann tue das mit den Werten (also ohne evtl. deren Datenformate).
Gruß Werner
.. , - ...
AW: Verarbeitung inkosistenter Daten
28.10.2019 19:47:17
ChrisL
Hi Nick
Wie Werner schon schreibt, eigentlich ein Feature von PQ :)
(insbesondere wenn die Datenverbindung direkt auf eine externe Quelle mit regelmässigem Datentyp geht)
Der guten Ordnung halber noch ein einmal der Hinweis, dass bereits je einmal die Formel ZÄHLENWENNS reicht, um Fehlerbeginn und -ende zu ermitteln. Geschickt mit ein paar WENN UND ODER verknüpft, fertig. Formeln haben in dem spezifischen Fall den Vorteil, dass du einfach eine Abhängigkeit zwischen dem aktuellen und dem vorherigen Datensatz erstellen kannst (sinngemäss z.B. wenn Zeile oberhalb gleich "prüfen" und aktuelle Zeile nicht Fehlerende, dann nochmal prüfen).
Inkonsistent und trotzdem korrekt, bedeutet die Daten sind jetzt konsistent oder mindestens konsistent inkonsistent. Damit kann ich leben :)
cu
Chris
@Werner: Danke für deine Beiträge. Ich konnte deinem Lösungsvorschlag ein paar Inputs (andere Herangehensweisen) entnehmen.
AW: Verarbeitung inkosistenter Daten
29.10.2019 11:59:58
Nick
Guten Tag Werner und Chris,
vielen Dank für eure bisherige Hilfe und Geduld mit mir. Ich habe es geschafft, dass ich den Lösungsvorschlag von Chris auf meine Formatierung angepasst habe (andere Spaltennamen). Diese habe ich in allen Abfragen korrekt umbenannt. Der PQ-Editor spuckt auch keinerlei Fehler aus. Allerdings habe ich noch einige Probleme damit und würde mich freuen, wenn mir noch einmal geholfen werden würde.
Der PQ-Editor spuckt zwar keine Fehler aus, aber irgendwie bekomme ich in der Tabelle "Ergebnis" keine Vermerke in der Spalte "Prüfung". Muss ich, abgesehen von der korrekten Umbenennung der Spalten, auf weitere Parameter im Code achten? In der Tabelle "FehlerMitIndex" bzw. "FehlerBereinigt" werden die Arbeitsschritte nicht indexiert, so wie es eigentlich passieren sollte. Da wird wohl der Fehler liegen, aber erklären kann ich es mir nicht.
Ein weiteres Problem ist, dass ich bei meinen Versuchen die Abfrage von Werner Table.Distinct(#"Hinzugefügte bedingte Spalte", {"Index", "Datum", "Artikelnummer", "Beginn", "Ende"}) einzubauen, die Fehlermeldung "Expression.SyntaxError: Erwartetes Token: Equal." im PQ-Editior erhalte. Die Meldung bekomme ich sowohl in meiner abgewandelten Lösung, als auch in der ursprünglichen Lösung von Chris. Weiß jemand, woran das liegen könnte?
An den Einbau der Formeln habe ich mich noch nicht gewagt und kann dazu noch keine Rückmeldung geben.
LG Nick
AW: so nicht nachvollziehbar ...
29.10.2019 13:32:13
neopa
Hallo Nick,
... ich vermute jedoch, dass Du übersehen/bzw. noch nicht berücksichtigt haben könntest, dass ich in meinem Vorschlag die maßgebl. Überschriften in Tabelle2 an die in Tabelle1 angepasst hatte und Chris in seiner Lösung die Tabellenblattnamen geändert hatte. Außerdem hatte ich das echte Datum der Tabelle2 überführt in die Textform der Datumsangabe in Tabelle1, während Chris genau den umgekehrten Weg beschritten hatte.
Sieh mal ob Dir das schon etwas weiterhilft.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige