Excel: Datenzuordnung mit Zeitspanne

[Luftologe]

Hallo!
Ich arbeite gerade für meine Diplomarbeit an einem Kreislaufverbundsystem zur Kühlung/ Heizung einer Pharmaindustrie-Produktionsanlage und muss dazu die Umwelteinflüsse beim Werk analysieren, dabei stellt sich mir folgendes Problem:
Die Firmenleitung hat mir die gesamten Aufzeichnungen von 2017 in einem Programm zukommen lassen, aus dem ich die Daten (nach ewigem kopieren) in eine Excel-Datei übertragen konnte. Dabei sind zwei Tabellen entstanden:
Aufbau:
T1.: Zeit Temperatur T2.: Zeit Feuchte
Bsp.: 30-Dec-2017 23:31:46 2,47 30-Dec-2017 23:28:26 4,31

Um die Enthalpie zu berechnen müsssen diese Tabellen zusammengeführt werden, jedoch hat die T1. nur ca. 26.000 Zeilen und die T2. knapp 68.000 Zeilen und darum stimmen natürlich auch die Zeiten nicht überein...
Meine Idee wäre eine Art "Zuordnen"-Befehl mit Zeitspanne, d.h. zu Tabelle 1. und Zeit 30-Dec-2017 23:31:46 die Feuchten aus Tabelle 2. zuzuordnen die in einer Zeitspanne von +/- 2min der Zeit in T1. entsprechen. Leider scheitere ich als eher unbegabter Excel-Nutzer an der Umsetzung...
Ich wäre heilfroh wenn mir jemand weiterhelfen könnte und bin für jede Idee dankbar
Danke euch im Voraus!!
lg Peter

 

[Rego]

Sind die Zeiten, die zusammengeführt werden sollen identisch? Sind die Daten in einzelnen Zellen (also 30-Dec-2017 in einer Zelle, 23:31:46 in einer Spalte)?

 

[Alchemist]

Hier mal ein erster Versuch, der jeweils den Wert mit dem minimalen Zeitabstand zuordnet:

In B2 steht folgende Formel:

=INDEX($E$2:$E$4;VERGLEICH(MIN(ABS(A2-$D$2:$D$4));A2-$D$2:$D$4;0);1)

Die Formel muss als Matrixformel mit STRG + SHIFT + ENTER eingefügt werden. Also Formel kopieren und dann nicht Enter sondern die andere Tastenkombie nehmen. Die Formel steht dann in geschweiften Klammern.

EDIT: Das Ganze sollte erstmal getestet werden. Die Formel auf alle Werte anzuwenden wird mit Sicherheit ewig dauern. Ich meine EWIG! Die Frage ist auch ein bisschen, wie weit die Zeiten so auseinander liegen. Ich würde der Einfachheit halber auch die Datumsangaben mit der Formel kopieren und dann in einem gesonderten Schritt die Differenz ausrechnen und mittels bedingter Formatierung o.Ä. Ausreißer markieren. Kommt aber wirklich auf die Daten an und wie häufig es ist, dass die Zeiten sich arg unterscheiden.

 

[CHaos.Gentle]

@über mir: Wie sollen sie identisch sein, wenn die Tabellen unterschiedliche Zeilenanzahl haben?

@TE: Das mit deinen 1-2 Minuten unterschied wird schwer, wenn du einfach zur Feuchte die nächstgelegene Temperatur willst, einfach beide Tabellen nach Datum sortieren und einen einfachen sverweis

Markiere in der Feuchte-Tabelle die erste Zelle hinter dem ersten Feuchte-Wert, bspw. Zelle C2. In meinem Beispiel E2, weil beide Tabellen auf dem selben Blatt stehen.

Tippe =SVERWEIS und klicke auf das kleine fx Symbol

Im Formeleditor im ersten Feld auf die erste Datumszelle der Temperatur-Tabelle klicken.
Im zweiten Feld die komplette Temperaturmatrix markieren und danach (in der Zelle steht sowas wie A1:A28000 drin) auf das A1 klicken und F4 drücken und auf das A28000 klicken und F4 drücken. Dann sollte da $A$1:$A$28000 stehen.
In der dritten den Spaltenindex der Temperatur eingeben, wahrscheinlich 2.
Die vierte mit 1 füllen oder leer lassen.

Wichtig ist wie gesagt, dass die Temperatur-Tabelle nach Datum aufsteigend sortiert ist.

Beispiel der Formel in der Temp-Spalte der Feuchte-Tabelle:
=SVERWEIS(E2;$A$2:$B$4;2;1)

Dann einfach auf die rechte untere Ecke der Zelle doppelklicken. Fertig

 

[Alchemist]

Also zwei Minuten geht auch, die Formel sieht schlimm aus und ich vermute stark, dass es viel schöner geht aber hey, sie sollte funktionieren.

=WENNFEHLER(INDEX($E$2:$E$4;VERGLEICH(WENN(MIN(ABS(A4-$D$2:$D$4))<(2/24/60);MIN(ABS(A4-$D$2:$D$4));"FEHLER");ABS(A4-$D$2:$D$4);0);1);"zu weit weg")

Der Code bezieht sich weiterhin auf mein Beispiel. In der Formel findest du den Ausdruck: 2/24/60, dies entspricht zwei Minuten. Wird kein Wert gefunden, wird ein Fehler ausgegeben.

EDIT: Korrektur!!

@CHaos.Gentle das Problem an SVerweis ist nur, dass es nicht immer den nächsten Wert zuordnet. Siehe Beispiel:

 

[CHaos.Gentle]

Jo hab mich schlecht ausgedrückt, verweis nimmt immer den nächsten Wert, bei 68000 Zeilen für Jahr, also ca. alle 8 min eine Temperaturmessung aber sicherlich nicht kriegsentscheidend...

Die Formel von Alchemist alias http://www.excelformeln.de ist schon perfekt. Matrixformeln mit so vielen Werten laufen zwar unangenehm lange in diesem Falle aber sicherlich irrelevant.

 

[Luftologe]

Erstmals vielen dank euch!
Bin gerade echt fasziniert wie hilfsbereit hier alle sind und wie schnell das geht.
Ich versuch mal ob ich es nach euren Lösungsvorschlägen hinbekomme!
Zu den ersten Fragen: nein leider stimmen die Zeiten nicht überein...


Ich werde jetzt einfach mal schauen ob ich es hinbekomme und danach meine Resultate senden!
Danke danke danke !

Update:
Leider funktioniert das bei mir nicht so gut...
@CHaos.Gentle deinen sverweis habe ich als erstes versucht.
Irgendwie kommt das aber nicht mit den versch. Zeiten klar, denn es hat mir für die ersten paar Zeilen einen falschen Wert ausgegeben und danach nur #nv
@Alchemist deine Formel gibt bei mir nur "zu weit weg aus" aber dabei ist es leicht möglich, dass ich was falsch gemacht habe, denn die versteh ich einfach nicht sieht bei mir so aus:

habe mal bisschen mit der zeit gespielt aber das hatte auch nicht den gewünschten Effekt.

 

[CHaos.Gentle]

Ganz wichtig, beim Verweis muss die Tabelle mit der kleineren Anzahl Einträge aufsteigend sortiert werden.
Also genau umgekehrt zu deinem Screenshot.

Aber du bekommst dann, wie angemerkt eben nicht den Wert der zeitlich am nächsten liegt, sondern immer den höheren.
Suchst du also nach 12 Uhr und hast einen Wert für 11:59 und einen für 12:05 bekommst du den Wert für 12:05.

Und bezüglich der Formel von Alchemist musst du darauf achten, dass die Bezüge alle stimmen.

Bei allen lila- und blau-farbenen Bezügen muss noch die Endzeile der kleineren Tabelle eingetragen werden.

Zusätzlich musst du JEDE Bearbeitung der Zelle mit STRG+SHIFT+ENTER abschließen und die Formel muss dann von geschweiften Klammern umgeben sein.

Achso und noch einen Nachtrag:
Die Formel von Alchemist liefert kein Ergebnis, wenn die Suchzelle genau in der Mitte von zwei Zeilen liegt:
Also 12:00 wird gesucht und 11:59 und 12:01 werden gefunden.

Korrektur:

=WENNFEHLER(INDEX($E$2:$E$9;VERGLEICH(WENN(MIN(ABS(A2-$D$2:$D$9))<=(2/24/60);MIN(ABS(A2-$D$2:$D$9));"FEHLER");ABS(A2-$D$2:$D$9);0);1);"zu weit weg")

 

[Luftologe]

Ah ok dann weiß ich zumindest was bei mir falsch war. danke!
leider brauche ich den wert der am nächsten dran ist, damit meine Berechnungen korrekt sind.

 

[Alchemist]

Die Formel von Alchemist liefert kein Ergebnis, wenn die Suchzelle genau in der Mitte von zwei Zeilen liegt:
Also 12:00 wird gesucht und 11:59 und 12:01 werden gefunden.

Ja, dass stimmt. Darüber habe ich auch nachgedacht, bin aber nicht zu einem richtigen Ergebnis gekommen. Da seine Tabelle sogar Sekundenwerte enthält habe ich das Risiko einfach als extrem gering angenommen.
Ich bin gespannt ob es funktioniert

 

[CHaos.Gentle]

Naja, ein Gleichheitszeichen an da richtigen Stelle behebt das Problem ja...von daher

 

[Alchemist]

^das Gleichheitszeichen erlaubt, dass auch Ergebnisse gefunden werden, die genau 2 Minuten entfernt sind. Vielen Dank für die Korrektur!
Was aber bleibt, ist dass bei exakt gleichen Abständen jeweils der erste gefundene Wert genommen wird. Ist aber in dem Sinne ja nicht falsch.

Ich wollte aber vor allem noch mal die Formel erklären:
1) $ - Zeichen für feste Zellbezüge
2 Matrixformeln mit STRG+SHIFT+ENTER müssen verwendet werden, da wir gleichzeitig eine Reihe von Werten untersuchen müssen. Mehr dazu später.

Die Spalten:
A: Datum 1
B: Werte, uninteressant hier
C: Zielzelle
D: Datum 2; zu Datum 1 wird dasjenige Datum 2 gesucht, dass minimal weit entfernt ist UND weniger als 2 Minuten
E: Zu übertragene Werte

Die Formel:

=WENNFEHLER(INDEX($E$2:$E$9;VERGLEICH(WENN(MIN(ABS(A2-$D$2:$D$9))<=(2/24/60);MIN(ABS(A2-$D$2:$D$9));"FEHLER");ABS(A2-$D$2:$D$9);0);1);"zu weit weg")

Von innen nach außen:

MIN(ABS(A2-$D$2:$D$9))

Liefert den kleinsten absoluten (betragsmäßig) Abstand zwischen dem Datum der aktuellen Zeile (A2) und allen anderen Daten (Spalte D). Die Angaben zur D Spalte müssen entsprechend an dein Dokument angepasst werden.

WENN(MIN(ABS(A2-$D$2:$D$9))<=(2/24/60);MIN(ABS(A2-$D$2:$D$9));"FEHLER")

In dieser Wenn Funktion wird geprüft ob der Abstand geringer ist als zwei Minuten. Da in Excel bei einem Datum eine Differenz von 1 einem Tag entspricht, müssen wir das entsprechend auf Minuten runter brechen.
Ist der Abstand kleiner als 2 Minuten, so wird der Abstand ausgegeben, wenn nicht, so wird FEHLER geschrieben. Ist egal was man hier nimmt, Hauptsache es ist keine Zahl.

VERGLEICH(WENN(...);ABS(A2-$D$2:$D$9);0);1)

Nun suchen wir die Zeile, in der Datum 2 mit der minimalen Differenz steht. Die allgemeine Syntax von Vergleich: VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp]).
- Die Wenn Funktion liefert diese Differenz, also das Suchkriterium.
- Die Suchmatrix ist wieder unsere Differenz vom Anfang. Auch hier gilt, dass der D Bereich alle Werte umfassen muss (bzw. genauso viele wie in der anderen MIN Funktion).
- Der Vergleichstyp steht auf 0, dies bedeutet genaue Übereinstimmung.

INDEX($E$2:$E$9;VERGLEICH(...);1)

Die Index Funktion lautet: INDEX(Matrix;Zeile;[Spalte]) und liefert so einen Wert zurück.
- Matrix: Spalte E, also die zu übertragenen Werte; auch hier wieder alle Werte in E (oder soviele wie aus Spalte D)
- Zeile: Hier steht unsere Vergleichsfunktion, die die Zeile gesucht hat
- Spate: ist optional, hier kann 1 stehen oder auch nichts, da die Matrix ohnehin nur eine Spalte hat

=WENNFEHLER(...;"zu weit weg")

Nun kann es aber sein, dass eben kein Wert das 2 Minuten Kriterium erfüllt. In diesem Fall kann keine Zeile gefunden werden und es wird ein Fehler von der Formel zurückgegeben. Um dies Abzufangen wird die WENNFEHLER Funktion verwendet. Im Fehlerfall wird der String "zu weit weg" ausgegeben.

In Kürze:
Alle Verweise auf D und E sollten immer gleich lang sein ($D$2:$D$100 ==> $E$2:$E$100). Die Formel mittels besagter Matrixschreibweise einfügen!

 

[Luftologe]

Bis jetzt hat es trotz der extrem guten Erklärung leider nicht geklappt...
Für heute raucht der Kopf genug aber ich versuch dann morgen nochmals genau nach deinem Schema! @Alchemist
vielen vielen Dank! würd euch auf jeden Fall nen Kasten spendieren!

 

[Luftologe]

Hallo @Alchemist, wäre es möglich dir die Datei per mail zu schicken. Vielleicht gelingt es dir unser Problem zu lösen.
mfG