Jak tworzyć nazwane zakresy w programie Excel (przewodnik krok po kroku)

Co jest w nazwie?

Jeśli pracujesz z arkuszami kalkulacyjnymi Excel, może to oznaczać dużo oszczędności czasu i wydajności.

W tym samouczku dowiesz się, jak tworzyć nazwane zakresy w programie Excel i jak z nich korzystać, aby zaoszczędzić czas.

Nazwane zakresy w programie Excel - wprowadzenie

Jeśli ktoś będzie musiał do mnie zadzwonić lub powołać się na mnie, użyje mojego imienia (zamiast mówić, że mężczyzna przebywa w takim a takim miejscu o takim a takim wzroście i wadze).

Dobrze?

Podobnie w programie Excel możesz nadać nazwę komórce lub zakresowi komórek.

Teraz zamiast używać odwołania do komórki (takiego jak A1 lub A1:A10), możesz po prostu użyć przypisanej do niej nazwy.

Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej:

W tym zestawie danych, jeśli musisz odwołać się do zakresu, który ma datę, będziesz musiał użyć A2:A11 w formułach. Podobnie w przypadku przedstawicieli handlowych i sprzedaży będziesz musiał użyć B2:B11 i C2:C11.

Chociaż jest to w porządku, gdy masz tylko kilka punktów danych, ale w przypadku ogromnych złożonych zestawów danych używanie odwołań do komórek w celu odwoływania się do danych może być czasochłonne.

Nazwane zakresy programu Excel ułatwiają odwoływanie się do zestawów danych w programie Excel.

Możesz utworzyć nazwany zakres w programie Excel dla każdej kategorii danych, a następnie użyć tej nazwy zamiast odwołań do komórek. Na przykład daty można nazwać „Data”, dane przedstawiciela handlowego można nazwać „przedstawicielem handlowym”, a dane sprzedaży można nazwać „sprzedażą”.

Możesz także utworzyć nazwę dla pojedynczej komórki. Na przykład, jeśli masz procent prowizji od sprzedaży w komórce, możesz nazwać tę komórkę „Prowizja”.

Korzyści z tworzenia nazwanych zakresów w Excelu

Oto zalety używania nazwanych zakresów w programie Excel.

Użyj nazw zamiast odwołań do komórek

Podczas tworzenia nazwanych zakresów w programie Excel można używać tych nazw zamiast odwołań do komórek.

Na przykład możesz użyć =SUMA(SPRZEDAŻ) zamiast =SUMA(C2:C11) dla powyższego zestawu danych.

Zapoznaj się z poniższymi formułami. Zamiast używać odwołań do komórek, użyłem nazwanych zakresów.

• Liczba sprzedaży o wartości większej niż 500: =LICZ.JEŻELI(Sprzedaż”>500”)
• Suma wszystkich sprzedaży dokonanych przez Toma: =SUMA.JEŻELI(RepSprzedaż”,Tom”,Sprzedaż)
• Prowizja zarobiona przez Joe (sprzedaż Joe pomnożona przez procent prowizji):
  =SUMA.JEŻELI(Przed.sprzedaży;Jan;Sprzedaż)*Prowizja

Zgodzisz się, że te formuły są łatwe do stworzenia i łatwe do zrozumienia (zwłaszcza, gdy udostępniasz je komuś innemu lub ponownie je odwiedzasz.

Nie musisz wracać do zbioru danych, aby wybrać komórki

Inną istotną zaletą korzystania z nazwanych zakresów w programie Excel jest to, że nie trzeba wracać i wybierać zakresów komórek.

Możesz po prostu wpisać kilka alfabetów tego nazwanego zakresu, a Excel pokaże pasujące nazwane zakresy (jak pokazano poniżej):

Nazwane zakresy sprawiają, że formuły są dynamiczne

Używając nazwanych zakresów w programie Excel, możesz tworzyć dynamiczne formuły programu Excel.

Na przykład w przypadku prowizji od sprzedaży zamiast wartości 2,5% można użyć Nazwanego Zakresu.

Teraz, jeśli Twoja firma zdecyduje się później zwiększyć prowizję do 3%, możesz po prostu zaktualizować Nazwany zakres, a wszystkie obliczenia zostaną automatycznie zaktualizowane, aby odzwierciedlić nową prowizję.

Jak tworzyć nazwane zakresy w Excelu?

Oto trzy sposoby tworzenia nazwanych zakresów w programie Excel:

Metoda nr 1 - Używanie Definiuj nazwy

Oto kroki, aby utworzyć nazwane zakresy w programie Excel przy użyciu funkcji Definiuj nazwę:

• Wybierz zakres, dla którego chcesz utworzyć nazwany zakres w programie Excel.
• Przejdź do Formuły -> Zdefiniuj nazwę.
• W oknie dialogowym Nowa nazwa wpisz nazwę, którą chcesz przypisać do wybranego zakresu danych. Możesz określić zakres jako cały skoroszyt lub określony arkusz. Jeśli wybierzesz określony arkusz, nazwa nie będzie dostępna w innych arkuszach.
• Kliknij OK.

Spowoduje to utworzenie Nazwanego zakresu SPRZEDAŻ.

Metoda #2: Korzystanie z pola nazwy

• Wybierz zakres, dla którego chcesz utworzyć nazwę (nie zaznaczaj nagłówków).
• Przejdź do pola nazwy po lewej stronie paska formuły i wpisz nazwę, z którą chcesz utworzyć nazwany zakres.
• Zauważ, że utworzona tutaj nazwa będzie dostępna dla całego skoroszytu. Jeśli chcesz ograniczyć go do arkusza roboczego, użyj metody 1.

Metoda #3: Korzystanie z opcji Utwórz z zaznaczenia

Jest to zalecany sposób, gdy masz dane w formie tabelarycznej i chcesz utworzyć nazwany zakres dla każdej kolumny/wiersza.

Na przykład w poniższym zestawie danych, jeśli chcesz szybko utworzyć trzy nazwane zakresy (Data, Sprzedaż_Rep i Sprzedaż), możesz użyć metody pokazanej poniżej.

Oto kroki, aby szybko utworzyć nazwane zakresy z zestawu danych:

• Wybierz cały zestaw danych (w tym nagłówki).
• Przejdź do Formuły -> Utwórz z zaznaczenia (Skrót klawiaturowy – Control + Shift + F3). Otworzy się okno dialogowe „Utwórz nazwy z zaznaczenia”.
• W oknie dialogowym Utwórz nazwy z zaznaczenia zaznacz opcje, w których masz nagłówki. W tym przypadku wybieramy tylko górny wiersz, ponieważ nagłówek znajduje się w górnym wierszu. Jeśli masz nagłówki w górnym wierszu i lewej kolumnie, możesz wybrać oba. Podobnie, jeśli dane są ułożone, gdy nagłówki znajdują się tylko w lewej kolumnie, zaznaczysz tylko opcję Lewa kolumna.

Spowoduje to utworzenie trzech nazwanych zakresów - Data, Przedstawiciel_sprzedaży i Sprzedaż.

Zauważ, że automatycznie pobiera nazwy z nagłówków. Jeśli między słowami jest jakaś spacja, wstawia podkreślenie (ponieważ nie możesz mieć spacji w nazwanych zakresach).

Konwencja nazewnictwa dla nazwanych zakresów w programie Excel

Istnieją pewne reguły nazewnictwa, które musisz znać podczas tworzenia nazwanych zakresów w programie Excel:

• Pierwszym znakiem nazwanego zakresu powinna być litera i znak podkreślenia (_) lub ukośnik odwrotny (\). Jeśli to coś innego, pokaże błąd. Pozostałe znaki mogą być literami, cyframi, znakami specjalnymi, kropką lub podkreśleniem.
• Nie można używać nazw, które reprezentują również odwołania do komórek w programie Excel. Na przykład nie możesz użyć AB1, ponieważ jest to również odwołanie do komórki.
• Nie możesz używać spacji podczas tworzenia nazwanych zakresów. Na przykład przedstawiciel handlowy nie może być nazwanym zakresem. Jeśli chcesz połączyć dwa słowa i utworzyć nazwany zakres, użyj podkreślenia, kropki lub wielkich liter, aby go utworzyć. Możesz na przykład mieć Przedstawiciela_Sprzedaży, PrzedstawicielaSprzedażowego lub PrzedstawicielaSprzedażowego.
  • Podczas tworzenia nazwanych zakresów Excel traktuje wielkie i małe litery w ten sam sposób. Na przykład, jeśli utworzysz nazwany zakres SPRZEDAŻ, nie będziesz mógł utworzyć innego nazwanego zakresu, takiego jak „sprzedaż” lub „sprzedaż”.
• Nazwany zakres może mieć do 255 znaków.

Zbyt wiele nazwanych zakresów w programie Excel? Nie martw się

Czasami w dużych zestawach danych i złożonych modelach może się skończyć tworzenie wielu nazwanych zakresów w programie Excel.

Co zrobić, jeśli nie pamiętasz nazwy utworzonego przez siebie nazwanego zakresu?

Nie martw się - oto kilka przydatnych wskazówek.

Uzyskiwanie nazw wszystkich nazwanych zakresów

Oto kroki, aby uzyskać listę wszystkich nazwanych zakresów, które utworzyłeś:

• Przejdź do zakładki Formuły.
• W grupie Zdefiniowane nazwane kliknij Użyj w formule.
• Kliknij „Wklej nazwy”.

Spowoduje to wyświetlenie listy wszystkich nazwanych zakresów w tym skoroszycie. Aby użyć nazwanego zakresu (w formułach lub komórce), kliknij go dwukrotnie.

Wyświetlanie pasujących nazwanych zakresów

• Jeśli masz jakieś pojęcie o nazwie, wpisz kilka początkowych znaków, a Excel wyświetli listę pasujących nazw.

Jak edytować nazwane zakresy w programie Excel

Jeśli utworzyłeś już nazwany zakres, możesz go edytować, wykonując następujące czynności:

• Przejdź do zakładki Formuły i kliknij Menedżer nazw.
• Okno dialogowe Menedżer nazw wyświetli listę wszystkich nazwanych zakresów w tym skoroszycie. Kliknij dwukrotnie nazwany zakres, który chcesz edytować.
• W oknie dialogowym Edytuj nazwę wprowadź zmiany.
• Kliknij OK.
• Zamknij okno dialogowe Menedżer nazw.

Przydatne skróty do nazwanych zakresów (moc F3)

Oto kilka przydatnych skrótów klawiaturowych, które przydadzą się podczas pracy z nazwanymi zakresami w programie Excel:

• Aby uzyskać listę wszystkich nazwanych zakresów i wkleić ją do formuły: F3
• Aby utworzyć nową nazwę za pomocą okna dialogowego Menedżera nazw: Ctrl + F3
• Aby utworzyć nazwane zakresy z zaznaczenia: Ctrl + Shift + F3

Tworzenie dynamicznych nazwanych zakresów w Excelu

Jak dotąd w tym samouczku stworzyliśmy statyczne nazwane zakresy.

Oznacza to, że te nazwane zakresy zawsze odwołują się do tego samego zestawu danych.

Na przykład, jeśli A1:A10 został nazwany jako „Sprzedaż”, zawsze będzie odnosić się do A1:A10.

Jeśli dodasz więcej danych sprzedaży, będziesz musiał ręcznie przejść i zaktualizować referencję w nazwanym zakresie.

W świecie stale powiększających się zbiorów danych może to zająć dużo czasu. Za każdym razem, gdy otrzymujesz nowe dane, może być konieczne zaktualizowanie nazwanych zakresów w programie Excel.

Aby rozwiązać ten problem, możemy utworzyć dynamiczne nazwane zakresy w programie Excel, które automatycznie uwzględnią dodatkowe dane i uwzględnią je w istniejącym nazwanym zakresie.

Na przykład, jeśli dodam dwa dodatkowe punkty danych sprzedaży, dynamiczny nazwany zakres będzie automatycznie odwoływał się do A1:A12.

Ten rodzaj Dynamic Named Range można utworzyć za pomocą funkcji Excel INDEX. Zamiast określać odwołania do komórek podczas tworzenia Nazwanego zakresu, określamy formułę. Formuła jest automatycznie aktualizowana po dodaniu lub usunięciu danych.

Zobaczmy, jak tworzyć dynamiczne nazwane zakresy w programie Excel.

Załóżmy, że mamy dane sprzedaży w komórce A2:A11.

Oto kroki, aby utworzyć dynamiczne nazwane zakresy w programie Excel:

1. 1. Przejdź do zakładki Formuła i kliknij Zdefiniuj nazwę.
   2. W oknie dialogowym Nowa nazwa wpisz:
      • Nazwa: Sprzedaż
      • Zakres: skoroszyt
      • Odnosi się do: = $ A 2 $: INDEKS ($ A 2 $: 100 $, COUNTIF ($ A 2 $: 100 $ ”” i ””))
   3. Kliknij OK.

Zrobione!

Masz teraz dynamiczny nazwany zakres o nazwie „Sprzedaż”. To byłoby automatycznie aktualizowane za każdym razem, gdy dodasz do niego dane lub usuniesz z niego dane.

Jak działają dynamiczne nazwane zakresy?

Aby wyjaśnić, jak to działa, musisz dowiedzieć się nieco więcej o funkcji INDEKSU Excela.

Większość ludzi używa INDEX do zwracania wartości z listy na podstawie numeru wiersza i kolumny.

Ale funkcja INDEX ma też drugą stronę.

Może być używany do zwróć odwołanie do komórki gdy jest używany jako część odwołania do komórki.

Na przykład oto formuła, której użyliśmy do utworzenia dynamicznego zakresu nazwanego:

=$A$2:INDEKS($A$2:$A$100;LICZBA($A$2:$A$100;""&""))

INDEX($A$2:$A$100,COUNTIF($A$2:$A$100””&””) -> Ta część formuły powinna zwrócić wartość (która byłaby dziesiątą wartością z listy, biorąc pod uwagę, że jest dziesięć pozycji).

Jednak w przypadku użycia przed odnośnikiem (=2$ A$:INDEKS($A$2:$A$100,COUNTIF($A$2:$A$100""&""))) zwraca odwołanie do komórki zamiast wartości.

Stąd tutaj zwraca = $ A $ 2: $ A $ 11

Jeśli dodamy dwie dodatkowe wartości do kolumny sprzedaży, zwróci ona = $ A 2: $ A $ 13

Po dodaniu nowych danych do listy funkcja LICZ.JEŻELI programu Excel zwraca liczbę niepustych komórek w danych. Ta liczba jest używana przez funkcję INDEKS do pobrania odwołania do komórki ostatniego elementu na liście.

Notatka:

• Działałoby to tylko wtedy, gdy w danych nie ma pustych komórek.
• W powyższym przykładzie przypisałem dużą liczbę komórek (A2:A100) do formuły Nazwany zakres. Możesz to dostosować na podstawie swojego zestawu danych.

Możesz również użyć funkcji PRZESUNIĘCIE, aby utworzyć dynamiczne nazwane zakresy w programie Excel, jednak ponieważ funkcja PRZESUNIĘCIE jest niestabilna, może prowadzić do powolnego skoroszytu programu Excel. Z drugiej strony INDEKS jest częściowo niestabilny, co czyni go lepszym wyborem do tworzenia dynamicznych nazwanych zakresów w programie Excel.

Możesz również polubić następujące zasoby programu Excel:

• Darmowe szablony Excela.
• Bezpłatne szkolenie online z programu Excel (7-częściowy kurs wideo online).
• Przydatne przykłady kodu makr programu Excel.
• 10 przykładów zaawansowanych funkcji WYSZUKAJ.PIONOWO w Excelu.
• Tworzenie listy rozwijanej w programie Excel.
• Tworzenie nazwanego zakresu w Arkuszach Google.
• Jak odwoływać się do innego arkusza lub skoroszytu w programie Excel