Contents
Idan kuna da babban jeri da aka jera ta wasu ginshiƙai, to zai yi kyau a raba jeri da aka samu ta atomatik tare da raba layin kwance don tsabta:
A cikin misalin da ke sama, waɗannan layuka ne tsakanin ƙasashe, amma, gabaɗaya, tsakanin kowane abu mai maimaitawa a cikin ginshiƙi ɗaya. Bari mu dubi wasu hanyoyi don aiwatar da wannan.
Hanyar 1. Sauƙi
Hanya mafi sauri don yin wannan abu ne mai sauƙi tare da tsara yanayin yanayi, wanda zai zana iyakar ƙasa na sel idan abun ciki na tantanin halitta a cikin shafi A bai daidaita da abun ciki na tantanin halitta na gaba a cikin wannan shafi ba. Zaɓi duk sel a cikin tebur banda taken kuma zaɓi babban umarnin tab Tsarin Yanayi - Ƙirƙiri Doka (Gida - Tsarin Sharadi - Sabon Doka). Zaɓi nau'in ƙa'ida Yi amfani da dabara don tantance waɗanne sel don tsarawa (Yi amfani da dabara don tantance wane sel don tsarawa) kuma shigar da dabara mai zuwa a cikin filin:
Kula da daloli a cikin adiresoshin don gyara haruffan shafi, amma ba lambobin layi ba, saboda. kawai muna kwatanta ƙasashe a shafi na A. Kada a sami sarari a cikin dabarar.
Latsa maɓallin tsarin (Format) kuma a cikin taga bude akan shafin Border (Borders) kunna layin launi da ake so akan iyakar ƙasa. Bayan danna kan OK mulkin mu zai yi aiki kuma a kwance layukan dashing za su bayyana tsakanin ƙungiyoyin layi
Hanyar 2. Tare da goyon bayan tace don lambobi da kwanakin
Ƙananan ƙananan amma sanannen hasara na hanyar farko shine cewa irin waɗannan iyakoki ba koyaushe za su yi aiki daidai ba yayin tace jerin ta wasu ginshiƙai. Don haka, alal misali, idan muka tace teburinmu da kwanan wata (Janairu kawai), to ba za a ƙara ganin layin a tsakanin dukkan ƙasashe ba, kamar yadda a da:
A wannan yanayin, zaku iya fita ta amfani da aikin SUBTOTALS (SUBTOTAL), wanda zai iya aiwatar da ayyuka daban-daban na lissafi ( jimla, matsakaita, ƙidaya, da sauransu), amma “duba” sel masu tacewa kawai. Misali, bari mu jera teburinmu ta ginshiƙi na ƙarshe tare da kwanan wata kuma mu zana layin rarraba tsakanin kwanakin. A cikin tsarin tsari, dole ne ku ƙirƙiri wata doka mai kama da hanyar farko, amma kar a yi amfani da hanyoyin haɗin kai tsaye wajen kwatanta sel D2 da D3, amma ku haɗa su azaman muhawara a cikin aikin SUBTOTAL:
Hujja ta farko na aikin (lamba 109) ita ce taƙaitaccen opcode. A gaskiya ma, ba mu ƙara wani abu a nan kuma muyi, a gaskiya, aikin wauta kamar SUM (D2), wanda, ba shakka, daidai yake da D2. Amma wannan aikin ya bambanta da SUM daidai da cewa yana yin ayyuka ne kawai akan ƙwayoyin da ake iya gani, watau kuma sel da suka rage bayan tacewa akan allon za a kwatanta, wanda shine abin da muke so.
Hanyar 3. Tare da goyon bayan tace don kowane bayanai
Kamar yadda kuke iya gani cikin sauƙi, hanya ta biyu kuma tana da koma baya: jimlar aikin ba za a iya amfani da shi kawai ga lambobi ko kwanan wata (wadanda kuma lambobi ne a cikin Excel), amma ba ga rubutu ba. Wato idan ana so a yi layi tsakanin kasashe, kamar yadda aka yi a hanya ta farko, amma ta yadda za a nuna shi daidai bayan an tace, to dole ne mu yi amfani da hanya mafi rikitarwa. Zaɓi gabaɗayan tebur ɗin kuma ban da kan taken, ƙirƙirar sabuwar doka bisa dabara kuma shigar da ginin mai zuwa a cikin filin tabbatarwa:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
A cikin harshen Ingilishi zai kasance:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Ta danna maballin tsarin (Format) saita iyaka mai jan layi a saman sannan danna OK. Sakamakon rabo ta ƙasa zai yi aiki daidai ko da bayan tacewa, misali, ta kwanan wata:
- Haskaka Kwanaki da Lokuta tare da Tsarin Sharadi
- Yadda Excel a zahiri ke aiki tare da kwanaki da lokuta
- Yadda ake amfani da tsari na yanayi don haskaka sel ta yanayi a cikin Excel