როგორ გავტეხოთ ბმულები Excel-ში

კომუნიკაცია ძალიან სასარგებლო ფუნქციაა Excel-ში. ყოველივე ამის შემდეგ, ძალიან ხშირად მომხმარებლებს უწევთ ინფორმაციის გამოყენება სხვა ფაილებიდან. მაგრამ ზოგიერთ სიტუაციაში მათ შეუძლიათ უფრო მეტი ზიანი მიაყენონ, ვიდრე კარგი. ბოლოს და ბოლოს, მაგალითად, თუ ამ ფაილებს ფოსტით აგზავნით, ბმულები არ მუშაობს. დღეს ჩვენ უფრო დეტალურად ვისაუბრებთ იმაზე, თუ რა უნდა გავაკეთოთ ასეთი პრობლემის თავიდან ასაცილებლად.

რა არის ურთიერთობები Excel-ში

Excel-ში ურთიერთობები ძალიან ხშირად გამოიყენება ფუნქციებთან ერთად, როგორიცაა VPRსხვა სამუშაო წიგნიდან ინფორმაციის მისაღებად. მას შეუძლია მიიღოს სპეციალური ბმულის ფორმა, რომელიც შეიცავს არა მხოლოდ უჯრედის, არამედ წიგნის მისამართს, რომელშიც განთავსებულია მონაცემები. შედეგად, ასეთი ბმული ასე გამოიყურება: =VLOOKUP(A2;'[Sales 2018.xlsx]ანგარიში'!$A:$F;4;0). ან, უფრო მარტივი წარმოდგენისთვის, წარმოადგინეთ მისამართი შემდეგი ფორმით: ='[Sales 2018.xlsx]ანგარიში'!$A1. მოდით გავაანალიზოთ ამ ტიპის ბმულის თითოეული ელემენტი:

1. [გაყიდვები 2018.xlsx]. ეს ფრაგმენტი შეიცავს ფაილის ბმულს, საიდანაც გსურთ ინფორმაციის მიღება. მას წყაროსაც უწოდებენ.
2. ფოტო. ჩვენ გამოვიყენეთ შემდეგი სახელი, მაგრამ ეს არ არის ის სახელი, რომელიც უნდა იყოს. ეს ბლოკი შეიცავს ფურცლის სახელს, რომელშიც უნდა იპოვოთ ინფორმაცია.
3. $A:$F და $A1 - უჯრედის ან დიაპაზონის მისამართი, რომელიც შეიცავს მონაცემებს, რომლებიც შეიცავს ამ დოკუმენტს.

ფაქტობრივად, გარე დოკუმენტთან ბმულის შექმნის პროცესს ჰქვია დაკავშირება. მას შემდეგ, რაც ჩვენ დავარეგისტრირებთ სხვა ფაილში შემავალი უჯრედის მისამართს, იცვლება ჩანართის "მონაცემების" შინაარსი. კერძოდ, აქტიურდება ღილაკი „Change Connections“, რომლის დახმარებით მომხმარებელს შეუძლია არსებული კავშირების რედაქტირება.

პრობლემის არსი

როგორც წესი, დამატებითი სირთულეები არ წარმოიქმნება ბმულების გამოყენებისთვის. მაშინაც კი, თუ შეიქმნა სიტუაცია, რომელშიც უჯრედები იცვლება, მაშინ ყველა ბმული ავტომატურად განახლდება. მაგრამ თუ თქვენ უკვე დაარქვით თავად სამუშაო წიგნს ან გადაიტანეთ იგი სხვა მისამართზე, Excel უძლური ხდება. ამიტომ, ის აწარმოებს შემდეგ შეტყობინებას.

აქ, მომხმარებელს აქვს ორი შესაძლო ვარიანტი, თუ როგორ უნდა მოიქცეს ამ სიტუაციაში. მას შეუძლია დააჭიროს „გაგრძელებას“ და შემდეგ ცვლილებები არ განახლდება, ან დააწკაპუნოს ღილაკზე „ასოციაციების შეცვლა“, რომლითაც შეუძლია მათი ხელით განახლება. ამ ღილაკზე დაწკაპუნების შემდეგ, გამოჩნდება დამატებითი ფანჯარა, რომელშიც შესაძლებელი იქნება ბმულების შეცვლა, სადაც მითითებულია, სად მდებარეობს ამ მომენტში სწორი ფაილი და რა ჰქვია მას.

გარდა ამისა, თქვენ შეგიძლიათ შეცვალოთ ბმულები "მონაცემების" ჩანართზე განთავსებული შესაბამისი ღილაკის საშუალებით. მომხმარებელს ასევე შეუძლია გაარკვიოს, რომ კავშირი გატეხილია #LINK შეცდომით, რომელიც ჩნდება მაშინ, როდესაც Excel ვერ წვდება კონკრეტულ მისამართზე მდებარე ინფორმაციას იმის გამო, რომ თავად მისამართი არასწორია.

როგორ გავაუქმოთ კავშირი ექსელში

ზემოთ აღწერილი სიტუაციის გადასაჭრელად ერთ-ერთი უმარტივესი მეთოდი იმ შემთხვევაში, თუ თქვენ თვითონ ვერ განაახლებთ დაკავშირებული ფაილის მდებარეობას, არის თავად ბმულის წაშლა. ეს განსაკუთრებით ადვილია, თუ დოკუმენტი შეიცავს მხოლოდ ერთ ბმულს. ამისათვის თქვენ უნდა შეასრულოთ ნაბიჯების შემდეგი თანმიმდევრობა:

1. გახსენით მენიუ "მონაცემები".
2. ჩვენ ვპოულობთ განყოფილებას "კავშირები" და იქ - ვარიანტი "კავშირების შეცვლა".
3. ამის შემდეგ დააჭირეთ ღილაკს "გაუქმება".

თუ თქვენ აპირებთ ამ წიგნის გაგზავნას სხვა პირისთვის, რეკომენდებულია ამის გაკეთება წინასწარ. ყოველივე ამის შემდეგ, ბმულების წაშლის შემდეგ, ყველა ის მნიშვნელობა, რომელიც შეიცავს სხვა დოკუმენტს, ავტომატურად ჩაიტვირთება ფაილში, რომელიც გამოიყენება ფორმულებში და უჯრედის მისამართის ნაცვლად, შესაბამის უჯრედებში ინფორმაცია უბრალოდ გარდაიქმნება მნიშვნელობებად. .

როგორ გავაუქმოთ ყველა წიგნი

მაგრამ თუ ბმულების რაოდენობა ძალიან დიდი ხდება, მათ ხელით წაშლას შეიძლება დიდი დრო დასჭირდეს. ამ პრობლემის ერთჯერად გადასაჭრელად, შეგიძლიათ გამოიყენოთ სპეციალური მაკრო. ის VBA-Excel-ის დამატებაშია. თქვენ უნდა გაააქტიუროთ იგი და გადახვიდეთ ამავე სახელწოდების ჩანართზე. იქნება განყოფილება "ბმულები", რომელშიც უნდა დავაჭიროთ ღილაკს "გატეხე ყველა ბმული".

VBA კოდი

თუ შეუძლებელია ამ დანამატის გააქტიურება, შეგიძლიათ თავად შექმნათ მაკრო. ამისათვის გახსენით Visual Basic რედაქტორი Alt + F11 კლავიშების დაჭერით და კოდის შეყვანის ველში ჩაწერეთ შემდეგი სტრიქონები.

ქვე UnlinkWorkBooks()

    ჩამქრალი WbLinks

    ბუნდოვანი და როგორც დიდხანს

    აირჩიეთ Case MsgBox("სხვა წიგნების ყველა მითითება წაიშლება ამ ფაილიდან და სხვა წიგნებზე მითითებული ფორმულები შეიცვლება მნიშვნელობებით." & vbCrLf & "დარწმუნებული ხართ, რომ გსურთ გააგრძელოთ?", 36, "გაუქმება?" )

    საქმე 7′ No

        გასვლა ქვე

    დასრულება აირჩიეთ

    WbLinks = ActiveWorkbook.LinkSources(ტიპი:=xlLinkTypeExcelLinks)

    თუ არ არის ცარიელი (WbLinks) მაშინ

        i = 1 To UBound (WbLinks)

            ActiveWorkbook.BreakLink სახელი:=WbLinks(i), ტიპი:=xlLinkTypeExcelLinks

        შემდეგი

    სხვა

        MsgBox "ამ ფაილში სხვა წიგნების ბმული არ არის.", 64, "სხვა წიგნების ბმულები"

    ბოლო თუ

ბოლო Sub

როგორ გავწყვიტოთ კავშირები მხოლოდ შერჩეულ დიაპაზონში

დროდადრო, ბმულების რაოდენობა ძალიან დიდია და მომხმარებელს ეშინია, რომ რომელიმე მათგანის წაშლის შემდეგ, შეუძლებელი იქნება ყველაფრის უკან დაბრუნება, თუ ზოგიერთი ზედმეტი იყო. მაგრამ ეს არის პრობლემა, რომლის თავიდან აცილება ადვილია. ამისათვის თქვენ უნდა აირჩიოთ დიაპაზონი, რომელშიც უნდა წაშალოთ ბმულები და შემდეგ წაშალოთ ისინი. ამისათვის თქვენ უნდა შეასრულოთ მოქმედებების შემდეგი თანმიმდევრობა:

1. აირჩიეთ მონაცემთა ნაკრები, რომელიც უნდა შეიცვალოს.
2. დააინსტალირეთ VBA-Excel დანამატი და შემდეგ გადადით შესაბამის ჩანართზე.
3. შემდეგი, ჩვენ ვიპოვით მენიუს "ბმულები" და დააწკაპუნეთ ღილაკზე "ბმულების გატეხვა არჩეულ დიაპაზონში".

ამის შემდეგ, უჯრედების შერჩეული ნაკრების ყველა ბმული წაიშლება.

რა უნდა გააკეთოს, თუ კავშირები არ გატეხილია

ყოველივე ზემოთქმული კარგად ჟღერს, მაგრამ პრაქტიკაში ყოველთვის არის გარკვეული ნიუანსი. მაგალითად, შეიძლება იყოს სიტუაცია, როდესაც კავშირები არ წყდება. ამ შემთხვევაში, კვლავ გამოჩნდება დიალოგური ფანჯარა, რომელშიც ნათქვამია, რომ შეუძლებელია ბმულების ავტომატურად განახლება. რა უნდა გააკეთოს ამ სიტუაციაში?

1. პირველ რიგში, თქვენ უნდა შეამოწმოთ არის თუ არა რაიმე ინფორმაცია დასახელებულ დიაპაზონებში. ამისათვის დააჭირეთ კლავიშთა კომბინაციას Ctrl + F3 ან გახსენით ჩანართი "ფორმულები" - "სახელების მენეჯერი". თუ ფაილის სახელი სავსეა, თქვენ უბრალოდ უნდა შეცვალოთ იგი ან საერთოდ წაშალოთ იგი. დასახელებული დიაპაზონების წაშლამდე, თქვენ უნდა დააკოპიროთ ფაილი სხვა ადგილას, რათა შეგეძლოთ დაუბრუნდეთ საწყის ვერსიას, თუ არასწორი ნაბიჯები გადადგა.
2. თუ ვერ გადაჭრით პრობლემას სახელების წაშლით, შეგიძლიათ შეამოწმოთ პირობითი ფორმატირება. სხვა ცხრილის უჯრედების მითითება შესაძლებელია პირობითი ფორმატირების წესებში. ამისათვის იპოვნეთ შესაბამისი ელემენტი "მთავარი" ჩანართზე და შემდეგ დააჭირეთ ღილაკს "ფაილის მართვა".

   ჩვეულებრივ, Excel არ გაძლევთ შესაძლებლობას მიაწოდოთ სხვა სამუშაო წიგნების მისამართი პირობითი ფორმატირებით, მაგრამ ამას აკეთებთ, თუ მიუთითებთ დასახელებულ დიაპაზონს სხვა ფაილზე მითითებით. ჩვეულებრივ, ბმულის წაშლის შემდეგაც კი ბმული რჩება. ასეთი ლინკის წაშლის პრობლემა არ არის, რადგან ბმული ფაქტობრივად არ მუშაობს. ამიტომ, თუ ამოიღებთ, ცუდი არაფერი მოხდება.

თქვენ ასევე შეგიძლიათ გამოიყენოთ "მონაცემთა შემოწმება" ფუნქცია, რათა გაარკვიოთ, არის თუ არა არასაჭირო ბმულები. ბმულები, როგორც წესი, რჩება, თუ გამოყენებულია მონაცემთა ვალიდაციის ტიპი "List". მაგრამ რა უნდა გააკეთოს, თუ ბევრი უჯრედია? მართლაც აუცილებელია თითოეული მათგანის თანმიმდევრულად შემოწმება? Რათქმაუნდა არა. ყოველივე ამის შემდეგ, ამას ძალიან დიდი დრო დასჭირდება. ამიტომ, თქვენ უნდა გამოიყენოთ სპეციალური კოდი, რომ მნიშვნელოვნად შეინახოთ იგი.

ვარიანტი აშკარა

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

ავტორი: The_Prist (შჩერბაკოვი დიმიტრი)

ნებისმიერი სირთულის MS Office აპლიკაციების პროფესიონალური შემუშავება

ტრენინგების ჩატარება MS Excel-ზე

https://www.excel-vba.ru

[ელფოსტა დაცულია]

'WebMoney-R298726502453; Yandex.Money — 41001332272872

' მიზანი:

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

ქვე FindErrLink()

    ჩვენ უნდა ვეძიოთ მონაცემთა -Change links-ის ბმულში წყაროს ფაილზე

    და ჩასვით საკვანძო სიტყვები აქ მცირე ასოებით (ფაილის სახელის ნაწილი)

    ვარსკვლავი უბრალოდ ცვლის სიმბოლოების ნებისმიერ რაოდენობას, ასე რომ თქვენ არ უნდა ინერვიულოთ ზუსტი სახელის შესახებ

    Const sToFndLink$ = „*გაყიდვები 2018*“

    Dim rr როგორც დიაპაზონი, rc როგორც დიაპაზონი, rres როგორც დიაპაზონი, s$

    ყველა უჯრედის განსაზღვრა მონაცემთა დადასტურებით

    წლის შეცდომა რეზიუმე შემდეგი

    დააყენეთ rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    თუ rr არაფერია მაშინ

        MsgBox "აქტიურ ფურცელზე არ არის უჯრედები მონაცემთა დადასტურებით", vbInformation, "www.excel-vba.ru"

        გასვლა ქვე

    ბოლო თუ

    შეცდომის შესახებ GoTo 0

    შეამოწმეთ თითოეული უჯრედი ბმულებისთვის

    თითოეული rc In rr

        ”ყოველ შემთხვევაში, ჩვენ გამოვტოვებთ შეცდომებს - ეს ასევე შეიძლება მოხდეს

        მაგრამ ჩვენი კავშირები მათ გარეშე უნდა იყოს და ისინი აუცილებლად მოიძებნება

        s = «»

        წლის შეცდომა რეზიუმე შემდეგი

        s = rc.Validation.Formula1

        შეცდომის შესახებ GoTo 0

        ნაპოვნია - ჩვენ ვაგროვებთ ყველაფერს ცალკე დიაპაზონში

        თუ LCase(s) Like sToFndLink მაშინ

            თუ rres არაფერია მაშინ

                დააყენეთ rres = rc

            სხვა

                კომპლექტი rres = კავშირი (rc, rres)

            ბოლო თუ

        ბოლო თუ

    შემდეგი

    თუ არის კავშირი, აირჩიეთ ყველა უჯრედი ასეთი მონაცემების შემოწმებით

    თუ არა rres არაფერია მაშინ

        rres.აირჩიეთ

' rres.Interior.Color = vbRed 'თუ გსურთ მონიშნოთ ფერით

    ბოლო თუ

ბოლო Sub

აუცილებელია მაკრო რედაქტორში სტანდარტული მოდულის გაკეთება, შემდეგ კი ამ ტექსტის ჩასმა. ამის შემდეგ, გამოიძახეთ მაკრო ფანჯარა კლავიშთა კომბინაციის გამოყენებით Alt + F8, შემდეგ აირჩიეთ ჩვენი მაკრო და დააჭირეთ ღილაკს "Run". ამ კოდის გამოყენებისას უნდა გახსოვდეთ რამდენიმე რამ:

1. სანამ ეძებთ ბმულს, რომელიც აღარ არის რელევანტური, ჯერ უნდა დაადგინოთ, როგორ გამოიყურება ბმული, რომლის მეშვეობითაც ის იქმნება. ამისათვის გადადით მენიუში "მონაცემები" და იპოვნეთ "ბმულების შეცვლა" პუნქტი. ამის შემდეგ, თქვენ უნდა გადახედოთ ფაილის სახელს და მიუთითოთ იგი ბრჭყალებში. მაგალითად, ასე: Const sToFndLink$ = „*გაყიდვები 2018*“
2. შესაძლებელია სახელის დაწერა არა სრულად, არამედ უბრალოდ შეცვალოთ არასაჭირო სიმბოლოები ვარსკვლავით. და ბრჭყალებში ჩაწერეთ ფაილის სახელი პატარა ასოებით. ამ შემთხვევაში, Excel იპოვის ყველა ფაილს, რომელიც შეიცავს ასეთ სტრიქონს ბოლოს.
3. ამ კოდს შეუძლია შეამოწმოს მხოლოდ ბმულები ფურცელში, რომელიც ამჟამად აქტიურია.
4. ამ მაკროთი შეგიძლიათ აირჩიოთ მხოლოდ ის უჯრედები, რომლებიც მან იპოვა. თქვენ უნდა წაშალოთ ყველაფერი ხელით. ეს არის პლიუსი, რადგან თქვენ შეგიძლიათ კიდევ ერთხელ შეამოწმოთ ყველაფერი.
5. თქვენ ასევე შეგიძლიათ გააკეთოთ ხაზგასმული უჯრედები სპეციალურ ფერში. ამისათვის ამოიღეთ აპოსტროფი ამ ხაზის წინ. rres.Interior.Color = vbRed

ჩვეულებრივ, მას შემდეგ რაც დაასრულებთ ზემოთ მოცემულ ინსტრუქციებში აღწერილ ნაბიჯებს, აღარ უნდა იყოს ზედმეტი კავშირები. მაგრამ თუ დოკუმენტში არის რამდენიმე მათგანი და თქვენ ვერ ახერხებთ მათ ამოღებას ამა თუ იმ მიზეზის გამო (ტიპიური მაგალითია ფურცლის მონაცემების უსაფრთხოება), მაშინ შეგიძლიათ გამოიყენოთ მოქმედებების განსხვავებული თანმიმდევრობა. ეს ინსტრუქცია მოქმედებს მხოლოდ 2007 და უფრო მაღალი ვერსიებისთვის.

1. ჩვენ ვქმნით დოკუმენტის სარეზერვო ასლს.
2. გახსენით ეს დოკუმენტი არქივის გამოყენებით. თქვენ შეგიძლიათ გამოიყენოთ ნებისმიერი, რომელიც მხარს უჭერს ZIP ფორმატს, მაგრამ WinRar ასევე იმუშავებს, ისევე როგორც Windows-ში ჩაშენებული.
3. არქივში, რომელიც გამოჩნდება, თქვენ უნდა იპოვოთ xl საქაღალდე და შემდეგ გახსენით externalLinks.
4. ეს საქაღალდე შეიცავს ყველა გარე ბმულს, რომელთაგან თითოეული შეესაბამება ფაილის ფორმის externalLink1.xml. ყველა მათგანი მხოლოდ დანომრილია და, შესაბამისად, მომხმარებელს არ აქვს შესაძლებლობა გაიგოს, რა სახის კავშირია ეს. იმის გასაგებად, თუ რა სახის კავშირია, თქვენ უნდა გახსნათ _rels საქაღალდე და გადახედოთ იქ.
5. ამის შემდეგ, ჩვენ ვშლით ყველა ან კონკრეტულ ბმულს, იმის საფუძველზე, რასაც ვისწავლით externalLinkX.xml.rels ფაილში.
6. ამის შემდეგ, ჩვენ ვხსნით ჩვენს ფაილს Excel-ის გამოყენებით. იქნება ინფორმაცია შეცდომის შესახებ, როგორიცაა „შეცდომა წიგნის შინაარსის ნაწილში“. ჩვენ ვაძლევთ თანხმობას. ამის შემდეგ გამოჩნდება სხვა დიალოგი. ჩვენ ვხურავთ მას.

ამის შემდეგ, ყველა ბმული უნდა წაიშალოს.