Mashandisiro Ekushandisa Excel SaPro: 18 Nyore Excel Matipi, Matipi, & Mapfupi

Dzimwe nguva, Excel inoita kunge yakanyanya kunaka kuti ive yechokwadi. Zvese ini zvandinofanira kuita kuisa fomu, uye zvakanyanyisa kunaka chero chinhu chandingazomboda kuita nemaoko chinogona kuitwa otomatiki. Unoda kubatanidza machira maviri ane data rakafanana? Excel inogona kuzviita. Unoda kuita nyore masvomhu? Excel inogona kuzviita. Unoda kubatanidza ruzivo mumasero akawanda? Excel inogona kuzviita.

Dambudziko rega nderekuti zvinogona kunetsa kushandisa kune vanotanga. Nepo kuisa data pamurume kuri nyore, zvinogona kuve zvinotyisa kudzidza ese mafomula uye mapfupi aunoda kuti ugone kushandisa chishandiso. Uyezve, kunyangwe iwe ukashandisa mafomula nenzira kwayo, pane nguva dzose mukana waunomhanyira mauri mhosho meseji. Zvichitaurwa zviri nyore, Excel inogona kuve yakaoma kwazvo kushandisa.

Kwete kunetseka. Mune ino posvo, ini ndichaenda pamusoro pezvakanakisa matipi, matipi, uye mapfupi aunogona kushandisa izvozvi kutora yako Excel mutambo kune inotevera nhanho. Hapana ruzivo rwemberi rweExcel runodikanwa.

Chii chinonzi Excel?

Microsoft Excel ispredishiti software inoshandiswa nevashambadzi, maakaundendi, vaongorori ve data, uye vamwe vashandi vanoshandisa kuchengetedza, kuronga, uye kuteedzera seti yedata Icho chikamu cheMicrosoft Office suite yezvigadzirwa. Dzimwe nzira dzinosanganisira Google Mapepa uye Manhamba. Tsvaga dzimwe nzira dzeExcel pano.

Kuti utange kudzidza kwako kweExcel, tarisa vhidhiyo pazasi pamaitiro ekushandisa Excel kune vanotanga.

Excel inonyanya kushandiswa kugadzira zvinyorwa zvemari nekuda kwesimba rayo rakasimba rekuverenga. Iwe unowanzo tsvaga iyo software mumahofisi ekuverenga uye zvikwata nekuti inobvumira maakaundendi kuti vangoona wega masvomhu, mavhareji, uye totari. NaExcel, vanogona kuve nemusoro webhizinesi ravo 'data.

Ipo Excel ichinyanyo kuzivikanwa seyekushandisa yekuverenga, nyanzvi mune chero ndima dzinogona kushandisa maficha uye mafomula - kunyanya vatengesi - nekuti inogona kushandiswa kuteedzera chero mhando yedata. Inobvisa kudiwa kwekushandisa maawa nemaawa kuverenga masero kana kuteedzera nekunamatira manhamba ekuita. Excel kazhinji ine nzira pfupi kana yekukurumidza kugadzirisa iyo inomhanyisa maitiro.

Hauna chokwadi chekuti unganyatso shandisa sei Excel muchikwata chako? Heino runyorwa rwemagwaro iwe aunogona kugadzira:

Zvinyorwa zveMari: Unogona kushandisa Excel spreadsheet kuteedzera yekutengesa yekambani zviitiko uye hutano hwemari.
Balance Mapepa: Balance sheet ndeimwe yemhando dzakajairika dzemagwaro iwe dzaunogadzira neExcel. Iyo inokutendera iwe kuti uwane iyo yakazara maonero eiyo kambani kumira kwemari.
karenda: Unogona kugadzira pepanhau repamwedzi nyore nyore kuteedzera zviitiko kana rumwe ruzivo rwezuva.

Heano mamwe magwaro aunogadzira zvakananga kune vatengesi.

Kushambadzira Mabhajeti: Excel chishandiso chakasimba chekuchengetedza bhajeti. Iwe unogona kugadzira uye kuteedzera kushambadzira mabhajeti, pamwe nekushandisa, uchishandisa Excel. Kana iwe usiri kuda kugadzira gwaro kubva pakutanga, tora edu ekushambadzira matemplate emahara.
Kushambadzira Mishumo: Kana iwe ukasashandisa chishandiso chekushambadzira senge Yekushambadzira Hub, unogona kuzviwana uri mukuda kwedashboard nezvako zvese zvinyorwa. Excel chishandiso chakanakisa kugadzira mishumo yekutengesa. Dhawunirodha yemahara Excel yekushambadzira matemplate pano.
Edhisheni Makarenda: Unogona kugadzira makarenda ekugadzirisa muExcel. Iyo tabu fomati inoita kuti zvive nyore kwazvo kuteedzera yako yemukati yekugadzira kuyedza kweyakajairwa nguva masheya. Dhawunorodha yemahara yekunyora zvemukati khalendari template pano.
Traffic uye Inotungamira Calculator: Nekuda kwesimba rayo rakasimba rekuverenga, Excel chishandiso chakanakisa kugadzira ese macalculator - kusanganisira imwe yekutungamira inotungamira uye traffic. Dzvanya apa kudhanilodha yemahara premade Calculator.

Ichi chingori chidiki sampling yemhando dzekutengesa uye emabhizinesi magwaro aunogadzira muExcel. Isu takagadzira iyo rakakura runyorwa rweExcel matemplate iwe unogona kushandisa izvozvi kushambadzira, invoice, manejimendi manejimendi, bhajeti, uye zvimwe.

Iwe unogona zvakare kurodha pasi matemplate eExcel pazasi pane ako ese ekushambadzira zvaunoda.

Mushure mekurodha pasi matemplate, inguva yekutanga kushandisa software. Ngatitangei kufukidza izvo zvekutanga kutanga.

Excel Basics

Kana iwe uchingotanga neExcel, pane mashoma mashoma mirairo yatinokurudzira kuti uzive. Izvi zvinhu zvakaita se:

Kugadzira spreadsheet nyowani kubva pakutanga.
Kuita macomputer akakosha sekuwedzera, kubvisa, kuwedzera, uye kupatsanura.
Kunyora uye fomati yekoramu zvinyorwa uye mazita.
Uchishandisa maficha ekuzadza auto eExcel.
Kuwedzera kana kudzima mbiru imwe chete, mitsara, uye maspredishiti. Pazasi, isu tinopinda mune ekuwedzera zvinhu sezvakawanda makoramu uye mitsara.
Kuchengeta makoramu uye mitsara yemazita anoonekwa paunenge uchipururudza nepashure paspredishiti, kuitira kuti iwe uzive kuti ndeipi data yauri kuzadza iwe paunenge uchienderera kumberi pasi gwaro

Nezve kunyura kwakadzika pane izvi zvekutanga, tarisa edu yakazara Microsoft Excel gwara.

Mumweya wekushanda zvakanyanya uye kudzivirira kunetesa, basa remanyore, heano mashoma mafomula eExcel uye mabasa auchazoda kuziva.

Excel Mafomula

Zviri nyore kukurirwa nehupamhi hwakawanda hwe Excel mafomula iyo iwe yaunogona kushandisa kuita zvine musoro kubva kune yako data. Kana iwe uchangotanga kushandisa Excel, unogona kuvimba nenzira dzinotevera kuti uite mamwe mabasa akaomarara - pasina kuwedzera kune kuomarara kwenzira yako yekudzidza.

Chiratidzo chakaenzana: Usati wagadzira chero fomula, iwe unofanirwa kunyora chiratidzo chakaenzana (=) muchitokisi maunoda kuti mhedzisiro ioneke.
KuwedzeraKuti uwedzere kukosha kwemasero maviri kana anopfuura, shandisa iyo + chiratidzo. Muenzaniso: = C5 + D3.
Kubvisa: Kuti ubvise hunhu hwemasero maviri kana anopfuura, shandisa iyo - chiratidzo. Muenzaniso: = C5-D3.
Kuwedzera: Kuti uwedzere kukosha kwemasero maviri kana anopfuura, shandisa iyo * chiratidzo. Muenzaniso: = C5 * D3.
Division: Kupatsanura kukosha kwemasero maviri kana anopfuura, shandisa iyo / chiratidzo. Muenzaniso: = C5 / D3.

Kuisa zvese izvi pamwechete, unogona kugadzira fomula iyo inowedzera, inobvisa, yakawanda, uye inokamura zvese musero rimwe. Muenzaniso: =(C5-D3)/((A5+B6)*3).

Kune mamwe mafomula akaomarara, iwe uchafanirwa kushandisa mabhureki akatenderedza matauriro kuti udzivise netsaona uchishandisa iyo PEMDAS kuraira kwekushanda. Ramba uchifunga kuti iwe unogona kushandisa akajeka manhamba mune ako mafomula.

Excel Mabasa

Excel inoshanda otomatiki mamwe emabasa iwe aunoshandisa mune yakajairika fomula. Semuenzaniso, panzvimbo pekushandisa iyo + kusaina kuwedzera huwandu hwesero, iwe waizoshandisa iyo SUM basa. Ngatitarisei mamwe mashoma mabasa ayo anobatsira kugadzirisa maverengero uye mabasa.

SUMBasa reSUM rinowedzera roga masero kana nhamba. Kuti upedze huwandu, iwe unokwanisa kuisa sero rekutanga uye sero rekupedzisira nekoloni iri pakati. Hezvino izvo zvinoita senge: SUM (Cell1: Cell2). Muenzaniso: = SUM (C5: C30).
AVERAGE: AVERAGE basa rinoburitsa kunze kwehunhu hweruzhinji maseru. Iyo syntax yakafanana nebasa reSUM: Avhareji (Cell1: Cell2). muenzaniso: = Avhareji (C5: C30).
IF: IF IF basa rinokutendera kuti udzore kukosha kunoenderana nemuedzo unonzwisisika. Iyo syntax inotevera: KANA (zvine musoro_ kuyedza, kukosha_if_ chokwadi, [kukosha_if_false]). Muenzaniso: = IF (A2> B2, "Pamusoro peBhajeti", "Zvakanaka").
VLOOKUPBasa reVLOOKUP rinobatsira iwe kutsvaga chero chinhu pamitsara yetsetse yako. Iyo syntax ndeiyi: VLOOKUP (kukosha kwekutarisa, tafura, tafura yenhamba, Inofungidzirwa mechi (CHOKWADI) kana Chaiko mutambo (FALSE)). Muenzaniso: = VLOOKUP ([@ Gweta], tbl_Attorneys, 4, FALSE).
INDEX: INDEX basa rinodzosa kukosha kubva mukati mehurefu. Iyo syntax inotevera: INDEX (array, row_num, [column_num]).
MATCH: Iko MATCH basa rinotarisa chimwe chinhu mune yakasarudzika maseru uye inodzosera chinzvimbo chechinhu icho. Inogona kushandiswa muchiyero pamwe neiyo INDEX basa. Iyo syntax ndeiyi: MATCH (lookup_value, lookup_array, [match_type]).
COUNTIFIko COUNTIF basa rinodzosera iyo nhamba yemasero anosangana neimwe nzira kana ane imwe kukosha. Iyo syntax ndeiyi: COUNTIF (renji, maitiro). muenzaniso: = COUNTIF (A2: A5, "London").

Zvakanaka, wakagadzirira kupinda mune nitty-gritty? Ngatisvikei pairi. (Uye kune vese vateveri vaHarry Potter kunze uko… munogamuchirwa pamberi.)

chitsamba: MaGIF uye zvinoonekwa zvinobva pane yapfuura vhezheni yeExcel. Kana zvichikwanisika, iyo kopi yakagadziridzwa kuti ipe rairo kune vashandisi veese ari maviri ekare uye ekare maBhaibheri eExcel.

1. Shandisa matafura ePivot kuziva nekuita zvine musoro zve data.

Pivot matafura anoshandiswa kuronga data muspredishiti. Ivo havazoshandure iyo data yauinayo, asi ivo vanogona kuunganidza huwandu uye kuenzanisa ruzivo rwakasiyana mune rako pepanhau, zvichienderana nezvaunoda kuti vaite.

Ngatitorei muenzaniso. Ngatitii ndinoda kutarisa kuti vanhu vangani vari mune yega imba kuHogwarts. Unogona kunge uchifunga kuti ini handina data rakawandisa, asi kune akareba data seti, izvi zvichauya zvinobatsira.

Kugadzira iyo Pivot Tafura, ini ndinoenda ku Data > Pivot Tafura. Kana iwe uri kushandisa yazvino vhezheni yeExcel, iwe unogona kuenda ku Insert > Pivot Tafura. Excel inozozvara yega Pivot Tafura yako, asi iwe unogona kugara uchichinja kutenderedza marongero e data. Ipapo, iwe une sarudzo ina dzaunosarudza kubva.

Revera Firita: Izvi zvinokutendera iwe kuti utarise chete kune mamwe mitsara mune yako dhatabhesi. Semuenzaniso, kana ini ndaida kugadzira firita neimba, ini ndaigona kusarudza kungoisa vadzidzi muGryffindor pachinzvimbo chevadzidzi vese.
Column Labels: Aya aizove musoro wako mune dhata.
Row Zvinyorwa: Iyi inogona kuve yako mitsara mune iyo dhatabhesi. Mavara maviri eRow neColumn anogona kuve ne data kubva kumakoramu ako (semuenzaniso Zita rekutanga rinogona kudhonzwa kuenda kuRow kana Column label - zvinongoenderana nekuti unoda kuona sei data.)
ukoshi: Ichi chikamu chinokutendera iwe kutarisa yako data zvakasiyana. Panzvimbo pekungodhonza chero kukosha kwenhamba, unogona kuverenga, kuverenga, avhareji, max, min, kuverenga nhamba, kana kuita zvimwe zvishoma zvekubata nedata rako. Muchokwadi, nekutadza, kana iwe uchizvuzvurudza munda kuKukosha, inogara ichiverenga.

Sezvo ini ndoda kuverenga huwandu hwevadzidzi mumba imwe neimwe, ini ndichaenda kune iyo Pivot tafura yekuvaka uye kudhonza iyo imba kholamu kune ese maRow Labels uye ma Values. Izvi zvichapeta huwandu hwevadzidzi vanobatana neimba yega yega.

2. Wedzera zvinopfuura mutsara mumwe kana koramu.

Sezvo iwe uchitamba uchitenderera nedata rako, unogona kuwana uri kugara uchida kuwedzera mamwe mitsara nemakoramu. Dzimwe nguva, ungatoda kuwedzera mazana emitsara. Kuita izvi-ne-imwe kungave kunonetesa. Neraki, panogara paine nzira iri nyore.

Kuti uwedzere mitsara yakawanda kana makoramu muspredishiti, simbisa iyo yakafanana nhamba yepreexisting mitsara kana makoramu iwe aunoda kuwedzera. Wobva wadzvanya-kurudyi wobva wasarudza "Insert."

Mumuenzaniso pazasi, ini ndoda kuwedzera mamwe matatu mitsara. Nokusimbisa mitsara mitatu uye nekudzvanya yekuisa, ndinokwanisa kuwedzera mamwe matatu asina chinhu mitsara muspredishiti rangu nekukurumidza uye nyore.

3. Shandisa mafirita kurerutsa data rako.

Paunenge uchitarisa pamadhigidhi akakura kwazvo, haufanire kunge uchitarisa pamutsetse wega wega panguva imwe chete. Dzimwe nguva, iwe unongoda kutarisa dhata inokodzera mune mamwe maitiro.

Ndipo panopinda mafirita.

Mafirita anobvumidza iwe kudzikisira pasi data rako kuti utarise chete kune mamwe mitsara panguva imwechete. MuExcel, firita inogona kuwedzerwa kukoramu imwe neimwe mu data rako - uye kubva ipapo, unogona kusarudza kuti ndeapi maseru aunoda kutarisa kamwechete.

Ngatitorei muenzaniso pazasi. Wedzera firita nekudzvanya paData tebhu uye kusarudza "Sefa." Kudzvanya museve padhuze nemusoro wemusoro uye iwe unozogona kusarudza kana iwe uchida kuti data rako rirongedzwe mukukwira kana kudzika kurongeka, pamwe nemitsara chaiyo yaunoda kuratidza.

Mumuenzaniso wangu waHarry Potter, ngatitii ini ndinongoda chete kuona vadzidzi muGryffindor. Nokusarudza iyo Gryffindor firita, mimwe mitsara inonyangarika.

Pro Tip: Kopa uye unamate iwo maitiro muspredishiti kana Firita yavepo kuti iwedzere kuongorora mune imwe spreadsheet.

4. Bvisa duplicate data mapoinzi kana seti.

Mahombe madhata seti anowanzo kuve nemaviri maficha. Iwe unogona kuve nerunyorwa rwevakawanda vanobatana mukambani uye unongoda chete kuona huwandu hwemakambani aunazvo. Mumamiriro ezvinhu akadai, kubvisa zvakapetwa kunouya zviri nyore.

Kuti ubvise zvakapetwa, simbisa mutsara kana ikholamu iwe yaunoda kubvisa zvakapetwa. Wobva waenda kuData dhata uye sarudza "Bvisa zvakapetwa" (iri pasi pemidziyo midziyo mune yekare vhezheni yeExcel). Pop-up ichaonekwa kuratidza iyo data yaunoda kushanda nayo. Sarudza "Bvisa zvakapetwa," uye wakanaka kuenda.

Iwe unogona zvakare kushandisa chiitiko ichi kuti ubvise mutsara wese zvichienderana nekwakapetwa koriamu kukosha. Saka kana iwe uine mitsara mitatu ine ruzivo rwaHarry Potter uye iwe uchingoda chete kuona imwe, ipapo unogona kusarudza iyo dhatabhesi rese uye wozobvisa zvakapetwa zvinoenderana neemail. Chinyorwa chako chinoguma chichava nemazita akasarudzika chete pasina zvadzokororo.

Shandura mitsara mumakoramu.

Paunenge uine mitsara yedata muspredishiti yako, unogona kusarudza iwe uchinyatsoda kushandura zvinhu mune imwe yeaya mitsara kuita makoramu (kana zvinopesana). Zvinotora nguva yakawanda kutevedzera uye kunama musoro wega wega - asi izvo izvo zvinorehwa netripose zvaungobvumidza iwe kungo fambisa data rako remutsara mumakoramu, kana imwe nzira yakatenderedza.

Kutanga nekusimbisa iyo ikholamu iyo iwe yaunoda kufambisa mumitsara. Dzvanya-kurudyi wobva wasarudza "Kopa." Tevere, sarudza masero ari paspredishiti yako kwaunoda mutsara wako wekutanga kana ikholamu kuti itange. Dzvanya kurudyi pane sero, wobva wasarudza "Nyora Zvakakosha." Module ichaonekwa - pasi, iwe uchaona sarudzo yekuchinja. Tarisa iro bhokisi uye sarudza OK. Chikamu chako zvino chichaendeswa kumutsara kana zvinopesana.

Pamashanduro matsva eExcel, kudonha kunozoonekwa pane pop-up.

6. Kakamura ruzivo rwemavara pakati pemakoramu.

Ko kana iwe uchida kupatsanura ruzivo ruri mune rimwe sero mumasero maviri akasiyana? Semuenzaniso, pamwe iwe unoda kuburitsa zita remumwe munhu rekambani kuburikidza neemail kero. Kana kuti pamwe iwe unoda kupatsanura zita remumwe munhu rizere mune rekutanga uye rekupedzisira zita kune yako email yekushambadzira matemplate.

Kutenda kuExcel, zvese zvinogoneka. Kutanga, simbisa iyo ikholamu yaunoda kupatsanura. Tevere, enda kuData dhata uye sarudza "Zvinyorwa kune Makoramu." Module ichaonekwa neruzivo rwekuwedzera.

Kutanga, unofanirwa kusarudza kana "Yakatemerwa" kana "Yakagadziriswa Upamhi."

"Yakatemerwa" zvinoreva kuti iwe unoda kuputsa iyo ikholamu zvichibva pane mavara senge makoma, nzvimbo, kana tabo.
"Fixed Width" zvinoreva kuti iwe unoda kusarudza nzvimbo chaiyo pamakoramu ese aunoda kuti kupatsanurwa kuitike.

Mumuenzaniso wezasi pazasi, ngatisarudze "Yakagadziriswa" kuti tikwanise kupatsanura zita rizere muzita rekutanga nerekupedzisira.

Zvino, inguva yekusarudza maDelimiters. Iyi inogona kunge iri tebhu, semi-colon, koma, nzvimbo, kana chimwewo chinhu. ("Chimwe chinhu" inogona kunge iri "@" chiratidzo chinoshandiswa mune email kero, semuenzaniso.) Mumuenzaniso wedu, ngatisarudze nzvimbo. Excel inozobva yakuratidzira tarisiro yekuti macolumn ako matsva anotaridzika sei.

Kana iwe uchifara nekutarisa, tinya "Inotevera." Iri peji rinokutendera iwe kuti usarudze mafomati epamberi kana ukasarudza. Paunenge wapedza, tinya "Pedza."

7. Shandisa nzira dzekuverenga zvakapusa.

Kunze kwekuita kuverenga kwakaomarara, Excel inogona kukubatsira kuita masvomhu akareruka sekuwedzera, kubvisa, kuwedzera, kana kupatsanura chero dhata rako.

Kuwedzera, shandisa + chiratidzo.
Kuti ubvise, shandisa - chiratidzo.
Kuti uwedzere, shandisa * chiratidzo.
Kugovanisa, shandisa iyo / chiratidzo.

Iwe unogona zvakare kushandisa mabhureki kuona kuti dzimwe kuverenga kunoitwa kutanga. Mumuenzaniso pazasi (10 + 10 * 10), yechipiri neyechitatu gumi dzakawedzeredzwa pamwechete usati wawedzera zvimwe gumi. Zvisinei, kana tikazviita (10 + 10) * 10, yekutanga neyechipiri yegumi yaizowedzerwa pamwechete kutanga. .

$Excel maitiro akareruka mukuita$

8. Tora avhareji yenhamba mumasero ako.

Kana iwe uchida avhareji seti yenhamba, unogona kushandisa fomula = KUDZIDZA (Cell1: Cell2). Kana iwe uchida kupfupisa ikholamu yenhamba, unogona kushandisa fomula = SUM (Cell1: Cell2).

9.Shandisa mamiriro ekumisikidza kuti maseru achinje kamwe ruvara zvichienderana nedata.

Mamiriro ekumisikidza anotendera iwe kuti uchinje ruvara rwechitokisi zvichienderana neruzivo rwuri mukati mechitokisi. Semuenzaniso, kana iwe uchida kumaka mamwe manhamba ari pamusoro pevhareji kana ari pamusoro gumi muzana yedhata mune yako pepanhau, unogona kuzviita. Kana iwe uchida kupenda kodhi zvakajairika pakati pemitsara yakasiyana muExcel, unogona kuzviita. Izvi zvinokubatsira iwe nekukurumidza kuona ruzivo rwakakosha kwauri.

Kuti utange, simbisa iro boka remasero iwe aunoda kushandisa mamiriro ekuomesa on. Wobva wasarudza "Mamiriro Ekumisikidza" kubva kuImba menyu uye sarudza pfungwa yako kubva pakudonhedza. (Unogona zvakare kugadzira yako wega mutemo kana iwe uchida chimwe chakasiyana.) Iwindo rinobuda iro rinokukurudzira kuti upe rumwe ruzivo nezve yako fomati mutemo. Sarudza "Zvakanaka" kana wapedza, uye unofanirwa kuona mhedzisiro yako ichingooneka.

Shandisa IF IF formula fomati kuita mamwe mabasa eExcel.

Dzimwe nguva, hatidi kuverenga iyo nhamba yenguva iyo kukosha kunoonekwa. Pane kudaro, tinoda kuisa ruzivo rwakasiyana musero kana paine sero inoenderana neruzivo irworwo.

Semuenzaniso, mune iri pazasi mamiriro, ndinoda kupa zvibodzwa gumi kune wese munhu ari mumba maGryffindor. Panzvimbo pekunyora manyore mu10's padhuze nezita rega rega remudzidzi weGryffindor, ndinogona kushandisa IF Excel fomula kutaura kuti kana mudzidzi ari kuGryffindor, vanofanira kuwana mapoinzi gumi.

Fomura yacho ndeiyi: KANA (zvine musoro_ kuyedza, kukosha_if_ chokwadi, [kukosha_if_false])

Muenzaniso Unoratidzwa Pazasi: = IF (D2 = "Gryffindor", "10 ″," 0 ″)

Mukutaura kwakawanda, fomula yacho ichave IF (Logical Test, kukosha kwechokwadi, kukosha kwenhema). Ngatitsvakei mune yega yega misiyano.

Zvinonzwisisika_Test: Muedzo unonzwisisika chikamu che "IF" chikamu chechirevo. Panyaya iyi pfungwa ndeye D2 = "Gryffindor" nekuti tinoda kuona kuti sero rinoenderana nemudzidzi rinoti "Gryffindor." Ita shuwa yekuisa Gryffindor mumakotesheni emakesi pano.
Kukosha_if_True: Izvi ndizvo zvatiri kuda kuti sero iratidze kana kukosha kuri kwechokwadi. Panyaya iyi, tinoda kuti sero iratidze “gumi” kuratidza kuti mudzidzi akapihwa mapoinzi gumi. Ingoshandisa makotesheni emakotesheni kana iwe uchida kuti mhedzisiro ive chinyorwa panzvimbo yenhamba.
Kukoshesa_if_False: Izvi ndizvo zvatiri kuda kuti sero iratidze kana kukosha kuri kunyepa. Mune ino kesi, kune chero mudzidzi asiri muGryffindor, tinoda kuti sero iratidze "0". Ingoshandisa makotesheni emakotesheni kana iwe uchida kuti mhedzisiro ive chinyorwa panzvimbo yenhamba.

chitsamba: Mumuenzaniso uri pamusoro, ini ndakapa mapoinzi gumi kune wese munhu muGryffindor. Dai ini gare gare ndaida kuunganidza huwandu hwenongedzo, ini ndaisazokwanisa nekuti ma10 ari mumakotesheni, nekudaro ndichivaita mameseji uye kwete nhamba inogona kuunganidzwa neExcel.

11. Shandisa zviratidzo zvemadhora kuti uchengetedze fomula yesero rimwe chete zvakafanana zvisinei nekwainofamba.

Wakamboona chiratidzo chedhora mune yeExcel fomula? Kana ikashandiswa mufomura, haisi inomiririra dhora reAmerica; pachinzvimbo, zvinoita kuti ive nechokwadi chekuti ikholamu chaiyo nemutsara zvakabatwa zvakafanana kunyangwe ukateedzera fomula imwe mumitsara iri padhuze

Iwe unoona, chirevo chesero - kana iwe uchinongedzera kuchitokisi A5 kubva kuchitokisi C5, semuenzaniso - inoenderana nekutadza. Pakadaro, urikunyanya kureva sero rine makoramu mashanu kuruboshwe (C kubvisa A) uye mumutsara mumwechete (5). Iyi inonzi nzira yechimiro. Kana iwe ukateedzera fomula yehukama kubva kune imwe sero uchienda kune imwe, ichagadzirisa hunhu mufomura zvichibva nekwakatamisirwa. Asi dzimwe nguva, isu tinoda iwo maitiro kuti agare akafanana zvisinei kuti vafamba-famba here kana kwete - uye isu tinogona kuzviita nekushandura fomula kuita fomati yakakwana.

Kuti uchinje fomula yehukama (= A5 + C5) ive fomula yakakwana, isu tinotungamira mutsara uye nhamba yekolamu nemasaini emadhora, seizvi: (= $ A $ 5 + $ C $ 5). (Dzidza zvakawanda pane peji rekutsigira reMicrosoft Office pano.)

12. Shandisa iyo VLOOKUP basa kudhonza dhata kubva kune imwe nzvimbo yeteet kune imwe.

Wati wambove uine maviri seti e data pane maviri akasiyana maspredishiti iwe aunoda kusanganisa mune imwechete spreadsheet?

Semuenzaniso, unogona kuve nerunyorwa rwemazita evanhu padhuze neemaimeri adhi mune imwe spreadsheet, uye runyorwa rweavo chete maemail adhiresi padyo nemazita emakambani mune imwe - asi iwe unoda mazita, email kero, uye mazita emakambani yevanhu ivavo kuti vaoneke munzvimbo imwechete.

Ini ndinofanira kusanganisa seti yedhata seiyi zvakanyanya - uye kana ndadaro, iyo VLOOKUP ndiyo yangu yekuenda-kufomula.

Usati washandisa fomula, zvakadaro, iva nechokwadi chechokwadi chekuti une kana imwechete ikholamu inowoneka zvakafanana munzvimbo mbiri idzi. Tsvaga yako data seti kuti uve nechokwadi chekoramu yedata iwe yauri kushandisa kusanganisa ruzivo rwako zvakangofanana, kusanganisira pasina dzimwe nzvimbo.

Fomura: = VLOOKUP (kukosha kwekutarisa, tafura yakarongedzwa, nhamba yekoramu, Inofungidzirwa mechi (CHOKWADI) kana Chaiko mutambo (FALSE))

Iyo fomula ine akasiyana kubva pamuenzaniso wedu pazasi: = VLOOKUP (C2, Sheet2! A: B, 2, FALSE)

Mune fomula iyi, pane akasiyana misiyano. Izvi zvinotevera ichokwadi kana iwe uchida kusanganisa ruzivo mune Sheet 1 uye Sheet 2 paSheet 1.

Kutarisa Kukosha: Iyi ndiyo kukosha kwakaenzana kwauinako mumaspredishiti maviri. Sarudza kukosha kwekutanga muspredishiti yako yekutanga. Mumuenzaniso unotevera, izvi zvinoreva kero yeemail yekutanga pane iri runyorwa, kana cell 2 (C2).
Tafura Array: Tafura yakarongeka ndeyero yemakoramu paSheet 2 iwe uchadhonza data rako kubva, kusanganisira iyo dhata yedata yakafanana neyako yekutarisa kukosha (mune yedu muenzaniso, email kero) muSheet 1 pamwe nekoramu yedata. urikuedza kuteedzera ku Sheet 1. Mumuenzaniso wedu, iri ndi "Sheet2! A: B." "A" zvinoreva Column A muSheet 2, inova ikholamu mune Sheet 2 apo iyo data yakafanana neyedu yekutarisa kukosha (email) muSheet 1 yakanyorwa. "B" zvinoreva Column B, ine ruzivo rwunongowanikwa muSheet 2 iyo iwe yaunoda kushandurira ku Sheet 1.
Column Nhamba: Izvi zvinotaurira Excel kuti ndeyipi dhata nyowani iyo data nyowani yaunoda kuteedzera kuSheet 1 iri. Mumuenzaniso wedu, iyi ingangove iri iyo "Imba" iri mukati. "Imba" ndiyo yechipiri ikholamu mumakero edu emakoramu ( tafura yakarongeka), saka nhamba yedu yekoramu ndeye 2. [chitsamba: Rudzi rwako runogona kupfuura makoramu maviri. Semuenzaniso, kana paine matatu matatu paSheet 2 - Email, Zera, uye Imba - uye iwe uchiri kuda kuunza Imba paSheet 1, unogona kushandisa VLOOKUP. Iwe unongoda kushandura iyo "2" kuita "3" saka inodzosera kumashure kukosha mukoramu yechitatu: = VLOOKUP (C2: Sheet2! A: C, 3, manyepo).]
Inofungidzirwa Mutambo (CHOKWADI) kana Chaiyo Mechi (FALSE): Shandisa FALSE kuti uve nechokwadi chekuti iwe unodhonza chete chaiyo chaiyo kukosha machisi. Kana iwe ukashandisa CHOKWADI, basa racho rinodhonza mumatanho ekufungidzira.

Mumuenzaniso pazasi, Sheet 1 uye Sheet 2 zvine zvinyorwa zvinotsanangura ruzivo rwakasiyana nezve vanhu vamwe chete, uye tambo yakajairika pakati pevaviri aya maemail adhiresi. Ngatitii isu tinoda kusanganisa ese madhatabhesi kuitira kuti ruzivo rwese rweimba kubva kuSheet 2 rududzire kune Sheet 1.

Saka patinonyora mufomura = VLOOKUP (C2, Sheet2! A: B, 2, FALSE), tinounza data rese repamba muSheet 1.

Ramba uchifunga kuti VLOOKUP inongodhonza kumashure kukosha kubva kune rechipiri jira riri kurudyi kwekoramu ine yako yakafanana data. Izvi zvinogona kutungamira kune kumwe kugumira, ndosaka vamwe vanhu vachisarudza kushandisa iyo INDEX uye MATCH mabasa panzvimbo.

13. Shandisa INDEX uye MATCH mafomula kudhonza data kubva kune yakatwasuka makoramu.

SaVLOOKUP, iyo INDEX uye MATCH mabasa anodhonzera mu data kubva kune imwe dhatabhesi kuenda kune imwe nzvimbo yepakati. Heano mutsauko mukuru:

VLOOKUP iri fomula iri nyore. Kana iwe uri kushanda neakakura madhata seti ayo angangoda zviuru zvekutarisa, kushandisa iyo INDEX uye MATCH basa kuchadzora zvakanyanya mutoro nguva muExcel.
Iyo INDEX uye MATCH mafomura anoshanda kurudyi-kuruboshwe, nepo VLOOKUP mafomura anongoshanda sekutarira kuruboshwe-kurudyi. Mune mamwe mazwi, kana iwe uchida kuita yekutarisa iyo ine yekutarisa ikholamu kurudyi kwemhedzisiro ikholamu, saka unofanirwa kugadzirisa zvakare iwo makoramu kuitira kuti uite VLOOKUP. Izvi zvinogona kunetesa nemadhata makuru uye / kana kutungamira kuzvikanganiso.

Saka kana ini ndichida kusanganisa ruzivo muSheet 1 uye Sheet 2 paSheet 1, asi kukosha kwemakoramu muSheets 1 ne2 hakuna kufanana, kuti ndiite VLOOKUP, ini ndaizofanira kutenderedza macolamu angu. Mune ino kesi, ini ndaizosarudza kuita INDEX uye MATCH pachinzvimbo.

Ngatitarisei pamuenzaniso. Ngatitii Sheet 1 ine runyorwa rwemazita evanhu uye avo maHogwarts email kero, uye Sheet 2 rine runyorwa rwevanhu email kero uye Patronus iyo mudzidzi wega wega anayo. (Kune vateveri vasiri vaHarry Potter kunze uko, muroyi wese kana muroyi ane muchengeti wemhuka anonzi "Patronus" akabatana naye.) Ruzivo rwunogara mumashizha ese ari maviri ikholamu ine email kero, asi iyi email kero iri mumakoramu akasiyana manhamba pane rimwe bepa. Ini ndaizoshandisa iyo INDEX uye MATCH mafomati pachinzvimbo cheVLOOKUP saka ini ndaisazofanira kuchinjisa chero macolamu akatenderedza.

Saka chii chirevo, saka? Iyo fomula iri chaizvo MATCH fomula yakavakirwa mukati meINDEX fomula. Uchaona ndakasiyanisa MATCH fomula ndichishandisa rimwe vara pano.

Fomura: = INDEX (tafura yakarongeka, MATCH fomula)

Izvi zvinova: = INDEX (tafura yakarongeka, MATCH (lookup_value, tarisa_array))

Iyo fomula ine akasiyana kubva pamuenzaniso wedu pazasi: = INDEX (Sheet2! A: A, (MATCH (Sheet1! C: C, Sheet2! C: C, 0)))

Heano akasiyana.

Tafura Array: Mhando yemakoramu paSheet 2 ine iyo data nyowani yaunoda kuunza kuSheet 1. Mumuenzaniso wedu, "A" zvinoreva Column A, iyo ine "Patronus" ruzivo rwemunhu mumwe nemumwe.
Kutarisa Kukosha: Iyi ndiyo ikholamu muSheet 1 ine zviyero zvakafanana mumaspredishiti maviri. Mumuenzaniso unotevera, izvi zvinoreva "email" ikholamu paSheet 1, inova Column C. Saka: Sheet1! C: C.
Kutarisa Array: Iyi ndiyo ikholamu iri muSheet 2 ine zviyero zvakafanana mumaspredishiti maviri. Mumuenzaniso unotevera, izvi zvinoreva chikamu che "email" paSheet 2, chinoitika zvakare kuva Column C. Saka: Sheet2! C: C.

Paunenge iwe uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchitaura

14. Shandisa iyo COUNTIF basa kuti ugadzire Excel kuverenga mazwi kana manhamba mune chero maseru maseru.

Panzvimbo pekuverenga pamurume kuti kangani imwe nhamba kana nhamba inoonekwa, rega Excel ikuitire basa. Nebasa re COUNTIF, Excel inogona kuverenga nhamba yenguva izwi kana nhamba inowanikwa mune chero masero.

Semuenzaniso, ngatitii ndinoda kuverenga iyo nhamba yenguva izwi iro "Gryffindor" rinowanikwa mune yangu data set.

Fomura: = COUNTIF (renji, maitiro)

Iyo fomula ine akasiyana kubva pamuenzaniso wedu pazasi: = COUNTIF (D: D, "Gryffindor")

Mune fomula iyi, pane akasiyana misiyano:

dungwerungweRudzi rwatinoda kuti fomula ifukidze. Mune ino kesi, sezvo isu tichingotarisa pane imwe kholamu, tinoshandisa "D: D" kuratidza kuti yekutanga neyokupedzisira ikholamu ese ari maviri D. Dai ndaive ndakatarisa pamakoramu C uye D, ini ndaishandisa "C: D . ”
Zvikonzero: Chero nhamba kana chidimbu chemavara iwe chaunoda kuti Excel iverenge. Ingoshandisa makotesheni emakotesheni kana iwe uchida kuti mhedzisiro ive chinyorwa panzvimbo yenhamba. Mumuenzaniso wedu, iyo nzira ndeye "Gryffindor."

Kungo nyora mu formula COUNTIF mune chero sero uye kudzvanya "Pinda" kunondiratidza kangani izwi rekuti "Gryffindor" rinowanikwa mune dhata.

15. Sanganisa masero uchishandisa &.

Databases inowanzo kupatsanura dhata kuti iite sezvazviri sezvazvinogona. Semuenzaniso, pachinzvimbo chekuva nekoramu inoratidza zita rizere remunhu, dhatabhesi rinogona kunge riine data sezita rekutanga uyezve zita rekupedzisira mumakoramu akapatsanurwa. Kana, inogona kuve nenzvimbo yemunhu yakaparadzaniswa neguta, nyika, uye zip kodhi. MuExcel, unogona kusanganisa maseru ane data rakasiyana kuita sero rimwe uchishandisa chiratidzo "&" mune rako basa.

Iyo fomula ine akasiyana kubva pamuenzaniso wedu pazasi: = A2 & "" & B2

Ngatipfuurei fomula pamwe chete tichishandisa muenzaniso. Kuita sekuti isu tinoda kusanganisa ekutanga mazita uye ekupedzisira mazita mumazita akazara mukoramu imwe chete. Kuti tiite izvi, isu tinotanga taisa chitupa chedu muchitokisi chisina chinhu patinoda kuti zita rizere rioneke. Tevere, tinosimbisa sero rimwe rine zita rekutanga, nyora muchiratidzo che "&", tobva taratidza sero rine zita rekupedzisira rinoenderana.

Asi hauna kupera - kana zvese zvaunonyora mu = A2 & B2, ipapo hakuzovi nenzvimbo pakati pezita remunhu uye zita rekupedzisira. Kuwedzera iyo inodiwa nzvimbo, shandisa basa racho = A2 & "" & B2. Makotesheni akakomberedza nzvimbo anotaurira Excel kuti iise nzvimbo pakati pezita rekutanga uye rekupedzisira.

Kuti uite izvi zvive zvechokwadi pamitsara yakawanda, ingo dhonza kona yeiyo yekutanga sero pasi pasi sezvakaratidzwa mumuenzaniso.

16. Wedzera mabhokisi ekutarisa.

Kana iwe uri kushandisa Excel pepa kuteedzera yevatengi data uye uchida kutarisa chimwe chinhu chisingaverengeke, unogona kuisa mabhokisi ekutarisa mukolamu.

Semuenzaniso, kana urikushandisa pepa reExcel kugadzirisa tarisiro yako yekutengesa uye uchida kuona kana wakavafonera mukota yekupedzisira, unogona kuve ne "Inodaidzwa kota ino?" column uye tarisa maseru ari mairi kana iwe uchinge wafonera mutengi akakodzera.

Heano maitiro ekuzviita.

Simbisa sero raungade kuwedzera mabhokisi ekutarisa mune rako spreadsheet. Wobva wadzvanya DEVELOPER. Ipapo, pasi peFOMU DZIDZISO, tinya bhokisi rekutarisa kana denderedzwa rekusarudzwa rakaratidzwa mumufananidzo pazasi.

Kana bhokisi rangoonekwa muchitokisi, rikope, simbisa iwo maseru iwe aunodawo kuti aoneke maari, uye woisa.

17. Hyperlink sero kune webhusaiti.

Kana iwe uri kushandisa yako pepa kuteedzera enhau midhiya kana webhusaiti metric, zvinogona kubatsira kuve nereferensi ikholamu ine zvinongedzo mutsara wega uri kuteedzera. Kana iwe ukawedzera URL yakananga kuExcel, inofanira kungoita nyore kubatika. Asi, kana iwe uchifanira kuisa hyperlink mazwi, senge peji rezita kana musoro wenyaya wepositi iwe yauri kuteedzera, heino maitiro.

Simbisa mazwi aunoda kuisa hyperlink, wobva wadzvanya Shift K. Kubva ipapo bhokisi rinobuda richikubvumidza iwe kuisa iyo hyperlink URL. Kopa uye unamate iyo URL mubhokisi iri uye rova kana tinya Enter.

Kana kiyi yekudimbudzira isiri kushanda chero chikonzero, iwe unogona zvakare kuita izvi nemaoko nekusimbisa sero nekudzvanya Insert > Hyperlink.

18. Wedzera menyu ekudonhedza pasi.

Dzimwe nguva, iwe unenge uchishandisa yako spreadsheet kuteedzera maitiro kana zvimwe zvehunhu zvinhu. Panzvimbo pekunyora mazwi mupepa rako uchidzokororazve, senge "Hongu", "Kwete", "Mutengo weVatengi", "Kutungamira Kwekutengesa", kana "Tarisiro", unogona kushandisa mamenu ekudonhedza kumaka zvinhu zvinotsanangudza nezve vako vaunosangana navo kana chero chaunowana 'tiri kutevera.

Heano maitiro ekuwedzera donhwe-pasi kumasero ako.

Simbisa maseru aunoda kuti madonhwe ave mukati mawo, wobva wadzvanya iyo Dhizaini menyu mune yekumusoro yekufambisa uye pinda Validation.

Kubva ipapo, iwe uchaona yeDhivhidhi Yekusimbisa Zvirongwa bhokisi rakavhurwa. Tarisa pane tendera sarudzo, wobva wadzvanya Lists uye sarudza Kudonhedza-pasi Chinyorwa. Tarisa iyo In-Cell inodonhedza bhatani, wobva wadzvanya OK.

Dzimwe Excel Rubatsiro Zviwanikwa

Shandisa Excel Kugadzirisa Maitiro muChikwata Chako

Kunyangwe usiri accountant, iwe uchiri kugona kushandisa Excel kugadzirisa mabasa uye maitiro muchikwata chako. Nemazano uye manomano atakagovana mune ino posvo, iwe uchave neshuwa yekushandisa Excel kune yakazara zvizere uye uwane zvakanyanya kubva pasoftware kuti ikure bhizinesi rako.

Article Yepakutanga