Suchwert innerhalb eines Bereichs um einen bestimmten Suchwert

396
Jim Richalds

Ich habe 2 Datenbanken, mit denen ich in Excel arbeite. Hierbei handelt es sich um Internet-Geschwindigkeitstests, bei denen die vom Benutzer eingegebene Adresse und der tatsächliche Geschwindigkeitstest in 2 Datenbanken getrennt werden. Die Adresstabelle und die Geschwindigkeitstesttabelle haben keine eindeutige Kennung, um sie miteinander zu verknüpfen (whoops!), Aber sie haben Zeitstempel.

Ich möchte die Zeitstempel als Bezeichner verwenden. Wie mache ich einen Lookup, der auf Zeitstempelwerte prüft, die zB 15 Minuten vor oder nach dem Lookup-Wert auftreten.

0

1 Antwort auf die Frage

0
Bandersnatch

Es wäre hilfreich gewesen, wenn Sie einige Beispieldaten bereitgestellt hätten. Ich nahm eine Vermutung an und fabrizierte einige Daten, die denen Ihrer ähneln könnten, und hoffentlich ermöglicht es Ihnen die Erklärung der Funktionsweise der Formeln, diese an Ihre Datenstruktur anzupassen.

Der Hauptschwerpunkt Ihrer Frage ist, wie Sie Zeiten in einer Spalte finden, die innerhalb von 15 Minuten nach den Zeiten in einer anderen Spalte auftreten. Die Zeitstempel-Spalten in der nachstehenden Tabelle sind tatsächlich Datums- und Uhrzeitangaben, jedoch so formatiert, dass nur die Stunden und Minuten angezeigt werden.

Die Matrixformel in E3 lautet:

=IFERROR(INDEX(D$3:D$52,MATCH(1,(1*(24*60*ABS(B3-D$3:D$52)<=$H$3)),0)),"")

Es muss mit eingegeben CTRLShiftEnterund dann ausgefüllt werden.

Das funktioniert so: Der innere Ausdruck ABS(B3-D$3:D$52)erzeugt ein Array der absoluten Unterschiede zwischen der Zeit in B3 und allen Zeiten in Spalte D. Das Ergebnis ist eine Zahl, bei der der Dezimalteil die gebrochene Anzahl von Tagen zwischen den beiden Datumsangaben darstellt. Durch Multiplizieren mit 24 * 60 wird dies in Minuten konvertiert, und die Ungleichung prüft, ob diese Werte <= Anzahl der Minuten in H3 sind (anfangs 15, später jedoch mehr dazu).

An diesem Punkt (24*60*ABS(B3-D$3:D$52)<=$H$3)erzeugt der Ausdruck ein Array von Wahr / Falsch-Werten, die der Frage entsprechen, ob sich B3 innerhalb von 15 Minuten nach den Zeiten in Spalte D befindet. Durch Multiplizieren mit 1 werden die Wahr / Falsch-Werte in Einsen und Nullen umgewandelt.

Nun findet MATCH () die Position der ersten 1 im Array und INDEX () erzeugt die entsprechende Zeit aus Spalte D. Schließlich erzeugt IFERROR () ein Leerzeichen (anstelle von # N / A), wenn keine passende Zeit innerhalb von + / - 15 Minuten wurde gefunden.

Diese Formel in F3: =IFERROR(INDEX(C$3:C$50,MATCH(E3,D$3:D$50,0)),"")sucht einfach die Spalte E-Zeit in Spalte D und gibt die entsprechende Geschwindigkeit aus Spalte C zurück.

Schließlich wird diese Formel in G3: =IF(SUMPRODUCT(1*(24*60*ABS(B3-D$3:D$52)<=$H$3))>1,"Yes","")auf Fehler geprüft. Es summiert das Array von Einsen und Nullen im inneren Ausdruck und erzeugt "Ja", wenn die Antwort> 1 ist. Dies bedeutet, dass es in Spalte B zwei oder mehr Male innerhalb von 15 Minuten der Zeit gab. Die Funktion MATCH () wird nur verwendet finde den ersten, der möglicherweise nicht korrekt ist. Um dies zu umgehen, reduzieren Sie den Wert in H3, bis das "Ja" aus einer bestimmten Zeile verschwindet. Zu diesem Zeitpunkt ist die Zeit in Spalte E die einzige Zeit, die der Zeit in Spalte B am nächsten ist.

Ich hoffe, Sie fanden das hilfreich.

Anmerkungen:

  1. Ich habe zufällige Zeiten für die Spalten B und D generiert, daher gibt es einige Zeiten in Spalte B, die keine Übereinstimmungszeit (+/- 15 Minuten) in Spalte D haben. Wenn Ihre Daten keine Übereinstimmungen aufweisen, können Sie die IFERROR-Wrapper entfernen.

  2. Vermutlich wächst Ihre Datenbank, so dass Sie ganze Spaltenverweise (z. B. D: D) in den Formeln verwenden können.

Verwandte Probleme