Maitiro ekudzima masero asina chinhu muExcel. 3 Nzira dzekubvisa Masero asina Kuchena muExcel

Kana uchiendesa tafura kubva kune yekunze sosi kuenda kuExcel, mamiriro ezvinhu anowanzo simuka nekuchinja kwemasero ane ruzivo uye kuumbwa kwevoids. Paunenge uchishandisa mafomula, rimwe basa harigoneki. Panyaya iyi, mubvunzo unomuka: iwe unogona sei kukurumidza kubvisa masero asina chinhu?

Cases umo zvinokwanisika kudzima blank masero

Panguva yekushanda, kuchinja kwemashoko kunogona kuitika, izvo zvisingadiwi. Kubvisa kunoitwa chete mune zvimwe zviitiko, semuenzaniso:

• Hapana ruzivo mumutsara wese kana koramu.
• Hapana hukama hune musoro pakati pemasero.

Iyo yekare nzira yekubvisa voids chinhu chimwe panguva. Iyi nzira inogoneka kana ukashanda nenzvimbo dzinoda kugadziridzwa kudiki. Kuvapo kwenhamba huru yemasero asina chinhu kunotungamirira kukuda kwekushandisa nzira yekubvisa batch.

Solution 1: bvisa nekusarudza boka remasero

Nzira iri nyore ndeyekushandisa chishandiso chakakosha chekusarudza mapoka emasero. Execution process:

1. Sarudza nzvimbo ine dambudziko umo maseru asina chinhu akaungana, wobva wadzvanya F5 kiyi.

2. Chidzitiro chinofanira kuvhura hwindo rinotevera rekuraira. Dzvanya pane inopindirana Sarudza bhatani.
3. Chirongwa chichavhura rimwe hwindo. Sarudza "Empty Cells". Tarisa bhokisi uye tinya OK.
4. Pane otomatiki sarudzo yenzvimbo dzisina kuzadzwa. Kudzvanya-kurudyi pane chero nzvimbo isiri-ruzivo kunomutsa kuvhurwa kwehwindo kwaunoda kudzvanya "Delete".
5. Zvadaro, "Delete Cells" ichavhura. Isa chiratidzo padivi pe "Masero ane shift kumusoro." Tinobvumirana nekudzvanya bhatani "OK".

6. Nekuda kweizvozvo, chirongwa chinozobvisa otomatiki nzvimbo dzinoda kugadziriswa.
7. Kuti ubvise sarudzo, tinya LMB chero patafura.

Ziva! Nzira yekudzima nekuchinja inosarudzwa chete mumamiriro ezvinhu apo pasina mitsetse mushure mekusarudza nzvimbo inotakura chero ruzivo.

Solution 2: Shandisa Sefa uye Conditional Formatting

Iyi nzira inonyanya kuoma, saka, usati waenderera mberi nekugadzirisa, zvinokurudzirwa kuti iwe utange kuzviziva nehurongwa hwakadzama hwekuita kwechiito chimwe nechimwe.

Chenjerera! Chinonyanya kukanganisa nzira iyi ndeyokuti inoshandiswa kushanda nekoramu imwe chete isina mafomula.

Funga nezvetsananguro inotevedzana yekusefa data:

1. Sarudza nzvimbo yekoramu imwe. Tsvaga chinhu "Editing" pane toolbar. Nekudzvanya pairi, hwindo rine runyoro rwezvirongwa richaonekwa. Enda kune "Ronga uye Sefa" tab.

2. Sarudza sefa uye shandisa LMB.

3. Somugumisiro, sero repamusoro rinoshandiswa. Chiratidzo chakaita sikweya chine museve wepasi chichaonekwa padivi. Izvi zvinoratidza mukana wekuvhura hwindo nemamwe mabasa.
4. Dzvanya pabhatani uye mune iyo tebhu inovhura, bvisa bhokisi riri padyo nenzvimbo ye "(Empty)", tinya "OK".

5. Mushure mekuita manipulations, maseru akazadzwa chete ndiwo acharamba ari mukoramu.

Mazano enyanzvi! Kubvisa voids uchishandisa kusefa kwakakodzera chete kana pasina masero akazadzwa akatenderedza, zvikasadaro, kana uchiita nzira iyi, data rese richarasika.

Zvino ngatitarisei maitiro ekuita mafomati ezvimiso pamwe nekusefa:

1. Kuti uite izvi, sarudza nzvimbo ine dambudziko uye, wawana "Styles" toolbar, shandisa "Conditional Formatting" bhatani.

2. Muhwindo rinovhura, tsvaga mutsara "Zvimwe" uye tevera iyi link.
3. Tevere, muhwindo rinoonekwa mumunda wekuruboshwe, isa kukosha "0". Mumunda wekurudyi, sarudza sarudzo yekuzadza ruvara iwe yaunoda kana kusiya iyo default tsika. Tinya "OK". Nekuda kweizvozvo, maseru ese ane ruzivo anozopendwa nemuvara waunosarudza.
4. Kana iyo purogiramu ikabvisa yakamboitwa kusarudzwa, tinoigadzira zvakare uye shandisa "Filter" tool. Hover pamusoro peiyo kukosha "Sefa nesero ruvara" kana nefonti uye shandisa imwe yezvinzvimbo.
5. Somugumisiro, masero chete ane mavara ane ruvara, uye naizvozvo akazadzwa nedata, acharamba aripo.

6. Sarudzazve nzvimbo ine ruvara neruvara uye tsvaga bhatani rekuti "Copy" kumusoro kwebhari rekushandisa, ridzvanye. Inomiririrwa nemashizha maviri akaiswa pamusoro peumwe neumwe.
7. Nekusarudza imwe nzvimbo papepa iri, tinoita imwe sarudzo.
8. Tinya-kurudyi kuti uvhure menyu, kwatinowana "Values". Iyo icon inoratidzwa muchimiro chehwendefa ine digital enumeration 123, tinya.

Ziva! Paunenge uchisarudza nzvimbo, zvinodikanwa kuti chikamu chepamusoro chiri pazasi pemutsara weiyo yakasimbiswa rondedzero.

9. Nekuda kweizvozvo, iyo data yakakopwa inotamiswa pasina kushandisa color filter.

Rimwezve basa rine data rinogona kuitwa munharaunda kana nekuiendesa kune imwe nzvimbo yepepa.

Solution 3: shandisa fomula

Kubvisa masero etafura asina chinhu nenzira iyi kune mamwe matambudziko uye naizvozvo haina kufarirwa. Dambudziko riri mukushandisa fomula, iyo inofanirwa kuchengetwa mune imwe faira. Ngatiende kuburikidza nekuita nenzira yakarongeka:

1. Sarudza huwandu hwemasero anoda kugadziriswa.
2. Tinobva tadzvanya-kurudyi uye towana murairo wekuti "Ipa zita." Isa zita kuchikamu chakasarudzwa, tinya OK.

4. Munzvimbo ipi neipi pane pepa, sarudza nzvimbo yakasununguka, iyo inoenderana nehukuru hwenzvimbo iyo kugadziriswa kunoitwa. Tinya-kurudyi woisa rimwe zita.

5. Mushure mekuda kumisa iyo yepamusoro sero yenzvimbo yemahara uye isa iyo formula mairi: =KANA(ROW() -ROW(Adjustment)+1>NOTROWS(LastNames)-COUNTBLLANK(LastNames);””;INDIRECT(ADDRESS(LOW((IF(LastNames<>“”),ROW(LastNames);ROW() + MITSARA(Surname));MUTSARA()-MUROW(Adjustment)+1);COLUMN(Surname);4))).

Ziva! Mazita enzvimbo anosarudzwa zvisina tsarukano. Mumuenzaniso wedu, aya ndiwo "Surname" uye "Adjustment".

6. Kana aya mafomula angopinzwa, tinya kiyi yekubatanidza "Ctrl + Shift + Enter". Izvi zvinodikanwa nekuti pane arrays mune formula.

Tambanudza sero repamusoro pasi kusvika kumiganhu yenzvimbo yakambotsanangurwa. Koramu ine data rakatamiswa inofanira kuratidzwa, asi isina maseru asina chinhu.

mhedziso

Kubvisa masero asina chinhu kunogoneka nenzira dzinoverengeka, imwe neimwe yadzo inosiyana muyero yekuoma, kuitira kuti vese vasina ruzivo uye vepamberi mushandisi wespredishiti vanogona kusarudza yakanyatsokodzera sarudzo ivo pachavo.