Excel — Zestawy w Tabelach Przestawnych (Excel 2013) — porada #371

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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Excel — Wypełnienie kropkami jak w spisie treści — porada #370

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).

rys. nr 1 — tekst jak w spisie treści

Tej sztuczki nauczyłem się z warsztatu Maliny z Malinowego bloga o Excelu, który odbył się 27 lutego 2019 roku. Opis do kodu formatowania niestandardowego możemy znaleźć na stronie Microsoftu https://support.office.com/pl-pl/article/kody-format%C3%B3w-liczb-5026bbd6-04bc-48cd-bf33-80f18b4eae68

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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Excel Usuwanie ulic z adresów porada #369

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:

= USUŃ.ZBĘDNE.ODSTĘPY(LEWY(A2; ZNAJDŹ("ul.";A2)-1)))

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:

=JEŻELI.BŁĄD(USUŃ.ZBĘDNE.ODSTĘPY(LEWY(A2; ZNAJDŹ("ul.";A2)-1)));A2)

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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Excel — Pasek szybkiego dostępu — polecenie (makro) tylko dla konkretnego pliku — porada #368

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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Excel — Matura z informatyki 2018 — Cykl pracy zbiornika zad 5.4 — porada #367

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:

=JEŻELI([@[Stan zbiornika]]>1000000;1000000-[@[Stan zbiornika]] ‑ZAOKR.GÓRA(0,02*[@[Stan zbiornika]];0);-ZAOKR.GÓRA(0,02*[@[Stan zbiornika]]))

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:

=PODAJ.POZYCJĘ(prawda;tWoda[Stan zbiornika]>1000000;0)

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:

=PODAJ.POZYCJĘ(prawda;tWoda[Stan zbiornika]>1000000;0)+1)

rys. nr 9 — Wynik funkcji PODAJ.POZYCJĘ

Otrzymaliśmy numer wiersza jako wynik, teraz aby otrzymać datę z tego wiersza, musimy użyć funkcji INDEKS. Zapis formuły będzie wyglądał następująco:

=INDEKS(tWoda[Data]; PODAJ.POZYCJĘ(prawda;tWoda[Stan zbiornika]>1000000;0)+1)

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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

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.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama