Excel Hilfe

Hallo Dietmar,

Datumswerte werden in Office immer in Tagen seit dem 30.12.1899 geführt.
0 = 30.12.1899
0,5 = 30.12.1899 12:00:00
So gesehen gibt es eigentlich keinen wirklichen Unterschied zwischen irgendeiner Gleitkommazahl und einem Datum.
Die Darstellung des Wertes als Datum ist nur eine Frage der Formatierung der Zelle. Man muss Excel also nur "mitteilen" dass der Wert ein Datum darstellt und als solches angezeigt werden soll.

Das "benutzerdefinierte Zellformat"
TT.MM.JJJJ hh:mm:ss
sollte das von dir gewünschte Ergebnis liefern.

CS
Harold

Hallo Dietmar,

wie Harold es beschrieben hat, sollte es eigentlich gehen. Siehe mein Bildschirmfoto. Spalte A als Zahl mit 4 Dezimalen formatieren, Spalte B wie zu sehen. Auf Punkte und Kommata bei der Eingabe achten!

Hallo Dietmar,

um dein Problem zu verstehen und dir helfen zu können, würde es vermutlich sehr helfen, wenn du einige Beispiele von Werten in deiner Eingabezelle "A3" mit ihrer ursprünglichen Bedeutung bzw. Interpretation und das von dir erwartete Ausgabeergebnis nennen köntest.

Die von dir Eingangs angegebene Formel ändert ja nichts an der Art des Wertes. Letztlich ist es nur eine Verschiebung des Wertes von Zelle A3 auf der Zeitachse:

Ausgabewert = A3 + DATUM(2000;1;1) - 2451544,5
<=>
Ausgabewert = A3 + 36526 - 2451544,5

Damit das funktionieren kann, muss in jedem Fall dein Eingangswert in Zelle A3 so "gebaut" sein, dass 1 = ein Tag ist. Ist das nicht der Fall, muss der Wert aus A3 zuerst in dieses Schema umgerechnet werden.
Auch muss das Ergebnis nach dem 30.12.1899 liegen. Für "negative", also frühere Zeiten ist der Kalender meines Wissens nicht implementiert. Da müsste man sich tatsächlich die Kalenderlogik selber nachbauen.

CS
Harold

Hallo Dietmar,

deine lezte Info klingt mir sehr nach einer falschen Übernahme der Daten aus dem CSV bzw. einem formatierten CSV in deutscher Zahlennotation.

VBA "denkt" englisch. Das Dezimaltrennzeichen ist hier der Punkt. Tausendertrennzeichen sind in VBA bei der Interpretation von Zeichenfolgen unzulässig und müssen vorher entfernt werden.
Val("1.234.567,123) liefert - nicht wie man vielleicht nach deutscher Notation erwarten würde 1234567,123 sondern 1,234.
Val("1,234,567.123") liefert lediglich 1, da ein Komma in VBA kein Bestandteil eine Zahl ist .

val(replace(replace("1.234.567,123", ".", ""),",",".")) liefert 1234567,123

CS
Harold

Dietmar,
was Harold sagt ist noch nicht alles. Eine Zelle, die, weil das Zahlenformat durch unbrauchbare Zeichen (z.B. Punkt anstelle von Komma) nicht als Zahl erkannt wird, wird zu einer Zelle, die Excel beim CSV-Import als Text 'formatiert'. Das hat ungeahnte Seiteneffekte:

Beispiel: "0815" als Text behält die führende Null, als Zahl steht da 815, es sei denn, man formatiert die Zelle als Zahl mit "0000" (man zwingt ihr eine vierstellige Darstellung auf). Beim Sortieren der Zellen werden aber Texte und Zahlen getrennt. Aus
814
"0815"
816

wird dann

"0815"
814
816 ... und keine Sau weiß dann warum.

Konkret in Deinem, Fall: Zellen mit 0:00h als Zeit haben keine Nachkommawerte und könnten richtig als Zahl reinkommen. Zellen mit anderen Uhrzeiten kommen als Text rein.

Nur zur Abgrenzung:
Postleitzahlen sollten besser als Text eingelesen werden, mit denen soll keiner "rechnen". Spätestens, wenn jemand ein "D-" im Adressfeld davor hat, gibt es sonst Probleme, dito IBAN, ISBN, Sternkatalognummern etc.

Hallo Dietmar,

ja, es hilft, wenn Eingangsdaten und Programm im aktuellen Zustand bekannt sind.
Es handelt sich ja in beiden Fällen um einfachen Text. Insofern kannst Du vielleicht die ersten z.B. 10 Zeilen des CSV und den Programmabschnitt, über den du die Daten einliest, direkt hier posten.

Der Hinweis von Kalle ist bei Excel auch immer wichtig. Merkwürdige Effekte der beschriebenen Art kann man ggf vermeiden, indem man Excel davon abhält selber zu bestimmen, was für ein Format denn wohl passt.
Im Kalkulationsblatt hilft oft, die Zellen VOR einfügen der Daten im gewünschten Format zu formatieren. Nachträgliches formatieren funktioniert nicht immer. Gelegentlich werden die enthaltenen Werte trotz nun scheinbar passend eingestelltem Format immer noch falsch behandelt. In meinen Augen ein sehr unschönes Verhalten (Fehler?) in Excel, da ggf. schlecht zu erkennen.
Bei Programmen hilft das explizite casten in einen bestimmten Typ bzw. Aufnahme des auszugebenden Wertes in eine typisierte Variable, die dann in die Zelle ausgegeben wird. Implizit definierte Variablen ohne klaren Typ können ein Fallstrick sein.

option explicit am Beginn des VBA-Moduls erzwingt die Deklaration der Variablen und vermeidet so auch Fehler durch Tippfehler in Variablennamen.
Variant Tyen nur verwenden, wenn nötig! Deklaration von Variablen immer typisiert also z.B. dim MeineZahl# für eine Gleitkommazahl. (grob) falsche bzw. unpassende Werte führen dann ggf. zu Laufzeitfehlern und sind so leichter zu finden.

CS
Harold

Hallo Dietmar,

um den Inhalt vom CSV "im Original" zu sehen und kopieren zu können, die CSV-Datei über den Explorer über "Öffnen mit" aufrufen und hierfür den Editor wählen.

Sehe ich es richtig, dass du das CSV einfach mit Excel aufrufst und dann mit den enthaltenen Daten weiterarbeitest?

Was ist bei Aufruf des Makros "Text_in_Spalten" markiert? Kannst Du diesen markierten Inhalt hier bitte auch einmal einfügen?

Kannst du bitte auch noch einmal alle Makros als einfachen Text einfügen. (Ich bin ein wenig zu faul das abzutippen.) Dann kann ich das einmal "live" testen und ggf anpassen. (Ich programmiere sehr wenig mit Excel und auch mehr "zu Fuß". So gesehen bin ich mit den Excel-spezifischen Worksheet-Funktionen nicht unbedingt "per Du".

CS
Harold

PS:
ich ergänze mal einen mini-Programmschnipsel, der eine CSV Zeichenfolge aus Zelle A3 zerlegt und ausgibt:

Reihe 1: "as is"
Reihe 2: "geputzt" als Zahlen
Reihe 3: Zahl in ein Datum "konvertiert" (implizite Formatierung)
Reihe 4: Das Datum als manuell formatierter Text

Vielleicht kannst du hiermit ja schon etwas anfangen. Deine Berechnung mit der Verschiebung des Wretes lässt sich auf die Zellenwerte in den Reihen 2 und 3 anwenden.

Sub TextToCol()
Dim CSV_Datumswerte As String
Dim Datumswert
Dim i As Integer
i = 0
CSV_Datumswerte = Cells(3, 1).Value
For Each Datumswert In Split(CSV_Datumswerte, ";")
i = i + 1
Cells(4, i).Value = CStr(Datumswert)
Cells(5, i).Value = Val(Replace(Replace(Replace(Datumswert, """", ""), ".", ""), ",", "."))
Cells(6, i).Value = CDate(Val(Replace(Replace(Replace(Datumswert, """", ""), ".", ""), ",", ".")))
Cells(7, i).Value = Format$(CDate(Val(Replace(Replace(Replace(Datumswert, """", ""), ".", ""), ",", "."))), "dd.mm.yyyy hh:nn:ss")
Next
End Sub

Ich möchte die Schäden, die das Beharren auf das schwachsinnige Dezimalkomma im Deutschen verursacht, nicht ermessen. Bei CSV ist das Spaltentrennszeichen in Deutschland ein ";", weil man ja nicht das anderswo normale "," nehmen kann. Bei wie vielen Windows-Rechnern habe ich die Datumseinstellungen auf Dezimalpunkt umgestellt? Wie viele Studis kamen mit scheinbar erratischen Ergebnissen dieser Art zu mir. Wann wird das mal endlich reformiert?

Hartwig

Hallo Dietmar,

so wird die Sache klarer.

Ich habe mal eine VBA-Rotine zusammengestrickt, die die Daten aus einer CSV-Datei entsprechend dem Beispielformat extrahiert und passend**(?) formatiert in Excel einfügt.

Es muss nichts in Excel eingefügt werden. Die Prozedur enthält den Dialog zur Dateiauswahl.

**: Ich lese Die Daten als ein julianisches Datum, jeweils gefolgt von zwei Zahlen.

Der Import liefert in den ersten Zeilen:
04.06.2017 20:53:50 -0,223310 0,005790
04.06.2017 20:54:32 -0,225450 0,005820
04.06.2017 20:55:14 -0,238860 0,005850
04.06.2017 20:55:56 -0,219560 0,005810
04.06.2017 20:59:13 -0,225150 0,005470

CS
Harold

Hier die Routine (etwas "roh"):

Sub muniwinImport()
Dim lngCount As Long
Dim MuniWinFile As String
Dim CSV_Zeile$

Dim CSV_Feldwert As Variant
Dim CSV_Zahl As Double
Dim Datumswert As Double
Dim Spalten_Nr As Integer
Dim Zeilen_Nr As Integer
Dim Selection As Excel.Range

On Error GoTo FB

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.InitialFileName = "C:\Users\HTRAP\Documents\MUNITST.CSV"
.Show
If .SelectedItems.Count = 1 Then
MuniWinFile = .SelectedItems(1)
Else
'Keine gültige Dateiauswahl
End If
End With

Open MuniWinFile For Input As #1

Zeilen_Nr = 0 'Die Ausgabe beginnt bei Zeilen_Nr + 1 - also in Zeile 1
While Not EOF(1)
Zeilen_Nr = Zeilen_Nr + 1

Spalten_Nr = 0 'Die Ausgabe beginnt bei Spalten_Nr + 1 - also in Zeile 1

Line Input #1, CSV_Zeile

If Zeilen_Nr >= 2 Then
'Datenzeilen! Zeile 1 auslassen, da sie keine auszuwertenden Werte transportiert

'Felder separieren und in Zielbereich ausgeben
'Problem: Wie sieht die Eingabe aus?
'Ohne Kenntnis der Ziel-Datenformate kann hier nur stumpf übetragen
'und auf die richtigenFormate gehofft werden
'Zielbaerich?
'Transponierte Ausgabe?
'Feldformate zu den Spaltenindizes der CSV-Eingangsdaten


For Each CSV_Feldwert In Split(CSV_Zeile, ",")
Spalten_Nr = Spalten_Nr + 1

Set Selection = Cells(Zeilen_Nr, Spalten_Nr)

CSV_Zahl = CDbl(Replace(CSV_Feldwert, ".", ",")) 'Dezimalpunkt durch Komma ersetzen
Select Case Spalten_Nr
Case 1: 'Die Spalte enthält offenbar das julianische Datum
'=> Zelle passend formatieren
Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"

Datumswert = CSV_Zahl + 36526# - 2451544.5
Cells(Zeilen_Nr, Spalten_Nr).Value = CDate(Datumswert)
Case Else: 'Die Spalte enthält eine Gleitkommazahl
Selection.NumberFormat = "0.000000"
Cells(Zeilen_Nr, Spalten_Nr).Value = CSV_Zahl
End Select
Next
End If

Wend
Close #1

Exit Sub
FB:
MsgBox "Fehler beim CSV-Import: " & Error, vbExclamation

On Error Resume Next
Close #1
End Sub

(edit: Nachkommastellen in der Formatierung erweitert)

Hallo Dietmar,

schön dass es Dir weiterhilft.

Zum stöbern bezüglich Datenaufbereitung:
Bei der Suche nach muniwin-csv-Dateien bin ich hierüber gestolpert:
https://www.bav-astro.eu/rb/rb2015-1/1.pdf
Vielleicht ist das etwas?

zum PS:
Ja, programmiererei füllt einen nicht unerheblichen Teil meiner Arbeitszeit. Da VBA in unserem Warenwirtschaftssystem, Office und auch den Autodesk-Produkten die aktuelle Skripting-Sprache ist, komme ich damit (nach einigen 100000 Zeilen Programmcode) recht gut zurecht. Programmiert wird, was gebraucht wird bzw. die Arbeit erleichtert.
Excel selbst ist meistens nicht das Werkzeug der Wahl, geht aber auch wenn es sein muss.

CS
Harold

Hallo Dietmar,

in den genannten Zeilen steht jetzt:
04.06.2017 21:46:00 -1,017480 0,005740
04.06.2017 21:46:42 -1,032590 0,003630
04.06.2017 21:50:50 -1,007210 0,004430
04.06.2017 21:51:32 -1,004410 0,004210

Was soll mit den Zeilen passieren?
- löschen?
- den Zahlenwert verändern (wie)?
- ...

Wenn klar ist, was du möchtest, kann ich dir das kurz anpassen.

(edit: Die Daten sollen wohl komplett weg, wenn ich deine Makros richtig interpretiere. Modifizierte Prozedur s.u.

edit2: nur Zahlenwerte < -1 weg. Code angepasst)

CS
Harold

Sub muniwinImport()
Dim MuniWinFile As String
Dim CSV_Zeile$
Dim CSV_Feldwert As Variant
Dim CSV_Zahl As Double
Dim Datumswert As Double
Dim Spalten_Nr As Integer
Dim Zeilen_Nr As Integer
Dim Selection As Excel.Range

On Error GoTo FB

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.InitialFileName = "C:\Users\HTRAP\Documents\MUNITEST.CSV"
.Show
If .SelectedItems.Count = 1 Then
MuniWinFile = .SelectedItems(1)
Else
'Keine gültige Dateiauswahl
MsgBox "Keine gültige Dateiauswahl! Funktion wird abgebrochen", vbExclamation
Exit Sub
End If
End With

Open MuniWinFile For Input As #1

Zeilen_Nr = 0 'Die Ausgabe beginnt bei Zeilen_Nr + 1 - also in Zeile 1
While Not EOF(1)
Zeilen_Nr = Zeilen_Nr + 1
Spalten_Nr = 0 'Die Ausgabe beginnt bei Spalten_Nr + 1 - also in Zeile 1
Line Input #1, CSV_Zeile
If Zeilen_Nr >= 2 Then
'Datenzeilen! Zeile 1 auslassen, da sie keine auszuwertenden Werte transportiert
'Felder separieren und in Zielbereich ausgeben
For Each CSV_Feldwert In Split(CSV_Zeile, ",")
Spalten_Nr = Spalten_Nr + 1

Set Selection = Cells(Zeilen_Nr, Spalten_Nr)

CSV_Zahl = CDbl(Replace(CSV_Feldwert, ".", ",")) 'Dezimalpunkt durch Komma ersetzen
Select Case Spalten_Nr
Case 1: 'Die Spalte enthält offenbar das julianische Datum
'=> Zelle passend formatieren
Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"

Datumswert = CSV_Zahl + 36526# - 2451544.5
Cells(Zeilen_Nr, Spalten_Nr).Value = CDate(Datumswert)
Case Else: 'Die Spalte enthält eine Gleitkommazahl
Selection.NumberFormat = "0.000000"
Cells(Zeilen_Nr, Spalten_Nr).Value = CSV_Zahl
End Select
Next

CSV_Zahl = Cells(Zeilen_Nr, 2).Value
If (CSV_Zahl < -1#) Then
Cells(Zeilen_Nr, 1).ClearContents
Cells(Zeilen_Nr, 2).ClearContents
Cells(Zeilen_Nr, 3).ClearContents
'Zeile verwerfen bzw. mit folgenden Daten überschreiben
Zeilen_Nr = Zeilen_Nr - 1
End If

End If
Wend

'Alte Daten am Ende der Liste verwerfen
Set Selection = Range(Cells(Zeilen_Nr + 1, 1), Cells(Zeilen_Nr + 1000, 3))
Selection.Clear


Close #1

Exit Sub
FB:
MsgBox "Fehler beim CSV-Import: " & Error, vbExclamation

On Error Resume Next
Close #1
End Sub

Hallo Dietmar,

Ich habe dein Diagramm-Problem mal grob nachgestellt. (Standard-Punktdiagramm)
Danach ist das beschneiden der Feldwerte, wie zuletzt im Programm ergänzt, der falsche Weg. Dieser Programmabschnitt sollte nach meiner Meinung für deine Auswertungen wieder entfernt werden.

Die Daten sind ja offenbar i.O. und werden unnötigerweise beschnitten. Das Kernproblem ist, dass Excel die Achsformatierung per Automatik nicht besonders glücklich einstellt. Mit ein klein wenig manueller "Nachhilfe" bei den Achs- und Datenreihenformaten wird es dann doch deutlich besser.
(Datumsachse mauell als Datum formatieren, Wertachse manuell mit z.B. 0,000 formatieren.)
Auch die Achsintervalle würde ich manuell setzen, damit sich die Beschriftung auf glatten Zeiten - z.B. Stunden-Haupt- und 5-Minuten-Hilfsraster - bewegt (Hauptintervall 0,041666666666; Hilfsintervall 0,003472222222).
Gitternetzlinien für Haupt und Hilfsintervall. Linien für Hilfsintervall dünn, gestrichelt und mit geringer Farbdeckung, ...
Datenpunktmarkierungen kleiner und ggf. mit teil-transparenter Farbe.

(edit: Diagrammbeispiel eingefügt)
Bsp.:

CS
Harold

Moin,
wenn ich regelmäßig Daten in CSV oder einfachen Textformat importiere, richte ich ein extra Importblatt dafür ein, um dort den Import "aufzubereiten", bevor er in meiner Auswertung landet.
Mittels Kontrollsummen in der Kopfzeile kann man auch Ausreißer sofort erkennen. Z.B. indem man eine Spalte einfügt, die nur die Formel
"=isttext(Quelle)+0 enthält (bzw. istzahl()). Oben in die Kopfzeile dann einfach
=Wenn(Summe(kontrollzellen)=Anzahl(Importzellen);"OK";"Fehler")
Das geht mit jeder anderen Prüfung natürlich auch und man sieht sofort, wenn was falsch läuft. Ich habe keine Ahnung, wie die Importdaten bei Dir aufgebaut sind, aber kannst Du ausschließen, dass nicht irgendwo, ab und zu mal ein NV-Wert enthalten ist?

Hallo Kalle,

sprichst Du hier Dietmar an?

Nach dem bisherigen Verlauf denke ich, dass Dietmars Erfahrungen mit Excel noch relativ gering sind. Ich weiß nicht, ob er Deine Info so zum jetzigen Zeitpunkt einordnen und verwerten kann.

Direktere Hinweise auf Abweichungen vom Zielformat sind in meinen Augen intuitiver über bedingte Formatierungen aufzuzeigen.

Wenn eine Zahl erwartet wird, die Zelle aber keine Zahl enthält ist
<font color="orange">
=(ISTZAHL(INDIREKT(ADRESSE(ZEILE();SPALTE())) )=falsch)
</font id="orange">
eine geeignete Bedingungsformel für z.B. einen roten Zellhintergrund.

für komplexere Bedingungen können ggf auch eigene VBA-Funktionen erstellt werden, ....

Ich denke aber, dass das hier im Moment zu weit führt.

--------------
Der gepostete Import-Code hat eine rudimentäre Fehlerbehandlung, die den beim casten unpassender CSV-Feldinhalte in das gewünschte Zielformat auftretenden Laufzeitfehler "abfängt" und die Datenübernahme abbricht.

Das programmtechnisch so aufzubohren, dass fehlerhafte Eingabezeilen erkannt, separiert und erkannte Abweichungen als Klartext gemeldet werden ist einfach - wenn man bereits ein wenig Programmiererfahrung hat.
Ggf. werde ich das nachher noch ergänzen. Das mag Dietmar oder anderen interressierten Mitlesern helfen, eigene, ähnlich gelagerte Probleme zu lösen.

CS
Harold

Harold,
gerade, wenn keine größeren Erfahrungen im Umgang mit Excel bestehen, lohnt es sich, den Workflow in Excel auf separate Blätter abzubilden.
Z.B. ein Blatt für Import, ein weiteres für Auswertung, ein drittes für die Ausgabe/Grafik.

Bei kleinen Datenmengen (die komplett auf den Bildschirm passen), ist die bedingte Formatierung sicher eine gute Wahl. Aber wenn es mehr werden, so meine Erfahrung, nutze ich eine Kopfzeile für - ich nenne es mal - summarische Kontrollangaben. Die fixiere ich durch Fensterteilung/Fixierung. Die eigentlichen Eingaben landen dann erst ab Zeile 3. So sieht man anhand Zeile 2, ob man auch tatsächlich nach oben gescrollt hat, wenn man große Bereiche markiert.

Leider lehrt mir meine Berufserfahrung, dass händisches Arbeiten mit Excel mehrere Fehler je Stunde Arbeit erzeugt. Entweder man hat eine zweite Instanz, die kontrolliert oder man baut so viel wie möglich Kontrollbedingungen in seinen Workflow ein, um den Großteil sofort zu erkennen. Mein Vorschlag oben kommt zudem ohne VBA-Programmierung aus und lässt sich auch 4 Wochen später noch nachvollziehen. Die bedingten Formatierungen haben den Nachteil, dass man der Zelle nicht gleich ansieht, ob sie formatiert ist oder nicht, es sei denn, man formatiert gleich den Komplementärfall mit z.B. Rot bei Fehler, grün bei OK, gelb wenn Zelle nicht befüllt, aber bedingt formatiert ist. Ist sie dann ohne Farbe, fehlt die Formatierung, aus welchem Grund auch immer.

Der Nachteil von VBA/Makros ist regelmäßig, dass sie nicht dokumentiert sind und man später kaum noch weiß, was alles gemacht wird. Deshalb bevorzuge ich klassische Formeln in Extra-Kontrollspalten sowie die Kommentarfunktion zu Zellen.

Das alles hört sich vielleicht anfangs etwas umständlich an, aber die vielen Fallstricke in Excel haben es nunmal in sich. So Projekte wachsen ja auch über die Zeit und man erspart sich viel Ärger, wenn man so viel wie möglich in Excel selbst dokumentiert.

Gruß
... muss jetzt wieder zurück an so eine Excel-Daten-Tapete ...