Cel "adres" bepalen van een getal uit een matrix

[steven77]

Wie kan mij helpen met het volgende probleem

Ik heb in excel een aantal rijen en kolommen. In het voorbeeld gebruik ik 5 rijen en drie kolommen. Alle cellen hierin bevatten getallen. Van de complete set wil ik uiteindelijk het grootste getal bepalen en van dit getal ben ik op zoek naar de celverwijzing. In het onderstaande voorbeeld zou ik dan op zoek zijn naar $C$5. Hoe kan ik dit voor elkaar krijgen?
De grootste bepalen is het probleem niet (GROOTSTE(A1:C5;1)). Echter werkt grootste niet in combinatie met CEL("adres". Wie weet het antwoord?

01,06,11
02,07,12
03,08,13
04,09,14
05,10,15

Alvast bedankt!

 

[AlexCEL]

Probeer deze eens:

=ADRES(SOMPRODUCT(($A$1:$C$5=MAX($A$1:$C$5))*RIJ($A$1:$C$5));SOMPRODUCT(($A$1:$C$5=MAX($A$1:$C$5))*KOLOM($A$1:$C$5));1;1)

NB dit gaat goed zolang er maar één maximum is (dus geen 2 cellen met 15 in jouw voorbeeld)! Anders moet de formule aangepast worden.

 

[steven77]

Heel erg bedankt. Dit werkt prima!

 

[Cobbe]

Of via deze UDF:

Function MaxAddress(bereik)
  MaxNum = Application.Max(bereik)
    For Each cell In bereik
      If cell = MaxNum Then
        MaxAddress = cell.Address
          Exit For
      End If
    Next cell
End Function

 

[steven77]

Bedankt voor de UDF.

Graag zou ik nog twee aanpassingen willen, indien mogelijk. Het eerste is inderdaad het probleem wanneer er meerdere dezelfde grootste getallen zijn. hoewel statistisch klein, kan dit inderdaad voorkomen. Het liefst zou ik dan de locatie van het grootste getal willen hebben dat het dichtste bij het midden zit.

Ten tweede zou ik de range graag dynamisch willen maken. Een voorbeeld:

In een groot bereik getallen komt zo nu en dan de letter HH voor. Deze letters zijn het middelpunt van een matrix waarbinnen de grootste waarden wordt gezocht. Het bereik waarbinnen gezocht moet worden hangt af van de locatie van HH en van andere factoren waardoor het bereik soms 9 cellen groot is en soms bv 25.

(Het grootste getal wordt bepaald en dat wordt in principe bij een volgende keer dat de formule gestart wordt het nieuwe HH punt.) Hierdoor heb ik in principe ook bij meerdere grootste waarden slechts 1 grootste waarde nodig, welke het dichtst bij het middelpunt ligt.



flexibele waarden die het bereik aangeven waarin de maximale waarden gezocht moet worden welke in andere cellen berekend worden
(bereik welke middels een formule bepaald wordt in een andere cel) = C1:G5


totale bereik
01,02,03,04,05,06,07,08,09,10
01,02,03,04,05,06,07,08,09,10
01,02,20,20,HH,06,07,08,09,10
01,02,03,04,05,06,07,08,50,10
01,02,03,04,05,06,07,08,09,10
01,02,03,04,05,06,07,08,09,10

Resultaat moet dan zijn $D$3

Is dit ook mogelijk?

 

[sylvester-ponte]

ik snap het niet.
als HH het middelpunt is dan dan doet 50 ook mee.
of bedoel je met middelpunt rechts onder?

ps het is handiger voor de helpers als je een voorbeeld_excel bestandje mee stuurt

en wat is dichterbij? hoe tel je de schuine afstanden?
en wat als ze even ver staan?

 

[steven77]

Ik zal het proberen uit te leggen, want ik heb nog geen echt goed voorbeeld waar je in kunt stoeien (work in progress ).

Het totale bereik van getallen is grofweg 2000 cellen lang en 250 cellen breed. In dit bereik zitten op bepaalde intervallen de letters H. Van elke H wordt een sub-bereik middels een functie bepaald. Stel de formule geeft aan dat H1 een sub-bereik heeft dat 7 cellen breed en hoog is, met de H in het midden. Alleen van dit sub-bereik wil ik dan de grootste waarden bepalen, welke het dichtst bij de oorspronkelijke H/midden ligt.

Door de grootste waarde te bepalen en om te zetten in een H, de oude H weg te halen en middels een macro weg te schrijven kan ik met een bepaalde waarschijnlijkheid (welke middels formules bepaald worden) de H over het totale bereik laten bewegen.

In bovenstaande voorbeeld is inderdaad 50 het grootste getal. Echter valt de 50 alleen in het totale bereik en niet in het sub-bereik (C1:G5). De getallen 20 (C3 en D3) vallen wel in het sub-bereik, echter is D3 het dichtst bij het midden van het sub-bereik.

Ik hoop dat dit zo duidelijk is.

 

[sylvester-ponte]

ik had er nog een vraagje bij gezet over schuine afstanden
en wat te doen als de afstanden gelijk zijn
heeft een sub bereik altijd een duidelijk midden ? waar zit het midden in een 5 * 4 matrix?

 

[snb]

Function F_snb(sn As Range)
    F_snb = Application.Index(sn, Application.Match(Application.Max(sn), sn, 0), 1).Address
End Function

 

[Cobbe]

Deze vind ik ook goed.

 

[steven77]

01 02 03 04 05
06 07 08 09 10
11 12 H 13 14
15 16 17 18 19
20 21 22 23 24

Prioriteit:
1.Dichtste bij H gaat voor verder weg (2 en 16 zijn grootste waarde -> 16 eind resultaat)
2 Oostelijke kolom voor Westelijke kolom (14 en 11 zijn grootste waarde -> 14 eind resultaat)
3 Noordelijke rij voor Zuidelijke rij (3 en 22 zijn grootste waarde -> 3 eind resultaat)
4 Westelijke kolom voor Noordelijke rij (14 en 3 zijn grootste waarde -> 3 eind resultaat)
5 Random roll bij uiteindelijke problemen (10 en 19 zijn grootste waarde -> random 50% kans voor een van de twee)

Geen rekening houden met schuine afstanden.

 

[steven77]

Function F_snb(sn As Range)
    F_snb = Application.Index(sn, Application.Match(Application.Max(sn), sn, 0), 1).Address
End Function

Bedankt snb,

Echter ben ik nog niet zo handig met al deze user defined functies dus hoe kan ik deze het beste gebruiken?

 

[sylvester-ponte]

regel 5 lijkt gelijk (Random) maar in regel 3 staat "Noordelijke rij voor Zuidelijke " dat geld hier dan toch ook?

hoe vergelijk je in jouw schema 07 met 18 ?

 

[sylvester-ponte]

alle cel_waarden behalve cel 33 zijn gelijk. de cijfertjes zijn slechts coördinaten.

11 12 13 14 15
21 22 23 24 25
31 32 hh 34 35
41 42 43 44 45
51 52 53 54 55

Steven, geef nu eens een lijst van al deze cellen van groot naar klein .

 

[steven77]

regel 5 lijkt gelijk (Random) maar in regel 3 staat "Noordelijke rij voor Zuidelijke " dat geld hier dan toch ook?

hoe vergelijk je in jouw schema 07 met 18 ?

Helemaal correct foutje...

Hier de prioriteiten volgorde:
23, 19, 15, 17, 21
13, 05, 07, 02, 10
12, 04, h , 01, 09
14, 06, 08, 03, 11
24, 20, 16, 18, 22

 

[sylvester-ponte]

Steven, wil je post 14 ook beantwoorden?

graag in een excel bestand

 

[steven77]

Bijgevoegd een concept in Excel. Helaas wel in zip vanwege de grootte.

Hoewel ik zie dat mijn prioriteiten getallen niet helemaal kloppen met mijn regels (ik ben niet echt consequent ) vind ik dat niet het grootste probleem. Op het laatste tabblad de volgorde zoals ik die graag zou zien. Indien een variant makkelijker te bereiken is door middel van formules is dat eigenlijk ook prima. De probability table zal er voor zorgen dat de kans relatief klein is dat er identieke getallen komen en sturing geven waar ik de H's naar toe wil laten gaan.

Tabblad 1 geeft de kleurcodes die van invloed zijn op de probability table in tabblad 2. Van tabel 1 in the probability table worden alleen de waarde gekopieerd dmv een macro naar tabel 2 in hetzelfde tabblad, welke fixatie geeft van de waarden. Deze waarden worden in het tabblad kaart gebruikt om de grootste waarden te bepalen. Het tabblad datum bepaalt in eerste instantie door middel van een datum check (niet bijgevoegd) de locatie van de eerste H, welke vervolgens te zien zijn in het tabblad kaart.

Door uiteindelijk in tabblad kaart de waarden in AO6:AO15 te kopieren naar AN6:AN15 verschuiven de H's. Dit zal via een macro gedaan worden

* Het probleem nu is alleen dat het bereik van de formules in AO6:AO15 dynamisch moet worden om deze verschuiving van bereiken aan te passen.
* Het probleem is dat de kaart circulair is. Het bereik van h1 moet daarom verder gaan in kolom 35 en 36
* Het probleem met dubbele waarden

Hopelijk kunnen jullie me wat verder helpen !

 

[steven77]

Het dynamische bereik heb ik nu opgelost volgens mij

=ALS(AO7="";"";ADRES(SOMPRODUCT((VERSCHUIVING(INDIRECT($AN7);-((datum!$W3-1)/2);-((datum!$V3-1)/2);datum!$W3;datum!$V3)=MAX(VERSCHUIVING(INDIRECT($AN7);-((datum!$W3-1)/2);-((datum!$V3-1)/2);datum!$W3;datum!$V3)))*RIJ(VERSCHUIVING(INDIRECT($AN7);-((datum!$W3-1)/2);-((datum!$V3-1)/2);datum!$W3;datum!$V3)));SOMPRODUCT((VERSCHUIVING(INDIRECT($AN7);-((datum!$W3-1)/2);-((datum!$V3-1)/2);datum!$W3;datum!$V3)=MAX(VERSCHUIVING(INDIRECT($AN7);-((datum!$W3-1)/2);-((datum!$V3-1)/2);datum!$W3;datum!$V3)))*KOLOM(VERSCHUIVING(INDIRECT($AN7);-((datum!$W3-1)/2);-((datum!$V3-1)/2);datum!$W3;datum!$V3)));1;1))

 

[steven77]

Door het splitsen van het bereik links en rechts van de H en de bovenstaande verschuiving door te passen heb ik het dynamische bereik circulair gemaakt. Het enige probleem is nu hoe ik dubbele grootste waarden kan selecteren via een voorgekozen prioriteiten lijst, zodat ik toch met een enkele grootste waarde kan komen

Iemand een idee?

 

[steven77]

iemand nog een idee hoe je toch de grootste kunt bepalen in een bereik als er meerdere grootste waarden zijn?