เนื้อหา
การสื่อสารเป็นคุณลักษณะที่มีประโยชน์มากใน 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 ยกเลิกการเชื่อมโยงสมุดงาน()
หรี่ WbLinks
สลัวและนาน
เลือก Case MsgBox("การอ้างอิงถึงหนังสือเล่มอื่นทั้งหมดจะถูกลบออกจากไฟล์นี้ และสูตรที่อ้างถึงหนังสือเล่มอื่นจะถูกแทนที่ด้วยค่า" & vbCrLf & "คุณแน่ใจหรือไม่ว่าต้องการดำเนินการต่อ", 36, "ยกเลิกการลิงก์" )
กรณีที่ 7 'ไม่ใช่
ออกจาก Sub
สิ้นสุดการเลือก
WbLinks = ActiveWorkbook.LinkSources (ประเภท: = xlLinkTypeExcelLinks)
ถ้าไม่ IsEmpty(WbLinks) แล้ว
สำหรับผม = 1 ถึง UBound(WbLinks)
ActiveWorkbook.BreakLink ชื่อ:=WbLinks(i), Type:=xlLinkTypeExcelLinks
ถัดไป
อื่น
MsgBox “ไม่มีลิงก์ไปยังหนังสือเล่มอื่นในไฟล์นี้”, 64, “ลิงก์ไปยังหนังสือเล่มอื่นๆ”
End If
ย่อยสิ้นสุด
วิธีทำลายความสัมพันธ์เฉพาะในช่วงที่เลือก
ในบางครั้ง จำนวนของลิงก์จะมีจำนวนมาก และผู้ใช้กลัวว่าหลังจากลบลิงก์ใดลิงก์หนึ่งไปแล้ว จะไม่สามารถส่งคืนทุกอย่างกลับคืนมาได้หากมีบางอันที่ไม่จำเป็น แต่นี่เป็นปัญหาที่หลีกเลี่ยงได้ง่าย ในการดำเนินการนี้ คุณต้องเลือกช่วงที่จะลบลิงก์ จากนั้นจึงลบออก ในการทำเช่นนี้ คุณต้องดำเนินการตามลำดับต่อไปนี้:
- เลือกชุดข้อมูลที่ต้องการแก้ไข
- ติดตั้งโปรแกรมเสริม VBA-Excel จากนั้นไปที่แท็บที่เหมาะสม
- ต่อไปเราจะพบเมนู "ลิงก์" และคลิกที่ปุ่ม "แบ่งลิงก์ในช่วงที่เลือก"
หลังจากนั้น ลิงก์ทั้งหมดในชุดเซลล์ที่เลือกจะถูกลบออก
จะทำอย่างไรถ้าเนคไทไม่ขาด
ทั้งหมดข้างต้นฟังดูดี แต่ในทางปฏิบัติมีความแตกต่างอยู่เสมอ ตัวอย่างเช่น อาจมีสถานการณ์ที่ความสัมพันธ์ไม่ขาด ในกรณีนี้ กล่องโต้ตอบยังคงปรากฏขึ้นโดยระบุว่าไม่สามารถอัปเดตลิงก์โดยอัตโนมัติได้ สิ่งที่ต้องทำในสถานการณ์เช่นนี้?
- ขั้นแรก คุณต้องตรวจสอบว่ามีข้อมูลใดอยู่ในช่วงที่ระบุชื่อหรือไม่ ในการดำเนินการนี้ ให้กดคีย์ผสม Ctrl + F3 หรือเปิดแท็บ "สูตร" - "ตัวจัดการชื่อ" หากชื่อไฟล์เต็ม คุณเพียงแค่ต้องแก้ไขหรือลบออกทั้งหมด ก่อนลบช่วงที่มีชื่อ คุณต้องคัดลอกไฟล์ไปยังตำแหน่งอื่นเพื่อให้คุณสามารถกลับไปใช้เวอร์ชันดั้งเดิมได้หากทำตามขั้นตอนที่ไม่ถูกต้อง
- หากคุณแก้ปัญหาด้วยการเอาชื่อออกไม่ได้ คุณสามารถตรวจสอบการจัดรูปแบบตามเงื่อนไขได้ เซลล์ในตารางอื่นสามารถอ้างอิงได้ในกฎการจัดรูปแบบตามเงื่อนไข ในการทำเช่นนี้ ค้นหารายการที่เกี่ยวข้องในแท็บ "หน้าแรก" จากนั้นคลิกที่ปุ่ม "การจัดการไฟล์"
โดยปกติ Excel จะไม่ให้คุณสามารถระบุที่อยู่ของเวิร์กบุ๊กอื่นในการจัดรูปแบบตามเงื่อนไขได้ แต่คุณทำได้หากคุณอ้างอิงช่วงที่มีชื่อโดยมีการอ้างอิงไปยังไฟล์อื่น โดยปกติ แม้หลังจากลบลิงก์แล้ว ลิงก์ก็จะยังคงอยู่ ไม่มีปัญหาในการลบลิงก์ดังกล่าว เนื่องจากลิงก์ใช้งานไม่ได้จริง ดังนั้นจะไม่มีอะไรเลวร้ายเกิดขึ้นหากคุณลบออก
คุณยังสามารถใช้ฟังก์ชัน "ตรวจสอบข้อมูล" เพื่อดูว่ามีลิงก์ที่ไม่จำเป็นหรือไม่ ลิงก์มักจะยังคงอยู่หากใช้การตรวจสอบข้อมูลประเภท "รายการ" แต่จะทำอย่างไรถ้ามีเซลล์จำนวนมาก? จำเป็นต้องตรวจสอบแต่ละรายการตามลำดับหรือไม่? แน่นอนไม่ ท้ายที่สุดมันจะใช้เวลานานมาก ดังนั้น คุณจำเป็นต้องใช้รหัสพิเศษเพื่อบันทึกอย่างมาก
ตัวเลือกที่ชัดเจน
'——————————————————————
' ผู้แต่ง : The_Prist(Shcherbakov Dmitry)
' การพัฒนาแอปพลิเคชันสำหรับ MS Office ที่มีความซับซ้อนอย่างมืออาชีพ
' จัดอบรมโปรแกรม MS Excel
' https://www.excel-vba.ru
' [ป้องกันอีเมล]
'เว็บมันนี่—R298726502453; Yandex.Money — 41001332272872
' วัตถุประสงค์:
'——————————————————————
ค้นหาย่อยErrLink()
'เราต้องดูในลิงก์ Data -Change เชื่อมโยงไปยังไฟล์ต้นฉบับ
'และใส่คำหลักที่นี่เป็นตัวพิมพ์เล็ก (ส่วนหนึ่งของชื่อไฟล์)
'เครื่องหมายดอกจันจะแทนที่อักขระจำนวนเท่าใดก็ได้ ดังนั้นคุณจึงไม่ต้องกังวลกับชื่อที่แน่นอน
Const sToFndLink$ = “*ยอดขายปี 2018*”
Dim rr เป็นช่วง, rc เป็นช่วง, rres เป็นช่วง, s$
'กำหนดเซลล์ทั้งหมดด้วยการตรวจสอบข้อมูล
เกี่ยวกับข้อผิดพลาดต่อไป
ตั้งค่า rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
ถ้า rr ไม่มีอะไรแล้ว
MsgBox "ไม่มีเซลล์ที่มีการตรวจสอบข้อมูลในแผ่นงานที่ใช้งานอยู่", vbInformation, "www.excel-vba.ru"
ออกจาก Sub
End If
เมื่อเกิดข้อผิดพลาด GoTo 0
'ตรวจสอบแต่ละเซลล์เพื่อหาลิงก์
สำหรับแต่ละ rc ใน rr
'เผื่อในกรณีที่เราข้ามข้อผิดพลาด – สิ่งนี้อาจเกิดขึ้นได้
'แต่ความสัมพันธ์ของเราต้องไม่มีพวกเขาและพวกเขาจะพบอย่างแน่นอน
ส = «»
เกี่ยวกับข้อผิดพลาดต่อไป
s = rc.Validation.Formula1
เมื่อเกิดข้อผิดพลาด GoTo 0
'พบ – เรารวบรวมทุกอย่างในช่วงที่แยกจากกัน
ถ้า LCase ชอบ sToFndLink แล้ว
ถ้า rres Is Nothing แล้ว
ตั้งค่า rres = rc
อื่น
ตั้งค่า rres = ยูเนี่ยน (rc, rres)
End If
End If
ถัดไป
'หากมีการเชื่อมต่อให้เลือกเซลล์ทั้งหมดที่มีการตรวจสอบข้อมูลดังกล่าว
ถ้าไม่ rres ก็ไม่มีอะไรแล้ว
rres เลือก
' rres.Interior.Color = vbRed 'ถ้าคุณต้องการเน้นด้วยสี
End If
ย่อยสิ้นสุด
จำเป็นต้องสร้างโมดูลมาตรฐานในตัวแก้ไขแมโคร แล้วแทรกข้อความนี้ที่นั่น หลังจากนั้นให้เรียกหน้าต่างมาโครโดยใช้คีย์ผสม Alt + F8 จากนั้นเลือกมาโครของเราแล้วคลิกที่ปุ่ม "เรียกใช้" มีบางสิ่งที่ต้องจำไว้เมื่อใช้รหัสนี้:
- ก่อนที่คุณจะค้นหาลิงก์ที่ไม่เกี่ยวข้องอีกต่อไป คุณต้องพิจารณาก่อนว่าลิงก์ที่ใช้สร้างนั้นมีลักษณะอย่างไร ในการดำเนินการนี้ ไปที่เมนู "ข้อมูล" และค้นหารายการ "เปลี่ยนลิงก์" ที่นั่น หลังจากนั้น คุณต้องดูชื่อไฟล์และระบุในเครื่องหมายคำพูด ตัวอย่างเช่นเช่นนี้: Const sToFndLink$ = “*ยอดขายปี 2018*”
- เป็นไปได้ที่จะเขียนชื่อไม่เต็ม แต่เพียงแทนที่อักขระที่ไม่จำเป็นด้วยเครื่องหมายดอกจัน และในเครื่องหมายคำพูด ให้เขียนชื่อไฟล์ด้วยตัวอักษรตัวเล็ก ในกรณีนี้ Excel จะค้นหาไฟล์ทั้งหมดที่มีสตริงดังกล่าวต่อท้าย
- รหัสนี้สามารถตรวจสอบได้เฉพาะลิงก์ในแผ่นงานที่ใช้งานอยู่เท่านั้น
- ด้วยมาโครนี้ คุณสามารถเลือกได้เฉพาะเซลล์ที่พบเท่านั้น คุณต้องลบทุกอย่างด้วยตนเอง นี่เป็นข้อดีเพราะคุณสามารถตรวจสอบทุกอย่างได้อีกครั้ง
- คุณยังสามารถทำให้เซลล์ถูกเน้นด้วยสีพิเศษได้อีกด้วย เมื่อต้องการทำเช่นนี้ ให้เอาเครื่องหมายอะพอสทรอฟีออกก่อนบรรทัดนี้ rres.Interior.Color = vbRed
โดยปกติ หลังจากที่คุณทำตามขั้นตอนที่อธิบายไว้ในคำแนะนำข้างต้นแล้ว จะไม่มีการเชื่อมต่อที่ไม่จำเป็นอีกต่อไป แต่ถ้ามีบางส่วนในเอกสารและคุณไม่สามารถเอาออกได้ด้วยเหตุผลใดก็ตาม (ตัวอย่างทั่วไปคือความปลอดภัยของข้อมูลในชีต) คุณสามารถใช้ลำดับการดำเนินการที่แตกต่างกันได้ คำแนะนำนี้ใช้ได้กับเวอร์ชัน 2007 ขึ้นไปเท่านั้น
- เราสร้างสำเนาสำรองของเอกสาร
- เปิดเอกสารนี้โดยใช้ตัวเก็บถาวร คุณสามารถใช้รูปแบบใดก็ได้ที่รองรับรูปแบบ ZIP ได้ แต่ WinRar จะใช้งานได้เช่นเดียวกับรูปแบบที่มีอยู่ใน Windows
- ในไฟล์เก็บถาวรที่ปรากฏขึ้น คุณต้องค้นหาโฟลเดอร์ xl แล้วเปิด externalLinks
- โฟลเดอร์นี้มีลิงก์ภายนอกทั้งหมด ซึ่งแต่ละลิงก์จะสอดคล้องกับไฟล์ในรูปแบบ externalLink1.xml ทั้งหมดมีหมายเลขเท่านั้น ดังนั้นผู้ใช้จึงไม่มีโอกาสเข้าใจว่านี่คือการเชื่อมต่อประเภทใด เพื่อให้เข้าใจถึงประเภทของการเชื่อมต่อ คุณต้องเปิดโฟลเดอร์ _rels และดูที่นั่น
- หลังจากนั้น เราจะลบลิงก์ทั้งหมดหรือเฉพาะตามสิ่งที่เราเรียนรู้ในไฟล์ externalLinkX.xml.rels
- หลังจากนั้นเราเปิดไฟล์ของเราโดยใช้ Excel จะมีข้อมูลเกี่ยวกับข้อผิดพลาดเช่น “ข้อผิดพลาดในส่วนของเนื้อหาในหนังสือ” เราให้ความยินยอม หลังจากนั้น กล่องโต้ตอบอื่นจะปรากฏขึ้น เราปิดมัน
หลังจากนั้นควรลบลิงก์ทั้งหมด