Mukati
Sezvo mutemo, vanhu vanoshandisa chete nhamba shoma yeExcel mafomula, kunyangwe paine akati wandei mabasa ayo vanhu vanokanganwa zvisizvo. Zvisinei, vanogona kubatsira zvikuru mukugadzirisa matambudziko akawanda. Kuti uzive nezve masvomhu mabasa, unofanirwa kuvhura iyo "Formulas" tebhu uye wotsvaga iyo "Math" chinhu ipapo. Tichatarisa mamwe emabasa aya nekuti imwe neimwe yemafomula anogoneka muExcel ine mashandisiro ayo anoshanda.
Mabasa eMasvomhu enhamba dzisina kurongeka uye zvinobvira musanganiswa
Aya mabasa anokutendera kuti ushande nenhamba dzisina kurongeka. Ndinofanira kutaura kuti hapana nhamba dzakangoitika zvechokwadi. Ese anogadzirwa zvinoenderana nemamwe mapatani. Zvakadaro, pakugadzirisa matambudziko akaiswa, kunyangwe jenareta yenhamba dzisina kurongeka inogona kubatsira zvakanyanya. Masvomhu anoita manhamba asina kurongeka anosanganisira PAKATI PANYAYA, SLCHIS, CHISLCOMB, CHOKWADI. Ngatitarisei mumwe nomumwe wavo zvakadzama.
basa PAKATI PANYAYA
Ichi ndicho chimwe chezvinoshandiswa zvakanyanya muchikamu ichi. Inogadzira nhamba isina kurongeka inokodzera mukati meimwe muganhu. Zvakakosha kufunga kuti kana huwandu hwakanyanya kutetepa, nhamba dzinogona kunge dzakafanana. Syntax iri nyore: =RANDBETWEEN(mutengo wakaderera; kukosha kwepamusoro). Maparamita anopfuudzwa nemushandisi anogona kunge ari ese manhamba nemasero ane dzimwe nhamba. Kunosungirwa kupinza kwegakava rega rega.
Nhamba yekutanga mumabhuraketi ndiyo nhamba shoma pazasi iyo jenareta isingashande. Maererano naizvozvo, yechipiri ndiyo nhamba yepamusoro. Kupfuura izvi zvakakosha, Excel haizotsvaga nhamba isina kurongeka. Nharo dzinogona kunge dzakafanana, asi munyaya iyi nhamba imwe chete ichagadzirwa.
Nhamba iyi inogara ichichinja. Nguva imwe neimwe iyo gwaro rinogadziriswa, kukosha kwakasiyana.
basa SLCHIS
Iri basa rinogadzira kukosha kusingatarisirwi, miganhu iyo inogadziriswa pakarepo pamwero we0 uye 1. Unogona kushandisa maitiro akawanda uchishandisa basa iri, uyewo kushandisa rimwe basa kakawanda. Muchiitiko ichi, hapazove nekugadziriswa kwekuverenga.
Haufanire kupfuudza mamwe ma parameter kune iyi basa. Naizvozvo, syntax yayo iri nyore sezvinobvira: =SUM(). Zvinogonekawo kudzosa mafractional random values. Kuti uite izvi, unofanirwa kushandisa basa SLCHIS. Iyo formula ichave: =RAND()*(max muganho-min muganho)+min muganho.
Kana iwe ukawedzera fomula kune ese maseru, saka unogona kuseta chero nhamba yenhamba dzisina kurongeka. Kuti uite izvi, iwe unofanirwa kushandisa iyo autofill marker (iyo sikweya iri pazasi kuruboshwe kona yesero yakasarudzwa).
basa NUMBERCOMB
Basa iri nderebazi remasvomhu rakadai se combinatorics. Inotara huwandu hwemisanganiswa yakasarudzika yeimwe nhamba yezvinhu mumuenzaniso. Inoshandiswa zvakanyanya, semuenzaniso, mukutsvaga kwenhamba mune socionomic sainzi. Syntax yebasa iri seizvi: =NUMBERCOMB(set size, nhamba yezvinhu). Ngatitarisei nharo idzi zvakadzama:
- Saizi yakatarwa ndiyo nhamba yese yezvinhu mumuenzaniso. Inogona kuva nhamba yevanhu, zvinhu, nezvimwewo.
- Huwandu hwezvinhu. Iyi parameter inoreva chinongedzo kana nhamba inoratidza huwandu hwezvinhu zvinofanirwa kubuda. Chinodiwa chikuru chekukosha kwenharo iyi ndechekuti inofanira kugara iri diki pane yapfuura.
Kupinza nharo dzese kunodiwa. Pakati pezvimwe zvinhu, ivo vese vanofanirwa kuve vakanaka muhunhu. Ngatitorei muenzaniso mudiki. Ngatitii tine zvinhu zvina – ABCD. Basa nderekutevera: kusarudza masanganiswa nenzira yekuti nhamba dzirege kudzokorora. Nekudaro, nzvimbo yavo haina kuverengerwa. Ndiko kuti, chirongwa hachizove nehanya kana chiri musanganiswa weAB kana BA.
Ngatiisei ikozvino fomula yatinoda kuti tiwane aya masanganiswa: =NUMBERCOMB(4). Nekuda kweizvozvo, 6 inogoneka misanganiswa icharatidzwa, inosanganisira maitiro akasiyana.
INVOICE basa
Mumasvomhu, pane chinhu chinonzi factorial. Ukoshi uhwu hunoreva nhamba inowanikwa nekuwanza manhamba ese akasikwa kusvika panhamba iyi. Semuenzaniso, iyo factorial yenhamba 3 ichava nhamba 6, uye iyo factorial yenhamba 6 ichava nhamba 720. The factorial inoratidzwa neinodanidzira. Uye kushandisa basa CHIKAMU zvinova zvinogoneka kuwana factorial. Syntax yeFormula: = CHOKWADI(nhamba). Iyo factorial inoenderana nehuwandu hwezvinogoneka musanganiswa wemakoshero museti. Semuenzaniso, kana tiine zvinhu zvitatu, saka huwandu hwehuwandu hwekusanganiswa munyaya iyi huchava 6.
Nhamba yekushandura mabasa
Kushandura nhamba kuita kwemamwe maoperation nawo asina hukama nesvomhu. Semuenzaniso, kushandura nhamba kuita chiRoman, ichidzosa module yayo. Aya maficha anoitwa uchishandisa mabasa ABS uye ROMAN. Ngatitarisei zvakadzama.
ABS basa
Tinokuyeuchidza kuti modulus chinhambwe kusvika zero pane coordination axis. Kana iwe uchifungidzira mutsara wakatwasuka une manhamba akaiswa pairi mukuwedzera kwe1, zvino unogona kuona kuti kubva panhamba 5 kusvika zero uye kubva panhamba -5 kusvika zero pachava nenhamba imwechete yemasero. Chinhambwe ichi chinonzi modulus. Sezvatinoona, modulus ye -5 ndeye 5, sezvo zvichitora masero mashanu kuti apfuure kusvika pa zero.
Kuti uwane modulus yenhamba, unofanirwa kushandisa iyo ABS basa. Syntax yaro iri nyore kwazvo. Zvakakwana kunyora nhamba mumabhuraketi, mushure mezvo kukosha kuchadzorerwa. Syntax inoti: =ABS(nhamba). Kana iwe ukaisa fomula =ABS(-4), zvino mhedzisiro yemabasa aya ichave 4.
ROMAN basa
Iri basa rinoshandura nhamba mumutauro wechiArabic kuita chiRoman. Iyi fomula ine nharo mbiri. Yekutanga inosungirwa, uye yechipiri inogona kusiiwa:
- Number. Iyi inhamba yakananga, kana chirevo chesero rine kukosha muchimiro ichi. Chinodiwa chakakosha ndechekuti iyi parameter inofanirwa kunge yakakura kupfuura zero. Kana iyo nhamba iine manhamba mushure meiyo decimal point, zvino mushure mekushandurwa kwayo kuchimiro cheRoma, chikamu chechikamu chinongogurwa.
- Format. Nharo iyi haichadiwi. Inotsanangura chimiro chemharidzo. Nhamba imwe neimwe inoenderana neimwe kutaridzika kwenhamba. Pane zvakawanda zvingasarudzwa zvinogona kushandiswa senharo iyi:
- 0. Muchiitiko ichi, kukosha kunoratidzwa mumhando yayo yekare.
- 1-3 - marudzi akasiyana ekuratidza nhamba dzeRoma.
- 4. Nzira yakareruka yekuratidza nhamba dzechiRoman.
- Chokwadi neNhema. Mumamiriro ezvinhu ekutanga, nhamba yacho inoratidzwa nenzira yakajairika, uye yechipiri - yakapfava.
SUBTOTAL basa
Iri ibasa rakanyanya kuoma iro rinokupa iwe kugona kupfupisa subtotals zvichienderana nehunhu hunopfuudzwa kwairi senharo. Iwe unogona kugadzira basa iri kuburikidza neyakajairwa mashandiro eExcel, uye zvinokwanisika kuishandisa nemaoko.
Iri ibasa rakaoma kushandisa, saka tinofanira kutaura nezvazvo zvakasiyana. Syntax yebasa iri ndeiyi:
- Feature number. Nharo iyi inhamba iri pakati pa1 na 11. Nhamba iyi inoratidza kuti nderipi basa richashandiswa kupfupisa huwandu hwataurwa. Somuenzaniso, kana tichida kuwedzera nhamba, saka tinofanira kutsanangura nhamba 9 kana 109 seyekutanga parameter.
- Batanidza 1. Ichi zvakare chinodiwa parameter chinopa chinongedzo kune huwandu hwakatorwa mukupfupikisa. Sezvo mutemo, vanhu vanoshandisa mutsara mumwe chete.
- Link 2, 3… Inotevera inouya imwe nhamba yezvibatanidza kune renji.
Huwandu hwenharo dzenharo dzinogona kuve neiri basa i30 (nhamba yebasa + 29 mareferensi).
Chinyorwa chakakosha! Nhamba dzeNested hadzina hanya. Ndiko kuti, kana basa racho rakatoshandiswa mune imwe nhanho SUBTOTALS, inofuratirwa nepurogiramu.
Ziva zvakare kuti kushandisa basa iri kune subtotal horizontal arrays yedata hakukurudzirwe sezvo isina kugadzirirwa izvozvo. Muchiitiko ichi, zvigumisiro zvingave zvisina kururama. Function SUBTOTALS kazhinji inosanganiswa ne autofilter. Ngatitii tine dataset yakadaro.
Ngatiedzei kuisa iyo autofilter pairi uye tosarudza chete maseru akanyorwa se "Chigadzirwa1". Tevere, tinoisa basa rekuona kushandisa basa SUBTOTALS subtotal yezvinhu izvi. Pano isu tinoda kushandisa kodhi 9 sezvakaratidzwa mune iyo skrini.
Kupfuurirazve, basa racho rinosarudza otomatiki iwo mitsara isina kuisirwa muchero mhedzisiro uye haisanganisire iwo mukuverenga. Izvi zvinokupa zvimwe zvakawanda zvingasarudzwa. Nenzira, kune yakavakirwa-muExcel basa inonzi Subtotals. Ndeupi musiyano uripo pakati pezvishandiso izvi? Icho chokwadi ndechekuti basa racho rinobvisa otomatiki kubva pakusarudza mitsara yese isiri kuratidzwa parizvino. Izvi hazvifungidzire kodhi function_number.
Nenzira, chishandiso ichi chinokubvumira kuita zvinhu zvakawanda, uye kwete kungoona huwandu hwehukoshi. Heino rondedzero yemakodhi ane mabasa ayo anoshandiswa kupfupisa subtotals.
1 – MOYO;
2 – COUNT;
3 – SCHÖTZ;
4 – MAX;
5 MINUTES;
6 – PRODUCT;
7 – STDEV;
8 – STANDOTKLONP;
9 – SUM;
10 – DISP;
11 - DISP.
Iwe unogona zvakare kuwedzera zana kune idzi nhamba uye mabasa achafanana. Asi pane musiyano mumwe. Musiyano ndewekuti muchiitiko chekutanga, masero akavanzika haafungidzirwe, asi mune yechipiri ivo vachadaro.
Mamwe mabasa emasvomhu
Masvomhu isainzi yakaoma kunzwisisa iyo inosanganisira akawanda mafomula emhando dzakasiyana dzemabasa. Excel inosanganisira zvinenge zvese. Ngatitarisei zvitatu zvacho chete: SIGN, Pi, PRODUCT.
SIGN basa
Nebasa iri, mushandisi anogona kuona kana nhamba yacho yakanaka kana yakaipa. Inogona kushandiswa, semuenzaniso, kuunganidza vatengi kune avo vane zvikwereti mubhangi uye avo vasina kutora chikwereti kana kudzosera panguva ino.
Basa re syntax rakaita seizvi: = CHIRATIDZO(nhamba). Tinoona kuti pane nharo imwe chete, iyo yekuisa iyo inosungirwa. Mushure mekutarisa nhamba, basa rinodzorera kukosha -1, 0, kana 1, zvichienderana nechiratidzo chei. Kana iyo nhamba ikava yakaipa, zvino ichava -1, uye kana iri positive - 1. Kana zero yakabatwa senharo, zvino inodzorerwa. Basa rinoshandiswa pamwe chete nebasa IF kana mune chero imwe nyaya yakafanana kana iwe uchida kutarisa nhamba.
basa Pi
Nhamba PI ndiyo ine mukurumbira wemasvomhu isingachinji, inoenzana ne3,14159 … Uchishandisa basa iri, unogona kuwana vhezheni yakatenderedzwa yenhamba iyi kusvika kunzvimbo gumi neina. Iyo haina nharo uye ine inotevera syntax: =PI().
basa PRODUCT
Basa rakafanana nemusimboti ku SUM, inongoverengera chigadzirwa chenhamba dzese dzakapfuudzwa kwairi senharo. Iwe unogona kutsanangura kusvika ku255 nhamba kana mitsara. Izvo zvakakosha kufunga kuti basa racho haritarise zvinyorwa, zvine musoro uye chero humwe hunhu husingashandiswe mukuita arithmetic. Kana iyo boolean value ikashandiswa senharo, ipapo kukosha TRUE zvinoenderana nechimwe, uye kukosha NHEMA – zero. Asi zvakakosha kunzwisisa kuti kana pane boolean kukosha muhuwandu, zvino chigumisiro chichave chisina kunaka. Formula syntax ndeiyi inotevera: =PRODUCT(nhamba 1; nhamba 2…).
Tinoona kuti nhamba dzinopiwa pano dzakaparadzaniswa nesemicolon. Nharo inodiwa ndiyo imwe - nhamba yekutanga. Muchidimbu, haugone kushandisa basa iri nenhamba shoma yehukoshi. Ipapo iwe unofanirwa kuramba uchiwedzera nhamba dzese uye maseru. Asi kana paine akawanda acho, ipapo mune manual mode zvinotora nguva yakawanda. Kuti uichengetedze, pane basa PRODUCT.
Nokudaro, tine nhamba huru yemabasa ayo anoshandiswa kashoma, asi panguva imwechete anogona kushandiswa zvakanaka. Usakanganwa kuti mabasa aya anogona kusanganiswa pamwe chete. Nekuda kweizvozvo, huwandu hwemikana inovhura inowedzerwa zvakanyanya.