Contidos
A comunicación é unha característica moi útil en Excel. Despois de todo, moitas veces os usuarios teñen que usar información doutros ficheiros. Pero nalgunhas situacións, poden facer máis mal que ben. Despois de todo, por exemplo, se envías estes ficheiros por correo, as ligazóns non funcionan. Hoxe falaremos con máis detalle sobre que facer para evitar tal problema.
Que son as relacións en Excel
As relacións en Excel úsanse con moita frecuencia en conxunto con funcións como VPRpara obter información doutro libro de traballo. Pode tomar a forma dunha ligazón especial que contén o enderezo non só da cela, senón tamén do libro no que se atopan os datos. Como resultado, tal ligazón ten un aspecto similar ao seguinte: =BUSCARV(A2;'[Informe de vendas 2018.xlsx]'!$A:$F;4;0). Ou, para unha representación máis sinxela, represente o enderezo na seguinte forma: ='[Informe de vendas 2018.xlsx]'!$A1. Analizemos cada un dos elementos de ligazón deste tipo:
- [Ventas 2018.xlsx]. Este fragmento contén unha ligazón ao ficheiro do que desexa obter información. Tamén se lle chama fonte.
- fotos. Usamos o seguinte nome, pero este non é o que debería ser. Este bloque contén o nome da folla na que precisa buscar información.
- $A:$F e $A1 – o enderezo dunha cela ou intervalo que contén datos contidos neste documento.
En realidade, o proceso de creación dunha ligazón a un documento externo chámase ligazón. Despois de rexistrar o enderezo da cela contida noutro ficheiro, o contido da pestana "Datos" cambia. É dicir, o botón "Cambiar conexións" faise activo, coa axuda do cal o usuario pode editar as conexións existentes.
A esencia do problema
Como regra xeral, non hai dificultades adicionais para usar as ligazóns. Aínda que se produza unha situación na que as celas cambien, todas as ligazóns actualízanse automaticamente. Pero se xa cambias o nome do propio libro de traballo ou o moves a un enderezo diferente, Excel queda impotente. Polo tanto, produce a seguinte mensaxe.
Aquí, o usuario ten dúas opcións posibles sobre como actuar nesta situación. Pode facer clic en "Continuar" e despois os cambios non se actualizarán, ou pode facer clic no botón "Cambiar asociacións", co que pode actualizalos manualmente. Despois de facer clic neste botón, aparecerá unha ventá adicional na que será posible cambiar as ligazóns, indicando onde se atopa o ficheiro correcto neste momento e como se chama.
Ademais, pode editar as ligazóns a través do botón correspondente situado na pestana "Datos". O usuario tamén pode descubrir que a conexión está rota polo erro #LINK, que aparece cando Excel non pode acceder á información situada nun enderezo específico debido ao feito de que o propio enderezo non é válido.
Como desvincular en excel
Un dos métodos máis sinxelos para resolver a situación descrita anteriormente no caso de que non poida actualizar vostede mesmo a localización do ficheiro ligado é eliminar a propia ligazón. Isto é especialmente sinxelo se o documento contén só unha ligazón. Para iso, debes realizar a seguinte secuencia de pasos:
- Abre o menú "Datos".
- Atopamos a sección "Conexións" e alí - a opción "Cambiar conexións".
- Despois diso, fai clic en "Desvincular".
Se queres enviar este libro por correo a outra persoa, é moi recomendable que o fagas con antelación. Despois de todo, despois de eliminar as ligazóns, todos os valores contidos noutro documento cargaranse automaticamente no ficheiro, empregaranse en fórmulas e, en lugar do enderezo da cela, a información das celas correspondentes simplemente transformarase en valores. .
Como desvincular todos os libros
Pero se o número de ligazóns se fai demasiado grande, eliminalos manualmente pode levar moito tempo. Para resolver este problema dunha soa vez, pode usar unha macro especial. Está no complemento VBA-Excel. Debe activalo e ir á pestana co mesmo nome. Haberá unha sección "Ligazóns", na que temos que facer clic no botón "Romper todas as ligazóns".
Código VBA
Se non é posible activar este complemento, pode crear vostede mesmo unha macro. Para iso, abra o editor de Visual Basic premendo as teclas Alt + F11 e escriba as seguintes liñas no campo de entrada de código.
Sub UnlinkWorkBooks()
Dim WbLinks
Dim e As Long
Seleccione Case MsgBox ("Todas as referencias a outros libros eliminaranse deste ficheiro e as fórmulas que se refiran a outros libros substituiranse por valores." & vbCrLf & "Estás seguro de que queres continuar?", 36, "Desvincular?" )
Caso 7′ No
Saír Sub
Finalizar a selección
WbLinks = ActiveWorkbook.LinkSources (Tipo:=xlLinkTypeExcelLinks)
Se non está baleiro (WbLinks), entón
Para i = 1 Para UBound(WbLinks)
ActiveWorkbook.BreakLink Nome:=WbLinks(i), Tipo:=xlLinkTypeExcelLinks
seguinte
outro
MsgBox “Non hai ligazóns a outros libros neste ficheiro.”, 64, “Ligazóns a outros libros”
End If
End Sub
Como romper os lazos só no rango seleccionado
De cando en vez, o número de ligazóns é moi grande, e o usuario ten medo de que despois de eliminar un deles, non sexa posible devolver todo se algunha era superflua. Pero este é un problema que é fácil de evitar. Para iso, cómpre seleccionar o intervalo no que eliminar as ligazóns e, a continuación, eliminalas. Para iso, debes realizar a seguinte secuencia de accións:
- Seleccione o conxunto de datos que precisa modificar.
- Instale o complemento VBA-Excel e, a continuación, vaia á pestana correspondente.
- A continuación, atopamos o menú "Ligazóns" e prememos no botón "Romper ligazóns nos intervalos seleccionados".
Despois diso, eliminaranse todas as ligazóns do conxunto de celas seleccionado.
Que facer se non se rompen os lazos
Todo o anterior soa ben, pero na práctica sempre hai algúns matices. Por exemplo, pode haber unha situación na que non se rompan os lazos. Neste caso, aínda aparece un cadro de diálogo indicando que non é posible actualizar automaticamente as ligazóns. Que facer nesta situación?
- En primeiro lugar, cómpre comprobar se hai información contida nos intervalos nomeados. Para iso, prema a combinación de teclas Ctrl + F3 ou abra a pestana "Fórmulas" - "Xestor de nomes". Se o nome do ficheiro está completo, só tes que editalo ou eliminalo por completo. Antes de eliminar intervalos con nome, cómpre copiar o ficheiro noutro lugar para poder volver á versión orixinal se se tomaron os pasos incorrectos.
- Se non podes resolver o problema eliminando nomes, podes comprobar o formato condicional. Pódese facer referencia ás celas doutra táboa en regras de formato condicional. Para iso, busque o elemento correspondente na pestana "Inicio" e, a continuación, faga clic no botón "Xestión de ficheiros".
Normalmente, Excel non lle dá a posibilidade de dar o enderezo doutros libros de traballo en formato condicional, pero si se refire a un intervalo nomeado cunha referencia a outro ficheiro. Normalmente, aínda que se elimine a ligazón, a ligazón permanece. Non hai ningún problema en eliminar tal ligazón, porque a ligazón non funciona. Polo tanto, non pasará nada malo se o eliminas.
Tamén pode usar a función "Comprobación de datos" para descubrir se hai ligazóns innecesarias. As ligazóns adoitan permanecer se se utiliza o tipo de validación de datos "Lista". Pero que facer se hai moitas células? É realmente necesario comprobar cada un deles secuencialmente? Por suposto que non. Despois de todo, vai levar moito tempo. Polo tanto, cómpre usar un código especial para gardalo significativamente.
Opción Explícita
'———————————————————————————————
Autor: The_Prist (Shcherbakov Dmitry)
' Desenvolvemento profesional de aplicacións para MS Office de calquera complexidade
' Realización de formacións en MS Excel
https://www.excel-vba.ru
' [correo electrónico protexido]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' Finalidade:
'———————————————————————————————
Sub FindErrLink()
'Necesitamos buscar na ligazón Datos -Cambiar ligazóns ao ficheiro fonte
'e pon aquí as palabras clave en minúsculas (parte do nome do ficheiro)
O asterisco só substitúe calquera número de caracteres para que non teñas que preocuparte polo nome exacto
Const sToFndLink$ = "*vendas 2018*"
Dim rr como intervalo, rc como intervalo, rres como intervalo, s$
'Defina todas as celas con validación de datos
En Error Resume Next
Establecer rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Se rr é nada, entón
MsgBox "Non hai celas con validación de datos na folla activa", vbInformation, "www.excel-vba.ru"
Saír Sub
End If
En caso de erro GoTo 0
'comproba cada cela para as ligazóns
Para cada rc In rr
'por se acaso, saltamos erros; isto tamén pode ocorrer
'pero as nosas conexións deben estar sen elas e definitivamente atoparanse
s = «»
En Error Resume Next
s = rc.Validación.Fórmula1
En caso de erro GoTo 0
'atopado: recollemos todo nun rango separado
Se LCase (s) como sToFndLink, entón
Se rres é nada, entón
Establecer rres = rc
outro
Establecer rres = Unión (rc, rres)
End If
End If
seguinte
'se hai unha conexión, seleccione todas as celas con tales comprobacións de datos
Se non é nada, entón
rres.Seleccionar
' rres.Interior.Color = vbRed 'se queres resaltar con cor
End If
End Sub
É necesario facer un módulo estándar no editor de macros, e despois inserir alí este texto. Despois diso, chame á xanela da macro usando a combinación de teclas Alt + F8, seleccione a nosa macro e prema no botón "Executar". Hai que ter en conta algunhas cousas ao usar este código:
- Antes de buscar unha ligazón que xa non é relevante, primeiro debes determinar como é a ligazón a través da cal se crea. Para iso, vai ao menú "Datos" e busca alí o elemento "Cambiar ligazóns". Despois diso, cómpre mirar o nome do ficheiro e especificalo entre comiñas. Por exemplo, así: Const sToFndLink$ = "*vendas 2018*"
- É posible escribir o nome non completo, pero simplemente substituír os caracteres innecesarios cun asterisco. E entre comiñas, escriba o nome do ficheiro en minúsculas. Neste caso, Excel atopará todos os ficheiros que conteñan esa cadea ao final.
- Este código só pode comprobar as ligazóns na folla que está activa actualmente.
- Con esta macro, só pode seleccionar as celas que atopou. Tes que borrar todo manualmente. Esta é unha vantaxe, porque podes comprobar todo de novo.
- Tamén podes facer que as celas destaquen nunha cor especial. Para iso, elimina o apóstrofo antes desta liña. rres.Interior.Color = vbVermello
Normalmente, despois de completar os pasos descritos nas instrucións anteriores, non debería haber máis conexións innecesarias. Pero se hai algúns deles no documento e non podes eliminalos por un motivo ou outro (un exemplo típico é a seguridade dos datos nunha folla), podes usar unha secuencia de accións diferente. Esta instrución só é válida para as versións 2007 e posteriores.
- Creamos unha copia de seguridade do documento.
- Abre este documento usando o arquivo. Podes usar calquera que admita o formato ZIP, pero WinRar tamén funcionará, así como o integrado en Windows.
- No arquivo que aparece, cómpre atopar o cartafol xl e, a continuación, abrir externalLinks.
- Este cartafol contén todas as ligazóns externas, cada unha das cales corresponde a un ficheiro do formato externalLink1.xml. Todos eles só están numerados e, polo tanto, o usuario non ten a oportunidade de entender que tipo de conexión se trata. Para entender que tipo de conexión, cómpre abrir o cartafol _rels e miralo alí.
- Despois diso, eliminamos todas as ligazóns ou específicas, en función do que aprendemos no ficheiro externalLinkX.xml.rels.
- Despois diso, abrimos o noso ficheiro usando Excel. Haberá información sobre un erro como "Erro en parte do contido do libro". Damos o consentimento. Despois diso, aparecerá outro diálogo. Pechamos.
Despois diso, todas as ligazóns deben ser eliminadas.