Ingewikkeld excel werkblad

[Verwijderd]

Beste Tweakers,

Op mijn werk werk ik dagelijks met personeelsplanningen die opgemaakt zijn in excel (2000). Aan deze personeelsplanningen (3 ploegendienst) hangt een overzicht (2de rekenblad) met daarin de uren van de werknemers gekoppeld aan een bepaald ploegenpercentage. Momenteel genereer ik dit overzicht handmatig. Dus; namen en uren overnemen uit personeelsplanning en deze in de juiste "toeslagkolom" plaatsen. Tijdens dit handmatig overnemen ontstaan nogal eens fouten (het gaat hier vaak over meer dan 100 personen in 3 verschillende ploegen verdeeld over 2 fabrieken en 10 afdelingen). Om dit in de toekomst te voorkomen probeer ik nu dit urenoverzicht automatisch te laten genereren. Ik ben nu zo ver dat ik op het tweede blad met de onderstaande formule de waarde van een cel uit het eerste blad (weekplanning) mee kan nemen mits deze cel gevuld is.

=ALS(Weekplanning!B4>"",Weekplanning!B4,"")

Maar nu; ik wil alle namen onder elkaar hebben zonder dat lege cellen meegenomen worden en ik wil de bijbehorende uurtotalen uit het eerste blad in de juiste toeslagkolom hebben. Dit alles bij voorkeur ook nog alphabetisch (kan natuurlijk ook handmatig met de optie sorteren).

Het komt er dus op neer dat ik in een formule aan wil geven dat er uit een bepaalde cel een waarde meegenomen moet worden naar het tweede blad mits er een waarde in de cel staat. Afhankelijk van de rij moet de waarde in een bepaalde kolom geplaatst worden op het tweede blad.

Ik realiseer me dat het een abstract verhaal is maar hoop toch dat jullie me wat handreikingen kunnen geven. Ik zit hier al een aantal uur in een dik handboek te lezen maar kom eigenlijk geen steek verder....

[Verwijderd]

Wat jij nodig heb is een macro. Ik weet niet of je bekend met met Visual Basic for Applications ? Hier zou je dat perfect mee kunnen realiseren. Een echt concreet voorbeeld kan ik je niet geven aangezien ik niet precies weet hoe het er bij je aan toe gaat.

[SinergyX]

om inderdaad de lege cellen eruit te filteren moet je aan de slag met de macro-editor (zijn geen formules voor bij mij weten). Hele tabel in matrix inladen, sorteren en selecteren en op 2de blad laten invullen.

[BtM909]

Probeer in de eerste instantie een Macro te recorden, waarbij je deze akties eenmalig handmatig uitvoert. Ga vervolgens met ALT+F11 kijken wat Excel ervan gebrouwen heeft. Probeer te begrijpen wat er gebeurt en pas dat eventueel aan met behulp van de VBA help (of GoT ).

[Verwijderd]

Met die macro's kan ik inderdaad het een en ander oplossen. Vooral voor de sortering op alfabetische volgorde is het een prima oplossing. Ik geloof alleen niet dat ik met een macro kan aangeven dat een cel alleen meegenomen mag worden als deze "gevuld" is. Ook Kan ik volgens mij niet in een macro verwerken dat totalen (uren) liggende tussen rij x en rij y in kolom A gezet moeten worden. Of begrijp ik het begrip macro niet helemaal?

Onder macro versta ik; "opnemen" van een aantal handelingen die vervolgens onder 1 functieknop geplaats kunnen worden.

[Verwijderd]

[b] Of begrijp ik het begrip macro niet helemaal?

Onder macro versta ik; "opnemen" van een aantal handelingen die vervolgens onder 1 functieknop geplaats kunnen worden.

Ja je begrijpt het begrip macro niet helemaal goed.

Een macro is niets anders dan een stukje programmeer code. Oftewel een klein programmaatje wat je binnen Excel weer kan uitvoeren. De mogelijkheid van het opnemen van acties in een macro is alleen een handige optie waarmee je dus geen kennis van het handmatig programmeren nodig hebt.

Als je dus wel de kennis hebt om handmatig de macro-code te 'verzinnen' (VBA = Visual Basic For Applications) dan zijn de mogelijkheden in principe oneindig. Het moet dan zeker mogelijk zijn wat jij hierboven beschrijft.

Maar afgaande op jouw reactie over het begrip 'macro' neem ik aan dat je waarschijnlijk geen ervaring hebt met het programmeren in VBA. Dan wordt het misschien wel lastig om te realiseren wat jij wilt.

[Verwijderd]

Inderdaad, mijn kennis van programmeren is bijzonder klein. Altijd gedacht dat ik redelijk handig was met Excel maar dat valt dus toch een beetje tegen. Dat het mogelijk is wat ik wil weet ik want zo vreselijk veel vraag ik toch ook weer niet van het programma. Anyway, ik ga mij maar eens wat meer verdiepen in de macro's. En anders toch maar een "mannetje" inhuren die professioneel met dit soort zaken bezig is.

[Verwijderd]

Verwijderd schreef op 24 november 2003 @ 15:13:
Inderdaad, mijn kennis van programmeren is bijzonder klein. Altijd gedacht dat ik redelijk handig was met Excel maar dat valt dus toch een beetje tegen. Dat het mogelijk is wat ik wil weet ik want zo vreselijk veel vraag ik toch ook weer niet van het programma. Anyway, ik ga mij maar eens wat meer verdiepen in de macro's. En anders toch maar een "mannetje" inhuren die professioneel met dit soort zaken bezig is.

Ok...

Ik heb de 1 op laatste alinea van je vraag nog eens goed bekeken en dit moet misschien wel te doen zijn met een formule. Want als ik je goed begrijp moet er op 2 dingen gecontroleerd worden.

1. Staat er een waarde in deze cel
2. In welke rij staan we nu.

De eerste vraag heb je zelf al opgelost met:
=ALS(Weekplanning!B4>"",Weekplanning!B4,"")

Kleine aanpassing, ik zou er van maken:
=ALS(Weekplanning!B4<>"",Weekplanning!B4,"") (dus <> i.p.v. >)

Wat mij dus je echte vraag lijkt: Hoe kan ik afhankelijk van vraag 1. vraag 2 ook nog beantwoorden.

Dit is mogelijk door het zogenaamde "nesten" van de =ALS vraag. Dit betekend dat je een =ALS binnen een =ALS stopt. Uit de (engels talige helaas) help van excel.

"You can use the following nested IF function:

IF(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"D","F"))))

In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on."

Ik hoop dat je dit een beetje begrijpt want hier zou je misschien wel iets verder mee kunnen komen.

SUCCES!

[ Voor 7% gewijzigd door Verwijderd op 24-11-2003 15:34 ]

[SinergyX]

ik zou er best eens naar willen kijken, zelf heb ik een hele tijden-registratie onderexcel draaien (5x genestelde ALS functies). Als et mag, stuur em (zonder data mag) naar de email in mijn profile.

[Verwijderd]

KarreMania schreef op 24 november 2003 @ 16:19:
ik zou er best eens naar willen kijken, zelf heb ik een hele tijden-registratie onderexcel draaien (5x genestelde ALS functies). Als et mag, stuur em (zonder data mag) naar de email in mijn profile.

Mmmm ja dat zou je kunnen doen maar dan leert Mauritz er zelf niet veel van!

Bovendien kan de rest van het forum dan niet leren van de oplossing die hier eventueel wordt gevonden. Je zou dan tenminste jouw oplossing hier nog eens beknopt moet plaatsen.....

Het forum is er toch niet alleen om het probleem van de poster op te lossen?
Het is er ook zodat anderen later nog eens terug kunnen vallen op de gevonde oplossing?

[SinergyX]

ik was er ook van plan grote feedback op te geven, heb al eens een handleiding voor de tijd-registratie moeten schrijven (incl. formule uitleg) voor degene die nu de tijd-reg doet.

Weet ook niet of het wel te doen, vandaar dat ik wat mer inzicht wilde. Maar verder nog nix van de TS gehoord, dus we wachten af

[Verwijderd]

Ja, hier de TS dan weer. KarreMania, bedankt voor het aanbod. Ik ben er echter nog niet op ingegaan omdat ik het, net als iedere fanatieke hobbyist, graag zelf wil proberen.

Ik ben inmiddels aan de slag gegaan met macro's. Dit helpt een heel eind. Ik krijg nu met een toetsencombinatie een prachtig overzicht per fabriek. Dit gaat ook nog eens netjes op alphabethische (Offtopic; is het nou alpha- of alfa-) volgorde. Het enige dat ik niet mee krijg in de macro is het verwijderen van alle lege regels. Dit kan niet omdat het aantal gebruikte regels per keer verschilt. Ook is het met een macro natuurlijk zo dat het hele overzicht in 1 keer gemaakt wordt en je dus geen "meelopend" overzicht hebt. Nu is dat ook niet echt nodig maar persoonlijk vind ik het wel iets netter.

De vraag die me nu dus nog rest heeft betrekking op het automatisch verwijderen van de niet gebruikte regels en hoe ik dat in kan passen in een macro. Keep in mind dat het aantal niet gebruikte regels dus per keer verschilt.

En natuurlijk ga ik nu met de dubbel genestelde ALS formule aan de gang. Ik hou jullie op de hoogte

[Verwijderd]

Ik ben er echter nog niet op ingegaan omdat ik het, net als iedere fanatieke hobbyist, graag zelf wil proberen.

Heel goed

Dit gaat ook nog eens netjes op alphabethische (Offtopic; is het nou alpha- of alfa-) volgorde.

kuch..... http://www.woordenboek.nl...ek/?zoekwoord=alfabetisch ......kuch

Het enige dat ik niet mee krijg in de macro is het verwijderen van alle lege regels. Dit kan niet omdat het aantal gebruikte regels per keer verschilt.

Hoe wordt het overzicht elke week aangemaakt?
Misschien kan je automatisch een sluit code in de laatste cel plaatsen?
Dan kan je zoeken naar deze code en weet je automatisch dat de voorgaande rij het einde van het overzicht is.

Ook is het met een macro natuurlijk zo dat het hele overzicht in 1 keer gemaakt wordt en je dus geen "meelopend" overzicht hebt. Nu is dat ook niet echt nodig maar persoonlijk vind ik het wel iets netter.

Bedoel je hiermee dat je sheet niet automatisch geupdate wordt als je er in werkt?
Daar moet ongetwijfeld ook iets voor te verzinnen zijn. Ik denk dan aan een bepaald event waar je een stukje update code in zet. Ik zou nu alleen zo gauw ff niet kunnen verzinnen welk event. Maar dat moet niet moeilijk zijn om te vinden.

De vraag die me nu dus nog rest heeft betrekking op het automatisch verwijderen van de niet gebruikte regels en hoe ik dat in kan passen in een macro. Keep in mind dat het aantal niet gebruikte regels dus per keer verschilt.

Euhm tja da's toch gewoon een kwestie van in je macro controleren off de desbetreffende rij/cel leeg is? Zo ja dan overslaan en met de volgende verder gaan.

En natuurlijk ga ik nu met de dubbel genestelde ALS formule aan de gang. Ik hou jullie op de hoogte

Euh tja ik wil natuurlijk niet zeuren maar als je een macro gemaakt hebt hoef je toch niet meer met een formule te gaan werken?

Het lijkt me in dit geval of/of en niet en/en. Maar goed 'correct me if i'm wrong' (wat natuurlijk niet kan )

[NetForce1]

Kun je niet alle regels gewoon overnemen met Excel-functies, en vervolgens de macro gebruiken om de lege regels ertussenuit te halen. Dat zou je kunnen doen door alle regels langs te lopen tot je een sluitcode tegenkomt. Verwijderen gaat meen ik met selection.delete(xlEntireRow), maar dat staat uiteraard ook in de VBA-help.

[Verwijderd]

Verwijderd schreef op 25 november 2003 @ 17:32:En natuurlijk ga ik nu met de dubbel genestelde ALS formule aan de gang. Ik hou jullie op de hoogte

Zit er al schot in de zaak? We zijn natuurlijk erg benieuwd naar je oplossing Al was het alleen maar voor de archief functie van het forum.

[Verwijderd]

sorry, wegens drukte op een ander project heb ik dit kleine projectje nog niet verder kunnen ontwikkelen. Maar we gaan er deze week weer mee aan de slag. Dus zodra ik eruit ben of jullie hulp nodig heb meld ik me hier weer. Thanks tot zover

[Verwijderd]

Eindelijk heb ik dit projectje weer op kunnen pakken. Situatie is nu als volgt; Weekplanning naar totaallijst gaat prima mbv een macro die ik "opgenomen" heb. Dit werkt geweldig. Nu wil mijn leidinggevende er nog het volgende aan toegevoegd zien; een aparte lijst wie er per dag in welke dienst werken. Ook dit moet geen probleem zijn. Ik loop echter op 1 ding vast (vandaar deze post).

Het dagoverzicht bestaat uit 3 x 2 kolommen. In de eerste kolom van een setje komt een naam, de 2de dient leeg te blijven. De informatie komt uit het weekoverzicht waarin mbv van 3 symbolen aangegeven is of iemand moet werken, vrij is of reservedienst heeft. In het dagoverzicht moeten alleen die mensen staan die inderdaad moeten werken, wat aangegeven is met een puntje in het weekoverzicht. De oplossing zal ik dus als volgt vorm moeten geven;

als B1 van weekoverzicht . is dan wordt A1 van dagoverzicht A1 van weekoverzicht. Is B1 weekoverzicht iets anders of leeg kijk dan naar B2 weekoverzicht (waar vervolgens weer hetzelfde voor geldt).

Hoe pak ik samen in een formule. Ik heb al lopen rommelen met Als(weekoverzicht!B1.,Weekoverzicht A1) maar dat blijkt niet te werken. MAW, ik kom er niet uit....

[Verwijderd]

Inmiddels ben ik na een halve dag rommelen niet veel verder. Iemand die meer van excel weet dan ik??

[Verwijderd]

Inmiddels zijn we iets verder. Als de waarde . is wordt de naam overgenomen. Wat we er nu nog in moeten krijgen is dat als de waarde anders is dan . bekijk dan de volgende cel en toetst deze op dezelfde manier. Formule ziet er nu zo uit;

=ALS(weekoverzicht!B5=".",weekoverzicht!A5)

[ Voor 96% gewijzigd door Verwijderd op 29-01-2004 11:21 ]

[BtM909]

Verwijderd schreef op 29 januari 2004 @ 10:42:
schopje!

Heb je toevallig een screenshot, want ik kan er even niks bij voorstellen.

[Verwijderd]

Screenshotje gaat een beetje ingewikkeld worden. Zie het als volgt. Op blad 1 staan namen in kolom A en symbolen in kolom B. Op blad 2 wil ik de namen overnemen als kolom B van blad 1 symbool . heeft. Anders moet hij de cel leeg laten. Dit heb ik nu voor elkaar met onderstaande formule.

=ALS(weekoverzicht!B3=".",weekoverzicht!A3,ALS(weekoverzicht!B3<>".",""))

Wat eigenlijk nog beter zou zijn is als de waarde niet . is de formule kijkt naar de volgende cel en hier de volgende vergelijking volgens dezelfde randvoorwaarde maakt. Hoe ik dat voor elkaar moet krijgen zou ik echt niet weten. Iemand??

[ Voor 29% gewijzigd door Verwijderd op 29-01-2004 11:58 ]

[Lustucru]

Verwijderd schreef op 29 januari 2004 @ 11:50:

=ALS(weekoverzicht!B3=".",weekoverzicht!A3,ALS(weekoverzicht!B3<>".",""))

Wat eigenlijk nog beter zou zijn is als de waarde niet . is de formule kijkt naar de volgende cel en hier de volgende vergelijking volgens dezelfde randvoorwaarde maakt. Hoe ik dat voor elkaar moet krijgen zou ik echt niet weten. Iemand??

Er staat in ieder geval een overbodige 'als'
=ALS(weekoverzicht!B3=".",weekoverzicht!A3,"").

Als ik je goed begrijp wil je het volgende:
in rij 1 komt de naam bij het eerste voorkomen van een "."
in rij 2 komt de naam bij het tweede voorkomen van een "."
...
in rij n komt de naam bij het ne voorkomen van een "."

Je kunt dat voor elkaar krijgen door middel van vergelijken, waarbij je een hulpkolom maakt waarin je de rij vermeldt waarin de punt gevonden is. In de cel in de regel eronder beperk je het zoekbereik tot de regels onder het voorkomen. met de gevonden regelnummers haal je je data weer op.
Een alternatief is dat je een macrootje maakt dat het weergaveblad filtert, en dat laat aflopen als er op je invulblad een punt wordt gezet.

Maar hemel, dit soort dingen zijn zo eenvoudig in een database, en zo lastig in een werkblad...

[ Voor 28% gewijzigd door Lustucru op 29-01-2004 13:03 ]

[Lustucru]

Hierbij de formule die een tabelmaakt met regelnummers waarin een "." voorkomt.
(punten in b, formule zelf doorgekopieerd in kolom c)
In c1: =VERGELIJKEN(".";B1:B500;0)
in c2 ev: =C1+VERGELIJKEN(".";INDIRECT("B"&C1+1):B500;0)
Het resultaat is dat in kolom c de opeenvolgende rijnummers komen te staan; aangevuld met rijen #N/B.

[Verwijderd]

Hmmm, nielsje, jij begrijpt er een hoop meer van dan ik maar het is niet helemaal wat ik bedoel. Aan het einde van de formule die ik hierboven gepost heb moet alleen nog een verwijzing komen naar de volgende cel om daar weer dezelfde vergelijking te maken. Dus ipv de cel leeg te laten als het object geen . is wil ik dat hij dezelfde vergelijking maakt voor de eerstevolgende cel.

[Lustucru]

Ja, en als daar ook geen punt staat? Wat dan? En als hij die vergelijking heeft opgehaald, dan neem ik aan de eerstvolgende cel de al gecontroleerde cel moet overslaan?

code:

naam1  
naam2  .
naam3  .

moet resulteren in

code:

naam2
naam3

Of begrijp ik je verkeerd?

[Verwijderd]

nee, dat zie je idd goed. Dan heb ik jouw verhaal niet helemaal begrepen. Ik ga er vanavond mee aan de gang. Morgen hoor je het resultaat. Alvast bedankt voor het meedenken!!

[Lustucru]

om het dan maar helemaal af te maken: als je in kolom c de juiste rijnummers hebt, haal de naam op met:
=ALS(ISFOUT(C1);"";INDIRECT("A"&C1))
en natuurlijk naar beneden doorkopieren.
(kolom a namen, b="." of iets anders, c de opgehaalde rijnummers)

[ Voor 23% gewijzigd door Lustucru op 29-01-2004 18:06 ]

[Verwijderd]

Bedankt Niesje, ben al een heel eind verder!!