Jak używać nazw w formułach w programie Excel

Anonim

W tym artykule dowiemy się, jak używać nazw w formule w programie Excel.

Scenariusz:

Podczas pracy w programie Excel na pewno słyszałeś o nazwanych zakresach w programie Excel. Może od znajomego, kolegi lub jakiegoś samouczka online. Wielokrotnie wspominałem o tym w moich artykułach. W tym artykule dowiemy się o nazwanym zakresie w programie Excel i zbadamy każdy jego aspekt.

Nazwane zakresy w Excelu

Formuły -> Menedżer nazw -> Zdefiniuj nazwę

Cóż, nazwane zakresy to nic innego jak niektóre zakresy programu Excel, którym nadano jakąś znaczącą nazwę. Na przykład, jeśli masz komórkę powiedzmy B1, zawierającą codzienne cele, 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.

  • Iść do Formuła Patka
  • Znajdź sekcję Zdefiniowane nazwy i kliknij Zdefiniuj nazwy. To otworzy Imię menedżera.
  • 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. Oto kilka.

  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 A1, B1 lub AZ100 itp.
  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.

Cóż, przez większość czasu będziesz pracować z tabelami danych strukturalnych. 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. Excel udostępnia narzędzie do automatycznego nazywania zakresów za pomocą nagłówkó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, a nie chcę nazywać wierszy.
  • Kliknij OK.

Teraz każda kolumna jest nazwana jako ich nagłówek. Za każdym razem, gdy wpiszesz formułę, nazwy te będą wyświetlane jako opcje, do których można uzyskać dostęp.

Kiedy tabulujemy dane w Excelu za pomocą CTRL + T, nagłówek kolumny jest automatycznie przypisywany jako nazwa odpowiedniej kolumny. Powinieneś zapoznać się z tabelami Excela i ich zaletami.

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 Zakładka Formuła > Imię i nazwisko menedżera. Spowoduje to wyświetlenie listy 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. To jest dobre. Eliminuje to możliwość niejednoznacznoś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ę wklej listę okno dialogowe.
  • Kliknij na wklej lista przycisk.
  • Lista zostanie wklejona do wybranych komórek 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ć nazwany zakres. Aby zaktualizować nazwane zakresy, wykonaj następujące kroki.

  • naciskać CTRL+F3 otworzyć nazwa menedżera.
  • 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 sumaryczną 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ędzia do usuwania tylko nazw z błędami. 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 wykorzystanie nazwanych zakresów jest badane za pomocą formuł. Formuły stają się naprawdę elastyczne i czytelne dzięki zakresom nazwanym. 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”. W przypadku nazw łatwo jest napisać tę formułę LICZ.JEŻELI. Tylko napisz

=LICZ.JEŻELI(Przedmiot,"Ołówek")

Jak tylko wpiszesz nawias otwierający formułę, pojawi się lista dostępnych nazwanych zakresów

Bez nazwy napisałbyś zakres funkcji LICZ.JEŻELI programu Excel, dla której 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.

Nazwane zakresy są wyświetlane jako sugestie podczas wpisywania dowolnej litery. Jak Excel pokazuje listę formuł. Na przykład, jeśli wpiszesz =u, każda formuła i nazwany zakres będą wyświetlane zaczynając od u, dzięki czemu można 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 utworzyć 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ć liczby jako stałe 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 do nazwanego zakresu, będzie on zachowywał się jak odwołanie względne. Zobacz poniższy obraz.

Ale kiedy używasz 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 domyślnie są 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 go wcześniej w funkcji KOLUMNA. 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ę przed powtarzaniem się 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ły. 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 otrzyma informację, że liczymy wystąpienia '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 ma tej samej nazwy.

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 policzyć 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

Łatwiejsza nawigacja w skoroszycie z nazwanymi zakresami. Wystarczy wpisać nazwę w polu nazwy. Excel zabierze Cię do zakresu, bez względu na to, gdzie jesteś w skoroszycie. Biorąc pod uwagę, że nazwany zakres jest zakresem skoroszytu.

Na przykład, jeśli jesteś na arkuszu 10 i chcesz uzyskać 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, kolejne menu powinno 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

Mam nadzieję, że ten artykuł o tym, jak używać nazw w formule w programie Excel, jest objaśniający. Więcej artykułów na temat zakresów nazewnictwa i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.

Pole nazwy w Excelu : Pole nazwy programu Excel to nic innego jak mały obszar wyświetlania w lewym górnym rogu arkusza programu Excel, który pokazuje nazwę aktywnej komórki lub zakresów w programie Excel. Możesz zmienić nazwę komórki lub tablicy dla odwołań.

Jak uzyskać nazwę arkusza arkusza roboczego w programie Excel? : Funkcja KOMÓRKA w programie Excel dostarcza informacji dotyczących dowolnego arkusza roboczego, takiego jak kolor, zawartość, nazwa pliku itp. Dowiedz się, jak uzyskać nazwę arkusza za pomocą funkcji KOMÓRKA tutaj.

Jak uzyskać sekwencyjny numer wiersza w programie Excel: Czasami musimy uzyskać kolejny numer wiersza w tabeli, może to być numer seryjny lub cokolwiek innego. W tym artykule dowiemy się, jak numerować wiersze w programie Excel od początku danych.

Zwiększ liczbę w ciągu tekstowym w programie Excel: Jeśli masz dużą listę elementów i musisz zwiększyć ostatnią liczbę tekstu starego tekstu w programie Excel, będziesz potrzebować pomocy z dwóch funkcji TEKST i PRAWY.

Popularne artykuły :

Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.

Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.

Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.

Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.