Maxwaarde in matrix bekend,nu graag mijn kolom- en rijnummer

[Vato0o]

Goedenavond,

Ik ben bezig met Excel en loop vast bij het volgende. Ik heb één grote tabel van twee tabellen die zijn gecombineerd. Door dit te combineren krijg ik twee waardes die ik nodig heb: de maximale waarde en minimale waarde, deze krijg ik door de functies MAX en MIN te gebruiken. Nu ik mijn Max waarde en Min waarde weet wil ik graag de plaats en het begin weten zie: [img]https://www.mupload.nl/img/57fv30idaun.png[/img}]. Hoe krijg ik dit voor elkaar zie groene en oranje rij. Stel mijn Min waarde = -66,11 dan wil ik = plaats 17 en begin = 5. Ik heb het geprobeerd met VERT zoeken en INDEX, kom er maar niet uit.

Alvast bedankt!!!!

[Paul1987]

Ik begrijp niet precies wat je bedoelt en jouw afbeelding is niet goed in je post opgenomen.
Daarnaast mis ik wat achtergrond over wat je precies al geprobeerd hebt.

Mijn interpretatie van jouw vraag is dat jij een manier zoekt om op basis van 1 zoekwaarde die in jouw matrix van 'Begin en Plaats'-nummers voorkomt, het corresponderende plaatsnr/beginnr te achterhalen.

Belangrijk blijft wel dat het onduidelijk is of een waarde meer dan 1 keer voor kan komen in de matrix.

Correct me if i'm wrong, maar ik bouw op deze veronderstelling verder.
Ik heb dit snel in een stukje VBA gemaakt, mogelijk dat dit ook met een formule kan maar het onderstaande stukje code kan het sowieso. Je zou deze kunnen koppelen aan een 'Worksheet_change' sub of een knop maken waar je de module aan koppelt. Maar dat kun je zelf d.m.v. google makkelijk uitvinden.

Visual Basic .NET: Module1

Sub test()

Dim wb As Workbook
Dim ws As Worksheet
Dim zoekwaarde As Range

Dim Plaats As Range
Dim Begin As Range


Set wb = ActiveWorkbook
Set ws = wb.Sheets("blad1")
Set zoekwaarde = ws.Range("A5") 'Cel waar de zoekwaarde ingevuld wordt. Dit kan een willekeurige cell zijn
Set Plaats = ws.Range("A6") 'cel waar het plaats nummer te staan komt
Set Begin = ws.Range("A7") 'cel waar het begin nummer te staan komt


'zorgt ervoor dat de cell altijd eerst leeggemaakt wordt (oude resultaten worden verwijderd
Plaats.Value = ""
Begin.Value = ""


'Definieert de laatste (gevulde) cel in kolom B, dit om ervoor te zorgen dat je niette veel rijen raadpleegt bij een zoek opdracht.
Dim Last_R
With ws
Last_R = .Cells(.Rows.Count, "B").End(xlUp).Row
End With


'Definieert de laatste (gevulde) cel in rij 2, dit om ervoor te zorgen dat je niet teveel kolommen doorloopt bij de zoekopdracht.
Dim Last_C
With ws
    Last_C = .Cells(2, .Columns.Count).End(xlToLeft).Column
End With


For C = 3 To Last_C 'eerste kolom met waarden die je in de matrix wilt hebben
If ws.Cells(2, C).Value <> "" Then
    For r = 4 To Last_R 'eerste rij met waarden die je in de matrix wilt hebben
    If ws.Cells(r, C).Value = zoekwaarde.Value Then
        Plaats.Value = ws.Cells(2, C).Value
        Begin.Value = ws.Cells(r, 2).Value
    End If
    Next r
End If
Next C


End Sub

[Vato0o]

Beste Paul,

Allereerst bedankt voor je snelle reactie, ik zal het proberen te verduidelijken aangezien de afbeelding niet duidelijk genoeg is. Ik heb even snel een matrix bijgevoegd hieronder.

De waardes die ik nodig heb staan in de eerste rij (de bovenste rij) deze loopt van 1 tot 6 en de eerste kolom ( de meest linkse kolom), loopt ook van 1 tot 6.
0 1 2 3 4 5 6
1 2 2 9 0 2 1
2 5 32 46 2 2
3 7 7 7 0 2
4 10 4 6 6 3 0
5 11 2 1 2 3 2
6 1 32 2 1 2

voorbeeld:
Dus stel mijn max waarde in de matrix is 46, dan wil ik dat ik de volgende waardes krijg: De waarde in rij1= 3, de waarde in kolom 1 = 2.
Nu lopen de waardes in bovenstaande tabel van de eerste rij en kolom van 1 tot 6, echter kan deze worden aangepast tot een maximale afstand van 130 en zal de plaats waarde maximale waarde staat ook veranderen.

Daarnaast geef je goed aan dat het onduidelijk is of deze waarde één keer voorkomt: deze Minimale óf Maximale waarde kan MAXIMAAL twee keer voorkomen.

Ik heb geen ervaring met VBA, indien boven geschreven script nog steeds goed toepasbaar is dan ga ik hiermee bezig.

Alvast bedankt!

[Lustucru]

De makkelijkste manier is een hulpkolom of hulprij waarin je met aantal.als() bepaalt of de gezochte waarde in een rij resp kolom voorkomt. Vergelijken op >0 doet de rest.

Het kan ook in een keer met een matrixformule. Met iets als

code:

{=som(als((a1:g10)=999;rij(a1:g10);0))}

haal je de rij op waar 999 voorkomt in de range a1g10. Komt de waarde vaker voor werk dan met grootste() ipv som.

[Vato0o]

Bedankt voor je reactie, maar onderstaande functie werkt niet:
{=som(als((a1:g10)=999;rij(a1:g10);0))}

Moet: rij(a1:g10) niet aangeven van welke rij ik de waarde wil hebben, in dit geval rij 1?
999 moet ik vervangen door de maximale waarde?

"som" wordt vervangen door "grootste" want de maximale / minimale waarde KAN 2 keer voorkomen.

[jeanj]

Je kan de functie vergelijken (NL) of match gebruiken.

Voor elke rij kan je met deze functie in die rij zoeken of de waarde voorkomt en op welke kolom. Komt deze waarde meerder keren voor, krijg je meerdere kolom nummers, 1 per rij.
Doe hetzelfde voor de kolommen, en je vind de rij.

Als het niet wordt gevonden geeft de functie #N/B, dat kan je afvangen met de constructie als(niet(isnb(vergelijking));vergelijking;""). (dit kan efficiënter )

Dus als ik jou voorbeeld matrix in A1:G7 zet, dan zet ik in $K$1 de zoek max waarde 46 neer en in i2 =ALS(NIET(ISNB(VERGELIJKEN($K$1;A2:G2;0)));VERGELIJKEN($K$1;A2:G2;0);""). Dit trek je door naar i2:i7

NB dit kan efficiënter door ALS.FOUT(VERGELIJKEN($K$1;A1:G1;0);"")

De uitkomst is 4, of te wel kolom D (de schaal begint bij 1, als je een schaal vanaf 0 wilt, trek er 1 af).
(NB de getallen zijn relatief ten opzicht van de kolom waar je in zoekt, zoek je niet vanuit A1, krijg je getallen relatief daaraan)

Doe dit zelfde truukje voor de rijen.
B9 =ALS(NIET(ISNB(VERGELIJKEN($K$1;B1:B7;0)));VERGELIJKEN($K$1;B1:B7;0);"") en door trekken naar G9. Uitkomst 3

Zet naast I2 in J2 =ALS(I2<>"";RIJ();""). Idem voor J3:J7
Voor B10 =ALS(B9<>"";KOLOM();""). Idem voor C10:G10

Dan heb je in alle situaties de locaties, alleen het staat op twee verschillende plekken, namelijk als de waarde dubbel voorkomt in een rij of kolom staat het op een andere plek, zie de voorbeelden beneden

Met 1 waarde krijg je dit kolom 4, rij 3 (je moet er 1 aftrekken om op de waarde uit de eerste rij/kolom ui te komen)

0	1	2	3	4	5	6				46
1	2	2	9	0	2	1
2	5	32	46	2	2			4	3
3	7	7	7	0	2
4	10	4	6	6	3	0
5	11	2	1	2	3	2
6	1	32	2	1	2
			3
			4

En met twee waarden kolom 5, rij 2 en kolom 4 en rij 3

0	1	2	3	4	5	6				46
1	2	2	9	46	2	1		5	2
2	5	32	46	2	2			4	3
3	7	7	7	0	2
4	10	4	6	6	3	0
5	11	2	1	2	3	2
6	1	32	2	1	2
			3	2
			4	5

En met twee waarden waarvan 1 in dezelfde kolom (in het voorbeeld kolom D de 4 de kolom)

0	1	2	3	4	5	6				46
1	2	2	9	0	2	1
2	5	32	46	2	2			4	3
3	7	7	7	0	2
4	10	4	6	6	3	0
5	11	2	46	2	3	2		4	6
6	1	32	2	1	2
			3
			4

Twee waarden in de zelfde rij (3de rij)

0	1	2	3	4	5	6				46
1	2	2	9	0	2	1
2	5	32	46	2	46			4	3
3	7	7	7	0	2
4	10	4	6	6	3	0
5	11	2	1	2	3	2
6	1	32	2	1	2
			3		3
			4		6

Je ziet dat in de verschillende gevallen de waarden op andere plekken staan

Met deze tip kan de lege cellen opschonen . Verder voor het samen oegen van beide situatie kan je een controle doen op de I kolom en de 9de rij. Als dit 1 en 2 is, moet je niet de I en J kolom hebben maar de 9de en 10de rij (die word opgeschoond)

Dus in de cellen
l1: =ALS(EN(AANTAL(A9:G9)=2;AANTAL(I1:I7)=1);ALS.FOUT(INDEX($A$10:$G$10;AGGREGAAT(15;6;(KOLOM($A$10:$G$10)-KOLOM($A$10)+1)/($A$10:$G$10<>"");RIJEN(L$1:L1)));"");ALS.FOUT(INDEX($I$1:$I$7;AGGREGAAT(15;6;(RIJ($I$1:$I$7)-RIJ($I$1)+1)/($I$1:$I$7<>"");RIJEN(L$1:L1)));""))

l2: =ALS(EN(AANTAL(A9:G9)=2;AANTAL(I1:I7)=1);ALS.FOUT(INDEX($A$10:$G$10;AGGREGAAT(15;6;(KOLOM($A$10:$G$10)-KOLOM($A$10)+1)/($A$10:$G$10<>"");RIJEN(L$1:L2)));"");ALS.FOUT(INDEX($I$1:$I$7;AGGREGAAT(15;6;(RIJ($I$1:$I$7)-RIJ($I$1)+1)/($I$1:$I$7<>"");RIJEN(L$1:L2)));""))

M1: =ALS(EN(AANTAL(A9:G9)=2;AANTAL(I1:I7)=1);ALS.FOUT(INDEX($A$9:$G$9;AGGREGAAT(15;6;(KOLOM($A$9:$G$9)-KOLOM($A$9)+1)/($A$9:$G$9<>"");RIJEN(M$1:M1)));"");ALS.FOUT(INDEX($J$1:$J$7;AGGREGAAT(15;6;(RIJ($J$1:$J$7)-RIJ($J$1)+1)/($J$1:$J$7<>"");RIJEN(M$1:M1)));""))

m2: =ALS(EN(AANTAL(A9:G9)=2;AANTAL(I1:I7)=1);ALS.FOUT(INDEX($A$9:$G$9;AGGREGAAT(15;6;(KOLOM($A$9:$G$9)-KOLOM($A$9)+1)/($A$9:$G$9<>"");RIJEN(M$1:M2)));"");ALS.FOUT(INDEX($J$1:$J$7;AGGREGAAT(15;6;(RIJ($J$1:$J$7)-RIJ($J$1)+1)/($J$1:$J$7<>"");RIJEN(M$1:M2)));""))

en je krijgt in kolom rij formaat op schaal 1...X in kolom L en M de gevraagde coördinaten, hieronder twee voorbeelden

0	1	2	3	4	5	6				46	4	3
1	2	2	9	0	2	1					4	5
2	5	32	46	2	2			4	3
3	7	7	7	0	2
4	10	4	46	6	3	0		4	5
5	11	2	1	2	3	2
6	1	32	2	1	2
			3
			4

0	1	2	3	4	5	6				46	4	3
1	2	2	9	0	2	1
2	5	32	46	2	2			4	3
3	7	7	7	0	2
4	10	4	6	6	3	0
5	11	2	1	2	3	2
6	1	32	2	1	2
			3
			4

(Dezelfde exercitie kan je herhalen voor de min waarde)

Overigens het voorbeeld heeft meer dan twee keer een min waarde (0)!

[ Voor 163% gewijzigd door jeanj op 01-05-2018 13:02 ]

[Lustucru]

jeanj schreef op dinsdag 1 mei 2018 @ 10:36:
hulpkolommen, hulprijen en lange formules op het antwoord op één plek te krijgen

Met alle respect voor de uitgebreidheid van je antwoord en de uitwerking, maar dit lijkt me een scenario waarin de kracht van matrixformules zichtbaar worden. @Vato0o kan dan beweren dat het niet werkt, het werkt als een tierelier als je het volgende bedenkt: en de moeite neemt om even op te zoeken hoe je een matrixformule invoert.

* elke cel kun je een unieke aanduiding geven door het rijnummer te vermenigvuldigen met een getal wat groter is dan het aantal kolommen en daar het kolomnummer bij op te tellen.
* Met een matrixformule bouw je een matrix op van cel-aanduidingen waarin het gezochte getal voorkomt, bv
{=($B$2:$E$5=$G$1)*(1000*RIJ($B$2:$E$5)+KOLOM($B$2:$E$5))}
resulteert in een matrix van celaanduidingen in het bereik B2:E5 waarin de waarde van G1 staat.
* Omdat de gezochte waarde meerdere keren kan voorkomen haal je met grootste de opvolgende waarden eruit.
{=GROOTSTE(($B$2:$E$5=$G$1)*(1000*RIJ($B$2:$E$5)+KOLOM($B$2:$E$5));1)}
* de celaanduiding kun je met delen door en module weer omzetten naar rij- resp. kolomnummers
* met het juiste rij- of kolomnummer haal je met index de waarde uit de rij- of kolomtitel.

In één keer:

code:

{=INDEX($A$1:$A$5;(GROOTSTE(($B$2:$E$5=$G$1)*(1000*RIJ($B$2:$E$5)+KOLOM($B$2:$E$5));1)/1000))}

[ Voor 4% gewijzigd door Lustucru op 02-05-2018 13:02 ]

[jeanj]

Dit is heel mooi, en kan je gebruiken om alles op te zoeken ongeacht het aantal wat er is.
Ik gebruik ze bijna niet, dus vandaar dat ik er niet aan dacht.

Even twee vragen
- Zou dit ook kunnen als je de grijze hulp rijen niet hebt? Ze staan er nu bij als hulp.
- En die formules in H1:I2 zijn matrix formules per cel? Zo niet, als het 1 formule is voor die 4 cellen, hoe krijg je die 2 in H2 bij grootste?


Voor de mensen die het antwoord niet begrijpen
- Grootste heeft een parameter die de aangeeft welke van de waarden je wilt hebben, voor H1 is de waarde 1, voor H2 waarde 2 etc. Je kan in dit voorbeeld ook Rij() gebruiken, zo kan je de formule makkelijk doortrekken naar H2 en verder....
- In I1 doe je de index over de eerste rij, en neem de rest van de deling zoals gezegd om die waarde op te zoeken

code:

{=INDEX($A$1:$e$1;(REST(GROOTSTE(($B$2:$e$5=$K$1)*(1000*RIJ($B$2:$e$5)+KOLOM($B$2:$e$5));1);1000)))}

En i2 idem maar met een 2 bij grootste. Of gebruik hiervoor ook rij().
- De matrix formule {} krijg je door cntrl-shift enter te doen bij de invoer van de formule in een of meer cellen (je kan er meerder selecteren)

[ Voor 14% gewijzigd door jeanj op 02-05-2018 13:59 ]

[Lustucru]

jeanj schreef op woensdag 2 mei 2018 @ 13:48:
Even twee vragen
- Zou dit ook kunnen als je de grijze hulp rijen niet hebt? Ze staan er nu bij als hulp.
- En die formules in H1:I2 zijn matrix formules per cel? Zo niet, als het 1 formule is voor die 4 cellen, hoe krijg je die 2 in H2 bij grootste?

Die grijze cellen zijn de titels die de TS zocht. Daar kan dus van alles staan, ook tekst of wat dan ook. Als je alleen rij- en kolomnummer hebt laat je ze weg, evenals de index.

Het zijn nu vier losse formules, omdat je voor het rij nummer de aanduiding deelt door 1000 en voor het kolomnummer aanduiding modules 1000 nodig hebt. Het kan in één formule, als je meteen het eindresultaat ophaalt en de grootste parameter K ophaalt uit rij e/ kolom. Maar dat komt de leesbaarheid niet ten goede.