Dlaczego powinieneś używać nazwanych zakresów w programie Excel

Nazwane zakresy są przydatną, ale często niewykorzystaną funkcją Microsoft Excel.  Nazwane zakresy mogą ułatwić zrozumienie (i debugowanie), uprościć tworzenie skomplikowanych arkuszy kalkulacyjnych i uprościć swoje makra.

Nazwany zakres to tylko zakres (albo pojedyncza komórka lub zakres komórek), do którego przypisujesz nazwę.  Następnie możesz użyć tej nazwy zamiast normalnych odniesień do komórek w formułach, w makrach i do zdefiniowania źródła wykresów lub sprawdzania poprawności danych.

Używając nazwy zasięgu, takiej jak Taxrate, zamiast standardowego odniesienia do komórki, takie jak arkusz2!$ C $, może ułatwić zrozumienie arkusza kalkulacyjnego i debugowania/audytu.

Używanie nazwanych zakresów w programie Excel

Na przykład spójrzmy na prosty formularz zamówienia.  Nasz plik zawiera wypełnienie formularza zamówienia z rozwijaną menadą, a także drugi arkusz z tabelą kosztów wysyłki i stawką podatkową.

Wersja 1 (bez nazwanych zakresów) używa normalnych odniesień do komórek w stylu A1 w swoich formułach (pokazanych na pasku Formuły poniżej).

Wersja 2 używa nazwanych zakresów, dzięki czemu jego formuły jest znacznie łatwiejsze do zrozumienia.  Nazwane zakresy ułatwiają wprowadzanie formuł, ponieważ Excel wyświetli listę nazw, w tym nazwy funkcji, z których możesz wybrać, ilekroć zaczniesz pisać nazwę w formule.  Kliknij dwukrotnie nazwę na liście wyboru, aby dodać ją do formuły.

Otwieranie Menedżer nazw okno od Formuły Karta wyświetla listę nazw zakresów i zakresu komórki, które odwołują.

Ale nazywane zakresy mają również inne korzyści.  W naszych przykładowych plikach metoda wysyłki jest wybierana za pomocą rozwijania (sprawdzanie poprawności danych) w komórce B13 na arkuszu.  Wybrana metoda jest następnie wykorzystywana do wyszukiwania kosztów wysyłki na arkuszu2.

Bez nazwanych zakresów wybory rozwijane należy wprowadzić ręcznie, ponieważ sprawdzanie poprawności danych nie pozwoli na wybranie listy źródłowej na innym arkuszu.  Więc wszystkie opcje muszą zostać wprowadzone dwukrotnie: raz na liście rozwijanej i ponownie na arkuszu2.  Ponadto obie listy muszą pasować.

Jeśli popełni się błąd w jednym z wpisów na dowolnej liście, wówczas formuła kosztu wysyłki wygeneruje błąd #nierodzony.  Nazywanie listy na arkuszu 2 jako Wysyłki eliminuje oba problemy.

Możesz odwołać się do nazwanego zakresu podczas definiowania sprawdzania poprawności danych dla listy rozwijanej, po prostu wpisując = Wysyłki Na przykład w polu źródłowym.  To pozwala użyć listy opcji, które są na innym arkuszu.

A jeśli rozwijana jest odwołuje się do rzeczywistych komórek używanych w wyszukiwaniu (dla formuły kosztu wysyłki), wówczas wybory rozwijane zawsze będą pasować do listy wyszukiwania, unikając #nie dotyczy błędów.

Utwórz nazwaną zasięg w programie Excel

Aby utworzyć nazwany zakres, po prostu wybierz komórkę lub zakres komórek, które chcesz wymienić, a następnie kliknij Pudełko na nazwę (Tam, gdzie wybrany adres komórki jest zwykle wyświetlany, po lewej stronie paska formuły), wpisz nazwę, której chcesz użyć, i naciśnij Wchodzić.

Możesz także utworzyć wymieniony zakres, klikając Nowy przycisk w oknie menedżera nazwy.  To otwiera Nowe imie okno, w którym możesz wprowadzić nową nazwę.

Domyślnie zasięg, który należy wymienić, jest ustawiony na dowolny zakres wybrany po kliknięciu nowego przycisku, ale możesz edytować ten zakres przed lub po zapisaniu nowej nazwy.

Zauważ, że nazwy zakresu nie mogą obejmować spacji, chociaż mogą zawierać podkreślenia i okresy.  Zasadniczo nazwy powinny zaczynać się od litery, a następnie zawierać tylko litery, liczby, okresy lub podkreśla.

Nazwy nie są wrażliwe na literę, ale użycie serii kapitalizowanych słów, takich jak Taxrate lub grudzień 2018 r., Ułatwia nazwy i rozpoznawanie.  Nie możesz użyć nazwy zasięgu, który naśladuje prawidłowe odniesienie do komórki, takie jak Dog26.

Możesz edytować nazwy zakresu lub zmienić zakresy, które odnoszą.

Zwróć również uwagę, że każdy wymieniony zakres ma określony zakres.  Zwykle zakres będzie domyślny zeszyt ćwiczeń, co oznacza, że ​​nazwa zasięgu można odwoływać z dowolnego miejsca w skoroszycie.  Możliwe jest jednak posiadanie dwóch lub więcej zakresów o tej samej nazwie na osobnych arkuszach, ale w tym samym skoroszycie.

Na przykład możesz mieć plik danych sprzedaży z osobnymi arkuszami na styczeń, luty, marca itp.  Każdy arkusz może mieć komórkę (nazywaną zakres) o nazwie MonthlySales, ale zwykle zakres każdej z tych nazw byłby tylko arkuszem zawierającym.

Zatem formuła = Round (MonthlySales, 0) dałby lutową sprzedaż, zaokrągloną do najbliższego całego dolara, jeśli formuła znajduje się na lutym arkuszu, ale marcowa sprzedaż, jeśli na arkuszu marcowym itp.

Aby uniknąć zamieszania w skoroszytach o wielu zakresach na osobnych arkuszach o tej samej nazwie lub po prostu skomplikowanych skoroszyt z dziesiątkami lub setkami nazwanych zakresów, pomocne może być włączenie nazwy arkusza jako części każdej nazwy zasięgu.

To sprawia, że ​​każda nazwa zasięgu jest wyjątkowa, dzięki czemu wszystkie nazwy mogą mieć zakres skoroszytu.  Na przykład styczeń_monthlysales, luty_monthlysales, budżet_date, order_date itp.

Dwie ostrzeżenia dotyczące zakresu nazwanych zakresów: (1 Nowy przycisk w Menedżer nazw okno.

Jeśli utworzysz nową nazwę zasięgu, wpisując ją w polu nazwy, zasięg domyślnie nie będzie skoroszytu (jeśli nie istnieje żaden inny zakres o tej samej nazwie) lub do arkusza, w którym nazwa jest tworzona.  Dlatego, aby utworzyć nowy wymieniony zakres, którego zakres jest ograniczony do konkretnego arkusza, użyj przycisku „Nowy” menedżera nazwy.

Wreszcie, dla tych, którzy piszą makr, nazwy zakresu można łatwo odwoływać się do kodu VBA, po prostu umieszczając nazwę zasięgu w nawiasach.  Na przykład zamiast tego robota.Arkusze (1).Komórki (2,3) można po prostu użyć [salestotal], jeśli ta nazwa odnosi się do tej komórki.

Zacznij używać nazwanych zakresów w arkuszach Excel, a szybko będziesz docenić korzyści! Cieszyć się!