Suchen vergleichen rechnen - lang und kompliziert

Bild

Betrifft: Suchen vergleichen rechnen - lang und kompliziert
von: Fritz
Geschrieben am: 09.10.2003 15:24:09

Hallo liebe Experten,

ich habe gestern einen ersten Anlauf wegen meines Problems gemacht und habe
einen Code von ChrisL bekommen (danke nochmals hierfür).
Leider macht der Code nicht das richtige, vielleicht habe ich mich zu undeutlich ausgedrückt, oder aber das Problem ist ZU kompliziert bzw. vielleicht sogar in Excel unlösbar.

Problem:
Tabelle mit 3 Blättern: Berechnung, Eingang, Ausgang

Blatt Eingang
Spalte Q: hier stehen die zu suchenden Größen (die können mehrfach vorkommen, d.h. wenn eine unter Ausgang stand kann sie unter einem späteren Datum im Eingang wieder auftauchen)
Spalte Y: das dazugehörige Datum des Eingangs

Blatt Ausgang
Spalte L: hier stehen die zu findenden Größen (können natürlich auch mehrfach vorkommen, da innerhalb des Auswertezeitraums (1 Jahr) mehrfach Ein- Ausgänge zu verzeichnen sind).
Spalte Y: das dazugehörige Datum des Ausgangs

Blatt Berechung
hier erscheint die Auswertung

Was passieren soll:
Es soll in Eingang Spalte Q der erste Eintrag gelesen werden, hier UBB1, das dazugehörige Datum aus Spalte Y ermittelt: hier 05.08.2003

Dann soll in Ausgang Spalte L nach eben diesem Eintrag (UBB1) gesucht werden, das dazugehörige Ausgangsdatum (hier 27.08.2003) gelesen und die Verweilzeit in Berechung ausgegeben werden:

IN Berechung soll erscheinen:
UBB1 23 Tage Verweilzeit


Dann soll wieder in Eingang der nächste Eintrag gelesen werden, hier IBC3, Datum 10.08.2003 und wiederum mit Ausgang verrechnet werden: hier IBC3, Datum 25.08.2003
Auf Berechnung erscheint: IBC3 16 Tage Verweilzeit

Der nächste IBC3 läuft identisch, Eingang 10.08.2003 Ausgang 25.08.2003

Der nächte ist wieder der UBB1 usw.

Ich habe die Tabelle stark vereinfacht, normalerweise sind die Suchbegriffe wesentlich länger als Ubb1, also z.B. UBB10967345


Das Ergebniss aus den Einträgen der Beispieltabelle sollte also so aussehen:

UBB1 23 Tage
IBC3 11 Tage
IBC3 11 Tage
UBB1 24 Tage
IBC3 23 Tage

Eingangs und Ausgangtag zählen immer mit.

Hier der Code von ChrisL

Option Explicit


Sub Vergleich()
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
Dim iZeile As Long, Zelle As Range, LetzteZeile As Long
Application.ScreenUpdating = False
Set WS1 = Worksheets("Berechnung")
Set WS2 = Worksheets("Eingang")
Set WS3 = Worksheets("Ausgang")
WS1.Range("A2:C65536").ClearContents
For iZeile = 3 To WS2.Range("Q65536").End(xlUp).Row
    If Application.WorksheetFunction.CountIf(WS3.Range("L3:L" & WS3.Range("L65536").End(xlUp).Row), WS2.Cells(iZeile, 17)) > 0 Then
    
        For Each Zelle In WS3.Range("L3:L" & WS3.Range("L65536").End(xlUp).Row)
            If Zelle = WS2.Cells(iZeile, 17) Then Exit For
        Next Zelle
        
        LetzteZeile = WS1.Range("A65536").End(xlUp).Row + 1
        WS1.Cells(LetzteZeile, 1) = Zelle
        WS1.Cells(LetzteZeile, 2) = Format(WS2.Cells(iZeile, 25) - WS3.Cells(Zelle.Row, 25), "dd")
        
        If Application.WorksheetFunction.Count(WS3.Range("L3:L" & WS3.Range("L65536").End(xlUp).Row), WS2.Cells(iZeile, 17)) > 1 Then _
        WS1.Cells(LetzteZeile, 3) = "Achtung Mehrfacheintrag in Tabelle " & WS3.Name
        
    End If
Next iZeile
Application.ScreenUpdating = True
End Sub



Als Ergebnis liefert er
UBB1 8
IBC3 15
IBC3 15
UBB1 5
IBC3 7


Das stimmt also etwas nicht - wer kann mit bei diesem kompliziertem Problem helfen??

Noch ein Hinweis:
Aufgrund meiner Rohdatenstruktur (Tabelle jährlich) kann es vorkommen das es einen Ausgang (z.B. 03.01.2003) gibt hedoch keine Eingang in der Tabelle, weil dieser im alten Jahr war (z.B. 12.12.2002)
Bei der Berechnung müßte (nach miner Logik) dabei eine negative Verweilzeit rauskommen, weil ja der bis zum nächten Eingang gesucht wird (z.B. 16.01.2003)
für die gesuchte Größe.
In so einem Fall (negativ) muß der erste gelesene Ausgang ignoriert werden und bis zum nächsten gesucht werden.

hier meine Datei:
https://www.herber.de/bbs/user/1357.xls

Danke an alle die sich den Kopf für mich zermartern wollen.

Gruß Fritz





Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Karl-Otto Reimann
Geschrieben am: 09.10.2003 15:43:09

Hallo Fritz
Nichts für ungut, aber Du solltest Dein Problem um diese
Uhrzeit in mehrere kleine aufteilen. Dann ist Hilfe viel einfacher.
mfG Ko


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Fritz
Geschrieben am: 09.10.2003 18:05:34

hi Ko,
leichter gesagt als getan - es ist eben sehr komplex.
Und da ich kein Programmierer bin habe ich dieses Verständnis wo ich das Problem teilen soll nicht.
Vielleicht hilft Martins Aussage und meine Antwort darauf. Wenn der Code erstmal richitg rechnet, also nicht die Tage falschrum abzieht, habe ich (bzw. Martin) Teil 1 ja gelöst und ich kann das Problem kleiner machen.
Wenn ich mit diesem Thread keine Lösung finde, wird mir aber zwangsweise nichts anderes übrig bleiben als das große Problem zu splitten.
Danke und Gruß
Fritz


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Martin Bolleter
Geschrieben am: 09.10.2003 17:16:08

Hallo Fritz

zunächst mal ist in deinem Code ein Fehler: Er zieht das Ausgangsdateum vom Eingangsdatum ab statt umgekehrt. Ausserdem macht die Format-Anweisung bei der Differenz auch noch was komisches (was ich nicht verstehe). Wenn du sie weglässt und noch +1 rechnest (da du Eingangs- und Ausgangstag mitzählen willst) kommen wenigstens mal nachvollziehbare Ergebnisse raus, nämlich 24 für UBB1 und 16 für IBC3 (wie du selber korrekt rechnest, nicht 11 wie du dann weiter unten angibst !!).
Dann fehlt in deiner Logik aber noch eine wichtige Angabe:
Wenn er zB für UBB1 im Blatt "Eingang" den (zB) 5. Eingang findet, muss er dann im Blatt "Ausgang" auch den 5. Eintrag zuordnen? Allgemein gesagt: wie kann festgelegt werden, welcher Eintrag im Ausgang zu einem gegebenen Eingang gehört? Insbesondere in den Fällen, wo am selben Datum mehrere Eingänge oder Ausgänge vorkommen können.

Um so was programmieren zu können braucht es wesentlich mehr und präzisere Angaben.

Gruss
Martin


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Fritz
Geschrieben am: 09.10.2003 17:57:53

Hi Martin
danke für deine Hinweise, mit den Änderungen kann ich ja wenigstens schonmal richtig rechnen.

Quote:
Wenn er zB für UBB1 im Blatt "Eingang" den (zB) 5. Eingang findet, muss er dann im Blatt "Ausgang" auch den 5. Eintrag zuordnen?
:Unquote

Ja, ich kann, wenn ich die Rohdaten auslesen, sozusagen von Beginn der Aufzeichnung im
Juni 2002, die Tabelle so hindrehen, das sich das so verhält. Das Prblem ist bei diesen Geschichten immer nur der Startpunkt, meine wenn bei Beginn der Aufzeichung schon ein Eingang drin war, der aber erst nach Beginn der Aufzeichung wieder raus ging.
Wenn so etwas fehlt werde ich einen fiktiven Eingang, als Start davorsetzen damit 5 = 5
bleibt

Quote:
Insbesondere in den Fällen, wo am selben Datum mehrere Eingänge oder Ausgänge vorkommen können
:unquote

müßte dann ja auch gleich bleiben, dann findet das Makro doch 5 zu 5 und 6 zu 6 für einen Tag, oder liege ich da falsch?

Denn Jahreswechsel muß ich dann eben auch vermeiden und die Tabelle so lange es geht weiterlaufen lassen (bei ca. 400 Zeilen pro Monat habe ich Luft)

Danke
Fritz


Ich kann jetzt leider keine konkreteren Daten uploaden, werde das aber morgen mal machen.


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Martin Bolleter
Geschrieben am: 09.10.2003 21:21:28

Hallo Fritz

genau das hatte ich befürchtet! Das heisst, dass das Programm sich die zusammengehörigen Ein- und Ausgänge selbst suchen muss, und dabei vermeiden, dass der selbe Ausgang zweimal für verschiedenen Eingänge verwendet wird.

Ich habe mal was versucht, das mit den bisherigen (plus eines von mir) Beispielen korrekte Resultate liefert. Versuchs mal mit deinen Originaldaten:
https://www.herber.de/bbs/user/1368.xls

Gruss
Martin


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Fritz
Geschrieben am: 10.10.2003 09:00:41

Hi Martin,
sieht klasse aus, danke dir.
Wenn allerdings ein Eingang OHNE zugehörigem Ausgang in der Splate steht bricht das Macro mit Laufzeitfehler 9 ab.

Es kommt aber ständig vor das zwar Eingänge aber keine Ausgänge vorhanden sind.

Gruß Fritz


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Martin Bolleter
Geschrieben am: 10.10.2003 09:56:30

Hallo Fritz

dann sag mir mal, wie du (und dann eben auch ein Programm) zu einem Eingang den richtigen Ausgang findest resp. entscheidest, dass es zu diesem keinen gibt, und was in diesem Fall zu tun ist. Der Ablauf muss so beschrieben sein, dass Entscheidungen ausschliesslich auf grund der verfügbaren Daten gefällt werden, nicht auf grund irgendwelchen Vorwissens (von Daten aus dem letzten Jahr) oder "aus Erfahrung".
Ansonsten lässt sich die Aufgabe nicht programmieren.

Gruss
Martin


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Fritz
Geschrieben am: 10.10.2003 10:22:25

Hi Martin,

nun ja - wenn du in der Tabelle, die du upgeloadet hast im Eingang noch eine weitere
Größe hinzufügst, aber im Ausgang nicht.

wenn die letzte z.B. IBC3 keine Gegenposition im Ausgang mehr hat ist sie noch auf Lager und kann dann auch (noch) nicht berechnet werden.

Das passiert natürlich bei jeder Größe erst im letzten Eingang:
z.B. IBC3 kann 40 mal ein und aus Gegangen sein, funkioniert in deinem Code prächtig,
wenn jetzt aber z.B. gerade heute der 41 Eingang war hat er ja noch keinen Ausgang (der käme dann meinentwegen nächste Woche.
Wenn ich dann für nächste Woche die Daten anfüge und dein Macro laufe lasse wird der 41 auch berechnet.

einfacher nachgestellter Fall:
https://www.herber.de/bbs/user/1378.xls

Gruß Fritz


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Martin Bolleter
Geschrieben am: 10.10.2003 14:42:06

Hallo Fritz

also, ich hab's noch mal versucht: Wenn er keinen Ausgang findet, schreibt er in der Tabelle "Berechnen": "Noch kein Ausgang".
Versuchs nochmal:
https://www.herber.de/bbs/user/1382.xls

Gruss
Martin


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Fritz
Geschrieben am: 10.10.2003 17:52:13

Hi Martin,

ich kann dir nur vielmals für deine Mühen danken.
Hab jetzt erstmal eine Woche Urlaub, werde den Code dann Testen und dir, wenn die wünscht, privat das Ergebnis mailen.

Gruß Fritz


Bild


Betrifft: AW: Suchen vergleichen rechnen - lang und kompliziert
von: Martin Bolleter
Geschrieben am: 10.10.2003 22:57:04

Hallo Fritz

schönen Urlaub!

Meine Mailadresse ist: martin.bolleter@datacomm.ch

Gruss
Martin


 Bild

Beiträge aus den Excel-Beispielen zum Thema " Suchen vergleichen rechnen - lang und kompliziert"