Mukati
Muchikamu chino, iwe uchadzidza nzira mbiri dzinokurumidza dzekuisa fomula imwechete kana zvinyorwa mumaseru akawanda kamwechete muExcel. Izvi zvinobatsira mumamiriro ezvinhu apo iwe unoda kuisa fomula mumaseru ose mumutsara, kana kuzadza masero asina chinhu ane kukosha kwakafanana (semuenzaniso, "N/A"). Maitiro ese ari maviri anoshanda muMicrosoft Excel 2, 2013, 2010 uye yapfuura.
Kuziva aya akareruka matipi kunokuchengetedza iwe nguva yakawanda yezvimwe zvinonakidza zviitiko.
Sarudza maseru ese aunoda kuisa data rakafanana
Idzi ndidzo nzira dzinokurumidza kuratidza maseru:
Sarudza chikamu chose
- Kana iyo data muExcel yakagadzirwa setafura yakazara, ingodzvanya pane chero sero yekoramu yaunoda wodzvanya Ctrl+Space.
Cherechedza: Paunosarudza chero sero mutafura yakazara, boka rematebhu rinoonekwa paMenu Ribhoni Shanda nematafura (Table Tools).
- Kana iyi iri nhanho yenguva dzose, kureva kuti kana imwe yesero yerudzi urwu yasarudzwa, boka rematebhu Shanda nematafura (Table Tools) haioneki, ita zvinotevera:
Cherechedza: Nehurombo, mune iyo iri nyore renji, kudzvanya Ctrl+Space ichasarudza maseru ese ekoramu mupepa, semuenzaniso kubva C1 to C1048576, kunyangwe data iri mumaseru chete C1:C100.
Sarudza sero rekutanga rekoramu (kana yechipiri, kana sero rekutanga rakagarwa nemusoro), wobva wadzvanya. Shift+Ctrl+Endkusarudza ese masero etafura kusvika kure kurudyi. Tevere, kubata kusuduruka, dzvanya kiyi kakawanda museve wakasarakutozosvikira koramu yaunoda chete inoramba yakasarudzwa.
Iyi ndiyo nzira inokurumidza yekusarudza maseru ese mukoramu, kunyanya kana iyo data ichipindirana nemaseru asina chinhu.
Sarudza mutsara wose
- Kana iyo data muExcel yakagadzirwa setafura yakazara-yakazara, ingo dzvanya pane chero sero yemutsara waunoda uye tinya. Shift+Space.
- Kana iwe uine yakajairika data renji pamberi pako, tinya yekupedzisira sero yemutsara waunoda uye tinya Shift+Kumba. Excel ichasarudza renji kutanga kubva pasero rawakatsanangura uye kusvika kukoromo А. Kana iyo data inodiwa ikatanga, semuenzaniso, nekoramu B or C, pinch kusuduruka wobva wadzvanya kiyi Miseve yakaruramakusvika wawana mhedzisiro yaunoda.
Kusarudza maseru akawanda
bata Ctrl uye tinya bhatani rekuruboshwe mbeva pamaseru ese anoda kuzadzwa nedata.
Sarudza tafura yose
Dzvanya pane chero sero mutafura wodzvanya Ctrl + A.
Sarudza maseru ese papepa
dzvanya Ctrl + A kamwechete kusvika katatu. Kutanga kudzvanya Ctrl + A inoratidza nzvimbo yazvino. Kudzvanya kechipiri, kuwedzera kune yazvino nzvimbo, inosarudza mitsara ine misoro uye yakazara (somuenzaniso, mumatafura akazara). Muchina wechitatu unosarudza pepa rose. Ndinofunga wakazvifungidzira, mune mamwe mamiriro zvinotora iwe chete kudzvanya kamwe chete kuti usarudze shiti rese, uye mune mamwe mamiriro zvinotora akawanda seatatu kudzvanya.
Sarudza maseru asina chinhu munzvimbo yakapihwa (mumutsara, mumutsara, mutafura)
Sarudza nzvimbo yaunoda (ona mufananidzo uri pazasi), semuenzaniso, koromo yese.
dzvanya F5 uye mubhokisi rinoonekwa Shanduko (Enda ku) dzvanya bhatani Highlight (Special).
Mubhokisi rehurukuro Sarudza boka remasero (Go To special) tarisa bhokisi Masero asina chinhu (Zvisina chinhu) uye kanya OK.
Iwe unozodzokera kune ekugadzirisa maitiro eExcel sheet uye iwe uchaona kuti chete masero asina chinhu anosarudzwa munzvimbo yakasarudzwa. Masero matatu asina chinhu ari nyore kwazvo kusarudza nekudzvanya mbeva - iwe uchataura uye iwe uchave wakarurama. Asi ko kana paine anopfuura mazana matatu masero asina chinhu uye akapararira zvisina tsarukano pamusoro pehuwandu hwe300 maseru?
Inokurumidza nzira yekuisa fomula mumaseru ese ekoramu
Kune tafura hombe, uye iwe unofanirwa kuwedzera koramu nyowani ine imwe formula kwairi. Ngatitii iyi rondedzero yemakero eInternet pauri kuda kubvisa mazita emadomain kuti uenderere mberi nebasa.
- Shandura huwandu kune tafura yeExcel. Kuti uite izvi, sarudza chero sero mune data renji uye tinya Ctrl + Tkuunza dialog box Kugadzira tafura (Gadzira Tafura). Kana iyo data iine misoro yemusoro, tarisa bhokisi Tafura ine misoro (Tafura yangu ine misoro). Kazhinji Excel inoziva iyo misoro otomatiki, kana izvo zvisingashande, tarisa bhokisi nemawoko.
- Wedzera koramu itsva patafura. Netafura, kushanda uku kuri nyore kupfuura nedhadha yakapusa. Tinya-kurudyi pane chero sero mukoramu inouya mushure mekuda kuisa iyo nyowani nyowani, uye kubva pane yemukati menyu sarudza. Insert > Koramu kuruboshwe (Isa > Tafura Column Kuruboshwe).
- Ipa zita kuchikamu chitsva.
- Isa fomula musero rekutanga rekoramu itsva. Mumuenzaniso wangu, ini ndinoshandisa fomula kuburitsa mazita emazita:
=MID(C2,FIND(":",C2,"4")+3,FIND("/",C2,9)-FIND(":",C2,"4")-3)=ПСТР(C2;НАЙТИ(":";C2;"4")+3;НАЙТИ("/";C2;9)-НАЙТИ(":";C2;"4")-3) - dzvanya pinda. Voila! Excel yakazadza otomatiki mumaseru asina chinhu mukoromo itsva ine fomula imwechete.
Kana iwe ukafunga kudzoka kubva patafura kuenda kune yakajairwa renji fomati, wobva wasarudza chero sero mutafura uye pane tab Constructor (Design) tinya Shandura kuhuwandu (Shandura kuhuwandu).
Uhwu hunyengeri hunogona kushandiswa chete kana maseru ese ari mukoramu asina chinhu, saka zvakanaka kuti uwedzere mutsara mutsva. Icho chinotevera chakanyanya kuwanda.
Namira iyo data yakafanana mumasero akati wandei uchishandisa Ctrl + Enter
Sarudza maseru paExcel sheet yaunoda kuzadza neiyo data yakafanana. Maitiro anotsanangurwa pamusoro apa achakubatsira iwe nekukurumidza kusarudza masero.
Ngatitii tine tafura ine runyorwa rwevatengi (isu, chokwadi, tichatora runyorwa rwekunyepedzera). Imwe yemakoramu etafura iyi ine masaiti kwakabva vatengi vedu. Masero asina chinhu ari muchikamu chino anofanira kuzadzwa nemavara “_unknown_” kuti zvifambire mberi kuronga:
- Sarudza maseru asina chinhu ari mukoramu.
- dzvanya F2kugadzirisa sero rinoshanda, uye kuisa chimwe chinhu mariri: inogona kuve mavara, nhamba, kana fomula. Kwatiri, ichi ndicho chinyorwa "_unknown_".
- Zvino panzvimbo yekuti pinda watinya Ctrl + Pinda. Masero ese akasarudzwa anozadzwa nedata rakapinda.
Kana iwe uchiziva dzimwe nzira dzekukurumidza dzekupinda data, tiudze nezvadzo mumashoko. Ndichavawedzera nemufaro kuchinyorwa chino, ndichidudza iwe semunyori.