תוכן
תקשורת היא תכונה שימושית מאוד באקסל. אחרי הכל, לעתים קרובות מאוד משתמשים צריכים להשתמש במידע מקבצים אחרים. אבל במצבים מסוימים, הם יכולים לעשות יותר נזק מתועלת. אחרי הכל, למשל, אם אתה שולח את הקבצים האלה בדואר, הקישורים לא עובדים. היום נדבר בפירוט רב יותר על מה לעשות כדי למנוע בעיה כזו.
מהם מערכות יחסים באקסל
מערכות יחסים באקסל משמשות לעתים קרובות מאוד בשילוב עם פונקציות כגון VPRכדי לקבל מידע מחוברת עבודה אחרת. זה יכול ללבוש צורה של קישור מיוחד המכיל את הכתובת של לא רק התא, אלא גם הספר שבו הנתונים נמצאים. כתוצאה מכך, קישור כזה נראה בערך כך: =VLOOKUP(A2;'[Sales 2018.xlsx]דוח'!$A:$F;4;0). לחלופין, לייצוג פשוט יותר, ייצג את הכתובת בצורה הבאה: ='[מכירות 2018.xlsx]דוח'!$A1. בואו ננתח כל אחד ממרכיבי הקישור מסוג זה:
- [מכירות 2018.xlsx]. קטע זה מכיל קישור לקובץ שממנו ברצונך לקבל מידע. זה נקרא גם המקור.
- תמונות. השתמשנו בשם הבא, אבל זה לא השם שצריך להיות. בלוק זה מכיל את שם הגיליון שבו אתה צריך למצוא מידע.
- $A:$F ו-$A1 - הכתובת של תא או טווח המכילים נתונים הכלולים במסמך זה.
למעשה, תהליך יצירת קישור למסמך חיצוני נקרא קישור. לאחר שרשמנו את כתובת התא הכלול בקובץ אחר, התוכן של לשונית "נתונים" משתנה. כלומר, כפתור "שנה חיבורים" הופך לפעיל, בעזרתו המשתמש יכול לערוך את החיבורים הקיימים.
מהות הבעיה
ככלל, לא מתעוררים קשיים נוספים על מנת להשתמש בקישורים. גם אם נוצר מצב בו התאים משתנים, אז כל הקישורים מתעדכנים אוטומטית. אבל אם כבר משנה את שם חוברת העבודה עצמה או מעביר אותה לכתובת אחרת, Excel הופך חסר אונים. לכן, הוא מייצר את ההודעה הבאה.
כאן, למשתמש יש שתי אפשרויות אפשריות כיצד לפעול במצב זה. הוא יכול ללחוץ על "המשך" ואז השינויים לא יתעדכנו, או שהוא יכול ללחוץ על כפתור "שנה אסוציאציות", שבאמצעותו הוא יכול לעדכן אותם באופן ידני. לאחר שנלחץ על כפתור זה, יופיע חלון נוסף בו ניתן יהיה לשנות את הקישורים, שיציין היכן נמצא הקובץ הנכון כרגע ואיך קוראים לו.
בנוסף, אתה יכול לערוך קישורים דרך הכפתור המתאים שנמצא בכרטיסייה "נתונים". המשתמש יכול גם לגלות שהחיבור מנותק בשגיאת #LINK המופיעה כאשר לאקסל אין אפשרות לגשת למידע שנמצא בכתובת ספציפית בשל העובדה שהכתובת עצמה אינה חוקית.
איך לבטל קישור באקסל
אחת השיטות הפשוטות ביותר לפתור את המצב המתואר לעיל למקרה שלא תוכל לעדכן את מיקום הקובץ המקושר בעצמך היא למחוק את הקישור עצמו. זה קל במיוחד לביצוע אם המסמך מכיל קישור אחד בלבד. לשם כך, עליך לבצע את רצף השלבים הבא:
- פתח את התפריט "נתונים".
- אנו מוצאים את הסעיף "חיבורים", ושם - את האפשרות "שנה חיבורים".
- לאחר מכן, לחץ על "בטל קישור".
אם אתה מתכוון לשלוח את הספר הזה לאדם אחר, מומלץ מאוד לעשות זאת מראש. אחרי הכל, לאחר מחיקת הקישורים, כל הערכים הכלולים במסמך אחר ייטענו אוטומטית לקובץ, בשימוש בנוסחאות, ובמקום כתובת התא, המידע בתאים המתאימים פשוט יהפוך לערכים .
כיצד לבטל את הקישור של כל הספרים
אבל אם מספר הקישורים הופך גדול מדי, מחיקה ידנית שלהם עלולה להימשך זמן רב. כדי לפתור בעיה זו במכה אחת, אתה יכול להשתמש במאקרו מיוחד. זה נמצא בתוסף VBA-Excel. אתה צריך להפעיל אותו וללכת ללשונית באותו שם. יהיה קטע "קישורים", שבו עלינו ללחוץ על כפתור "שבור את כל הקישורים".
קוד VBA
אם לא ניתן להפעיל את התוסף הזה, אתה יכול ליצור מאקרו בעצמך. כדי לעשות זאת, פתח את עורך Visual Basic על ידי לחיצה על מקשי Alt + F11, וכתוב את השורות הבאות בשדה הזנת הקוד.
Sub UnlinkWorkBooks()
עמום WbLinks
Dim ו-As Long
בחר Case MsgBox ("כל ההפניות לספרים אחרים יוסרו מהקובץ הזה, ונוסחאות המתייחסות לספרים אחרים יוחלפו בערכים." & vbCrLf & "האם אתה בטוח שאתה רוצה להמשיך?", 36, "בטל קישור?" )
תיק 7′ מס
צא תת
סיום בחירה
WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
אם לא IsEmpty(WbLinks) אז
עבור i = 1 ל-UBound(WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks
הַבָּא
אחר
MsgBox "אין קישורים לספרים אחרים בקובץ הזה.", 64, "קישורים לספרים אחרים"
סוף אם
End Sub
כיצד לשבור קשרים רק בטווח הנבחר
מעת לעת, מספר הקישורים גדול מאוד, והמשתמש חושש שאחרי מחיקת אחד מהם, לא ניתן יהיה להחזיר הכל בחזרה אם חלקם היו מיותרים. אבל זו בעיה שקל להימנע ממנה. לשם כך, עליך לבחור את הטווח שבו למחוק קישורים ולאחר מכן למחוק אותם. לשם כך, עליך לבצע את רצף הפעולות הבא:
- בחר את מערך הנתונים שיש לשנות.
- התקן את התוסף VBA-Excel ולאחר מכן עבור לכרטיסייה המתאימה.
- לאחר מכן, אנו מוצאים את תפריט "קישורים" ולוחצים על כפתור "שבור קישורים בטווחים שנבחרו".
לאחר מכן, כל הקישורים בקבוצת התאים שנבחרה יימחקו.
מה לעשות אם הקשרים לא נשברים
כל האמור לעיל נשמע טוב, אבל בפועל תמיד יש כמה ניואנסים. למשל, יכול להיות מצב שבו הקשרים לא נשברים. במקרה זה, עדיין מופיעה תיבת דו-שיח המציינת כי לא ניתן לעדכן את הקישורים באופן אוטומטי. מה לעשות במצב הזה?
- ראשית, עליך לבדוק אם מידע כלשהו כלול בטווחים הנקובים. כדי לעשות זאת, הקש על צירוף המקשים Ctrl + F3 או פתח את הכרטיסייה "נוסחאות" - "מנהל שמות". אם שם הקובץ מלא, אתה רק צריך לערוך אותו או להסיר אותו לגמרי. לפני מחיקת טווחים בעלי שם, עליך להעתיק את הקובץ למקום אחר כדי שתוכל לחזור לגרסה המקורית אם בוצעו הצעדים הלא נכונים.
- אם אינך יכול לפתור את הבעיה על ידי הסרת שמות, תוכל לבדוק עיצוב מותנה. ניתן להפנות לתאים בטבלה אחרת בכללי עיצוב מותנה. כדי לעשות זאת, מצא את הפריט המתאים בלשונית "בית", ולאחר מכן לחץ על כפתור "ניהול קבצים".
בדרך כלל, Excel לא נותן לך את היכולת לתת את הכתובת של חוברות עבודה אחרות בעיצוב מותנה, אבל אתה עושה זאת אם אתה מתייחס לטווח בעל שם עם הפניה לקובץ אחר. בדרך כלל, גם לאחר הסרת הקישור, הקישור נשאר. אין בעיה להסיר קישור כזה, כי הקישור למעשה לא עובד. לכן שום דבר רע לא יקרה אם תסיר אותו.
אתה יכול גם להשתמש בפונקציה "בדיקת נתונים" כדי לברר אם יש קישורים מיותרים. קישורים נשארים בדרך כלל אם נעשה שימוש בסוג "רשימה" של אימות נתונים. אבל מה לעשות אם יש הרבה תאים? האם באמת יש צורך לבדוק כל אחד מהם ברצף? ברור שלא. אחרי הכל, זה ייקח הרבה מאוד זמן. לכן, עליך להשתמש בקוד מיוחד כדי לשמור אותו באופן משמעותי.
אפשרות מפורשת
'————————————————————————————
מחבר: The_Prist(שצ'רבקוב דמיטרי)
פיתוח מקצועי של יישומים עבור MS Office בכל מורכבות
' העברת הדרכות על MS Excel
' https://www.excel-vba.ru
' [מוגן באימייל]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' מטרה:
'————————————————————————————
Sub FindErrLink()
אנחנו צריכים להסתכל בקישור נתונים -שינוי קישורים לקובץ המקור
'ושם את מילות המפתח כאן באותיות קטנות (חלק משם הקובץ)
'כוכבית פשוט מחליפה כל מספר של תווים כך שאתה לא צריך לדאוג לגבי השם המדויק
Const sToFndLink$ = "*מכירות 2018*"
Dim rr As Range, rc As Range, rres As Range, s$
'להגדיר את כל התאים עם אימות נתונים
על שגיאה קורות הבא
הגדר rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
אם rr זה כלום אז
MsgBox "אין תאים עם אימות נתונים בגיליון הפעיל", vbInformation, "www.excel-vba.ru"
צא תת
סוף אם
בשגיאה GoTo 0
'בדוק בכל תא קישורים
עבור כל rc ב-rr
"למקרה, אנחנו מדלגים על שגיאות - זה גם יכול לקרות
"אבל הקשרים שלנו חייבים להיות בלעדיהם והם בהחלט יימצאו
s = «»
על שגיאה קורות הבא
s = rc.Validation.Formula1
בשגיאה GoTo 0
'נמצא - אנחנו אוספים הכל בטווח נפרד
אם LCase(ים) כמו sToFndLink אז
אם rres הוא כלום אז
הגדר rres = rc
אחר
הגדר rres = Union(rc, rres)
סוף אם
סוף אם
הַבָּא
אם יש חיבור, בחר את כל התאים עם בדיקות נתונים כאלה
אם לא rres זה כלום אז
rres.בחר
' rres.Interior.Color = vbRed 'אם אתה רוצה להדגיש עם צבע
סוף אם
End Sub
יש צורך ליצור מודול סטנדרטי בעורך המאקרו, ולאחר מכן להכניס את הטקסט הזה לשם. לאחר מכן, התקשר לחלון המאקרו באמצעות צירוף המקשים Alt + F8, ולאחר מכן בחר את המאקרו שלנו ולחץ על כפתור "הפעלה". יש לזכור כמה דברים בעת שימוש בקוד זה:
- לפני שאתם מחפשים קישור שכבר לא רלוונטי, תחילה עליכם לקבוע כיצד נראה הקישור שדרכו הוא נוצר. כדי לעשות זאת, עבור לתפריט "נתונים" ומצא שם את הפריט "שנה קישורים". לאחר מכן, עליך להסתכל על שם הקובץ, ולציין אותו במרכאות. לדוגמה, כך: Const sToFndLink$ = "*מכירות 2018*"
- אפשר לכתוב את השם לא במלואו, אלא פשוט להחליף תווים מיותרים בכוכבית. ובמרכאות, כתוב את שם הקובץ באותיות קטנות. במקרה זה, Excel ימצא בסוף את כל הקבצים המכילים מחרוזת כזו.
- קוד זה מסוגל לבדוק רק קישורים בגיליון הפעיל כעת.
- עם מאקרו זה, אתה יכול לבחור רק את התאים שהוא מצא. אתה צריך למחוק הכל ידנית. זה יתרון, כי אתה יכול לבדוק הכל שוב.
- ניתן גם להדגיש את התאים בצבע מיוחד. לשם כך, הסר את האפוסתרוף לפני השורה הזו. rres.Interior.Color = vbRed
בדרך כלל, לאחר השלמת השלבים המתוארים בהוראות לעיל, לא אמורים להיות יותר חיבורים מיותרים. אבל אם יש כמה מהם במסמך ואתה לא מצליח להסיר אותם מסיבה זו או אחרת (דוגמה טיפוסית היא אבטחת הנתונים בגיליון), אז אתה יכול להשתמש ברצף אחר של פעולות. הוראה זו תקפה רק עבור גרסאות 2007 ומעלה.
- אנו יוצרים עותק גיבוי של המסמך.
- פתח את המסמך הזה באמצעות הארכיון. אתה יכול להשתמש בכל אחד שתומך בפורמט ZIP, אבל גם WinRar יעבוד, כמו גם זה המובנה ב-Windows.
- בארכיון שמופיע, עליך למצוא את תיקיית xl, ולאחר מכן לפתוח קישורים חיצוניים.
- תיקיה זו מכילה את כל הקישורים החיצוניים, שכל אחד מהם מתאים לקובץ בצורת externalLink1.xml. כולם רק ממוספרים, ולכן אין למשתמש אפשרות להבין באיזה סוג של חיבור מדובר. כדי להבין איזה סוג של חיבור, אתה צריך לפתוח את התיקיה _rels, ולהסתכל עליה שם.
- לאחר מכן, אנו מסירים את כל הקישורים או הקישורים הספציפיים, על סמך מה שאנו לומדים בקובץ externalLinkX.xml.rels.
- לאחר מכן, אנו פותחים את הקובץ שלנו באמצעות Excel. יהיה מידע על שגיאה כמו "שגיאה בחלק מהתוכן בספר". אנחנו נותנים הסכמה. לאחר מכן, תופיע דו-שיח נוסף. אנחנו סוגרים את זה.
לאחר מכן, יש להסיר את כל הקישורים.