צ' שואל:
יש לי קובץ אקסל עם כמה גיליונות נתונים, ובגיליון אחד אני שואב מידע משאר הגיליונות.
האם יש אפשרות לסמן על כל התאים (עיצוב מותנה או נוסחא) איזה תא משפיע על תא אחר ואיזה עדיין לא?
בביקורת נוסחאות זה משפיע רק על תא פעיל אחד בכל פעם.
תשובה:
באקסל ניתן לסמן בכל פעם קשרי גומלין (תאים תלויים או תאים מזינים) של תא אחד.
ניתן לסמן קשרי גומלין של כל התאים בגיליון על ידי הגדרת פונקציה בקוד VBA.
מצ"ב דוגמה:
ניצור קובץ אקסל חדש ונשמור אותו בפורמט XLSM המאפשר הפעלת קוד VBA.
בגיליון אקסל נגדיר את הטבלה הבאה המכילה ערכים (שמות פונקציות וקלטים) בעמודות A-C ונוסחאות בעמודה D.
בתא D2 נגדיר את הנוסחה SUM(B2:C2) הסוכמת את הערכים בתאים B2 ו-C2.
בתא D3 נגדיר את הנוסחה AVERAGE(B3:C3) המחשבת ממוצע של הערכים בתאים B3 ו-C3.


נפתח את עורך VBA על ידי לחיצה על Alt+F11 ונוסיף מודול חדש על ידי סימון Modules לחיצה על לחצן ימני והוסף מודול חדש.
נעתיק את הקוד של הפונקציות MarkDependent.
הפונקציה פועלת על הגיליוו הפעיל באקסל.
הפונקציה מבצעת מעבר על השורות ועמודות בהם נעשה שימוש בגיליון אקסל.
לכל תא מבוצעת בדיקת תלות בתאים (תאים מזינים את התא הנבדק) עד לשש רמות (ניתן לשנות זאת בקוד ע"י עדכון ערך Depth לערך המבוקש).
בזמן פעולת הקוד מוצג בשורת הסטאטוס של אקסל מספר העמודה והתא שנבדקים.
Sub MarkDependent()
Dim OffsetCell As Range, NumOfRows As Integer, NumOfCols As Integer, Depth As Integer
Dim c As Integer, r As Integer, k As Integer
With ActiveSheet
Set OffsetCell = Range("A1")
NumOfRows = .UsedRange.Rows.Count
NumOfCols = .UsedRange.Columns.Count
Depth = 6
Application.ScreenUpdating = False
For c = 0 To NumOfCols - 1
DoEvents
For r = 0 To NumOfRows - 1
Application.StatusBar = " עמודה " & c + 1 & " - שורה" & r + 1
If r = Round((NumOfRows - 1) / 2, 0) Then DoEvents
For k = 1 To Depth
OffsetCell.Offset(r, c).ShowDependents
Next
Next
Next
Application.StatusBar = False
End With
End Sub
Dim OffsetCell As Range, NumOfRows As Integer, NumOfCols As Integer, Depth As Integer
Dim c As Integer, r As Integer, k As Integer
With ActiveSheet
Set OffsetCell = Range("A1")
NumOfRows = .UsedRange.Rows.Count
NumOfCols = .UsedRange.Columns.Count
Depth = 6
Application.ScreenUpdating = False
For c = 0 To NumOfCols - 1
DoEvents
For r = 0 To NumOfRows - 1
Application.StatusBar = " עמודה " & c + 1 & " - שורה" & r + 1
If r = Round((NumOfRows - 1) / 2, 0) Then DoEvents
For k = 1 To Depth
OffsetCell.Offset(r, c).ShowDependents
Next
Next
Next
Application.StatusBar = False
End With
End Sub

נבחר את גיליון האקסל בו נדרש לבצע סימון תאים תלויים ובעורך VBA נלחץ על לחצן המשולש הירוק להפעלת הקוד.
התוצאה היא שיסומנו התאים המזינים של הנוסחאות בתאים D1 ו-D2.

בברכה,
צוות אניפיט