Jak utworzyć dynamiczną listę nazw arkuszy w programie Excel?

Jeśli masz wiele arkuszy w skoroszycie i chcesz utworzyć dynamiczną listę wszystkich nazw arkuszy w nowym arkuszu w skoroszycie, jak możesz to zrobić? W tym samouczku przedstawiono kilka sztuczek umożliwiających szybkie wykonanie tej misji w programie Excel.

Utwórz dynamiczną listę nazw arkuszy za pomocą opcji Zdefiniuj nazwę i formułę

Utwórz dynamiczną listę nazw arkuszy z kodem VBA

Utwórz dynamiczną listę nazw arkuszy za pomocą Kutools for Excel

Wyświetl dynamiczną listę nazw arkuszy roboczych za pomocą Kutools for Excel

Utwórz dynamiczną listę nazw arkuszy za pomocą opcji Zdefiniuj nazwę i formułę

1. Wybierz komórkę w pustym arkuszu, tutaj wybieram A1, a następnie klikam Wzory > Zdefiniuj nazwę. zobacz zrzut ekranu:

2. Następnie w Nowe imie dialog, wpisz arkusze w do Imię pole tekstowe (możesz zmienić według potrzeb) i wpisz tę formułę = SUBSTITUTE (GET.WORKBOOK (1), "[" & GET.WORKBOOK (16) & "]", "") w polu tekstowym Odnosi się do. Zobacz zrzut ekranu:

3. kliknij OK. Przejdź do wybranej komórki (A1) i wpisz tę formułę = INDEKS (Arkusze, ROWS (1 $ A $: A1 $)) (A1 to komórka, do której wpisujesz tę formułę, „Arkusze” to nazwa zdefiniowana w kroku 2), a następnie przeciągnij uchwyt autouzupełniania w dół, aż #REF! pojawia się.

Wskazówka: Jeśli są usunięte lub dodane arkusze, musisz przejść do A1 i nacisnąć klawisz Enter, a następnie ponownie przetrzeć uchwyt autouzupełniania.

Utwórz dynamiczną listę nazw arkuszy z kodem VBA

Jeśli chcesz utworzyć dynamiczną listę nazw arkuszy, które mogą łączyć się z każdym arkuszem, możesz użyć kodu VBA.

1. Utwórz nowy arkusz roboczy i zmień jego nazwę na Indeks. Zobacz zrzut ekranu:

2. Kliknij prawym przyciskiem myszy nazwę arkusza Indeks, wybierz Wyświetl kod z menu kontekstowego. Zobacz zrzut ekranu:

3. W wyskakującym oknie skopiuj i wklej do niego poniższy kod VBA.

VBA: Utwórz dynamiczną listę nazw arkuszy.

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. kliknij run or F5 aby uruchomić VBA, tworzona jest teraz dynamiczna lista nazw arkuszy.

Wskazówka:

1. Po usunięciu lub wstawieniu arkusza roboczego ze skoroszytu lista nazw arkuszy zmieni się automatycznie.

2. Możesz kliknąć nazwę arkusza listy nazw, aby przejść do arkusza.

Z powyższymi dwiema metodami nie są wystarczająco wygodne, jeśli jesteś zainteresowany nowym narzędziem, możesz przejść do kolejnych dwóch metod, które mogą ułatwić to zadanie.

Utwórz dynamiczną listę nazw arkuszy za pomocą Kutools for Excel

 Jeśli chcesz tylko szybko wyświetlić wszystkie nazwy arkuszy w skoroszycie i połączyć je z oryginalnymi arkuszami, możesz użyć Kutools dla programu Excel'S Utwórz listę nazw arkuszy.

Po bezpłatna instalacja Kutools dla programu Excel, wykonaj poniższe czynności:

1. kliknij Kutools Plus > Ćwiczenie > Utwórz listę nazw arkuszy. Zobacz zrzut ekranu:

2. w Utwórz listę nazw arkuszy dialog:

 

(1) Sprawdź styl indeksu, którego potrzebujesz Style indeksu arkuszy Sekcja;
(2) Nazwij nowy arkusz indeksu, wpisując dane do Określ nazwę arkusza dla indeksu arkuszy pole tekstowe;
(3) Określ lokalizację, w której zostanie umieszczony dodany arkusz indeksu w pliku Wstaw indeks arkusza lista;
(4) Jeśli chcesz wyświetlić nazwy arkuszy na jednej liście, wybierz jedną kolumnę w Wyświetlanie indeksu arkusza w formacie lista.

3. kliknij Ok. Teraz możesz zobaczyć listę nazw arkuszy.

Porady:

1. Możesz kliknąć nazwę arkusza, aby szybko przejść do oryginalnego arkusza.

2. Nazwy list lub arkuszy nie mogą zmieniać się dynamicznie podczas wstawiania lub usuwania arkuszy.

3. Właściwie możesz również utworzyć listę przycisków, aby połączyć odpowiedni arkusz, wystarczy sprawdzić Zawiera przyciski i makra w oknie dialogowym. zobacz screeshshot:

Kliknij tutaj, aby dowiedzieć się więcej o tworzeniu listy nazw arkuszy.

  Utwórz klikalną listę nazw arkuszy