Wszystko o nazwanych zakresach programu Excel - Nazwa zakresu Excel

Anonim

Podczas pracy w Excelu na pewno słyszałeś o nazwanych zakresach w Excelu. Może od znajomego, kolegi lub jakiegoś samouczka online. Nawet ja wielokrotnie o tym wspominałem w swoich artykułach. W tym artykule dowiemy się o nazwanych zakresach w programie Excel i zbadamy każdy ich aspekt.

Co to jest nazwany zakres w programie Excel?

Cóż, nazwane zakresy to nic innego jak niektóre zakresy programu Excel, które są oznaczone jakąś znaczącą nazwą. Na przykład, jeśli masz komórkę, powiedzmy B1, zawiera codzienny cel, możesz nazwać tę komórkę konkretnie „Cel”. Teraz możesz użyć „Cel”, aby odnieść się do A1 zamiast pisać B1.
W skrócie nazwany zakres to tylko nazwanie zakresów.

Jak nazwać zakres w programie Excel?

Zdefiniuj nazwę ręcznie:

Aby zdefiniować nazwę zakresu, możesz użyć skrótu CTRL+F3. Możesz też wykonać te kroki.

    • Przejdź do zakładki Formuła
    • Znajdź sekcję Zdefiniowane nazwy i kliknij Zdefiniuj nazwy. Otworzy się Menedżer nazw.

    • Kliknij Nowy.
    • Wpisz nazwę.

  • Wybierz zakres (skoroszyt lub arkusz)
  • Napisz komentarz, jeśli chcesz.
  • W polu Odnosi się do wpisz odwołanie lub wybierz zakres za pomocą myszy.
  • Naciśnij OK. Zrobione.

Teraz możesz się do niego odwołać, wpisując jego nazwę.
Podczas tworzenia nazw należy przestrzegać kilku zasad. Oni są

  1. Nazwy nie powinny zaczynać się od cyfr lub znaków specjalnych innych niż podkreślenie (_) i ukośnik odwrotny (\).
  2. Nazwy nie mogą zawierać spacji ani znaków specjalnych z wyjątkiem _ i \.
  3. Zakres nie powinien być nazywany jako odwołania do komórek. Na przykład nazwy A1, B1 lub AZ100 itd. są nieprawidłowe.
  4. Nie możesz nazwać zakresu „r” i „c”, ponieważ są one zarezerwowane dla odwołań do wierszy i kolumn.
  5. Dwa nazwane zakresy nie mogą mieć takiej samej nazwy w skoroszycie.
  6. Ten sam zakres może mieć wiele nazw.

Zdefiniuj nazwę Automatycznie

Cóż, przez większość czasu będziesz pracować z ustrukturyzowaną tabelą danych. Będą miały kolumny i wiersze z nagłówkami kolumn i nagłówkami wierszy. W większości przypadków te nazwy mają znaczenie dla danych i chcesz nazwać swój zakres tak, jak te nagłówki kolumn. Program Excel udostępnia narzędzie do automatycznego nazywania zakresów za pomocą tytułów. Wykonaj następujące kroki.

    • Zakresy, które chcesz nazwać jako ich nagłówki
    • naciskać CTRL+SHIFT+F3, lub Zlokalizuj sekcję Nazwy zdefiniowane na karcie Formuła i kliknij opcję Utwórz z zaznaczenia.

    • Pojawi się poniższe pole opcji. Wybrałem tylko Top Row, ponieważ chcę nazwać ten zakres jako nagłówek i nie chcę nazywać wierszy.

  • Kliknij OK.

Teraz każda kolumna jest nazwana jako ich nagłówek. Za każdym razem, gdy wpiszesz, wpiszesz formułę, nazwa ta zostanie wymieniona w opcji do użycia.

Zakres nazewnictwa przy użyciu tabel Excel

Kiedy organizujemy dane jako tabelę w programie Excel za pomocą CTRL + T, nagłówki kolumn są automatycznie przypisywane jako nazwa odpowiedniej kolumny. Powinieneś zapoznać się z tabelami Excela i ich zaletami.

Jak wyświetlić wszystkie nazwane zakresy?

Cóż, będą chwile, kiedy będziesz chciał zobaczyć wszystkie dostępne nazwane zakresy w skoroszycie. Aby zobaczyć wszystkie zakresy nazw Naciśnij CTRL+F3. Lub możesz iść do Karta Formuła > Menedżer nazw. Wyświetli listę wszystkich nazwanych zakresów dostępnych w skoroszycie. Możesz edytować dostępne nazwane zakresy, usuwać je, dodawać nowe nazwy.

Jeden zakres Wiele nazw

Excel pozwala użytkownikom nazywać ten sam zakres różnymi nazwami. Na przykład zakres A2:A10 można nazwać jednocześnie „Klientami” i „Klientami”. Obie nazwy będą odnosić się do tego samego zakresu A2:A10.
Ale nie możesz mieć tych samych nazw dla dwóch różnych zakresów. Eliminuje możliwość niejasności.

Pobierz listę nazwanych zakresów w arkuszu

Jeśli więc chcesz mieć listę nazwanych zakresów i zakresów, które obejmują, możesz użyć tego skrótu do wklejenia ich w miejscu w arkuszu.

    • Wybierz komórkę, w której chcesz uzyskać listę nazwanych zakresów.
    • Naciśnij F3. Otworzy się PImię i nazwisko okno dialogowe.
    • Kliknij wklej listę przycisk.

  • Lista zostanie wklejona w wybranej komórce i dalej.


Jeśli dwukrotnie klikniesz nazwę nazwanych zakresów w polu nazwy wklejania, zostaną one zapisane jako formuły w komórce. Spróbuj.

Ręczna aktualizacja nazwanych zakresów

Cóż, po wstawieniu komórki do nazwanego zakresu, aktualizuje się ona automatycznie i rozszerza ją. Ale jeśli dodasz dane na końcu tabeli, musisz zaktualizować nazwane pole. Aby zaktualizować nazwane zakresy, wykonaj następujące kroki.

  • naciskać CTRL+F3, aby otworzyć menedżera nazw.
  • Kliknij nazwany zakres, który chcesz edytować. Kliknij Edytuj.
  • W kolumnie Odnosi się do wpisz zakres, do którego chcesz rozszerzyć i naciśnij OK.

I gotowe. Jest to ręczna aktualizacja nazwanych zakresów. Możemy jednak nadać mu dynamikę, używając pewnych formuł.

Aktualizuj nazwane zakresy dynamicznie

Rozsądnie jest uczynić nazwane zakresy dynamicznymi, aby nie trzeba było ich edytować, gdy dane przekraczają predefiniowany zakres.
Omówiłem to w osobnym artykule o nazwie Dynamic Named ranges. Tutaj możesz szczegółowo poznać i zrozumieć jego zalety.

Usuwanie nazwanych zakresów

Kiedy usuniesz część nazwanego zakresu, automatycznie dostosuje swój zakres. Ale kiedy usuniesz cały zakres nazw, znika z listy nazw. Każda formuła zależna od tych zakresów pokaże błąd #REF lub da niepoprawny wynik (funkcje zliczające).
Z jakiegokolwiek powodu, jeśli chcesz usunąć nazwane zakresy, wykonaj następujące kroki.

  • Naciśnij klawisze CTRL+F3. Menedżer nazw otworzy.
  • Wybierz nazwane zakresy, które chcesz usunąć.
  • Kliknij przycisk Usuń lub naciśnij przycisk Usuń na klawiaturze.


Ostrożność: Przed usunięciem nazwanych zakresów upewnij się, że żadne formuły nie są zależne od tych nazw. Jeśli istnieją, najpierw przekonwertuj je na zakresy. W przeciwnym razie zobaczysz błąd #REF.

Usuwanie nazw z błędami

Program Excel udostępnia narzędzie do usuwania tylko nazw zawierających błędy. Nie musisz samodzielnie identyfikować każdego z nich. Aby usunąć nazwy z błędami, wykonaj następujące kroki:

    • Otwórz Menedżera nazw (CTRL+F3).
    • Kliknij listę rozwijaną Filtr w prawym górnym rogu.
    • Wybierz „Nazwa z błędami”

  • Wybierz Wszystko i naciśnij przycisk Usuń.

I już ich nie ma. Wszystkie nazwiska z błędami zostaną natychmiast usunięte z rejestru.

Nazwane zakresy z formułami

Najlepsze użycie nazwanych zakresów dotyczy formuł. Formuły stają się bardziej elastyczne i czytelne dzięki nazwanym zakresom. Zobaczmy jak.

Łatwe do pisania formuły

Załóżmy teraz, że nazwałeś zakres jako „Pozycje”. Teraz lista przedmiotów, które chcesz policzyć „Ołówki”. Z nazwą łatwo jest napisać tę formułę LICZ.JEŻELI. Tylko napisz

=LICZ.JEŻELI(element;"Ołówek")

Jak tylko wpiszesz nawias otwierający formułę, pojawi się lista dostępnych nazwanych zakresów. Bez nazwy napisałbyś funkcję GI LICZ.JEŻELI programu Excel z zakresami, dla których być może będziesz musiał najpierw spojrzeć na zakres, a następnie wybrać zakres lub wpisać go w formule.

Excel obsługuje dostępne zakresy nazw.

Nazwy zakresów są wyświetlane jako sugestie po wpisaniu dowolnej litery po znaku =. Tak samo jak Excel pokazuje listę formuł. Na przykład, jeśli wpiszesz =u, każda metoda i nazwany zakres będą wyświetlane zaczynając od u, dzięki czemu możesz z nich łatwo korzystać.

Twórz stałe za pomocą nazwanych zakresów

Do tej pory dowiedzieliśmy się o zakresach nazewnictwa, ale w rzeczywistości możesz również nazwać wartości. Na przykład, jeśli nazwa Twojego klienta to Sunder Pichai, możesz nadać nazwę „Klient” i odnosi się ona do napisania „Sundar Pichai”. Teraz za każdym razem, gdy napiszesz =Klient w dowolnej komórce, wyświetli się Sundar Pichai.


Nie tylko tekst, ale możesz także przypisać liczbę jako stałą do pracy. Na przykład definiujesz cel. Albo wartość czegoś, co się nie zmieni.
Odwoływanie się bezwzględne i względne z nazwanymi zakresami

Odwoływanie się za pomocą nazwanych zakresów w jest bardzo elastyczne. Na przykład, jeśli wpiszesz nazwę nazwanego zakresu w komórce względnej, będzie ona zachowywać się jak odpowiednie odwołanie. Zobacz poniższy obraz.

Ale kiedy użyjesz go z formułami, będzie zachowywał się jak absolut. Cóż, przez większość czasu będziesz ich używać z formułami, więc można powiedzieć, że są domyślnie Absolutne, ale w rzeczywistości są elastyczne.

Ale możemy też uczynić je względnymi.

Jak tworzyć względne nazwane zakresy w programie Excel?

Powiedzmy, że chcę nazwać zakres „Przed”, który będzie odnosić się do komórki pozostawionej do miejsca, w którym jest napisane. W jaki sposób mogę to zrobić? Wykonaj następujące kroki:

  • Naciśnij CTRL+F3
  • Kliknij Nowy
  • Wpisz „Przed” w sekcji „Nazwa”.
  • W sekcji „Odnosi się do:” wpisz adres komórki po lewej stronie. Na przykład, jeśli jesteś w komórce B1, wpisz „= A2” w sekcji „Odnosi się do:”. Upewnij się, że nie ma znaku $.


Teraz, gdziekolwiek napiszesz „Przed” w formule, będzie to odnosić się do pozostawionej komórki.

Tutaj użyłem wcześniej w funkcji COLUMN. Formuła zwraca numer kolumny lewej komórki, w której została zapisana. Ku mojemu zdziwieniu A1 pokazuje numer ostatniej kolumny. Co oznacza, że ​​arkusz jest okrągły. Myślałem, że pokaże błąd #REF.

Jak nazwać często używane formuły?

Teraz ten jest niesamowity. Wiele razy używasz tej samej formuły w arkuszu. Na przykład możesz chcieć sprawdzić, czy nazwisko znajduje się na liście klientów, czy nie. A taka potrzeba może wystąpić wielokrotnie. W tym celu za każdym razem napiszesz tę samą złożoną formułę.

=JEŻELI(LICZ.JEŻELI(Klient;I3);"Na liście";"Nie na liście")

Co powiesz na to, że po prostu wpiszesz „=IsInCustomer” w komórce, a zobaczysz, czy wartość w lewej komórce znajduje się na liście klientów, czy nie?

Na przykład przygotowałem tutaj tabelę. Teraz chcę tylko wpisać „=IsInCustomer” w J5 i chciałbym sprawdzić, czy wartość w I5 znajduje się na liście klientów lub nie. Aby to zrobić, wykonaj następujące kroki.

    • Naciśnij CTRL+F3
    • Kliknij Nowy
    • W Imię napisz „JestKlientem”
    • W polu „Odsyłacze do” wpisz swoją formułę. =JEŻELI(LICZ.JEŻELI(Klient;I5);"Na liście";"Nie na liście")

  • Naciśnij przycisk OK.

Teraz, gdziekolwiek wpiszesz „IsInCustomer”, sprawdzi wartość w lewej komórce na liście klientów.

To powstrzymuje cię od powtarzania siebie w kółko.

Zastosuj nazwane zakresy do formuł

Tyle razy definiujemy nazwy naszych zakresów po tym, jak już napisaliśmy formuły oparte na zakresach. Na przykład mam całkowitą cenę jako komórki = E2 * F2. Jak możemy to zmienić na Jednostki*Jednostka_Koszt.

    • Wybierz formuły.
    • Przejdź do zakładki Formuła. Kliknij menu rozwijane Zdefiniuj nazwę.

  • Kliknij Zastosuj nazwy.
  • Pojawi się lista wszystkich nazwanych zakresów. Wybierz właściwe nazwy i naciśnij OK.


I nazwy są teraz stosowane. Możesz to zobaczyć na pasku formuły.

Łatwe do odczytania formuły z nazwanymi zakresami

Jak widziałeś, nazwane zakresy ułatwiają czytanie formuł. Jeśli napiszę =LICZ.JEŻELI("A2:A100",B2), nikt nie zrozumie, co próbuję policzyć, dopóki nie zobaczy danych lub ktoś im to nie wyjaśni.

Ale jeśli napiszę =LICZ.JEŻELI(region,'wschód'), większość użytkowników natychmiast zorientuje się, że liczymy wystąpienie 'wschód' w nazwanym regionie.

Przenośne formuły

Nazwane zakresy bardzo ułatwiają kopiowanie i wklejanie formuł bez martwienia się o zmianę referencji. Możesz przenieść jedną formułę z jednego skoroszytu do drugiego i będzie działać dobrze, dopóki skoroszyt docelowy nie będzie miał takich samych nazw.

Na przykład, jeśli masz formułę =LICZ.JEŻELI(region;wschód) w tabeli dystrybucji i masz inny skoroszyt, powiedz klienci który ma również nazwany zakres „Region”. Teraz, jeśli skopiujesz tę formułę bezpośrednio w dowolnym miejscu tego skoroszytu, wyświetli ona prawidłowe informacje. Struktura danych nie będzie miała znaczenia. Nie ma znaczenia, gdzie u diabła jest ta kolumna w twoim skoroszycie. To zadziała poprawnie.

Na powyższym obrazku użyłem dokładnie tej samej formuły w dwóch różnych plikach, aby zliczyć liczbę lub wschód występujące na liście regionów. Teraz są w różnych kolumnach, ale ponieważ oba są nazwane jako regiony, będą działać idealnie.

Łatwa nawigacja w skoroszycie

Łatwiej jest nawigować w skoroszycie z nazwanymi zakresami. Wystarczy wpisać nazwę w polu nazwy. Excel zabierze Cię do zakresu, nie ma znaczenia, gdzie jesteś w skoroszycie. Biorąc pod uwagę, że nazwany zakres jest zakresem skoroszytu.
Na przykład, jeśli jesteś na arkuszu10 i chcesz mieć listę klientów, a nie wiesz, na którym arkuszu jest. Po prostu przejdź do pola imienia i wpisz „klient”. W ułamku sekundy zostaniesz przekierowany do wskazanego zakresu.


Zmniejszy to wysiłek związany z zapamiętywaniem zakresów.

Nawiguj za pomocą hiperłączy z nazwanym zakresem

Kiedy arkusz jest duży i często przechodzisz z jednego punktu do drugiego, lubisz korzystać z hiperłączy, aby łatwo nawigować. Dobrze nazwane zakresy mogą doskonale współpracować z hiperłączami. Aby dodać hiperłącza za pomocą nazwanych zakresów, wykonaj następujące kroki.

    • Wybierz komórkę, w której chcesz hiperłącze
    • Naciśnij CTRL+K lub przejdź do Wstaw kartę> Hiperłącze, aby otworzyć okno dialogowe Wstaw hiperłącze.

    • Kliknij Umieść w tym dokumencie.
    • Przewiń w dół, aby zobaczyć dostępne nazwane zakresy w zdefiniowanych nazwach

  • Wybierz nazwany zakres, aby wstawić hiperłącze do tego zakresu.

I gotowe. Masz hiperłącze do wybranego zakresu nazwanego. Korzystając z tego, możesz utworzyć indeks nazwanych zakresów, które możesz zobaczyć i kliknąć, aby przejść do nich bezpośrednio. Dzięki temu Twój skoroszyt będzie naprawdę przyjazny dla użytkownika.

Nazwany zakres i walidacja danych

Nazwane zakresy i walidacja danych są jakby stworzone dla siebie. Nazwane zakresy sprawiają, że walidacja danych jest wysoce konfigurowalna. Znacznie łatwiej jest dodać walidację z listy przy użyciu nazwanego zakresu. Zobaczmy, jak…

    • Przejdź do zakładki Dane
    • Kliknij Walidacja danych
    • Wybierz Lista w sekcji „Zezwól:”
    • W sekcji „Źródło:” wpisz „=Klient” (wpisz dowolny nazwany zakres, który posiadasz)

  • Naciśnij OK

Teraz ta komórka będzie zawierała nazwy klientów, którzy są częścią zakresu o nazwie Customer. To proste, prawda?

Zależna lub kaskadowa walidacja danych z nazwanymi zakresami

A co, jeśli chcesz kaskadowej lub zależnej walidacji danych. Na przykład, jeśli chcesz mieć listę rozwijaną zawierającą kategorie, Owoce i Warzywa. Teraz, jeśli wybierzesz owoce, inna lista rozwijana powinna pokazywać opcję tylko owoce, a jeśli wybierzesz Warzywa, to tylko warzywa.

Można to łatwo osiągnąć, używając nazwanych zakresów. Naucz się jak.

  • Zależne menu rozwijane przy użyciu nazwanego zakresu
  • Inne sposoby kaskadowego sprawdzania poprawności danych

Brak walidacji danych z nazwami danych w tabeli

Chociaż tabele programu Excel zawierają nazwy strukturalne, nie można ich używać do sprawdzania poprawności danych i formatowania warunkowego. Nie wiem, dlaczego Excel na to nie pozwala.
Ale to nie znaczy, że nie da się tego zrobić. Możesz nazwać zakresy w tabeli, a następnie użyć ich do walidacji. Excel nie ma z tym żadnego problemu.

Zakres nazwanych zakresów

Do tej pory rozmawialiśmy o nazwanych zakresach, które miały zakres skoroszytu. Co? Nie rozmawialiśmy o tym? Ok, więc szybko zrozumiemy, jaki jest zakres nazwanych zakresów.

Co to jest zakres zakresu nazw?

Dobrze zakres określa, gdzie można rozpoznać zakres nazw. Żadnej nazwy nie można rozpoznać poza jej zakresem. Na przykład nazwa w skoroszycie1 nie może być rozpoznana w innym skoroszycie. Excel udostępnia dwie opcje zakresu nazwanych zakresów Arkusz i Skoroszyt.

Jak zdefiniować zakres nazwanego zakresu?

Podczas tworzenia nowego zakresu nazw możesz zobaczyć sekcję „Zakres:”. Kliknij listę rozwijaną i wybierz zakres dla swojego zakresu nazw. Nie możesz zmienić zakresu po utworzeniu nazwanego zakresu. Więc lepiej zrób to wcześniej. Domyślnie jest to skoroszyt.

Zakres skoroszytu

Jest to domyślny zakres nazwanego zakresu. Nazwa zdefiniowana z zakresem skoroszytu może być używana w całym skoroszycie, w którym jest zdefiniowana (nie w innych skoroszytach).
Wszystkie powyższe przykłady miały zakres skoroszytu.

Zakres arkusza roboczego

Nazwa zdefiniowana w zakresie arkusza może być używana tylko w arkuszu definiowania. Na przykład, jeśli zdefiniuję „Total” dla całkowitej komórki z zakresem arkusza1. Wtedy suma zostanie rozpoznana tylko na arkuszu 1. Inne arkusze nie rozpoznają.

Chcę zakres programu Excel

Program Excel nie ma globalnego ani powiedzmy zakresu programu Excel. Właściwie chciałbym zdefiniować niektóre nazwy, które mogą być rozpoznawane we wszystkich skoroszytach w moim systemie. Jeśli ktoś wie, jak możemy to zrobić, daj mi znać.

Zakres edycji po utworzeniu nazw

Nie możesz. Excel nie pozwala na edycję zakresu nazwanego zakresu po utworzeniu. Ponieważ wszystkie nazwane zakresy w arkuszu są domyślnie objęte zakresem skoroszytu i możesz chcieć zmienić ich zakres na arkusz.
Aby to zrobić, po prostu zrób kopię tego arkusza, a Excel sprawi, że każda nazwa na tym arkuszu będzie lokalna, aby uniknąć niejasności. Możesz teraz usunąć oryginalny arkusz, jeśli chcesz.

Wytnij Wklej Zakres nazw

Kiedy wycinasz i wklejasz nazwany zakres z jednego miejsca docelowego do drugiego, odniesienie zmienia się na nową lokalizację. Na przykład, jeśli masz nazwany zakres „Klient” w A2:A10 i wycinasz go i wklejasz do B2:B10, nazwa klienta będzie odnosić się do nowej lokalizacji B2:B10.

Dynamiczne nazwane zakresy w Excelu

17 niesamowitych funkcji tabel Excel

Popularne artykuły:

50 skrótów Excela, które zwiększą Twoją produktywność

Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel

Jak korzystać z funkcji LICZ.JEŻELI w programie Excel

Jak korzystać z funkcji SUMA.JEŻELI w programie Excel