Gruppen-Summen bilden

Das Tabellenkalkulationsprogramm

Moderator: Moderatoren

Ludwig77
Beiträge: 9
Registriert: Sa, 06.02.2016 07:28

Gruppen-Summen bilden

Beitrag 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
Karolus
********
Beiträge: 7431
Registriert: Mo, 02.01.2006 19:48

Re: Gruppen-Summen bilden

Beitrag 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....
LO7.4.7.2 debian 12(bookworm) auf Raspberry4b 8GB (64bit)
LO7.6.2.1 flatpak debian 12(bookworm) auf Raspberry4b 8GB (64bit)
Ludwig77
Beiträge: 9
Registriert: Sa, 06.02.2016 07:28

Re: Gruppen-Summen bilden

Beitrag 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
oo_calc.JPG (128.09 KiB) 3979 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
excel.JPG (42.46 KiB) 3979 mal betrachtet
.

Was ich wirklich bräuchte, habe ich dann daraus durch kopieren und händische Ergänzung erzeugt:
optimal.JPG
optimal.JPG (40.04 KiB) 3979 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
Zuletzt geändert von Ludwig77 am So, 07.02.2016 10:04, insgesamt 1-mal geändert.
Axel Richter
****
Beiträge: 159
Registriert: So, 17.10.2010 16:54

Re: Gruppen-Summen bilden

Beitrag 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
Karolus
********
Beiträge: 7431
Registriert: Mo, 02.01.2006 19:48

Re: Gruppen-Summen bilden

Beitrag 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
LO7.4.7.2 debian 12(bookworm) auf Raspberry4b 8GB (64bit)
LO7.6.2.1 flatpak debian 12(bookworm) auf Raspberry4b 8GB (64bit)
Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Re: Gruppen-Summen bilden

Beitrag 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
Dateianhänge
Gruppen-summieren.ods
(11.59 KiB) 114-mal heruntergeladen
Karolus
********
Beiträge: 7431
Registriert: Mo, 02.01.2006 19:48

Re: Gruppen-Summen bilden

Beitrag von Karolus »

Hallo

@Stephan: Es geht per Datenpilot. ( auch auf Daten die nicht vorsortiert sind
datenpilot_mit_Teilergebnissen.ods
(16.66 KiB) 108-mal heruntergeladen
Karolus
LO7.4.7.2 debian 12(bookworm) auf Raspberry4b 8GB (64bit)
LO7.6.2.1 flatpak debian 12(bookworm) auf Raspberry4b 8GB (64bit)
Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Re: Gruppen-Summen bilden

Beitrag 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
Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Re: Gruppen-Summen bilden

Beitrag 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
ist.gif (27.69 KiB) 3909 mal betrachtet
der Frager will aber meines Erachtens optimaler Weise diese Art der Darstellung:
soll.gif
soll.gif (29.67 KiB) 3909 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
Ludwig77
Beiträge: 9
Registriert: Sa, 06.02.2016 07:28

Re: Gruppen-Summen bilden

Beitrag von Ludwig77 »

Hallo,
erstmal vielen Dank an alle !!!
Das Hauptziel habe ich durch die Hinweise erstmal erreicht:
OO calc ok.JPG
OO calc ok.JPG (33.7 KiB) 3892 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
Karolus
********
Beiträge: 7431
Registriert: Mo, 02.01.2006 19:48

Re: Gruppen-Summen bilden

Beitrag 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
LO7.4.7.2 debian 12(bookworm) auf Raspberry4b 8GB (64bit)
LO7.6.2.1 flatpak debian 12(bookworm) auf Raspberry4b 8GB (64bit)
Antworten