[Excel] Het grote Excel vragen topic #39

Ik zal maar niet vragen waarom zo'n database in vredesnaam in Excel is gemaakt hè?

quote:

Op vrijdag 17 januari 2014 19:05 schreef Janneke141 het volgende:
Ik zal maar niet vragen waarom zo'n database in vredesnaam in Excel is gemaakt hè?

Omdat Word niet van die mooie lijntjes had natuurlijk...

quote:

Op woensdag 15 januari 2014 14:17 schreef Saaske het volgende:
Goedemiddag,

Win7/excel 2012 NL

Ik ben een bestand aan het bouwen met gebruik van een soort 'database' waar al mijn gegevens op 1 plek staan. nu loop ik al tegen het probleem aan dat als ik ergens om wat voor reden dan ook een kolom wil toevoegen de formules 'scheef gaan lopen' door het kolomindexgetal

Is er een manier om het kolomindex_getal bij vert,zoeken te koppelen aan een cel? of mee te laten lopen in het geval dat er ergens een kolom in de matrix toegevoegd wordt?

Ik probeerde dit op te lossen door een regel in te voegen boven de kolommen en het kolomindexgetal als verwijzing naar die cel bv (blad database d3 (waar dus waarde 3 staat)) om op die manier te voorkomen dat ik iedere formule aan moet passen, maar alleen even de nummers in die extra regel goed mee moet laten lopen.

Naar mijn idee moet dit makkelijker kunnen, maar kom er met behulp van google/ms help/dit forum niet echt uit.

Alvast bedankt!

Gr.

Mag ik je heel erg aanraden alle vlookups te vervangen door index(match()) ? Is veel sneller en nooit geen gedoe meer met index nummers. Vlookup(;;match()) is alleen maar zwaarder in dit geval

quote:

Op vrijdag 17 januari 2014 19:04 schreef Zocalo het volgende:
Ja, twee keer 750.000 ongeveer. Gigantisch bestand ook.

Die heb ik niet eerder gezien. Wel een keer een file van bijna een gigabyte :p

Hallo Allemaal!
Ik zit met het volgende probleem:

Ik heb een excelsheet gemaakt waarin ik de winst wil verdelen. (getallen zijn voorbeelden)
De eerste 0-50 boeken 20% van omzet, 51-100 25% etc. etc.
Ik heb 675 boeken verkocht. Nu zou ik graag willen dat hij de boxen automatisch aanvult,
totdat hij de maximale waarde heeft bereikt (Alles in 'AR'). Ik moet nu zelf bij box 1: 50 invullen,
box 2: 49 etc.
Dus: is er een formule die de boxen automatisch 'opvult' totdat hij bij het getal 675 is?
Het getal 675 (of eventueel een andere waarde) staat in cel AG27.

Vriendelijk bedankt!

Waar komen de getallen 49 en 99 vandaan? Ik zou daar namelijk 50 en 100 verwachten.

Hoe dan ook, met in AR25 =ALS($AG$27<50;$AG$27;50)
In AR26 =ALS(SOM(AR$25:AR25)=$AG$27;0;MIN($AG$27-SOM(AR$25:AR25);AP26)
En die naar beneden slepen tot AR33

Moet het een heel eind goedkomen.

[ Bericht 40% gewijzigd door Janneke141 op 18-01-2014 14:52:07 ]

quote:

Op zaterdag 18 januari 2014 14:41 schreef Janneke141 het volgende:
Waar komen de getallen 49 en 99 vandaan? Ik zou daar namelijk 50 en 100 verwachten.

Hoe dan ook, met in AR25 =ALS($AG$27<50;$AG$27;50)
In AR26 =ALS(SOM(AR$25:AR25)=$AG$27;0;MIN($AG$27-SOM(AR$25:AR25);AP26)
En die naar beneden slepen tot AR33

Moet het een heel eind goedkomen.

quote:

Op zaterdag 18 januari 2014 14:57 schreef qu63 het volgende:

[..]

Volgens mij zitten er in box 2 ook 50 verkochte boeken, en in box3 100, toch?

Nee, het zijn de verkopen van:
0 - 50 (dus 50)
51 - 100 (dus 49)
101 - 200 (dus 99)
201 - 250 (dus 49)

De tranen springen in mijn ogen. Maar dat zal wel beroepsdeformatie zijn.

quote:

Op zaterdag 18 januari 2014 15:07 schreef Janneke141 het volgende:
De tranen springen in mijn ogen. Maar dat zal wel beroepsdeformatie zijn.

Sorry mensen. Helemaal mijn fout. De moeheid slaat toe na 6 uur met deze sheet bezig te zijn.

De formule werkt. Bedankt!

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 ]