Kugwira ntchito ndi matebulo olumikizidwa ku Microsoft Excel

Mukamachita ntchito zina ku Excel, nthawi zina mumayenera kuthana ndi matebulo angapo, omwe amakhalanso okhudzana. Ndiye kuti, deta yomwe imachokera pagome limodzi imatengedwera kwina ndipo ikasinthidwa, malingaliro amawerengedwa m'magawo onse okhudzana ndi matebulo.

Ma tebulo omwe amalumikizidwa ndi osavuta kugwiritsa ntchito pokonzekera zambiri. Kuyika zidziwitso zonse patebulo limodzi, pambali, ngati mulibe, sizothandiza kwambiri. Zimakhala zovuta kugwira ntchito ndi zinthu zotere ndikuzifufuza. Vuto lomwe likuwonetsedwa lakonzedwa kuti kuthetsedwe ndi matebulo olumikizidwa, chidziwitso chomwe chimagawidwa, koma nthawi yomweyo chikugwirizana. Magawo omwe amagwirizanitsidwa ndi matebulo amatha kukhala pokhapokha ngati buku limodzi kapena buku limodzi, amathanso kupezeka m'mabuku osiyana (mafayilo). Zosankha ziwiri zomalizazi nthawi zambiri zimagwiritsidwa ntchito pochita, chifukwa cholinga chaukadaulo uwu ndichakuti asachokere ku deta, ndikuiunjikira patsamba limodzi sikuti kumathetsa vutoli. Tiyeni tiphunzire momwe tingapangire komanso momwe mungagwirire ntchito ndi mtundu wamtunduwu wowongolera deta.

Kupanga Matepi Ogwirizanitsidwa

Choyamba, tiyeni tilingalire pa funso loti ndi njira ziti zomwe mwayi ungakhalire ubale pakati pa magawo osiyanasiyana.

Njira 1: molumikizira magome ndi chilinganizo

Njira yosavuta yomangira deta ndikugwiritsa ntchito njira zomwe zimatchulanso magawo ena. Amatchedwa kumangiriza mwachindunji. Njira iyi ndiyabwino, popeza ndi izi kulumikizana kumachitika chimodzimodzi monga kulenga kulumikizana kwa tsatanetsatane patsamba limodzi.

Tiyeni tiwone momwe, mwachitsanzo, mgwirizano ungapangidwe mwa kumangirira mwachindunji. Tili ndi matebulo awiri pamasamba awiri. Patebulo limodzi, omwe amalipira amawerengedwa pogwiritsa ntchito fomula pochulukitsa kuchuluka kwa ogwira ntchito ndi cholingana chimodzi kwa onse.

Patsamba lachiwiri pali magawo a tebulo, omwe ali ndi mndandanda wa ogwira ntchito ndi malipiro awo. Mndandanda wa ogwira nawo ntchito onsewa amaperekedwa munjira yomweyo.

Ndikofunikira kuwonetsetsa kuti zomwe zakhala zikuchokera patsamba lachiwirili zimakokedwa m'maselo ofanana ndi oyambayo.

1. Patsamba loyamba, sankhani foni yoyamba Pikisano. Timayika chikwangwani "=". Kenako, dinani njira yachidule "Mapepala 2", yomwe ili kumanzere kwa mawonekedwe a Excel pamwamba pa bar.



2. Asamukira kudera lachiwiri la chikalatacho. Timadula khungu loyamba m'ndandayo Pikisano. Kenako dinani batani Lowani pa kiyibodi yolowetsa chidziwitso mu foni momwe chikwangwani chidayikidwira kale zofanana.



3. Kenako pamakhala kusintha kwa pepala loyamba. Monga mukuwonera, kuchuluka kwa wogwira ntchito patebulo lachiwiri amakokedwa m'chipinda chimodzi. Poika cholozera pafoni yomwe ili ndi kubetcha, tikuwona kuti njira yachizolowezi imagwiritsidwa ntchito kuwonetsa deta pazenera. Koma asanagwirizane ndi khungu kuchokera komwe data yatulutsidwa, pali mawu "Sheet2!", zomwe zikuwonetsa dzina la malo omwe alembedwa. Makina ambiri mwanjira yathu akuwoneka motere:

   = Sheet2! B2



4. Tsopano muyenera kusamutsa deta pamitengo ya onse ena ogwira ntchito. Zachidziwikire, izi zitha kuchitika momwemo momwe tidamalirira ntchito woyamba, koma poti mndandandandondomeko onse awiri adayikidwa mwadongosolo lofananalo, ntchitoyi ikhoza kuphweka ndikuchepetsa mayankho ake. Izi zitha kuchitika mwa kungotengera chilinganizo kumizere yomwe ili pansipa. Chifukwa chakuti maulalo mu Excel amakhala osakwaniritsidwa, akamakopedwa, zosintha zimasinthidwa, zomwe ndi zomwe timafunikira. Njira yotsitsira nokha ikhoza kuchitidwa pogwiritsa ntchito cholembera.

   Chifukwa chake, ikani cholozera cholowera pansi kumanzere kwa chinthucho ndi kakhalidwe. Pambuyo pake, cholozera chizisinthidwa kukhala chikhomo chodzaza ndi mtanda wakuda. Tsitsani batani lakumanzere ndikukokera kutemberera mpaka pansi penipeni.



5. Zambiri kuyambira pa mzere womwewo Mapepala 2 adakokedwa pagome Mapepala 1. Mukamasintha deta kukhala Mapepala 2 asintha zokha pa woyamba.

Njira yachiwiri: kugwiritsa ntchito mulu wa othandizira INDEX - SEARCH

Koma bwanji ngati mndandanda wa ogwira nawo ntchito omwe akukhala patebulo sakhala womwewo? Pankhaniyi, monga tanena kale, imodzi mwazomwe mungasankhe ndi kukhazikitsa kulumikizana pakati pa selo iliyonse yomwe imayenera kulumikizidwa pamanja. Koma izi ndizoyenera kwa magome ochepa. Pazigawo zazikulu, kusankha koteroko kumatenga nthawi yambiri kuti mukwaniritse, ndipo koposa pamenepo, sizingatheke. Koma vutoli litha kuthetsedwa pogwiritsa ntchito gulu la ogwiritsa ntchito INDEX - Sakani. Tiyeni tiwone momwe izi zitha kuchitidwira polumikiza data yomwe ili m'matafura yomwe idakambidwa pazomwe zidachitidwa kale.

1. Sankhani gawo loyambirira Pikisano. Pitani ku Fotokozerani Wizardpodina chizindikiro "Ikani ntchito".



2. Mu Ntchito wiz pagululi Malingaliro ndi Kufika pezani ndikuwonetsa dzinalo INDEX.



3. Wogwiritsa ntchitoyu ali ndi mitundu iwiri: mawonekedwe ogwiritsira ntchito makonzedwe ndi dzina lake. M'malo mwathu, njira yoyamba ndiyofunikira, chifukwa chake, pazenera lotsatira posankha mawonekedwe omwe amatsegula, sankhani ndikudina batani "Zabwino".



4. Window yotsutsana ndi Ophunzira idayambitsidwa INDEX. Ntchito ya ntchitoyi ndikutulutsa mtengo womwe uli mumtundu wosankhidwa mu mzere ndi nambala yomwe idafotokozedwayo. Makina opangira onse INDEX Izi ndi:

   = INDEX (magulu; mzere_number; [ndime_number])

   Menya - mkangano womwe uli ndi adilesi ya malo omwe tithandizire kuchuluka kwa mzere womwe udafotokozedwawu.

   Nambala ya mzere - kutsutsana, komwe nambala ya mzere womwewu. Ndikofunikira kudziwa kuti nambala ya mzere siyenera kufotokozedwa zofanana ndi chikalata chonse, koma okhawo omwe adasankhidwa.

   Chiwerengero Cha Column - mkangano wosankha. Sitizigwiritsa ntchito kuti tithetse vuto lathu, chifukwa chake sikofunikira kufotokoza tanthauzo lake padera.

   Ikani wolemba m'munda Menya. Pambuyo pake, pitani Mapepala 2 ndikugwira batani lamanzere lakumanzere, sankhani zonse zomwe zalembedwa Pikisano.



5. Maulalo atawonetsedwa pazenera la wothandizira, ikani cholozera m'munda Nambala ya mzere. Tichotsa mkanganowu pogwiritsa ntchito wothandizira Sakani. Chifukwa chake, timadula patatu, yomwe ili kumanzere kwa mzere wa ntchito. Mndandanda wa ogwiritsa ntchito omwe atha kugulidwa kumene akutsegulidwa. Ngati mupeza dzina pakati pawo "Sakani"ndiye mutha kuwonekera. Kupanda kutero, dinani chinthu chomaliza pamndandanda - "Zina ...".



6. Zenera loyenera limayamba Ogwira Ntchito. Timadutsira mu gulu lomweli Malingaliro ndi Kufika. Pano, sankhani zomwe zalembedwa "Sakani". Dinani batani. "Zabwino".



7. Windo la otsutsana ndi ogwiritsira ntchito limagwira Sakani. Ntchito yomwe yatchulidwa imawonetsedwa kuti iwonetse kuchuluka kwa mtengo mumndandanda wake ndi dzina lake. Chifukwa cha izi, tiziwerengera mzere wamtundu wa phindu linalake pantchitoyo INDEX. Syntax Sakani zoyimiriridwa motere:

   = SEARCH (search_value; lookup_array; [match_type])

   "Kufunafuna mtengo" - mkangano wokhala ndi dzina kapena adilesi ya foni yamgulu lachitatu momwe aliri. Ndi malo a dzinali m'gulu la chandamale lomwe likuyenera kuwerengedwa. M'malo mwathu, mkangano woyamba uzikhala ma cell omwe ali Mapepala 1komwe mayina antchito amakhala.

   Wawonedwa - mkangano woyimira kalozera wamagulu momwe mtengo womwe umasunthidwa umasunthidwa kuti udziwe udindo wake. Adilesi "" ayimba pano. "Dzina loyamba pa Mapepala 2.

   Mtundu Wofananira - mkangano, womwe ungakhale wosankha, koma, mosiyana ndi mawu am'mbuyomu, tifunikira malingaliro osankha awa. Zikuwonetsa momwe wothandizira angafanane ndi mtengo wosakira ndi makanawo. Kutsutsana kumatha kukhala ndi chimodzi mwazinthu zitatu: -1; 0; 1. Pazosanja zosasanjika, sankhani "0". Izi ndi zoyenera mlandu wathu.

   Chifukwa chake, tiyeni tiyambe kudzaza magawo a zenera zotsutsa. Ikani wolemba m'munda "Kufunafuna mtengo"dinani pa foni yoyamba ya mzati "Dzinalo" pa Mapepala 1.



8. Mapulogalamu atatha kuwonetsedwa, ikani cholozera m'munda Wawonedwa ndipo dinani njira yachidule "Mapepala 2", yomwe ili pansi pazenera la Excel pamwamba pazenera. Gwirani batani la mbewa yakumanzere ndikusankha maselo onse omwe ali mgulowo "Dzinalo".



9. Pambuyo pazolumikizidwa zawo ziwonekera kumunda Wawonedwapitani kumunda Mtundu Wofananira ndikuyika manambala pamenepo kuchokera ku kiyibodi "0". Pambuyo pake, timabwereranso kumunda Wawonedwa. Chowonadi ndi chakuti tidzatsata fomula, monga momwe tidachitiranso kale. Kusintha kwa adilesi kudzachitika, koma apa tiyenera kukonza magwirizano a gulu lomwe likuwonedwa. Sichiyenera kuchotsedwa ntchito. Sankhani zolumikizira ndi tvomerero ndikusindikiza batani la ntchito F4. Monga mukuwonera, chikwangwani cha dollar chidawonekera patsogolo paogwirizanitsa, zomwe zikutanthauza kuti cholumikizachi chidasinthika kuchoka pachiyanjano kupita pamtheradi. Kenako dinani batani "Zabwino".



10. Zotsatira zake zikuwonetsedwa mu foni yoyamba ya mzati. Pikisano. Koma tisanayambe kukopera, tikuyenera kukonza gawo lina, lomwe ndilo lingaliro loyamba la ntchitoyi INDEX. Kuti muchite izi, sankhani gawo lomwe lili ndi fomula, ndikusunthira kumzere wa mafomula. Timasankha mkangano woyamba wothandizira INDEX (B2: B7) ndikudina batani F4. Monga mukuwonera, chikwangwani cha dollar chidawoneka pafupi ndi ma adilesi osankhidwa. Dinani batani Lowani. Mwambiri, formula idatenga mawonekedwe otsatirawa:

    = INDEX (Sheet2! $ B $ 2: $ B $ 7; SEARCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0))



11. Tsopano mutha kukopera kugwiritsa ntchito chikhomo. Timazitcha momwemonso momwe tidalankhulira kale, ndikuzitambasulira kumapeto kwa tebulo.



12. Monga mukuwonera, ngakhale kuti mzere wazida ziwiri zomwe zikugwirizana sizikugwirizana, komabe, mfundo zonse zimakokedwa molingana ndi mayina antchito. Izi zidakwaniritsidwa pogwiritsa ntchito ophatikiza ophatikiza INDEX-Sakani.

Werengani komanso:
Ntchito ya EXEX ku Excel
Ntchito ya EXCEL ku Excel

Njira 3: gwiritsani ntchito masamu ndi zokhudzana ndi deta

Kumanga deta mwachindunji ndikwabwino chifukwa kumakupatsani mwayi woti musangowonetsa ziwonetsero zina zomwe zikuwonetsedwa pamndandanda wina wa tebulo, komanso mumachita nawo masamu osiyanasiyana (kuwonjezera, kugawa, kuchotsa, kuchulukitsa, ndi zina).

Tiyeni tiwone momwe izi zimachitikira. Tiyeni timveke Mapepala 3 Zambiri zolipira pamakampani zikuwonetsedwa popanda kusweka ndi antchito. Kuti muchite izi, mitengo ya antchito idzachotsedwa Mapepala 2, mwachidule (pogwiritsa ntchito ntchitoyo) SUM) ndi kuchulukitsa ndi coefflication pogwiritsa ntchito njira.

1. Sankhani khungu lomwe zotsatira za kuwerengera kolipira zikuwonetsedwa. Mapepala 3. Dinani batani. "Ikani ntchito".



2. Zenera liyenera kuyamba Ogwira Ntchito. Pitani pagululo "Masamu" ndi kusankha dzina pamenepo SUM. Kenako, dinani batani "Zabwino".



3. Zotsutsana za ntchito zimasunthidwa pazenera SUM, yomwe idapangidwa kuwerengera kuchuluka kwa manambala osankhidwa. Ili ndi mtundu wotsatira:

   = SUM (nambala1; nambala2; ...)

   Minda yomwe ili pazenera limafanana ndi zotsutsana za ntchito yomwe idatchulidwa. Ngakhale kuchuluka kwawo kungafikire 255, imodzi yokha ndiyoikwanira pazolinga zathu. Ikani wolemba m'munda "Nambala1". Dinani pa njira yachidule "Mapepala 2" Pamwamba pa bar.



4. Tikasamukira ku gawo lomwe mukufuna bukuli, sankhani gawo lomwe lifupikitsidwe. Timachita izi ndi tchire pomwe tili ndi batani lakumanzere. Monga mukuwonera, zogwirizanitsa za malo osankhidwa zimawonetsedwa nthawi yomweyo m'munda wa zenera zotsutsa. Kenako dinani batani "Zabwino".



5. Pambuyo pake, timasamukira Mapepala 1. Monga mukuwonera, chiwerengero chonse cha mayendedwe antchito chikuwonetsedwa kale pazogwirizana.



6. Koma si zokhazo. Monga momwe timakumbukira, malipiro amawerengedwa pochulukitsa kuchuluka kwa mtengo ndi chinthu. Chifukwa chake, timasankhanso cell yomwe mtengo wofotokozedwawu ukupezeka. Pambuyo pake timadutsa pamzere wa njira. Onjezani mufomula momwemo chisonyezo chokuchulukitsira (*), ndipo dinani pazinthu zomwe zikuyimira chisonyezo chokwanira. Kuti muwerengere, dinani batani Lowani pa kiyibodi. Monga mukuwonera, pulogalamuyo idawerengera ndalama zonse zomwe bizinesiyo imabweretsa.



7. Kubwerera ku Mapepala 2 ndi kusintha kuchuluka kwa wogwira ntchito.



8. Pambuyo pake, timasunthanso kutsamba ndi kuchuluka kwathunthu. Monga mukuwonera, chifukwa cha kusintha pagome lolumikizidwa, zotsatira za malipiro onse zidachitikanso.

Njira 4: kulowetsera

Mutha kulumikizanso kusintha kwa tebulo ku Excel pogwiritsa ntchito kupangira kwapadera.

1. Timasankha zomwe zidzafunika "kukokedwa" pagome lina. M'mbuto mwathu, lino ndi gawo la mzati Pikisano pa Mapepala 2. Timadina pachidutswa chosankhidwa ndi batani loyenera la mbewa. Pamndandanda womwe umatsegulira, sankhani Copy. Njira yocheperako kiyibodi ndiy Ctrl + C. Pambuyo pake timapita ku Mapepala 1.



2. Popeza takhala kudera la buku lomwe timafunikira, timasankha maselo momwe tidzafunikira kukokera zomwe tikuzitsatira. M'malo mwathu, iyi ndi mzati Pikisano. Timadina pachidutswa chosankhidwa ndi batani loyenera la mbewa. Pazomwe zili mndandanda wazipangizo zothandizira Ikani Zosankha dinani pachizindikiro Matani Ulalo.

   

   Palinso njira ina. Zodabwitsa ndizakuti, ndiokhayo pamitundu yakale ya Excel. Pazosankha zofanizira, onjezerani "Lowetsani mwapadera". Pazowonjezera zomwe zimatsegulira, sankhani malo omwe ali ndi dzina lomwelo.



3. Pambuyo pake, zenera loyika lapadera limatseguka. Dinani batani Matani Ulalo kumunsi kwakumanzere kwa khungu.



4. Njira iliyonse yomwe mungasankhe, yamtengo wazomwe zimayikidwa pagome limodzi. Mukamasintha zomwe zalembedwa, zisinthanso zokha pazomwe zimayikidwa.

Phunziro: Lowetsani Mwapadera ku Excel

Njira 5: kulumikizana pakati pa matebulo m'mabuku angapo

Kuphatikiza apo, mutha kukonza njira yolumikizirana pakati pa malo a tebulo m'mabuku osiyanasiyana. Chida chofunikira cholumikizira chimagwiritsidwa ntchito. Zochitazi zikhala zofanana ndendende ndi zomwe tidakambirana m'mbuyomu, kupatula kuti muyenera kuyendayenda mukupanga mafayilo osakhala pakati pa malo amodzi, koma pakati pa mafayilo. Mwachilengedwe, mabuku onse okhudzana ayenera kukhala otseguka.

1. Sankhani magawo osiyanasiyana omwe mukufuna kusinthira ku buku lina. Dinani kumanja pa icho ndikusankha mawonekedwe mumenyu omwe akutsegulira. Copy.



2. Kenako timasunthira ku buku lomwe izi zidzafunika kuyikidwamo. Sankhani mtundu womwe mukufuna. Dinani kumanja. Pazosankha zam'maguluwo Ikani Zosankha sankhani Matani Ulalo.



3. Pambuyo pake, mfundozo zimayikidwa. Zomwe zosungidwa mubookbookbook zingasinthe, mindandanda yazosintha patebulo imangodzikokera zokha. Kuphatikiza apo, sizofunikira kuti mabuku onse azitsegulira izi. Ndikokwanira kutsegula buku limodzi lokha, ndipo imangokoka yokha kuchokera ku chikalata chogwirizanitsidwa ngati zosintha zinasinthidwa kale.

Koma ziyenera kudziwidwa kuti pankhaniyi kuwayika kudzapangidwa ngati chosasinthika. Mukamayesa kusintha khungu lililonse ndi chinthu chomwe mwayika, uthenga umayamba kukudziwitsani kuti sizingatheke kuchita izi.

Zosintha pamtundu wotere womwe zimagwirizanitsidwa ndi buku lina zitha kupangidwa mwa kuswa ulalo.

Pakati pa matebulo

Nthawi zina muyenera kuthana ndi kulumikizana pakati pa magawo a tebulo. Cholinga cha izi zitha kukhala mlandu wofotokozedwa pamwambapa, ngati pakufunika kusintha makina omwe afotokozedwa kuchokera ku buku lina, kapena kungokayika kwa wosuta kuti zomwe zili mu tebulo limodzi zimangosinthidwa kuchokera kwina.

Njira 1: kuthyola kulumikizana kwa mabuku

Mutha kuthana ndi kulumikizana kwa mabuku a m'maselo onse mwa kuchitapo kanthu. Nthawi yomweyo, zomwe zimasungidwa mu maselowo zidzatsalira, koma zidzakhala kale mfundo zosasinthika zomwe sizimadalira zolemba zina.

1. Mu buku, momwe maukadaulo amajambula amakokedwa, pitani ku tabu "Zambiri". Dinani pachizindikiro "Sinthani Zoyankhulana"ili pa riboni m'bokosi la chida Maulalo. Dziwani kuti ngati bukuli lilibe maulalo pamafayilo ena, ndiye kuti batani silothandiza.



2. Iwindo losintha maulalo likhazikitsidwa. Timasankha fayilo yomwe tikufuna kuti tisiyane ndi mndandanda wamabuku okhudzana (ngati alipo angapo). Dinani batani Phwanya ulalo.



3. Windo lazidziwitso limatsegulidwa, momwe mumakhala chenjezo lazotsatira za zochita zina. Ngati mukutsimikiza zomwe muti muchite, ndiye dinani batani "Kuthamangitsa".



4. Zitatha izi, maulalo onse amtundu wapafayilo mu chikalata chamakono adzasinthidwa ndi maimidwe abwino.

Njira 2: Ikani Malingaliro

Koma njira yomwe ili pamwambapa ndi yoyenera pokhapokha ngati mukufuna kuthana ndi maulalo onse omwe ali pakati pa mabuku awiriwa. Zoyenera kuchita ngati mukufuna kupatula matebulo okhudzana omwe ali mkati mwa fayilo yomweyo? Mutha kuchita izi mwa kukopera zomwezo kenako kuziyika pamalo amodzi momwe mulili. Mwanjira, momwemonso, mutha kuthyola kulumikizana pakati pa magulu amtundu wautali m'mabuku osiyanasiyana popanda kuswa mgwirizano wapakati pa mafayilo. Tiyeni tiwone momwe njirayi imagwirira ntchito.

1. Sankhani mtundu womwe tikufuna kuchotsa ulalo wina pagome lina. Timadulira pomwepo ndi batani la mbewa. Pazosankha zomwe zimatsegulira, sankhani Copy. M'malo mwazochita izi, mutha kujambula kuphatikiza kosinthana ndi mafungulo otentha Ctrl + C.



2. Komanso, osachotsa kusankhako kachidutswa kamodzi, dinani pomwepo. Nthawi ino pamndandanda wazomwe mungachite, dinani pazizindikiro "Makhalidwe"lomwe lili mgulu la chida Ikani Zosankha.



3. Pambuyo pake, maulalo onse omwe ali mumtundu wosankhidwa adzasinthidwa ndi mitengo yokhazikika.

Monga mukuwonera, ku Excel pali njira ndi zida zolumikizira matebulo angapo pamodzi. Nthawi yomweyo, deta ya tabular ikhoza kukhala pamapepala ena ngakhale pamabuku osiyanasiyana. Ngati ndi kotheka, kulumikizana uku kukhoza kusweka.