Performance matrix formule

[Peter B]

Ik gebruik nu de volgende matrix formule:

{=SOM(ALS(ISGETAL(VIND.SPEC($A2;'Blad1'!$G$2:$G$2001));1;0)*('Blad1'!$R$2:$R$2001=K$1))}

Het probleem is dat deze formule zo'n 3000 keer voorkomt. En dat mijn computer "wat" moeite heeft om 'm te berekenen. Is er een formule die hetzelfde doet maar sneller is? Ik werk met Excel 2010.

 

[Joske2000]

Zonder voorbeeldje valt dit niet uit te dokteren.

Thx,

Joske

 

[snb]

misschien bedoel je:

{=SOM((Blad1!$G$2:$G$2001=$A$2)*('Blad1'!$R$2:$R$2001=K$1))}

 

[sylvester-ponte]

geef in je te plaatsen voorbeeldje aan hoe de sortering van de kolommen is (als die er is)

 

[Peter B]

@Joske2000: Ik meende dat dit wel zonder voorbeeld bestand kon. Blijkt toch weer hoe belangrijk dat is ...
@snb: Er moet in de matrix-formule op Blad2 worden gezocht naar een deel van de tekst op Blad1. Helaas werkt je vereenvoudiging dus niet. Zie ook het voorbeeld
@sylvester-ponte: Er is geen specifieke sortering. Zie ook het voorbeeld

Bekijk bijlage Performance matrix formule.xlsx

 

[sylvester-ponte]

misschien werkt dit sneller omdat de "zoek in het woord naar bepaalde letters " alleen uitgevoerd wordt wanneer Ja of Nee klopt.

Function mijnAantal(JaNeeWaarde As String, Testwoord As String, JaNeeTabel As Range, Woorden As Range)
    Dim R As Range, KolomVerschil As Integer
    KolomVerschil = Woorden.Column - JaNeeTabel.Column
    For Each R In JaNeeTabel
        If R = JaNeeWaarde Then
            If InStr(1, R.Offset(, KolomVerschil), Testwoord) > 0 Then mijnAantal = mijnAantal + 1
        End If
    Next
End Function

zie voorbeeld:
zorg er voor dat je de JaNeeTabel zo kort mogelijk is dus niet de hele kolom weergeven als gebied.

 

[snb]

Sub M_snb()
    sn = [transpose(Blad1!B1:B3000&Blad1!C1:C3000)]

    With Sheet2
        MsgBox .Cells(2, 1) & vbTab & .Cells(1, 2) & vbTab & UBound(Filter(Filter(sn, .Cells(2, 1)), .Cells(1, 2))) + 1
        MsgBox .Cells(2, 1) & vbTab & .Cells(1, 3) & vbTab & UBound(Filter(Filter(sn, .Cells(2, 1)), .Cells(1, 3))) + 1
        MsgBox .Cells(3, 1) & vbTab & .Cells(1, 2) & vbTab & UBound(Filter(Filter(sn, .Cells(3, 1)), .Cells(1, 2))) + 1
        MsgBox .Cells(3, 1) & vbTab & .Cells(1, 3) & vbTab & UBound(Filter(Filter(sn, .Cells(3, 1)), .Cells(1, 3))) + 1
    End With
End Sub

 

[Peter B]

@sylvester-ponte: Ik heb een aantal metingen gedaan, maar helaas merkte ik geen verbetering in de performance.

@snb: Ik moet je voorbeeld eens bestuderen. Nu wordt natuurlijk een messagebox getoond. Daarnaast was mijn voorbeeld erg simpel. In de praktijk zijn er zo'n 500 rijen en moeten voor 6 variabelen de aantallen worden gesommeerd. De aantallen komen uit 2 kolommen van zo'n 5000 regels (waar ik hier dus maar 25 regels heb gebruikt).

 

[sylvester-ponte]

vreemd,
als dit niets oplevert, zou je zeggen dat de vertraging ergens anders zit.
heb je een "klein" voorbeeldje van wat je hoe doet?

 

[snb]

Zoals je ziet heb ik in mijn code 3000 regels gebruikt.

Wat in een msgbox kan staan kan overal elders in een werkblad gezet worden (het voordeel van variabelen)

toegesneden op jouw criteria (dus zonder matrixformules in Excel):

Sub M_snb()
    sn = [transpose(Blad1!B1:B5000&Blad1!C1:C5000)]
    sp = Sheet2.Cells(1).CurrentRegion
    
    For j = 2 To UBound(sp)
      For jj = 2 To UBound(sp, 2)
        sp(j, jj) = UBound(Filter(Filter(sn, sp(j, 1)), sp(1, jj))) + 1
      Next
    Next
    
    Sheet2.Cells(1).CurrentRegion.Offset(, 10) = sp
End Sub

 

[sylvester-ponte]

snb, ik heb jouw code getest.
hij is iets sneller dan de orginele code.
maar doordat de gegevens samen gevoegd worden,
is er een risico dat de teller wat hoger komt dan de bedoeling.
als op abcd gezocht wordt
en het woord is bvb :
"abcd_neeljejans"
dan telt dit woord zowel bij de nee, als bij de ja.

ps mijn code is zeer traag , daar moet ik nog wat aan verbeteren

 

[snb]

Wat bedoel je met 'de originele code' ?

 

[sylvester-ponte]

{=SOM(ALS(ISGETAL(VIND.SPEC($A2;'Blad1'!$G$2:$G$2001));1;0)*('Blad1'!$R$2:$R$2001=K$1))} uit post 1

 

[snb]

Formule zou ik zeggen.

De snelheid kan, lijkt mij, het best getest en vergeleken worden in het echte bestand van de vragensteller.

 

[sylvester-ponte]

helemaal me eens.

 

[sylvester-ponte]

hier iets met een tijds opname

test het eens uit misschien klopt het niet helemaal

 

[Peter B]

Dank jullie beiden voor de aangedragen oplossing(en) en voor de tool t.b.v. de performance meting. Hoewel uit de performance tool naar voren komt dat de functie het snelste zou moeten zijn werkt dit in het echte bestand niet sneller. Ik begrijp niet goed waarom niet. De gepresenteerde tijden voor mijn formule en de oplossing van snb komen wel redelijk overeenl!

De formule kost in het echte bestand ongeveer 18 seconden; in het performance tool 14-16 (hier zit wat variatie in)
De functie kost in het echte bestand 16-18 seconden; in het performance tool 4-5(!)
De macro kost in het echte bestand ongeveer 8 seconden; in het performance tool ook

Waar het verschil door wordt veroorzaakt begrijp ik niet. Ik heb echter besloten dat ik de optie van een macro het meest handig vind. Dit omdat ik deze sheet graag op de achtergrond open wil houden en automatisch berekenen aan wil houden.

Nu zit ik echter met het volgende probleem, en dat is dat ik de macro van snb niet begrijp, waardoor het opnemen in mijn eigen sheet niet werkt. Het gaat om het "currentregion" statement en de offset. Ik krijg er gewoon geen grip op wat deze nu precies doen.

Concrete vraag. De tekst waarop gecontroleerd moet worden staat op blad2 in kolom A, te starten vanaf regel 14 (regel 13 is de header regel, waarin de JA en NEE uit het voorbeeld staan). De JA (header) uit het voorbeeld staat in kolom K. In de kolommen B-J staat informatie die voor de telling niet benodigd zijn. Hoe pas ik dit in in de macro van snb? Ik kom er echt niet uit ...

 

[sylvester-ponte]

ps in post 16 heb ik een versnelde versie van mijn formule gebruikt

en dat het in de macro kan voorkomen dat als in kolom B ergens een NEE of een JA verstopt zit dat dit tot een telfout kan leiden.
(of is het lijden)
zo als te zien is in de test

 

[Peter B]

Ah. Dat verklaart mogelijk het verschil! Je hebt hiermee dus wel duidelijk de snelste oplossing aangedragen.

 

[snb]

Maak even een echt voorbeeld van het bestand waarin je de macro wil laten draaien.

@sylv

Je hebt mijn macro aardig vernaggeld, zodat de tijdmeting nietszeggend is geworden.