Spis treści
Komunikacja to bardzo przydatna funkcja w Excelu. W końcu bardzo często użytkownicy muszą korzystać z informacji z innych plików. Ale w niektórych sytuacjach mogą wyrządzić więcej szkody niż pożytku. W końcu, na przykład, jeśli wyślesz te pliki pocztą, linki nie działają. Dziś porozmawiamy bardziej szczegółowo o tym, co zrobić, aby uniknąć takiego problemu.
Czym są relacje w Excelu
Relacje w Excelu są bardzo często używane w połączeniu z funkcjami takimi jak VPRaby uzyskać informacje z innego skoroszytu. Może przybrać postać specjalnego linku, który zawiera adres nie tylko komórki, ale także książki, w której znajdują się dane. W efekcie taki link wygląda mniej więcej tak: =WYSZUKAJ.PIONOWO(A2;'[Sprzedaż 2018.xlsx]Raport'!$A:$F;4;0). Lub, dla prostszego przedstawienia, przedstaw adres w następującej formie: ='[Sprzedaż 2018.xlsx]Raport'!$A1. Przeanalizujmy każdy z elementów linku tego typu:
- [Sprzedaż 2018.xlsx]. Ten fragment zawiera link do pliku, z którego chcesz uzyskać informacje. Jest również nazywany źródłem.
- ZDJĘCIA. Użyliśmy następującej nazwy, ale nie jest to nazwa, która powinna być. Ten blok zawiera nazwę arkusza, w którym musisz znaleźć informacje.
- $A:$F i $A1 – adres komórki lub zakresu zawierającego dane zawarte w tym dokumencie.
W rzeczywistości proces tworzenia linku do zewnętrznego dokumentu nazywa się linkowaniem. Po zarejestrowaniu adresu komórki zawartej w innym pliku zmienia się zawartość zakładki „Dane”. Mianowicie uaktywnia się przycisk „Zmień połączenia”, za pomocą którego użytkownik może edytować istniejące połączenia.
Istota problemu
Z reguły nie pojawiają się żadne dodatkowe trudności w korzystaniu z linków. Nawet jeśli zaistnieje sytuacja, w której komórki się zmienią, wszystkie linki zostaną automatycznie zaktualizowane. Ale jeśli już zmienisz nazwę samego skoroszytu lub przeniesiesz go pod inny adres, program Excel stanie się bezsilny. Dlatego generuje następujący komunikat.
Tutaj użytkownik ma dwie możliwe opcje postępowania w tej sytuacji. Może kliknąć „Kontynuuj”, a wtedy zmiany nie zostaną zaktualizowane lub kliknąć przycisk „Zmień powiązania”, za pomocą którego może je zaktualizować ręcznie. Po kliknięciu tego przycisku pojawi się dodatkowe okno, w którym będzie można zmienić linki, wskazując, gdzie aktualnie znajduje się właściwy plik i jak się nazywa.
Ponadto możesz edytować linki za pomocą odpowiedniego przycisku znajdującego się w zakładce „Dane”. Użytkownik może również dowiedzieć się, że połączenie jest zerwane przez błąd #LINK, który pojawia się, gdy Excel nie może uzyskać dostępu do informacji znajdujących się pod określonym adresem z powodu tego, że sam adres jest nieprawidłowy.
Jak odłączyć w programie Excel
Jedną z najprostszych metod rozwiązania opisanej powyżej sytuacji w przypadku, gdy nie możesz samodzielnie zaktualizować lokalizacji dołączonego pliku, jest usunięcie samego łącza. Jest to szczególnie łatwe, jeśli dokument zawiera tylko jeden link. Aby to zrobić, musisz wykonać następującą sekwencję kroków:
- Otwórz menu „Dane”.
- Znajdujemy sekcję „Połączenia”, a tam – opcję „Zmień połączenia”.
- Następnie kliknij „Odłącz”.
Jeśli zamierzasz wysłać tę książkę do innej osoby, zdecydowanie zalecamy, abyś zrobił to z wyprzedzeniem. W końcu po usunięciu linków wszystkie wartości zawarte w innym dokumencie zostaną automatycznie załadowane do pliku, użytego w formułach, a zamiast adresu komórki informacje w odpowiednich komórkach zostaną po prostu przekształcone w wartości .
Jak odłączyć wszystkie książki
Ale jeśli liczba linków stanie się zbyt duża, ręczne ich usunięcie może zająć dużo czasu. Aby rozwiązać ten problem za jednym razem, możesz użyć specjalnego makra. Znajduje się w dodatku VBA-Excel. Musisz go aktywować i przejść do zakładki o tej samej nazwie. Pojawi się sekcja „Linki”, w której musimy kliknąć przycisk „Przerwij wszystkie linki”.
Kod VBA
Jeśli nie można aktywować tego dodatku, możesz samodzielnie utworzyć makro. Aby to zrobić, otwórz edytor Visual Basic, naciskając klawisze Alt + F11, i wpisz następujące wiersze w polu wprowadzania kodu.
Sub OdłączKsiążki Robocze()
Przyciemnij WbLinki
Ciemne i tak długie
Select Case MsgBox("Wszystkie odniesienia do innych książek zostaną usunięte z tego pliku, a formuły odnoszące się do innych książek zostaną zastąpione wartościami." & vbCrLf & "Czy na pewno chcesz kontynuować?", 36, "Odłącz?" )
Przypadek 7′ Nie
Exit Sub
End Select
WbLinks = ActiveWorkbook. LinkSources(Type:=xlLinkTypeExcelLinks)
Jeśli nie jest pusty (WbLinks), to
Dla i = 1 To UBound (WbLinks)
ActiveWorkbook.BreakLink Nazwa:=WbLinks(i), Typ:=xlLinkTypeExcelLinks
Następna
więcej
MsgBox „W tym pliku nie ma linków do innych książek.”, 64, „Linki do innych książek”
End If
End Sub
Jak zerwać remisy tylko w wybranym zakresie
Od czasu do czasu liczba linków jest bardzo duża, a użytkownik obawia się, że po usunięciu jednego z nich nie będzie można zwrócić wszystkiego, jeśli niektóre były zbędne. Ale jest to problem, którego łatwo uniknąć. Aby to zrobić, musisz wybrać zakres, w którym chcesz usunąć linki, a następnie je usunąć. Aby to zrobić, musisz wykonać następującą sekwencję czynności:
- Wybierz zestaw danych, który chcesz zmodyfikować.
- Zainstaluj dodatek VBA-Excel, a następnie przejdź do odpowiedniej zakładki.
- Następnie znajdujemy menu „Linki” i klikamy przycisk „Przerwij linki w wybranych zakresach”.
Następnie wszystkie linki w wybranym zestawie komórek zostaną usunięte.
Co zrobić, jeśli więzi nie zostaną zerwane?
Wszystko to brzmi dobrze, ale w praktyce zawsze są jakieś niuanse. Na przykład może dojść do sytuacji, w której remisy nie zostaną zerwane. W takim przypadku nadal pojawia się okno dialogowe z informacją, że nie można automatycznie aktualizować łączy. Co zrobić w tej sytuacji?
- Najpierw musisz sprawdzić, czy w nazwanych zakresach znajdują się jakieś informacje. Aby to zrobić, naciśnij kombinację klawiszy Ctrl + F3 lub otwórz zakładkę „Formuły” – „Menedżer nazw”. Jeśli nazwa pliku jest pełna, wystarczy ją edytować lub całkowicie usunąć. Przed usunięciem nazwanych zakresów należy skopiować plik do innej lokalizacji, aby móc wrócić do oryginalnej wersji w przypadku podjęcia niewłaściwych kroków.
- Jeśli nie możesz rozwiązać problemu przez usunięcie nazw, możesz sprawdzić formatowanie warunkowe. Do komórek w innej tabeli można odwoływać się w regułach formatowania warunkowego. Aby to zrobić, znajdź odpowiedni element na karcie „Strona główna”, a następnie kliknij przycisk „Zarządzanie plikami”.
Zwykle program Excel nie daje możliwości podania adresu innych skoroszytów w formatowaniu warunkowym, ale robisz to, jeśli odwołujesz się do nazwanego zakresu z odwołaniem do innego pliku. Zwykle nawet po usunięciu linku link pozostaje. Nie ma problemu z usunięciem takiego linku, ponieważ link w rzeczywistości nie działa. Dlatego nic złego się nie stanie, jeśli go usuniesz.
Możesz również skorzystać z funkcji „Sprawdzanie danych”, aby sprawdzić, czy nie ma niepotrzebnych linków. Linki zwykle pozostają, jeśli używany jest typ walidacji danych „Lista”. Ale co zrobić, jeśli komórek jest dużo? Czy naprawdę konieczne jest sprawdzanie każdego z nich po kolei? Oczywiście nie. W końcu zajmie to bardzo dużo czasu. Dlatego musisz użyć specjalnego kodu, aby go znacznie zaoszczędzić.
Opcja Jawna
'——————————————————————————————
Autor: The_Prist (Szczerbakow Dmitrij)
'Profesjonalne tworzenie aplikacji dla MS Office o dowolnej złożoności
' Prowadzenie szkoleń na MS Excel
https://www.excel-vba.ru
' [e-mail chroniony]
„WebMoney — R298726502453; Yandex.Pieniądze — 41001332272872
' Zamiar:
'——————————————————————————————
Sub FindErrLink()
'musimy zajrzeć do linku Dane -Zmień do pliku źródłowego
'i umieść tutaj słowa kluczowe małymi literami (część nazwy pliku)
'gwiazdka po prostu zastępuje dowolną liczbę znaków, więc nie musisz się martwić o dokładną nazwę
Const sToFndLink$ = „*sprzedaż 2018*”
Dim rr jako zakres, rc jako zakres, rres jako zakres, s$
'zdefiniuj wszystkie komórki z walidacją danych'
On Error Resume Next
Ustaw rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Jeśli rr jest niczym, to
MsgBox „W aktywnym arkuszu nie ma komórek z walidacją danych”, vbInformation, „www.excel-vba.ru”
Exit Sub
End If
Przy błędzie GoTo 0
'sprawdź każdą komórkę pod kątem linków'
Dla każdego rc In rr
„na wszelki wypadek pomijamy błędy – to też może się zdarzyć”
„ale nasze powiązania muszą być bez nich i na pewno zostaną odnalezione”
s = «»
On Error Resume Next
s = rc.Walidacja.Wzór1
Przy błędzie GoTo 0
„znaleziono – zbieramy wszystko w osobny asortyment
Jeśli LCase lubi sToFndLink, to
Jeśli rres jest niczym, to
Ustaw rres = rc
więcej
Ustaw rres = Union(rc, rres)
End If
End If
Następna
'jeśli istnieje połączenie, zaznacz wszystkie komórki z takimi kontrolami danych
Jeśli nie rres to nic, to
rres.Wybierz
' rres.Interior.Color = vbRed 'jeśli chcesz wyróżnić kolorem
End If
End Sub
Konieczne jest wykonanie standardowego modułu w edytorze makr, a następnie wstawienie tam tego tekstu. Następnie wywołaj okno makra za pomocą kombinacji klawiszy Alt + F8, a następnie wybierz nasze makro i kliknij przycisk „Uruchom”. Podczas korzystania z tego kodu należy pamiętać o kilku rzeczach:
- Zanim zaczniesz szukać linku, który nie jest już istotny, musisz najpierw określić, jak wygląda link, przez który został utworzony. Aby to zrobić, przejdź do menu „Dane” i znajdź tam element „Zmień linki”. Następnie musisz spojrzeć na nazwę pliku i określić ją w cudzysłowie. Na przykład tak: Const sToFndLink$ = „*sprzedaż 2018*”
- Możliwe jest wpisanie nazwy nie w całości, ale po prostu zastąp niepotrzebne znaki gwiazdką. A w cudzysłowie napisz nazwę pliku małymi literami. W takim przypadku Excel znajdzie na końcu wszystkie pliki zawierające taki ciąg.
- Ten kod jest w stanie sprawdzić tylko linki w arkuszu, który jest aktualnie aktywny.
- Za pomocą tego makra możesz wybrać tylko znalezione komórki. Musisz wszystko usunąć ręcznie. To plus, bo wszystko można jeszcze raz sprawdzić.
- Możesz także wyróżnić komórki specjalnym kolorem. Aby to zrobić, usuń apostrof przed tym wierszem. rres.Interior.Color = vbCzerwony
Zwykle po wykonaniu kroków opisanych w powyższych instrukcjach nie powinno być więcej niepotrzebnych połączeń. Ale jeśli niektóre z nich są w dokumencie i nie możesz ich usunąć z tego czy innego powodu (typowym przykładem jest bezpieczeństwo danych w arkuszu), możesz użyć innej sekwencji działań. Niniejsza instrukcja dotyczy tylko wersji 2007 i wyższych.
- Tworzymy kopię zapasową dokumentu.
- Otwórz ten dokument za pomocą archiwizatora. Możesz użyć dowolnego, który obsługuje format ZIP, ale WinRar również będzie działał, podobnie jak ten wbudowany w Windows.
- W wyświetlonym archiwum musisz znaleźć folder xl, a następnie otworzyć externalLinks.
- Ten folder zawiera wszystkie linki zewnętrzne, z których każdy odpowiada plikowi w postaci externalLink1.xml. Wszystkie są tylko ponumerowane, dlatego użytkownik nie ma możliwości zrozumienia, jaki to jest rodzaj połączenia. Aby zrozumieć, jakiego rodzaju połączenie, musisz otworzyć folder _rels i tam go obejrzeć.
- Następnie usuwamy wszystkie lub określone linki, na podstawie tego, czego dowiadujemy się z pliku externalLinkX.xml.rels.
- Następnie otwieramy nasz plik za pomocą Excela. Pojawi się informacja o błędzie, takim jak „Błąd w części treści w księdze”. Wyrażamy zgodę. Następnie pojawi się kolejne okno dialogowe. Zamykamy to.
Następnie wszystkie linki powinny zostać usunięte.