Opprette egendefinerte funksjoner i Excel

Obs!: Vi ønsker å gi deg det nyeste hjelpeinnholdet så raskt som mulig, på ditt eget språk. 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 for deg nederst på denne siden? Her er den engelske artikkelen for enkel referanse.

Selv om Excel inneholder et stort antall innebygde regnearkfunksjoner, er den ikke har en funksjon for hver type beregning du utfører. Designere av Excel kan ikke muligens regner med alle brukernes beregningen behov. I stedet gir Excel deg mulighet til å opprette egendefinerte funksjoner, som forklart i denne artikkelen.

Egendefinerte funksjoner, som makroer, bruke Visual Basic for Applications (VBA) programmeringsspråket. De skiller seg fra makroer i betydelig på to måter. Først bruke de Function -prosedyrene i stedet for Sub -prosedyrer. Det vil si begynner de med en Function -setningen i stedet for en Sub -setning og en slutt med End Function i stedet for End Sub. Det andre utføre de beregninger i stedet for å utføre handlinger. Visse typer setninger, for eksempel setninger som velger og formaterer områder, utelates fra egendefinerte funksjoner. I denne artikkelen lærer du hvordan du oppretter og bruke egendefinerte funksjoner. Hvis du vil opprette funksjoner og makroer, arbeide med Visual Basic Editor (VBE), som åpnes i et nytt vindu som er atskilt fra Excel.

La oss si at firmaet tilbyr antall rabatt på 10 prosent på grunnlag av salget av et produkt, angitt rekkefølgen er for mer enn 100 enheter. Vi kan demonstrere en funksjon for å beregne denne rabatten i avsnittene nedenfor.

Eksemplet nedenfor viser et ordreskjema som viser hvert element, antall, pris, rabatt (hvis aktuelt), og den resulterende Totalpris.

Eksempel ordreskjema uten en egendefinert funksjon

Hvis du vil opprette en egendefinert rabatt-funksjon i denne arbeidsboken, følger du disse trinnene:

  1. Trykk Alt + F11 for å åpne Visual Basic Editor (på Mac, trykker FN + ALT + F11 ), og klikk deretter Sett inn > modul. Det åpnes et nytt modulvindu 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!: Du kan bruke Tab for å rykke inn linjer for å gjøre det enklere å lese koden. Innrykket er for fordelen bare, og er valgfritt, som koden kjøres med eller uten den. Når du skriver inn en innrykkede linjen, Visual Basic-redigering tar utgangspunkt i den neste linjen blir på samme måte innrykket. Flytte (det vil si til venstre) én tabulator tegn, trykker du Skift + Tab.

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

=DISCOUNT(D7,E7)

Excel beregner 10 prosent rabatten på 200 enheter på 47.50 per enhet, og returnerer $950.00.

I den første linjen i VBA-kode (funksjon) DISCOUNT(quantity, price) anga du at rabatt-funksjonen krever to argumenter, Antall og Pris. Når du ringer funksjonen i en regnearkcelle, må du ta med disse to argumenter. I formelen = DISCOUNT(D7,E7), D7 er argumentet Antall og E7 er argumentet Pris . Nå kan du kopiere rabatt formelen til G8:G13 for å få ønsket resultat som vist nedenfor.

La oss se hvordan Excel tolker denne function-prosedyre. Når du trykker Enter, ser etter navnet rabatt i den gjeldende arbeidsboken i Excel, og finner at det er en egendefinert funksjon i en VBA-modul. Argumentnavn i parentes, Antall og Pris, er plassholdere for verdiene som er basert beregningen av rabatten.

Eksempel Bestillingsskjema med en egendefinert funksjon

Den Hvis setningen i følgende blokk med kode undersøker argumentet Antall og avgjør 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 verdien for Antall fargeverdi Pris og deretter multipliseres resultatet med 0,1:

Discount = quantity * price * 0.1

Resultatet er lagret som variabelen rabatt. En VBA-setning som lagrer en verdi i en variabel kalles en tildeling -setning, fordi den evaluerer uttrykket på høyre side av likhetstegnet og tilordner resultatet til variabelnavnet til venstre. Fordi variabelen rabatt har samme navn som function-prosedyre, returneres verdien som er lagret i variabelen til regnearkformel som heter rabattfunksjonen.

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

Discount = 0

Til slutt Avrunder setningen nedenfor verdien tilordnes til variabelen rabatt til to desimaler:

Discount = Application.Round(Discount, 2)

VBA har ingen AVRUND-funksjonen, men Excel svarer. Hvis du vil bruke funksjonen AVRUND i denne setningen, se du derfor VBA til å søke etter metoden AVRUND (funksjon) i programobjektet (Excel). Du gjør dette ved å legge til ordet programmet foran ordet Avrund. Bruk denne syntaksen når du trenger å få tilgang til en Excel-funksjon fra en VBA-modul.

En egendefinert funksjon må begynne med en funksjon-setning og slutter med en End Function-setningen. I tillegg til funksjonsnavnet angir Function-setningen 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.

Function-prosedyre inneholder én eller flere VBA-setninger som ta avgjørelser og utføre beregninger ved hjelp av argumenter som sendes til funksjonen ved å følge Function-setningen. Til slutt må et sted i function-prosedyre, du inkludere en setning som tilordner en verdi til en variabel med samme navn som funksjonen. Denne verdien returneres til formelen som kaller funksjonen.

Hvor mange VBA-nøkkelord du kan bruke i egendefinerte funksjoner som er mindre enn hvor du kan bruke i makroer. Egendefinerte funksjoner er ikke tillatt å gjøre noe annet enn retur en verdi i en formel i et regneark eller et uttrykk brukt i en annen VBA-makro eller funksjon. Egendefinerte funksjoner kan for eksempel endre størrelse på windows, redigere en formel i en celle eller endre skrift, farge eller mønster alternativer for tekst i en celle. Hvis du inkluderer "handling"-koden slike i en function-prosedyre, returnerer funksjonen feilverdien #VALUE! feil.

Én handlingen function-prosedyre kan gjøre (med mindre du utføre beregninger) er vises en dialogboks. Du kan bruke en InputBox-setning i en egendefinert funksjon som en metode for å hente inndata fra brukeren utfører funksjonen. Du kan bruke en Meldingsboks-setningen som en metode for å formidle informasjon til brukeren. Du kan også bruke egendefinerte dialogbokser eller UserForm-skjemaer, men det er et emne utover omfanget av denne innføringen.

Selv enkel makroer og egendefinerte funksjoner kan være vanskelig å lese. Du kan gjøre dem enklere å forstå ved å skrive inn forklarende tekst i form av kommentarer. Du kan legge til kommentarer ved foran forklarende tekst med en apostrof. Eksemplet nedenfor viser for eksempel rabattfunksjonen med kommentarer. Legge til kommentarer som disse gjør det enklere for deg eller andre til å vedlikeholde VBA-kode som tiden går. Hvis du trenger å gjøre en endring i koden i fremtiden, har du en enklere tid forstå det du gjorde opprinnelig.

Eksempel på en VBA-funksjon med kommentarer

En apostrof forteller Excel å ignorere alt til høyre på samme linje, slik at du kan opprette merknader enten på linjene alene eller på høyre side av linjer som inneholder VBA-kode. Du kan begynne en relativt lange blokk med kode med en kommentar som forklarer formålet generell og deretter bruker du linjebundne kommentarer til individuelle setninger i dokumentet.

En annen måte å dokumentere makroer og egendefinerte funksjoner er å gi dem beskrivende navn. Hvis du for eksempel i stedet for navnet på en makro etiketter, som du kan kalle MonthLabels til å beskrive nærmere formålet makroen fungerer. Bruke beskrivende navn for makroer og egendefinerte funksjoner er spesielt nyttig når du har opprettet mange prosedyrer, spesielt hvis du oppretter prosedyrer som har samme, men ikke identiske formål.

Hvordan du dokumentere makroer og egendefinerte funksjoner er et spørsmål om foretrekker. Hva er viktige er å begynne å bruke en metode i dokumentasjonen for og bruk det konsekvent.

Hvis du vil bruke en egendefinert funksjon, må arbeidsboken som inneholder modulen der du opprettet funksjonen være åpen. Hvis arbeidsboken ikke er åpen, kan du få #NAME? feilmelding når du prøver å bruke funksjonen. Hvis du refererer til funksjonen i en annen arbeidsbok, må du være før funksjonsnavnet med navnet på arbeidsboken der funksjonen ligger. Hvis du oppretter en funksjon som heter rabatt i en arbeidsbok som heter Personal.xlsb, og du ringer funksjonen fra en annen arbeidsbok, må du for eksempel skriver inn =personal.xlsb!discount(), ikke bare =discount().

Du kan spare tastetrykk (og mulige skrivefeil) ved å velge de egendefinerte funksjonene i dialogboksen Sett inn funksjon. Egendefinerte funksjoner vises i kategorien brukerdefinert:

dialogboksen Sett inn funksjon

En enklere måte å gjøre egendefinerte funksjoner som er tilgjengelig på alltid er å lagre dem i en annen arbeidsbok, og deretter lagre arbeidsboken som et tillegg. Du kan gjøre tillegget tilgjengelig hver gang du starter Excel. Her får du vite hvordan du gjør dette:

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

    I Excel 2007, klikker du Microsoft Office-knappen, og klikk Lagre som.

  2. Åpne rullegardinlisten Filtype i dialogboksen Lagre som, og velg Excel-tillegg. Lagre arbeidsboken med et gjenkjennelig navn, for eksempel MyFunctionsi AddIns -mappen. Dialogboksen Lagre som foreslår denne mappen, slik at alt du må gjøre er å godta standardplasseringen.

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

    Klikk Microsoft Office-knappen i Excel 2007, og klikk Alternativer for Excel.

  4. Klikk kategorien Tillegg i dialogboksen Alternativer for Excel.

  5. Velg Excel-tillegg i Behandle-rullegardinlisten. Klikk knappen .

  6. Merk av ved siden av navnet du brukte til å lagre arbeidsboken, som vist nedenfor, i dialogboksen Tillegg.

    dialogboksen Tillegg

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

  2. Åpne rullegardinlisten Filtype i dialogboksen Lagre som, og velg Excel-tillegg. Lagre arbeidsboken med et gjenkjennelig navn, for eksempel MyFunctions.

  3. Når du har lagret arbeidsboken, klikker du Verktøy > Excel-tillegg.

  4. Velg Bla gjennom for å finne tillegget, klikker du Åpne, og Merk av i boksen ved siden av tilleggsprogrammet ditt i boksen Tilgjengelige tillegg i dialogboksen Tillegg.

Når du har fulgt disse trinnene, vil egendefinerte funksjoner være tilgjengelig hver gang du starter Excel. Hvis du vil legge til i funksjon-biblioteket, kan du gå tilbake til Visual Basic Editor. Hvis du ser i Visual Basic-redigering Prosjektutforsker under en VBA-overskrift, får du en modul med navnet etter filen-tillegget. Et tillegg har filtypen xlam.

navngitt modul i vbe

Når du dobbeltklikker modulen i Prosjektutforsker utløses Visual Basic-redigering til å vise koden (funksjon). Hvis du vil legge til en ny funksjon, Plasser innsettingspunktet etter End Function-setningen som avslutter funksjonen siste i kodevinduet, og begynn å skrive. Du kan opprette så mange funksjoner som du trenger på denne måten, og de vil alltid være tilgjengelig i fanen brukerdefinert i dialogboksen Sett inn funksjon.

Denne innholdstypen ble opprinnelig skrevet av Mark Dodge og Craig Stinson som en del av deres bok Microsoft Office Excel 2007 Inside Out. Det er oppdatert siden hvis du vil bruke 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.

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.

×