Hanyoyin haɗin yanar gizo sune ɗayan manyan kayan aikin yayin aiki a Microsoft Excel. Su bangare ne na asali wanda aka yi amfani da su a cikin shirin. Wasu daga cikinsu suna bautar don canzawa zuwa wasu takardu ko ma albarkatun kan Intanet. Bari mu gano yadda ake ƙirƙirar nau'ikan maganganu na nunawa a cikin Excel.
Kirkirar nau'ikan hanyoyin haɗi
Ya kamata a sani yanzunnan ana iya raba duk maganganun magana zuwa manyan rukuni biyu: waɗanda aka yi niyyar lissafin azaman ɓangare na dabaru, ayyuka, sauran kayan aikin, da waɗanda aka yi amfani da su zuwa abin da aka ƙayyade. Karshen su kuma ana kiranta hyperlinks. Bugu da kari, hanyoyin (hanyoyin) sun kasu gida da waje. Ciki ana nufin maganganu a cikin littafi. Mafi yawanci ana amfani dasu don lissafi, a matsayin ɓangaren tsari ko hujja game da aiki, suna nuni zuwa takamaiman abu inda bayanan ke gudana. A cikin wannan rukuni za a iya danganta su ga waɗanda ke nufin wani wuri a kan takaddar takaddar. Dukkanin, dangane da dukiyoyinsu, sun kasu zuwa dangi da madaidaici.
Hanyoyin haɗin waje suna magana da wani abu wanda baya waje da littafin yanzu. Wannan na iya zama wani littafin aikin Excel na gaba ko wuri a ciki, daftarin tsari daban, ko ma yanar gizo akan Intanet.
Nau'in halittar da kake son ƙirƙirar ya dogara da irin nau'in da kake son ƙirƙirar. Bari mu zauna kan hanyoyi da yawa daki-daki daki daki daki.
Hanyar 1: ƙirƙirar hanyar haɗi a cikin tsari tsakanin takarda ɗaya
Da farko dai, zamu kalli yadda ake kirkirar zabin hanyoyin haɗi iri iri don tsarin dabara, ayyuka, da sauran kayan aikin ƙididdigar Excel a cikin takarda guda. Bayan haka, ana yawan amfani dasu a aikace.
A mafi sauki bayanin magana yayi kama da wannan:
= A1
Abun da ake buƙata na bayyanar alama hali ne "=". Sai kawai lokacin da ka shigar da wannan alama a cikin tantanin kafin magana, za a gan shi yana nufin. Siffar da ake buƙata shine sunan shafi (a wannan yanayin) A) da lambar shafi (a wannan yanayin 1).
Bayyanawa "= A1" ya ce a cikin abin da aka shigar dashi, an jawo bayanai daga abu tare da kayan haɗin keɓaɓɓu A1.
Idan muka maye gurbin magana a cikin sel inda aka nuna sakamakon, misali, "= B5", sannan darajoji daga abu tare da masu gudanarwa za'a jawo su a ciki B5.
Yin amfani da hanyoyin haɗin yanar gizo zaka iya yin ayyukan lissafi daban-daban. Misali, rubuta wannan magana:
= A1 + B5
Latsa maballin Shigar. Yanzu, a cikin abin da aka samo wannan magana, taƙaita abubuwan dabi'u waɗanda aka sanya cikin abubuwa tare da daidaitawa A1 da B5.
Ta hanyar tsarin manufa guda ne, ayi yawa, raguwa da kowane aikin lissafi.
Don rubuta wata hanyar haɗin daban ko kuma wani ɓangare na dabara, ba lallai ba ne a fitar da shi daga maballin. Kawai saita alama "=", sannan hagu-danna kan abu wanda kake so ka ambata. Za a nuna adireshin sa a cikin abin da aka sanya alamar. daidai.
Amma ya kamata a lura cewa salon daidaitawa A1 ba shine kadai za'a iya amfani da shi ba. A cikin Excel, salo yana aiki R1C1, a cikin, sabanin sigar da ta gabata, ana nuna alamun ba ta haruffa da lambobi ba, amma lambobi ne kawai.
Bayyanawa R1C1 daidai A1, da R5C2 - B5. Wannan shine, a wannan yanayin, ya bambanta da salon A1, a farkon wuri sune daidaitawar layin, da kuma shafi a na biyu.
Dukansu nau'ikan suna aiki daidai a cikin Excel, amma ƙirar daidaitawa ta asali ita ce A1. Don canza shi don kallo R1C1 da ake buƙata a cikin zaɓuɓɓukan Excel a ƙarƙashin Tsarin tsari duba akwatin kusa da "Tsarin hanyar R1C1".
Bayan haka, lambobi za su bayyana a kan kwamitin daidaitawa a maimakon haruffa, kuma maganganun da ke cikin dabarar sigogin zai dauki fom R1C1. Haka kuma, maganganun da aka rubuta ba ta shigar da masu gudanarwa da hannu ba, amma ta danna kan abin da ya dace, za a nuna su ta hanyar kayan aikin dangin da aka sanya su. A hoton da ke ƙasa, wannan shine dabara
= R [2] C [-1]
Idan ka rubuta magana da hannu, to zai dauki hanyar da aka saba R1C1.
A farkon lamari, nau'in dangi (= R [2] C [-1]), kuma a na biyu (= R1C1) - cikakke. Cikakkun hanyoyin haɗi suna nufin takamaiman abu, da waɗanda ke da alaƙa - zuwa matsayin ɓangaren, dangane da tantanin halitta.
Idan kun koma zuwa ga daidaitaccen salon, to, hanyoyin haɗin dangi suna da nau'i A1, kuma cikakke $ A $ 1. Ta hanyar tsoho, duk hanyoyin haɗin da aka kirkira a cikin Excel suna da alaƙa. An bayyana wannan a gaskiyar cewa lokacin kwafa ta amfani da alamar cikawa, ƙimar da ke cikinsu tana canzawa dangane da motsi.
- Don ganin yadda zai kaya a aikace, muke magana akan tantanin halitta A1. Saita alamar a cikin kowane abun da babu komai a ciki "=" kuma danna kan abu tare da masu gudanarwa A1. Bayan an nuna adireshin a matsayin wani ɓangaren dabara, danna maɓallin Shigar.
- Matsar da siginan kwamfuta zuwa toan hagun dama na abin da aka nuna sakamakon aikin. Maɓallin sigari ya canza zuwa alamar mai cikawa. Riƙe maɓallin linzamin kwamfuta na hagu kuma ja mai a layi ɗaya zuwa kewayon tare da bayanan da kake son kwafa.
- Bayan an gama kwafa, za mu ga cewa dabi'u a abubuwan da ke biyo baya na kewayon sun bambanta da na farkon (wanda aka kwafa). Idan ka zaɓi kowane tantanin halitta inda muka kwafa bayanan, to a cikin masarar dabara zaka ga cewa an sauya hanyar haɗin dangane da motsi. Wannan alama ce ta dangantakarsa.
Kayan haɗin dangi wani lokaci yana taimakawa mai yawa yayin aiki tare da dabaru da tebur, amma a wasu lokuta kuna buƙatar kwafa ainihin madaidaicin tsari ba tare da wani canje-canje ba. Don yin wannan, dole ne a canza hanyar haɗin zuwa cikakke.
- Don aiwatar da juyawa, ya isa a sanya alamar dollar kusa da maƙallan layin tsaye da na tsaye (a tsaye)$).
- Bayan mun yi amfani da alamar cikawa, zamu iya ganin cewa darajar a cikin duk ƙwayoyin masu zuwa lokacin da aka nuna kwafin daidai yake da na farko. Bugu da kari, lokacin da kuka hau kan kowane abu daga zangon da ke ƙasa a cikin masarar dabara, zaku lura cewa hanyoyin ba su canzawa ba.
Baya ga cikakke da dangi, akwai kuma hanyoyin hadewa. A cikinsu, alamar dala tana nuna alama ko dai kawai daidaitawar shafi (misali: $ A1),
ko kawai daidaitawar kirtani (misali: $ 1).
Za'a iya shigar da alamar dala da hannu ta danna kan alamar da ke daidai a kan keyboard ($) Za a haskaka idan a cikin babban layin rubutu na Ingilishi a cikin babban harka danna maballin "4".
Amma akwai wata hanya mafi dacewa don ƙara yanayin da aka ƙayyade. Kawai zaka zabi magana game da latsa madannin F4. Bayan wannan, alamar dollar zata bayyana lokaci guda a duk daidaitawa na kwance da a tsaye. Bayan danna kan F4 Haɗin ɗin ya canza zuwa gauraye: alamar dollar za ta kasance ne kawai a cikin daidaitawar layin, kuma a daidaitawar shafi zai shuɗe. Karo daya danna F4 zai haifar da kishiyar sakamako: alamar dollar ta bayyana a wurin daidaitawar ginshiƙai, amma ya ɓace a daidaitawar layuka. Na gaba, lokacin da aka matsa F4 An canza hanyar haɗin zuwa dangi ba tare da alamun dala ba. Latsa bugu na gaba ya juya shi ya zama cikakke. Sabili da haka a cikin sabon da'irar.
A cikin Excel, zaku iya nufin ba kawai ga takamaiman tantanin halitta ba, har ma zuwa daukacin kewayon. Adireshin kewayon yayi kama da daidaitawa daga abubuwan hagu na hagu da ƙananan dama, keɓaɓɓu biyu:) Misali, kewayon da aka fifita a hoton da ke ƙasa yana da daidaitawa A1: C5.
Dangane da haka, hanyar haɗi zuwa wannan tsararren abu zai yi kama da:
= A1: C5
Darasi: Cikakke kuma alaƙa da haɗin gwiwa a Microsoft Excel
Hanyar 2: kirkiro hanyar haɗi a cikin dabarun zuwa wasu zanen gado da littattafai
Kafin wannan, mun dauki ayyuka ne kawai a cikin takardar guda. Yanzu bari mu ga yadda za mu koma ga wani wuri a kan takarda ko ma littafi. A cikin maganar ta ƙarshe, wannan ba zai zama hanyar haɗin ciki ba, amma hanyar haɗin waje.
Ka'idodin halitta daidai suke kamar yadda muka tattauna a sama tare da ayyuka akan shafi ɗaya. A wannan yanayin ne kawai zai zama dole a nuna ban da adireshin takardar ko littafin da kwayar ko zangon da kake son ambata yana wurin.
Dangane da darajar da ke kan wani takarda, kuna buƙatar tsakanin alamar "=" kuma tantanin halitta ya nuna sunanta, sannan saita saita alamar.
Don haka hanyar haɗi zuwa tantanin halitta akan Sheet 2 tare da gudanarwa B4 zai yi kama da wannan:
= Sheet2! B4
Ana iya fitar da magana a hannu da hannu daga maballin, amma yafi dacewa a ci gaba kamar haka.
- Saita alamar "=" a cikin abin da zai ƙunshi faɗar magana. Bayan haka, ta amfani da gajeriyar hanya ta sama da matsayin matsayin, je zuwa takarda inda abin da kake son haɗawa da shi ya kasance.
- Bayan miƙa mulki, zaɓi abu da aka bayar (tantina ko kewayo) kuma danna maɓallin Shigar.
- Bayan haka, za a sami dawowar atomatik zuwa takardar da ta gabata, amma hanyar haɗin da muke buƙata za a haifar.
Yanzu bari mu gano yadda zamu koma ga wani abu wanda yake cikin wani littafi. Da farko dai, kuna buƙatar sanin cewa ka'idodin aiki na ayyukan Excel da kayan aiki tare da sauran littattafai sun bambanta. Wasu daga cikinsu suna aiki tare da wasu fayilolin Excel, ko da idan an rufe su, yayin da wasu ke buƙatar ƙaddamar da waɗannan fayilolin don hulɗa.
Dangane da waɗannan fasalulluka, nau'in haɗin zuwa wasu littattafai ma daban ne. Idan ka saka shi a cikin kayan aiki wanda yake aiki tare da fayiloli masu gudana, to a wannan yanayin, zaka iya bayyana sunan littafin da kake magana a kai. Idan kun yi niyyar aiki tare da fayil ɗin da ba za ku buɗe ba, to a wannan yanayin kuna buƙatar bayyana cikakken hanyar zuwa gare ta. Idan baku sani ba a cikin wane yanayin zaku yi aiki tare da fayil ɗin ko ba ku da tabbacin yadda wani kayan aiki zai iya aiki tare da shi, to a wannan yanayin yana da kyau a faɗi cikakken hanyar. Tabbas wannan ba zai zama superfluous ba.
Idan kuna buƙatar komawa zuwa abu tare da adireshin C9a kan Sheet 2 a cikin wani littafi mai gudana wanda ake kira "Excel.xlsx", sannan ya kamata ku rubuta wannan magana a cikin takardar takardar, inda za a nuna darajar:
= [excel.xlsx] Sheet2! C9
Idan kuna shirin yin aiki tare da takaddara takaddar, to, a tsakanin sauran abubuwa, kuna buƙatar ƙayyade hanyar da wurin sa. Misali:
= 'D: Sabon babban fayil [Excel.xlsx] Sheet2'! C9
Kamar yadda yake kan batun samar da magana game da wani takarda, yayin ƙirƙirar hanyar haɗi zuwa wani ɓangaren wani littafi, zaka iya shigar da shi da hannu ko zaɓi shi ta zaɓar lambar da ya dace ko kewayon cikin wani fayil.
- Mun sanya alama "=" a cikin tantanin halitta inda za'a nuna magana mai ma'ana.
- Daga nan sai mu bude littafin a kan abin da ake buƙata ya koma, idan ba a fara ba. Danna maballin a wurin da kake son ambata. Bayan haka, danna kan Shigar.
- Wannan yana komawa zuwa littafin baya. Kamar yadda kake gani, tuni tana da hanyar haɗi zuwa wani ɓangaren fayil ɗin da muka danna a cikin matakin da ya gabata. Ya ƙunshi sunan kawai ba tare da hanya ba.
- Amma idan muka rufe fayil din da muke magana, mahaɗin zai canza kai tsaye. Zai gabatar da cikakken hanyar zuwa fayil ɗin. Don haka, idan dabara, aiki ko kayan aiki na goyan bayan aiki tare da littattafan da ke rufe, yanzu, godiya ga canji na magana mai ma'ana, zaku iya amfani da wannan damar.
Kamar yadda kake gani, buɗe hanyar haɗi zuwa wani ɓangaren fayil ɗin ta danna kan shi ba kawai ya fi dacewa da shigar da adireshin da hannu ba, har ma ya fi ko'ina, tunda a wannan yanayin hanyar haɗin da kanta tana jujjuya ne ko littafin da yake magana yana rufe, ko bude.
Hanyar 3: INDIRECT aiki
Wani zaɓi don nuna wani abu a cikin Excel shine amfani da aikin INDIA. Wannan kayan aikin kawai an ƙirƙiri don ƙirƙirar maganganun tunani a cikin rubutu. Hanyoyin haɗin yanar gizon da aka kirkira ta wannan hanyar ana kiransu "super-cikakke", tunda suna da alaƙa da tantanin halitta da aka nuna a cikin su har ma fiye da yadda aka bayyana su. Gwanin wannan bayanin shine:
= INDIRECT (mahadar; a1)
Haɗi - wannan wata mahawara ce da ke magana kan tantanin halitta a rubutun rubutu (a nade a alamomin magana);
"A1" - zaɓi na zaɓi wanda ke tabbatar da wane salo ne ake amfani da daidaitawar: A1 ko R1C1. Idan darajar wannan hujja "GASKIYA"sannan zaɓi na farko zai kasance idan KARYA - sannan na biyu. Idan an cire wannan mahaɗan gaba ɗaya, to, ta hanyar tsohuwar magana ana ganin shine magance nau'in A1.
- Munyi alama a cikin takardar da za'a samo asalin samfurin. Danna alamar "Saka aikin".
- A Mayen aiki a toshe Tunani da Arrays bikin "INDIA". Danna "Ok".
- Daga nan sai taga mahawara ta wannan mai aiki ta bude. A fagen Hanyar Sadarwa saita siginan kwamfuta kuma zaɓi kashi a kan takardar da muke so mu koma ta danna tare da linzamin kwamfuta. Bayan an nuna adireshin a filin, sai mu “lullube shi” da alamomin magana. Na biyu filin ("A1") bar komai. Danna kan "Ok".
- Sakamakon sarrafa wannan aikin an nuna shi cikin tantanin da aka zaɓa.
A cikin dalla-dalla dalla-dalla da fa'ida da kuma amfani da aiki tare da aikin INDIA yi nazari a cikin wani daban darasi.
Darasi: Aikin INDX a Microsoft Excel
Hanyar 4: ƙirƙiri hanyoyin sadarwa
Hyperlinks sun bambanta da nau'in hanyoyin haɗin yanar gizon da muka bincika a sama. Ba su yin amfani da "jawo" bayanai daga wasu yankuna zuwa tantanin da suke inda, amma don yin sauyi lokacin danna kan yankin da suke magana a kai.
- Akwai zaɓuɓɓuka uku don kewaya zuwa taga ƙirƙirar hyperlink. Dangane da farkon su, kuna buƙatar zaɓar tantanin da za a shigar da hyperlink, sa'annan danna-dama akansa. A cikin menu na mahallin, zaɓi zaɓi "Shafin yanar gizo ...".
Madadin haka, bayan zaɓin kashi inda za'a sanya hyperlink, zaku iya zuwa shafin Saka bayanai. A can akan tef ɗin kana buƙatar danna maballin "Shafin yanar gizo".
Hakanan, bayan zaɓar tantanin halitta, zaku iya amfani da maɓallin keystrokes CTRL + K.
- Bayan amfani da ɗayan waɗannan zaɓuɓɓuka guda uku, taga halitta hyperlink yana buɗewa. A gefen hagu na taga, zaka iya zaɓar wane abu da kake son tuntuɓar:
- Tare da wuri a cikin littafin yanzu;
- Tare da sabon littafi;
- Tare da yanar gizo ko fayil;
- Tare da e-mail.
- Ta hanyar tsoho, taga yana farawa a cikin yanayin sadarwa tare da fayil ko shafin yanar gizo. Domin danganta wani abu tare da fayil, a tsakiyar ɓangaren taga ta amfani da kayan aikin kewayawa kana buƙatar zuwa jigon rumbun kwamfutarka inda fayilolin da ake so yana wurin kuma zaɓi shi. Zai iya zama ko littafin aikin Excel ko fayil na kowane tsari. Bayan haka, za a nuna kwarrafan a fagen "Adireshin". Na gaba, don kammala aikin, danna maɓallin "Ok".
Idan akwai buƙatar haɗi zuwa yanar gizo, to, a wannan yanayin a cikin wannan sashi na taga halittar hyperlink a cikin filin "Adireshin" kawai kuna buƙatar bayyana adireshin kayan aikin yanar gizo da ake so kuma danna maballin "Ok".
Idan kana son tantance shafin alaƙa zuwa wani wuri a littafin yanzu, to sai ka je ɓangaren "Hanyar shiga wuri cikin daftarin aiki". Furtherarin gaba a tsakiyar ɓangaren taga kana buƙatar tantance takarda da adireshin tantanin halitta da kake son haɗa haɗin. Danna kan "Ok".
Idan kuna buƙatar ƙirƙirar sabon takaddar Excel kuma ku ɗaure ta amfani da hyperlink zuwa littafin aiki na yanzu, je zuwa sashin Haɗi zuwa sabon daftarin aiki. Na gaba, a tsakiyar yankin na taga, ba shi suna kuma nuna inda yake a diski. Saika danna "Ok".
Idan ana so, zaku iya danganta ɓangaren takardar tare da hyperlink, koda da e-mail. Don yin wannan, matsa zuwa sashin Haɗi zuwa Email kuma a fagen "Adireshin" saka e-mail. Danna kan "Ok".
- Bayan an shigar da hyperlink, rubutun cikin tantanin da yake ciki ya zama shuɗi ta hanyar tsohuwa. Wannan yana nufin cewa hyperlink yana aiki. Don zuwa abin da aka haɗa shi, kawai danna sau biyu akansa tare da maɓallin linzamin kwamfuta na hagu.
Bugu da kari, za a iya samar da hanyar sadarwa ta amfani da ginanniyar hanyar, wacce ke da suna wanda ke magana da kanta - "HYPERLINK".
Wannan magana tana da yanayin magana:
= HYPERLINK (adireshi; suna)
"Adireshin" - wata takaddama mai nuna adireshin gidan yanar gizo a Intanet ko fayil akan fayel ɗin da kake son kafa haɗin.
"Suna" - gardamar a cikin hanyar rubutu da za a nuna shi a cikin takardar takardar da ke ɗauke da hanyar haɗi. Wannan magana ba na tilas bane. Idan babu, adireshin abin da aikin ke nunawa za a nuna shi a cikin takardar takardar.
- Zaɓi wayar wanda za'a sanya hyperlink, sa'annan danna kan gunkin "Saka aikin".
- A Mayen aiki je zuwa sashen Tunani da Arrays. Yi alama sunan "HYPERLINK" sannan ka latsa "Ok".
- A cikin akwatin muhawara a cikin filin "Adireshin" saka adireshin zuwa gidan yanar gizon ko fayil akan rumbun kwamfutarka. A fagen "Suna" rubuta rubutun da za a nuna shi a cikin takardar takardar. Danna kan "Ok".
- Bayan haka za a ƙirƙiri hanyar sadarwa.
Darasi: Yadda ake yin ko cire hyperlinks a cikin Excel
Mun gano cewa a cikin teburin Excel akwai rukuni biyu na haɗin haɗin gwiwar: waɗanda aka yi amfani da su a cikin dabaru da waɗanda aka yi amfani da su don canzawa (hyperlinks). Kari akan haka, wadannan rukuni biyu sun kasu gida biyu. Tsarin aiwatar da halittar ya dogara da irin nau'in haɗin yanar gizon.
