Excel. Kusiyanasiyana kwa ma cell

Zachidziwikire, lingaliro lamitundu mu Excel ndi imodzi mwazofunikira. Ndi chiyani? Tonse tikudziwa kuti pepala limapangidwa ndi maselo. Tsopano, ngati angapo aiwo ali ndi chidziwitso, ndiye kuti uwu ndi mndandanda. M'mawu osavuta, awa ndi maselo awiri kapena kuposerapo muzolemba.

Mipikisano imagwiritsidwa ntchito mwachangu pamapangidwe, ndipo itha kugwiritsidwanso ntchito ngati gwero la data la ma graph, ma chart, ndi njira zina zowonera zowonetsera zambiri. Tiyeni tione mwatsatanetsatane mmene ntchito ndi osiyanasiyana.

Momwe mungasankhire maselo, mizere ndi mizati

Selo ndi chinthu chomwe chimakhala kapena chimakhala ndi chidziwitso china. Mzere ndi maselo motsatizana. Mzere, motero, muzambiri. Zonse ndi zophweka. 

Musanalowetse deta kapena kupanga data ina ndi mitundu yosiyanasiyana, muyenera kuphunzira momwe mungasankhire ma cell, mizati, ndi mizere.

Kuti musankhe selo, muyenera kudina. Selo lililonse lili ndi adilesi. Mwachitsanzo, yomwe ili pamzere wa C ndi mzere 3 imatchedwa C3.

Chifukwa chake, kuti musankhe mzati, muyenera dinani chilembo chomwe chikuwonetsa dzina lagawolo. Kwa ife, iyi ndi gawo C.

Monga momwe mungaganizire, kusankha mzere, muyenera kuchita chimodzimodzi, ndi dzina la mzerewo.

Maselo osiyanasiyana: chitsanzo

Tsopano tiyeni tiyang'ane ntchito zina zomwe zitha kuchitidwa mwachindunji pamitundu yosiyanasiyana. Chifukwa chake, kuti musankhe mtundu wa B2: C4, muyenera kupeza ngodya yakumanja ya selo B2, yomwe kwa ife imakhala ngati cell yakumanzere yakumanzere, ndikukokerani cholozera ku C4.

Zofunika! Osati lalikulu mu ngodya yakumanja yakumanja, koma mophweka, titero, kukoka cell iyi. Sikweya ndi chikhomo chomaliza, ndi chosiyana pang'ono.

Magawo nthawi zonse amakhala ndi ma cell omwe amakhala moyandikana. Kuti musankhe, muyenera kukanikiza Ctrl kiyi ndipo, osamasula, dinani pa selo lililonse lomwe liyenera kuphatikizidwa pamndandandawu.

Momwe mungadzazire mndandanda

Kuti mudzaze mndandanda ndi zinthu zina, muyenera kuchita izi:

1. Lowetsani mtengo womwe mukufuna mu cell B2. Zitha kukhala manambala kapena mawu. Ndikothekanso kuyika chilinganizo. Kwa ife, iyi ndi nambala 2.
   

2. Kenako, dinani cholembera cha autofill (bokosi lomwelo lomwe tidapempha kuti tisadina) ndikulikokera mpaka kumapeto kwa mndandanda.

Zotsatira zake zidzakhala zotsatirazi. Apa tadzaza ma cell onse ofunikira ndi nambala 2.

Autocomplete ndi imodzi mwamafunso omwe amafunsidwa kwambiri mu Excel. Zimakulolani kuti mulembe ku maselo amtunduwo osati mtengo umodzi wokha, komanso deta yonse yomwe ikugwirizana ndi ndondomeko inayake. Mwachitsanzo, mndandanda wa manambala ndi 2, 4, 6, 8, 10 ndi zina zotero.

Kuti tichite izi, tifunika kuyika miyeso iwiri yoyambira m'maselo oyandikana nawo ndikusuntha cholembera cha autofill pama cell ofunikira.

Mofananamo, mukhoza kudzaza mndandanda ndi masiku omwe mukufuna, omwe amatsatiranso ndondomeko inayake. Kuti tichite izi, tiyeni tilowetse tsiku la June 13, 2013 ndi tsiku la June 16, 2013 mu mtundu wa US.

Pambuyo pake, timachita kukoka ndikugwetsa zomwe tikudziwa kale.

Kusintha kwamitundu

Kuti musunthe mtunda, ingotsatirani njira zingapo zosavuta. Choyamba muyenera kusankha mtundu wofunikira ndikusunga malire ake. Kwa ife, yoyenera.

Ndiye mumangofunika kusunthira kumalo oyenera ndikumasula mbewa.

Koperani ndi kumata osiyanasiyana

Ichinso ndi chimodzi mwazochita zodziwika bwino zomwe ogwiritsa ntchito a Excel amachita ndi magawo.

Kuti muchite izi, muyenera kusankha mtundu, dinani pomwepa ndikudina "Koperani". Mukhozanso kugwiritsa ntchito njira yachidule ya kiyibodi Ctrl + C.

Mutha kupezanso batani lodzipatulira pa tabu Yanyumba mu gulu la Clipboard. 

Chotsatira ndikuyika zomwe mukufuna kwina. Kuti muchite izi, muyenera kupeza selo lomwe lidzakhala kumtunda kumanzere kwa mndandanda, ndiyeno muyitane mndandanda wazomwezo mofanana, koma nthawi yomweyo pezani chinthu cha "Insert". Mutha kugwiritsanso ntchito kuphatikiza kwa Ctrl + V, komwe kumagwira ntchito mu pulogalamu iliyonse.

Momwe mungayikitsire mzere kapena chigawo china

Kuyika mzere kapena ndime kumachitika mofananamo. Choyamba muyenera kusankha iwo.

Pambuyo pake muyenera dinani kumanja ndikudina batani la "Insert", lomwe lili pansipa.

Mwanjira imeneyi, tinakwanitsa kuyika mzere.

Mitundu Yotchedwa

Monga momwe dzinalo likusonyezera, dzina limatanthawuza mndandanda womwe wapatsidwa dzina. Izi ndizosavuta kwambiri, chifukwa zimawonjezera zomwe zili patsamba lake, zomwe ndizothandiza kwambiri, mwachitsanzo, ngati anthu angapo akugwira ntchito pachikalata chimodzi nthawi imodzi. 

Mutha kugawa dzina kumitundu yambiri kudzera pa Name Manager, yomwe imapezeka pansi pa Mafomula - Maina Ofotokozedwa - Name Manager.

Koma kawirikawiri, pali njira zingapo. Tiyeni tione zitsanzo zina.

Mwachitsanzo 1

Tiyerekeze kuti tikuyang'anizana ndi ntchito yodziwitsa kuchuluka kwa malonda a katundu. Pachifukwa ichi, tili ndi B2:B10. Kuti mupereke dzina, muyenera kugwiritsa ntchito maumboni athunthu.

Nthawi zambiri, zochita zathu ndi izi:

1. Sankhani mtundu womwe mukufuna.
2. Pitani ku tabu "Mafomu" ndikupeza lamulo la "Pakani Dzina" pamenepo.
3. Kenaka, bokosi la zokambirana lidzawoneka momwe muyenera kufotokozera dzina la mndandanda. Kwa ife, izi ndi "Sales".
4. Palinso gawo la "Region", lomwe limakupatsani mwayi wosankha pepala lomwe mtundu uwu uli.
5. Onetsetsani kuti mulingo woyenera watchulidwa. Fomula iyenera kukhala: ='1season'!$B$2:$B$10
6. Dinani OK.
   

Tsopano mutha kulowa dzina lake m'malo mwa adilesi yamitundu. Choncho, kugwiritsa ntchito formula = SUM (Zogulitsa) mutha kuwerengera kuchuluka kwa malonda azinthu zonse.

Mofananamo, mutha kuwerengera pafupifupi kuchuluka kwa malonda pogwiritsa ntchito fomula = AVERAGE(Zogulitsa).

Chifukwa chiyani tagwiritsa ntchito kuyitanitsa kotheratu? Chifukwa imalola Excel ku hardcode osiyanasiyana omwe sasintha akakopera.

Nthawi zina ndi bwino kugwiritsa ntchito ulalo wachibale.

Mwachitsanzo 2

Tiyeni tsopano tione kuchuluka kwa malonda pa iliyonse ya nyengo zinayi. Mutha kudziwa zambiri zamalonda patsamba la 4_season. 

Mu chithunzithunzi ichi, mizere ili motere.

B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10

Chifukwa chake, tiyenera kuyika mafomu m'maselo B11, C11, D11 ndi E11.

Zachidziwikire, kuti ntchitoyi ichitike, mutha kupanga magawo angapo, koma izi ndizosokoneza pang'ono. Ndibwino kugwiritsa ntchito imodzi. Kuti moyo ukhale wosavuta, muyenera kugwiritsa ntchito maadiresi achibale. Pankhaniyi, ndikwanira kungokhala ndi mtundu umodzi, womwe mwa ife udzatchedwa "Seasonal_Sales"

Kuti muchite izi, muyenera kutsegula dzina la woyang'anira, lowetsani dzina mu bokosi la zokambirana. Makinawa ndi omwewo. Musanayambe kuwonekera "Chabwino", muyenera kuonetsetsa kuti chilinganizo chalowa mu "Range" mzere ='4seasons'!B$2:B$10

Mu nkhani iyi, maadiresi ndi osakanikirana. Monga mukuonera, palibe chizindikiro cha dollar kutsogolo kwa dzina lazambiri. Izi zimakupatsani mwayi wowerengera zomwe zili m'mizere yofanana koma mizere yosiyana. 

Komanso, ndondomeko ndi yofanana. 

Tsopano tiyenera kuyika fomula mu cell B11 =SUM(Nyengo_Zogulitsa). Kupitilira apo, pogwiritsa ntchito cholembera cha autocomplete, timachisamutsa kumaselo oyandikana nawo, ndipo izi ndi zotsatira zake.

Langizo: Mukasindikiza batani la F2 pomwe selo lomwe lili ndi fomula yokhala ndi dzina losiyanasiyana likusankhidwa, ma cell olondola adzawonetsedwa ndi malire a buluu.

Mwachitsanzo 3

Mtundu wotchulidwa ukhoza kugwiritsidwanso ntchito munjira yovuta. Tiyerekeze kuti tili ndi chilinganizo chachikulu chomwe chigawo chotchulidwa chimagwiritsidwa ntchito kangapo.

=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

Ngati mukufuna kusintha magawo omwe agwiritsidwa ntchito, muyenera kuchita izi katatu. Koma ngati mutapereka dzina kumtunduwo musanasinthe mwachindunji, ndiye kuti ndikwanira kusintha mu woyang'anira dzina, ndipo dzinalo lidzakhala lofanana. Izi ndizothandiza kwambiri. 

Kuphatikiza apo, ngati mutayamba kulemba dzina lamitundu yosiyanasiyana, Excel imangopereka mafomuwo pamodzi ndi mitundu ina.

Zosintha zokha

Nthawi zambiri, pogwira ntchito ndi chidziwitso mu spreadsheet, sizingatheke kudziwiratu kuchuluka kwa deta yomwe idzasonkhanitsidwe. Choncho, sitidziwa nthawi zonse kuti titchule dzina linalake liti. Choncho, mukhoza kupanga osiyanasiyana basi kusintha malinga ndi kuchuluka kwa deta analowa.

Tiyerekeze kuti ndinu Investor ndipo muyenera kudziwa kuchuluka kwa ndalama zomwe munalandira panthawi yogulitsa chinthu china. Ndipo tiyerekeze kuti muli ndi lipoti lotero.

Kuti muchite izi, pali ntchito "Dynamic names". Kuti mupereke, muyenera kuchita izi:

1. Tsegulani zenera la Perekani Dzina.
2. Lembani minda monga momwe tawonetsera pazithunzi.
   

Ndikofunika kuzindikira kuti m'malo mwa mndandanda, ndondomeko yokhala ndi ntchito imagwiritsidwa ntchito Kutaya pamodzi ndi ntchito ZAKE.

Tsopano muyenera kulowa ntchito ya SUM ndi dzina lamtunduwo ngati mkangano. Mukayesa izi pochita, mutha kuwona momwe ndalamazo zimasinthira kutengera kuchuluka kwazinthu zomwe zidalowetsedwa. 

Monga mukuwonera, pali njira zambiri zosangalatsa zolumikizirana ndi magulu. Tikukhulupirira kuti mwakonda bukhuli kuyambira pazoyambira mpaka ukatswiri ndipo mwawona kuti ndi lothandiza.