Pehea e wāwahi ai i nā loulou ma Excel

He mea pono loa ke kamaʻilio ma Excel. Ma hope o nā mea a pau, hoʻohana pinepine nā mea hoʻohana i ka ʻike mai nā faila ʻē aʻe. Akā i kekahi mau kūlana, hiki iā lākou ke hana i ka ʻino ma mua o ka maikaʻi. Ma hope o nā mea a pau, no ka laʻana, inā hoʻouna ʻoe i kēia mau faila ma ka leka uila, ʻaʻole hana nā loulou. I kēia lā e kamaʻilio mākou i nā kikoʻī e pili ana i ka mea e hana ai e pale aku i kēlā pilikia.

He aha nā pilina ma Excel

Hoʻohana pinepine ʻia nā pilina ma Excel me nā hana e like me ʻO VPRe kiʻi i ka ʻike mai kahi puke hana ʻē aʻe. Hiki iā ia ke lawe i ke ʻano o kahi loulou kūikawā i loaʻa ka helu o ka cell wale nō, akā ʻo ka puke pū kahi i loaʻa ai ka ʻikepili. ʻO ka hopena, ʻano like kēia loulou: =VLOOKUP(A2;'[Kuai 2018.xlsx] Hōʻike'!$A:$F;4;0). A i ʻole, no kahi hōʻike maʻalahi, e hōʻike i ka helu wahi ma kēia ʻano: = '[Kuai 2018.xlsx] Hōʻike '!$A1. E noʻonoʻo kākou i kēlā me kēia ʻano loulou o kēia ʻano:

  1. [Kuai 2018.xlsx]. Aia kēia ʻāpana i kahi loulou i ka faila āu e makemake ai e kiʻi i ka ʻike. Ua kapa ʻia ʻo ia ke kumu.
  2. Photos. Ua hoʻohana mākou i kēia inoa, akā ʻaʻole kēia ka inoa e pono ai. Aia i loko o kēia poloka ka inoa o ka pepa āu e ʻike ai.
  3. $A:$F a me $A1 – ka helu wahi o ke kelepona a i ʻole ka laulā i loaʻa ka ʻikepili i loko o kēia palapala.

ʻOiaʻiʻo, ua kapa ʻia ke kaʻina hana o ka hana ʻana i kahi loulou i kahi palapala waho. Ma hope o kā mākou hoʻopaʻa inoa ʻana i ka helu o ka cell i loko o kahi faila ʻē aʻe, hoʻololi nā ʻike o ka pā "Data". ʻO ia hoʻi, lilo ka pihi "Change connections", me ke kōkua e hiki ai i ka mea hoʻohana ke hoʻoponopono i nā pilina e kū nei.

ʻO ke kumu o ka pilikia

Ma ke ʻano maʻamau, ʻaʻohe pilikia hou e hoʻohana i nā loulou. ʻOiai inā e kū mai kahi kūlana e hoʻololi ai nā pūnaewele, a laila e hoʻonui ʻia nā loulou āpau. Akā inā ʻoe i kapa hou i ka puke hana ponoʻī a neʻe paha i kahi helu ʻē aʻe, lilo ʻo Excel i mana ʻole. No laila, hoʻopuka ia i kēia memo.

Pehea e wāwahi ai i nā loulou ma Excel

Maʻaneʻi, loaʻa i ka mea hoʻohana ʻelua mau koho no ka hana ʻana i kēia kūlana. Hiki iā ia ke kaomi "E hoʻomau" a laila ʻaʻole e hoʻololi hou ʻia nā loli, a i ʻole hiki iā ia ke kaomi i ke pihi "Change Associations", kahi hiki iā ia ke hoʻonui iā lākou me ka lima. Ma hope o kā mākou kaomi ʻana i kēia pihi, e ʻike ʻia kahi puka aniani e hiki ai ke hoʻololi i nā loulou, e hōʻike ana i kahi o ka faila kūpono i kēia manawa a me ka mea i kapa ʻia.

Pehea e wāwahi ai i nā loulou ma Excel

Eia hou, hiki iā ʻoe ke hoʻoponopono i nā loulou ma o ka pihi pili i loaʻa ma ka pā "Data". Hiki i ka mea hoʻohana ke ʻike i ka haki ʻana o ka pilina e ka hewa #LINK, e ʻike ʻia inā ʻaʻole hiki iā Excel ke komo i ka ʻike i loaʻa ma kahi kikoʻī kikoʻī ma muli o ka hewa ʻole o ka helu.

Pehea e wehe ai i ka loulou ma Excel

ʻO kekahi o nā ala maʻalahi e hoʻoponopono ai i ke kūlana i hōʻike ʻia ma luna inā ʻaʻole hiki iā ʻoe ke hoʻololi i kahi o ka faila i hoʻopili ʻia iā ʻoe iho ʻo ka holoi ʻana i ka loulou ponoʻī. He mea maʻalahi kēia inā loaʻa i ka palapala hoʻokahi loulou. No ka hana ʻana i kēia, pono ʻoe e hana i kēia kaʻina hana:

  1. E wehe i ka papa kuhikuhi "Data".
  2. ʻIke mākou i ka ʻāpana "Connections", a ma laila - ke koho "Change connections".
  3. Ma hope o kēlā, kaomi ma luna o "Unlink".

Inā makemake ʻoe e leka i kēia puke i kekahi kanaka ʻē aʻe, makemake nui ʻia ʻoe e hana ma mua. Ma hope o nā mea a pau, ma hope o ka holoi ʻana i nā loulou, e hoʻoili ʻia nā waiwai āpau i loko o kahi palapala ʻē aʻe i loko o ka faila, hoʻohana ʻia i nā formula, a ma kahi o ka helu kelepona, e hoʻololi wale ʻia ka ʻike i loko o nā cell pili i nā waiwai. .

Pehea e wehe ai i nā puke a pau

Akā inā nui ka helu o nā loulou, hiki i ka holoi lima ʻana iā lākou ke lōʻihi. No ka hoʻoponopono ʻana i kēia pilikia i ka hele hoʻokahi, hiki iā ʻoe ke hoʻohana i kahi macro kūikawā. Aia ia ma ka VBA-Excel addon. Pono ʻoe e hoʻāla a hele i ka pā o ka inoa like. E loaʻa kahi ʻāpana "Links", kahi e pono ai mākou e kaomi i ka pihi "Break all links".

Pehea e wāwahi ai i nā loulou ma Excel

Code VBA

Inā ʻaʻole hiki ke hoʻāla i kēia add-on, hiki iā ʻoe ke hana i kahi macro iā ʻoe iho. No ka hana ʻana i kēia, wehe i ka mea hoʻoponopono Visual Basic ma ke kaomi ʻana i nā kī Alt + F11, a kākau i nā laina aʻe ma ke kahua komo code.

Sub UnlinkWorkBooks()

    Poʻo WbLinks

    Dim and Like Long

    E koho i ka Case MsgBox("E wehe ʻia nā ʻōlelo kuhikuhi a pau i nā puke ʻē aʻe mai kēia faila, a e hoʻololi ʻia nā ʻōkuhi e pili ana i nā puke ʻē aʻe me nā waiwai." & vbCrLf & "Are you sure you want to continue?", 36, "Unlink?" )

    Hihia 7′ No

        Kuhi Sub

    Hoʻopau koho

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Inā ʻaʻole ʻo IsEmpty(WbLinks) A laila

        No i = 1 i UBound(WbLinks)

            ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks

        Aʻe

    e ae

        MsgBox "ʻAʻohe loulou i nā puke ʻē aʻe ma kēia faila.", 64, "Nā loulou i nā puke ʻē aʻe"

    Hoʻopau Inā

Ka pau 'iiaciiie

Pehea e hoʻokaʻawale ai i nā pilina ma ka laulā i koho ʻia

Mai kēlā manawa kēia manawa, nui loa ka helu o nā loulou, a makaʻu ka mea hoʻohana ma hope o ka holoi ʻana i kekahi o lākou, ʻaʻole hiki ke hoʻihoʻi i nā mea āpau inā ʻoi aku kekahi. Akā, he pilikia kēia i maʻalahi ke pale aku. No ka hana ʻana i kēia, pono ʻoe e koho i ka laulā kahi e holoi ai i nā loulou, a laila holoi iā lākou. No ka hana ʻana i kēia, pono ʻoe e hana i kēia kaʻina hana:

  1. E koho i ka ʻikepili e pono e hoʻololi.
  2. E hoʻouka i ka VBA-Excel hoʻohui, a laila e hele i ka pā kūpono.
  3. A laila, ʻike mākou i ka papa kuhikuhi "Links" a kaomi i ka pihi "Break links i nā pae i koho ʻia".

Pehea e wāwahi ai i nā loulou ma Excel

Ma hope o kēlā, e hoʻopau ʻia nā loulou a pau i ka pūʻulu i koho ʻia.

He aha kāu e hana ai inā ʻaʻole i haki nā kaula

He maikaʻi nā mea a pau i luna, akā i ka hoʻomaʻamaʻa aia mau kekahi mau nuances. Eia kekahi laʻana, aia paha kahi kūlana kahi i haki ʻole ai nā pili. I kēia hihia, ʻike ʻia kahi pahu kamaʻilio e hōʻike ana ʻaʻole hiki ke hoʻololi aunoa i nā loulou. He aha ka hana ma kēia kūlana?

  1. ʻO ka mea mua, pono ʻoe e nānā inā aia kekahi ʻike i loko o nā pae i kapa ʻia. No ka hana ʻana i kēia, e kaomi i ka hui kī Ctrl + F3 a i ʻole e wehe i ka pā "Formulas" - "Name Manager". Inā piha ka inoa faila, pono ʻoe e hoʻoponopono a wehe aʻe paha. Ma mua o ka holoi ʻana i nā pae i kapa ʻia, pono ʻoe e kope i ka faila i kekahi wahi ʻē aʻe i hiki iā ʻoe ke hoʻi i ka mana kumu inā i hana hewa ʻia.
  2. Inā ʻaʻole hiki iā ʻoe ke hoʻoponopono i ka pilikia ma ka wehe ʻana i nā inoa, hiki iā ʻoe ke nānā i ka hoʻopili kūlana. Hiki ke kuhikuhi ʻia nā kelepona ma kahi papa ʻē aʻe ma nā lula hoʻopono kūlana. No ka hana ʻana i kēia, e ʻimi i ka mea pili ma ka pā "Home", a laila kaomi i ka pihi "File Management". Pehea e wāwahi ai i nā loulou ma Excel

    ʻO ka mea maʻamau, ʻaʻole hāʻawi ʻo Excel iā ʻoe i ka hiki ke hāʻawi i ka helu wahi o nā puke hana ʻē aʻe i ka hoʻohālikelike kūlana, akā e hana ʻoe inā ʻoe e nānā i kahi ʻano inoa me kahi kuhikuhi i kahi faila ʻē aʻe. ʻO ka maʻamau, ʻoiai ma hope o ka wehe ʻia ʻana o ka loulou, e mau ana ka loulou. ʻAʻohe pilikia i ka wehe ʻana i ia loulou, no ka mea, ʻaʻole hana ka loulou. No laila, ʻaʻohe mea ʻino e hiki mai ke wehe ʻoe.

Hiki iā ʻoe ke hoʻohana i ka hana "Data Check" e ʻike ai inā he mau loulou pono ʻole. Noho mau nā loulou inā hoʻohana ʻia ke ʻano "List" o ka hōʻoia ʻikepili. Akā, he aha ka mea e hana ai inā he nui nā pūnaewele? He mea pono anei e nānā i kēlā me kēia o lākou ma ke kaʻina? ʻAʻole naʻe. Ma hope o nā mea a pau, e lōʻihi loa ka manawa. No laila, pono ʻoe e hoʻohana i kahi code kūikawā e mālama nui ai.

Koho maopopo

'————————————————————————————

' Mea kākau : The_Prist(Shcherbakov Dmitry)

' Hoʻomohala ʻoihana o nā noi no MS Office o kekahi paʻakikī

' Ke alakaʻi nei i nā hoʻomaʻamaʻa ma MS Excel

' https://www.excel-vba.ru

' [palapala leka uila]

'WebMoney—R298726502453; Yandex.Money — 41001332272872

' Ke kumu:

'————————————————————————————

Sub FindErrLink()

    'pono mākou e nana i ka Ikepili -Change loulou loulou i ka waihona kumu

    'a hoʻokomo i nā huaʻōlelo ma ʻaneʻi ma ka liʻiliʻi liʻiliʻi (kahi o ka inoa faila)

    'Asterisk ho'ololi wale i kekahi helu o nā huapalapala no laila 'a'ole 'oe e hopohopo e pili ana i ka inoa pololei

    Const sToFndLink$ = “*kūʻai 2018*”

    Dim rr E like me ka Laulā, rc E like me ka Lāhui, rres E like me ka Lāhui, s$

    'e wehewehe i nā pūnaewele āpau me ka hōʻoia ʻikepili

    Ma ka Haʻa Haʻawina Ma hope

    Hoʻonohonoho rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Inā ʻaʻohe mea ʻo rr a laila

        MsgBox "ʻAʻohe pūnaewele me ka hōʻoiaʻikepili ma ka pepa hana", vbInformation, "www.excel-vba.ru"

        Kuhi Sub

    Hoʻopau Inā

    E hele i ka 0

    'E nānā i kēlā me kēia pūnaewele no nā loulou

    No kela ame keia rc I rr

        'o ka hihia wale nō, ke hoʻokuʻu nei mākou i nā hewa - hiki nō kēia

        'akā ʻaʻole pono kā mākou pilina me ka ʻole o lākou a e ʻike maoli ʻia lākou

        s = «»

        Ma ka Haʻa Haʻawina Ma hope

        s = rc.Validation.Formula1

        E hele i ka 0

        'loa'a - 'ohi mākou i nā mea a pau ma kahi ka'awale

        Inā LCase(s) Like sToFndLink A laila

            Inā ʻaʻohe mea a rres a laila

                E hoʻonoho i nā rres = rc

            e ae

                E hoʻonohonoho i ka rres = Union(rc, rres)

            Hoʻopau Inā

        Hoʻopau Inā

    Aʻe

    'inā loa'a kahi pilina, koho i nā pūnaewele a pau me ia mau hō'ike 'ikepili

    Inā ʻAʻole rres ʻAʻohe mea a laila

        rres.Koho

' rres.Interior.Color = vbRed 'inā makemake ʻoe e hōʻailona me ke kala

    Hoʻopau Inā

Ka pau 'iiaciiie

Pono e hana i kahi module maʻamau i ka hoʻoponopono macro, a laila e hoʻokomo i kēia kikokikona ma laila. Ma hope o kēlā, e kāhea i ka puka aniani me ka hoʻohana ʻana i ka hui kī Alt + F8, a laila koho i kā mākou macro a kaomi i ka pihi "Run". Aia kekahi mau mea e hoʻomanaʻo i ka hoʻohana ʻana i kēia code:

  1. Ma mua o kou ʻimi ʻana i kahi loulou pili ʻole, pono ʻoe e hoʻoholo i ke ʻano o ka loulou i hana ʻia ai. No ka hana ʻana i kēia, e hele i ka papa kuhikuhi "Data" a loaʻa i ka mea "Change Links" ma laila. Ma hope o kēlā, pono ʻoe e nānā i ka inoa faila, a kuhikuhi iā ia ma nā ʻōlelo. No ka laʻana, e like me kēia: Const sToFndLink$ = “*kūʻai 2018*”
  2. Hiki ke kākau i ka inoa ʻaʻole i piha, akā e hoʻololi wale i nā mea pono ʻole me kahi asterisk. A ma nā huaʻōlelo, e kākau i ka inoa faila ma nā leka liʻiliʻi. I kēia hihia, e ʻike ʻo Excel i nā faila āpau i loaʻa i kēlā string ma ka hopena.
  3. Hiki i kēia code ke nānā i nā loulou i ka pepa e hana nei i kēia manawa.
  4. Me kēia macro, hiki iā ʻoe ke koho wale i nā cell i loaʻa iā ia. Pono ʻoe e holoi i nā mea a pau me ka lima. He mea hoʻohui kēia, no ka mea hiki iā ʻoe ke nānā hou i nā mea āpau.
  5. Hiki iā ʻoe ke hana i nā cell i hōʻike ʻia i kahi kala kūikawā. No ka hana ʻana i kēia, wehe i ka apostrophe ma mua o kēia laina. rres.Interior.Color = vbRed

ʻO ka maʻamau, ma hope o kou hoʻopau ʻana i nā ʻanuʻu i wehewehe ʻia i nā ʻōlelo aʻoaʻo ma luna nei, ʻaʻohe mea pili pono ʻole. Akā inā aia kekahi o lākou i loko o ka palapala a ʻaʻole hiki iā ʻoe ke wehe iā lākou no kekahi kumu a i ʻole kekahi (ʻo kahi hiʻohiʻona maʻamau ka palekana o ka ʻikepili i loko o kahi pepa), a laila hiki iā ʻoe ke hoʻohana i kahi ʻano hana ʻē aʻe. Pono kēia ʻōlelo aʻo no nā mana 2007 a ʻoi aʻe.

  1. Hana mākou i kope kope o ka palapala.
  2. E wehe i kēia palapala me ka hoʻohana ʻana i ka waihona. Hiki iā ʻoe ke hoʻohana i kekahi mea e kākoʻo ana i ka format ZIP, akā e hana pū ʻo WinRar, a me ka mea i kūkulu ʻia i loko o Windows.
  3. I ka waihona i hōʻike ʻia, pono ʻoe e ʻimi i ka waihona xl, a laila wehe i nā externalLinks.
  4. Aia kēia waihona i nā loulou waho a pau, pili kēlā me kēia me kahi faila o ka puka externalLink1.xml. He helu wale lākou a pau, a no laila ʻaʻole hiki i ka mea hoʻohana ke hoʻomaopopo i ke ʻano o kēia pilina. No ka hoʻomaopopo ʻana i ke ʻano o ka pilina, pono ʻoe e wehe i ka waihona _rels, a nānā iā ia ma laila.
  5. Ma hope o kēlā, wehe mākou i nā loulou āpau a i ʻole nā ​​loulou kikoʻī, e pili ana i ka mea a mākou e aʻo ai ma ka faila externalLinkX.xml.rels.
  6. Ma hope o kēlā, wehe mākou i kā mākou faila me Excel. E loaʻa ka ʻike e pili ana i kahi hewa e like me "Error in part of content in the Book." Hāʻawi mākou i ka ʻae. Ma hope o kēlā, e hōʻike ʻia kahi kamaʻilio hou. Hoʻopili mākou.

Ma hope o kēlā, pono e wehe ʻia nā loulou a pau.

Waiho i ka Reply