[Excel] automatisch updaten van top 5 waardes

[Coolyo_Lars]

Dag allemaal,

Momenteel loop ik stage, en ben ik bezig een hoop data in excel te gooien. En moet eerlijk zeggen ben best trots op dat ik sommige dingen werkend heb gekregen. Maar 1 ding blijft mij echt continue tegenwerken. Daarom dacht ik misschien kan iemand mij hier helpen met de oplossing.
Ik ben bezig met het identificeren van bacteriën per periode. En ik wil graag dat excel hiervan een top 5 meest voorkomende bacteriën grafiek maakt. Daarom dacht ik ik maak een lijstje met de 5 meest voorkomende bacteriën en hoe vaak deze dan ook voorkomen. En met een beetje hulp van internet ben ik tot de volgende formule gekomen: =INDEX($B$5:$B$19;(VERGELIJKEN(GROOTSTE($C$5:$C$19;RIJEN($B$24:$F25));$C$5:$C$19;0)))
Echter zou dit dan ook de getallen moeten geven van de waardes die bij de meest voorkomende horen. Dat doet hij bij mij alleen niet. En ik krijg dat ook eigenlijk niet aan de praat. Verder vult hij ook vaker dezelfde in als er meerdere zijn met dezelfde waarde (op dit moment zijn dat er nog heel veel maar er komen bijna 300 extra datapunten nog bij). Maar voor het geval dat er in de top 5 toch nog 2 dezelfde waardes hebben wil ik dat alvast even voor zijn. Kan iemand mij helpen met misschien het verbeteren van de formule waardoor hij beter gaat lopen? Ik heb hieronder een afbeelding toegevoegd over wat ik zie in excel. Alvast heel erg bedankt voor de hulp.


Mijn vraag

[Tazzios]

Wat ik altijd doe om ingewikkelde formules en ingewikkelde constructies en onderhoud te voorkomen is:
Van de data een excel tabel maken ('Opmaken als tabel')
en dan met draaitabel overzichten maken op ander tabbladen.
KolomB op kolommen zetten, top5 en sorteren op de gewenste waarde.

[dix-neuf]

Ik zie in je afbeelding 5 kolommen met aantallen en 5 met percentages.
Kun je zeggen welk resultaat je van je formule verwacht? Dat is me nl. niet duidelijk.
Je schrijft dat je fomule getallen zou moeten weergeven, maar de index in je fomule bevat alleen tekstwaarden, dus dan kan de uitkomst sowieso nooit een getal zijn.

[Lanfear89]

Even kijken of ik begrijp wat je wil. Je wil een top 5, op basis van de aantallen in kolom C de corresponderende bacterie hebben in kolom B. ie is je lijstje 5,2,1,1,1. Dan wil je eerst de waarde uit kolom B die hoort bij 5, dan de waarde die hoort bij 2, en dan de 3 unieke waardes voor de verschillende 1'en?

[ Voor 127% gewijzigd door Lanfear89 op 16-05-2022 21:58 ]

[The_Vice]

Als je data wilt analyseren is het beter om de brondata onder elkaar te zetten alvorens er rapportage van te maken.
In jou geval, de data uit B & C onder elkaar herhalen, met de waardes voor de periodes erachter. en een kolom die de periode benoemt (1,2,3,4. A,B,C,D of iets dergelijks)

Dan kan je daarna met draaitabellen (pivot tables) aan de slag.
Daar kan je dan per periode de Max waarde achterhalen. En vervolgens terugzoeken, met een reguliere functie, welke bacterie bij de maximale waarde van de periode hoort.

de brondata zou er uit moeten zien als:

bacterie	waarde	periode
eucalypta	10	A
Oehoeboeroe	5	A
eucalypta	9	B
Oehoeboeroe	6	B

[ Voor 28% gewijzigd door The_Vice op 16-05-2022 21:54 ]

[Coolyo_Lars]

Tazzios schreef op maandag 16 mei 2022 @ 15:30:
Wat ik altijd doe om ingewikkelde formules en ingewikkelde constructies en onderhoud te voorkomen is:
Van de data een excel tabel maken ('Opmaken als tabel')
en dan met draaitabel overzichten maken op ander tabbladen.
KolomB op kolommen zetten, top5 en sorteren op de gewenste waarde.

Ik ben gisteren met die draaitabelen aan de slag gegaan. Das wel heftig zeg, begrijp er nog niet zoveel van maar goed ga mn best doen er iets moois van te maken.

dix-neuf schreef op maandag 16 mei 2022 @ 20:33:
Ik zie in je afbeelding 5 kolommen met aantallen en 5 met percentages.
Kun je zeggen welk resultaat je van je formule verwacht? Dat is me nl. niet duidelijk.
Je schrijft dat je fomule getallen zou moeten weergeven, maar de index in je fomule bevat alleen tekstwaarden, dus dan kan de uitkomst sowieso nooit een getal zijn.

ja ik zie dat nu inderdaad, dat was niet mn slimste momentje van tekst kan je niet opeens getallen maken.

Lanfear89 schreef op maandag 16 mei 2022 @ 21:42:
Even kijken of ik begrijp wat je wil. Je wil een top 5, op basis van de aantallen in kolom C de corresponderende bacterie hebben in kolom B. ie is je lijstje 5,2,1,1,1. Dan wil je eerst de waarde uit kolom B die hoort bij 5, dan de waarde die hoort bij 2, en dan de 3 unieke waardes voor de verschillende 1'en?

Ja dat klopt dat is exact wat ik wil, dat mochten er 2 soorten zijn die allebei in de top 5 staan met dezelfde hoeveelheid dat ze dan beide in de top 5 komen te staan en niet alleen de bovenste.

[Lustucru]

Als je het zonder een draaitabel wilt:

Maak een hulpkolom met rang. Om dubbele waarden te vermijden voeg je daar een kleine waarde aan toe. Dat kan met willekeurig of bv rij()/1000. Bijvoorbeeld
=RANG.GELIJK(C5;$C$5:$C$20;1)+ RIJ()/100

In die kolom zoek je steeds de rij op van de k-1 grootste waarde en met de gevonden rijwaarde haal je naam en aantallen op. Dus stel de rangkolom is kolom d dan wordt dan haal je met
=INDEX($B$5:$B$19;(VERGELIJKEN(GROOTSTE($D$5:$D$19;1);$D$5:$D$19;0)))
de eerste naam op; door in de formule B5:B19 te vervangen door C5:C19 het aantal.