Hvordan kvalitetssikre regneark

Av Gjermund Dahl og Kai-Erik Uleberg, Norconsult AS

Regneark: Disse fine datafilene som kan være så nyttige, men som også kan skape store frustrasjoner og masse arbeid. De kan brukes til å kombinere store mengder data for å regne ut omtrent hva det måtte være. Med egendefinerte formler og interne miniprogrammer (makroer) finnes det knapt grenser for hva som går an. I mange organisasjoner og bedrifter er den daglige driften fullstendig avhengig av regneark. Regnearkene varierer fra de aller enkleste til store, komplekse systemer. Så var det dette med kvalitetssikring, da. Er det virkelig nødvendig? Og hvordan gjør man det?

En studie fra Oracle, Performance Management: An Incomplete Picture, viser at bedrifter stadig vekk baserer viktige beslutninger på informasjon i regneark. Da er det kritisk at underlaget er til å stole på. For å oppnå det, må alle kritiske regneark være under god kontroll. Det forutsetter full oversikt over deres funksjoner, status og kvalitet, som igjen krever oppdatert dokumentasjon.

Det er menneskelig å feile, noe som da også skjer rett som det er. Om tallmaterialet er stort nok, blir vi trøtte og gjør feil før eller siden. Et regneark blir aldri trøtt. Regneark gjør heller ikke feil hvis det er satt opp riktig. Men hvis det inneholder feil, vil det gjøre den samme feilen konsekvent, uten at vi nødvendigvis oppdager feilen før det er for sent.

Så hvordan kvalitetssikrer man regneark? Vi vil i denne artikkelen gi en oversikt over «beste praksis» fra farmasøytisk industri. Det er en industri med strenge krav til dokumentasjon og testing, inkludert datasystemer, og inkludert regneark.

UTFORDRENDE FILER

Regneark tilbyr kraftige funksjoner med lav brukerterskel. De kan følgelig lages av brukere som ikke har erfaring med applikasjonsutvikling fra før. Noen typiske utfordringer er:

1. Hvem som helst kan utvikle regneark
2. Alle har tilgang til dem, og regnearkene kan enkelt spres via nettverket
3. Regneark kan brukes uten at resultatene dokumenteres eller kontrolleres på andre måter
4. Hvem som helst kan gjøre endringer i layout og funksjoner
5. Kalkulasjoner blir ikke tilstrekkelig testet på forhånd
6. Varierende bruksmiljøer (for eksempel typer datamaskin, operasjonssystem, versjon) mens utviklingen derimot er gjort under bare en enkelt variant av disse
7. Regnearkene møter ikke nødvendigvis de krav som stilles av kunder og offentlige instanser

En profesjonell utvikler forstår ut fra erfaring hvor feil kan oppstå og hvor kritiske de er, og er derfor vant med å gjøre risikovurderinger og kvalitetstester. For en uerfaren bruker er det i teorien ingenting som hindrer henne fra å utvikle regneark med graverende feil. Slike feil kan godt dukke opp når man minst venter det, når det passer dårligst og når konsekvensene blir størst. Her lister vi opp noen typiske feil:

• Feil i egendefinerte formler
• Feil under inntasting
• Mangelfulle eller ingen forhåndstester
• Mangelfulle eller ingen restriksjoner mot feil inntasting
• Utdatert versjon av regnearket benyttet
• Ukjente, mellomliggende formler i skjulte rader og kolonner påvirker resultatet
• Feil tallformat

Noen eksempler
I 2010 publiserte økonomene Reinhart og Rogoff artikkelen «Growth in a Time of Debt». Konklusjonen deres var at når et lands offentlig gjeld overstiger 90 prosent av BNP blir økonomisk vekst vesentlig lavere enn i land med lavere gjeld. Tre år senere demonstrerte akademiske kritikere at dataunderlaget ikke støttet forfatternes konklusjoner. Blant annet hadde Reinhart og Rogoff markert feil celler i et regneark og dermed regnet gjennomsnittet i 15 i stedet for 20 land, i tillegg til selektivt å ekskludere år med høy gjeld i kombinasjon med normal vekst. Det alvorligste var at artikkelen fikk betydelig innvirkning på valget mellom budsjettpolitiske modeller i mange land, og konsekvensene kan vise seg å bli kostbare.

Artikkelen fortsetter etter annonsen

annonse

Da euroen kom til Trondheim

Se også:

• European Spreadsheet Risks Interest Group
• Researchers Finally Replicated Reinhart-Rogoff, and There Are Serious Problems
• En flau og kostbar feil; Innflytelsesrik økonomisk forskning basert på feil tall

Med andre ord gjør til og med de profesjonelle feil, men heldigvis er ikke alle feilene like alvorlige. Norges Bank klarte å publisere en feil i sin Pengepolitiske rapport II/2011. Tallet på ventet gjennomsnittlig styringsrenteprosent i tredje kvartal 2011 skulle vært 2,39, men ble oppgitt som 2,35. Feilen ble raskt korrigert, og sjansen for renteendring i august det året steg fra 70 til 100 prosent.

Under OL i London i 2012 ble fire øvelser oversolgt med flere tusen billetter. Et av stabsmedlemmene hadde nemlig tastet inn 20.000 i stedet for 10.000 gjenværende billetter i det sentrale regnearket. De plassløse fikk heldigvis billetter til andre øvelser i stedet.

KATEGORIER AV REGNEARK

Det er viktig å vite hvor man skal legge lista. Det er her som ellers: For mye eller for lite blir feil. Regnearkets bruksområde har stor betydning for hvor mye arbeid som bør legges ned i utvikling og testing.

Regneark brukes på ulikt vis:

• som en enkel kalkulator for engangsbruk
• som en erstatning for en tekstbehandler hvor beregninger også blandes inn i teksten
• som en database, eller
• som en mal som brukes gjentatte ganger hvor spennet er fra enkle beregninger til større komplekse systemer med tilhørende VBA makroer eller linker til datakilder fra andre systemer.

Det er ikke nødvendig å legge inn samme innsats i testingen av regneark fra de ulike bruksområdene- Figur 1 viser at innsatsen i testfasen øker med økende kompleksitet i regnearket.

Hvor omfattende bør man så teste? Et kritisk, komplekst regneark, som for eksempel har betydelig innvirkning på et produkts kvalitet, mange logiske funksjoner, lenker til andre datakilder over nettverket, makroer eller egendefinerte funksjoner, bør testes tilsvarende grundig, fordi muligheten for feil øker med kompleksiteten.

Her har typiske bruksområder:

Regneark for engangsbruk

Regnearket lages og brukes av en og samme person, og brukes til å løse en bestemt oppgave, omtrent som man bruker en kalkulator. Filen blir slettet eller arkivert, eller regnearket blir ikke lagret i utgangspunktet.
Regneark som database
Regnearket brukes som et lagringssted for større mengder data. Det gjøres ingen kalkulasjoner på de lagrede dataene. Det brukes bare standardfunksjoner i applikasjonen.
Regneark som mal
Regnearket er satt opp på forhånd med dedikerte celler for å legge inn data, formler for kalkulasjoner, statistiske funksjoner, makroer, oppslag eller kobling til andre datakilder. Resultatet lagres som en unik fil.

HVORDAN UTFORME ET GODT REGNEARK

Spesifikasjon
Hvis regneark skal utformes som en mal, bør man starte med å lage en spesifikasjon. Denne bør inneholde en beskrivelse av hensikten med regnearket, hvilken arbeidsoppgave det skal benyttes til, en detaljert beskrivelse av kalkulasjoner eller andre oppgaver som skal gjøres, og en nøyaktig beskrivelse av hvordan resultatet skal presenteres (for eksempel antall gjeldende sifre, desimaler og benevning).

Struktur
Et regneark kan lett bli uoversiktlig, og det er derfor viktig å tenke struktur helt fra begynnelsen av. Her en noen tips:

Toppteksten bør inneholde tittel, filnavn og -bane, versjonsnummer, samt en referanse til spesifikasjon eller siste testrapport. Da vil en bruker raskt kunne identifisere regnearket, kontrollere at filen er hentet fra riktig sted, og at riktig versjon benyttes.

Regneark leses på samme måte som man leser en bokside, fra venstre til høyre, rad for rad nedover, og innholdet bør derfor struktureres på samme måte. Da vil regnearket oppleves brukervennlig, og det blir lettere for en annen part å sette seg inn i det.

Dersom utskriften skal signeres, er det en fordel å sette inn egne felter for signatur og dato under sluttresultatet. Her bør det være felter for både den som utfører utregningen, og for den som skal kontrollere i etterkant.

I bunnteksten bør man ha sidetall og totalt antall sider, slik at det blir mulig å kontrollere om utskriften er komplett.

Fargekoder
Bruk av farger kan øke lesbarheten og brukervennligheten til et regneark. Vårt råd er å være ryddig og konsekvent. Hvis det stilles krav til bruk av farger, bør korrekt bruk verifiseres i testfasen.

Datasikkerhet, datavalidering
For faste, kritiske inndata kan det være fornuftig å definere lister over alle aktuelle verdier. Brukeren må da velge fra listen, og muligheten for feil inntasting blir mindre.

Dersom applikasjonen har en innebygd valideringsfunksjon er det en god ide å bruke denne til å sikre riktig formatering av inndata (tekst, tallverdi, antall desimaler, datoformat, og slike ting).

Lås alle celler utenom inndatacellene, og bekreft dette i testfasen. Cellelås og passordbeskyttelse gir en ekstra sikkerhet, men husk at alle som kjenner passordet kan låse opp og endre regnearket uten at det registreres noe sted. Her kan opplæring og arbeidsinstrukser være til hjelp.

Restriksjoner på inndata må brukes med forsiktighet og ettertanke. Slike tiltak skal ikke gå på bekostning av brukervennlighet, snarere støtte den ved å gjøre arbeidsprosessen mer intuitiv.

RISIKOANALYSE

Det bør gjennomføres en dokumentert risikovurdering for regneark som skal behandle forretningskritisk eller produktkritiske data.

Noen grunnleggende spørsmål som risikoanalysen skal besvare, er:

1. Hva er påvirkningen på kvalitet, dataintegritet og business?
2. Hvor stort er problemet om data som er generert av regnearket er feilaktige eller går tapt?
3. Hva er sannsynligheten for at regnearket genererer feilaktige data eller at data går tapt

Punkter som da bør vurderes er:

• Hvor kritisk er regnearket og de data det behandler?
• Kompleksitet eller sannsynlighet for feil. Disse går hånd i hånd. Komplekse regneark har større sjanse for å generere feil. Det enkleste er ofte det beste.
• Modenhet. Det kan være en fordel om regnearket er brukt tidligere.
• Sannsynlighet for å oppdage og korrigere eventuelle feil. De feilene man ikke oppdager i tide er de skumleste feilene.
• Innvirkning på kvaliteten i produktet eller tjenesten som regnearket brukes til
• Innvirkning på dataintegritet (troverdige og intakte data)

Når dette er behørig vurdert og tenkt gjennom, er det klart for å lage en strategi for testing og sikring av data:

• Testomfang: Hva skal testes og hvor omfattende skal testen være?
• Datasikkerhet og -integritet
  • Hvordan sikrer vi at riktig versjon av applikasjonen benyttes?
  • Hvordan håndterer vi filen med malen
  • Hvordan håndterer vi resultatfilene?
• Hvilke tiltak må på plass for å sikre kontroll over endringer? Hvordan unngår vi at planlagte eller utilsiktede endringer skaper feil i regnearket?
• Hvilken påvirkning, positiv eller negativ, har operativsystem og nettverk på datasikkerhet og dataintegritet?

Risikoanalysen dokumenteres på samme måte som alle andre formelle dokumenter, så klart.

TESTING

Når man har definert testomfanget under risikoanalysen, handler det om to ting: Å beskrive testene godt, og å utføre testene riktig

Her må det beskrives i detalj hva testpersonen skal gjøre og hvilke akseptkriterier som skal tilfredsstilles. En god testbeskrivelse deler opp oppgaven i overkommelige og målbare trinn. Hvert trinn kan ha ett eller flere akseptkriterier slik at det blir enkelt å avgjøre om trinnet er en suksess eller ei. Erfaringsvis fungerer det godt med et tabelloppsett med kolonner for testbeskrivelse, forventet resultat, faktisk resultat, samt utfall (for eksempel «Akseptert» eller «Ikke akseptert»).

Det lyder enkelt «å utføre testen riktig», men realiteten er overraskende nok en ganske annen. For å sikre at testene utføres nøyaktig slik de er beskrevet, er det en god idé å bruke en utenforstående som ikke har vært involvert i å utvikle regnearket eller å utarbeide testene. Det er også viktig å avklare kjørereglene på forhånd. Her følger noen eksempler på slike regler:

• Utfør én test om gangen. Arbeid aldri på flere tester parallelt. Gjør deg ferdig med hvert trinn før du går videre til neste.
• Fyll ut underveis. Ikke i etterkant.
• Undertegn og dater når du er ferdig.
• Bruk penn eller annet permanent skriveredskap.
• Skriv datoer på et enhetlig format, for eksempel 1. januar 2014.
• La ikke felter stå tomme når du er ferdig, da dette gir et tvetydig uttrykk. Skriv ganske enkelt «Skal ikke brukes» dersom feltet ikke skal brukes.
• Unngå bruk av gjentakelsestegn.
• Unngå bruk av korrekturlakk. Gjør du en feil, sett en rett og fin strek over, slik at det fortsatt er mulig å lese hva som ble opprinnelig skrevet. Skriv den riktige innføringen ved siden av. Slik ser det penere ut, og det hersker ingen tvil om hva som er feil og hva som er riktig.

Test av beregningsformler
Kontroller at alle cellereferansene stemmer – bruk gjerne innebygde verktøy for dette. Alle egendefinerte beregningsformler, inklusive bruk av logiske funksjoner (IF, AND, OR og så videre) må testes, inkludert håndtering av «ekstremverdier» (0, negative, utenfor grenser). Slik testing gjøres ofte ved å sammenligne med manuelle beregninger.

Egendefinerte makroer eller koblinger mot andre datakilder må også testes.

Avrundning/forkortning
Vær bevisst på hvordan et tall avrundes til et gitt antall desimaler (opp eller ned) eller når det forkortes uten avrunding. Dette bør være beskrevet i kravene og verifiseres i testfasen.

Sikker plassering
Et testet regneark bør lagres på nettverksdisk i en katalog med tilgangskontroll. Lagre gjerne filen som et templat slik at en lesekopi åpnes når regnearket skal brukes. Ferdig utfylt regneark bør lagres i en nærmere spesifisert (prosjektrelatert?) katalog på en nettverksdisk slik at det kan finnes igjen. Filformatet det lagres i kan gjerne være PDF. Det forutsettes her at nettverksdiskene har definerte og fungerende rutiner for sikkerhetskopi.

OPPSUMMERING

Fordi regneark er så fleksible, er det nærliggende å bruke dem til oppgaver de ikke er designet for. Det er viktig å tenke gjennom om regnearket passer til bruksområdet, og eventuelt vurdere å gå over til en annen, mer egnet applikasjon.

For å kvalitetssikre et regneark anbefaler vi denne arbeidsflyten:

• Planlegge
• Spesifisere
• Utvikle / endre
• Risikoanalyse
• Teste
• Gi opplæring
• Ta i bruk

Ved behov for endringer begynner prosessen på nytt. Alle trinn i prosessen dokumenteres.

På denne måten har du god kontroll over viktige data, du har oppnådd et høyere kvalitetsnivå, og du har høynet sjansene dine for å ta riktige beslutninger.