[Excel] Het grote Excel vragen topic #39

Oké, ik loop tegen het volgende aan:

Ik heb een tabel met één x-as en 10 datasets. De data op de y-assen zijn ongeveer gelijk (maar zeker niet precies). Het geheel ziet er dus als volgt uit:


Daarvan wil ik graag dat de gebruiker een x-waarde kan invullen (hoeft niet gelijk te vallen met de x-waarden in de tabel) en dat dan de mediaan van S1 tot S10 wordt berekend. Op dit moment heb ik het opgelost door naast "S10" nog een kolom met "mediaan" (absolute kolom M) te maken met daarin de formule =MEDIAN(B2:K2) en deze dan met de vulgreep naar beneden te trekken zodat ik voor elke rij een nieuwe mediaan heb van de punten. Vervolgens doe ik dan dit:
Dit werkt gewoon prima. Maar nu heb ik dus een kolom met loze data behalve één punt die bij elke bewerking in die sheet allemaal herberekend worden. Eigenlijk wil ik dus dit zonder deze omweg doen, en ik een array-functie zetten, dus ik hoopte hiermee weg te komen:
(dus met ctrl+shift+enter gedrukt)

Maarja, dat was een beetje ijdele hoop. Snapt iemand wat ik wil en heeft die een idee om het werkend te krijgen? Ik wil later er nog de forecast functie overheensmijten om het e.e.a. preciezer te maken.

Edit: Het weghalen van de row number (de ; 1) op het laatst in de index functie was denk ik wel nodig, maar leverde niets op.

[ Bericht 2% gewijzigd door Watertornado op 19-01-2014 22:11:28 ]

quote:

Op zondag 19 januari 2014 21:50 schreef Watertornado het volgende:
Oké, ik loop tegen het volgende aan:

Ik zit even te zoeken of je nu de Nederlandse of Engelse hebt.
Zelf zou ik gebruik maken van INDIRECT
=MEDIAAN(INDIRECT("B"&1+VERGELIJKEN(X1;A2:A101)&":K"&1+VERGELIJKEN(X1;A2:A101))

Hier heb ik je cel met je zoekwaarde naar x ook in de cel x1 gezet
(VERGELIJKEN = MATCH in het Engels)

quote:

Op zondag 19 januari 2014 22:35 schreef snabbi het volgende:

[..]

Ik zit even te zoeken of je nu de Nederlandse of Engelse hebt.
Zelf zou ik gebruik maken van INDIRECT
=MEDIAAN(INDIRECT("B"&1+VERGELIJKEN(X1;A2:A101)&":K"&1+VERGELIJKEN(X1;A2:A101))

Hier heb ik je cel met je zoekwaarde naar x ook in de cel x1 gezet
(VERGELIJKEN = MATCH in het Engels)

Ik heb de Engelse Excel (2007).

De indirect functie heb ik nog nooit gebruikt; ik zal eens kijken of ik jouw formule kan ontleden/begrijpen. Want zo te zien "plak" je (met &) cellocaties aan elkaar.

Edit: oké, ik begrijp het. Ik vind het een slimme oplossing. Alhoewel het een hele kluwen van code is (in het "echie" verwijst het ook nog eens naar andere tabbladen, dus het wordt al snel heel druk) is het eigenlijk verrassend simpel.

[ Bericht 11% gewijzigd door Watertornado op 19-01-2014 22:57:04 ]

Het is natuurlijk simpel te maken wanneer je tussenresultaat wegschrijft. Dan voorkom je in ieder geval het dubbele aspect. Aangezien je toch alles ineen wilde toch maar zo gedaan

Vraagje: ik heb een excel document met meerdere hyperlinks (naar afbeeldingen). Kan ik nu ook automatisch die afbeeldingen meeprinten? Want ik wil dat de afbeeldingen niet te zien zijn in het document vanwege de onoverzichtelijkheid.

wellicht als je de afbeeldingen in een opmerking plaatst en de opmerkingen uitprint?

Hieronde een functie die via hyperlinks de afbeelding in een opmerking plaatst.

=InsertCI("Hier een tekst";P2)

Had ik toevallig zelf nodig afgelopen week.

Ander hyperlink probleempje.

Ik heb een aantal hyperlinks gemaakt naar verschillende bestanden op een netwerkschijf. In totaal 5 hyperlinks. In eerste instantie werkten ze alle 5. Maar ineens krijg ik er bij 2 een melding: het opgegeven bestand kan niet worden geopend.

Ik snap er niks van omdat het een zelfde bestand is als de andere (pdf) en in eerste instantie werkte het gewoon. Ik heb de links nu al een paar keer verwijderd en opnieuw gemaakt, maar steeds hetzelfde probleem. Bestanden zijn ook niet veranderd van locatie ofzo.... iemand bekend met dit probleem?

quote:

Op dinsdag 21 januari 2014 10:38 schreef qu63 het volgende:

[..]

Foutje met de aanhalingstekens? Spaties? Rare tekens?

Yup dat was het. Ik heb alles maar hernoemt en nu doet ie het weer.

Ik heb de volgende vraag, ik heb een bak met data die met weken en hoofdafdelingen, afdelingen en afdelingen is gevuld.
Op een ander blad heb ik een overzicht/rapport gemaakt.
In dit overzicht kan de gebruiker kiezen welke Hoofdafdeling hij wilt zien Als je in cel B2 voor Hoofdafdeling kiest, dan worden automatisch de bijbehorende afdelingen en subafdelingen getoond.
Ook kan er een week gekozen worden. In cel B3 dus.
Nu heb ik door middel van het gebruik van naam en verschuiving de kolom en het weeknummer variabel gekregen, maar kan je ook zonder naam de gekozen kolom variabel krijgen?
Want nu moet ik gebruik maken van hulpcellen, die staan in B1:D1, in cel A1 staat de formule =MAX(B1:D1) zo weet ik in welke kolom de gekozen waarde staat. De naam kijkt dus naar cel A1 en weet zo welke kolom hij moet hebben.
De bedoeling is dat door som.als van de gekozen week de juiste getallen worden opgeteld. De uitkomsten worden automatisch getoond zoals te zien in J4:W5.
In dit voorbeeld moet som.als dus week 2 (cel B3) en hoofdafdeling LH (cel B2) worden gezocht met alle bijbehorende afdelingen en subafdelingen.

De formule voor cel K5 ziet er op dit ogenblik zo uit =som.als(KolomHoofd:J5:week) voor de hoofdafdeling weet de naam KolomHoofd dus dat in kolom 2 de gezochte waarde staat, week weet dus dat in week 2 gezocht moet worden.
Voor M5 is de formule =som.als(KolomL:L5:week) maar voor die formule maak ik weer gebruik van andere hulpcellen (die hier niet te zien zijn) die de kolom bepalen voor deze cel.
Zo moet ik dus voor elk getal een naam aanmaken.

Want ik weet niet of in M4 een afdeling of subafdeling komt, dus of het getal in M5 naar een afdeling of subafdeling moet zoeken.
Want als er gekozen wordt voor hoofdafeling LG komt er op M4 en M5 een subafdeling, dus een andere kolom dan de afdeling.
Nu mijn vraag, kan ik in plaats van gebruik te maken van namenzoals kolomHoofd en KolomL (die dus nu een formule met verschuiving bevat) vervangen door een (1) formule?
Ik hoop dat ik niet teveel heb neergezet, maar ik probeer zo goed mogelijk te omschrijven wat ik zoek.
Alvast bedankt.

Is dit niet meer iets om te regelen met een draaitabel?

sommen.als lijkt me voldoende
* draaitabellen leveren veel inzicht, maar eisen ook meer kennis van de gebruiker. Zeker wanneer de poster het ook moet delen met anderen lijkt een formule een betere oplossing.

quote:

Op donderdag 23 januari 2014 21:47 schreef snabbi het volgende:
sommen.als lijkt me voldoende

Het probleem zit 'm in rij 5 die niet constant is.

quote:

Op donderdag 23 januari 2014 21:41 schreef Janneke141 het volgende:
Is dit niet meer iets om te regelen met een draaitabel?

Draaitabel is niet de bedoeling, het gaat om veel meer gegevens dan dit.
Ik heb op een andere pagina allemaal rapporten gemaakt met een standaard lay-out. De gegevens die ik in J4:W5 heb gezet, staan dus op een ander tabblad. Daar staan nog veel meer gegevens, rooster uren, ziekte uren, verlof uren, diverse soorten werkaanbod en ga zo maar door.
Een gebruiker moet niet met draaitabellen werken, ze moeten een weeknummer en een hoofdafdeling ingeven dan moet er een rapport gevuld worden wat ze snel moeten kunnen lezen.
Met een draaitabel is dat allemaal erg lastig.

quote:

Op donderdag 23 januari 2014 21:47 schreef snabbi het volgende:
sommen.als lijkt me voldoende
* draaitabellen leveren veel inzicht, maar eisen ook meer kennis van de gebruiker. Zeker wanneer de poster het ook moet delen met anderen lijkt een formule een betere oplossing.

Ik moet het inderdaad delen met veel andere gebruikers, de meeste weten hoe ze 2 cellen bij elkaar kunnen optellen, maar dat is het dan.
Hoe maak ik in sommen.als dan de kolom variabel?

Edit: Ook oplossingen met VBA mogen niet, niemand snapt dit, anders had ik het allang opgelost. De filosofie is dat er altijd wel iemand te vinden is die een formule kan ontrafelen, maar VBA is vele malen lastiger.

quote:

Op donderdag 23 januari 2014 21:50 schreef Janneke141 het volgende:

[..]

Het probleem zit 'm in rij 5 die niet constant is.

Bijna vergeten, alvast bedankt voor het meedenken, geldt ook voor snabbi natuurlijk.
Dat klopt de getallen in K5, M5 enz enz zijn altijd variabel, daardoor de omschrijving van K4 en M4 ook, maar dat heb ik simpel op kunnen lossen.

Je hebt dus al een manier gevonden om (via een ander blad of weet ik wat) de rijen 4 en 5 vanaf kolom J te vullen?

Dan kun je ervoor kiezen om in K5, M5 etc. een SOM.ALS(B5:B20;J5;week)+SOM.ALS(C5:C20;J5;week)+SOM.ALS(D5:D20;J5;week) te zetten. Het is een beetje lomp (twee van de drie sommen zijn 0) maar het werkt, omdat de codes voor hoofd-, x-, en sub-afdelingen toch allemaal verschillend zijn. Scheelt een hoop gerommel.

-edit-

Volgens mij hoeft dit trouwens niet eens, maar dat moet je even uitproberen.

Als je in K5 het volgende zet:
=SOM.ALS(B5:D20;J5;week) moet het volgens mij ook goedkomen, maar dat moet je even uitproberen.

[ Bericht 23% gewijzigd door Janneke141 op 23-01-2014 22:14:45 ]

quote:

Op donderdag 23 januari 2014 22:03 schreef Janneke141 het volgende:
Je hebt dus al een manier gevonden om (via een ander blad of weet ik wat) de rijen 4 en 5 vanaf kolom J te vullen?

Dan kun je ervoor kiezen om in K5, M5 etc. een SOM.ALS(B5:B20;week)+SOM.ALS(C5:C20;week)+SOM.ALS(D5:D20;week) te zetten. Het is een beetje lomp (twee van de drie sommen zijn 0) maar het werkt, omdat de codes voor hoofd-, x-, en sub-afdelingen toch allemaal verschillend zijn. Scheelt een hoop gerommel.

Klopt, het werkt allemaal wel, maar ik heb het allemaal zo ingewikkeld gemaakt.
Nu je dit zo post, de drie som.als bij elkaar optellen, is inderdaad een beetje lomp, maar wel heel simpel.
Dat ik daar niet eerder aan heb gedacht! Want naast mij hoeft maar 1 persoon dit te onderhouden, dus verder ziet niemand hoe dit is opgelost.
Ik ga hier morgen even mee aan de slag, tss, zou het zo simpel zijn?
Het lijkt mij inderdaad een goede oplossing.
In ieder geval bedankt!
Ik zal je morgen laten weten of het inderdaad de oplossing is.
Mocht er iemand een andere oplossing weten, ik houd me aanbevolen. Altijd leuk om iets nieuws te leren.
Al heb ik nu ook geleerd dat ik niet gelijk te moeilijk moet denken.

-edit-

Ik zag je edit, ga ik ook proberen. Ook niet aan gedacht, ik zat met verschuiving, vergelijken, zoeken te werken.
Nog een keer bedankt!

Zie edit.

quote:

Op donderdag 23 januari 2014 22:16 schreef Janneke141 het volgende:
Zie edit.

Had ik gezien.

quote:

Op donderdag 23 januari 2014 21:56 schreef Arliator het volgende:

[..]

Ik moet het inderdaad delen met veel andere gebruikers, de meeste weten hoe ze 2 cellen bij elkaar kunnen optellen, maar dat is het dan.
Hoe maak ik in sommen.als dan de kolom variabel?

Edit: Ook oplossingen met VBA mogen niet, niemand snapt dit, anders had ik het allang opgelost. De filosofie is dat er altijd wel iemand te vinden is die een formule kan ontrafelen, maar VBA is vele malen lastiger.

De subafdelingen maakt het wat lastiger, want daardoor kan ik de afdelingen niet mooi uitlijnen. Ook maakt subafdelingen het lastiger omdat de formule daar verspringt.

Ik had het eerst onderschat hoor, maar dit is wel het principe wat ik bedoelde. Ik had nog niet nagedacht over het weergeven van alle afdelingen bij een hoofdafdeling maar nu dus wel voor je verwerkt in een voorbeeld.

Edit:
http://www.mijnbestand.nl/Bestand-3N6UJHLS4TTQ.xlsx (nieuw bestandje met 2 uitwerkingen)

[ Bericht 4% gewijzigd door snabbi op 23-01-2014 23:12:20 ]

quote:

Op donderdag 23 januari 2014 23:03 schreef snabbi het volgende:

[..]

De subafdelingen maakt het wat lastiger, want daardoor kan ik de afdelingen niet mooi uitlijnen. Ook maakt subafdelingen het lastiger omdat de formule daar verspringt.

Ik had het eerst onderschat hoor, maar dit is wel het principe wat ik bedoelde. Ik had nog niet nagedacht over het weergeven van alle afdelingen bij een hoofdafdeling maar nu dus wel voor je verwerkt in een voorbeeld.

Edit:
http://www.mijnbestand.nl/Bestand-3N6UJHLS4TTQ.xlsx (nieuw bestandje met 2 uitwerkingen)

Snabbi,

Dank je wel, ik heb het net geopend, ik snap je gedachtegang, maar het is nog niet helemaal goed, want als ik in jouw voorbeeld LG invul in B2, dan zie ik alleen maar nulwaardes, dat is dus niet de bedoeling.
Maar, ik zal kijken of ik hiermee verder kan.