Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Suchen das nächste zurückligende Datum

Suchen das nächste zurückligende Datum
edie
Hallo zusammen,
ich suche eine Möglichkeit, per VBA wenn’s geht, das annähernd nächste zurückliegende Datum aus einer Datumsreihe zu suchen bzw. finden und das Datum und die entsprechend Werte zu kopieren.
Hier im Beispiel: Wenn in Zelle F1 der Tabelle 2 das Datum 16.01.2010 steht, dann soll aus der Tabelle1 das Datum 14.01.2010 gefunden werden, ist ja das erste zurückliegende Datum in der Datumsreihe,
das Datum mit dem Wert1 und Wert2 entsprechend in A3:C3 kopiert werden.
Siehe Beispiel:
Tabelle1
 ABC
1DatumWert1Wert2
2     
301.01.20101052
407.01.20101212
514.01.20101543
621.01.20101722
728.01.20103210
805.02.20101665
912.02.2010169

Tabellendarstellung in Foren Version 5.3


Tabelle2
 ABCDEF
1DatumWert1Wert2  Gesucht16.01.2010
2           
314.01.20101543     

Tabellendarstellung in Foren Version 5.3


Bitte um Hilfe.
Vielen Dank im Voraus.
Grüße
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
mit Formel
28.02.2010 16:23:33
Christian
Hallo Edie,
mit Formel, die daten in Tabelle1 müssen aber aufsteigend sortiert sein!
Tabelle2

 ABCDEF
1DatumWert1Wert2 Gesucht16.01.2010
2      
314.01.20101543   
4      
5      
6      

Formeln der Tabelle
ZelleFormel
A3{=INDEX(Tabelle1!A:A;MAX(WENN((Tabelle1!$A$1:$A$100>0)*(Tabelle1!$A$1:$A$100<=$F$1); ZEILE($1:$100))))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
MfG Christian
Anzeige
AW: mit Formel
28.02.2010 16:32:23
edie
Hallo Christian,
vielen Dank für die Hilfe.
Frage: Wie kopiere ich nun die entsprechende Werte (Wert1 und Wert2) die zum Datum passen
in die Tabelle2?
Vorab vielen Dank.
Grüße-edie
Formel nacht rechts kopieren!!! o.T.
28.02.2010 16:34:01
Christian
AW: Danke, es funktioniert
28.02.2010 16:42:46
edie
Hallo Christian,
meine Schuld, war mir nicht ganz sicher.
Vielen Dank.
Grüße-edie
Anzeige
AW: Suchen das nächste zurückligende Datum
28.02.2010 16:25:28
Hajo_Zi
Hallo Edie,
das geht ohne VBA.
Tabelle1
 ABC
1DatumWert1Wert2
2     
301.01.20101052
407.01.20101212
514.01.20101543
621.01.20101722
728.01.20103210
805.02.20101665
912.02.2010169

Zahlenformate
Zelle Format Wert
A3 TT.MM.JJJJ40179
A4 TT.MM.JJJJ40185
A5 TT.MM.JJJJ40192
A6 TT.MM.JJJJ40199
A7 TT.MM.JJJJ40206
A8 TT.MM.JJJJ40214
A9 TT.MM.JJJJ40221
Zellen mit Format Standard werden nicht dargestellt



Tabelle2
 ABCDEF
1DatumWert1Wert2  Gesucht16.01.2010
2           
314.01.20101543     
4           

verwendete Formeln
Zelle Formel
A3 {=MAX(WENN(Tabelle1!A3:A9<Tabelle2!F1;Tabelle1!A3:A9))}
B3 =SVERWEIS($A$3;Tabelle1!$A$3:$C$9;SPALTE();0)
C3 =SVERWEIS($A$3;Tabelle1!$A$3:$C$9;SPALTE();0)
{} Matrixformel mit Strg+Umschalt+Enter abschließen

Zahlenformate
Zelle Format Wert
F1 TT.MM.JJJJ40194
A3 TT.MM.JJJJ40192
Zellen mit Format Standard werden nicht dargestellt

Tabellendarstellung in Foren Version 5.16



Anzeige
AW: Suchen das nächste zurückligende Datum
28.02.2010 16:40:10
edie
Hallo Hajo,
es funktioniert so wie ich es mir vorgestellt habe!
Vielen herzlichen Dank.
Grüße-edie
AW: Suchen das nächste zurückligende Datum
01.03.2010 06:09:49
BoskoBiati
Hallo,
so wie die Tabelle 1 aufgebaut ist, genügt eine Formel:
=Sverweis($F1$;Tabelle1!§A§3:$C$9;Spalte(A1);1) und nach rechts ziehen.
Gruß
Bosko
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Nächstes zurückliegendes Datum in Excel finden


Schritt-für-Schritt-Anleitung

Um das nächstgelegene zurückliegende Datum in Excel zu finden, kannst Du folgende Schritte befolgen:

  1. Daten eingeben: Stelle sicher, dass Deine Datumsreihe in Tabelle1 aufsteigend sortiert ist. Die Daten sollten in Spalte A beginnen, und die zugehörigen Werte in den Spalten B und C stehen.

  2. Gesuchtes Datum eingeben: In Tabelle2 trägst Du das Datum, für das Du das nächstgelegene zurückliegende Datum finden möchtest, in Zelle F1 ein.

  3. Formel eingeben: In Zelle A3 von Tabelle2 gibst Du die folgende Formel ein:

    =MAX(WENN(Tabelle1!$A$3:$A$9<F1;Tabelle1!$A$3:$A$9))

    Diese Formel sucht das maximalste Datum, das kleiner als das gesuchte Datum in F1 ist.

  4. Werte kopieren: Um die zugehörigen Werte (Wert1 und Wert2) zu kopieren, kannst Du in Zelle B3 und C3 folgende Formeln verwenden:

    =SVERWEIS(A3;Tabelle1!$A$3:$C$9;SPALTE();0)
  5. Formel nach rechts ziehen: Ziehe die Formeln in B3 und C3 nach rechts, um die Werte in den Zellen D3 und E3 zu füllen.


Häufige Fehler und Lösungen

  • Fehler: #NV: Dies kann passieren, wenn kein passendes Datum gefunden wird. Überprüfe, ob das gesuchte Datum kleiner ist als das kleinste Datum in Tabelle1.

  • Fehler: #WERT!: Achte darauf, dass alle Zellen in der Datumsreihe korrekt als Datumsformat formatiert sind.

  • Daten nicht aufsteigend sortiert: Stelle sicher, dass Deine Datumsreihe in Tabelle1 aufsteigend sortiert ist, da die Formeln nur dann korrekt funktionieren.


Alternative Methoden

Wenn Du VBA verwenden möchtest, um das nächste zurückliegende Datum zu finden, kannst Du folgenden Code verwenden:

Function FindPreviousDate(searchDate As Date) As Date
    Dim rng As Range
    Dim cell As Range
    Dim closestDate As Date

    Set rng = ThisWorkbook.Sheets("Tabelle1").Range("A3:A9")

    For Each cell In rng
        If cell.Value < searchDate Then
            If closestDate = 0 Or cell.Value > closestDate Then
                closestDate = cell.Value
            End If
        End If
    Next cell

    FindPreviousDate = closestDate
End Function

Verwende die Funktion in einer Zelle, um das nächstgelegene Datum zu finden.


Praktische Beispiele

Hier ein Beispiel zur Veranschaulichung:

  • In Tabelle1 hast Du folgende Datumsreihe:

    • 01.01.2010
    • 07.01.2010
    • 14.01.2010
    • 21.01.2010
  • Wenn Du in Tabelle2 in Zelle F1 das Datum 16.01.2010 eingibst, wird in Zelle A3 das Datum 14.01.2010 angezeigt, und die Werte in B3 und C3 sind 15 und 43.


Tipps für Profis

  • Verwendung von Matrixformeln: Du kannst die Suche nach dem nächsten Datum auch als Matrixformel anlegen, um die Effizienz zu erhöhen. Denke daran, die Formel mit STRG + SHIFT + ENTER abzuschließen.

  • Datenvalidierung: Nutze Datenvalidierung, um sicherzustellen, dass nur gültige Datumswerte in F1 eingegeben werden.

  • Fehlerüberprüfung: Verwende die Funktion WENNFEHLER, um eventuelle Fehler in den Formeln abzufangen und benutzerfreundliche Fehlermeldungen anzuzeigen.


FAQ: Häufige Fragen

1. Wie kann ich das Datum finden, wenn meine Datumsreihe nicht aufsteigend sortiert ist? Du musst die Datumsreihe in Tabelle1 zuerst aufsteigend sortieren, damit die Formeln korrekt arbeiten.

2. Kann ich diese Methode auch für andere Datumsformate verwenden? Ja, solange die Datumswerte korrekt in Excel formatiert sind, funktioniert die Methode unabhängig vom Format.

3. Was ist, wenn ich nur die Werte und nicht das Datum benötige? Du kannst die SVERWEIS-Funktion anpassen, um nur die Werte zu extrahieren, ohne das Datum zu berücksichtigen.

4. Welche Excel-Version wird benötigt? Die beschriebenen Methoden sind in Excel 2010 und späteren Versionen anwendbar.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige