Das Archiv des Excel-Forums

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

Excel-Beispiele zum Thema " Suchen vergleichen rechnen - lang und kompliziert"

Zellinhalt suchen und Zelle auswählen download Suchbegriff über mehrere Tabellenblätter suchen. download
Suchen und weitersuchen download Zahl +/- 1 suchen download
Ein Zeichen in einer Formel suchen download Datum suchen und Wert eintragen download
Wert in Tabelle suchen und in UserForm ausgeben download Letzte Zelle mit Inhalt suchen download
Textdatei nach Begriff durchsuchen und Fundzeile importieren download Wert in Array suchen, ohne jedes Datenfeld abzufragen download
Wert in Tabellenblatt suchen und alle Fundwerte in zweite Tabelle download Begriffe in ausgeblendeter Spalte suchen und Fundzeile markieren download
Wert aus UserForm-TextBox in Tabelle suchen und in ListBox listen download Suchbegriff aus einer UserForm-Textbox in Tabelle suchen und melden download
Suchwert in zweiter Tabelle suchen und Fundzeile eintragen download Suchen von Nachnamen in Zeichenfolgen download
Dateien in einem Verzeichnis und Unterverzeichnissen suchen download Datum suchen und Adresse der Fundzelle ausgeben download
Begriffe in zweiter Tabelle suchen und Werte anfügen download Text aus UserForm-TextBox in Tabelle suchen download
Zweidimensionale Matrix mit der SVERWEIS-Funktion durchsuchen download Suchbegriff in vorhergehenden Tabellen suchen download
Werte aus UserForm-ComboBox suchen und Fundstelle kopieren download Neueste Textdatei eines Verzeichnisses suchen und umbenennen download
Artikelnummern suchen und jeweils letzte Fundstelle listen download Wörter eines Satzes in Indizliste suchen download
Wert aus UserForm-TextBox suchen und Fundstelle kopieren download Zellwert nach Doppelklick in Tabelle suchen download
Suchen in 2. Tabelle und Ersetzen der Quelldaten download Wert in Tabelle 2 suchen und Fundstellen übertragen download
Suchbegriff in Tabelle suchen und Fundzeilen auswählen download Suchbegriffe aus Tabelle im Excel-WebArchiv suchen download
Wert aus ComboBox in TextBox, dann in Tabelle suchen download Artikelnummer über InputBox suchen und eintragen download
Anwendung auf Fesplatte suchen download Tabelle nach ComboBox-Auswahl durchsuchen download
Werte suchen, eintragen und löschen download Zeile 1 nach Wert der aktiven Zelle durchsuchen download
Suchbegriff in Tabelle suchen und Fundzeilenindex zurückgeben download Wert in zweiter Tabelle suchen und als Kommentar ausgeben download
Wert in externer Mappe suchen und mit Format übernehmen. download Artikelnummern nach Kriterien suchen und Datensätze kopieren download
Wert in ComboBox einlesen, suchen, editieren, ausgeben download Grafikdateien suchen download
Suchen der Nachbarzelle der Zelle mit dem Maximalwert download Text in Textdateien suchen und Fundzeilen in Tabelle ausgeben download
Begriff suchen und Fundzeilen in anderes Blatt kopieren download Zahl suchen und an den Fundstellen Zeile einfügen download
Name und Vorname suchen, Werte und Fundstellen importieren download Begriff in der ersten Spalte einer ListBox suchen download
Zellen vergleichen und markieren download Tabellen vergleichen download
Spalten A und B vergleichen und Doppel in C listen download Werte vergleichen download
Bereiche auf Übereinstimmung vergleichen download Daten in jeweils einer Spalte zweier Tabellen vergleichen download
Werte vergleichen und Ergebnis in Tabelle eintragen download Tabellen vergleichen und Zeilen ersetzen download
Tabellen vergleichen und bei Nichtübereinstimmung markieren download Textdateien vergleichen und Unterschiede dokumentieren download
Spalte A mit B vergleichen und Doppel in C eintragen download Telefonnumern mit Liste vergleichen und durch Namen ersetzen download
Werte vergleichen und übertragen download Teilstrings vergleichen und markieren download
Textmuster vergleichen download Quersumme berechnen download
Arbeitsmappe alle 5 Minuten neu berechnen download Wert aus 2. UserForm-ComboBox-Spalte berechnen download
Mit TextBox-Werten in UserForm rechnen download Portokosten aus einer Gewichts-/Gebietsmatrix errechnen download
Aktuelle Zeit über UserForm eintragen und Rennzeit berechnen download Verbleibende Nettoarbeitstage des Monats berechnen download
Zeitdifferenz in Schulstunden (45 Minuten) errechnen download Schulnote anhand einer Punktetabelle errechnen download
Modalwert einer gefilterten Reihe berechnen download Werte summieren, in erste freie Zeile eintragen, Differenz errechnen download
Werte aus Zeichenfolgen filtern und berechnen download Rechnen mit Zehntausendstel-Sekunden download
Zuschläge gem. Tabelle berechnen download TextBox-Eingabe mit Formel weiterberechnen und zurückgeben download
Eine WENN-Formel nur bei der ersten Werteänderung berechnen download Mehrwertsteuer hinzu- oder abrechnen download
Bei ComboBox-Auswahl Zellformel neu berechnen download Vorgegebenes Datum plus 33 1/3 Jahr errechnen download
Nachtstunden ermitteln und Lohn errechnen download Fracht gem. Entfernung und Gewicht berechnen download
Median ohne ausgeblendete Zellen berechnen download Km/h errechnen download
Schenkungssteuern in einer UserForm berechnen download