Microsoft Excel
Avancerade funktioner
Avancerade funktioner
OM()
Returnerar ett värde om ett angivet villkor beräknas till SANT och ett annat värde om det beräknas till FALSKT.
=OM(A6>A5;C9;OM(A6>A4;C10;OM(A6>A3;C11;”Falskt”))) A6>A5 = Påstående som testas
C9 = Cell med det värde som skall returneras om påståendet stämmer
OM… = Kapslad sats för att påbörja ett nytt test inom funktionen (7 kapslingar möjliga)
”Falskt” = Textvärde som skall returneras som inget påstående stämmer.
MÅNAD(), DAG(), ÅR()
Funktioner för att ur en cell med ett datum bryta ut år månad och dag.
Exempel: Cell B4 innehåller datumet 2005-12-10
=ÅR(B4) resultat 2005, =MÅNAD(B4) resultat 12, DAG(B4) resultat 10 TEXT()
Hämtar ett värde ur en cell och skriver in det som textvärde i formelcellen. Med hjälp av denna funktion kan man sätta samman data från flera celler med ett &-tecken.
Exempel: Cell B4 innehåller cellvärdet November, Cell C4 innehåller värdet 2005.
=TEXT(B4;0)& ” ”&TEXT(C4;0) ger resultatet November 2005 som text.
RAD(), KOLUMN()
Returnerar en rad eller kolumns ordningsnummer
=RAD(C5)-3
C5 = Den cell vars radnummer skall returneras -3 = Avräkning av celler som ej skall medräknas LETARAD(), LETAKOLUMN()
Söker efter ett värde i kolumnen längst till vänster eller översta raden i en tabell och returnerar sedan ett värde i samma rad eller kolumn som du anger i tabellen.
=LETARAD(A4;C13:F47;3)
A4 = Den cell vilkens värde skall letas upp C13:F47 = Det cellområde där tabellen finns
3 = Det antal kolumn eller radsteg ifrån upphittat värde som skall returneras Statistiska funktioner
MIN, MAX
Söker efter och returnerar det minsta eller största värdet från ett cellområde.
Exempel: =MAX(A1:B6) returneras det största värdet, alltså 64.
Detta kan naturligtvis kombineras med andra funktioner ovan för att ta fram data i olika former.
A B
1 15 64 2 24 19 3 18 32
Andra nyttiga funktioner
RUNDA.UPP/RUNDA.NED
Avrundar ett värde uppåt eller nedåt till den signifikans (ental, tiotal, hundratal, decimaler) argumentet anger.
Ex. RUNDA.UPP(B2;10)
Om B2 innehåller värdet 4 avrundas uppåt till närmsta tiotal och ger resultatet 10.
Ex. RUNDA.NED(B2;0,5)
Om B2 innehåller 4,77 avrundas neråt till närmsta halvtal och ger resultatet 4,5 HELTAL
Heltal är en funktion som omvandlar ett decimaltal till heltal Ex. HELTAL(B4)
Avancerade programfunktioner
Villkorsstyrd formatering
När värden som på ett eller annat sätt faller ur ramen i en kalkylbladsuppställning skall tydliggöras finns möjlighet att med hjälp av villkorsstyrd formatering tillämpa ett format på t.ex. alla värden under noll.
Markera det cellområde där värden finns som ev. skall formateras.
Klicka på formatmenyn och kommandot ”Villkorsstyrd formatering”.
Ange villkor i dialogrutan och klicka på knappen formatera
Ställ in önskat cellformat och klicka OK i bägge dialogrutorna Aktuellt format appliceras nu på alla celler i området där villkoret är SANT.
Verifiering
Via verifiering i ”Data”menyn kan möjligheten att mata in värden i Excel begränsas. Detta för att inte få in felaktiga värden i tabellen.
På första fliken inställningar anges vilka värden de markerade cellerna skall acceptera.
Fliken Indatameddelande används för att ge användaren information om vad som får skrivas i cellen.
Fliken Felmeddelande används för att ge information om användaren ändå matar in ogiltiga data.
Arbeta med Formulärkontroller
Verktygsfältet ”formulär” i Excel ger oss tillgång till ett antal automationsobjekt som kan placeras i kalkylbladen. Textrutor, Listrutor, kommandoknappar och
alternativknappar kan vara mycket användbara men kräver att kalkylbladsfunktionerna konstrueras med eftertanke.
Eftersom dessa kontroller oftast bara ”orkar” returnera sitt ordningsnummer måste vi ta hand om resultaten på något sätt via andra befintliga funktioner.
Etikett
Används endast som beskrivning till andra element i kalkylbladet.
Gruppruta
Används för att hålla samman ex. alternativknappar i ett område Kommandoknapp
Kopplas till makron för att kunna köra kod från kalkylbladet Kryssruta
Returnerar ett booleskt värde, bockad = SANT, tom = FALSKT Alternativknapp
Ger möjlighet att välja från ett antal alternativ. Endast ett val är möjligt. Alternativets ordningsnummer returneras
Listruta
Ger möjlighet att välja från ett stort antal alternativ i fönster med rullningslist.
Alternativets ordningsnummer returneras Kombinationsruta
Ger möjlighet att välja från ett stort antal alternativ i en ”Dropmeny”. Alternativets ordningsnummer returneras
Kontrollernas egenskaper
Var kontrollerna skall hämta sin information eller vart dom skall skicka utdata anges i kontrollernas egenskaper. Dessa öppnas via ett högerklick på kontrollen följt av
”formatera kontroll” i menyn.
Indataområde är det område i ett kalkylblad där valbara data hämtas!
Cellänk är den cell dit resultatet av aktuellt val skickas!
Såväl indataområde som cellänk kan referera till andra kalkylblad i arbetsboken.
När kontrollerna är i funktion och skickar sitt värde till målcellen (”cellänk”) används excelfunktionerna ovan för att ta hand om och ”göra någonting av” dessa värden.
VBA-Kontroller
Verktygsfältet ”Kontroller” ger oss tillgång till ett antal kontroller som i första anblick är identiska med formulärkontrollerna. Vid en närmare granskning märker vi dock att dessa skiljer sig på många sätt från dessa.
VBA-kontrollerna kräver mer kunskap och är omständigare att länka till
cellreferenser. Å andra sidan kan dessa kontroller namnges och därmed också kopplas till kod. Dessutom har dessa kontroller många fler egenskaper och inställningsmöjligheter.
Här visas egenskapsfönstret för en kombinationsruta med alla egenskapsinställningar.
Lägg märke till egenskaperna LinkedCell och
ListFillRange som alltså motsvarar egenskaperna för en formulärkontroll.
Här måste alltså informationen om cellreferenser skrivas in. Dessa kan alltså ej markeras som i en kombinationsruta från formulärverktygsfältet.
Namnge VBA-kontroller
För att kunna hålla reda på de olika kontrollerna bör vi frångå den automatiska namngivningen som bara tilldelar respektive kontroll ett ordningsnummer.
Om man skall följa gängse regler tilldelas kontrollen först en prefix utifrån vilken typ av kontroll det är och sedan ett beskrivande namn som talar om vad den gör.
Exempel: ”txtSkattesats” – en textruta där man skall skriva in skattesatsen, CboSaljare – en kombinationruta där vi väljer säljare, CmdFakturatotal – en
kommandoknapp som kör en procedur som beräknar en fakturatotal.
Makron
Makron är små programfunktioner som oftast lagras i den arbetsbok där dom skall finnas tillgängliga. Alla funktioner i Excel kan sammanföras i en sammanhängande programsats som sedan körs via ett kortkommando, en kommandoknapp eller en knapp i ett verktygsfält eller meny. Man behöver dock inte ett uns av
programmeringskunskaper då makroinspelaren kan lagra programsekvenser utifrån dom musklickningar och tangentbordstryckningar vi gör i programmet.
1. Klicka på ”Spela in nytt makro” i menyalternativet Makro i verktygsmenyn.
2. Namnge Makrot (använd ej å, ä, ö eller reserverade tecken) Klicka OK!
Placera makrot i ”den här arbetsboken” (Arbetsboken egna makron laddas när Excel startas vilket gör att makron som placeras här alltid är tillgängliga
medans makron som placeras i den här arbetsboken bara blir tillgängliga där 3. Ett nytt verktygsfält med bara två knappar finns tillgängligt när dialogrutan
stängs.
Gör exakt de funktioner med muspekare och tangentbord som makrot sedan skall utföra. Anteckna gärna klickningar steg för steg innan start så undviks fel- klickningar och justeringar som naturligtvis också spelas in och blir en del av den nya funktionen.
4. Klicka på knappen stoppa inspelning i det fristående lilla fristående verktygsfältet!
5. Funktionen är nu lagrad och kan anropas på något av ovanstående sätt.
Koppla makron till verktygsfält och menyer…
1. Högerklicka på ett valfritt verktygsfält och välj anpassa.
2. Välj den mittersta fliken kommandon 3. Verktygsfält och menyer är i detta läge
redigeringsbara och kan ändras utifrån våra specifika behov. Klicka på
kategorin makron och dra den ”knapp”
som visas i det högra fönstret i dialogrutan till en valfri plats i ett verktygsfält eller en meny.
Högerklicka sedan på den nya knappen och välj koppla makro till knapp.
4. Välj det makro knappen skall köra och klicka OK!
5. Stäng dialogrutan anpassa och kolla om
knappen utför de instruktioner som spelades in i makrot.
Anpassa verktygsfält
När dialogrutan anpassa är öppnad är verktygsfälten i programmet redigeringsbara.
Det innebär att knappar som ej används kan plockas bort och andra funktioner från fliken kommandon kan placeras i befintliga verktygsrader genom dragochsläpp- metoden.
Hur mycket verktygsfälten än ändras kan man alltid enkelt ,på fliken verktygsfält, återställa dessa till sitt grundutseende. På denna flik kan även egna verktygsfält skapas som innehåller våra specifika funktioner – kanske skapade med makron.
Mallar
En excelmall är en arbetsbok eller ett kalkylblad som ligger till grund för nya
arbetsböcker där information specifik för mallen redan finns förarbetad.
Att spara en excelfil som en mall är en enkel
process. Filen sparas som vanligt med den
skillnaden att filformatet mall väljs. Programmet öppnar själv rätt mapp i filsystemet. Ändra ej den föreslagna sökvägen då denna gör att en ny arbetsbok som baseras på den nya mallen enkelt
kan skapas via Nytt… från arkivmenyn.
Excel VBA
VBA (Visual Basic för Applications) är ett bantat programmeringsspråk anpassat för office-programmen.
VBA-editorn
En programmeringsmiljö för aktuell arbetsbok som hittas under makro på verktygsmenyn.
Objektorienterad programmering Objektorienterad programmering bygger enkelt uttryckt på tre olika saker. Objekt, Händelser och Egenskaper.
Dessa kan förändras med hjälp av rutiner och funktioner som vi skriver i kod.
T.ex Cellen H4 är ett objekt i kalkylbladet. Detta objekt har flera egenskaper t.ex.
egenskapen värde. När en händelse inträffar – Vi byter markerad cell i kalkylbladet – Då kan vi med hjälp av kod förändra egenskapen värde till att bli en beräkning av egenskapen värde från flera andra objekt.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim berakning As Long
berakning = Blad1.Range("D4").Value + Blad1.Range("D5").Value Blad1.Range("H4").Value = berakning
End Sub
Programmeringrutiner
En rutin anges I kodfönstret med ett Subkommando som följs av rutinens namn. Efter den kod som utgör rutinens funktioner avslutas rutinen med End Sub
Ex:
Sub MinRutin() En massa kod…
End Sub
En subrutin kan också utgöra en händelse (t.ex. Workbook_Open) Dessa anges ofta som Private Sub vilket innebär att de ej kan anropas från andra rutiner.
Rutinerna kan placeras i det objekt där de skall köras (kalkylbladet) eller i utanförliggande moduler.
Deklarera variabler
Ofta behöver man mellanlagra värden utan att dessa värden kan knytas till ett
befintligt objekt. Detta görs med en dimensionering (dim). Samtidigt som man anger detta tillfälliga objekt (variabel) anger man också vilken datatyp som skall kunna knytas till variabeln.
Ex.
Sub MinRutin()
Dim antal As Integer, kronor As Currency
(Detta deklarerar två tillfälliga objekt. ”antal” som kan ta emot heltal och ”kronor” som accepterar valuta.)
antal = txtAntal.Value kronor = txtPris.Value txtAttBetala = antal * kronor
(Sedan kan man använda dessa ”nya” objekt till beräkningar i koden) End Sub
If eller Select Case (Villkor)
If anges om en egenskap skall ändras utifrån två möjliga förutsättningar (villkor).
T.ex. En kryssruta är antingen ikryssad eller inte.
If chkTest.Value = True then (Värde om kryssutan är ibockad) Else
(Värde om kryssrutan inte är ibockad) End If
Om villkoren blir tre eller fler används en Select Case
T.ex om värdet i en textruta är större än 50, mindre än 50, eller ej ifylld.
Select Case txtTest.Value Case is >=50
Värde om…
Case is >=0
Värde om…
Case Else
Värde om…
End Select
Loopar – Do While eller Do Until
Do While utförs så länge ett villkor stämmer medans Do Until körs tills villkoret hittas Ex.
Do Until ActiveCell.Value = "" (Kör tills du hitter en tomcell) ActiveCell.Offset(1, 0).Select (Flytta markerad cell en rad) Loop
