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 pokażemy jak zrobić podsumowanie danych na wykresie po wybranych grupach. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
W tej poradzie omówimy rozwiązanie prostsze dla podsumowania danych po Sprzedaży i premii dla poszczególnych grup w całości. Otrzymamy wykres przedstawiony na rys. nr 2.
Rys. nr 2 – wykres podsumowania Sprzedaży i Premii
W kolejnym poście omówimy rozwiązanie bardziej skomplikowane, a wykres będzie się prezentował jak na rys. nr 3. Na tym wykresie będzie widoczny dodatkowy podział na poszczególnych pracowników.
Rys. nr 3 – wykres podsumowania Sprzedaży i Premii z podziałem na pracowników
W pierwszym
etapie musimy sobie podsumować (pogrupować) nasze dane według grup A, B i C. W
tym celu stworzymy osobną tabelkę z danymi pomocniczymi. Skorzystamy tutaj z
funkcji SUMA.JEŻELI. Pierwszym argumentem funkcji jest zakres,
czyli zakres z kolumny B zablokowany bezwzględnie, na podstawie którego
będziemy sprawdzać nasze kryterium – nazwę zespołu ($B$2:$B$11). Zakres
blokujemy bezwzględnie za pomocą klawisza F4. Kolejnym argumentem funkcji są kryteria,
czyli dział, dla którego chcemy uzyskać sumę (komórka $F2, zablokowana bezwzględnie
po kolumnie). Trzeci argument to suma_zakres, czyli kolumna z której
chcemy zsumować dane. Tę formułę piszemy dla wartości Sprzedaży, więc odpowiednio
musimy w tym argumencie zaznaczyć zakres z kolumny C (Sprzedaż). Chcemy napisać
formułę, którą będziemy mogli przeciągnąć na wiersze poniżej ale również na
kolumnę obok (dla Premii), dlatego zakres ten blokujemy bezwzględnie tylko po
wierszach. Zakres ten będzie wyglądał następująco: C$2:C$11. Cała formuła powinna
być zapisana w następujący sposób:
=SUMA.JEŻELI($B$2:$B$11;$F2;C$2:C$11)
Po
zatwierdzeniu formuły, kopiujemy ją na wiersze poniżej. Otrzymamy wtedy
podsumowanie Sprzedaży po poszczególnych działach. Następnie kopiujemy formułę
na kolumnę obok. Otrzymamy wtedy podsumowanie Premii po poszczególnych działach
(rys. nr 4). Zapis formuły automatycznie zmieni się tak, aby uwzględniał dane z
kolumny Premia (zakres D$2:D$11), będzie się on prezentował następująco:
=SUMA.JEŻELI($B$2:$B$11;$F2;D$2:D$11)
Rys. nr 4 – tabelka zsumowanych danych Sprzedaży i Premii dla poszczególnych grup
W tym momencie pozostaje nam przedstawić te dane na wykresie. W tym celu zaznaczamy zakres całej tabeli (F1:H4) i rozwijamy ikonkę wykresu kolumnowego z karty Wstawianie, a następnie wybieramy pierwszą ikonkę wykresu z grupy Kolumnowy 2D (rys. nr 5).
Rys. nr 5 – polecenie wykresu kolumnowy 2D
Otrzymamy automatycznie stworzone wykres przedstawiony na rys. nr 6.
Rys. nr 6 – automatycznie wstawiony wykres kolumnowy
Teraz zajmiemy się edycją tego wykresu. Musimy zmienić jego tytuł, aby było jasne, czego dotyczy. Klikamy dwukrotnie na pole z Tytułem wykresu i wpisujemy nowy tytuł (Podsumowania Sprzedaży i Premii dla zespołów). Następnie możemy dopracować nasze kolumny na wykresie. Zaznaczamy taką kolumnę na wykresie, następnie za pomocą skrótu klawiszowego Ctrl+1, otwieramy okno Formatowania serii danych. Załóżmy, że chcemy aby Kolumny Sprzedaży stykała się z kolumną Premii. W tym celu w oknie Formatowania serii danych pasek Nakładania serii ustawiamy na 0% (rys. nr 7).
Rys. nr 7 – ustawienie Nakładania serii
Następnie możemy ustawić Szerokość odstępu, aby poszerzyć nasze kolumny. W naszym przykładzie ustawimy szerokość odstępu na 94% (rys. nr 8).Rys. nr 8 – ustawienie Szerokości odstępuNastępnie możemy ustawić Szerokość odstępu, aby poszerzyć nasze kolumny. W naszym przykładzie ustawimy szerokość odstępu na 94% (rys. nr 8).
Rys. nr 8 – ustawienie Szerokości odstępu
Po powyższych zmianach otrzymamy wykres przedstawiony na rys. nr 9.
Rys. nr 9 – wykres Podsumowania Sprzedaży i Premii dla zespołów
Podsumowując, jeśli mamy zrobić wykres podsumowania Sprzedaży i Premii jako całość dla poszczególnych grup, to wystarczy stworzyć tabelę pomocniczą z sumą tych danych, uzyskanych przy użyciu funkcji SUMA.JEŻELI.
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.
W
dzisiejszym poście nauczymy się jak numerować przedmioty wewnątrz grupy
produktów, jak wstawić odpowiedni indeks. Zadanie to omówimy na podstawie
przykładowych danych z rysunku nr 1.
Rys. nr 1 – przykładowe dane
Przykładowo
mamy różne kategorie produktów takie jak myszki, klawiatury, monitory, naszym
celem jest ponumerowanie przedmiotów w danej kategorii, czyli nadanie numeru
porządkowego. Na przykład Mysz Mini to będzie L.p. 1, Klawiatura Dark to L.p.
1, a mysz bezprzewodowa L.p. 2 itd. Ręcznie wpisaną numerację pokazano na rys.
nr 2.
Rys. nr 2 – ręczne numerowanie produktów w danej kategorii
W Excelu
rozwiązanie takiego zadania jest bardzo proste. Wystarczy skorzystać tutaj z
funkcji LICZ.JEŻELI. Najważniejsze dla tej funkcji jest odpowiednie
zbudowanie Zakresu, czyli pierwszego argumentu funkcji. Zakres ten musi
się dynamicznie rozrastać jak będziemy naszą formułę przeciągać w dół – na
wiersze poniżej. Zakres dla pierwszego wiersza powinien wyglądać następująco
C2:C2. Drugi argument funkcji to Kryteria, czyli będziemy tutaj
sprawdzać czy w zakresie określonym w pierwszym argumencie znajduje się wartość
ze sprawdzanej w tym momencie komórki, czyli C2. Zanim zatwierdzimy formułę
musimy dobrze określić argument zakres, czyli zablokować pierwszą komórkę
(pierwszą część zakresu), aby uzyskać zakres rozrastający się. Pierwszą komórkę
C2 blokujemy bezwzględnie za pomocą klawisza F4. Druga część zakresu powinna
pozostać bez zmian, dzięki temu będzie się przesuwać w dół. Zapis funkcji
powinien wyglądać następująco:
=LICZ.JEŻELI
($C$2:C2;C2)
Powyższą
funkcje zatwierdzamy przyciskiem Enter i kopiujemy na wiersze poniżej.
Otrzymamy dane przedstawione na rys. nr 3.
Rys. nr 3 – ponumerowane produkty z poszczególnych kategorii
Za pomocą
klawisza F2 możemy podejrzeć jak wygląda formuła dla kolejnego wiersza. Zapis
funkcji wygląda następująco:
=LICZ.JEŻELI
($C$2:C3;C3)
Podsumowując
z każdym kolejnym wierszem zakres się powiększa o kolejną komórkę (rys. nr 4).
Rys. nr 4 – formuła funkcji LICZ.JEŻELI dla komórki C3
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.
Kiedy korzystasz z funkcji SUMA masz bardzo dużo możliwości. Jeśli chcesz dodawać proste wartości nie potrzeba nawet korzystać z funkcji SUMA. Wystarczy, że korzystasz ze znaku plus. Jednak funkcja SUMA ma dużą przewagę nad zwykłym dodawaniem. Przede wszystkim łatwiej możesz w niej korzystać z zakresów.
Sumowanie po zakresach ma wiele zalet w porównaniu z sumowaniem pojedynczych komórek.
Zobacz jakie są różnice w dodawaniu pojedynczych komórek i sumowaniu całych zakresów — zobacz wideo poniżej. Dodawanie po zakresach oszczędzi Ci wiele Twojego cennego czasu podczas pracy w Excelu.
