المحتويات
التواصل هو ميزة مفيدة للغاية في Excel. بعد كل شيء ، غالبًا ما يتعين على المستخدمين استخدام معلومات من ملفات أخرى. لكن في بعض الحالات ، يمكن أن تضر أكثر مما تنفع. بعد كل شيء ، على سبيل المثال ، إذا قمت بإرسال هذه الملفات عن طريق البريد ، فإن الروابط لا تعمل. اليوم سوف نتحدث بمزيد من التفصيل حول ما يجب القيام به لتجنب مثل هذه المشكلة.
ما هي العلاقات في Excel
غالبًا ما تُستخدم العلاقات في Excel جنبًا إلى جنب مع وظائف مثل VPRللحصول على معلومات من مصنف آخر. يمكن أن يتخذ شكل رابط خاص لا يحتوي على عنوان الخلية فحسب ، بل أيضًا الكتاب الذي توجد به البيانات. نتيجة لذلك ، يبدو هذا الرابط كالتالي: = VLOOKUP (A2؛ '[مبيعات 2018.xlsx] تقرير'! $ A: $ F؛ 4؛ 0). أو ، للحصول على تمثيل أبسط ، قم بتمثيل العنوان بالشكل التالي: = '[مبيعات 2018.xlsx] تقرير'! $ A1. دعنا نحلل كل عنصر من عناصر الارتباط من هذا النوع:
- [مبيعات 2018.xlsx]. يحتوي هذا الجزء على ارتباط إلى الملف الذي تريد الحصول على معلومات منه. ويسمى أيضا المصدر.
- الصور. استخدمنا الاسم التالي ، لكن هذا ليس الاسم الذي ينبغي أن يكون. تحتوي هذه الكتلة على اسم الورقة التي تحتاج إلى العثور على معلومات فيها.
- $ A: $ F и $ A1 - عنوان خلية أو نطاق يحتوي على بيانات واردة في هذا المستند.
في الواقع ، تسمى عملية إنشاء ارتباط إلى مستند خارجي بالربط. بعد تسجيل عنوان الخلية الموجودة في ملف آخر ، تتغير محتويات علامة التبويب "البيانات". وبالتحديد ، يصبح زر "تغيير الاتصالات" نشطًا ، ويمكن للمستخدم من خلاله تعديل الاتصالات الحالية.
جوهر المشكلة
كقاعدة عامة ، لا تنشأ صعوبات إضافية لاستخدام الروابط. حتى إذا ظهر موقف تتغير فيه الخلايا ، فسيتم تحديث جميع الروابط تلقائيًا. ولكن إذا قمت بالفعل بإعادة تسمية المصنف نفسه أو نقله إلى عنوان مختلف ، فإن Excel يصبح عاجزًا. لذلك ، تظهر الرسالة التالية.
هنا ، لدى المستخدم خياران محتملان لكيفية التصرف في هذه الحالة. يمكنه النقر فوق "متابعة" وبعد ذلك لن يتم تحديث التغييرات ، أو يمكنه النقر فوق الزر "تغيير الاقترانات" ، والذي يمكنه من خلاله تحديثها يدويًا. بعد أن نضغط على هذا الزر ، ستظهر نافذة إضافية يمكن من خلالها تغيير الروابط ، تشير إلى مكان الملف الصحيح في الوقت الحالي وما يطلق عليه.
بالإضافة إلى ذلك ، يمكنك تعديل الروابط من خلال الزر المقابل الموجود في علامة التبويب "البيانات". يمكن للمستخدم أيضًا معرفة أن الاتصال مقطوع بسبب الخطأ #LINK ، والذي يظهر عندما يتعذر على Excel الوصول إلى المعلومات الموجودة في عنوان معين نظرًا لحقيقة أن العنوان نفسه غير صالح.
كيفية فك الارتباط في Excel
واحدة من أبسط الطرق لحل الموقف الموصوف أعلاه في حالة عدم تمكنك من تحديث موقع الملف المرتبط بنفسك هي حذف الرابط نفسه. من السهل القيام بذلك بشكل خاص إذا كان المستند يحتوي على ارتباط واحد فقط. للقيام بذلك ، يجب عليك تنفيذ التسلسل التالي من الخطوات:
- افتح قائمة "البيانات".
- نجد قسم "اتصالات" ، وهناك - خيار "تغيير الاتصالات".
- بعد ذلك ، انقر فوق "Unlink".
إذا كنت تنوي إرسال هذا الكتاب بالبريد إلى شخص آخر ، فيوصى بشدة أن تقوم بذلك مقدمًا. بعد كل شيء ، بعد حذف الروابط ، سيتم تحميل جميع القيم الموجودة في مستند آخر تلقائيًا في الملف ، وتستخدم في الصيغ ، وبدلاً من عنوان الخلية ، سيتم ببساطة تحويل المعلومات الموجودة في الخلايا المقابلة إلى قيم .
كيفية فك ارتباط كل الكتب
ولكن إذا أصبح عدد الروابط كبيرًا جدًا ، فقد يستغرق حذفها يدويًا وقتًا طويلاً. لحل هذه المشكلة دفعة واحدة ، يمكنك استخدام ماكرو خاص. إنه موجود في الملحق VBA-Excel. تحتاج إلى تنشيطه والانتقال إلى علامة التبويب التي تحمل الاسم نفسه. سيكون هناك قسم "روابط" نحتاج فيه إلى النقر فوق الزر "كسر جميع الروابط".
كود فبا
إذا لم يكن من الممكن تنشيط هذه الوظيفة الإضافية ، يمكنك إنشاء ماكرو بنفسك. للقيام بذلك ، افتح محرر Visual Basic بالضغط على مفاتيح Alt + F11 ، واكتب الأسطر التالية في حقل إدخال الرمز.
Sub UnlinkWorkBooks ()
خافت WbLinks
قاتمة وطويلة
حدد Case MsgBox ("ستتم إزالة جميع المراجع إلى كتب أخرى من هذا الملف ، وسيتم استبدال الصيغ التي تشير إلى كتب أخرى بالقيم." & vbCrLf & "هل أنت متأكد من أنك تريد المتابعة؟" ، 36 ، "إلغاء الارتباط؟" )
الحالة 7 - لا
خروج الفرعية
إنهاء اختر
WbLinks = ActiveWorkbook.LinkSources (النوع: = xlLinkTypeExcelLinks)
If Not IsEmpty (WbLinks) ثم
بالنسبة إلى i = 1 إلى UBound (WbLinks)
اسم ActiveWorkbook.BreakLink: = WbLinks (i) ، النوع: = xlLinkTypeExcelLinks
التالى
آخر
MsgBox "لا توجد روابط لكتب أخرى في هذا الملف." ، 64 ، "روابط لكتب أخرى"
إنهاء حالة
نهاية الفرعية
كيفية قطع العلاقات فقط في النطاق المحدد
من وقت لآخر ، يكون عدد الروابط كبيرًا جدًا ، ويخشى المستخدم أنه بعد حذف أحدها ، لن يكون من الممكن إعادة كل شيء مرة أخرى إذا كان بعضها غير ضروري. لكن هذه مشكلة يسهل تجنبها. للقيام بذلك ، تحتاج إلى تحديد النطاق الذي تريد حذف الارتباطات فيه ، ثم حذفها. للقيام بذلك ، يجب عليك تنفيذ التسلسل التالي من الإجراءات:
- حدد مجموعة البيانات التي تريد تعديلها.
- قم بتثبيت الوظيفة الإضافية VBA-Excel ، ثم انتقل إلى علامة التبويب المناسبة.
- بعد ذلك ، نجد قائمة "الروابط" وانقر على الزر "كسر الروابط في النطاقات المحددة".
بعد ذلك ، سيتم حذف جميع الروابط في مجموعة الخلايا المحددة.
ماذا تفعل إذا لم يتم قطع العلاقات
كل ما سبق يبدو جيدًا ، ولكن هناك دائمًا بعض الفروق الدقيقة في الممارسة العملية. على سبيل المثال ، قد يكون هناك موقف لا يتم فيه قطع العلاقات. في هذه الحالة ، لا يزال يظهر مربع حوار يوضح أنه لا يمكن تحديث الروابط تلقائيًا. ماذا تفعل في هذه الحالة؟
- أولاً ، تحتاج إلى التحقق مما إذا كانت هناك أية معلومات واردة في النطاقات المحددة. للقيام بذلك ، اضغط على مجموعة المفاتيح Ctrl + F3 أو افتح علامة التبويب "الصيغ" - "مدير الاسم". إذا كان اسم الملف ممتلئًا ، فأنت تحتاج فقط إلى تعديله أو إزالته تمامًا. قبل حذف النطاقات المسماة ، تحتاج إلى نسخ الملف إلى موقع آخر حتى تتمكن من العودة إلى الإصدار الأصلي إذا تم اتخاذ الخطوات الخاطئة.
- إذا لم تتمكن من حل المشكلة عن طريق إزالة الأسماء ، يمكنك التحقق من التنسيق الشرطي. يمكن الرجوع إلى الخلايا الموجودة في جدول آخر في قواعد التنسيق الشرطي. للقيام بذلك ، ابحث عن العنصر المقابل في علامة التبويب "الصفحة الرئيسية" ، ثم انقر فوق الزر "إدارة الملفات".
عادةً ، لا يمنحك Excel القدرة على إعطاء عنوان المصنفات الأخرى بتنسيق شرطي ، لكنك تفعل ذلك إذا أشرت إلى نطاق مسمى بمرجع إلى ملف آخر. عادة ، حتى بعد إزالة الارتباط ، يبقى الرابط. لا توجد مشكلة في إزالة مثل هذا الرابط ، لأن الرابط في الواقع لا يعمل. لذلك ، لن يحدث شيء سيء إذا قمت بإزالته.
يمكنك أيضًا استخدام وظيفة "فحص البيانات" لمعرفة ما إذا كانت هناك أية روابط غير ضرورية. تبقى الروابط عادةً إذا تم استخدام نوع "القائمة" للتحقق من صحة البيانات. ولكن ماذا تفعل إذا كان هناك الكثير من الخلايا؟ هل من الضروري حقًا التحقق من كل منهم بالتسلسل؟ بالطبع لا. بعد كل شيء ، سوف يستغرق وقتا طويلا جدا. لذلك ، تحتاج إلى استخدام رمز خاص لحفظه بشكل كبير.
الخيار صريح
'——————————————————————————————
المؤلف: The_Prist (Shcherbakov Dmitry)
التطوير المهني لتطبيقات MS Office مهما كانت درجة تعقيدها
إجراء تدريبات على برنامج MS Excel
https://www.excel-vba.ru
"[البريد الإلكتروني محمي]
"WebMoney — R298726502453 ؛ ياندكس موني - 41001332272872
' غاية:
'——————————————————————————————
FindErrLink فرعي ()
نحن بحاجة إلى البحث في رابط البيانات - تغيير الروابط إلى الملف المصدر
"ووضع الكلمات الرئيسية هنا بأحرف صغيرة (جزء من اسم الملف)
تحل علامة النجمة محل أي عدد من الأحرف ، لذا لا داعي للقلق بشأن الاسم الدقيق
Const sToFndLink $ = "* مبيعات 2018 *"
خافت rr كنطاق ، rc كنطاق ، rres كنطاق ، s $
تحديد جميع الخلايا مع التحقق من صحة البيانات
على خطأ استئناف التالي
تعيين rr = ActiveSheet.UsedRange.SpecialCells (xlCellTypeAllValidation)
إذا كان rr لا شيء إذًا
MsgBox "لا توجد خلايا مع التحقق من صحة البيانات على الورقة النشطة" ، vbInformation ، "www.excel-vba.ru"
خروج الفرعية
إنهاء حالة
على خطأ GoTo 0
تحقق من كل خلية بحثًا عن روابط
لكل RC In rr
'فقط في حالة تخطي الأخطاء - يمكن أن يحدث هذا أيضًا
'لكن اتصالاتنا يجب أن تكون بدونها وسيتم العثور عليها بالتأكيد
ق = «»
على خطأ استئناف التالي
s = rc.Validation.Formula1
على خطأ GoTo 0
وجدت - نجمع كل شيء في نطاق منفصل
إذا كان LCase (s) Like sToFndLink ثم
إذا كان rres لا شيء إذن
تعيين rres = rc
آخر
تعيين rres = Union (rc، rres)
إنهاء حالة
إنهاء حالة
التالى
إذا كان هناك اتصال ، فحدد جميع الخلايا التي تحتوي على عمليات فحص البيانات هذه
إذا لم يكن rres لا شيء ثم
حدد
"rres.Interior.Color = vbRed" إذا كنت تريد التظليل بالألوان
إنهاء حالة
نهاية الفرعية
من الضروري إنشاء وحدة نمطية قياسية في محرر الماكرو ، ثم إدراج هذا النص هناك. بعد ذلك ، اتصل بنافذة الماكرو باستخدام مجموعة المفاتيح Alt + F8 ، ثم حدد الماكرو الخاص بنا وانقر فوق الزر "تشغيل". هناك بعض الأشياء التي يجب وضعها في الاعتبار عند استخدام هذا الرمز:
- قبل البحث عن ارتباط لم يعد ذا صلة ، يجب عليك أولاً تحديد الشكل الذي يبدو عليه الارتباط الذي تم إنشاؤه من خلاله. للقيام بذلك ، انتقل إلى قائمة "البيانات" وابحث عن عنصر "تغيير الروابط" هناك. بعد ذلك ، تحتاج إلى إلقاء نظرة على اسم الملف وتحديده بين علامتي اقتباس. على سبيل المثال ، مثل هذا: Const sToFndLink $ = "* مبيعات 2018 *"
- من الممكن كتابة الاسم ليس كاملاً ، ولكن ببساطة استبدل الأحرف غير الضرورية بعلامة النجمة. وفي علامات الاقتباس ، اكتب اسم الملف بأحرف صغيرة. في هذه الحالة ، سيجد Excel جميع الملفات التي تحتوي على مثل هذه السلسلة في النهاية.
- هذا الرمز قادر فقط على التحقق من الروابط الموجودة في الورقة النشطة حاليًا.
- باستخدام هذا الماكرو ، يمكنك فقط تحديد الخلايا التي عثر عليها. يجب عليك حذف كل شيء يدويًا. هذه ميزة إضافية ، لأنه يمكنك إعادة التحقق من كل شيء مرة أخرى.
- يمكنك أيضًا جعل الخلايا مميزة بلون خاص. للقيام بذلك ، قم بإزالة الفاصلة العليا قبل هذا السطر. rres.Interior.Color = vbRed
عادةً ، بعد إكمال الخطوات الموضحة في الإرشادات أعلاه ، لن يكون هناك المزيد من الاتصالات غير الضرورية. ولكن إذا كان هناك بعضها في المستند ولم تتمكن من إزالتها لسبب أو لآخر (مثال نموذجي هو أمان البيانات في ورقة) ، فيمكنك استخدام تسلسل مختلف من الإجراءات. هذه التعليمات صالحة فقط للإصدارات 2007 والإصدارات الأحدث.
- نقوم بإنشاء نسخة احتياطية من المستند.
- افتح هذا المستند باستخدام أرشيفي. يمكنك استخدام أي تنسيق يدعم تنسيق ZIP ، لكن WinRar سيعمل أيضًا ، بالإضافة إلى التنسيق المضمن في Windows.
- في الأرشيف الذي يظهر ، تحتاج إلى العثور على مجلد xl ، ثم فتح ارتباطات خارجية.
- يحتوي هذا المجلد على كافة الارتباطات الخارجية ، كل منها يتوافق مع ملف على شكل externalLink1.xml. جميعها مرقمة فقط ، وبالتالي لا تتاح للمستخدم الفرصة لفهم نوع الاتصال هذا. لفهم نوع الاتصال ، تحتاج إلى فتح مجلد _براميل ، وإلقاء نظرة عليه هناك.
- بعد ذلك ، نزيل كل الروابط أو روابط محددة ، بناءً على ما نتعلمه في ملف externalLinkX.xml.rels.
- بعد ذلك ، نفتح ملفنا باستخدام Excel. ستكون هناك معلومات حول خطأ مثل "خطأ في جزء من محتوى الكتاب". نعطي الموافقة. بعد ذلك ، سيظهر مربع حوار آخر. نحن نغلقه.
بعد ذلك ، يجب إزالة جميع الروابط.