Maitiro ekutyora zvinongedzo muExcel

Kukurukurirana chinhu chinobatsira zvikuru muExcel. Mushure mezvose, kazhinji vashandisi vanofanirwa kushandisa ruzivo kubva kune mamwe mafaera. Asi mune mamwe mamiriro ezvinhu, vanogona kukuvadza kupfuura kunaka. Mushure mezvose, semuenzaniso, kana iwe ukatumira mafaira aya netsamba, zvisungo hazvisi kushanda. Nhasi tichataura zvakadzama pamusoro pezvaunofanira kuita kuti udzivise dambudziko rakadaro.

Chii chinonzi hukama muExcel

Hukama muExcel inowanzoshandiswa pamwe chete nemabasa akadai VPRkuti uwane ruzivo kubva kune rimwe bhuku rekushanda. Inogona kutora chimiro chekubatanidza kwakakosha iyo ine kero kwete chete sero, asiwo bhuku iro data riripo. Nekuda kweizvozvo, chinongedzo chakadaro chinotaridzika seizvi: =VLOOKUP(A2;'[Sales 2018.xlsx]Report'!$A:$F;4;0). Kana kuti, kuitira kumiririra kuri nyore, miririra kero iri mufomu rinotevera: ='[Sales 2018.xlsx]Report'!$A1. Ngationgororei chimwe nechimwe chezvibatanidza zvemhando iyi:

1. [Kutengeswa 2018.xlsx]. Ichi chidimbu chine chinongedzo kune faira raunoda kuwana ruzivo kubva kwairi. Inonziwo tsime.
2. Photos. Takashandisa zita rinotevera, asi iri harisi zita rinofanira kuva. Iyi block ine zita repepa raunoda kutsvaga ruzivo.
3. $A:$F uye $A1 - kero yesero kana renji ine data iri mugwaro iri.

Chaizvoizvo, maitiro ekugadzira chinongedzo kugwaro rekunze anonzi linking. Mushure mekunge tanyoresa kero yesero iri mune imwe faira, zviri mukati me "Data" tab inoshanduka. Sezvineiwo, bhatani re "Shandura yekubatanidza" rinova rinoshanda, nerubatsiro rwekuti mushandisi anogona kugadzirisa zviripo.

Hunhu hwedambudziko

Sezvo mutemo, hapana mamwe matambudziko anomuka kuitira kushandisa zvinongedzo. Kunyangwe kana mamiriro ezvinhu akamuka umo maseru anochinja, ipapo zvese zvinongedzo zvinogadziridzwa otomatiki. Asi kana iwe uchinge watopa zita rebhuku rebasa pacharo kana kuriendesa kune imwe kero, Excel inoshaya simba. Nokudaro, rinobudisa shoko rinotevera.

Pano, mushandisi ane maviri anogoneka sarudzo dzekuita mumamiriro ezvinhu aya. Anogona kudzvanya "Rambai" uye ipapo shanduko hadzizogadziriswe, kana anogona kudzvanya bhatani re "Change Associations", iro raanogona kuzvigadzirisa nemaoko. Mushure mekunge tadzvanya bhatani iri, imwe hwindo ichaonekwa umo zvichave zvichigoneka kushandura zvinongedzo, zvichiratidza kuti faira rakakodzera riri kupi panguva ino uye kuti rinodaidzwa sei.

Mukuwedzera, iwe unogona kugadzirisa zvinongedzo kuburikidza nebhatani rinoenderana riri pa "Data" tab. Mushandisi anogona zvakare kuona kuti kubatana kwakaputswa ne #LINK kukanganisa, iyo inoonekwa kana Excel isingakwanisi kuwana ruzivo rwuri pane imwe kero nekuda kwekuti iyo kero pachayo haina basa.

Nzira yekubvisa sei muExcel

Imwe yedzakareruka nzira dzekugadzirisa mamiriro akatsanangurwa pamusoro kana iwe usingakwanise kugadzirisa nzvimbo yefaira rakabatana iwe pachako kudzima chinongedzo pachacho. Izvi zvakanyanya nyore kuita kana gwaro riine chete chinongedzo. Kuti uite izvi, unofanirwa kuita nhanho dzinotevera dzematanho:

1. Vhura iyo "Data" menyu.
2. Tinowana chikamu che "Connections", uye ipapo - sarudzo "Shandura hukama".
3. Mushure meizvozvo, tinya pakanzi "Unlink".

Kana uchida kutumira bhuku rino kune mumwe munhu, zvinokurudzirwa zvikuru kuti uite kudaro pachine nguva. Mushure mezvose, mushure mekudzima zvinongedzo, hunhu hwese huri mune rimwe gwaro huchaiswa otomatiki mufaira, hunoshandiswa mumafomula, uye pachinzvimbo chekero yesero, ruzivo rwuri mumasero anoenderana rwunongoshandurwa kuve hunhu. .

Nzira yekubvisa sei kubatanidza mabhuku ese

Asi kana nhamba yezvibatanidza ikakura zvakanyanya, kuzvibvisa nemaoko zvinogona kutora nguva yakareba. Kugadzirisa dambudziko iri mune imwe chete, unogona kushandisa yakakosha macro. Iyo iri muVBA-Excel addon. Iwe unofanirwa kuimisa uye enda kune iyo tebhu yezita rimwe chete. Pachava nechikamu che "Links", umo tinoda kudzvanya bhatani re "Break all links".

VBA kodhi

Kana zvisingaite kumisa iyi yekuwedzera, unogona kugadzira macro iwe pachako. Kuti uite izvi, vhura Visual Basic editor nekudzvanya makiyi eAlt + F11, uye nyora mitsara inotevera mundima yekupinda kodhi.

Sub UnlinkWorkBooks()

    Dim WbLinks

    Dim uye Nekureba

    Sarudza Nyaya MsgBox("Manongedzo ese emamwe mabhuku achabviswa mufaira iri, uye mafomula anoreva mamwe mabhuku achatsiviwa nehunhu." & vbCrLf & "Une chokwadi chekuti unoda kuenderera mberi?", 36, "Unlink?" )

    Nyaya 7′ Kwete

        Buda Sub

    End Sarudza

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Kana isiri IsEmpty (WbLinks) Zvadaro

        For i = 1 Ku UBound (WbLinks)

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

        Next

    mumwe

        MsgBox "Hapana chinongedzo kune mamwe mabhuku mufaira iri.", 64, "Zvinongedzo kune mamwe mabhuku"

    Endai Kana

End Sub

Nzira yekuputsa zvisungo chete munharaunda yakasarudzwa

Nguva nenguva, nhamba yezvisungo yakakura kwazvo, uye mushandisi anotya kuti mushure mekudzima chimwe chazvo, hazvizoitike kudzorera zvese kumashure kana zvimwe zvakanyanyisa. Asi iri idambudziko riri nyore kudzivisa. Kuti uite izvi, unofanirwa kusarudza iyo nhanho yekudzima zvinongedzo, wobva wadzima. Kuti uite izvi, iwe unofanirwa kuita zvinotevera kutevedzana kwezviito:

1. Sarudza dataset inoda kuchinjwa.
2. Isa iyo VBA-Excel yekuwedzera, uye wozoenda kune yakakodzera tab.
3. Tevere, tinowana iyo "Links" menyu uye tinya bhatani re "Break links muzvikamu zvakasarudzwa".

Mushure meizvozvo, zvese zvinongedzo museti yakasarudzwa yemasero zvinodzimwa.

Chii chekuita kana zvisungo zvisina kuputswa

Zvose zviri pamusoro zvinonzwika zvakanaka, asi mukuita pane nguva dzose mamwe ma nuances. Somuenzaniso, pangava nemamiriro ezvinhu apo zvisungo hazvina kuputswa. Muchiitiko ichi, bhokisi rebhokisi richiri kuoneka richitaura kuti hazvigoneke kugadzirisa otomatiki ma link. Chii chekuita mumamiriro ezvinhu aya?

1. Chekutanga, iwe unofanirwa kutarisa kana chero ruzivo rwurimo mune mazita mazita. Kuti uite izvi, tinya kiyi musanganiswa Ctrl + F3 kana kuvhura iyo "Formulas" tebhu - "Zita Maneja". Kana zita refaira rakazara, saka unongoda kurigadzirisa kana kuribvisa zvachose. Usati wadzima mazita ane mazita, unofanirwa kukopa faira kune imwe nzvimbo kuitira kuti udzoke kune yekutanga shanduro kana matanho asina kunaka atorwa.
2. Kana iwe usingakwanise kugadzirisa dambudziko nekubvisa mazita, unogona kutarisa mafomati ane zvirevo. Masero mune imwe tafura anogona kutariswa mumitemo yekufomatidza. Kuti uite izvi, tsvaga chinhu chinoenderana pane "Kumba" tab, uye wobva wadzvanya bhatani re "File Management".

   Kazhinji, Excel haipe iwe kugona kupa kero yemamwe mabhuku ebasa mune inomisikidzwa fomati, asi iwe unoita kana iwe ukatarisa kune yakatumidzwa renji ine referensi kune rimwe faira. Kazhinji, kunyangwe iyo link yabviswa, chinongedzo chinoramba chiripo. Iko hakuna dambudziko mukubvisa chinongedzo chakadaro, nekuti chinongedzo chacho hachisi kushanda. Nokudaro, hapana chakaipa chichaitika kana ukachibvisa.

Iwe unogona zvakare kushandisa iyo "Data Chengetedza" basa kuti uone kana paine zvisingaite zvinongedzo. Zvisungo zvinowanzosara kana iyo "Rondedzero" mhando yekusimbisa data ikashandiswa. Asi chii chaunofanira kuita kana pane masero akawanda? Izvo zvakakosha here kutarisa imwe neimwe yadzo sequentially? Chokwadi kwete. Mushure mezvose, zvichatora nguva yakareba kwazvo. Naizvozvo, iwe unofanirwa kushandisa yakakosha kodhi kuti uichengetedze zvakanyanya.

Sarudzo Yakajeka

‘——————————————————————————————

Munyori: The_Prist(Shcherbakov Dmitry)

' Professional kuvandudza kwekunyorera kweMS Hofisi yechero yakaoma

' Kuitisa dzidziso paMS Excel

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

' [email protected]

'WebMoney—R298726502453; Yandex.Money - 41001332272872

' Chinangwa:

‘——————————————————————————————

Sub FindErrLink()

    'isu tinofanirwa kutarisa muData -Shandura zvinongedzo zvinongedzo kune iyo sosi faira

    'uye isa mazwi akakosha pano mumavara madiki (chikamu chezita refaira)

    'asterisk inongotsiva chero nhamba yemavara kuti usanetsekane nezve zita chairo

    Const sToFndLink$ = "* kutengesa 2018*"

    Dim rr Sezvo Range, rc Sezvo Range, rres Sezvo Range, s$

    'tsanangura maseru ese ane data kusimbiswa

    Pane Error Resume Zvadaro

    Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Kana rr Hapana Chiripo Ipapo

        MsgBox "Hapana maseru ane data kusimbiswa pane inoshanda shizha", vbInformation, "www.excel-vba.ru"

        Buda Sub

    Endai Kana

    PaKukanganisa GoTo 0

    'check cell yega yega kuti uwane malink

    Kune Imwe neimwe rc Mu rr

        'kungoitika, isu tinosvetuka zvikanganiso - izvi zvinogonawo kuitika

        'asi hukama hwedu hunofanirwa kunge husina ivo uye vanozowanikwa

        s = «»

        Pane Error Resume Zvadaro

        s = rc.Validation.Formula1

        PaKukanganisa GoTo 0

        'tawanikwa - tinounganidza zvese mumutsara wakasiyana

        Kana LCase(s) Like sToFndLink Ipapo

            Kana res Hapana Chinhu Ipapo

                Set res = rc

            mumwe

                Set res = Union (rc, res)

            Endai Kana

        Endai Kana

    Next

    'kana paine chinongedzo, sarudza maseru ese ane data macheki akadaro

    Kana Zvisiri Res Hapana Chiripo Ipapo

        rres.Sarudza

' res.Interior.Color = vbRed 'kana iwe uchida kuratidza neruvara

    Endai Kana

End Sub

Izvo zvinodikanwa kugadzira yakajairwa module mune macro edhita, wobva waisa chinyorwa ichi ipapo. Mushure meizvozvo, fonera iyo macro hwindo uchishandisa kiyi musanganiswa Alt + F8, uye wozosarudza yedu macro uye tinya bhatani re "Mhanya". Pane zvinhu zvishoma zvekuchengeta mupfungwa kana uchishandisa iyi kodhi:

1. Usati watsvaga chinongedzo icho chisisakoshese, iwe unofanirwa kutanga waona kuti chinongedzo chayakagadzirwa chinotaridzika sei. Kuti uite izvi, enda kune "Data" menyu uye tsvaga "Change Links" chinhu ipapo. Mushure meizvozvo, iwe unofanirwa kutarisa zita refaira, uye ritsanangure mune makotesheni. Semuenzaniso, seizvi: Const sToFndLink$ = "* kutengesa 2018*"
2. Zvinokwanisika kunyora zita racho kwete zvizere, asi kungotsiva mavara asina kufanira neasterisk. Uye mune makotesheni, nyora zita refaira mumavara madiki. Muchiitiko ichi, Excel inowana mafaera ese ane tambo yakadaro kumagumo.
3. Kodhi iyi inongokwanisa kutarisa ma link ari mushizha riri kushanda parizvino.
4. Neiyi macro, unogona chete kusarudza maseru aakawana. Iwe unofanirwa kudzima zvese nemaoko. Izvi zvakanakira, nekuti iwe unogona kupeta-kutarisa zvese zvakare.
5. Iwe unogona zvakare kuita kuti masero ajekeswe mune yakakosha ruvara. Kuti uite izvi, bvisa apostrophe pamberi pemutsara uyu. res.Interior.Color = vbRed

Kazhinji, mushure mekupedza matanho anotsanangurwa mumirayiridzo iri pamusoro, hapafanirwe kuve nekubatana kusingakoshi. Asi kana pane mamwe acho mugwaro uye iwe usingakwanisi kuvabvisa nokuda kwechikonzero chimwe kana chimwe (muenzaniso wakajairika ndewekuchengetedza data mune pepa), saka unogona kushandisa kutevedzana kwakasiyana kwezviito. Murairo uyu unoshanda chete kune shanduro 2007 uye yepamusoro.

1. Isu tinogadzira kopi yekuchengetedza yegwaro.
2. Vhura gwaro iri uchishandisa archiver. Iwe unogona kushandisa chero inotsigira iyo ZIP fomati, asi WinRar ichashanda zvakare, pamwe neiyo yakavakwa muWindows.
3. Mune iyo archive inoonekwa, iwe unofanirwa kutsvaga iyo xl folda, uye wovhura ekunzeLinks.
4. Folder iri rine zvese zvekunze zvinongedzo, imwe neimwe inoenderana nefaira refomu rekunzeLink1.xml. Ese akaverengerwa chete, uye saka mushandisi haana mukana wekunzwisisa rudzi rwekubatana uku. Kuti unzwisise rudzi rwekubatanidza, unofanirwa kuvhura iyo _rels folda, woitarisa ipapo.
5. Mushure mezvo, tinobvisa zvese kana matinji chaiwo, zvichibva pane zvatinodzidza muexternalLinkX.xml.rels faira.
6. Mushure meizvozvo, tinovhura faira redu tichishandisa Excel. Pachave neruzivo nezve kukanganisa senge "Kukanganisa muchikamu chezviri muBhuku." Tinopa mvumo. Mushure maizvozvo, imwe dialog ichaonekwa. Tinovhara.

Mushure meizvozvo, zvese zvinongedzo zvinofanirwa kubviswa.