W tym poście omówimy zamianę zakresów na ich nazwę.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
Chodzi o to, że zazwyczaj w formułach mamy odwołania do zakresów. Takie zakresy możemy nazwać, że podglądając formułę od razu wiedzieć z jakich zakresów korzysta dana formuła, np. zakres C2:C197 możemy nazwać Sprzedaż.
Zaznaczmy kolumny Sprzedaż i Koszty, następnie żeby przejść na koniec tabeli musimy użyć skrótu klawiszowego Ctrl+Shift+Strzałka w dół. Po zaznaczeniu danych wybieramy polecenie Utwórz z zaznaczenia z karty Formuły (rys. nr 2).
Rys. nr 2 – polecenie Utwórz z zaznaczenia
Otworzy nam się okienko Tworzenia nazw z zaznaczenia, gdzie zaznaczamy checkbox przy opcji Górny wiersz (nazwy zakresów zostaną utworzone z nazwy w górnym wierszu, czyli nagłówka kolumn). Zatwierdzamy przyciskiem OK (rys. nr 3).
Rys. nr 3 – okno Tworzenia nazw z zaznaczenia
Teraz z w polu Nazwy możemy zobaczyć dwa nazwane zakresy – Koszty i Sprzedaż (rys. nr 4).
Rys. nr 4 – dwa nazwane zakresy
W tabeli z Sumą i Średnią sprzedaży i kosztów mamy formuły z zakresami:
=SUMA($C$2:$C$197)
=SUMA($D$2:$D$197)
W tej sytuacji pracujemy z na tym samym arkuszu, w którym znajdują się zakresy z formuł. Dlatego sprawa jest oczywista, do czego się one odnoszą. Może być jednak sytuacja, że formuły będą w innym arkuszu niż zakresy, do których się odwołują. W takiej sytuacji lepiej wykorzystać nazwy zakresów. Wtedy łatwiej się domyślić, czego dana formuła dotyczy.
Nasze zadanie będzie polegać na zastąpieniu w formułach zakresach odpowiednimi nazwami. Excel daje nam taką możliwość. Wystarczy zaznaczyć zakres z formułami, w których chcemy podmienić zakresy na nazwy, następnie rozwinąć polecenie Definiuj nazwy (punkt 2 na rys. nr 5) z karty Formuły (punkt 1) i wybrać polecenie Zastosuj nazwy (punkt 3).
Rys. nr 5 – polecenie Zastosuj nazwy
Otworzy nam się okienko Stosowania nazw, gdzie w polu Zastosuj nazwy (punkt 1 na rys. nr 6) muszą być zaznaczone obie nazwy (chcemy podmienić dwa zakresy jednocześnie). Musimy pamiętać, aby był zaznaczony checkbox przy Ignoruj względne/bezwzględne (punkt 2), chodzi tutaj o znaki $ przy zakresach. Następnie odznaczamy checkbox przy Używaj nazw wierszy i kolumn (punkt 3). Tak ustawione parametry zatwierdzamy przyciskiem OK.
Rys. nr 6 – okno Stosowania nazw
Otrzymamy formuły z podmienionymi zakresami na nazwy. Przykładowe formuły zostały przedstawione poniżej
=SUMA(Sprzedaż)
=SUMA(Koszty)
Analogicznie zmieniły się formuły dla wartości Średniej z kosztów i sprzedaży, czy zakresy odpowiednio $C$2:$C$197 i $D$2:$D$197 zostały zamienione na Sprzedaż i Koszty.
Podsumowując, dla nas najważniejsze jest podstawienie nazw pod zakresy. Dzięki temu możemy łatwiej domyślić się, czego dotyczy dana formuła, szczególnie w sytuacji, kiedy formuła i dane, do których się odwołuje są w innych arkuszach Excela.
W tym poście zajmiemy się nazywaniem zakresów komórek ręcznie i z zaznaczenia. Temat ten omówimy przy użyciu danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Jeśli chcemy nazwać jakiś zakres danych, wystarczy zaznaczyć je zaznaczyć i kliknąć w pole Nazwa. Wpisujemy tam naszą nazwę (u nas Sprzedaż) i zatwierdzamy wciskając ENTER (rys. nr 2)
rys. nr 2 — Pole Nazwa
Teraz możemy się odwoływać do tego zakresu danych, bo jest ona dostępna po rozwinięciu listy pola Nazwa (rys. nr 3)
rys. nr 3 — Rozwinięcie pola Nazwa
Do naszego zakresu możemy się odwołać w każdej komórce. Wpisując formułę, jak zaczniemy pisać Sprzedaż, to oprócz nazw formuł pojawi nam się ikonka odwołująca się do naszego zaznaczonego zakresu (rys. nr 4)
rys. nr 4 — Odwołanie do nazwy zakresu
Możemy również odwołać się do naszego nazwanego zakresu wpisując funkcje w Excelu. Wystarczy, że napiszemy np. =SUMA(…) i jako zakres wpiszemy Sprzedaż, to nie będziemy musieli zaznaczać danych, które chcemy dodać (rys. nr 5)
rys. nr 5 — Odwołanie do nazwy zakresu w formule SUMA
W efekcie otrzymamy sumę wszystkich wartości w obrębie
zakresu Sprzedaż (rys. nr 6)
rys. nr 6 — Suma danych z zapisanego zakresu
Oprócz tego, że możemy sobie nazywać dowolne, spójne zakresy w Excelu to możemy to zrobić niejako automatycznie z zaznaczenia. Zaznaczamy dane Przychód, Koszt i Dochód, następnie z karty Formuła wybieramy polecenie Utwórz z zaznaczenia lub możemy użyć skrótu klawiszowego Ctrl + Shift + F3 (rys. nr 7
rys. nr 7 — Polecenie Utwórz z zaznaczenia
Excel otworzy nam okno Tworzenie nazw z zaznaczenia.
Wybieramy, gdzie znajdują się wartości, na podstawie których chcemy utworzyć
nazwy zakresów. W naszym przypadku nazwy są zawarte w kolumnie po lewej stronie
(rys. nr 8), następnie zatwierdzamy przyciskiem OK.
rys. nr 8 — Tworzenie nazw z zaznaczenia
W efekcie w polu Nazwa pojawiły nam się nasze nazwy z kolumny
A (rys. nr 9)
rys. nr 9 — Nazwane zakresy w polu Nazwa
Te czynności możemy powtórzyć dla zakresu, któremu nadaliśmy nazwę Sprzedaż. Tym razem użyjemy polecenia Utwórz z zaznaczenia z karty Formuły lub skrótu klawiszowego Ctrl + Shift + F3. Otworzy nam się okno, w którym zaznaczymy Górny wiersz i Lewą kolumnę, następnie zatwierdzamy klikając przycisk OK (rys. nr 10)
rys. nr 10 — Tworzenie nazw z zaznaczenia
W polu Nazwa pojawią nam się kolejne zakresy o nazwach
Styczeń, Luty, Marzec i Ryszard, Edward, Natalia i Maria (rys. nr 11)
rys. nr 11 — Nazwane zakresy w polu Nazwa
Nazwy naszych zakresów możemy również zobaczyć, klikając
polecenie Menedżer nazw z zakładki Formuły. W oknie Menedżera nazw Wyświetli
nam się lista wszystkich nazwanych zakresów (rys. nr 12)
rys. nr 12 — Menedżer nazw
Dzięki nazywaniu zakresów przy pisaniu formuł nie musimy
zaznaczać zakresów do obliczeń, wystarczy, że podamy ich nazwy. Istotne w
działaniu Nazywania zakresów z zaznaczenia jest to, że nawet gdy klikniemy na
konkretną komórkę lub zakres przy pisaniu formuły, to Excel wstawi nam nazwę tej
komórki lub zakresu, a nie jej numer (rys. nr 13)
rys. nr 13 — Odwołania do zapisanych nazw zakresów
Jeśli w formule nie chcemy korzystać z nazw zakresów, to jedynym wyjściem jest ręczne wpisanie numeru komórki lub zakresu (rys. nr 14)
rys. nr 14 — Ręczne wpisanie nazw komórek
Definiowanie nazw ma ciekawą opcję. Z karty Formuły wybieramy polecenie Definiuj nazwę, a następnie Zastosuj nazwy (rys. nr 15)
rys. nr 15 — Zastosuj nazwy
Otworzy nam się okno Stosowanie nazw (rys. nr 16). Zaznaczamy
wszystkie zakresy, następnie zatwierdzamy, klikając przycisk OK. Należy zwrócić
szczególną uwagę, czy mamy zaznaczoną opcję Ignoruj względne/ bezwzględne. Zakresy, którym nadaliśmy nazwę stały się
domyślnie odwołaniami bezwzględnymi, a czasem zdarza się, że my potrzebujemy
odwołań względnych.
rys. nr 16 — Ignoruj względne/bezwzględne
W komórce Dochód — B3 mieliśmy zapisaną formułę =B1-B2 dzięki zaznaczeniu w oknie Stosowanie nazw, wszystkich zakresów które nazwaliśmy, Excel automatycznie podmieni nam numery komórek na odpowiadające im nazwy zakresów (rys. nr 17)
rys. nr 17 — Automatyczna podmiana nazw zakresów
Podsumowując, jeśli chcemy szybko podmienić numery komórek w
naszych formułach na nazwy zakresów, do których się odnoszą wystarczy
zastosować polecenie Zastosuj nazwy z karty Formuły. Czasem formuły zapisane w
postaci numerków sprawiają wrażenie skomplikowanych, a stosując zapis z nazwami
zakresów stają się bardziej przejrzyste.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
