| Hoofdpagina's |
andere
pagina's |
Excel
Tips en Truks |
Indexpagina
Nieuwspagina
Overzicht
downloads
Trika Roosters Maatwerk
|
Overzicht slimme tips
Externe links
Excel
rekenmodellen
Excel
voorbeelden |
bescherming en
beveiliging
celverwijzingen
Excel database
verticaal zoeken
|
keuzelijstje maken
logische
functies
Excel printtips
sjabloontips |
Tijdstempel
verplaatsen
kopieeren
voorwaardelijke
opmaak
datums
en tijden |
|
|
financiële functies Excel |
|
|
|
|
|
Werken met datum en tijden in
Microsoft Excel een
duidelijke uitleg met scherm voorbeelden.
Onderstaand ziet U de opmaak van een kalender. In het rode vakje wordt
de eerste datum van de kalender gezet. De formule
in cel E
= de vorige cel + 1 In cel F wordt dezelfde formule
herhaald enz enz.
De maand-jaar
in cel
BC2 verwijst naar de eerste datum invoer door
celopmaak kun je er voor zorgen dat alleen maand en jaar wordt
weergegeven.
De dagen van de week verwijzen steeds naar de datum
eronder en door cel opmaak kun je er voor zorgen dat alleen ma di wo do
vr za en zo wordt weergegeven.
Een andere oplossing zou kunnen zijn om deze formule te gebruiken
=TEKST(D5;"ddd") maar ikzelf vindt de celeigenschappen aanpassen
simpeler.
Bij meerde bladen in
je werkmap
kun je de datums van de kalander aan elkaar koppelen door in de formule
te verwijzen naar de laatste datum van je vorige werkblad.
Bv de kalenderbladen
jan febr =jan!AJ5+1 de eerste datum in
je werkblad febrverwijst hier naar Cel AJ5
(hier staat 31 januari) in het werkblad jan .
Bij een Schrikkeljaar
zou de doortelling in maart een probleem kunnen geven. Om dit
op
te lossen bestaat er een formule die kijkt of in het lopend jaar de
29-ste dag van februari in februari of in maart
valt en het
resultaat is WAAR voor een schrikkeljaar en ONWAAR bij geen
schrikkeljaar.
de formule ziet er zo uit:
=ALS(MAAND(DATUM(JAAR(F5);2;29))=2;WAAR;ONWAAR).
in onderstaand voorbeeld heb ik dit als voorbeeeld uitgewerkt.
De ingevoerde formule verwijst naar cel F5 ( 1 januari 2008) en geeft
als reslultaat WAAR
In het volgende werkblad februari (hierboven) vraag ik in cel
AH2
om de waarde van cel F2 in werkblad januari (=jan!F2) deze waarde is
WAAR. dit heb ik nodig voor mijn formule op 1 maart in het volgende
werkblad.
De formule op 1-3-2008 vraagt hier of de waarde in het vorige werkblad
(feb!AH2 WAAR is zoja dan moet hij 1 dag optellen bij 29
februari bij ONWAAR moet hij 1 dag optellen bij de waarde uit de cel
daarvoor (feb!AG2)
Werken
met
tijden
In het onderstaande schema willen we uitrekenen hoeveel uren er
verstreken is tussen de begintijd en de eindtijd.
Hieronder ziet
U het zelfde schermafdrukje waarbij ik bij de opties het vakje formules
heb aangevinkt.
Omdat Excel de tijden berekend met seriele getallen zult U dat de bij
de celeigenschappen moeten aangeven (zie tekening bij celeigenschappen)
De formule nader bekeken:
=((H6-F6+(H6<F6))*24)
Eindtijd - begintijd+(de tijd voor 00:00 uur) x 24
wordt gebruikt om de decimale uren in minuten om te zetten.
rekenen met tijden
door namen te gebruiken.
de cellen onder begin- en eindtijd heb ik een naam gegeven. de formule
ziet U hieronder
bij de formuleweergave worden tijden als serieele getallen weergegeven.
de celeigenschappen zijn ingesteld op [u]:mm: ss
bovenstaand bestandje kunt U in excel hier
downloaden.
Sneltoetsen (toetsenbord commando's) voor invoer van tijd en datum:
Moet je vaak in lijst de datum en of tijd invoeren, dat kun je zeer snel doen met een toestenbord commando
zorg wel dat de datum en tijd van je PC correct zijn.
Voor de datum: Druk de Controle toets in en houdt hem vast, geef dan een punt komma (;)
Cntrol ; de tijd wordt ingevoerd
Voor de tijd: Druk de Control toets in en houdt hem vast, klik de shifttoets en de punt komma( ;)
Cntrol Shift ; de datum wordt ingevoerd.
Indien de tijd en de datum ieder keer dat U het werkblad open moet
worden bijgewerkt heeft U niets aan deze tip U dienst dan de functie
=Vandaag() en =Nu() te gebruiken.
Hoe vindt Excel de eerste zondag en
de laatste
zondag van de maand.
Bij het maken van de (verjaardags) kalender voor de webpagina zelf een
verjaardagskalender maken moest de eerste zondag van de maand worden
gevonden en daar 6 dagen van af trekken omdat de kalender steeds op
maandag begint In onderstaand voorbeeld vindt U de manier om de eerste
zondag van de maand te vinden.
Deze
formule
heb ik geleend van de website: www.schoutsen.nl een
aanrader om eens te bezoeken vanwege de duidelijke uitleg bij de
vermelde formules .
Download: Het
bovenstaande bestandje berekening eerste en laatste zondag van de maand
berekenen.
Totalen per maand opzoeken uit een
bank- kasboek.
Onderstaand bestand is een gedeelte van een verkoopboek. Van ieder
order wordt de btw uitgerekend.
Een aantal totaal tellingen zitten in het bestand verborgen in
gesloten regels. Het blad BTW haalt de benodigde gegevens op uit dit
verkoopboek.
Voor het vinden van de maanden is in kolom Z de formule MAAND gebruikt
in cel Z4 ziet de formule er zo uit:
=ALS(C4="";"";MAAND(C4)) dwz als cel C4 leeg is moet er
niets
worden ingevuld. Anders moet het maand nummer worden gegeven.
In kolom S cellen 1529 t/m 1540 willen we het btw tarief laag hebben.
In kolom T cellen 1529 t/m 1540 willen we het btw tarief hoog hebben.
In kolom V cellen 1529 t/m 1540 willen we het goederen bedrag zonder
BTW hebben.
In cel S 1529 staat de volgende formule:
=SOM.ALS($Z$4:$Z$1114;$D1529;$S$4:$S$1114)
Het eerste gedeelte van de formule verwijst naar kolom Z waar het
maandnummer wordt weergegeven.
Je ziet hier dat het verkoopboek uit 1110 regels bestaat.
Het tweede gedeelte van de formule verwijst naar het nummer wat hij
moet opzoeken in Cel D1529.
Het derde en laatse deel van de formule verwijst naar de kolom waar de
totaal telling uit moet komen.
Omdat Z4 t/m Z1114 een vast gegeven is heb ik er een absolute celadres
van gemaakt ($Z$4:$Z$1114)
je kunt dan de formule eenvoudig naar beneden kopieeren. Als je hem
naar rechts kopieert hoef je maar weinig aan te passen.
Het bovenstaand bestand is de verkooplijst die je vindt op de pagina administratie
voor marktplaats verkopers
Verder zoeken op het web:
Een automatische genummerde lijst maken
Een
automatisch genummerde lijst maken: In onderstaand voorbeeld willen we
bij iedere boeking automatisch een nummer toekennen aan de boekings
regel. We willen
dat de cellen waar formules in komen leeg blijven als er niets is
ingevuld. Omdat we willen zien welke dag van de week het drukste is
willen we de naam van de dag erbij hebben staan. Tevens willen we door
de SOMALS functie maand totalen uit kunnen filteren.
We hebben
gekozen om dit bestand als tabel te maken. Bij een tabel hoef je maar
1x de formule te plaatsen, deze wordt door de tabel automatisch
aangepast naar het aantal regels. Een later regel toevoegen of kolommen
bijplaatsen is geen probleem. De tabel past de situatie automatisch aan.
(zie ook mijn webpagina database tips)
In regel
3 heb ik het nummer 2200 ingevuld, dit kan natuurlijk ieder gewenst
nummer zijn. Ik wil echter niet bij 1 beginnen. In de formules heb ik
voor de A-1e, C-3e en D44 kolom de als functie gebruikt: Simpel gezegd.
als B leeg is wil ik hier niets hebben staan, anders B1.
=ALS(B4="";"";B4) zie hieronder:
Je kunt als laatste het nummer 2200 in regel 3 dezelfde kleur geven als de achtergrond.
De formule voor het maandnummer is =Maand(B4)
Om de
naam van de dag te vinden verwijs ik naar Cel B4 (als hij niet leeg is
natuurlijk) hij geeeft nu het seriele nummer. Bij celeigenschappen ga
ik naar aaangepast en type daar ddddd een d-tje minder geeft ZA
In dit voorbeeld hebben we de genummerde lijst aan een datum kolom
gekoppeld, je kunt hem natuurlijk in ieder gewenst bestand koppelen.
Misschien wil je de nummers wel kopelen aan een invulformulier dat
je hebt weggeschreven als sjabloon die zijn gegevens uit je gemaakte
database haalt. Daar kun je de functie verticaal zoeken voor gebruiken.
Op deze website staan verschillende voorbeelden van verticaal zoeken.
Tabellen maken het lijkt moeilijk, maar als je even doorzet zul je er erg veel gemak van hebben.
Voor deze uitleg heb je wel Excel 2007 nodig.
Veel succes.
werken met datums en tijden:
Nieuw!!!!: Vanaf begin augustus 2011 ben ik bezig aan een verzameling tips en trucks in een excelmap
deze werkmap kunt U hier downloaden.
|
|
|
|
|
. |
|
.
|
|
|
Alle
technieken die ik heb gebruikt om een Excel rekenmodel
te maken vindt U op deze website. Om het voor de beginnende Excel
gebruiker simpel
te houden gebruik ik geen VBA (de programmeer taal van Excel) Bij de meeste rekenmodellen heb ik de
formule opbouw bij de
uitleg van het rekenmodel geplaatst. Iedere download op deze website
mag U
gratis downloaden en gebruiken. U mag de downloads van deze
website niet verhandelen
of op andere website ter download aanbieden. Ieder rekenmodel is met de meeste zorg door
mij gemaakt. Een
aantal rekenmodellen op deze website zijn voorzien van een paswoord.
Mocht U
een rekenmodel als uitgangspunt voor Uw werk willen gaan gebruiken dan
heb ik
al heel veel voorbereidend werk voor U verricht. U kunt dan tegen
betaling een
paswoord vrije versie bestellen. De paswoord vrije versie mag U niet
gebruiken
om later aangepast te gaan verhandelen. Bij de betreffende
rekenmodellen vindt
U een opgave van de prijs en het aantal werkuren per rekenmodel.
|
|
