Vedlegg 2. Teknisk manual

Denne tekniske manualen viser hvordan man kan gå fram for å lage tabeller og diagrammer som kan inngå i arealregnskapet for kommunens arealreserver (kap. 4 i veilederen) og til det samlete arealregnskapet (kap. 5 i veilederen). Framgangsmåten er i utgangspunktet den samme, med den forskjellen at datagrunnlaget for det samlete arealregnskapet inneholder arealformål for både gjeldede kommuneplan og forslag til ny kommuneplan. Den tekniske manualen er ment som et hjelpemiddel for å vise hvordan man går frem for å lage et arealregnskap i et GIS-verktøy, regnearkprogram og alternativt analyseprogram.

For å kunne følge manualen trenger du å ha installert et GIS-verktøy. Manualen viser til eksempler fra ArcGIS, men det er også mulig å gjennomføre den metoden med andre lignende GIS-verktøy som QGIS. Fremgangsmåten som blir brukt er omtrent det samme for hvert program. Videre brukes regnearkprogrammet Excel for å analysere og legge til data i etterkant av operasjonene i GIS-verktøyet.

Til slutt vises hvordan rådata fra regnearkprogrammet kan bli brukt i et analyseprogram (her vist i Power BI). I slike programvarer kan data bli analysert noe enklere og mer dynamisk enn i regnearkprogrammer. Eksempelvis har også programvaren Power BI blitt brukt arealregnskap for Agder fylkeskommune for å gi publikum enkel tilgang til datagrunnlaget, slik at de kan analysere data selv.

I manualen beskrives fremgangsmåten for å lage en enkel versjon av arealregnskapet. I tillegg viser manualen også hvordan kommunen kan gå videre med å lage en mer avansert versjon for ytterligere analyser.

1 Om manualen

1.1 Sluttproduktet

Sluttproduktene i denne manualen er tabeller og diagrammer i et regneark eller i et analyseprogram. Se eksempel i figur 1 og 2.

Figur 1: Eksempel på tabell i regneark som viser forskjell på gjeldende og ny plan.

Forstørr bilde

Figur 2: Eksempel på tabeller og diagrammer laget i et analyseprogram.

Forstørr bilde

1.2 Hva slags datasett jobber vi med?

I arealregnskapet jobber vi med flere typer temadata. Arealregnskapet bygger på kartdata om planlagt arealbruk, faktisk arealbruk, naturdata og eventuelt kartdata som deler inn kommunen i administrative enheter eller geografiske soner. Hvilke geografiske datasett som må være med i datagrunnlaget for arealregnskapet og hvilke som kan tas med i datagrunnlaget er gjort rede for i veilederens kapittel 3.

Figur 3: Oversikt over temadata som inngår i datagrunnlaget for arealregnskapet.

Forstørr bilde

1.3 Hvilke utfordringer kan datasettene gi?

Arbeidet med datasettene i GIS og i regnearkprogram kan gi noen utfordringer. I det følgende er det beskrevet noen temaer som dere bør være bevisst på i arbeidet.

Geografisk presisjon

Datasettene som inngår i grunnlaget for arealregnskapet har ulik grad av geografisk presisjon. Det er viktig å huske på at arealregnskapet skal være et beslutningsgrunnlag for kommuneplanens arealdel som gir en oversikt over planlagt og faktisk arealbruk i kommunen på et overordnet nivå. Behovet for geografisk presisjon vil derfor generelt være lavere enn for beslutningsgrunnlag for reguleringsplan og for konsekvensutredninger av arealinnspill.

Kvalitet på geometri

Ulike GIS-programmer og formater for GIS-data kan ha ulik toleranse for kvalitet på geometrien. Det anbefales derfor å kjøre prosedyrer for geometri-kontroll i GIS-programmet som skal brukes i prosessen. Prosedyrer for geometrikontroll kan med fordel kjøres flere ganger i prosessen for å luke ut feil.

Sammenstilling av plandata

Kommuneplandata kan være stykkevis og delt. Denne manualen legger opp til at plandata gjelder for hele kommunens utstrekning. Her forutsettes det at plandata for kommuneplanens arealdel og kommunedelplaner er sammenslått til ett lag som gjelder for hele kommunen.

Manualen er, som veilederen, skrevet med tanke på bruk i kommuneplanprosessen, ikke for reguleringsplaner. Der plankartet til kommuneplanen ikke angir arealformål, men kun angir at reguleringsplaner skal fortsatt gjelde, må dere vurdere om det skal brukes arealformål fra reguleringsplaner for dette området eller et overordnet kommuneplanformål.

Datasett med kolonnenavn som er forskjellig fra produktstandarden

Datasett kan ha kolonnenavn som avviker fra det som er beskrevet i produktstandarden for datasettet. Derfor kan det være nødvendig å undersøke hvilke kolonnenavn som er brukt i hvert datasett. Ugunstige kolonnenavn kan omskrives i GIS-programmet. Ved å omskrive egendefinerte kolonnenavn til standardnavn blir det enklere å sortere rådata i etterkant av GIS-operasjonene. Slik kan det bli det enklere å jobbe med rådata i regnearket i etterkant.

Arealregnskap med mange tilleggsdatasett

Kapittel 3 i veilederen beskriver hvilke typer geografiske datasett som skal inngå og som kan inngå i arealregnskapet. Ved å legge til mange tilleggsdatasett kan det endelige datasettet i GIS bli svært stort og tungt å arbeide med. Det bør derfor vurderes hvor mange valgfrie datasett som legges til, spesielt hvis nye datasett omfatter mange små arealer. Eventuelt kan dere vurdere om det er tilstrekkelig å

inkludere slike datasett kun i og rundt områder som er avsatt eller foreslått avsatt til utbyggingsområder.

Bruk av rasterdata i arealregnskapet

Datagrunnlaget for arealregnskapet bygger på vektordata. Noen rasterdatasett kan også være aktuelle for noen kommuner. Disse datasettene må først vektoriseres. Kombinasjon mellom vektordata og rasterdata kan gjøre et samlet datasett tyngre, siden rasterdata består av kvadratiske celler med en fast størrelse som sjelden sammenfaller med avgrensninger i vektordatasett. Dersom det ønskes flere vektoriserte rasterdata kan dette bli svært tungt å arbeide med. Vurder derfor om ekstra datasett basert på rasterinformasjon kan begrenses til kun utbyggingsområder eller områder som er foreslått avsatt til utbyggingsområder.

Figur 4: Eksempler på vektor- og rasterdata og kombinasjon av disse.

Forstørr bilde

Forstørr bilde

Små polygoner

Der det er mange små polygoner som gir svært mange rader i datasettet er det mulig å bruke Eliminate-funksjonen i GIS. Verktøyet fjerner små, avlange polygoner som kan være resultat av et komplekst sammensatt datasett. Bruk dette med forsiktighet. Ulempen med å bruke Eliminate-funksjonen er at det kan fjerne data om registrert natur som går langs avlange soner slik som bekker og elver. Generelt bør dere også være forsiktig med å bruke Eliminate-funksjonen etter at naturdata er sammenslått med plandata, da dette kan forflytte arealformålsgrensene.

Datasett med forskjellig koordinatsystem

Nedlastede datasett kan ha forskjellig koordinatsystem. Enkelte data kan være laget med tanke på lokale formål, andre datasett skal ivareta nasjonale interesser. Sjekk derfor hva slags koordinatsystem kartdata er lagret i, og lagre de til et koordinatsystem kommunen jobber med lokalt. De vanligste koordinatsystemene er:

• ETRS89 / UTM Sone 32N (EPSG: 25832)
• ETRS89 / UTM Sone 33N (EPSG: 25833)
• ETRS89 / UTM Sone 35N (EPSG: 25835)

Utdaterte datasett med forskjellig oppdateringsfrekvens

Enkelte datasett kan også være utdatert. Treg ajourføring med eksempelvis AR5-datasett gjør at faktisk arealbruk ikke alltid stemmer. Her kan dere vurdere å ta i bruk datasettet SSB arealbruk for å oppdatere data om faktisk arealbruk selv.

Datasett med ulik utstrekning

Flere av datasettene som skal, eller kan inngå, i arealregnskapet dekker ikke alltid hele kommunens landareal. Arealregnskapet må inneholde heldekkende datasett for planlagt arealbruk (arealformål) og faktisk arealbruk (arealtyper). I noen kommuner er ikke datasettet AR5 heldekkende. For å ha data om faktisk arealbruk i slike situasjoner kan AR5-data suppleres med AR50. Framgangsmåten for dette vil likne sammenslåingen av Naturtyper – Miljødirektoratets instruks og DN-håndbok 13 (se 2.4).

Andre temadata trenger ikke være heldekkende.

For datasettet Naturtyper – Miljødirektoratets instruks finnes det dekningskart som viser hvor det har blitt gjort naturundersøkelser. Utenfor dekningsområdet for Naturtyper – Miljødirektoratets instruks kan det være aktuelt å bruke det eldre datasettet DN-håndbok 13. Hvordan de to datasettene kan kombineres er beskrevet i 2.4.

Tomme celler og bruk av kodeverdi 0

Enkelte datasettstandarder bruker verdien 0 som en kode i kolonner, f.eks. står 0 i kolonnen lokalitetskvalitet i Naturtyper – Miljødirektoratets instruks står for en lokalitet med svært lav kvalitet. Dette kan gi utfordringer i et regneark hvis tomme regnearkceller også regnes om til 0. I disse tilfellene bør det opprettes spørringer på andre kolonner i regnearket som stammer fra samme opprinnelige datasett, f.eks. om lokalitetstype inneholder en tekstkode eller er tom. En annen mulighet er å fikse dette i et GIS-verktøy. Her kan eksempelvis celleverdiene for lokalitetskvalitet endres fra nummer til tekst, slik at lokaliteter med svært lav kvalitet har verdien 0 og arealer uten registreringer har tomme celler.

1.4 Oversikt over prosessen

Arbeidet med å lage datagrunnlag, tabeller og diagrammer til arealregnskapet for kommuneplanens arealdel følger denne prosessen:

Figur 5: Skisse over prosessen

Forstørr bilde

I veilederen til arealregnskap i kommuneplanens arealdel beskrives det hvordan arealregnskap kan lages for to faser i arbeidet med kommuneplanens arealdel. I veilederens kapittel 4 forklares det hvordan et arealregnskap kan brukes tidlig i planprosessen til å beskrive arealreserven i gjeldende plan. I veilederens kapittel 5 forklares hvordan et samlet arealregnskap kan brukes for å beskrive planforslaget og endringene mellom gjeldende plan og ny plan.

Datagrunnlaget for de to arealregnskapene er i prinsippet det samme, men det samlete arealregnskapet har også data om den nye planlagte arealbruken i planforslaget. I veiledningen som følger er det tatt utgangspunkt i datagrunnlaget som finnes når det skal lages et samlet arealregnskap. For å lage arealregnskap for arealreserven ser dere bort fra data om ny planlagt arealbruk.

2 Lag datagrunnlaget i GIS

I dette kapittelet beskrives det hvordan datagrunnlaget for arealregnskap skal settes sammen av ulike datasett i et GIS-program. Det sentrale oppgaven i kapittelet er å slå sammen ulike geografiske datasett til ett datasett som deretter eksporteres ut som en tabell med rådata som kan bearbeides videre i et regnearkprogram.

Først vises en oversikt over hva resultatet for dette kapitelet skal bli. Deretter viser manualen hvordan dette skal gjøres skritt for skritt.

2.1 Oversikt over resultatet

Oversikten under viser hvilke kolonner tabellen med rådataene, som blir resultatet etter dette kapittelet, kan inneholde og hvilke datasett kolonnene er hentet fra. Hvilke kolonner som inngår i rådataene vil variere noe mellom kommunene ettersom hvilke datasett kommunen velger å bruke.

Når dere kommer til 2.6 må dere komme tilbake til denne tabellen, for å vite hvilke kolonner i rådataene som bør slettes.

Tabell 1: Oversikt over geografiske datasett og hvilke kolonner fra disse som normalt vil inngå i datagrunnlaget

2.2 Hent data inn i GIS

Last ned datasettene fra geonorge.no eller framskaff dem fra kommunens egne systemer.

Legg datasettene inn i GIS. Her kan det være nødvendig å gjøre noe innledende bearbeiding av de enkelte datasettene for å håndtere noen av utfordringene som er beskrevet i 1.3.

2.3 Klipp datasett mot kommunegrensen

Arealregnskapet skal lages kun for arealer der kommuneplanen gjelder. Datasettene som arealregnskapet skal bygges på må derfor klippes mot kommunegrensen for å sikre at ikke arealer utenfor kommunen lager problemer.

Begynn med å åpne verktøykassen (1). Kjør verktøyet «Clip» (2) for alle datasett mot kommunens totale areal (3). Datasettet for kommunens areal skal være ett polygon. Denne prosessen begrenser alle datasett til kommunens arealer. For kystkommuner gjelder kommuneplanens arealdel ut til 1 nautisk mil. Datasett må eventuelt også klippes mot denne avgrensningen.

Figur 6: Knapp for verktøykasse i ArcGIS

Forstørr bilde

Figur 7: Klipping av datasettet med Clip i ArcGIS.

2.4 Klippe datasettet naturtyper DN-Håndbok 13

I de færreste kommunene vil det være foretatt heldekkende kartlegginger av Naturtyper – Miljødirektoratets instruks. De fleste kommuner vil derfor ha nytte av å legge til registreringer fra det eldre datasettet DN-Håndbok 13 i områder som er utenfor dekningskartet til Naturtyper – Miljødirektoratets instruks. For å unngå dobbelt opp med naturregistreringer, må derfor datasettet DN-Håndbok 13 klippes motdekningskartet til Naturtyper – Miljødirektoratets instruks.

Gå inn på verktøyfanen øverst. Under Analysis ligger det en knapp for verktøykasse hvor du kan finne GIS-verktøy for å arbeide med geodata (1).

Figur 8: Knapp for verktøykasse i ArcGIS.

Forstørr bilde

Søk på verktøyet pairwise erase (1). Legg inn DN-Håndbok 13 som Input Features og Dekningskart som Erase Features.

Figur 9: Klipping av datasettet med Pairwise Erase i ArcGIS.

Figur 10: Kartet til venstre viser datasettet DN-håndbok 13 før det er klippet mot dekningskartet til datasettet Naturtyper – Miljødirektoratets instruks. Kartet til høyre viser resultatet av klippingen.

Forstørr bilde

2.5 Slå sammen datasett

Deretter kan datasettene som skal inngå i datagrunnlaget for arealregnskapet slås sammen. Her brukes verktøyet «Union». Dette verktøyet kombinerer alle datasettene til et datasett. Ofte tillater GIS-programmer kun at to datasett av gangen slås sammen med Union. Da må Union kjøres flere ganger for å få med alle datasettene.

Figur 11: Sammenslåing av datasett med verktøyet Union i ArcGIS.

2.6 Legg inn kolonner for areal

For å kunne lage arealstatistikk trenger vi å finne størrelsen på hvert polygon i datasettet. Hensiktsmessige enheter er m² og dekar (daa). Gå inn på attributtabellen og trykk «Add» (1). Legg så til to nye felt, «Areal_M2» og Areal_Daa». Legg de inn som double (3) og numerisk (4).

Figur 12: Knapp for å legge til kolonner i ArcGIS.

Forstørr bilde

Figur 13: Legge til kolonner i ArcGIS.

Forstørr bilde

Når du velger numerisk format for Areal_M2, velg 0 desimaler som vist under:

Figur 14: Sette antall desimaler for en kolonne i ArcGIS.

For Areal_Daa ønsker vi tre desimaler.

Figur 15: Sette antall desimaler for en kolonne i ArcGIS.

Kalkuler areal i attributtabellen for Areal_M2 først ved å høyreklikke på den nye kolonnen Areal_M2, og velg «Calculate geometry». Enheten skal være i kvadratmeter for det koordinatsystemet dere bruker i kommunen.

Figur 16: Åpne menyen for å beregne areal i en kolonne i ArcGIS.

Forstørr bilde

Figur 17: Beregne areal i kvadratmeter for en kolonne i ArcGIS.

I kolonnen Areal_Daa skal kvadratmeter omregnes til dekar. Omregn til dekar ved å bruke feltet Areal_M2, og del verdien i feltet på 1000. Dette kan du gjøre ved å høyreklikke på Areal_Daa og velge «Calculate Field».

Figur 18: Åpne menyen for å beregne areal i en kolonne i ArcGIS.

Forstørr bilde

Dobbelklikk på Areal_M2 i det nye vinduet (1), del på 1000 ved å skrive inn « / 1000» etter feltnavnet !Areal_M2! (2). Valider utrykket (3) og velg ok.

Figur 19: Beregne areal i dekar i ArcGIS.

2.7 Slett kolonner

Etter å ha slått sammen datasettene med verktøyet «Union» vil det være mange overflødige kolonner i det endelige datasettet. Bruk oversikten i Tabell 1 til å slette unødvendige kolonner. Grunnen til å slette celler er at dette vil gjøre det enklere å behandle rådata i et regnearkprogram senere.

Du kan slette kolonner ved å gå på attribute table (1), høyreklikk på kolonnen du skal slette, og velg delete (2).

Figur 20: Eksempel på sletting av kolonner i ArcGIS.

2.8 Eksport av rådata til regneark

Eksporter attributtabellen til regneark ved å høyreklikke på det samlete datasettet og velg «Export table». Velg et sted å lagre regnearket på datamaskinen ved å klikke på mappeikonet. Formatet dette bør lagres i er kommaseparerte verdier (csv). Husk derfor å skrive inn «.csv» etter filnavnet.

Figur 21: Eksport av attributtabell til regneark.

3 Tilrettelegge datagrunnlaget i regnearkprogram

I dette kapittelet vises hvordan rådataene som er eksportert fra GIS kan bearbeides i et regneark, slik at det blir enkelt å lage tabeller og diagrammer til arealregnskapet. I korte trekk skal det lages nye kolonner i regnearket med rådata der tallkoder blir oversatt til tekstbeskrivelser. Videre skal det lages en kolonne som regner ut arealstørrelser der arealformål er endret fra gjeldende til ny plan. Dette gjøres ved å lage en spørring i et regnearkprogram.

Når dere har gjennomført instruksjonene i dette kapittelet kan dere begynne å lage tabeller og diagrammer til arealregnskapet.

3.1 Import av rådata til regnearkprogram

Last inn CSV-filen med rådata til et regnearkprogram. For å gjøre dette kan dere følge figuren under:

Figur 22: Import av rådata til Excel.

3.2 Verktøy for bearbeiding av tabell

Ved å importere rådata fra CSV-filen på denne måten vil dere få et regneark som vises som en tabell. Tabell i et regnearkprogram regnes om et objekt, slik at det kan filtreres og behandles på en enklere måte enn som et rent regneark.

Hver rad i rådata-tabellen tilsvarer et areal i kommunen med unike verdier hentet fra det sammenslåtte datasettet. Hvert areal har et arealformål i gjeldende plan, et arealformål i planforslaget, data fra AR5 osv. Enkelte celler i kolonner vil mangle verdier. Manglende verdier betyr at det på dette arealet ikke er gjort noen funn. Eksempelvis til dette si det på dette arealet er det ikke registrert dyrkbar jord eller en naturtype.

Resultatet vil se omtrent slik ut:

Figur 23: Eksempel på hvordan rådataene ser ut i et regneark.

Forstørr bilde

Når rådataene er lastet inn som fra CSV tabell kan dere også filtrere på data ved å klikke på nedtrekksmenyene som kommer ved hver kolonne.

De fleste kolonnene vil bestå av tallkoder som beskriver et bestemt arealformål eller en type registrering. For å bruke rådata videre på en enklere måte trengs det å legges inn kodebeskrivelser som omformulerer tallkoder til tekstbeskrivelser. For å omformulere data trenger dere å legge inn nye kolonner.

For å lage nye kolonner kan du klikke på cellen øverst til høyre for en tabell og skrive inn et kolonnenavn:

Figur 24: Hvordan legge til ny kolonne i tabell på regneark

Forstørr bilde

Figur 25: Alternativt kan dere høyreklikke på kolonnenavn i tabellen og sette inn ny kolonne

Forstørr bilde

Når dere senere skal legge inn formler i tabellen holder det å skrive inn formelen i cellen under kolonnenavnet. Formelen vil da kopieres til alle cellene nedover i kolonnen.

Figur 26: Skriv inn formel i cellen under kolonnenavnet for å regne på alle celler i en tabell

Forstørr bilde

3.3 Legge til kodebeskrivelser

Nå trenger dere å legge inn tekstbeskrivelser for tallkodene i rådata-tabellen. I regneark-dokumentet kan dere legge inn flere regneark (faner) (1) som fungerer som oppslagsverk. Oppslagsverket oversetter en tallkode til tekst. Under er et eksempel på et slikt regneark med navn KPAREALFORMÅL (2). I dette eksempelet omformuleres SOSI-koder for arealformål til tekstbeskrivelser.

Figur 27: Regneark med kodebeskrivelser.

Forstørr bilde

På rådata-fanen kan du lage en ny kolonne som du kaller Arealregnskap gjeldende. Her skal vi bruke FINN.RAD-funksjonen (VLOOKUP på engelsk) for å hente inn oversettelsen av koder fra oppslagsverket på fanen KPAREALFORMÅL til fanen med rådata.

FINN.RAD-funksjonen fungerer slik:

=FINN.RAD(Koden du vil slå opp; oppslagsverket som inneholder koden; kolonnebokstaven i oppslagsverket som inneholder teksten som svarer til koden; returnerer et omtrentlig eller nøyaktig samsvar – angitt som 1/SANN eller 0/USANN).

Eksempel på hvordan du kan slå opp tekstbeskrivelser for SOSI-koder under fanen KPAREALFORMÅL.

= FINN.RAD([@KPAREALFOR];KPAREALFORMÅL!A:C;2;1)

Marker cellen under kolonnenavnet (1) og velg / skriv inn formel for =FINN.RAD

Figur 28: Eksempel på hvor du skriver inn formeltekst for å regne på alle celler i kolonnen

Forstørr bilde

Eksempelet viser at det har blitt opprettet fire nye kolonner (E, F, G, H) som viser hovedformål og arealformål i gjeldende plan og ny plan. I kolonnene har FINN.RAD-formelen blitt benyttet for å knytte tallkodene opp mot kodetabellen under fanen KPAREALFORMÅL.

Tilsvarende kan gjøres for alle tallkoder.

Figur 29: Tilsvarende kan gjøres for beskrivelser for arealtype, treslag, bonitet, lokalitetskvalitet osv.

Forstørr bilde

På temasiden for arealregnskap på planlegging.no kan dere laste ned regneark med kodebeskrivelser som brukes til arealregnskapet.

3.4 Opprette kolonne med arealstørrelse for areal med endret formål

Når det skal lages et samlet arealregnskap som viser endringer fra gjeldende kommuneplan til planforslaget, trengs det en kolonne som kun viser arealstørrelsen (i dekar) for de arealene der arealformålet er endret.

For å gjøre dette, må dere legge inn en ny kolonne med en logisk spørring. Dette gjøres med formelen =HVIS (=IF på engelsk).

Formelen kan forstås slik:

=HVIS(Logisk test; Resultat hvis logisk test er sann; Resultat hvis logisk test er usann)

Eksempel for å lage en spørring som viser arealstørrelse der det er forskjell mellom gjeldene og nytt arealformål:

Lag en ny kolonne som dere kaller Arealdifferanse (daa). For å spørre om arealformålet er endret for dette hver rad, skriver dere inn denne funksjonen i første celle under kolonnenavnet:

=HVIS([@[Arealformål gjeldende]]<>[@[Arealformål ny]];[@[Areal_Daa]];0)

Tegnet «<>» spør om verdien i kolonne «Arealformål gjeldende» ikke er lik verdien i kolonne «Arealformål ny». Dersom utsagnet er sant returneres arealverdien i kolonnen Areal_Daa. Dersom utsagnet er usant returneres verdien 0.

I eksemplet under er formålet Arealformål gjeldende (5100 – LNFR) ikke lik verdien i kolonnen Arealformål ny (1110 – Boligbebyggelse). Siden den logiske testen er sann vil formelen returnere verdien 0.14 dekar. Hvis arealformålet hadde vært likt i hver kolonne (gjeldene og ny) ville verdien blitt 0.

Figur 30: Eksempel på hvordan arealformålsdifferanse kan skrives inn med =HVIS formelen

Forstørr bilde

3.5 Flytte kolonne for arealverdier lengst til høyre i rådata-fanen

For å kunne regne på arealformålsendringer i kapittel 4.7 trenger kolonnene for arealverdier Areal_M2, Areal_Daa og Arealdifferanse (daa) å bli flyttet lengst til høyre i regnearket i rådata-fanen.

Marker kolonnen, dra i kanten av den grønne markeringen med ved å holde inne høyre museknapp. Slipp musa der du vil flytte kolonnen til og velg «Shift right and Move» (1).

Figur 31: Flytting av kolonne for arealstørrelse (Areal_Daa) til høyre i rådata-fanen

4 Presentere data med regnearkprogram

Dette kapittelet viser noen tabeller som trengs til arealregnskapet, og hvordan disse kan lages med utgangspunkt i datagrunnlaget som er bearbeidet i kapittelet over. Først gis det en kort introduksjon til noen verktøy og metoder som brukes. Deretter gis beskrivelser av hvordan dere kan lage noen av tabellene som er omtalt i veilederen.

Alle tabeller henter data fra datagrunnlaget som ble etablert i kapittel 3. Framgangsmåtene for å lage de ulike tabellene likner hverandre. Det kan derfor være nyttig å lese igjennom hele kapittelet før dere begynner å følge instruksjonene i et avsnitt. Etter å ha blitt kjent med eksemplene i kapittelet bør det være lettere å lage egne spesialtilpassede tabeller og diagrammer.

4.1 Introduksjon av sentrale verktøy og metoder

I analysene under bør dere bli kjent med databaseverktøyene pivottabell, tabeller, filtre og diagrammer. Under forklarer vi litt om hvordan de ulike verktøyene fungerer.

Pivottabell

En pivottabell kan gruppere data basert på verdier i kolonner og rader. Basert på rådata kan en pivottabell summere opp verdier basert på unike celleverdier. Under kapitel 4.2 viser vi hvordan du kan komme i gang med bruk av pivottabeller. I tillegg har pivottabeller mulighet til å filtrere på data, slik at dere kan avgrense verdiene til et eller flere gitte arealformål, arealtyper eller verdier i andre kolonner.

Selv om pivottabeller er nyttige verktøy, kan det noen ganger likevel være hensiktsmessig å klippe ut verdiene fra en pivottabell og jobbe videre med dem i et vanlig regneark. Et tips er da er å kopiere pivottabellen, men bruke lim inn utvalg og lime den inn igjen som rene verdier det vil si uten formatering som pivottabell.

Tabeller

En tabell er en database basert på kolonnenavn som kan kobles til ved hjelp av tegnet «@» og [Kolonnenavn]. Bruk av tabeller gjør det enklere å regne på data ved at tabellen vet hvor radene slutter, dermed trenger man ikke å markere flere hundretusen rader for å regne på verdier.

Diagrammer

Du kan enkelt lage diagrammer basert på pivottabeller. Marker cellene du vil basere diagrammet på og velg diagram (1).

Figur 32: Sett inn diagram basert på celleutvalg

Forstørr bilde

4.2 Faktisk arealbruk i arealreserven

Denne tabellen skal vise hvilken faktisk arealbruk som finnes i arealreserven i gjeldende plan. (Se kapittel 4 i veilederen for nærmere forklaring.) Tabellen bruker kolonnene Arealformål_gjeldende og Arealtype (AR5).

Sett inn en pivot-tabell ved å velge den øverste venstre cellen (1) i fanen rådata. Automatisk vil alle celler i rådata-fanen velges. Velg Insert (2) og Pivottabell (3).

Figur 33: Sett inn pivottabell fra rådata-fanen

Forstørr bilde

Sett inn en pivottabell i ny fane.

Figur 34: Verktøyboks for innsetting av pivottabell

Sett gjeldene arealformål som rader i pivottabellen (1) og Arealtype som kolonner (2). Sett også Areal_Daa som verdier det skal gjøres statistikk på (3).

Figur 35: Egenskapstabell for pivottabell for faktisk arealbruk i arealreserven

I pivottabellen kan du også lage grupper ved å velge flere rader du vil slå sammen. Høyreklikk etter valget og velg «group» (1).

Figur 36: Gruppering av rader i pivottabell

Forstørr bilde

Begynn med å gruppere arealformålene. Grupperingen av arealformål som er brukt i tabellen under er:

• 1. Bebyggelse og anlegg | 1110 - Boligbebyggelse
• 1. Bebyggelse og anlegg | 1120 - Fritidsbebyggelse
• 1. Bebyggelse og anlegg | Annet
• 2. Samferdselsanlegg og teknisk infrastruktur
• 3. Grønnstruktur
• 5. Landbruk-, natur- og friluftsformål samt reindrift
• 6. Bruk og vern av sjø og vassdrag med tilhørende strandsone

Til tabellen som viser arealreserven er vi kun opptatt av utbyggingsformålene, altså de fire øverste gruppene i lista over. For å filtrere bort unødvendige grupper må dere velge nedtrekksmenyen (1) og velg feltet dere skal filtrer på (2). Velg så radene / gruppene dere vil filtrere bort (3).

Figur 37: Filtrere på radnavn i pivottabellen

For kolonnen AR5 (1) kan du filtere på ulike arealtyper (2). For å vise ubebygde arealer må dere ta med alt annet enn arealtypeverdi: 0, 11, 12, 81 og 82.

Figur 38: Filtrere på kolonneverdier i pivottabell

Forstørr bilde

Nå skal tabellen være ferdig for presentasjon. Dere kan enkelt kopiere over celledata til et nytt regneark, eller legge til egne farger og oppsett for tabellen. I tillegg kan dere også lage enkle diagrammer basert på data herfra.

Figur 39: Tabell for faktisk arealbruk i arealreserven

Forstørr bilde

Figur 40: Eksempel på diagram basert på pivottabell for faktisk arealbruk i arealreserven

Forstørr bilde

4.3 Lokalitetskvalitet for naturtyper i arealreserve (innenfor dekningsområde for Naturtyper – Miljødirektoratets instruks)

Tabellen skal brukes til å gi informasjon om hva slags natur som befinner seg innenfor arealreserven. (Se kapittel 4 i veilederen for nærmere forklaring.) Tabellen bruker kolonnene Arealformål_gjeldende, Arealtype og Lokalitetskvalitet (fra Naturtyper – Miljødirektoratets instruks).

Legg inn pivottabell fra rådata-fanen på samme måte som i kapittel 4.2.

Legg inn gjeldene arealformål som rader (1). Du kan også legge inn gjeldende hovedformål (2). Videre legges Lokalitetskvalitet som kolonner (3), og Areal_Daa som verdier det skal regnes på (4). I tillegg legges arealtype inn som filter (5).

Figur 41: Egenskapstabell for pivottabell for lokalitetskvalitet i arealreserven

Legg inn følgene arealtyper som filtreringskriterie:

Figur 42: Filtreringskriterie i pivottabell for arealtype

Under vises en tabell der det er filtrert på lokalitetskvalitet som viser områder der lokalitetskvalitet er moderat eller høyere.

Figur 43: Eksempel på tabell for lokalitetskvalitet på arealreserve

4.4 Truet naturtype i arealreserven (innenfor dekningsområde)

Denne tabellen viser også registrerte naturtyper innenfor arealreserven, men viser bare naturtyper som er truet.

Lag en pivottabell av rådataene som vist over. Legg så inn gjeldene arealformål som rader (1), om naturtypen er truet som kolonne (2), hovedformål gjeldende og arealtype fra AR5 som filter (3 og 4), og Areal_Daa som verdier det skal regnes på (5).

Figur 44: Egenskaper for pivottabell for truet naturtype i arealreserve

For å begrense utvalget til arealreserven, må dere filtrere på hovedformål (Bebyggelse og anlegg og Samferdselsanlegg og teknisk infrastruktur) og arealtype (alt utenom Bebygd, Samferdsel, Ferskvann, Hav og 0 – ikke registrerte arealer).

Figur 45: Mulighet for å legge inn filter på pivottabell

Figur 46: Filter for hovedformål (venstre) og arealtype (høyre)

Slik blir tabellen seende ut til slutt:

Figur 47: Tabell for truet naturtype i arealreserve

4.5 Natur i arealreserven etter skogbonitet (utenfor dekningskart)

Mye natur befinner seg utenfor områder der det er foretatt registreringer av Naturtyper – Miljødirektoratets instruks. I veilederens kapittel 4.5 forklares det hvordan disse arealene kan beskrives med utgangspunkt i data om skogsbonitet (produksjonsevne i skog). Siden arealreserven som er innenfor dekningskart til Naturtyper – Miljødirektoratets instruks forutsettes vist i egne tabeller, er det nødvendig å filtrere vekk disse arealene.

For å regne på bonitetsklasser utenfor dekningskart kan du hente inn en pivotabell fra rådata-fanen. Velg så arealformål og hovedformål som rader (1), bonitet som kolonner (2), Areal_Daa som verdier (3) og dekningskart som et filter (4).

Figur 48: Egenskaper for pivottabell for bonitet utenfor dekningskart i arealreserven

Som vist i framgangsmåte for tabeller over kan dere klikke på nedtrekksmenyen øverst i pivottabellen for å filtrere vekk områder utenfor dekningskartet.

Figur 49: Mulighet for å filtrere på dekningskart i pivottabellen

Forstørr bilde

Til slutt vil dere få en tabell som ser omtrent slik ut:

Figur 50: Eksempel på tabell for skogsbonitet utenfor dekningskart i arealreserven

Forstørr bilde

4.6 Dyrkbar jord i arealreserven

Datasettet dyrkbar jord brukes for å supplere data om utbyggingsarealer på aktive jordbruksarealer. Når dere skal se på forekomsten av dyrkbar jord i arealreserven, er det derfor nødvendig å kunne filtrere ut dyrkbar jord som ligger på innmarksbeite (arealtype 23) og dessuten ta ut eventuelle overlapp med fulldyrka jord (21) og overflatedyrka jord (22) som følge av feil og manglende presisjon i datasettet.

Lag en pivottabell fra rådata-fanen.

Legg inn arealformål og hovedformål som rader (1), dyrkbar jord som kolonne (2), Areal_Daa som verdier (3) og arealtype som filter (4).

Figur 51: Egenskapstabell for pivottabell for dyrkbar jord i arealreserven

Filter bort arealtyper (AR5) som jordbruksarealer (21, 22, 23) og vann (81, 82) for å se arealer hvor arealformål overlapper områder som er registrert som dyrkbar jord.

Figur 52: Filtrere på arealtype i pivottabell

Forstørr bilde

Tabellen vil se omtrent slik ut:

Figur 53: Eksempel på tabell for dyrkbar jord i arealreserven

Forstørr bilde

4.7 Arealformålsendringer

Når dere har laget forslag til ny kommuneplanens arealdel kan dere beregne omdisponeringer mellom arealformål. Her vises hvordan dere kan regne på arealdifferanser, og få frem hvilke arealformål som skifter til hva. (Se veilederens kapittel 5 for mer informasjon.)

Tabellen viser totalt areal av gjeldene arealformål, hvilke arealstørrelser som tas ut, hvilke arealstørrelser som tas inn, netto endring, og netto endring i prosenstørrelser.

Før dere går i gang med å lage en tabell må dere sørge for at rådata-fanen har tekstbeskrivelser for hvert arealformål, og en egen kolonne med arealdifferanse (arealstørrelse der arealformålet endres fra gjeldende plan til planforslaget). Se kapittel 3.2 og 3.3 for ytterligere beskrivelser om dette.

Lag en ny fane på regnearket som skal hete Arealformålsendringer. Her kopierer du over en komplett formålskodeliste fra fanen KPAREALFORMÅL. Legg kolonnene inn slik at du får fire kolonner:

• KPAREALFORMÅL – Gjeldende
• KPAREALFORMÅL – Ny
• Hovedformål – Gjeldende
• Hovedformål – Ny

Figur 54: Kopierte celler fra fanen KPAREALFORMÅL lagt inn i fanen Arealformålsendringer. Egne kolonner for gjeldende og ny plan.

Forstørr bilde

Legg inn fem nye kolonner kalt:

• Total arealstørrelse – Gjeldende
• Brutto ut (daa)
• Brutto inn (daa)
• Netto endring (daa)
• Netto endring (%)

Figur 55: Legg til egne celler for total arealstørrelse, brutto inn og ut, netto endring, og netto endring %

Marker alle kolonner og velg sett inn en ny tabell (1 og 2)

Figur 56: Marker hele tabellens utstrekning og lag en tabell av utvalget

Forstørr bilde

Figur 57: Marker at tabellen har kolonnenavn

Oppgaven nå handler om å legge inn formler i kolonnene i fanen Arealformålsendringer som henter og summerer data fra fanen Rådata:

• (1) Total arealstørrelse – Gjeldende
• (2) Brutto ut (daa)
• (3) Brutto inn (daa)
• (4) Netto endring (daa)
• (5) Netto endring (%)

Figur 58: Fem nye kolonner legges til i tabellen for å vise arealformålsendringer

Forstørr bilde

Klikk på den tomme cellen under kolonnen «Total arealstørrelse – gjeldende» og bruk =SUMMERHVIS formelen (=SUMIF på engelsk).

For å regne ut summert arealstørrelse per arealformål i gjeldende plan (1) kan du bruke følgene formel:

=SUMMERHVIS(Kolonnen Arealformål_gjeldende fra Rådata-fanen: Kolonnen Areal_Daa fra Rådata-fanen; [@[Kolonnen KPAREALFORMÅL-Gjeldende i Arealformålsendring-fanen]]; Kolonnen Areal_Daa fra Rådata-fanen)

Her har vi brukt utrykket:

=SUMMERHVIS('Rådata'!F:K;[@[KPAREALFORMÅL - Gjeldende]];'Rådata'!K:K)

For kolonnene Brutto ut (daa) (2) og Brutto inn (daa) (3) brukes kolonnen Arealdifferanse (Daa) i Rådata-fanen for å regne på verdier.

=SUMMERHVIS(Kolonnen Arealformål_gjeldende fra Rådata-fanen: Kolonne Arealdifferanse (Daa) fra Rådata-fanen; [@[Kolonnen KPAREALFORMÅL-Gjeldende i Arealformålsendring-fanen]; Kolonne Arealdifferanse (Daa) fra Rådata-fanen)

For Brutto ut (daa) har vi brukt utrykket:

=SUMMERHVIS(Rådata!M:Q;[@[KPAREALFORMÅL - Gjeldende]];Rådata!P:P)

For Brutto inn (daa) har vi brukt utrykket:

=SUMMERHVIS(Kolonnen Arealformål_ny fra Rådata-fanen: Kolonne Arealdifferanse (Daa) fra Rådata-fanen; [@[Kolonnen KPAREALFORMÅL-Ny i Arealformålsendring-fanen]; Kolonne Arealdifferanse (Daa) fra Rådata-fanen)

Netto endring (daa) vil bli

= Brutto inn – Brutto ut

Her har vi brukt utrykket:

=[@[Brutto inn (daa)]]-[@[Brutto ut (daa)]]

Netto endring (%) vil bli

= Netto endring (daa) / Total arealstørrelse - Gjeldende

Her har vi brukt utrykket:

=[@[Netto endring (daa)]]/[@[Total arealstørrelse - Gjeldende]]

Til slutt sitter vi igjen med en slik tabell:

Figur 59: Eksempel på regneark for arealformålsendringer etter at formler har blitt lagt inn

Forstørr bilde

Ved å skjule kolonner for hovedformål ny og gjeldende, og arealformål ny, kan tabellen forenkles til videre bruk:

Figur 60: Eksempel på regneark for arealformålsendringer etter at overflødige kolonner er skjult

Forstørr bilde

Eventuelle delefeil med tekst #DIV/0! kan slettes manuelt for å rense tabellen.

For å regne ut arealdifferanse aggregert på hovedformål kan dere bruke samme framgangsmåte som i forrige beskrivelse. Lag en ny fane, lim inn hovedformål – gjeldende og ny, slett kolonner for arealformål og ha en rad for hvert hovedformål.

Figur 61: Eksempel der arealformålsendringer er gjort for hovedformål

Forstørr bilde

For å lage en tabell over omdisponering av arealer mellom hovedformål kan du lage en pivottabell fra rådata-fanen.

Legg inn hovedformål gjeldende som rader (1), hovedformål ny som kolonner (2), og arealdifferanse som verdier (3).

Figur 62: Egenskaper for pivottabell som viser omdisponering av areal mellom hovedformål

Ved å gjøre dette vil du få en tabell som ser omtrent slik ut:

Figur 63: Pivottabell som viser forflytning av arealer mellom hovedformål

Forstørr bilde

Du kan lese tabellen slik:

Tabellen viser hvilke arealer i gjeldende plan som endres til nye arealformål i ny plan.

Radene viser arealformål i gjeldende plan.

Eksempel:

I gjeldende plan blir 554.8 dekar overført fra 5. LNFR-formål til 1. Bebyggelse og anlegg

Samtidig blir 593.4 dekar overført fra 1. Bebyggelse og anlegg til 5. LNFR-formål

4.8 Eksempel på tabell for sonedata

Det er også nyttig å kunne gjøre uttrekk av data basert på definerte soner i kommunen. Dette kan gjøres for å få oversikt over eksempelvis planlagt arealbruk og arealformålsendringer i et avgrenset område. Avgrensningene kan være basert på administrative inndelinger av kommunen eller andre tematiske inndelinger.

I dette eksemplet vises arealreserven fordelt på arealformål for ulike soner i kommunen.

Lag en pivottabell fra rådata-fanen først. Legg inn arealformål og hovedformål som rader (1), og sonedata som kolonner (2). Alternativt kan også sonedata komme inn som rader som kan grupperes i etterkant. Legg inn Areal_Daa som verdier (3) og arealtype som filter (4).

Figur 64: Egenskapstabell for pivottabell for sonedata med arealtyper

Legg inn filter på arealtype slik at verdiene 21, 22, 23, 30, 50 og 60 vises. Filter også bort andre hovedformål enn 1. bebyggelse og anlegg, og 2. samferdselsanlegg og infrastruktur.

Til slutt vil du få en tabell der du kan skille temadata per inndelte sone, her vist som arealreserve per sone:

Figur 65: Eksempel på sonedata som viser arealreserve fordelt på egendefinerte soner i kommunen

Forstørr bilde

5 Presentere data med analyseprogram

Dere kan ytterligere bearbeide rådata ved å anvende analyseprogrammer for databaser/regneark. Eksempler på slike programmer er PowerBI, Tableau og Qlik. Fordelen ved å bruke slike programmer er at dere enklere kan analysere komplekse data. I disse programmene er det blant annet enklere å filtrere på sammensatte data. I tillegg er gjerne grafikken dynamisk, slik at du enkelt kan se sammenhenger mellom tabelldata og diagrammer.

Videre har programmene ofte mulighet til å publisere og presentere data direkte på nett. Dette kan tilgjengeliggjøre data på en slik måte at brukere har mulighet til å analysere og dypdykke i dataene på egenhånd.

Datagrunnlaget vil være det samme som er etablert fra kapittel 3, og det er også mulighet for å hente inn analyser fra kapittel 4.

Dette kapittelet beskriver ikke framgangsmåten like detaljert som i kapittel 4, men viser eksempler på hvordan tabeller og diagrammer fra arealregnskapet som kan lages med et slikt program.

5.1 Dynamiske dataanalyser i analyseprogram for regneark (avansert)

Legg inn rådata fra regnearket dere jobbet med i kapittel 3 og 4 inn i analyseprogrammet.

Figur 66: Legg inn rådata fra regneark i analyseprogram for regneark

Forstørr bilde

Transformer datasettet Rådata (1) og velg transformer (2).

Figur 67: Legg inn rådata og transformer data

Forstørr bilde

Alle kolonner utenom arealstørrelser og arealdifferanser skal leses inn som tekst (1).

Figur 68: Legg inn alle kolonner som tekst utenom kolonner for arealstørrelser

Forstørr bilde

Under datakolonnen (1) kan du velge hvilke data som skal analyseres. Under visualiseringer (2) kan du velge hvilke type visualiseringer og diagrammer du ønsker å presenter. Under filtre (3) kan du legge inn filtre for selve diagrammet, sidevisningen, eller hele datasettet. På sidevisningen (4) legger du inn datavisualiseringen og grafisk ordner diagrammer.

Figur 69: Oversikt over egenskapstabeller for data, diagramtyper, filtreringer og visualiseringsfane

Forstørr bilde

I datakolonnen kan gruppere data (1). Eksempelvis kan du gruppere hovedformål slik at du enkelt kan lage en gruppe for å vise arealreserve.

Figur 70: Grupperingsmuligheter for kolonner i analyseprogrammet

Forstørr bilde

Under ligger det flere eksempler på analyser som kan gjøres gjennom analyseprogram for databaser.

Det er også mulig å eksportere ut data som har blitt analysert gjennom analyseprogram for databaser. Dette kan hentes ut som .CSV, som kan leses inn tilbake til et regnearkprogram.

Figur 71: Eksempel på enkel visning av forskjell på arealreserve mellom gjeldende og ny plan

Forstørr bilde

Figur 72: Arealreserve vist som tabell og diagram

Forstørr bilde

Figur 73: Interaktivt diagram

Forstørr bilde

Figur 74: Interaktivt diagram

Forstørr bilde

Figur 75: Interaktivt diagram

Forstørr bilde

Figur 76: Presentasjon av arealreserve utenfor dekningskart

Forstørr bilde

Figur 77: Arealreserve og naturtyper

Forstørr bilde

Figur 78: Arealformålsendringer

Forstørr bilde

Figur 79: Arealformålsendringer som diagram og tabell

Forstørr bilde

Figur 80: Omdisponering mellom hovedformål

Forstørr bilde