Maulalo ndi chimodzi mwazida zazikulu pogwira ntchito ku Microsoft Excel. Ndi gawo limodzi mwamafomu omwe amagwiritsidwa ntchito mu pulogalamuyi. Ena mwa iwo amasinthana ndi zolemba zina kapena zinthu zina pa intaneti. Tiyeni tiwone momwe mungapangire mitundu yosiyanasiyana yamatchulidwe ku Excel.
Kupanga mitundu yosiyanasiyana ya maulalo
Tiyenera kudziwa kuti nthawi yomweyo mawu onse omwe akutchulidwa akhoza kugawidwa m'magulu awiri akulu: omwe amawerengedwa kuti awerengere magawo amachitidwe, ntchito, zida zina, ndi zomwe zimagwiritsidwa ntchito popanga chinthucho. Zotsirizazi zimadziwikanso kuti ma hyperlink. Kuphatikiza apo, maulalo (maulalo) amagawidwa mkati ndi kunja. Amkati akufotokoza mawu mkati mwa buku. Nthawi zambiri amagwiritsidwa ntchito kuwerengera, ngati gawo la formula kapena ntchito yolozera, kuloza ku chinthu china chomwe data ikukonzedwa ilimo. Mugawo lomwelo lingatchulidwe la iwo omwe amatanthauza malo ena papepala lina. Onse a iwo, kutengera katundu wawo, agawidwa kukhala wachibale komanso mtheradi.
Maulalo akwina amatanthauza chinthu chomwe chiri kunja kwa bukuli. Itha kukhala buku lina la Excel kapena malo mmenemu, chikalata chosiyana, komanso tsamba pa intaneti.
Mtundu wa chilengedwe chomwe mukufuna kupanga zimatengera mtundu womwe mukufuna kupanga. Tikhale mnjira zosiyanasiyana mwatsatanetsatane.
Njira 1: pangani maulalo mu fomati imodzi
Choyamba, tikuwona momwe tingapangire zosankha zingapo zamalumikizidwe amtundu wa Excel, ntchito, ndi zida zina zowerengera za Excel mkati mwake. Kupatula apo, nthawi zambiri amagwiritsidwa ntchito pochita.
Mawu osavuta kwambiri amawoneka motere:
= A1
Chofunikira chofunikira cha mawonekedwe ndi mawonekedwe "=". Mukakhazikitsa chizindikiro ichi mu khungu musanayankhule, chizindikirika kuti chikuimira. Chofunikira chikuyeneranso dzina la mzati (pankhaniyi A) ndi nambala yamsika (pankhaniyi 1).
Kukopa "= A1" imati mumtundu momwe adaikiramo, deta yochokera pazomwe zimalumikizidwa imakokedwa A1.
Ngati titha kusintha mawu omwe ali mu khungu momwe zotsatira zikuwonekera, mwachitsanzo, "= B5", ndiye kuti mfundo zochokera pachinthucho mogwirizana ndizokokedwa B5.
Pogwiritsa ntchito maulalo mutha kugwiranso ntchito zosiyanasiyana zamasamu. Mwachitsanzo, lembani mawu otsatirawa:
= A1 + B5
Dinani batani Lowani. Tsopano, pakapangidwe kamene mawuwa akupezeka, omwe ali ndi malingaliro omwe amaikidwa pazinthu zogwirizana A1 ndi B5.
Pogwiritsa ntchito mfundo zomwezi, kuchulukitsa, kuchotsa ndi masamu ena onse zimachitika.
Kulemba ulalo wosiyana kapena ngati fomula, sikofunikira kuyendetsa pa kiyibodi. Ingokhalani chizindikirocho "=", kenako dinani kumanzere pachinthu chomwe mukufuna kutumizirako. Adilesi yake iwonetsedwa pamalo omwe chikwangwanicho chikhazikitsidwa. zofanana.
Koma ziyenera kudziwika kuti kalembedwe koyenera A1 osati yokhayo yomwe ingagwiritsidwe ntchito mwanjira. Ku Excel, kalembedwe amagwira ntchito R1C1, momwe, mosiyana ndi mtundu wapitawu, magwirizanidwe sawonetsedwa ndi zilembo ndi manambala, koma mwa manambala okha.
Kukopa R1C1 chimodzimodzi A1, ndi R5C2 - B5. Ndiye kuti, motere, mosiyana ndi kalembedwe A1, pa malo oyamba pali zogwirizira mzere, ndi mzere wachiwiri.
Masitayilo onsewa amagwira ntchito molingana mu Excel, koma kukula kwake kumagwirizana A1. Kuti musinthe kuti muwone R1C1 chofunikira mu Excel zosankha pansi Mawonekedwe onani bokosi pafupi "R1C1 Lumikizano Waumwini".
Pambuyo pake, manambala adzawonekera pazolinganiza yolinganiza m'malo mwa zilembo, ndipo mawu omwe ali mu formula bar atenga mawonekedwe R1C1. Kuphatikiza apo, mawu omwe sanalembedwe polowa kudzera pamanja, koma podina chinthu chofananira, adzawonetsedwa ngati gawo lolumikizana ndi selo yomwe adayikiramo. Mu chithunzi pansipa, iyi ndi njira
= R [2] C [-1]
Ngati mulemba mawuwo pamanja, ndiye kuti amatenga mawonekedwe R1C1.
Poyambirira, mtundu wa wachibale (= R [2] C [-1]), ndipo lachiwiri (= R1C1) - Mtheradi. Maulalo athunthu amatanthauza chinthu china, ndipo chapachibale - pamalo a chinthucho, cholingana ndi khungu.
Ngati mungabwerere pamtundu wokhazikika, ndiye kuti maulalo apachibale ndi amtunduwo A1, komanso mtheradi $ A $ 1. Mwachangu, maulalo onse omwe amapangidwa ku Excel ndi achuma. Izi zikufotokozedwa kuti mukamakopera kugwiritsa ntchito chikhomo, kufunikira kwake kumasintha mogwirizana ndi kayendedwe.
- Kuti tiwone momwe zimawonekera machitidwe, timayang'ana foni A1. Ikani chizindikirocho pachinthu chilichonse chopanda pepala "=" ndipo dinani pachinthucho ndikugwirizanitsa A1. Pambuyo adilesi kuwonetsedwa ngati gawo la chilinganizo, dinani batani Lowani.
- Sunthani chidziwitso kumunsi chakumanzere kwa chinthu chomwe chotsatira chake chikusakira. Chopereka chikusintha kukhala chikhomo chodzaza. Gwirani batani lakumanzere ndikudula zolemba ziwirizi ndi mbaliyo ndi zomwe mukufuna kutengera.
- Kukopera kumamalizidwa, tikuwona kuti zofunikira pazomwe zidatsatidwa zamitunduzo ndizosiyana ndi zomwe zidalipo (zoyesedwa). Ngati mungasankhe foni iliyonse komwe tinakopera tsambalo, ndiye kuti mu fomulo mungawone kuti ulalo wasinthidwa mogwirizana ndi mayendedwewo. Ichi ndi chizindikiro cha ubale wake.
Katundu wa ubale nthawi zina amathandiza kwambiri pogwira ntchito ndi mitundu ndi matebulo, koma nthawi zina muyenera kutengera njira yeniyeni popanda kusintha. Kuti muchite izi, ulalo uyenera kusinthidwa kuti ukhale mtheradi.
- Kuti muchite kutembenuka, ndikokwanira kuyika chikwangwani cha dola pafupi ndi malo opingasa ndi owongoka ($).
- Tikatha kugwiritsa ntchito chikhomo, titha kuwona kuti mtengo mu maselo onse otsatira pambuyo kukopera uku akuwonetsedwa chimodzimodzi monga woyamba. Kuphatikiza apo, mukasunthasuntha pazinthu zilizonse kuchokera pamunsi pansipa, mudzazindikira kuti maulalo sanasinthe.
Kuphatikiza pa mtheradi ndi wachibale, palinso maulalo osakanikirana. Mwa iwo, siginecha ya dollar imangokhala mndandanda wokhawo womwe ukugwirizana (monga: $ A1),
kapena kungogwirizanitsa ndi zingwe (mwachitsanzo: A $ 1).
Chizindikiro cha dola chitha kuikidwa pamanja podina chizindikiro chofananira pa kiyibodi ($) Adziwikiridwa kwambiri ngati mu kiyibodi ya Chingerezi pamtunda wapamwamba dinani kiyi "4".
Koma pali njira yosavuta yowonjezerapo munthu yemwe mwatchulidwa uja. Mukungofunika kusankha zonena ndikusindikiza fungulo F4. Pambuyo pake, chikwangwani cha dollar chidzawoneka nthawi imodzi pamadongosolo onse opingasa. Pambuyo podina F4 cholumikizacho chimasinthidwa kukhala chosakanikirana: chikwangwani cha dollar chidzangokhala pazolumikizana za mzere, ndipo pazogwirizanitsa mzati zimatha. Dinani limodzi linanso F4 izitsogolera ku cholakwika china: chikwangwani cha dollar chimawonekera pazolumikizana za mizati, koma chimazimiririka pazoyang'anira mizere. Chotsatira, chikakanikizidwa F4 Ulalo umasinthidwa kukhala wachibale popanda zizindikiro za dollar. Makina osindikiza otsatira amasinthira kukhala amtheradi. Ndipo kotero pagulu latsopano.
Mu Excel, simungangotanthauza khungu limodzi lokha, komanso mtundu wonse. Adilesi yamtunduwu imawoneka ngati yolumikizana ndi mbali zake zamanzere zakumanzere ndi zam'munsi, zopatulidwa ndi koloni (:) Mwachitsanzo, mtundu womwe ukuonetsedwa pachithunzi pansipa uli ndi zomwe zikugwirizana A1: C5.
Chifukwa chake, cholumikizira cha mndandandawu chiziwoneka ngati:
= A1: C5
Phunziro: Zolumikizana kwathunthu komanso zachibale ku Microsoft Excel
Njira 2: pangani maulalo mu machitidwe kuzinthu zina zamasamba ndi mabuku
Izi zisanachitike, tidaganizira zochita mokha papepala limodzi. Tsopano tiwone momwe mungatanthauzire malo papepala lina kapena buku. Potsirizira pake, ichi sichingakhale ulalo wamkati, koma ulalo wakunja.
Mfundo za chilengedwe ndi zofanana ndendende zomwe tidakambirana pamwambapa ndi zomwe zidachitika papepala limodzi. Pamenepa ndi pokhapokha pakufunika kuwonetsa kuwonjezera adilesi ya pepalalo kapena buku lomwe khungu kapena mtundu womwe mukufuna kuloza.
Kuti muwone phindu pa pepala lina, muyenera pakati pa chikwangwani "=" ndipo ma cell a ma cell amawonetsa dzina lake, kenako ndikukhazikitsa chizimba.
Ndiye kulumikizana ndi selo Mapepala 2 ndi magwirizano B4 zikuwoneka chonchi:
= Sheet2! B4
Mawuwo akhoza kuyendetsedwa pamanja kuchokera pa kiyibodi, koma ndikosavuta kupitiliza motere.
- Khazikitsani chikwangwani "=" mu chinthu chomwe chizikhala ndi mawu akuti. Zitatha izi, pogwiritsa ntchito njira yachidule yomwe ili pamwamba pa malo omwe muli, pitani pa pepalalo lomwe mukufuna kuti mulumikizane.
- Pambuyo pa kusinthaku, sankhani chinthu chopatsidwa (khungu kapena mtundu) ndikudina batani Lowani.
- Pambuyo pake, pamakhala kubwerera pompopompo patsamba lakale, koma ulalo womwe timafunikira udzapangidwa.
Tsopano tiyerekeze momwe tingatanthauzire ku chinthu chomwe chili m'buku lina. Choyamba, muyenera kudziwa kuti mfundo zoyendetsera ntchito zosiyanasiyana za Excel ndi zida zomwe zili ndi mabuku ena ndizosiyana. Ena a iwo amagwira ntchito ndi mafayilo ena a Excel, ngakhale atatsekedwa, pomwe ena amafunikira kuti mafayilowa ayambitsidwe.
Pokhudzana ndi izi, mtundu wa ulalo wamabuku ena nawonso ndi wosiyana. Ngati mungagwiritse ntchito chida chomwe chimagwira ntchito ndi mafayilo, ndiye kuti, mutha kungotchula dzina la bukulo komwe mungalifotokozere. Ngati mukufuna kugwirira ntchito ndi fayilo yomwe simufuna kutsegula, pamenepa muyenera kutchula njira yonse yobwererera. Ngati simukudziwa momwe mungagwiritsire ntchito fayilo kapena simukudziwa momwe chida china chake chitha kugwirira ntchito ndi iyo, ndiye pankhaniyi ndibwino kutchula njira yonse. Izi sizachidziwikire.
Ngati mukufuna kulozera chinthu ndi adilesi C9yomwe ili Mapepala 2 mu buku loyendetsa lotchedwa "Excel.xlsx", kenako muyenera kulemba mawu otsatirawa mu pepalalo, pomwe phindu liziwonetsedwa:
= [excel.xlsx] Sheet2! C9
Ngati mukufuna kugwira ntchito ndi chikalata chotsekedwa, ndiye, mwa zina, muyenera kutchula njira ya komwe akukhalako. Mwachitsanzo:
= 'D: Foda yatsopano [excel.xlsx] Sheet2'! C9
Monga momwe mungapangire mawu osinthira ku pepala lina, mukapanga ulalo wa chinthu china cha buku lina, mutha kulowetsa pamanja kapena kusankha posankha foni yolumikizana kapena mtundu wina mufayilo lina.
- Timayika chizindikiro "=" muchipinda momwe mawu ofotokozedirawo adzapezekere.
- Kenako timatsegula buku lomwe likufunikira, ngati silinayambitsidwe. Dinani patsamba lake pamalo omwe mukufuna. Pambuyo pake, dinani Lowani.
- Izi zimangobwerera ku buku lakale. Monga mukuwonera, ili kale ndi yolumikizana ndi gawo la fayilo yomwe tidadinapo kale. Ili ndi dzina lokhalo lopanda njira.
- Koma ngati titseka fayilo lomwe tikutchulalo, ulalo ungosintha zokha. Idzapereka njira yonse ku fayilo. Chifukwa chake, ngati chilinganizo, chida kapena chida chikuthandizira kugwira ntchito ndi mabuku otsekedwa, tsopano, chifukwa cha kusintha kwa mawu omwe munganene, mutha kugwiritsa ntchito mwayiwu.
Monga mukuwonera, kulumikiza ulalo wa fayilo ina ndikudina pa iyo siyothandiza kwambiri kuposa kungolemba adilesiyi pamanja, komanso paliponseponseponseponseponseponseponse, chifukwa pamenepa ulalo womwe umasinthasintha kutengera kuti buku lomwe amalifotokoza latsekedwa, kapena tsegulani.
Njira 3: ntchito IND
Njira ina yosakira chinthu mu Excel ndi kugwiritsa ntchito INDIA. Chida ichi chidangopangidwira kuti zilembedwe monga mawu. Maulalo omwe adapangidwa mwanjira imeneyi amatchedwanso "chitsimikiziro chachikulu", chifukwa amalumikizidwa ndi selo lomwe limawonetsedwa mwamphamvu kuposa mawu amtheradi. Mawu osakira akuti:
= INDIRECT (yolumikizira; a1)
Lumikizani - Uku ndi kutsutsana komwe kumayang'ana cell mu malembo a malembedwe (atakulungidwa mu zolemba);
"A1" - mkangano wosankha womwe ukugwirizana ndi momwe masanjidwewo agwiritsidwira ntchito: A1 kapena R1C1. Ngati kufunikira kwa mkanganowu "ZOONA"ndiye njira yoyamba imagwira ntchito ngati FALSE - kenako chachiwiri. Ngati mkanganowo sutchulidwa konse, ndiye kuti mosasinthika kumaganiziridwa kuti kuwongolera mtunduwo A1.
- Timayika zomwe zili mu pepalalo momwe formula izikhala. Dinani pachizindikiro "Ikani ntchito".
- Mu Ntchito wiz mu block Malingaliro ndi Kufika sangalalani "INDIA". Dinani "Zabwino".
- Zenera la tsambali la wothandizira likutsegulidwa. M'munda Cell Link ikani cholozera ndi kusankha chinthucho patsamba lomwe tikufuna kutanthauzira posintha ndi mbewa. Adilesi ikawonetsedwa m'munda, "timakulunga" ndi mawu olemba. Munda wachiwiri ("A1") siyani kanthu. Dinani "Zabwino".
- Zotsatira za kukonza ntchitoyi zimawonetsedwa mu khungu losankhidwa.
Mwatsatanetsatane maubwino ndi kusiyanasiyana kwa kugwira ntchito ndi ntchitoyo INDIA kuyesedwa mu gawo lophunzirira.
Phunziro: Ntchito ya INDX mu Microsoft Excel
Njira 4: pangani zopangidwazo
Ma Hyperlink ndi osiyana ndi mtundu wa maulalo omwe tawunika pamwambapa. Sathandizira "kukoka" deta kuchokera kumadera ena kupita ku foni komwe kuli, koma kusintha pomwe akungowona komwe akutanthauza.
- Pali njira zitatu zosunthira pawindo la hyperlink. Malinga ndi woyamba wa iwo, muyenera kusankha khungu lomwe oikamo azikhalamo, ndikudina kumanja kwake. Pazosankha, muyenera kusankha njira "Hyperlink ...".
M'malo mwake, mutasankha chinthu chomwe hyperlink ikayika, mutha kupita ku tabu Ikani. Pamenepo pa tepi muyenera kumadina batani "Pikokoyamaula".
Komanso, mukasankha foni, mutha kuyika ma keytroke CTRL + K.
- Pambuyo pakugwiritsa ntchito iliyonse mwanjira zitatu izi, zenera lopangira hyperlink limatseguka. Mbali yakumanzere ya zenera, mutha kusankha chinthu chomwe mukufuna kulumikizana:
- Ndi malo m'buku lakalipano;
- Ndi buku latsopano;
- Ndi tsamba la webusayiti kapena fayilo;
- Ndi imelo.
- Pokhapokha, zenera limayamba mumayendedwe olumikizirana ndi fayilo kapena tsamba la webusayiti. Pofuna kuphatikiza chinthu ndi fayilo, mkati mwa zenera pogwiritsa ntchito zida zoyendera muyenera kupita kumalo osungira komwe kuli fayilo yomwe mukufuna ndikuisankha. Itha kukhala buku la Excel kapena fayilo ya mtundu wina uliwonse. Pambuyo pake, zogwirizira ziwonetsedwa m'munda "Adilesi". Kenako, kumaliza ntchitoyo, dinani batani "Zabwino".
Ngati pakufunika kulumikizidwa ndi tsamba lawebusayiti, ndiye kuti mu gawo ili la zenera la Hyperlink "Adilesi" mukungoyenera kufotokoza adilesi yazofunikira patsamba ndikudina batani "Zabwino".
Ngati mukufuna kufotokozera zophatikizira kumalo komwe kulibe buku, pitani ku gawolo "Lumikizanani ndi kuyika chikalata". Komanso pakatikati pazenera muyenera kutchula pepalalo ndi adilesi ya foni yomwe mukufuna kulumikiza. Dinani "Zabwino".
Ngati mukufuna kupanga chikalata chatsopano cha Excel ndikumumangiriza pogwiritsa ntchito cholembera ku buku latsopanoli, pitani pagawo Lumikizani ku chikalata chatsopano. Kenako, pakatikati pa zenera, lipatseni dzina ndikuwonetsa komwe kuli disk. Kenako dinani "Zabwino".
Ngati mungafune, mutha kulumikiza pepalalo ndi chosakanizira, ngakhale ndi imelo. Kuti muchite izi, sinthani ku gawo Lumikizani Imelo ndi m'munda "Adilesi" tchulani imelo. Dinani "Zabwino".
- Pambuyo poyikapo chithunzi Izi zikutanthauza kuti chophatikiza chimagwira. Kuti mupite ku chinthu chomwe chikugwirizana nacho, ingodinani kawiri pa icho ndi batani lakumanzere.
Kuphatikiza apo, Hyperlink imatha kupangidwa pogwiritsa ntchito ntchito yomangidwa, yomwe ili ndi dzina lomwe limadziyimira lokha - "HYPERLINK".
Mawu awa ali ndi syntax:
= HYPERLINK (adilesi; dzina)
"Adilesi" - mkangano wowonetsa adilesi ya webusayiti pa intaneti kapena fayilo pa hard drive yomwe mukufuna kukhazikitsa kulumikizana.
"Dzinalo" - mkangano mumtundu wa mawu omwe awonetsedwa pazinthu zokhala ndi zotsatsira. Mkanganowu ndi wosankha. Ngati kulibe, adilesi ya chinthu chomwe ntchitoyo ikutanthauza iwonetsedwa mu pepalalo.
- Sankhani khungu lomwe Hyperlink ikayika, ndikudina chizindikiro "Ikani ntchito".
- Mu Ntchito wiz pitani pagawo Malingaliro ndi Kufika. Maka dzina "HYPERLINK" ndikudina "Zabwino".
- M'bokosi lamakani m'munda "Adilesi" tchulani adilesi ku webusayiti kapena fayilo pa hard drive. M'munda "Dzinalo" lembani zolemba zomwe zikuwonetsedwa mu pepala. Dinani "Zabwino".
- Pambuyo pake chizindikiritso chidzapangidwa.
Phunziro: Momwe mungapangire kapena kuchotsa ma hyperlinks ku Excel
Tidazindikira kuti m'magome a Excel pali magulu awiri azilumikizano: omwe amagwiritsidwa ntchito pazomangamanga ndi omwe amagwiritsidwa ntchito pakusintha (ma hyperlink). Kuphatikiza apo, magulu awiriwa agawidwa m'mitundu yaying'ono yambiri. Kukongoletsa kwa kapangidwe kake kumatengera mtundu wa ulalo.
