Gwiritsani ntchito matebulo ogwirizana mu Microsoft Excel

Mukamagwira ntchito zina mu Excel, nthawi zina mumayenera kuthana ndi matebulo angapo omwe amathandizana. Ndiko kuti, deta kuchokera pa tebulo imodzi imalowetsedwamo, ndipo ikawasintha, miyeso ya mndandanda wa tebulo yonse ikuwerengedwanso.

Ma tebulo ogwiritsidwa ntchito ndi othandiza kwambiri pokonza zambirimbiri. Sizomwe zili bwino kuti mukhale ndi chidziwitso chonse mu tebulo limodzi, ndipo ngati sichigwirizana. Ndikovuta kugwira ntchito ndi zinthu zotere ndikuzifufuza. Vutoli limapangidwira kuthetsa matebulo ofanana, chidziwitso pakati pa zomwe zimagawidwa, koma panthawi imodzimodziyo chikugwirizana. Mipukutu yowonjezerayi ingapezeke kokha mu pepala limodzi kapena buku limodzi, komanso likupezeka m'mabuku osiyana (mafayilo). Mwachizoloŵezi, njira ziwiri zomalizira zimagwiritsidwa ntchito kawirikawiri, chifukwa cholinga cha teknolojiyi ndi kuchoka pa kusonkhanitsa deta, ndipo kuyika pa tsamba lomwelo sikungathetseretu vutoli. Tiyeni tiphunzire momwe tingakhalire ndi momwe tingagwirire ndi mtundu uwu wa kasamalidwe ka deta.

Kupanga matebulo ogwirizana

Choyamba, tiyeni tipitirire pafunso la momwe zingatheke kupanga chiyanjano pakati pa ma tebulo osiyanasiyana.

Njira 1: Kulumikiza mwachindunji matebulo ndi ndondomeko

Njira yosavuta yolumikizitsa deta ndiyo kugwiritsa ntchito njira zomwe zimagwirizanitsa ndi ma tebulo ena. Icho chimatchedwa mwachindunji chomangiriza. Njirayi ndi yosavuta, popeza ndiyiyi yokhayo imagwiridwa mofanana ndi kulenga zolemba pa deta limodzi.

Tiyeni tiwone momwe chitsanzo chingakhalire mgwirizano womangiriza mwachindunji. Tili ndi matebulo awiri pa mapepala awiri. Mu tebulo limodzi, malipiro amawerengedwa pogwiritsira ntchito ndondomeko mwa kuchulukitsa mlingo wa antchito ndi mlingo umodzi kwa onse.

Pa pepala lachiwiri pali mndandanda wa malemba omwe muli mndandanda wa ogwira ntchito ndi malipiro awo. Mndandanda wa ogwira ntchito m'mabuku onse awiriwa umaperekedwa mofanana.

Ndikofunika kupanga kotero kuti deta pamtengo wochokera pa tsamba lachiwiri ikulumikizidwa m'maselo ofanana.

1. Pa pepala loyamba, sankhani selo yoyamba ya selo. "Bet". Ife timayika mu chizindikiro chake "=". Kenaka, dinani palemba "Mapepala 2"Imene ili pambali ya kumanzere kwa mawonekedwe a Excel pamwamba pa barreti yoyenera.



2. Zimasunthira kumalo ena achiwiri. Dinani pa selo yoyamba m'mbali. "Bet". Kenaka dinani pa batani. Lowani pa kibokosilo kuti apange deta mu selo yomwe chizindikirocho chinayikidwa kale zofanana.



3. Kenaka pali kusintha kosinthika ku pepala loyamba. Monga momwe mukuonera, mlingo wa wogwira ntchito yoyamba kuchokera pa tebulo yachiwiri amakokeretsedwa mu selo yoyenera. Tikayika chithunzithunzi mu selo yomwe ili ndi bet, tikuwona kuti njira yowonjezera imagwiritsidwa ntchito kusonyeza deta pazenera. Koma zisanachitike makonzedwe a selo kumene deta ikuwonetsedwa, pali ndondomeko "Sheet2!"zomwe zimasonyeza dzina la malo omwe alipo. Njira yowonjezeramo kwa ife ndi izi:

   = Mapepala2! B2



4. Tsopano mukufunikira kusamutsa deta pa mitengo ya antchito ena onse a malonda. Inde, izi zikhoza kuchitidwa mofanana ndi momwe tinagwirira ntchitoyi kwa wogwira ntchito yoyamba, koma popeza kuti ndondomeko yonse ya antchito ikukonzekera chimodzimodzi, ntchitoyo ikhoza kuchepetsedwa mosavuta ndi kufulumira yankho lake. Izi zikhoza kuchitika mwa kungoponyera ndondomekoyi m'munsimu. Chifukwa chakuti zogwirizana ndi Excel zimakhala zosasinthika, zikaponyedwa, zimasintha, zomwe ndi zomwe timafunikira. Ndondomeko yojambula yokha ikhoza kuchitidwa pogwiritsa ntchito chikhomo chodzaza.

   Choncho, ikani chithunzithunzi m'munsi mwachindunji cha chinthucho ndi njirayi. Pambuyo pake, thumbalo liyenera kutembenuzidwa kuti lidzaze mawonekedwe a mtanda wakuda. Timachititsa kupopera kwa batani lamanzere ndikusakaniza chithunzithunzi mpaka pansi pake.



5. Deta yonse yochokera pa tsamba limodzi Mapepala 2 adakwezedwa ku gome Mapepala 1. Deta ikasintha Mapepala 2 iwo adzasintha okha pa choyamba.

Njira 2: Gwiritsani ntchito gulu la ogwira ntchito INDEX - MATCH

Koma bwanji ngati mndandanda wa ogwira ntchito muzolemba zosasankhidwa sizinakonzedwe mofanana? Pankhaniyi, monga tanenera poyamba, imodzi mwa njira zomwe mungasankhe ndi kukhazikitsa mgwirizano pakati pa maselo omwe ayenera kulumikizidwa mwawokha. Koma izi ndi zoyenera pa matebulo ang'onoang'ono. Pazigawo zazikulu, njirayi, mwabwino, idzatenga nthawi yochuluka kuti ikwaniritse, ndipo poipa - pakuchita izo sizingatheke konse. Koma mutha kuthetsa vutoli ndi gulu la ogwira ntchito INDEX - GANIZANI. Tiyeni tiwone momwe izi zingathere pogwirizanitsa deta m'matawuni amtundu, omwe adafotokozedwa mu njira yapitayi.

1. Sankhani chinthu choyamba m'ndandanda. "Bet". Pitani ku Mlaliki Wachipangizomwa kuwonekera pa chithunzi "Ikani ntchito".



2. Mu Wizard ntchito mu gulu "Zolumikizana ndi zolemba" pezani ndi kusankha dzina INDEX.



3. Wogwiritsa ntchitoyi ali ndi mitundu iwiri: mawonekedwe ogwira ntchito ndi zilembo. Kwa ife, njira yoyamba ikufunika, kotero muwindo lotsatira posankha mawonekedwe omwe adzatsegule, ife timasankha ilo ndi dinani pa batani "Chabwino".



4. Wowonjezera zowutsa makanema watha. INDEX. Ntchito ya ntchito yapadera ndiyo kuwonetsera mtengo umene uli muchisankho choyenderana ndi chiwerengero choyikidwa. Njira yoyendetsera ntchito INDEX ndi izi:

   = INDEX (mzere; mndandanda_nambala; [ndondomeko_numberu])

   "Mzere" - mtsutso wokhala ndi adiresi yazomwe tingapatsidwepo ndi chiwerengero cha chingwe chofotokozedwa.

   "Nambala ya mzere" - mtsutso womwe uli nambala ya mzerewu wokha. Ndikofunika kudziwa kuti nambala ya mzere sayenera kufotokozedwa mogwirizana ndi chilembo chonsecho, koma kokha poyerekeza ndi osankhidwawo.

   "Nambala ya column" - Mtsutso ndiwotheka. Kuti tithetse vuto lathu makamaka, sitidzaligwiritsa ntchito, choncho sichiyenera kufotokozera zomwe zilipo padera.

   Ikani cholozera mmunda "Mzere". Pambuyo pake pita Mapepala 2 ndipo, pokhala ndi batani lamanzere, dinani zonse zomwe zili m'ndandanda "Bet".



5. Pambuyo pazowonjezereka zikuwonetsedwa muwindo la operekera, ikani cholozera mmunda "Nambala ya mzere". Tidzawonetsa zokambiranazi pogwiritsira ntchito woyendetsa GANIZANI. Choncho, dinani pa katatu komwe kuli kumanzere kwa ntchitoyi. Mndandanda wa oyendetsa ntchito atsopano wagwiritsidwa ntchito. Ngati inu mutapeza pakati pawo dzina "KUKHALA"ndiye inu mukhoza kuwonekera pa izo. Popanda kutero, dinani pa chinthu chamtundu waposachedwa - "Zina ...".



6. Windo loyambira likuyamba. Oyang'anira ntchito. Pitani ku gulu lomwelo. "Zolumikizana ndi zolemba". Nthawi ino m'ndandanda, sankhani chinthucho "KUKHALA". Dinani pa batani. "Chabwino".



7. Imagwiritsa ntchito zokhudzana ndi zenera zowonetsera GANIZANI. Ntchito yowonjezedwayo ikukonzekera kuti iwonetse chiwerengero cha mtengo wapadera mwa dzina lake. Chifukwa cha mwayi uwu, tidzatha kuwerengera nambala ya mzere wa mtengo wapadera wa ntchitoyi. INDEX. Syntax GANIZANI anapereka monga:

   = MATCHA (kuyeza kosaka; kujambula mzere; [match_type])

   "Ndalama yamtengo wapatali" - ndemanga yomwe ili ndi dzina kapena adiresi ya selo lachitatu la chipani lomwe liripo. Ndilo malo a dzina ili m'magulu omwe ayenera kuwerengedwa. Kwa ife, kukangana koyamba kudzakhala mafotokozedwe a selo Mapepala 1omwe ali maina a antchito.

   "Zowonongeka" - ndewu yomwe imayimira kulumikiza ku gulu limene mtengo wapadera wafufuzidwa kuti uzindikire malo ake. Tidzakhala ndi gawo ili la aderesi "Dzina loyamba on Mapepala 2.

   "Mapu Mtundu" - kutsutsana komwe kuli kotheka, koma, mosiyana ndi zomwe tanena kale, tidzakhala ndi mkangano wosankha. Imawonetsa momwe wogwiritsira ntchitoyo adzafanane ndi mtengo wofunidwa ndi gulu. Mtsutso uwu ukhoza kukhala ndi umodzi mwazinthu zitatu: -1; 0; 1. Kuti musagwirizane nazo, sankhani kusankha "0". Njira iyi ndi yoyenera pa mlandu wathu.

   Kotero, tiyeni tiyambe kudzaza m'minda ya zenera. Ikani cholozera mmunda "Ndalama yamtengo wapatali", dinani selo yoyamba ya chigawocho "Dzina" on Mapepala 1.



8. Pambuyo pa makonzedwe awonetsedwe, ikani cholozera mmunda "Zowonongeka" ndipo pitani pa njira yochepa "Mapepala 2"yomwe ili pansi pawindo la Excel pamwamba pa barreti yoyenera. Gwirani batani lamanzere la mouse ndikusindikizira maselo onse m'mbali. "Dzina".



9. Pambuyo pazitsulo zawo zikuwonetsedwa mmunda "Zowonongeka"pitani kumunda "Mapu Mtundu" ndi kuyika chiwerengerocho kuchokera ku kibodibodi "0". Zitatha izi, tibwerera kumunda. "Zowonongeka". Chowonadi ndi chakuti tidzatsanzira ndondomekoyi, monga momwe tachitira mu njira yapitayi. Padzakhala maadiresi otsutsa, koma tikuyenera kukonza mgwirizano wa zinthu zomwe zikuwonedwa. Sitiyenera kusintha. Sankhani makonzedwe a chithunzithunzi ndi dinani pafungulo la ntchito F4. Monga mukuonera, chizindikiro cha dola chinawonekera kutsogolo kwa makonzedwe, zomwe zikutanthauza kuti kugwirizana kuchokera pachibale kwakhala koyenera. Kenaka dinani pa batani "Chabwino".



10. Zotsatira zimasonyezedwa mu selo yoyamba ya mndandanda. "Bet". Koma tisanayambe kukopera, tifunika kukonza dera lina, ndilo mtsutso woyamba wa ntchitoyo INDEX. Kuti muchite izi, sankhani zinthu zomwe zili m'ndandanda yomwe ili ndi ndondomekoyi, ndipo pita ku bar. Sankhani mtsutso woyamba wa woyendetsa INDEX (B2: B7) ndipo dinani pa batani F4. Monga momwe mukuonera, chizindikiro cha dola chinawonekera pafupi ndi malo osankhidwa. Dinani pa batani Lowani. Kawirikawiri, mawonekedwewa anatenga mawonekedwe otsatirawa:

    = INDEX (Mapepala 2 $ $ B $ 2: $ B $ 7; MATCH (Mapepala1! A4; Sheet2! $ A $ 2: $ A $ 7; 0))



11. Tsopano mukhoza kukopera pogwiritsa ntchito chikhomo chodzaza. Iitaneni mofanana momwe tinayankhulira kale, ndikutambasula mpaka kumapeto kwa magulu a tebulo.



12. Monga mukuonera, ngakhale kuti mzere wa mizere iwiri yosagwirizana ikugwirizana, komabe, miyezo yonse imakhazikika mogwirizana ndi mayina a antchito. Izi zinapindula mwa kugwiritsa ntchito ophatikiza ochita ntchito INDEX-GANIZANI.

Onaninso:
Excel ntchito INDEX
Masewerawa akugwira ntchito ku Excel

Njira 3: Pangani Masamu Ntchito ndi Associated Data

Kulumikizana kwachindunji kwabwino kumathandizanso kuti asonyeze kuwonetsera komwe kumawonetsedwa mndandanda umodzi wa matebulo mu imodzi mwa matebulo, komanso kuti achite masabata osiyanasiyana osiyanasiyana (kuphatikiza, magawano, kuchotsa, kuchulukitsa, etc.).

Tiyeni tiwone momwe izi zikuchitidwira. Tiyeni tichite zimenezo Mapepala 3 Dongosolo la malipiro la anthu onse lidzawonetsedwa popanda kuwonongeka kwa ogwira ntchito. Pachifukwa ichi, ndalama za antchito zidzachotsedwa Mapepala 2, onetsani (ntchitoyi SUM) ndi kuchulukitsidwa ndi coefficient pogwiritsa ntchito njirayi.

1. Sankhani selo komwe malipiro onse adzawonetsedwe Mapepala 3. Dinani pa batani "Ikani ntchito".



2. Iyenera kuyambitsa zenera Oyang'anira ntchito. Pitani ku gululo "Masamu" ndipo sankhani dzina pamenepo "SUMM". Kenako, dinani pakani "Chabwino".



3. Kusunthira ku zenera zotsutsana zenera SUMyomwe yapangidwa kuti iwerengere kuchuluka kwa manambala osankhidwa. Lili ndi mawu ofanana awa:

   = SUM (nambala1; nambala2; ...)

   Masamba pawindo amagwirizana ndi zifukwa za ntchitoyi. Ngakhale kuti chiwerengero chawo chikhoza kufika zidutswa 255, cholinga chathu chokha chimakhala chokwanira. Ikani cholozera mmunda "Number1". Dinani pa chizindikiro "Mapepala 2" pamwamba pa bolodi la chikhalidwe.



4. Titasamukira ku gawo lofunidwa la bukhuli, sankhani ndime yomwe iyenera kufotokozedwa. Timapanga kukhala chithunzithunzi, ndikugwira batani lamanzere. Monga mukuonera, zogwirizanitsa za dera losankhidwa zimapezeka nthawi yomweyo m'munda wa zenera. Kenaka dinani pa batani. "Chabwino".



5. Pambuyo pake, ife timasamukira Mapepala 1. Monga mukuonera, chiwerengero cha malipiro a ogwira ntchito akuwonetsedwa kale.



6. Koma sizo zonse. Pamene tikukumbukira, malipiro amawerengedwa powonjezera mtengo wa mlingo wa coefficient. Chomwecho, timasankhiranso selo limene chiwerengerochi chapezeka. Zitatero pitani ku bar. Timaonjezera chizindikiro chochulukitsa ku ndondomeko yake (*), kenako dinani mbali yomwe coefficient ili. Kuti muyese chiwerengero, dinani Lowani pabokosi. Monga momwe mukuonera, pulogalamuyi inkawerengetsera malipiro onse a malonda.



7. Bwererani ku Mapepala 2 ndi kusintha kukula kwa mlingo wa wogwira ntchito aliyense.



8. Pambuyo pa izi, pitirizani kusamba ndi tsamba lonse. Monga mukuonera, chifukwa cha kusintha kwa tebulo lofanana, zotsatira za malipiro onse adakonzedweratu.

Njira 4: Kuyika kwapadera

Mukhozanso kugwirizanitsa ma tebulo pa Excel ndi choyika chapadera.

1. Sankhani mfundo zomwe ziyenera "kuyimitsidwa" ku gome lina. Kwa ife, ili ndilolumikiza. "Bet" on Mapepala 2. Dinani pa chidutswa chosankhidwa ndi batani lamanja la mouse. M'ndandanda yomwe imatsegula, sankhani chinthucho "Kopani". Mgwirizanowu wamagulu ena ndi Ctrl + C. Zitatha izi Mapepala 1.



2. Kusunthira ku gawo lofunidwa la bukhuli, timasankha maselo omwe mukufuna kukoka. Kwa ife, iyi ndi mzere. "Bet". Dinani pa chidutswa chosankhidwa ndi batani lamanja la mouse. Mmenemo mauthenga mu toolbar "Njira Zowonjezera" dinani pazithunzi "Lowani Chizindikiro".

   

   Palinso njira ina. Mwa njira, ndi yokhayo yazaka zowonjezera za Excel. Mu menyu yachidule, yendetsani cholozera ku chinthucho "Sakani Mwapadera". Mu menyu owonjezera omwe amatsegula, sankhani chinthucho ndi dzina lomwelo.



3. Pambuyo pake, mawindo ena apadera akuwonekera. Timakanikiza batani "Lowani Chizindikiro" mu ngodya ya kumanzere ya selo.



4. Mulimonse momwe mungasankhire, zoyenera kuchokera pa tebulo limodzi zidzalowetsedwa mu zina. Mukasintha deta mu gwero, iwonso adzasintha pazowonjezera.

PHUNZIRO: Sakanizani Zapadera mu Excel

Njira 5: Kuyanjana pakati pa matebulo m'mabuku ambiri

Kuphatikizanso, mungathe kukonza mgwirizano pakati pa ma tebulo a mabuku osiyanasiyana. Izi zimagwiritsa ntchito chida choyikapo. Zochita zidzakhala zofanana ndi zomwe talingalira mu njira yapitayi, kupatula kuti kayendetsedwe kazomwekuyambitsayo sikuyenera kuchitika pakati pa malo a bukhu limodzi, koma pakati pa mafayilo. Mwachibadwa, mabuku onse okhudzana nawo ayenera kukhala omasuka.

1. Sankhani ma deta omwe mukufuna kutumiza ku bukhu lina. Dinani pa ilo ndi botani lamanja la mouse ndipo sankhani malo omwe akuwonekera "Kopani".



2. Kenaka timasamukira ku buku limene deta iyi iyenera kuikidwa. Sankhani mtundu woyenera. Dinani botani lamanja la mouse. M'ndandanda wamakono mu gululo "Njira Zowonjezera" sankhani chinthu "Lowani Chizindikiro".



3. Pambuyo pake, zikhulupiliro zidzalowetsedwa. Mukasintha deta mu bukhu la chitsimikizo, malemba omwe amachokera ku bukhuli adzawakweza. Ndipo sizomwe kuli kofunikira kuti mabuku onse awiri akhale otseguka pa izi. Ndikwanira kutsegula buku limodzi lokha, ndipo lidzangotulutsa deta kuchokera ku chilolezo chotsekedwa, ngati kusintha kunapangidwa kale.

Koma tisaiwale kuti pakadali pano kulembedwa kudzapangidwa ngati mawonekedwe osasinthika. Ngati mutayesa kusintha selo iliyonse ndi deta, uthenga udzakudziwitsani kuti sikutheka kuchita izi.

Zosintha m'magulu oterewa ogwirizanitsidwa ndi bukhu lina zingangopangidwanso mwa kuswa chiyanjano.

Kusokoneza pakati pa matebulo

Nthawi zina ndikofunikira kuthetsa mgwirizano pakati pa ma tebulo. Chifukwa cha ichi chikhoza kukhala, monga momwe tafotokozera pamwambapa, pamene mukufuna kusintha mndandanda wazinthu kuchokera ku bukhu lina, kapena chifukwa chakuti wosuta sakufuna kuti deta ikhale yosinthidwa kuchokera kwa wina.

Njira 1: kuchotsa pakati pa mabuku

Mukhoza kusokoneza kugwirizana pakati pa mabuku mu maselo onse pochita opaleshoni imodzi. Pa nthawi yomweyi, deta yomwe ili m'maselo idzakhalabe, koma izi zidzakhala zowonongeka zomwe sizidalira malemba ena.

1. Mu bukhuli, momwe mfundo za maofesi ena amachotsedwa, pitani ku tabu "Deta". Dinani pazithunzi "Sinthani maumboni"yomwe ili pa tepiyi mu zida za zipangizo "Connections". Tiyenera kukumbukira kuti ngati bukhu lamakono liribe mauthenga kwa mafayilo ena, batani iyi siyatha.



2. Fenera la kusintha maulumikilo amayamba. Sankhani kuchokera mndandanda wa mabuku ofanana (ngati alipo angapo) fayilo limene tikufuna kuti tisiyane. Dinani pa batani "Yambani chiyanjano".



3. Zowonjezera zowonjezera zimatsegula, pomwe pali chenjezo lokhudza zotsatira za zochita zina. Ngati muli otsimikiza za zomwe muchita, ndiye dinani pa batani. "Mphwanyani".



4. Pambuyo pake, maumboni onse a fayilo yomwe ilipo pakadali pano adzasinthidwa ndi machitidwe abwino.

Njira 2: Yesani Makhalidwe

Koma njira yomwe ili pamwambayi ndi yoyenera kokha ngati mukufuna kuchotsa zonse zogwirizana pakati pa mabuku awiriwa. Kodi mungatani ngati mukufuna kutsegula matebulo okhudzana ndi mafayilo omwewo? Mukhoza kuchita izi polemba deta, kenako ndikuziyika pamalo omwewo monga momwe zimakhalira.Mwa njira, njira yomweyo ingagwiritsidwe ntchito kusokoneza mgwirizano pakati pa mapepala osiyanasiyana a mabuku osiyanasiyana popanda kuphwanya kugwirizana kwakukulu pakati pa mafayilo. Tiyeni tiwone momwe njira iyi ikugwirira ntchito.

1. Sankhani mtundu umene tikufuna kuchotsa chiyanjano ku gome lina. Dinani pa ilo ndi batani lamanja la mouse. Mu menyu yomwe imatsegula, sankhani chinthucho "Kopani". Mmalo mwa zochitika izi, mukhoza kulemba njira yowonjezera yowonjezera. Ctrl + C.



2. Ndiye, popanda kuchotsa chosankhidwa kuchokera ku chidutswa chomwecho, ife timabwereza pa iyo ndi batani lamanja la mouse. Panthawi ino m'ndandanda wa zochitika zomwe timakani pa chithunzi "Makhalidwe"yomwe imayikidwa mu gulu la zida "Njira Zowonjezera".



3. Pambuyo pake, maulumikizano onse omwe ali osankhidwawo adzasinthidwa ndizomwe zimakhazikika.

Monga mukuonera, Excel ili ndi njira ndi zida zogwirizanitsa matebulo angapo palimodzi. Pachifukwa ichi, deta yanu ikhonza kukhala pamapepala ena komanso ngakhale m'mabuku osiyanasiyana. Ngati ndi kotheka, kugwirizana kumeneku kungawonongeke mosavuta.