Momwe mungakhazikitsire mitundu ingapo mu Excel

Mukamagwiritsa ntchito Microsoft Excel, nthawi zambiri wogwiritsa ntchito sadziwa pasadakhale kuchuluka kwa zomwe zidzakhale patebulo. Chifukwa chake, sitimvetsetsa muzochitika zonse zomwe ziyenera kufotokozedwa. Kupatula apo, gulu la maselo ndi lingaliro losinthika. Kuchotsa vutoli, m'pofunika kupanga osiyanasiyana m'badwo basi kuti zachokera kokha pa kuchuluka kwa deta kuti analowa ndi wosuta.

Kusintha ma cell mu Excel

Ubwino wamagawo odziyimira pawokha mu Excel ndikuti zimapangitsa kuti zikhale zosavuta kugwiritsa ntchito mafomu. Kuonjezera apo, zimapangitsa kuti zikhale zotheka kuti zikhale zosavuta kusanthula deta yovuta yomwe ili ndi ma formula ambiri, omwe ali ndi ntchito zambiri. Mutha kupereka dzina lamtunduwu, kenako lidzasinthidwa zokha malinga ndi zomwe zili.

Momwe mungasinthire masinthidwe a automatic mu Excel

Tiyerekeze kuti ndinu Investor yemwe akufunika kuyikapo ndalama pazinthu zina. Chotsatira chake, tikufuna kudziwa zambiri za momwe mungapezere ndalama zonse panthawi yonse yomwe ndalamazo zidzagwire ntchitoyo. Komabe, kuti tipeze chidziwitsochi, tiyenera kuyang'anira nthawi zonse kuchuluka kwa phindu lomwe chinthuchi chimabweretsa kwa ife. Pangani lipoti lomwelo monga momwe zilili pachithunzichi.

Poyang'ana koyamba, yankho ndi lodziwikiratu: muyenera kungowerengera gawo lonse. Ngati zolowa zikuwonekera, ndalamazo zidzasinthidwa paokha. Koma njirayi ili ndi zovuta zambiri:

1. Ngati vutoli litathetsedwa motere, sizingatheke kugwiritsa ntchito maselo omwe ali mu gawo B pazinthu zina.
2. Gome loterolo lidzadya RAM yambiri, zomwe zidzapangitse kukhala kosatheka kugwiritsa ntchito chikalatacho pamakompyuta ofooka.

Chifukwa chake, ndikofunikira kuthetsa vutoli pogwiritsa ntchito mayina amphamvu. Kuti mupange, muyenera kuchita zotsatirazi:

1. Pitani ku tabu "Mafomu", yomwe ili mumenyu yayikulu. Padzakhala gawo la "Defined names", pomwe pali batani "Perekani dzina", pomwe tiyenera dinani.
2. Kenaka, bokosi la zokambirana lidzawoneka momwe muyenera kudzaza minda monga momwe tawonetsera pazithunzi. Ndikofunikira kudziwa kuti tiyenera kugwiritsa ntchito ntchitoyi = KUSINTHA pamodzi ndi ntchito ZAKEkupanga zosintha zokha. 
3. Pambuyo pake tiyenera kugwiritsa ntchito SUM, yomwe imagwiritsa ntchito mayendedwe athu ngati mkangano. 

Tikamaliza masitepewa, titha kuwona momwe kufalikira kwa ma cell a "ndalama" kumasinthidwa pomwe tikuwonjezera zinthu zatsopano pamenepo.

OFFSET ntchito mu Excel

Tiyeni tiwone ntchito zomwe tidazilemba mugawo la "range" kale. Kugwiritsa ntchito Kutaya Titha kudziwa kuchuluka kwa magawo omwe apatsidwa kuchuluka kwa maselo omwe ali mugawo B adzazidwa. Zotsutsana za ntchito ndi izi:

1. Yambani cell. Ndi mtsutso uwu, wogwiritsa ntchito amatha kuwonetsa kuti ndi selo liti mumndandanda womwe udzatengedwe kumanzere. Ifotokoza pansi ndi kumanja.
2. Mzere wosiyana ndi mizere. Pogwiritsa ntchito izi, timayika chiwerengero cha maselo omwe akuyenera kuchitika kuchokera kumtunda kumanzere kwa selo. Simungagwiritse ntchito zikhalidwe zabwino zokha, koma ziro ndi minus. Pankhaniyi, kusamutsidwa sikungachitike konse, kapena kudzachitika mbali ina.
3. Kusiyanasiyana kumasinthidwa ndi mizati. Parameter iyi ndi yofanana ndi yapitayi, yokhayo imakulolani kuti muyike mlingo wa kusintha kosasunthika kwamtundu. Apa mutha kugwiritsanso ntchito ziro ndi zosayenera.
4. Kuchuluka kwa kutalika kwake. Ndipotu mutu wa mkangano umenewu umatithandiza kumvetsa tanthauzo lake. Ichi ndi chiwerengero cha maselo omwe mulingo uyenera kuwonjezeka.
5. Mtengo wamtali mwake. Mtsutsowo ndi wofanana ndi wapitawo, wokhawo umakhudza mizati.

Simufunikanso kufotokoza mfundo ziwiri zomaliza ngati simukuyenera kutero. Pankhaniyi, mtengo wamtundu udzakhala selo limodzi lokha. Mwachitsanzo, ngati mufotokoza fomula = OFFSET(A1;0;0), fomula ili litanthauzanso selo lomwe lili mumkangano woyamba. Ngati vertical offset yakhazikitsidwa kukhala mayunitsi 2, ndiye kuti pamenepa seloyo imayimira selo A3. Tsopano tiyeni tifotokoze mwatsatanetsatane tanthauzo la ntchitoyi ZAKE.

COUNT ntchito mu Excel

Kugwiritsa ntchito ZAKE timazindikira kuti ndi ma cell angati omwe tadzaza mugawo B. Ndiko kuti, pogwiritsa ntchito ntchito ziwiri, timadziwa kuti ndi maselo angati omwe ali mumtunduwo amadzazidwa, ndipo malinga ndi zomwe alandira, amasankha kukula kwake. Chifukwa chake, fomula yomaliza ikhala motere: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Tiyeni tiwone momwe tingamvetsetse bwino mfundo ya chilinganizochi. Mtsutso woyamba umaloza pomwe kusintha kwathu kumayambira. Kwa ife, iyi ndi cell B2. Magawo ena ali ndi ziro zolumikizira. Izi zikusonyeza kuti sitifunika kuchotsera poyerekeza ndi selo pamwamba kumanzere. Zomwe tikudzaza ndi kukula koyima kwamtundu, komwe tidagwiritsa ntchito ngati ZAKE, yomwe imatsimikizira kuchuluka kwa maselo omwe ali ndi deta. Gawo lachinayi lomwe tidadzaza ndi unit. Chifukwa chake tikuwonetsa kuti m'lifupi mwake mulingo uyenera kukhala gawo limodzi.

Choncho, kugwiritsa ntchito ZAKE wogwiritsa ntchito angagwiritse ntchito kukumbukira moyenera momwe angathere pokweza maselo okhawo omwe ali ndi makhalidwe ena. Mogwirizana ndi izi, sipadzakhala zolakwika zina pa ntchito yokhudzana ndi kusagwira bwino ntchito kwa kompyuta yomwe spreadsheet idzagwire ntchito.

Chifukwa chake, kuti mudziwe kukula kwamtunduwo kutengera kuchuluka kwa mizati, muyenera kuchita zinthu zofananira, pokhapokha muyenera kufotokozera gawolo pagawo lachitatu, ndi chilinganizo chachinayi. ZAKE.

Tikuwona kuti mothandizidwa ndi ma fomula a Excel, simungathe kuwerengera masamu okha. Uku ndi dontho chabe m'nyanja, koma m'malo mwake amakulolani kuti muzitha kuchita chilichonse chomwe chimabwera m'maganizo.

Ma chart a Dynamic mu Excel

Chifukwa chake, mu gawo lomaliza, tidatha kupanga mawonekedwe osinthika, kukula kwake kumatengera kuchuluka kwa ma cell omwe ali nawo. Tsopano, kutengera deta iyi, mutha kupanga ma chart osinthika omwe angasinthe pokhapokha wogwiritsa ntchito akasintha kapena kuwonjezera gawo lina kapena mzere. Zotsatira zake pankhaniyi ndi motere:

1. Timasankha mtundu wathu, kenako timayika tchati cha mtundu wa "Histogram ndi gulu". Mutha kupeza chinthuchi mu gawo la "Ikani" mu gawo la "Ma chart - Histogram".
2. Timadina mbewa yakumanzere pagawo losasintha la histogram, pambuyo pake ntchito = SERIES() iwonetsedwa pamzere wantchito. Mu skrini mutha kuwona chilinganizo chatsatanetsatane. 
3. Pambuyo pake, zosintha zina ziyenera kupangidwa ku formula. Muyenera kusintha mtunduwo pambuyo pa "Sheet1!" ku dzina la range. Izi zitha kugwira ntchito zotsatirazi: =ROW(Sheet1!$B$1;;Ndalama1!ndalama;1)
4. Tsopano zatsala kuwonjezera mbiri yatsopano ku lipotilo kuti muwone ngati tchatichi chikusinthidwa zokha kapena ayi.

Tiyeni tiwone chithunzi chathu.

Tiyeni tifotokoze mwachidule momwe tinachitira. Mu sitepe yapitayi, tinapanga zosinthika, kukula kwake kumadalira kuchuluka kwa zinthu zomwe zili nazo. Kuti tichite izi, tidagwiritsa ntchito zosakaniza ZAKE и Kutaya. Tidapanga mndandandawu kutchulidwa, kenako tidagwiritsa ntchito mawu otchulira dzinali ngati mtundu wa histogram yathu. Ndi mitundu iti yomwe mungasankhe ngati gwero la data pagawo loyamba silofunika kwambiri. Chinthu chachikulu ndikuchisintha ndi dzina lamtundu wina pambuyo pake. Mwanjira iyi mutha kusunga RAM yambiri.

Mitundu yodziwika ndi kugwiritsa ntchito kwawo

Tiyeni tsopano tikambirane mwatsatanetsatane momwe mungapangire mizere yotchulidwa bwino ndikuigwiritsa ntchito pochita ntchito zomwe zakhazikitsidwa kwa ogwiritsa ntchito a Excel.

Mwachisawawa, timagwiritsa ntchito ma adilesi amtundu wamba kuti tisunge nthawi. Izi ndizothandiza mukafuna kulemba mndandanda kamodzi kapena zingapo. Ngati ikufunika kugwiritsidwa ntchito nthawi zonse kapena ikufunika kusintha, ndiye kuti mizere yotchulidwa iyenera kugwiritsidwa ntchito. Amapangitsa kupanga mafomu kukhala kosavuta, ndipo sizikhala zovuta kwa wogwiritsa ntchito kusanthula mafomu ovuta omwe ali ndi ntchito zambiri. Tiyeni tifotokoze masitepe ena omwe amakhudzidwa popanga magawo osinthika.

Zonse zimayamba ndikutchula selo. Kuti muchite izi, ingosankhani, ndiyeno lembani dzina lomwe tikufuna m'munda wa dzina lake. Ndikofunika kuti zikhale zosavuta kukumbukira. Pali zoletsa zina zomwe muyenera kuziganizira potchula mayina:

1. Kutalika kwakukulu ndi zilembo 255. Izi ndizokwanira kukupatsani dzina lomwe mtima wanu ukulakalaka.
2. Dzinali lisakhale ndi mipata. Chifukwa chake, ngati lili ndi mawu angapo, ndiye kuti ndizotheka kuwalekanitsa pogwiritsa ntchito zilembo za underscore.

Ngati pambuyo pake pamapepala ena a fayiloyi tifunika kuwonetsa mtengo uwu kapena kuuyika kuti tiwerengenso, ndiye kuti palibe chifukwa chosinthira ku pepala loyamba. Mutha kungolemba dzina la cell range iyi.

Chotsatira ndikupanga mtundu womwe watchulidwa. Ndondomekoyi ndi yofanana. Choyamba muyenera kusankha osiyanasiyana, ndiyeno tchulani dzina lake. Pambuyo pake, dzinali litha kugwiritsidwa ntchito muzolemba zina zonse mu Excel. Mwachitsanzo, mizere yotchulidwa nthawi zambiri imagwiritsidwa ntchito kufotokoza kuchuluka kwa zinthu.

Kuphatikiza apo, ndizotheka kupanga mtundu wotchulidwa pogwiritsa ntchito tabu ya Mafomu pogwiritsa ntchito chida cha Set Name. Titasankha, zenera lidzawonekera pomwe tifunika kusankha dzina lamtundu wathu, komanso kufotokozera dera lomwe lidzapitirire pamanja. Mutha kutchulanso komwe mndandandawu udzagwire ntchito: mkati mwa pepala limodzi kapena m'buku lonse.

Ngati dzina lamtundu wapangidwa kale, ndiye kuti mugwiritse ntchito, pali ntchito yapadera yotchedwa name manager. Sichimalola kusintha kapena kuwonjezera mayina atsopano, komanso kuwachotsa ngati sakufunikanso.

Ziyenera kukumbukiridwa kuti mukamagwiritsa ntchito magawo otchulidwa m'mafawuni, ndiye mukachotsa, ma fomuwa sangangolembedwanso ndi zikhalidwe zolondola. Choncho, zolakwika zikhoza kuchitika. Chifukwa chake, musanachotse mndandanda womwe umatchulidwa, muyenera kuwonetsetsa kuti sunagwiritsidwe ntchito mwanjira iliyonse.

Njira inanso yopangira mtundu wotchulidwa ndikuwutenga patebulo. Kuti muchite izi, pali chida chapadera chotchedwa "Pangani kuchokera Kusankha". Monga tikumvetsetsa, kuti mugwiritse ntchito, choyamba muyenera kusankha mtundu womwe tidzasintha, ndikukhazikitsa malo omwe tili ndi mitu. Zotsatira zake, kutengera deta iyi, Excel idzakonza zonse zokha, ndipo mitu idzaperekedwa yokha.

Ngati mutuwo uli ndi mawu angapo, Excel idzawalekanitsa okha ndi underscore.

Chifukwa chake, tidawona momwe mungapangire magawo osinthika omwe amatchulidwa komanso momwe amakulolani kuti muzitha kugwira ntchito ndi data yochulukirapo. Monga mukuonera, ndizokwanira kugwiritsa ntchito ntchito zingapo ndi zida zamapulogalamu zomwe zimapangidwira magwiridwe antchito. Palibe zovuta konse, ngakhale zingawoneke choncho kwa woyambitsa poyang'ana koyamba.