Maximum zoeken in combinatie van tabellen

[RoosT]

Allen,

Ik loop al tijdje te worstelen met wat volgens mij gewoon moet kunnen, maar mijn excel-kunde reikt blijkbaar niet zo ver.
Dus ik heb me over de schaamte drempel heen gezet en hoop dat één van jullie me kan helpen.

Ik heb 2 tabellen,
tabel 1 meetlocaties x meetplan (sheet 1)
tabel 2 analyses x meetplan (sheet 2)

Nu wil ik er een andere tabel uitkrijgen waarbij ik het maximale aantal van de analyses per meetlocatie wil weten. (sheet 3)
Ik kom er niet uit!!! (per cel lukt het wel, maar in werkelijkheid gaat het om duizenden locaties en 100 meetplannen..)

Wie o Wie?

 

[Thankyou]

RoosT,

Als eerste welkom op dit forum.
Schamen voor vragen hoeft hier niet, schamen voor niet vragen misschien wel.

Ik hoop dat je hier wat mee kunt op blad3 B2:

=MAX(SOMPRODUCT((Blad1!$A$2:$A$8=$A2)*(Blad1!$B$2:$E$8));SOMPRODUCT((Blad2!$A$2:$A$7=B$1)*(Blad2!$B$2:$E$7)))

Succes,
Martin

 

[ExcelMark]

Welk maximum wil je precies hebben? Ik heb hier jou voorbeeldprobleem voor me, maar snap niet helemaal hoezo je er nou 2 tabellen voor hebt met verschillende kolomtitels.

Zou bij 'Locatie 1' en 'Metalen' in Blad3 het maximum moeten komen te staan van alle getallen die je hebt staan in de eerste tabel (Blad1) in de rij van 'Locatie 1' (1;1;0;0) en die van 'Metalen' (Blad2) (12;4;6;0)? Met dus als antwoord 12 in dit geval? Of hebben die 1en en 0len nog iets met die 12;4;6 en 0 te maken? Aangezien in je eerste tabel alleen maar 1tjes staan...

 

[RoosT]

Welk maximum wil je precies hebben? Ik heb hier jou voorbeeldprobleem voor me, maar snap niet helemaal hoezo je er nou 2 tabellen voor hebt met verschillende kolomtitels.

Zou bij 'Locatie 1' en 'Metalen' in Blad3 het maximum moeten komen te staan van alle getallen die je hebt staan in de eerste tabel (Blad1) in de rij van 'Locatie 1' (1;1;0;0) en die van 'Metalen' (Blad2) (12;4;6;0)? Met dus als antwoord 12 in dit geval? Of hebben die 1en en 0len nog iets met die 12;4;6 en 0 te maken? Aangezien in je eerste tabel alleen maar 1tjes staan...

Het ééntje op blad 1 geeft aan of er gemeten moet worden op die locatie voor het betreffende meetplan.
Op blad 2 geef je aan hoe vaak per jaar de analyse gedaan moet worden per jaar (over het algemeen 1 tot 24).

Nu moet ik weten hoe vaak een locatie per jaar bezocht moet worden (als er voor een meetplan 4x per jaar iets geanalyseerd moet worden en voor de andere 12, moet er dus 12 uitkomen)

 

[RoosT]

RoosT,

Als eerste welkom op dit forum.
Schamen voor vragen hoeft hier niet, schamen voor niet vragen misschien wel.

Ik hoop dat je hier wat mee kunt op blad3 B2:

=MAX(SOMPRODUCT((Blad1!$A$2:$A$8=$A2)*(Blad1!$B$2:$E$8));SOMPRODUCT((Blad2!$A$2:$A$7=B$1)*(Blad2!$B$2:$E$7)))

Succes,
Martin

Bedankt!
Helaas krijg ik hiermee volgens mij alleen de gesommeerde aantallen van de analyses.
Kun je mij vertellen wat de =$A2 doet in het definiëren van je matrix? Ik snap dat niet helemaal...

 

[Thankyou]

RoosT,

Hierbij een nieuwe poging, ik ben er vanuitgegaan dat als er op blad1 er minimaal1 meetplan is er dan gekeken moet worden wat het maximale (of grootste ) getal is op blad2 bij de betreffende metalen, beestjes etc.

=ALS(SOMPRODUCT((Blad1!$A$2:$A$8=$A2)*(Blad1!$B$2:$E$8))>0;MAX(INDIRECT("Blad2!$B$"&VERGELIJKEN(B$1;Blad2!$A$1:$A$7;0)&":$E$"&VERGELIJKEN(B$1;Blad2!$A$1:$A$7;0)));"")

Als het dan ook nog vermenigvuldig moet worden met het aantal metingen op de lokatie dan wordt het:

=ALS(SOMPRODUCT((Blad1!$A$2:$A$8=$A2)*(Blad1!$B$2:$E$8))>0;MAX(INDIRECT("Blad2!$B$"&VERGELIJKEN(B$1;Blad2!$A$1:$A$7;0)&":$E$"&VERGELIJKEN(B$1;Blad2!$A$1:$A$7;0)))*SOMPRODUCT((Blad1!$A$2:$A$8=$A2)*(Blad1!$B$2:$E$8));"")

Succes,
Martin

P.S.
Die A2 gaf aan dat er gezocht moest worden naar de info die in de betreffende cel staat (lokatie1) de $zet in dit geval de kolom vast bij het slepen.

 

[RoosT]

Hallo Allemaal,

Het is inmiddels een tijd verder en ik ben er nog niet uit. (ik heb het eerst handmatig gedaan, maar dat is niet efficient).

nogmaals de probleemstelling:
Blad 1: meetplannen (kolommen) x meetlocaties (rijen)
Er staat een 1 als er voor het betreffende meetprogramma gemeten moet worden.

Blad 2: meetplannen (kolommen, dezelfde als blad 1) x parameters (rijen)
Er staat een getal, wat het aantal keer per jaar weergeeft hoe vaak de betreffende parameter voor het betreffend meetplan gemeten moet worden.

Blad 3: Ik moet weten hoe vaak een parameter maximaal op een locatie gemeten moet worden. Dus ik moet het maximale hebben van de parameter, per locatie.
Volgens mij moet ik eerst zoeken welke parameters in totaal voor de locaties gemeten moeten worden. En daaruit moet ik de maximale selecteren.

Het resultaat wat het moet zijn, staat in een tabel in blad 3. Hopelijk kan er nog iemand met me meedenken. Mijn dank is bij voorbaat groot...
NB. Het advies wat ik vorige keer heb gekregen staat nu in de te maken tabel, maar hij maakt nog geen selectie tussen of een parameter wel of niet op die locatie gemeten moet worden.

 

[Thankyou]

Hallo weer RoosT,

Wat ik nog niet begrijp is het volgende
bij elke lokatie is er 2x per jaar een meting, bij metalen : diertjes komen allemaal getallen in de matrix voor, wat is dan het criterium om op blad 3 wel of niet te tellen?

Martin

 

[RoosT]

Hallo weer RoosT,

Wat ik nog niet begrijp is het volgende
bij elke lokatie is er 2x per jaar een meting, bij metalen : diertjes komen allemaal getallen in de matrix voor, wat is dan het criterium om op blad 3 wel of niet te tellen?

Martin

Hallo Martin,

Het aantal keer dat een analyse gedaan wordt, verschilt per meetplan.

bv. metalen op meetlocatie 1. Dan moet ik eerst zoeken naar de meetplannen waarvoor meetlocatie1 bezocht wordt. En dan kijken of er in de betreffende meetplannen metalen gemeten moeten worden en wat het maximale aantal.

Snap je het zo??? (lastig uit te leggen, zeg)

 

[Thankyou]

lastig uit te leggen, zeg

Of lastig te snappen.

Als ik naar lokatie1 en 4 kijk, meting1 =1, dan kom ik op blad 2 bij metalen 12 en bij planten 1 tegen, dat vind ik ook terug in het overzicht. Meting 2 kom ik niet tegen in het overzicht, waarom niet.
Bij bijvoorbeeld lokatie 2 is er geen meting1 wel een meting2, de waarden daarvan zijn bij blad2 4 metalen en 4 beestjes terwijl het overzicht zegt 6 metalen, 12 bestrijdingsmiddelen en 3 plantjes. etc. etc.

Als ik een formule voor je wil bouwen zal ik moeten snappen hoe, wat en waarom.
Ik hoop dat ik mijn vraag duidelijk omschreven heb voor je

Succes.
Martin

 

[RoosT]

Of lastig te snappen.

Als ik naar lokatie1 en 4 kijk, meting1 =1, dan kom ik op blad 2 bij metalen 12 en bij planten 1 tegen, dat vind ik ook terug in het overzicht. Meting 2 kom ik niet tegen in het overzicht, waarom niet.
Bij bijvoorbeeld lokatie 2 is er geen meting1 wel een meting2, de waarden daarvan zijn bij blad2 4 metalen en 4 beestjes terwijl het overzicht zegt 6 metalen, 12 bestrijdingsmiddelen en 3 plantjes. etc. etc.

Als ik een formule voor je wil bouwen zal ik moeten snappen hoe, wat en waarom.
Ik hoop dat ik mijn vraag duidelijk omschreven heb voor je

Succes.
Martin

Praktijk voorbeeld werkt misschien beter?

Wet x (meetplan 1) zegt mij dat ik 12 x per jaar metalen en 1x plantjes moet meten op locatie 1 en 4

Wet y (meetplan 2) zegt mij dat ik 4x metalen en 4x beestjes moet meten op locatie 1 t/m 7

Wet z (meetplan 3) zegt mij dat ik 6x metalen, 12 x bestrijdingsmiddelen, en 3x plantjes moet meten op locaties 2 en 3.

Wet ZZ (meetplan 4) zegt mij dat ik geen analyses hoef te doen op locaties 5,6 en7. [slecht voorbeeld dus]

Uit deze gegevens haal je dat ik maximaal 12x metalen op locatie 1 moet meten, max 6x metalen op locatie2 en locatie 3, max 12x metalen op locaties 4, en max 4x op locaties 5-7. [= eerste kolom voorbeeldresultaat]

ps. ik ben nu naar bed, maar hoop dat je het begrijpt!!!!

 

[Thankyou]

RoosT,

Sorry dat hetr even duurde, maar ja onze vergaderingen vallen 's avonds na werktijd.
Kijk eens of je hiermee uit de voeten kan?
Ik heb 1 hulprij gebruikt en een matrix formule, deze afsluiten met CTRL+SHIFT+ENTER.

Succes,
Martin

 

[RoosT]

Volgens mij werkt ie!
Geweldig!

Ik ga dit weekend eens uitpluizen wat er precies gebeurt in de formule, want ik wil dit zelf ook kunnen verzinnen

 

[Thankyou]

Zo moeilijk is hij niet

{=INDEX(Blad2!$B$3:$E$6;VERGELIJKEN(B$1;Blad2!$A$3:$A$6;0);VERGELIJKEN(MAX(Blad1!$B2:$E2*Blad2!$B$1:$E$1);Blad2!$B$1:$E$1;0))}

Het is een gewone index/vergelijken formule, het enige wat er een matrix formule van maakt is alle 1-en op blad 1 vermenigvuldigen met de som van meting 1 t/m 4 en dan kijken wat de hoogste is dmv MAX ( MAX(Blad1!$B2:$E2*Blad2!$B$1:$E$1) )

Succes met uitpluizen.
Martin

 

[Gotty]

Volgens mij werkt ie!
Geweldig!

Ik ga dit weekend eens uitpluizen wat er precies gebeurt in de formule, want ik wil dit zelf ook kunnen verzinnen

Als je het zeker weet zeg het dan even als je wil, dan ga ik er ook wat beter naar kijken, want volgens mij is hij voor mij te doen.