W dzisiejszym poście nauczymy się jak dodać Zestawy w
tabelach przestawnych. Zestawy to nic innego jak zbiory elementów, które
później łatwo możemy dodawać do tabel przestawnych. W naszym przykładzie
stworzymy zestawy zawierające tylko owoce lub tylko warzywa (rys. nr 1).
rys. nr 1 — Stworzone przykładowe zestawy w tabelach przestawnych
Zagadnienie to omówimy sobie na podstawie przykładowych danych
z rysunku nr 2.
rys. nr 2 — Przykładowe dane
Przede wszystkim musimy sobie stworzyć tabelę przestawną,
czyli zaznaczamy pojedynczą komórkę w tabeli (lub cały zakres danych) i
wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 3).
rys. nr 3 — Wstawianie tabeli przestawnej
Otworzy nam się okno Tworzenie tabeli przestawnej,
zaznaczamy, że chcemy stworzyć tabelę przestawną w istniejącym arkuszu (punkt
1) i wskazujemy komórkę (punkt 2), gdzie ma zostać wstawiona. Najważniejsza
sprawa tutaj, żeby mieć dostęp do Zestawów, musimy mieć przynajmniej Excela
2013, żeby w Oknie Tworzenie tabeli przestawnej mieć możliwość zaznaczenia checboxa
Dodaj te dane do modelu danych (punkt nr 3 na rysunku nr 4). Polecenie to jest
związane z dodatkiem do Excela – Power Pivot, skorzystamy tutaj z jego
elementów. Parametry nowej tabeli przestawnej zatwierdzamy przyciskiem OK.
rys. nr 4 — Parametry nowej tabeli
Otworzy nam się okno Pola tabeli przestawnej, gdzie
przeciągamy pole Produkt do obszaru etykiet wierszy i pole Wartość do obszaru
podsumowań wartości. Otrzymamy tabelę przestawną przedstawioną na rysunku nr 5.
rys. nr 5 — Pola tabeli przestawnej
Aby mieć bardziej czytelne dane, sformatujemy sobie kolumnę
Suma wartości, naciskamy prawym przyciskiem myszy na dowolną wartość z tej
kolumny i za pomocą polecenia Format liczby z podręcznego menu, otworzymy okno
Formatowanie komórek. Wybieramy formatowanie walutowe i zatwierdzamy
przyciskiem OK (rys. nr 6).
rys. nr 6 — Formatowanie walutowe
Otrzymamy dane sformatowane wyrażone w złotówkach. W naszych
danych mamy zarówno owoce jak i warzywa, a chcielibyśmy je mieć w osobnych
zestawach. Musimy rozwinąć polecenie Pola, elementy i zestawy(punkt 2 na
rysunku nr 7) z karty Analiza (punkt 1 na rysunku nr 7), a następnie wybrać
polecenie Utwórz zestaw na podstawie elementów z wiersza (punkt 3). Ponieważ
jesteśmy w modelu danych (zaznaczyliśmy ten checkbox przy tworzeniu tabeli
przestawnej) nie będziemy mieć dostępu do poleceń dotyczących pól i elementów.
Będziemy mieć natomiast dostęp do zestawów. Możemy utworzyć zestawy z elementów
w wierszach które potem w tabeli przestawnej trafią do obszaru etykiet wiersza.
Analogicznie zadziała polecenie dla kolumn, jeśli będziemy mieć jakieś elementy
w tabeli przestawnej w obszarze etykiet kolumn.
rys. nr 7 — Polecenie Utwórz zestaw
W poleceniu Utwórz zestaw na podstawie elementów z wiersza,
tworzymy zestaw na podstawie danych, gdzie mamy tylko jedno pole wstawione do
obszaru etykiet wierszy, bo przy większej ilości pól staje się to dużo bardziej
skomplikowane. Otworzy nam się okno Nowy zestaw (rys. nr 8). W pierwszym kroku
musimy nazwać nasz zestaw – w naszym przykładzie zestaw będzie się nazywał Owoce
(punkt nr 1 na rysunku poniżej). Następnie musimy zająć się produktami w tym
zestawie. Przy każdym produkcie jest lista rozwijana ze wszystkimi elementami
dostępnymi dla pola Produkt. Oprócz wymienionych elementów zawierających się w
polu Produkt mamy Wszystko, czyli podsumowanie po wszystkich wartościach. Z
listy usuwamy wszystkie warzywa za pomocą przycisku Usuń wiersz (punkt 2).
rys. nr 8 — Nowy zestaw
Zostawiamy wiersz Wszystko, ponieważ zawiera on cenę
całkowitą. Kiedy zatwierdzimy Nowy zestaw przyciskiem OK otrzymamy tabelę
przestawną zawierającą tylko owoce, ale suma końcowa nam się nie zmieni (będzie
zawierać ceny owoców i warzyw, mimo, że wyświetlać się będą tylko owoce) – rys.
nr 9.
rys. nr 9 — Zestaw Owoce 2 z sumą wszystkich produktów
Zostawiony w zestawie element Wszystko zawsze będzie
pokazywać cenę całkowitą (razem warzyw i owoców), więc usuniemy go sobie, żeby
nie przeszkadzał nam w obliczeniach. Wybieramy polecenie Zarządzaj zestawami z
karty Analiza (rys. nr 10).
rys. nr 10 Zarządzaj zestawami
Otworzy nam się okno Menedżer zestawów, w którym zaznaczamy
interesujący nas zestaw i klikamy przycisk Edytuj (oznaczony strzałką na
rysunku nr 11).
rys. nr 11 — Menedżer zestawów
Otworzy się okno Modyfikuj zestaw, gdzie musimy usnąć wiersz
Wszystko, wprowadzone zmiany zatwierdzamy przyciskiem OK (rys. nr 12)
rys. nr 12 — modyfikuj zestaw
Otrzymamy tabelę przestawną z samymi owocami, co jest ważne
wybraliśmy tutaj zestaw Owoce a nie produkt, więc Excel sam podstawi nam ten
zestaw do obszaru etykiet wierszy (rys. nr 13).
rys. nr 13 — Pola tabeli przestawnej z zestawem Owoce2
Analogicznie możemy stworzyć zestaw dla warzyw. Możemy to
zrobić na tej samej tabeli przestawnej, zaznaczamy dowolną komórkę w tabeli,
wybieramy polecenie Utwórz zestaw na podstawie elementów z wiersza z karty
Analiza, ale wyświetli nam się komunikat, Excel nie pozwoli nam stworzyć
zestawu na innym zestawie. Musimy najpierw usunąć istniejący zestaw z tabeli
przestawnej (rys. nr 14).
rys. nr 14 — Komunikat Excela
Najłatwiej skopiować sobie tabele przestawną i w oknie Pola
tabeli przestawnej odznaczyć Zestaw Owoce2, a następnie przeciągnąć pole
Produkt do obszaru etykiet wierszy (jak przy tworzeniu tabeli przestawnej od
nowa). Teraz na podstawie dowolnej komórki w kolumnie Produkt możemy sobie
stworzyć nowy Zestaw. Kolejno karta Analiza, potem Pola, elementy i zestawy, a
następnie Utwórz zestaw na podstawie elementów wiersza (jak na rysunku nr 7)
W oknie Nowy zestaw usuwamy wszystkie owoce z listy i wiersz
Wszystko analogicznie jak na rysunku nr 8. Zmieniamy nazwę zestawu na Warzywa2
i zatwierdzamy przyciskiem OK. Otrzymamy tabelę przestawną z zestawem Warzywa2
(rys. nr 15).
rys. nr 15 — Tabela przestawna z zestawem Warzywa2
Mamy teraz 2 osobne tabele przestawne, górna z zestawem
Owoce2 i dolna z zestawem Warzywa2.
Załóżmy że chcemy tym razem podsumować dane w tabeli dla
województw. Skopiujemy tabelę przestawną z owocami i wyłączymy zestaw owoce w
oknie Pola tabeli przestawnej. Następnie przeciągniemy pole Województwa do
obszaru etykiet wierszy to otrzymamy tabelę przedstawioną na rysunku nr 16.
rys. nr 16 — Tabela przestawna podsumowująca według województw
Teraz chcemy podsumować dane dla województw po sprzedaży
owoców. Dzięki temu że mamy stworzony zestaw Owoce2, to nie musimy przeciągać
pola Produkt a następnie przefiltrowywać tabeli po tych produktach, które nie
są owocami, wystarczy że przeciągniemy sobie do obszaru etykiet wierszy pole
Owoce2. Wtedy automatycznie powstanie tabela przestawna przedstawiająca
sprzedaż owoców w danych województwach (rys. nr 17).
rys. nr 17 — Tabela przestawna dla województw i zestawu Owoce2
Analogicznie dla podsumowania po województwach pod kątem
sprzedaży warzyw. Odznaczamy zestaw Owoce2 i zaznaczamy Warzywa2, Excel
automatycznie stworzy nam odpowiednią tabelę (rys. nr 18).
rys. nr 18 — Tabela przestawna dla województw i zestawu Warzywa2
Podsumowując, jeśli często działamy na tabelach przestawnych,
możemy oszczędzić dużo czasu. Wystarczy stworzyć zestawy danych, których
później możemy używać do podsumowywania danych. Tak jak wspominałem wcześniej
możemy tworzyć zestawy dla 2 pól z obszaru etykiet wierszy, ale wtedy w oknie
Nowy zestaw mamy bardzo dużo elementów (rys. nr 19)
rys. nr 19 — Wiele możliwości stworzenia nowego zestawu
Na koniec ciekawostka, jeśli zależy nam na innej kolejności
elementów w danych zestawach to w oknie Modyfikuj zestaw możemy zmieniać tą
kolejność za pomocą strzałek oznaczonych na rysunku nr 20.
rys. nr 20 — Zmiana kolejności danych w zestawie
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 wypełnić kropkami tekst
(komórkę do końca lub do początku) tak jak się to często robi w spisie treści (rys.
nr 1).
Kluczem do uzyskania efektu jak w spisie treści jest
odpowiednie formatowanie liczbowe. Kropek, których tu użyliśmy faktycznie nie
ma w komórce, są efektem formatowania niestandardowego.
Aby dodać kropki do naszych danych, musimy zaznaczyć
odpowiednie komórki w kolumnie A, a następnie za pomocą skrótu klawiszowego
Ctrl+1, otworzyć okno Formatowanie komórek. Wybieramy Kategorię Niestandardowe
(punkt 2 na rysunku nr 2). W polu Typ (punkt 3) chcemy wpisać tekst, a tekst w
Excelu zaznaczamy małpą, więc wpiszemy @ a następnie znak * i . (* powtarza
znak po niej następujący – u nas kropka).
rys. nr 2 — Formatowanie niestandardowe za tekstem
Zatwierdzamy OK i Excel automatycznie wypełni nam zaznaczone
komórki do końca kropkami (rys. nr 3).
rys. nr 3 — Wynik uzupełnienia komórki kropkami z prawej strony
Nie ważne jak szeroką będzie dana kolumna, Excel do końca
wypełni ją kropkami. Mnie nie podoba się że kropki zaczynają się od razu po
tekście, więc w oknie Formatowania komórek w polu Typ wstawię Spację po znaku @
(rys. nr 4).
rys. nr 4 — Formatowanie niestandardowe dołożenie odstępu
Analogicznie postąpimy dla komórek obok, z tą różnicą że
kropki chcemy mieć przed tekstem/liczbą. Przy użyciu skrótu klawiszowego Ctr+1
otworzymy okno Formatowania komórek , wybierzemy kategorię formatowania
Niestandardowe i wpiszemy w polu Typ *. Standardowy. Zapis ten oznacza że
będziemy tyle razy powtarzać kropkę przed tekstem, aż wypełni się cała komórka.
Zatwierdzamy przyciskiem OK (rys. nr 5).
rys. nr 5 — Formatowanie niestandardowe przed tekstem
Otrzymamy dane z rysunku nr 6.
rys. nr 6 — Efekt końcowy
Jeśli zmniejszymy szerokość kolumny z tekstem to pokażą nam
się znaki #, informujące, że mamy za małą szerokość kolumny (rys. nr 7).
rys. nr 7 — Zmiana szerokości kolumny
Podsumowując jeśli chcemy wypełnić komórkę jakimś znakiem
wystarczy, że w formatowaniu niestandardowym w polu Typ wpiszemy * i znak jakim
chcemy wypełnić tą komórkę.
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 poprawić dane, a
konkretnie usuwać ulice z adresów. W przykładowych danych mamy podane nazwy
miast i ulic, chcemy usunąć nazwy ulic, a zostawić tylko nazwy miast (rys. nr 1).
rys. nr 1 — Przykładowe dane
W naszych danych mamy też błędy, brakuje spacji pomiędzy
danymi, nie mamy też identycznie rozpoczynających się nazw, bo raz jest ulica
raz aleja albo plac.
Zaczniemy od tego że chcemy znaleźć ulicę, czyli użyjemy
funkcji ZNAJDŹ. Jako pierwszy argument Szukany_tekst wpiszemy "ul.".
Drugi argument to miejsce gdzie szukamy tego tekstu, czyli komórka A2. Formuła
będzie wyglądać następująco:
=ZNAJDŹ("ul.";A2)
Zatwierdzamy formułę i kopiujemy na wiersze poniżej. Funkcja
ta zadziała prawidłowo, ale tylko dla sytuacji kiedy mamy w adresie
"ul." Co widać na rysunku nr 2.
rys. nr 2 — Działanie funkcji ZNAJDŹ
Liczba 8 oznacza że "ul." Pojawia nam się w danym
wierszu na miejscu ósmego znaku. Kiedy mamy w adresach plac lub aleje albo nie
mamy żadnej nazwy ulicy to funkcja zwraca nam błąd #ARG! Innymi oznaczeniami
zajmiemy się później, najpierw poprawimy przypadki gdzie mamy do czynienia ze
skrótem "ul.".
W naszym przykładzie w pierwszym wierszu mamy "ul."
dopiero na 8 znaku, ale między nazwą miasta a ulicy jest jeszcze spacja. Dałoby
się ją usunąć gdyby nie przypadki, gdzie w naszych danych brakuje odstępu
między nazwą miasta a ulicy. Czyli usuwając wszystko za nazwą "ul." trafimy
na spację lub nie za ostatnią literę nazwy miasta.
Do naszej funkcji ZNAJDŹ musimy dołożyć funkcję do wyciągania
tekstu LEWY. Zapis formuły będzie wyglądał następująco:
=LEWY(A2; ZNAJDŹ("ul.";A2)-1)
Z tekstu w komórce A2 chcemy wyciągnąć tyle znaków ile
znalazła funkcja ZNAJDŹ minus 1, czyli o jeden wcześniej niż się zaczyna
"ul.". Zatwierdzamy Ctrl+Enter i kopiujemy formułę (rys. nr 3).
rys. nr 3 — Działanie funkcji LEWY
Musimy pamiętać, że za częścią nazw miast jest jeszcze
spacja, aby ją usunąć musimy skorzystać z kolejnej funkcji USUŃ.ZBĘDNE.ODSTĘPY.
Zapis formuły będzie wyglądał następująco:
Funkcja USUŃ.ZBĘDNE.ODSTĘPY usunie spację, która została,
między nazwami miast a skrótami ul., natomiast jeśli spacji nie będzie nie
usunie nic – i tak otrzymamy prawidłowy wynik jak na rysunku nr 4.
rys. nr 4 — Działanie funkcji USUŃ.ZBĘDNE
Mamy wyciągnięte miasta z ulicą w adresie, a my chcemy pozbyć
się też nazw placów czy też alei. Użyjemy tutaj funkcji JEŻELI.BŁĄD, która w
przypadku kiedy wartość formuły dla ulic zwróciła nam błąd, zwróci nam tekst
pełnego adresu. Zapis funkcji będzie wyglądał następująco:
Zatwierdzamy formułę Ctrl+Enter i otrzymamy wyniki pokazane
na rysunku nr 5.
rys. nr 5 — Działanie funkcji JEŻELI.BŁĄD
W sytuacji gdzie mieliśmy błąd, mamy teraz powtórzone adresy.
Teraz możemy napisać analogiczne formuły dla sytuacji ze skrótami
"pl." oraz "al.". Możemy też poradzić sobie w inny sposób
przy założeniu, że chcemy się pozbyć tych dodatkowych danych, usunąć je i do
nich nie wracać. Zaznaczamy całą kolumnę z tymi danymi i wciskamy skrót
klawiszowy Ctrl+H. Otworzy nam się okno Znajdowanie i zamienianie, w którym
wpisujemy, żeby Excel zamienił nam każdy skrót pl. na ul. Następnie wciskamy
polecenie Zamień wszystko – punkt 3 na rysunku nr 6.
rys. nr 6 — Znajdowanie i zamienianie
Pojawi nam się komunikat (rys. nr 7). Skróty zostaną
zamienione i kiedy to się stanie zadziała nam formuła dla skrótu ul.
rys. nr 7 Komunikat Excela
Psujemy nasze dane zamieniając w danych adresy z placów na
ulice, ale zakładamy że te dane nie będą nam później potrzebne i że taką
operację wykonujemy tylko raz. Analogicznie postępujemy dla nazw alei w
adresach i otrzymujemy same nazwy miast, bo kolejny raz po zmianie nazw
zadziała nam formuła dla ulic (rys. nr 8).
rys. nr 8 — Wynik usunięcia nazw ulic
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 do paska narzędzi
szybkiego dostępu dodać polecenie (makro), które jest dodane tylko do
konkretnego pliku. Pomysł na ten post zaczerpnąłem z lekcji Leili Gharani, a
dokładniej z jej kursu na stronie www.udemy.com na temat
VBA. Temat ten omówimy na przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Mam przygotowane makro, które w prosty sposób przekształci te
dane, aby stworzyć raport. Chcielibyśmy aby makro pojawiło nam się w pasku narzędzi
szybkiego dostępu w miejscu pokazanym na rysunku nr 2, ale tylko dla tego pliku.
rys. nr 2 — Pasek narzędzi szybkiego dostępu
Musimy rozwinąć opcje na pasku i wybrać polecenie Więcej
poleceń (rys. nr 3).
rys. nr 3 — Więcej poleceń
Otworzy nam się okno Opcje programu Excel. W polu Dostosuj
pasek narzędzi szybko dostęp jest lista rozwijana, w której możemy wybrać nazwę
pliku, na którym aktualnie pracujemy (rys. nr 4).
rys. nr 4 — Miejsce dostosowania paska narzędzi szybkiego uruchamiania
Kiedy wybierzemy nasz aktualny plik, możemy dodać sobie
polecenia, których będziemy potrzebować. W polu Wybierz polecenia z (oznaczony
na rysunku poniżej punktem 1) wybieramy Makra i na liście poniżej wyświetlą się
polecenie z danego działu. U nas pojawił się Raport (punkt 2), zaznaczamy to
polecenie (makro) i klikamy przycisk Dodaj (punkt 3). Następnie zatwierdzamy
przyciskiem OK (rys. nr 5).
rys. nr 5 — Dodaj polecenie do paska
Dodana ikonka pojawi nam się na pasku szybkiego dostępu (rys.
nr 6).
rys. nr 6 — Dodana ikonka makra
Ikonka ta jest dostępna tylko w tym pliku, jeśli otworzymy
inny bądź nowy nie będzie jej. Możemy wcisnąć to polecenie i nasze dane zostaną
w prosty sposób przekształcone co widać na rysunku nr 7.
rys. nr 7 — Dane po użyciu makra
Kiedy naciśniemy klawisz Alt, na pasku pod poleceniami
powinny się pojawić cyferki i literki, których możemy użyć aby uruchomić dane
polecenia za pomocą skrótu klawiszowego (rys. nr 8).
rys. nr 7 — Skróty klawiszowe na pasku narzędzi
W naszym przykładzie dostęp do makra za pomocą skrótu
klawiszowego uzyskamy wciskając Alt+6.
Pamiętajmy, że dodaliśmy to polecenie tylko dla aktualnego
pliku, nie zadziała ono kiedy otworzymy inny.
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 zajmiemy się kolejnym zadaniem z matury
z informatyki 2018. Omówimy rozwiązanie zadania nr 5.4 dotyczące cyklu pracy
zbiornika (rys. nr 1 i rys nr 2). Najtrudniejszym elementem zadania jest
opisanie pracy zbiornika retencyjnego i czasu, kiedy wylewamy nadmiar wody
(ponad 1 000 000 m3 wody) oraz codziennie o 8 rano
wylewanie 2% wody z pomiaru o północy.
rys. nr 1 — Treść zadania 5.4
rys. nr 2 — Treść zadania 5.4 ciąg dalszy
Zakładamy, że mamy już wczytane
dane dotyczące zbiornika retencyjnego z pliku tekstowego (rys. nr 3).
rys. nr 3 — Wczytane dane do zadania
Do danych źródłowych dopisujemy dwie kolumny, pierwsza ze
stanem zbiornika i druga z ilością wody jaką wylewamy. Zaznaczamy pierwszy
wiersz danych, pod nagłówkami i za pomocą skrótu klawiszowego Ctrl+Shift+Plus
dodajemy nowy wiersz, żeby dołożyć stan początkowy zbiornika, jaki był podany w
treści zadania.
W komórce D2 (kolumna Wylewamy) musimy napisać formułę, która
obliczy ile wody musimy wylać w zależności od poziomu napełnienia zbiornika.
Wylewać wodę będziemy dopiero kiedy jej ilość przekroczy 1 000 000 m3
pojemności. Użyjemy do tego celu funkcji JEŻELI. Zapis funkcji będzie wyglądał
następująco:
Sprawdzamy w teście logicznym czy stan zbiornika jest większy
od 1 000 000.
Jeśli przekroczył to od razu wylewamy tą nadwyżkę powyżej
miliona, czyli musimy odjąć 1000000 od zawartości zbiornika ([@[Stan
zbiornika]]-1000000). Chcemy, aby do dalszych obliczeń ta wartość była ujemna,
więc musimy ją zapisać inaczej, mianowicie odjąć aktualny stan zbiornika od
miliona (1000000-[@[Stan zbiornika]]). Oprócz tej wartości musimy jeszcze wylać
2% pojemności wody w zbiorniku w chwili pomiaru zaokrąglone w górę. Użyjemy
funkcji ZAOKR.GÓRA, w której lepiej nie pisać 2% bo to też jest operacja
matematyczna w Excelu, lepiej zapisać jako 0,02 i pomnożyć to przez aktualny
stan zbiornika. Jako drugi argument wpiszemy 0, bo chcemy zaokrąglić do liczb
całkowitych.
Jeśli natomiast stan zbiornika nie przekroczy miliona m3
to wylewamy tylko 2% aktualnego stanu zbiornika, czyli ‑ZAOKR.GÓRA(0,02*[@[Stan
zbiornika]]).
Zatwierdzamy formułę i otrzymujemy wyniki, formuła
automatycznie skopiuje się do wszystkich wierszy, bo działamy w tabeli Excela
(rys. nr 4).
rys. nr 4 — Ilość wody jaką wylewamy
Teraz zajmiemy się formułą dla aktualnego stanu zbiornika.
Użyjemy tutaj funkcji SUMA, dodamy do siebie wartości: objętość wody jaka
wpłynęła danego dnia, stan zbiornika z dnia wcześniejszego i objętość jaką
wylaliśmy. Zapis funkcji będzie wyglądał następująco:
=SUMA([@[Woda m3];C2;D2)
Zatwierdzamy formułę. Excel chciałby zastąpić wszystkie
komórki, ale my chcemy aby w pierwszym wierszu została stała wartość (500000),
którą wpisaliśmy ręcznie, dlatego klikamy dwa razy na prawy dolny róg komórki z
wynikiem obliczeń (zaznaczone strzałką na rysunku poniżej) i otrzymujemy wyniki
przedstawione na rysunku nr 5.
rys. nr 5 — Codzienny stan zbiornika
W pierwszej części zadania mamy podać dzień w którym pierwszy
raz stan zbiornika przekroczył 1 000 000 m3. Moglibyśmy
sobie to łatwo sprawdzić w tabeli za pomocą polecenia Filtry liczb (rys. nr 6).
rys. nr 6 — Filtry liczb — Mniejsze niż
Otworzy nam się okno Autofiltr niestandardowy, gdzie na
rysunku nr 7 w miejscu oznaczonym strzałką wpisujemy wartość 1 000 000
(rys. nr 7).
rys. nr 7 — Autofiltr niestandardowy
Zatwierdzamy przyciskiem OK i otrzymujemy pierwszy dzień, w
którym stan zbiornika przekroczył 1 000 000 m3, to jest
2015-04-18. Musimy pamiętać, że jest to dzień kiedy stan przekroczył ten poziom
a wodę wylewamy dopiero następnego dnia, więc prawidłowa odpowiedz to
2015-04-19 (rys. nr 8).
rys. nr 8 — Pierwszy dzień w którym stan zbiornika przekroczył 1000000 m³
W taki sposób możemy sprawdzić to ręcznie, a teraz pokażemy
jak to zrobić za pomocą formuł. Możemy wykorzystać funkcję PODAJ.POZYCJĘ, która
będzie szukała wyniku naszej operacji logicznej. Zapis funkcji będzie wyglądał
następująco:
Szukamy wartości logicznej prawda, czyli robimy test
logiczny, czy stan zbiornika jest większy od 1 000 000. Jako trzeci
argument wybieramy dopasowanie dokładne, czyli wpisujemy 0. Funkcja
PODAJ.POZYCJĘ zwróci nam pierwszy wiersz, gdzie stan zbiornika przekroczył
1 000 000 m3 wody.
Musimy pamiętać, że jest to dzień kiedy stan przekroczył ten
poziom a wodę wylewamy dopiero następnego dnia, więc prawidłowa odpowiedź to
2015-04-19, czyli do naszej formuły musimy dodać jeden dzień (rys. nr 9). Zapis
funkcji powinien wyglądać następująco:
Funkcja INDEKS patrzy na kolumnę z datą, a funkcja PODAJ.POZYCJĘ
zwraca numer wiersza, z którego chcemy wyciągnąć datę z kolumny Data.
Zatwierdzamy Ctrl+Enter i otrzymujemy w wyniku liczbę (pamiętamy, że w Excelu
daty to liczby). Kolejnym krokiem jest zmiana formatowania na Datę (rys. nr 10).
rys. nr 10 — Zmiana formatowania
Drugie zadanie polega na wyznaczeniu wszystkich dni, kiedy
stan zbiornika był powyżej 800 000 m3. Wystarczy użyć funkcji
LICZ.JEŻELI, dla której pierwszym argumentem jest kolumna stan zbiornika, a
Kryterium to "> 800000". Zapis funkcji będzie wyglądał następująco:
=LICZ.JEŻELI(tWoda[Stan zbiornika]; "> 800000")
Zatwierdzamy Ctrl+Enter. Otrzymaliśmy wynik – 188 dni, w
których poziom wody w zbiorniku przekraczał 800 000 m3 (rys. nr 11).
rys. nr 11 — Wynik funkcji LICZ.JEŻELI
Trzecie zadanie polega na wyznaczeniu maksymalnego stanu
zbiornika, jeśli zmodyfikujemy sposób wylewania wody. Przyjmiemy, że nie
będziemy wylewać wody po przekroczeniu stanu 1 000 000 m3,
a tylko 2% dziennie oraz że zbiornik ma niegraniczoną pojemność. Możemy
skopiować całą tabelkę obok, żeby nie stracić wcześniejszych obliczeń. Teraz
musimy zmodyfikować formułę w kolumnie Wylewamy. Zapis powinien wyglądać
następująco:
=-ZAOKR.GÓRA(0,02*[@[Stan zbiornika]])
Zatwierdzamy Ctrl+Enter. Formuła sama wypełni cała tabelę
(rys. nr 12).
rys. nr 12 — Wynik funkcji ZAOKR.GÓRA
Wystarczy teraz użyć funkcji MAX. Szukamy wartości
maksymalnej w kolumnie Stan zbiornika w drugiej tabeli. Zapis będzie wyglądał
następująco:
=MAX(tWoda2[Stan zbiornika])
Zatwierdzamy formułę i otrzymujemy wynik przedstawiony na
rysunku nr 13, czyli maksymalny poziom wody w zbiorniku wyniesie 1 399 242
m3.
rys. nr 13 — Wynik funkcji MAX
Podsumowując większość z tych zadań można rozwiązać stosując
odpowiednie filtry, ale my pokazaliśmy jak można je rozwiązać za pomocą
odpowiednich funkcji.
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.
