zakres nazw Excel – jak definiować i używać nazw w Excelu

samouczek wyjaśnia, czym jest nazwa programu Excel i pokazuje, jak zdefiniować nazwę dla komórki, zakresu, stałej lub formuły. Dowiesz się również, jak edytować, filtrować i usuwać zdefiniowane nazwy w programie Excel.

nazwy w Excelu są rzeczą paradoksalną: będąc jedną z najbardziej przydatnych funkcji, często są uważane za bezcelowe lub kujońskie. Powodem jest to, że bardzo niewielu użytkowników rozumie istotę nazw Excela., Ten samouczek nie tylko nauczy Cię tworzenia nazwanego zakresu w programie Excel, ale także pokaże, jak wykorzystać tę funkcję, aby znacznie ułatwić pisanie, czytanie i ponowne wykorzystanie formuł.,opcja

co oznacza nazwa w programie Excel?,

w życiu codziennym nazwy są szeroko stosowane w odniesieniu do ludzi, przedmiotów i lokalizacji geograficznych. Na przykład, zamiast mówić „miasto leżące na szerokości geograficznej 40.7128 ° N i długości geograficznej 74.0059° W, po prostu powiedzieć „Nowy Jork”.

podobnie w programie Microsoft Excel można nadać pojedynczą komórkę lub zakres komórek czytelną dla człowieka nazwę i odwoływać się do tych komórek po nazwie, a nie po odwołaniu.,

na przykład, aby znaleźć sumę sprzedaży (B2:B10) dla określonego elementu (E1), możesz użyć następującej formuły:

=SUMIF($A$2:$A$10, $E$1, $B$2:$B$10)

lub możesz nadać znaczące nazwy zakresom i poszczególnym komórkom i podać te nazwy do Formuły:

=SUMIF(items_list, item, sales)

patrząc na poniższy zrzut ekranu, które z dwóch formuł są łatwiejsze do zrozumienia?,

typy nazw programu Excel

w programie Microsoft Excel można tworzyć i używać dwóch typów nazw:

zdefiniowana nazwa – nazwa, która odnosi się do pojedynczej komórki, zakresu komórek, stałej wartości lub formuły. Na przykład, gdy definiujesz nazwę dla zakresu komórek, nazywa się to nazwanym zakresem lub zdefiniowanym zakresem. Nazwy te są przedmiotem dzisiejszego tutoriala.

Nazwa tabeli – Nazwa tabeli Excel, która jest tworzona automatycznie po wstawieniu tabeli w arkuszu roboczym (Ctrl + T). Aby uzyskać więcej informacji na temat tabel programu Excel, zobacz jak tworzyć i używać tabeli w programie Excel.,

jak utworzyć zakres nazw programu Excel

Ogólnie rzecz biorąc, istnieją 3 sposoby definiowania nazwy w programie Excel: okno nazwy, przycisk zdefiniuj nazwę i menedżer nazw programu Excel.

wpisz nazwę w polu Nazwa

pole nazwa w programie Excel jest najszybszym sposobem utworzenia nazwanego zakresu:

1. wybierz komórkę lub zakres komórek, które chcesz nazwać.
2. wpisz nazwę w polu Nazwa.
3. naciśnij klawisz Enter.

Voila, nowy Excel o nazwie zakres jest tworzony!,

Utwórz nazwę za pomocą opcji zdefiniuj nazwę

innym sposobem utworzenia nazwanego zakresu w programie Excel jest:

1. wybierz komórkę(komórki).
2. na karcie formuły w grupie Definiuj nazwy kliknij przycisk Definiuj nazwę.
3. w oknie dialogowym Nowa nazwa określ trzy rzeczy:
   • w polu Nazwa wpisz nazwę zakresu.
   • w rozwijanym obszarze Ustaw nazwę zakres (domyślnie skoroszyt).
   • w polu odnosi się do zaznacz odniesienie i popraw je w razie potrzeby.
4. kliknij OK, aby zapisać zmiany i zamknąć okno dialogowe.,

w porównaniu do poprzedniej metody, użycie Definiuj nazwę w Excelu wymaga kilku dodatkowych kliknięć, ale zapewnia również kilka dodatkowych opcji, takich jak ustawienie zakresu nazwy i dodanie komentarza, który wyjaśnia coś o nazwie., Dodatkowo funkcja zdefiniuj nazwę programu Excel umożliwia utworzenie nazwy stałej lub formuły.

Utwórz nazwany zakres za pomocą programu Excel Name Manager

Zwykle Menedżer nazw w programie Excel jest używany do pracy z istniejącymi nazwami. Może jednak pomóc w zbudowaniu nowej nazwy. Oto jak:

1. przejdź do karty Formuły > zdefiniowana Grupa nazw, Kliknij Menedżer nazw. Lub po prostu naciśnij Ctrl + F3 (mój preferowany sposób).,
2. w lewym górnym rogu okna dialogowego Menedżer nazw kliknij przycisk Nowy…:
   
3. spowoduje to otwarcie okna dialogowego nowa nazwa, w którym skonfigurujesz nazwę, jak pokazano w poprzedniej sekcji.

jak utworzyć nazwę programu Excel dla stałej

oprócz nazwanych zakresów program Microsoft Excel pozwala zdefiniować nazwę bez odniesienia do komórki, która będzie działać jako stała o nazwie. Aby utworzyć taką nazwę, użyj funkcji Excel Define Name lub Menedżera nazw, jak wyjaśniono powyżej.

a teraz możesz użyć tej nazwy w dowolnym miejscu w swoich formułach, aby przekonwertować USD na EUR:

gdy tylko zmieni się Kurs wymiany, aktualizujesz wartość tylko w jednej centralnej lokalizacji, a wszystkie Twoje formuły zostaną przeliczone w jednym kroku!,

jak zdefiniować nazwę formuły

w podobny sposób możesz nadać nazwę formule Excel, na przykład tej, która zwraca liczbę niepustych komórek w kolumnie a, z wyłączeniem wiersza nagłówka (-1):

=COUNTA(Sheet5!$A:$A)-1

teraz, gdy chcesz wiedzieć, ile elementów znajduje się w kolumnie a na Arkuszu5, nie licząc nagłówka kolumny, po prostu wpisz znak równości, a następnie nazwę formuły w dowolnej komórce, w następujący sposób: =Items_count

jak nazywać kolumny w Excelu (nazwy z wyboru)

Jeśli Twoje dane są ułożone w formie tabelarycznej, możesz szybko utwórz nazwy dla każdej kolumny i/lub wiersza na podstawie ich etykiet:

1. wybierz całą tabelę, w tym nagłówki kolumn i wierszy.,
2. przejdź do karty Formuły > Zdefiniuj grupę nazw i kliknij przycisk Utwórz z wyboru. Lub naciśnij skrót klawiaturowy Ctrl + Shift + F3.
3. tak czy inaczej otworzy się okno dialogowe Utwórz nazwy z wyboru. Wybierz kolumnę lub wiersz z nagłówkami lub oba i kliknij OK.,

w tym przykładzie mamy nagłówki w górnym wierszu i lewej kolumnie, więc wybieramy te dwie opcje:

w rezultacie program Excel utworzy 7 nazwanych zakresów, automatycznie pobierając nazwy z nagłówków:

• jabłka, banany, cytryny i pomarańcze dla wierszy i
• Jan, Feb i Mar dla kolumn.

Excel dynamiczny nazwany zakres

we wszystkich poprzednich przykładach mieliśmy do czynienia ze statycznymi nazwanymi zakresami, które zawsze odnoszą się do tych samych komórek, co oznacza, że będziesz musiał ręcznie aktualizować odniesienie do zakresu, gdy chcesz dodać nowe dane do nazwanego zakresu.

Jeśli pracujesz z rozszerzalnymi zestawami danych, warto utworzyć dynamiczny zakres nazwany, który automatycznie uwzględnia nowo dodane dane.,

Szczegółowe wskazówki dotyczące tworzenia dynamicznego zakresu nazw w programie Excel można znaleźć tutaj:

• formuła przesunięcia Excela do tworzenia zakresu dynamicznego
• formuła indeksu do tworzenia zakresu dynamicznego

reguły nazewnictwa Excela

podczas tworzenia nazwy w programie Excel należy pamiętać o kilku regułach:

zakres nazwy Excela

pod względem nazw programu Excel zakres jest lokalizacją lub poziom, w ramach którego rozpoznawana jest nazwa., Może to być:

• konkretny arkusz roboczy – lokalny poziom arkusza roboczego
• skoroszyt – globalny poziom skoroszytu

nazwy poziomów arkusza roboczego

nazwa poziomu arkusza roboczego jest rozpoznawana w arkuszu roboczym, w którym się znajduje. Na przykład, jeśli utworzysz nazwany zakres i ustawisz jego zakres Na Arkusz1, będzie on rozpoznawany tylko w arkuszu1.

aby móc używać nazwy arkusza roboczego w innym arkuszu roboczym, musisz przedrostek nazwy arkusza roboczego, a następnie wykrzyknik (!), w ten sposób:

Sheet1!,items_list

aby odwołać się do nazwy poziomu arkusza roboczego w innym skoroszycie, należy również podać nazwę skoroszytu zamkniętą w nawiasach kwadratowych:

Arkusz1!items_list

Jeśli nazwa arkusza lub nazwa skoroszytu zawiera spacje, powinny one być zamknięte w pojedynczych cudzysłowach:

’Sheet1′!items_list

nazwy poziomów skoroszytu

nazwa poziomu skoroszytu jest rozpoznawana w całym skoroszycie i można się do niego odwoływać po prostu nazwą z dowolnego arkusza w tym samym skoroszycie.,

użycie nazwy skoroszytu w innym skoroszycie poprzedza nazwę nazwą skoroszytu (łącznie z rozszerzeniem), a następnie wykrzyknikiem:

Book1.xlsx!items_list

priorytet zakresu

zdefiniowana nazwa musi być unikalna w swoim zakresie. Można używać tej samej nazwy w różnych zakresach, ale może to spowodować konflikt nazw. Aby temu zapobiec, domyślnie poziom arkusza ma pierwszeństwo przed poziomem skoroszytu.,

Jeśli istnieje kilka identycznie nazwanych zakresów z różnymi zakresami i chcesz użyć nazwy poziomu skoroszytu, przedrostek nazwy z nazwą skoroszytu tak, jakbyś odnosił się do nazwy w innym skoroszycie, np.: Book1. xlsx!data. W ten sposób konflikt nazw może zostać nadpisany dla wszystkich arkuszy z wyjątkiem pierwszego arkusza, który zawsze używa lokalnej nazwy poziomu arkusza.,

Menedżer nazw Excel – szybki sposób edycji, usuwania i filtrowania nazw

jak sama nazwa wskazuje, Menedżer nazw Excel jest specjalnie zaprojektowany do zarządzania nazwami: zmieniać, filtrować lub usuwać istniejące nazwy, a także tworzyć nowe.

są dwa sposoby na przejście do Menedżera nazw w programie Excel:

• na karcie formuły, w grupie Definiuj nazwy, kliknij Menedżera nazw
  
• naciśnij skrót Ctrl + F3.

tak czy inaczej otworzy się okno dialogowe Menedżera nazw, w którym na pierwszy rzut oka zobaczysz wszystkie nazwy w bieżącym skoroszycie., Teraz możesz wybrać nazwę, z którą chcesz pracować, i kliknąć jeden z 3 przycisków u góry okna, aby wykonać odpowiednią akcję: Edytuj, Usuń lub filtruj.

Jak edytować zakres nazw w programie Excel

aby zmienić istniejącą nazwę programu Excel, otwórz Menedżera nazw, wybierz nazwę i kliknij przycisk Edytuj…. Spowoduje to otwarcie okna dialogowego Edytuj nazwę, w którym można zmienić nazwę i odniesienie. Zakres nazwy nie może zostać zmieniony.

Tip. Aby edytować odniesienie do nazwy, nie musisz otwierać okna dialogowego Edytuj nazwę., Wystarczy wybrać interesującą nazwę w Menedżerze nazw programu Excel i wpisać nowe odniesienie bezpośrednio w polu odnosi się do lub kliknąć przycisk po prawej stronie i wybrać żądany zakres na arkuszu. Po kliknięciu przycisku Zamknij program Excel zapyta, czy chcesz zapisać zmiany, a Ty klikniesz Tak.

Jak filtrować nazwy w programie Excel

Jeśli masz wiele nazw w określonym skoroszycie, kliknij przycisk Filtruj w prawym górnym rogu okna Menedżera nazw programu Excel, aby wyświetlić tylko nazwy istotne w danym czasie., Dostępne są następujące filtry:

• nazwy z zakresem do arkusza roboczego lub skoroszytu
• nazwy z błędami lub bez błędów
• zdefiniowane nazwy lub nazwy tabel

Jak usunąć nazwany zakres w programie Excel

aby usunąć nazwany zakres, wybierz go w Menedżerze nazw i kliknij przycisk Usuń u góry.

aby usunąć kilka nazw, kliknij imię, a następnie naciśnij klawisz Ctrl i przytrzymaj je, klikając inne nazwy, które chcesz usunąć. Następnie kliknij przycisk Usuń, a wszystkie wybrane nazwy zostaną usunięte za jednym razem.,

aby usunąć wszystkie zdefiniowane nazwy ze skoroszytu, wybierz imię na liście, Naciśnij i przytrzymaj klawisz Shift, a następnie kliknij nazwisko. Zwolnij klawisz Shift i kliknij Usuń.

jak usunąć zdefiniowane nazwy z błędami

Jeśli masz liczbę nieprawidłowych nazw z błędami odniesienia, kliknij przycisk Filtruj > nazwy z błędami, aby je filtrować:

następnie wybierz wszystkie filtrowane nazwy, jak wyjaśniono powyżej( za pomocą klawisza Shift), a następnie kliknij przycisk Usuń.

Top 5 korzyści z używania nazw w Excelu

do tej pory w tym samouczku skupiliśmy się głównie na rozwiązaniach, które obejmują różne aspekty tworzenia i używania nazwanych zakresów w Excelu. Ale możesz być ciekaw, co jest tak wyjątkowego w nazwach Excela, że są warte wysiłku? Pięć najważniejszych zalet używania zdefiniowanych nazw w programie Excel znajduje się poniżej.,

nazwy programu Excel ułatwiają tworzenie i odczytywanie formuł

nie musisz wpisywać złożonych odniesień ani wybierać zakresów na arkuszu. Po prostu zacznij wpisywać nazwę, której chcesz użyć w formule, a program Excel pokaże listę pasujących nazw do wyboru., Kliknij dwukrotnie żądaną nazwę, a program Excel od razu wstawi ją do Formuły:

nazwy Excel umożliwiają tworzenie rozszerzalnych formuł

używając dynamicznych nazw zakresów, możesz utworzyć „dynamiczną” formułę, która automatycznie zawiera nowe dane w obliczeniach bez konieczności ręcznej aktualizacji każdego odniesienia.

nazwy programu Excel ułatwiają ponowne użycie formuł

nazwy programu Excel znacznie ułatwiają kopiowanie formuły do innego arkusza lub przenoszenie formuły do innego skoroszytu., Wszystko, co musisz zrobić, to utworzyć te same nazwy w skoroszycie docelowym, skopiować / wkleić formułę tak, jak jest, a natychmiast ją uruchomisz.

zakresy nazw uprość nawigację

aby szybko przejść do określonego zakresu nazw, wystarczy kliknąć jego nazwę w polu Nazwa. Jeśli nazwany zakres znajduje się na innym arkuszu, program Excel automatycznie przeniesie Cię do tego arkusza.

zakresy nazwane umożliwiają tworzenie dynamicznych list rozwijanych

, aby utworzyć rozszerzalną i aktualizowalną listę rozwijaną, najpierw utworzyć dynamiczny zakres nazwany, a następnie utworzyć listę walidacji danych na podstawie tego zakresu. Szczegółowe instrukcje krok po kroku można znaleźć tutaj: jak utworzyć dynamiczne rozwijane menu w programie Excel.,

zakres nazw w Excelu – porady i wskazówki

teraz, gdy znasz podstawy tworzenia i używania nazw w Excelu, pozwól mi podzielić się jeszcze kilkoma wskazówkami, które mogą okazać się pomocne w twojej pracy.

Jak uzyskać listę wszystkich nazw w skoroszycie

aby uzyskać bardziej namacalną listę wszystkich nazw w bieżącym skoroszycie, wykonaj następujące czynności:

1. wybierz najwyższą komórkę zakresu, w której mają się pojawić nazwy.
2. przejdź do karty Formuły > Zdefiniuj grupę nazw, kliknij Użyj w formułach, a następnie kliknij Wklej nazwy… lub po prostu naciśnij klawisz F3.,
3. w oknie dialogowym Wklej nazwy kliknij Wklej listę.

spowoduje to wstawienie wszystkich nazw programu Excel wraz z ich odniesieniami do bieżącego arkusza roboczego, zaczynając od wybranej komórki.

bezwzględne nazwy Excel vs.względne nazwy Excel

domyślnie nazwy Excel zachowują się jak bezwzględne odniesienia – zablokowane do określonych komórek. Możliwe jest jednak ustawienie nazwanego zakresu względem położenia aktywnej komórki w momencie zdefiniowania nazwy., Nazwy względne zachowują się jak odniesienia względne – są zmieniane, gdy formuła jest przenoszona lub kopiowana do innej komórki.

w rzeczywistości, nie mogę wymyślić żadnego powodu, dla którego ktoś chciałby utworzyć względny zakres nazwany, może z wyjątkiem sytuacji, gdy zakres składa się z pojedynczej komórki. Jako przykład, stwórzmy względną nazwę, która odnosi się do komórki w jednej kolumnie po lewej stronie bieżącej komórki, w tym samym wierszu:

1. wybierz komórkę B1.
2. naciśnij Ctrl + F3, aby otworzyć Menedżera nazw programu Excel i kliknij Nowy…
3. w polu Nazwa, wpisz żądaną nazwę, powiedzmy, item_left.,
4. w polu należy wpisać =A1.
5. kliknij OK.

teraz zobaczmy, co się stanie, gdy użyjemy nazwy item_left w formule, na przykład:

gdzie items_list odnosi się do $A$2:$a$10, a sprzedaż odnosi się do $B$2:$B$10 w poniższej tabeli.,

Po wprowadzeniu formuły w komórce E2, a następnie skopiowaniu jej w dół kolumny, obliczy całkowitą sprzedaż dla każdego produktu indywidualnie, ponieważ item_left jest nazwą względną, a jego odniesienie dostosowuje się na podstawie względnej pozycji kolumny i wiersza, w którym formuła jest kopiowana:

Jak zastosować nazwy programu Excel do istniejących formuł

Jeśli zdefiniowałeś zakresy, które są już używane w formułach program Excel nie zmieni automatycznie odniesień do odpowiednich nazw., Jednak zamiast ręcznie zastępować referencje nazwami, możesz zlecić Excel wykonanie pracy za Ciebie. Oto jak:

1. Wybierz jedną lub więcej komórek formuły, które chcesz zaktualizować.
2. przejdź do karty Formuły > Zdefiniuj grupę nazw i kliknij zdefiniuj nazwę >Zastosuj nazwy…
   
3. w oknie dialogowym Zastosuj nazwy, kliknij nazwy, które chcesz zastosować, a następnie kliknij OK., Jeśli program Excel jest w stanie dopasować dowolną z istniejących nazw z odniesieniami używanymi w formułach, nazwy zostaną automatycznie wybrane:
   

dodatkowo dostępne są dwie dodatkowe opcje (wybrane domyślnie):

• Ignoruj względne/bezwzględne – zachowaj to pole zaznaczone, jeśli chcesz, aby Excel stosował tylko nazwy z tym samym typem odniesienia: zamień względne odniesienia na nazwy względne i bezwzględne odniesienia na nazwy bezwzględne.,
• użyj nazw wierszy i kolumn – jeśli jest zaznaczone, Excel zmieni nazwę wszystkich odwołań do komórek, które można zidentyfikować jako przecięcie nazwanego wiersza i nazwanej kolumny. Aby uzyskać więcej możliwości, kliknij Opcje

skróty nazw programu Excel

Jak to często bywa w programie Excel, najpopularniejsze funkcje można uzyskać na kilka sposobów: za pomocą wstążki, menu prawym przyciskiem myszy i skrótów klawiaturowych. Zakresy nazw programu Excel nie są wyjątkiem. Oto trzy przydatne skróty do pracy z nazwami w programie Excel:

• Ctrl + F3, aby otworzyć Menedżera nazw programu Excel.,
• Ctrl + Shift + F3, aby utworzyć nazwane zakresy z zaznaczenia.
• F3, aby uzyskać listę wszystkich nazw programu Excel w skoroszycie.

błędy nazwy programu Excel (#REF I #NAME)

Domyślnie program Microsoft Excel dokłada wszelkich starań, aby zdefiniowane nazwy były spójne i poprawne, dostosowując odwołania do zakresu automatycznie po wstawieniu lub usunięciu komórek w istniejącym zakresie nazw. Na przykład, jeśli utworzyłeś nazwany zakres dla komórek A1: A10, a następnie wstawisz nowy wiersz w dowolnym miejscu między wierszami 1 i 10, odniesienie do zakresu zmieni się na A1: A11., Podobnie, jeśli usuniesz komórki między A1 i A10, Twój nazwany zakres będzie się odpowiednio kurczył.

Jeśli jednak usuniesz wszystkie komórki, które tworzą zakres o nazwie Excel, nazwa stanie się nieprawidłowa i wyświetli #REF! błąd w Menedżerze nazw. Ten sam błąd pojawi się w formule odwołującej się do tej nazwy:

Jeśli formuła odnosi się do nieistniejącej nazwy (błędnie wpisanej lub usuniętej), #nazwa? pojawi się błąd. W obu przypadkach otwórz Menedżera nazw programu Excel i Sprawdź ważność zdefiniowanych nazw (najszybszym sposobem jest filtrowanie nazw z błędami).,

tak tworzysz i używasz nazw w Excelu. Dziękuję za przeczytanie i mam nadzieję, że do zobaczenia na naszym blogu w przyszłym tygodniu!

• jak tworzyć i używać dynamicznego zakresu nazwanego w programie Excel
• podstawowe formuły programu Excel & funkcje z przykładami
• formuły programu Microsoft Excel z przykładami