Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen

Werte aus Matrix in 1 Spalte bringen

Betrifft: Werte aus Matrix in 1 Spalte bringen von: thema
Geschrieben am: 04.08.2008 12:43:44

Hallo liebe ExcelExperten!

Habe Tabelle mit Werten in einer Matrix, d.h.
Stundenwerte von 07:00 Uhr bis 06:00 Uhr (in Zeilen von links nach rechts) für n Tage (in Spalten)

vgl. Anlage
https://www.herber.de/bbs/user/54317.xls

AUFGABE:
Werte sollen nun cronologisch in 1 Spalte gebracht werden, d.h.
01.01.2006 07:00 Uhr xxxx,xx
01.01.2006 08:00 Uhr xxxx,xx
01.01.2006 09:00 Uhr xxxx,xx
usw.
30.07.2007 23:00 Uhr xxxx,xx
30.07.2007 24:00 Uhr xxxx,xx

Gibt's dazu eine hilfreiche Funktion? Oder ein VBA-Skript?

Vielen Dank für eure Hinweise und Tipps

Grüße
TheMa

  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Rudi Maintaire
Geschrieben am: 04.08.2008 13:12:53

Hallo,
erst eine Pivot-Tabelle erstellen und diese dann per Formeln ergänzen.





  A B C D E F G
1 Pivot       Auswertung    
2              
3 DATUM Daten Summe   Datum Zeit Wert
4 01.01.2006 Summe von 01 36283   01.01.2006 01:00:00 36283
5   Summe von 02 36150   01.01.2006 02:00:00 36150
6   Summe von 03 36931   01.01.2006 03:00:00 36931
7   Summe von 04 37876   01.01.2006 04:00:00 37876
8   Summe von 05 41495   01.01.2006 05:00:00 41495
9   Summe von 06 48286   01.01.2006 06:00:00 48286
10   Summe von 07 54257   01.01.2006 07:00:00 54257
11   Summe von 08 55220   01.01.2006 08:00:00 55220
12   Summe von 09 54389   01.01.2006 09:00:00 54389
13   Summe von 10 53672   01.01.2006 10:00:00 53672
14   Summe von 11 53442   01.01.2006 11:00:00 53442
15 02.01.2006 Summe von 01 38649   02.01.2006 01:00:00 38649
16   Summe von 02 38679   02.01.2006 02:00:00 38679
17   Summe von 03 38780   02.01.2006 03:00:00 38780
18   Summe von 04 39784   02.01.2006 04:00:00 39784
 Zur Formelanzeige mit der Maus auf die gelben Zellen gehen


Gruß
Rudi


  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Tino
Geschrieben am: 04.08.2008 14:28:45

Hallo,
teste mal diesen Code, es wird dabei eine neue Tabelle erstellt.

Option Explicit

Sub Test()
Dim Bereich As Range
Dim A As Long, B As Long
Dim lngRow1 As Long, lngRow2 As Long
Dim lngColumn As Long, lngColumn2 As Long
On Error GoTo Fehler:
Application.ScreenUpdating = False

With Sheets("Gesamt")
Set Bereich = .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
lngColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column - 1
End With

With Sheets.Add 'Neue Tabelle erstellen
 .Cells.Clear
 lngColumn2 = 2

For A = 0 To lngColumn - 2
 'Kopieren der Bereiche
  lngRow1 = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  Bereich.Offset(0, A).Copy .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
  Bereich.Offset(0, -1).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
  lngRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row
 
  'Datum und Uhrzeit zusammenführen
 For B = lngRow1 To lngRow2
    .Cells(B, 1) = .Cells(B, 1) + CDbl(Tabelle2.Cells(1, lngColumn2) / 24)
 Next B
 lngColumn2 = lngColumn2 + 1
 
 
Next A
.Range("A:A").NumberFormat = "dd/mm/yyyy hh:mm;@"

Set Bereich = .UsedRange
'Neuer Bereich Sorieren
        Bereich.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With

Application.ScreenUpdating = True
Fehler:
If Err.Number <> 0 Then MsgBox Err.Description, vbCritical, "Fehler"


End Sub




Gruß Tino

www.VBA-Excel.de




  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Rudi Maintaire
Geschrieben am: 04.08.2008 15:00:36

Hallo,
dauert aber ganz schön lange.
Schneller:

Sub tt()
  Dim vntTmp(), vntQuelle
  Dim i As Long, j As Long, n As Long
  vntQuelle = Sheets("Gesamt").Range("A1").CurrentRegion
  ReDim vntTmp(1 To (UBound(vntQuelle) - 1) * (UBound(vntQuelle, 2) - 1) + 1, 1 To 3)
  n = 1
  vntTmp(1, 1) = "Datum"
  vntTmp(1, 2) = "Uhrzeit"
  vntTmp(1, 3) = "Wert"
  For i = 2 To UBound(vntQuelle)
    For j = 2 To UBound(vntQuelle, 2)
      n = n + 1
      vntTmp(n, 1) = vntQuelle(i, 1) * 1
      vntTmp(n, 2) = vntQuelle(1, j) / 24
      vntTmp(n, 3) = vntQuelle(i, j)
    Next
  Next
  With Worksheets.Add
    .Range("A1").Resize(n, 3) = vntTmp
    .Columns(1).NumberFormat = "DD.MM.YYYY"
    .Columns(2).NumberFormat = "[hh]:mm"
    .Range("A1").Sort _
        key1:=.Range("A2"), order1:=xlAscending, _
        key2:=.Range("B2"), order2:=xlAscending, _
        header:=xlYes
  End With
End Sub


Gruß
Rudi


  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Tino
Geschrieben am: 04.08.2008 15:17:32

Hallo,
ihr seid aber heute kleinlich, heute ist doch Montag. ;-)

Stimmt Dein Code ist 2 Sekunden schneller.

Korrektur in
For j = 2 To UBound(vntQuelle, 2) – 1
wegen „Uhr“ anstatt einer Zahl in der Überschrift.


Gruß Tino


  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Daniel
Geschrieben am: 04.08.2008 15:34:28

Hi
da passt aber was noch nicht so ganz
der 1.1.2006 1:00 gehört zwar zum 1.1. ist aber vom Realatum her schon der 2.1., somit ist die Sortierung chronologisch falsch.

allerdings wäre hier eine genauere Spezifikation von thema erforderlich, wie mit den Werten umgegangen werden soll, die nach mitternacht liegen. Soll hier das Realdatum eingetragen werden?
oder muss der bezug zum Vortag erhalten bleiben?
wenn dies der Fall ist und trotzdem die Sortierung richig sein Soll, dann bräuchte man 2 Datumspalten:
1. Bezugdatum
2. Realdatum

Gruß, Daniel


  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Tino
Geschrieben am: 04.08.2008 16:12:47

Hallo Daniel,
worüber du dir alles Gedanken machst.
Ich sehe das anders, in der Tabelle geöhrt der 1.1.2006 1:00 zum 1.1., was wäre dies sonst für Zuordnung in dieser Tabelle?
Aber dies kann uns nur TheMa beantworten welche Zuordnung richtig ist.


Gruß Tino


  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Daniel
Geschrieben am: 04.08.2008 16:19:35

Hi
naja, schau mal in die Fernsehzeitung, auf welcher Seite die Programme aufgelistet sind, die an einem bestimmten Wochentag um 1:00 laufen.
stehen die bei dem realen Wochentag oder stehen die beim Vortag?
wenn du jetzt das Fernsehprogramm chronologisch in die richtige Gesamtreihenfolge bringen willst, wie musst du dann sortieren?

Gruß, Daniel


  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Tino
Geschrieben am: 04.08.2008 16:32:57

Hallo,
wir sind hier bei Exceltabellen und nicht beim Fernsehn ;-)
Ich schreibe doch nicht
04.08.2008 07:00 Uhr und meine eigendlich 05.08.2008 07:00 Uhr ???

thema, bitte lüfte dass geheimnis!

Gruß Tino


  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Daniel
Geschrieben am: 04.08.2008 17:37:33

Hi
naja, die Anordnung der Zeiten in der Originaltabelle hat dich nicht etwas stutzig gemacht?
schließlich werden Exceltabellen immer für reale Anwendungen geschrieben.
aber wie schon gesagt,dazu muss thema stellung nehemen.

Gruß,Daniel


  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: Daniel
Geschrieben am: 04.08.2008 14:37:33

Hi

das geht auch ohne VBA, nur mit Formeln, dazu in einem neuen Blatt

diese Formel in A2 eintragen (Datum + Zeit)
~f~
=INDEX(Gesamt!$A$2:$A$943;AUFRUNDEN((ZEILE()-1)/24;0))+ZEIT(WERT(INDEX(Gesamt!$B$1:$Y$1;1;(ZEILE()-1)-24*(AUFRUNDEN((ZEILE()-1)/24;0)-1)));0;0)+1*(WERT(INDEX(Gesamt!$B$1:$Y$1;1;(ZEILE()-1)-24*(AUFRUNDEN((ZEILE()-1)/24;0)-1)))<7)
~f~

diese Formel in B2 eintragen (Werte)
=INDEX(Gesamt!$B$2:$Y$943;AUFRUNDEN((ZEILE()-1)/24;0);(ZEILE()-1)-24*(AUFRUNDEN((ZEILE()-1)/24;0)-1))

und beide Formeln weitgenug nach unten kopieren

als Script könntest du das hier verwenden:

Sub umgestalten()
Dim shNeu As Worksheet
Dim shAlt As Worksheet
Set shAlt = Sheets("Gesamt")
Set shNeu = Worksheets.Add(before:=shAlt)

Application.ScreenUpdating = False
Do Until shAlt.Cells(2, 2).Value = ""
    shAlt.Range("b1:y2").Copy
    shNeu.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlValues, Transpose:=True
    Range(shNeu.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0), _
          shNeu.Cells(Rows.Count, 3).End(xlUp).Offset(0, -1)).Value = shAlt.Cells(2, 1).Value
    shAlt.Rows(2).Delete
Loop
With Range(shNeu.Cells(2, 1), shNeu.Cells(Rows.Count, 2).End(xlUp).Offset(0, -1))
    .FormulaR1C1 = "=RC[1]+TIME(VALUE(RC[2]),0,0)+1*(VALUE(RC[2])<7)"
    .Formula = .Value
End With
With shNeu
    .Range("B:C").Delete
    .Range("A:A").NumberFormat = "DD.MM.YYYY hh:mm"
    .Range("B:B").NumberFormat = "#,##0.00"
End With
Application.ScreenUpdating = True
End Sub


allerdings werden die Daten von "Gesamt" dabei gelöscht, also vorher lieber ne sicherungskopie anlegen.
Außerdem musst du darauf achten daß es nicht zuviele daten werden, das Excel 2003 nur 65536 Zeilen hat.


wesentlich effektiver wäre aber ein Script, daß die Formeln in die Tabelle schreibt:

Sub umgestalten()
Dim Anz As Long
Dim shNeu As Worksheet
Dim shAlt As Worksheet
Set shAlt = Sheets("Gesamt")
Set shNeu = Worksheets.Add(before:=shAlt)

Anz = Range("B2:y943").Cells.Count

With shNeu
    .Range("A2").Resize(Anz, 1).FormulaR1C1 = "=INDEX(Gesamt!R2C1:R943C1,ROUNDUP((ROW()-1)/24,0) _
)+TIME(VALUE(INDEX(Gesamt!R1C2:R1C25,1,(ROW()-1)-24*(ROUNDUP((ROW()-1)/24,0)-1))),0,0)+1*(VALUE(INDEX(Gesamt!R1C2:R1C25,1,(ROW()-1)-24*(ROUNDUP((ROW()-1)/24,0)-1)))<7)"
    .Range("B2").Resize(Anz, 1).FormulaR1C1 = "=INDEX(Gesamt!R2C2:R943C25,ROUNDUP((ROW()-1)/24, _
0),(ROW()-1)-24*(ROUNDUP((ROW()-1)/24,0)-1))"
    .Range("A:A").NumberFormat = "DD.MM.YYYY hh:mm"
    .Range("B:B").NumberFormat = "#,##0.00"
End With
With shNeu.Range("A2").Resize(Anz, 2)
.Formula = .Value
End With

End Sub




Gruß, Daniel


  

Betrifft: AW: Werte aus Matrix in 1 Spalte bringen von: thema
Geschrieben am: 04.08.2008 15:49:19

Hallo liebe Experten: Daniel, Tino, Rudi!
Vielen Dank für eure ausführlichen Hinweise:
Jetzt habe ich etwas zum Ausprobieren!

Grüße
TheMa


 

Beiträge aus den Excel-Beispielen zum Thema "Werte aus Matrix in 1 Spalte bringen"