כיצד ליצור רשימה דינמית של שמות גליונות עבודה ב-Excel?

אם יש לך מספר גליונות בעבודה, וברצונך ליצור רשימה דינמית של כל שמות הגליונות בגיליון חדש באותה חוברת העבודה, כיצד תוכל לעשות זאת? כעת מדריך זה מציג כמה טריקים כדי להשלים משימה זו במהירות ב-Excel.

צור רשימה דינמית של שמות גליונות עם הגדרת שם ונוסחה

צור רשימה דינמית של שמות גליונות עם קוד VBA

צור רשימה דינמית של שמות גליונות עם Kutools עבור Excel

הצג רשימה דינמית של שמות גליונות עם Kutools עבור Excel

צור רשימה דינמית של שמות גליונות עם הגדרת שם ונוסחה

1. בחר תא בגיליון ריק, כאן אני בוחר A1, ולאחר מכן לחץ על נוסחאות > הגדר שם. ראה צילום מסך:

2. לאחר מכן, בתוך שם חדש בדיאלוג, הקלד גליונות לתוך שם תיבת טקסט (אתה יכול לשנות לפי הצורך), והקלד את הנוסחה הבאה =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","") לתוך תיבת הטקסט 'מתייחס אל'. ראה צילום מסך:

3. לחץ על אישור. ואז עבור לתא שנבחר (A1) והקלד את הנוסחה הבאה =INDEX(Sheets,ROWS($A$1:$A1)) (A1 הוא התא שבו אתה מקליד את הנוסחה, "Sheets" הוא השם שהגדרת בשלב 2) לתוכו, ולאחר מכן גרור את ידית האוטומטיקה למטה עד ש-#REF! יופיע.

עצה: אם ישנם גליונות עבודה שנמחקו או נוספו, יהיה עליך לעבור ל-A1 וללחוץ על מקש Enter ואז לגרור את ידית האוטומטיקה שוב.

צור רשימה דינמית של שמות גליונות עם קוד VBA

אם ברצונך ליצור רשימה דינמית של שמות גליונות שניתן לקשר לכל גיליון, באפשרותך להשתמש בקוד VBA.

1. צור גיליון עבודה חדש ושנה את שמו ל-Index. ראה צילום מסך:

2. לחץ ימני על שם הגיליון של Index, בחר צפה בקוד מתפריט ההקשר. ראה צילום מסך:

3. בחלון הקופץ, העתק והדבק את קוד ה-VBA הבא לתוכו.

VBA: צור רשימה דינמית של שמות גליונות.

Private Sub Worksheet_Activate()
	'Updateby20150305
	Dim xSheet As Worksheet
	Dim xRow As Integer
	Dim calcState As Long
	Dim scrUpdateState As Long
	Application.ScreenUpdating = False
	xRow                       = 1
	With Me
		.Columns(1).ClearContents
		.Cells(1, 1) = "INDEX"
		.Cells(1, 1).Name = "Index"
	End With
	For Each xSheet In Application.Worksheets
		If xSheet.Name <> Me.Name Then
			xRow                     = xRow + 1
			With xSheet
				.Range("A1").Name = "Start_" & xSheet.Index
				.Hyperlinks.Add anchor: = .Range("A1"), Address: = "", _
				SubAddress:             = "Index", TextToDisplay: = "Back to Index"
			End With
			Me.Hyperlinks.Add anchor: = Me.Cells(xRow, 1), Address: = "", _
			SubAddress: = "Start_" & xSheet.Index, TextToDisplay: = xSheet.Name
		End If
	Next
	Application.ScreenUpdating = True
End Sub

4. לחץ על הרץ או F5 להרצת ה-VBA, כעת נוצרה רשימה דינמית של שמות גליונות.

עצה:

1. כאשר גליון העבודה מחוברת העבודה נמחק או מוכנס, רשימת שמות הגליונות תשתנה אוטומטית.

2. באפשרותך ללחוץ על שם הגיליון ברשימת השמות כדי לעבור לגיליון.

עם שתי השיטות לעיל הן לא מספיק נוחות, אם אתה מעוניין בכלי חדש, באפשרותך לעבור לשתי השיטות הבאות שעשויות להפוך את המשימה ליותר קלה.

צור רשימה דינמית של שמות גליונות עם Kutools עבור Excel

 אם אתה רק רוצה מהר לרשום את כל שמות הגליונות של חוברת העבודה ולהקיש אותם לגיליונות המקוריים, באפשרותך להשתמש ב-Kutools עבור Excel’s צור טבלת קישור.

לאחר התקנת Kutools עבור Excel בחינם, אנא עשה כך:

1. לחץ על Kutools Plus > Worksheet > צור טבלת קישור. ראה צילום מסך:

2. בדיאלוג צור טבלת קישור:

 

(1) סמן את הסגנון המבוקש שלך ב סגנון חלק;
(2) שנה את שם הגיליון החדש על ידי הקלדת הנתונים לתוך שם הטבלה המקושרת תיבת טקסט;
(3) ציין את המיקום שבו תמקם את הגיליון המעודכן בתיבה מיקום בחוברת העבודה רשימה;
(4) אם ברצונך להציג את שמות הגליונות ברשימה אחת, בחר שורה אחת ב מספר השורות לכסות רשימה.

3. לחץ על אישור. כעת תוכל לראות את שמות הגליונות מפורטים.

עצות:

1. באפשרותך ללחוץ על שם הגיליון כדי לעבור במהירות לגיליון המקורי שלו.

2. הרשימה או שמות הגליונות אינם יכולים להשתנות באופן דינמי עם הכנסת או מחיקת גליונות.

3. למעשה, אתה גם יכול ליצור רשימה של כפתורים כדי לקשר לגליונות המתאימים, רק צריך לסמן צור כפתורים ומקרו בדיאלוג. ראה צילום מסך:

לחץ כאן כדי לדעת עוד אודות צור טבלת קישור.

  צור רשימה ניתנת ללחיצה של שמות גליונות

הצג רשימה דינמית של שמות גליונות עם Kutools עבור Excel

אם באמצעות Kutools עבור Excel, אתה גם יכול להשתמש בכלי ניווט כדי להציג את שמות גליונות העבודה המקושרים בחלונית

לאחר התקנת Kutools עבור Excel בחינם, אנא עשה כך:

1. לחץ על Kutools > ניווט. לחץ על חוברת עבודה וגיליון כדי להציג את חוברת העבודה והגליון, ובאפשרותך לבחור חוברת עבודה, ואז גליונות העבודה שלה יוצגו ב ניווט חלונית. ראה צילום מסך:
   

עצה:

כאשר ישנם גליונות עבודה שנמחקו או נוספו, באפשרותך ללחוץ על כפתור רענון  בתוך ניווטחלונית כדי לרענן את שמות גליונות העבודה.

לחץ כאן למידע נוסף אודות ניווט.

  חלונית ניווט -- רשימות גליונות