Contents
Sadarwa abu ne mai matukar amfani a cikin Excel. Bayan haka, sau da yawa masu amfani dole ne su yi amfani da bayanai daga wasu fayiloli. Amma a wasu yanayi, suna iya yin illa fiye da kyau. Bayan haka, misali, idan kun aika waɗannan fayilolin ta wasiƙa, hanyoyin haɗin yanar gizon ba sa aiki. A yau za mu yi magana dalla-dalla game da abin da za mu yi don kauce wa irin wannan matsala.
Menene dangantaka a cikin Excel
Ana amfani da alaƙa a cikin Excel sau da yawa tare da ayyuka kamar VPRdon samun bayanai daga wani littafin aiki. Yana iya ɗaukar nau'i na hanyar haɗi na musamman wanda ya ƙunshi adireshin ba kawai tantanin halitta ba, amma har da littafin da bayanan ke ciki. Sakamakon haka, irin wannan hanyar haɗin gwiwa tana kama da wani abu kamar haka: = VLOOKUP(A2;'[Sales 2018.xlsx] Rahoton'!$A:$F;4;0). Ko, don wakilci mai sauƙi, wakiltar adireshin a cikin tsari mai zuwa: ='[Sales 2018.xlsx] Rahoton'!$A1. Bari mu bincika kowane ɗayan abubuwan haɗin wannan nau'in:
- [Sales 2018.xlsx]. Wannan guntun ya ƙunshi hanyar haɗi zuwa fayil ɗin da kake son samun bayanai daga gare shi. Ana kuma kiransa tushen.
- Photos. Mun yi amfani da suna mai zuwa, amma wannan ba sunan da ya kamata ya zama ba. Wannan toshe ya ƙunshi sunan takardar da kuke buƙatar nemo bayanai a cikinta.
- $A:$F da $A1- adireshin tantanin halitta ko kewayon da ke ɗauke da bayanan da ke cikin wannan takarda.
A zahiri, tsarin ƙirƙirar hanyar haɗi zuwa takaddar waje ana kiransa haɗawa. Bayan mun yi rajistar adireshin tantanin halitta da ke cikin wani fayil, abubuwan da ke cikin shafin "Data" suna canzawa. Wato, maɓallin "Change Connections" yana aiki, tare da taimakon wanda mai amfani zai iya gyara haɗin da ke ciki.
Asalin matsalar
A matsayinka na mai mulki, babu ƙarin matsalolin da ke tasowa don amfani da hanyoyin haɗin gwiwa. Ko da wani yanayi ya taso inda sel ke canzawa, to duk hanyoyin haɗin suna sabunta ta atomatik. Amma idan kun riga kun sake sunan littafin aikin da kansa ko matsar da shi zuwa wani adireshin daban, Excel ya zama mara ƙarfi. Saboda haka, yana samar da saƙo mai zuwa.
Anan, mai amfani yana da zaɓuɓɓuka biyu masu yiwuwa don yadda zai yi aiki a wannan yanayin. Yana iya danna "Ci gaba" sannan ba za a sabunta canje-canjen ba, ko kuma yana iya danna maɓallin "Change Associations", wanda zai iya sabunta su da hannu. Bayan mun danna wannan maɓallin, ƙarin taga zai bayyana inda za'a iya canza hanyar haɗi, yana nuna inda ainihin fayil ɗin yake a yanzu da abin da ake kira.
Bugu da kari, za ka iya shirya hanyoyin sadarwa ta hanyar madaidaicin maballin dake kan shafin "Data". Hakanan mai amfani zai iya gano cewa haɗin yanar gizon ya lalace ta hanyar kuskuren #LINK, wanda ke bayyana lokacin da Excel ba zai iya samun bayanan da ke cikin takamaiman adireshin ba saboda gaskiyar adireshin kansa ba shi da inganci.
Yadda ake cire haɗin yanar gizo a cikin Excel
Ɗayan hanya mafi sauƙi don magance halin da aka kwatanta a sama idan ba za ku iya sabunta wurin da aka haɗa fayil ɗin da kanku ba shine share hanyar haɗin kanta. Wannan yana da sauƙi musamman idan takardar ta ƙunshi hanyar haɗi ɗaya kawai. Don yin wannan, dole ne ku aiwatar da matakan matakai masu zuwa:
- Bude menu na "Data".
- Mun sami sashin "Haɗin kai", kuma a can - zaɓi "Canja haɗi".
- Bayan haka, danna kan "Unlink".
Idan kuna da niyyar aikawa da wannan littafin zuwa wani mutum, ana ba da shawarar ku yi haka a gaba. Bayan haka, bayan share hanyoyin haɗin yanar gizon, duk ƙimar da ke cikin wani takaddar za a loda su ta atomatik cikin fayil ɗin, ana amfani da su a cikin ƙididdiga, kuma maimakon adireshin tantanin halitta, bayanan da ke cikin sel masu dacewa za a canza su kawai zuwa ƙima. .
Yadda za a cire haɗin duk littattafai
Amma idan adadin hanyoyin haɗin gwiwar ya yi girma, share su da hannu na iya ɗaukar lokaci mai tsawo. Don magance wannan matsala ta tafi ɗaya, zaka iya amfani da macro na musamman. Yana cikin VBA-Excel addon. Kuna buƙatar kunna shi kuma je zuwa shafin sunan iri ɗaya. Za a sami sashin "Links", wanda a ciki muna buƙatar danna maɓallin "Break all links".
Lambar VBA
Idan ba zai yiwu a kunna wannan ƙarawa ba, zaku iya ƙirƙirar macro da kanku. Don yin wannan, buɗe editan Visual Basic ta latsa maɓallin Alt + F11, sannan rubuta layin masu zuwa a cikin filin shigar da lambar.
Sub UnlinkWorkBooks()
Dim WbLinks
Dim da kuma tsawon lokaci
Zaɓi Case MsgBox("Dukkan abubuwan da aka ambata akan wasu littattafai za'a cire su daga wannan fayil ɗin, kuma za'a maye gurbin daftarin da ke magana akan wasu littattafai da ƙima." & vbCrLf & "Shin kun tabbata kuna son ci gaba?", 36, "Unlink?" )
Kaso 7′ No
Mafita Sub
Ƙare Zaɓi
WbLinks = ActiveWorkbook.LinkSources(Nau'i:=xlLinkTypeExcelLinks)
Idan Ba Ba komai bane (WbLinks) to
Don i = 1 zuwa UBound (WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Nau'in:=xlLinkTypeExcelLinks
Next
wani
MsgBox "Babu hanyar haɗi zuwa wasu littattafai a cikin wannan fayil ɗin.", 64, "Haɗin zuwa wasu littattafai"
Ƙare Idan
karshen Sub
Yadda ake karya alaƙa kawai a cikin kewayon da aka zaɓa
Daga lokaci zuwa lokaci, yawan hanyoyin haɗin yanar gizon yana da girma sosai, kuma mai amfani yana jin tsoron cewa bayan share ɗaya daga cikinsu, ba zai yiwu a dawo da komai ba idan wasu sun kasance masu banƙyama. Amma wannan matsala ce mai sauƙin kaucewa. Don yin wannan, kuna buƙatar zaɓar kewayon inda zaku goge hanyoyin haɗin gwiwa, sannan ku goge su. Don yin wannan, dole ne ku aiwatar da jerin ayyuka masu zuwa:
- Zaɓi saitin bayanan da ke buƙatar gyara.
- Shigar da VBA-Excel add-on, sannan je zuwa shafin da ya dace.
- Na gaba, za mu sami menu na "Links" kuma danna maballin "Break links a cikin zaɓaɓɓun jeri".
Bayan haka, za a share duk hanyoyin haɗin yanar gizon da aka zaɓa.
Abin da za a yi idan ba a karya alaƙar ba
Duk abubuwan da ke sama suna da kyau, amma a aikace koyaushe akwai wasu nuances. Misali, za a iya samun yanayin da ba a karya alaka. A wannan yanayin, har yanzu akwatin maganganu yana bayyana yana bayyana cewa ba zai yiwu a sabunta hanyoyin ta atomatik ba. Me za a yi a wannan yanayin?
- Da farko, kuna buƙatar bincika ko kowane bayani yana ƙunshe a cikin jeri mai suna. Don yin wannan, danna maɓallin haɗin Ctrl + F3 ko buɗe shafin "Formulas" - "Mai sarrafa suna". Idan sunan fayil ɗin ya cika, to kawai kuna buƙatar gyara shi ko cire shi gaba ɗaya. Kafin share jeri mai suna, kuna buƙatar kwafin fayil ɗin zuwa wani wuri daban don ku iya komawa zuwa ainihin sigar idan an ɗauki matakan da ba daidai ba.
- Idan ba za ku iya magance matsalar ta hanyar cire sunaye ba, kuna iya duba tsarin tsari. Kwayoyin da ke cikin wani tebur za a iya yin la'akari da su cikin ƙa'idodin tsara yanayin. Don yin wannan, nemo abin da ya dace akan shafin "Gida", sannan danna maɓallin "Gudanar da Fayil".
A al'ada, Excel ba ya ba ku ikon ba da adireshin sauran littattafan aiki a cikin tsari na yanayi, amma kuna yin idan kun koma kewayon mai suna tare da ambaton wani fayil. Yawancin lokaci, ko da bayan an cire hanyar haɗin yanar gizon, hanyar haɗin yanar gizon ta kasance. Babu matsala wajen cire irin wannan hanyar haɗin yanar gizon, saboda haɗin yanar gizon ba ya aiki. Saboda haka, babu wani mummunan abu da zai faru idan kun cire shi.
Hakanan zaka iya amfani da aikin "Check Data" don gano ko akwai hanyoyin haɗin da ba dole ba. Hanyoyin haɗi yawanci suna kasancewa idan an yi amfani da nau'in ingantattun bayanai na "Jerin". Amma menene za a yi idan akwai sel da yawa? Shin da gaske ya zama dole a duba kowannensu a jere? Tabbas ba haka bane. Bayan haka, zai ɗauki lokaci mai tsawo sosai. Don haka, kuna buƙatar amfani da lambar musamman don adana ta sosai.
Zabin Bayyananne
'—————————————————————————————
Marubuci: The_Prist (Shcherbakov Dmitry)
' Haɓaka ƙwararrun aikace-aikace don MS Office na kowane rikitarwa
' Gudanar da horo akan MS Excel
' https://www.excel-vba.ru
' [email protected]
'WebMoney-R298726502453; Yandex.Money - 41001332272872
'Manufa:
'—————————————————————————————
Sub FindErrLink()
'muna buƙatar duba a cikin Data -Change links link to the source file
'kuma sanya keywords anan cikin ƙananan haruffa (ɓangaren sunan fayil)
'Asterisk kawai yana maye gurbin kowane adadin haruffa don kada ku damu da ainihin sunan
Const sToFndLink$ = "* tallace-tallace 2018*"
Dim rr Matsayin Range, rc Matsayin Range, rres Kamar Range, s$
'bayyana duk sel tare da ingantaccen bayanai
A kan Kuskuren Zaima A gaba
Saita rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Idan rr Ba Komai ba To
MsgBox "Babu wasu sel masu ingantattun bayanai akan takardar aiki", vbInformation, "www.excel-vba.ru"
Mafita Sub
Ƙare Idan
Kan Kuskure GoTo 0
'duba kowane tantanin halitta don hanyoyin haɗin gwiwa
Ga Kowane rc A cikin rr
' kawai idan, mun tsallake kurakurai - wannan kuma na iya faruwa
'amma dole ne alakar mu ta kasance ba tare da su ba kuma tabbas za a same su
s = ""
A kan Kuskuren Zaima A gaba
s = rc. Tabbatarwa.Formula1
Kan Kuskure GoTo 0
'samu - muna tattara duk abin da ke cikin kewayon daban
Idan LCase(s) Kamar sToFndLink Sannan
Idan rres Ba Komai ba To
Saita rres = rc
wani
Saita rres = Ƙungiyar (rc, rres)
Ƙare Idan
Ƙare Idan
Next
'idan akwai haɗi, zaɓi duk sel masu irin wannan binciken bayanan
Idan Ba rres Ba Komai To
res.Zaɓi
' rres.Interior.Launi = vbRed 'idan kuna son haskaka da launi
Ƙare Idan
karshen Sub
Wajibi ne a yi daidaitaccen module a cikin editan macro, sannan saka wannan rubutu a can. Bayan haka, kira macro taga ta amfani da maɓallin haɗin Alt + F8, sannan zaɓi macro ɗin mu kuma danna maɓallin "Run". Akwai ƴan abubuwa da ya kamata ku tuna yayin amfani da wannan lambar:
- Kafin ka nemo hanyar haɗin da ba ta dace ba, dole ne ka fara tantance yadda hanyar da aka ƙirƙira ta take. Don yin wannan, je zuwa menu na "Data" kuma sami abu "Change Links" a can. Bayan haka, kuna buƙatar duba sunan fayil ɗin, kuma saka shi a cikin ƙididdiga. Misali, kamar haka: Const sToFndLink$ = "* tallace-tallace 2018*"
- Yana yiwuwa a rubuta sunan ba cikakke ba, amma kawai maye gurbin haruffa marasa mahimmanci tare da alamar alama. Kuma a cikin ƙididdiga, rubuta sunan fayil a cikin ƙananan haruffa. A wannan yanayin, Excel zai sami duk fayilolin da ke ɗauke da irin wannan kirtani a ƙarshen.
- Wannan lambar tana da ikon bincika hanyoyin haɗin kai a cikin takardar da ke aiki a halin yanzu.
- Tare da wannan macro, zaku iya zaɓar sel ɗin da ya samo. Dole ne ku share komai da hannu. Wannan ƙari ne, saboda zaku iya sake duba komai sau biyu.
- Hakanan zaka iya yin sel waɗanda aka haskaka a cikin launi na musamman. Don yin wannan, cire ridda kafin wannan layin. res.Interior.Launi = vbRed
Yawancin lokaci, bayan kun kammala matakan da aka kwatanta a cikin umarnin da ke sama, bai kamata a sami ƙarin haɗin da ba dole ba. Amma idan akwai wasu daga cikinsu a cikin takaddun kuma ba za ku iya cire su ba saboda dalili ɗaya ko wani (misali na yau da kullun shine tsaro na bayanai a cikin takarda), to zaku iya amfani da jerin ayyuka daban-daban. Wannan umarnin yana aiki ne kawai don nau'ikan 2007 da sama.
- Mun ƙirƙiri kwafin daftarin aiki.
- Bude wannan daftarin aiki ta amfani da ma'ajiyar bayanai. Kuna iya amfani da duk wanda ke goyan bayan tsarin ZIP, amma WinRar kuma zai yi aiki, da kuma wanda aka gina a cikin Windows.
- A cikin ma'ajin da ya bayyana, kuna buƙatar nemo babban fayil ɗin xl, sannan ku buɗe externalLinks.
- Wannan babban fayil ɗin ya ƙunshi duk hanyoyin haɗin waje, kowannensu ya yi daidai da fayil ɗin hanyar wajeLink1.xml. Dukkanin su ana ƙididdige su ne kawai, sabili da haka mai amfani ba shi da damar fahimtar irin haɗin da wannan yake. Don fahimtar wane irin haɗin gwiwa, kuna buƙatar buɗe babban fayil ɗin _rels, sannan ku duba wurin.
- Bayan haka, muna cire duk ko takamaiman hanyoyin haɗin gwiwa, dangane da abin da muka koya a cikin fayil na wajeLinkX.xml.rels.
- Bayan haka, muna buɗe fayil ɗin mu ta amfani da Excel. Za a sami bayani game da kuskure kamar "Kuskure a ɓangaren abubuwan da ke cikin Littafi." Muna ba da izini. Bayan haka, wani maganganu zai bayyana. Mu rufe shi.
Bayan haka, ya kamata a cire duk hanyoyin haɗin gwiwa.