Konten
Komunikasi adalah fitur yang sangat berguna di Excel. Lagi pula, sangat sering pengguna harus menggunakan informasi dari file lain. Tetapi dalam beberapa situasi, mereka dapat melakukan lebih banyak kerugian daripada kebaikan. Lagi pula, misalnya, jika Anda mengirim file-file ini melalui surat, tautannya tidak berfungsi. Hari ini kita akan berbicara lebih detail tentang apa yang harus dilakukan untuk menghindari masalah seperti itu.
Apa itu hubungan di Excel
Hubungan di Excel sangat sering digunakan bersama dengan fungsi seperti: VPRuntuk mendapatkan informasi dari buku kerja lain. Itu bisa berupa tautan khusus yang berisi alamat tidak hanya sel, tetapi juga buku tempat data berada. Akibatnya, tautan seperti itu terlihat seperti ini: =VLOOKUP(A2;'[Penjualan 2018.xlsx]Laporan'!$A:$F;4;0). Atau, untuk representasi yang lebih sederhana, nyatakan alamat dalam bentuk berikut: ='[Penjualan 2018.xlsx]Laporan'!$A1. Mari kita analisis setiap elemen tautan jenis ini:
- [Penjualan 2018.xlsx]. Fragmen ini berisi tautan ke file dari mana Anda ingin mendapatkan informasi. Itu juga disebut sumber.
- Foto. Kami menggunakan nama berikut, tetapi ini bukan nama yang seharusnya. Blok ini berisi nama lembar di mana Anda perlu menemukan informasi.
- $A:$F $A1 – alamat sel atau rentang yang berisi data yang terdapat dalam dokumen ini.
Sebenarnya, proses membuat link ke dokumen eksternal disebut linking. Setelah kami mendaftarkan alamat sel yang terdapat dalam file lain, isi tab "Data" berubah. Yaitu, tombol "Ubah koneksi" menjadi aktif, dengan bantuan pengguna dapat mengedit koneksi yang ada.
Inti dari masalah
Sebagai aturan, tidak ada kesulitan tambahan yang muncul untuk menggunakan tautan. Bahkan jika muncul situasi di mana sel berubah, maka semua tautan diperbarui secara otomatis. Tetapi jika Anda sudah mengganti nama buku kerja itu sendiri atau memindahkannya ke alamat yang berbeda, Excel menjadi tidak berdaya. Oleh karena itu, menghasilkan pesan berikut.
Di sini, pengguna memiliki dua opsi yang memungkinkan untuk bertindak dalam situasi ini. Dia dapat mengklik "Lanjutkan" dan kemudian perubahan tidak akan diperbarui, atau dia dapat mengklik tombol "Ubah Asosiasi", yang dengannya dia dapat memperbaruinya secara manual. Setelah kami mengklik tombol ini, sebuah jendela tambahan akan muncul di mana dimungkinkan untuk mengubah tautan, yang menunjukkan di mana file yang benar berada saat ini dan apa namanya.
Selain itu, Anda dapat mengedit tautan melalui tombol yang sesuai yang terletak di tab "Data". Pengguna juga dapat mengetahui bahwa koneksi terputus oleh kesalahan #LINK, yang muncul ketika Excel tidak dapat mengakses informasi yang terletak di alamat tertentu karena fakta bahwa alamat itu sendiri tidak valid.
Cara membatalkan tautan di excel
Salah satu metode paling sederhana untuk menyelesaikan situasi yang dijelaskan di atas jika Anda tidak dapat memperbarui sendiri lokasi file tertaut adalah dengan menghapus tautan itu sendiri. Ini sangat mudah dilakukan jika dokumen hanya berisi satu tautan. Untuk melakukan ini, Anda harus melakukan urutan langkah-langkah berikut:
- Buka menu "Data".
- Kami menemukan bagian "Koneksi", dan di sana - opsi "Ubah koneksi".
- Setelah itu, klik "Batalkan Tautan".
Jika Anda bermaksud mengirimkan buku ini kepada orang lain, sangat disarankan agar Anda melakukannya terlebih dahulu. Lagi pula, setelah menghapus tautan, semua nilai yang terkandung dalam dokumen lain akan secara otomatis dimuat ke dalam file, digunakan dalam formula, dan alih-alih alamat sel, informasi dalam sel yang sesuai hanya akan diubah menjadi nilai .
Cara membatalkan tautan semua buku
Tetapi jika jumlah tautan menjadi terlalu besar, menghapusnya secara manual bisa memakan waktu lama. Untuk mengatasi masalah ini sekaligus, Anda dapat menggunakan makro khusus. Itu ada di addon VBA-Excel. Anda perlu mengaktifkannya dan pergi ke tab dengan nama yang sama. Akan ada bagian "Tautan", di mana kita perlu mengklik tombol "Hancurkan semua tautan".
Kode VBA
Jika pengaya ini tidak dapat diaktifkan, Anda dapat membuat makro sendiri. Untuk melakukannya, buka editor Visual Basic dengan menekan tombol Alt + F11, dan tulis baris berikut di bidang entri kode.
Sub Batalkan TautanBuku Kerja()
Redup WbLink
Redup dan Selamanya
Select Case MsgBox(“Semua referensi ke buku lain akan dihapus dari file ini, dan rumus yang merujuk ke buku lain akan diganti dengan nilai.” & vbCrLf & “Apakah Anda yakin ingin melanjutkan?”, 36, “Unlink?” )
Kasus 7′ Tidak
Keluar dari Sub
End Select
WbLinks = ActiveWorkbook.LinkSources(Tipe:=xlLinkTypeExcelLinks)
Jika Tidak Kosong(WbLinks) Lalu
Untuk i = 1 Ke UBound(WbLinks)
ActiveWorkbook.BreakLink Nama:=WbLinks(i), Jenis:=xlLinkTypeExcelLinks
Selanjutnya
lain
MsgBox “Tidak ada link ke buku lain dalam file ini.”, 64, “Link ke buku lain”
End If
End Sub
Cara memutuskan ikatan hanya dalam rentang yang dipilih
Dari waktu ke waktu, jumlah tautan sangat besar, dan pengguna takut bahwa setelah menghapus salah satunya, tidak mungkin mengembalikan semuanya jika ada yang berlebihan. Tapi ini adalah masalah yang mudah dihindari. Untuk melakukan ini, Anda perlu memilih rentang untuk menghapus tautan, lalu menghapusnya. Untuk melakukan ini, Anda harus melakukan urutan tindakan berikut:
- Pilih kumpulan data yang perlu diubah.
- Instal add-on VBA-Excel, lalu buka tab yang sesuai.
- Selanjutnya, kami menemukan menu "Tautan" dan klik tombol "Putuskan tautan di rentang yang dipilih".
Setelah itu, semua tautan di kumpulan sel yang dipilih akan dihapus.
Apa yang harus dilakukan jika ikatannya tidak putus
Semua hal di atas terdengar bagus, tetapi dalam praktiknya selalu ada beberapa nuansa. Misalnya, mungkin ada situasi di mana ikatan tidak putus. Dalam hal ini, kotak dialog masih muncul yang menyatakan bahwa tidak mungkin memperbarui tautan secara otomatis. Apa yang harus dilakukan dalam situasi ini?
- Pertama, Anda perlu memeriksa apakah ada informasi yang terkandung dalam rentang bernama. Untuk melakukan ini, tekan kombinasi tombol Ctrl + F3 atau buka tab "Rumus" - "Manajer Nama". Jika nama file sudah lengkap, maka Anda hanya perlu mengeditnya atau menghapusnya sama sekali. Sebelum menghapus rentang bernama, Anda perlu menyalin file ke beberapa lokasi lain sehingga Anda dapat kembali ke versi asli jika langkah yang diambil salah.
- Jika Anda tidak dapat menyelesaikan masalah dengan menghapus nama, Anda dapat memeriksa pemformatan bersyarat. Sel di tabel lain dapat direferensikan dalam aturan pemformatan bersyarat. Untuk melakukan ini, temukan item yang sesuai di tab "Beranda", lalu klik tombol "Manajemen File".
Biasanya, Excel tidak memberi Anda kemampuan untuk memberikan alamat buku kerja lain dalam pemformatan bersyarat, tetapi Anda melakukannya jika Anda merujuk ke rentang bernama dengan referensi ke file lain. Biasanya, bahkan setelah tautan dihapus, tautan tetap ada. Tidak ada masalah dalam menghapus tautan seperti itu, karena tautan tersebut sebenarnya tidak berfungsi. Karena itu, tidak ada hal buruk yang akan terjadi jika Anda menghapusnya.
Anda juga dapat menggunakan fungsi "Pemeriksaan Data" untuk mengetahui apakah ada tautan yang tidak perlu. Tautan biasanya tetap ada jika jenis validasi data "Daftar" digunakan. Tetapi apa yang harus dilakukan jika ada banyak sel? Apakah benar-benar perlu untuk memeriksa masing-masing secara berurutan? Tentu saja tidak. Lagipula, itu akan memakan waktu yang sangat lama. Karena itu, Anda perlu menggunakan kode khusus untuk menyimpannya secara signifikan.
Opsi Eksplisit
'——————————————————————————————
Penulis : The_Prist(Shcherbakov Dmitry)
'Pengembangan aplikasi profesional untuk MS Office dengan kompleksitas apa pun
' Melakukan pelatihan tentang MS Excel
' https://www.excel-vba.ru
' [dilindungi email]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' Tujuan:
'——————————————————————————————
Sub TemukanErrLink()
'kita perlu melihat di Data -Ubah tautan tautan ke file sumber
'dan letakkan kata kunci di sini dalam huruf kecil (bagian dari nama file)
'tanda bintang hanya menggantikan sejumlah karakter sehingga Anda tidak perlu khawatir tentang nama persisnya
Const sToFndLink$ = “*penjualan 2018*”
Dim rr Sebagai Rentang, rc Sebagai Rentang, rres Sebagai Rentang, s$
'tentukan semua sel dengan validasi data
On Error Resume Next
Setel rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Jika rr Bukan Apa-apa Maka
MsgBox “Tidak ada sel dengan validasi data pada lembar aktif”, vbInformation, “www.excel-vba.ru”
Keluar dari Sub
End If
Pada Kesalahan GoTo 0
'periksa setiap sel untuk tautan
Untuk Setiap rc Dalam rr
'untuk jaga-jaga, kami melewatkan kesalahan – ini juga bisa terjadi
'tapi koneksi kita harus tanpa mereka dan mereka pasti akan ditemukan
s = «»
On Error Resume Next
s = rc.Validasi.Formula1
Pada Kesalahan GoTo 0
'ditemukan – kami mengumpulkan semuanya dalam rentang terpisah
Jika LCase (s) Suka sToFndLink Kemudian
Jika rres Bukan Apa-apa Maka
Tetapkan rres = rc
lain
Atur rres = Union(rc, rres)
End If
End If
Selanjutnya
'jika ada koneksi, pilih semua sel dengan cek data tersebut
Jika Tidak rres Tidak Ada Apa-apanya
rres.Pilih
' rres.Interior.Color = vbRed 'jika Anda ingin menonjolkan dengan warna
End If
End Sub
Penting untuk membuat modul standar di editor makro, dan kemudian masukkan teks ini di sana. Setelah itu, panggil jendela makro menggunakan kombinasi tombol Alt + F8, lalu pilih makro kami dan klik tombol "Jalankan". Ada beberapa hal yang perlu diingat saat menggunakan kode ini:
- Sebelum Anda mencari tautan yang tidak lagi relevan, Anda harus terlebih dahulu menentukan seperti apa tautan itu dibuat. Untuk melakukan ini, buka menu "Data" dan temukan item "Ubah Tautan" di sana. Setelah itu, Anda perlu melihat nama file, dan menentukannya dalam tanda kutip. Misalnya seperti ini: Const sToFndLink$ = “*penjualan 2018*”
- Dimungkinkan untuk menulis nama tidak secara lengkap, tetapi cukup ganti karakter yang tidak perlu dengan tanda bintang. Dan dalam tanda kutip, tulis nama file dengan huruf kecil. Dalam hal ini, Excel akan menemukan semua file yang berisi string seperti itu di akhir.
- Kode ini hanya dapat memeriksa tautan di lembar yang sedang aktif.
- Dengan makro ini, Anda hanya dapat memilih sel yang telah ditemukan. Anda harus menghapus semuanya secara manual. Ini adalah nilai tambah, karena Anda dapat memeriksa ulang semuanya lagi.
- Anda juga dapat membuat sel disorot dalam warna khusus. Untuk melakukan ini, hapus apostrof sebelum baris ini. rres.Interior.Warna = vbMerah
Biasanya, setelah Anda menyelesaikan langkah-langkah yang dijelaskan dalam petunjuk di atas, seharusnya tidak ada lagi koneksi yang tidak perlu. Tetapi jika ada beberapa di antaranya dalam dokumen dan Anda tidak dapat menghapusnya karena satu dan lain alasan (contoh umum adalah keamanan data dalam lembar), maka Anda dapat menggunakan urutan tindakan yang berbeda. Instruksi ini hanya berlaku untuk versi 2007 dan lebih tinggi.
- Kami membuat salinan cadangan dokumen.
- Buka dokumen ini menggunakan pengarsip. Anda dapat menggunakan apa pun yang mendukung format ZIP, tetapi WinRar juga akan berfungsi, serta yang ada di dalam Windows.
- Di arsip yang muncul, Anda perlu menemukan folder xl, lalu membuka ExternalLinks.
- Folder ini berisi semua tautan eksternal, yang masing-masing terkait dengan file berbentuk externalLink1.xml. Semuanya hanya diberi nomor, dan oleh karena itu pengguna tidak memiliki kesempatan untuk memahami jenis koneksi apa ini. Untuk memahami jenis koneksi apa, Anda perlu membuka folder _rels, dan melihatnya di sana.
- Setelah itu, kami menghapus semua atau tautan tertentu, berdasarkan apa yang kami pelajari di file externalLinkX.xml.rels.
- Setelah itu, kami membuka file kami menggunakan Excel. Akan ada informasi tentang kesalahan seperti “Kesalahan sebagian dari konten dalam Buku.” Kami memberikan persetujuan. Setelah itu, dialog lain akan muncul. Kami menutupnya.
Setelah itu, semua tautan harus dihapus.