ctrl-shift-enter probleempje

[Renee123]

Ik heb in Excelmacros.xls een macro gemaakt (analyse_trc) met daarin een formule die je moet bevestigen d.m.v. crtl-shift-enter. Wanneer ik een ander excelbestand open (trc.xls) en via F8 de macro analyse_trc run, dan werkt het allemaal prima. Maar ik wil de uitvoer van deze macro graag opnemen in een stukje Matlab-code. De Matlabcode ziet er als volgt uit:

Excel=actxserver('Excel.Application');
set(Excel, 'Visible', 0);
winopen('C:\Excelmacros.xls');
Excel.Workbooks.Open('trc.xls');
Excel.Run(['''', 'Excelmacros.xls', '''!analyse_trc']);

Oftewel:
open Excel, , open Excelmacros.xls, open daarna trc.xls en run vervolgens in trc.xls de macro analyse_trc (die in Excelmacros.xls staat). De macro begint ook te werken, maar loopt vast op de formule die bevestigt moet worden d.m.v. crtl-shift-enter... De foutmelding die ik krijg is: "Eigenschap FormulaArray van klasse Range kan niet worden ingesteld"

De formule ziet er trouwens als volgt uit:

Range("Z4").Select
Selection.FormulaArray = _ "=SUM(IF(R2C3:R65536C3>=R[-1]C25,IF(R2C3:R65536C3<=R[0]C25,IF(R2C1:R65536C1=R3C,IF(R2C2:R65536C2=R2C26,R2C9:R65536C9,0),0),0),0))/SUM(IF(R2C3:R65536C3>=R[-1]C25,IF(R2C3:R65536C3<=R[0]C25,IF(R2C1:R65536C1=R3C,IF(R2C2:R65536C2=R2C26,1,0),0),0),0))"


Ja, het is inderdaad een lange formule, maar hij werkt

Iemand een idee/uitleg wat er misgaat in de communicatie tussen Matlab en Excel? Want waarom doet die macro het wel als ik 'm run vanuit excel en doet die het niet als ik 'm laat runnen vanuit Matlab?

 

[snb]

Vermijd select:

Range("Z4").FormulaArray = "=SUM(IF(R2C3:R65536C3>=R[-1]C25,IF(R2C3:R65536C3<=R[0]C25,IF(R2C1:R65536C1=R3C,IF(R2C2:R65536C2=R2C26,R2C9:R65536C9,0),0),0),0))/SUM(IF(R2C3:R65536C3>=R[-1]C25,IF(R2C3:R65536C3<=R[0]C25,IF(R2C1:R65536C1=R3C,IF(R2C2:R65536C2=R2C26,1,0),0),0),0))"

Ik heb ook een underscore weggehaald.
Het probleem zit in Excel, want dit is een gebruikelijke mededeling voor een foutieve matrixformule.
Kun je die formule niet even herschrijven in A1-vorm ?

Range("Z4").FormulaArray ="SUM(IF(C2:C65536>Y3,IF(C2:C65536<=Y4,IF(A2:A65536=Z3,IF(B2:B65536=Z2,I2:I65536,0),0),0),0))/SUM(IF(C2:C65536>=Y3,IF(C2:C65536<=Y4,IF(A2:A65536=Z3,IF(B2:B65536=Z2,1,0),0),0),0))"
Als ik het goed zie kan er nogal eens door 0 gedeeld worden (wat een foutmelding oplevert).
De SUM in de deler lijkt mij ook niet erg op zijn plaats: het resultaat is sum(1) of sum(0), wat overeenkomt met 1 en 0.
Delen door 1 lijkt me evenmin interessant.
De formule zou daarom al beperkt kunnen worden tot:

Range("Z4").FormulaArray ="SUM(IF(C2:C65536>Y3,IF(C2:C65536<=Y4,IF(A2:A65536=Z3,IF(B2:B65536=Z2,I2:I65536,0),0),0),0))"

 

[Wigi]

Zulke (matrix)formules zijn gewoon gepruts, gebruik een databasefunctie zoals DBSOM.

Als iemand als Snb er al de nodige moeite mee heeft, ben je op de verkeerde weg

Zowel jijzelf als eventuele toekomstige beheerders van deze sheets en VBA code gaan je dankbaar zijn. Je krijgt er de performantiewinst gratis bij.

Wigi

 

[Renee123]

Ok, ik snap de hint

Ik ben nu bezig met dbgemiddelde (want dat is dus wat ik uiteindelijk wil bereken), maar ik kom d'r (nog) niet uit... Dus ik zal het proberen uit te leggen wat de bedoeling is (het is een beetje complex, dus als het niet duidelijk is hoor ik het graag!!).

Zoals je in de bijlage kan zien heb ik een excelbestand met daarin data in 5 kolommen. Ik wil met die data graag de 3 tabellen vullen die in cellen K3:S10, K14:S21 en K25:S32 staan. De cellen moeten gevuld worden met het gemiddelde van de waarden in Kolom I (v (km/u)) indien aan alle vier de volgende voorwaarden voldaan wordt:
soort=XX
t (s)<=XX
t (s)>=XX
pos (m)=XX

In cel M4 moet bijvoorbeeld het gemiddelde komen van de snelheden in km/u indien voldoen wordt aan: soort = 1 én t (s)<=60 én t (s)>=0 én pos (m)=1000.
De dikgedrukte tekst geeft de variabele weer zoals die in rij 1 staan, de onderstreepte tekst geeft de voorwaarde weer, zoals die uit tabel K3:S10 blijkt. Het juiste antwoord voor M4 = 120,16 km/u.

Nog een voorbeeld. In cel N18 moet het gemiddelde komen van de snelheden in km/u indien voldaan wordt aan: soort = 2 én t (s)<=240 én t (s)>=180 én pos (m)=1250. Het getal dat uiteindelijk in N18 moet komen is 112,07.

Voor de cellen waar niet aan alle vier de voorwaarden voldaan wordt, mag een #DEEL/0! komen te staan, of een -999. Op zich maakt dat verder niet uit

Ik hoop dat iemand met de echte excel-skills dit probleem wel op kan lossen

Dank alvast!!

 

[Wigi]

Bestudeer (en gebruik!) de standaard functionaliteiten binnen Excel ipv dit na te bootsen met hopen VBA-code. Een draaitabel doet hetgeen jij wil zonder 1 enkele formule.

Wigi

 

[Renee123]

Hmm, nooit eerder met die draaitabellen gewerkt, maar dat is inderdaad super handig!

Probleem opgelost dus