Doorgaan naar hoofdcontent

Excel: Aanpak voor de opzet van rekenmodellen

Informatieanalyse

· Zet alle gegevens die nodig zijn voor het rekenblad op een rijtje.
· Bepaal de onderlinge samenhang van deze gegevens: wat hoort bij wat.
· Bepaal welke gegevens afleidbaar zijn van andere en hoe ze af te leiden zijn; zo berekenen we het totaal
  van een orderregel met de formule aantal * prijs en het eindtotaal als som van alle aantal * prijs.
· Maak de gegevens ondeelbaar. In een kolom mogen bij voorkeur alleen gelijksoortige gegevens staan. 
  Adres moeten we dus splitsen in straat, huisnummer en eventuele toevoeging. Namen in bijvoorbeeld voor-
  , achternaam en tussenvoegsel.
· Bepaal ten slotte welke gegevens we direct moeten invoeren.

Opzet van de rekenbladen

Bij het opzetten van het rekenblad gaan we uit van het volgende procesmodel:

· Er gaan gegevens in: de input.
· De gegevens ondergaan een bewerking: het proces.
· Er gaan gegevens uit: de output.

Veelal is dit niet alleen maar eenrichtingsverkeer: bewerkte gegevens kunnen weer de input vormen voor nieuwe bewerkingen. We kunnen er ook gaandeweg de opzet achterkomen dat er input ontbreekt omdat er output gevraagd wordt die we niet kunnen afleiden uit bestaande gegevens. Het kan dus zijn dat we stappen moeten herhalen.

We gaan de opzet nu zodanig maken dat het model terug te vinden is. We gaan input, bewerking en output zo overzichtelijk mogelijk van elkaar scheiden.

De output

We beginnen achteraan omdat juist het gewenste resultaat bepaalt welke gegevens we nodig hebben.

· Beschrijf welke overzichten we nodig hebben en waar we die overzichten en van welke gegevens we die af
  moeten leiden
· Beschrijf de grafieken die we nodig hebben, af te leiden van welke gegevens
· Maak aparte bladen voor grafieken en overzichten
· Gebruik bij overzichten en grafieken namen voor bereiken en baseer deze namen waar mogelijk op 
  flexibele bereiken

Invoerbladen

De volgende stap is de opzet en de uitwerking van de invoerbladen.
· Maak invoerbladen voor de gegevens naar hun onderlinge samenhang.
· Maak eventueel een apart blad voor constanten als omrekenfactoren e.d.
· Maak invoervelden en valideer deze desgewenst.
· Maak voor de opmaak zoveel mogelijk gebruik van stijlen zodat opmaak en beveiliging centraal aan te
  sturen zijn.

Opzet bewerkingsbladen

En ten slotte de verschillende bewerkingsbladen.
· Maak aparte rekenbladen voor het aansturen van grafieken en overzichten.
· Maak de formules om de gegevens van de invoerbladen om te zetten naar de juiste resultaten.
· Hou er bij het maken van formules rekening mee eventuele criteria zo veel mogelijk in afzonderlijke cellen
  te zetten
· Hou er bij het opzetten van formules zoveel mogelijk rekening mee dat bereiken met invoergegevens
  kunnen groeien.
· Hou bij het opzetten van formules ook rekening met fouten.
· Documenteer eventueel je formules zodat het voor anderen te begrijpen is.
· Maak voor de opmaak zoveel mogelijk gebruik van stijlen zodat opmaak en beveiliging centraal aan te
  sturen zijn.

Eventueel gebruik van VBA

· Maak eventueel menuknoppen en besturingsknoppen
· Verberg bladen als we ze alleen maar voor bewerking gebruiken of als ze niet in beeld hoeven te zijn

Beveiliging

· Zorg ervoor dat de formules zelf desgewenst uit beeld blijven en alleen het resultaat zichtbaar is
· Beveilig werkbladen en werkmap zodanig dat alleen invoer en bekijken mogelijk zijn
Labels:

Reacties

Een reactie posten

Populaire posts van deze blog

Excel: VBA script om wachtwoord te verwijderen

Af en toe krijg ik een vraag om een wachtwoord van een Excel blad te halen. Doodsimpel met VBA. Hier een script dat ik gebruik: Sub WachtwoordCrack()     Dim a As Integer, b As Integer, c As Integer, d As Integer, _     e As Integer, f As Integer, g As Integer, h As Integer, _  I As Integer, j As Integer, k, m As Integer     Dim begin As Date, eind As Date     Dim duur As String     Dim objSheet As Worksheet     begin = TimeValue(Time)     On Error Resume Next     For Each objSheet In Application.Worksheets         For a = 65 To 66: For b = 65 To 66: For c = 65 To 66             For d = 65 To 66: For e = 65 To 66: For f = 65 To 66                 For g = 65 To 66: For h = 65 To 66: For I = 65 To 66                     For j = 65 To 66: For k = 65 To...
1 reactie
Meer lezen

Excel 2013: uniek aantal in draaitabel

Tot en met versie 2010 was het in Excel lastig om in een draaitabel een uniek aantal (DISTINCT COUNT) te tellen. We geven een voorbeeld op basis van een verkoperslijst. In deze lijst kunnen we zien welke verkopers welke artikelen hebben verkocht. Willen we nu in een draaitabel laten zien hoeveel artikelen een verkoper heeft verkocht, dan krijgen we wel de aantallen maar niet de unieke aantallen te zien. Om toch de unieke aantallen te laten zien, hebben we een aantal stappen nodig. Op het moment dat we de draaitabel invoegen, krijgen we in Excel 2013 dit dialoogvenster: Onderaan zien we daar een nieuwe optie: Deze gegevens toevoegen aan het gegevensmodel . Deze optie moeten we aanvinken, voor we op OK klikken. We krijgen dan een iets ander beeld dan normaal: Normaliter krijgen we alleen de veldnamen. Nu zien we er het woord Bereik boven staan. Voor het voorbeeld heb ik nu Verkoper toegevoegd aan Rijen en Artikelomschrijving aan Waarden . Het resultaat is identiek...
Een reactie posten
Meer lezen

Excel: laatste datum voor een groep, draaitabel of matrixformule?

Via een Excel groep krijg ik de vraag hoe je de laatste datum voor een groep er uit kunt pikken. We geven hier even de voorbeelddata: Voor zover ik kan zien zijn er in ieder geval twee mogelijkheden: met matrixformules en met een draaitabel . Oplossing: draaitabel We zullen het in dit voorbeeld maar even helemaal volgens de regels van de Excel kunst doen. Voor het maken van de draaitabel heb ik de lijst eerst omgezet naar een tabel ( INVOEGEN => DRAAITABEL ). De naam veranderen we dan even van Tabel1 in draaitabel . Vervolgens maken we de draaitabel. Via Waardeveldinstellingen kiezen we dan voor het datumveld voor Max en bij Getalnotatie voor Datum . De kopjes zetten we even om naar Naam en Laatste datum . Klaar. Oplossing: matrixformules Voor dat we de matrixformules gaan maken, creëren we eerst namen met flexibele bereiken: datum =VERSCHUIVING(Blad1!$B$2;0;0;AANTALARG(Blad1!$B:$B)-1;1) naam =VERSCHUIVING(Blad1!$A$2;0;0;AANTALARG(Blad1!$A:$A...
Een reactie posten
Meer lezen