Praca z nazwanym zasięgiem w programie Microsoft Excel

Jednym z narzędzi upraszczających pracę z formułami i umożliwiającym optymalizację pracy z tablicami danych jest przypisanie nazw do tych tablic. Tak więc, jeśli chcesz odwołać się do szeregu homogenicznych danych, nie będziesz musiał pisać złożonego odniesienia, ale po prostu podaj prostą nazwę, którą sam wcześniej wyznaczyłeś określoną tablicę. Zobaczmy podstawowe niuanse i zalety pracy z nazwanymi zakresami.

Manipulacja z nazwanymi obszarami

Nazwany zakres to obszar komórek, do którego użytkownik ma przypisaną określoną nazwę. W tym przypadku nazwa ta jest traktowana jako Excel, jako adres określonego obszaru. Może być używany jako część formuł i argumentów funkcji, a także w specjalistycznych narzędziach Excel, na przykład "Sprawdzanie wartości wejściowych" .

Istnieją obowiązkowe wymagania dotyczące nazwy grupy komórek:

• Nie powinno w nim być żadnych spacji;
• Musi koniecznie zaczynać się od litery;
• Jego długość nie może być większa niż 255 znaków;
• Nie powinien być reprezentowany przez współrzędne formularza A1 lub R1C1 ;
• Książka nie powinna mieć tego samego imienia.

Nazwa obszaru komórek może być widoczna, gdy zostanie wybrana w polu nazw, umieszczonym po lewej stronie linii formuły.

Jeśli nazwa nie jest przypisana do zakresu, to w powyższym polu, po wybraniu, wyświetlany jest adres lewej górnej komórki tablicy.

Tworzenie nazwanego zakresu

Przede wszystkim nauczymy się tworzyć nazwany zakres w Excelu.

1. Najszybszym i najłatwiejszym sposobem przypisania nazwy do tablicy jest zapisanie jej w polu nazwy po wybraniu odpowiedniego obszaru. Wybierz tablicę i wprowadź nazwę w polu, które uważamy za konieczne. Pożądane jest, aby był łatwo zapamiętany i odpowiedział na zawartość komórek. I, oczywiście, konieczne jest, aby spełniał on obowiązkowe wymagania, które zostały określone powyżej.
2. Aby program wpisał tę nazwę do własnego rejestru i zapamiętaj ją, kliknij klawisz Enter . Nazwa zostanie przypisana do wybranego obszaru komórki.

Powyżej została nazwana najszybszą opcją nadania nazwy tablicy, ale w żadnym wypadku nie jest jedyną. Tę procedurę można również wykonać za pomocą menu kontekstowego

1. Wybierz tablicę, powyżej której chcesz wykonać operację. Kliknij zaznaczenie prawym przyciskiem myszy. Na liście, która się otworzy, zatrzymujemy wybór w opcji "Przypisuj nazwę ..." .
2. Otwiera okno tworzenia nazwy. W obszarze "Nazwa" należy wpisać nazwę zgodnie z warunkami podanymi powyżej. W obszarze "Zakres" wyświetlany jest adres wybranej tablicy. Jeśli dokonałeś prawidłowego wyboru, nie musisz wprowadzać żadnych zmian w tym obszarze. Kliknij przycisk "OK" .
3. Jak widać w polu nazwy, nazwa regionu jest pomyślnie przypisana.

Inną opcją dla tego zadania jest użycie narzędzi na taśmie.

1. Wybierz obszar komórki, który chcesz przekonwertować na nazwaną komórkę. Przechodzimy do zakładki "Formuły" . W grupie "Specific Names" kliknij ikonę "Assign name" .
2. Otworzy się to samo okno przypisywania nazw, tak jak w poprzedniej wersji. Wszystkie dalsze operacje wykonywane są dokładnie w ten sam sposób.

Ostatnią opcją przypisywania nazwy do obszaru komórek, które rozważymy, jest użycie menedżera nazw .

1. Wybierz tablicę. Na karcie "Formuły" klikamy dużą ikonę "Name Manager" znajdującą się w tej samej grupie "Specific Names" . Zamiast tego możesz użyć skrótu klawiaturowego Ctrl + F3 .
2. Okno Menedżer nazw zostało aktywowane. W tym celu kliknij przycisk "Utwórz ..." w lewym górnym rogu.
3. Następnie rozpoczyna się już znane okno tworzenia pliku, w którym należy wykonać czynności opisane powyżej. Nazwa, która zostanie przypisana do tablicy, zostanie wyświetlona w Menedżerze . Można go zamknąć, klikając standardowy przycisk zamykania w prawym górnym rogu.

Lekcja: Jak przypisać nazwę do komórki w Excelu

Operacje z nazwanymi zakresami

Jak wspomniano powyżej, nazwane tablice mogą być używane podczas wykonywania różnych operacji w Excelu: formuły, funkcje, specjalne narzędzia. Spójrzmy na konkretny przykład tego, jak to się dzieje.

Na jednym arkuszu znajduje się lista modeli sprzętu komputerowego. Na drugim arkuszu w tabeli mamy zadanie, aby utworzyć listę rozwijaną z tej listy.

1. Przede wszystkim na arkuszu z listą przypisujemy zakres do nazwy za pomocą dowolnej z wyżej wymienionych metod. W rezultacie, po wybraniu listy w polu nazwy, powinniśmy wyświetlić nazwę tej tablicy. Niech to będzie nazwa "Modelu" .
2. Następnie przejdź do arkusza, w którym znajduje się stół, w którym musimy utworzyć listę rozwijaną. Wybierz obszar w tabeli, w którym planujemy wprowadzić listę rozwijaną. Przechodzimy do zakładki "Dane" i klikamy przycisk "Sprawdź dane" w polu narzędzia "Praca z danymi" na taśmie.
3. W oknie, które rozpoczyna weryfikację danych, przejdź do zakładki "Parametry" . W polu "Typ danych" wybierz "Lista" . W polu "Źródło" w zwykłym przypadku musisz ręcznie wprowadzić wszystkie elementy przyszłej rozwijanej listy lub podać link do ich listy, jeśli znajduje się ona w dokumencie. Nie jest to bardzo wygodne, zwłaszcza jeśli lista znajduje się na innym arkuszu. Ale w naszym przypadku wszystko jest znacznie prostsze, ponieważ przypisaliśmy nazwę do odpowiedniej tablicy. Po prostu umieść znak "równy" i wpisz tę nazwę w polu. Otrzymano następujące wyrażenie:

   =Модели

   Kliknij "OK" .

4. Teraz po przesunięciu kursora do dowolnej komórki w zakresie, w którym zastosowaliśmy sprawdzanie danych, po prawej stronie pojawi się trójkąt. Kliknięcie tego trójkąta otwiera listę danych wejściowych, które są wyciągane z listy na innym arkuszu.
5. Musimy tylko wybrać odpowiednią opcję, aby wartość z listy była wyświetlana w wybranej komórce tabeli.

Nazwany zakres jest również wygodny do użycia jako argumenty dla różnych funkcji. Rzućmy okiem na to, jak to działa w praktyce na konkretnym przykładzie.

Mamy zatem tabelę, w której miesięcznie rejestrowane są dochody pięciu oddziałów przedsiębiorstwa. Musimy znać całkowity przychód dla Filii 1, Oddziału 3 i Oddziału 5 przez cały okres wskazany w tabeli.

1. Po pierwsze, każdy wiersz odpowiedniego oddziału w tabeli zostanie nazwany. W Oddziale 1 wybierz obszar z komórkami, które zawierają dane o przychodach za ten okres przez 3 miesiące. Po zaznaczeniu w polu nazwy wpisz nazwę "Branch_1" (nie zapominaj, że nazwa nie może zawierać spacji) i kliknij klawisz Enter . Nazwa odpowiedniego obszaru zostanie przypisana. Jeśli chcesz, możesz użyć dowolnego innego sposobu nadania wyżej wspomnianej nazwy.
2. W ten sam sposób, wybierając odpowiednie obszary, podajemy nazwy linii innych gałęzi: "Branch_2" , "Branch_3" , "Branch_4" , "Branch_5" .
3. Wybierz element arkusza, na którym zostanie wydrukowany wynik sumowania. Klikamy na ikonę "Wstaw funkcję" .
4. Rozpocznie się uruchamianie Kreatora funkcji . Przechodzimy do bloku "Matematycznego" . Zatrzymujemy wybór z listy dostępnych operatorów na nazwie "SUMM" .
5. Aktywacja okna operatora SUMM jest aktywowana. Ta funkcja, która jest częścią grupy operatorów matematycznych, została specjalnie zaprojektowana do podsumowania wartości liczbowych. Składnia jest reprezentowana przez następującą formułę:

   =СУММ(число1;число2;…)

   Jak nietrudno zrozumieć, operator podsumowuje wszystkie argumenty grupy "Numer" . Jako argumenty można wykorzystać zarówno wartości liczbowe, jak i odniesienia do komórek lub zakresów, w których się znajdują. W przypadku tablic argumenty są sumą wartości zawartych w ich elementach, liczonych w tle. Możemy powiedzieć, że "skaczemy", poprzez działanie. Do rozwiązania naszego problemu będzie użyte sumowanie zakresów.

   Łącznie operator SUMM może mieć od jednego do 255 argumentów. Ale w naszym przypadku potrzebne będą tylko trzy argumenty, ponieważ dodamy trzy zakresy: "Oddział_1" , "Oddział_3" i "Oddział_5" .

   Ustaw kursor w polu "Numer 1" . Ponieważ nadaliśmy nazwy zakresom, które chcesz dodać, nie musisz wprowadzać współrzędnych w polu ani wybierać odpowiednich obszarów na arkuszu. Wystarczy podać nazwę tablicy, która ma zostać dodana : "Branch_1" . W polach "Numer 2" i "Numer 3" wpisujemy odpowiednio "Oddział_3" i "Oddział_5" . Po wykonaniu powyższych manipulacji kliknij "OK" .

6. Wynik obliczeń jest wyświetlany w komórce wybranej przed przejściem do Kreatora funkcji .

Jak widać, przypisanie nazwy do grup komórek w tym przypadku pozwoliło na uproszczenie zadania dodawania wartości liczbowych znajdujących się w nich, w porównaniu z sytuacją, gdy mamy do czynienia z adresami, a nie nazwami.

Oczywiście, te dwa przykłady, które cytowaliśmy powyżej, nie pokazują wszystkich zalet i możliwości wykorzystania nazwanych zakresów podczas korzystania z nich w funkcjach, formułach i innych narzędziach Excel. Użycie tablic, którym nadano nazwę, jest nieobliczalne. Niemniej jednak, przykłady te pozwalają zrozumieć główne zalety przypisywania nazw do obszarów arkusza w porównaniu z wykorzystaniem ich adresów.

Lekcja: Jak obliczyć kwotę w programie Microsoft Excel

Zarządzanie nazwanymi przedziałami

Zarządzanie tworzonymi nazwanymi zakresami jest najłatwiejsze dzięki Menedżerowi nazw . Za pomocą tego narzędzia możesz przypisać nazwy do tablic i komórek, zmodyfikować istniejące nazwane obszary i je wyeliminować. Już rozmawialiśmy o tym, jak nadać nazwę przy pomocy kontrolera , a teraz uczymy się, jak wykonywać w nim inne manipulacje.

1. Aby przejść do Dispatchera , przechodzimy do zakładki "Formuły" . Tam powinieneś kliknąć ikonę, która nazywa się "Name Manager" . Określona ikona znajduje się w grupie "Specific Names" .
2. Po przejściu do Menedżera, aby dokonać niezbędnych manipulacji z zakresem, musisz znaleźć jego nazwę na liście. Jeśli lista elementów nie jest zbyt obszerna, to jest dość prosta. Ale jeśli w obecnej książce znajduje się kilka dziesiątek nazwanych tablic lub więcej, to aby ułatwić zadanie, warto użyć filtra. Klikamy przycisk "Filtr" , znajdujący się w prawym górnym rogu okna. Filtrowanie można wykonać na następujące sposoby, wybierając odpowiedni element w otwartym menu:
   • Nazwy na arkuszu;
   • w książce;
   • z błędami;
   • bez błędów;
   • Niektóre imiona;
   • Nazwy tabel.

   Aby powrócić do pełnej listy nazw, wystarczy wybrać opcję "Wyczyść filtr" .

3. Aby zmienić ramkę, nazwę lub inne właściwości nazwanego zakresu, wybierz żądaną pozycję w Menedżerze i kliknij przycisk "Zmień ..." .
4. Otworzy się okno do zmiany nazwy. Zawiera dokładnie te same pola co okno do tworzenia nazwanego zakresu, o którym wspominaliśmy wcześniej. Tylko tym razem pola zostaną wypełnione danymi.

   W polu "Nazwa" możesz zmienić nazwę pola. W polu "Uwaga" możesz dodać lub edytować istniejącą notatkę. W polu "Zakres" możesz zmienić adres nazwanej tablicy. Można to zrobić, stosując ręczne wprowadzanie wymaganych współrzędnych i ustawiając kursor w polu i wybierając odpowiednią tablicę komórek na arkuszu. Jego adres jest natychmiast wyświetlany w polu. Jedynym polem, którego wartości nie można edytować, jest "Obszar" .

   Po zakończeniu edycji danych kliknij przycisk "OK" .

Ponadto w Menedżerze, jeśli to konieczne, można wykonać procedurę usuwania nazwanego zakresu. W tym przypadku, oczywiście, nie zostaną usunięte, obszar na samym arkuszu i przypisana do niego nazwa. Tak więc, po zakończeniu procedury, określonej tablicy można uzyskać tylko poprzez jej współrzędne.

Jest to bardzo ważne, ponieważ jeśli już zastosowałeś usuniętą nazwę w jakiejś formule, to po skasowaniu nazwy ta formuła stanie się błędna.

1. Aby wykonać procedurę usuwania, wybierz żądaną pozycję z listy i kliknij przycisk "Usuń" .
2. Następnie uruchamiane jest okno dialogowe, które prosi o potwierdzenie decyzji o usunięciu wybranego elementu. Odbywa się to, aby zapobiec pomyleniu procedury przez użytkownika. Tak więc, jeśli jesteś pewny, że chcesz usunąć, a następnie kliknij przycisk "OK" w polu potwierdzenia. W przeciwnym razie kliknij przycisk "Anuluj" .
3. Jak widać, wybrany element został usunięty z listy kontrolera . Oznacza to, że tablica, do której został dołączony, straciła swoją nazwę. Teraz będzie identyfikowany tylko przez współrzędne. Po zakończeniu wszystkich operacji w Menedżerze kliknij przycisk "Zamknij" , aby opuścić okno.

Używanie nazwanego zakresu może ułatwić pracę z formułami, funkcjami i innymi narzędziami programu Excel. Wymienione elementy można kontrolować (modyfikować i usuwać) za pomocą specjalnego wbudowanego menedżera .

• ← Tworzenie łączy w Microsoft Excel
• Obliczanie różnicy w programie Microsoft Excel →