Gratis tips Excel - WISSELENDE MAANDELIJKSE TABEL


Tips Excel		go to : Index

Tip 260
Excel Tip

WISSELENDE MAANDELIJKSE TABEL
Deze tip is misschien iets meer voor de meer dan beginnende gebruiker van Excel, maar alles zal op een zo goed en makkelijke manier worden uitgelegd, dat iedereen er wel iets van snapt. Er komen heel wat verschillende functies aan bod, maar laat jullie niet afschrikken, het zijn allemaal standaard functies. De bedoeling van dit bestand is, afhankelijk van de geselecteerde maand en jaar, dat de cellen worden ingekleurd in kolom F en G, dat enkel deze cellen kunnen worden aangepast, en dat dit enkel de cellen zijn waarvan er een datum bestaat. Zo zal bv. de datum 31 april nooit bestaan, en kan er dus ook nooit iets worden ingevuld.
Wens je al mijn gelul niet te lezen en onmiddellijk te beginnen, klik hier om het bestand te ontvangen.

Wat ik reeds heb gemaakt is in cel A2 een validatielijst met de maanden uit kolom J (1). En in cel B2 een validatielijst met de jaren uit kolom L (2). Hoe je een validatielijst maakt zou je wel al moeten weten, anders lees je even Les 67 van de cursus.


Wat we vervolgens moeten bepalen is het aantal dagen dat er in de maand voorkomen. Een maand januari heeft steeds 31 dagen, een maand april heeft steeds 30 dagen. Een maand februari heeft dan weer, of 28 dagen of 29 dagen, afhankelijk of het een schrikkeljaar is. De eerste dag van de gekozen maand/jaar bepalen we in cel A4, de laatste dag van de gekozen maand/jaar bepalen we in cel B4. Afhankelijk van wat er is gekozen als maand en jaar, moet er in kolom D het dagnummer, bv 1,2,3 enzoverder komen te staan en in kolom E de dag, bv maandag, dinsdag, enzoverder. Afhankelijk van wat er is gekozen als maand en jaar, moeten de kolommen F en G worden ingekleurd. Bestaat de dag niet, dan worden deze niet ingekleurd. En bestaat de dag niet, dan kan er ook niets worden ingevuld. Zie voorbeeld hieronder. Ter informatie: De rijen 14 t/m 27 heb ik even verborgen om jullie een beter overzicht te kunnen tonen.


De formule om de begindatum van de maand te bepalen is =DATUM(B2;VERT.ZOEKEN(A2;J:K;2;0);1 Laat ik even uitleggen wat hier staat. De DATUM-functie laat ons toe een datum samen te stellen met dag, maand en jaar. Het jaar haalt hij in cel B2. De maand haalt hij door middel van de VERT.ZOEKEN-functie. Deze informatie haalt hij in cel A2, en gaat hij zoeken in het bereik J:K, heeft hij een overeenkomst gevonden, dan moet hij uit dit bereik de de gegevens uit 2de kolom weergeven. Dus een 1=januari, 2=februari, 3=maart, enzoverder. De nul staat er omdat hij een exacte overeenkomst moet vinden. En de eerste dag van de maand haalt hij bij de 1 achteraan de formule.


Om de laatste dag van de maand te bepalen gebruiken we de formule =LAATSTE.DAG(A4;0) Deze lijkt me duidelijk, maar toch zal ik ze even uitleggen. Hier staat, geef me de laatste dag van de maand bepaalt in cel A4. Het tweede argument, de nul, bepaalt exact de laatste dag. Typ je hier bijvoorbeeld een 1, dan geeft hij de eerste dag weer van de volgende maand.


Zo is bijvoorbeeld de laatste dag van de maand februari 2016, de 29ste.


De dag van de week, die wordt weergegeven in kolom E, wordt weergegeven in kolom E met volgende formule: =ALS(DAG($B$4)<D2;"";WEEKDAG(($A$4)+D2-1)) B4 en A4 zijn absoluut, deze moeten bij kopiëren dezelfde verwijzing blijven. D2 is relatief. deze verwijzing moet bij het kopiëren worden aangepast. Wat staat hier: ALS de DAG in B4 kleiner is dan de het cijfer in D2 (kolom D ), dan geef je me niks, anders geef je me de weekdag van cel A4 + D2 - 1. De plus D2 zal ons één dag verder geven, dus moeten we de min 1 erbij plaatsen oml dit te corrigeren. Dit laatste deel is misschien moeilijk uit te leggen, maar moest je dit er niet bij plaatsen, dan geeft hij in elke rij dinsdag weer.


Nu, de foutmelding die hij moet weergeven wanneer je gegevens typt in een cel waarvan de datum niet bestaat, bepalen we met de optie "Gegevensvalidatie". Minder dan 28 dagen in een maand bestaan er niet dus selecteer de cellen van rijen en kolommen F en G waarvoor er nooit een dag kan voorkomen. Klik de knop "Gegevensvalidattie" en geef volgende formule in: =LENGTE($E30)<>0 Wat staat hier: als de lengte van de inhoud in deze cellen niet gelijk is aan niks, (de kolomverwijzing is absoluut, het is steeds dezelfde kolom, de rijverwijzing is dat uiteraard niet).


dan geef je me deze foutmelding:


We testen dit even uit. En ja hoor, proberen we iets in te geven in rij 30 waar in kolom E niks staat, dan geeft hij me een foutmelding.


Wat we nu moeten toevoegen is de voorwaardelijke opmaak. Dus wanneer de datum bestaat, en in de cel iets mag worden getypt, dan moet deze groen worden weergegeven. Selecteer eerst alle cellen waarop je deze opmaak wil toepassen en klik vervolgens de knop "Voorwaardelijke opmaak".


Kies "Een formule gebruiken...", en geef volgende formule in: =LENGTE($E2)<>0 Dit is ongeveer hetzelfde dan de formule voor het weergeven van de waarschuwing. Dus wanneer de lengte van de inhoud van de cel niet gelijk is aan niks, kleur deze dan groen. e kolom wordt ook hier ingesteld als absoluut, de rij als relatief.


Probeer alles even uit en je zal zien dat alles perfect werkt.
Het enige wat we nu nog moeten doen, is een formule plaatsen in kolom H.
Welke formule jij hier gaat gebruiken maakt niet uit, het voorbeeld dat ik gebruik rekent het gemiddelde bedrag uit per transactie. Dit doen we met de formule =ALS(OF(ISFOUT(G2/F2);LENGTE(E2)=0);"";G2/F2) Wat staat hier. Wanneer de deling een fout geeft, of de lengte van de inhoud in kolom E is 0, dan geef je me niks. Is dit niet het geval dan deel je de inhoud van kolom G door de inhoud van kolom F.


Wanneer we dit even proberen blijkt alles netjes te werken.


Deze formule kunnen we doortrekken met de vulgreep.


In deze tip kwamen heel wat functies aan bod, maar toch niet zo'n moeilijke. Eénmaal het bestand gemaakt, kun je dit vrijwel op elke berekening toepassen.


GratisCursus.be denkt aan jullie.
> Index