PDA

Vollständige Version anzeigen : Verweis mit mehreren Kriterien und Datumsvergleich


Krokos
21.07.2014, 06:47
Hallo zusammen

Ich habe folgendes Problem, was mir ziemlich zu schaffen macht:

Ich habe zwei Tabellen:
In der ersten Tabelle habe ich in der ersten Spalte mehrere Kategorien (Rot, Blau, Gelb etc.) und in der zweiten Spalte verschiedene Daten (TT.MM.YYYY).

In einer zweiten Tabelle habe ich dieselben Kategorien aber in der zweiten Spalte andere Daten. In der dritten Spalte sind dann die Werte, die ich gerne der ersten Tabelle zuordnen möchte. Die Zuordnungskritieren sind die Kategorien und innerhalb der Kategorien die Daten. Es soll immer der Wert zugeordnet werden, der mit der Kategorie und dem Datum übereinstimmt, oder nur mit der Kategorie übereinstimmt und mit dem Datum, das am nächsten vor dem Datum der ersten Tabelle liegt.

Beispiel
Tabelle 1
Rot 07.08.2005 (gesuchter Wert)


Tabelle 2
Rot 18.04.2005 drei
Blau 11.06.2005 acht
Rot 06.08.2005 eins


Der gesuchte Wert wäre „eins“. Er stimmt nicht genau mit dem Datum der ersten Tabelle überein, ist dafür aber am nächsten DAVOR.
Ich hoffe Ihr könnt mir da irgendwie weiterhelfen!

Beste Grüsse
Krokos

Mc Santa
21.07.2014, 09:02
Hallo,

ich denke das geht nur, wenn du nach Farbe und Datum sortierst, und dann eine Matrixformel für die Auswertung nutzt. Beachte dazu die Hinweise am Ende.

<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#92D050 ;; text-align:left; ">Daten</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#92D050 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#92D050 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00B0F0 ;; text-align:left; ">Auswertung</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00B0F0 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00B0F0 ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">blau</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">11.06.2005</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">eins</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">rot</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">17.04.2005</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">zwei</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">rot</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">06.04.2005</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">zwei</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">rot</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">18.04.2005</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">drei</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>G2</td><td>{=SVERWEIS(F2;WENN($A$2:$A$4=E2;$B$2:$C$4);2;1)}</td><td>$G$2</td><td>&nbsp;</td></tr></table><b>{} Matrixformel mit Strg+Umschalt+Enter abschließen<br/>Matrixformeln sind durch geschweifte Klammern {} eingeschlossen<br/>Diese Klammern nicht eingeben!!</b><br/><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>

Hilft dir das weiter?

VG

Hasso
21.07.2014, 09:21
Hallo Krokos,

ich habe das mit einer benutzerdefinierten Funktion (UDF) gelöst:Option Explicit

Function Krokos(rngZelle As Range) As String

Dim rngSuchwert As Range
Dim lngLetzteZeile As Long
Dim lngDatumsdifferenz As Long

'letzte Zeile in Tabelle2 ermitteln:
lngLetzteZeile = Worksheets("Tabelle2").Cells(Rows.Count, 1).End(xlUp).Row

'Datumsdifferenz auf größtmöglichen Wert setzen:
lngDatumsdifferenz = CLng(Date)
For Each rngSuchwert In Worksheets("Tabelle2").Range("A2:A" & lngLetzteZeile)
'wenn in Spalte A auf beiden Blättern der gleiche Wert steht:
If rngSuchwert.Value = rngZelle.Value Then
'wenn die Datumsdifferenz kleiner als die bisherige Datumsdifferenz ist:
If Abs(rngZelle.Offset(0, 1).Value - rngSuchwert.Offset(0, 1).Value) < lngDatumsdifferenz Then
lngDatumsdifferenz = Abs(rngZelle.Offset(0, 1).Value - rngSuchwert.Offset(0, 1).Value)
'Wert aus Spalte C in Tabelle2 zurückgeben:
Krokos = rngSuchwert.Offset(0, 2).Value
End If
End If
Next rngSuchwert

End Function

Mc Santa
21.07.2014, 09:32
Hasso, bei dir habe ich einen Fehler gefunden:
<br/><b><em>Tabelle2</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Blau</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">21.07.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Tausend</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Rot</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">18.04.2005</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">drei</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Blau</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">11.06.2005</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">acht</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Rot</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">06.04.2005</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">hundert</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>

<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Ergebnis</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Erwartet</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Rot</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">17.04.2005</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF6600 ;; text-align:left; ">drei</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:left; ">hundert</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>C2</td><td>=Krokos(A2)</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>

Viele Grüße

Hasso
21.07.2014, 10:24
Danke McSanta,

ich hatte verstanden, dass das Datum auf Tabelle2 dem in Tabelle1 am nächsten kommt - aber tatsächlich hieß die Bedingungdas am nächsten vor dem Datum der ersten Tabelle liegtHabe das überarbeitet:Option Explicit

Function Krokos(rngZelle As Range) As String

Dim rngSuchwert As Range
Dim lngLetzteZeile As Long
Dim lngDatumsdifferenz As Long

'letzte Zeile in Tabelle2 ermitteln:
lngLetzteZeile = Worksheets("Tabelle2").Cells(Rows.Count, 1).End(xlUp).Row

'Datumsdifferenz auf größtmöglichen Wert setzen:
lngDatumsdifferenz = CLng(Date)
For Each rngSuchwert In Worksheets("Tabelle2").Range("A2:A" & lngLetzteZeile)
'wenn in Spalte A auf beiden Blättern der gleiche Wert steht:
If rngSuchwert.Value = rngZelle.Value Then
'wenn die Datumsdifferenz kleiner als die bisherige Datumsdifferenz ist:
If Abs(rngZelle.Offset(0, 1).Value - rngSuchwert.Offset(0, 1).Value) < lngDatumsdifferenz _
And rngZelle.Offset(0, 1).Value > rngSuchwert.Offset(0, 1).Value Then
lngDatumsdifferenz = Abs(rngZelle.Offset(0, 1).Value - rngSuchwert.Offset(0, 1).Value)
'Wert aus Spalte C in Tabelle2 zurückgeben:
Krokos = rngSuchwert.Offset(0, 2).Value
End If
End If
Next rngSuchwert

End Function

Josef B
21.07.2014, 18:02
Hallo

Für alle die schon ihre kosbare Zeit für Krokos vergeudet haben. Siehe

http://www.online-excel.de/fom/fo_read.php?f=1&bzh=79054&h=79026&ao=1#a123x

oder:

http://www.office-loesung.de/p/viewtopic.php?f=166&t=666244

Gruss Sepp