Chati nesero rakasarudzwa

Ngatiti iwe neni tinoda kuona data kubva patafura inotevera ine mutengo wekutengesa mota nenyika dzakasiyana muna 2021 (data chairo rakatorwa kubva pano, nenzira):

Sezvo nhamba yedata yakatevedzana (nyika) yakakura, kuyedza kupinza ese mugirafu imwe kamwechete zvinotungamira kune inotyisa "spaghetti chati" kana kuvaka machati akaparadzana ega ega akateedzana, izvo zvinonetsa.

Mhinduro yakanakisa yedambudziko iri inogona kuve kuronga chati chete pane data kubva kumutsara wazvino, kureva mutsara unowanikwa sero rinoshanda:

Kuita izvi kuri nyore kwazvo - iwe unongoda maviri mafomula uye imwe diki macro mumitsetse mitatu.

Nhanho 1. Nhamba yemutsara yazvino

Chinhu chekutanga chatinoda izita rezita iro rinoverengera nhamba yemutsara pane pepa panowanikwa sero redu rinoshanda. Kuvhura pane tab Mafomura - Maneja Wezita (Mafomura - Maneja wezita), tinya bhatani sika (Gadzira) uye isa chimiro chinotevera ipapo:

Danho 2. Batanidza kune musoro

Kuti tiratidze nyika yakasarudzwa mumusoro uye ngano yechati, isu tinofanirwa kuwana rengedzo kune sero ine (nyika) zita kubva pakoramu yekutanga. Kuti tiite izvi, tinogadzira imwe yenzvimbo (kureva nharaunda = shizha razvino, kwete Bhuku!) rondedzero ine mazita anotevera:

Pano, INDEX basa rinosarudza kubva pachikamu chakapihwa (column A, panogara nyika dzedu dzekusaina) sero rine nhamba yemutsara yatakasarudza kare.

Nhanho 3. Batanidza kune data

Iye zvino, nenzira yakafanana, ngatitorei chibatanidzo kune chiyero nezvose zvekutengesa data kubva mumutsara wezvino, apo sero rinoshanda ikozvino riripo. Gadzira imwe zita renji neinotevera fomula:

Pano, nharo yechitatu, inova zero, inoita kuti INDEX idzore kwete imwe kukosha, asi mutsara wose semugumisiro.

Nhanho 4. Kuisa Manongedzo muChati

Zvino sarudza musoro wetafura uye mutsara wekutanga une data (renji) uye gadzira chati yakavakirwa pavari kushandisa Pinza – Machati (Isa — Machati). Kana ukasarudza mutsara une data mune chati, ipapo basa racho richaratidzwa mubhari yefomula mutsara (SERIES) ibasa rakakosha rinoshandiswa neExcel otomatiki paunenge uchigadzira chero chati kureva kune yekutanga data uye zvinyorwa:

Ngatinyatso kutsiva yekutanga (siginicha) neyechitatu (data) nharo muchiitiko ichi nemazita ezvikamu zvedu kubva panhanho 2 ne3:

Iyo chati ichatanga kuratidza data yekutengesa kubva mumutsara wazvino.

Danho 5. Recalculation Macro

Kubata kwekupedzisira kunoramba kuri. Microsoft Excel inoverengera mafomula chete kana data riri papepa rachinja kana kiyi yadzvanywa F9, uye tinoda kuti kuverengera kuitike kana sarudzo yachinja, kureva kana sero rinoshanda richifambiswa nepakati pebepa. Kuti tiite izvi, tinoda kuwedzera macro iri nyore kubhuku redu rebasa.

Tinya-kurudyi pane data sheet tab uye sarudza rairo mabviro (Source code). Muhwindo rinovhura, isa kodhi ye macro-handler yechiitiko chekuchinja sarudzo:

Sezvaunogona kufungidzira zviri nyore, zvese zvazvinoita kukonzeresa pepa recalculation pese pachinja chinzvimbo chesero rinoshanda.

Nhanho 6. Kuratidzira Mutsetse Wazvino

Kuti ujekese, unogonawo kuwedzera mutemo wekufomatidza une zvisungo kuratidza nyika iri kuratidzwa pachati. Kuti uite izvi, sarudza tafura uye sarudza Musha — Conditional Formatting — Gadzira Rule — Shandisa Formula Kuona Masero eKufomati (Kumba - Conditional fomati - Mutemo mutsva - Shandisa fomula kuona kuti ndeapi maseru ekufomatidza):

Pano chimiro chinotarisa sero rimwe nerimwe riri patafura kuti nhamba yemutsara wayo inoenderana nenhamba yakachengetwa muTekRow variable, uye kana pane machisi, ipapo kuzadza nemuvara wakasarudzwa kunotanga.

Ndizvozvo - zviri nyore uye zvakanaka, handiti?

Notes

• Pamatafura mahombe, runako urwu rwese runogona kudzikira - mafomati ezvimiso chinhu chinowedzera zviwanikwa, uye kuverengera pasarudzo yega yega kunogona zvakare kurema.
• Kudzivirira data kubva pakunyangarika pachati kana sero rasarudzwa netsaona pamusoro kana pazasi petafura, unogona kuwedzera cheki kuzita reTekRow uchishandisa nested IF mabasa efomu:

  =IF(CELL(“row”)<4,IF(CELL("row")>4,CELL(“mutsara”)))
  

• Kuratidzira makoramu akatsanangurwa muchati
• Maitiro ekugadzira inodyidzana chati muExcel
• Coordinate Selection