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

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

Użycie nazwy zakresu, takiej jak TaxRate , zamiast standardowego odwołania do komórki, takiego jak Arkusz2^(Sheet2) !$C$11, może ułatwić zrozumienie arkusza kalkulacyjnego i debugowanie/kontrolę.

Używanie nazwanych zakresów w Excelu

Spójrzmy na przykład na prosty formularz zamówienia.  Nasz plik zawiera formularz zamówienia do wypełnienia z rozwijanym menu wyboru metody wysyłki oraz drugi arkusz z tabelą kosztów wysyłki i stawką podatku.

Wersja 1 (bez nazwanych zakresów) używa w swoich formułach zwykłych odwołań do komórek w stylu A1^(A1-style) (pokazanych na pasku formuły poniżej).

Wersja 2 używa nazwanych zakresów, dzięki czemu jej formuły są znacznie łatwiejsze do zrozumienia. Nazwane zakresy ułatwiają również wprowadzanie formuł, ponieważ program Excel wyświetla listę nazw, w tym nazwy funkcji, z których można wybierać, za każdym razem, gdy zaczniesz wpisywać nazwę w formule.  Kliknij dwukrotnie^{(Double-click)} nazwę na liście wyboru, aby dodać ją do formuły.

Otwarcie okna Menedżer nazw^{(Name Manager)} z zakładki Formuły^(Formulas) wyświetla listę nazw zakresów i zakresów komórek, do których się odwołują.

Ale nazwane zakresy mają również inne zalety. W naszych przykładowych plikach metoda wysyłki jest wybierana za pomocą listy rozwijanej (walidacja danych) w komórce B13 w arkuszu Arkusz1^(Sheet1) . Wybrana metoda jest następnie używana do wyszukiwania kosztów wysyłki w Arkuszu2^(Sheet2) .

Bez nazwanych zakresów opcje listy rozwijanej należy wprowadzić ręcznie, ponieważ sprawdzanie poprawności danych nie pozwoli na wybranie listy źródeł w innym arkuszu. Tak więc wszystkie wybory muszą być wprowadzone dwukrotnie: raz na liście rozwijanej i ponownie na Sheet2 . Ponadto obie listy muszą się zgadzać.

W przypadku wystąpienia błędu w jednym z wpisów na jednej z list formuła kosztów wysyłki wygeneruje błąd #N/D po wybraniu błędnego wyboru. Nazwanie listy w Sheet2 jako ShippingMethods eliminuje oba problemy.

Możesz odwołać się do nazwanego zakresu podczas definiowania sprawdzania poprawności danych dla listy rozwijanej, na przykład wprowadzając =ShippingMethods w polu źródłowym. Pozwala to na użycie listy wyborów, które znajdują się na innym arkuszu.

A jeśli menu rozwijane odwołuje się do komórek faktycznie użytych w wyszukiwaniu (dla formuły kosztów wysyłki), opcje menu rozwijanego zawsze będą pasować do listy wyszukiwania, unikając błędów #N/D.

Utwórz nazwany zakres w programie Excel

Aby utworzyć nazwany zakres, po prostu zaznacz komórkę lub zakres komórek, które chcesz nazwać, a następnie kliknij w polu Nazwa^{( Name Box)} (gdzie zwykle wyświetlany jest adres wybranej komórki, tuż po lewej stronie paska formuły^{(Formula Bar)} ), wpisz nazwę, której chcesz użyć i naciśnij klawisz Enter .

Możesz również utworzyć nazwany zakres, klikając przycisk Nowy w oknie ^(New)Menedżera^(Manager) nazw . Spowoduje to otwarcie okna Nowa nazwa^{(New Name)}  , w którym możesz wprowadzić nową nazwę.

Domyślnie zakres, który ma zostać nazwany, jest ustawiony na dowolny zakres wybrany po kliknięciu przycisku Nowy^(New) , ale można edytować ten zakres przed zapisaniem nowej nazwy lub po zapisaniu.

Pamiętaj, że nazwy zakresów nie mogą zawierać spacji, chociaż mogą zawierać podkreślenia i kropki. Ogólnie nazwy powinny zaczynać się od litery, a następnie zawierać tylko litery, cyfry, kropki lub podkreślenia.

W nazwach nie jest rozróżniana wielkość liter, ale użycie ciągu słów pisanych wielką literą, takich jak TaxRate lub December2018Sales , ułatwia czytanie i rozpoznawanie nazw. Nie można użyć nazwy zakresu naśladującej prawidłowe odwołanie do komórki, takiej jak Dog26 .

Możesz edytować nazwy swoich zakresów lub zmieniać zakresy, do których się odnoszą, korzystając z okna Menedżera nazw.^(Manager)

Zauważ również, że każdy nazwany zakres ma zdefiniowany zakres. Zwykle zakres zostanie ustawiony domyślnie na Workbook , co oznacza, że ​​do nazwy zakresu można się odwoływać z dowolnego miejsca w skoroszycie. Jednak możliwe jest również 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 oddzielnymi arkuszami dla stycznia^(January) , lutego^(February) , marca^(March) itd. Każdy arkusz może mieć komórkę (nazwany zakres) o nazwie MonthlySales , ale zwykle zakresem każdej z tych nazw byłby tylko arkusz zawierający to.

Zatem formuła =ROUND(MonthlySales,0) dałaby sprzedaż w lutym^(February) zaokrągloną do najbliższego pełnego dolara, jeśli formuła znajduje się w arkuszu z lutego^(February) , ale sprzedaż w marcu w arkuszu z ^(March)marca^(March) itd.

Aby uniknąć nieporozumień w skoroszytach zawierających wiele zakresów na osobnych arkuszach o tej samej nazwie lub po prostu skomplikowanych skoroszytach z dziesiątkami lub setkami nazwanych zakresów, pomocne może być uwzględnienie nazwy arkusza jako części nazwy każdego zakresu.

Dzięki temu każda nazwa zakresu jest unikatowa, dzięki czemu wszystkie nazwy mogą mieć zakres skoroszytu . ^(Workbook)Na przykład January_MonthlySales^{(Budget_Date)} , February_MonthlySales , Budget_Date^{(January_MonthlySales)} , Order_Date , itd.

Dwa ostrzeżenia dotyczące zakresu nazwanych zakresów:^{(Two cautions regarding the scope of named ranges:)} (1) Nie możesz edytować zakresu nazwanego zakresu po jego utworzeniu oraz (2) możesz określić zakres nowego nazwanego zakresu tylko, jeśli utworzysz go za pomocą przycisku Nowy^(New) w okno Menedżer nazw^{( Name Manager)} .

Jeśli utworzysz nową nazwę zakresu, wpisując ją w polu Nazwa ,^(Box) zakres zostanie domyślnie ustawiony na Skoroszyt^(Workbook) (jeśli nie istnieje żaden inny zakres o tej samej nazwie) lub na arkusz, w którym tworzona jest nazwa. Dlatego, aby utworzyć nowy nazwany zakres, którego zakres jest ograniczony do konkretnego arkusza, użyj przycisku „Nowy” Menedżera nazw.

Wreszcie, dla tych, którzy piszą makra, nazwy zakresów można łatwo odwoływać się w kodzie VBA , po prostu umieszczając nazwę zakresu w nawiasach. Na przykład zamiast ThisWorkbook.Sheets (1).Cells(2,3) możesz po prostu użyć [ SalesTotal ], jeśli ta nazwa odnosi się do tej komórki.

Zacznij^(Start) używać nazwanych zakresów w arkuszach programu Excel^(Excel) , a szybko docenisz korzyści! Cieszyć się!

Why You Should Be Using Named Ranges in Excel

Named rangeѕ are a useful, but often underutilizеd, feature of Mіcrosoft Excel.  Named ranges can makе formulas easier to understand (and debug), simplify the creation of cоmplicated spreadsheets, and simplify your macros.

A named range is just a range (either a single cell, or a range of cells) to which you assign a name.  You can then use that name in place of normal cell references in formulas, in macros, and for defining the source for graphs or data validation.

Using a range name, like TaxRate, in place of a standard cell reference, like Sheet2!$C$11, can make a spreadsheet easier to understand and debug/audit.

Using Named Ranges in Excel

For example, let’s look at a simple order form.  Our file includes a fillable order form with a dropdown to select the shipping method, plus a second sheet with a table of shipping costs, and the tax rate.

Version 1 (without named ranges) uses normal A1-style cell references in its formulas (shown in the formula bar below).

Version 2 uses named ranges, making its formulas much easier to understand.  Named ranges also make it easier to enter formulas, since Excel will display a list of names, including function names, that you can pick from, whenever you start to type a name in a formula.  Double-click the name in the pick list to add it to your formula.

Opening the Name Manager window from the Formulas tab displays a list of the range names and the cell ranges they reference.

But named ranges have other benefits also.  In our example files, the shipping method is selected using a dropdown (data validation) in cell B13 on Sheet1.  The selected method is then used to lookup the shipping costs on Sheet2.

Without named ranges, the dropdown choices must be manually entered since data validation will not allow you to select a source list on a different sheet.  So all of the choices must be entered twice: once in the dropdown list, and again on Sheet2.  In addition, the two lists must match.

If an error is made in one of the entries in either list, then the shipping cost formula will generate an #N/A error when the erroneous choice is selected.  Naming the list on Sheet2 as ShippingMethods eliminates both problems.

You can reference a named range when defining the data validation for a dropdown list by simply entering =ShippingMethods in the source field, for example.  This allows you to use a list of choices that are on another sheet.

And if the dropdown is referencing the actual cells used in the lookup (for the shipping cost formula), then the dropdown choices will always match the lookup list, avoiding #N/A errors.

Create a Named Range in Excel

To create a named range, simply select the cell or range of cells you want to name, then click in the Name Box (where the selected cell address is normally displayed, just left of the Formula Bar), type the name you want to use, and press Enter.

You can also create a named range by clicking the New button in the Name Manager window.  This opens a New Name window where you can enter the new name.

By default, the range to be named is set to whatever range is selected when you click the New button, but you can edit that range before or after saving the new name.

Note that range names cannot include spaces, although they can include underscores and periods.  Generally, names should begin with a letter and then contain only letters, numbers, periods, or underscores.

Names are not case-sensitive, but using a string of capitalized words, such as TaxRate or December2018Sales, makes the names easier to read and recognize.  You cannot use a range name that mimics a valid cell reference, such as Dog26.

You can edit your range names or change the ranges they refer to using the Name Manager window.

Note also that each named range has a defined scope.  Normally, the scope will default to Workbook, which means the range name can be referenced from anywhere within the workbook.  However, it is also possible to have two or more ranges with the same name on separate sheets, but within the same workbook.

For example, you might have a sales data file with separate sheets for January, February, March, etc.  Each sheet could have a cell (named range) called MonthlySales, but normally the scope of each of those names would only be the sheet containing it.

Thus, the formula =ROUND(MonthlySales,0) would give February sales, rounded to the nearest whole dollar, if the formula is on the February sheet, but March sales if on the March sheet, etc.

To avoid confusion in workbooks having multiple ranges on separate sheets with the same name or simply complicated workbooks with dozens or hundreds of named ranges, it can be helpful to include the sheet name as part of each range name.

This also makes each range name unique, so that all the names can have a Workbook scope.  For example, January_MonthlySales, February_MonthlySales, Budget_Date, Order_Date, etc.

Two cautions regarding the scope of named ranges: (1) You cannot edit the scope of a named range after it is created, and (2) you can only specify the scope of a new named range if you create it using the New button in the Name Manager window.

If you create a new range name by typing it in the Name Box, the scope will default to either Workbook (if no other range with the same name exists), or to the sheet where the name is being created.  Therefore, to create a new named range whose scope is limited to a particular sheet, use the Name Manager “New” button.

Finally, for those who write macros, range names can be easily referenced in VBA code by simply placing the range name within brackets.  For example, instead of ThisWorkbook.Sheets(1).Cells(2,3) you can simply use [SalesTotal] if that name refers to that cell.

Start using named ranges in your Excel worksheets and you will quickly being to appreciate the benefits! Enjoy!

Related posts

• Jak usunąć puste linie w Excelu?

• Jak korzystać z funkcji Speak Cells programu Excel

• Jak wstawić arkusz programu Excel do dokumentu programu Word?

• Jak korzystać z analizy warunkowej programu Excel

• Jak naprawić wiersz w programie Excel

• Jak korzystać z funkcji PMT w programie Excel?

• Grupuj wiersze i kolumny w arkuszu programu Excel

• 10 porad i wskazówek dotyczących programu Excel na rok 2019

• Różnice między Microsoft Excel Online i Excel dla komputerów stacjonarnych

• Korzystanie z narzędzia wyszukiwania celów analizy warunkowej w programie Excel

• Jak wstawić CSV lub TSV do arkusza programu Excel?

• Jak wyszukiwać w Excelu

• Jak scalić dane w wielu plikach Excel

• Jak usunąć linie siatki w programie Excel

• Arkusze Google a Microsoft Excel – jakie są różnice?

• Jak korzystać z odwołań bezwzględnych w programie Excel

• Jak utworzyć wiele połączonych list rozwijanych w programie Excel?

• Podłączanie Excela do MySQL

• Kiedy używać dopasowania indeksu zamiast funkcji WYSZUKAJ.PIONOWO w programie Excel

• Jak korzystać z wypełniania Flash w programie Excel