Opprette egen definerte funksjoner i Excel

Selv om Excel inneholder en stort antall med innebygde regne ark funksjoner, er det ikke med en funksjon for hver type beregning du utfører. Utformerne av Excel kunne ikke forutse hver brukers beregnings behov. I stedet gir Excel deg muligheten til å opprette egen definerte funksjoner, som er forklart i denne artikkelen.

Leter du etter informasjon om hvordan du oppretter en egen definert JavaScript-funksjon som du kan kjøre på Excel for Windows, Excel for Mac eller Excel for nettet ? Hvis du vil, kan du se artikkelen egen definerte funksjoner i Excel.

Egen definerte funksjoner, for eksempel makroer, bruker programmerings språket Visual Basic for Applications (VBA) . De skiller seg fra makroer på to måter. Først bruker de Function -prosedyrer i stedet for del prosedyrer. Det vil si at de begynner med en funksjons setning i stedet for en under setning og slutter med end-funksjonen i stedet for End Sub. For det andre utfører de beregninger i stedet for å utføre handlinger. Enkelte typer setninger, for eksempel setninger som velger og formaterer områder, utelates fra egen definerte funksjoner. I denne artikkelen lærer du hvordan du oppretter og bruker egen definerte funksjoner. Hvis du vil opprette funksjoner og makroer, arbeider du med Visual Basic Editor (VBE), som åpnes i et nytt vindu atskilt fra Excel.

La oss anta at firmaet har en kvantums rabatt på 10 prosent på salget av et produkt, Hvis bestillingen er for mer enn 100 enheter. I avsnittene nedenfor viser vi en funksjon for å beregne denne rabatten.

Eksemplet nedenfor viser et bestillings skjema som viser hvert element, antall, pris, rabatt (hvis det finnes) og den endelige prisen.

Eksempel på bestillings skjema uten en egen definert funksjon

Følg denne Fremgangs måten for å opprette en egen definert rabatt funksjon i denne arbeids boken:

  1. Trykk Alt + F11 for å åpne Visual Basic Editor (på Mac, trykk på FN + alt + F11), og klikk deretter på Sett inn > modul. Et nytt modul vindu vises på høyre side av Visual Basic-redigering.

  2. Kopier og lim inn følgende kode i den nye modulen.

    Function DISCOUNT(quantity, price)
       If quantity >=100 Then
         DISCOUNT = quantity * price * 0.1
       Else
         DISCOUNT = 0
       End If
     
     DISCOUNT = Application.Round(Discount, 2)
    End Function
    

Obs!: Hvis du vil gjøre koden mer lesbar, kan du bruke tab -tasten til å rykke inn linjer. Inn rykket er bare for fordelen, og er valg fri, da koden kjøres med eller uten den. Når du har skrevet inn en inn rykket linje, forutsetter Visual Basic Editor den neste linjen på samme måte som inn rykk. Hvis du vil gå ut av (det vil si til venstre) ett tabulatortegn, trykker du SKIFT + TAB.

Nå er du klar til å bruke den nye rabatt funksjonen. Lukk Visual Basic Editor, Velg celle G7, og skriv inn følgende:

= RABATT (D7; E7)

Excel beregner den 10% rabatten på 200 enheter på $47,50 per enhet og returnerer $950,00.

I den første linjen i VBA-koden, funksjons rabatt (antall, pris), indikert at rabatt-funksjonen krever to argumenter, antall og pris. Når du ringer til funksjonen i en regne ark celle, må du inkludere disse to argumentene. I formelen = DISCOUNT (D7; E7); D7 er argumentet antall , og E7 er Price- argumentet. Nå kan du kopiere rabatt formelen til G8: G13 for å få resultatene vist nedenfor.

La oss vurdere hvordan Excel tolker denne Function-prosedyren. Når du trykker Enter, ser Excel etter navne rabatten i gjeldende arbeids bok og finner ut at den er en egen definert funksjon i en VBA-modul. Argument navnene i parenteser, antall og pris, er plassholdere for verdiene som beregningen av rabatten er basert på.

Eksempel på bestillings skjema med en egen definert funksjon

Hvis-setningen i den følgende kode blokken undersøker argumentet antall og bestemmer om antall solgte varer er større enn eller lik 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Hvis antall solgte varer er større enn eller lik 100, utfører VBA følgende setning, som multipliserer antall verdien etter pris verdien og deretter multipliseres resultatet med 0,1:

Discount = quantity * price * 0.1

Resultatet blir lagret som variabel rabatt. Et VBA-uttrykk som lagrer en verdi i en variabel, kalles en tilordnings setning, fordi den evaluerer uttrykket på høyre side av likhets tegnet og tilordner resultatet til variabel navnet til venstre. Fordi variabel rabatten har samme navn som Function-prosedyren, returneres verdien som er lagret i variabelen, til regne ark formelen som kalles rabatt-funksjonen.

Hvis antall er mindre enn 100, utfører VBA følgende setning:

Discount = 0

Til slutt runder følgende setning verdien som er tilordnet til rabatt variabelen, til to desimaler:

Discount = Application.Round(Discount, 2)

VBA har ingen AVRUND-funksjon, men Excel gjør det. Hvis du vil bruke ROUND i denne erklæringen, kan du derfor angi at VBA skal søke etter den runde metoden (funksjonen) i Application-objektet (Excel). Det gjør du ved å legge til Word- programmet før ord avrundet. Bruk denne syn tak sen når du trenger å få tilgang til en Excel-funksjon fra en VBA-modul.

En egen definert funksjon må starte med en funksjons setning og slutte med en end Function-setning. I tillegg til funksjons navnet, angir funksjons uttrykket vanligvis ett eller flere argumenter. Du kan imidlertid opprette en funksjon uten argumenter. Excel inneholder flere innebygde funksjoner, tilfeldig og nå, for eksempel, som ikke bruker argumenter.

Når du følger funksjons setningen, inneholder en Function-prosedyre én eller flere VBA-setninger som tar avgjørelser og utfører beregninger ved hjelp av argumentene som sendes til funksjonen. Til slutt, et sted i Function-prosedyren, må du inkludere en setning som tilordner en verdi til en variabel med samme navn som funksjonen. Denne verdien returneres til formelen som kaller funksjonen.

Antall VBA-nøkkelord du kan bruke i egen definerte funksjoner, er mindre enn tallet du kan bruke i makroer. Egen definerte funksjoner kan ikke gjøre noe annet enn å returnere en verdi til en formel i et regne ark, eller til et uttrykk som brukes i en annen VBA-makro eller-funksjon. Egen definerte funksjoner kan for eksempel ikke endre størrelse på Vinduer, redigere en formel i en celle eller endre alternativene for skrift, farge eller mønster for teksten i en celle. Hvis du inkluderer «handling» kode av denne typen i en Function-prosedyre, returnerer funksjonen #VALUE! .

Den ene handlingen en Function-prosedyre kan gjøre (bortsett fra å utføre beregninger), vises en dialog boks. Du kan bruke en InputBox -setning i en egen definert funksjon som en metode for å få inn data fra brukeren som kjører funksjonen. Du kan bruke en MsgBox -setning som en metode for å formidle informasjon til brukeren. Du kan også bruke egen definerte dialog bokser eller UserForm-er, men det er et emne utenfor omfanget av denne innføringen.

Selv enkle makroer og egen definerte funksjoner kan være vanskelig å lese. Du kan gjøre dem enklere å forstå ved å skrive inn forklarende tekst i kommentar form. Du legger til kommentarer ved å foran den forklarende teksten med en apostrof. Det følgende eksemplet viser for eksempel rabatt-funksjonen med kommentarer. Hvis du legger til kommentarer som dette, blir det enklere for deg eller andre å beholde VBA-koden etter hvert som tiden går gjennom. Hvis du trenger å gjøre en endring i koden i fremtiden, har du en enklere tid å forstå det du opprinnelig gjorde.

Eksempel på en VBA-funksjon med kommentarer

En apostrof gir Excel beskjed om å ignorere alt til høyre på samme linje, slik at du kan opprette kommentarer enten på linjer alene eller på høyre side av linjer som inneholder VBA-kode. Du kan starte en relativ, lang kode blokk med en kommentar som forklarer det overordnede formålet, og deretter bruke linje bundne kommentarer til å dokumentere individuelle setninger.

En annen måte å dokumentere makroer og egen definerte funksjoner på, er å gi dem beskrivende navn. I stedet for å gi navn til en makro etiketter, kan du for eksempel gi det et navn til MonthLabels for å beskrive mer spesifikt formålet makroen tjener. Det er spesielt nyttig å bruke beskrivende navn på makroer og egen definerte funksjoner når du har opprettet mange prosedyrer, spesielt hvis du oppretter prosedyrer som har lignende, men ikke identiske formål.

Hvordan du dokumenterer makroer og egen definerte funksjoner er en person som foretrekker å være med. Det er viktig at du bruker en dokumentasjons metode, og du kan bruke det konsekvent.

Hvis du vil bruke en egen definert funksjon, må arbeids boken som inneholder modulen du opprettet funksjonen være åpen. Hvis denne arbeids boken ikke er åpen, får du en #NAME? feil melding når du prøver å bruke funksjonen. Hvis du refererer til funksjonen i en annen arbeids bok, må du skrive inn navnet på arbeids boken som funksjonen ligger i, før funksjons navnet vises. Hvis du for eksempel oppretter en funksjon som heter DISCOUNT i en arbeids bok som heter personlig. xlsb, og du kaller denne funksjonen fra en annen arbeids bok, må du skrive inn = Personal. xlsb! DISCOUNT (), ikke bare = rabatt ().

Du kan spare deg selv noen taste trykk (og mulige skrive feil) ved å velge de egen definerte funksjonene fra dialog boksen Sett inn funksjon. De egen definerte funksjonene vises i brukerdefinert Kategori:

dialogboksen Sett inn funksjon

En enklere måte å gjøre de egen definerte funksjonene tilgjengelig til alle tider på, er å lagre dem i en egen arbeids bok og deretter lagre arbeids boken som et tillegg. Deretter kan du gjøre tillegget tilgjengelig når du kjører Excel. Slik gjør du dette:

  1. Når du har opprettet funksjonene du trenger, klikker du fil > Lagre som.

    Klikk på Microsoft Office-knappeni Excel 2007, og klikk på Lagre som

  2. Åpne rulle gardin listen filtype i dialog boksen Lagre som , og velg Excel-tillegg. Lagre arbeids boken under et gjenkjennelig navn, for eksempel MyFunctions, i tillegg -mappen. Dialog boksen Lagre som vil foreslå denne mappen, så alt du trenger å gjøre, er å godta standard plasseringen.

  3. Når du har lagret arbeids boken, klikker du fil > Alternativer for Excel.

    Klikk Microsoft Office-knappeni Excel 2007, og klikk deretter Alternativer for Excel.

  4. Klikk kategorien tillegg i dialog boksen Alternativer for Excel .

  5. Velg Excel-tilleggfra rulle gardin listen Behandle . Klikk deretter på -knappen.

  6. Merk av i boksen ved siden av navnet du brukte til å lagre arbeids boken, som vist nedenfor, i dialog boksen tillegg .

    dialogboksen Tillegg

  1. Når du har opprettet funksjonene du trenger, klikker du fil > Lagre som.

  2. Åpne rulle gardin listen filtype i dialog boksen Lagre som , og velg Excel-tillegg. Lagre arbeids boken under et gjenkjennelig navn, for eksempel MyFunctions.

  3. Når du har lagret arbeids boken, klikker du verktøy > Excel-tillegg.

  4. Velg bla gjennom-knappen i dialog boksen tillegg for å finne tillegget, klikk Åpne, og merk deretter av i boksen ved siden av tillegget i Tilgjengelige tillegg- boksen.

Når du har fulgt disse trinnene, blir de egen definerte funksjonene tilgjengelige hver gang du kjører Excel. Hvis du vil legge til i funksjons biblioteket, går du tilbake til Visual Basic Editor. Hvis du ser i Visual Basic Editor Project Explorer under en prosjektegenskaper-overskrift, vil du se en modul som heter etter tilleggs filen. Tillegget vil ha filtypen. xlam.

navngitt modul i vbe

Hvis du dobbelt klikker denne modulen i prosjekt utforske ren, får du Visual Basic-redigering til å vise funksjons koden. Hvis du vil legge til en ny funksjon, plasserer du innsettings punktet etter end Function-setningen som avsluttet den siste funksjonen i kode vinduet, og begynner å skrive. Du kan opprette så mange funksjoner som du trenger på denne måten, og de vil alltid være tilgjengelige i brukerdefinert kategori i dialog boksen Sett inn funksjon .

Dette innholdet ble opprinnelig laget ved å merke lysne og Craig Stinson som en del av boken Microsoft Office Excel 2007 Inside Out. Den har blitt oppdatert til å gjelde for nyere versjoner av Excel også.

Trenger du mer hjelp?

Du kan alltid spørre en ekspert i Excel tekniske fellesskap, få støtte i Svar-fellesskapet eller foreslå en ny funksjon eller forbedring på Excel User Voice.

Obs!:  Denne siden er oversatt gjennom automatisering og kan inneholde grammatiske feil eller unøyaktigheter. Formålet vårt er at innholdet skal være nyttig for deg. Kan du fortelle oss om informasjonen var nyttig? Her er den engelske artikkelen for referanse.

Bli bedre på Office
Utforsk opplæring
Vær først ute med de nye funksjonene
Bli med i Office Insiders

Var denne informasjonen nyttig?

Takk for tilbakemeldingen!

Takk for tilbakemeldingen! Det høres ut som det kan være lurt å sette deg i kontakt med én av våre Office-kundestøtteagenter.

×