Excel 2007 SVERWEIS über MITTERNACHT

Bild

Betrifft: Excel 2007 SVERWEIS über MITTERNACHT
von: Immo Woschny
Geschrieben am: 15.07.2015 14:56:17

Hallo,
ich habe folgendes Problem mit Excel 2007 :
(Daten fangen ab Zeile 5 an, etwa 10.000 Werte)
Hilfsspalten
Spalte A : Original Uhrzeit (SS:MM:SS)
Spalte B : Breitengrad =SVERWEIS(F5;$P$5:$T$1370;2;0)
Spalte C : Längengrad =SVERWEIS(F5;$P$5:$T$1370;3;0)
Auswertungsspalten
Spalte E : Datum (TT.MM.JJJJ)
Spalte F : Uhrzeit korrigiert (+01:00:00) (SS:MM:SS) =A5+$F$4 ,(F4 = 01:00:00)
Spalte G-I : zugehörige Daten (Temp, L-Feuchte,)
Spalte J : Breitengrad (=WENN(ISTFEHLER(B5);0;B5)
Spalte K : Längengrad (=WENN(ISTFEHLER(C5);0;C5)
GPS Daten
Spalte O : Datum (TT.MM.JJJJ)
Spalte P : Uhrzeit (SS:MM:SS)
Spalte Q : Breitengrad (Originaldaten)
Spalte R : Längengrad (Originaldaten)
jetzt ist es so, daß sich die Koordinaten aus den Spalten Q und R nur den Daten bis 23:59:59 zuordnen lassen (Daten gehen von etwa 22h - 1h), danach kommt ausschließlich NV
Was kann ich tun, um alle Daten zuzuordnen ?

Bild

Betrifft: AW: Excel 2007 SVERWEIS über MITTERNACHT
von: Immo Woschny
Geschrieben am: 15.07.2015 15:26:19
https://www.herber.de/bbs/user/98842.xlsx

Bild

Betrifft: nachgefragt ...
von: der neopa C
Geschrieben am: 15.07.2015 16:23:57
Hallo immo,
... die Daten sollen nur übertragen werden, wenn die Uhrzeiten übereinstimmen? in B5:C10 sollen weiterhin die Fehlermeldungen kommen oder sollen die Daten der vorhergehenden Zeit aus Q übertragen werden oder?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nachgefragt ...
von: Immo Woschny
Geschrieben am: 15.07.2015 16:34:27
Hallo,
die Daten sollen erstmal nur übertragen (zugeordnet) werden. Es sind ja weniger GPS Daten als Temp,L-feuchte, usw. .
Mir geht es hauptsächlich darum, die Werte nach 23:59:59 auch zuordnen zu lassen !
Gruß
iMMo

Bild

Betrifft: AW: nachgefragt ...
von: Immo Woschny
Geschrieben am: 15.07.2015 16:37:21
Im nächsten Schritt soll dann eine Interpolation in den Felden mit NV erfolgen...jeweils zwischen dem oberen und dem unteren Koordinatenwert!
Auch hierbei habe ich noch keinen Ansatz um es zu automatisieren...und von Hand ist es zu aufwendig

Bild

Betrifft: zum "ersten Schritt" ...
von: der neopa C
Geschrieben am: 15.07.2015 16:42:30
Hallo Immo,
.... und damit gleichzeitig in Vorbereitung des 2. Schrittes. Füge nach Spalte F sowie Q jeweils eine eine neue Spalte ein. In diese dann in G5: =E5+F5 und Formel nach unten und in die die neue R-Spalte kopieren. Nun vergleichst Du mit SVERWEIS() nicht mehr mit den werten aus Spalte F und Q sondern eben aus G mit R.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: zum "ersten Schritt" ...
von: Immo Woschny
Geschrieben am: 15.07.2015 16:52:23
hab ich gemacht, aber das Problem daß es ab 0 Uhr nicht funktioniert, bleibt erhalten !

Bild

Betrifft: es war ja auch nur der erste Schritt ;-) ...
von: der neopa C
Geschrieben am: 15.07.2015 17:25:25
Hallo Immo,
... hast Du Dir die Daten in Spalte G ab Zeile mal im Datenformat: TT.MM.JJJJ hh:mm:ss angeschaut?
Da steht ja auch nicht der 7. sondern der 8. Juli. Das liegt daran, dass Du in Spalte F immer einen konstanten Zeitwert addierst und dadurch über 24:00 kommst, was in Excel ein Tag ist und Du das Zeitformat aus der Anzeige nicht erkennen konntest (war/ist noch nicht entsprechend erweitert).
Eine einfache Abhilfe-Möglichkeit wäre: Du belässt es in Spalte E konstant bei dem 6. Juli (anstelle 7.7) Dann erhältst Du schon mal ein paar Ergebniswerte für den Folgetag.
Ich nehme jetzt gleich noch den 3. Schritt voraus. Ändere mal die Formel in G5 zu wie folgt:
=RUNDEN(E5+F5;5) und das für diese und die Spalte R in Gänze.
Nun hast Du noch ein paar Ergebnisse mehr. Hintergrund: in Excel sind Zeitwerte Dezimalzahlen. Mehr dazu sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=128 und ff.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Excel 2007 SVERWEIS über MITTERNACHT
von: Daniel
Geschrieben am: 15.07.2015 17:26:36
Hi
wenn möglich, Datum und Uhrzeit in Hilfsspalten zu einem Wert zusammenfassen.
ein Datum ist für Excel eine Ganzzahl, ein Tag hat den Wert 1.
die NK-Stellen stellen die Uhrzeit dar.
Wenn man kein Datum hat sondern nur Uhrzeiten, muss man zu Uhrzeiten, die nach Mitternacht liegen den Wert 1 hinzuaddieren (nächster Tag).
Gruß Daniel

Bild

Betrifft: warum liest Du vorher nicht die Beiträge ? owT
von: der neopa C
Geschrieben am: 15.07.2015 17:28:58
Gruß Werner
.. , - ...

Bild

Betrifft: AW: warum liest Du vorher nicht die Beiträge ? owT
von: Immo Woschny
Geschrieben am: 15.07.2015 17:36:05
Ich habe das Runden jetzt eingebaut, aber daran, daß ich ab Mitternacht keine Werte bekomme, ändert sich leider nix.
Ich muss nun leider weg und kann erst heute ab 20.30h weitermachen.
Vielen Dank für Eure Hilfe, ich bin leider noch nicht so versiert, bin also dankbar für weitere workarounds

Bild

Betrifft: hierzu ...
von: der neopa C
Geschrieben am: 15.07.2015 17:45:33
Hallo Immo,
... mein Beitrag mit dem Betreff: "warum liest Du vorher nicht die Beiträge ? owT" war nicht an Dich gerichtet sondern an Daniel.
Ich bin in Kürze für Heute nicht mehr online. Das RUNDEN() in der Formel war nur eine Aussage in meinem vorherigen Beitrag an Dich. Lies diesen nochmal.
Du brauchst dazu lediglich E499:E500 markieren und ein Doppelklick auf das kleine rechte Fadenkreuz vorzunehmen, wenn Du zuvor mit der Maus über die rechte untere Ecke des markierten Bereiches "streichst".
Danach steht in Spalte E nur noch der 7.07.
Und klappt es jetzt?
Weiter dann Morgen.
Gruß Werner
.. , - ...

Bild

Betrifft: AW:darum, die Antwort steht im Beitrag
von: Daniel
Geschrieben am: 15.07.2015 18:09:01
Ich lese sie schon, nur gibt es da bei dir relativ wenig zu lesen.
erkläre einfach ein bisschen besser was du meinst, anstatt einfach nur irgendwelche Formeln hinzuschreiben.
auch dieser "Beitrag" hier von dir enthält ja eigentlich keine Beitrag, sondern nur eine Überschrift.
Gruß Daniel

Bild

Betrifft: Nicht ärgern, ...
von: Luc:-?
Geschrieben am: 16.07.2015 03:00:59
…Werner,
das wäre ja nicht das 1.Mal. Dieser „Gast“ aus dem OL-Forum hält sich eh für superschlau und legt sich allzugern mit anderen an, besonders, wenn sie WFs Auffassung von Betreffs folgen. Das ist er auf Grund seiner Foren-Sozialisation halt nicht gewohnt… ;-]
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: AW: Nicht ärgern, ...
von: Immo Woschny
Geschrieben am: 16.07.2015 15:17:08
So, das erste Problem habe ich nun gelöst (dank Eurer Mithilfe) komischerweise musste ich von den Werten ab 00:00:00 am Folgetag "1" abziehen...
Jetzt wärs super wenn mir noch jemand sagen/erklären könnte, wie ich in Spalte K und L immer jeweils zwischen den vorhandenen Zahlen/Koordinaten interpolieren kann... Habe es soweit hinbekommen, daß ich nachdem ich die Nullen entfernt habe, den Bereich mit Start und Endzahl markierth, mit der Funktion "Füllen" und Trend das gewünschte Ergebnis erhalte...das wäre jetzt super, wenn ich das noch automatisieren könnte !?
Grüße
iMMo

Bild

Betrifft: AW: Nicht ärgern, ...
von: Immo Woschny
Geschrieben am: 16.07.2015 15:19:43
https://www.herber.de/bbs/user/98866.xlsx

Bild

Betrifft: dazu wäre vor und zur Interpolation ...
von: der neopa C
Geschrieben am: 16.07.2015 15:36:56
Hallo iMMo.
... zunächst ratsam, die SVERWEIS()-Formeln zu "umklammern" und zwar mit der Funktion WENNFEHLER(), damit Du die Fehlerergebnisse "eliminierst".
Für die ermittelten Werte könntest Du am einfachsten in einer zusätzlichen Hilfszeile die Interpolation vornehmen. Dazu rate ich Dir Dich als nächstes mal z.B. mit den Formelvorschlag von Sepp Burch hier: http://www.excelformeln.de/formeln.html?welcher=373 vertraut zu machen.
Anschließend reduziere Deine große Tabelle zunächst mal in einer neuen Tabellenblatt auf einen kleinen Bereich und versuche die Interpolation zunächst dort zu lösen. Dazu ermittele Dir als erstes ein paar Zielergebnisse händisch, damit Du immer gleich den Vergleich hast.
Bei evtl. Nachfragen dann bitte auch einen entsprechenden kleinen Tabellenausschnitt einstellen.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dazu wäre vor und zur Interpolation ...
von: Immo Woschny
Geschrieben am: 16.07.2015 22:22:23
Ich kriege es nicht hin...
Ich habe ja auch eine andere Situation als auf der von Ihnen verlinkten Seite !
https://www.herber.de/bbs/user/98877.xlsx
Wie gesagt, per Knopfdruck auf "Füllbereich" + Reihe + Trend erhalte ich das gewünschte Ergebnis (in orange in der Datei markiert), nur eben nicht gleich für die gesamte Spalte.

Bild

Betrifft: hier Excelrechen-(Un-)Genauigkeit ....
von: der neopa C
Geschrieben am: 17.07.2015 09:46:46
Hallo IMMo,
... wie bereits geschrieben sind in Excel die Uhrzeiten nur entsprechend formatierte Dezimalzahlen bezogen auf einen Tag=1. Excel rechnet nur bis zu einer Genauigkeit von 15 Stellen. Das kann bei sehr kleinen Werten und sehr großen Werten sich negativ auswirken. In Deine Fall wäre das so.
Schreibe mal in Z6 folgende Formel: =G6-G$6 und kopiere diese nach unten und formatiere die Zellen als Uhrzeit. Was fällt Dir auf? Genau, und das würde sich auch auf eine lineare Interpolation auswirken, wenn Du mit den Zeitangaben rechnen willst. Bliebe nur die Alternative ohne diese zu interpolieren. Dies setzt aber voraus, dass immer konstante Zeitabstände vorliegen. Das hab ich jetzt nicht geprüft.
Also wie ist Deine Entscheidung?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: hier Excelrechen-(Un-)Genauigkeit ....
von: Immo Woschny
Geschrieben am: 17.07.2015 14:18:37
Moin. Also verstehe ich das richtig daß sich die KOORDINATEN in Spalte K und L nur mit Hilfe der Datums&Uhrzeit Spalte G interpolieren lassen ODER eben ohne diese dann aber nur bei jeweils gleicher Anzahl leerer Zellen zwischen der obenen und unteren Koordinate ?
Und wieso reichen 15 Stellen nicht aus ? Wenn "1" = 01.01.1900 ist, dann sind wir heute bei 3,6 Milliarden (115 Jahre * 365 Tage * 24 Stunden * 60 Minuten * 60 Sekunden) also 10 Stellen ...
Was ist mit diesen VB Scribts ?
https://www.herber.de/bbs/user/98877.xlsx

Bild

Betrifft: ja und nein ...
von: der neopa C
Geschrieben am: 17.07.2015 15:24:17
Hallo IMMo,
... der obere Teil Deiner Aussage entspricht voll meiner Aussage. Hierzu müsstest Du entscheiden.
Die von Dir dann aufgemachte Rechnung ist sicherlich (hab es jetzt nicht nachgerechnet) mathematisch richtig, trifft aber nicht die Problematik, die damit in Excel besteht
Denn da gehst Du davon aus, dass eine Sekunde durch als Dezimal 1 abgebildet wird. Ich schrieb Dir jedoch: "... sind in Excel die Uhrzeiten nur entsprechend formatierte Dezimalzahlen bezogen auf einen Tag=1" . Wenn dezimal 1,00 (entsprechend formatiert) einem Tag entspricht sind somit 12 Stunden in Excel dezimal 0,50. Aber schon z.B. 5h sind 0,2083333 ... etc.
Soweit für Dich jetzt verständlich, warum es bei EXCEL-Zeitvergleichen fast immer ein Genauigkeitsproblem gibt?
Gruß Werner
.. , - ...


Bild

Betrifft: AW: ja und nein ...
von: Immo Woschny
Geschrieben am: 17.07.2015 16:37:45
"Formel: =G6-G$6" hab ich jetzt probiert...ja, das ist doof !
Dann ist mein Problem also nicht mit Excel lösbar, denn mit den DatumZeit Ungenauigkeiten funktioniert es nicht und es sind immer unterschiedliche Lücken zwischen den Koordinaten...
Mist.

Bild

Betrifft: nun es bestünde schon noch eine Möglichkeit ...
von: der neopa C
Geschrieben am: 18.07.2015 11:57:46
Hallo iMMo,
... um zu einer vertretbaren Lösung zu kommen.
Vorausgesetzt wird aber, dass wirklich immer Messungen im jeweils betreffenden Bereich im gleichen Zeitabstand (z.B. eine Sekunde) vorliegen und entsprechend lückenlos (ohne Leerzeilen) dokumentiert sind. Die bisher ermittelten Werte müssen von Dir auch so akzeptiert sein und eine lineare Interpolation ist für Dich ausreichend genau.
In dem Fall muss man ja nicht über die dokumentierte (Excel-)Zeit arbeiten sondern kann über den Zellenabstand der ermittelten Werte interpolieren. Den Zellenabstand (auch wenn er verschieden groß ist) entspricht dann quasi z.B. einer Sekunde und lässt sich formeltechnisch ermitteln und so ins entsprechende Verhältnis setzen.
Wäre Dir damit geholfen?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nun es bestünde schon noch eine Möglichkeit ...
von: Immo Woschny
Geschrieben am: 19.07.2015 10:48:10
Ja, ich denke schon...und wie stelle ich das an ?
Es wäre schön es anhand meiner hochgeladenen Datei zu erklären !?

Bild

Betrifft: nun denn ...
von: der neopa C
Geschrieben am: 19.07.2015 15:30:30
Hallo iMMo,
... folgende Formel in Zelle U5 kopieren, das Zellenzahlenformat auf benutzerdefiniert: 0,00;; einstellen und dann diese Formel ziehend nach rechts und unten kopieren.
(In U5:V5 kann formeltechnisch nicht wirklich sinnvoll eine Interpolation vorgenommen werden, deshalb hab ich diese Werte formeltechnisch auf 0,00 gesetzt und mit dem benutzerdefinierten Zahlenformat "ausgeblendet".)
Die Spalten K:L kannst Du einfach ausblenden.

 KLUV
3BreitengradLängengradBreitengradLängengrad
4  interpoliertinterpoliert
500  
652.249.14910.522.82052249149,0010522820,00
70052249149,5010522754,67
80052249150,0010522689,33
90052249150,5010522624,00
100052249151,0010522558,67
110052249151,5010522493,33
1252.249.15210.522.42852249152,0010522428,00
130052249151,0010522359,40
140052249150,0010522290,80
150052249149,0010522222,20
160052249148,0010522153,60
1752.249.14710.522.08552249147,0010522085,00
180052249146,0010522018,40

Formeln der Tabelle
ZelleFormel
U5=WENNFEHLER(((ZEILE()-VERWEIS(9;1/(K$1:K5>0); ZEILE(K$1:K5)))/(ZEILE()-VERWEIS(9;1/(K$1:K5>0); ZEILE(K$1:K5))+VERGLEICH(1;MMULT((K5:K56>0)*1;1); )-1))*(INDEX(K:K;ZEILE()+VERGLEICH(1;MMULT((K5:K15>0)*1;1); )-1)-VERWEIS(9;1/(K$1:K5>0); K$1:K5))+VERWEIS(9;1/(K$1:K5>0); K$1:K5); K5)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nun denn ...
von: Immo Woschny
Geschrieben am: 19.07.2015 16:11:20
Wow, das scheint es zu sein ... Vielen vielen Dank !!!
Was für eine Wahnsinnsformel...gerne würde ich das verstehen was da passiert, das ist aber sicher aussichtslos :)
Beste Grüße und ein schönes Restwochenende

Bild

Betrifft: na ja ...
von: der neopa C
Geschrieben am: 19.07.2015 17:39:46
Hallo iMMo,
... als aussichtslos würde ich es nicht bezeichnen. Allerdings ohne entsprechenden Aufwand ist es natürlich auch nicht realisierbar.
In die Wirkungsweise einiger der verwendeten Funktionen und Formelteile kannst Du z.B. hier: http://www.online-excel.de/ etwas einlesen.
Der "Rest" ist Übung und Erfahrung.
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Excel 2007 SVERWEIS über MITTERNACHT"