Mukati
Microsoft Excel inopa akakura akasiyana siyana emabasa ayo anotendera iwe kubata nemasvomhu, ehupfumi, emari uye mamwe mabasa. Iyo purogiramu ndeimwe yezvishandiso zvikuru zvinoshandiswa mumasangano madiki, epakati uye makuru ekuchengetedza marudzi akasiyana e accounting, kuita macalculation, etc. Pazasi isu tichatarisa mabasa emari anonyanya kudiwa muExcel.
Kupinza basa
Kutanga, ngatirangarirei nzira yekuisa basa musero retafura. Iwe unogona kuita izvi nenzira dzakasiyana:
- Mushure mekusarudza sero yaunoda, tinya pane icon "fx (Isa basa)" kuruboshwe rweformula bar.
- Kana kuti chinja kune tab "Nzira" uye tinya bhatani rakafanana riri mukona yekuruboshwe yeribhoni rechirongwa.
Pasinei nesarudzo yakasarudzwa, hwindo rekuisa basa rinovhurwa, raunofanirwa kusarudza chikamu “Mari”, sarudza pane yaunoda mushandisi (semuenzaniso, MARI), wobva wadzvanya bhatani OK.
Hwindo richaonekwa pachiratidziro rine nharo dzebasa raunoda kuzadza, wobva wadzvanya bhatani rekuti OK kuti uwedzere kune yakasarudzwa sero uye uwane mhedzisiro.
Iwe unogona kutsanangura data nemaoko uchishandisa kiyi yekhibhodi (chaiwo kukosha kana maseru mareferensi), kana nekuisa mumunda wakatarisana nenharo yaunoda, sarudza zvinhu zvinoenderana patafura pachayo (maseru, huwandu hwemasero) uchishandisa bhatani rekuruboshwe. kana zvichibvumirwa).
Ndokumbira utarise kuti dzimwe nharo dzinogona kusaratidzwa uye unofanirwa kudzika pasi kuti uzviwane (uchishandisa masiraidhi akatwasuka kurudyi).
Imwe nzira
Kuve mune tab "Nzira" unogona kudzvanya bhatani “Mari” muboka "Function Library". Rondedzero yezvisarudzo zviripo ichavhurwa, pakati payo kungodzvanya pane yaunoda.
Mushure meizvozvo, hwindo rine basa rekuita nharo rekuzadza rinobva ravhurika.
Yakakurumbira mabasa emari
Zvino zvataona kuti basa rinopinzwa sei musero muExcel spreadsheet, ngatienderei kune rondedzero yevashandi vezvemari (inounzwa mune alfabheti).
BS
Mushandi uyu anoshandiswa kuverenga kukosha kweramangwana rekudyara zvichibva pamari yakaenzana nguva nenguva (nguva dzose) uye mubereko (nguva dzose).
Nharo dzinodiwa (maparameter) ekuzadza ndeaya:
- bheji - mubereko wenguva yacho;
- Kper - nhamba yese yenguva dzekubhadhara;
- Plt - kubhadhara nguva dzose kwenguva imwe neimwe.
Optional nharo:
- Ps ndiyo iripo (ikozvino) kukosha. Kana yasiiwa isina chinhu, kukosha kwakaenzana ne "0";
- Rudzi - inoti pano:
- 0 - kubhadhara pakupera kwenguva;
- 1 - kubhadhara pakutanga kwenguva
- kana munda ukasiiwa usina chinhu, unozoenda ku zero.
Izvo zvakare zvinogoneka kuisa nemaoko fomula yebasa nekukurumidza musero rakasarudzwa, nekupfuura basa uye nharo yekuisa windows.
Sintax yebasa:
=БС(ставка;кпер;плт;[пс];[тип])
Mhedzisiro musero uye chirevo chiri muformula bar:
VSD
Basa racho rinokutendera kuti uverenge chiyero chemukati chekudzoka kune akatevedzana emari inoyerera inoratidzwa munhamba.
Nharo inodiwa imwe chete - “Values”, iyo yaunoda kutsanangura rondedzero kana makonisheni emhando yemasero ane manhamba enhamba (inenge imwe isina kunaka uye imwe yakanaka nhamba) iyo kuverenga kuchaitwa.
Optional nharo - "Fungidziro". Pano, kukosha kunotarisirwa kunoratidzwa, iyo iri pedyo nemugumisiro VSD. Kana munda uyu ukasiiwa usina chinhu, kukosha kwekutanga kuchava 10% (kana 0,1).
Sintax yebasa:
=ВСД(значения;[предположение])
Mhedzisiro musero uye chirevo chiri muformula bar:
MARI
Uchishandisa mushandisi uyu, unogona kuverenga goho rezvibatiso zvinobhadharwa nguva nenguva.
Zvinodiwa nharo:
- date_acc - zuva rekubvumirana / kugadziriswa pazvivimbiso (zvino zvinonzi securities);
- Effective_date - zuva rekupinda musimba / kuregererwa kwezvivimbiso;
- bheji - gore negore coupon rate yezvivimbiso;
- mutengo - mutengo wezvivimbiso zve 100 rubles yechiso chakakosha;
- Kudzorera - huwandu hwekudzikinura kana kukosha kwekudzikinura kwezvibatiso. kune 100 rubles chiso kukosha;
- maitikiro - nhamba yekubhadhara pagore.
Argument "Basis" is kusarudza, inotsanangura kuti zuva rinoverengwa sei:
- 0 kana isina chinhu - American (NASD) 30/360;
- 1 - chaiyo / chaiyo;
- 2 - chaiyo/360;
- 3 - chaiyo/365;
- 4 - European 30/360.
Sintax yebasa:
=ДОХОД(дата_согл;дата_вступл_в_силу;ставка;цена;погашение;частота;[базис])
Mhedzisiro musero uye chirevo chiri muformula bar:
MVSD
Mushandi anoshandiswa kuverenga muyero wemukati wekudzoka kwehuwandu hwekuyerera kwemari nguva nenguva zvichienderana nemutengo wekusimudza mari, pamwe nechikamu chemari yakadzoserwa.
Basa rine chete zvaida nharo, zvinosanganisira:
- Zvinokosha - zvisina kunaka (kubhadhara) uye nhamba dzakanaka (marisiti) anoratidzwa, anoratidzwa seanorongedzerwa kana sero referensi. Saizvozvo, kamwechete kwakanaka uye imwe yakashata nhamba kukosha inofanira kuratidzwa pano;
- Rate_finance - mubereko wakabhadharwa pamari iri kutenderera;
- Rate _reinvest - mubereko wekudzoreredza zvinhu zvazvino.
Sintax yebasa:
=МВСД(значения;ставка_финанс;ставка_реинвест)
Mhedzisiro musero uye chirevo chiri muformula bar:
INORMA
Mushandi anotendera iwe kuti uverenge mubereko wemari yezvibatiso zvakadyarwa zvizere.
Mabasa nharo:
- date_acc - zuva rekugadziriswa kwezvivimbiso;
- Effective_date - kuchengetedzwa zuva rekudzikinura;
- inivhesitimendi - iyo mari inoiswa muzvibatiso;
- Kudzorera - mari inogamuchirwa pakudzikinurwa kwezvibatiso;
- nharo "Basis" kana riri basa MARI inosarudzika.
Sintax yebasa:
=ИНОРМА(дата_согл;дата_вступл_в_силу;инвестиция;погашение;[базис])
Mhedzisiro musero uye chirevo chiri muformula bar:
PLT
Iri basa rinotara huwandu hwekubhadhara nguva nenguva pachikwereti zvichienderana nekuwanda kwekubhadhara uye mubereko.
Zvinodiwa nharo:
- bheji - mubereko wenguva yechikwereti;
- Kper - nhamba yese yenguva dzekubhadhara;
- Ps ndiyo iripo (ikozvino) kukosha.
Optional nharo:
- Bs - kukosha kweramangwana (chiyero mushure mekubhadhara kwekupedzisira). Kana munda ukasiiwa usina chinhu, unozogara uchiita "0".
- Rudzi - pano iwe unotsanangura kuti mubhadharo uchaitwa sei:
- "0" kana isina kutaurwa - pakupera kwenguva;
- "1" - pakutanga kwenguva.
Sintax yebasa:
=ПЛТ(ставка;кпер;пс;[бс];[тип])
Mhedzisiro musero uye chirevo chiri muformula bar:
AKAKAMUCHIRWA
Inoshandiswa kutsvaga mari inozogamuchirwa nekukura kwezvivimbiso zvakaiswa mari.
Mabasa nharo:
- date_acc - zuva rekugadziriswa kwezvivimbiso;
- Effective_date - kuchengetedzwa zuva rekudzikinura;
- inivhesitimendi - mari yakasimwa mumasecurities;
- dhisikaundi - kuderedzwa mwero wezvivimbiso;
- "Basis" -Kusarudza nharo (ona basa MARI).
Sintax yebasa:
=ПОЛУЧЕНО(дата_согл;дата_вступл_в_силу;инвестиция;дисконт;[базис])
Mhedzisiro musero uye chirevo chiri muformula bar:
PS
Mushandisi anoshandiswa kutsvaga ikozvino (kureva, kusvika nhasi) kukosha kwekudyara, izvo zvinoenderana nenhevedzano yekubhadhara kweramangwana.
Zvinodiwa nharo:
- bheji - mubereko wenguva yacho;
- Kper - nhamba yese yenguva dzekubhadhara;
- Plt - kubhadhara nguva dzose kwenguva imwe neimwe.
Optional nharo - zvakafanana nezvebasa racho "PLT":
- Bs - kukosha kweramangwana;
- Rudzi.
Sintax yebasa:
=ПС(ставка;кпер;плт;[бс];[тип])
Mhedzisiro musero uye chirevo chiri muformula bar:
RANGANO
Mushandi achakubatsira kuwana mubereko wemari pa annuity (financial rendi) kwenguva 1.
Zvinodiwa nharo:
- Kper - nhamba yese yenguva dzekubhadhara;
- Plt - kubhadhara nguva dzose kwenguva imwe neimwe;
- Ps ndiko kukosha kwazvino.
Optional nharo:
- Bs - kukosha kweramangwana (ona basa PLT);
- Rudzi (ona basa PLT);
- Fungidziro - iyo inotarisirwa kukosha kwebheti. Kana zvisina kutaurwa, kukosha kwe10% (kana 0,1) kuchashandiswa.
Sintax yebasa:
=СТАВКА(кпер;;плт;пс;[бс];[тип];[предположение])
Mhedzisiro musero uye chirevo chiri muformula bar:
PRICE
Mushandi anokubvumira kuti uwane mutengo we 100 rubles yezita rekukosha kwezvivimbiso, iyo iyo periodic interest inobhadharwa.
Zvinodiwa nharo:
- date_acc - zuva rekugadziriswa kwezvivimbiso;
- Effective_date - kuchengetedzwa zuva rekudzikinura;
- bheji - gore negore coupon rate yezvivimbiso;
- mari yaunowana - mari yegore negore yezvivimbiso;
- Kudzorera - rudzikinuro kukosha kwezvivimbiso. kune 100 rubles chiso kukosha;
- maitikiro - nhamba yekubhadhara pagore.
Argument "Basis" kana ari mushandisi MARI is kusarudza.
Sintax yebasa:
=ЦЕНА(дата_согл;дата_вступл_в_силу;ставка;доход;погашение;частота;[базис])
Mhedzisiro musero uye chirevo chiri muformula bar:
ChPS
Uchishandisa basa iri, unogona kuona mambure azvino kukosha kwekudyara zvichibva pamutengo wekuderedzwa, pamwe nehuwandu hwemarisiti uye kubhadhara kweramangwana.
Mabasa nharo:
- bheji - mutengo wekuderedzwa kwe1 nguva;
- Meaning1 - mari yekubhadhara (negative values) uye risiti (yakanaka tsika) pakupera kwenguva imwe neimwe inoratidzwa pano. Munda unogona kuva ne254 values.
- Kana nharo muganhu "Kukosha 1" waneta, unogona kuenderera mberi nekuzadza zvinotevera - "Value2", "Value3" etc.
Sintax yebasa:
=ЧПС(ставка;значение1;[значение2];...)
Mhedzisiro musero uye chirevo chiri muformula bar:
mhedziso
chikamu “Mari” Excel ine anopfuura makumi mashanu mabasa akasiyana, asi mazhinji acho akajeka uye akatariswa zvishoma, ndosaka asingawanzo kushandiswa. Isu takafunga nezve 50 inonyanya kufarirwa, mumaonero edu.