21 Inobatsira Excel Zvimiro zveVatengesi Vepamhepo

Kushambadzira kweInternet inzvimbo inobhadhara zvinoshamisa yezviitwa zvevanhu, kunyanya munguva pfupi yapfuura, apo chero bhizinesi riri kufamba online pese pazvinogoneka. Uye zvisinei nekuti akawanda mabhizinesi maitiro anotungamirwa neakasarudzika zvirongwa, munhu haagari aine bhajeti rakakwana kuti azviwane, pamwe nenguva yekuzvigona.

Uye mhinduro yedambudziko iri iri nyore kwazvo - yakanaka yekare Excel, iyo yaunogona kuchengetedza inotungamira dhatabhesi, matsamba ekutumira, kuongorora kushambadzira kwekuita, kuronga bhajeti, kuita tsvakurudzo uye kuita mamwe mabasa anodiwa mubasa iri rakaoma. Nhasi tichajairana ne21 Excel mabasa ayo anoenderana nemushambadzi wese weInternet. Tisati tatanga, ngatinzwisise mamwe akakosha pfungwa:

1. Syntax. Izvi ndizvo zvikamu zvinoumba basa uye kuti rinonyorwa sei uye munhevedzano ipi zvikamu izvi zvinovakwa. Kazhinji, syntax yechero basa yakakamurwa kuita zvikamu zviviri: zita rayo pacharo uye nharo - izvo zvinosiyanisa izvo basa rinogamuchira kuti riwane mhedzisiro kana kuita chimwe chiitiko. Usati wanyora fomula, unofanirwa kuisa chiratidzo chakaenzana, icho muExcel chinoratidza hunhu hwekuisa kwayo.
2. Nharo dzinogona kunyorwa mune zvese nhamba uye zvinyorwa. Uyezve, iwe unogona kushandisa vamwe vashandisi senharo, izvo zvinokutendera kuti unyore yakazara-yakazara algorithms muExcel. Nharo yatora kukosha inonzi function parameter. Asi kazhinji mazwi maviri aya anoshandiswa seanofanana. Asi kutaura zvazviri, pane musiyano pakati pavo. Nharo yenharo inotanga nebhuraketi rakavhurika, rakapatsanurwa nesemicolon, uye bhuroka renharo rinopera nebhureketi rakavharwa.

Ready function muenzaniso − =SUM(A1:A5). Zvakanaka, totanga here?

VLOOKUP basa

Nechinhu ichi, mushandisi anogona kuwana ruzivo rwunoenderana neimwe nzira uye oishandisa mune imwe fomula kana kuinyora mune imwe sero. VPR ichidimbu chinomirira "Vertical View". Iyi fomula yakaoma kunzwisisa ine nharo ina:

1. Ukoshi hunodiwa. Uku ndiko kukosha uko kutsvaga kweruzivo rwatinozoda kunoitwa. Inoshanda sekero yesero kana kukosha kwayo pachayo kana kudzoserwa neimwe fomula.
2. Tafura. Iyi ndiyo nzvimbo yaunoda kutsvaga ruzivo. Kukosha kunodiwa kunofanirwa kunge kuri muchikamu chekutanga chetafura. Iko kukosha kwekudzoka kunogona kuve zvachose mune chero sero inosanganisirwa muchikamu ichi.
3. Column nhamba. Iyi ndiyo ordinal nhamba (tarisisa - kwete kero, asi nhamba ye ordinal) yekoramu ine kukosha.
4. Interval viewing. Iyi ndiyo boolean kukosha (ndiko kuti, pano iwe unofanirwa kuisa fomula kana kukosha kunoburitsa TRUE or KUNYEPA), iyo inoratidza kuti ruzivo runofanira kuumbwa sei. Kana iwe ukapasa iyi nharo kukosha TRUE, zvino zviri mukati memasero zvinofanira kurongwa mune imwe yenzira mbiri: alfabheti kana kukwira. Muchiitiko ichi, fomula ichawana kukosha kwakanyanya kufanana neiri kutsvakwa. Kana iwe uchitsanangura senharo KUNYEPA, ipapo kukosha chaiko chete ndiko kuchatsvagwa. Mumamiriro ezvinhu aya, kurongeka kwekoramu data hakuna kukosha zvakanyanya.

Nharo yekupedzisira haina kunyanya kukosha kushandisa. Ngatipei mimwe mienzaniso yekuti basa iri rinogona kushandiswa sei. Ngatitii tine tafura inotsanangura huwandu hwekudzvanya pamibvunzo yakasiyana. Tinoda kuziva kuti vangani vakaitirwa chikumbiro "kutenga piritsi".

Mune yedu formula, isu taitsvaga chete izwi rekuti "tablet", iyo yatinoisa seyaidiwa kukosha. Iyo "tafura" nharo pano seti yemasero anotanga nesero A1 uye anopera nesero B6. Nhamba yekoramu munyaya yedu ndeye 2. Mushure mokunge tapinda maparameter ose anodiwa mufomu, tine mutsara unotevera: =VLOOKUP(C3;A1:B6;2).

Mushure mekunge tazvinyora muchitokisi, takawana mhedzisiro inoenderana nenhamba yezvikumbiro zvekutenga piritsi. Iwe unogona kuzviona mune iyo skrini iri pamusoro. Muchiitiko chedu, takashandisa basa racho VPR nezviratidzo zvakasiyana zvenharo yechina.

Pano takaisa nhamba 900000, uye chimiro chacho chakawana kukosha kwepedyo kune iyi uye yakabudisa mubvunzo we "kutenga motokari". Sezvatinoona, iyo "interval lookup" nharo ine kukosha TRUE. Kana tikatsvaga nenharo imwe chete iyo inonzi FALSE, saka tinoda kunyora nhamba chaiyo seyokoshi yekutsvaga, sezviri mumufananidzo uyu.

Sezvatinoona, basa VPR ine mikana yakafara kwazvo, asi, hongu, yakaoma kunzwisisa. Asi vamwari havana kupisa hari.

KANA basa

Iri basa rinodiwa kuti uwedzere zvimwe zvinhu zvehurongwa kuspredishiti. Inobvumira mushandisi kuti atarise kana shanduko inosangana nemamwe maitiro. Kana hongu, saka basa rinoita chimwe chiito, kana kwete, chimwe. Syntax yebasa iri inosanganisira nharo dzinotevera:

1. Direct boolean kutaura. Ichi ndicho chiero chinofanira kutariswa. Semuenzaniso, kunyangwe kunze kuri pasi pe zero kana kwete.
2. Iyo data yekugadzirisa kana chiyero chiri chokwadi. Chimiro hachigoni kungova manhamba. Iwe unogona zvakare kunyora tambo yemavara inodzoserwa kune imwe fomula kana kunyorerwa sero. Zvakare, kana kukosha kuri kwechokwadi, unogona kushandisa fomula inozoita mamwe maverengero. Unogonawo kushandisa mabasa IF, izvo zvakanyorwa senharo kune rimwe basa IF. Muchiitiko ichi, tinogona kuisa algorithm yakazara-yakakwana: kana chirevo chinosangana nemamiriro acho ezvinhu, zvino tinoita chiito 1, kana chisingaiti, zvino tinotarisa kutevedzera chirevo 2. Uyezve, kunewo branching. Kana pane zvakawanda zveketani dzakadaro, ipapo mushandisi anogona kuvhiringidzika. Naizvozvo, zvichiri kukurudzirwa kushandisa macros kunyora yakaoma algorithms.
3. Kukosha kana nhema. Izvi zvakafanana chete kana chirevo chisingaenderane nemaitiro akapihwa munharo yekutanga. Muchiitiko ichi, iwe unogona kushandisa chaizvo nharo dzakafanana nezvakaitika kare.

Kuenzanisira, ngatitorei muenzaniso muduku.

Iyo formula inoratidzwa mune ino skrini inotarisa kana mari yezuva nezuva yakakura kupfuura 30000. Kana hongu, saka sero rinoratidza ruzivo rwekuti chirongwa chakapedzwa. Kana kukosha uku kuri kushoma kana kuenzana, ipapo chiziviso chinoratidzwa kuti chirongwa hachina kupera. Ziva kuti tinogara tichiisa tambo dzemavara mumakotesheni. Mutemo iwoyo unoshanda kune mamwe mafomati ese. Zvino ngatipei muenzaniso unoratidza mashandisirwo emabasa akawanda anested IF.

Isu tinoona kuti pane zvitatu zvinogoneka mhedzisiro yekushandisa iyi fomula. Uwandu hwehuwandu hwemhedzisiro iyo formula ine nested mabasa inogumira IF - 64. Unogonawo kutarisa kana sero risina chinhu. Kuita rudzi urwu rwecheki, pane yakakosha formula inonzi EPUSTO. Inokubvumira kutsiva basa refu IF, iyo inotarisa kana sero risina chinhu, neine fomula imwe yakapfava. Muchiitiko ichi, formula ichave:

basa ISBLANK returns inotora sero senharo, uye inogara ichidzosa kukosha kwe boolean. Function IF iri musimboti wezvimwe zvakawanda zvatichatarisa zvinotevera, nekuti vanoita basa rakakura kudaro mukushambadzira. Muchokwadi, kune akawanda awo, asi isu tichatarisa matatu nhasi: SUMMESLI, COUNTIF, IFERROR.

SUMIF uye SUMIFS mabasa

basa SUMMESLI inoita kuti zvikwanisike kupfupikisa chete aya data anosangana neimwe chirevo uye ari muhuwandu. Iri basa rine nharo nhatu:

1. Range. Iyi isheti yemaseru inoda kutariswa kuti ione kana paine maseru pakati pawo anofanana nezvakatsanangurwa.
2. Criterion. Iyi inharo inotsanangura maparamendi chaiwo achapfupikiswa maseru. Chero mhando yedata inogona kushanda sechiyero: sero, zvinyorwa, nhamba, uye kunyange basa (semuenzaniso, rine musoro). Zvakakosha kufunga kuti maitiro ane mavara uye masvomhu zviratidzo zvinofanirwa kunyorwa mumakotesheni.
3. Summation range. Nhaurirano iyi haidi kutsanangurwa kana huwandu hwehuwandu hwakafanana nehupamhi hwekuyedza nzira.

Ngatitorei muenzaniso mudiki kuenzanisira. Pano, tichishandisa basa racho, takawedzera zvikumbiro zvese zvine shanduko dzinopfuura zviuru zana.

Kune zvakare yechipiri shanduro yebasa iri, iyo yakanyorwa se SUMMESLIMN. Nekubatsira kwayo, maitiro akati wandei anogona kuverengerwa kamwechete. Syntax yaro inochinjika uye zvinoenderana nehuwandu hwenharo dzinofanirwa kushandiswa. Iyo generic formula inoita seizvi: =SUMIFS(summation_range, condition_range1, condition1, [condition_range2, condition2], …). Nharo nhatu dzekutanga dzinofanirwa kutsanangurwa, uyezve zvese zvinoenderana nekuti ingani nzira iyo munhu anoda kuisa.

COUNTIF uye COUNTIFS mabasa

Iri basa rinotara kuti mangani maseru muchikamu anofanana nechimwe chimiro. Iyo syntax yebasa inosanganisira nharo dzinotevera:

1. Range. Iri ndiro dataset richasimbiswa uye kuverengwa.
2. Criterion. Iyi ndiyo mamiriro ayo data inofanira kusangana.

Mumuenzaniso watiri kupa izvozvi, basa iri rakatara kuti makiyi mangani ane shanduko dzinopfuura zviuru zana. Zvakaratidza kuti paingova nemakiyi matatu chete.

Iyo yakawanda inogoneka nhamba yezviyero mubasa iri chimiro chimwe chete. Asi zvakafanana kune yakapfuura sarudzo, unogona kushandisa basa COUNTIFSkuseta mamwe maitiro. Syntax yebasa iri ndeiyi: COUNTIFS(condition_range1, condition1, [condition_range2, condition2], …).

Huwandu hwehuwandu hwemamiriro uye huwandu hunofanirwa kuongororwa uye kuverengerwa ndeye 127.

ERROR basa

Nebasa iri, sero rinodzosa kukosha-kwakatsanangurwa nemushandisi kana kukanganisa kukaitika semugumisiro wekuverenga kune rimwe basa. Syntax yebasa iri ndeiyi inotevera: =IFERROR(value;value_if_error). Sezvauri kuona, basa iri rinoda nharo mbiri:

1. Meaning. Pano iwe unoda kunyora pasi fomu, maererano nezvikanganiso zvichagadziriswa, kana zviripo.
2. Kukosha kana kukanganisa. Uhwu ndihwo kukosha hucharatidzwa muchitokisi kana formula yekushanda ikatadza.

Uye muenzaniso kuenzanisira. Ngatitii tine tafura yakadaro.

Isu tinoona kuti counter haishande pano, saka hapana vashanyi, uye 32 kutenga kwakaitwa. Sezvingatarisirwa, mamiriro akadaro haagone kuitika muhupenyu chaihwo, saka isu tinofanirwa kugadzirisa kukanganisa uku. Takaita izvozvo chaizvo. Takahwina mufanction IFERROR nharo muchimiro cheformula yekugovera nhamba yekutenga nenhamba yevashanyi. Uye kana kukanganisa kukaitika (uye munyaya iyi kupatsanurwa ne zero), fomu inonyora "recheck". Iri basa rinoziva kuti kupatsanurwa ne zero hazvigoneke, saka rinodzosera kukosha kwakakodzera.

LEFT basa

Nebasa iri, mushandisi anogona kuwana nhamba inodiwa yemavara echinyorwa tambo, ayo ari kuruboshwe. Basa racho rine nharo mbiri. Muzhinji, iyo formula ndeyotevera: =KURUboshwe(mavara,[nhamba_ye_mavara]).

Mapoka ebasa iri anosanganisira tambo yemavara kana sero rine mavara anozotorwa, pamwe nenhamba yemavara achaverengerwa kubva kuruboshwe. Mukushambadzira, chimiro ichi chinokutendera kuti unzwisise kuti mazita emapeji ewebhu anotaridzika sei.

Muchiitiko ichi, takasarudza mavara makumi matanhatu kubva kuruboshwe rwetambo iri muchitokisi A60. Taida kuyedza kuti zita rakapfupika raizotaridzika sei.

PTR basa

Basa iri rakafanana nerekare, chete rinokubvumira kuti usarudze pekutangira paunotanga kuverenga mavara. Syntax yaro inosanganisira nharo nhatu:

1. Rudzi rwemavara. Zvamazvirokwazvo, unogona kunyora mutsara pano zvakananga, asi zvinonyanya kushanda kupa zvinongedzo kumaseru.
2. Nzvimbo yekutanga. Urwu ndirwo hunhu hunotangira kuverenga nhamba yemavara anotsanangurwa munharo yechitatu.
3. Nhamba yemavara. Nhaurirano yakafanana neiri muchikamu chekare.

Nebasa iri, semuenzaniso, unogona kubvisa imwe nhamba yemavara pakutanga uye kumagumo kwetambo yemavara.

Kwatiri isu, takavabvisa chete kubva pakutanga.

UPPER basa

Kana iwe uchida kuve nechokwadi chekuti mazwi ese ari mumutsara tambo iri mune chaiyo sero akanyorwa nemavara makuru, saka unogona kushandisa basa racho. KUTONGA. Zvinotora nharo imwe chete, tambo yemavara kuti iitwe yakakura. Inogona kuve yakananga nenyundo mubhuraketi, kana musero. Muchiitiko chekupedzisira, iwe unofanirwa kupa chinongedzo kwairi.

PASI basa

Basa iri ndiro rinonyatsopesana nerekare. Nekubatsira kwayo, unogona kuita kuti mavara ose ari mutambo ave maduku. Zvinotorawo nharo imwe chete setambo yemavara, ingave inoratidzwa zvakananga sechinyorwa kana kuchengetwa mune chaiyo sero. Heunoi muenzaniso wemashandisiro atakaita basa iri kushandura zita re "Date of Birth" column kune rimwe iro mavara ese ari madiki.

SEARCH basa

Nebasa iri, mushandisi anogona kuona kuvepo kwechimwe chinhu mumutengo wakaiswa uye kunyatsonzwisisa kwachiri. Ine nharo dzakawanda:

1. Ukoshi hunodiwa. Iyi ndiyo tambo yemavara, nhamba, inofanirwa kutsvakwa mudhadha renji.
2. The array iri kuonekwa. Seti yedata inotsvakwa kuti iwane kukosha kuri munharo yapfuura.
3. Mhando yemepu. Nharo iyi ndeyekusarudza. Nezvo, iwe unogona kuwana iyo data zvakanyanya. Kune mhando nhatu dzekuenzanisa: 1 - kukosha kushoma pane kana kuenzana (tiri kutaura nezvenhamba data, uye hurongwa pachahwo hunofanira kurongwa mukukwira kurongeka), 2 - chaiyo match, -1 - kukosha kukuru kupfuura kana kuenzana.

Nokuda kwekujekesa, muenzaniso muduku. Pano takaedza kunzwisisa kuti ndeipi yezvikumbiro ine nhamba yeshanduko isingasviki kana kuenzana ne900.

Formula yakadzosa kukosha kwe3, iyo isiri nhamba yemutsara wakakwana, asi imwe hama. Izvozvi, kwete nekero, asi nenhamba inoenderana nekutanga kweiyo yakasarudzwa data renji, iyo inogona kutanga chero kupi.

DLSTR basa

Basa iri rinoita kuti zvikwanise kuverenga kureba kwetambo yemavara. Zvinotora nharo imwe - kero yesero kana tambo yemavara. Semuenzaniso, mukushambadzira, zvakanaka kuishandisa kutarisa nhamba yemavara mune tsananguro.

CONCATENATE basa

Nemushandisi uyu, unogona kubatanidza akawanda mameseji kukosha mune imwe hombe tambo. Nharo idzi maseru kana kuti tambo dzemavara dzakananga mumakotesheni akapatsanurwa nemakoma. Uye heino muenzaniso mudiki wekushandisa basa iri.

Basa PROP

Mushandisi uyu anotendera kuti uite kuti mavara ese ekutanga emazwi atange nemavara makuru. Zvinotora tambo yemavara kana basa rinodzosa imwe senharo yayo chete. Iri basa rakanyatsokodzera kunyora mazita anosanganisira akawanda mazita chaiwo kana mamwe mamiriro ayo anogona kubatsira.

BASA BASA

Mushandisi uyu anoita kuti zvibvire kubvisa mavara ese asingaonekwe kubva pamutsara tambo. Zvinotora nharo imwe chete. Mumuenzaniso uyu, zvinyorwa zvine mavara asina kudhindwa akabviswa nebasa.

Ichi chimiro chinofanirwa kushandiswa mumamiriro ezvinhu apo mushandisi akakopa zvinyorwa kubva kune imwe chirongwa uye mavara asingadhindike akaendeswa otomatiki kuExcel spreadsheet.

TRIM basa

Nemushandisi uyu, mushandisi anogona kubvisa zvese zvisina basa nzvimbo pakati pemashoko. Inosanganisira kero yesero, inova ndiyo yega nharo. Heino muenzaniso wekushandisa basa iri kusiya nzvimbo imwe chete pakati pemazwi.

WANA basa

Nebasa iri, mushandisi anogona kuwana mavara mukati memamwe mavara. Iri basa rinonyanya kujeka. Naizvozvo, mavara makuru nemadiki anofanira kuremekedzwa. Iri basa rinotora nharo nhatu:

1. Chinyorwa chaidiwa. Iyi ndiyo tambo irikutsvagwa.
2. Mavara arikutariswa ndiwo machinjiro ekutsvaga.
3. Nzvimbo yekutanga igakava resarudzo rinotsanangura mavara ekutanga kubva pakutsvaga.

INDEX basa

Nebasa iri, mushandisi anogona kuwana kukosha kwaari kutsvaga. Iine nharo nhatu dzinodiwa:

1. Array. Huwandu hwe data iri kuongororwa.
2. Line nhamba. Nhamba ye ordinal yemutsara mune iyi renji. Attention! Kwete kero, asi nhamba yemutsara.
3. Column nhamba. Zvakangofanana nenharo yapfuura, yekoramu chete. Nharo iyi inogona kusiiwa isina chinhu.

EXACT basa

Mushandisi uyu anogona kushandiswa kuona kana tambo mbiri dzemavara dzakafanana. Kana dzakafanana, inodzosera kukosha TRUE. Kana vakasiyana - KUNYEPA.

OR basa

Iri basa rinokubvumira kuti uise kusarudzwa kwechimiro 1 kana chimiro 2. Kana chimwe chazvo chiri chechokwadi, ipapo kukosha kwekudzoka nde- TRUE. Unogona kutsanangura kusvika ku255 boolean values.

Basa uye

Basa rinodzosa kukosha TRUEkana nharo dzayo dzose dzichidzorera kukosha kwakafanana.

Iyi ndiyo inonyanya kukosha nharo inonzwisisika iyo inokutendera iwe kuti uise akati wandei mamiriro kamwechete, ayo anofanirwa kucherechedzwa panguva imwe chete.

OFFSET basa

Iri basa rinokutendera iwe kuti uwane referensi kune renji inogadziriswa neimwe nhamba yemitsara nemakoramu kubva kune ekutanga macoordinates. Nharo: chirevo chesero rekutanga renji, mitsetse mingani yekuchinja, mapikoni mangani ekuchinja, ndeupi hurefu hwehuwandu hutsva uye hupamhi hwemhando itsva.

mhedziso

Nerubatsiro rweExcel mabasa, mushambadzi anogona kunyatso chinja-chinja maitiro esaiti, shanduko, uye zvimwe zviratidzo. Sezvauri kuona, hapana akakosha mapurogiramu anodiwa, yakanaka yekare hofisi suite inokwana kuita chero zano.