Ranking in lijst met meerdere voorwaarden

[michvand]

Beste Excelexperts,

Kunnen jullie me helpen met het volgende probleem die ik als bijlage heb meegezonden.

In kolom A heb ik datums, kolom B min temp die dag en in C de max van de corresponderende datum.
Nu heb ik in kolom F de dagen vh jaar en wil ik in kolom G de hoogste temp gehaald op de dag zoals die in kolom F staat. Er moet dan een jaartal verschijnen met erachter de temp. In kolom H moet dan 1 na hoogste temp en in I 2 na hoogste. Iets van een formule die ik in g4 kan zetten en dan kan doorvoeren. De andere kolommen zullen dan iets soortgelijks hebben met ipv een ranking max een ranking min.

Even terugkomend op kolom F, er staat 1 jan, maar in de display staat hardnekkig 01-01-2022. Hoe kan ik bij doorvoeren voor het hele jaar ook 29 feb laten verschijnen?
Bij voorbaat dank voor jullie antwoorden.
Bekijk bijlage Voorbeeld 2.docx
Vr. groet,
Mich

 

[JEC.]

Hiervoor is een voorbeeldbestand nodig. Anders een gokje

Office 365

=LET(rng;SORTEREN(FILTER($A$3:$C$14;$A$3:$A$14=F4);3;-1);INDEX(JAAR(INDEX(rng;;1))&" - "&INDEX(rng;;3);{1\2\3}))

ofwel

=INDEX(BYROW(SORTEREN(FILTER($A$3:$C$14;$A$3:$A$14=F4);3;-1);LAMBDA(x;JAAR(INDEX(x;;1))&" - "&INDEX(x;;3)));{1\2\3})

 

[michvand]

Ik heb een nieuw bestandje gemaakt want de formule werkt niet.
1-1 ingevuld in een cel geeft altijd 1-1-2022
Ik heb de datum maar gesplitst, de dagen staan in de e-kolom en de maanden in de f kolom.
Welke formule moet ik nu gebruiken om de hoogst voorkomende temp van een bepaalde datum in kolom G te krijgen.
Het moet een formule worden die ik in G4 moet invullen waarbij dan jaartal en temp gaat komen. volgens mij moet dit automatisch kunnen. Met doorvoeren naar rechts en beneden zou ik alle dagen zo kunnen invullen.
Als voorbeeld heb ik wat ingevuld om te laten zien hoe ik het wil hebben. Voor G4 zou de uitkomst moeten zijn 1902: +9.8. Ik heb verder de kolommen voor hoogste en laatste maximumtemp voor 1-1 ingevuld. Ik gebruik Office 2019.
Bij voorbaat dank voor jullie suggesties,
Mich.
Bekijk bijlage Voorbeeld 3.xlsx

 

[JEC.]

Voor de hoogste max even voorgedaan.
Deze afsluiten met ctrl+shift+enter (matrix) en doortrekken vanaf G4

=INDEX(YEAR($A$2:$A$218)&" : "&$C$2:$C$218;MAX(IF(LARGE((DAY($A$2:$A$218)&MONTH($A$2:$A$218)=$E4&$F4)*($C$2:$C$218);G$3)=(DAY($A$2:$A$218)&MONTH($A$2:$A$218)=$E4&$F4)*($C$2:$C$218);ROW($1:$217))))

 

[michvand]

Ik heb het formuletje ingegeven, maar krijg helaas een foutmelding.

 

[JEC.]

Bij mij werkt deze prima

 

[michvand]

Dank voor het toegevoegd bestandje. De maximumwaarden met de rangen 1, 2 en 3 werken uitstekend. Ik heb de formule voor minimumwaarden toegepast door max te vervangen door min en large door small. Dit gaat bij een heleboel datums goed, totdat de waarde positief is.... Dan wordt de negatieve waarde van 1 jan ingevuld. Ter illustratie heb ik het doc toegevoegd. Heb je misschien een aanpassing zodat de juiste waarden ingevuld worden? De kolom C geeft max waarden, in k,l en m kolom de rangorden. In kolom O de waarden zoals ze moeten worden voor rang 1. Bij voorbaat dank voor je antwoord.Bekijk bijlage Voorbeeld 4.xlsx

 

[michvand]

Misschien was het bestandje te groot. Ik heb een nieuwe versie gemaakt met alleen de max temp zoals die in maart voorkwamen. In kolom A vind je de datums, in kolom C de de temp. In kolom E en F de dagen en de maand. In de kolommen K,L, en M wil ik de rangorde hebben van laagste max temp, 1 na laagste en 2 na laagste, corresponderend met de dag en maandBekijk bijlage Example.xlsx. Het gaat goed als de laagste temp negatief is, maar als deze positief is gaat het fout. Hoe kan ik dit oplossen?Bekijk bijlage Example.xlsx

 

[dirktimm]

Ranking

Hallo Michvand,

Ik neem aan dat je nog een oplossing krijgt van JEC.

Ik denk dat je nog een ander probleem hebt. Het is namelijk mogelijk dat een bepaalde zelfde waarde in verschillende jaren op de zelfde datum kan voor komen. Je moet dus eigenlijk meervoudig zoeken. Je kunt ook met een filter gaan werken.

Ik heb voor een filter een idee. In B1 de datum van vandaag. In D1 een aantal dagen in te geven om te plussen of te minnen om op een juiste datum te komen. Had ik nu ook nodig omdat de datums allemaal in maart liggen. In F1 dus de peildatum. In H1 de gevonden temperatuur over een bepaalde dag over alle jaren. In I1 de gevonden dag en in J1 de gevonden maand. Je kunt nu met een filter over de kolommen B t/m D de filter laten werken. Onder de knop zit een macro die het nog niet doet. Even sleutelen. Maar voor dat ik verder ga, het is misschien helemaal geen goed idee.

groet

Dirk

 

[JEC.]

Er kunnen inderdaad dubbelingen zijn. Mocht dat niet uitmaken:
Let wel op. Je bestand wordt extreem traag als je matrixformules op veel data loslaat. Met deze 4000 regels wordt hij al aardig traag.

=INDEX(JAAR($A$2:$A$3800)&" : "&$C$2:$C$3800;MAX(ALS(KLEINSTE(ALS.FOUT(1/(1/((DAG($A$2:$A$3800)&MAAND($A$2:$A$3800)=$E4&$F4)*($C$2:$C$3800)));EXP(99));K$3)=(DAG($A$2:$A$3800)&MAAND($A$2:$A$3800)=$E4&$F4)*($C$2:$C$3800);RIJ($1:$3799))))

 

[michvand]

Beste Dirktimm en JEC,

Beiden hartelijk dank voor de antwoorden.
Bijgaand een vernieuwde versie, example 3 genoemd.
Ik heb een hulpkolom D gemaakt, zodat alle temperaturen negatief zijn.
Op sommige datums als de waarden positief waren kreeg ik een lege cel te zien.
Door het hulpkolom is deze nu verholpen.
Inderdaad doet zich nu een probleem voor bij ex eaquo gevallen. Dit is inderdaad een probleem. Als dit bij plek 3 gebeurt, is het dan mogelijk dat dat vermeld wordt en dat er dan een jaar toegevoegd wordt?
Bij het toepassen van de oplossing zoals JEC aangaf krijg ik foutmelding.
Kan je misschien een document retourneren in de bijlage van je antwoord met de goede formule? Door vertaling verdwijnen er haakjes. Graag zonder macro’s, zodat ik de formule kan toepassen in een ander document.
Is het zelfs mogelijkBekijk bijlage Example 3.xlsxs zonder hulpkolom?
Bij voorbaat dank,
Mich.

 

[michvand]

Ik ben nog steeds aan het puzzelen met dit probleem. Voor een heleboel datums werkt de formule uitstekend, maar voor sommige datums niet. Kunnen jullie misschien uitvinden waar het probleem zit? Kolom a de datums, kolom c de temperaturen, kolom een 10-void verminderd met een factor per lijn om duplicaten te voorkomen. in I4 de formule, maar deze werkt niet. Bij voor baat dank voor jullie hulp.

Bekijk bijlage Example 6.xlsx

 

[dirktimm]

ranking

Hallo Michand,

In de formule staat helemaal op het eind: "RIJ($1:$125)))) Volgens mij moet dat zijn : RIJ($1:$123))))

Of dat voor alle formule het probleem is weet ik niet. testen maar. Ik ben benieuwd hoe de snelheid gaat op het gehele bestand.

Groet,

Dirk

 

[michvand]

Dit heeft inderdaad geholpen. Het ging echter om een voorbeeldbestand. In mijn oorspronkelijke bestand blijven er fouten in zitten

 

[michvand]

Allereerst hartelijk dank voor de correctie op mijn voorbeeldbestand. Maar in de oorspronkelijke bestand is het probleem niet opgelost. Ik heb deze als bijlage toegevoegd, genaamd "Bestand". In kolom A vind je de datums, in kolom C de temperaturen. Ik heb een hulpkolom D gemaakt. De formule bleek alleen te werken als alle getallen negatief waren. Vandaar dat ik in kolom D de temp met 40 graden vermeerderd heb. Om ex aequo's te voorkomen heb ik dit getal met 10 vermenigvuldigd en een fractie van afgetrokken, afhankelijk van de regel. Met het toepassen van de formule heb ik voor enige datums (bv 21-02 en een paar datums in november en december) foute antwoorden. Ik heb geen idee hoe dat kan, misschien weet iemand dit op te lossen. Misschien met een andere formule waarbij de hulpkolom niet nodig is?

Bij voorbaat dank voor jullie tijd,

Vr. groet,

Mich.

Bekijk bijlage Bestand.xlsx

 

[michvand]

Ik heb het probleem zelf opgelost. Ik heb het grote bestand in tweeën verdeeld. Een deel omvat de eerste 6 maanden, het andere de laatste 6 maanden. Nu werkt de formule wél zonder fouten. Blijkbaar was het bestand te groot om alles in één keer te kunnen doen.
Ik beschouw mijn vraag als opgelost en dank voor jullie antwoorden.