Excel økonomiske former

En betydelig del av tiden for mange økonomer eller finansfolk blir brukt på å jobbe på en datamaskin med MS Excel office-applikasjonen. Dette programmet har et betydelig antall funksjoner beregnet på å lage rapporter, dataanalyse, informasjonsplaner, matematiske beregninger og mye mer. Kunnskap om de viktigste Excel-formlene og en kombinasjon av forskjellige funksjoner forenkler løsningen av praktiske problemer og reduserer tiden og kreftene du bruker på det.
INDEX og MATCH-funksjoner
IF-funksjon kombinert med AND-funksjon
Kombinasjon av SUM- og OFFSET-funksjoner
SUMIF- og COUNTIF-funksjoner
Funksjon MODE.SNGL

INDEX og MATCH-funksjoner

I økonomiske beregninger brukes ofte en kombinasjon av INDEX og MATCH-funksjonene. Den felles handlingen deres ligner driften av VLOOKUP-funksjonen, men har mange fordeler sammenlignet med den. Først vil vi vurdere disse funksjonene separat.

INDEX-funksjon

INDEX-funksjonen finner verdien til et element i en datablokk med det angitte radnummeret og kolonnenummeret. Generelt sett vil strukturen se slik ut:

INDEX (array;row_number;[column_number]), hvor

  • array – Dette er en blokk med celler der søket skal utføres.
  • row_number – Dette er sekvensnummeret på linjen som verdien er funnet i. Denne parameteren er påkrevd hvis kolonnenummeret ikke er spesifisert.
  • column_number – Dette er serienummeret til kolonnen der ønsket verdi ligger. Hvis det ikke er spesifisert noe linjenummer i formelen, er denne parameteren påkrevd.

Hvis et radnummer og et kolonnetall er spesifisert, returnerer funksjonen verdien til cellen som ligger i skjæringspunktet mellom disse dataene.

Tenk på et eksempel. Det er en tabell med en liste over produktnumre og deres dimensjoner. For å finne lengden på produkt nummer 2, må du skrive en formel med skjemaet =INDEX(B2:D6;3;2)
Function INDEX
I dette eksemplet vil resultatet av funksjonen være innholdet i cellen i skjæringspunktet mellom den tredje raden og den andre kolonnen i denne matrisen.

Dessverre, oftest i beregninger, er rad- eller kolonnetallet ukjent. Da kommer MATCH-funksjonen til unnsetning.

MATCH-funksjon

Handlingen til MATCH-funksjonen tilsvarer handlingen til INDEX-funksjonen, men MATCH returnerer ikke celleverdien, men celleposisjonen i det spesifiserte området. Generelt sett vil formelen se slik ut:

MATCH (search_value;array;[match_type]), hvor

  • search_value – dette er hva du trenger å finne. Her kan ikke bare være en tekst eller en numerisk verdi, men også en logisk, samt en lenke til en celle.
  • array – dette er celleområdet som blir sett på.
  • match_type – dette er en valgfri parameter som kan settes til “1”, “0” eller “-1”. Den forteller funksjonen hvilken verdi du skal finne: nøyaktig eller omtrentlig. Hvis dataene i matrisen synker i stigende rekkefølge, vil parameter “1” indikere at det er nødvendig å velge maksimalverdien mindre enn eller lik den ønskede. Parameteren “-1” er spesifisert for en reduserende matrise. I dette tilfellet vil funksjonen velge minimumsverdi større enn eller lik den ønskede. Parameter “0” finner den første verdien lik den ønskede. Det er denne verdien av samsvarende type som brukes i kombinasjonen av INDEX- og MATCH-funksjonene.

For å illustrere bruken av denne funksjonen, kan du vurdere et eksempel. I tabellen over finner vi i kolonnen “Product Number” hva kontoen vil være for produktnummer 2. For dette skriver vi formelen: =MATCH(2;B2:B6;0)
Function MATCH
Hvorfor trenger jeg å vite posisjonen til et element i en tabell? Det viser seg at det er veldig praktisk å bruke denne verdien som et argument for INDEX-funksjonen.

Kombinasjon av INDEX og MATCH-funksjoner

Hvis vi analyserer begge funksjonene, blir det tydelig at INDEX-funksjonen søker etter celleverdier etter radnummer og kolonnenummer. Samtidig finner MATCH-funksjonen radnumre og kolonnetall. Så hvis du bruker disse to funksjonene i samme formel, vil MATCH finne den relative posisjonen til ønsket verdi, og INDEX-funksjonen vil bruke disse verdiene og returnere innholdet i de beregnede cellene.

Tenk på et eksempel. I tabellen over finner vi bredden på produktet på nummer 3. For å gjøre dette, bruk denne formelen: =INDEX($D$2:$D$6;MATCH(3;$B$2:$B$6;0))
Combination of INDEX and MATCH
Det anbefales at du bruker absolutte lenker for disse formlene, slik at søkeområdet ikke blir forvekslet når du kopierer formler.

Hvorfor er det bedre å bruke en kombinasjon av INDEX og MATCH-funksjoner i stedet for å bruke VLOOKUP-funksjonen? For det første tillater en formel basert på INDEX og MATCH deg å søke etter ønsket data i det angitte området fra venstre til høyre og fra høyre til venstre, mens når du bruker VLOOKUP, bør den ønskede verdien alltid være i kolonnen helt til venstre i området.

For det andre, når du bruker VLOOKUP-funksjonen, kan du ikke slette eller legge til kolonner i tabellen. Ellers vil resultatet av formelen være feil. Dette er fordi syntaks for denne funksjonen innebærer å spesifisere hele området og det spesifikke kolonnenummeret som dataene skal tas fra. Når du bruker INDEX og MATCH-funksjonene, kan du slette eller legge til så mange kolonner du vil.

For det tredje, når du bruker en kombinasjon av INDEX og MATCH-funksjoner, er det ingen begrensninger i størrelsen på søkeverdien, mens VLOOKUP-funksjonen begrenser antall tegn i søkeverdien til 255 tegn.

For det fjerde, når du utfører beregninger i store dataarrayer, bruker INDEX og MATCH-funksjonene betydelig tiden som trengs for å søke etter verdier sammenlignet med VLOOKUP-funksjonen. Dette er fordi VLOOKUP-funksjonen blir kalt for hver verdi fra det spesifiserte dataområdet. I kontrast utfører en formel basert på INDEX- og MATCH-funksjonene ganske enkelt et søk og returnerer et resultat.

IF-funksjon kombinert med AND-funksjon

IF-boolske funksjonen sjekker om innholdet i cellene oppfyller visse betingelser. Hvis den stemmer, returnerer funksjonen en av de brukerdefinerte verdiene. I tilfelle avvik, returnerer du en annen angitt verdi. Syntaks for funksjonen er som følger:

=IF(logisk_uttrykk; verdi_if_true; verdi_if_false), hvor

  • logisk_uttrykk – dette er dataene som må sjekkes og vilkårene for bekreftelse. For eksempel А2>10.
  • verdi_if_true – dette er posten som vil vises hvis celleverdien tilfredsstiller den gitte betingelsen.
  • verdi_if_false – dette er posten som vil vises hvis celleverdien ikke tilfredsstiller den gitte betingelsen.

Mange brukere som har utført komplekse økonomiske beregninger, vet hvor vanskelig det er å forstå formler som bruker nestede løkker ved å bruke IF-setningen. Det viser seg at disse formlene kan forenkles hvis du bruker IF-funksjonen i kombinasjon med AND/OR-funksjonene. Kombinasjonen av AND- og IF-funksjonene fungerer som følger. Hvis A = 1 og A = 2, returnerer formelen verdien B, ellers returnerer den C. For OR-funksjonen fungerer ikke formelen slik. Hvis A = 1 eller A = 2, returnerer formelen verdien B, ellers – verdien C.

Tenk på et eksempel. Lag en formel som sjekker innholdet i celle C2, lik 110. Hvis tallet ligger i området fra 90 til 300, blir resultatet 1, ellers 0. Formelen vil se slik ut: =IF(AND(C2>=C4;C2<=C5);C7;C8)
IF function
Som det fremgår av figuren, vil en verdi lik 1 bli plassert i cellen med resultatet av funksjonen. Tallet 110 er virkelig i området fra 90 til 300.

Den totale verdien kan ikke bare være et tall, men også en tekst, for eksempel ordene “JA” eller “Nei” eller andre setninger.

Kombinasjon av SUM- og OFFSET-funksjoner

Selve OFFSET-funksjonen brukes sjelden, men å kombinere den med andre funksjoner kan gi veldig gode resultater. For eksempel lar den kombinerte bruken av SUM- og OFFSET-funksjonene deg lage ganske komplekse formler når du oppretter en dynamisk funksjon som summerer et variabelt antall celler. For å løse dette problemet brukes SUM-funksjonen, og i stedet for den endelige cellen spesifiseres OFFSET-funksjonen, det vil si formelen blir dynamisk.

Den resulterende formelen vil se slik ut:

= SUM (start_range:OFFSET(referanse,antall rader,antall kolonner)), hvor

  • start_range – Dette er utgangspunktet for celleområdet som brukes av SUM-funksjonen.
  • referanse – Dette er cellereferansen som brukes til å beregne endepunktet for området.
  • antall rader – dette er antall rader som brukes ved beregning av celleforskyvning. Denne verdien kan være positiv, negativ og lik null.
  • antall kolonner – dette er antall kolonner til høyre eller venstre for den gitte cellehenvisningen. Brukes ved beregning av forskyvningen. Når du flytter til venstre, er denne verdien negativ. Når du forskyves til høyre, er verdien positiv. Hvis de beregnede dataene er i samme kolonne, er denne parameteren lik null.

Tenk på et eksempel. Det er en tabell med serienumrene til månedene og inntektene mottatt hver dag. Hver dag oppdateres informasjonen i tabellen ved å legge til en rad med inntektene mottatt per dag. Vi komponerer formelen i den endelige cellen etter den fjerde salgsdagen: =SUM(B2:OFFSET(B6;-1;0))
OFFSET functions
For å legge til informasjon om den femte salgsdagen, må du legge til en tom linje etter den fjerde dagen og legge inn nødvendig informasjon i den. I dette tilfellet vil formelen ha formen: =SUM(B2:OFFSET(B7;-1;0)), og den samlede inntekten vil øke med inntektsbeløpet den femte dagen.

SUMIF- og COUNTIF-funksjoner

Disse to funksjonene brukes ofte i økonomiske beregninger. SUMIF finner summen i et gitt område av celler etter en viss tilstand. COUNTIF teller alle celler som samsvarer med den gitte tilstanden.

SUMIF-funksjonen har følgende struktur:

SUMIF(område;kriterium;sum_range), hvor

  • område – dette er en rekke celler der overholdelsen av de spesifiserte kriteriene vil bli sjekket.
  • kriterium – dette er en rekke celler der overholdelsen av de spesifiserte kriteriene vil bli sjekket..
  • sum_range – Dette er en valgfri parameter. Hvis du ikke spesifiserer det, vil summeringen bli utført under hensyntagen til argumentet “Område “.

Handlingen til denne funksjonen er lett å forstå med et eksempel. Det er en tabell med en liste over varer og deres mengde. Du må finne mengden “Product 1”. Formelen for beregningen vil være som følger: =SUMIF(B2:B7;”Product 1″;C2:C7)
SUMIF functions
I samme eksempel kan du beregne antall produkter unntatt “Product 1” ved hjelp av denne formelen: =SUMIF(B2:B7;”<>Product 1″;C2:C7)

COUNTIF-funksjonen har følgende struktur:

COUNTIF (rekkevidde; kriterium), hvor

rekkevidde og kriterium ligner på SUMIF.

I det samme eksemplet teller vi antall rader “Product 1”. Formelen for beregning vil se ut: =COUNTIF(B2:B7;”Product 1″).
COUNTIF functions
Ved hjelp av denne formelen kan du beregne antall rader som tilfredsstiller visse betingelser. For å beregne antall rader med mer enn 10 elementer i denne tabellen, må du for eksempel lage en slik formel: =COUNTIF(C2:C7;”>10″).

Funksjon MODE.SNGL

Den statistiske funksjonen MODE.SNGL og dens foreldede versjon MODE finner den hyppigst forekommende verdien i dataområdet (matrisen) og returnerer denne verdien. Funksjonssyntaks: =MODE((cislo1; cislo2];…), hvor

  • cislo1 – Dette er et nødvendig argument, som er et tall, en referanse til en tallcelle, en matrise eller et område med celler.
  • cislo2 – valgfritt argument. Slike argumenter kan være fra 1 til 255.

Argumenter som ikke kan konverteres til tall, forårsaker en formelfeil. Hvis det ikke er identiske tall i det spesifiserte området, vil resultatet av funksjonen være feilverdien #N/A.

Hvorfor kan jeg bruke denne funksjonen i økonomiske beregninger? For eksempel for å finne ut hvilke produkter som oftest kjøpes på grunnlag av sammendragsdata. Valgkriteriet kan være prisen på produktet, størrelse, volum, dimensjoner og så videre. Som en illustrasjon vurderer vi en tabell som gjenspeiler salg av varer med salgsdatoen, størrelsen på produktet og prisen. For å finne ut hvilke størrelser på produkter som oftest selges, bruker vi formelen: =MODE(C2:C6)
MODE function
Figuren viser at produkter med en størrelse på 36 er mest etterspurt. Dermed bestemmer MODE-funksjonen den hyppigst forekommende hendelsen i hendelsesområdet.