Zamkatimu
Ngati muli ndi mndandanda wawukulu wosanjidwa ndi gawo lina, ndiye kuti zingakhale bwino kuti musiyanitse mizere yotsatila ndikulekanitsa mizere yopingasa kuti imveke bwino:
Mu chitsanzo pamwambapa, izi ndi mizere pakati pa mayiko, koma, kawirikawiri, pakati pa zinthu zobwerezabwereza zomwe zili pamndandanda womwewo. Tiyeni tiwone njira zingapo zochitira izi.
Njira 1. Zosavuta
Njira yachangu kwambiri yochitira izi ndiyosavuta kwambiri ndi mawonekedwe okhazikika, omwe amajambula malire apansi a ma cell ngati zomwe zili mu cell mu gawo A sizingafanane ndi zomwe zili mu cell yotsatira mugawo lomwelo. Sankhani maselo onse mu tebulo kupatula mutu ndikusankha waukulu lamulo tabu Mapangidwe Okhazikika - Pangani Lamulo (Kunyumba - Mapangidwe Okhazikika - Lamulo Latsopano). Sankhani mtundu wa malamulo Gwiritsani ntchito fomula kuti mudziwe kuti ndi maselo ati omwe angasanjidwe (Gwiritsani ntchito fomula kuti mudziwe kuti ndi maselo ati) ndipo lowetsani fomula ili pansipa:
Samalani madola mu maadiresi kuti mukonze zilembo, koma osati manambala a mzere, chifukwa. timangofanizira mayiko omwe ali mugawo A. Pasakhale mipata mu fomula.
Dinani batani Makhalidwe (Mtundu) ndi pawindo lotseguka pa tabu Border (Malire) tsegulani mzere wa mtundu womwe mukufuna pamalire apansi. Pambuyo kuwonekera pa OK lamulo lathu lidzagwira ntchito ndipo mizere yopingasa yopingasa idzawonekera pakati pa magulu a mizere
Njira 2. Ndi chithandizo chosefera manambala ndi masiku
Choyipa chaching'ono koma chowoneka bwino cha njira yoyamba ndikuti malire otere sangagwire ntchito moyenera nthawi zonse posefa mndandanda ndi mizati ina. Kotero, mwachitsanzo, ngati tisefa tebulo lathu ndi madeti (Januware yekha), mizere sidzawonekanso pakati pa mayiko onse, monga kale:
Pankhaniyi, mukhoza kutuluka pogwiritsa ntchito ntchitoyi ZONSE ZONSE (SUBTOTAL), yomwe imatha kuchita masamu osiyanasiyana (chiwerengero, avareji, kuwerengera, ndi zina), koma "onani" maselo osefedwa okha. Mwachitsanzo, tiyeni tisankhe tebulo lathu potsatira gawo lomaliza ndi deti ndikujambulitsa mzere pakati pa masikuwo. Pamapangidwe okhazikika, muyenera kupanga lamulo lofanana ndi njira yoyamba, koma osagwiritsa ntchito maulalo achindunji poyerekeza ma cell D2 ndi D3, koma atsekeni ngati mfundo mu SUBTOTAL ntchito:
Mtsutso woyamba wa ntchitoyi (nambala 109) ndi opcode. M'malo mwake, sitikuwonjezera kalikonse pano ndikuchita, kwenikweni, ntchito yopusa ngati SUM (D2), yomwe, ndithudi, ndi yofanana ndi D2. Koma ntchitoyi imasiyana ndi SUM ndendende chifukwa imagwira ntchito pama cell owoneka okha, mwachitsanzo, maselo otsala pambuyo pa fyuluta pazenera adzafaniziridwa, zomwe tinkafuna.
Njira 3. Ndi chithandizo cha fyuluta pa data iliyonse
Monga mukuwonera mosavuta, njira yachiwiri ilinso ndi zovuta zake: kuchuluka kwa ntchito kumatha kugwiritsidwa ntchito ku manambala kapena masiku (omwe alinso manambala mu Excel), koma osati kulemba. Ndiko kuti, ngati tikufuna kujambula mzere pakati pa mayiko, monga njira yoyamba, koma kuti iwonetsedwe bwino pambuyo pa kusefa, ndiye kuti tigwiritse ntchito njira yovuta kwambiri. Sankhani tebulo lonse kachiwiri kupatula chamutu, pangani lamulo latsopano kutengera chilinganizo ndikulowetsa zomanga zotsatirazi mugawo lotsimikizira:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
M'Chingerezi zikhala:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Mwa kuwonekera pa batani Makhalidwe (Mtundu) ikani malire okhala ndi mzere wofiira pamwamba ndikudina OK. Kugawikana kotsatira ndi dziko kudzagwira ntchito moyenera ngakhale mutasefa, mwachitsanzo, pofika tsiku:
- Onetsani Madeti ndi Nthawi ndi Mapangidwe Okhazikika
- Momwe Excel imagwirira ntchito ndi masiku ndi nthawi
- Momwe mungagwiritsire ntchito masanjidwe okhazikika kuti muwunikire ma cell ndi chikhalidwe mu Excel