Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.
Excel - kurs online. Dlaczego warto?
Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.
Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.
Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.
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 kontynuujemy rozwiązywanie zadań z Matury z informatyki
2018, zajmiemy się rozwiązaniem zadania nr 5.3 w Power Query. Treść zadania
została przedstawiona na rys. nr 1.
rys. nr 1 — treść zadania 5.3 z informatyki z Matury 2018
Zadanie
polega na stworzeniu zostawienia wpływów wody do zbiornika retencyjnego w
kolejnych miesiącach roku 2008. Zaczynamy od włączenia sobie okna Zapytania
i połączenia, aby mieć dostęp do wcześniej wczytanych danych (które
załadowaliśmy sobie do rozwiązania poprzednich zadań). Rys. nr 2.
rys. nr 2 — polecenie Zapytania i połączenia
Klikamy na
nazwę zapytania Woda, gdzie mamy wczytane dane z pliku tekstowego zawierającego
ilości dopływającej wody do zbiornika retencyjnego. Otworzy nam się Edytor
zapytań Power Query z wczytanymi danymi, przedstawionymi na rys. nr 3.
rys. nr 3 — edytor zapytań z wczytanymi danymi
Po raz
kolejny będziemy się odwoływać do danych z tego zapytania, czyli klikamy prawym
przyciskiem myszy na nazwę zapytania Woda a następnie z podręcznego menu
wybieramy polecenie Odwołanie (rys. nr 4).
rys. nr 4 — ścieżka dostępu do polecenia Odwołanie
Będziemy
pracować na zapytaniu woda(2). Interesuje nas konkretny rok (2008) i podział na
miesiące. W pierwszej kolejności wyciągniemy numer miesiąca z daty, czyli rozwijamy
polecenie Data (punkt 2 na rys. nr 5) z karty Dodaj kolumnę
(punkt 1), następnie rozwijamy polecenie Miesiąc (punkt 3) i wybieramy
polecenie Miesiąc (punkt 4).
rys. nr 5 — ścieżka dostępu do polecenia Miesiąc
Otrzymamy
dane z nową kolumną zawierającą numer miesiąca w roku przedstawione na rys. nr 6.
rys. nr 6 — Tabela z nową kolumną z numerem miesiąca
W kolejnym
etapie interesuje nas rok – potrzebujemy danych tylko z roku 2008. W tym celu
rozwijamy polecenie Data (punkt 2 na rys. nr 7) z karty Przekształć
(punkt 1), a następnie rozwijamy polecenie Rok (punkt 3) i wybieramy
polecenie Rok (punkt 4).
rys. nr 7 — ścieżka dostępu do polecenia Rok
Otrzymamy
przekształcone dane w kolumnie Data przedstawione na rys. nr 8.
rys. nr 8 — Dane z przekształconą kolumną Data
Na tym
etapie możemy przefiltrować dane w kolumnie Data. Klikamy ikonkę z trójkącikiem
przy nazwie kolumny Data, a następnie zaznaczamy checkbox przy roku 2008. Tak
ustawiony parametr filtru zatwierdzamy przyciskiem OK (rys. nr 9).
rys. nr 9 — filtrowanie danych w kolumnie Data
Otrzymamy
dane zawierające tylko rok 2008. Możemy ponadto zmienić kolejność kolumn, czyli
przeciągnąć nową kolumnę o nazwie Miesiąc w miejsce za kolumną Data. Otrzymamy
uporządkowane dane przedstawione na rys. nr 10.
rys. nr 10 — uporządkowane dane
Ponadto zmieniamy
nazwę kolumny Data na Rok. Kolejny etap będzie polegał na pogrupowaniu danych
miesiącami. Dodatkowo potrzebujemy wiersza z podsumowaniem, co w Power Query
jest trudniejsze niż standardowo w Excelu. Aby ułatwić sobie zadanie
zduplikujemy to, co do tej pory zrobiliśmy, czyli klikamy prawym przyciskiem
myszy na nazwę zapytania woda(2) i z podręcznego menu wybieramy polecenie Duplikuj
(rys. nr 11).
rys. nr 11 — polecenie Duplikuj
Otrzymamy
kolejne zapytanie o nazwie woda(3). W zapytaniu woda (2) zrobimy grupowanie po kolumnach
Rok i Miesiąc. W tym celu zaznaczamy kolumny Rok i Miesiąc przytrzymując
klawisz Ctrl, następnie wybieramy polecenie Grupowanie według z karty Narzędzia
główne (rys. nr 12).
rys. nr 12 — ścieżka dostępu do polecenia Grupowanie według
Otworzy nam
się okno grupowania według, gdzie dzięki temu że zaznaczyliśmy dwie kolumny
mamy od razu grupowanie zaawansowane. W polu Operacja wybieramy polecenie Suma,
w polu Kolumna wybieramy Woda m3, natomiast w polu Nazwa nowej kolumny
wpisujemy Przypływy. Tak ustawione parametry grupowania zatwierdzamy
przyciskiem OK (rys. nr 13).
rys. nr 13 — parametry Grupowania danych
Otrzymamy
pogrupowane (zsumowane) dane według miesięcy przedstawione na rys. nr 14.
Tabela zawiera w kolumnie Przypływy ilość wody jaka wpływa do zbiornika
retencyjnego w poszczególnych miesiącach roku 2008.
rys. nr 14 — Pogrupowane dane w kolumnie Przypływy
Analogiczne
działanie wykonamy w zapytaniu woda(3), ale tutaj zrobimy podsumowanie po całym
roku. Zaznaczamy tylko kolumnę Rok, następnie wybieramy polecenie Grupowanie
według z karty Narzędzia główne (jak na rys. nr 12).
Otworzy nam
się okno Grupowania według z typem grupowania podstawowym. W polu Operacja
wybieramy Sumę, w polu Kolumna wybieramy kolumnę Woda m3, natomiast w polu
Nazwa Nowej kolumny wpisujemy Przypływy. Tak ustawione parametry grupowania
zatwierdzamy przyciskiem OK (rys. nr 15).
rys. nr 15 — parametry Grupowania według
W zapytaniu
woda(3) otrzymamy podsumowane dane po cały roku 2008 przedstawione na rys. nr 16.
rys. nr 16 — dane pogrupowane według roku (zsumowane)
Teraz możemy
sobie dołączyć zapytania, w tym celu wybieramy polecenie Dołącz zapytania
z karty Narzędzia główne (rys. nr 17).
rys. nr 17 — ścieżka dostępu do polecenia Dołącz zapytania
Do zapytania
z podsumowaniem po roku i miesiącach (woda(2)) dołączamy zapytanie z
podsumowaniem po roku (woda(3)). Otworzy nam się okno Dołączanie, gdzie w polu
Tabela do dołączenia wybieramy zapytanie woda(3). Nasz wybór zatwierdzamy
przyciskiem OK (rys. nr 18).
rys. nr 18 — okno dołączania zapytań
Dzięki temu,
że w obu zapytaniach nazwaliśmy tak samo kolumnę z podsumowaniem (Przypływy) to
Power Query automatycznie połączył te kolumny w jedną. Otrzymamy połączone
zapytania przedstawione na rys. nr 19.
rys. nr 19 — połączone dane z dwóch zapytań
W kolumnie
Miesiąc dla podsumowania po roku otrzymaliśmy wartość null, ponieważ tej
kolumny nie było w naszym podsumowaniu w zapytaniu woda (3).
Dobrze
byłoby zamiast wartości null wpisać np. wartość tekstową suma. W Power Query
pojawia się problem, ponieważ dane w kolumnie Miesiąc zostały określone jako
typ liczbowy i kiedy podejmiemy próbę wpisania tekstu to Power Query będzie
zgłaszał sprzeciw. W cely wyjaśnienia problemu wybieramy polecenie Zamienianie wartości z karty Narzędzia główne (rys. nr 20).
rys. nr 20 — ścieżka dostępu do polecenia Zamienianie wartości
Otworzy nam
się okno Zamieniania wartości, gdzie w polu Wartość do znalezienia wpisujemy
null, natomiast w polu Zamień na wpisujemy Suma. Jeśli spróbujemy zatwierdzić
tę zmianę przyciskiem OK, Power Query zaprotestuje, co widać na rys. nr 21.
rys. nr 21 — parametry zamieniania wartości
Musimy
anulować okno Zamieniania wartości. Poradzić sobie tutaj możemy zmieniając typ
danych w kolumnie Miesiąc, czyli klikamy na ikonkę 123 przy nazwie kolumny
Miesiąc i wybieramy typ danych Tekst (rys. nr 22).
rys. nr 22 — zmiana typu danych na tekst
Po takiej
zmianie po raz kolejny wybieramy polecenie Zamienianie wartości z karty Narzędzia główne (jak na rys.
nr 20). Otworzy nam się okno Zamieniania wartości, gdzie już bez problemu
możemy wpisać parametry jak na rys. nr 21 i zatwierdzić je przyciskiem OK.
Otrzymamy dane przedstawione na rys. nr 23.
rys. nr 23 — zamienione wartości w danych
Tak
przygotowane dane możemy załadować do Excela, w tym celu wybieramy polecenie Zamknij
i załaduj do z karty Narzędzia główne (rys. nr 24).
rys. nr 24 — polecenie Zamknij i załaduj do
Otworzy nam
się okno Importowania danych w Excelu. Ponieważ mamy dwa zapytania nie możemy
ich od razu wstawić do Istniejącego arkusza, dlatego wybieramy opcję Utwórz
tylko połączenie i zatwierdzamy nasz wybór przyciskiem OK (rys. nr 25).
rys. nr 25 — okno Importowania danych
W panelu
bocznym Zapytania i połączenia mamy wszystkie zapytania wczytane jako
połączenie. Aby wiedzieć, które zapytanie jest tym właściwym klikamy na
zapytanie woda (2) i przechodzimy do edytora zapytań Power Query, gdzie
zmieniamy jego nazwę w polu Nazwa na Woda 5_3. Następnie kolejny raz musimy
załadować dane do Excela za pomocą polecenia Zamknij i załaduj z karty Narzędzia
główne. Zapytanie załaduje się jako połączenie. Na tym etapie z okienka Zapytań
i połączeń możemy załadować to konkretne zapytanie klikając na mnie prawym
przyciskiem myszy a następnie wybrać polecenie Załaduj do (rys. nr 26).
rys. nr 26 — polecenie Załaduj do
Otworzy nam
się po raz kolejny okno Importowania danych, gdzie tym razem możemy wybrać sposób
wyświetlania danych w skoroszycie jako Tabela oraz wskazać konkretną komórkę w
istniejącym arkuszu. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys.
nr 27).
rys. nr 27 — okno Importowania danych
Otrzymamy
dane wczytane do Excela przedstawione na rys. nr 28.
rys. nr 28 — Dane wczytane do Excela
Dla lepszej
prezentacji danych możemy zmienić formatowanie w kolumnie Przypływy. Korzystając
ze skrótu klawiszowego Ctrl+1 otwieramy okno Formatowania komórek. Przechodzimy
na Kategorię Liczbowe, a następnie ustawiamy 0 miejsc po przecinku i zaznaczamy
checkbox przy opcji Użyj separatora. Tak ustawione parametry formatowania
zatwierdzamy przyciskiem OK (rys. nr 29).
rys. nr 29 — Formatowanie komórek
Kolejnym
etapem zadania jest stworzenie wykresu przypływów wody do zbiornika
retencyjnego. Zaznaczamy dane z kolumny Przypływy, ale bez komórki
podsumowującej dla całego roku a następnie wybieramy wykres kolumnowy z
karty Wstawianie (rys. nr 30).
rys. nr 30 — ścieżka dostępu do polecenia wykres kolumnowy
Pierwszą
rzeczą jaką poprawimy na standardowym wykresie, będzie pogrubienie kolumn.
Zaznaczamy serie na wykresie, a następnie korzystając ze skrótu klawiszowego
Ctrl+1, otworzymy panel Formatowania serii danych, gdzie możemy zmienić
szerokość odstępu między seriami (rys. nr 31).
rys. nr 31 — Formatowanie serii danych
W kolejnym
etapie musimy zmienić tytuł wykresu, czyli klikamy dwukrotnie na napis Tytuł
wykresu i zmieniamy jego nazwę na Przypkywy Wirki w roku 2008 [m3].
Aby w Excelu uzyskać indeks górny musimy napisać normalną 3, następnie ją
zaznaczyć i korzystając ze skrótu klawiszowego Ctrl+1, otworzyć okno Czcionki,
gdzie zaznaczmy check box przy opcji Indeks górny (rys. nr 32).
rys. nr 32 — indeks górny w oknie czcionka
Ponadto
potrzebujemy na wykresie zamieścić informacje przy osiach (co przedstawiają
dane osie). Od Excela 2013 klikamy na ikonę + w rogu wykresu i zaznaczamy
checkbox przy opcji Tytuły osi (rys. nr 33).
rys. nr 33 — Elementy wykresu (tytuły osi)
Tytuły osi
wstawiają się jako domyślne, czyli musimy je sami odpowiednio zmienić. Klikamy
dwukrotnie na tytuł osi i wpisujemy dla osi pionowej Woda m3,
natomiast dla osi poziomej Nr miesiąca. Otrzymamy poprawny wykres przedstawiony
na rys. nr 34
rys. nr 34 — poprawiony wykres Przypływów do zbiornika retencyjnego
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.
Kontynuujemy rozwiązywanie zadań z matury 2018 z informatyki
dotyczącej zbiornika retencyjnego i przepływów na rzece Wirka. W dzisiejszym
poście omówimy rozwiązanie zadania nr 5.3 (rys. nr 1)
rys. nr 1 — Treść zadania z matury 2018
Naszym zadaniem jest stworzenie zestawienia przepływów
miesiąc po miesiącu w roku 2008 a następnie wykresu kolumnowego na podstawie
tego zostawienia. Zakładamy, że już pobraliśmy sobie dane do tego zadania i
stworzyliśmy z nich tabelę. Na ich podstawie musimy stworzyć sobie tabelę
przestawną, bo za jej pomocą najłatwiej jest zrobić podsumowanie.
Zaznaczamy pojedynczą komórkę w tabeli z danymi, a następnie
z karty Wstawianie wybieramy polecenie Tabela przestawna (rys. nr 2).
rys. nr 2 — Tabela przestawna
W oknie Tworzenie tabeli przestawnej wybieramy komórkę w
której chcemy wstawić tabelę przestawną i zaznaczamy, że chcemy ją wstawić w
istniejącym arkuszu. Następnie przeciągamy pole Data do obszaru etykiet
wierszy. Excel sam domyślnie nie grupuje nam danych, więc klikamy prawym
przyciskiem myszy na dowolną datę w tabeli przestawnej i wybieramy polecenie
Grupuj (rys. nr 3).
rys. nr 3 — Grupuj
Otworzy nam się okno Grupowanie. Chcemy pogrupować nasze dane
po miesiącach i po datach, zatwierdzamy klikając przycisk OK (rys. nr 4).
rys. nr 4 — Okno Grupowanie
Otrzymaliśmy układ tabelaryczny, gdzie Excel po grupowaniu
stworzył 3 rodzaje dat (rys. nr 5)
rys. nr 5 — pola tabeli przestawnej
My nie potrzebujemy zwykłej daty, czyli dni więc musimy
odznaczyć checkbox zaznaczony strzałką na rysunku powyżej. Otrzymamy daty
przedstawione na rysunku nr 6.
rys. nr 6 — Pogrupowane daty
Nas interesuje jeden konkretny rok (2008), więc musimy
przefiltrować nasze dane. Rozwijamy menu przy Lata (Data) i odznaczamy
wszystkie lata a następnie zaznaczamy tylko 2008 i zatwierdzamy przyciskiem OK
(rys. nr 7)
rys. nr 7 — Filtrowanie daty
W karcie Projektowanie wybieramy polecenie Układ raportu, a
następnie Pokaż w formie kompaktowej
rys. nr 8 — Układ raportu
Otrzymamy tabelę przestawną przedstawioną na rysunku nr 9.
rys. nr 9 — Tabela przestawna dla roku 2008
Teraz
kiedy mamy pogrupowane dane i przefiltrowane, aby otrzymać tylko rok 2008,
możemy Pole Woda m3 przeciągnąć do obszaru podsumowań wartości. Otrzymamy dane
pogrupowane miesiącami w roku 2008 z zsumowanymi przepływami w danych
miesiącach. Nie chcemy mieć podanych sum wartości końcowych i częściowych więc
w karcie Projektowanie w Sumach częściowych wybieramy polecenie Nie pokazuj sum
częściowych i w Sumach końcowych opcję Wyłącz dla wierszy i kolumn (rys. nr 10).
rys. nr 10 — Tabela przestawna z podsumowanymi wartościami przepływów
Mamy podsumowane dane więc możemy się zając tworzeniem
wykresu. Zanim jednak to nastąpi powinniśmy zmienić sobie formatowanie liczb, klikamy
na dowolną komórkę z liczbą prawym przyciskiem myszy i z podręcznego menu
wybieramy polecenie Format liczby. Zmieniamy Formatowanie na liczbowe,
ustawiamy zero miejsc po przecinku i zaznaczamy opcję Używaj separatora (rys.
nr 11).
rys. nr 11 — Formatowanie liczbowe
Jeśli mamy dane w tabeli przestawnej, to wystarczy zaznaczyć
dowolną komórkę i wybrać polecenie wykres kolumnowy (punkt 2 na rysunku nr 12)
z karty Wstawianie i Excel sam pobierze wszystkie wartości z naszej tabeli i
stworzy wykres.
rys. nr 12 — Wstawianie wykresu
Excel automatycznie wstawił legendę do wykresu, ale mamy tylko
jeden rodzaj danych więc legenda nie jest potrzebna (rys. nr 13). Z tego
wykresu należy usunąć legendę.
rys. nr 13 — Wykres z legendą
Możemy zaznaczyć na wykresie serię i nacisnąć skrót
klawiszowy Ctrl+1, uruchomi nam się panel boczny Formatowanie serii danych. W
opcjach serii zmniejszamy szerokość odstępu między seriami, aby nasze kolumny
były szersze i bardziej wyraźne (rys. nr 14).
rys. nr 14 — Szerokość odstępu między seriami
Nie chcemy mieć dodatkowych przycisków na wykresie, które
służą do filtrowania naszych danych. Na dowolny z nich klikamy prawym przyciskiem
myszy i wybieramy z podręcznego menu opcję Ukryj wszystkie przyciski pól na
wykresie (rys. nr 15)
rys. nr 15 — Ukryj wszystkie przyciski pól na wykresie
Kolejnym krokiem jest zmiana tytułu wykresu na np. Przepływy
rzeki Wirki w roku 2008 [m3], ponieważ zadanie zakładało jasną i przejrzystą
prezentację danych na wykresie. Aby w Excelu w tytule wykresu zmienić m3 na
metry sześcienne (indeks górny), zaznaczamy cyfrę 3 i wciskamy skrót klawiszowy
Ctrl+1. Otworzy nam się okno Czcionka, gdzie zaznaczamy opcję indeks górny,
zatwierdzamy przyciskiem OK (rys. nr 16).
rys. nr 16 — Indeks górny
Musimy jeszcze zmienić opis osi. Od Excela 2013 wystarczy
kliknąć znak plus w prawym górnym roku wykresu. Rozwija się podręczne menu,
gdzie możemy wybrać tytuły osi i rodzaj osi jaka nas interesuje – my chcemy
opisać tylko oś główną pionową (rys. nr 17)
rys. nr 17 — Tytuł głównej pionowej osi
Kolejnym krokiem jest zmiana nazwy osi na Woda m3,
analogicznie jak w tytule wykresu. Otrzymaliśmy podsumowane dane i wykres
wykonany na ich podstawie (rys. nr 18).
rys. nr 18 — zakończony wykres
Pozostają tylko drobne poprawki upiększające. Można założyć, że zdałbym maturę na 5 🙂
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 tym poście nauczymy się jak stworzyć grafikę pokazującą
postęp pracy, czyli taką która będzie się zmieniać w zależności od procentowego
postępu pracy. Są na to różne sposoby. Pierwszym etapem jest wybranie jakiegoś
kształtu, na którym pokażemy ten postęp. Aby wybrać symbol wybieramy polecenie
Symbol z zakładki Wstawianie (rys. nr 1).
rys. nr 1 — Wstawianie symbolu
Pojawi nam się okno Symbol, wybieramy serduszko z okazji
niedawnych walentynek. Znajdziemy je w Czcionce Webdings, następnie
zatwierdzamy wybór klikając przycisk Wstaw (rys. nr 2).
rys. nr 2 — Okno Symbol
Kolejnym krokiem jest skopiowanie komórki, w której wstawiliśmy
serduszko i korzystając z prawego przycisku myszy, z podręcznego menu wybieramy
polecenie Wklej specjalnie, a następnie z Innych opcji wklejania wybieramy
Obraz połączony (rys. nr 3).
rys. nr 3 — Wklejanie Obrazu połączonego
Możemy wtedy powiększyć nasz symbol. Musimy zmienić
wypełnienie na np. kolor szary, żebyśmy widzieli, że mamy również tło (w
zakładce Narzędzia główne – rys. nr 4)
rys. nr 4 — Zmiana koloru wypełnienia
Problematyczne jest wyrównywanie symboli wstawionych w
Excelu. Możemy to zrobić za pomocą polecenia Przytnij w zakładce Formatowanie
(rys. nr 5).
rys. nr 5 — Polecenie przytnij
Otrzymujemy przycięty obraz tak, aby z każdej strony
pozostawało mało miejsca. W zakładce Formatowanie mamy interesującą nas opcję –
Ustaw kolor przezroczysty (rys. nr 6).
rys. nr 6 — Polecenie Ustaw kolor przezroczysty
Klikamy na czarny obszar serduszka i w efekcie otrzymujemy
puste tło. Drugim etapem jest wykonanie wykresu. Zaznaczamy naszą wartość
procentową, a następnie z zakładki Wstawianie wybieramy Wykres i wstawiamy
zwykły wykres kolumnowy (rys. nr 7).
rys. nr 7 — Wstawianie wykresu kolumnowego
W otrzymanym wykresie musimy zmienić parametry osi y. Zaznaczmy
ją a następnie używamy skrótu klawiszowego Ctrl+1, otworzy nam się panel boczny
Formatowanie osi. Ustawiamy minimum 0, a maksimum 1, aby Excel wiedział że te
wartości mają być na stałe, żeby nie zostały zmienione (rys. nr 8).
rys. nr 8 — Formatowanie osi
Kolejnym etapem formatowania wykresu jest zwiększenie
szerokości naszej kolumny. Klikamy na Serię (prostokąt wykresu) następnie
używając skrótu klawiszowego Ctrl+1, otwieramy okno Formatowania serii danych.
Ustawiamy Szerokość odstępu na wartość 0 (rys. nr 9). Ponadto usuwamy tytuł
wykresu.
rys. nr 9 — Formatowanie serii danych
Nie potrzebujemy linii na obszarze kreślenia, czyli klikamy
na obszar kreślenia i z zakładki Formatowanie wybieramy polecenie Wypełnienie
kształtu i wybieramy Brak wypełnienia lub zmieniamy kolor wypełnienia na biały
(rys. nr 10). Usuwamy również oś.
rys. nr 10 — Ustawienie koloru wypełnienia
Ustawiamy
jak największy obszar kreślenia, aby zajmował jak największą powierzchnię
wykresu. Dopasowujemy wielkość naszego wykresu do wcześniej przygotowanego
serduszka. Nakładamy wykres na serduszko i w następnej kolejności musimy
zmienić w zakładce Formatowanie kolejność, czyli opcja Przesuń do przodu
sprawi, że na wierzchu będzie nasze serduszko (rys. nr 11).
rys. nr 11 — Opcja Przesuń do przodu
Kolejnym etapem jest dopasowanie wielkości serduszka, aby przy danych 100% było całe zamalowane, a przy wartości 0, było puste.
Podsumowując w zależności od wartości procentowej wypełnia się wykres z tyłu, a my widzimy wypełniający się kształt na przodzie.
Ostatnim etapem jest zaznaczenie tych dwóch elementów i
zgrupowanie ich razem, co może być trudne. W karcie Narzędzia główne, wybieramy
opcję Znajdź i zaznacz, a następnie Okienko zaznaczenia (rys. nr 12).
rys. nr 12 — Okienko zaznaczenia
Otworzy nam się panel boczny Zaznaczenie, w którym
przytrzymując przycisk Ctrl klikamy na Obraz 2 i Wykres 3. Zaznaczymy w ten
sposób wykres i nasze serduszko (rys. nr 13).
rys. nr 13 — Panel boczny Zaznaczenie
Następnie w zakładce Formatowanie wybieramy polecenie Grupuj
(rys. nr 14).
rys. nr 14 — Polecenie Grupuj
Dzięki tej czynności nie będzie już problemu z przesuwaniem
naszego wykresu, będzie się przesuwała całość (wykres i obraz serduszka).
Gdybyśmy chcieli, aby na wykresie wyświetlała się wartość procentowa musimy
wstawić Pole tekstowe (w zakładce Wstawianie polecenie Tekst, a następnie Pole
tekstowe). Ustawiamy wielkość czcionki w polu tekstowym i przyrównujemy to pole
do wartości w komórce C2 (=$C$2), czyli aby wyświetlała nam się wartość z tej
komórki. Analogicznie można takie wykresy robić na dowolnych obrazkach (rys. nr 15).
rys. nr 15 — Przykładowe zastosowanie wykresów
Ważne jest, aby pamiętać o stworzeniu tła w tych obrazkach,
bo jeśli zmienimy kolor wypełnienia na biały, to nie widzimy konturu naszego
obrazka w miejscu powyżej aktualnego poziomu pracy zaznaczonego na jakiś kolor.
Możemy to natomiast łatwo zrobić w Power Point. Zaczynamy od wstawienia pola
tekstowego, czyli z zakładki Wstawianie wybieramy polecenie Pole tekstowe,
ustawiamy wielkość czcionki i w to pole tekstowe wstawiamy symbol (zakładka
Wstawianie, a następnie Symbol – kształt kobiety). Kolejnym krokiem jest
wstawienie kształtu – prostokąta, który będzie swoją powierzchnią obejmował
kształt kobiety (zakładka Wstawianie, potem Kształty).
Następnie z zakładki Narzędzia
główne wybieramy polecenie Zaznacz, a potem Okienko zaznaczenia. Przytrzymujemy
klawisz Ctrl i zaznaczamy Prostokąt 7 i Pole tekstowe 6. W programie Power
Point jest dodatkowa opcja, której nie ma w Excelu, mianowicie scalanie
elementów. Z zakładki Formatowanie wybieramy polecenie Scal kształty, a
następnie Połącz lub Odejmij (rys. nr 16)
rys. nr 16 — Polecenie Odejmij lub Połącz w Power Point
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.
