Rekenen met tijden in Excel

Symptomen De gebruiker houdt in een rekenblad het aantal gewerkte uren van de medewerkers in de organisatie bij, en berekent daarna de loonkosten. Maar in de totaalberekening leidt de urentelling niet tot de juiste bedragen. Remedie Twee zaken kunnen het probleem veroorzaken: de opgetelde uren vermenigvuldigen met bedragen, en de urentelling die over de daggrens gaat. Hieronder de oplossing voor beide. Verdieping Rekenen met tijdwaarden is – net als met datums – wat lastiger dan met ‘gewone’ getallen. Dat heeft te maken met de afwijkende getalsystemen waar u dan mee rekent: een dag kent nu eenmaal 24 uur en een uur 60 minuten. Zolang u binnen de hoeveelheid van 24 uren blijft en u niet over de daggrens gaat, kunt u berekeningen op de tijdwaarden toepassen alsof het gewone getallen waren. Zo kunt u werktijden (die in cellen staan van een rekenmodel) gewoon van elkaar aftrekken en zo uitrekenen hoeveel uren of minuten tussen begin en eindtijd zijn verstreken. In onderstaande figuur ziet u hoe de begin- en eindwerktijden en de lengte van de pauzes als uren worden genoteerd in een model en hoe dan het aantal gewerkte uren wordt uitgerekend met behulp van de formule in de formulebalk. De formule (zie de formulebalk voor cel F5) moge duidelijk zijn: eindtijd minus begintijd minus pauze, levert 5½ gewerkte uren op. Maar zodra het aantal uren over de daggrens heen gaat (24:00/0:00 uur) kan het rekenprogramma geen waarde weergeven die binnen de klok valt, omdat de formule een negatieve waarde oplevert. Om dit te voorkomen kunt u de berekening als volgt formuleren: =(D5-C5+(D5<C5))-E5 Hierin wordt gekeken of de eindtijd kleiner...

Alternatief voor VERT.ZOEKEN?

Symptomen Gebruikers weten dat herberekeningen erg traag worden als VERT.ZOEKEN veelvuldig gebruikt wordt. Maar wat is het alternatief? Het opslaan van werkmappen met de functie VERT.ZOEKEN duurt een eeuwigheid, en gebruikers zoeken naar een oplossing. Remedie Vervang alle functies van de gedaante VERT.ZOEKEN(zoekwaarde;tabelmatrix;kolom-index_getal) door een samengestelde functie van de gedaante =INDEX(tabelmatrix;VERGELIJKEN(zoekwaarde;eerste_kolom_tabelmatrix;kolomindex-getal). Verdieping De functie VERT.ZOEKEN wordt herberekend tekens wanneer een van de cellen in een van de in deze functie gebruikte parameterbereiken wijzigt. In een ander artikel is uiteengezet dat u beter bereiknamen kunt definiëren en de functie VERT.ZOEKEN kunt vervangen door intersecties. Schat u in dat dit niet de meest ideale oplossing is, vermijd de herberekening dan door te zoeken in een kleiner bereik. Alle wijzigingen in cellen buiten dit kleinere bereik leiden niet tot een serie tijdrovende herberekeningen. Het lijkt misschien vreemd dat het gebruik van twee (geneste) functies in plaats van één, tot een kleinere werkmap en tot een snellere herberekening leidt. Dat komt niet omdat VERT.ZOEKEN een inefficiënt geprogrammeerde functie is: de functie doe haar werk supersnel, maar het werkaanbod is gewoon onevenredig groot (zie Tips). Tips Excel is een spreadsheet. De functionaliteit die VERT.ZOEKEN biedt, is functionaliteit die doorgaans geboden wordt door database-pakketten. Overweeg het gebruik van programma’s als MS Access of een SQL-database. Op dezelfde manier maakt u een einde aan de functie HORIZ.ZOEKEN. Hebt u een werkmap met tientallen, honderden, duizenden of (niet te hopen) tienduizenden VERT.ZOEKEN-functies? De alternatieve geneste formule is niet alleen sneller en compacter, maar zelfs ook nog krachtiger dan VERT.ZOEKEN. Bij VERT.ZOEKEN geldt immers dat de kolom waarin gezocht wordt, de meest linkse kolom is van het bereik...

Formules plakken in Excel lukt niet

Symptomen In de ene werkmap is een cel of een bereik gekopieerd, maar het lukt niet deze gegevens in een andere werkmap te plakken.  Remedie Sluit de werkmap waarin u de gegevens wilt plakken (de doelwerkmap). Sluit de Excel-instantie (het Excel-programma) waarin deze werkmap geopend was. Kies in de Excel-instantie waarin de bronwerkmap is geopend Bestand, Openen (of de equivalente opdracht in uw Excelversie) en open de doelwerkmap. Kopieer het gewenste bereik in de bronwerkmap. Plak de gegevens in de doelwerkmap. Verdieping Opent u twee werkmappen met behulp van het Excel-menu, dan is er maar één Excel-programma actief op uw computer. Beide werkmappen maken gebruik van dit ene Excel-programma, en communiceren dus prima met elkaar. Opent u Excel via het Start-menu, opent u een werkmap, start u Excel nogmaals via het Start-menu en opent u weer een werkmap, dan is de situatie anders. Er zijn dan twee Excel-programma’s actief op uw computer: twee Excel-instanties. Die communiceren niet zo goed met elkaar: het plakken van formules vanuit de ene naar de andere werkmap is daar een voorbeeld van. Tips Wilt u herhaald een aantal formule-bereiken omzetten in vaste waarden, dan kunt u misschien handig gebruik maken van dit ‘nadeel’. [Ctrl]+[V] in de tweede Excel-instantie werkt dan immers als Plakken Speciaal, Waarden. Zie ook Excel-instanties. Ctrl+C en Ctrl+V. Bewerken, Plakken Speciaal,...

Ik gebruik datavalidatie, maar het werkt niet.

Symptomen In een Excel werkblad gebruikt u datavalidatie, maar vanaf rij 400 werkt het ‘ineens’ niet meer. Remedie 1 Verwijder overbodige lege cellen en lege regels/kolommen tussen cellen (bereiken) met datavalidatie. Het aantal gebieden met data-validatie is aan een maximum gebonden: door kleinere gebieden aaneen te sluiten, verkleint u het probleem. Remedie 2 Open het Excel-document waarin het probleem zich voordoet. Druk op [Alt]+[F11] om de Visual Basic-omgeving te activeren. Aan de linkerkant van dit Visual-Basic-venster ziet u de Projectverkenner. Zoek hier naar de naam van uw Excel-document. Open dit item indien nodig, door op het plusje links van de bestandsnaam te klikken. Open vervolgens ook de map Microsoft Excel-objecten. Activeer het item van het werkblad waarin u de datavalidatieproblemen wilt oplossen: Zoek de regel met de naam van het werkblad op. Dubbelklik op deze regel. Aan de rechterkant van het venster wordt het venster Programmacode geopend: een groot venster, dat waarschijnlijk nog helemaal wit is. Geef aan dat u een programma wilt maken dat in actie komt op het moment dat in het werkblad een andere cel wordt geselecteerd: Bovenin het venster Programmacode ziet u twee lijstvakken. Open het linkerlijstvak en kies Worksheet. Open vervolgens ook het rechterlijstvak en kies SelectionChange. Open in het Projectvenster de werkmap en in die werkmap het macroblad behorend bij het werkblad waarin u de datavalidatie wilt uitvoeren. Wilt u gegevens valideren in Blad1, dubbelklik dan in het Projectvenster op Blad1. Klik in het codevenster (het grote witte venster rechts). Neem de volgende tekst letterlijk over: Test deze programmacode. Richt het werkblad Blad1 als volgt in: Test het werkblad. Als u in een...

De performance van m’n Excel-werkmap is slecht

Symptomen Een ontwikkelaar heeft een werkmap gemaakt die op de eigen PC redelijke tot goede reactietijden geeft. Als de werkmap echter op een netwerkschijf wordt geplaatst en ter beschikking wordt gesteld aan eindgebruikers, stort de performance in. Het opslaan van een werkmap duurt (veel) langer dan verwacht. Remedie Breng de omvang van uw werkmap terug. Verdieping Wanneer een Excel-werkmap wordt geopend vanaf een netwerkschijf, reist de werkmap van de netwerkschijf naar uw pc. Slaat u de (ingevulde of gewijzigde) werkmap op, dan is het een heel ander verhaal. Eerst gaat er een verzoek van uw pc naar de fileserver. Dit verzoek bevat de complete werkmap. De fileserver beoordeelt het verzoek en antwoordt ‘ja, bestand mag worden opgeslagen’. Ook dit antwoord bevat weer de hele werkmap. Als uw pc dit antwoord ontvangt, start het feitelijke opslaan: de hele werkmap reist voor de derde keer over het netwerk. Een werkmap van 100 Mb zorgt dus voor minimaal 300 Mb netwerkverkeer. Elke megabyte die u de werkmap kleiner maakt, bespaart dus 3 Mb netwerkverkeer! Tips Er zijn veel manieren om de omvang van een werkmap te verkleinen: Houd het actieve gebied zo klein mogelijk. Neem geen overbodige en/of onnodig grote afbeeldingen in uw werkmap op. Wees kritisch bij het gebruik van ingesloten objecten (embedded objects), en schenk aandacht aan de manier waarop de koppeling tot stand wordt gebracht. Vermijd formules met externe koppelingen zoveel mogelijk. Vermijd het gebruik van functies als VERT.ZOEKEN. Gebruikt u bereiknamen, houd het referentiebereik dan zo klein mogelijk. Laat de bereiknaam niet standaard verwijzen naar bijvoorbeeld $B$2:$B$65535 (tot en met Excel 2003) of $B$2:$B$1048576 (vanaf Excel 2007), als...

Volatiel / Volatile in Excel

Symptomen Werkmappen zijn traag, u bent op zoek naar methoden om ze te versnellen. Een herberekening wordt doorgaans vlot uitgevoerd, maar af en toe is de wachttijd tenenkrommend. Een Excel-expert heeft gewaarschuwd voor het gebruik van ‘volatiele’ functies en acties. U probeert de diepere betekenis van deze opmerking te achterhalen. Remedie voor volatiele standaard Excel-functies Gebruik ze niet. Het betreft: de wiskundige functies ASELECT en ASELECTTUSSEN, de datumfuncties NU en VANDAAG, en de functies INDIRECT, VERSCHUIVEN, CEL en INFO. De waarde van deze functies worden altijd opnieuw bepaald herberekend als Excel gaat herberekenen. Remedie voor zelfgeschreven Excel-functies Neem als eerste instructie in het programma dat de functiewaarde bepaald, de volgende programmacode op: APPLICATION.VOLATILE Deze instructie voorkomt dat de zelfgeschreven functie wordt doorlopen telkens wanneer Excel een herberekening uitvoert. Remedie voor volatiele acties Gebruik ze niet. Het betreft: het openen van een niet-Excel-bestand, bijvoorbeeld een .TXT of een .CSV-bestand, het openen van een werkmap die laatstelijk door een andere Excel-versie was herberekend, het opslaan van een werkmap op het moment dat de optie Berekenen voor opslaan actief is, het invoegen of verwijderen van werkbladen, of het wijzigen van de namen ervan, het invoegen van cellen, rijen en/of kolommen in een werkblad, het filteren of (on)zichtbaar maken van rijen, het toevoegen, wijzigen of verwijderen van bereiknamen, De waarde van deze functies worden altijd opnieuw bepaald herberekend als Excel gaat herberekenen. Verdieping Een aantal Excel-functies staat bij een groot aantal Excel-gebruikers in het zwartboek, omdat ze volatiel zouden zijn. Het betreft met name de functie sINDEX, RIJEN, KOLOMMEN en BEREIKEN. De waarde van deze functies wordt alleen opnieuw bepaald als daar daadwerkelijk...