Momwe mungachotsere fomula mu cell mu Excel

Excel ili ndi ntchito zambiri zomwe zitha kugwiritsidwa ntchito kuwerengera zovuta kwambiri. Amagwiritsidwa ntchito ngati mawonekedwe olembedwa m'maselo. Wogwiritsa ntchito nthawi zonse amakhala ndi mwayi wowasintha, kusintha zina ndi zina kapena mfundo.

Monga lamulo, kusunga chilinganizo mu selo ndikosavuta, koma osati nthawi zonse. Nthawi zina, zimakhala zofunikira kusunga chikalata popanda mafomu. Mwachitsanzo, pofuna kupewa ogwiritsa ntchito ena kumvetsetsa momwe manambala ena adalandirira. 

Ndiyenera kunena kuti ntchitoyi ndi yosavuta. Ndikokwanira kutsatira njira zingapo zosavuta kuti mukhale ndi moyo: Panthawi imodzimodziyo, pali njira zingapo, zomwe zimakhala zosavuta kugwiritsa ntchito pazochitika zinazake. Tiyeni tione mwatsatanetsatane. 

Njira 1: Kugwiritsa Ntchito Zosankha za Paste

Njirayi ndiyosavuta, ngakhale wongoyamba kumene angagwiritse ntchito. Mukungoyenera kutsatira izi:

1. Choyamba muyenera kudina mbewa yakumanzere ndikukokera kusankha ma cell omwe ntchitoyo ndikuchotsa mafomuwo. Chabwino, kapena chimodzi. Ndiye kungodina kamodzi kokha ndikokwanira.
   

   

2. Kenako muyenera kutsegula menyu yankhani ndikupeza chinthu "Koperani". Koma nthawi zambiri kuphatikiza Ctrl + C kumagwiritsidwa ntchito kukwaniritsa cholinga ichi. Izi ndizosavuta komanso zachangu kuposa kudina kumanja pazomwe zimafunikira, kenako ndikudina chinthu china. Izi ndizothandiza makamaka pa laputopu, pomwe touchpad imagwiritsidwa ntchito m'malo mwa mbewa.
   

   

3. Palinso njira yachitatu yokopera, yomwe, kuti ikhale yosavuta, ili pakati pa ziwirizi pamwambapa. Kuti muchite izi, pezani tabu ya "Home", kenako dinani batani lomwe lawonetsedwa mu lalikulu lofiira.
   

   

4. Kenako, timazindikira cell yomwe deta yomwe iyenera kukopera kuchokera pagawo loyambira iyenera kuyamba (zidzakhala kumtunda kumanzere kwa mndandanda wamtsogolo). Pambuyo pake, timadina kumanja ndikudina njira yomwe ikuwonetsedwa ndi bwalo lofiira (batani likuwoneka ngati chithunzi chokhala ndi manambala).
   

   

5. Zotsatira zake, tebulo lofananalo lidzawonekera pamalo atsopano, popanda mafomu.
   

   

Njira 2: Ikani Pasta Wapadera

Choyipa cha njira yapitayi ndikuti sichisunga mapangidwe apachiyambi. Kuti mutaya izi, muyenera kugwiritsa ntchito njira ina yokhala ndi dzina lofanana - "Paste Special". Zimachitika motere:

1. Apanso, sankhani mtundu womwe tikufuna kukopera. Tiyeni tigwiritse ntchito batani la kukopera pazida pazida izi. Tebulo lonse likhala likugwiritsidwa ntchito kale ngati mndandanda, popeza mitu yake ili ndi masanjidwe ovuta omwe tiyenera kukopera.
   

   

2. Masitepe otsatirawa ndi ofanana. Muyenera kupita ku cell yomwe idzakhala tebulo popanda mafomu. Kapena m'malo mwake, mu cell yakumanzere yakumanzere, chifukwa chake muyenera kuwonetsetsa kuti palibe zina zowonjezera m'malo mwa tebulo lamtsogolo. Dinani kumanja pa izo ndi kupeza "Matani Special" njira. Pafupi ndi icho pali chithunzi cha makona atatu, chomwe chimalunjikitsidwa kumanja ndi pamwamba pake. Mukadina, gulu lina lidzawonekera, pomwe tifunika kupeza gulu la "Insert Values" ndikusankha batani lomwe likuwonetsedwa mofiira pachithunzichi.
   

   

3. Zotsatira zake ndi tebulo lomwelo lomwe lili muchidutswa chomwe chinakopera, kokha m'malo mwa formula, mitengo yauXNUMXbuXNUMXbare yomwe yalembedwa kale pamenepo.
   

   

Njira 3: Chotsani Fomula mu Cell Source

Choyipa cha njira zonsezi pamwambapa ndikuti sizipereka kuthekera kochotsa chilinganizocho mwachindunji mu cell. Ndipo ngati mukufuna kukonza pang'ono, muyenera kukopera, kuyika ndi magawo ena kwina, ndiyeno kusamutsa tebulo ili kapena maselo amodzi kumalo awo oyambirira. Mwachiwonekere, izi ndizovuta kwambiri.

Chifukwa chake, tiyeni tiwone mwatsatanetsatane njira yomwe imakupatsani mwayi wochotsa mafomu mwachindunji m'maselo. Tsatirani izi:

1. Lembani mzere wofunikira pogwiritsa ntchito njira iliyonse yomwe ili pamwambayi. Kuti timveke bwino, tidzadina kumanja kwa mbewa ndikusankha njira ya "Copy" pamenepo.
   

   

2. Mofanana ndi njira yapitayi, tiyenera kumata malo omwe tinakopera kale kumalo atsopano. Ndipo nthawi yomweyo kusiya choyambirira masanjidwe. Kenako, tiyenera kumata tebulo ili pansipa.
   

   

3. Pambuyo pake, timapita kumtunda wakumanzere kwa tebulo lomwe linali poyambirira (kapena sankhani mtundu womwewo womwe unali mu gawo 1), kenako timatcha menyu yankhani ndikusankha "Values".
   

   

4. Zitatha kutengera kwathunthu ma cell omwe mukufuna osasunga mafomuwo, koma ndi mfundo zomwezo, muyenera kufufuta chobwereza. Kuti muchite izi, muyenera kusankha mtundu wa data womwe mukufuna kuchotsa, kenako dinani pomwepa ndikudina "Chotsani".
   

   

5. Kenako, zenera laling'ono lidzawoneka momwe muyenera kusankha chinthu cha "mzere" ndikutsimikizira kufufutidwa mwa kukanikiza batani "Chabwino".
   

   

6. Mukhozanso kusankha chinthu china. Mwachitsanzo, "maselo, osunthika kumanzere" amagwiritsidwa ntchito kuchotsa chiwerengero cha maselo omwe ali kumanzere, pokhapokha ngati palibe mfundo zomwe zafotokozedwa kumanja.

Chilichonse, tsopano tili ndi tebulo lomwelo, popanda mafomu. Njirayi ili ngati kukopera ndi kumata tebulo lomwe linapezedwa ndi njira yachiwiri kumalo ake oyambirira, koma penapake yabwino kwambiri poyerekeza ndi izo. 

Njira 4: Pewani kukopera malo ena konse

Zochita zotani ngati palibe chikhumbo chokopera tebulo kumalo ena? Iyi ndi njira yovuta kwambiri. Choyipa chake chachikulu ndikuti zolakwika zimatha kuwononga kwambiri deta yoyambirira. Zachidziwikire, mutha kuwabwezeretsa pogwiritsa ntchito kuphatikiza kwa Ctrl + Z, koma kuyambiranso momwemo kudzakhala kovuta. Kwenikweni, njira yokhayo ili motere:

1. Timasankha selo kapena mtundu womwe tikufunika kuchotsa pamapangidwewo, kenako timawakopera pogwiritsa ntchito njira iliyonse yomwe ili pamwambapa. Mutha kusankha yomwe mumakonda kwambiri. Tidzagwiritsa ntchito njira yomwe ikuphatikiza kugwiritsa ntchito batani lomwe lili pazida pa tabu Yanyumba.
   

   

2. Sitichotsa zosankhidwa m'malo omwe adakopera, ndipo nthawi yomweyo timadina pomwepa, ndikusankha chinthu cha "Values" mu gulu la "Paste Options".
   

   

3. Zotsatira zake, zikhalidwe zenizeni zimalowetsedwa m'maselo oyenera.
   

   

4. Ngati pali masanjidwe mu selo, ndiye kuti muyenera kugwiritsa ntchito njira ya "Paste Special".

Njira 5: Kugwiritsa Ntchito Macro

Macro ndi pulogalamu yaying'ono yomwe imachita zinthu zina muzolemba za wogwiritsa ntchito. Zimafunika ngati nthawi zambiri mumayenera kuchita zinthu zomwezo. Koma simudzatha kugwiritsa ntchito macros nthawi yomweyo, chifukwa mawonekedwe opangira mapulogalamu samathandizidwa mwachisawawa, omwe amayenera kutsegulidwa musanathe kuchotsa mwachindunji mafomuwo.

Kuti muchite izi, chitani zotsatirazi:

1. Dinani pa "Fayilo".
   

   

2. Idzawoneka zenera lomwe mumenyu yomwe ili kumanzere, tikuyang'ana "Zosankha" ndikusankha.
   

   

3. Padzakhala chinthu "Sinthani Riboni", ndipo kumanja kwa zenera muyenera kuyang'ana bokosi pafupi ndi chinthucho "Mapulogalamu".
   

   

Kuti mulembe macro, tsatirani izi:

1. Tsegulani tabu ya "Developer", komwe pitani ku Visual Basic mkonzi podina batani la dzina lomwelo.
   

   

2. Kenako, tiyenera kusankha pepala lolondola, ndiyeno dinani "Onani Code" batani. Njira yosavuta ndikudina kawiri motsatana mwachangu ndi batani lakumanzere papepala lomwe mukufuna. Izi zidzatsegula macro editor.
   

   

Ndiye code yotere imayikidwa mu gawo la mkonzi.

Sub Delete_formulas()

Selection.Value = Selection.Value

mapeto Sub

Mizere yaying'ono yotere idakhala yokwanira kuchotsa ma fomu omwe adasankhidwa. Kenako muyenera kusankha dera lomwe tikufuna ndikudina batani la "Macros". Itha kupezeka pafupi ndi Visual Basic Editor. Iwindo la kusankha ma subroutines osungidwa limawonekera, momwe muyenera kupeza script yomwe mukufuna ndikudina "Thamangani".

Mukadina batani ili, fomula iliyonse idzasinthidwa ndi zotsatira. Zikungowoneka zovuta. Ndipotu, masitepewa amatenga mphindi zochepa chabe. Ubwino wa njirayi ndikuti mutha kupanga pulogalamu yovuta kwambiri yomwe, mwachitsanzo, idzadziwonetsera yokha kuti ndi maselo ati omwe angachotsere ndondomekoyi pogwiritsa ntchito njira zina. Koma izi kale aerobatics.

Njira 6: Chotsani zonse fomula ndi zotsatira

Pafupifupi munthu aliyense posakhalitsa ayenera kuchotsa osati chilinganizo chokha, komanso zotsatira zake. Chabwino, ndiye kuti palibe chomwe chatsala m'chipindacho. Kuti muchite izi, sankhani ma cell omwe mukufuna kuyeretsa, dinani pomwepa ndikusankha "Chotsani zomwe zili".

Chabwino, kapena ingogwiritsani ntchito backspace kapena del key pa kiyibodi. M'mawu osavuta, izi zimachitika mofanana ndi kuchotsa deta mu selo ina iliyonse. 

Pambuyo pake, deta yonse idzachotsedwa.

Mawuwo

Monga mukuonera, ndikosavuta kuchotsa mafomuwa m'maselo. Ubwino wake ndikuti pali njira zingapo zokwaniritsira cholinga ichi. Munthu ali ndi ufulu wosankha chilichonse chomwe chili choyenera iye chifukwa, mwachitsanzo, kumasuka. Mwachitsanzo, njira zobwerezabwereza ndizothandiza ngati mukufuna kubweza zosintha mwachangu kapena kusinthanso zotsatira zake kuti chidziwitso choyambirira chisungidwe. Izi zitha kukhala zothandiza kwambiri, mwachitsanzo, ngati mukufuna kuonetsetsa kuti pepala limodzi lili ndi mafomula, ndipo linalo limakhala ndi mikhalidwe yokhayokha popanda kusintha mafomu.