Unganqamula kanjani izixhumanisi ku-Excel

Ukuxhumana kuyisici esiwusizo kakhulu ku-Excel. Phela, ngokuvamile abasebenzisi kufanele basebenzise ulwazi oluvela kwamanye amafayela. Kodwa kwezinye izimo, angenza umonakalo omkhulu kunokuhle. Phela, isibonelo, uma uthumela lawa mafayela ngeposi, izixhumanisi azisebenzi. Namuhla sizokhuluma ngokuningiliziwe mayelana nokuthi yini okufanele uyenze ukuze ugweme inkinga enjalo.

Buyini ubudlelwano ku-Excel

Ubudlelwano ku-Excel buvame ukusetshenziswa ngokuhlangana nemisebenzi efana VPRukuthola ulwazi kwenye incwadi yokusebenzela. Kungathatha uhlobo lwesixhumanisi esikhethekile esiqukethe ikheli leseli hhayi kuphela, kodwa futhi nencwadi lapho idatha itholakala khona. Ngenxa yalokho, isixhumanisi esinjalo sibukeka kanjena: =I-VLOOKUP(A2;'[Intengiso 2018.xlsx]Bika'!$A:$F;4;0). Noma, ukuze uthole isethulo esilula, melela ikheli ngendlela elandelayo: ='[Ukuthengisa 2018.xlsx]Bika'!$A1. Ake sihlaziye ngayinye yezakhi zesixhumanisi zalolu hlobo:

1. [Ukuthengisa 2018.xlsx]. Lesi siqeshana siqukethe isixhumanisi sefayela ofuna ukuthola kulo ulwazi. Ibizwa nangokuthi umthombo.
2. Izithombe. Sisebenzise igama elilandelayo, kodwa leli akulona igama okufanele libe yilo. Leli bhulokhi liqukethe igama leshidi lapho udinga ukuthola khona ulwazi.
3. $A:$F kanye ne-$A1 - ikheli leseli noma ububanzi obuqukethe idatha equkethwe kule dokhumenti.

Empeleni, inqubo yokwakha isixhumanisi kudokhumenti yangaphandle ibizwa ngokuthi ukuxhumanisa. Ngemva kokuba sesibhalise ikheli leseli eliqukethwe kwelinye ifayela, okuqukethwe kwethebhu ethi “Idatha” kuyashintsha. Okungukuthi, inkinobho "Shintsha uxhumano" iqala ukusebenza, ngosizo lapho umsebenzisi angakwazi ukuhlela ukuxhumana okukhona.

Umnyombo wenkinga

Njengomthetho, abukho ubunzima obengeziwe obuvelayo ukuze usebenzise izixhumanisi. Ngisho noma kuvela isimo lapho amaseli eshintsha, khona-ke zonke izixhumanisi zibuyekezwa ngokuzenzakalelayo. Kodwa uma usuvele uyiqambe kabusha incwadi yokusebenza ngokwayo noma uyiyisa kwelinye ikheli, i-Excel iyaphelelwa amandla. Ngakho-ke, ikhiqiza umlayezo olandelayo.

Lapha, umsebenzisi unezinketho ezimbili zokuthi enze njani kulesi simo. Angachofoza okuthi “Qhubeka” bese izinguquko ngeke zibuyekezwe, noma angachofoza inkinobho ethi “Shintsha Izinhlangano”, angazibuyekeza ngayo mathupha. Ngemva kokuchofoza le nkinobho, iwindi elengeziwe lizovela lapho kuzokwazi ukushintsha khona izixhumanisi, okubonisa lapho ifayela elilungile likhona okwamanje nokuthi libizwa ngani.

Ngaphezu kwalokho, ungahlela izixhumanisi ngokusebenzisa inkinobho ehambisanayo etholakala kuthebhu "Idatha". Umsebenzisi angathola futhi ukuthi ukuxhumana kwephulwe iphutha le-#LINK, elivela lapho i-Excel ingakwazi ukufinyelela ulwazi olutholakala ekhelini elithile ngenxa yokuthi ikheli ngokwalo alivumelekile.

Ungasusa kanjani ukuxhumanisa ku-Excel

Enye yezindlela ezilula zokuxazulula isimo esichazwe ngenhla uma kwenzeka ungakwazi ukubuyekeza indawo yefayela elixhunyiwe ngokwakho ukususa isixhumanisi ngokwaso. Lokhu kulula kakhulu ukukwenza uma idokhumenti iqukethe isixhumanisi esisodwa kuphela. Ukuze wenze lokhu, kufanele wenze ukulandelana kwezinyathelo ezilandelayo:

1. Vula imenyu ethi "Idatha".
2. Sithola isigaba "Ukuxhumana", futhi lapho - inketho "Shintsha uxhumano".
3. Ngemva kwalokho, chofoza "Susa ukuxhumana".

Uma uhlose ukuthumela le ncwadi komunye umuntu, kunconywa kakhulu ukuthi ukwenze lokho kusenesikhathi. Phela, ngemva kokususa izixhumanisi, wonke amanani aqukethwe kwenye idokhumenti azolayishwa ngokuzenzakalelayo efayeleni, asetshenziswe kumafomula, futhi esikhundleni sekheli leseli, ulwazi olukumaseli ahambisanayo luzomane luguqulwe lube amanani. .

Ungasusa kanjani ukuxhumanisa zonke izincwadi

Kodwa uma inani lezixhumanisi liba likhulu kakhulu, ukuzisusa ngokwakho kungathatha isikhathi eside. Ukuxazulula le nkinga ngesikhathi esisodwa, ungasebenzisa i-macro ekhethekile. Iku-addon ye-VBA-Excel. Udinga ukuyivula futhi uye kuthebhu yegama elifanayo. Kuzoba nesigaba esithi "Izixhumanisi", lapho sidinga ukuchofoza inkinobho ethi "Phula zonke izixhumanisi".

Ikhodi ye-VBA

Uma kungenakwenzeka ukwenza lesi sengezo sisebenze, ungazidalela i-macro ngokwakho. Ukuze wenze lokhu, vula umhleli we-Visual Basic ngokucindezela izinkinobho ze-Alt + F11, bese ubhala imigqa elandelayo endaweni yokufaka ikhodi.

I-Sub UnlinkWorkBooks()

    Fiphaza ama-WbLinks

    Dim futhi As Long

    Khetha I-Case MsgBox(“Zonke izikhombo eziya kwezinye izincwadi zizokhishwa kuleli fayela, futhi amafomula abhekisela kwezinye izincwadi azothathelwa indawo amanani.” & vbCrLf & “Ingabe uqinisekile ukuthi ufuna ukuqhubeka?”, 36, “Susa ukuxhumanisa?” )

    Icala 7′ No

        Phuma ku-Sub

    Qeda Ukukhetha

    WbLinks = ActiveWorkbook.LinkSources(Uhlobo:=xlLinkTypeExcelLinks)

    Uma Kungeyona I-IsEmpty(WbLinks) Bese

        Okwe i = 1 ukuya ku-UBound(WbLinks)

            I-ActiveWorkbook.BreakLink Name:=WbLinks(i), Uhlobo:=xlLinkTypeExcelLinks

        Olandelayo

    futhi

        MsgBox “Azikho izixhumanisi eziya kwezinye izincwadi kuleli fayela.”, 64, “Izixhumanisi eziya kwezinye izincwadi”

    Qeda Uma

Qeda i-Sub

Unganqamula kanjani izibopho kuphela ebangeni elikhethiwe

Ngezikhathi ezithile, inani lezixhumanisi likhulu kakhulu, futhi umsebenzisi wesaba ukuthi ngemva kokususa enye yazo, ngeke kwenzeke ukubuyisela yonke into emuva uma okunye bekungadingekile. Kodwa lena inkinga okulula ukuyigwema. Ukuze wenze lokhu, udinga ukukhetha ibanga lapho uzosusa khona izixhumanisi, bese uzisusa. Ukuze wenze lokhu, kufanele wenze ukulandelana kwezenzo ezilandelayo:

1. Khetha idathasethi edinga ukushintshwa.
2. Faka isengezo se-VBA-Excel, bese uya kuthebhu efanele.
3. Okulandelayo, sithola imenyu ethi "Izixhumanisi" bese uchofoza inkinobho ethi "Phula izixhumanisi kububanzi obukhethiwe".

Ngemva kwalokho, zonke izixhumanisi kusethi ekhethiwe yamaseli zizosuswa.

Okufanele ukwenze uma izibopho zinganqamuki

Konke lokhu okungenhla kuzwakala kukuhle, kodwa ekusebenzeni kuhlale kukhona ama-nuances. Isibonelo, kungase kube nesimo lapho izibopho zinganqamuki. Kulesi simo, ibhokisi lengxoxo lisavela elisho ukuthi akwenzeki ukuvuselela izixhumanisi ngokuzenzakalelayo. Yini okufanele uyenze kulesi simo?

1. Okokuqala, udinga ukuhlola ukuthi ngabe yiluphi ulwazi oluqukethwe kububanzi obuqanjiwe. Ukuze wenze lokhu, cindezela inhlanganisela yokhiye Ctrl + F3 noma uvule ithebhu "Amafomula" - "Isiphathi Segama". Uma igama lefayela ligcwele, udinga nje ukulihlela noma ulisuse ngokuphelele. Ngaphambi kokususa ububanzi obuqanjiwe, udinga ukukopishela ifayela kwenye indawo ukuze ukwazi ukubuyela enguqulweni yokuqala uma izinyathelo ezingalungile zithathwe.
2. Uma ungakwazi ukuxazulula inkinga ngokukhipha amagama, ungabheka ukufometha okunemibandela. Amaseli kwelinye ithebula angabhekisela emithethweni yokufometha enemibandela. Ukuze wenze lokhu, thola into ehambisanayo kuthebhu ethi "Ekhaya", bese uchofoza inkinobho ethi "Ukuphathwa Kwefayela".

   Ngokuvamile, i-Excel ayikuniki ikhono lokunikeza ikheli lezinye izincwadi zokusebenza ngokufometha okunemibandela, kodwa wenza kanjalo uma ubhekisela kububanzi obuqanjiwe ngereferensi yelinye ifayela. Ngokuvamile, ngisho nangemva kokuba isixhumanisi sesisusiwe, isixhumanisi sihlala. Ayikho inkinga ekususeni isixhumanisi esinjalo, ngoba isixhumanisi empeleni asisebenzi. Ngakho-ke, akukho okubi okuzokwenzeka uma uyisusa.

Ungasebenzisa futhi umsebenzi othi "Ukuhlolwa Kwedatha" ukuthola ukuthi azikho yini izixhumanisi ezingadingekile. Izixhumanisi zivame ukuhlala uma kusetshenziswa uhlobo “lohlu” lokuqinisekisa idatha. Kodwa yini okufanele uyenze uma kunamaseli amaningi? Ingabe kuyadingeka ngempela ukuhlola ngayinye yazo ngokulandelana? Vele akunjalo. Phela kuzothatha isikhathi eside kakhulu. Ngakho-ke, udinga ukusebenzisa ikhodi ekhethekile ukuze uyilondoloze kakhulu.

Inketho Icacile

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

Umbhali : The_Prist(Shcherbakov Dmitry)

' Ukuthuthukiswa kochwepheshe kwezicelo ze-MS Office zanoma ibuphi ubunzima

' Ukwenza ukuqeqeshwa ku-MS Excel

https://www.excel-vba.ru

' [i-imeyili ivikelwe]

'I-WebMoney—R298726502453; Yandex.Money - 41001332272872

' Inhloso:

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

I-Sub FindErrLink()

    'sidinga ukubheka isixhumanisi seDatha -Shintsha kufayela elingumthombo

    'bese ufaka amagama angukhiye lapha ngosonhlamvukazi abancane (ingxenye yegama lefayela)

    'inkanyezi ivele imiselele noma iyiphi inombolo yezinhlamvu ukuze ungakhathazeki mayelana negama ngqo

    Const sToFndLink$ = “*sales 2018*”

    Dim rr Njengobubanzi, rc Njengobubanzi, rres Njengobubanzi, s$

    'chaza wonke amaseli anokuqinisekiswa kwedatha

    Kuphutha Qalisa kabusha Okulandelayo

    Setha okuthi rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Uma rr Ingelutho Khona

        MsgBox “Awekho amaseli anokuqinisekiswa kwedatha eshidini elisebenzayo”, vbInformation, “www.excel-vba.ru”

        Phuma ku-Sub

    Qeda Uma

    Ngephutha GoTo 0

    'hlola iseli ngalinye ukuthola izixhumanisi

    Ku-rc Ngayinye i-rr

        'uma kwenzeka, seqa amaphutha - lokhu kungenzeka futhi

        'kodwa ukuxhumana kwethu kumele kube ngaphandle kwabo futhi bazotholakala nakanjani

        s = «»

        Kuphutha Qalisa kabusha Okulandelayo

        s = rc.Validation.Formula1

        Ngephutha GoTo 0

        'sitholiwe - siqoqa yonke into ebangeni elihlukene

        Uma LCase(ama) Like sToFndLink Bese

            Uma i-res Ayilutho Khona

                Setha i-rres = rc

            futhi

                Setha i-rres = Union(rc, res)

            Qeda Uma

        Qeda Uma

    Olandelayo

    'uma kukhona ukuxhumana, khetha wonke amaseli anokuhlolwa okunjalo kwedatha

    Uma kungenjalo res Ayilutho Khona

        rres.Khetha

' res.Interior.Color = vbRed 'uma ufuna ukugqamisa ngombala

    Qeda Uma

Qeda i-Sub

Kuyadingeka ukwenza imodyuli ejwayelekile kusihleli esikhulu, bese ufaka lo mbhalo lapho. Ngemuva kwalokho, shayela iwindi le-macro usebenzisa inhlanganisela yokhiye Alt + F8, bese ukhetha i-macro yethu bese uchofoza inkinobho ethi "Run". Kunezinto ezimbalwa okufanele uzikhumbule lapho usebenzisa le khodi:

1. Ngaphambi kokuthi useshele isixhumanisi esingasabalulekile, kufanele uqale unqume ukuthi isixhumanisi esenziwa ngaso sibukeka kanjani. Ukuze wenze lokhu, iya kumenyu ethi "Idatha" bese uthola into ethi "Shintsha Izixhumanisi". Ngemuva kwalokho, udinga ukubheka igama lefayela, futhi ulicacise kumacaphuno. Ngokwesibonelo, kanje: Const sToFndLink$ = “*sales 2018*”
2. Kuyenzeka ukuthi ungalibhali igama ngokugcwele, kodwa umane ufake inkanyezi esikhundleni sezinhlamvu ezingadingekile. Futhi ezicashuniwe, bhala igama lefayela ngezinhlamvu ezincane. Kulokhu, i-Excel izothola wonke amafayela aqukethe iyunithi yezinhlamvu ekugcineni.
3. Le khodi ikwazi kuphela ukuhlola izixhumanisi eshidini elisebenzayo okwamanje.
4. Ngale macro, ungakhetha kuphela amaseli owatholile. Kufanele ususe yonke into ngesandla. Lokhu kuhlanganisa, ngoba ungaphinda uhlole yonke into futhi.
5. Ungenza futhi amaseli agqanyiswe ngombala okhethekile. Ukuze wenze lokhu, susa i-apostrophe ngaphambi kwalo mugqa. res.Interior.Color = vbRed

Ngokuvamile, ngemva kokuqeda izinyathelo ezichazwe emiyalweni engenhla, akumele kusaba khona ukuxhumana okungadingekile. Kodwa uma kukhona ezinye zazo kudokhumenti futhi ungakwazi ukuzisusa ngesizathu esisodwa noma esinye (isibonelo esijwayelekile ukuphepha kwedatha eshidini), ungasebenzisa ukulandelana okuhlukile kwezenzo. Lo myalelo usebenza kuphela ezinguqulweni zika-2007 nangaphezulu.

1. Sakha ikhophi eyisipele yedokhumenti.
2. Vula le dokhumenti usebenzisa i-archiver. Ungasebenzisa noma iyiphi esekela ifomethi ye-ZIP, kodwa i-WinRar izosebenza, kanye naleyo eyakhelwe ku-Windows.
3. Kungobo yomlando evelayo, udinga ukuthola ifolda ye-xl, bese uvula izixhumanisi zangaphandle.
4. Le folda iqukethe zonke izixhumanisi zangaphandle, ngayinye ehambelana nefayela lefomu langaphandleLink1.xml. Zonke zinezinombolo kuphela, ngakho-ke umsebenzisi akanalo ithuba lokuqonda ukuthi hlobo luni lokuxhumana lolu. Ukuze uqonde ukuthi hlobo luni lokuxhumana, udinga ukuvula ifolda ye-_rels, futhi uyibuke lapho.
5. Ngemva kwalokho, sisusa zonke noma izixhumanisi ezithile, ngokusekelwe kulokho esikufunda kufayela langaphandleLinkX.xml.rels.
6. Ngemuva kwalokho, sivula ifayela lethu sisebenzisa i-Excel. Kuzoba nolwazi mayelana nephutha njengokuthi "Iphutha engxenyeni yokuqukethwe eBhukwini." Sinikeza imvume. Ngemva kwalokho, kuzovela enye ibhokisi. Siyayivala.

Ngemva kwalokho, zonke izixhumanisi kufanele zisuswe.