Περιεχόμενα
Η επικοινωνία είναι μια πολύ χρήσιμη δυνατότητα στο Excel. Εξάλλου, πολύ συχνά οι χρήστες πρέπει να χρησιμοποιούν πληροφορίες από άλλα αρχεία. Αλλά σε ορισμένες περιπτώσεις, μπορούν να κάνουν περισσότερο κακό παρά καλό. Εξάλλου, για παράδειγμα, εάν στείλετε αυτά τα αρχεία μέσω ταχυδρομείου, οι σύνδεσμοι δεν λειτουργούν. Σήμερα θα μιλήσουμε λεπτομερέστερα για το τι πρέπει να κάνετε για να αποφύγετε ένα τέτοιο πρόβλημα.
Τι είναι οι σχέσεις στο Excel
Οι σχέσεις στο Excel χρησιμοποιούνται πολύ συχνά σε συνδυασμό με λειτουργίες όπως π.χ VPRγια να λάβετε πληροφορίες από άλλο βιβλίο εργασίας. Μπορεί να λάβει τη μορφή ενός ειδικού συνδέσμου που περιέχει τη διεύθυνση όχι μόνο του κελιού, αλλά και του βιβλίου στο οποίο βρίσκονται τα δεδομένα. Ως αποτέλεσμα, ένας τέτοιος σύνδεσμος μοιάζει κάπως έτσι: =VLOOKUP(A2;'[Πωλήσεις 2018.xlsx]Αναφορά'!$A:$F;4;0). Ή, για απλούστερη αναπαράσταση, αντιπροσωπεύστε τη διεύθυνση με την ακόλουθη μορφή: ='[Πωλήσεις 2018.xlsx]Αναφορά'!$A1. Ας αναλύσουμε καθένα από τα στοιχεία συνδέσμου αυτού του τύπου:
- [Πωλήσεις 2018.xlsx]. Αυτό το τμήμα περιέχει έναν σύνδεσμο προς το αρχείο από το οποίο θέλετε να λάβετε πληροφορίες. Λέγεται και πηγή.
- Φωτογραφίες. Χρησιμοποιήσαμε το παρακάτω όνομα, αλλά αυτό δεν είναι το όνομα που θα έπρεπε. Αυτό το μπλοκ περιέχει το όνομα του φύλλου στο οποίο πρέπει να βρείτε πληροφορίες.
- $A:$F και $A1 - τη διεύθυνση ενός κελιού ή μιας περιοχής που περιέχει δεδομένα που περιέχονται σε αυτό το έγγραφο.
Στην πραγματικότητα, η διαδικασία δημιουργίας ενός συνδέσμου προς ένα εξωτερικό έγγραφο ονομάζεται σύνδεση. Αφού καταχωρίσουμε τη διεύθυνση του κελιού που περιέχεται σε άλλο αρχείο, τα περιεχόμενα της καρτέλας «Δεδομένα» αλλάζουν. Δηλαδή, ενεργοποιείται το κουμπί «Αλλαγή συνδέσεων», με τη βοήθεια του οποίου ο χρήστης μπορεί να επεξεργαστεί τις υπάρχουσες συνδέσεις.
Η ουσία του προβλήματος
Κατά κανόνα, δεν προκύπτουν πρόσθετες δυσκολίες για τη χρήση συνδέσμων. Ακόμα κι αν προκύψει μια κατάσταση στην οποία τα κελιά αλλάζουν, τότε όλοι οι σύνδεσμοι ενημερώνονται αυτόματα. Αλλά εάν μετονομάσετε ήδη το ίδιο το βιβλίο εργασίας ή το μετακινήσετε σε διαφορετική διεύθυνση, το Excel γίνεται ανίσχυρο. Επομένως, παράγει το ακόλουθο μήνυμα.
Εδώ, ο χρήστης έχει δύο πιθανές επιλογές για το πώς να ενεργήσει σε αυτήν την κατάσταση. Μπορεί να κάνει κλικ στο «Συνέχεια» και μετά οι αλλαγές δεν θα ενημερωθούν ή μπορεί να κάνει κλικ στο κουμπί «Αλλαγή συσχετίσεων», με το οποίο μπορεί να τις ενημερώσει χειροκίνητα. Αφού κάνουμε κλικ σε αυτό το κουμπί, θα εμφανιστεί ένα πρόσθετο παράθυρο στο οποίο θα είναι δυνατή η αλλαγή των συνδέσμων, υποδεικνύοντας πού βρίσκεται το σωστό αρχείο αυτή τη στιγμή και πώς ονομάζεται.
Επιπλέον, μπορείτε να επεξεργαστείτε συνδέσμους μέσω του αντίστοιχου κουμπιού που βρίσκεται στην καρτέλα «Δεδομένα». Ο χρήστης μπορεί επίσης να ανακαλύψει ότι η σύνδεση έχει διακοπεί από το σφάλμα #LINK, το οποίο εμφανίζεται όταν το Excel δεν μπορεί να έχει πρόσβαση σε πληροφορίες που βρίσκονται σε μια συγκεκριμένη διεύθυνση λόγω του γεγονότος ότι η ίδια η διεύθυνση δεν είναι έγκυρη.
Πώς να αποσυνδέσετε στο excel
Μία από τις απλούστερες μεθόδους για να λύσετε την κατάσταση που περιγράφεται παραπάνω σε περίπτωση που δεν μπορείτε να ενημερώσετε μόνοι σας τη θέση του συνδεδεμένου αρχείου είναι να διαγράψετε τον ίδιο τον σύνδεσμο. Αυτό είναι ιδιαίτερα εύκολο να γίνει εάν το έγγραφο περιέχει μόνο έναν σύνδεσμο. Για να το κάνετε αυτό, πρέπει να εκτελέσετε την ακόλουθη σειρά βημάτων:
- Ανοίξτε το μενού "Δεδομένα".
- Βρίσκουμε την ενότητα "Συνδέσεις" και εκεί - την επιλογή "Αλλαγή συνδέσεων".
- Μετά από αυτό, κάντε κλικ στο "Αποσύνδεση".
Εάν σκοπεύετε να ταχυδρομήσετε αυτό το βιβλίο σε άλλο άτομο, συνιστάται ιδιαίτερα να το κάνετε εκ των προτέρων. Εξάλλου, μετά τη διαγραφή των συνδέσμων, όλες οι τιμές που περιέχονται σε άλλο έγγραφο θα φορτωθούν αυτόματα στο αρχείο, θα χρησιμοποιηθούν σε τύπους και αντί για τη διεύθυνση κελιού, οι πληροφορίες στα αντίστοιχα κελιά απλώς θα μετατραπούν σε τιμές .
Πώς να αποσυνδέσετε όλα τα βιβλία
Αλλά αν ο αριθμός των συνδέσμων γίνει πολύ μεγάλος, η μη αυτόματη διαγραφή τους μπορεί να διαρκέσει πολύ. Για να λύσετε αυτό το πρόβλημα με μία κίνηση, μπορείτε να χρησιμοποιήσετε μια ειδική μακροεντολή. Είναι στο πρόσθετο VBA-Excel. Πρέπει να το ενεργοποιήσετε και να μεταβείτε στην καρτέλα με το ίδιο όνομα. Θα υπάρχει μια ενότητα "Σύνδεσμοι", στην οποία πρέπει να κάνουμε κλικ στο κουμπί "Διάσπαση όλων των συνδέσμων".
Κωδικός VBA
Εάν δεν είναι δυνατή η ενεργοποίηση αυτού του πρόσθετου, μπορείτε να δημιουργήσετε μόνοι σας μια μακροεντολή. Για να το κάνετε αυτό, ανοίξτε το πρόγραμμα επεξεργασίας της Visual Basic πατώντας τα πλήκτρα Alt + F11 και γράψτε τις ακόλουθες γραμμές στο πεδίο εισαγωγής κώδικα.
Sub UnlinkWorkBooks()
Dim WbLinks
Dim και As Long
Επιλέξτε Case MsgBox("Όλες οι αναφορές σε άλλα βιβλία θα αφαιρεθούν από αυτό το αρχείο και οι τύποι που αναφέρονται σε άλλα βιβλία θα αντικατασταθούν με τιμές." & vbCrLf & "Είστε βέβαιοι ότι θέλετε να συνεχίσετε;", 36, "Αποσύνδεση;" )
Υπόθεση 7′ Αρ
Έξοδος Sub
Επιλέξτε Τερματισμός
WbLinks = ActiveWorkbook.LinkSources(Τύπος:=xlLinkTypeExcelLinks)
Αν δεν είναι κενό (WbLinks) Τότε
Για i = 1 To UBound (WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks
Επόμενο
αλλιώς
MsgBox "Δεν υπάρχουν σύνδεσμοι προς άλλα βιβλία σε αυτό το αρχείο.", 64, "Σύνδεσμοι προς άλλα βιβλία"
End If
Sub End
Πώς να σπάσετε τους δεσμούς μόνο στο επιλεγμένο εύρος
Από καιρό σε καιρό, ο αριθμός των συνδέσμων είναι πολύ μεγάλος και ο χρήστης φοβάται ότι μετά τη διαγραφή ενός από αυτούς, δεν θα είναι δυνατό να επιστρέψει τα πάντα πίσω, εάν κάποιοι ήταν περιττοί. Αλλά αυτό είναι ένα πρόβλημα που είναι εύκολο να αποφευχθεί. Για να το κάνετε αυτό, πρέπει να επιλέξετε το εύρος στο οποίο θα διαγράψετε συνδέσμους και, στη συνέχεια, να τους διαγράψετε. Για να το κάνετε αυτό, πρέπει να εκτελέσετε την ακόλουθη σειρά ενεργειών:
- Επιλέξτε το σύνολο δεδομένων που πρέπει να τροποποιηθεί.
- Εγκαταστήστε το πρόσθετο VBA-Excel και, στη συνέχεια, μεταβείτε στην κατάλληλη καρτέλα.
- Στη συνέχεια, βρίσκουμε το μενού «Σύνδεσμοι» και κάνουμε κλικ στο κουμπί «Διακοπή συνδέσμων στις επιλεγμένες περιοχές».
Μετά από αυτό, όλοι οι σύνδεσμοι στο επιλεγμένο σύνολο κελιών θα διαγραφούν.
Τι να κάνετε αν δεν σπάσουν οι δεσμοί
Όλα τα παραπάνω ακούγονται καλά, αλλά στην πράξη υπάρχουν πάντα κάποιες αποχρώσεις. Για παράδειγμα, μπορεί να υπάρξει μια κατάσταση όπου οι δεσμοί δεν σπάνε. Σε αυτήν την περίπτωση, εξακολουθεί να εμφανίζεται ένα παράθυρο διαλόγου που δηλώνει ότι δεν είναι δυνατή η αυτόματη ενημέρωση των συνδέσμων. Τι να κάνετε σε αυτή την κατάσταση;
- Αρχικά, πρέπει να ελέγξετε εάν περιέχονται πληροφορίες στις επονομαζόμενες περιοχές. Για να το κάνετε αυτό, πατήστε το συνδυασμό πλήκτρων Ctrl + F3 ή ανοίξτε την καρτέλα "Τύποι" - "Διαχείριση ονομάτων". Εάν το όνομα του αρχείου είναι πλήρες, τότε απλά πρέπει να το επεξεργαστείτε ή να το αφαιρέσετε εντελώς. Προτού διαγράψετε ονομασμένα εύρη, πρέπει να αντιγράψετε το αρχείο σε κάποια άλλη τοποθεσία, ώστε να μπορείτε να επιστρέψετε στην αρχική έκδοση εάν έγιναν τα λάθος βήματα.
- Εάν δεν μπορείτε να λύσετε το πρόβλημα καταργώντας ονόματα, μπορείτε να ελέγξετε τη μορφοποίηση υπό όρους. Τα κελιά σε έναν άλλο πίνακα μπορούν να αναφέρονται σε κανόνες μορφοποίησης υπό όρους. Για να το κάνετε αυτό, βρείτε το αντίστοιχο στοιχείο στην καρτέλα "Αρχική σελίδα" και, στη συνέχεια, κάντε κλικ στο κουμπί "Διαχείριση αρχείων".
Κανονικά, το Excel δεν σας δίνει τη δυνατότητα να δώσετε τη διεύθυνση άλλων βιβλίων εργασίας σε μορφοποίηση υπό όρους, αλλά το δίνετε εάν αναφέρεστε σε μια περιοχή με όνομα με αναφορά σε άλλο αρχείο. Συνήθως, ακόμη και μετά την αφαίρεση του συνδέσμου, ο σύνδεσμος παραμένει. Δεν υπάρχει πρόβλημα με την κατάργηση ενός τέτοιου συνδέσμου, επειδή ο σύνδεσμος στην πραγματικότητα δεν λειτουργεί. Επομένως, δεν θα συμβεί τίποτα κακό αν το αφαιρέσετε.
Μπορείτε επίσης να χρησιμοποιήσετε τη λειτουργία "Έλεγχος δεδομένων" για να μάθετε εάν υπάρχουν περιττοί σύνδεσμοι. Οι σύνδεσμοι συνήθως παραμένουν εάν χρησιμοποιείται ο τύπος επικύρωσης δεδομένων "Λίστα". Τι να κάνετε όμως αν υπάρχουν πολλά κύτταρα; Είναι πραγματικά απαραίτητο να ελέγχεται κάθε ένα από αυτά διαδοχικά; Φυσικά και όχι. Μετά από όλα, θα πάρει πολύ χρόνο. Επομένως, πρέπει να χρησιμοποιήσετε έναν ειδικό κωδικό για να τον αποθηκεύσετε σημαντικά.
Επιλογή ρητή
«——————————————————————————————
Συγγραφέας: The_Prist (Στσερμπάκοφ Ντμίτρι)
Επαγγελματική ανάπτυξη εφαρμογών για MS Office οποιασδήποτε πολυπλοκότητας
Διεξαγωγή εκπαιδεύσεων στο MS Excel
https://www.excel-vba.ru
[email protected]
«WebMoney—R298726502453; Yandex.Money — 41001332272872
Σκοπός:
«——————————————————————————————
Sub FindErrLink()
«Πρέπει να κοιτάξουμε στον σύνδεσμο Δεδομένα -Αλλαγή συνδέσμων προς το αρχείο προέλευσης
'και βάλτε τις λέξεις-κλειδιά εδώ με πεζά (μέρος του ονόματος αρχείου)
Ο αστερίσκος απλώς αντικαθιστά οποιονδήποτε αριθμό χαρακτήρων, ώστε να μην ανησυχείτε για το ακριβές όνομα
Const sToFndLink$ = "*εκπτώσεις 2018*"
Dim rr ως εύρος, rc ως εύρος, rres ως εύρος, s$
«καθορίστε όλα τα κελιά με επικύρωση δεδομένων
On Error Συνέχιση Επόμενη
Ορισμός rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Αν το rr δεν είναι τίποτα τότε
MsgBox "Δεν υπάρχουν κελιά με επικύρωση δεδομένων στο ενεργό φύλλο", vbInformation, "www.excel-vba.ru"
Έξοδος Sub
End If
Στο σφάλμα GoTo 0
'ελέγξτε κάθε κελί για συνδέσμους
Για κάθε rc Σε rr
«για κάθε περίπτωση, παραλείπουμε σφάλματα – αυτό μπορεί επίσης να συμβεί
«Αλλά οι διασυνδέσεις μας πρέπει να είναι χωρίς αυτές και σίγουρα θα βρεθούν
s = «»
On Error Συνέχιση Επόμενη
s = rc.Validation.Formula1
Στο σφάλμα GoTo 0
'βρέθηκε - συλλέγουμε τα πάντα σε ξεχωριστό εύρος
Εάν LCase(s) Like sToFndLink Τότε
Αν το rres δεν είναι τίποτα τότε
Σετ rres = rc
αλλιώς
Σετ rres = Ένωση (rc, rres)
End If
End If
Επόμενο
«Εάν υπάρχει σύνδεση, επιλέξτε όλα τα κελιά με τέτοιους ελέγχους δεδομένων
If Not rres Is Nothing Τότε
rres.Επιλέξτε
' rres.Interior.Color = vbRed 'αν θέλετε να τονίσετε με χρώμα
End If
Sub End
Είναι απαραίτητο να δημιουργήσετε μια τυπική ενότητα στον επεξεργαστή μακροεντολών και, στη συνέχεια, να εισαγάγετε αυτό το κείμενο εκεί. Μετά από αυτό, καλέστε το παράθυρο μακροεντολής χρησιμοποιώντας το συνδυασμό πλήκτρων Alt + F8 και, στη συνέχεια, επιλέξτε τη μακροεντολή μας και κάντε κλικ στο κουμπί "Εκτέλεση". Υπάρχουν μερικά πράγματα που πρέπει να λάβετε υπόψη όταν χρησιμοποιείτε αυτόν τον κώδικα:
- Πριν αναζητήσετε έναν σύνδεσμο που δεν είναι πλέον σχετικός, πρέπει πρώτα να προσδιορίσετε πώς φαίνεται ο σύνδεσμος μέσω του οποίου δημιουργείται. Για να το κάνετε αυτό, μεταβείτε στο μενού "Δεδομένα" και βρείτε το στοιχείο "Αλλαγή συνδέσμων" εκεί. Μετά από αυτό, πρέπει να δείτε το όνομα του αρχείου και να το καθορίσετε σε εισαγωγικά. Για παράδειγμα, όπως αυτό: Const sToFndLink$ = "*εκπτώσεις 2018*"
- Είναι δυνατό να γράψετε το όνομα όχι ολόκληρο, αλλά απλώς να αντικαταστήσετε τους περιττούς χαρακτήρες με έναν αστερίσκο. Και σε εισαγωγικά, γράψτε το όνομα του αρχείου με μικρά γράμματα. Σε αυτήν την περίπτωση, το Excel θα βρει όλα τα αρχεία που περιέχουν μια τέτοια συμβολοσειρά στο τέλος.
- Αυτός ο κωδικός μπορεί να ελέγξει μόνο για συνδέσμους στο φύλλο που είναι ενεργό αυτήν τη στιγμή.
- Με αυτήν τη μακροεντολή, μπορείτε να επιλέξετε μόνο τα κελιά που έχει βρει. Πρέπει να διαγράψετε τα πάντα χειροκίνητα. Αυτό είναι ένα πλεονέκτημα, γιατί μπορείτε να ελέγξετε ξανά τα πάντα.
- Μπορείτε επίσης να κάνετε τα κελιά τονισμένα με ειδικό χρώμα. Για να το κάνετε αυτό, αφαιρέστε την απόστροφο πριν από αυτή τη γραμμή. rres.Interior.Color = vbRed
Συνήθως, αφού ολοκληρώσετε τα βήματα που περιγράφονται στις παραπάνω οδηγίες, δεν πρέπει να υπάρχουν πλέον περιττές συνδέσεις. Αλλά αν υπάρχουν μερικά από αυτά στο έγγραφο και δεν μπορείτε να τα αφαιρέσετε για τον ένα ή τον άλλο λόγο (ένα τυπικό παράδειγμα είναι η ασφάλεια των δεδομένων σε ένα φύλλο), τότε μπορείτε να χρησιμοποιήσετε διαφορετική ακολουθία ενεργειών. Αυτή η οδηγία ισχύει μόνο για εκδόσεις 2007 και νεότερες.
- Δημιουργούμε ένα αντίγραφο ασφαλείας του εγγράφου.
- Ανοίξτε αυτό το έγγραφο χρησιμοποιώντας την αρχειοθέτηση. Μπορείτε να χρησιμοποιήσετε οποιοδήποτε που υποστηρίζει τη μορφή ZIP, αλλά το WinRar θα λειτουργήσει επίσης, καθώς και αυτό που είναι ενσωματωμένο στα Windows.
- Στο αρχείο που εμφανίζεται, θα πρέπει να βρείτε τον φάκελο xl και, στη συνέχεια, να ανοίξετε τα externalLinks.
- Αυτός ο φάκελος περιέχει όλους τους εξωτερικούς συνδέσμους, καθένας από τους οποίους αντιστοιχεί σε ένα αρχείο της μορφής externalLink1.xml. Όλα είναι μόνο αριθμημένα και επομένως ο χρήστης δεν έχει την ευκαιρία να καταλάβει τι είδους σύνδεση είναι αυτή. Για να καταλάβετε τι είδους σύνδεση, πρέπει να ανοίξετε τον φάκελο _rels και να τον δείτε εκεί.
- Μετά από αυτό, αφαιρούμε όλους ή συγκεκριμένους συνδέσμους, με βάση όσα μαθαίνουμε στο αρχείο externalLinkX.xml.rels.
- Μετά από αυτό, ανοίγουμε το αρχείο μας χρησιμοποιώντας το Excel. Θα υπάρχουν πληροφορίες σχετικά με ένα σφάλμα όπως "Σφάλμα σε μέρος του περιεχομένου στο Βιβλίο". Δίνουμε συγκατάθεση. Μετά από αυτό, θα εμφανιστεί ένα άλλο παράθυρο διαλόγου. Το κλείνουμε.
Μετά από αυτό, όλοι οι σύνδεσμοι θα πρέπει να αφαιρεθούν.