Mukati
Kana uine runyoro rwakakura rwakarongwa neimwe koramu, zvino zvingave zvakanaka kuti uzviparadzanise otomatiki mitsara inoguma nekupatsanura mitsetse yakachinjika kuti ijekeswe:
Mumuenzaniso uri pamusoro apa, iyi mitsetse pakati penyika, asi, kazhinji, pakati pezvimwe zvinhu zvakadzokororwa mumutsara mumwe chete. Ngatitarisei nzira shoma dzekuita izvi.
Nzira 1. Nyore
Iyo inokurumidza nzira yekuita izvi iri nyore kwazvo nemamiriro ekugadzirisa, ayo anozodhirowa muganho wepasi wemaseru kana zviri mukati mesero mukoromo A zvisina kuenzana nezviri musero rinotevera mukoramu imwechete. Sarudza maseru ese ari patafura kunze kwemusoro uye sarudza huru command tab Conditional Formatting - Gadzira Rule (Kumba - Conditional Formatting - New Rule). Sarudza rudzi rwemutemo Shandisa fomula kuti uone kuti ndeapi maseru ekufomatidza (Shandisa fomula kuona kuti ndeapi maseru ekufomatidza) uye isa inotevera fomula mumunda:
Nyatsoteerera madhora mumakero ekugadzirisa mavara emakoroni, asi kwete nhamba dzemutsara, nokuti. tinongoenzanisa nyika dziri muchikamu A. Panofanira kunge pasina nzvimbo mufomula.
Dzvanya bhatani Framework (Format) uye muhwindo rakavhurwa pane tab mupendero (Miganhu) vhura mutsara wemuvara waunoda pamuganhu wepasi. Mushure mekudzvanya pa OK mutemo wedu uchashanda uye yakachinjika dashing mitsetse ichaonekwa pakati pemapoka emitsetse
Method 2. Nesefa tsigiro yenhamba nemazuva
Chinhu chidiki asi chinoonekwa zvakanyanya chenzira yekutanga ndechekuti miganho yakadaro haishande nemazvo kana uchisefa rondedzero nemamwe makoramu. Saka, semuenzaniso, kana tikasefa tafura yedu nemazuva (Ndira chete), ipapo mitsetse haichazoonekwa pakati penyika dzese, sepakutanga:
Muchiitiko ichi, unogona kubuda uchishandisa basa SUBTOTALS (ZVIRI MUKATI), iyo inogona kuita akasiyana masvomhu mabasa (sum, avhareji, kuverenga, zvichingodaro), asi "ona" chete masero akasefa. Semuenzaniso, ngatirongei tafura yedu nekoramu yekupedzisira ine zuva uye todhirowa mutsetse wekuparadzanisa pakati pemazuva. Mukugadzirisa mafomati, iwe uchafanirwa kugadzira mutemo wakafanana neyekutanga nzira, asi usashandise zvakananga zvinongedzo mukuenzanisa maseru D2 neD3, asi vavhare senharo muSUBTOTAL basa:
Yekutanga gakava rebasa (nhamba 109) ndeyekupfupisa opcode. Muchokwadi, isu hatiwedzere chero chinhu pano uye tinoita, kutaura zvazviri, basa reupenzi seSUM (D2), iro, chokwadi, rakaenzana neD2. Asi basa iri rinosiyana neSUM chaizvo pakuti rinoita zviito chete pamaseru anooneka, kureva kuti masero akasara mushure mesefa pachiratidziro achaenzaniswa, ndizvo zvataida.
Nzira 3. Nerutsigiro rwesefa kune chero data
Sezvauri kuona zviri nyore, nzira yechipiri inewo dhizaini: iyo sum basa inogona kungoshandiswa kune manhamba kana mazuva (ayo ariwo manhamba muExcel), asi kwete kunyora. Ndiko kuti, kana tichida kudhirowa mutsara pakati penyika, senzira yekutanga, asi kuti ionekwe nemazvo mushure mekusefa, isu tichafanirwa kushandisa nzira yakaoma kwazvo. Sarudza iyo tafura yese zvakare kunze kweiyo musoro, gadzira mutemo mutsva unoenderana nefomati uye isa chinotevera chivakwa mundima yekusimbisa:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Mushanduro yeChirungu ichave:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Nekudzvanya bhatani Framework (Format) isa muganhu nemutsara mutsvuku pamusoro uye baya OK. Iyo kupatsanurwa kunoguma nenyika kuchashanda nemazvo kunyangwe mushure mekusefa, semuenzaniso, nemazuva:
- Simbisa Mazuva uye Nguva neConditional Formatting
- Iyo Excel inoshanda sei nemazuva uye nguva
- Mashandisiro ekugadzirisa mafomati kuratidza maseru nemamiriro muExcel