Jak utworzyć dynamiczny nazwany zakres w programie Excel?
Zazwyczaj Nazwane zakresy są bardzo przydatne dla użytkowników programu Excel, możesz zdefiniować serię wartości w kolumnie, nadać tej kolumnie nazwę, a następnie możesz odwoływać się do tego zakresu według nazwy zamiast odwołań do komórek. Ale przez większość czasu musisz dodać nowe dane, aby w przyszłości rozszerzyć wartości danych z zakresu, którego dotyczy odwołanie. W takim przypadku musisz wrócić do Wzory > Menedżer nazw i przedefiniuj zakres, aby uwzględnić nową wartość. Aby tego uniknąć, możesz utworzyć dynamiczny nazwany zakres, co oznacza, że nie musisz dostosowywać odwołań do komórek za każdym razem, gdy dodajesz nowy wiersz lub kolumnę do listy.
Utwórz dynamiczny nazwany zakres w programie Excel, tworząc tabelę
Utwórz dynamiczny nazwany zakres w programie Excel za pomocą funkcji
Utwórz dynamiczny nazwany zakres w programie Excel za pomocą kodu VBA
Utwórz dynamiczny nazwany zakres w programie Excel, tworząc tabelę
Jeśli używasz programu Excel 2007 lub nowszych wersji, najłatwiejszym sposobem utworzenia dynamicznego nazwanego zakresu jest utworzenie nazwanej tabeli programu Excel.
Powiedzmy, że masz zakres następujących danych, które muszą stać się dynamicznym nazwanym zakresem.
1. Najpierw zdefiniuję nazwy zakresów dla tego zakresu. Wybierz zakres A1: A6 i wprowadź nazwę Data do Nazwa Box, A następnie naciśnij przycisk Wchodzę klucz. Aby zdefiniować nazwę dla zakresu B1: B6 jako Saleprice w ten sam sposób. W tym samym czasie tworzę formułę = suma (cena sprzedaży) w pustej komórce, zobacz zrzut ekranu:
2. Wybierz zakres i kliknij wstawka > Stółzobacz zrzut ekranu:
3, w Utwórz tabelę pole zachęty, zaznacz Moja tabela ma nagłówki (jeśli zakres nie ma nagłówków, odznacz go), kliknij OK przycisk, a dane zakresu zostały przekonwertowane na tabelę. Zobacz zrzuty ekranu:
 |
 |
 |
4. Gdy wprowadzisz nowe wartości po danych, nazwany zakres zostanie automatycznie dostosowany, a utworzona formuła również zostanie zmieniona. Zobacz poniższe zrzuty ekranu:
 |
|
 |
Uwagi:
1. Nowe wprowadzane dane muszą sąsiadować z powyższymi danymi, co oznacza, że nie ma pustych wierszy ani kolumn między nowymi danymi a istniejącymi danymi.
2. W tabeli możesz wstawiać dane między istniejącymi wartościami.
Utwórz dynamiczny nazwany zakres w programie Excel za pomocą funkcji
W programie Excel 2003 lub wcześniejszej wersji pierwsza metoda nie będzie dostępna, więc oto inny sposób dla Ciebie. Następujące PRZESUNIĘCIE () funkcja może wyświadczyć ci tę przysługę, ale jest to nieco kłopotliwe. Przypuśćmy, że mam zakres danych zawierający nazwy zakresów, które zdefiniowałem, na przykład A1: A6 nazwa zakresu to Data, B1: B6 nazwa zakresu to Cena sprzedażyw tym samym czasie tworzę formułę dla Cena sprzedaży. Zobacz zrzut ekranu:
Możesz zmienić nazwy zakresów na nazwy zakresów dynamicznych, wykonując następujące czynności:
1. Przejdź do kliknięcia Wzory > Menedżer nazwzobacz zrzut ekranu:
2, w Menedżer nazw w oknie dialogowym wybierz element, którego chcesz użyć, i kliknij Edytuj przycisk.
3. W wyskoczył Edytuj imię wprowadź tę formułę = PRZESUNIĘCIE (Arkusz1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) do Odnosi się do pole tekstowe, patrz zrzut ekranu:
4. Następnie kliknij OK, a następnie powtórz krok 2 i krok 3, aby skopiować tę formułę = PRZESUNIĘCIE (Arkusz1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) do Odnosi się do pole tekstowe dla Cena sprzedaży nazwa zakresu.
5. Zostały utworzone dynamiczne nazwane zakresy. Gdy wprowadzisz nowe wartości po danych, nazwany zakres zostanie automatycznie dostosowany, a utworzona formuła również zostanie zmieniona. Zobacz zrzuty ekranu:
|
|
|
Uwaga: Jeśli w środku zakresu znajdują się puste komórki, wynik formuły będzie nieprawidłowy. Dzieje się tak, ponieważ niepuste komórki nie są liczone, więc Twój zakres będzie krótszy niż powinien, a ostatnie komórki w zakresie zostaną pominięte.
Wskazówka: wyjaśnienie tego wzoru:
- = OFFSET (odwołanie, wiersze, kolumny, [wysokość], [szerokość])
- = PRZESUNIĘCIE (Arkusz1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
- odniesienie odpowiada początkowej pozycji komórki w tym przykładzie Arkusz1! 1 $ A $ XNUMX;
- rząd odnosi się do liczby wierszy, które zamierzasz przesunąć w dół w stosunku do komórki początkowej (lub w górę, jeśli użyjesz wartości ujemnej), w tym przykładzie 0 oznacza, że lista rozpocznie się od pierwszego wiersza w dół
- kolumna odpowiada liczbie kolumn, które przesuniesz w prawo w stosunku do komórki początkowej (lub w lewo, używając wartości ujemnej), w powyższym przykładowym wzorze 0 oznacza rozszerzenie 0 kolumn w prawo.
- [wysokość] odpowiada wysokości (lub liczbie rzędów) zakresu począwszy od ustawionej pozycji. $ A: $ A, policzy wszystkie pozycje wprowadzone w kolumnie A.
- [szerokość] odpowiada szerokości (lub liczbie kolumn) zakresu począwszy od ustawionej pozycji. W powyższym wzorze lista będzie miała szerokość 1 kolumny.
Możesz zmienić te argumenty według swoich potrzeb.
Utwórz dynamiczny nazwany zakres w programie Excel za pomocą kodu VBA
Jeśli masz wiele kolumn, możesz powtórzyć i wprowadzić indywidualną formułę dla wszystkich pozostałych kolumn, ale byłby to długi, powtarzalny proces. Dla ułatwienia możesz użyć kodu do automatycznego tworzenia dynamicznego nazwanego zakresu.
1. Aktywuj swój arkusz.
2. Przytrzymaj ALT + F11 klucze i otwiera plik Okno Microsoft Visual Basic for Applications.
3. Kliknij wstawka > Modułi wklej następujący kod w Okno modułu.
Kod VBA: utwórz dynamiczny nazwany zakres
Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName <> "" Then
wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
End If
Next
End Sub
4. Następnie naciśnij F5 klucz do uruchomienia kodu, a zostaną wygenerowane dynamiczne nazwane zakresy, które są nazwane wartościami pierwszego wiersza, a także utworzy zakres dynamiczny o nazwie Moje dane który obejmuje wszystkie dane.
5. Gdy wprowadzisz nowe wartości po wierszach lub kolumnach, zakres również zostanie rozszerzony. Zobacz zrzuty ekranu:
 |
|
 |
Uwagi:
1. W przypadku tego kodu nazwy zakresów nie są wyświetlane w Nazwa Box, aby wygodnie przeglądać i używać nazw zakresów, zainstalowałem Kutools dla programu Excel, Z siedzibą w Okienko nawigacji, zostaną wyświetlone nazwy utworzonych zakresów dynamicznych.
2. Za pomocą tego kodu cały zakres danych można rozszerzyć w pionie lub w poziomie, ale należy pamiętać, że podczas wprowadzania nowych wartości między danymi nie powinno być pustych wierszy ani kolumn.
3. W przypadku korzystania z tego kodu zakres danych powinien zaczynać się od komórki A1.
Podobne artykuły:
Jak automatycznie zaktualizować wykres po wprowadzeniu nowych danych w programie Excel?
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
