nazwane zakresy są jedną z tych starych, chrupiących funkcji w Excelu, które rozumie niewielu użytkowników. Nowi użytkownicy mogą uznać je za dziwne i przerażające, a nawet stare ręce mogą ich unikać, ponieważ wydają się bezcelowe i złożone.
ale nazwane zakresy to faktycznie całkiem fajna funkcja. Mogą sprawić, że formuły* o wiele * będą łatwiejsze do tworzenia, czytania i utrzymywania. A jako bonus, sprawiają, że formuły łatwiejsze do ponownego użycia (bardziej przenośne).
w rzeczywistości używam nazwanych zakresów cały czas podczas testowania i prototypowania formuł. Pomagają mi przyspieszyć działanie receptur., Używam również nazwanych zakresów, ponieważ jestem leniwy i nie lubię wpisywać skomplikowanych odniesień 🙂
podstawy nazwanych zakresów w Excelu
Co to jest nazwany zakres?
nazwany zakres jest tylko czytelną dla człowieka nazwą zakresu komórek w programie Excel. Na przykład, jeśli nazwę zakres A1: A100 „dane”, mogę użyć MAX, aby uzyskać maksymalną wartość za pomocą prostej formuły:
=MAX(data) // max value
piękno nazwanych zakresów polega na tym, że możesz używać znaczących nazw w formułach bez myślenia o odwołaniach do komórek., Gdy masz nazwany zakres, po prostu użyj go tak jak odniesienia do komórki. Wszystkie te formuły są poprawne z nazwanym zakresem "dane":
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valueWideo: Jak utworzyć nazwany zakres
Tworzenie nazwanego zakresu jest łatwe
Tworzenie nazwanego zakresu jest szybkie i łatwe. Po prostu wybierz zakres komórek i wpisz nazwę w polu Nazwa. Po naciśnięciu klawisza return zostanie utworzona Nazwa:
aby szybko przetestować nowy zakres, wybierz nową nazwę z listy rozwijanej obok pola Nazwa. Excel wybierze zakres w arkuszu.,
program Excel może automatycznie tworzyć nazwy (ctrl + shift + F3)
Jeśli masz dobrze zorganizowane dane z etykietami, możesz poprosić program Excel o utworzenie nazwanych zakresów. Po prostu wybierz dane wraz z etykietami i użyj polecenia" Utwórz z wyboru " na karcie Formuły wstążki:
Możesz również użyć skrótu klawiaturowego control + shift + F3.
korzystając z tej funkcji, możemy utworzyć nazwane zakresy dla populacji 12 stanów w jednym kroku:
Po kliknięciu OK, nazwy są tworzone., Wszystkie nowo utworzone nazwy znajdziesz w rozwijanym menu obok pola Nazwa:
z utworzonymi nazwami możesz ich używać w takich formułach
=SUM(MN,WI,MI)Aktualizuj nazwane zakresy w Menedżerze nazw (Kontrola + F3)
Po utworzeniu zakresu nazw, użyj Menedżera nazw (control + F3), aby zaktualizować w razie potrzeby. Wybierz nazwę, z którą chcesz pracować, a następnie zmień bezpośrednio odniesienie (np. edytuj "odnosi się do") lub kliknij przycisk po prawej stronie i wybierz nowy zakres.,
nie ma potrzeby klikania przycisku Edytuj, aby zaktualizować odniesienie. Po kliknięciu przycisku Zamknij nazwa zakresu zostanie zaktualizowana.
Uwaga: Jeśli wybierzesz cały nazwany zakres w arkuszu roboczym, możesz przeciągnąć do nowej lokalizacji, a odniesienie zostanie zaktualizowane automatycznie. Nie wiem jednak, jak dostosować odniesienia do zakresu, klikając i przeciągając bezpośrednio na arkuszu. Jeśli wiesz, jak to zrobić, zadzwoń poniżej!
Zobacz wszystkie nazwane zakresy (control + F3)
aby szybko wyświetlić wszystkie nazwane zakresy w skoroszycie, użyj menu rozwijanego obok pola Nazwa.,
Jeśli chcesz zobaczyć więcej szczegółów, otwórz Menedżera nazw (Control + F3), który wyświetla listę wszystkich nazw z referencjami i zapewnia również filtr:
uwaga: na komputerze Mac nie ma menedżera nazw, więc zamiast tego zobaczysz okno Definiuj nazwę.
Kopiuj i wklej wszystkie nazwane zakresy (F3)
Jeśli chcesz mieć bardziej trwały zapis nazwanych zakresów w skoroszycie, możesz wkleić pełną listę nazw w dowolnym miejscu., Przejdź do formuł > Użyj w Formule (lub użyj skrótu F3), a następnie wybierz Wklej nazwy > Wklej listę:
Po kliknięciu przycisku Wklej listę zobaczysz nazwy i referencje wklejone do arkusza roboczego:
zobacz nazwy bezpośrednio w arkuszu roboczym
Jeśli ustawisz poziom powiększenia na mniej niż 40%, program Excel wyświetli nazwy zakresów bezpośrednio w arkuszu roboczym:
dzięki za tę wskazówkę, Felipe!,
nazwy mają reguły
podczas tworzenia nazwanych zakresów postępuj zgodnie z tymi regułami:
nazwane zakresy w formułach
nazwane zakresy są łatwe w użyciu w formułach
na przykład, powiedzmy, że nazwiesz komórkę w skoroszycie "zaktualizowano". Chodzi o to, że możesz umieścić bieżącą datę w komórce (Ctrl + 😉 i odwołać się do daty w innym miejscu skoroszytu.
formuła w B8 wygląda następująco:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")możesz wkleić tę formułę w dowolnym miejscu skoroszytu i będzie wyświetlana poprawnie., Za każdym razem, gdy zmienisz datę w "zaktualizowano", wiadomość zostanie zaktualizowana wszędzie tam, gdzie używana jest formuła. Więcej przykładów można znaleźć na tej stronie.
nazwane zakresy pojawiają się podczas wpisywania formuły
Po utworzeniu nazwanego zakresu pojawi się on automatycznie w formułach po wpisaniu pierwszej litery nazwy. Naciśnij klawisz tab, aby wprowadzić nazwę, gdy masz dopasowanie i chcesz, aby Excel wprowadził nazwę.,
nazwane zakresy mogą działać jak stałe
ponieważ nazwane zakresy są tworzone w centralnej lokalizacji, możesz ich używać jak stałych bez odniesienia do komórki. Na przykład możesz tworzyć nazwy takie jak " MPG "(mile na galon) i " CPG " (koszt na galon) i przypisywać stałe wartości:
następnie możesz używać tych nazw w dowolnym miejscu w formułach i aktualizować ich wartość w jednym centralnym miejscu.,
nazwane zakresy są domyślnie absolutne
domyślnie nazwane zakresy zachowują się jak odniesienia bezwzględne. Na przykład w tym arkuszu kalkulacyjnym wzór do obliczenia paliwa byłby następujący:
=C5/$D$2
odniesienie do D2 jest bezwzględne (zablokowane), więc formuła może być skopiowana bez zmiana D2.,
jeśli nazwiemy D2 "MPG", formuła staje się:
=C5/MPG
ponieważ MPG jest domyślnie absolutna, formuła może być skopiowana w dół kolumny D jako-jest.
nazwane zakresy mogą być również względne
chociaż nazwane zakresy są domyślnie bezwzględne, mogą być również względne. Względny nazwany zakres odnosi się do zakresu, który jest względny do położenia aktywnej komórki w czasie, gdy zakres jest tworzony. W rezultacie względne zakresy nazwane są przydatne w budowaniu formuł generycznych, które działają wszędzie tam, gdzie są przenoszone.,
na przykład możesz utworzyć ogólny "CellAbove" o nazwie range w następujący sposób:
- wybierz komórkę A2
- Control + F3, aby otworzyć kartę Name Manager
- w sekcji 'Refers to', a następnie wpisz: =A1
CellAbove pobierze teraz wartość z komórki powyżej, gdziekolwiek jest używana.
ważne: przed utworzeniem nazwy upewnij się, że aktywna komórka znajduje się we właściwym miejscu.
Zastosuj nazwane zakresy do istniejących formuł
Jeśli masz istniejące formuły, które nie używają nazwanych zakresów, możesz poprosić program Excel o zastosowanie nazwanych zakresów w formułach., Zacznij od wybrania komórek zawierających formuły, które chcesz zaktualizować. Następnie uruchom Formuły > Zdefiniuj nazwy > Zastosuj nazwy.
program Excel zastąpi referencje, które mają odpowiadający im zakres nazw, samą nazwą.
Możesz również zastosować nazwy za pomocą znajdź i zamień:
ważne: Zapisz kopię zapasową arkusza roboczego i wybierz tylko komórki, które chcesz zmienić przed użyciem znajdź i zamień na formułach.,kluczowe zalety nazwanych zakresów
nazwane zakresy sprawiają, że formuły są łatwiejsze do odczytania
największą zaletą nazwanych zakresów jest to, że formuły są łatwiejsze do odczytania i utrzymania. Dzieje się tak, ponieważ zastępują one tajemnicze odniesienia znacznymi nazwami. Na przykład rozważ ten arkusz z danymi na temat planet w naszym Układzie Słonecznym., Bez nazwanych zakresów, formuła VLOOKUP do pobrania "pozycji" z tabeli jest dość tajemnicza:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
jednak z B3:E11 o nazwie "data", a H4 o nazwie "planeta", możemy pisać formuły w następujący sposób:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
w skrócie można zobaczyć jedyną różnicę w tych wzorach w indeksie kolumny.
nazwane zakresy sprawiają, że formuły są przenośne i wielokrotnego użytku
nazwane zakresy mogą znacznie ułatwić ponowne użycie formuły w innym arkuszu roboczym., Jeśli zdefiniujesz nazwy z wyprzedzeniem w arkuszu roboczym, możesz wkleić formułę, która używa tych nazw i będzie "po prostu działać". Jest to świetny sposób, aby szybko uzyskać działanie formuły.
na przykład ta formuła liczy unikalne wartości w zakresie danych liczbowych:
=SUM(--(FREQUENCY(data,data)>0))aby szybko "przenieść" tę formułę do własnego arkusza roboczego, nazwij zakres "Dane" i wklej formułę do arkusza roboczego. Tak długo, jak "dane" zawierają wartości liczbowe, formuła będzie działać od razu.,
Wskazówka :polecam utworzyć potrzebne nazwy zakresu * first * w skoroszycie docelowym, a następnie skopiować formułę tylko jako tekst (tj. nie kopiuj komórki zawierającej formułę w innym arkuszu roboczym, po prostu skopiuj tekst formuły). Uniemożliwia to programowi Excel tworzenie nazw w locie i pozwala w pełni kontrolować proces tworzenia nazw. Aby skopiować tylko tekst formuły, skopiuj tekst z paska formuły lub skopiuj za pomocą innej aplikacji(np.).
nazwane zakresy mogą być używane do nawigacji
nazwane zakresy są świetne do szybkiej nawigacji., Po prostu wybierz menu rozwijane obok pola Nazwa i wybierz nazwę. Po zwolnieniu myszy zostanie wybrany zakres. Gdy na innym arkuszu istnieje określony zakres, zostanie on automatycznie przeniesiony do tego arkusza.
nazwane zakresy działają dobrze z hiperłączami
nazwane zakresy ułatwiają hiperłącza. Na przykład, jeśli nazwiesz A1 w arkuszu 1 "Strona główna", możesz utworzyć hiperłącze w innym miejscu, które zabierze cię tam z powrotem.,
aby użyć nazwanego zakresu wewnątrz funkcji hiperłącza, Dodaj symbol funta przed nazwanym zakresem:
=HYPERLINK("#home","take me home")uwaga: co dziwne, nie można hiperłącza do tabeli jak można normalną nazwę zakresu. Można jednak zdefiniować nazwę równą tabeli (np. =Table1) i hiperłącze do niej. Jeśli ktoś zna sposób na bezpośrednie połączenie ze stołem, zapraszam!,
zakresy nazw dla walidacji danych
zakresy nazw działają dobrze dla walidacji danych, ponieważ pozwalają na użycie logicznie nazwanego odniesienia do walidacji danych z rozwijanym menu. Poniżej, zakres G4:G8 ma nazwę "statuslist", a następnie zastosuj walidację danych z listą połączoną w ten sposób:
wynikiem jest menu rozwijane w kolumnie E, które pozwala tylko na wartości w nazwanym zakresie:
dynamiczne nazwane zakresy
zakresy nazw są niezwykle przydatne, gdy automatycznie dostosowują się do nowych danych w arkuszu roboczym., Zakres ustawiony w ten sposób jest określany jako "dynamiczny zakres nazwany". Istnieją dwa sposoby dynamiki zakresu: formuły i tabele.
dynamiczny zakres nazwany z tabelą
tabela jest najprostszym sposobem na utworzenie dynamicznego zakresu nazwanego. Wybierz dowolną komórkę z danych, a następnie użyj skrótu Control + t:
podczas tworzenia tabeli Excel automatycznie tworzy się nazwa (np. Table1), ale możesz zmienić nazwę tabeli, jak chcesz. Po utworzeniu tabeli zostanie ona automatycznie Rozszerzona po dodaniu danych.,
dynamiczny nazwany zakres z formułą
Możesz również utworzyć dynamiczny nazwany zakres z formułami, używając funkcji takich jak OFFSET i INDEX. Chociaż te formuły są umiarkowanie złożone, zapewniają lekkie rozwiązanie, gdy nie chcesz używać tabeli., Poniższe linki zawierają przykłady z pełnymi wyjaśnieniami:
- przykład formuły zakresu dynamicznego z indeksem
- przykład formuły zakresu dynamicznego z offsetem
nazwy tabel w walidacji danych
ponieważ tabele Excela zapewniają Automatyczny zakres dynamiczny, wydają się być naturalnym dopasowaniem do reguł walidacji danych, gdzie celem jest Walidacja na podstawie listy, która może się zawsze zmieniać. Jednak jeden problem z tabelami polega na tym, że nie można używać odwołań strukturalnych bezpośrednio do tworzenia zasad walidacji danych lub formatowania warunkowego., Innymi słowy, nie można używać nazwy tabeli w obszarach formatowania warunkowego lub zatwierdzania danych.
jednak jako obejście można zdefiniować nazwany zakres wskazujący na tabelę, a następnie użyć nazwanego zakresu do walidacji danych lub formatowania warunkowego. Poniższy film szczegółowo opisuje to podejście.
Wideo: Jak używać nazwanych zakresów z tabelami
usuwanie nazwanych zakresów
Uwaga: Jeśli masz formuły, które odnoszą się do nazwanych zakresów, możesz najpierw zaktualizować formuły przed usunięciem nazw. W przeciwnym razie zobaczysz #imię?, błędy w formułach, które nadal odnoszą się do usuniętych nazw. Zawsze Zapisz arkusz roboczy przed usunięciem nazwanych zakresów na wypadek problemów i konieczności powrotu do oryginału.
nazwane zakresy dostosowują się podczas usuwania i wstawiania komórek
gdy usuniesz *część* nazwanego zakresu lub jeśli wstawisz komórki/wiersze / kolumny wewnątrz nazwanego zakresu, odniesienie do zakresu zostanie odpowiednio dostosowane i pozostanie ważne. Jeśli jednak usuniesz wszystkie komórki, które zawierają nazwany zakres, nazwany zakres straci odniesienie i wyświetli błąd # REF., Na przykład, jeśli nazwę A1 „test”, a następnie Usuń kolumnę A, menedżer nazw pokaże „odnosi się do”jako:
=Sheet1!#REF!Usuń nazwy za pomocą Menedżera nazw
aby ręcznie usunąć nazwane zakresy ze skoroszytu, otwórz Menedżera nazw, wybierz zakres i kliknij przycisk Usuń. Jeśli chcesz usunąć więcej niż jedną nazwę w tym samym czasie, możesz Shift + Click lub Ctrl + Click, aby wybrać wiele nazw, a następnie usunąć w jednym kroku.,
Usuń nazwy z błędami
Jeśli masz wiele nazw z błędami odniesienia, możesz użyć przycisku filtruj w Menedżerze nazw, aby filtrować nazwy z błędami:
następnie shift+Kliknij, aby wybrać wszystkie nazwy i usunąć.
nazwane zakresy i zakres
nazwane zakresy w programie Excel mają coś o nazwie „zakres”, który określa, czy nazwany zakres jest lokalny dla danego arkusza roboczego, czy globalny dla całego skoroszytu. Nazwy globalne mają zakres „skoroszytu”, a nazwy lokalne mają zakres równy nazwie arkusza, na którym istnieją., Na przykład, zakres dla nazwy lokalnej może być „Sheet2”.
cel zakresu
nazwane zakresy z globalnym zakresem są przydatne, gdy chcesz, aby wszystkie arkusze w skoroszycie miały dostęp do pewnych danych, zmiennych lub stałych. Na przykład możesz użyć globalnego zakresu nazwanego założeniem stawki podatkowej używanego w kilku arkuszach roboczych.
zakres lokalny
zakres lokalny oznacza, że nazwa działa tylko na arkuszu, na którym został utworzony. Oznacza to, że możesz mieć wiele arkuszy w tym samym skoroszycie, które wszystkie używają tej samej nazwy., Na przykład, być może masz skoroszyt z miesięcznymi arkuszami śledzenia (jeden na miesiąc), które używają nazwanych zakresów o tej samej nazwie, wszystkie mają zasięg lokalny. Może to umożliwić ponowne użycie tych samych formuł w różnych arkuszach. Zakres lokalny pozwala nazwom w każdym arkuszu działać poprawnie bez kolizji z nazwami w innych arkuszach.
aby odnosić się do nazwy z lokalnym zakresem, możesz przedrostek nazwy arkusza do nazwy zakresu:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenuenazwy zakresu utworzone za pomocą pola Nazwa automatycznie mają zasięg globalny., Aby nadpisać to zachowanie, Dodaj nazwę arkusza podczas definiowania nazwy:
Sheet3!my_new_namezakres Globalny
zakres Globalny oznacza, że nazwa będzie działać w dowolnym miejscu skoroszytu. Na przykład możesz nazwać komórkę „last_update”, wprowadzić datę w komórce. Następnie możesz użyć poniższej formuły, aby wyświetlić datę ostatniej aktualizacji w dowolnym arkuszu roboczym.
=last_updatenazwy Globalne muszą być unikalne w skoroszycie.
zakres lokalny
zakresy nazwane lokalnie mają sens dla arkuszy roboczych, które używają nazwanych zakresów tylko dla założeń lokalnych., Na przykład, być może masz skoroszyt z miesięcznymi arkuszami śledzenia (jeden na miesiąc), które używają nazwanych zakresów o tej samej nazwie, wszystkie mają zasięg lokalny. Zakres lokalny pozwala nazwom w każdym arkuszu działać poprawnie bez kolizji z nazwami w innych arkuszach.
Zarządzanie nazwanym zakresem zakresu
Domyślnie nowe nazwy utworzone za pomocą namebox są globalne i nie można edytować zakresu nazwanego zakresu po jego utworzeniu. Jednak jako obejście można usunąć i odtworzyć nazwę z żądanym zakresem.,
Jeśli chcesz zmienić kilka nazw jednocześnie z globalnych na lokalne, czasami warto skopiować arkusz zawierający te nazwy. Gdy zduplikujesz arkusz zawierający nazwane zakresy, program Excel kopiuje nazwane zakresy do drugiego arkusza, zmieniając jednocześnie Zakres na lokalny. Po uzyskaniu drugiego arkusza z miejscowymi nazwami można opcjonalnie usunąć pierwszy arkusz.
Jan Karel Pieterse i Charles Williams opracowali narzędzie o nazwie Name Manager, które zapewnia wiele przydatnych operacji dla nazwanych zakresów. Możesz pobrać narzędzie Name Manager tutaj.,
