Mukati
Ngatitii tinoda kukurumidza uye nekuedza kushoma kugadzira karenda repagore rinoratidza otomatiki mazuva ematanho eprojekiti (kana zororo revashandi, kana kudzidziswa, nezvimwewo)
workpiece
Ngatitangei pasina:
Sezvauri kuona, zvese zviri nyore pano:
- Mitsara mwedzi, makoramu mazuva.
- Sero A2 rine gore iro karenda iri kuvakirwa. Mumasero A4: A15 - nhamba dzebetsero dzemwedzi. Tichada ese ari maviri gare gare kuti tigadzire misi mukarenda.
- Kurudyi kwetafura kune mazita ematanho ane mazuva ekutanga neanogumira. Unogona kupa maseru asina chinhu pamberi pematanho matsva akawedzerwa mune ramangwana.
Kuzadza karenda nemazuva nekuaviga
Zvino ngatizadze karenda redu nemazuva. Sarudza yekutanga sero C4 uye pinda basa ipapo DATE (DATE), iyo inogadzira zuva kubva pagore, mwedzi, uye nhamba yezuva:
Mushure mekupinda fomula, inofanirwa kukopwa kune yese kubva muna Ndira 1 kusvika Zvita 31 (C4: AG15). Sezvo maseru ari akamanikana, pachinzvimbo chemazuva akagadzirwa, tichaona hashi mamaki (#). Nekudaro, kana iwe ukatenderedza mbeva yako pamusoro pesero yakadaro, unogona kuona zvayo zvirimo muchokushandisa:
Kuti tichengetedze magridi kubva munzira, tinogona kuvanza nemaitiro akangwara etsika. Kuti uite izvi, sarudza mazuva ese, vhura hwindo Sero Format uye pa tab nhamba (nhamba) sarudza sarudzo Mafomu ese (Tsika). Zvino mumunda Rudzi isa semicolons matatu mumutsara (hapana nzvimbo!) uye tinya OK. Zviri mukati memasero zvichavanzwa uye magiradhi achanyangarika, kunyange zvazvo mazuva ari mumasero, chaizvoizvo, acharamba ari - izvi zvinongoonekwa chete.
Stage kuonesa
Ikozvino, tichishandisa mafomati ezvimiso, ngatiwedzerei kutarisisa kumaseru ane mazuva akavanzika. Sarudza mazuva ese ari murudzi C4:AG15 uye sarudza pane tab Musha — Conditional Formatting — Gadzira Rule (Kumba - Conditional fomati - Gadzira Rule). Muhwindo rinovhurwa, sarudza sarudzo Shandisa fomula kuti uone kuti ndeapi maseru ekufomatidza (Shandisa fomula kuverengera masero ekufomatidza) uye isa iyo formula:
Iyi fomula inotarisa sero rega rega kubva kuC4 kusvika pakupera kwegore kuti rione kana richiwira pakati pekutanga uye kupera kwechimwe chiitiko chakakosha. Iyo inobuda ichave 4 chete kana ese akatariswa mamiriro mumabhuraketi (C4>=$AJ$13:$AJ$4) uye (C4<=$AK$13:$AK$1) aburitsa TRUE ine musoro, inodudzirwa naExcel se0 ( zvakanaka , FALSE yakafanana ne4, hongu). Zvakare, nyatso teerera kune chokwadi chekuti mareferenzi ekutanga sero CXNUMX ane hukama (pasina madhora), uye kumatanho ematanho - absolute (ane maviri $).
Mushure mekunge mavharidza OK tichaona zvakakosha mukarenda yedu:
Kuratidzira mharadzano
Kana mazuva emamwe matanho akapindirana (vaverengi vanonyatsoteerera vanofanira kunge vakatoona nguva ino yedanho rekutanga nerechitanhatu!), saka zvingava nani kuratidza kusawirirana uku muchati yedu nemuvara wakasiyana uchishandisa mumwe mutemo wekufomatidza. Inongoita imwe-kune-imwe yakafanana neyakapfuura, kunze kwekuti isu tiri kutsvaga maseru anosanganisirwa mune inodarika nhanho imwe:
Mushure mekunge mavharidza OK mutemo wakadaro ucharatidza zvakajeka kupindirana kwemazuva mukarenda yedu:
Kubvisa mamwe mazuva mumwedzi
Zvechokwadi, haisi mwedzi yose ine mazuva makumi matatu nerimwe, saka mamwe mazuva aFebruary, April, June, nezvimwewo. Zvingave zvakanaka kutarisa sezvisina basa. Function DATE, iyo inoumba karenda yedu, mumasero akadaro achashandura zuva racho kumwedzi unotevera, kureva February 30, 2016 ichava March 1. Kureva, nhamba yemwedzi yemasero akadaro haizoenzana nenhamba yemwedzi iri muchikamu A. Izvi zvinogona kushandiswa paunenge uchigadzira mutemo une conditional fomati kusarudza maseru akadaro:
Kuwedzera kupera kwevhiki
Sarudzo, iwe unogona kuwedzera kukarenda yedu uye kupera kwevhiki. Kuti uite izvi, unogona kushandisa basa ZUVA (WEEKDAY), iyo ichaverenga nhamba yezuva revhiki (1-Mon, 2-Tue…7-Sun) yezuva rega rega uye kusimbisa ayo anowira neMugovera neSvondo:
Nekuratidzwa kwakaringana, usakanganwa kunyatsogadzirisa kurongeka kwakaringana kwemitemo muhwindo. Musha — Conditional Formatting — Manage Rules (Kumba - Conditional fomati - Tonga Mitemo), nekuti mitemo nekuzadza zvinoshanda chaizvo mukutevedzana kunonzwisisika kwauchagadzira munhaurirano iyi:
- Vhidhiyo chidzidzo chekushandisa zvimiro zvekufometa muExcel
- Magadzirirwo eProjekti Schedule (Gantt Chati) Uchishandisa Mamiriro Ekuita
- Maitiro ekugadzira chirongwa chenguva muExcel