Praca z nazwami zakresów

Podczas analizy danych odwoływanie się do różnych danych będzie bardziej znaczące i łatwiejsze, jeśli odwołanie będzie dotyczyło nazw, a nie odwołań do komórek - dotyczy to pojedynczej komórki lub zakresu komórek. Na przykład, jeśli obliczasz wartość bieżącą netto na podstawie stopy dyskontowej i serii przepływów pieniężnych, wzór

Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

ma większe znaczenie niż

C10 = NPV (C2, C6: C8)

Za pomocą programu Excel możesz tworzyć i używać znaczących nazw dla różnych części danych. Zalety używania nazw zakresów obejmują -

• Znacząca nazwa zakresu (na przykład Cash_Flows) jest znacznie łatwiejsza do zapamiętania niż adres Range (na przykład C6: C8).

• Wprowadzenie nazwy jest mniej podatne na błędy niż wprowadzenie adresu komórki lub zakresu.

• Jeśli wpiszesz nazwę niepoprawnie w formule, program Excel wyświetli plik #NAME? błąd.

• Możesz szybko przejść do obszarów arkusza, używając zdefiniowanych nazw.

• Dzięki nazwom Twoje formuły będą bardziej zrozumiałe i łatwiejsze w użyciu. Na przykład formuła Net_Dochód = Brutto_Dochód - Odliczenia jest bardziej intuicyjna niż C40 = C20 - B18.

• Tworzenie formuł z nazwami zakresów jest łatwiejsze niż w przypadku adresów komórek lub zakresów. Możesz skopiować nazwę komórki lub zakresu do formuły przy użyciu funkcji autouzupełniania formuły.

W tym rozdziale dowiesz się -

• Reguły składni dla nazw.
• Tworzenie nazw odwołań do komórek.
• Tworzenie nazw dla stałych.
• Zarządzanie nazwiskami.
• Zakres twoich zdefiniowanych nazw.
• Edycja nazw.
• Filtrowanie nazw.
• Usuwanie nazwisk.
• Stosowanie nazw.
• Używanie nazw w formule.
• Przeglądanie nazwisk w skoroszycie.
• Używanie wklejania nazw i wklejania listy.
• Używanie nazw dla przecięć zakresów.
• Kopiowanie formuł z nazwami.

Kopiowanie nazwy za pomocą funkcji autouzupełniania formuł

Wpisz pierwszą literę nazwy w formule. Pojawi się lista rozwijana z nazwami funkcji i nazwami zakresów. Wybierz wymaganą nazwę. Jest kopiowany do twojej formuły.

Reguły składni nazw zakresów

Excel ma następujące reguły składni dla nazw -

• Możesz użyć dowolnej kombinacji liter, cyfr i symboli - podkreśleń, ukośników odwrotnych i kropek. Inne symbole nie są dozwolone.

• Nazwa może zaczynać się od znaku, podkreślenia lub ukośnika odwrotnego.

• Nazwa nie może zaczynać się od liczby (na przykład - 1stQuarter) ani przypominać adresu komórki (na przykład - QTR1).

• Jeśli wolisz używać takich nazw, poprzedź je podkreśleniem lub ukośnikiem odwrotnym (przykład - \ 1stQuarter, _QTR1).

• Nazwy nie mogą zawierać spacji. Jeśli chcesz rozróżnić dwa słowa w nazwie, możesz użyć podkreślenia (przykład- Cash_Flows zamiast Cash Flow)

• Twoje zdefiniowane nazwy nie powinny kolidować z wewnętrznie zdefiniowanymi nazwami programu Excel, takimi jak Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title. Jeśli zdefiniujesz te same nazwy, zastąpią one wewnętrzne nazwy programu Excel i nie otrzymasz żadnego komunikatu o błędzie. Jednak odradza się tego.

• Nazwy powinny być krótkie, ale zrozumiałe, ale możesz użyć do 255 znaków

Tworzenie nazw zakresów

Nazwy zakresów można tworzyć na dwa sposoby -

• Używając Name box.

• Używając New Name Okno dialogowe.

• Używając Selection Okno dialogowe.

Utwórz nazwę zakresu za pomocą pola nazwy

Aby utworzyć nazwę zakresu, użyj rozszerzenia NameNajszybszym sposobem jest pole po lewej stronie paska formuły. Wykonaj czynności podane poniżej -

Step 1 - Wybierz zakres, dla którego chcesz zdefiniować Nazwę.

Step 2 - Kliknij pole Nazwa.

Step 3 - Wpisz nazwę i naciśnij Enter, aby utworzyć Nazwę.

Utwórz nazwę zakresu za pomocą okna dialogowego Nowa nazwa

Nazwy zakresów można również tworzyć za pomocą okna dialogowego Nowa nazwa na karcie Formuły.

Step 1 - Wybierz zakres, dla którego chcesz zdefiniować nazwę.

Step 2 - Kliknij kartę Formuły.

Step 3- Kliknij opcję Zdefiniuj nazwę w grupie Nazwy zdefiniowane. PlikNew Name pojawi się okno dialogowe.

Step 4 - Wpisz nazwę w polu obok opcji Nazwa

Step 5- Sprawdź, czy zakres wybrany i wyświetlany w polu Odwołania jest prawidłowy. Kliknij OK.

Utwórz nazwę zakresu za pomocą okna dialogowego Utwórz nazwy z wyboru

Możesz również tworzyć nazwy zakresów za pomocą rozszerzenia Create Names z okna dialogowego Wybór na karcie Formuły, jeśli masz wartości tekstowe sąsiadujące z zakresem.

Step 1 - Wybierz zakres, dla którego chcesz zdefiniować nazwę, wraz z wierszem / kolumną zawierającą nazwę.

Step 2 - Kliknij kartę Formuły.

Step 3 - Kliknij Create from Selectionw grupie Nazwy zdefiniowane. PlikCreate Names from Selection pojawi się okno dialogowe.

Step 4 - Wybierz górny wiersz, ponieważ tekst pojawi się w górnym wierszu zaznaczenia.

Step 5- Sprawdź zakres, który został wybrany i wyświetlony w polu obok Odnosi się, aby był poprawny. Kliknij OK.

Teraz możesz znaleźć największą wartość w zakresie za pomocą =Sum(Nazwisko ucznia), jak pokazano poniżej -

Możesz także tworzyć nazwy z wielokrotnym wyborem. W poniższym przykładzie możesz nazwać rząd ocen każdego ucznia imieniem i nazwiskiem ucznia.

Teraz możesz znaleźć całkowitą liczbę ocen dla każdego ucznia za pomocą =Sum (nazwisko ucznia), jak pokazano poniżej.

Tworzenie nazw dla stałych

Załóżmy, że masz stałą, która będzie używana w całym skoroszycie. Możesz nadać mu nazwę bezpośrednio, bez umieszczania go w komórce.

W poniższym przykładzie stopa procentowa banku oszczędności jest ustawiona na 5%.

• Kliknij Zdefiniuj nazwę.
• W oknie dialogowym Nowa nazwa wpisz Savings_Bank_Interest_Rate w polu Nazwa.
• W zakresie wybierz skoroszyt.
• W polu Odwołuje się do wyczyść zawartość i wpisz 5%.
• Kliknij OK.

Imię Savings_Bank_Interest_Ratejest ustawiony na stałe 5%. Możesz to sprawdzić w menedżerze nazw. Widać, że wartość jest ustawiona na 0,05 i w plikuRefers to = 0,05.

Zarządzanie nazwami

Skoroszyt programu Excel może zawierać dowolną liczbę nazwanych komórek i zakresów. Możesz zarządzać tymi nazwami za pomocą Menedżera nazw.

• Kliknij kartę Formuły.

• Kliknij Name Manager w Defined NamesGrupa. PlikName Managerpojawi się okno dialogowe. Wyświetlane są wszystkie nazwy zdefiniowane w bieżącym skoroszycie.

Lista Names są wyświetlane ze zdefiniowanym Values, Cell Reference (w tym nazwa arkusza), Scope i Comment.

Menedżer nazw ma opcje:

• Zdefiniuj New Nazwij z New Przycisk.

• Edit Zdefiniowana nazwa.

• Delete Zdefiniowana nazwa.

• Filter Nazwy zdefiniowane według kategorii.

• Zmodyfikuj zakres zdefiniowanej nazwy Refers to.

Zakres nazwy

Plik Scopenazwy domyślnie jest skoroszytem. Możesz znaleźćScope zdefiniowanych nazw z listy nazw pod Scope kolumna w Name Manager.

Możesz zdefiniować Scope z a New Name kiedy definiujesz nazwę za pomocą New NameOkno dialogowe. Na przykład definiujesz nazwę Interest_Rate. Wtedy możesz zobaczyć, żeScope z New Name Interest_Rate to Workbook.

Załóżmy, że chcesz Scope tej stopy procentowej ogranicza się do tego Worksheet tylko.

Step 1- Kliknij strzałkę w dół w Zakresie Modelu. Dostępne opcje zakresu pojawią się na liście rozwijanej.

Opcje zakresu obejmują Workbooki nazwy arkuszy w skoroszycie.

Step 2- Kliknij nazwę bieżącego arkusza, w tym przypadku NPV, i kliknij OK. Możesz zdefiniować / znaleźć nazwę arkusza w zakładce arkusza.

Step 3 - Aby sprawdzić, czy zakres to arkusz roboczy, kliknij Name Manager. W kolumnie Zakres znajdziesz wartość NPV dla stopy procentowej. Oznacza to, że możesz użyć nazwy Interest_Rate tylko w NPV arkusza roboczego, ale nie w innych arkuszach.

Note - Po zdefiniowaniu zakresu nazwy nie można go później zmodyfikować.

Usuwanie nazw z wartościami błędów

Czasami może się tak zdarzyć, że definicja nazwy może zawierać błędy z różnych powodów. Możesz usunąć takie nazwy w następujący sposób -

Step 1 - Kliknij Filter w Name Manager Okno dialogowe.

Pojawiają się następujące opcje filtrowania -

• Czysty filtr
• Nazwy ograniczone do arkusza roboczego
• Nazwy ograniczone do skoroszytu
• Nazwy z błędami
• Nazwy bez błędów
• Zdefiniowane nazwy
• Nazwy tabel

Możesz aplikować Filter do defined Names wybierając jedną lub więcej z tych opcji.

Step 2 - Wybierz Names with Errors. Zostaną wyświetlone nazwy zawierające wartości błędów.

Step 3 - Z uzyskanej listy Names, wybierz te, które chcesz usunąć i kliknij Delete.

Otrzymasz wiadomość potwierdzającą usunięcie. Kliknij OK.

Edycja nazw

Możesz użyć Edit opcja w Name Manager okno dialogowe do -

• Zmienić Name.

• Zmodyfikuj Refers to zasięg

• Edytuj plik Comment w Name.

Zmień nazwę

Step 1 - Kliknij komórkę zawierającą funkcję Large.

Jak widać, do tablicy dodano jeszcze dwie wartości, ale nie są one zawarte w funkcji, ponieważ nie są częścią tablicy Array1.

Step 2 - Kliknij Name chcesz edytować w Name ManagerOkno dialogowe. W tym przypadku,Array1.

Step 3 - Kliknij Edit. PlikEdit Name pojawi się okno dialogowe.

Step 4 - Zmień Name wpisując nową nazwę w Name Box.

Step 5 - Kliknij Range przycisk po prawej stronie Refers to Box i dołącz nowe odwołania do komórek.

Step 6 - Dodaj Comment (Opcjonalny)

Zauważ, że Scope jest nieaktywny i dlatego nie można go zmienić.

Kliknij OK. Będziesz obserwować wprowadzone zmiany.

Stosowanie nazw

Rozważmy następujący przykład -

Jak widać, nazwy nie są zdefiniowane i używane w funkcji PMT. Jeśli umieścisz tę funkcję w innym miejscu arkusza, musisz również pamiętać, gdzie dokładnie znajdują się wartości parametrów. Wiesz, że używanie nazw jest lepszą opcją.

W tym przypadku funkcja jest już zdefiniowana przy użyciu odwołań do komórek, które nie mają nazw. Nadal możesz definiować nazwy i je stosować.

Step 1 - Korzystanie Create from Selectionzdefiniuj nazwy.

Step 2- Wybierz komórkę zawierającą formułę. Kliknij

obokDefine Name w Defined Names grupa na Formulaspatka. Na liście rozwijanej kliknijApply Names.

Step 3 - The Apply Namespojawi się okno dialogowe. WybierzNames że chcesz Apply i kliknij OK.

Wybrane nazwy zostaną zastosowane do wybranych komórek.

Możesz też Apply Names do całego arkusza, wybierając arkusz i powtarzając powyższe kroki.

Używanie nazw w formule

Możesz użyć Name w Formula w następujący sposób -

• Wpisanie Name jeśli to pamiętasz, lub

• Wpisując jedną lub dwie pierwsze litery i używając Excel Formula Autocomplete funkcja.

• Kliknięcie opcji Użyj w formule w grupie Nazwy zdefiniowane na karcie Formuły.

  • Wybierz wymaganą Nazwę z rozwijanej listy zdefiniowanych nazw.

  • Kliknij dwukrotnie tę nazwę.

• Używając Paste Name Okno dialogowe.

  • Wybierz opcję Wklej nazwy z rozwijanej listy zdefiniowanych nazw. Pojawi się okno dialogowe Wklej nazwę.

  • Wybierz Name w Paste Names okno dialogowe i kliknij je dwukrotnie.

Przeglądanie nazw w skoroszycie

Możesz uzyskać wszystkie pliki Names w skoroszycie wraz z plikami References i Save je lub Print im.

• Kliknij pustą komórkę, do której chcesz skopiować plik Names w skoroszycie.

• Kliknij Use in Formula w Defined Names Grupa.

• Kliknij Paste Names z listy rozwijanej.

• Kliknij Paste List w Paste Name wyświetlone okno dialogowe.

Lista nazwisk i odpowiadających im odniesień jest kopiowana w określonej lokalizacji w arkuszu, jak pokazano na zrzucie ekranu podanym poniżej -

Używanie nazw dla przecięć zakresów

Range Intersections to te pojedyncze komórki, które mają dwa wspólne zakresy.

Na przykład w danych podanych poniżej zakres B6: F6 i zakres C3: C8 mają wspólną komórkę C6, która w rzeczywistości reprezentuje oceny uzyskane przez ucznia Kodedę, Adama z egzaminu 1.

Możesz uczynić to bardziej znaczącym dzięki Range Names.

• Stwórz Names z Create from Selection zarówno dla studentów, jak i egzaminów.

• Twój Names będzie wyglądać następująco -

• Wpisz =Kodeda_Adam Exam_1 w B11.

Tutaj używasz operacji Przecięcie zakresów, odstęp między dwoma zakresami.

Spowoduje to wyświetlenie śladów Kodedy, Adama z egzaminu 1, które są podane w komórce C6.

Kopiowanie formuł z nazwami

Możesz skopiować formułę z nazwami według Copyi Paste w tym samym arkuszu.

Możesz również skopiować formułę z nazwami do innego arkusza przez copy i paste, pod warunkiem, że wszystkie names w formula mieć workbook tak jak Scope. W przeciwnym razie otrzymasz plik#VALUE błąd.