Excel finansiella former

En betydande del av tiden för många ekonomer eller finansiärer tillbringas arbetet på en dator med MS Excel office-applikationen. Detta program har ett betydande antal funktioner avsedda för att skapa rapporter, dataanalys, informationsplaner, matematiska beräkningar och mycket mer. Kunskap om de viktigaste Excel-formlerna och en kombination av olika funktioner underlättar i hög grad lösningen av praktiska problem och minskar den tid och den ansträngning som spenderas på den.
INDEX och MATCH-funktioner
IF-funktion kombinerad med AND-funktion
Kombination av SUM- och OFFSET-funktioner
SUMIF- och COUNTIF-funktioner
Funktion MODE.SNGL

INDEX och MATCH-funktioner

I finansiella beräkningar används ofta en kombination av INDEX- och MATCH-funktionerna. Deras gemensamma åtgärd liknar funktionen för VLOOKUP-funktionen, men har många fördelar jämfört med den. Först kommer vi att överväga dessa funktioner separat.

INDEX-funktion

INDEX-funktionen hittar värdet på ett element i ett datablock med det angivna radnumret och kolumnnumret. Generellt sett kommer dess struktur att se ut så här:
INDEX (matris; radnummer; [kolumnnummer]), där

  • matris – Detta är ett block med celler där sökningen kommer att utföras.
  • radnummer – Detta är sekvensnumret på den linje där värdet finns. Denna parameter krävs om kolumnnumret inte anges.
  • kolumnnummer – Detta är serienumret på kolumnen där det önskade värdet finns. Om inget radnummer anges i formeln krävs denna parameter.

Om ett radnummer och ett kolumnnummer anges returnerar funktionen värdet på cellen som ligger i skärningspunkten mellan dessa data.

Tänk på ett exempel. Det finns en tabell med en lista över produktnummer och deras mått. För att hitta längden på produktnummer 2 måste du skriva en formel för formuläret =INDEX(B2:D6;3;2)
Function INDEX
I detta exempel kommer resultatet av funktionen att bli innehållet i cellen i skärningspunkten mellan den tredje raden och den andra kolumnen i denna grupp.

Tyvärr, ofta i beräkningar, är rad- eller kolumnnumret okänt. Då räddas MATCH-funktionen.

MATCH-funktion

Handlingen för MATCH-funktionen liknar åtgärden för INDEX-funktionen, men MATCH returnerar inte cellvärdet utan cellpositionen i det angivna intervallet. I allmänhet kommer formeln att se ut:

MATCH (search_value; array; [match_type]), där

  • search_value – detta är vad du behöver hitta. Här kan inte bara vara en text eller ett numeriskt värde, utan också ett logiskt, liksom en länk till en cell.
  • array – detta är cellområdet som visas.
  • match_type – detta är en valfri parameter som kan ställas in på “1”, “0” eller “-1”. Den säger funktionen vilket värde som ska hittas: exakt eller ungefärligt. Om data i arrayen minskar i stigande ordning, indikerar parameter “1” att det är nödvändigt att välja det maximala värdet mindre än eller lika med det önskade. Parametern “-1” specificeras för en minskande matris. I detta fall väljer funktionen det minsta värdet som är större än eller lika med det önskade. Parameter “0” hittar det första värdet lika med det önskade värdet. Det är detta mapptypsvärde som används i kombinationen av INDEX- och MATCH-funktionerna.

För att illustrera funktionen för denna funktion, överväga ett exempel. I tabellen ovan hittar vi i kolumnen ”Product Number” vad kontot kommer att vara för produktnummer 2. För detta skriver vi formeln: =MATCH(2;B2:B6;0)
Function MATCH
Varför måste jag känna till elementets position i en tabell? Det visar sig att det är mycket bekvämt att använda detta värde som ett argument till INDEX-funktionen.

Kombination av INDEX och MATCH-funktioner

Om vi analyserar båda funktionerna blir det tydligt att INDEX-funktionen söker efter cellvärden efter radnummer och kolumnnummer. Samtidigt hittar MATCH-funktionen radnummer och kolumnnummer. Således, om du använder dessa två funktioner i samma formel, hittar MATCH den relativa positionen för önskat värde, och INDEX-funktionen använder dessa värden och returnerar innehållet i de beräknade cellerna.

Tänk på ett exempel. I tabellen ovan hittar vi produktens bredd vid nummer 3. För detta använder vi följande formel:=INDEX($D$2:$D$6;MATCH(3;$B$2:$B$6;0))
Combination of INDEX and MATCH
Det rekommenderas att du använder absoluta länkar för dessa formler så att sökintervallen inte förväxlas när du kopierar formler.

Varför är det bättre att använda en kombination av INDEX- och MATCH-funktioner snarare än att använda VLOOKUP-funktionen? Först låter en formel baserat på INDEX och MATCH söka efter önskad data i det angivna intervallet från vänster till höger och från höger till vänster, medan när du använder VLOOKUP bör det önskade värdet alltid ligga längst till vänster i området.

För det andra, när du använder VLOOKUP-funktionen, kan du inte ta bort eller lägga till kolumner i tabellen. Annars kommer resultatet av formeln att vara felaktigt. Detta beror på att syntaxen för denna funktion innebär att specificera hela intervallet och det specifika kolumnnumret från vilket data kommer att tas. När du använder INDEX- och MATCH-funktionerna kan du ta bort eller lägga till så många kolumner du vill.

För det tredje, när man använder en kombination av INDEX- och MATCH-funktioner, finns det ingen begränsning för storleken på sökvärdet, medan VLOOKUP-funktionen begränsar antalet tecken i sökvärdet till 255 tecken.

För det fjärde, när du utför beräkningar i stora dataarrayer, använder INDEX- och MATCH-funktionerna avsevärt den tid som krävs för att söka efter värden jämfört med VLOOKUP-funktionen. Detta beror på att VLOOKUP-funktionen anropas för varje värde från det angivna dataområdet. Däremot utför en formel baserad på INDEX- och MATCH-funktionerna helt enkelt en sökning och returnerar ett resultat.

IF-funktion kombinerad med AND-funktion

IF-booleska funktionen kontrollerar om innehållet i cellerna uppfyller vissa villkor. Om den matchar, returnerar funktionen ett av de användardefinierade värdena. I händelse av avvikelse, returnerar ett annat inställt värde. Syntaxen för funktionen är som följer:

=IF(logisk_uttryck; värde_if_true; värde_if_false), där

  • logisk_uttryck – detta är de uppgifter som måste kontrolleras och villkoren för verifiering. Till exempel А2>10.
  • värde_if_true – detta är den post som kommer att visas om cellvärdet uppfyller det angivna villkoret.
  • värde_if_false – detta är posten som kommer att visas om cellvärdet inte uppfyller det givna villkoret.

Många användare som har utfört komplexa ekonomiska beräkningar vet hur svårt det är att förstå formler som använder kapslade slingor med IF-uttalandet. Det visar sig att dessa formler kan förenklas om du använder IF-funktionen i kombination med AND/OR-funktionerna. Kombinationen av AND- och IF-funktionerna fungerar enligt följande. Om A = 1 och A = 2 returnerar formeln värdet B, annars returnerar det C. För OR-funktionen fungerar inte formeln så. Om A = 1 eller A = 2, returnerar formeln värdet B, annars – värdet C.

Tänk på ett exempel. Skapa en formel som kontrollerar innehållet i cell C2, lika med 110. Om antalet ligger i intervallet 90 till 300, blir resultatet 1, annars 0. Formeln ser ut så här: =IF(AND(C2>=C4;C2<=C5);C7;C8)
IF function
Som framgår av figuren kommer ett värde lika med 1 att placeras i cellen med resultatet av funktionen.Talet 110 ligger verkligen i området 90 till 300.

Det totala värdet kan inte bara vara ett tal utan också en text, till exempel orden “JA” eller “Nej” eller någon annan fras.

Kombination av SUM- och OFFSET-funktioner

Själva OFFSET-funktionen används sällan, men att kombinera den med andra funktioner kan ge mycket goda resultat. Den kombinerade användningen av SUM- och OFFSET-funktionerna till exempel tillåter dig att skapa ganska komplexa formler när du skapar en dynamisk funktion som summerar ett variabelt antal celler. För att lösa detta problem används SUM-funktionen, och i stället för den slutliga cellen anges OFFSET-funktionen, det vill säga formeln blir dynamisk.

Den resulterande formeln ser ut så här:

= SUM (start_range: OFFSET (referens, antal rader, antal kolumner)), där

  • start_range – detta är utgångspunkten för det cellområde som används av SUM-funktionen.
  • referens – detta är en referens till cellen som används för att beräkna slutpunkten för intervallet.
  • antal rader – detta är antalet rader som används för att beräkna cellförskjutningen. Detta värde kan vara positivt, negativt och lika med noll.
  • antal kolumner – detta är antalet kolumner till höger eller vänster om den givna cellreferensen. Används vid beräkning av offset. När det flyttas till vänster är detta värde negativt. När det flyttas till höger är värdet positivt. Om de beräknade uppgifterna finns i samma kolumn är denna parameter noll.

Tänk på ett exempel. Det finns en tabell med serienumren för månadens dagar och inkomsterna varje dag. Varje dag uppdateras informationen i tabellen genom att lägga till en rad med inkomsterna per dag. Vi komponerar formeln i den slutliga cellen efter den fjärde försäljningsdagen: =SUM(B2:OFFSET(B6;-1;0))
OFFSET functions
För att lägga till information om den femte försäljningsdagen måste du lägga till en tom rad efter den fjärde dagen och ange nödvändig information i den. I det här fallet kommer formeln att ha formen: =SUM(B2:OFFSET(B7;-1;0)), och den totala inkomsten kommer att öka med det inkomster som erhålls den femte dagen.

SUMIF- och COUNTIF-funktioner

Dessa två funktioner används ofta i finansiella beräkningar. SUMIF hittar summan i ett givet antal celler efter ett visst tillstånd. COUNTIF räknar alla celler som matchar det givna villkoret.
SUMIF-funktionen har följande struktur:

SUMIF(intervall; kriterium; sum_range), där

  • intervall – detta är en matris med celler där överensstämmelsen med de specificerade kriterierna kommer att kontrolleras.
  • kriterium – detta är ett villkor för att välja celler. Detta kan vara ett nummer, text, uttryck eller cellreferens.
  • sum_range – Detta är en valfri parameter. Om du inte anger det kommer summeringen att utföras med hänsyn till argumentet “Intervall “.

Handlingen med denna funktion är lätt att förstå med ett exempel. Det finns en tabell med en lista över varor och deras kvantitet. Du måste hitta mängden “Product 1”. Formeln för beräkningen kommer att vara följande: =SUMIF(B2:B7;”Product 1″;C2:C7)
SUMIF functions
I samma exempel kan du beräkna antalet produkter exklusive ”Product 1” med hjälp av denna formel: =SUMIF(B2:B7;”<>Product 1″;C2:C7)

COUNTIF-funktionen har följande struktur:

COUNTIF (intervall; kriterium), där

intervall och kriterium liknar SUMIF.

I samma exempel räknar vi antalet rader “Product 1”. Formeln för beräkning kommer att se ut: =COUNTIF(B2:B7;”Product 1″).
COUNTIF functions
Med denna formel kan du beräkna antalet rader som uppfyller vissa villkor. För att i den här tabellen till exempel beräkna antalet rader med antalet produkter större än 10, måste du skapa en sådan formel : =COUNTIF(C2:C7;”>10″).

Funktion MODE.SNGL

Den statistiska funktionen MODE.SNGL och dess föråldrade version MODE hittar det oftast förekommande värdet i dataområdet (array) och returnerar detta värde. Funktionssyntax : =MODE((nummer1; [nummer2]; …), där

  • nummer1– Detta är ett nödvändigt argument, som är ett nummer, en hänvisning till en siffercell, en matris eller ett intervall av celler.
  • nummer2 – valfritt argument. Sådana argument kan vara från 1 till 255.

Argument som inte kan konverteras till siffror orsakar ett formelfel. Om det inte finns identiska nummer i det angivna intervallet blir resultatet av funktionen felvärdet # N/A.

Varför kan jag använda den här funktionen i finansiella beräkningar? Till exempel för att ta reda på vilka produkter som oftast köps på grundval av sammanfattande data. Urvalskriteriet kan vara priset på produkten, storlek, volym, mått och så vidare. Som illustration betraktar vi en tabell som återspeglar försäljning av varor med försäljningsdatum, produktens storlek och dess pris. För att ta reda på vilka storlekar av produkter som oftast säljs använder vi formeln: =MODE(C2:C6)
MODE function
Figuren visar att produkter med en storlek 36 är i efterfrågan. Således bestämmer MODE-funktionen den mest frekventa händelsen i intervallet av händelser.