Momwe mungaswe maulalo mu Excel

Kulumikizana ndi gawo lothandiza kwambiri mu Excel. Kupatula apo, nthawi zambiri ogwiritsa ntchito amayenera kugwiritsa ntchito zidziwitso zamafayilo ena. Koma nthawi zina, amatha kuvulaza kwambiri kuposa zabwino. Kupatula apo, mwachitsanzo, ngati mutumiza mafayilowa ndi makalata, maulalo sakugwira ntchito. Lero tikambirana mwatsatanetsatane zomwe tingachite kuti tipewe vutoli.

Kodi maubwenzi mu Excel ndi chiyani

Maubwenzi mu Excel amagwiritsidwa ntchito nthawi zambiri limodzi ndi ntchito monga VPRkuti mupeze zambiri kuchokera ku bukhu lina lantchito. Ikhoza kutenga mawonekedwe a chiyanjano chapadera chomwe chili ndi adiresi osati selo yokha, komanso buku lomwe deta ili. Zotsatira zake, ulalo wotere umawoneka motere: =VLOOKUP(A2;'[Zogulitsa 2018.xlsx]Ripoti'!$A:$F;4;0). Kapena, kuti muyimire chosavuta, imirirani adilesiyo mwanjira iyi: ='[Zogulitsa 2018.xlsx]Ripoti'!$A1. Tiyeni tiwunike chilichonse mwazolumikizana zamtunduwu:

1. [Zogulitsa 2018.xlsx]. Chidutswachi chili ndi ulalo wa fayilo yomwe mukufuna kudziwa zambiri. Amatchedwanso gwero.
2. Photos. Tidagwiritsa ntchito dzina lotsatirali, koma ili siloyenera kukhala. Chida ichi chili ndi dzina la pepala lomwe mukufuna kupeza zambiri.
3. $A:$F ndi $A1 - adilesi ya selo kapena gulu lomwe lili ndi data yomwe ili mu chikalatachi.

Kwenikweni, njira yopangira ulalo ku chikalata chakunja imatchedwa kulumikizana. Titalembetsa adilesi ya cell yomwe ili mu fayilo ina, zomwe zili mu tabu ya "Data" zikusintha. Mwakutero, batani la "Sinthani maulalo" limakhala logwira ntchito, mothandizidwa ndi omwe wogwiritsa ntchito amatha kusintha maulumikizidwe omwe alipo.

Chiyambi cha vuto

Monga lamulo, palibe zovuta zowonjezera zomwe zimachitika kuti mugwiritse ntchito maulalo. Ngakhale zitakhala kuti ma cell asintha, maulalo onse amasinthidwa okha. Koma ngati mwatchulanso buku lantchito lokha kapena kulisunthira ku adilesi ina, Excel imakhala yopanda mphamvu. Choncho, imatulutsa uthenga wotsatira.

Apa, wogwiritsa ali ndi njira ziwiri zotheka momwe angachitire izi. Akhoza kudina "Pitirizani" ndiyeno zosinthazo sizidzasinthidwa, kapena akhoza kudina batani la "Change Associations", momwe angasinthire pamanja. Tikadina batani ili, zenera lowonjezera lidzawonekera momwe zingathekere kusintha maulalo, kuwonetsa komwe fayilo yolondola ili pakali pano komanso zomwe imatchedwa.

Kuphatikiza apo, mutha kusintha maulalo kudzera pa batani lolingana lomwe lili pa tabu ya "Data". Wogwiritsa ntchito amatha kudziwanso kuti kulumikizanako kwaphwanyidwa ndi cholakwika cha #LINK, chomwe chimawoneka ngati Excel sangathe kupeza zidziwitso zomwe zili pa adilesi inayake chifukwa choti adilesiyo ndiyosavomerezeka.

Momwe mungasinthire kulumikizana mu Excel

Imodzi mwa njira zosavuta zothetsera zomwe tafotokozazi ngati simungathe kusintha malo a fayilo yolumikizidwa nokha ndikuchotsa ulalo wokha. Izi ndizosavuta kuchita ngati chikalatacho chili ndi ulalo umodzi wokha. Kuti muchite izi, muyenera kuchita zotsatirazi:

1. Tsegulani "Data" menyu.
2. Timapeza gawo "Malumikizidwe", ndipo apo - kusankha "Sinthani maulumikizidwe".
3. Pambuyo pake, dinani "Chotsani".

Ngati mukufuna kutumiza bukuli kwa munthu wina, ndibwino kuti muchite izi pasadakhale. Kupatula apo, mukachotsa maulalo, zikhalidwe zonse zomwe zili m'chikalata china zimatsitsidwa zokha mufayilo, zomwe zimagwiritsidwa ntchito m'mafayilo, ndipo m'malo mwa adilesi ya cell, zomwe zili m'maselo ofananirako zimangosinthidwa kukhala zikhalidwe. .

Momwe mungasinthire mabuku onse

Koma ngati kuchuluka kwa maulalo kumakhala kwakukulu, kuzichotsa pamanja kungatenge nthawi yayitali. Kuti muthane ndi vutoli nthawi imodzi, mutha kugwiritsa ntchito ma macro apadera. Ili mu VBA-Excel addon. Muyenera yambitsa ndi kupita ku tabu la dzina lomwelo. Padzakhala gawo la "Maulalo", momwe tiyenera kudina batani la "Break all links".

VBA kodi

Ngati sikutheka kuyambitsa chowonjezera ichi, mutha kupanga macro nokha. Kuti muchite izi, tsegulani Visual Basic mkonzi mwa kukanikiza makiyi a Alt + F11, ndipo lembani mizere yotsatirayi m'munda wolowera ma code.

Sub UnlinkWorkBooks()

    Dim WbLinks

    Dim ndi As Long

    Sankhani Case MsgBox(“Mabuku onse a m’mabuku ena adzachotsedwa mufayiloyi, ndipo mafomu osonyeza mabuku ena adzasinthidwa ndi mfundo zofunika kwambiri.” & vbCrLf & “Kodi mukutsimikiza kuti mukufuna kupitiriza?”, 36, “Chotsani ulalo?” )

    Mlandu 7' No

        Tulukani Sub

    Mapeto Sankhani

    WbLinks = ActiveWorkbook.LinkSources(Mtundu:=xlLinkTypeExcelLinks)

    Ngati Sikuti IsEmpty(WbLinks) Ndiye

        Kwa ine = 1 mpaka UBound (WbLinks)

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

        Ena

    china

        MsgBox "Palibe maulalo a mabuku ena mufayiloyi.", 64, "Malumikizidwe a mabuku ena"

    Kutha Ngati

mapeto Sub

Momwe mungathyole zomangira pazosankha zomwe mwasankha

Nthawi ndi nthawi, chiwerengero cha maulalo ndi chachikulu kwambiri, ndipo wosuta akuwopa kuti pambuyo deleting mmodzi wa iwo, sikungatheke kubwezera chirichonse mmbuyo ngati ena anali osafunika. Koma ili ndi vuto losavuta kulipewa. Kuti muchite izi, muyenera kusankha momwe mungachotsere maulalo, ndiyeno kuwachotsa. Kuti muchite izi, muyenera kuchita zotsatirazi:

1. Sankhani deta yomwe ikufunika kusinthidwa.
2. Ikani zowonjezera za VBA-Excel, ndiyeno pitani ku tabu yoyenera.
3. Kenako, timapeza menyu "Maulalo" ndikudina batani "Sungani maulalo m'magawo osankhidwa".

Pambuyo pake, maulalo onse m'maselo osankhidwa adzachotsedwa.

Zoyenera kuchita ngati zomangira sizinadulidwe

Zonse zomwe zili pamwambazi zikumveka bwino, koma muzochita nthawi zonse pamakhala ma nuances. Mwachitsanzo, pangakhale zochitika pamene maubwenzi saduka. Pamenepa, bokosi la zokambirana likuwonekerabe kuti sizingatheke kusintha maulalowo. Zotani zikatere?

1. Choyamba, muyenera kuyang'ana ngati pali chidziwitso chilichonse chomwe chili m'magawo otchulidwawo. Kuti muchite izi, dinani makiyi ophatikizira Ctrl + F3 kapena tsegulani tabu "Mafomula" - "Name Manager". Ngati dzina lafayilo ladzaza, ndiye kuti mumangofunika kulisintha kapena kulichotsa palimodzi. Musanafufute masanjidwe otchulidwa, muyenera kukopera fayilo kumalo ena kuti muthe kubwerera ku mtundu wakale ngati zolakwika zidachitidwa.
2. Ngati simungathe kuthetsa vutoli pochotsa mayina, mukhoza kuyang'ana masanjidwe ovomerezeka. Maselo omwe ali mu tebulo lina akhoza kutchulidwa mu malamulo okhazikika. Kuti muchite izi, pezani chinthu chofananira patsamba la "Home", kenako dinani batani "Fayilo Yoyang'anira".

   Nthawi zambiri, Excel simakupatsani mwayi wopereka ma adilesi a mabuku ena ogwiritsira ntchito mwadongosolo, koma mumatero ngati mutatchula mndandanda womwe umatchulidwa ndi fayilo ina. Nthawi zambiri, ngakhale ulalo utachotsedwa, ulalowo umakhalabe. Palibe vuto kuchotsa ulalo wotero, chifukwa ulalowo sukugwira ntchito. Choncho, palibe choipa chingachitike ngati mutachotsa.

Mukhozanso kugwiritsa ntchito ntchito ya "Data Check" kuti mudziwe ngati pali maulalo osafunika. Maulalo nthawi zambiri amakhalabe ngati mtundu wa "List" wotsimikizira deta ukugwiritsidwa ntchito. Koma chochita ngati pali ma cell ambiri? Kodi m'pofunikadi kufufuza aliyense wa iwo motsatizana? Inde sichoncho. Kupatula apo, zitenga nthawi yayitali kwambiri. Choncho, muyenera kugwiritsa ntchito code yapadera kuti mupulumutse kwambiri.

Njira Yachidule

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

Wolemba: The_Prist(Shcherbakov Dmitry)

' Kupititsa patsogolo ntchito zaukadaulo za MS Office pazovuta zilizonse

' Kuchititsa maphunziro pa MS Excel

https://www.excel-vba.ru

' [Email protected]

'WebMoney—R298726502453; Yandex.Money - 41001332272872

' Cholinga:

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

Sub FindErrLink ()

    'tiyenera kuyang'ana mu ulalo wa data -Change ku fayilo yoyambira

    'ndipo ikani mawu osakira apa m'makalata ang'onoang'ono (gawo la dzina lafayilo)

    'asterisk imangolowa m'malo mwa zilembo zilizonse kuti musade nkhawa ndi dzina lenileni

    Const sToFndLink$ = "* malonda 2018*"

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

    'tanthauzirani ma cell onse okhala ndi kutsimikizira kwa data

    Pa Zolakwika Zowonjezera Zotsatira

    Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Ngati rr Palibe Kanthu Ndiye

        MsgBox "Palibe ma cell omwe ali ndi chitsimikizo cha data pa pepala logwira", vbInformation, "www.excel-vba.ru"

        Tulukani Sub

    Kutha Ngati

    Pa Zolakwika GoTo 0

    'fufuzani selo lililonse kuti mupeze maulalo

    Kwa rc Iliyonse Mu rr

        'ngati tingolumpha zolakwika - izi zitha kuchitikanso

        'koma kulumikizana kwathu kuyenera kukhala kopanda iwo ndipo apezeka

        s = «»

        Pa Zolakwika Zowonjezera Zotsatira

        s = rc.Validation.Formula1

        Pa Zolakwika GoTo 0

        'tapezeka - timasonkhanitsa chilichonse munjira zosiyanasiyana

        Ngati LCase (s) Ngati sToFndLink Ndiye

            Ngati res Palibe Ndiye

                Ikani res = rc

            china

                Set res = Union (rc, res)

            Kutha Ngati

        Kutha Ngati

    Ena

    'ngati pali kulumikizana, sankhani ma cell onse okhala ndi macheke otere

    Ngati Sichoncho Res Palibe Ndiye

        rres.Sankhani

' res.Interior.Color = vbRed 'ngati mukufuna kuwunikira ndi mtundu

    Kutha Ngati

mapeto Sub

Ndikofunikira kupanga gawo lokhazikika mu mkonzi wamkulu, kenako ndikuyika mawuwa pamenepo. Pambuyo pake, imbani zenera lalikulu pogwiritsa ntchito kiyi Alt + F8, kenako sankhani macro athu ndikudina batani la "Thamanga". Pali zinthu zingapo zomwe muyenera kukumbukira mukamagwiritsa ntchito code iyi:

1. Musanafufuze ulalo womwe suli wofunikira, choyamba muyenera kudziwa momwe ulalo womwe umapangidwira umawonekera. Kuti muchite izi, pitani ku "Data" menyu ndikupeza chinthu "Sinthani maulalo" pamenepo. Pambuyo pake, muyenera kuyang'ana dzina la fayilo, ndikulitchula m'mawu. Mwachitsanzo, monga chonchi: Const sToFndLink$ = "* malonda 2018*"
2. Ndizotheka kulemba dzinalo mosakwanira, koma kungosintha zilembo zosafunikira ndi asterisk. Ndipo muzolemba, lembani dzina la fayilo m'malembo ang'onoang'ono. Pankhaniyi, Excel ipeza mafayilo onse omwe ali ndi chingwe chotere kumapeto.
3. Khodi iyi imatha kuwona maulalo amasamba omwe akugwira ntchito pano.
4. Ndi macro iyi, mutha kusankha ma cell omwe mwapeza. Muyenera kuchotsa zonse pamanja. Izi ndizowonjezera, chifukwa mutha kuyang'ananso chilichonse.
5. Mukhozanso kupanga maselo anatsindika mu mtundu wapadera. Kuti muchite izi, chotsani apostrophe pamaso pa mzerewu. res.Interior.Color = vbRed

Nthawi zambiri, mukamaliza masitepe omwe afotokozedwa m'malangizo omwe ali pamwambapa, pasakhalenso kulumikizana kosafunika. Koma ngati pali zina mwazolembazo ndipo simungathe kuzichotsa pazifukwa zina (chitsanzo chachitetezo cha data papepala), ndiye kuti mutha kugwiritsa ntchito njira zingapo. Malangizowa ndi ovomerezeka m'mabaibulo a 2007 ndi apamwamba.

1. Timapanga zosunga zobwezeretsera za chikalatacho.
2. Tsegulani chikalatachi pogwiritsa ntchito archive. Mutha kugwiritsa ntchito iliyonse yomwe imathandizira mtundu wa ZIP, koma WinRar idzagwiranso ntchito, komanso yomwe idamangidwa mu Windows.
3. Pazosungira zomwe zikuwonekera, muyenera kupeza chikwatu cha xl, ndikutsegula Maulalo akunja.
4. Fodayi ili ndi maulalo onse akunja, chilichonse chomwe chimafanana ndi fayilo yakunjaLink1.xml. Zonsezi zimangowerengedwa, choncho wogwiritsa ntchito alibe mwayi womvetsetsa kuti ndi kugwirizana kotani. Kuti mumvetse mtundu wa kulumikizana, muyenera kutsegula chikwatu _rels, ndikuyang'ana pamenepo.
5. Pambuyo pake, timachotsa maulalo onse kapena enieni, kutengera zomwe timaphunzira mufayilo yakunja yaLinkX.xml.rels.
6. Pambuyo pake, timatsegula fayilo yathu pogwiritsa ntchito Excel. Padzakhala zambiri za cholakwika ngati "Zolakwika mu gawo la zomwe zili mu Bukhu." Timapereka chilolezo. Pambuyo pake, kukambirana kwina kudzawonekera. Timatseka.

Pambuyo pake, maulalo onse ayenera kuchotsedwa.