Contents [show]
Komunikasi adalah ciri yang sangat berguna dalam Excel. Lagipun, selalunya pengguna perlu menggunakan maklumat daripada fail lain. Tetapi dalam beberapa situasi, mereka boleh melakukan lebih banyak kemudaratan daripada kebaikan. Lagipun, sebagai contoh, jika anda menghantar fail ini melalui mel, pautan tidak berfungsi. Hari ini kita akan bercakap dengan lebih terperinci tentang apa yang perlu dilakukan untuk mengelakkan masalah sedemikian.
Apakah perhubungan dalam Excel
Perhubungan dalam Excel sangat kerap digunakan bersama dengan fungsi seperti VPRuntuk mendapatkan maklumat daripada buku kerja lain. Ia boleh dalam bentuk pautan khas yang mengandungi alamat bukan sahaja sel, tetapi juga buku di mana data berada. Akibatnya, pautan sedemikian kelihatan seperti ini: =VLOOKUP(A2;'[Jualan 2018.xlsx]Laporan'!$A:$F;4;0). Atau, untuk perwakilan yang lebih mudah, nyatakan alamat dalam bentuk berikut: ='[Jualan 2018.xlsx]Laporan'!$A1. Mari analisa setiap elemen pautan jenis ini:
- [Jualan 2018.xlsx]. Serpihan ini mengandungi pautan ke fail dari mana anda ingin mendapatkan maklumat. Ia juga dipanggil sumber.
- pics. Kami menggunakan nama berikut, tetapi ini bukan nama yang sepatutnya. Blok ini mengandungi nama helaian yang anda perlukan untuk mencari maklumat.
- $A:$F dan $A1 – alamat sel atau julat yang mengandungi data yang terkandung dalam dokumen ini.
Sebenarnya, proses mencipta pautan ke dokumen luaran dipanggil pautan. Selepas kami telah mendaftarkan alamat sel yang terkandung dalam fail lain, kandungan tab "Data" berubah. Iaitu, butang "Tukar sambungan" menjadi aktif, dengan bantuan pengguna boleh mengedit sambungan sedia ada.
Intipati masalah
Sebagai peraturan, tiada kesulitan tambahan timbul untuk menggunakan pautan. Walaupun timbul situasi di mana sel berubah, maka semua pautan dikemas kini secara automatik. Tetapi jika anda sudah menamakan semula buku kerja itu sendiri atau mengalihkannya ke alamat lain, Excel menjadi tidak berkuasa. Oleh itu, ia menghasilkan mesej berikut.
Di sini, pengguna mempunyai dua pilihan yang mungkin untuk cara bertindak dalam situasi ini. Dia boleh mengklik "Teruskan" dan kemudian perubahan tidak akan dikemas kini, atau dia boleh mengklik butang "Tukar Persatuan", yang dengannya dia boleh mengemas kininya secara manual. Selepas kami mengklik butang ini, tetingkap tambahan akan muncul di mana ia mungkin untuk menukar pautan, menunjukkan di mana fail yang betul terletak pada masa ini dan apa namanya.
Di samping itu, anda boleh mengedit pautan melalui butang sepadan yang terletak pada tab "Data". Pengguna juga boleh mengetahui bahawa sambungan terputus oleh ralat #LINK, yang muncul apabila Excel tidak dapat mengakses maklumat yang terletak di alamat tertentu disebabkan oleh fakta bahawa alamat itu sendiri tidak sah.
Bagaimana untuk menyahpaut dalam excel
Salah satu kaedah paling mudah untuk menyelesaikan situasi yang diterangkan di atas sekiranya anda tidak boleh mengemas kini sendiri lokasi fail yang dipautkan ialah memadamkan pautan itu sendiri. Ini amat mudah dilakukan jika dokumen mengandungi hanya satu pautan. Untuk melakukan ini, anda mesti melakukan urutan langkah berikut:
- Buka menu "Data".
- Kami mencari bahagian "Sambungan", dan di sana - pilihan "Tukar sambungan".
- Selepas itu, klik pada "Nyahpaut".
Jika anda berhasrat untuk menghantar buku ini kepada orang lain, anda amat disyorkan untuk berbuat demikian lebih awal. Lagipun, selepas memadamkan pautan, semua nilai yang terkandung dalam dokumen lain akan dimuatkan secara automatik ke dalam fail, digunakan dalam formula, dan bukannya alamat sel, maklumat dalam sel yang sepadan hanya akan diubah menjadi nilai. .
Cara menyahpaut semua buku
Tetapi jika bilangan pautan menjadi terlalu besar, memadamnya secara manual boleh mengambil masa yang lama. Untuk menyelesaikan masalah ini sekali gus, anda boleh menggunakan makro khas. Ia ada dalam addon VBA-Excel. Anda perlu mengaktifkannya dan pergi ke tab dengan nama yang sama. Akan ada bahagian "Pautan", di mana kita perlu mengklik pada butang "Pecah semua pautan".
kod VBA
Jika tidak mungkin untuk mengaktifkan alat tambah ini, anda boleh membuat makro sendiri. Untuk melakukan ini, buka editor Visual Basic dengan menekan kekunci Alt + F11, dan tulis baris berikut dalam medan kemasukan kod.
Sub UnlinkWorkBooks()
Malapkan WbLinks
Malap dan Panjang
Pilih Case MsgBox("Semua rujukan kepada buku lain akan dialih keluar daripada fail ini dan formula yang merujuk kepada buku lain akan digantikan dengan nilai." & vbCrLf & "Adakah anda pasti mahu meneruskan?", 36, "Nyahpaut?" )
Kes 7′ No
Keluar Sub
Pilih Tamat
WbLinks = ActiveWorkbook.LinkSources(Jenis:=xlLinkTypeExcelLinks)
Jika Tidak IsEmpty(WbLinks) Kemudian
Untuk i = 1 Kepada UBound(WbLinks)
Nama ActiveWorkbook.BreakLink:=WbLinks(i), Type:=xlLinkTypeExcelLinks
Seterusnya
lagi
MsgBox "Tiada pautan ke buku lain dalam fail ini.", 64, "Pautan ke buku lain"
End If
Akhir Sub
Cara memutuskan hubungan hanya dalam julat yang dipilih
Dari semasa ke semasa, bilangan pautan sangat besar, dan pengguna takut bahawa selepas memadamkan salah satu daripadanya, tidak mungkin untuk mengembalikan semuanya jika ada yang berlebihan. Tetapi ini adalah masalah yang mudah dielakkan. Untuk melakukan ini, anda perlu memilih julat untuk memadamkan pautan, dan kemudian memadamkannya. Untuk melakukan ini, anda mesti melakukan urutan tindakan berikut:
- Pilih set data yang perlu diubah suai.
- Pasang alat tambah VBA-Excel, dan kemudian pergi ke tab yang sesuai.
- Seterusnya, kami mencari menu "Pautan" dan klik pada butang "Putuskan pautan dalam julat yang dipilih".
Selepas itu, semua pautan dalam set sel yang dipilih akan dipadamkan.
Apa yang perlu dilakukan jika ikatan tidak putus
Semua perkara di atas terdengar baik, tetapi dalam praktiknya selalu ada beberapa nuansa. Sebagai contoh, mungkin terdapat situasi di mana ikatan tidak terputus. Dalam kes ini, kotak dialog masih muncul menyatakan bahawa tidak mungkin untuk mengemas kini pautan secara automatik. Apa yang perlu dilakukan dalam keadaan ini?
- Mula-mula, anda perlu menyemak sama ada sebarang maklumat terkandung dalam julat yang dinamakan. Untuk melakukan ini, tekan kombinasi kekunci Ctrl + F3 atau buka tab "Formula" - "Pengurus Nama". Jika nama fail penuh, maka anda hanya perlu mengeditnya atau mengalih keluarnya sama sekali. Sebelum memadamkan julat yang dinamakan, anda perlu menyalin fail ke beberapa lokasi lain supaya anda boleh kembali ke versi asal jika langkah yang salah telah diambil.
- Jika anda tidak dapat menyelesaikan masalah dengan mengalih keluar nama, anda boleh menyemak pemformatan bersyarat. Sel dalam jadual lain boleh dirujuk dalam peraturan pemformatan bersyarat. Untuk melakukan ini, cari item yang sepadan pada tab "Rumah", dan kemudian klik pada butang "Pengurusan Fail".
Biasanya, Excel tidak memberi anda keupayaan untuk memberikan alamat buku kerja lain dalam pemformatan bersyarat, tetapi anda melakukannya jika anda merujuk kepada julat bernama dengan rujukan kepada fail lain. Biasanya, walaupun selepas pautan dialih keluar, pautan itu kekal. Tiada masalah untuk mengalih keluar pautan sedemikian, kerana pautan itu sebenarnya tidak berfungsi. Oleh itu, tiada perkara buruk akan berlaku jika anda mengeluarkannya.
Anda juga boleh menggunakan fungsi "Semakan Data" untuk mengetahui sama ada terdapat sebarang pautan yang tidak diperlukan. Pautan biasanya kekal jika jenis pengesahan data "Senarai" digunakan. Tetapi apa yang perlu dilakukan jika terdapat banyak sel? Adakah benar-benar perlu untuk menyemak setiap satu daripada mereka secara berurutan? Sudah tentu tidak. Lagipun, ia akan mengambil masa yang sangat lama. Oleh itu, anda perlu menggunakan kod khas untuk menyimpannya dengan ketara.
Pilihan Eksplisit
'—————————————————————————————
' Pengarang : The_Prist(Shcherbakov Dmitry)
' Pembangunan profesional aplikasi untuk MS Office dari sebarang kerumitan
' Menjalankan latihan tentang MS Excel
' https://www.excel-vba.ru
' [e-mel dilindungi]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
'Tujuan:
'—————————————————————————————
Sub FindErrLink()
'kita perlu melihat dalam pautan Data -Tukar pautan ke fail sumber
'dan letakkan kata kunci di sini dalam huruf kecil (sebahagian daripada nama fail)
'asterisk hanya menggantikan sebarang bilangan aksara supaya anda tidak perlu risau tentang nama yang tepat
Const sToFndLink$ = “*jualan 2018*”
Dim rr Sebagai Julat, rc Sebagai Julat, rres Sebagai Julat, s$
'takrifkan semua sel dengan pengesahan data
Pada Error Resume Next
Set rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
Jika rr Tiada Maka
MsgBox "Tiada sel dengan pengesahan data pada helaian aktif", vbInformation, "www.excel-vba.ru"
Keluar Sub
End If
Pada Ralat GoTo 0
'semak setiap sel untuk pautan
Bagi Setiap rc Dalam rr
'sekiranya, kami melangkau ralat - ini juga boleh berlaku
'tetapi hubungan kita mesti tanpa mereka dan mereka pasti akan ditemui
s = «»
Pada Error Resume Next
s = rc.Pengesahan.Formula1
Pada Ralat GoTo 0
'ditemui – kami mengumpul segala-galanya dalam julat yang berasingan
Jika LCase Suka sToFndLink Kemudian
Jika rres Is Nothing Then
Tetapkan rres = rc
lagi
Tetapkan rres = Union(rc, rres)
End If
End If
Seterusnya
'jika ada sambungan, pilih semua sel dengan semakan data sedemikian
Jika Tidak rres Is Nothing Then
rres.Pilih
' rres.Interior.Color = vbRed 'jika anda ingin menyerlahkan dengan warna
End If
Akhir Sub
Ia adalah perlu untuk membuat modul standard dalam editor makro, dan kemudian masukkan teks ini di sana. Selepas itu, panggil tetingkap makro menggunakan kombinasi kekunci Alt + F8, dan kemudian pilih makro kami dan klik pada butang "Jalankan". Terdapat beberapa perkara yang perlu diingat apabila menggunakan kod ini:
- Sebelum anda mencari pautan yang tidak lagi berkaitan, anda mesti terlebih dahulu menentukan rupa pautan yang melaluinya ia dibuat. Untuk melakukan ini, pergi ke menu "Data" dan cari item "Tukar Pautan" di sana. Selepas itu, anda perlu melihat nama fail, dan nyatakan dalam petikan. Sebagai contoh, seperti ini: Const sToFndLink$ = “*jualan 2018*”
- Ia adalah mungkin untuk menulis nama tidak sepenuhnya, tetapi hanya menggantikan aksara yang tidak perlu dengan asterisk. Dan dalam petikan, tulis nama fail dalam huruf kecil. Dalam kes ini, Excel akan mencari semua fail yang mengandungi rentetan sedemikian pada penghujungnya.
- Kod ini hanya boleh menyemak pautan dalam helaian yang sedang aktif.
- Dengan makro ini, anda hanya boleh memilih sel yang ditemuinya. Anda perlu memadam semuanya secara manual. Ini adalah satu kelebihan, kerana anda boleh menyemak semula semuanya.
- Anda juga boleh membuat sel diserlahkan dalam warna khas. Untuk melakukan ini, keluarkan tanda kutip sebelum baris ini. rres.Interior.Color = vbRed
Biasanya, selepas anda melengkapkan langkah yang diterangkan dalam arahan di atas, tiada lagi sambungan yang tidak perlu. Tetapi jika terdapat beberapa daripadanya dalam dokumen dan anda tidak dapat mengalih keluarnya atas satu sebab atau yang lain (contoh biasa ialah keselamatan data dalam helaian), maka anda boleh menggunakan urutan tindakan yang berbeza. Arahan ini hanya sah untuk versi 2007 dan lebih tinggi.
- Kami membuat salinan sandaran dokumen.
- Buka dokumen ini menggunakan arkib. Anda boleh menggunakan mana-mana yang menyokong format ZIP, tetapi WinRar juga akan berfungsi, serta yang terbina dalam Windows.
- Dalam arkib yang muncul, anda perlu mencari folder xl, dan kemudian buka Pautan luar.
- Folder ini mengandungi semua pautan luaran, setiap satunya sepadan dengan fail dalam bentuk externalLink1.xml. Kesemua mereka hanya bernombor, dan oleh itu pengguna tidak mempunyai peluang untuk memahami jenis sambungan ini. Untuk memahami jenis sambungan, anda perlu membuka folder _rels dan melihatnya di sana.
- Selepas itu, kami mengalih keluar semua atau pautan khusus, berdasarkan apa yang kami pelajari dalam fail externalLinkX.xml.rels.
- Selepas itu, kami membuka fail kami menggunakan Excel. Akan terdapat maklumat tentang ralat seperti "Ralat pada sebahagian kandungan dalam Buku". Kami memberi persetujuan. Selepas itu, dialog lain akan muncul. Kami tutup.
Selepas itu, semua pautan harus dialih keluar.