Kusanthula kwa ntchito ya INDIRECT mwa zitsanzo

Poyang'ana koyamba (makamaka powerenga chithandizo), ntchitoyo KUMALO (KODI) zikuwoneka zosavuta komanso zosafunikira. Chofunikira chake ndikutembenuza mawu omwe amawoneka ngati ulalo kukhala ulalo wathunthu. Iwo. Ngati tikufuna kulozera ku cell A1, ndiye kuti titha kupanga ulalo wachindunji (lowetsani chizindikiro chofanana mu D1, dinani A1 ndikudina Enter), kapena titha kugwiritsa ntchito. KUMALO pa cholinga chomwecho:

Chonde dziwani kuti mkangano wantchito - wonena za A1 - walowetsedwa m'mawu otchulira, kotero kuti, kwenikweni, ndilemba apa.

“Chabwino,” inu mukuti. "Ndipo ubwino wake ndi chiyani?" 

Koma musaweruze ndi lingaliro loyamba - ndi lachinyengo. Izi zitha kukuthandizani muzochitika zambiri.

Chitsanzo 1. Transpose

Mtundu wapamwamba wamtunduwu: muyenera kutembenuzira vertical dia

kupita kumtunda (transpose). Inde, mungagwiritse ntchito choyikapo kapena ntchito yapadera Mtengo wa TRANSP (TRANSPOSE) m'njira zingapo, koma mutha kupitiliza ndi zathu KUMALO:

Lingaliro ndilosavuta: kuti tipeze adilesi ya cell yotsatira, timamatira chilembo "A" ndi zilembo zapadera "&" ndi nambala ya cell yomwe ilipo, yomwe ntchitoyi imatipatsa. Danga (COLUMN).

Njira yosinthira bwino imachitika mosiyana. Kuyambira nthawi ino tifunika kupanga ulalo wama cell B2, C2, D2, ndi zina zambiri, ndikosavuta kugwiritsa ntchito njira yolumikizirana ya R1C1 m'malo mwa "nkhondo yam'nyanja" yapamwamba. Munjira iyi, ma cell athu amasiyana mumndandanda wokhawokha: B2=R1C2, C2=R1C3, D2=R1C4 etc.

Apa ndipamene mkangano wachiwiri wosankha zochita umabwera. KUMALO. Ngati ndi ofanana KUnama (ZABODZA), ndiye mutha kuyika adilesi yolumikizira mu R1C1 mode. Chifukwa chake titha kusinthira mosavuta magawo opingasa kubwerera ku ofukula:

Chitsanzo 2. Kuwerengera ndi nthawi

Tasanthula kale njira imodzi yofotokozera mwachidule zenera (zosiyanasiyana) za kukula kwake papepala pogwiritsa ntchito ntchitoyi. Kutaya (ZOCHITIKA). Vuto lofananalo litha kuthetsedwanso pogwiritsa ntchito KUMALO. Ngati tifunika kufotokoza mwachidule deta kuchokera ku nthawi inayake, ndiye kuti tikhoza kumata kuchokera kuzidutswa ndikusandutsa ulalo wathunthu, womwe titha kuyika mkati mwa ntchitoyi. SUM (SUM):

Chitsanzo 3. Anzeru tebulo dontho mndandanda

Nthawi zina Microsoft Excel sichitengera mayina a tebulo lanzeru ndi mizati ngati maulalo athunthu. Chifukwa chake, mwachitsanzo, poyesa kupanga mndandanda wotsitsa (tab Deta - Kutsimikizika kwa data) kutengera ndime antchito kuchokera ku smart table anthu tipeza cholakwika:

Ngati "tikulunga" ulalo ndi ntchito yathu KUMALO, ndiye kuti Excel ivomereza mosavuta ndipo mndandanda wathu wotsikira pansi udzasinthidwa mwamphamvu powonjezera antchito atsopano kumapeto kwa tebulo lanzeru:

Chitsanzo 4. Maulalo Osasweka

Monga mukudziwira, Excel imangosintha maadiresi achinsinsi mukamayika kapena kuchotsa mizere papepala. Nthawi zambiri, izi ndi zolondola komanso zosavuta, koma osati nthawi zonse. Tinene kuti tikufunika kusamutsa mayina kuchokera ku bukhu la ogwira ntchito kupita ku lipoti:

Ngati muyika maulalo okhazikika (lowetsani = B2 mu cell yoyamba yobiriwira ndikuyikopera), ndiye mukachotsa, mwachitsanzo, Dasha, tipeza # LINK! cholakwika mu selo lobiriwira lolingana ndi iye. (#REF!). Pankhani yogwiritsira ntchito ntchitoyi kuti mupange maulalo KUMALO sipadzakhala vuto ngati limenelo.

Chitsanzo 5: Kusonkhanitsa deta kuchokera pamapepala angapo

Tiyerekeze kuti tili ndi mapepala 5 okhala ndi malipoti amtundu womwewo kuchokera kwa antchito osiyanasiyana (Mikhail, Elena, Ivan, Sergey, Dmitry):

Tiyeni tiyerekeze kuti mawonekedwe, kukula, malo ndi ndondomeko ya katundu ndi miyezi mu matebulo onse ndi ofanana - manambala okha amasiyana.

Mutha kusonkhanitsa zidziwitso pamapepala onse (osangophatikiza, koma ikani pansi pa "mulu") ndi chilinganizo chimodzi chokha:

Monga mukuonera, lingaliro ndi lofanana: timamatira ulalo ku cell yomwe tikufuna ya pepala lopatsidwa, ndi KUMALO amasintha kukhala "moyo". Kuti zikhale zosavuta, pamwamba pa tebulo, ndinawonjezera zilembo za mizati (B, C, D), ndi kumanja - manambala a mzere omwe ayenera kutengedwa pa pepala lililonse.

mbuna

Ngati mukugwiritsa ntchito KUMALO (KODI) muyenera kukumbukira zofooka zake:

• Ngati mungalumikizane ndi fayilo ina (poyika dzina la fayilo m'mabulaketi apakati, dzina la pepala, ndi adilesi ya selo), ndiye kuti imagwira ntchito pomwe fayilo yoyambirira yatsegulidwa. Tikatseka, tipeza cholakwika # LINK!
• INDIRECT sangatanthauze masinthidwe osinthika. Pa static - palibe vuto.
• INDIRECT ndi ntchito yosasinthika kapena "yosinthika", mwachitsanzo, imawerengedwanso pakusintha kulikonse mu cell ya pepala, osati kungokhudza maselo, monga momwe zimakhalira. Izi zimakhala ndi zotsatira zoyipa pakuchita bwino ndipo ndibwino kuti musatengeke ndi matebulo akulu a INDIRECT.

• Momwe mungapangire magawo osinthika okhala ndi kukula kwa auto
• Kufotokozera mwachidule pazenera lamitundu yosiyanasiyana papepala lomwe lili ndi ntchito ya OFFSET