OpenOffice.info

Verfasst: **Do, 05.07.2012 15:41**

Servus miteinander!

Ich möchte die Calc-Funktion "Summenprodukt" mittels OOO-Basic-Makro für eine einfache Abfrage verwenden. Die Tabelle ist mit 3 Spalten recht simpel (siehe Anhang). Sie enthält die geleisteten Minuten pro Mitarbeiter und Monat, wobei Spalte A das Mitarbeiterkürzel (String), Spalte B die Monatsangabe (Value) und Spalte C die Minuten (Value) enthält. In Calc lässt sich die Formel z.B. wie folgt einwandfrei anwenden:

=summenprodukt(A2:A8="AEB";B2:B8=2012.06;C2:C8)

Leider schaff ich es nicht, diese Funktion korrekt in Basic aufzurufen. Das Grundgerüst sollte vermutlich wie folgt aussehen:

Code: Alles auswählen

Sub Main
	
	oS = ThisComponent.sheets(0)
	
	sMA = "AEB"
	nPer = 2012.06
	
	FuncInst = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	Matrix1 = oS.getCellRangeByPosition(0,1,0,8)
	Matrix2 = oS.getCellRangeByPosition(1,1,1,8)
	Matrix3 = oS.getCellRangeByPosition(2,1,2,8)
	lMonTotal = FuncInst.callFunction("sumproduct",array(Matrix1,Matrix2,Matrix3)) 

End Sub

Nun müsste ich für Matrix1 noch das Argument ="AEB" und bei Matrix2 das Argument =2012.06 mitgeben, aber ich komme nicht dahinter wie!

Hat vielleicht jemand eine Idee?

Vielen Dank schonmal und liebe Grüsse
Migesius

Verfasst: **Do, 05.07.2012 22:39**

Hallo,

Migesius hat geschrieben:Nun müsste ich für Matrix1 noch das Argument ="AEB" und bei Matrix2 das Argument =2012.06 mitgeben,

Hm, nein. SUMPRODUCT erwartet als Parameter Matrixen. Dass die Angabe A2:A9="AEB" in eine {TRUE;FALSE;...} Matrix umgewandelt wird, das managed CALC *vor* dem Aufruf der Funktion. Die Funktion sieht am Ende nur die Matrix.

Willst Du also die Funktion entsprechend aufrufen, musst Du das vorher managen.

Bsp1:

Code: Alles auswählen

Sub Main1
   oS = ThisComponent.sheets(0)
   
   sMA = "AEB"
   nPer = 2012.06
   
   FuncInst = createUnoService( "com.sun.star.sheet.FunctionAccess" )
   
   Matrix1 = oS.getCellRangeByPosition(0,1,0,8).DataArray
   for i = lbound(Matrix1) to ubound(Matrix1)
    zeile = Matrix1(i)
    for j = lbound(zeile) to ubound(zeile)
     zeile(j) = (zeile(j) = sMa)*-1
    next j
    Matrix1(i) = zeile
   next i
  
   Matrix2 = oS.getCellRangeByPosition(1,1,1,8).DataArray
   for i = lbound(Matrix2) to ubound(Matrix2)
    zeile = Matrix2(i)
    for j = lbound(zeile) to ubound(zeile)
     zeile(j) = (zeile(j) = nPer)*-1
    next j
    Matrix2(i) = zeile
   next i
     
   Matrix3 = oS.getCellRangeByPosition(2,1,2,8).DataArray
   
   lMonTotal = FuncInst.callFunction("sumproduct",array(Matrix1,Matrix2,Matrix3))

   msgbox lMonTotal

End Sub

oder mit einem temporären Sheet:

Code: Alles auswählen

sub Main2
   oS = ThisComponent.sheets(0)
   
   sMA = "AEB"
   nPer = 2012.06
    
   FuncInst = createUnoService( "com.sun.star.sheet.FunctionAccess" )
   
   tempSheet = ThisComponent.createInstance("com.sun.star.sheet.Spreadsheet")
   ThisComponent.Sheets.insertByName("tempSheet", tempSheet)

   Matrix1 = oS.getCellRangeByPosition(0,1,0,8).DataArray
   tempSheet.getCellRangeByPosition(0,1,0,8).DataArray = Matrix1
   tempSheet.getCellRangeByPosition(1,1,1,8).ArrayFormula = "A2:A9=""AEB"""
   Matrix1 = tempSheet.getCellRangeByPosition(1,1,1,8).DataArray
  
   Matrix2 = oS.getCellRangeByPosition(1,1,1,8).DataArray
   tempSheet.getCellRangeByPosition(1,1,1,8).DataArray = Matrix2
   tempSheet.getCellRangeByPosition(2,1,2,8).ArrayFormula = "B2:B9=2012.06"
   Matrix2 = tempSheet.getCellRangeByPosition(2,1,2,8).DataArray
     
   ThisComponent.Sheets.removeByName("tempSheet")  
     
   Matrix3 = oS.getCellRangeByPosition(2,1,2,8).DataArray
   
   lMonTotal = FuncInst.callFunction("sumproduct",array(Matrix1,Matrix2,Matrix3))

   msgbox lMonTotal
end sub

viele Grüße

Axel

Verfasst: **Do, 05.07.2012 23:16**

Hallo
@Axel
Das sind ja ganz nette Beispiele - aber eigentlich können wir dann gleich ohne SUMPRODUCT arbeiten, ist vom Aufwand das gleiche oder weniger.

Karo

Verfasst: **Fr, 06.07.2012 07:17**

Hallo Karolus,

Karolus hat geschrieben:Das sind ja ganz nette Beispiele - aber eigentlich können wir dann gleich ohne SUMPRODUCT arbeiten, ist vom Aufwand das gleiche oder weniger.

Ja, dieser Gedankengang ist naheliegend

.

Ich hatte die Hoffnung, dass jemand weiß, ob die Funktionialität, mit der CALC die ArrayFormula A2:A9="blah" in einem Literal auswertet und in eine Ergebnismatrix wandelt, irgendwie per API greifbar ist. Geben muss es sie ja.

Aber wahrscheinlich wird in solchen Fällen mit temporären CellRanges gearbeitet. Aber die sind, soweit ich weiß, nicht per Makro erzeugbar. Auch per createInstance("com.sun.star.sheet.Spreadsheet") erzeugte Sheets funktionieren erst, wenn man sie in eine Arbeitsmappe eingehängt hat.

Wenn ich mal Zeit habe, suche ich mal im Quellcode, ob ich da was finde.

viele Grüße

Axel

Verfasst: **Sa, 07.07.2012 09:16**

Hallo,

Axel Richter hat geschrieben:Ich hatte die Hoffnung, dass jemand weiß, ob die Funktionialität, mit der CALC die ArrayFormula A2:A9="blah" in einem Literal auswertet und in eine Ergebnismatrix wandelt, irgendwie per API greifbar ist. Geben muss es sie ja.

Ok, es gibt den FormulaParser dafür.

Aber auch damit wird es natürlich nicht weniger komplex. Zumindest nicht, wenn man es allgemeingültig machen wollen würde

.

Naja, vielleicht braucht ja jemand mal den FormulaParser. Deshalb hier der Vollständigkeit halber auch noch dieses Beispiel:

Code: Alles auswählen

sub Main3

oDoc = ThisComponent

oFormulaParser = oDoc.createInstance("com.sun.star.sheet.FormulaParser")

dim ca as new com.sun.star.table.CellAddress
ca.sheet = 0 'hier Tabelle einstellen aus der A2:A9 kommen sollen

secuenceFormulaToken = oFormulaParser.parseFormula("A2:A9=""AEB""", ca)

'Das Folgende ist nur auf das konkrete Beispiel abgestellt. Eine allgemeine Funktion müsste weit aufwändiger programmiert werden.
'Wir wissen, dass die Formel 3 Token hat. Das müsste in einer allgemeinen Funktion erst abgeprüft werden.
'Wir wissen, dass das erste Token eine Zellreferenz ist.
oReference1 = secuenceFormulaToken(0).Data.Reference1
oReference2 = secuenceFormulaToken(0).Data.Reference2

'Wir wissen, dass das zweite Token ein Operator ist.
lOpCode = secuenceFormulaToken(1).OpCode '46 => "ist gleich" siehe sub getOpNameByOpCode()

'Wir wissen, dass das dritte Token ein Wert ist.
vValue = secuenceFormulaToken(2).Data

sheet1 = oReference1.Sheet
col1 = oReference1.Column
row1 = oReference1.Row
sheet2 = oReference2.Sheet
col2 = oReference2.Column
row2 = oReference2.Row

'Wir wissen, dass es eine ArrayFormula ist.
'Wir wissen, dass alles in sheet1 stattfindet.
'Wir wissen, dass es nur eine Spalte gibt.
dim matrix1(0 to row2-row1) as variant
for i = lbound(matrix1) to ubound(matrix1)
 matrix1(i) = array((oDoc.Sheets(sheet1).getCellByPosition(col1,row1+i).Formula = vValue)*-1)
next i

secuenceFormulaToken = oFormulaParser.parseFormula("B2:B9=2012.06", ca)
oReference1 = secuenceFormulaToken(0).Data.Reference1
oReference2 = secuenceFormulaToken(0).Data.Reference2
vValue = secuenceFormulaToken(2).Data
sheet1 = oReference1.Sheet
col1 = oReference1.Column
row1 = oReference1.Row
row2 = oReference2.Row
dim matrix2(0 to row2-row1) as variant
for i = lbound(matrix2) to ubound(matrix2)
 matrix2(i) = array((oDoc.Sheets(sheet1).getCellByPosition(col1,row1+i).Formula = vValue)*-1)
next i

matrix3 = oDoc.Sheets(sheet1).getCellRangeByPosition(2,1,2,8).DataArray

FuncInst = createUnoService( "com.sun.star.sheet.FunctionAccess" )
lMonTotal = FuncInst.callFunction("sumproduct",array(matrix1,matrix2,matrix3))

msgbox lMonTotal

end sub

sub getOpNameByOpCode()
oMapper = CreateUnoService("com.sun.star.sheet.FormulaOpCodeMapper")
allMappings = oMapper.getAvailableMappings(com.sun.star.sheet.FormulaLanguage.ENGLISH, com.sun.star.sheet.FormulaMapGroup.ALL_EXCEPT_SPECIAL)
xray allMappings
for i = lbound(allMappings) to ubound(allMappings)
 if allMappings(i).Token.OpCode = 46 then
  msgbox allMappings(i).Name
 endif
next i
end sub

viele Grüße

Axel

Verfasst: **Sa, 07.07.2012 14:40**

Hallo Axel!

Vielen herzlichen Dank für deine Hilfe! Ich konnte mein Problem anhand deiner Beispiele lösen:-)
Freue mich darauf, auch den FormulaParser auszuprobieren!

Man lernt so coole Sachen dank euch Leuten hier!
Schönes Wochenende und Gruss
Migesius

OpenOffice.info

Calc-Funktion "Summenprodukt" via Makro durchführen

Calc-Funktion "Summenprodukt" via Makro durchführen

Re: Calc-Funktion "Summenprodukt" via Makro durchführen

Re: Calc-Funktion "Summenprodukt" via Makro durchführen

Re: Calc-Funktion "Summenprodukt" via Makro durchführen

Re: Calc-Funktion "Summenprodukt" via Makro durchführen

Re: Calc-Funktion "Summenprodukt" via Makro durchführen