Po co nam nazwy w Excelu? Dowiedz się i ułatw sobie życie.

Excel ma nam ułatwiać życie. Z kolei, używanie nazwy w Excelu ma nam ułatwić zadania jakie w nim wykonujemy. Dlatego najważniejsze powody, dla których stosujemy nazwy to:

1. Zwiększenie przejrzystości i czytelności formuł, dzięki czemu stają się łatwiejsze w obsłudze.
2. Automatyzacja zmian w arkuszach, w sytuacji gdy musimy zaktualizować jakieś dane.

W tym artykule dowiesz się jak:

1. Definiować nazwy i jakie obowiązują przy tym zasady.
2. W jakich sytuacjach „opłaca się” użycie nazw.
3. Jak zarządzać nazwami, czyli: modyfikować, usuwać czy zmieniać ich parametry.

Jak tworzymy nazwy w Excelu

Nazwy w programie Excel odnoszą się do pojedynczych komórek, zakresów komórek, stałych wartości, tabeli i funkcji. Są 4 zasady dotyczące tworzenia nazw:

1. Nazwa nie może być dłuższa niż 255 znaków (nie polecam tworzenia takich długich)
2. Nie zaczynaj nazwy inaczej jak od litery lub znaku podkreślenia.
3. W nazwie nie można używać spacji i znaków specjalnych, typu: @,!,#,$.
4. Nazwy nie mogą się powtarzać (są unikalne w obrębie pliku)

Istnieją dwa sposoby na utworzenie nazwy:

1. Poprzez pole nazwy komórki.
2. Z wykorzystaniem menedżera nazw.

Pole nazwy komórki

W celu nadania nazwy pojedynczej komórce lub zakresowi komórek możesz skorzystać z pola nazwy komórki.

Aby utworzyć nazwę dla komórki lub zakresu komórek, możesz skorzystać z jednej z dwóch ścieżek.

Sposób 1. Zaznacz komórkę lub zakres komórek, a następnie kliknij lewym przyciskiem myszy w pole nazwy komórki i wpisz nazwę. Koniecznie zatwierdź ją ENTEREM na klawiaturze.

Sposób 2. Zaznacz komórkę wraz z jej etykietą i skorzystaj z przycisku UTWÓRZ Z ZAZNACZENIA. Zostanie wyświetlone okno, w którym wskazujesz, gdzie znajduje się etykieta nazwy w zaznaczonym przez Ciebie zakresie. Następnie kliknij na przycisk OK, a nazwa zostanie utworzona automatycznie.

Menedżer nazw

Okno Menadżera nazw możesz wywołać przez skrót CTRL + F3. Odpowiednie polecenie znajdziesz także na wstążce FORMUŁY. Aby, utworzyć nazwę w ten sposób:

• zaznacz komórkę lub zakres komórek.
• na wstążce FORMUŁY, w sekcji NAZWY ZDEFINIOWANE, kliknij polecenie MENEDŻER NAZW.

Zostanie wyświetlone okno tworzenia nowej nazwy. Teraz uzupełnij kolejne pola:

• Nazwa – to nazwa zaznaczonej komórki / zakresu.
• Zakres – można zostawić wartość domyślną – określasz do jakiej części skoroszytu będzie przypisana dana nazwa, czyli gdzie będzie jej można użyć w formułach np. tylko jeden z arkuszy w skoroszycie.
• Komentarz – możesz zostawić puste – to opis nazwy, który wyświetli się pod daną nazwą podczas wpisywania formuły.
• Odwołuje się do – jeśli przed kliknięciem NOWY została zaznaczona komórka lub zakres, to pole będzie uzupełnione adresem tej komórki lub zakresu.

Nazwy w Excelu mogą się także odwoływać do wartości stałej, czyli takiej która nie będzie znajdować się w żadnej komórce arkusza. Aby utworzyć taką nazwę także korzystasz z MENEDŻERA NAZW. W sekcji ODWOŁUJE SIĘ DO skasuj podpowiedziany adres i wpisz wartość stałą po znaku =.

Nazwy w Excelu – kiedy to się opłaca?

Porównaj ze sobą takie dwa zapisy:

=([Zeszyt1]Arkusz1!$A$1+[Zeszyt1]Arkusz1!$B$1)*[Zeszyt1]Arkusz1!$C$1

=(podstawa+premia)*składka_ZUS

Który jest bardziej czytelny? 😉

Nazwy ułatwiają zrozumienie formuł

To pierwszy powód, dla którego warto używać nazw. W sytuacji gdy tworzysz wiele formuł, a poziom ich skomplikowania rośnie – zdecydowanie dobrym pomysłem jest upraszczać ich zapis.

Czasami napracujemy się solidnie, tworząc jakieś narzędzie w Excelu. Tworzymy wiele skomplikowanych zależności i formuł. W momencie, gdy tworzymy – wszystko jest dla nas jasne. Ale spróbuj wrócić do swojej pracy po tygodniu, czy miesiącu i zrozumieć co miałeś na myśli, a zrozumiesz o co mi chodzi.

Nazwy pozwalają na automatyzację

Ale w używaniu nazw nie chodzi tylko o upraszczanie zapisów formuł. Z utworzonych nazw możesz oczywiście korzystać w formułach. Aby to zrobić, musisz wpisać zdefiniowaną nazwę w formułę (po wpisaniu pierwszych liter, nazwy będą się podpowiadać).

Możesz też skorzystać z przycisku UŻYJ W FORMULE, ze wstążki FORMUŁY w sekcji NAZWY ZDEFINIOWANE. Po kliknięciu przycisku zostanie rozwinięta lista wszystkich nazw zdefiniowanych w skoroszycie.

Wyobraź sobie teraz, że dostałeś zadanie zbudowania modelu budżetowego. W modelu będzie bardzo wiele formuł, w których liczysz m.in. podatek VAT, wg różnych stawek. Używasz formuł w postaci:

=SUMA(A1:A10)*23%

=SUMA(B10:B20)*8%

W efekcie, po wielu godzinach czy dniach pracy, jesteś zadowolony. Powstało narzędzie, które automatyzuje proces budżetowania w Twojej firmie. Wtedy przychodzi TA chwila…, czyli zmiana przepisów. Rząd wprowadza podwyżkę (wszakże, jakżeby inaczej) stawek podatkowych. Co to oznacza dla Twojego modelu? Oczywiście, musisz znaleźć wszystkie komórki, w których używałeś do tej pory stawek 8% czy 23%, i zmienić je na nowe…

Ilość pracy, jaką będziesz musiał wykonać będzie wprost proporcjonalna do tego, jak często odwołanie do tych stawek występuje w modelu. Gdyby jednak, było można przewidzieć zmianę przepisów…?

Właściwie nie musisz ich przewidywać. Przede wszystkim powinieneś tworzyć elastyczne narzędzia, czyli Twoje pierwotne formuły, mogłyby wyglądać tak:

=SUMA(A1:A10)*vat_23

=SUMA(B10:B20)*vat_8

Wykorzystując nazwę (vat_8, vat_23) – po aktualizacji przepisów – Twoje zadanie ograniczy się tylko do modyfikacji wartości nazw w MENEDŻERZE NAZW. Wszystkie formuły, które wykorzystują te nazwy – zostaną automatycznie zaktualizowane. Podobnie proste będzie wykonywanie symulacji różnych stawek.

Modyfikowanie nazw

Jeżeli chcesz zmienić wcześniej utworzoną nazwę, skorzystaj z omawianej wcześniej ścieżki:

• na wstążce FORMUŁY, w sekcji NAZWY ZDEFINIOWANE, kliknij polecenie MENEDŻER NAZW.
• Zaznacz wybraną nazwę i kliknij EDYTUJ.
• Zostanie wyświetlone okno edycji nazwy, w którym możesz dokonać edycji poszczególnych pól.

Utworzoną nazwę możesz również usunąć, wykonując kolejne kroki:

• na wstążce FORMUŁY, w sekcji NAZWY ZDEFINIOWANE, kliknij polecenie MENEDŻER NAZW.
• Zaznacz wybraną nazwę i kliknij USUŃ.

Pamiętaj jednak, że jeśli dana nazwa była wykorzystana już w formułach, to po jej usunięciu formuły, które ją wykorzystywały zwrócą błąd #NAZWA?

Podsumowanie

Nazw w Excelu używasz z dwóch powodów. Po pierwsze upraszczasz zapisy formuł, a po drugie automatyzujesz pracę, w sytuacji zmian i konieczności aktualizacji parametrów, które się powtarzają.

Możesz je tworzyć za pomocą pola nazwy komórki i menedżera nazw. Pamiętaj o 4 regułach przy ich wprowadzaniu.

Dobrą praktyką w korzystaniu z nazw jest zaczynanie ich tym samym skrótem – na obrazkach powyżej mogłeś zauważyć, że każda nazwa poprzedzona jest skrótem „ex_”. Taki zabieg ułatwia późniejsze wpisywania nazw w formułach.

Jeżeli uważasz, że wiedza z tego artykułu może komuś jeszcze pomóc, udostępnij go proszę. A jeżeli masz jakieś dodatkowe pytania – zadaj je w komentarzu pod artykułem. Na pewno odpowiem 😉

Funkcja DATA RÓŻNICA, czyli odejmowanie dat

Funkcja DATA RÓŻNICA [Ang: DATEDIF] jest funkcją, która pokazuje wynik odejmowania dat. Została udokumentowana tylko w wersji Excel 2000, ale możesz jej używać we wszystkich późniejszych wersjach. Oznacza to także, że nie uzyskasz pomocy dotyczącej chociażby argumentów tej funkcji. Ale spokojnie –  funkcja działa 🙂

Jak działa?

Dzięki tej funkcji policzysz różnicę pomiędzy dwoma datami. W zależności od parametru jakiego użyjesz – otrzymasz różną wartość tej różnicy. Parametr umieszczamy w cudzysłowie.

Składnia i argumenty

=DATA.RÓŻNICA(data początkowa;data końcowa;"parametr" )

Lista parametrów:

• „D” – dla różnicy w dniach,
• „M” – dla różnicy w ukończonych miesiącach,
• „Y” – dla różnicy w ukończonych latach,
• „MD” – dla różnicy w dniach, z pominięciem miesięcy i lat,
• „YM” – dla różnicy w miesiącach, z pominięciem dni i lat,
• „YD” – dla różnicy w dniach, z pominięciem lat.

Przykład użycia

Chcesz policzyć różnicę w dniach pomiędzy datą wystawienia faktury, a terminem płatności. Powiedzmy, że datę wystawienia faktury masz w komórce A2, a datę płatności w B2.

Użyj:

=DATA.RÓŻNICA(A2;B2;"D")

Funkcja DATA RÓŻNICA jest także często wykorzystywana do obliczania wieku. Wtedy odejmowanie dat: urodzenia i aktualnej – da nam różnicę w latach o ile użyjemy parametru „R”.

Inne przykłady z parametrami:

Pobierz plik z przykładami.

Co jeszcze powinieneś wiedzieć?

1. Jeżeli data początkowa będzie większa niż końcowa, funkcja zwróci błąd #LICZBA!. Dlatego tworząc bardziej skomplikowaną formułę z jej wykorzystaniem, gdzie data początkowa / końcowa jest wynikiem obliczeń – warto użyć funkcji JEŻELI.BŁĄD, która zabezpieczy Cię przed niepożądanym rezultatem.
2. Microsoft nie zaleca używania parametru „MD” ze względu na „pewne ograniczenia”.
3. Daty w Excelu są „przechowywane” jako kolejne liczby. Dlatego możemy na nich dokonywać obliczeń. I tak 1 stycznia 1900 roku to liczba 1, a 31 grudnia 2018 to liczba 43 465. Możesz się o tym przekonać zamieniając format daty na format ogólny.

Podsumowanie

1. Odejmowanie dat jest możliwe przez zwykłe działanie matematyczne (od komórki z datą, odejmij inną komórkę z datą)
2. Funkcji DATA.RÓŻNICA użyj, kiedy chcesz policzyć bardziej skomplikowaną różnicę, np. w miesiącach czy latach.
3. Uważaj na potencjalny błąd związany z parametrem „MD”
4. Pamiętaj, aby data początkowa była mniejsza niż końcowa.

Jeżeli artykuł Ci pomógł zostaw proszę komentarz i podziel się ze znajomymi. I pamiętaj – szczęśliwi czasu nie liczą 😉

Data urodzenia z PESEL – krok po kroku

Szczęśliwi czasu nie liczą…ręcznie. Czytając dalej dowiesz się jak wyciągnąć z numeru PESEL datę urodzenia. Następnie na jej podstawie z łatwością policzysz wiek. O tym jak często trzeba taką umiejętność wykorzystywać, wie każdy HR-owiec. Ale nie tylko im przydadzą się opisane tutaj umiejętności.

Parę słów na temat numeru PESEL

Numer PESEL ma zakodowanych kilka informacji. Między innymi wiek – pierwsze 6 cyfr i płeć – 10 cyfra numeru. Jeżeli chodzi o wiek, zasada do roku 2000 była prosta:

• pierwsze dwie cyfry oznaczają rok (a konkretniej, dwie ostatnie cyfry roku),
• kolejne dwie – miesiąc 01-12,
• natomiast ostatnie – dzień 01-31

Od roku 2000 sprawa się skomplikowała. Dlatego, dla odróżnienia stuleci wprowadzono zasadę, że do miesiąca urodzenia będzie dodawana określona liczba w zależności od roku w jakim ktoś się urodził. I tak dla osób urodzonych w latach:

• 2000–2099 – dodajemy 20,
• 2100–2199 – dodamy 40,
• a dla 2200–2299 – dodamy 60.

Dla porządku – dla osób urodzonych w latach 1800–1899 dodaje się 80. Ale chyba nikt w podobnym wieku tego nie przeczyta 😉

Co to znaczy w praktyce? Jeżeli ktoś urodził się 9 czerwca 2011 roku, to pierwsze 6 cyfr jego numeru PESEL będzie wyglądało tak: 112609.

• 11 – bo rok 2011 (ostatnie dwie cyfry)
• 26 – bo do liczby miesiąca urodzenia 6, dodajemy 20 (dla osób urodzonych w latach 2000-2099)
• 09 – bo 9 dzień urodzenia.

Co będzie Ci potrzebne?

Aby policzyć wiek osoby na podstawie numeru PESEL, najpierw musisz „wyciągnąć” datę urodzenia. Następnie, na podstawie tej daty i np. daty aktualnej z łatwością policzysz wiek. Będziesz musiał połączyć w jedną formułę kilka funkcji:

1. JEŻELI – będzie Ci potrzebne do sterowania jakie funkcje mają być „uruchomione” (patrz punkt 6)
2. WARTOŚĆ – do zamiany tekstu na datę. Szczególnie, że PESEL powinien być wpisany w komórkach jako tekst (inaczej nie zapiszesz np. początkowego zera w wybranych przypadkach)
3. FRAGMENT.TEKSTU – bo musisz wydobyć z numeru PESEL określone fragmenty.
4. LEWY – do wyciągnięcia początkowego fragmentu (alternatywnie możesz cały czas stosować FRAGMENT.TEKSTU)
5. DATA – do zamiany fragmentów tekstu na datę
6. MOD – dzięki tej funkcji możesz zwrócić resztę z dzielenia. Wykorzystamy to do sprawdzenia czy masz do czynienia z PESEL-em osoby urodzonej przed lub po 1999 roku.

Data urodzenia z PESEL – do dzieła!

Zawsze, kiedy ostatecznie Twoja formuła osiąga finalnie taki wygląd:

=JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>12;DATA(„20″&LEWY(B8;2);MOD(FRAGMENT.TEKSTU(B8;3;2);20);FRAGMENT.TEKSTU(B8;5;2));DATA(„19″&LEWY(B8;2);FRAGMENT.TEKSTU(B8;3;2);FRAGMENT.TEKSTU(B8;5;2)))

musisz zacząć od pierwszego kroku. Zawsze! 😉

Stąd możesz pobrać plik początkowy, aby krok po kroku zrealizować ze mną zadanie. Na początek przyjmijmy założenie, że będziemy mieć do czynienia z PESEL-ami osób urodzonych w XX i XXI wieku (lata 19xx i 20xx) W komórce B8 pliku startowego mamy PESEL 84053112670. Do dzieła:

1. Datę możemy zbudować za pomocą funkcji DATA(rok; miesiąc; dzień). Dlatego z numeru PESEL musisz wyciągnąć…rok, miesiąc, dzień 😉
2. Rok to pierwsze dwie cyfry w PESEL-u. Użyjemy funkcji =LEWY(B8;2), w wyniku czego otrzymamy 84.
3. Miesiąc to cyfry 3 i 4. Użyjmy funkcji =FRAGMENT.TEKSTU(B8;3;2) i mamy 05.
4. Dzień to cyfry 5 i 6. Zatem =FRAGMENT.TEKSTU(B8;5;2) da nam 31.
5. Mamy już rok, miesiąc i dzień. Połączmy teraz wszystko w funkcji DATA:
   =DATA(„19″&LEWY(B8;2); FRAGMENT.TEKSTU(B8;3;2); FRAGMENT.TEKSTU(B8;5;2))
   Jak widzisz w pierwszym argumencie funkcji DATA połączyliśmy (&) tekst „19” z funkcją LEWY(B8;2). Robimy tak dlatego, że funkcja DATA w argumencie rok wymaga roku w formacie RRRR.

W wyniku otrzymujemy datę 1984-05-31 (pamiętaj, że komórka musi mieć format daty) Koniec? Nieeee.

A co z osobami urodzonymi po 1999 roku?

Wszystko pięknie, jeżeli będziesz tym sposobem wyciągać datę z PESEL-u osoby urodzonej do roku 1999 włącznie. Ale to nie wystarczy do obliczenia daty osób urodzonych po 1999. Jak pamiętasz, dla tych osób do liczby oznaczającej miesiąc dodawana jest liczba 20.

Dlatego, musimy znaleźć sposób na rozróżnienie osób urodzonych przed i po 1999 roku, aby w każdym przypadku zastosować nieco inną formułę. Jeżeli liczba oznaczająca miesiąc będzie większa niż 12, to mamy do czynienia z osobą urodzoną po 1999 roku (do liczby oznaczającej miesiąc dodano 20) Kolejne kroki to:

1. Sprawdzamy, czy PESEL dotyczy osoby urodzonej po 1999 roku: =JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>12;… – wprowadziliśmy funkcję WARTOŚĆ, żeby zamienić tekst na wartość i móc ją porównać z 12.
2. Jeżeli wartość jest większa niż 12 (osoba urodzona po 1999 roku) to zastosujemy taką formułę: DATA(„20″&LEWY(B8;2);MOD(FRAGMENT.TEKSTU(B8;3;2);20);
3. Skorzystaliśmy z funkcji MOD, która zwraca resztę z dzielenia. Dzięki temu możemy wyciągnąć faktyczną liczbę oznaczającą miesiąc urodzenia.
4. Dla osób urodzonych przed i w roku 1999 zastosujemy wcześniej opisany sposób: DATA(„19″&LEWY(B8;2);FRAGMENT.TEKSTU(B8;3;2);FRAGMENT.TEKSTU(B8;5;2)

Całość będzie miała postać jak niżej:

=JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>12;DATA(„20″&LEWY(B8;2);MOD(FRAGMENT.TEKSTU(B8;3;2);20);FRAGMENT.TEKSTU(B8;5;2));DATA(„19″&LEWY(B8;2);FRAGMENT.TEKSTU(B8;3;2);FRAGMENT.TEKSTU(B8;5;2)))

Taka formuła pozwoli wyciągnąć datę urodzenia, niezależnie od tego czy liczymy ją z PESEL-u osoby urodzonej przed, czy po 1999 roku. Końcowy efekt możesz także sprawdzić w pliku.

Oblicz wiek

Wiek w latach obliczysz korzystając z funkcji DATA.RÓŻNICA. Datę urodzenia mamy w komórce C8. Policzymy różnicę w latach w stosunku do aktualnej daty – funkcja DZIŚ(). Zapiszemy:

=DATA.RÓŻNICA(C8;DZIŚ();”Y”)

Parametr „Y” daje różnicę w latach, „M” w miesiącach, „D” w dniach. Gotowe. Mamy wiek z PESEL-u! Oczywiście możesz połączyć wszystkie funkcje w jedną jeszcze bardziej skomplikowaną formułę, która da dokładnie ten sam efekt:

=DATA.RÓŻNICA(JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>12;DATA(„20″&LEWY(B8;2);MOD(FRAGMENT.TEKSTU(B8;3;2);20);FRAGMENT.TEKSTU(B8;5;2));DATA(„19″&LEWY(B8;2);FRAGMENT.TEKSTU(B8;3;2);FRAGMENT.TEKSTU(B8;5;2)));DZIŚ();”Y”)

Podsumowanie

Jak widzisz im więcej warunków, tym bardziej złożona formuła. Ale zawsze jeżeli rozbijesz zadanie na części pierwsze – całość stanie się dużo łatwiejsza. Każda finalnie złożona reguła zaczyna się od środka. Znajdując rozwiązanie każdego problemu po kolei – ostatecznie dochodzisz do finalnego rozwiązania.

Całe zadanie zamiany numeru PESEL w wiek (oraz płeć) można też zrealizować funkcją w VBA. Może wspólnie zmierzymy się z tematem w jednym z kolejnych artykułów? Daj proszę znać czy byłoby to dla Ciebie interesujące.