Contenuti
La comunicazione è una funzionalità molto utile in Excel. Dopotutto, molto spesso gli utenti devono utilizzare le informazioni di altri file. Ma in alcune situazioni, possono fare più male che bene. Dopotutto, ad esempio, se invii questi file per posta, i collegamenti non funzionano. Oggi parleremo più in dettaglio di cosa fare per evitare un problema del genere.
Cosa sono le relazioni in Excel
Le relazioni in Excel sono molto spesso utilizzate insieme a funzioni come VPRper ottenere informazioni da un'altra cartella di lavoro. Può assumere la forma di un collegamento speciale che contiene l'indirizzo non solo della cella, ma anche del libro in cui si trovano i dati. Di conseguenza, un tale collegamento assomiglia a questo: = CERCA.VERT(A2;'[Vendite 2018.xlsx]Rapporto'!$A:$F;4;0). Oppure, per una rappresentazione più semplice, rappresentare l'indirizzo nella forma seguente: ='[Vendite 2018.xlsx]Rapporto'!$A1. Analizziamo ciascuno degli elementi di collegamento di questo tipo:
- [Vendite 2018.xlsx]. Questo frammento contiene un collegamento al file da cui si desidera ottenere informazioni. È anche chiamata la fonte.
- Foto. Abbiamo usato il seguente nome, ma questo non è il nome che dovrebbe essere. Questo blocco contiene il nome del foglio in cui è necessario trovare le informazioni.
- $A:$F e $A1 – l'indirizzo di una cella o intervallo contenente dati contenuti in questo documento.
In realtà, il processo di creazione di un collegamento a un documento esterno è chiamato collegamento. Dopo aver registrato l'indirizzo della cella contenuta in un altro file, il contenuto della scheda "Dati" cambia. Vale a dire, diventa attivo il pulsante "Cambia connessioni", con l'aiuto del quale l'utente può modificare le connessioni esistenti.
L'essenza del problema
Di norma, non sorgono ulteriori difficoltà per utilizzare i collegamenti. Anche se si verifica una situazione in cui le celle cambiano, tutti i collegamenti vengono aggiornati automaticamente. Ma se rinomini già la cartella di lavoro stessa o la sposti in un indirizzo diverso, Excel diventa impotente. Pertanto, produce il seguente messaggio.
Qui, l'utente ha due possibili opzioni su come agire in questa situazione. Può fare clic su "Continua" e quindi le modifiche non verranno aggiornate, oppure può fare clic sul pulsante "Cambia associazioni", con il quale può aggiornarle manualmente. Dopo aver cliccato su questo pulsante, apparirà un'ulteriore finestra in cui sarà possibile modificare i collegamenti, indicando dove si trova al momento il file corretto e come si chiama.
Inoltre, puoi modificare i collegamenti tramite il pulsante corrispondente situato nella scheda "Dati". L'utente può anche scoprire che la connessione è interrotta dall'errore #LINK, che appare quando Excel non può accedere alle informazioni che si trovano a un indirizzo specifico a causa del fatto che l'indirizzo stesso non è valido.
Come scollegare in excel
Uno dei metodi più semplici per risolvere la situazione sopra descritta nel caso in cui non sia possibile aggiornare personalmente la posizione del file collegato è eliminare il collegamento stesso. Ciò è particolarmente facile se il documento contiene un solo collegamento. Per fare ciò, è necessario eseguire la seguente sequenza di passaggi:
- Apri il menu "Dati".
- Troviamo la sezione "Connessioni" e lì - l'opzione "Cambia connessioni".
- Successivamente, fai clic su "Scollega".
Se intendi spedire questo libro a un'altra persona, ti consigliamo vivamente di farlo in anticipo. Dopotutto, dopo aver eliminato i collegamenti, tutti i valori contenuti in un altro documento verranno automaticamente caricati nel file, utilizzati nelle formule, e al posto dell'indirizzo della cella, le informazioni nelle celle corrispondenti verranno semplicemente trasformate in valori .
Come scollegare tutti i libri
Ma se il numero di collegamenti diventa troppo grande, l'eliminazione manuale degli stessi può richiedere molto tempo. Per risolvere questo problema in una volta sola, puoi utilizzare una macro speciale. È nel componente aggiuntivo VBA-Excel. Devi attivarlo e andare alla scheda con lo stesso nome. Ci sarà una sezione "Collegamenti", in cui dobbiamo fare clic sul pulsante "Interrompi tutti i collegamenti".
Codice VBA
Se non è possibile attivare questo componente aggiuntivo, puoi creare tu stesso una macro. Per fare ciò, apri l'editor di Visual Basic premendo i tasti Alt + F11 e scrivi le seguenti righe nel campo di immissione del codice.
Sub ScollegaWorkBooks()
Dim WbLinks
Dim e quanto a lungo
Selezionare Case MsgBox("Tutti i riferimenti ad altri libri verranno rimossi da questo file e le formule che fanno riferimento ad altri libri verranno sostituite con valori." & vbCrLf & "Sei sicuro di voler continuare?", 36, "Scollegare?" )
Caso 7′ n
Exit Sub
End Select
WbLinks = ActiveWorkbook.LinkSources(Tipo:=xlLinkTypeExcelLinks)
Se non è vuoto (WbLinks), allora
For i = 1 A UBound(WbLinks)
ActiveWorkbook.BreakLink Nome:=WbLinks(i), Tipo:=xlLinkTypeExcelLinks
Avanti
altro
MsgBox “Non ci sono collegamenti ad altri libri in questo file.”, 64, “Link ad altri libri”
End If
End Sub
Come rompere i pareggi solo nell'intervallo selezionato
Di tanto in tanto, il numero di collegamenti è molto elevato e l'utente teme che, dopo averne eliminato uno, non sarà possibile restituire tutto se alcuni fossero superflui. Ma questo è un problema facile da evitare. Per fare ciò, è necessario selezionare l'intervallo in cui eliminare i collegamenti, quindi eliminarli. Per fare ciò, è necessario eseguire la seguente sequenza di azioni:
- Seleziona il set di dati che deve essere modificato.
- Installa il componente aggiuntivo VBA-Excel, quindi vai alla scheda appropriata.
- Successivamente, troviamo il menu "Collegamenti" e facciamo clic sul pulsante "Interrompi collegamenti negli intervalli selezionati".
Successivamente, tutti i collegamenti nel set di celle selezionato verranno eliminati.
Cosa fare se i legami non si rompono
Tutto quanto sopra suona bene, ma in pratica ci sono sempre alcune sfumature. Ad esempio, potrebbe esserci una situazione in cui i pareggi non vengono rotti. In questo caso, viene comunque visualizzata una finestra di dialogo in cui si afferma che non è possibile aggiornare automaticamente i collegamenti. Cosa fare in questa situazione?
- Innanzitutto, è necessario verificare se sono contenute informazioni negli intervalli denominati. Per fare ciò, premi la combinazione di tasti Ctrl + F3 o apri la scheda "Formule" - "Gestione nomi". Se il nome del file è completo, devi solo modificarlo o rimuoverlo del tutto. Prima di eliminare gli intervalli denominati, è necessario copiare il file in un'altra posizione in modo da poter tornare alla versione originale se sono stati eseguiti i passaggi sbagliati.
- Se non riesci a risolvere il problema rimuovendo i nomi, puoi controllare la formattazione condizionale. È possibile fare riferimento alle celle in un'altra tabella nelle regole di formattazione condizionale. Per fare ciò, trova la voce corrispondente nella scheda "Home", quindi fai clic sul pulsante "Gestione file".
Normalmente, Excel non ti dà la possibilità di fornire l'indirizzo di altre cartelle di lavoro in formattazione condizionale, ma lo fai se fai riferimento a un intervallo denominato con un riferimento a un altro file. Di solito, anche dopo la rimozione del collegamento, il collegamento rimane. Non ci sono problemi nella rimozione di tale collegamento, perché il collegamento in realtà non funziona. Pertanto, non accadrà nulla di male se lo rimuovi.
Puoi anche utilizzare la funzione "Controllo dati" per scoprire se ci sono collegamenti non necessari. I collegamenti di solito rimangono se viene utilizzato il tipo di convalida dei dati "Elenco". Ma cosa fare se ci sono molte celle? È davvero necessario controllare ciascuno di essi in sequenza? Ovviamente no. Dopotutto, ci vorrà molto tempo. Pertanto, è necessario utilizzare un codice speciale per salvarlo in modo significativo.
Opzione esplicita
'——————————————————————————————
' Autore: The_Prist (Shcherbakov Dmitry)
' Sviluppo professionale di applicazioni per MS Office di qualsiasi complessità
' Conduzione di corsi di formazione su MS Excel
' https://www.excel-vba.ru
' [email protetta]
'WebMoney—R298726502453; Yandex.Denaro — 41001332272872
' Scopo:
'——————————————————————————————
Sub TrovaErrLink()
'dobbiamo cercare nel collegamento Data -Change links al file sorgente
'e metti qui le parole chiave in minuscolo (parte del nome del file)
'asterisco sostituisce solo un numero qualsiasi di caratteri, quindi non devi preoccuparti del nome esatto
Const sToFndLink$ = “*vendite 2018*”
Dim rr come intervallo, rc come intervallo, rres come intervallo, s$
'definisci tutte le celle con la convalida dei dati
On Error Resume Next
Imposta rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Se rr non è niente allora
MsgBox "Non ci sono celle con convalida dei dati sul foglio attivo", vbInformation, "www.excel-vba.ru"
Exit Sub
End If
On Error GoTo 0
'controlla ogni cella per i collegamenti
Per ogni rc In rr
'per ogni evenienza, saltiamo gli errori, anche questo può accadere
'ma le nostre connessioni devono essere prive di loro e saranno sicuramente trovate
s = «»
On Error Resume Next
s = rc.Validazione.Formula1
On Error GoTo 0
'trovato: raccogliamo tutto in un intervallo separato
Se a LCase piace sToFndLink allora
Se rres non è niente allora
Imposta rres = rc
altro
Imposta rres = Unione(rc, rres)
End If
End If
Avanti
'se c'è una connessione, seleziona tutte le celle con tali controlli dei dati
Se non rres non è niente allora
rres.Seleziona
' rres.Interior.Color = vbRed 'se vuoi evidenziare con il colore
End If
End Sub
È necessario creare un modulo standard nell'editor di macro e quindi inserire questo testo lì. Successivamente, chiama la finestra della macro usando la combinazione di tasti Alt + F8, quindi seleziona la nostra macro e fai clic sul pulsante "Esegui". Ci sono alcune cose da tenere a mente quando si utilizza questo codice:
- Prima di cercare un collegamento che non è più rilevante, devi prima determinare l'aspetto del collegamento attraverso il quale è stato creato. Per fare ciò, vai al menu "Dati" e trova lì la voce "Cambia collegamenti". Dopodiché, devi guardare il nome del file e specificarlo tra virgolette. Ad esempio, in questo modo: Const sToFndLink$ = “*vendite 2018*”
- È possibile scrivere il nome non per intero, ma semplicemente sostituire i caratteri non necessari con un asterisco. E tra virgolette, scrivi il nome del file in lettere minuscole. In questo caso, Excel troverà tutti i file che contengono tale stringa alla fine.
- Questo codice è in grado di controllare solo i collegamenti nel foglio attualmente attivo.
- Con questa macro, puoi selezionare solo le celle che ha trovato. Devi cancellare tutto manualmente. Questo è un vantaggio, perché puoi ricontrollare tutto di nuovo.
- Puoi anche evidenziare le celle con un colore speciale. Per fare ciò, rimuovi l'apostrofo prima di questa riga. rres.Interior.Color = vbRed
Di solito, dopo aver completato i passaggi descritti nelle istruzioni precedenti, non dovrebbero esserci più connessioni non necessarie. Ma se ce ne sono alcuni nel documento e non sei in grado di rimuoverli per un motivo o per l'altro (un tipico esempio è la sicurezza dei dati in un foglio), puoi utilizzare una sequenza di azioni diversa. Questa istruzione è valida solo per le versioni 2007 e successive.
- Creiamo una copia di backup del documento.
- Apri questo documento usando l'archiviatore. Puoi usare qualsiasi che supporti il formato ZIP, ma funzionerà anche WinRar, così come quello integrato in Windows.
- Nell'archivio che appare, devi trovare la cartella xl, quindi aprire externalLinks.
- Questa cartella contiene tutti i collegamenti esterni, ognuno dei quali corrisponde a un file del formato externalLink1.xml. Tutti sono solo numerati, e quindi l'utente non ha la possibilità di capire di che tipo di connessione si tratta. Per capire che tipo di connessione, devi aprire la cartella _rels e guardarla lì.
- Successivamente, rimuoviamo tutti o specifici collegamenti, in base a ciò che apprendiamo nel file externalLinkX.xml.rels.
- Successivamente, apriamo il nostro file utilizzando Excel. Ci saranno informazioni su un errore come "Errore in una parte del contenuto nel libro". Diamo il consenso. Successivamente, apparirà un'altra finestra di dialogo. Lo chiudiamo.
Dopodiché, tutti i collegamenti dovrebbero essere rimossi.