umfangreiche Abfrage

Bild

Betrifft: umfangreiche Abfrage
von: Chris
Geschrieben am: 02.06.2015 08:17:13

Hallo,
ich wollte fragen, ob ihr mir bei einem Problem helfen könnt.
Ich habs mit Excel-Formeln versucht, aber das ist ein riesen Aufwand, da muss ich 177x3 Formeln einzeln per Hand anpassen.
Muster-Datei habe ich angehängt:
https://www.herber.de/bbs/user/97979.xlsm
Im Sheet Karten werden etwa 177 Karten verwaltet, die an Mitarbeiter ausgegeben werden.
Der Name des Mitarbeiters (Spalten B,E,H,…), das Datum der Ausgabe (Spalten A,D,G,…) und das Datum der Bearbeitung bzw. Rückgabe (Spalten C, F, I,…) werden von mir entsprechend eingetragen. Karten können zurückgegeben oder auch von demselben Mitarbeiter nur als abgearbeitet eingetragen und dann wieder bearbeitet werden.
Die "1", die da noch eingetragen ist, hat für die Abfrage keine Bewantnis und kann ignoriert werden.
Im Sheet Auswertung sollen jetzt für jede Karte die jeweils letzten Einträge aus dem Sheet Karten jeder Karte übernommen werden.
Kurze Erklärung anhand der Karte Nr. 1:
Im Sheet Auswertung soll dann in B4 der letzte Name aus Sheet Karten Spalte B eingetragen werden (in dem Fall wäre es aus B8 „Peter Meier“), in C4 soll das letzte ausgegebene Datum aus Sheet Karten Spalte A eingetragen werden (in dem Fall wäre es aus A9 „01.04.2015“), in D4 soll das letzte bearbeitete Datum aus Sheet Karten Spalte A eingetragen werden (in dem Fall wäre es aus C9 „15.04.2015“), in diesem Fall ist die Karte dann nicht ausgegeben und in E4 würde „Nein“ stehen.
Und jetzt das Ganze bis Karte 177.
Habt ihr da eine Lösung für mich mit VBA?
Spalte E wäre eine Auswertung, wenn letzter Wert z.B. vom Sheet Karten Spalte A und C in derselben Zeile sind, dann würde da „Nein“ stehen, ansonsten steht dann „Ja“.
Wäre super, wenn ihr mir helfen könnt, ein bißchen kenn ich mich mit VBA aus, z.B. die Abfrage nach der letzten Zeile und dessen Wert krieg ich hin, nur bei Schleifen-Anweisungen da hab ich meine Probleme.
Ich hoffe, ich habs verständlich erklärt, wenn nicht, dann fragt einfach nach.
Vielen Dank schon mal vorab.

Bild

Betrifft: warum VBA, wenn es mit 4 Standard-Formeln geht ...
von: der neopa C
Geschrieben am: 02.06.2015 09:29:44
Hallo Chris,
... so (Formeln nach unten kopieren):

 ABCDE
3Nr.:NameAusgabeletzte Bearbeitungausgegeben
41Peter Meier01.04.201515.04.2015nein
52Stefan Heim31.05.2015 Ja
63   nein

Formeln der Tabelle
ZelleFormel
B4=WENN(C4="";"";INDEX(Karten!A:TK;VERGLEICH(C4;INDEX(Karten!A:TK;1;VERGLEICH(A4;Karten!$1:$1;)-2):INDEX(Karten!A:TK;999;VERGLEICH(A4;Karten!$1:$1;)-2); )-1;$A4*3-1))
C4=WENNFEHLER(VERWEIS(9^9;INDEX(Karten!A:TK;1;VERGLEICH(A4;Karten!$1:$1;)-2):INDEX(Karten!A:TK;999;VERGLEICH(A4;Karten!$1:$1;)-2)); "")
D4=WENN(C4="";0;INDEX(Karten!A:TK;VERGLEICH(C4;INDEX(Karten!A:TK;1;VERGLEICH(A4;Karten!$1:$1;)-2):INDEX(Karten!A:TK;999;VERGLEICH(A4;Karten!$1:$1;)-2); ); $A4*3))
E4=WENN((D4>0)+(C4=""); "nein";"Ja")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Bild

Betrifft: umfangreiche Abfrage
von: Chris
Geschrieben am: 02.06.2015 09:53:26
Hallo Werner,
Super Lösung, hätte nicht gedacht daß das auch so einfach mit einer Formel geht.
Hab nur das Problem, daß bei mir bei Karte Nr.2 in der Zelle D5 kein Leerfeld angezeigt wird so wie bei dir, sondern 00.01.1900, und zwar immer, wenn die Karte ausgegeben ist.
Ist vermutlich ein Formatierungsproblem, die Zelle ist formatiert auf Datum und egal was ich da einstelle bei Datum kommt immer was mit 0.1.19 oder ähnlich.
Woran könnte das noch liegen?
Gruß Chris

Bild

Betrifft: benutzerdefiniertes Zahlenformat zuweisen ...
von: der neopa C
Geschrieben am: 02.06.2015 09:59:32
Hallo Chris,
... hatte ich vergessen Dir mitzuteilen :-(
Ich habe den Zellen in Spalte D das benutzerdefinierte Zahlenformat: TT.MM.JJJJ;; zugewiesen.
Gruß Werner
.. , - ...

Bild

Betrifft: umfangreiche Abfrage
von: Chris
Geschrieben am: 02.06.2015 10:38:47
Hallo Werner,
super, jetzt funktioniert es.
Danke dir.
Gruß Chris

Bild

Betrifft: umfangreiche Abfrage
von: Chris
Geschrieben am: 05.06.2015 09:39:50
Hallo Werner,
könntest du mir nochmal helfen?
Ich mußte noch was anpassen, komme aber nicht mit den Formeln zurecht.
Hier die geänderte Datei:
https://www.herber.de/bbs/user/98044.xlsm
Ich habe noch die Spalte E eingefügt. Diese Spalte soll einfach nur das letzte Datum der Bearbeitung ausgeben z.B. im Sheet Karten Spalte C.
Außerdem hab ich noch im Sheet Karten in Spalte A neben dem Namen noch eine Zelle, in der 0 oder 1 steht.
0 bedeutet nicht ausgegeben, 1 bedeutet ausgegeben.
Dazu möchte ich die Auswertung für ausgegeben in Spalte F auf diese Zelle hin machen.
Kannst du mir bitte mit den Formeln helfen?
Danke schon mal vorab

Bild

Betrifft: vorhandene Formel entsprechend anpassen ...
von: der neopa C
Geschrieben am: 05.06.2015 11:10:33
Hallo Chris,
... die Formel aus C4 nach E4 kopieren und diese leicht anpassen zu:
=WENN(C4="";"";WENNFEHLER(VERWEIS(9^9;INDEX(Karten!A:TK;1;VERGLEICH(A4;Karten!$1:$1;)):INDEX(Karten!A:TK;999;VERGLEICH(A4;Karten!$1:$1;)));""))
Gruß Werner
.. , - ...

Bild

Betrifft: umfangreiche Abfrage
von: Chris
Geschrieben am: 05.06.2015 11:24:50
Vielen Dank Werner,
ich hatte versucht die Formel anzupassen, aber eine Bedingung falsch übernommen.
Könntest du mir noch die Formel für die Abfrage nach der 0 oder 1 machen?
Wäre echt super.
Danke di.r

Bild

Betrifft: AW: umfangreiche Abfrage
von: Chris
Geschrieben am: 05.06.2015 13:25:31
Hab jetzt eine andere Lösung gefunden.
Danke auf jeden Fall für deine/eure Unterstützung.
Mit der aktuellen Auswertung bin ich noch auf eine Sachse gestoßen, die ich noch bräuchte, aber ich weiß nicht, ob das mit Formeln lösbar ist oder mit VBA:
Und zwar bräuchte ich das Datum, wann der letzte gefundene Bearbeiter die Karte bekommen hat.
D.h. den letzten Namenseintrag finden (haben wir ja schon als Formel) und jetzt zurückgehen, bis der Name von hinten her das 1. Mal auftaucht und dann das Datum dieser Ausgabe (1 Zeile nach unten und 1 Spalte nach links) ein eine Zelle schreiben (in meiner Datei wäre das für Nr. 1 der 30.03.2015 in Zelle A7.
Hätte da jemand eine Lösung dafür?
Ich würde VBA auch akzeptieren.
Danke schon mal vorab.

Bild

Betrifft: geht mit Formel, zuvor aber noch nachgefragt ...
von: der neopa C
Geschrieben am: 05.06.2015 13:47:53
Hallo Chris,
... wenn in B4 auch "Peter Meier" stünde, wäre es dann wirklich auch der 30.03.2105 oder dann doch der 20.03.2015 den Du ermitteln willst
Analog, welches Datum wäre für Karte 2 zu ermitteln, wenn in E2 auch "Stefan Heim" stünde?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: geht mit Formel, zuvor aber noch nachgefragt ...
von: Chris
Geschrieben am: 05.06.2015 14:10:13
Hallo Werner,
richtig, wenn in B4 "Peter Meier" stehen würde, dann wäre es der 20.03.2015 und nicht der 30.03.2015.
Und wenn in E2 "Stefan Heim" stehen würde wäre es trotzdem der 01.05.2015 und nicht der 01.01.2015, weil dazwischen die Karte jemand anders hatte.
Ich benätige das dafür, wie lange eine Karte an den aktuellen Benutzer schon ausgegeben ist, teilweise sind Karten über 3-5 Jahre in Benutzung, es sollten aber nicht mehr als 2 Jahre sein. Wenn die Ksrte zwischendrin jemand anders hatte, dann ist das egal. Jetzt müssen alle Übersichten manuell geprüft und geschaut werden, wie lange die Karte schon an den aktuellen Benutzer ausgegeben ist und diese Arbeit macht sich derzeit keiner.
Deswegen die Auswertung.
Siehst du da eine Lösung?
Danke dir.
Chris

Bild

Betrifft: hatte ich es doch schon vermutet ...
von: der neopa C
Geschrieben am: 05.06.2015 15:35:15
Hallo Chris,
... demzufolge wäre nämlich Deine Aussage: "...bis der Name von hinten her das 1. Mal auftaucht" nicht zutreffend gewesen. Deshalb habe ich auch nachgefragt.
Folgender Formelvorschlag für das Auswertungstabellenblatt!G4: (nicht C4)
=WENNFEHLER(INDEX(Karten!A:TK;VERWEIS(9;1/(INDEX(Karten!A:TK;3;VERGLEICH(A4;Karten!$1:$1;)-1) :INDEX(Karten!A:TK;999;VERGLEICH(A4;Karten!$1:$1;)-1)=B4)/(INDEX(Karten!A:TK;1;VERGLEICH(A4; Karten!$1:$1;)-1):INDEX(Karten!A:TK;999;VERGLEICH(A4;Karten!$1:$1;)-1)<>B4);ZEILE(A:A)+3); VERGLEICH(A4;Karten!$1:$1;)-2);C4)
Allerdings ergibt die bisherige Ermittlung des Wertes in Spalte C für Dich keinen wirklichen Nutzen mehr. Da ich diesen Wert jetzt aber für die Ermittlung des neuen Datums mit nutze kannt Du diesen nicht einfach löschen. Ausblenden wäre aber möglich.
Normalerweise hätten sogar die gesamten Auswertungsformeln jetzt neu aufgestellt werden können, begonnen mit der Ermittlung des Namens und darauf basierend die Ermittlung der anderen Werte.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: hatte ich es doch schon vermutet ...
von: Chris
Geschrieben am: 05.06.2015 16:11:51
Super Werner,
funktioniert einwandfrei, ich bin begeistert.
Wie ich das dann auswerte muss ich noch sehen, aber ich hab zumindest den Wert
Danke nochmals.

Bild

Betrifft: AW: hatte ich es doch schon vermutet ...
von: Chris
Geschrieben am: 07.06.2015 22:10:15
Hallo Werner,
hab die Formeln jetzt mal komplett nach unten kopiert, so dass also alle 177 Karten ausgewertet werden.
Und wenn ich jetzt bei den Karten einen Eintrag mache, dann dauert es ein paar Sekunden bis der Eintrag ausgeführt ist, d.h. die komplette Datei ist sehr sehr träge und langsam geworden.
Es funktioniert zwar alles, aber extrem träge.
Hast du da vielleicht eine Lösung?
Oder muss ich vielleicht doch mit VBA die Lösung finden?
Gruß Chris.

Bild

Betrifft: dazu jetzt und hier ...
von: der neopa C
Geschrieben am: 08.06.2015 08:03:48
Hallo Chris,
... auf die Schnelle nur folgenden (jetzt meinerseits völlig ungetestet)Vorschlag: Teste mal, wenn Du die zuletzt eingebrachte Formel wie folgt abänderst:

=WENNFEHLER(INDEX(Karten!A$1:TK$199;VERWEIS(9;1/(INDEX(Karten!A$1:TK$199;3;VERGLEICH(A4; Karten!A$1:TK$1;)-1) :INDEX(Karten!A$1:TK$199;199;VERGLEICH(A4;Karten!A$1:TK$1;)-1)=B4) /(INDEX(Karten!A$1:TK$199;1;VERGLEICH(A4; Karten!A$1:TK$1;)-1):INDEX(Karten!A$1:TK$199;199; VERGLEICH(A4;Karten!A$1:TK$1;)-1)<>B4);ZEILE(A$1:A$199)+3); VERGLEICH(A4;Karten!A$1:TK$1;)-2);C4)
Und die anderen Formeln änderst Du analog auch. Wenn ich mich jetzt nicht irgendwo verschrieben habe, sollte ein erkennbarer Zeitgewinn erzielt sein.
Allerdings, wie ich bereits schon geschrieben hatte, hätte ich bei Kenntnis Deiner neuen zusätzlich gewünschten Auswertung, die Gesamtauswertung auch etwas anders strukturiert angegangen, welches wohl auch einen zusätzlichen Zeitgewinn ermöglicht hätte.
Gruß Werner
.. , - ...


Bild

Betrifft: AW: dazu jetzt und hier ...
von: Chris
Geschrieben am: 08.06.2015 08:13:35
Danke dir.
Ich werd die Formeln mal abändern und schauen obs was bringt.
Das Problem ist, daß er live auf das Sheet Karten zugreift und jeden Eintrag abfragt; das müßte er eigentlich gar nicht.
Es würde reichen, wenn er die Formeln bei Anklicken des Sheets Tabelle 2 1x durchläuft.
Gruß Christian

Bild

Betrifft: AW: dazu jetzt und hier ...
von: Chris
Geschrieben am: 08.06.2015 11:54:17
Wenn ich die Zellen fülle, wird es echt extrem langsam bei jedem Eintrag.
Schade, hat super geklappt.
Kennst du dich auch mit VBA aus?
Ich hab mal selber was probiert und habs so halbwegs hinbekommen.
Nur wieder diese Abfrage, wann die Karte das 1. Mal an den aktuellen Benutzer ausgegeben wurde, da häng ich derzeit.
Ich habs mit For-Schleife und if...then gemacht und da gehen ja leider nur 7 Verschachtelungen.
Nur mit der For-Schleife krieg ich es nicht hin.
Kannst du mir da weiterhelfen?
Gruß Christian

Bild

Betrifft: kann ich so nicht nachvollziehen ...
von: der neopa C
Geschrieben am: 08.06.2015 19:09:21
Hallo Chris,
... bei den relativ wenigen Datensätzen dürfte sich die Auswertung nicht derart langsam darstellen. Wenn Du willst und kannst, dann sende mir doch mal Deine Datei an:
=WECHSELN("neopaCode";"Co";ZEICHEN(64)&"email.")
Ich schau es mir dann Morgen mal an. Der thread wird hier ehe Morgen inaktiv im Archiv landen.
Gruß Werner
.. , - ...

Bild

Betrifft: könnte ich tun, doch es ist nicht notwendig ...
von: der neopa C
Geschrieben am: 05.06.2015 13:28:32
Hallo Chris,
... bereits die Formeln in den Zellen links der Namen in Karten!A:ZZ sind gar nicht notwendig, weil die Auswertung wie neulich auch bereits vorgenommen, direkt über die ermittelten Daten in der Auswertungstabelle erfolgen kann. In Spalte F ergibt sich immer dann ein "ja", wenn die Zelle unter "letzte Bearbeitung" in der gleichen Zeile leer ist und gleichzeitig eine Ausgabetermin ermittelt wurde.
Du könntest jetzt also in Auswertung!F4 einfach =WENN((D4=0)*(C4<>""); "ja";"") schreiben und diese Formel nach unten kopieren und Du erhältst Dein Zielergebnis. Die Formeln in Karten!A:A, Karten!D:D ... kannst Du eliminieren und die Zellen vielleicht anderweitig oder auch gar nicht nutzen .
Gruß Werner
.. , - ...

Bild

Betrifft: AW: könnte ich tun, doch es ist nicht notwendig ...
von: Chris
Geschrieben am: 05.06.2015 13:32:14
Danke Werner, ich hatte es dann gesehen und genauso gemacht wie du es vorgeschlagen hast.
Ich hatte das gerade vor etwa 2 Minuten geschrieben und noch eine weitere Auswertung gefagt, vielleicht kannst du dir das bitte kurz anschauen und schreiben, ob das mit Formeln möglich ist.
Danke dir.
Chris

 Bild

Beiträge aus den Excel-Beispielen zum Thema "umfangreiche Abfrage"