Microsoft Excel

Herbers Excel/VBA-Archiv

Automatisches Aufteilen zweier Spalten in viele Sp


Betrifft: Automatisches Aufteilen zweier Spalten in viele Sp von: J.B.
Geschrieben am: 09.09.2019 14:18:23

Hallo,

verzeiht mit meine unprofessionellen Ausdrucksweisen, ich bin in Excel was große Datenmengen angeht nicht sehr versiert...

Gegeben habe ich zwei Spalten mit hunderttausenden Zeilen. Die Erste Spalte enthält einen Zeitstempel im von Excel auswertbaren Format: 08.07.2019 12:39:39. Die Auflösung der Spalte ist auf die Minute genau. Manchmal fehlen aber einzelne Minuten, ganze Stunden oder gar Tage.
Zu jedem Zeitstempel aus Spalte 1 findet sich in der zweiten Spalte eine dazugehörige Leistung. Dieser Wert ist einfach eine ganze Zahl.

Nun möchte ich zur weiteren Verarbeitung die unhandliche Spalte folgendermaßen aufteilen:

A ; B ; C ; ...
1 ; ; Leistungswerte
2 ; Timestamp ; 08. Jul ; 09. Jul …
3 ; 0:00 ; 968 ; 3732
4 ; 0:01 ; 950 ; 3786
5 ; 0:02 ; 955 ; 3815
6 ; 0:03 ; 958 ; 3799
.
.
.
1442 ; 23:59 ; 3675

Die Spalte mit der Uhrzeit von 0:00 bis 23:59 nehme ich als gegeben. Genauso auch die Zeile 2 mit der Tages- und Monatsangabe.
Also müsste ja das gesuchte Datum diese Bedingung erfüllen:
UND(Stunde(Suchraumzelle)=Stunde($A3); Minute(Suchraumzelle)=Minute($A3); Tag(Suchraumzelle)=Tag(B$2); Monat(Suchraumzelle)=Monat(B$2))
Ich möchte jedoch den Leistungswert in der Zielzelle (B3) stehen haben, der eine Spalte neben der soeben ausfindig gemachten mit dem geprüften Datum steht.
Da einzelne Daten und Bereiche fehlen entsteht leider auch ein Flickenteppich aber das ist nicht weiter schlimm. Es ist sichergestellt, dass jeder Minute nur ein Zeitstempel zugeordnet werden kann. Es sind keine Duplikate vorhanden.
Welche Funktionen kann ich nutzen? Oder ist mein Ansatz zu kompliziert und geht einfacher? Ein VBA-Skript kommt zum Lösen des Problems nicht in Frage.

Vielen Dank schon im voraus!

J.B.

  

Betrifft: AW: Automatisches Aufteilen zweier Spalten in viele Sp von: 1712021.html
Geschrieben am: 09.09.2019 14:53:35

Hi

1. füge in der Ausgangstabelle eine Hilfsspalte ein und wandle hier den Zeitstempel in einen Text mit dieser Formel um:
=Text(A1;"JJJJ-MM-TT hh:mm")

2. die Ausgangstabelle muss nach dem Zeitstempel aufsteigend sortiert sein.

3. in der Zieltabelle muss in Spalte A ab Zeile 3 der Zeitwert von 0:00 bis 23:59 stehen
4. in der Zieltabelle muss Zeile 2 ab Spalte B das jeweilige Datum stehen.

5. die Auswertung machst du dann mit dieser Formel, Formel für B3:

=Wenn(SVerweis(Text($A3+B$2;"JJJJ-MM-TT hh:mm");Tabelle1!B:B;1;1)=Text($A3+B$2;"JJJJ-MM-TT hh:mm"); SVerweis(Text($A3+B$2;"JJJJ-MM-TT hh:mm");Tabelle1!B:C;2;1);"") 
ich mache hier die Umwandlung in Text, weil bei Berechnungen mit Uhrzeiten gerne die Gleitkommafehler auftreten, welche bei der Umwandlung in Text weggerundet werden.

Gruß Daniel
  

Betrifft: AW: Automatisches Aufteilen zweier Spalten in viele Sp von: 1712146.html
Geschrieben am: 10.09.2019 07:29:42

Guten Morgen, vielen Dank für die schnelle Hilfe!

Das mit dem Datum in Text umwandeln merk ich mir, die Abweichung durch Gleitkommazahlen beim Datum ist mir schon häufiger negativ aufgefallen. Super Tipp auf den ich nicht gekommen wäre. =)

Die bei mir funktionierende Formel ist praktisch identisch mit der von dir vorgelegten:

=WENN(SVERWEIS(TEXT($A3+E$2;"JJJJ-MM-TT hh:mm");Tabelle1!$B$3:$B$26696;1;1)=TEXT($A3+E$2; "JJJJ-MM-TT hh:mm"); SVERWEIS(TEXT($A3+E$2;"JJJJ-MM-TT hh:mm");Tabelle1!$B:$C;2;1);"") 
Zur Dokumentation gehe ich jetzt noch näher darauf ein. Vielleicht hilft es ja irgendwann mal einem Laien wie mir der vor einem ähnlichen Problem steht.

In Tabelle 1 findet sich der Ausgangsdatensatz:
A ; B ; C
1 ; Timestamp ; Timestamp
2 ; Mitteleuropäische Sommerzeit ; im Textformat ; Sum Output Power
3 ; 2019-07-08 12:35:39 ; 2019-07-08 12:35 ; 90
4 ; 2019-07-08 12:36:39 ; 2019-07-08 12:36 ; 401

In Tabelle 2 die Zieltabelle:
A ; B ; C ; D ;
1 ; ; Sum Output Power ;
2 ; Timestamp ; 08. Jul ; 09. Jul ; 10. Jul ; 11. Jul ;
3 ; 0:00 ; ; 265 ; 55 ;
4 ; 0:01 ; ; 267 ; 54 ;
5 ; 0:02 ; ; 341 ; 53 ;

In Tabelle 1 wurde in Spalte B Datum und Uhrzeit aus Spalte A in Text umgewandelt:
Nachfolgend die Formel aus der Zelle B3:
=Text(A3;"JJJJ-MM-TT hh:mm")

Spalte C enthält dann die Nutzdaten, in diesem Fall Leistungswerte.
Spalte A muss aufsteigend sortiert sein.
In Tabelle 2 stehen ab Zelle A2 abwärts die Uhrzeiten des jeweiligen Tages beginnend mit 0:00. (Format: hh:mm)
Tag und Monat sind ab Zelle B2 in Zeilenform angegeben. (Format: TT-MM)
Die oben aufgeführte Formel ist dann in Zelle B3 enthalten und kann in jede andere Zelle des Tabellenblatts kopiert werden wo ein Wert stehen soll.

Zur Info: Hier (https://www.ms-office-forum.net/forum/showthread.php?p=1944831#post1944831) habe ich die gleiche Frage gestellt.
Vielen Dank nochmal Daniel!

Gruß J.B.
  

Betrifft: Crossposting von: 1712147.html
Geschrieben am: 10.09.2019 08:11:11

Zur Info...

https://www.ms-office-forum.net/forum/showthread.php?t=362308

Gruß,
steve1da

Beiträge aus dem Excel-Forum zum Thema "Automatisches Aufteilen zweier Spalten in viele Sp"