• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Laagste top 5

Status
Niet open voor verdere reacties.
B

Bart1980991

Gebruiker
Lid geworden
15 aug 2018
Berichten
40
Hallo,

Ik wil graag een top 5 snelste tijden uit een dataset halen alleen deze moeten aan een aantal voorwaarden voldoen:
- kolom A: Game naam
- Kolom B: Maand
- kolom E: Tijd
etc.

Met deze formule krijg ik precies wat ik moet hebben voor de snelste tijd die voldoet aan de criteria:
=MIN.ALS.VOORWAARDEN(E:E;A:A;"*rincon*";E:E;">0";C:C;2023;I:I;">0")

Alleen ik wil ook graag de op 1 na snelste en de op 2 na snelste tijd...

Ik probeerde deze formule te combineren met =Kleinste( 1 t/m 5 ) maar dan krijg ik steeds een fout.
Iemand een idee om dit op te lossen?

Alvast dank
 
Of een voorbeeldbestandje kan ook wonderen verrichten.
Natuurlijk ontdaan van gevoelige info.
Geef dan ook even aan welke Excel versie je gebruik.
 
voorbeeldbestandje

Hallo Peter,

Je hebt gelijk dat werkt makkelijker.

Vraag over bestand:
Ik wil de top 5 tabellen vullen met de snelste tijden per game, daaronder ook de top 5 met de snelste van een bepaalde maand, dus met een extra voorwaarden.

Ik heb 2 vragen (staan ook in het bestandje)
1. In Geel gearceerd de formule die ik heb gemaakt - Alleen deze werkt alleen andersom met de functie Grootste - maar als ik Kleinste gebruik dan blijft alles op '0' staan. Wat doe ik fout?
2. Hoe voeg ik extra voorwaarden in de formule zoals een specifieke maand?

Ik heb verschillende ALS() functies geprobeerd maar dat werkte niet.


Exel versie office professional plus 2019
 

Bijlagen

een oplossing voor gegevens < 1000 rijen (en mits nog een beetje foutafhandeling)
In de F-kolom een ranking voor de komma en het rijnummer na de komma
 

Bijlagen

Laatst bewerkt:
Hallo,

Ik heb het iets anders proberen op te lossen.
Met power query en draaitabellen dus zonder formules.
 

Bijlagen

Beste Cow18 en Peter,

Dank deze werken allebei!
Alleen begrijp ik de formule van Cow dus die is voor mij beter te reproduceren.
De power query is een mooie oplossing alleen weet ik niet hoe ik die over kan zetten naar mijn eigen gegevens.

(en ik vraag me nog steeds af waarom mijn "=GROOTSTE" formule wel werkt maar "=KLEINSTE" niet)

Hartelijk dank nogmaals voor het meedenken, ik ben er erg mee geholpen!
Bart
 
=KLEINSTE(INDEX(($A$2:$A$42=$H$2)*$C$2:$C$42;0);1)
alle games die niet $H$2 zijn resulteren in een 0, de goeie in de opgegeven tijd van kolom C, dus de kleinste waarde is altijd een 0 van zodra er 1 game <>$H$2
trek je top-5 anders eens door tot aan je top-42 en doe hetzelfde met je formule, na de zoveelste nul krijg je toch plots tijden binnen

Dit had bijvoorbeeld gekund in I22 (mogelijks aflsuiten met SHIFT+CTRL+ENTER ???)
Code: 
=KLEINSTE(ALS($A$2:$A$42=$H$2;$C$2:$C$42;"-");H22)
 
Laatst bewerkt:
Hallo Bart

De power query is een mooie oplossing alleen weet ik niet hoe ik die over kan zetten naar mijn eigen gegevens
Klik om te vergroten...
Als je lay-out en de kolomnamen gelijk zijn dan is dit geen enkel probleem.
Je ziet in de bijlage dat in tab "Data" de Maand kolom is verdwenen.
Deze wordt in de power query automatisch toegevoegd.
Tevens in tab "Data" enkele nieuwe gegevens ingevoerd.
Als je dat ook doet en je gaat hierna naar tab "DrTbl" en klik ergens op een cel in een tabel en hierna op "alles verversen" dan wordt het e.e.a. ge-update.
 

Bijlagen

cow18 zei:
=KLEINSTE(INDEX(($A$2:$A$42=$H$2)*$C$2:$C$42;0);1)
alle games die niet $H$2 zijn resulteren in een 0, de goeie in de opgegeven tijd van kolom C, dus de kleinste waarde is altijd een 0 van zodra er 1 game <>$H$2
trek je top-5 anders eens door tot aan je top-42 en doe hetzelfde met je formule, na de zoveelste nul krijg je toch plots tijden binnen

Dit had bijvoorbeeld gekund in I22 (mogelijks aflsuiten met SHIFT+CTRL+ENTER ???)
Code: 
=KLEINSTE(ALS($A$2:$A$42=$H$2;$C$2:$C$42;"-");H22)
Klik om te vergroten...

Ja nu snap ik het, zo had ik het nog niet bedacht. Op deze wijze kan je dus ook de maanden nog als voorwaarden toevoegen.
Nogmaals dank, ik ben er erg mee geholpen!
 
peter59 zei:
Hallo Bart


Als je lay-out en de kolomnamen gelijk zijn dan is dit geen enkel probleem.
Je ziet in de bijlage dat in tab "Data" de Maand kolom is verdwenen.
Deze wordt in de power query automatisch toegevoegd.
Tevens in tab "Data" enkele nieuwe gegevens ingevoerd.
Als je dat ook doet en je gaat hierna naar tab "DrTbl" en klik ergens op een cel in een tabel en hierna op "alles verversen" dan wordt het e.e.a. ge-update.
Klik om te vergroten...

Hallo Peter, ik zie hoe het werkt met de draaitabellen. Ik neem aan dat deze optie minder belastend is dan formules.
Ik ga even kijken of deze ook online luk t want ik schrijf data namelijk via een webhook (Make) in een Excel. Ik zal testen of de powerquery dan ook werkt.

Jij ook nogmaals dank voor het meedenken en de snelle reacties! Waardeer ik zeer!
 
"Ja, bij een zeer grootte dataset kunnen formules vertragend gaan werken."
2*20=40 aggregaat-formules en daarnaast nog een keer 80 gewone index-formules, ik zou het nog eens zou willen meevallen, het zijn geen matrixformules !.
Ik had vooraf gezegd dat deze formules werkten tot 999 rijen, mits aanpassing kan dat zo tot bv. 999.999 aangepast worden, maar geef anders eens een idee wat een groot dataset voor jullie betekent.
Zo'n query moet je anders telkens vernieuwen, dat is hetzelfde alsof dat je het herrekenen er eventjes afgooit en dan met F9 gaat rekenen. Je mag/kan geen appelen met peren vergelijken.
 
Laatst bewerkt:
@Cow: Mijn dataset is idd niet groot, misschien 2000 per jaar en ik kan deze jaarlijks verversen.
Dus jouw oplossing is op dit moment zekere de beste voor mij (omdat ik deze ook snap)

De power query vind ik interessant om me eens in te verdiepen want daar zie ik wel andere toepassingen voor die ik wellicht kan gebruiken. Zeker als die ook rechtstreeks op de webhook werkt.
 
ik heb S1 even aangemaakt zodat je nu tot rij 9999 mag gebruiken.
De foutwaarden bij de resultaten zijn nu voorwaardelijk grijs (kunnen nog lichter!), zodat die niet zo opvallen.
 

Bijlagen

@Cow

De foutwaarden kunnen toch worden opgelost met b.v. deze formule?
Code: 
=ALS.FOUT(INDEX($E:$E;R13);"")

Tevens een opmerking v.w.b. de traagheid.
In de div. index formules gebruik je hele kolommen met wel meer dan 10.000.000 rijen per kolom.
Dus dat wordt zeer traag.
Je hebt niet voor niets het e.e.a. begrenst tot 10.000 rijen.
 
de keuze tussen foutwaarden voorwaardelijk grijs kleuren of afvangen via zo'n "als.fout"-constructie, dat is inderdaad een afweging waard, maar ik had er net voor al alle formules eventjes aangepast aan die nieuwe 10.000 grens en was daar enkele minuten mee zoet en die voorwaardelijke was klaar in 2 sec, dus ... .

De 2e parameter van de index-formule heeft de grootste invloed, of er nu gezocht wordt op +1.048.000 rijen (geen 10.000.000) of op 42 is verwaarloosbaar.
Zie macro die 100.000 keer een index uitrekent op de volledige kolom dan wel een beperkt bereik. Traag worden is relatief, hier volledig in het geheugen = 1.35 sec voor 100.000 indexen (zonder wegschrijven naar de cel).
PS. ik kom bij die 100.000 loop of een tijdsverschil van 0.030 sec en laat dat ook zo ongeveer de nauwkeurigheid (=absolute fout) van die timer-functie zijn, eigenlijk zou je het met 1.000.000 moeten doen !
Net even de test gedaan met 1.000.000 loops is er een verschil van 0.3 sec in het voordeel van de volledige kolom, dus slotsom = de grootte van het bereik maakt geen bal uit.
Code: 
Sub test()
     s1 = "=index(E:E,mod(#-1)+1 )"    '=volledige kolom E
     s2 = "=index(E1:E42,mod(#-1)+1 )" '=eerste 42 cellen van E
     loops = 100000    '100.000 keer zoeken via beide methodes

     t0 = Timer

     For i = 1 To loops
          X = Evaluate(Replace(s1, "#", i))
     Next

     t1 = Timer

     For i = 1 To loops
          X = Evaluate(Replace(s2, "#", i))
     Next

     t2 = Timer

     delta1 = t1 - t0
     delta2 = t2 - t1
     MsgBox t1 - t0 & " s voor de volledige kolom" & vbLf & t2 - t1 & " s voor een beperkt bereik" & vbLf & vbLf & (delta2 - delta1) / loops & " s voordeliger voor een opzoeking via de volledige kolom", vbInformation, "100.000 loops"

End Sub
 
Laatst bewerkt:
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan