BITTE helfen Sie uns HEUTE mit einer SPENDE
Helfen Sie das LibreOffice Forum zu erhalten!

❤️ DANKE >><< DANKE ❤️

> KEINE WERBUNG FÜR REGISTRIERTE BENUTZER!<
Ihre Spende wird für die Deckung der laufenden Kosten sowie den Erhalt und Ausbau 🌱 des LibreOffice Forums verwendet.
🤗 Als Dankeschön werden Sie im Forum als LO-SUPPORTER gekennzeichnet. 🤗

Calc: Spalte in mehrere Zeilen transponieren

Alles zur Programmierung im LibreOffice.
Statistiker
Beiträge: 23
Registriert: Mi 7. Okt 2020, 16:47
Wohnort: Ostthüringen

Calc: Spalte in mehrere Zeilen transponieren

Beitrag von Statistiker » Mi 7. Okt 2020, 17:10

Hallo,
ich bin neu im Forum und habe gleich eine Frage.
Ich habe eine große Tabelle mit zigtausend statistischen Daten. Die Werte sind aber alle in einer Spalte konzentriert. Diese Werte möchte ich nun in Spalten verschieben, so dass die Tabelle nun etwas kürzer, aber dafür breiter wird. Es sind immer sechs Zeilen, die für ein Item (Block) stehen, diese müssten also in sechs Spalten aufgeteilt werden. Diese Spalten beginnen direkt rechts neben "Werte"-Spalte. Auf gleicher Höhe, d.h. die Zeilen in denen sich die Werte 1, 7, 13 usw. befinden.
Zur 'bildlichen 'Erklärung: aus den untereinanderstehenden Zahlen einer Spalte
1
2
3
4
5
6
7
8
9
10
11
12
13
14
...

sollen also sechs Spalten entstehen
1 2 3 4 5 6
7 8 9 10 11 12
13 14 ..

Das Makro sollte automatisch durchlaufen, bis am Ende der Spalte kein Wert mehr ist bzw. eine leere Zelle kommt.
Vielleicht könnten dann auch die nun freigewordenen jeweils 5 Spalten pro Block geöscht werden. GIbt es hier eine Variante. dies zu realisieren? In VBA war dies jedenfalls möglich, bin seit 2020 auf LO umgestiegen.

Danke im voraus für Eure Bemühungen.
Warum ist ein Kanaldeckel rund? Damit er nicht in den Kanal fallen kann...

Wanderer
Beiträge: 895
Registriert: Di 11. Feb 2014, 20:03
Wohnort: Berlin

Re: Calc: Spalte in mehrere Zeilen transponieren

Beitrag von Wanderer » Mi 7. Okt 2020, 21:22

Ohne Macro hilft INDIREKT()

http://www.ooowiki.de/CalcFunktionenTab ... essen.html

MG, Jörn
LO 6.0.7 (32Bit) Win8.1 Pro 32 Bit/ LO 6.3.2 Win10 64Bit / LO 6.0.7 Win7 Pro 64 Bit

craig
Beiträge: 1137
Registriert: Do 21. Apr 2016, 11:42

Re: Calc: Spalte in mehrere Zeilen transponieren

Beitrag von craig » Mi 7. Okt 2020, 23:50

Hallo,

mit Makro:
Spalte A per Array transponieren.ods
(12.01 KiB) 277-mal heruntergeladen
Beachte auch die Kommentare im Code...
Ich habe eine große Tabelle mit zigtausend statistischen Daten
Ich tranponiere die Daten im Code mittels zweier Array's.
Dieses hat zur Folge, dass die Daten schneller verarbeitet werden als bei einer
Tabellenaktion. Trotzdem kann es sein, dass bei riesigen Datenmengen die
Abarbeitung des Makros etwas dauert.
Gruß

Craig

Nie die Sicherungskopie vergessen!

════════════════════════════════════════════════
WIN 10 Pro 64-Bit • LO 7.4.5.1 (x64) • AOO 4.1.8

Statistiker
Beiträge: 23
Registriert: Mi 7. Okt 2020, 16:47
Wohnort: Ostthüringen

Re: Calc: Spalte in mehrere Zeilen transponieren

Beitrag von Statistiker » Do 8. Okt 2020, 14:32

@Wanderer
Hallo,
danke für deine Antwort. Mir kommt es so vor, daß man bei LibreOffice/OpenOffice lieber mit Formeln und Funktionen als mit Makros arbeitet - im Gegensatz zu Microsoft (Excel).
Ich bevorzuge Makros, die man immer wieder einsetzen kann nach einer Anpassung auf die entsprechenden Tabellen. Man kann diese Makros auskommentieren und weiß dann somit, zu was sie dienen.

So ist es einfacher für mich, Makros zu benutzen für Abläufe, die immer wiederkehren bzw. die sich anpassen lassen. Man muss nicht immer neue Formeln und Beziehungen nachschlagen und eingeben. Manche Dinge lassen sich nur auf diesem Weg lösen. Es ist nur schade, das das Aufzeichnungs-Script eines "mitgeschnittenen" Makros sehr unkomfortabel ist.

Trotzdem danke ich dir für deinen INDIREKTen Vorschlag und sende freundliche Grüße aus Westsachsen, Micha:el :D
Warum ist ein Kanaldeckel rund? Damit er nicht in den Kanal fallen kann...

Statistiker
Beiträge: 23
Registriert: Mi 7. Okt 2020, 16:47
Wohnort: Ostthüringen

Re: Calc: Spalte in mehrere Zeilen transponieren

Beitrag von Statistiker » Do 8. Okt 2020, 14:47

@craig
Hallo craig
Dein Makro läuft wunderbar - danke hierfür. Ich habe es angepasst - z.B. die Werte aus einer anderen Spalte als A genommen und auch die "Blockgröße" von 6 Werten erhöht. Alles bestens.
Das Problem ist aber, das bei den zeilenweise untereinander geschriebenen Werten keine Zuordnung zu einem Objekt (hier: der Region) erfolgt bzw. händisch eine mühselige Arbeit wäre. So wäre es also hilfreich, wenn z.B. der Textstring aus der ersten Spalte (hier stehen die Regionen) mit in das Array hineingenommen wird und dann als erster Spaltenwert in die "neue" Arrayzeile mit eingetragen wird, also z.B. (dein Beispiel erweitert)

Code: Alles auswählen

  Grünberg	Tag 1	Tag 2	Tag 3	...
  Holzhausen	Tag 7	Tag 8	Tag 9	...  
Da ja nun nach dem Array-Transponieren alle relevanten Werte untereinander stehen, erübrigt es sich wohl die (Original-) Spalten zu löschen, falls man zuvor die "Köpfe" der neuen Spalten definiert hat. Wichtig ist mir vor allem die Zuordnung, wie im obigen Beispiel angegeben.

Danke für Deine Programmierung, ich hoffe Du kannst das Makro meinen Wünschen entsprechend anpassen und danke Dir im voraus.
Warum ist ein Kanaldeckel rund? Damit er nicht in den Kanal fallen kann...

craig
Beiträge: 1137
Registriert: Do 21. Apr 2016, 11:42

Re: Calc: Spalte in mehrere Zeilen transponieren

Beitrag von craig » Do 8. Okt 2020, 16:04

Hallo Micha:el,
Dein Makro läuft wunderbar - danke hierfür.
Dass freut mich sehr, besonders das Du meine Art und Weise des Programmierens verstanden hast.
Bin kein Profil, Hauptsache es funktioniert. ;)
Mir kommt es so vor, daß man bei LibreOffice/OpenOffice lieber mit Formeln und Funktionen als mit Makros arbeitet - im Gegensatz zu Microsoft (Excel).
Ich habe sowohl VBA (bis ca, Excel 2003) und Starbasic programmiert und nutze meine Makros häufig.
Beides hat seine Berechtigung; Nutzen und Aufwand?
Gemäß Deiner Beschreibung ist aus meiner Sicht ein Makro von höherem Nutzen
und der Aufwand es zu schreiben eher gering.

Ein Grund für die eher ablehnende Haltung zu Makros ist die Libre-/OpenOffice API:
https://api.libreoffice.org/docs/idl/ref/index.html
Diese ist sehr komplex und wie im Code erwähnt gibt es es keine Intellisense-Unterstüzung.

Man kann zwar in den neueren Versionen von LibO unter:
Menü Extras → Optionen → LibreOffice → Erweitert → Rubrik: "Optionale Funktionen"
unter "experimentelle Funktionen" einen Haken setzen (erfordert Neustart),
dann kann man die Basic-IDE etwas steuern unter:
Menü Extras → Optionen → LibreOffice → Basic-IDE (überall einen Haken setzen)
Dies hat zur Folge, dass bei bestimmten Anweisung eine Intellisense angeboten wird,
sobald man nach einem Befehl einen Punkt setzt.
Nachteil man handelt sich sich eine Inkompatibilität zu älteren LibreOffice-Verionen und vor allem auch zu AOO ein.
Deshalb nutze ich diese Option nicht, d.h. bei mir sind alle Haken entfernt.

Ich nutze nur XRAY (Beispiele in meinem Code) zur Code-Inspektion.
Es ist nur schade, das das Aufzeichnungs-Script eines "mitgeschnittenen" Makros sehr unkomfortabel ist.
Das Problem des Rekoders ist, dass dieser nur die Dispatch-Commands kennt.
Diese Commands können hilfreich sein, reichen aber nicht für einen effektiven Programmablauf.

Ich halte mich meisten an die API und Basic-Anweisungen (For-Next-Schleifen, usw,).
Das Problem ist aber, das bei den zeilenweise untereinander geschriebenen Werten keine Zuordnung zu einem Objekt (hier: der Region) erfolgt bzw. händisch eine mühselige Arbeit wäre. So wäre es also hilfreich, wenn z.B. der Textstring aus der ersten Spalte (hier stehen die Regionen) mit in das Array hineingenommen wird und dann als erster Spaltenwert in die "neue" Arrayzeile mit eingetragen wird, also z.B. (dein Beispiel erweitert)
Ist kein Problem, benötige allerdings mehr Informationen.
  1. Poste hier Deinen jetzigen Code, das erspart Dir Nacharbeit.
  2. In welcher Tabelle/Spalte stehen die Regionen?
  3. Wie lautet die Zieladresse
    Beispiel für 10 Spalten = B2:K5000
  4. Ziel-Spalte B = Region ?
Gruß

Craig

Nie die Sicherungskopie vergessen!

════════════════════════════════════════════════
WIN 10 Pro 64-Bit • LO 7.4.5.1 (x64) • AOO 4.1.8

Statistiker
Beiträge: 23
Registriert: Mi 7. Okt 2020, 16:47
Wohnort: Ostthüringen

Re: Calc: Spalte in mehrere Zeilen transponieren

Beitrag von Statistiker » Do 8. Okt 2020, 17:17

Hallo,
danke für Deine Antwort, beonders über die bekannte Problematik. Vielleicht kann ich mich auch einmal überreden, XRAY zu nutzen...wie gewünscht poste ich nachfolgend meinen (angepassten) Code.

Code: Alles auswählen

Sub SPALTENTRANSPONIEREN
' Name geändet da Dein Makro parallel noch vorhanden
Dim oDoc as Object
Dim oSheet1 as Object, oSheet2 as Object
Dim oRange1 as Object, oRange2 as Object
Dim oRow1 as Object

Dim mArr1() as Variant, mArr2() as Variant

REM & = Long
Dim nLastRow&
Dim nCnt1&, nCnt2&
Dim nCntX&, nCntY&
	
	' Referenz dieses Dokument
	oDoc =ThisComponent
			
		' Referenz: Tabellenblatt 1
		' Hier ggf. Tabellenblattname anpassen
		oSheet1 =oDoc.Sheets().getByName("Tabelle6")

		' Referenz: Zielbereich der transponierten Daten
		' Spalte B  bis G = 6 Spalten
		' ggf. die Zeilenanzahl vergrößern → z.B.: G5000
		oRange2=oSheet1.getCellRangeByName("I2:ZZ3100")  ' "B2:G1000"

		' Array für die transponierten Daten auf Basis des Zellbereichs "oRange2" dimensionieren
		mArr2()=oRange2.getDataArray()
			
		' Arrayinhalt löschen, nur für den Fall, dass in
		' im Bereich oRange2 Daten enthalten sind.
		For  nCnt1 = 0 To UBound(mArr2())
			For nCnt2 = 0 To 5
				mArr2(nCnt1)(nCnt2)=""
			Next
		Next
'xray osheet1
	' letze befüllte Zeile in Spalte A ermitteln
    oRow1 = oSheet1.Columns(7).queryEmptyCells()  ' ANPASSEN an die Spaltennummer 7=H
'xray oRow1
'xray oRow1(oRow1.Count-1)
'xray oRow1(oRow1.Count-1).RangeAddress
    nLastRow1 = oRow1(oRow1.Count-1).RangeAddress.StartRow-1
'xray nLastRow1

REM Hinweis zur Analyse dieser Codezeile:
REM		getCellRangeByPosition(0,1,0,nLastRow)
			' ( nLeft as long = 0	→ Spalte A
			'	nTop as long = 1 →	Zeile 2
			'	nRight as long = 0	→ Spalte A
			'	nBottom as long = → nLastRow (letzte Zeile in Spalte A)
			oRange1=oSheet1.getCellRangeByPosition(7,1,7,nLastRow1) '(0,1,0,..)
'xRay oRange1
			' Daten aus Spalte A in Array einlesen
			mArr1()=oRange1.getDataArray()

REM Array transponieren	
    For nCnt1 = 0 To UBound(mArr1())
			mArr2(nCntY)(nCntX) = mArr1(nCnt1)(0)

REM		betrifft Array → mArr()	
	        nCntX=nCntX+1
        	' Wenn SpaltenzählerX = 6, dann SpaltenzählerX = 0
	        ' UND ZeilenzählerY + 1
	        ' 171 Werte gehören zu einem Block !
		If nCntX = [b]171[/b] Then
		        nCntX=0
		        nCntY=nCntY+1
		End If
    Next    
einige REMs wurden entfernt..
Also in der ersten Spalte der gleichen Tabelle ist der Name der Region (Gemeinde, Kreis, Bezirk, ...), in der zweiten Spalte ist der Regionencode. Die zu transponierenden Werte sind in der 8. Spalte (H). Zu jedem Block (Region) gehören 171 (19*3*3) Werte. Ich habe diesmal diese Extremwerte aus einer Tabelle mit Altersgruppen genommen. Die Werte sollen in den angegebenen Bereich ("I2:ZZ3100") geschrieben werden, bei mir füllt er die Spalten I bis FW. Die Zielspalte für die Region sollte die erste aus dem Array sein, also I, dementsprechend rücken dann die Werte in die Spalten J bis FX.

Beste Grüße
Warum ist ein Kanaldeckel rund? Damit er nicht in den Kanal fallen kann...

craig
Beiträge: 1137
Registriert: Do 21. Apr 2016, 11:42

Re: Calc: Spalte in mehrere Zeilen transponieren

Beitrag von craig » Do 8. Okt 2020, 19:03

Hallo,

da muss ich nochmal nachfragen, siehe Musterdatei ohne Makro:
Spalte A per Array transponieren ohne Makro.ods
(18.09 KiB) 218-mal heruntergeladen
Lade ggf. korrigierte Dateiversion hier hoch.

Danke
Gruß

Craig

Nie die Sicherungskopie vergessen!

════════════════════════════════════════════════
WIN 10 Pro 64-Bit • LO 7.4.5.1 (x64) • AOO 4.1.8

Statistiker
Beiträge: 23
Registriert: Mi 7. Okt 2020, 16:47
Wohnort: Ostthüringen

Re: Calc: Spalte in mehrere Zeilen transponieren

Beitrag von Statistiker » Do 8. Okt 2020, 20:08

Danke,
da habe ich mich wohl etwas unklar ausgedrückt, ich lade die veränderte Datei nochmals hoch. Hab ein bisschen herumgebastelt, ein neues Blatt mit Textfeld eingefügt. jetzt bin i gspannt... :)
Dateianhänge
Spalte_Array_transponieren.ods
(34.52 KiB) 199-mal heruntergeladen
Warum ist ein Kanaldeckel rund? Damit er nicht in den Kanal fallen kann...

craig
Beiträge: 1137
Registriert: Do 21. Apr 2016, 11:42

Re: Calc: Spalte in mehrere Zeilen transponieren

Beitrag von craig » Sa 10. Okt 2020, 17:39

Hallo,

jetzt bin ich gespannt, ob mein Geschreibsel Deine Wünsche erfüllt.
Spalte_Array_transponieren 4_20201010_151900.ods
(22.45 KiB) 219-mal heruntergeladen
Gruß

Craig

Nie die Sicherungskopie vergessen!

════════════════════════════════════════════════
WIN 10 Pro 64-Bit • LO 7.4.5.1 (x64) • AOO 4.1.8

An alle, die das LibreOffice-Forum nutzen:


Bitte beteiligen Sie sich mit 7 Euro pro Monat und helfen uns bei unserem Budget für das Jahr 2024.
Einfach per Kreditkarte oder PayPal.
Als Dankeschön werden Sie im Forum als LO-SUPPORTER gekennzeichnet.

❤️ Vielen lieben Dank für Ihre Unterstützung ❤️

Antworten