Seite 1 von 1
Gruppen-Summen bilden
Verfasst: Sa, 06.02.2016 09:29
von Ludwig77
Hallo,
ich habe ein für Fortgeschrittene wohl einfaches Problem, bei welchem ich aber nicht weiterkomme, zumindest nicht so schnell, wie es nötig ist.
Für Hilfe wäre ich sehr dankbar. Also:
Es gibt eine Input-Tabelle T-in mit zB 6 Spalten A B C X Y Z (sortiert nach A/B/C ).
Nun möchte ich 2 Arten von GruppenSummen berechnen, zum einen zB Summen von X für alle Zeilen mit jeweils einem bestimmten Wert von A
und zum anderen zB Summen von X für alle Zeilen mit jeweils einer bestimmten Kombination der Werte von A, B, C .
Hab's mit Pivot-Tabelle versucht, aber noch ohne Erfolg.
Weil ich später noch kompliziertere Sachen machen muss, für die ich wahrscheinlich Base brauche, ist noch die Frage, ob ich das nicht gleich mit Base machen sollte ?
Danke + Grüße
Ludwig
Re: Gruppen-Summen bilden
Verfasst: Sa, 06.02.2016 11:33
von Karolus
Hallo
Im Prinzip ist es eine Sache von wenigen Mausklicks|Sekunden eine Aufgabenstellung nach deiner Beschreibung mit einer Pivottabelle zu lösen.
Hab's mit Pivot-Tabelle versucht, aber noch ohne Erfolg.
Das ist eine wenig hilfreiche Problembeschreibung....
Re: Gruppen-Summen bilden
Verfasst: So, 07.02.2016 08:47
von Ludwig77
Hallo Karolus,
dankesehr für die erfreuliche Nachricht, dass es mit Pivot-Tabelle geht, und auch noch schnell - wenn man weiß wie.
Ich habe inzwischen gelesen und probiert, und habe auch ein Ergebnis, was inhaltlich hinkommt, allerdings mit dem Format/Struktur der Ausgabe habe ich noch ein Problem.
Was ich mit Calc zustande brachte siehe
- oo_calc.JPG (128.09 KiB) 5005 mal betrachtet
, also 2 Pivot-Tabellen, das Fenster "Pivot-Tabelle" gehört zur oberen.
Ein Versuch mit Excel, brachte ein Ergebnis, was vom Layout für mich geeigneter ist:
- excel.JPG (42.46 KiB) 5005 mal betrachtet
.
Was ich wirklich bräuchte, habe ich dann daraus durch kopieren und händische Ergänzung erzeugt:
- optimal.JPG (40.04 KiB) 5005 mal betrachtet
Meine Frage ist nun, wie kann ich OO Calc dazu bringen, sowas wie mein "Wunschformat" zu erzeugen ?
Vor allem: dass in einer Tabelle nicht nur die Summen für Gruppen von gleichen Werten von A B C angezeigt werden (z.B. Hose Leinen Blau), sondern auch Summen für A B ( Hose Leinen , alle Farben ) oder Summen für A ( zB alle Hosen), also so wie es im Excel-Beispiel ist.
Grüße
Ludwig
Re: Gruppen-Summen bilden
Verfasst: So, 07.02.2016 10:02
von Axel Richter
Hallo Ludwig,
zumindest die zu Excel äquivalente Ansicht kannst Du bekommen.
1. In der Layout-Einstellung (Fenster Pivot-Tabelle) klicke auf das Zeilen-Feld [ A ] und dann auf [Optionen]. Stelle dort ein: Teilergebnisse - automatisch.
2. In der Pivot-Tabelle im Tabellenblatt ziehe mit gedrückter linker Maustaste das Feld [Daten] nach rechts in das leere Feld daneben.
viele Grüße
Axel
Re: Gruppen-Summen bilden
Verfasst: So, 07.02.2016 10:51
von Karolus
Hallo
"Packe[1]" ( in der grössereren Datenpilotausgabe deines Screenshots ) das [Daten] -feld ziehe nach rechts in die Zelle daneben...
[1] heisst hier: mit der linken Maustaste anklicken und Maustaste gedrückt lassen.
Anmerkung zu @Axel Richter @1.) In Libreoffice ~5 sind diese Optionen erreichbar per Doppelklick auf das jeweilige Feld
Re: Gruppen-Summen bilden
Verfasst: So, 07.02.2016 11:38
von Stephan
Was ich wirklich bräuchte, habe ich dann daraus durch kopieren und händische Ergänzung erzeugt:
Das wird mit Pivottabelle nicht gehen, weil eine Pivottabelle immer versuchen wird gleiche Begriffe zusammenzufassen, was aber den gewünschten Wiederholungen in Spalte A und B widerspricht.
Dein Versuch in Excel scheint mir genau das zu sein was eine Pivottabelle liefern kann im Sinne des Optimums unter Berücksichtigung der Anforderungen.
Meine Frage ist nun, wie kann ich OO Calc dazu bringen, sowas wie mein "Wunschformat" zu erzeugen ?
meines Erachtens geht das nur mit Makro.
beispielsweise (nicht optimal programmiert aber funktioniert zunächst):
Code: Alles auswählen
Sub Main
tcs = ThisComponent.CurrentController.ActiveSheet
i = 1
j = 1 'obere Zeile/Index für Summe
Do
i = i + 1
IF tcs.getCellByPosition(0,i).String <> tcs.getCellByPosition(0,i-1).String AND tcs.getCellByPosition(0,i).String <> "" Then
tcs.Rows.InsertByIndex(i, 1)
tcs.getCellByPosition(0,i).String = tcs.getCellByPosition(0, i-1).String & " Ergebnis"
tcs.getCellByPosition(0,i).HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
tcs.getCellByPosition(3,i).Formula = "=SUM(D" & j+1 & ":D" & i & ")/2"
tcs.getCellByPosition(4,i).Formula = "=SUM(E" & j+1 & ":E" & i & ")/2"
i = i + 1
j = i
ElseIf tcs.getCellByPosition(0,i).String = "" AND tcs.getCellByPosition(0,i+1).String = "" Then
tcs.getCellByPosition(0,i).String = tcs.getCellByPosition(0, i-1).String & " Ergebnis"
tcs.getCellByPosition(0,i).HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
tcs.getCellByPosition(3,i).Formula = "=SUM(D" & j+1 & ":D" & i & ")/2"
tcs.getCellByPosition(4,i).Formula = "=SUM(E" & j+1 & ":E" & i & ")/2"
Exit Do
End If
Loop
i = 1
j = 1
hg = 0
Do
i = i + 1
IF tcs.getCellByPosition(1,i).String <> tcs.getCellByPosition(1,i-1).String AND tcs.getCellByPosition(1,i-1).String <> "" Then
If tcs.getCellByPosition(1,i).String = "" Then 'Übergang Hauptgruppe
hg = 1
ElseIf tcs.getCellByPosition(1,i).String <> "" Then
hg = 0
End If
tcs.Rows.InsertByIndex(i, 1)
tcs.getCellByPosition(1,i).String = tcs.getCellByPosition(1, i-1).String & " Ergebnis"
tcs.getCellByPosition(1,i).HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
tcs.getCellByPosition(0,i).String = tcs.getCellByPosition(0,i-1).String
tcs.getCellByPosition(3,i).Formula = "=SUM(D" & j+1 & ":D" & i & ")"
tcs.getCellByPosition(4,i).Formula = "=SUM(E" & j+1 & ":E" & i & ")"
i = i + 1
j = i
If hg = 1 Then
tmp = tcs.getCellByPosition(3,i).Formula
tmp2 = Split(tmp, ":")
laenge = LEN(tmp2(1))
tmp3 = Left(tmp2(1), laenge-1) '")" weg
tmp3 = Right(tmp3, laenge-2) '"D" weg
tmp3 = VAL(tmp3) + 1
tcs.getCellByPosition(3,i).Formula = tmp2(0) & ":D" & tmp3 & ")/2"
tmp = tcs.getCellByPosition(4,i).Formula
tmp2 = Split(tmp, ":")
laenge = LEN(tmp2(1))
tmp3 = Left(tmp2(1), laenge-1) '")" weg
tmp3 = Right(tmp3, laenge-2) '"D" weg
tmp3 = VAL(tmp3) + 1
tcs.getCellByPosition(4,i).Formula = tmp2(0) & ":E" & tmp3 & ")/2"
hg = 0
j = j + 1
End If
ElseIf tcs.getCellByPosition(1,i).String = "" AND tcs.getCellByPosition(1,i+1).String = "" Then
Exit Do
End If
Loop
End Sub
Gruß
Stephan
Re: Gruppen-Summen bilden
Verfasst: So, 07.02.2016 12:07
von Karolus
Hallo
@Stephan: Es
geht per Datenpilot. ( auch auf Daten die
nicht vorsortiert sind
Karolus
Re: Gruppen-Summen bilden
Verfasst: So, 07.02.2016 12:31
von Stephan
Ich bin gerade erst dabei LO 5 zum Testen runterzuladen, aber ich mag nicht glauben das es dort geht.
mmh...
Deine angehängte Beispieldatei zeigt in LO 4.3 keine Darstellung so wie sie der Frager will, denn der Frager will das z.B. "hose" in JEDER ZEile steht (siehe oben Bild "optimal.jpg") und nicht das es zusammen für mehrere Zellen einmal dasteht wie in Bild "exel.jpg" und auch in Deiner Beispieldatei (wenn ich sie in LO 4.3 öffne).
Dieses quasi allgemeine Verhalten einer Pivottabelle hatte ich beschrieben mit:
"Das wird mit Pivottabelle nicht gehen, weil eine Pivottabelle immer versuchen wird gleiche Begriffe zusammenzufassen, was aber den gewünschten Wiederholungen in Spalte A und B widerspricht."
Ob hingegen die Darstellung die der Frager sinnvoll ist will ich nicht diskutieren (mir persönlich scheint sie nicht sinnvoll), sondern meine Absicht war nur darauf gerichtet das zu erzielen was der Frage als die für ihn optimale Darstellung sieht.
Gruß
Stephan
Re: Gruppen-Summen bilden
Verfasst: So, 07.02.2016 12:44
von Stephan
ich habe es jetzt in LO 5.0.4 portable ausprobiert und die Darstellung ist auch dort wie in LO 4.3, nämlich:
- ist.gif (27.69 KiB) 4935 mal betrachtet
der Frager will aber meines Erachtens optimaler Weise diese Art der Darstellung:
- soll.gif (29.67 KiB) 4935 mal betrachtet
nochmals: ich halte es garnicht für sinnvoll die Art der Darstellung zu nutzen die der Frager will, ich habe mich nur bemüht die Darstellung zu erreichen die er wollte.
Gruß
Stephan
Re: Gruppen-Summen bilden
Verfasst: So, 07.02.2016 13:09
von Ludwig77
Hallo,
erstmal vielen Dank an alle !!!
Das Hauptziel habe ich durch die Hinweise erstmal erreicht:
- OO calc ok.JPG (33.7 KiB) 4918 mal betrachtet
Das mit dem "optimalen Wunschformat" ( x- mal "Hose" etc wiederholt untereinander ) muss ich noch versuchen.
Ob ich das wirklich brauche, hängt von der noch im Detail zu klärenden Weiterverarbeitung ab.
Zum anschauen ist die Variante oben (ohne Wiederholung) natürlich übersichtlicher.
Grüße
Ludwig
Re: Gruppen-Summen bilden
Verfasst: So, 07.02.2016 18:21
von Karolus
Hallo
Achso --- auf die wiederholten Kategorie-einträge hatte ich gar nicht geachtet, aber falls das wirklich gebraucht wird:
Code: Alles auswählen
#python
def fill_emtpties():
doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
sheet = sel.Spreadsheet
empties = sel.queryEmptyCells()
for i in range(empties.Count):
loc = empties.getByIndex(i)
if loc.CellStyle != 'Pivot-Tabelle Kategorie':
continue
cursor = sheet.createCursorByRange(loc)
cursor.gotoOffset(0,-1) # 1 nach oben
cursor.collapseToSize(1,cursor.Rows.Count+1)
cursor.fillSeries(0,0,0,0,0)
und weil das eh alles Api-methoden sind siehts in Basic nahezu genauso aus:
Code: Alles auswählen
'basic
sub fill_emtpties()
doc = ThisComponent
sel = doc.CurrentSelection
sheet = sel.Spreadsheet
empties = sel.queryEmptyCells()
for i = 0 to empties.Count-1
em = empties.getByIndex(i)
if em.CellStyle = "Pivot-Tabelle Kategorie" then
cursor = sheet.createCursorByRange(em)
cursor.gotoOffset(0,-1) '# 1 nach oben
cursor.collapseToSize(1,cursor.Rows.Count+1)
cursor.fillSeries(0,0,0,0,0)
end if
next i
end sub