Dataanalyse med Excel 9788778531025, 8778531020 [PDF]

Zitiervorschau

H E N R I K DAH L

DATAANALYSE MED EXCEL

Dataanalyse med Excel 1. udgave, 1. oplag 2011 Copyright © 2011 Libris Media A/S Forfatter: Henrik Dahl Redaktion: Charlotte Hannibalsen Omslag: Fru Tang Design DTP: Michael Karbo Korrektur: Jens Lyng Tryk: AKA-PRINT A/S ISBN: 978-87-7853-102-5

Libris Media A/S er en entreprenant udgivervirksomhed med rødder inden for media, it og detail. Vi har en stærk national tilstedeværelse med mere end 450 distributionspartnere offline og over 30 digitale distributionspartnere. Rettighederne til vores udgivelser er solgt til mere end 14 lande. Siden 1994 hvor selskabet blev grundlagt, har vi i Danmark solgt mere end ni millioner udgivelser.

libris.dk Besøg os på libris.dk,

hvor du finder alle vores udgivelser. Her kan du også give os kommentarer og tilmelde dig vores gratis nyhedsservice. På www.libris.dk/bilag kan du hente eventuelle øvelsesfiler, smags­prøver og opdateringer.

kapitel 0.1

Indledning

INDHOLD 0.1 Indledning

4



1.0 Import af data, pivottabel og pivotdiagram

7

Analyse af pengeforbrug

2.0 Import af data og benyttelse af tekstfunktioner

21

Undersøgelse af mobilforbrug

3.0 Diagrammer, tendenslinje og målsøgning

27

Pensionsberegning

4.0 Problemløser og SUMPRODUKT

39

Minimering af transportomkostninger, installering af tilføjelsesprogrammer

5.0 Målsøgning og problemløser

53

Løsning af ligninger

6.0 Sortering, filtrering og formatering Salgstal i en virksomhed,

diagrammer og pivottabel

7.0 Datoformler og tekstformler

79

Aldersberegning, absolutte referencer og relative referencer

8.0 Flere tekstfunktioner

61

89

Find for- og efternavn, LÆNGDE, FIND, UDSKIFT, HØJRE, VENSTRE og MIDT

0.2 Stikordsregister 96

Libris . d k

3

0 .1

Indledning Excel er et af de allermest benyttede programmer. På ethvert kontor benyttes regnearksprogrammer, og uden sammenligning er Excel det mest benyttede. Excel anvendes også af mange private brugere til at holde styr på privatøkonomien, bilens benzinforbrug eller lignende. Endvidere er næsten alle studerende formentlig stødt på Excel undervejs i studiet for at bruge det til beregninger af den ene eller anden art. Excel benyttes altså i mange forskellige situationer på alverdens arbejdspladser, studiepladser og hjem. Mulighederne med Excel er nu også mange. Programmet kan benyttes til fremstilling af præsentationer af talmateriale, til simple beregninger eller til videre bearbejdning af tekst og talmateriale - altså dataanalyse - som dette hæfte handler om. Det er hensigten, at du med hæftet får et bedre kendskab til Excel, så du vil erfare, at nogle arbejdsgange kan gøres på en hurtigere og lettere måde, når du bearbejder data. Dermed kan der spares tid, og du vil sandsynligvis også få et bedre slutprodukt med et større overblik over dine data. Hæftet er skrevet til personer der er brugere af Excel og derfor har et vist kendskab til programmet. Hæftet er opbygget med beskrivelser og illustrationer, så brugere med bare en smule kendskab til Excel vil kunne benytte hæftet.

4

kapitel 0.1

Indledning

Men også mere erfarne brugere vil kunne drage nytte af hæftet, idet det beskriver mange forskellige funktioner, rutiner og metoder, som er mindre kendte, men som kan være meget nyttige. Målgruppen for hæftet er både private brugere, studerende og personer, der bruger Excel i forbindelse med deres arbejde. Alle kan lære nogle nye sider af Excel at kende, som således kan være med til at udvide brugen af Excel til flere nyttige ting, og gøre brugen af programmet mere effektiv. Hæftet indeholder en beskrivelse af cases, hvor de benyttede funktioner og metoder er beskrevet. De enkelte cases kan give inspiration til nye måder at bruge Excel på, og måske kan de beskrevne funktioner og metoder give ideer til en mere hensigtsmæssig udnyttelse af Excel på helt andre områder.

I Excel kan du lave mange forskellige kurver og diagrammer ud af dine data. Her kan du se et eksempel på en omformning af figur 6.18 på side 74.

Libris . d k

5

6

kapitel 0.1

Indledning

1. 0 1. 0

Import af data, pc’ens diske pivottabel og pivotdiagram

Libris . d k

7

1. 0

Import af data, pivottabel og pivotdiagram Analyse af pengeforbrug Excel er et fantastisk godt program til at arbejde med data hentet fra andre programmer. Dataene eksporteres eller kopieres fra det benyttede program og overføres til Excel. Her er det så muligt at benytte alle Excels funktioner til analyse af dataene. Inden dataene kan analyseres i Excel, skal de ofte bearbejdes, og til dette er Excels tekstfunktioner meget anvendelige. I dette kapitel skal du se på, hvorledes data eksporteres fra en netbank til Excel, hvor tallene så analyseres. Til analysen benyttes tekstfunktionen VENSTRE, og der benyttes pivottabeller og pivotdiagrammer. Dette giver således et mere overskueligt overblik over pengeforbruget. I kapitlet vil der blive brugt funktionen VENSTRE. Des­uden vil kapitlet omhandle: • • • •

Import af data Kolonnebredde Pivottabel Pivotdiagram

Eksport af kontobevægelserne fra netbank til Excel Først skal du logge ind på din netbank og vælge konto­ bevægelser fra den konto du ønsker at se nærmere på. Du skal herefter angive den periode, i hvilken pengefor-

8

kapitel 1

Import af data, pivottabel og pivotdiagram

bruget skal undersøges nærmere. Det kan f.eks. være fra d. 01.01.10 til d. 31.12.10. Det sidste du skal gøre inde i netbanken, er at få eksporteret kontobevægelserne til Excel. Det kan variere lidt fra netbank til netbank. I de fleste netbanker er det dog ganske simpelt. Nedenfor er angivet eksempler fra Danske Netbank (Den Danske Banks netbank), Nordea og Lån & Spar Bank.

Danske Netbank Når du har fået den ønskede kontoopgørelse frem i Danske Netbank, skal du klikke på knappen Gem som fil. Herefter har du mulighed for at gemme filen som en XML-fil eller en CSV-fil. Se figur 1.1. Du skal vælge at gemme som en XMLfil. Denne fil kan du åbne i Excel og uden videre benytte i Excel. Figur 1.1 Gem data fra Danske Netbank.

Nordeas Netbank I Nordea er der et menupunkt der hedder Gem kontobe-

vægelser som Excel-fil. Se figur 1.2.

Libris . d k

9

Figur 1.2 Gem data fra Nordea netbank.

Du kan også her nemt gemme kontobevægelserne som en Excel-fil og senere åbne filen i Excel. Så det er lige så nemt som i Danske Netbank.

Lån & Spar Banks netbank I Lån & Spar Banks netbank foregår det næsten på samme måde. Her er dog lidt flere menupunkter du skal igennem, men det er ganske let. Når du har frembragt de ønskede kontobevægelser, skal du vælge Eksport. Se figur 1.3. Figur 1.3 Gem data fra Lån & Spar Banks netbank.

Herefter skal du vælge, hvilket eksportformat der ønskes. Vælg her csv-format (semikolon-separeret) og klik på OK. Se figur 1.4. Figur 1.4 Valg af format.

10

kapitel 1

Import af data, pivottabel og pivotdiagram

Til sidst skal du svare på, om du vil åbne eller gemme filen. Du kan f.eks. vælge at åbne filen og derefter gemme den med et navn og på et sted, du kan huske. Kontobevægelser fra andre netbanker får du på lignende måder med en anelse variation fra netbank til netbank. Uanset hvilken netbank du bruger, kan du altså let få en Excel-fil med dine kontobevægelser i den ønskede periode. I det følgende skal du se nærmere på en fil med kontobevægelser fra Lån & Spar Bank, hvor det er nødvendigt at benytte en CSV-fil og ikke en ”rigtig” Excel-fil. De Excel-formler og funktioner, du kommer til at benytte, vil være de samme uanset, hvilken bank dataene stammer fra.

Nogle få hurtige ændringer i Excel Først bliver du måske lidt forundret over, at der er en masse intetsigende ciffertegn (havelåger) i Excel-arket. Det skyldes at celleindholdet, som her enten er en tekst, en dato eller et tal, fylder så meget, at cellerne ikke har en tilstrækkelig bredde. Se figur 1.5. Det ordnes imidlertid let. Figur 1.5 Ciffertegn på grund af for lille kolonnebredde.

Marker kolonnerne A til E, ved at markere A’et i kolonne A og føre musen til E’et i kolonne E mens venstre musetast holdes nede, slip venstre musetast når du kommer hen til E’et. Kolonnerne vil skifte farve, når de er blevet markeret. Før musen hen til skillelinjen mellem kolonnerne A og B oppe ved overskrifterne. Markøren vil skifte udseende til en dobbeltpil. Se figur 1.6. Dobbeltklik nu med venstre musetast, og du vil se, at cellerne netop bliver så brede at ciffertegnene forsvinder. Libris . d k

11

Figur 1.6 Ændring af kolonnebredde.

Der skal nu indsættes nogle kolonneoverskrifter. Ved en udskrift fra f.eks. Nordea vil overskrifterne allerede være der, da de er kommet med kontobevægelserne i forbindelse med eksporten. Hvis der ingen kolonneoverskrifter er, som ved dataene fra Lån & Spar Bank, så klik på 1-tallet i øverste række, således at række 1 bliver markeret. Under fanen Startside i gruppen Celler klikkes på Indsæt. Se figur 1.7. Herved indsættes en tom række foroven i arket. Du kan enten blot klikke på ikonet for Indsæt, eller klikke på pilen neden for ikonet og efterfølgende vælge Indsæt arkrække.

Figur 1.7 Indsæt en række.

Du skal nu give kolonnerne overskrifter, som f.eks. ”Dato”, ”Rentedato”, ”Tekst”, ”Beløb” og ”Saldo”. I det følgende skal du se nærmere på det årlige forbruget. Du skal derfor slette linjerne med indtægterne, som f.eks. kan være tolv månedslønninger. I det videre arbejde skal betalingerne grupperes efter teksten, men her skal kun benyttes den første del af teksten, f.eks. de første otte bogstaver. Så derfor skal du tilføje en ekstra kolonne, som skal indeholde de første otte bogstaver af teksten. Excel klarer det selvfølgelig let. Skriv overskriften ”Kort tekst” i celle F1. Marker celle F2. Under fanen Formler i gruppen Funktionsbibliotek. vælges Tekst. Se figur 1.8. Fra rullelisten vælger du funktionen VENSTRE.

12

kapitel 1

Import af data, pivottabel og pivotdiagram

Figur 1.8 Vælg funktionen VENSTRE.

I dialogboksen Funktionsargumenter udfyldes feltet ved Tekst med C2, og feltet ved Antal_tegn udfyldes med 8. Teksten skal komme fra cellen C2, og du skal bruge de otte tegn der er længst til venstre. Se figur 1.9. Klik på OK.

Figur 1.9 Vælg celle og antal tegn.

Libris . d k

13

Figur 1.10 Kopiering af formlen i cellen til andre celler.

Excel har nu indsat formlen =VENSTRE(C2;8) i celle F2. Formlen bevirker at celle F2 kommer til at indeholde de 8 tegn, der er længst til venstre i celle C2. Kopier celle F2 til resten af kolonnen. Dette gøres ved at markere cellen F2 og flytte musemarkøren til det lille kvadrat i det nederste højre hjørne af cellen F2. Markøren vil så skifte udseende til et lille sort kors. Se figur 1.10. Tryk nu på venstre musetast og hiv musen ned til sidste række og slip. Du har nu kopieret formlen til hele kolonnen.

Pivottabel Nu kan forbruget undersøges ved hjælp af en pivottabel. Marker hele regnearket ved at klikke på firkanten til venstre for kolonne A over række 1. Se figur 1.11. Under fanen Indsæt i gruppen Tabeller vælges Pivottabel. Se figur 1.12. Figur 1.11 Markering af hele arket. Figur 1.12 Valg af Pivottabel.

Figur 1.13 Valg af data til pivottabellen.

14

kapitel 1

Der fremkommer nu en dialogboks, som kan ses på figur 1.13. Her kan du blot klikke på OK. Feltet ved Tabel/område er udfyldt af Excel.

Import af data, pivottabel og pivotdiagram

Får du i en lignende situation brug for selv at udfylde feltet, skal det udfyldes med celleområdet med dataene, som f.eks. A1:F325, husk at kolonneoverskrifterne skal med. Forneden i dialogboksen vælges blot Nyt regneark. Du kommer nu over til et nyt ark, hvori der er endnu en dialogboks. Figur 1.14 viser dialogboksen.

Figur 1.14 Valg af felter til pivottabellen. Figur 1.15 Ændring af Antal til Sum.

Med musen trækker du ordet Beløb fra det store felt foroven ned i det nederste højre felt med overskriften Værdier. Der kommer da til at stå Antal af Beløb. Med musen trækker du ligeledes Kort tekst fra det øverste felt ned i det nederste venstre felt med overskriften Rækkenavne. I feltet Værdier står der nu måske Antal af beløb. Det skal ændres til Sum af beløb. Klik på den lille pil til højre for Antal af beløb. Vælg Værdifeltindstillinger. Vælg Sum i den nye dialogboks. Se figur 1.15. Klik på OK. Du har nu en pivottabel med en oversigt over årets forbrug. Se figur 1.16. Libris . d k

15

Figur 1.16 Oversigt over årets forbrug.

Hvis du ikke har lukket vinduet Feltliste i pivottabel, se øverst i figur 1.14, så vil dette vindue komme frem, når du klikker på pivottabellen. Dette giver dig mulighed for at ændre på pivottabellen. Hvis du har lukket vinduet Feltliste i pivottabel, så vil vinduet dog ikke komme frem ved klik på pivottabellen. Du kan nu let få vinduet frem ved at højreklikke på pivottabellen og vælge Vis feltliste. Prøv også at dobbeltklikke på et af tallene, vælg f.eks. tallet ud for ”visa usd”. I et nyt ark vil du så få vist alle de poster, der har teksten ”visa usd”, og du har nu mulighed for at undersøge hver enkelt post nærmere. Se figur 1.17. Havelågerne (ciffertegnene) skyldes igen at kolonnebredden er for lille.

Pivotdiagram Det er ofte mere illustrativt at se forskellige diagrammer over tallene. Excel kan lave pivotdiagrammer lige så let som at lave pivottabeller.

16

kapitel 1

Import af data, pivottabel og pivotdiagram

Vend tilbage til arket med tallene, og marker igen hele regnearket. Under fanen Indsæt i gruppen Tabeller skal du klikke på den lille pil under ikonet Pivottabel.

Figur 1.17 Detaljering af et af punkterne i oversigten.

I den lille menu der kommer frem, skal du vælge Pivotdiagram. Der dukker nu en dialogboks op ligesom tidligere. Du kan ofte blot vælge OK. Vinduet skal være udfyldt som du kan se det på figur 1.13, og det har Excel sikkert allerede været så venlig at gøre for dig. Der fremkommer nu den samme dialogboks, som du så under pivottabeller. Se figur 1.14. Dialogboksen skal udfyldes ligesom du gjorde tidligere. Husk at ændre Antal af Beløb til Sum af Beløb, som omtalt under Pivottabel. Når du har gjort dette, kommer der denne gang både en pivottabel som tidligere samt et pivotdiagram. Se figur 1.18. I dette diagram kan det let ses, hvordan de største udgifter er blevet betalt. Dette kan være en fordel for nystartede virksomheder, som har behov for at finde ud af hvor store udgifterne er. Digrammet kan altså være med til at skabe et økonomisk overblik. I Excel kan du også lave andre diagramtyper. Højreklik på diagrammet. Vælg Skift diagramtype. Der kommer nu en dialogboks frem med et hav af forskellige diagramtyper. Du kan f.eks. vælge cirkel. Vælg den første i gruppen, klik på den og klik derefter på OK.

Libris . d k

17

Figur 1.18 Pivotdiagram over årets forbrug.

Du får nu et cirkeldiagram, også kaldet et lagkagediagram. Se figur 1.19. Det største cirkeludsnit er betalingsservice. Cirkeludsnit kan du adskille fra resten af cirklen: Klik på cirklen. Klik på det cirkeludsnit du ønsker adskilt, i dette tilfælde skal du klikke på cirkeludsnittet, der repræsenterer betalingsservice. Hold venstre musetast nede, og ryk cirkeludsnittet lidt til venstre, så det bliver adskilt lidt fra de øvrige cirkeludsnit. Se figur 1.20. Når du holder musen hen over et område, vises værdien og hvor mange procent området repræsenterer af den samlede sum. Det er nu let at se at cirkeludsnittet er lidt større end halvdelen. Excel giver dig utrolig mange andre muligheder for at ændre på diagrammet. Du skal blot højreklikke på diagrammet / cirklen og så f.eks. vælge Formater dataserie. Du får nu igen en dialogboks. Under fanen Serieindstillinger kan du f.eks. vælge at rotere diagrammet 180°. Du får herved udsnittet der repræsenterer indtægter på højre side af diagrammet.

18

kapitel 1

Import af data, pivottabel og pivotdiagram

Figur 1.19 Cirkeldiagram over årets forbrug.

Du kan også højreklikke og vælge Skift diagramtype (Skift seriediagramtype), og du kommer til dialogboksen, hvor du kan skifte diagramtype til en af alle de andre muligheder der findes. Der er ganske mange muligheder, og den bedste måde at lære dem at kende på, er at prøve sig frem.

Figur 1.20 Cirkeludsnit over årets indtægter.

Libris . d k

19

2.0

Import af data og benyttelse af tekstfunktioner

2.0

Import af data og benyttelse af tekstfunktioner Undersøgelse af mobilforbrug I mange mobilabonnementer er der inkluderet brug af en vis mængde mobildata, også kaldet GPRS eller 3G. I nogle abonnementer er der f.eks. inkluderet 1 GB data. Bruges der mere end 1 GB data, skal der hos nogle selskaber betales for merforbruget, og andre selskaber nedsætter hastigheden så meget, at internetforbindelsen reelt bliver ubrugelig. De selskaber, der tager betaling for merforbruget, tager sig pænt betalt. Det kan dreje sig om alt fra 35 øre til 10 kr. pr. MB, der bruges ud over den inkluderede mængde. Merforbruget kan altså resultere i en væsentlig forøgelse af mobilregningen, men ved hjælp af Excel er det let at holde øje med forbruget. I kapitlet vil der blive brugt faciliteterne: • •

Erstat Autosum

Desuden vil kapitlet omhandle: • •

22

kapitel 2

Import af data fra mobilselskab Kolonnebredde

Import af data og benyttelse af tekstfunktioner

Data fra mobilselskab I det følgende er dataene hentet fra mobilselskabet ”Oister”. Der skal foretages nogle konverteringer af dataene fra ”Oister”, før det samlede dataforbrug kan opgøres. Så, hvis du har et andet mobilselskab, hvilket der er stor sandsynlighed for, vil konverteringen formentlig være mere simpel. Men selv med data fra ”Oister” vil du se, hvor let det kan gøres. Fra andre mobilselskaber foregår det på næsten samme måde, og det er de samme formler og funktioner der skal benyttes. Først skal du logge ind på din konto hos dit mobilselskab og finde en opgørelse over dataforbruget i den ønskede periode. Dataene markeres og kopieres ind i Excel. I figur 2.1 kan du se et udsnit af dataene efter, at de er kopieret ind i Excel. Figur 2.1 Import af dataforbrug i Excel.

Antallet af brugte bytes står i kolonne B (1 GB ≈ 1.000 MB ≈ 1.000.000 KB ≈ 1.000.000.000 bytes ). Her står der dog ikke bare antallet, men før tallet står der ordet ”Data” og tallet er efterfulgt af enheden ”bytes”. Dette betyder, at du ikke blot kan summere alle tallene i Excel, men at du først skal foretage nogle simple manøvrer. I kolonne A er der en del ciffertegn. Det skyldes, at kolonnebredden er for lille.

Libris . d k

23

Figur 2.2 Valg af faciliteten Erstat

Kolonnen gøres bredere, som beskrevet i kapitel 1, ved at markere kolonne A, føre musen hen til skillelinjen mellem kolonnerne A og B oppe ved overskrifterne og dobbeltklikke med venstre musetast. Herefter vil dato stå rigtigt. Kolonne B indeholder forbruget, men foran tallet står teksten Data og efter tallet står enheden bytes. Det skal fjernes inden Excel kan foretage en sammentælling. Dette gøres ved at søge på ordet ”Data” og erstatte det med ingenting, og tilsvarende med enheden ”bytes”. Marker kolonne B. Under fanen Startside i gruppen Redigering vælges Søg og vælg. Fra rullelisten vælges Erstat. Se figur 2.2. Der fremkommer en dialogboks. Feltet ved Søg efter udfyldes med ordet ”Data” og feltet ved Erstat med skal forblive tomt. Se figur 2.3. Husk at D’et skal med stort, der er her forskel på store og små bogstaver.

Figur 2.3 Angivelse af søgeord.

Klik på Erstat alle. Excel fortæller dig herefter, hvor mange erstatninger der er blevet foretaget. Dette gentages med ordet ”bytes” i stedet for ordet ”Data”. Du har nu kun tallene tilbage i kolonne B. Vinduet Søg og erstat skal lukkes efter brugen.

24

kapitel 2

Import af data og benyttelse af tekstfunktioner

Det totale forbrug Det totale forbrug skal nu udregnes. Det er blot at lægge alle tallene i søjle B sammen. Vælg først en vilkårlig tom celle, f.eks. celle E2. Under fanen Startside i gruppen Redigering vælges Autosum. Her er der et summationstegn ∑ (det græske bogstav store sigma). Se figur 2.4. Figur 2.4 Brug af faciliteten Autosum

Klik herefter på B’et over kolonne B og tryk på ENTER, så vil Excel skrive formlen =SUM(B:B) i celle E2. Ved blot at klikke på B’et over kolonne B, i stedet for at markere et område, vil Excel summere alle tallene i søjle B, og du behøver ikke at bekymre dig om, hvor mange rækker data der er i Excel-arket. Se figur 2.5. Figur 2.5 Angivelse af celler der skal summeres.

I cellen E2 har Excel nu indsat formlen =SUM(B:B). Du har nu det totale forbrug af data i bytes. Ved at dividere forbruget med 1.000.000.000 fås forbruget i GB. Ønsker du at se forbruget af telefonopkald fra din mobiltelefon, kan dette gøres på tilsvarende måde. Opgørelsen af telefonopkald vil ofte være mere simpel, da der ikke nødvendigvis er tekst uden om tallene. Som du lige har set med ordene ”Data” og ”bytes”. Fra selskabet ”Oister” kan telefonforbruget blot sammenlægges når dataene er kopieret over i Excel. Libris . d k

25

3.0

Diagrammer, tendenslinje og målsøgning

3.0

Diagrammer, tendenslinje og målsøgning Pensionsberegning Du har sikkert en arbejdsmarkedspension, som er en pensionsordning, hvor en del af din løn hver måned går til. En gang om året får du så en opgørelse fra din pensionskasse, hvor du kan se, hvor stor en pension du vil få, hvis du går på pension, når du bliver 60 år, 62 år, 65 år eller 67 år. Men hvor meget vil du få, hvis du går på pension når du bliver 63 år og 4 måneder? Det står der ikke noget om. Med hjælp af Excel kan du heldigvis nemt lave et lille regneark, hvor det vil være muligt at få et overblik over pensionens størrelse ved andre aldre end dem, der er nævnt i pensionsoversigten. De pensionsbeløb, der således udregnes i Excel, vil være en tilnærmet værdi, da det er en meget kompliceret sag at udregne den nøjagtige værdi af pensionens størrelse. Kapitlet vil omhandle • • •

Diagrammer Tendenslinje Målsøgning

Data Først skal du indtaste oplysninger fra pensionsoversigten, som du modtager fra din pensionskasse en gang om året. Den opgørelse du får fra din pensionskasse, vil selvfølgelig

28

kapitel 3

Diagrammer, tendenslinje og målsøgning

indeholde nogle andre tal end dem du ser på figur 3.1, og de følgende beregninger vil derfor også indeholde nogle andre tal. Figur 3.1 Indtast data fra pensionskassen.

Diagram og tendenslinje Du skal nu lave en graf over disse tal, og bagefter skal du lave en tendenslinje. Senere i kapitlet står der omtalt, hvad en tendenslinje er.

Marker celleområdet A1:B5, og under fanen Indsæt i gruppen Diagrammer vælges menuen Punktdiagram. Se figur 3.2.

Figur 3.2 Markering af dataene og valg af Punktdiagram.

Libris . d k

29

Figur 3.3 Fremstilling af graf.

Der fremkommer nu en ny menu, og her vælges

Punktdiagram med jævne kurver og datamærker.

Grafen bliver nu tegnet automatisk. Grafen kommer til at se ud som den du ser på figur 3.3. Du kan ændre diagrammet på forskellige måder for at gøre grafen lettere overskuelig, og desuden skal tendenslinjen tilføjes. Klik med venstre musetast på et af tallene på den Vandrette (Værdi) akse, dvs. et af tallene 58, 60, osv. Klik herefter med højre musetast og vælge Tilføj overordnede gitterlinjer. Herved fremkommer der nogle lodrette støttelinjer. Klik på et af tallene på den lodrette (Værdi) akse med musen, højreklik og vælg Formater akse.

30

kapitel 3

Diagrammer, tendenslinje og målsøgning

Under Akseindstillinger vælges Minimum til Fast og skriv 150000 i feltet, så vil den lodrette (Værdi) akse starte ved 150.000 og grafen vil udnytte pladsen bedre. Herefter lukkes vinduet. Se figur 3.4. Figur 3.4 Ændringer af akseindstillinger.

Peg på grafen med musen, højreklik og vælg Tilføj tendenslinje. Der kommer nu en dialogboks frem. Vælg Indstillinger for tendenslinje og under Tendens-/ regressionstype markeres Eksponentiel og under Prog­ nose skrives 3 i feltet ved Fremad og til sidst sættes hak i Vis ligning i diagram og i Vis R-kvadreret værdi i diagram. Se figur 3.5. Tendenslinjen (eller tendenskurven) er en beregnet linje (eller kurve) som passer bedst muligt til de oprindelige værdier.

Libris . d k

31

Figur 3.5 Indstillinger for tendenslinje.

Tendenslinjen starter i Excel som udgangspunkt ved den første værdi (her 60 år) og slutter ved den sidste værdi (her 67 år). I Excel er det let at forlænge tendenslinjen enten fremad eller tilbage. Ved at skrive 3 i feltet ved Fremad forlænges tendenslinjen 3 år fremad; således kan du se pensionens størrelse, hvis du går på pension som 70-årig. Du kan selv lege med at ændre på nogle af de mange andre indstillinger. Der er virkelig mange muligheder. F.eks. kan du ændre på stregtype og stregfarve. Når du har tilpasset grafen, så den passer til dine behov, klikker du på Luk. Diagrammet har nu ændret sig, så det er kommet til at se nogenlunde ud som på figur 3.6. I diagrammet er der nu kommet yderligere tre ting. Tendenslinjen, den tynde sorte kurve – som her ikke er en ret linje men en kurve.

32

kapitel 3

Diagrammer, tendenslinje og målsøgning

Tendenslinjen / tendenskurven skal ligge (næsten) oven i den oprindelige kurve på det stykke, hvor den oprindelige kurve er. Du kan se at tendenskurven først stopper ved 70 år, altså tre år senere end den oprindelige graf, som stopper ved de 67 år.

Figur 3.6 Diagram med tendenslinje og funktionsudtryk.

Ligningen for tendenslinjen y = 1365,9e0,0821x – som her er ligningen for en eksponentialfunktion. R-kvadreret med værdien R² = 0,9994. Hvis R2 i øvrigt er tæt på 1, er tendenslinjen (tendenskurven) en god tilnærmelse til den rigtige kurve. I princippet kan diagrammet nu bruges til at se, hvor stor din pension vil være på et givent tidspunkt. Beregninger, som er lette at lave, vil dog give en mere nøjagtig værdi for pensionens størrelse end den værdi man kan aflæse i diagrammet. I det næste afsnit er beskrevet, hvordan du laver bereg­ ningerne.

Beregninger I regnearket tilføjes nu følgende celler i række 8 og 9. Se figur 3.7. Libris . d k

33

Figur 3.7 Beregning af pensionens størrelse.

Værdierne for tallene a og b fremgår af ligningen for tendenslinjen y = 1365,9e0,0821x . Værdien for tallet a, som du skal skrive i celle A9, er tallet der står efter e i ligningen (som altså er 0,0821) og værdien for tallet b, som du skal skrive i celle B9, er tallet der står foran tallet e i ligningen (som i dette tilfælde er 1365,9). Benytter du dine egne tal, vil du selvfølgelig også her få nogle andre tal end de viste. Antallet af år og måneder kan naturligvis ændres, og når du har skrevet formlerne ind i cellerne E9 og F9, vil værdien af pensionen ændres hver gang du ændrer alderen for pensionen i cellerne C9 og D9. Skriv følgende formel i celle E9: =C9+D9/12 Formlen udregner 63 år og 4 måneder til 63,33 år. Skriv følgende formel i celle F9: =B9*EKSP(A9*E9) Det er ligningen for eksponentialfunktionen. Værdien af den forventede pension står nu i celle F9. Du kan nu prøve at ændre alderen for den forventede pensionsalder, og du vil straks se at det forventede pensionsbeløb ændrer sig.

34

kapitel 3

Diagrammer, tendenslinje og målsøgning

Målsøgning

Figur 3.8 Valg af funktionen Målsøgning.

Du kan også stille spørgsmålet, hvornår kan jeg gå på pension, hvis jeg ønsker en årlig pension på f.eks. 375.000 kr.? Ved at se på grafen kan du aflæse, at en pensionsalder på lidt over 68 år giver en pension på 375.000 kr. Det kan også let udregnes mere nøjagtigt ved at benytte Excel-faciliteten Målsøgning. Slet formlen i celle E9 (marker cellen og tryk på Delete). Marker cellen F9. Under fanen Data i gruppen Dataværktøjer vælges What if analyse. Se figur 3.8. Der fremkommer en lille menu, hvor du skal vælge Målsøgning. Se figur 3.9. Figur 3.9 Angivelse af celler og slutværdi til brug for Målsøgning.

I dialogboksen der nu kommer frem, står der allerede F9 ud for Angiv celle. Libris . d k

35

I feltet ved Til værdi skrives 375000 og i feltet Ved ændring af celle skrives E9. Klik på OK, og værdien af celle E9 er nu ændret til 68,39. Det betyder, at ved at gå på pension i en alder af 68,39 år vil pensionen blive 375.000 kr. De 68,39 år kan omregnes til 68 år og 5 måneder, ved at gange decimalen 0,39 med 12. Det skal bemærkes, at det beløb du udregner, selvfølgelig ikke er bindende for din pensionskasse. Pensionens størrelse som du har udregnet, er en tilnærmet værdi. Ved en pensionsalder i intervallet 60 til 67 vil afvigelsen, mellem den værdi du har beregnet, og den værdi pensionskassen vil kunne beregne sig frem til, formentlig være under 1%. Der vil dog gælde at jo mere pensionsalderen er over 67, jo større kan du forvente at afvigelsen bliver.

Eksponentialfunktion Værdier der vokser med en fast procent pr. år (pr. måned, pr. dag eller helt generelt pr. enhed) vokser eksponentielt. Sættes et beløb, f.eks. 1000 kr., ind på konto i en bank til en fast årlig rente, vil saldoen med rente og rentes rente vokse eksponentielt som årene går (i praksis er den årlige rente dog ikke fast).

36

kapitel 3

Diagrammer, tendenslinje og målsøgning

Tendenslinje Når man har nogle punkter (der i vores tilfælde består af sammenhørende værdier af en alder og et beløb), kan man udregne den bedst mulige tendenslinje, d.v.s. den bedst mulige linje eller kurve, der passer til de pågældende punkter. Den simpleste form for en tendenslinje er en ret linje. Hvis punkterne imidlertid ikke tilnærmelsesvis ligger på en ret linje, så vil der være andre typer af tendenslinjer, der passer bedre til punkterne end en ret linje. I Excel er der flere muligheder at vælge imellem. Når man skal vælge en type tendenslinje (som altså snarere burde kaldes en tendenskurve), skal man finde den, der passer bedst til de oprindelige punkter. Dette kan gøres ved at se på diagrammet og se om kurven med punkterne og tendenslinjen tilnærmelsesvis er sammenfaldende. En anden mulighed er at se på den R-kvadrerede værdi R2. Værdien for R kaldes korrelationskoefficienten, så R2 er altså kvadratet på korrelationskoefficienten. Hvis værdien for R2 er tæt på 1, så er tilnærmelsen god. Så når man skal finde ud af hvilken type tendenslinje der passer til målepunkterne, kan man se værdien for R2. I vores tilfælde, hvor vi benyttede en eksponentiel tendenslinje, udregnede Excel R2 til 0,9994 som er tilstrækkelig tæt på 1. Herudover angiver Excel også ligningen for tendenslinjen, som i vores tilfælde altså er en eksponentialfunktion. Ligningen benyttede vi i cellen F9, der udregner størrelsen af pensionen.

Libris . d k

37

4.0

Problemløser og SUMPRODUKT

4.0

Problemløser og SUMPRODUKT Minimering af transportomkostninger Excel har nogle tilføjelsesprogrammer som kan installeres efter behov. Først i dette kapitel skal du se på installationen af tilføjelsesprogrammet Problemløser, hvilket gøres med nogle få klik med musen. Andre tilføjelsesprogrammer installeres på tilsvarende måde, hvis du på et andet tidspunkt skulle få brug for dette. Senere i kapitlet gennemgås et eksempel på, hvor nyttigt tilføjelsesprogrammet Problemløser kan være til løsning af en minimeringsopgave. Du skal se på en klassisk minimeringsopgave. Opgaven eller problemet er at få transporteret varer eller personer med mindst mulige omkostninger. Men selvom det er et svært matematisk problem, så bliver det let og elegant klaret af Excel på et øjeblik. Når du har fået problemet beskrevet, så kan du evt. se om du overhovedet kan løse problemet uden brug af Excel. Det er ikke let, så er du advaret. I kapitlet vil der blive brugt funktionerne • •

SUM SUMPRODUKT

Desuden vil kapitlet omhandle Figur 4.1 Indstillinger vælges under fanen Filer.

40

kapitel 4

• •

Installering af tilføjelsesprogrammer Tilføjelsesprogrammet: Problemløser

Problemløser og SUMpRODUKT

Installering af tilføjelsesprogrammer Du skal her installere tilføjelsesprogrammet Problemløser. Det gøres på følgende måde: Klik på fanen Filer i øverste venstre hjørne. Se figur 4.1. Vælg Indstillinger næsten nederst på den menu der dukker op. Vælg Tilføjelsesprogrammer på den dialogboks der kommer frem. Se figur 4.2. Nederst i den fremkomne dialogboks er kommandoen Administrer efterfulgt af en rullemenu. Fra rullemenuen vælges Excel-tilføjelsesprogrammer. Klik herefter på Udfør. Afkryds Tilføjelsesprogrammet Problemløser i den dialogboks der kommer frem, og klik på OK.

Figur 4.2 Valg af admi­ nistration af Excel-tilføjelsesprogrammer

Libris . d k

41

Det kan godt tænkes at der er flere af de øvrige tilføjelsesprogrammer der allerede er krydset af. Se figur 4.3. Nu er tilføjelsesprogrammet Problemløser installeret, og du skal senere i dette kapitel se, hvorledes Excel løser et avanceret minimeringsproblem på en utrolig let måde ved hjælp af tilføjelsesprogrammet Problemløser. Figur 4.3 Valg af tilføjelsesprogrammet Problemløser.

Hvis der ikke er blevet benyttet en standardinstallation ved installeringen af Excel, men der er blevet valgt Ikke tilgæng­e­lig ved tilføjelsesprogrammet Problemløser, vil du ikke kunne finde tilføjelsesprogrammet Problemløser. Du skal da have tilføjelsesprogrammet installeret: Indsæt installations CD’en med Office 2010 og kør programmet setup.exe. Vælg Tilføj eller fjern funktioner og klik på Fortsæt. Klik herefter på pilen ud for Microsoft Excel, vælg Kør alt fra Denne computer, og klik på Fortsæt. Tilføjelsesprogrammet er nu blevet overført til computeren. Excel skal herefter genstartes, og du kan så installere tilføjelsesprogrammet Problemløser som beskrevet tidligere i kapitlet.

42

kapitel 4

Problemløser og SUMpRODUKT

Transportproblem – beskrivelse af opgaven Du skal se på et traditionelt transportproblem. Du kommer her til at se på et tænkt eksempel, men det vil være let at benytte eksemplet i andre situationer. I Europa er der 3 distributionscentre til en bestemt type varer. De 3 distributionscentre ligger i Stockholm, London og Paris. Der skal herfra leveres varer til 5 grossister der har lagre i Oslo, Dublin, København, Prag og Rom. Leveringen skal selvfølgelig foretages således, at transportomkostningerne bliver mindst mulige. Den mængde varer de 3 distributionscentraler kan levere, og den mængde varer de 5 grossister skal bruge, er anført i skemaet nedenfor. Varemængden er angivet i tons pr. år. Desuden fremgår transportomkostningerne i kr. pr. ton også af figur 4.4. Af skemaet kan ses at transportomkostningerne er 1400 kr. pr. ton fra Stockholm til København, at distributionscentralen i Stockholm kan levere 47 tons varer pr. år, samt at grossisten i København skal bruge 21 tons varer pr. år. Figur 4.4 Opgørelse af transportomkostninger.

Libris . d k

43

Transportproblemet består nu i at finde ud af, hvorledes mængden af varer skal transporteres fra distributionscentralerne til grossisterne billigst muligt. Ved hver transport er omkostningerne altså antallet af tons varer ganget med transportomkostningerne pr. ton. De samlede omkostninger bliver summen af omkostninger for alle transporterne, og det er denne sum der skal minimeres.

Eksempel Her er først et eksempel inden selve opgaven løses. Af figur 4.5 kan ses hvorledes en plan for levering af varerne kan være. Her er der ikke nødvendigvis tale om den leveringsplan der giver de mindste leveringsomkostninger, men bare en plan der får fordelt varerne. Af skemaet kan ses, at der skal transporteres 13 tons varer fra London til Dublin pr. år. Dette er en mulig løsning af firmaets varetransport, det er dog ikke den løsning der giver den mindste transportomkostning. Transportomkostningerne bliver i dette eksempel: 27*2300 + 20*1200 +13*1000 +21*1600 + 17*2400 + 17*1800 + 28*1400 =243300 Figur 4.5 Eksempel på en mulig fordeling af varer.

44

kapitel 4

Problemløser og SUMpRODUKT

Det er antallet af tons varer der skal transporteres, ganget med omkostningerne pr. ton. Excel kan nu nemt hjælpe dig med at finde hvorledes leverancen af varer skal foregå for at leveringsomkostningerne bliver mindst mulige.

Indtastninger af data Først skal du indtaste alle oplysningerne i Excel: Lav de viste skemaer i Excel. Se figur 4.6. Cellerne B12 til F14 kan enten indeholde tallene fra eksemplet ovenfor eller blot være tomme. I de celler der har en orange udfyldningsfarve, skal der være formler. Formlerne kan du se på figur 4.6. Når du skriver formlerne ind, vil der fremkomme nogle tal i cellerne, du vil altså ikke se formlerne som på figuren. Celle B15 skal udregne summen af cellerne B12 til B14, dvs. du skal skrive formlen =SUM(B12:B14). For at udregne den ønskede sum kan du benytte Autosum, som findes under fanen Startside i gruppen Redigering.

Figur 4.6 Data og formler til løsning af transportproblemet.

Libris . d k

45

Se figur 4.7. Marker celle B15 og klik på Autosum. Herefter skal du markere cellerne B12 til B14 med musen og trykke på ENTER. Excel har herved indsat formlen =SUM(B12:B14) i celle B15. Formlerne i cellerne C15 til F15 skal være tilsvarende. Figur 4.7 Valg af faciliteten Autosum.

Celle G12 skal udregne summen af cellerne B12 til F12, dvs. du skal skrive formlen =SUM(B12:F12). Her kan Autosum også bruges med fordel. Formlerne i cellerne G13 til G14 skal være tilsvarende. Formlerne i cellerne G6 og G15 bruges ikke. Herefter skal du have udregnet de samlede transportomkostninger. Omkostningerne ved en enkelt transport er lig omkostningen pr. ton ganget med antallet af tons. Det udregnes ved at gange celle B3 med celle B12, gange celle C3 med celle C12 osv. indtil celle F5 ganges med celle F14. Den samlede transportomkostning er lig med summen af alle disse omkostninger. Dette udregner Excel elegant ved brug af funktionen SUMPRODUKT. Skriv i celle A18 teksten: ”Samlede transportomkostninger” Marker celle G18 og vælg funktionen SUMPRODUKT, som du finder under fanen Formler i gruppen Funktionsbibliotek fra menuen Matematik & trigonometri.

46

kapitel 4

Problemløser og SUMpRODUKT

Se figur 4.8. Et stykke nede på rullelisten der fremkommer, finder du SUMPRODUKT. Når du har valgt funktionen, kommer der en dialogboks frem. Se figur 4.9.

I feltet ud for Matrix1 skrives B3:F5. Dette gøres lettest ved at markere feltet i dialogboksen og derefter markere cellerne B3 til F5 i regnearket. I feltet ud for Matrix2 skrives B12:F14. Klik til sidst på OK.

Figur 4.8 Funktionen SUMPRODUKT vælges fra menuen Matematik & trigonometri.

Figur 4.9 Argumenter til funktionen SUMPRODUKT.

I celle G18 vil nu være formlen =SUMPRODUKT(B3:F5;B12:F14)

Libris . d k

47

Figur 4.10 Valg af Problemløser.

Formlen udregner summen af produkterne B3*B12, …, F5*F14, altså netop den ønskede transportomkostning. Hvis du har benyttet tallene fra eksemplet ovenfor, vil den samlede transportomkostning være udregnet til 243300, som vil være resultatet der står i celle G18.

Løsning - find minimum Nu skal du finde den sammensætning af varetransporter der giver de mindste transportomkostninger. Under fanen Data i gruppen Analyse vælges Problemløser. Se figur 4.10. Der fremkommer nu en dialogboks. Se figur 4.11. Alle $-tegnene du ser på figuren, sætter Excel selv, og de har ingen betydning her, så dem skal du slet ikke tænke på. Figur 4.11 Angivelse af parametre til Problemløser.

48

kapitel 4

Problemløser og SUMpRODUKT

I feltet ved Angiv målsætning skrives G18. Hvis du markerer feltet og klikker på cellen G18 med musen, skriver Excel selv $G$18. Det er her $-tegnene kommer fra på figur 4.11, og de har ingen betydning her. I Til skal Min (minimum) være valgt. I feltet ved Via ændring af variabelceller skrives B12:F14. Dette kan evt. gøres ved at markere feltet og derefter markere cellerne B12 til F14 i regnearket. I feltet Underlagt begrænsninger skal der tilføjes nogle begrænsninger (bibetingelser). Klik på Tilføj, og du får endnu en dialogboks. Se figur 4.12. I feltet Cellereference skrives B15:F15. (Husk du skal ikke tænke på $-tegnene) Figur 4.12 Tilføjelse af begrænsning (bibetingelse).

I det midterste felt vælges = I feltet Begrænsning skrives B6:F6. Klikker du på feltet Begrænsning og markerer cellerne B6 til F6, vil Excel både tilføje $-tegnene og et lighedstegn. Begge dele er uden betydning her. Det vil bevirke at betingelsen der skal være opfyldt, er at B15 skal være lig med B6, C15 skal være lig med C6 osv. hen til F. Klik på OK. Tilsvarende tilføjes betingelsen G12:G14 = G3:G5 ved igen at klikke på Tilføj. Se figur 4.13.

Libris . d k

49

Figur 4.13 Tilføjelse af begrænsning (bibeting­else).

Klik nu endelig på knappen Løs. Vælg OK til Behold løsning i Problemløser. Se figur 4.14. Figur 4.14 Behold løsning.

Som det fremgår af figur 4.15, har Excel nu angivet i cellerne B12 til F14 den mængde varer, der skal transporteres fra distributionscentralerne til grossisterne, således at de samlede transportomkostninger bliver minimale. I celle G18 står de samlede transportomkostninger til 221400 (hvilket selvfølgelig er mindre end de transportomkostninger vi havde fra eksemplet før, som var på 243300).

50

kapitel 4

Problemløser og SUMpRODUKT

Figur 4.15 Løsningen af transportproblemet.

I cellen B13 står der 0,99999776 som skal afrundes til 1, og i celle D14 står der 7,7371E-07 som er lig med 0,00000077371. Dette tal afrundes til 0. Det letteste er at markere cellerne B12 til G18, og herefter under fanen Startside i gruppen Tal at ændre formatet Standard til Tal. Se figur 4.16. I nogle minimeringsopgaver kan der godt være flere løsninger, og Excel giver dig én af løsningerne. Du har nu set på løsningen af en minimeringsopgave. Inden for matematik er der skrevet tykke bøger om, hvorledes sådanne opgaver løses. Men ved at du har læst dette kapitel, der viser dig, hvordan du gør i Excel, kan du nu let løse sådanne opgaver fremover. Figur 4.16 Ændring af formatet til tal.

Libris . d k

51

5.0

Målsøgning og Problemløser

5.0

Målsøgning og Problemløser Løsning af ligninger Excel er ikke et matematikprogram, men Excel kan faktisk løse en hel del matematiske opgaver. I dette kapitel skal du se på hvorledes Excel kan hjælpe dig med at løse forskellige former for ligninger som du kender fra din matematikundervisning i skolen. I kapitlet vil der blive brugt funktionen •

SUMPRODUKT

Desuden vil kapitlet omhandle • •

Målsøgning Problemløser

Målsøgning er tidligere blevet omtalt i kapitel 3, og Problemløser samt SUMPRODUKT er tidligere blevet omtalt

i kapitel 4.

Almindelige ligninger, også kaldet en ligning med en ubekendt Her er et eksempel på løsning af en almindelig ligning med Excel. De ligninger du kommer ud for, vil sandsynligvis være lidt anderledes, men metoden er den samme.

54

kapitel 5.0

Målsøgning og Problemløser

Metoden bruger faciliteten Målsøgning som også blev benyttet i kapitel 3.

Figur 5.1 Løsning af en ligning.

Løs ligningen: 5*(2x-7)+6 = 5 – 2*(6-3x) Udfyld Excel som det fremgår af figur 5.1. Skriv teksten ”Værdi for x” i celle A1. Celle A2 benyttes til værdien for x. Skriv teksten ”Venstre side” i celle A3. Celle A4 benyttes til ligningens venstre side. Skriv teksten ”Højre side” i celle B3. Celle B4 benyttes til ligningens højre side. I Celle A4 skal du skrive formlen =5*(2*A2-7)+6. Det er blot ligningens venstre side, hvor x er udskiftet med cellereferencen A2. I cellen A4 vil der måske komme til at stå værdien -29, og ikke formlen på figur 5.1. I Celle B4 skal du skrive formlen =5-2*(6-3*A2). Det er blot ligningens højre side, hvor x er udskiftet med cellereferencen A2. I cellen A4 vil der måske komme til at stå værdien -7, og ikke formlen som på figur 5.1. Skriv teksten ”Venstre – Højre” i celle A5. Venstre side minus højre side skal give nul ved løsning af ligningen. I celle A6 skal du skrive formlen =A4-B4. I cellen A6 vil der måske komme til at stå værdien -22, og ikke formlen som på figur 5.1.

Libris . d k

55

Figur 5.2 Benyttelse af Målsøgning.

Under fanen Data i gruppen Dataværktøjer vælges What if-analyse. Se figur 5.2. I rullelisten der fremkommer, vælges Målsøgning. Der kommer nu en dialogboks frem. Se figur 5.3.

Figur 5.3 Angivelse af celler og slutværdi til brug for Målsøgning

I feltet ved Angiv celle skrives A6, i feltet ved Til værdi skrives 0, og i feltet Ved ændring af celle skrives A2. Klik på OK, og i vinduet med Målsøgningsstatus klikker du også blot på OK. I celle A2 kan du nu se løsningen til ligningen. I dette tilfælde bliver løsningen tallet 5,5. Løsning af en ligning med en ubekendt kan give forskellige typer af løsninger. Nogle ligninger har ingen løsninger, andre ligninger har netop én løsning, som du så i eksemplet her, og andre ligninger igen har uendelig mange løsninger. Excel vil finde én løsning til ligningen, hvis der findes en.

To ligninger med to ubekendte Excel kan også løse flere ligninger med flere ubekendte.

56

kapitel 5.0

Målsøgning og Problemløser

Figur 5.4 Løsning af to ligninger.

Her skal du se et eksempel med to ligninger med to ubekendte. Metoden kan selvfølgelig udvides til tre ligninger med tre ubekendte osv. Metoden benytter Problemløser som blev omtalt i kapitel 4. Husk at tilføjelsesprogrammet Problemløser skal være installeret før det kan benyttes, se ligeledes kapitel 4. Ligningerne skal være på formen: a*x + b*y = c, hvor a, b og c er konstanter, og x og y er de uafhængige variable. Og nu kommer eksemplet. Løs ligningerne: 1x + 2y = 16 og 6x - 3y = 21 Først skal du indskrive ligningerne i Excel. Se figur 5.4. Cellerne E3 og E4 er formler, hvor funktionen SUMPRODUKT er benyttet. Denne funktion er tidligere blevet beskrevet i kapitel 4. I cellerne E3 og E4 vil du se værdien 0 og ikke formlerne som på figur 5.4. I cellerne B1 og C1 er der gjort klart til at løsningsværdierne kan stå. Løsningsværdien til x vil komme i celle B1, og løsningsværdien til y vil komme i celle C1, men i øjeblikket er de blot tomme. Cellerne B3, C3, D3, B4, C4 og D4 er koefficienterne (tallene) fra ligningerne i selve opgaven. Husk at rækkefølgen har betydning. Resten af det der står i arket, er blot hjælpetekst. Nu kommer du til selve løsningen af ligningerne, og her benyttes Problemløser som du kan læse mere om i kapitel 4.

Libris . d k

57

Figur 5.5 Angivelse af parametre til Problemløser.

Under fanen Data i gruppen Analyse vælges Problemløser. Se figur 4.10. Der kommer derefter en dialogboks frem, som du ser på figur 5.5. I feltet Via ændringer af variabelceller skrives B1:C1. Det er cellerne hvor løsninger kommer til at stå, og de skal ændres således at begrænsningerne (ligninger) er opfyldt. Dette kan også gøres ved at klikke på feltet og derefter markere cellerne B1 til C1. Gøres det på denne måde, vil Excel

58

kapitel 5.0

Målsøgning og Problemløser

Figur 5.6 Tilføjelse af begrænsning (bibetingelse).

udfylde feltet med $B$1:$C$1. Dollartegnene har ingen betydning her. I feltet Underlagt begrænsninger skal de to begrænsninger (bibetingelser) D3 = E3 og D4 = E4 tilføjes. Klik på Tilføj, og udfyld dialogboksen der fremkommer som på figur 5.6. Dollartegnene er igen uden betydning, og måske kommer der også et ekstra lighedstegn, der heller ikke har nogen betydning. Den anden begrænsning tilføjes på lignende måde. Du kan evt. ændre Vælg en løsningsmetode til Simplex LP. Det har betydning for, hvorledes Excel internt kommer frem til løsningen.

Klik nu på Løs og klik på OK i den efterfølgende dialogboks. Excel har nu løst ligningerne, og løsningen står i cellerne B1 og C1. X-værdien står i celle B1 og har i dette tilfælde værdien 6. Tilsvarende står Y-værdien i celle C1, som i dette tilfælde har værdien 5. Husk at løsningen består både af en x-værdi og en y-værdi. Når du løser to ligninger med to ubekendte, er der forskellige typer af løsninger til et sådant ligningssystem. Nogle lig­nings­systemer har ingen løsninger, andre ligningssystemer har netop én løsning, som du så i eksemplet her, og andre ligningssystemer igen har uendelig mange løsninger. Excel vil finde én løsning til ligningssystemet, hvis der findes en. Libris . d k

59

6.0

Sortering, filtrering og formatering

6.0

Sortering, filtrering og formatering I de følgende kapitler kommer du til at stifte bekendtskab med det opdigtede lille salgsfirma ‘Hurtigt og Nemt A/S’, der har en afdeling i henholdsvis Øst- og Vestdanmark. Firmaet sælger både food og non-food produkter. På trods af firmaets lille størrelse og små datamængder, kan de anviste metoder fra Excel også bruges på større datamængder. Fremgangsmåden vil altså være den samme uanset størrelsen på virksomheden. Desuden kan metoderne generelt også bruges i private sammenhænge eller i forbindelse med uddannelse. Her er der blot taget udgangspunkt i en fiktiv virksomhed, hvor du vil blive præsenteret for forskellige problemstillinger og – selvfølgelig – løsninger ved hjælp af Excel. Du skal i dette kapitel se på nogle medarbejderes salgstal og analysere dem ved hjælp af Excel. De forskellige former for analysemetoder som gennemgås, vil bidrage til at virksomheden får et overblik over sine tal. I kapitlet vil der blive brugt • • • •

Filtrer Formatpensel Betinget formatering Sorter

Desuden vil kapitlet omhandle • • •

62

kapitel 6

Statuslinjen Diagrammer Pivottabel

Sortering, filtrering og formatering

Salgstal i Hurtigt og Nemt A/S

Figur 6.1 Salgstal for firmaet Hurtigt og Nemt A/S.

Tabellen i Excel nedenfor angiver for hver enkelt salgsmedarbejder, om der er tale om salg i Vest- eller Østdanmark, om der er tale om food eller non food, og budgettet i 1000 kr. Desuden angives det endelige salg i hvert kvartal og salget for hele året. Se figur 6.1. Række 13 er blot i alt-tallene, som er fremkommet ved at summere tallene fra den pågældende kolonne.

Filtrer I tabellen er der både salgsmedarbejdere fra Vest- og Østdanmark og fra både Food og Non food afdelingerne. Figur 6.2 Valg af funktionen Filtrer.

Libris . d k

63

Hvis du f.eks. ønsker at se nærmere på salget af food i Østdanmark, kan det let gøres ved at benytte funktionen filtrer. Marker cellerne B4 og C4 med overskrifterne ”Afdeling” og ”Type”. Under fanen Startside i gruppen Redigering vælges Sorter og filtrer, og vælg her Filtrer fra rullelisten. Se figur 6.2. Der kommer nu to små pile ved de markerede overskrifter. Se figur 6.3. Figur 6.3 Benyttelse af funktionen Filtrer.

Klik på den lille pil ved ”Afdeling” og sørg for, at der kun er afkrydset ved ”Øst” i den menu der kommer frem. Se figur 6.3. Klik nu på den lille pil ved ”Type” og sørg for, at der kun er afkrydset ved ”Food”. Excel vil nu kun vise de salgsmedarbejdere der kommer fra Food afdelingen i Østdanmark. Se figur 6.4.

Figur 6.4 Filtrering giver det ønskede udvalg.

64

kapitel 6

For at fjerne filtreringen, hvilket betyder at du igen får vist alle medarbejderne, skal du blot endnu en gang under fanen Startside i gruppen Redigering vælge Sorter og filtrer, og herefter igen klikke på Filtrer. Herved er filtreringen ophævet.

Sortering, filtrering og formatering

Statuslinjen

Figur 6.5 Statuslinjen angiver Middelværdi m.m.

Hvis du ønsker at se medarbejdernes gennemsnitlige salg på årsbasis, skal du blot markere cellerne med medarbejdernes årlige salg, altså cellerne I5 til I12. Nede til højre i statuslinjen kan du se middelværdien, som tit benævnes gennemsnittet, antallet og summen. Se figur 6.5. Ved at højreklikke på statuslinjen vil du kunne se egenskaberne for statuslinjen, og du vil også her kunne tilpasse statuslinjen. Se figur 6.6. Du kan sætte eller fjerne afkrydsningerne efter ønske. For eksempel kan du afkrydse Caps Lock, og du vil herefter kunne se om Caps Lock er slået til eller fra, nede i statuslinjen. Her skal du dog være opmærksom på, at det vises ude til venstre på statuslinjen. Figur 6.6 Caps Lock kan tilføjes statuslinjen.

Libris . d k

65

Formatpensel og Betinget formatering For at se på om medarbejderne har klaret sig bedre eller dårligere end budgetteret, skal der tilføjes en kolonne J til tabellen. Skriv overskrift ”Difference mellem salg og budget” i celle J4. I cellen J5 skrives formlen =+I5-D5 Figur 6.7 Valg af Formatpensel.

Kopier denne formel til cellerne J6 til J12 ved at trække i det lille sorte kors, der fremkommer når pilen flyttes til nederste højre hjørne af markeringen af celle J5. Se figur 1.10. Du kan udregne summen af differencerne i celle J13 ved at benytte knappen Autosum. Den er omtalt i kapitel 4. Se figur 4.7. Cellerne i række 4 og 13 var udfyldt med en grå toning, og skriften var fed. Denne formatering er ikke kommet med i cellerne J4 og J13. Men det klares let i Excel ved at benytte formatpenslen. Hvis det kun er en celle der skal formateres, er metoden følgende: Klik på den celle der har den ønskede formatering. F.eks. cellen I4. Under fanen Startside i gruppen Udklipsholder klikkes på Formatpenslen. Se figur 6.7. Klik på den celle som skal have den ønskede formatering. Her er det celle J4. Hvis der er flere celler der skal formateres, som i dette tilfælde, er fremgangsmåden: Klik på den celle der har den ønskede formatering, her er det f.eks. celle I4. Dobbeltklik på Formatpenslen der findes under fanen Startside i gruppen Udklipsholder. Klik på de celler som skal have den ønskede formatering, i dette tilfælde er det cellerne J4 og J13. Læg mærke til at cellerne skifter formatering efterhånden som du klikker på dem. Tryk på ESC når der ikke er flere celler der skal formateres. Skemaet er nu blevet formateret så det ser pænt ud. I kolonne J har du en opgørelse over, om medarbejdernes salg er over eller under det budgetterede.

66

kapitel 6

Sortering, filtrering og formatering

Figur 6.8 Ikonsæt til betinget formatering.

Ved at benytte Betinget formatering kan det visuelt blive meget tydeligt at se, hvem der sælger mere eller mindre end budgetteret. Marker cellerne med differencerne mellem det aktuelle salg og budget. Det vil sige du skal markere cellerne J5 til J12. Under fanen Startside i gruppen Typografier vælges Betinget formatering. Se figur 6.8. Der fremkommer en menu, hvor du skal vælge Ikonsæt. Endnu en menu kommer frem og vælg her Tre pile (farvede). Resultatet vil se ud som på figur 6.9. Ved de medarbejdere der har solgt en del mere end det budgetterede, er der en grøn pil, som peger opad, ved de medarbejdere der har klaret sig nogenlunde som budgetteret, er en vandret gul pil, og ved de medarbejdere der har solgt noget mindre end budgetteret, er der en rød pil, som peger nedad.

Figur 6.9 Betinget formatering angiver tendensen.

Libris . d k

67

Excel har her selv beregnet, hvordan pilene skal være. Metoden kræver en forklaring, som du kan se nedenfor. Det mindste tal er -441, og det største tal er 459. Forskellen mellem disse tal er 900. De tal der ligger i den laveste tredjedel af intervallet fra -441 til 459, markeres med en rød pil nedad. De tal der ligger i den midterste tredjedel, markeres med en vandret gul pil, og de tal der ligger i den højeste tredjedel, markeres med en grøn pil opad. Da du valgte Betinget formatering, og der kom en menu frem, kunne du se, at der er mange andre muligheder end den du benyttede her. Der er endda også mulighed for, at du kan lave din egen regel til Betinget formatering. Figur 6.10 Regler for Betinget formatering.

68

kapitel 6

Sortering, filtrering og formatering

Hvis du i menuen havde valgt Ny regel, havde du fået en dialogboks, hvor du kunne lave dine egne formateringsregler. For illustrationens skyld skal du nu se, hvorledes du kunne have lavet den benyttede formatering under punktet Ny regel. Der er selvfølgelig utallige andre muligheder for at lave regler for betingede formateringer. Grunden til at du skal se på den samme betingede formatering en gang til, bare lavet på en ny måde, er at du herved får en forklaring på, hvorfor Excel markerer dataene med de farvede pile på netop den omtalte måde. Vælg Ny regel, således at dialogboksen på figur 6.10 kommer frem. Vælg Ikonsæt i feltet ud for Formattypografi. Vælg 3 farvede pile i feltet ud for Ikontype. I felterne under Værdi kan du se, at denne regel som standard deler værdierne i intervallet ved 33 % og ved 67 %, som forklaret ovenfor. Her har du så muligheder for at ændre på procenterne, der afgør hvilken type pil et givet tal skal markeres med.

Sortering Måske er du interesseret i at se på de medarbejdere, der har de største salgstal. Du kan nemt få et overblik over, hvem der har solgt mest, ved at sortere medarbejderne efter deres årlige salgstal. Du skal markere hele tabellen på nær rækken med I alt, dvs. du skal markere cellerne A4 til J12. Du skal ikke markere rækken med I alt-tallene, da den ikke skal med i sorteringen. Under fanen Startside i gruppen Redigering vælges Sorter og filtrer. Se figur 6.11. Vælg Brugerdefineret sortering i den lille menu der kommer frem. Der kommer nu en dialogboks til syne. Her skal du afkrydse feltet Dataene har overskrifter. Under kolonnen i feltet ved Sorter efter skal du vælge Hele året 1000 kr. fra listen, og under Rækkefølge vælges Største til mindste fra rullelisten. Se figur 6.12. Til sidst skal du klikke på OK.

Figur 6.11 Valg af Brugerdefineret sortering. Libris . d k

69

Figur 6.12 Regler for Brugerdefineret sortering.

Du har nu fået sorteret medarbejderne efter, hvem der har det største årlige salg. Benyt nu denne metode til at sortere medarbejderne efter hvem der har haft det største årlige budget. Her vil du opdage, at der er to medarbejdere, der har haft det samme årlige budget. Hvem af dem skal nu stå øverst? Dette klarer du ved at tilføje et sorteringsniveau. Du skal igen markere hele tabellen på nær rækken med I alt, du skal altså igen markere cellerne A4 til J12. Under fanen Startside i gruppen Redigering vælges igen Sorter og filtrer. Se figur 6.11. Vælg igen Brugerdefineret sortering i den lille menu der kommer frem. Afkryds feltet Dataene har overskrifter, hvis ikke Excel allerede har gjort det. Under kolonnen i feltet ved Sorter efter skal du nu vælge Budget 1000 kr. fra listen, og under Rækkefølge vælges Største til mindste fra rullelisten. Klik nu på Tilføj niveau. Der tilføjes nu en linje der starter med Og derefter. I feltet ud for Og derefter vælger du Hele året 1000 kr. og under Rækkefølge vælger du igen Største til mindste. Se figur 6.13. Til sidst skal du igen klikke på OK.

70

kapitel 6

Sortering, filtrering og formatering

Tabellen er nu sorteret efter budgettallene, og der hvor budgettallene er ens, er sorteringen derefter sket efter det årlige salg. Du bør bemærke, at det ikke er nok at markere de kolonner du ønsker at sortere efter. Det ville godt nok bevirke at disse kolonner blev sorteret, men navnene og alle de øvrige tal ville ikke følge med. Så det ville altså ikke længere være de rigtige tal, der så stod ud for navnene.

Figur 6.13 Tilføjelse af niveau ved sortering.

Diagrammer Du skal nu lave et diagram over medarbejdernes budgetter og salgstal. Det er utrolig nemt at få lavet et diagram, da Excel har en funktion der laver diagrammerne næsten automatisk. Når diagrammet er lavet, skal du tilpasse det, så det bliver helt nøjagtigt som du vil have det. Først skal du sortere medarbejderne i den rækkefølge du ønsker dem nævnt i. Det kan f.eks. være i alfabetisk rækkefølge. Se det netop gennemgåede afsnit Sortering om hvorledes dette gøres. Marker hele tabellen på nær rækken med I alttallene. Under fanen Indsæt i gruppen Diagrammer vælges Søjle. Se figur 6.14.

Libris . d k

71

Figur 6.14 Valg af søjlediagram.

Under 2D-søjlediagram vælges Grupperet søjle, som er den første mulighed. Excel laver nu automatisk et diagram, sådan som Excel tror du vil have det, men ofte vil det være nødvendigt at tilpasse diagrammet, så det bliver helt perfekt. I figur 6.15 nedenfor kan du se diagrammet som Excel laver automatisk. Som du kan se, skal der arbejdes lidt med diagrammet for at det kan fremstå let overskueligt. Det er heldigvis let at foretage ændringer i diagrammet og tilpasse det, så det bliver lige som du ønsker. Højreklik på diagrammet. I den menu der dukker op vælges Vælg data, og der kommer nu en dialogboks frem.

Figur 6.15 Søjle­ diagram automatisk fremstillet af Excel.

72

kapitel 6

Sortering, filtrering og formatering

Figur 6.16 Valg af datakilder til diagrammet.

Se figur 6.16. I denne dialogboks skal du lave nogle tilpasninger. I vinduet Forklarende tekst (Serie) skal 1. kvartal 1000 kr. fjernes. Det gøres ved at markere 1. kvartal 1000 kr. og derefter klikke på Fjern. Herefter fjernes 2. kvartal, 3. kvartal, 4. kvartal og Difference mellem salg og budget på samme måde som beskrevet ovenfor. Du kan hele tiden følge med i, hvorledes diagrammet ændrer sig. I vinduet Vandrette (Kategori) akseetiketter vælges Rediger. Marker nu navnene, dvs. cellerne A5 til A12 for at definere Aksens etiketområde. Se figur 6.17. Klik på OK. Figur 6.17 Ændring af vandrette akseetiketter.

Klik endnu en gang på OK, og du kan nu se hvorledes diagrammet har ændret sig. Se figur 6.18. Nu ser diagrammet helt fornuftigt ud, og det kan let kopieres over i en rapport eller i en PowerPoint-præsentation. I Excel er der utrolig mange måder, hvorpå du kan ændre diagrammets udseende. Den bedste måde at lære mulighederne at kende på, er at prøve sig frem. Libris . d k

73

Figur 6.18 Ændret og mere overskueligt søjlediagram.

Men som du har set i det ovenstående, er det utroligt let at få Excel til at lave et ganske overskueligt og præsentabelt diagram.

Pivottabel Pivottabeller er allerede blevet bekrevet i afsnittet Pivottabel i kapitel 1. Men da Pivottabeller er utroligt brugbare i mange situationer, får du det at se en gang til. Pivottabeller bruges når en mængde data skal grupperes og sammentælles. Det kan f.eks. være som her, hvor man gerne vil gruppere salgstallene efter afdeling, og sammentælle salgstallene for hver enkelt afdeling. Der vil være mange andre situationer, hvor pivottabeller kan bruges. Du skal i det følgende se på pivottabeller og hvordan de fremstilles. Den datamængde der her er til rådighed, er for lille til, at det helt klart vil fremgå, hvor stor en arbejdsbesparelse der kan ligge i pivottabeller. Der gøres dog opmærksom på, at metoden er den samme uanset, om der er tale om en lille eller stor datamængde. Desuden er det også lige let at fremstille pivottabeller uanset størrelsen på datamængden. I det følgende kan du derfor med fordel forestille dig, at metoden bruges på en større datamængde. Når du har læst

74

kapitel 6

Sortering, filtrering og formatering

afsnittet, vil du forhåbentlig kunne se fordelene ved pivottabeller. I dette afsnit vil du lære at lave almindelige pivottabeller, erfare hvor simpelt det er at lave dem, og forhåbentlig se hvor arbejdsbesparende de kan være i mange situationer. Emnet pivottabeller er imidlertid stort, og der findes bøger, der kun beskæftiger sig med dette emne. Marker tabellen med data på nær rækken med I alt (ligesom du har gjort tidligere). Under fanen Indsæt i gruppen Tabeller vælges Pivottabel. Se figur 1.12. Der fremkommer en dialogboks ved navn Opret pivottabel, som er udfyldt af Excel. Du kan derfor blot klikke OK. I dialogboksen kunne du se at feltet ved Tabel/område er udfyldt med det ønskede område A4:J12. Det skyldes at du startede med at markere dette område. Desuden er det valgt, at pivottabellen skal placeres i et nyt regneark. Det betyder at der kommer et nyt regneark med sin egen arkfane i projektmappen. Til venstre i det nye regneark er der et område med overskriften Pivottabel1. Dette område skal du se nærmere på om lidt. Til højre i regnearket er der en dialogboks ved navn Feltliste i pivottabel. Felterne i det øverste vindue er overskrifterne fra tabellen. Med musen hives nu overskriften Afdeling ned i vinduet Rækkenavne og overskriften Hele året 1000 kr. hives ned i vinduet Værdier. Titlen ændrer sig til Sum af Hele å… Se figur 6.19. Hvis titlen er ændret til noget andet, f.eks. til Antal af Hele å… kan det let ændres. Så skal du klikke på den lille pil til højre for Antal af Hele å…, vælge Værdifeltindstillinger, vælge Sum og klikke på OK. Dette er beskrevet i kapitel 1. Du kan til venstre i regnearket se hvorledes ændringerne i pivottabellen bliver. Se figur 6.20.

Libris . d k

75

Figur 6.19 Valg af felter til pivottabellen.

Figur 6.20 Oversigt over salg i afdeling‑ erne.

Inden emnet om pivottabeller forlades, er der lige et par ting til du skal prøve.

76

kapitel 6

Sortering, filtrering og formatering

Vend tilbage til Feltliste i pivottabel. Hvis den er forsvundet, får du den frem igen ved at højreklikke på pivottabellen og vælge Vis Feltliste på den liste der kommer frem. Fra listen i det øverste vindue skal du med musen hive overskriften Budget 1000 kr. ned i vinduet Værdier. Her kommer så til at stå Sum af Budge… Hvis der kommer til at stå Antal af Budge…, så skal det ændres under Værdifeltindstillinger som beskrevet i det netop gennemgåede afsnit. Bemærk at pivottabellen er blevet udvidet med endnu en kolonne. Fra listen i det øverste vindue skal du nu hive overskriften Type ned i vinduet Rækkenavne. Pivottabellen bliver igen udvidet, således at der nu er en opgørelse over afdelingerne Vest og Øst med en underopdeling i Food og Non food. Se figur 6.21. I vinduet Rækkenavne er der nu to overskrifter, nemlig Afdeling og Type. Prøv ved hjælp af musen at bytte om på rækkefølgen af de to overskrifter. Pivottabellen vil nu lave en opgørelse over typen Food og Non food med en underopdeling i Vest og Øst. Figur 6.21 Oversigt over salg i afde­ling‑ er med en under­ opdeling i Food og Non food.

Libris . d k

77

7. 0

Datoformler og tekstformler

7.0

Datoformler og tekstformler Aldersberegning I dette kapitel tager vi igen udgangspunkt i firmaet Hurtigt og Nemt A/S. Denne gang skal du se på medarbejdernes alder. Ud fra medarbejdernes cpr-numre skal du beregne deres alder. En medarbejders alder skal f.eks. bruges når det skal afgøres om en medarbejder er omfattet af en seniorordning eller en ungdomsordning. Det lyder nemt, for du kan umiddelbart se deres fødselsdag i cpr-numret, men det kræver så lidt hovedregning at finde ud af, hvor mange år, måneder og dage medarbejderne er. Det kan Excel let klare. Medarbejdernes aldre ændrer sig jo også hele tiden, så det er hensigtsmæssigt at det kan klares automatisk af Excel. I kapitlet vil der blive brugt funktionerne • • • •

DATO.FORSKEL NU VENSTRE MIDT

Desuden vil kapitlet omhandle • •

80

kapitel 7

Operatoren & Absolutte referencer og relative referencer

Datoformler og tekstformler

Liste med cpr-numre over medarbejdere

Figur 7.1 Oversigt over medarbejdernes personnumre.

Først skal du se på salgsmedarbejdernes aldre. I Excel laver du en liste med medarbejdernes navne og cpr-numre. Se figur 7.1. En sådan liste vil det ofte være muligt at få fra en database som allerede eksisterer i firmaet. Det kan f.eks. være, at det er muligt at lave en eksport fra lønprogrammet til Excel med information over medarbejdernes navne og cpr-numre.

Fødselsdag ud fra cpr-nummer For at få fødselsdagen ud fra et cpr-nummer kommer du til at bruge nogle funktioner til behandling af tekst. Du kommer til at benytte VENSTRE, MIDT og operatoren &. Funktionen VENSTRE kan du læse mere om i kapitel 1. Du skal først have trukket medarbejdernes fødselsdag ud af cpr-nummeret. Lav overskriften ”Fødselsdag” i celle C4.

Libris . d k

81

Lav følgende formel i celle C5: =VENSTRE(B5;2)&””&MIDT(B5;3;2)&”-”&MIDT(B5;5;2) Kopier denne formel til cellerne fra C6 til C12. Du har nu en liste over medarbejdernes fødselsdage. Formlen du har benyttet, er i virkeligheden sammensat af flere komponenter, og det kræver en forklaring. Formlen =VENSTRE(B5;2)&”-”&MIDT(B5;3;2)&””&MIDT(B5;5;2) består af VENSTRE, MIDT og &. Funktionen VENSTRE(B5;2), som også er nævnt i kapitel 1, tager de to (på grund af 2-tallet) tegn fra celle B5 der står længst til venstre. I dette tilfælde bliver det månedsdagen for fødselsdagen. Funktionen VENSTRE kan findes under fanen Formler i gruppen Funktionsbibliotek. Her skal du vælge Tekst, og der fremkommer en menu. Se figur 1.8. I denne menu kan vælges VENSTRE. Da der i dette tilfælde var brug for flere funktioner i samme celle, er det lettere blot at skrive formlen. Der findes også en funktion HØJRE, der fungerer ligesom VENSTRE, dog tages det ønskede antal tegn fra højre side. Funktionen MIDT(B5;3;2) tager ligeledes to tegn (på grund af 2-tallet) fra celle B5, men denne gang er det tegn nummer tre (på grund af 3-tallet) og det efterfølgende tegn. Det bliver altså tegn nummer tre og fire. I dette tilfælde bliver det måneden for fødselsdagen. MIDT benyttes på tilsvarende måde for at få fødselsåret. MIDT kan ligeledes findes under fanen Formler i gruppen Funktionsbibliotek. Du skal igen vælge Tekst, og i menuen kan du vælge funktionen MIDT. Se figur 1.8.

Operatoren & som også er med fire gange i formlen =VENSTRE(B5;2) &”-”&MIDT(B5;3;2)&””&MIDT(B5;5;2) kan sammenkæde tekster.

82

kapitel 7

Datoformler og tekstformler

Først får du månedsdagen (VENSTRE(B5;2)) derefter tegnet ”-” efterfulgt af måneden (MIDT(B5;3;2)), igen efterfulgt af tegnet ”-” og til sidst efterfulgt af fødselsåret (MIDT(B5;5;2)). Anførelsestegnene kommer ikke med, men Excel forlanger at de skal omslutte de tegn du ønsker at sammenkæde. Alt dette giver dig således følgende tabel.

Alder ud fra fødselsdagen

Figur 7.2 Oversigt over medarbejdernes fødselsdage.

For at kunne udregne alderen ud fra fødselsdagen benyttes Excel-funktionen DATO.FORSKEL. Denne funktion findes underligt nok ikke i gruppen Funktionsbibliotek under fanen Formler. Ud fra fødselsdagen skal du beregne alderen. Den ændrer sig imidlertid hele tiden, da vi jo bliver en dag ældre for hver dag der går. Så først skal du fortælle Excel datoen for i dag. Skriv ”Dato” i celle A2. Skriv i celle B2 formlen =NU() Excel skriver dags dato i celle B2. Funktionen NU kan findes under fanen Formler i gruppen Funktionsbibliotek. Libris . d k

83

Her vælges Dato og klokkeslæt, og i den menu der fremkommer, vælges funktionen NU. Ved at beregne forskellen mellem dags dato og fødselsdagen får du alderen. Skriv overskrifterne ”Alder i år”, ”Måneder”, og ”Dage” i cellerne D4, E4 og F4. I celle D5 skriver du formlen =DATO. FORSKEL(C5;$B$2;”y”) I celle E5 skriver du formlen =DATO. FORSKEL(C5;$B$2;”ym”) I celle F5 skriver du formlen =DATO. FORSKEL(C5;$B$2;”md”) Excel skriver i de 3 celler medarbejderens alder i antallet af år, antallet af måneder og antallet af dage. Resultaterne kan du se i kolonnerne D, E og F i figur 7.7. I de tre celler blev funktionen DATO.FORSKEL benyttet. Formlen udregner forskellen mellem datoerne der står i celle C5 og celle B2. Det sidste argument angiver hvorledes resultatet skal skrives. Når argumentet er ”y” angiver Excel antallet af hele år (year), der er forskellen mellem de to datoer. Er argumentet ”ym” angiver Excel antallet af hele måneder, der er forskellen mellem de to datoer. Dog medtages ikke hele år, dvs. du får et tal mellem 0 og 11. Det sidste argument der blev benyttet, er ”md”, her angiver Excel antallet af hele dage, der er forskellen mellem de to datoer. Her medtages ikke hele måneder, dvs. du får et tal mellem 0 og 30. De tre formler kan samles til en lang formel ved at benytte operatoren & som blev omtalt ovenfor. Formlen kommer så til at få følgende udseende (der er tale om én lang formel): =DATO.FORSKEL(C5;$B$2;”y”)&” år ”&DATO. FORSKEL(C5;$B$2;”ym”)&” måneder og ”&DATO. FORSKEL(C5;$B$2;”md”)&” dage” Resultaterne kan du se i kolonne G i figur 7.7.

84

kapitel 7

Datoformler og tekstformler

Figur 7.3 Relative cellereferencer.

Figur 7.4 Absolutte referencer.

Absolutte og relative referencer

Absolutte og relative referencer har stor betydning når du skal kopiere formler fra en celle til andre celler. Excel benytter som udgangspunkt relative referencer. For at forklare dette skal du se på 4 eksempler. Relative cellereferencer

I celle B2 står referencen A1. Denne kopieres til celle C4, altså en kolonne til højre og to rækker ned. Referencen bliver hermed ændret til B3. Kolonnereferencen øges med én fra A til B, og rækkereferencen øges med to fra 1 til 3. Se figur 7.3. Absolutte referencer

I celle B2 står nu referencen $A$1. Denne kopieres igen til celle C4, altså igen en kolonne til højre og to rækker ned. Referencen $A$1 er nu gjort absolut både hvad angår kolonne og række, idet der er placeret et $-tegn foran både kolonne- og rækkereferencen. Når formlen kopieres, vil formlen ikke blive ændret. Se figur 7.4. Relativ kolonnereference og absolut rækkereference

I celle B2 står referencen A$1. Denne kopieres endnu en gang til celle C4, altså atter en kolonne til højre og to rækker ned. Referencen A$1 er nu gjort absolut hvad angår rækken men ikke hvad angår kolonnen, idet der er placeret et $-tegn foran rækkereferencen. Når formlen kopieres, vil rækkereferencen ikke blive ændret, men kolonnereferencen øges med én fra A til B. Se figur 7.5. Libris . d k

85

Figur 7.5 Relativ kolonne- og absolut rækkereference.

Absolut kolonnereference og relativ rækkereference

Dette eksempel minder om det foregående, dog er kolonnereferencen nu gjort absolut i stedet for rækkereferencen. I celle B2 står referencen $A1. Denne kopieres som sædvanligt til celle C4, altså som bekendt en kolonne til højre og to rækker ned. Referencen $A1 er nu gjort absolut hvad angår kolonnen, men ikke hvad angår rækken, idet der er placeret et $-tegn foran kolonnereferencen. Når formlen kopieres, vil kolonnereferencen ikke blive ændret, men rækkereferencen øges med to fra 1 til 3. Se figur 7.6. Figur 7.6 Absolut kolonne- og relativ rækkereference.

Vend nu tilbage til aldersberegningen. I formlen i celle C5: =DATO.FORSKEL(C5;$B$2;”y”) er referencen $B$2 til celle B2 gjort absolut. Det betyder, at når formlen kopieres til de efterfølgende rækker, så vil referencen til celle B2 ikke blive ændret, hvilket den heller ikke skal, da dags dato jo bliver stående i celle B2.

86

kapitel 7

Datoformler og tekstformler

Figur 7.7 Oversigt over medarbejdernes alder.

Referencen til celle C5 bliver derimod ændret til C6, C7 osv. når formlen kopieres til de efterfølgende rækker. Der beregnes således hele tiden datoforskellen mellem fødselsdagen, der står i samme række som formlen, og dags dato som hele tiden står i celle B2. Formlerne kopieres nu til rækkerne fra 6 til 12, og resultatet bliver som på figur 7.7. Du har nu en liste over medarbejdernes aldre, som kan benyttes til at se på hvornår der skal fejres runde fødselsdage, eller hvilke medarbejdere der skal have seniordage eller lignende.

Libris . d k

87

8.0

Flere tekst­funktioner

8.0

Flere tekst­funktioner Find fornavn og efternavn Du skal nu se på listen over medarbejdernes navne. Listen med medarbejdernes navne indeholder både deres fornavne og efternavne, og de skal opsplittes således, at der kommer to kolonner, en med fornavnene og en med efternavnene. Det vil så herefter være muligt at sortere medarbejderne efter efternavn, altså efter kolonnen med efternavnene. I kapitlet vil der blive brugt funktionerne

Figur 8.1 Oversigt over medarbejdernes navne.

90

kapitel 8

• • • • • •

LÆNGDE UDSKIFT FIND VENSTRE MIDT HØJRE

Flere tekstfunktioner

Listen med medarbejderne kender du fra tidligere. Se figur 8.1. Inden du for alvor skal i gang med formlerne for at opdele navne i fornavn og efternavn, skal du se på nogle simple eksempler. I figur 8.2 kan du se de omtalte formler og det resultat der fremkommer. I alle eksemplerne benyttes celle A5 med teksten: Anders Andersen. LÆNGDE

Formlen LÆNGDE(A5) angiver længden dvs. antallet af tegn i teksten i celle A5. Anders Andersen består af 15 tegn, husk at mellemrummet tælles med. FIND

Formlen FIND(”er”;A5) finder starten af den første placering af bogstavkombinationen ”er” i teksten i celle A5. Første gang ”er” forekommer i teksten Anders Andersen, er som tegn nummer 4 og 5, altså starter det ved tegn nummer 4. Er det tredje argument angivet som f.eks. 6, betyder det at formlen først begynder at lede fra tegn nummer 6. Første gang ”er” forekommer i teksten Anders Andersen, når der startes ved tegn 6, er som tegn nummer 11 og 12, altså bliver resultatet af formlen 11. (Husk at tælle mellemrummet med.)

Figur 8.2 Oversigt over nogle tekstfunktioner.

Libris . d k

91

Figur 8.3 Menu til valg af tekstfunktioner.

UDSKIFT

Formlen UDSKIFT(A5;”Ander”;”Han”) udskifter i cellen A5 den første tekst ”Ander” med den anden tekst ”Han”. Der skal anførelsestegn om både teksten der udskiftes og teksten der indsættes. I dette tilfælde vil Bogstaverne ”Ander” blive erstattet af bogstaverne ”Han” på alle de steder hvor ”Ander” forekommer. Der kommer så til at stå Hans Hansen. Tilføjes et fjerde argument, som her er 2 så formlen bliver UDSKIFT(A5;”Ander”;”Han”;2), vil ”Ander” kun bliver udskiftet med ”Han” ved den anden forekomst af ”Ander”. Resultat bliver så Anders Hansen. HØJRE

Formlen HØJRE(A5;3) angiver de 3 tegn stående længst til højre i celle A5, hvilket i dette tilfælde er bogstaverne ”sen”. VENSTRE og MIDT

De to funktioner fungerer næsten som funktionlen HØJRE og er beskrevet tidligere i kapitel 1 og kapitel 8. Alle de nævnte funktioner kan findes under fanen Formler i gruppen Funktionsbibliotek under menuen Tekst. Se figur 8.3. Det var de funktioner der skal bruges for at få adskilt fornavne fra efternavnet. Formlerne skal dog sammensættes til nogle længere formler, og du skal selvfølgelig nok få en uddybende forklaring. Lav først overskrifterne ”Fornavne” og ”Efternavn” i cellerne B4 og C4. Se figur 8.4.

92

kapitel 8

Flere tekstfunktioner

Figur 8.4 Oversigt over medarbejdernes for- og efternavne.

I celle B5 skal du skrive formlen =VENSTRE(A5; FIND(”*”;UDSKIFT(A5;” ”;”*”;LÆNGDE(A5)LÆNGDE(UDSKIFT(A5;” ”;””))))-1) I celle C5 skal du skrive formlen =HØJRE(A5; LÆNGDE(A5)-FIND(”*”;UDSKIFT(A5;” ”;”*”;LÆNGDE(A5)-LÆNGDE(UDSKIFT(A5;” ”;””))))) Kopier formlerne til rækkerne 6 til 12. Resultatet bliver som du kan se på figur 8.4. Så let var det. Men formlerne kræver en nærmere forklaring, og den får du i mindre dele. Funktionen af de farvede dele af formlen forklares nedenfor den farvede markering. Først skal du se på formlen der giver fornavnene. I det følgende skal du se på celle A6 med teksten Bent B. Berthelsen (med to fornavne (Bent og B.) og derfor to mellemrum), da det vil give en forståelse af forklaringerne. =VENSTRE(A6; FIND(”*”;UDSKIFT(A6;” ”;”*”; LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))-1) Her udskiftes mellemrum med ingenting, altså fjernes alle mellemrummene i teksten i celle A6. Resultat: BentB.Berthelsen =VENSTRE(A6; FIND(”*”;UDSKIFT(A6;” ”;”*”; LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))-1) Her findes længden (antallet af tegn) af teksten i celle A6 når mellemrummene er fjernet. Resultat: 16 Libris . d k

93

=VENSTRE(A6; FIND(”*”;UDSKIFT(A6;” ”;”*”; LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))-1) Angiver længden af teksten i celle A6 med mellemrummene. Resultat: 18 =VENSTRE(A6; FIND(”*”;UDSKIFT(A6;” ”;”*”; LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))-1) Differencen mellem de to længder angiver antallet af mellemrum i teksten i celle A6. Resultat: 2 =VENSTRE(A6; FIND(”*”;UDSKIFT(A6;” ”;”*”; LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))-1) Udskifter det sidste/andet mellemrum med en *. Resultat: Bent B.*Berthelsen =VENSTRE(A6; FIND(”*”;UDSKIFT(A6;” ”;”*”; LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))-1) Finder nummeret på den plads hvor * står. Altså nummeret på den plads, hvor det sidste mellemrum var placeret, inden det blev udskiftet med *. Resultat: 8 =VENSTRE(A6; FIND(”*”;UDSKIFT(A6;” ”;”*”; LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))-1) Angiver tegnene fra venstre i celle A6, hvor antallet af tegn er det samme som nummeret på pladsen for * minus 1. Der trækkes 1 fra da * ikke skal med. Resultatet er teksten i celle A6 fra venstre og indtil sidste mellemrum, altså fås netop alle fornavnene. Resultat: Bent B. Formlen for efternavnet minder lidt om den formel du lige har set på, og her kommer forklaringen.

94

kapitel 8

Flere tekstfunktioner

=HØJRE(A6; LÆNGDE(A6)-FIND(”*”;UDSKIFT(A6;” ”;”*”;LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))) Finder ligesom ovenfor nummeret på den plads hvor * står. Altså nummeret på den plads, hvor det sidste mellemrum var placeret inden det blev udskiftet med *. Resultat: 8 =HØJRE(A6; LÆNGDE(A6)-FIND(”*”;UDSKIFT(A6;” ”;”*”;LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))) Angiver længden af teksten i celle A6, husk at mellemrummene medtælles. Resultat: 18 =HØJRE(A6; LÆNGDE(A6)-FIND(”*”;UDSKIFT(A6;” ”;”*”;LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))) Angiver antallet af tegn der befinder sig efter *, altså antallet af tegn der befinder sig efter det sidste mellemrum inden det blev udskiftet. Dette er netop antallet af tegn, som der er i efternavnet. Resultat: 10 =HØJRE(A6; LÆNGDE(A6)-FIND(”*”;UDSKIFT(A6;” ”;”*”;LÆNGDE(A6)-LÆNGDE(UDSKIFT(A6;” ”;””))))) Angiver tegnene fra højre i celle A6, hvor antallet af tegn netop er antallet af tegn som efternavnet rummer. Resultatet bliver derfor netop efternavnet. Resultat: Berthelsen Dette var et eksempel på, hvorledes simple formler kan sammensættes til en noget længere og mere kompliceret formel. Hvis alle navnene havde bestået af netop et fornavn og et efternavn, kunne formlerne have været noget simplere, men ofte vil der være personer med flere fornavne som i dette firma.

Libris . d k

95

0.2

Stikordsregister & 82 Absolutte reference 85 Autosum 25, 46 Begrænsning 49, 58 Bibetingelse; Se begrænsning Ciffertegn 11, 24 Cirkeldiagram 18 Dato.forskel 83, 84 Diagram 29, 30, 32, 71, 73 Eksponentialfunktion 33, 34 Erstat 24 Filtrer 63 Find 91 Formatpensel 66 Højre 82, 92 Import 8, 22 Kolonnebredde; Se ciffertegn Lagkagediagram; Se cirkeldiagram Ligning 54, 56 Længde 91 Midt 81, 82, 92 Målsøgning 35, 54, 56 Nu 83 Pivotdiagram 17 Pivottabel 14, 74 Problemløser 41, 48, 57 Punktdiagram 29 R-kvadreret 31, 33 Sorter 64, 69 Statuslinjen 65 Sumprodukt 46, 57 Tendenslinje 29, 31, 32 Tilføjelsesprogram 41 Udskift 91 Venstre 12, 81, 92

96

kapitel 0.2

Stikordsregister