MS Excel 2013 Videregående DK
I n d h o l d s f o r t e g n e l s e
Introduktion ............................................................................................................................ 1
Kursusmaterialet ..................................................................................................................... 1
Funktioner ............................................................................................................................... 2
Syntaks (Grammatik)............................................................................................................... 2
Guiden Funktion....................................................................................................................... 4
Formelhjælp ................................................................................................................................6
Flere funktioner ....................................................................................................................... 7
FV – Fremtidsværdi (Future Value).................................................................................................7
Absolutte referencer .............................................................................................................. 11
Navngivning af celler ............................................................................................................. 14
Etiketter ................................................................................................................................... 14
Navne ...................................................................................................................................... 14
Oprettelse af navne .................................................................................................................... 15
Navneoversigt ........................................................................................................................... 16
Opgave 1................................................................................................................................ 17
Opgave 2................................................................................................................................ 18
Formler .................................................................................................................................... 18
Formatering af tal .................................................................................................................. 20
Beskyttelse ............................................................................................................................ 20
1. Lås celler op indtastningsceller ................................................................................................ 20
2. Beskyt regnearket .................................................................................................................. 21
Opgave 3................................................................................................................................ 22
Kopiere Ark ............................................................................................................................ 22
Navngivning af Ark ................................................................................................................ 24
Formler på konsolideringsarket ............................................................................................. 24
Opgave 4................................................................................................................................ 26
Oprettelse af "Stamdata" regneark........................................................................................ 26
Oprettelse af bonusmodel. ..................................................................................................... 27
Sammenkædning af regnearksfiler ........................................................................................ 28
Opdatering af kæder .............................................................................................................. 29
Navngivning af celler ............................................................................................................. 29
Indsætning af formler............................................................................................................ 31
Hvis betingelser i formler. ..................................................................................................... 31
Opgave 5................................................................................................................................ 33
Markering af data til graf ....................................................................................................... 33
Størrelse og placering ............................................................................................................ 34
Hvordan læser Excel data ...................................................................................................... 35
© Target Gruppen marts 2014
i
I n d h o l d s f o r t e g n e l s e
Redigering af grafen .............................................................................................................. 35
Overskrifter ........................................................................................................................... 37
Tilføjelse af ny dataserie ........................................................................................................ 38
Opgave 6................................................................................................................................ 42
Dato-formler ............................................................................................................................. 44
Tilføje nye varenumre ................................................................................................................ 48
Gem som skabelon..................................................................................................................... 48
Målsøgning ............................................................................................................................ 50
Scenariestyring ...................................................................................................................... 52
Scenarieresumerapporter ............................................................................................................ 54
Kommentarer ......................................................................................................................... 56
Betinget formatering ............................................................................................................. 58
Datavalidering ....................................................................................................................... 61
Filtre og sortering .................................................................................................................. 63
Sortering .................................................................................................................................. 66
Subtotaler .............................................................................................................................. 67
Gruppering............................................................................................................................. 69
Forbered data til at blive grupperet. ............................................................................................. 70
Automatisk eller manuel disposition ............................................................................................. 70
Vis og skjul flere niveauer ........................................................................................................... 71
Pivottabeller .......................................................................................................................... 72
Anbefalede pivottabeller ............................................................................................................. 73
Hurtig analyse ........................................................................................................................... 74
Opret pivottabel......................................................................................................................... 74
Indstillinger til pivottabeller......................................................................................................... 80
Datafelter og celler .................................................................................................................... 80
Beregningsmetoder .................................................................................................................... 81
Udsnitsværktøjet ....................................................................................................................... 82
Hvad er en makro? ................................................................................................................. 85
Makroer: Automatisering af regelmæssige opgaver ........................................................................ 86
Afspille en makro i Microsoft Excel ............................................................................................... 88
Afspille en makro ved hjælp af en tastaturgenvej ........................................................................... 89
Importer data fra tekstfil ....................................................................................................... 91
Integration med MS Office og Internet .................................................................................. 94
PowerPoint og diagrammer ................................................................................................... 98
Konsolidering ....................................................................................................................... 100
Fremgangsmåde ...................................................................................................................... 101
Fremgangsmåde ...................................................................................................................... 102
Brødfabrikken ...................................................................................................................... 104
© Target Gruppen marts 2014
ii
I n d h o l d s f o r t e g n e l s e
Lav en tipskupon.................................................................................................................. 106
Tæl.Hvis og Sum.Hvis .......................................................................................................... 107
Udregn partistøtten ............................................................................................................. 109
Opgave i Solver .................................................................................................................... 110
Tekstfunktioner ................................................................................................................... 113
Eksperimenter med talformater ........................................................................................... 114
Tillæg til Excel ..................................................................................................................... 115
Funktionsoversigt .................................................................................................................... 115
Almindelige aritmetiske funktioner ............................................................................................. 116
=Sum(område1;område2;…) .................................................................................................... 116
=Middel(område1;område2;…) .................................................................................................. 116
=Maks(område1;område2;…) .................................................................................................... 116
=Min(område1;område2;…) ...................................................................................................... 117
=Tæl(område1;område2;…) ...................................................................................................... 117
=Hyppigst(område1;område2;…)............................................................................................... 117
=Median(område1;område2;…) ................................................................................................. 117
=Afrund(område;antal decimaler) .............................................................................................. 117
=Afkort(område;antal decimaler)............................................................................................... 117
=Heltal(område) ...................................................................................................................... 117
=Abs(område) ......................................................................................................................... 118
=Slump(tal) ............................................................................................................................ 118
Logiske funktioner ................................................................................................................... 118
=Hvis(betingelse;sand;falsk)..................................................................................................... 118
=Og(betingelse1;betingelse2;….) ............................................................................................... 119
=Eller(betingelse1;betingelse2;….) ............................................................................................ 119
=Tæl.hvis(område;kriterium) .................................................................................................... 119
=Sum.hvis(område;kriterium;evt. sumområde)........................................................................... 119
Datofunktioner ........................................................................................................................ 120
=Idag() .................................................................................................................................. 120
=Nu()..................................................................................................................................... 120
=Dato(år;måned;dag) .............................................................................................................. 120
=Sekund(tal eller område) ........................................................................................................ 121
=Minut(tal eller område)........................................................................................................... 121
=Time(tal eller område) ........................................................................................................... 121
=Dag(tal eller område) ............................................................................................................. 121
=Ugedag(tal eller område) ........................................................................................................ 121
=Måned(tal eller område) ......................................................................................................... 121
=År(tal eller område) ............................................................................................................... 121
=Datoværdi(tekst) ................................................................................................................... 121
© Target Gruppen marts 2014
iii
I n d h o l d s f o r t e g n e l s e
=Tidsværdi(tekst) .................................................................................................................... 121
Opslagsformler ........................................................................................................................ 122
=Lopslag(opslagscelle;opslagsområde;kolonnenr.;evt. falsk) ........................................................ 122
=Vopslag(opslagscelle;opslagsområde;rækkenr.;evt. falsk) .......................................................... 122
=Slå.op(opslagscelle;opslagsværdi;resultatværdi) ....................................................................... 123
Finansielle funktioner ............................................................................................................... 123
=Ydelse(rente;nper;nv) ............................................................................................................ 123
=FV(rente;nper;nv) ................................................................................................................. 123
Information ............................................................................................................................. 124
=Er.tekst(område) ................................................................................................................... 124
=Er.ikke.tekst(område) ............................................................................................................ 124
=Er.tal(område) ...................................................................................................................... 124
=Er.tom(område) .................................................................................................................... 124
=Er.fejl(område) ..................................................................................................................... 124
Tekstfunktioner ....................................................................................................................... 124
=Sammenkædning(tekst1;tekst2;tekst3;…) ................................................................................ 124
=Store.bogstaver(tekst/celle).................................................................................................... 124
=Små.bogstaver(tekst/celle) ..................................................................................................... 124
=Stort.forbogstav(tekst/celle) ................................................................................................... 124
=Venstre(tekst(celle;antal tegn) ................................................................................................ 125
=Højre(tekst(celle;antal tegn) ................................................................................................... 125
=Midt(tekst/celle;start ved;antal tegn) ....................................................................................... 125
=Værdi(tekst/celle) .................................................................................................................. 125
=Tekst(Tal/celle;format)........................................................................................................... 125
Liste over genvejstaster............................................................................................................ 126
© Target Gruppen marts 2014
iv
Microsoft Excel 2013 Videregående DK
Introduktion
Target Gruppens Excel 2013 videregående kursus tager sigte
på at lære deltagere med nogen forhåndsviden (evt. svarende
til grundkurset), hvorledes man ved hjælp af regnefunktioner
og makroprogrammering anvender programmet mere effektivt.
Nogle af øvelserne er “gengangere” fra grundkurset. Det betyder, at du dels får repeteret de mere grundlæggende begreber, dels at du lærer, hvorledes du kan optimere modellerne.
Der vil ligeledes på kurset blive lagt vægt på, hvorledes du
bruger hjælpesystemet til at komme videre med regnefunktioner og makrooptagelse/ programmering.
Kursusmaterialet
Kursusmaterialet er udarbejdet dels til at blive benyttet under
kurset, samt som "opslagsværk" efter kurset. Kommer instruktøren på kurset ind på emner, som ikke er beskrevet i
materialet, bliver der gjort opmærksom på dette, så deltagerne eventuelt kan tage notater.
© Target Gruppen marts 2014
Side 1
Microsoft Excel 2013 Videregående DK
Absolutte referencer
Det er som sagt ikke altid, at den almindelige kopiering i Excel
virker efter hensigten. Når der kopieres normalt, så ændres
henvisninger til kolonner og rækker sig på den måde, det blev
beskrevet ovenfor.
Men det er bestemt ikke altid en fordel. For hvad nu hvis den
originale henvisning til en celle skal gælde overalt, hvor formlen kopieres til? Hvis en række celler skal ganges med en bestemt procentsats – det være sig en momssats eller en rabatsats – så skal man jo til stadighed holde fast i henvisningen til
netop denne sats.
Og det kommer man ud for rigtig mange gange. Nogle gange
skal henvisningen til en celle holdes fast. Andre gange skal
man kun bruge tallene i en kolonne eller en række, hvorfor det
er disse, der skal holdes fast.
Når man vil holde henvisningen til en celle, en kolonne eller
en række fast, sætter man i formlen et dollartegn ($) for
henvisningen – det der også kaldes referencen. Og der er tre
forskellige placeringsmuligheder:
•
Cellen - der sættes dollartegn foran både kolonne og
rækkeangivelse som i =$A$1+A2. Uanset hvor denne
celle kopieres hen på arket, vil den stadig henvise til
cellen A1. Altså uanset om den kopieres til højre eller
venstre eller om den kopieres op eller ned.
•
Kolonnen - der sættes kun dollartegn foran kolonneangivelsen som i =$A1+A2. Dette dollartegn virker til
at holde det fast, der står i kolonne A, når der kopieres vandret - altså mod højre og venstre. Kopieres der
nedad, bruges det næste tal i kolonnen.
•
Rækken - omvendt sættes der her kun dollartegn
foran rækkeangivelsen som i =A$1+A2. Dette dollartegn virker til at holde det fast, der står i række 1, når
der kopieres lodret - altså op og ned. Kopieres der
vandret, bruges det næste tal i rækken.
Hvordan gør man nu i praksis? Ja, man kunne jo bare skrive
dollartegnet, hver gang der er brug for det. Med kombinationen Alt Gr + 4 skriver du et $.
Men der er en nemmere metode – og det er at bruge genvejstasten F4.
© Target Gruppen marts 2014
Side 11
Microsoft Excel 2013 Videregående DK
Det virker på denne måde:
•
Stil dig i cellen, hvor formlen skal stå og sæt et lighedstegn.
•
Du markerer celler til formlen som normalt ved at pege
på dem
•
Men når en celle, kolonne eller række skal fastholdes,
trykker du F4
•
Henvisningen til hele cellen er nu absolut
•
Tryk F4 igen for kun at sætte rækken absolut – og
•
Endnu et tryk på F4 gør i stedet kolonnen absolut
På denne måde kan du med F4 bladre mellem mulighederne.
NB! Væn dig til at bruge absolutte referencer. Du sparer en
masse tid, da du med dem stadig kan kopiere en masse. Alternativet er jo at lave en masse forskellige formler. Og selv
om det virker indviklet i starten, så skal det nok snart sidde,
hvis du konsekvent tænker dem med i dit arbejde!
© Target Gruppen marts 2014
Side 12
Microsoft Excel 2013 Videregående DK
Opgave 6
Opgave 6 er en øvelse i mange forskellige Excel faciliteter.
Hovedformålet med øvelsen er at opbygge en faktureringsmodel, som vha. regne-funktioner finder varenavne og priser i et
varekartotek på regnearket.
Opbyg varekartoteket som vist nedenfor (Navngiv Ark1, så
det hedder Varekartotek):
A
© Target Gruppen marts 2014
B
C
1
2
VAREKARTOTEK
3
Vare nr.
Navn
4
1
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Cykellygte lille
Cykellygte stor
Ringeklokke deluxe
Båthorn
Støtteben
Bagagebærer
Sadel
Sadel læder
Cykellås
Kodelås
Kodelås galvaniseret
Cykeltyverialarm
Fjernstyret lås
Pedalet
Pedalsæt rytter
D
Pris
12,80 Kr.
35,00 Kr.
56,00 Kr.
49,85 Kr.
22,00 Kr.
17,50 Kr.
85,80 Kr.
315,75 Kr.
75,45 Kr.
87,00 Kr.
112,00 Kr.
875,00 Kr.
118,00 Kr.
65,00 Kr.
212,00 Kr.
Side 42
Microsoft Excel 2013 Videregående DK
Opbyg Faktura-modellen som vist nedenfor på Ark 2 (Navngiv
Ark2, så det hedder Faktura):
A
B
1
FAKTURA FRA
2
EvermoeS & CO
C
D
E
F
3
4
Dato:
5
Faktura nr.:
6
7
8
Antal
Varenr.
Varenavn.
A pris
Total
9
10
11
12
13
14
15
16
17
18
19
20
21
22
I alt excl. moms:
23
+ 25% moms:
24
25
I alt incl. moms:
26
27
Betal senest den:
28
29
Du har nu opbygget varekartotek og fakturamodel på regnearket, og er klar til at indtaste formler.
© Target Gruppen marts 2014
Side 43
Microsoft Excel 2013 Videregående DK
Dato-formler
Vi forudsætter at, du fakturerer “tidstro” og placerer datoen i
celle F4 vha. funktionen:
=IDAG()
- og formaterer datoen til et ønsket præsentationsformat.
Da vi altid giver 45 dages kredit, placeres “Betal senest den:”
formlen i celle D27 vha. formlen:
=IDAG()+45
- og formateres ligeledes til et ønsket præsentationsformat.
Varenavn og Varepris formler:
I celle-området D10 til E20 skal vi have hentet informationer
fra varekartoteket, som befinder sig i celle-området A4 til D18
på “Varekartotek” arket. Til dette benyttes “OPSLAG” funktionen.
Da opslaget skal foretages vertikalt(lodret) i varekartotekets
“Varenr.” kolonne, skal vi bruge =lopslag funktionen. Indtast
formlen:
=LOPSLAG($B10;Varekartotek!$A$4:$D$18;3;FALSK)
i celle D10. De absolutte cellereferencer skyldes, at formlen
skal kunne kopieres til celleområdet D11 til D20, uden at tabel-området forskydes. Ligeledes kan formlen kopieres til kolonne E, blot med den ændring, at 3 tallet(kolonner til højre)
skal ændres til et 4 tal, altså:
=LOPSLAG($B10;Varekartotek!$A$4:$D$18;4;FALSK)
Afslutningen ”FALSK” – eller bare 0 – betyder, at man er ude
efter en nøjagtig værdi og ikke bare en værdi indenfor et interval. Hvis man bruger FALSK her, behøver man ikke nødvendigvis sortere den første kolonne i opslagsområdet, da Excel bliver ved, indtil den eksakte værdi findes. I modsat fald
returneres #I/T-meddelelsen – jf. nedenfor.
© Target Gruppen marts 2014
Side 44
Microsoft Excel 2013 Videregående DK
I varelisten findes ikke en vare med varenummer 0. Dette bevirker, at LOPSLAG ikke virker helt efter hensigten, hvis der
ikke er angivet noget varenummer i kolonne B - husk at værdien af en tom celle er nul. Problemet skyldes simpelthen, at
LOPSLAG ikke kan finde en vare med nummeret nul, og svarer med en fejlmeddelelse #I/T.
For at imødegå dette problem, vil vi kun foretage et opslag,
hvis der både er angivet et varenummer og et antal. Dette gør
du vha. HVIS-funktionen.
I celle D10 ændres formlen til:
=HVIS(OG($A10>0;$B10>0);LOPSLAG($B10;Varekartotek!$A$4:$D$18;3;FALSK);””)
Formlen “siger”, at hvis der ikke er indtastet både et varenummer og et antal, skal den returnere den tomme tekst (“”) i
cellen, ellers skal den foretage opslag i varekartoteket.
Alternativt til at indsætte den tomme tekst, kan man naturligvis også vælge at indsætte værdien nul (0). I så fald skal du,
hvis du ikke ønsker at se/udskrive nullerne, aktivere nulundertrykkelse. Dette gøres ved at vælge menupunkterne:
Alternativt til at indsætte den tomme tekst, kan man naturligvis også vælge at indsætte værdien nul (0). I så fald skal du,
hvis du ikke ønsker at se/udskrive nullerne, aktivere nulundertrykkelse. Dette gøres ved at vælge menupunkterne:
Dette gøres ved at vælge Indstillinger på Filer-menuen under Avanceret og Visningsindstillinger for dette regneark, Vis et nul i celler, der har nulværdi.
© Target Gruppen marts 2014
Side 45
Microsoft Excel 2013 Videregående DK
Men altså kun, hvis du indsætter et nul i stedet for den tomme
tekst.
Det er vigtigt at være opmærksom på, at LOPSLAG-funktionen forudsætter at den kolonne, opslaget foretages i, er sorteret i stigende orden – med mindre man bruger FALSE.
Er dette ikke tilfældet, kan dette gøres med følgende
knap/menu på Hjem:
Færdiggør fakturaen med de resterende formler til beregning
af “I alt ekskl. moms” osv.
Ikke eksisterende varenumre
Som nævnt ovenfor er det vigtigt, at varekartoteket er sorteret i stigende orden efter varenummer. Desuden er det vigtigt,
at varelisten ikke indeholder “huller”, dvs. varerne skal være
nummererede fortløbende, f.eks. fra 1 til 15 som i denne opgave. I virkelige anvendelser er det naturligvis ikke muligt at
opfylde dette krav - hvad gør man så?
I det ovenstående har vi løst problemet ved at indsætte
”FALSK” bagerst i vores LOPSLAG. Det virker fint, da man jo
klart kan se i cellen, at der er noget galt, da cellen kommer til
at indeholde #I/T.
Men her er en mere elegant løsning, som du kan prøve at lave,
hvis du har tid og lyst til det. Det kræver dog, at indstillingen
FALSK fjernes fra alle formler.
© Target Gruppen marts 2014
Side 46
Microsoft Excel 2013 Videregående DK
Prøv først at skabe et par huller i listen ved at slette varenummer 4, 7 og 8. Det er vigtigt, at du sletter hele rækker, og ikke
blot indholdet af de berørte celler!
Du har nu slettet 3 rækker fra varekartoteket - men hvad så
med formlerne i fakturaen? Et hurtigt check burde overbevise
dig om, at Excel faktisk ikke er helt dum: de formler der benytter LOPSLAG er automatisk blevet ændret, så de nu afspejler det faktum, at der er blevet fjernet 3 rækker fra tabelområdet. Smart!
Prøv nu på fakturaen at lave opslag på en af de varer, du netop
har fjernet fra kartoteket. Nu bliver det klart, hvorfor varelisten ikke må indeholde “huller”. Hvis et varenummer ikke eksisterer, finder LOPSLAG i stedet det varenummer i rækken,
der står umiddelbart foran det varenummer, der altså ikke findes - altså det sidste varenummer før “hullet”. Det samme gør
sig naturligvis gældende, hvis du søger et varenummer, der er
større end det største varenummer i tabellen. Prøv f. eks. at
søge vare nummer 20.
Det problem, der påpeges ovenfor, kan løses ved at benytte
sig af den tomme kolonne, der er på fakturaen mellem varenr.
og Varenavn. Her indsættes en formel, som ved hjælp af
LOPSLAG finder varenummeret på den vare, der rent faktisk
blev fundet af LOPSLAG i tabellen. Nu kan man, ved at sammenligne det varenummer der blev “bedt om” og det faktiske
varenummer, afgøre om varen eksisterer.
Indtast formlen
=HVIS(OG(A10>0;B10>0);LOPSLAG(B10;Varekartotek!$A$4:$D$18;1);””)
i celle C10, og kopiér den til de underliggende celler. Prøv nu
at søge efter en af de varer, der ikke findes. Bemærk, at det
nu er tegn på problemer, hvis indholdet af kolonne C er forskelligt fra indholdet af kolonne B.
Formlen, der finder varenavnet, skal nu ændres til:
=HVIS($B10=$C10;HVIS(OG($A10>0;$B10>0);
LOPSLAG($B10;Varekartotek!$A$4:$D$18;3);””);”Varen findes ikke!”)
Formlen kan læses som: Hvis indholdet af cellerne B10 og
C10 er identiske, slås op som før. Hvis ikke, meddeles dette
med teksten “Varen findes ikke!”
© Target Gruppen marts 2014
Side 47
Microsoft Excel 2013 Videregående DK
Teksten i Kolonne C kan skjules, enten ved at skjule selve kolonnen (højre-klik på selve kolonnen og vælg Skjul), eller ved
at vælge samme farve til teksten som den, der er brugt til
baggrunden (typisk hvid).
Tilføje nye varenumre
Du har nu modificeret fakturaen, så det er muligt at slette varer fra varekartoteket. Den næste naturlige udvidelse må naturligvis være at kunne tilføje varer! Proceduren er som følger:
Marker den sidste varelinje i kartoteket.
Vælg Indsæt, Indsæt arkrækker fra Startside-fanebladet
- eller tryk Ctrl-+.
Indtast den nye vare i den nye række. Gentag dette for her ny
vare. Marker alle varelinjer i kartoteket, og sorter efter kolonne A, op i stigende rækkefølge.
Fordelen ved at indsætte en ny række for hver vare der tilføjes, er at formlerne i fakturaen automatisk ændres til at omfatte et varenummer mere, for hver gang der tilføjes en
række. Det samme gjorde sig i øvrigt gældende, da du slettede rækker tidligere i opgaven.
Beskyt modellen for at forhindre ødelæggelse af de “usynlige”
formler.
Gem som skabelon
Nu skal vi have færdiggjort fakturaen, således at den kan tjene
som skabelon. For at den kan dette, skal følgende gøres:
© Target Gruppen marts 2014
•
Arket med varekartoteket skal skjules – og ubrugte ark
slettes
•
Formlerne i arket skal skjules, når der indtastes i det.
•
Der skal valideres ved indtastning, at der rent faktisk
kun bliver indtastet hele tal, der er mindre eller lig med
tallet 20 – idet vi åbner for et par nye varer.
•
Områderne Antal og Varenummer skal låses op – og
arket skal beskyttes
•
Endelig skal projektmappen beskyttes, så det skjulte
varekartotek ikke kan hentes frem igen.
Side 48
Microsoft Excel 2013 Videregående DK
Prøv selv at gøre dette – dog med følgende tips:
•
Ark skjules ved at højreklikke på arket med Skjul
•
Formler skjules i samme dialogboks, hvor du senere
skal låse celler op
•
Datavalidering ligger på fanebladet Data – se mere om
datavalidering nedenfor.
•
Beskyttelse af projektmappen ligger på Gennemse under Beskyt projektmappe.
Slut med at gemme fakturaen som en skabelon.
© Target Gruppen marts 2014
Side 49
Microsoft Excel 2013 Videregående DK
Subtotaler
Excel er rig på funktioner, der gør dig i stand til at opsummere
store datamængder. Funktioner som sortering, AutoFilter, pivottabeller og konsolidering er blot nogle af dem. Brug af subtotaler er en anden.
Her kan man i et sorteret dataområde hurtigt få Excel til at
indsætte totaler for hver ændring i en bestemt på forhånd sorteret kolonne. Disse totaler er som standard sum-formler, men
man kan også oprette totaler af en anden karakter som f.eks.
gennemsnitsformler.
Således virker funktionen:
© Target Gruppen marts 2014
•
Åbn en projektmappe med en struktur som en database – dvs. således at kolonneoverskrifterne dækker
over ensartede tekster og tal, som du vil have summeret op. Her åbner vi filen Pivodata.
•
Marker området – det er ofte nok blot at stille sig i området.
•
Sorter området - først på kolonnen Kunde (stigende),
dernæst på Afd. (stigende) og slutteligt på pris (faldende).
•
Nu er vi klar til at sætte subtotaler ind for hver kunde.
Vælg Data, Subtotal.
Side 67
Microsoft Excel 2013 Videregående DK
© Target Gruppen marts 2014
•
I første rulleliste vælges hvilken kolonne, der skal
danne grundlag for subtotalerne – for hver ændring i
kolonnen Kunde skal Excel indsætte en subtotal.
•
Dernæst skal Excel have at vide, hvilken regnearksfunktion, der skal benyttes. Her er valgt Sum – andre
valg er Middel, Antal, Maks, Min mm.
•
Endelig skal du markere den eller de kolonner, der skal
summeres op – i dette tilfælde kolonnen Pris.
•
Bemærk at du kan lave side Sideskift mellem grupper – altså for hver ændring i kolonnen Kunde.
•
Herefter bliver resultatet en gruppering af data, således at man kan vælge, hvor meget eller hvor lidt, man
vil overskue:
•
Bemærk at der til venstre for regnearket er indsat en
bjælke med numre øverst og knapper med plusser eller
minusser alt efter om tallene bag en subtotal er folde
ud eller ej. Ved at klikke på 1-tallet vises kun den samlede total. Med totallet ses også subtotalerne for hver
kunde, og ved klik på 3-tallet foldes hele listen ud.
Side 68
Microsoft Excel 2013 Videregående DK
•
Ved at klikke på plusser og minusser kan du folde listerne for de enkelte kunder ud enkeltvis.
•
Nu vil vi lave endnu en subtotal – denne gang for afdelingerne. Men det skal ikke kun være for afdelingerne.
Vi vil beholde de subtotaler, vi har i forvejen, således
at det bliver et fjerde niveau med afdelingerne under
kunderne.
•
Bemærk denne gang i Data, Subtotal…, at du skal
fjerne fluebenet i Erstat aktuelle subtotaler, da du
ellers kun får oprettet subtotaler for afdelingerne.
•
Når du undersøger resultatet, vil du se, at grupperingen har fået endnu en niveau, da der nu er fire tal
øverst til venstre – sælgertotalerne er nu tredje niveau.
•
Der er også to hovedtotaler – en for hver gruppe subtotaler. Hvis du synes, kan du sagtens slette den ene.
Men du kan også betragte det som en slags kontrol –
de to totaler skulle gerne give det samme.
Gruppering
Excel kan oprette en disposition for dine data og gi’ dig mulighed for at åbne og lukke for visningen af data i flere niveauer.
Man kan klikke på dispositionssymbolerne 1,2,3, + og - for at
vise kun de rækker og kolonner som giver summeringen eller
overskriften for sektioner af regnearket, eller for at se mere
detaljeret information for en enkelt summering eller overskrift.
© Target Gruppen marts 2014
Side 69
Microsoft Excel 2013 Videregående DK
Forbered data til at blive grupperet.
Detaljedata som man gerne vil oprette en disposition for skal
være i form af en liste hvor hver kolonne har en overskrift i
øverste række og ikke indeholder blanke rækker.
Inden man opretter dispositionen, skal man sorterer sine data,
så rækkerne, som skal grupperes, er sammen. I illustrationen
herover er listen sorteret efter Land, Sælger og Ordre dato.
Indsæt derefter sum rækker. I illustrationen er der en I alt
række under hver sælger.
Hvis man opretter dispositioner for kolonner i stedet for rækker, sørg for at listen indeholder en etikette (overskrift) i første
kolonne og at summerings kolonnerne enten er til højre eller
venstre for de detaljerede data.
Automatisk eller manuel disposition
Automatisk disposition. Hvis man anvender automatiske
subtotaler i en liste organiseret i rækker, vil Excel automatisk
gruppere regnearket samtidig. Se foregående emne - Automatiske subtotaler.
Manuel disposition. Hvis data ikke er organiseret, så Excel
kan oprette en disposition automatisk, kan man disposition
manuelt. F.eks. bliver man nødt til manuelt at oprette sin disposition, hvis summeringsrækkerne eller -kolonnerne indeholder værdier eller beskrivende tekst i stedet for formler.
Markér de data, som skal grupperes, og vælg Data, Gruppér
og Opdel gruppe. Dette gentages for hvert af de områder,
som skal grupperes.
© Target Gruppen marts 2014
Side 70
Microsoft Excel 2013 Videregående DK
Vis og skjul flere niveauer
En disposition kan have op til otte niveauer, hvor hvert indre
niveau indeholder data detaljerne for det ydre niveau. I illustrationen indeholder Hovedtotal summen af alle rækker, det
er niveau 1. Rækkerne som indeholder summen for hvert af
landene, UK Total, USA Total, er niveau 2, og rækkerne, som
viser de enkelte sælgeres totaler, er niveau 3 og niveau 4,
hvor man ser de enkelte ordrer. Man kan klikke på de enkelte niveauers numre for at få vist et niveau og + og – for at
vis eller skjulte detaljer under hvert niveau.
© Target Gruppen marts 2014
Side 71