Calc-Frage

de-users
#1

Hallo,

ich habe mal wieder eine Calc-Frage:

In einer Tabelle habe ich in Spalte A verschiedene Namen
In den Spalten B-...(z.B. B bis F) stehen Wahrheitswerte.
Diese sagen aus, ob derjenige, der in Spalte A steht, in Gruppe 1,
Gruppe 2, Gruppe 3 usw. Mitglied ist oder nicht.

Name Gruppe1 Gruppe2 Gruppe3 Gruppe4 ...
Name1 x
Name2 x x
Name3 x x
...

Jetzt benötige ich daraus ein weiteres Tabellenblatt, dass mir für jede
Mitgliedschaft eine einzelne Zeile generiert:

Name1 Gruppe1
Name2 Gruppe1
Name2 Gruppe3
Name3 Gruppe1
Name3 Gruppe2
...

Die Sortierung ist egal, es dürfte auch nach Gruppen sortiert sein.

Wie kriege ich das hin?

Danke für Tipps und liebe Grüße
Micha

#2

Hallo Micha,

hast du es mit Pivot versucht? Schaut mir ganz danach aus.

LG Günther

#3

Günther Breitfuß schrieb:

hast du es mit Pivot versucht? Schaut mir ganz danach aus.

Nein, davon habe ich noch keine Ahnung...

Michael

#4

Hallo
Link zu einem Tutorial, Pivottabelle: https://toptorials.com/libreofficecalc/
Gruß Detlef

#5

Hallo Micha,

ich habe mal ein kleines Makro (s.u.) geschrieben, welches das von Dir skizzierte Problem löst.

Da hier in den Liste-Mails die Makro-Layouts immer "versaubeutelt" werden an "Cc: michakuehn@habmalnefrage.de" die Dateianhänge:

+ "Name_Gruppe.txt" (Makro)
+ "Name_Gruppe.ods" (CALC-Beispieltabellen)

[1] Das 'Tabellenblatt 1' kann beliebig viele 'Gruppe'-Spalten beinhalten, wobei Du die Gruppen-Namen frei wählen kannst, muss also nicht "Gruppe 1", "Gruppe 2" ... sein.

[2] Das 'Tabellenblatt 1' kann beliebig viele Zeilen enthalten.

[3] Die Tabellenblätter kannst Du auch umbenennen, da die Tabellenblätter über Indizes angesprochen werden, müssen also nicht "Tabellenblatt 1" und Tabellenblatt 2" heißen.

[4] Der Wahrheitswert sollte von der Art 'Zeichen' sein. Wenn Du beispielsweise anstatt "x" als Wahrheitswert "ja" haben möchtest, musst Du im Makro folgende Zeile anpassen: Const WD = "x" => Const WD = "ja"

[5] Das SORTIEREN habe ich mir im Makro gespart, sind manuell eh nur wenige MausKlicks:

+ TB2 markieren Spalte "B"
+ [Daten]->[Sortieren]->[Erweiterte Auswahl]->[OK]

Und falls Dein Interesse an Makro-Programmierung "geweckt" worden sein sollte, hier findest Du Informationen:

StarBasic/OpenOffice.org Basic FAQ (einfache Beispiele zum direkten Ausprobieren)
http://www.dannenhoefer.de/faqstarbasic/index.html

BASIC-Makros für OpenOffice und LibreOffice (umfassendes "Standardwerk", manchmal nicht ganz leicht verständlich, inklusive Beispiele)
www.uni-due.de/~abi070/count.php?id=OOME_3_0_deutsch.odt
www.uni-due.de/~abi070/count.php?id=OOME_3_0_deutsch.pdf

Makro Grundlagen Band 1
https://www.tintal.de/index.php/buecher/fbuecher/31746-makro-grundlagen-band-1
Makro Grundlagen Band 2
https://www.tintal.de/index.php/buecher/fbuecher/31747-makro-grundlagen-band-2
Makro Kochbuch
https://www.tintal.de/index.php/buecher/fbuecher/31737-makro-kochbuch

Wenn noch Fragen, lass einfach hören ..

Viele Grüße
Hans-Werner :-))

==================== MAKRO ====================

   Option Explicit

   Sub Name_Gruppe

      Dim aGN() As String ' arr Gruppen Namen
      Dim iTB As Integer ' idx Tabellen Blatt
      Dim oCur As Object ' obj Cursor
      Dim oCD As Object ' obj CALC Dokument
      Dim oTB1 As Object ' obj Tabellen Blatt 1
      Dim oTB2 As Object ' obj Tabellen Blatt 2
      Dim oZ As Object ' obj Zelle
      Dim N As String ' Name
      Dim W As String ' Wahrheitswert
      Dim XaktTB1 As Long ' X aktuell Tabellen Blatt 1
      Dim XaktTB2 As Long ' X aktuell Tabellen Blatt 2
      Dim XmaxTB1 As Long ' X maximal Tabellen Blatt 1
      Dim XmaxTB2 As Long ' X maximal Tabellen Blatt 2
      Dim YaktTB1 As Long ' Y aktuell Tabellen Blatt 1
      Dim YaktTB2 As Long ' Y aktuell Tabellen Blatt 2
      Dim YmaxTB1 As Long ' Y maximal Tabellen Blatt 1
      Dim YmaxTB2 As Long ' Y maximal Tabellen Blatt 2

      Const WD = "x" ' Wahrheitswert Definition

      oCD = ThisComponent ' initialisieren Object CALC Dokument
      oTB1 = oCD.Sheets(0) ' initialisieren Tabellenblatt 1
      oTB2 = oCD.Sheets(1) ' initialisieren Tabellenblatt 2

      oCur = oTB1.createCursor ' TB1 Cursor initialisieren
      oCur.GotoEndOfUsedArea(False) ' TB1 genutzer Bereich
      XmaxTB1 = oCur.getRangeAddress().endColumn ' TB1 letzte Spalte
      YmaxTB1 = oCur.getRangeAddress().endRow ' TB1 letzte Zeile

      For XaktTB1=1 To XmaxTB1 Step 1 ' TB1 über Spalten "B" bis ...
         oZ = oTB1.getCellByPosition(XaktTB1,0) ' TB1 Zelle initialisieren
         ReDim Preserve aGN(XaktTB1-1) ' Array-Element initialisieren
         aGN(XaktTB1-1) = oZ.STRING ' Zellinhalt in Array-Element
      Next XaktTB1

      oCur = oTB2.createCursor
      oCur.GotoEndOfUsedArea(False) ' TB2 Cursor initialisieren
      XmaxTB2 = oCur.getRangeAddress().endColumn ' TB2 genutzer Bereich
      YmaxTB2 = oCur.getRangeAddress().endRow ' TB2 letzte Zeile

      oTB2.clearContents(com.sun.star.sheet.CellFlags.STRING) ' TB2 alle Inhalte löschen
      oZ = oTB2.getCellByPosition(0,0) ' "A1" initialisieren
      oZ.STRING = "Name" ' "Name" => "A1
      oZ = oTB2.getCellByPosition(1,0) ' "B1' initialisieren
      oZ.STRING = "Gruppe" ' "Gruppe" => "B2"

      YaktTB2 = 0 ' TB2 Y initialisieren
      For YaktTB1=1 To YmaxTB1 Step 1 ' über TB1 Y
         For XaktTB1=1 To XmaxTB1 Step 1 ' über TB1 X
            oZ = oTB1.getCellByPosition(0,YaktTB1) ' TB1 'Name'-Zelle initialisieren
            N = oZ.STRING ' 'Name' merken
            oZ = oTB1.getCellByPosition(XaktTB1,YaktTB1) ' TB1 'Wahrheitswert'-Zelle initialisieren
            W = oZ.STRING ' 'Wahrheitswert' merken
            If (W = WD) Then ' 'Wahrheitswert' ist "x"
               YaktTB2 = YaktTB2+1 ' TB2 nächste Zeile initialisieren
               oZ = oTB2.getCellByPosition(0,YaktTB2) ' TB2 'Name'-Zelle initialisieren
               oZ.STRING = N ' 'Name' => Zelle
               oZ = oTB2.getCellByPosition(1,YaktTB2) ' TB2 'Gruppe'-Zelle initialisieren
               oZ.STRING = aGN(XaktTB1-1) ' TB2 Array-'Gruppe' > Zelle
            EndIf
         Next XaktTB1
      Next YaktTB1

      For iTB=0 To oCD.Sheets.count-1 ' über TBs
         oCD.Sheets(iTB).getColumns().optimalWidth = True ' optimale Spaltenbreite
         oCD.Sheets(iTB).getRows().optimalHeight = True ' optimale Zeilenhöhe
      Next iTB

   End Sub

------ Originalnachricht ------

Name_Gruppe.txt (3.86 KB)

#6

Herzlichen Dank an euch beide, die ihr geantwortet habt.

Mein "Problem" war eher von der Sorte: Das wäre mal eine schöne
Anwendung zum Umsetzen, falls ich mal Zeit habe.
Ein Teil davon ist das Umschreiben der Tabelle.

Insgesamt erscheint mir dies jetzt aber doch als zu groß, sodass ich es
links liegen lasse. Weder Pivot oder Makros passen in meinen begrenzten
Zeitplan.

Ich hatte auf einfache, schnel nachvollziehbare Befehle über normale
Funktionen gedacht...

Danke euch trotzdem sehr!

Micha

#7

Hallo Micha,
hallo Liste

Ich habe auch eine Tabelle mit Funktion gebastelt. Ich bin zwar nicht der Erste mit einer Lösung, ich wollte meine Arbeit aber auch nicht für die Katz gemacht haben.
In der Beispieltabelle die du unter dem folgenden Link aus meiner Dropbox herunterladen kannst, habe ich eine Reihe von Befehlen so aufgebaut, das du automatisch deine gewünschte Liste zusammengebaut bekommst.
https://www.dropbox.com/s/manciw4bgczipxe/Calc-Frage.ods?dl=0

Einen Haken hat die ganze Sache. Die Ein-Zellen-Lösung funktioniert nur unter LibreOffice 7.0 und neuer. Erst dort ist ein Bug mit der Aggregat Funktion behoben worden. Diese Funktion habe ich genutzt. Ohne habe ich es noch nicht hinbekommen.

Da ich in der Tabelle eine Reihe von Hilfsspalten stehen gelassen habe (die du jederzeit löschen kannst), kannst du leicht nachvollziehen wie ich die Formel aufgebaut habe. In den Spalten T und V findest du dann auch dein Ergebnis.

Am Ende sind zwei Formeln in der Spalte H und I daraus geworden, die du so oft nach unten weiter kopieren kannst wie du willst.
H2=WENNFEHLER(INDEX(A:A;REST(AGGREGAT(15;6;WENNFEHLER(WENN(INDEX($B$2:$F$17;ZEILE(A$1:A$80)-(GANZZAHL((ZEILE(A$1:A$80)-1)/16)*16);GANZZAHL((ZEILE(A$1:A$80)-1)/16)+1)*ZEILE(A$2:A$81)=0;"";INDEX($B$2:$F$17;ZEILE(A$1:A$80)-(GANZZAHL((ZEILE(A$1:A$80)-1)/16)*16);GANZZAHL((ZEILE(A$1:$A$80)-1)/16)+1)*ZEILE(A$2:A$81));"");ZEILE()-1);16));"")

I2=WENNFEHLER(INDEX($1:$1;1;GANZZAHL(AGGREGAT(15;6;WENNFEHLER(WENN(INDEX($B$2:$F$17;ZEILE(A$1:A$80)-(GANZZAHL((ZEILE(A$1:A$80)-1)/16)*16);GANZZAHL((ZEILE(A$1:A$80)-1)/16)+1)*ZEILE(A$2:A$81)=0;"";INDEX($B$2:$F$17;ZEILE(A$1:A$80)-(GANZZAHL((ZEILE(A$1:A$80)-1)/16)*16);GANZZAHL((ZEILE(A$1:$A$80)-1)/16)+1)*ZEILE(A$2:A$81));"");ZEILE()-1)/16)+2);"")

Folgende Anpassungen musst du ggf. vornehmen:
$B$2:$F$17 = Der Bereich in dem deine Daten stehen. Ohne Spalten und Zeilenköpfe
ZEILE(A$1:A$80) = Anzahl der Zellen in deinem Bereich. Also in meinem Beispiel  16 x 5 = 80 Also bitte die 80 anpassen.
ZEILE(A$2:A$81) = Zeile in der die Formel beginnt. In meinem Fall Zeile 2 und die 80 Zeilen drauf. Vorsicht ist nicht 82 da die erste und die letzte Zeile mitgezählt werden.
Überall wo eine 16 steht, musst du die Anzahl der Zeilen die mit Daten gefüllt sind eingeben.

In der Formel wird der x.te Eintrag aus Spalte bzw. Zeile 1 errechnet in dem Calc ein Wahr findet. Damit das mit mehreren Spalten funktioniert, habe ich diese virtuell untereinander aufgereiht und dann nach den WAHR Einträgen gesucht. Grob zusammengefasst war es das schon. Noch ein bisschen Wennfehler und schon fertig.

Wer Lust verspürt die Formel zu verbessern und sie auch für LO 6 tauglich zu machen, der ist herzlich eingeladen.

Nochmal! Unter LO 6 oder älter kommt in der Spalte H und I nichts vernünftiges raus. Nicht meckern, das es unter diesen Versionen nicht funktioniert.

Für Rückfragen bitte einfach nochmals melden.

mfg

Jürgen

#8

Noch ein kleiner Nachtrag:

Wenn weiterhin die "x" in den Zellen als Wahrheitseintrag verwendet werden sollen, müssen die Formeln geringfügig verändert werden:

H2=WENNFEHLER(INDEX(A:A;REST(AGGREGAT(15;6;WENNFEHLER(WENN((INDEX($B$2:$F$17;ZEILE(A$1:A$80)-(GANZZAHL((ZEILE(A$1:A$80)-1)/16)*16);GANZZAHL((ZEILE(A$1:A$80)-1)/16)+1)="x")*ZEILE(A$2:A$81)=0;"";(INDEX($B$2:$F$17;ZEILE(A$1:A$80)-(GANZZAHL((ZEILE(A$1:A$80)-1)/16)*16);GANZZAHL((ZEILE(A$1:$A$80)-1)/16)+1)="x")*ZEILE(A$2:A$81));"");ZEILE()-1);16));"")

I2=WENNFEHLER(INDEX($1:$1;1;GANZZAHL(AGGREGAT(15;6;WENNFEHLER(WENN((INDEX($B$2:$F$17;ZEILE(A$1:A$80)-(GANZZAHL((ZEILE(A$1:A$80)-1)/16)*16);GANZZAHL((ZEILE(A$1:A$80)-1)/16)+1)="x")*ZEILE(A$2:A$81)=0;"";(INDEX($B$2:$F$17;ZEILE(A$1:A$80)-(GANZZAHL((ZEILE(A$1:A$80)-1)/16)*16);GANZZAHL((ZEILE(A$1:$A$80)-1)/16)+1)="x")*ZEILE(A$2:A$81));"");ZEILE()-1)/16)+2);"")

Gute Nacht!