Gibt es eine Funktion um...

Das Tabellenkalkulationsprogramm

Moderator: Moderatoren

Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Beitrag von Stephan »

Das hier:
OH! Ganz vergessen...
die Werte liegen zwischen 0 und 15


verwirrt mich - aber ist wohl nicht so wichtig.
Im Prinzip ok...
Wenn im Prinzip OK dann könnte ich versuchen das mal zunächst mit Einzelparametern formulieren. Ich habe verstanden das:
-obere Hälfe der Werte summieren und durch Anzahl (der oberen Werte) teilen
-falls Gesamtzahl der Werte nicht gerade, dann soll die obere Gruppe um 1 größer als die untere sein
-das habe ich auch verstanden:
5. Für den Fall 1,2,4,4,5,6 (der sehr wahrscheinlich ist) würde 1,2,4 - 4,5,6 getrennt (es geht darum die max. Summe aus der Hälfte der Werte zu bekommen).


Folgendes müßte ich noch wissen:

(a)
3. Sie stehen in einer Spalte (z.B. J7 bis J48)
momentan ist das mit den Spalten egal, aber wieviele Zellen müssen maximal berücksichtigt werden? (Da ich das jetzt erstmal über Einzelparameter realisieren will sollte diese Anzahl in realistischer Größenordnung sein.)

(b)
Müssen unbedingt Nullwerte berücksichtigt werden?
Beispiel: 0,0,2,3,4,5
Bei Berücksichtigung:
Kleine Gruppe: 0,0,2 große Gruppe: 3,4,5
Bei Nichtberücksichtigung:
kleine Gruppe: 2,3 große Gruppe: 4,5

Und wichtig: Was soll als Nullwert gelten? Eine leere Zelle oder nur eine Zelle wo definitiv "0" eingetragen ist?

(c)
In welchem Bereich liegen die Zahlen in den Ausgangszellen?
Können z.B. Exponenten auftreten (1,234E+011)?


(d)
Ich bin noch nicht sicher ob das hier Bedeutung haben wird, aber:
Sind die Zahlen in den Ausgangszellen immer als Zahlen formatiert, oder können dort auch Zahlen stehen, welche Strings sind?
Muß das Ergebnis immer eine als Zahl formatierte Zahl sein oder kann es auch eine als String formatierte Zahl sein?
Können negative Zahlen auftreten und sollen diese normal behandelt werden?

@Niels
Steht denn hinter der Zahlenreihe noch etwas in der nächsten Zelle der Zeile? Ansonsten könnte man doch die Zeile bis zum Ende des Blattes auf Ändrungen überwachen und braucht als Parameterübergabe nur die Startzelle. Dann liest man, bis man auf die 1. leere Zelle kommt und rechnet dann los.

@Stephan: Oder habe ich da etwas falsch verstanden mit der Funktion Deiner Funktion?
Im Prinzip völlig richtig.
Ich weiß nur bisher nicht ob die Bedingungen bei Lars auch so gegeben sind. Hinzu kommt auch die Sache mit der Null, dazu steht weiter oben etwas.

Gruß
Stephan
el Larso
*
Beiträge: 10
Registriert: Mi, 28.07.2004 20:55

Beitrag von el Larso »

Zu den Fragen:
(a) Es handelt sich momentan um 36 Werte, welche in den Zellen J7 bis J48 in vier Gruppen liegen. Gruppe eins umfasst 12 Werte (J7 - J18) Gruppe zwei 10, G. drei 8, G. vier 6. Zwischen den Gruppen befinden sich jeweils 2 leere Zellen (welche auch nicht berücksichtigt werden).

(b) Es kommen auch nullen vor, welche jedoch nicht berücksichtigt werden.

(c) Es gibt keine negativen Werte und 'schlimmsten Falls' eine '2,5'

(d) Leider weiß ich nicht wirklich welchen Unterschied das macht...

Gruß Lars
Don't Panic
Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Beitrag von Stephan »

Für maximal 36 Werte kannst Du folgende Funktion nutzen (zugegeben noch nicht sehr elegant):

Code: Alles auswählen

Function GRUTEI (W0,W1,W2,W3,W4,W5,W6,W7,W8,W9,W10,W11,W12,W13,W14, _
					W15,W16,W17,W18,W19,W20,W21,W22,W23,W24,W25,W26,W27, _
					W28,W29,W30,W31,W32,W33,W34,W35)

'==================================
'Funktion GRUppieren-TEIlen
'"V1"    Datum: 01.08.2004
'==================================
Dim W(35)
'Umwandeln 
'(Warum kann ich kein Variablenfeld
'als Parameter verwenden?)
W(0) = W0
W(1) = W1
W(2) = W2
W(3) = W3
W(4) = W4
W(5) = W5
W(6) = W6
W(7) = W7
W(8) = W8
W(9) = W9
W(10) = W10
W(11) = W11
W(12) = W12
W(13) = W13
W(14) = W14
W(15) = W15
W(16) = W16
W(17) = W17
W(18) = W18
W(19) = W19
W(20) = W20
W(21) = W21
W(22) = W22
W(23) = W23
W(24) = W24
W(25) = W25
W(26) = W26
W(27) = W27
W(28) = W28
W(29) = W29
W(30) = W30
W(31) = W31
W(32) = W32
W(33) = W33
W(34) = W34
W(35) = W35

For i = 0 to 34
	For j = i to 35
	If W(i) > W(j) Then
		tmp = W(i)
		W(i) = W(j)
		W(j) = tmp
	end if
	Next j
Next i
Dim X(0) As Single
n = 0
For i = 0 to 35
	if W(i)<>0 Then
		X(n) = W(i)
		n = n + 1
		ReDim Preserve X(n) As Single
	End If
Next i
If INT((n - 1) / 2) = (n - 1) / 2 Then
		gesamt = (n - 1) / 2
	Else
		gesamt = n / 2
End If
wert = 0
For i = gesamt to (n - 1)
	wert = wert + X(i) 
next   
GRUTEI = wert/(n - gesamt)
end function
Kopiere diesen Code in ein Modul der Bibliothek "Standard" von OpenOffice/Staroffice.

Wenn die 36 Werte in Spalte J stehen mußt Du im Tabellenblatt die Funktion aufrufen mit:

=GRUTEI (J1;J2;J3;J4;J5;J6;J7;J8;J9;J10;J11;J12;J13;J14;J15;J16;J17;J18;J19;J20;J21;J22;J23;J24;J25;J26;J27;J28;J29;J30;J31;J32;J33;J34;J35;J36)

Die Zellen können aber völlig beliebig angegeben sein (auch völlig beliebige Spalten oder Reihenfolge). Wichtig ist das immer 36 Zellen übergeben werden. Falls Du nicht alle brauchst schreibe einfach irgendwelche Zellen rein, die garantiert "0" oder leer sind.

Du müßtest das jetzt mal etwas testen, sollte aber bei Dir ein Fehler auftreten kann es nur noch eine Kleinigkeit sein.

(d) Leider weiß ich nicht wirklich welchen Unterschied das macht...
Das ist ganz einfach wichtig, weil definiertst Du die Variablen als String:

Dim a as String
Dim b as String
und übergibst Werte:
a = "3" (oder a = 3)
b = "2" (oder b = 2)
und rechnest dann z.B.:
Z = a + b
Z = 32 (!)
(die Werte werden "aneinander gehängt")

Sind die Variablen aber als Zahl definiert:
Dim c as integer
Dim d as integer
a = "3" (oder a = 3)
b = "2" (oder b = 2)
Dann ist:
Z = a + b
Z = 5 (!)
(die Werte werden addiert)

Das Gleiche würde geschehen wenn die Werte für a un b aus Tabellenzellen entnommen werden und dort als Zahlen oder Strings formatiert sind.


Gruß
Stephan
Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Beitrag von Stephan »

So mein Vorschlag für die Funktion ist fertig:

Code: Alles auswählen

Function GRUTEI (dummy, zellen as string, blattname as String)

'==================================
'Funktion GRUppieren-TEIlen
'"V2"    Datum: 02.08.2004
'==================================
Dim W(0) as Single '(!)
Dim b '(!)
Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object
Dim X(0) As Single
'StarDesktop.CurrentComponent (?)
Doc = ThisComponent 
'Tabellenblatt festlegen:
'entweder so:
Sheet = Doc.Sheets.getByName(blattname)
'
'oder so:
'Sheet = Doc.Sheets.getByIndex(0)
b = zellen
b = Split(b, ";")
d = 0
for i = LBound(b) to UBound(b)
	CellRange = Sheet.getCellRangeByName(b(i))
	Row = CellRange.Rows
	Column = CellRange.Columns
	For nc= 0 To Column.getCount-1
		For nr = 0 To Row.getCount-1
			W(d) = CellRange.getCellByPosition (nc,nr).Value
			d = d + 1
			ReDim Preserve W(d) As Single
		Next nr
	Next nc
Next i

For i = 0 to d-2
	For j = i to d-1
	If W(i) > W(j) Then
		tmp = W(i)
		W(i) = W(j)
		W(j) = tmp
	end if
	Next j
Next i

n = 0
For i = 0 to d-1
	if W(i)<>0 Then
		X(n) = W(i)
		n = n + 1
		ReDim Preserve X(n) As Single
	End If
Next i
If INT((n - 1) / 2) = (n - 1) / 2 Then
		gesamt = (n - 1) / 2
	Else
		gesamt = n / 2
End If
wert = 0
For i = gesamt to (n - 1)
	wert = wert + X(i) 
next
   
GRUTEI = wert/(n - gesamt)
end function
Kopiere diesen Code in ein Modul der Calc-Datei.

Die Funktion muß in einer Zelle des Tabellenblattes wie folgt eingetragen werden :

=GRUTEI(a;b;c)

wobei a
ein beliebig großer Zellbereich ist welcher die Zellen beinhaltet deren Werte verarbeitet werden sollen. Der Zellbereich kann beliebig viele andere Zellen (mit beliebigen Inhalten) umfassen, nur die Zelle in welcher die Funktion (GRUTEI) eingetragen ist darf keine Zelle des Zellbereichs sein - logisch. Dieser Zellbereich dient nur dazu die automatische Aktualisierung der Funktion sicherzustellen, da alle Veränderungen des Zellbereichs überwacht werden. Einträge für a könnten sein:
A1:A100
B2:C30
jedoch nicht:
A1:A20;A30:A100
also immer nur ein geschlossener Bereich gemäß:
<obere (linke) Zelle>:<untere (rechte) Zelle
Falls für a auch getrennte Zellen/Zellbereiche nötig wären ließe sich das noch ändern.

Für b
müssen die Zellen die ausgewertet werden sollen als Zellbereiche oder Zellen oder Beides gemischt eingetragen werden jedoch als String. Beispiel für b:
"A1:A3"
"A1:A6;B2;C3:C7;D4"
also allgemein:
"<Zelle/Zellbereich>;<Zelle/Zellbereich>;...;<Zelle/Zellbereich>"
Anzahl der Zellen und Zellbereiche egal, Reihenfolge auch egal, jedoch dazwischen immer ";" als Trenner und keine Leerzeichen.

c ist
der Name des Tabellenblattes auf dem die Funktion eingetragen wird, z.B.:
"Tabelle1"

Ein Gesamtbeispiel wäre:

=GRUTEI(A1:C10;"A1:B6";"Tabelle1")

-->die Werte der Zellen A1-B6 werden zur Berechnung herangezogen, die Zellen im Bereich A1:C10 werden 'überwacht', (die Formel darf nicht im Bereich A1:C10 eingetragen sein)


Gruß
Stephan
el Larso
*
Beiträge: 10
Registriert: Mi, 28.07.2004 20:55

el Larso

Beitrag von el Larso »

Auf den ersten Blick sieht das ja Prima aus...
Hab leider erst heute Abend Zeit das ganze auszuprobieren...
Freue mich aber schon, besonders auf V2 der Funktion!
Don't Panic
el Larso
*
Beiträge: 10
Registriert: Mi, 28.07.2004 20:55

JA & NEIN

Beitrag von el Larso »

Die Funktion läuft wirklich toll!

Allerdings und ich hoffe ich straperziere eure Nerven nicht zu sehr, haben
sich die Umstände etwas geändert...

Ich versuche den Sachverhalt kurz zu erklären:
Zu jeder der Zahlen mit denen wir bis jetzt gearbeitet haben gibt es einen zweiten Wert (dieser steht in der gleichen Zeile eine Spalte weiter rechts), die "Menge".
Nachdem die "alten" Werte gruppiert sind, müssen die einzelnen Positionen mit ihrer Menge multipliziert werden, um anschließend das Ergebnis durch die Summe der Multiplikatoren (MengeWerte) zu teilen.
Bis jetzt habe ich die Multiplikation mit der Menge über eine einfache Formel erledigt und danach GruTei auf die Spalte mit den Ergebnissen losgelassen.
Allerdings führte dies natürlich dazu, dass "die Menge ihrer selbst" nicht mehr der tatsächlichen Menge entsprach.

Ich hoffe ich konnte das Problem verdeutlichen...!?
Falls nun niemand mehr den Enthusiasmus aufbringt mir ein weiteres mal bei meiner Unkenntnis weiter zu helfen, kann ich das gut verstehen...
Falls doch noch eine Lösung in greifbare Nähe rücken sollte, würde ich mich überaus freuen!!!

Gruß Lars
Don't Panic
Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Beitrag von Stephan »

Allerdings und ich hoffe ich straperziere eure Nerven nicht zu sehr,
nö, schon garnicht wenn:
Die Funktion läuft wirklich toll!
Aber die Erklärung kann ich nicht verstehen:
Zu jeder der Zahlen mit denen wir bis jetzt gearbeitet haben gibt es einen zweiten Wert (dieser steht in der gleichen Zeile eine Spalte weiter rechts), die "Menge".
das ist klar:

Code: Alles auswählen

Spalte1    Menge
   2         5
   4         6
   5         4
   6         3

   9         2
   4         3
ich nehme an das in beiden Spalten leere Zellen vorkommen können, entweder 1 Spalte leer oder beide Spalten?
Nachdem die "alten" Werte gruppiert sind, müssen die einzelnen Positionen mit ihrer Menge multipliziert werden,

Code: Alles auswählen

Spalte1    Menge
   2         5   10
   4         6   24
   5         4   20
   6         3   18

   9         2   18
   4         3   12
was heißt denn gruppiert?
um anschließend das Ergebnis durch die Summe der Multiplikatoren (MengeWerte) zu teilen.
Summe = 5+6+4+3+2+3
aber soll jetzt in jeder Zeile (Wert Spalte 3)/Summe oder (Summe der Produkte in Spalte 3)/Summe ?
Bis jetzt habe ich die Multiplikation mit der Menge über eine einfache Formel erledigt und danach GruTei auf die Spalte mit den Ergebnissen losgelassen.
Allerdings führte dies natürlich dazu, dass "die Menge ihrer selbst" nicht mehr der tatsächlichen Menge entsprach.
Das verstehe ich nicht vor allem was ist "Menge ihrer selbst"?



Stephan
el Larso
*
Beiträge: 10
Registriert: Mi, 28.07.2004 20:55

Beitrag von el Larso »

Ok, also versuche ich es noch einmal etwas verständlicher...

Die Tabelle ist folgender maßen aufgebaut (auf das wesentliche reduziert):

Name:-----Kosten:-----Anzahl:

Pos.1---------3----------- 2
Pos.2---------2------------3
Pos.3---------5------------1
Pos.4---------4------------2

a) Nun sollen die Positionen nach ihren Kosten gruppiert werden (3,2 5,4)
b) Die Pos. der größeren Hälfte mit ihrer Anzahl multiplizieren (5,8)
c) Daraus die Summe bilden (13)
d) Die Summe der Anzahl (1+2=3)
e) c durch d dividieren (13/3=4,333)
f) 4,333 ist in diesem Fall die Zahl meiner Begierde!

Bis dato habe ich die Kosten und Anzahl im vorraus multipliziert und die Funktion GRUTEI auf die Spalte mit dem Ergebnis angewendet...
Dummer Denkfehler, da im Schritt e) dann 13/2=6,5 berechnet wurde!

So, ich hoffe nun ist es verständlicher...
Danke nochmals für die stätige Hilfe!

Gruß Lars
Don't Panic
Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Beitrag von Stephan »

Hallo Lars,

Ich denke jetzt funktioniert es wie gewünscht, aber teste erst einmal. Die Parameterübergabe muß wie bei GRUTEI V2 erfolgen, mit dem Unterschied das Du für den Parameter 'zellen' nun nur noch Zellen/Zellbereiche angeben kannst die keine horizontal benachbarten Zellen haben, da die rechts liegenden Zellen automatisch bei der Berechnung verwendet werden, so wie Du es erläutert hast. Für Deine Beispieltabelle habe ich es getestet und es ergibt 4,33 (für Stellenzahl passe Formatierung der Zelle an). Wenn ich Deine Beispieltabelle verwende (Zelle A1="Name" C5=2) klappt es mit folgendem Aufruf:

=GRUTEI(B2:C20;"B2:B5";"Tabelle1")

Code: Alles auswählen

Function GRUTEI (dummy , zellen as string, blattname as String)
'==================================
'Funktion GRUppieren-TEIlen
'"V 3.1"    Datum: 04.08.2004
'==================================
Dim W(1, 0) as Single , b '(!)
Dim Sheet As Object ,  CellRange As Object
Sheet = ThisComponent.Sheets.getByName(blattname)
b = zellen : b = Split(b, ";") : d = 0
for i = LBound(b) to UBound(b)
	CellRange = Sheet.getCellRangeByName(b(i))
	For nc = 0 To CellRange.Columns.getCount - 1
		For nr = 0 To CellRange.Rows.getCount - 1
			If CellRange.getCellByPosition (nc, nr).Value <> 0 Then
				x = CellRange.getCellByPosition (nc, nr) : x1 = x.GetCellAddress()
				W(0, d) = x.Value : w1y = x1.row : w1x = x1.column
				W(1, d) = Sheet.getCellByPosition(w1x + 1, w1y).value 
				d = d + 1
				ReDim Preserve W(1, d) As Single
			End If
		Next nr
	Next nc
Next i
For i = 0 to d - 2
	For j = i to d - 1
		If W(0, i) > W(0, j) Then
			tmp = W(0, i) : tmp2 = W(1, i)
			W(0, i) = W(0, j) : W(1, i) = W(1, j)
			W(0, j) = tmp : W(1, j) = tmp2
		end if
	Next j
Next i
For i = INT((d / 2) + 0.5) to (d - 1)
	prosum = prosum + W(0, i) * W(1, i)
	anzsum = anzsum + W(1, i)
Next
GRUTEI = prosum / anzsum
end function

Gruß
Stephan

geändert 05.08.2004 01:15
el Larso
*
Beiträge: 10
Registriert: Mi, 28.07.2004 20:55

Beitrag von el Larso »

Ja das scheint zu klappen...
Wie machst Du das nur immer so schnell!?
Werde heute Abend noch etwas ausführlicher testen...

Vielen, vielen dank!
Gruß Lars
Don't Panic
el Larso
*
Beiträge: 10
Registriert: Mi, 28.07.2004 20:55

Beitrag von el Larso »

Hmm... habe da doch noch ein Problem.

Immer wenn ich meine Datei öffne kommt folgende Fehlermeldung:
Fehler beim Laden des Dokument ...xy...
BASIC-Laufzeitfehler
Eigenschaft oder Methode nicht gefunden
Im Basic Fenster wird dann folgende Zeile markiert:
Sheet = ThisComponent.Sheets.getByName (blattname)

Nach einiger Zeit anderer Arbeit im Dokument wird das Ergebnis der Funktion dann doch angezeigt...
Ich konnte das allerdings noch nicht reproduzieren, weiß also nicht woran das liegt...

10min. später...
Ich kann es jetzt doch reproduzieren:
Immer wenn ich eine für die Funktion relevante Zelle überschreibe wird ein Ergebnis (das richtige) angezeigt.

Gruß Lars
Don't Panic
Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Beitrag von Stephan »

Ich kann das so nicht nachvollziehen, ändere probehalber die Zeile:

Code: Alles auswählen

Sheet = ThisComponent.Sheets.getByName(blattname)
in

Code: Alles auswählen

Sheet = StarDesktop.CurrentComponent.Sheets.getByName(blattname)

Aber die vorhergehende Funktion hat doch funktioniert?


Stephan
el Larso
*
Beiträge: 10
Registriert: Mi, 28.07.2004 20:55

Beitrag von el Larso »

So ich habe jetzt noch mal hin und her probiert, bin allerdings kein Stück weiter gekommen...

Wenn ich die Zeile ändere, erhalte ich die Fehlermeldung:
Objektvariable nicht belegt
Hab testweise noch mal Version 2 eingefügt und die gleiche Fehlermeldung bekommen!?

Daraufhin Open Office 1.1.2 installiert, doch keine Besserung in Sicht!

Nun habe ich die Beispieltabelle von oben benutzt... gleiches Problem!

Was ist den hier los!?

Immernoch bleibt das Phänomen, dass ich sobald ich eine Zelle aktualisiere das Ergebniss angezeigt bekomme...!?

Gruß Lars
Don't Panic
Antworten