Zo werkt Excel

Datumveld handig groeperen in een draaitabel

Bas — Sun, 01 Aug 2010 11:16:38 +0000

Het zal regelmatig voorkomen dat er een datumveld zit in je brongegevens voor een draaitabel. In het onderstaande voorbeeld zie je verkoopstatistieken per dag.

De eerste kolom is een datumveld. De sheet bevat zo’n duizend rijen. Een draaitabel is een prima hulpmiddel om deze gegevens overzichtelijk samen te vatten. Als je wil weten hoe je een draaitabel moet maken kijk dan nog eens hier.

In de draaitabel wil ik straks de mogelijkheid hebben om flexibel op maand, kwartaal en jaar te rapporteren. Normaliter zou ik daarvoor extra kolommen maken die op basis van de datum vervolgens de maand, het kwartaal en het jaar afleiden. Echter de draaitabel kan dit automatisch voor je doen. (more…)

Unieke waarden afdwingen met behulp van validatie

Bas — Sun, 25 Jul 2010 10:13:36 +0000

Met validatie is het mogelijk om de gebruiker een waarde in een cel te laten invoeren met een vervolgkeuzelijst. Als je wilt weten hoe je dit moet doen, kijk dan nog eens hier en hier. Met dezelfde techniek is het ook mogelijk om te controleren of een gebruiker een unieke waarden aan een lijst toevoegt.

Stel bijvoorbeeld dat we in de bovenstaande werkblad willen voorkomen dat een gebruiker in cel B6 opnieuw de waarde “A123″ invoert. We kunnen dan het volgende doen. (more…)

Minder fouten met de SOM functie

Bas — Fri, 25 Sep 2009 04:46:42 +0000

Het is algemeen bekend dat spreadsheet foutgevoelig zijn. Een van de meest voorkomende fouten is een SOM functie die niet meer naar het juiste bereik wijst. Deze fout ontstaat doordat er later rijen worden ingevoegd. Een voorbeeld van het ontstaan van deze fout zie je in het volgende filmpje.

(more…)

Getallen optellen op basis van meerdere voorwaarden met SOMMEN.ALS

Bas — Mon, 07 Sep 2009 03:00:35 +0000

In het artikel “Getallen optellen op basis van meerdere voorwaarden“hebben we gezien hoe je met de functie SOMPRODUCT getallen kunt optellen op basis van meerdere voorwaarden. We gebruikten daarbij het volgende voorbeeld:

Het subtotaal voor regio Zuid en productgroep Fruit in cel E12 berekende we met de formule:

=SOMPRODUCT(–($B$3:$B$10=C12);–($D$3:$D$10=D12);$E$3:$E$10)

Vanaf Excel 2007 hebben we de beschikking over een goed alternatief: de functie SOMMEN.ALS(optelbereik; criteriabereik1; criteria1;…). (more…)

Normale menu’s in Excel 2003

Bas — Mon, 31 Aug 2009 04:00:21 +0000

In Excel 2003 zie je standaard een ingekort menu met daarin de door jou meest gebruikte opties. De overige opties krijg je te zien als je op het dubbele pijltje onderaan in het menu (» maar dan een kwartslag gedraaid) klikt.

Microsoft heeft dit ongetwijfeld bedoeld als extra gebruikersvriendelijkheid. Op deze manier wordt je niet overladen met allerlei menu-opties die je zelden gebruikt. Echter voor mij werkt het niet. Ik ben visueel ingesteld en verwacht een bepaalde menu-optie op een vaste hoogte in een menu. Dat werkt niet als het menu er iedere keer anders uitziet. Gelukkig kun je deze optie ook uitzetten. (more…)

Een webquery dynamisch maken met parameters - deel II

Bas — Tue, 25 Aug 2009 05:00:34 +0000

In het artikel Aandelenkoersen ophalen met Webquery hebben we de koersen van het afgelopen jaar van het fonds KPN met een webquery van internet opgehaald in Excel. In het vorige artikel hebben we een start gemaakt met het aanpassen van de webquery, zodanig dat je ieder willekeurig fonds kunt ophalen. We hebben de definitie van de statische webquery opgeslagen als ‘iqy’ bestand.

We moeten dit bestand aanpassen om een dynamische webquery mogelijk te maken (waar Excel vraagt om een fondsnaam). Dit kunnen we niet in Excel zelf regelen. We moeten de eerder opgeslagen query file bewerken met (bijvoorbeeld) het kladblok. We krijgen dan het volgende te zien:

In de regel met de url moeten we nu een aanpassing maken. We willen dat het deel achter het ‘=’-teken wordt vervangen door een fonds dat we zelf opgeven. (more…)

Een webquery dynamisch maken met parameters - deel I

Bas — Wed, 19 Aug 2009 06:03:25 +0000

In één van de vorige artikelen (Aandelenkoersen ophalen met webquery) hebben we gekeken hoe we met een webquery gegevens automatisch kunnen downloaden van een webpagina.

De query die we toen gemaakt hebben, haalt de slotkoersen van het laatste jaar van KPN op van de site www.behr.nl. Maar wat nu als je niet de koersen van KPN, maar die van Ahold of Aegon wilt ophalen? Moeten we voor ieder fonds een aparte query bouwen? Gelukkig niet. We kunnen de webquery dynamisch maken door er parameters aan toe te voegen. (more…)

Tekst manipuleren met formules

Bas — Wed, 12 Aug 2009 05:12:15 +0000

In een vorig artikel hebben we aandelenkoersen opgehaald vanaf internet. We kregen toen het volgende resultaat:

De koersen staan nu in Excel, maar eerlijk gezegd kunnen we er nog niet zoveel mee. De datum en de tijd staan in één kolom, gescheiden door een dubbele punt en een spatie. Verder staat er op rij 117 nog een uitzondering:

090409: 9.55X

Gelukkig kunnen we met formules ervoor zorgen dat de datum en de koers, ieder in het juiste formaat in een eigen kolom komen te staan. (more…)

Aandelenkoersen ophalen met Webquery

Bas — Mon, 03 Aug 2009 06:00:17 +0000

In een vorig artikel hebben we gekeken naar het berekenen van het rendement op een aandelenportefeuille. Om dit te kunnen doen heb je op z’n minst koersinformatie nodig. Deze is op internet volop te vinden. Echter hoe krijg je deze gegevens makkelijk in Excel? Knippen en plakken lukt meestal wel, echter met een webquery kun je dit automatiseren.

Als voorbeeld gaan we kijken naar de slotkoersen van het fonds KPN van het afgelopen jaar. Deze informatie kun je o.a. vinden op de website van Behr. Hier vind je een pagina met de ‘kale’ koersen.

Deze pagina gaan we met een webquery overhalen naar Excel. Hiervoor gaan we in Excel naar het tabblad Gegevens en kiezen we voor de optie ‘Van web’. (In Excel 2003 gebruik je: Data -> Externe gegevens importeren -> Nieuwe webquery). (more…)

Het behaalde rendement op een aandelenportefeuille

Bas — Wed, 22 Jul 2009 03:22:51 +0000

Een tijdje geleden was ik bezig een overzicht te maken van de waarde van mijn beleggingsfondsen. De waarde alleen zegt niet zoveel, ik ben ook geinteresseerd in het behaalde rendement. Het leek mij een eenvoudig klusje om dit uit te rekenen, maar achteraf bleek dit toch lastiger dan ik dacht. Laten we met een eenvoudig voorbeeld beginnen. Stel ik heb de volgende fondsen gekocht:

(more…)