Momwe mungawunikire cell mu fomula ya Excel

Nthawi zambiri, ogwiritsa ntchito amakumana ndi kufunikira kokhoma cell mu fomula. Mwachitsanzo, zimachitika nthawi yomwe mukufuna kukopera fomula, koma kuti ulalo usasunthe m'mwamba ndi pansi pama cell omwewo monga momwe adakopera kuchokera pomwe adakopera.

Pankhaniyi, mutha kukonza ma cell cell mu Excel. Ndipo izi zitha kuchitika m'njira zingapo nthawi imodzi. Tiyeni tione mwatsatanetsatane mmene tingakwaniritsire cholinga chimenechi.

Kodi ulalo wa Excel ndi chiyani

Tsambali limapangidwa ndi ma cell. Iliyonse mwa iwo ili ndi chidziwitso chapadera. Maselo ena amatha kuzigwiritsa ntchito powerengera. Koma amamvetsetsa bwanji komwe angapeze detayo? Zimawathandiza kupanga maulalo.

Ulalo uliwonse umapanga selo yokhala ndi chilembo chimodzi ndi nambala imodzi. Chilembo chimayimira mzere ndipo nambala imayimira mzere. 

Pali mitundu itatu ya maulalo: mtheradi, wachibale ndi wosakanizika. Yachiwiri imayikidwa mwachisawawa. Kufotokozera mtheradi ndi komwe kumakhala ndi adilesi yokhazikika ya zonse ndime. Chifukwa chake, kusakanikirana ndi komwe kumakhala ndime yosiyana kapena mzere wokhazikika.

Njira 1

Kuti musunge maadiresi onse ndimizere, tsatirani izi:

1. Dinani pa cell yomwe ili ndi fomula.
2. Dinani pa formula bar ya selo yomwe tikufuna.
3. Onetsani F4.

Zotsatira zake, zolozera zamaselo zidzasintha kukhala mtheradi. Itha kudziwika ndi chizindikiro cha dollar. Mwachitsanzo, mukadina pa selo B2 ndiyeno dinani F4, ulalo uwoneka motere: $B$2.

Kodi chizindikiro cha dollar chimatanthawuza chiyani gawo la adilesi lisanakhale pa selo iliyonse?

1. Ngati itayikidwa patsogolo pa kalata, zimasonyeza kuti zolembazo zimakhalabe zofanana, ziribe kanthu komwe ndondomekoyo yasunthidwa.
2. Ngati chizindikiro cha dola chili kutsogolo kwa nambala, zimasonyeza kuti chingwecho chatsekedwa. 

Njira 2

Njirayi ndi yofanana ndi yapitayi, muyenera kukanikiza F4 kawiri. mwachitsanzo, tikadakhala ndi cell B2, pambuyo pake idzakhala B $2. M'mawu osavuta, motere tinatha kukonza mzere. Pankhaniyi, chilembo cha mzati chidzasintha.

Ndizosavuta, mwachitsanzo, m'matebulo pomwe muyenera kuwonetsa zomwe zili mu cell yachiwiri kuchokera pamwamba pa cell yapansi. M'malo mochita chilinganizo chotere nthawi zambiri, ndikokwanira kukonza mzere ndikulola kuti gawolo lisinthe.

Njira 3

Izi ndizofanana ndi njira yapitayi, muyenera kukanikiza kiyi ya F4 katatu kokha. Ndiye kokha kutchulidwa kwa ndime kudzakhala kotheratu, ndipo mzerewo udzakhala wokhazikika.

Njira 4

Tiyerekeze kuti tili ndi chilolezo chamtheradi ku selo, koma apa kunali kofunikira kuti likhale logwirizana. Kuti muchite izi, dinani batani la F4 kangapo kotero kuti palibe zizindikiro za $ mu ulalo. Ndiye izo zidzakhala wachibale, ndipo pamene inu kusuntha kapena kukopera chilinganizo, onse adiresi ndime ndi mzere adiresi zidzasintha.

Kukanikiza ma cell osiyanasiyana

Tikuwona kuti njira zomwe zili pamwambazi sizipereka zovuta zilizonse kuchita. Koma ntchito zake n’zachindunji. Ndipo, mwachitsanzo, chochita ngati tili ndi mafomu angapo nthawi imodzi, maulalo omwe ayenera kusinthidwa kukhala mtheradi. 

Tsoka ilo, njira zokhazikika za Excel sizingakwaniritse cholinga ichi. Kuti muchite izi, muyenera kugwiritsa ntchito addon yapadera yotchedwa VBA-Excel. Ili ndi zina zambiri zomwe zimakupatsani mwayi wochita ntchito wamba ndi Excel mwachangu kwambiri.

Zimaphatikizanso ntchito zopitilira zana zofotokozedwa ndi ogwiritsa ntchito ndi ma macros 25 osiyanasiyana, ndipo zimasinthidwa pafupipafupi. Zimakupatsani mwayi wowongolera ntchito ndi pafupifupi mbali iliyonse:

1. Maselo.
2. Zambiri.
3. Ntchito zamitundu yosiyanasiyana.
4. Links ndi arrays.

Makamaka, izi zowonjezera zimakupatsani mwayi wokonza maulalo mumitundu yambiri nthawi imodzi. Kuti muchite izi, muyenera kuchita izi:

1. Sankhani osiyanasiyana.
2. Tsegulani tabu ya VBA-Excel yomwe idzawonekere mutatha kukhazikitsa. 
3. Tsegulani menyu ya "Functions", pomwe njira ya "Lock formulas" ili.
   

   

4. Kenako, bokosi la zokambirana lidzawoneka momwe muyenera kufotokozera zofunikira. Addon iyi imakulolani kuti mujambule ndime ndi gawo padera, palimodzi, ndikuchotsanso pini yomwe ilipo kale ndi phukusi. Pambuyo posankha gawo lofunikira pogwiritsa ntchito batani lolingana la wailesi, muyenera kutsimikizira zochita zanu podina "Chabwino".

Mwachitsanzo

Tiyeni titenge chitsanzo kuti timveke bwino. Tiyerekeze kuti tili ndi chidziwitso chomwe chimafotokoza mtengo wa katundu, kuchuluka kwake komanso ndalama zogulitsa. Ndipo timayang'anizana ndi ntchito yopanga tebulo, kutengera kuchuluka kwake ndi mtengo wake, kudziwa kuti ndi ndalama zingati zomwe takwanitsa kupeza popanda kutaya zotayika.

Mu chitsanzo chathu, pa izi muyenera kulowa chilinganizo =B2*C2. Ndi zophweka, monga mukuonera. Ndizosavuta kugwiritsa ntchito chitsanzo chake pofotokoza momwe mungakonzere adilesi ya selo kapena mzere wake kapena mzere wake. 

Zachidziwikire, mu chitsanzo ichi, mutha kuyesa kukokera chilinganizo pansi pogwiritsa ntchito chikhomo chodzaza zokha, koma apa, ma cell asinthidwa okha. Kotero, mu selo D3 padzakhala njira ina, kumene manambala adzasinthidwa, motero, ndi 3. Komanso, malinga ndi ndondomeko - D4 - ndondomekoyi idzatenga mawonekedwe = B4 * C4, D5 - mofanana, koma ndi ndondomekoyi. nambala 5 ndi zina zotero.

Ngati kuli kofunikira (nthawi zambiri zimachitika), ndiye kuti palibe mavuto. Koma ngati muyenera kukonza chilinganizo mu selo limodzi kuti asasinthe pamene kukokera, izo zidzakhala penapake zovuta. 

Tiyerekeze kuti tikufunika kudziwa ndalama za dollar. Tiyeni tiyike mu cell B7. Tiyeni titengeko pang'ono ndikuwonetsa mtengo wa ma ruble 35 pa dola. Chifukwa chake, kuti mudziwe ndalama zomwe mumapeza mu madola, ndikofunikira kugawa ndalamazo mu ma ruble ndi ndalama zosinthira ndalama.

Izi ndi zomwe zikuwoneka mu chitsanzo chathu.

Ngati ife, mofanana ndi Baibulo lapitalo, tiyesera kupereka chilinganizo, ndiye kuti tidzalephera. Mofananamo, ndondomekoyi idzasintha kukhala yoyenera. Mu chitsanzo chathu, zidzakhala motere: =E3*B8. Kuchokera apa tikutha kuwona. kuti gawo loyamba la chilinganizo chasanduka E3, ndipo ife tidziika tokha ntchito imeneyi, koma sitiyenera kusintha gawo lachiwiri la chilinganizo B8. Choncho, tiyenera kusandutsa mawuwa kukhala mtheradi. Mutha kuchita izi popanda kukanikiza fungulo la F4, kungoyika chizindikiro cha dollar.

Titatembenuza mawu a cell yachiwiri kukhala mtheradi, idatetezedwa ku kusintha. Tsopano mutha kulikoka mosamala pogwiritsa ntchito chogwiririra chodzaza zokha. Deta yonse yosasunthika idzakhalabe yofanana, mosasamala kanthu za malo a ndondomekoyi, ndipo deta yosasunthika idzasintha mosavuta. M'maselo onse, ndalama za ruble zomwe zafotokozedwa pamzerewu zidzagawidwa ndi ndalama zomwezo.

Fomula yokha idzawoneka motere:

=D2/$B$7

Chenjerani! Tawonetsa zizindikiro ziwiri za dollar. Mwanjira iyi, tikuwonetsa pulogalamu yomwe mzere ndi mzere ziyenera kukhazikitsidwa.

Maselo a ma cell mu ma macros

Macro ndi gawo laling'ono lomwe limakupatsani mwayi woti musinthe zochita. Mosiyana ndi magwiridwe antchito a Excel, macro amakulolani kuti mukhazikitse selo linalake ndikuchita zinthu zina m'mizere yowerengeka ya ma code. Zothandiza pakukonza zidziwitso, mwachitsanzo, ngati palibe njira yokhazikitsira zowonjezera (mwachitsanzo, kompyuta yamakampani imagwiritsidwa ntchito, osati yamunthu).

Choyamba muyenera kumvetsetsa kuti lingaliro lofunikira la macro ndi zinthu zomwe zimatha kukhala ndi zinthu zina. The Workbooks objects ndi udindo wa bukhu lamagetsi (ndiko kuti, chikalata). Zimaphatikizapo chinthu cha Mapepala, chomwe ndi mndandanda wa mapepala onse otseguka. 

Chifukwa chake, maselo ndi chinthu cha Maselo. Lili ndi maselo onse a pepala linalake.

Chilichonse chimakhala ndi mikangano yamakolo. Pankhani ya maselo, amatchulidwa motere. Nambala ya mzere imatchulidwa koyamba, ndikutsatiridwa ndi nambala kapena chilembo (mitundu yonse ndi yovomerezeka).

Mwachitsanzo, mzere wa code womwe uli ndi mawu a selo C5 ungawoneke motere:

Mabuku Ogwirira Ntchito(“Book2.xlsm”).Mapepala(“List2”).Maselo(5, 3)

Mabuku Ogwirira Ntchito(“Book2.xlsm”).Mapepala(“List2”).Maselo(5, “C”)

Mukhozanso kulowa mu selo pogwiritsa ntchito chinthu Waudongo. Mwambiri, cholinga chake ndi kupereka zidziwitso zamitundu yosiyanasiyana (yomwe zinthu zake, mwa njira, zimathanso kukhala zenizeni kapena zachibale), koma mutha kungopereka dzina la cell, mwanjira yofananira ndi chikalata cha Excel.

Pankhaniyi, mzerewo udzawoneka motere.

Mabuku Ogwirira Ntchito(“Book2.xlsm”).Mapepala(“Mndandanda2”).Range(“C5”)

Zingawonekere kuti njira iyi ndi yabwino, koma ubwino wa zosankha ziwiri zoyambirira ndikuti mungagwiritse ntchito zosinthika m'mabokosi ndikupereka ulalo womwe sulinso mtheradi, koma chinthu ngati wachibale, chomwe chidzadalira zotsatira za mawerengedwe.

Chifukwa chake, ma macros amatha kugwiritsidwa ntchito bwino pamapulogalamu. M'malo mwake, maumboni onse okhudzana ndi ma cell kapena magawo apa adzakhala amtheradi, chifukwa chake amathanso kukhazikitsidwa nawo. Zoona, sizothandiza. Kugwiritsa ntchito macros kungakhale kothandiza polemba mapulogalamu ovuta okhala ndi masitepe ambiri mu algorithm. Nthawi zambiri, njira yokhazikika yogwiritsira ntchito maumboni amtheradi kapena achibale ndi yabwino kwambiri. 

Mawuwo

Tidazindikira kuti cell reference ndi chiyani, momwe imagwirira ntchito, ndi chiyani. Tidamvetsetsa kusiyana pakati pa maumboni athunthu ndi achibale ndipo tidapeza zomwe zikuyenera kuchitika kuti tisinthe mtundu wina kukhala wina (m'mawu osavuta, konza adilesi kapena kutulutsa). Tidawona momwe mungachitire nthawi yomweyo ndi ziwerengero zambiri. Tsopano muli ndi mwayi wogwiritsa ntchito izi muzochitika zoyenera.