Excel — Matura z informatyki 2018 — Zestawienie łączne po miesiącu zad 5.3 — porada #366

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.

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 — Najdłuższy okres zad 5.2 — porada #365

W dzisiejszym poście omówimy kolejne zadanie z matury 2018 z informatyki. Tym razem musimy znaleźć najdłuższy okres liczony w dniach, w którym codziennie dopływało do zbiornika retencyjnego co najmniej 10 000 m3 wody z rzeki Wirki. Musimy również podać datę początkową i końcową tego okresu.

rys. nr 1 — Zadanie z matury z informatyki 2018

Mamy już zaimportowane dane z poprzedniego zadania w tabeli przedstawione na rysunku nr 2.

rys. nr 2 — Zaimportowane dane

Aby wyznaczyć ten okres możemy sobie stworzyć w Excelu kolumnę pomocniczą. Użyjemy funkcji JEŻELI. Jej formuła będzie wyglądać następująco:

=JEŻELI([@[Wodam3]]>10000;SUMA(C1;1);0)

Formuła ta ma sprawdzić, czy wartość z kolumny Woda m3 jest większa od 10 000. Jeśli ta wartość będzie większa od 10 000 to musimy zacząć liczyć ten wzrastający okres. W Excelu możemy obliczyć ten okres za pomocą funkcji SUMA. Odwołujemy się do komórki powyżej komórki z formułą przez jej nazwę (C1). Nie blokujemy tego zakresu bo będzie się on przesuwał wraz z aktywną komórkę dla której aktualnie będzie się przeliczać formuła. Do tej wartości musimy dodać wartość 1. Używamy funkcji SUMA zamiast dodawania dlatego, że ta funkcja zignoruje nam pierwszą komórkę, czyli nagłówek, ponieważ funkcja SUMA ignoruje wartości tekstowe oraz logiczne —  PRAWDA i FAŁSZ.

Jeśli dana wartość będzie mniejsza od 10000 to wstawiamy 0, bo albo ten okres numerowania się jeszcze nie rozpoczął albo się zakończył więc musimy zmienić numerowanie na 0. Zatwierdzamy formułę Ctrl+Enter. Formułę wpisujemy w tabelę więc formuła automarycznie wypełnia się do końca tabeli.

Ilość dni możemy obliczyć za pomocą funkcji MAX po kolumnie pomocniczej. Zapis formuły będzie wyglądał następująco:

=MAX(tWoda[Pomocnicza])

Zatwierdzamy Ctrl+Enter i otrzymujemy wynik, czyli ile dni trwał najdłuższy okres, kiedy ilość wody wpływającej do zbiornika retencyjnego przekraczała 10000 m3 (rys. nr 3)

rys. nr 3 — Ile dni trwał najdłuższy okres przepływów powyżej 10000 m³

Kolejnym etapem zadania jest znalezienie końca i początku tego okresu. Najpierw wyznaczymy koniec tego okresu, bo mieliśmy podpowiedź w tekście zadania, że taki okres jest tylko jeden. Obliczymy to za pomocą funkcji PODAJ.POZYCJĘ. Zapis formuły będzie wyglądał następująco:

=PODAJ.POZYCJĘ(E6; tWoda[Pomocnicza];0)

Musimy znaleźć wartość maksymalną wyznaczoną wcześniej w kolumnie pomocniczej. Sposób dopasowania dokładny czyli wpisujemy 0 jako trzeci argument, czyli typ_porównania. Uzyskaliśmy wynik, że na pozycji 2687 była ta wartość maksymalna (rys. nr 4).

rys. nr 4 — Wynik funkcji PODAJ.POZYCJĘ

Znamy pozycję końca tego okresu w tabeli, a teraz chcemy aby Excel zwrócił nam datę jaka odpowiada tej pozycji. Możemy to zrobić za pomocą funkcji INDEKS, która będzie się odwoływała do kolumny Data, natomiast z funkcji PODAJ.POZYCJĘ otrzymaliśmy nr_wiersza, w którym była maksymalna ilość dni z przepływem ponad 10000 (drugi argument funkcji). Funkcje zatwierdzamy Ctrl+Enter.

Zapis funkcji będzie wyglądał następująco:

=INDEKS(tWoda[Data];PODAJ.POZYCJĘ(E6; tWoda[Pomocnicza];0))

Jako wynik otrzymamy wartość liczbową, bo pamiętamy, że daty w Excelu są wartościami liczbowymi (rys. nr 5).

rys. nr 5 — Wynik funkcji INDEKS

Aby uzyskać datę musimy zmienić formatowanie w karcie Narzędzia główne (rys. nr 6).

rys. nr 6 — Zmiana formatowania

Otrzymaliśmy datę końca najdłuższego okresu z przepływami wody na poziomie przekraczającym 10 000 m3.

Aby wyznaczyć początek tego okresu wystarczy od końca tego okres (daty wyznaczonej powyżej) odjąć długość trwania tego okresu plus 1. Zapis będzie wyglądał następująco:

=F6-E6+1, zatwierdzamy Ctrl+Enter. Otrzymamy wynik przedstawiony na rysunku nr 7.

rys. nr 7 — Wyznaczenie początku okresu wysokich przepływów

Podsumowując wyznaczyliśmy długość trwania najdłuższego okresu z przepływami wody powyżej 10 000 m3 oraz daty początku i końca tego okresu. Wszystko za pomocą funkcji w Excelu.


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 — Największe zasilenie zad 5.1- porada #364

W dzisiejszym poście pokażemy jak rozwiązać zadanie nr 5.1 z matury 2018 z informatyki. Treść zadania i dane zostały przedstawione na rysunku nr 1.

rys. nr 1 — Zadanie z matury z informatyki 2018

Zadanie polega na wyznaczeniu roku, w którym zbiornik retencyjny został zasilony największą ilością wody. Pierwszy etapem pracy jest pobranie danych do Excela. Dane te są w pliku tekstowym rozdzielone znakiem tabulacji, przedstawiają daty oraz ilość wody (m3), jaka zasiliła zbiornik retencyjny w danym dniu. W Excelu 2010 dane te możemy pobrać za pomocą polecenia Z tekstu z karty Dane, punkt 2 na rysunku nr 2.

rys. nr 2 — Dane zewnętrzne

W nowszym Excelu np. Excelu 365 te polecenia zniknęły ze wstążki, ponieważ większość miejsca zajmują polecenia związane z Power Query. Polecenia te są wyłączone, więc musimy je włączyć w Opcjach w menu Plik. Otworzy nam się okno Opcje programu Excel, gdzie w zakładce Dane musimy zaznaczyć checboxy przy poleceniach Pokaż kreatory importu starszych danych i zatwierdzić przyciskiem OK (rys. nr 3).

rys. nr 3 — pokaż kreatory importu starszych danych

Polecenia te są ukryte pod poleceniem Pobierz dane w zakładce Dane. Wybieramy polecenie Starsze kreatory a następnie Z pliku tekstowego (starsza wersja) – punkt 4 na rysunku nr 4.

rys. nr 4 — Starsze kreatory

Następnie musimy znaleźć lokalizacje pliku Woda.txt, który pobraliśmy, zaznaczyć go i zatwierdzić klikając Importuj (rys. nr 5).

rys. nr 5 — import danych z pliku tekstowego

Pokaże nam się okno Kreator importu tekstu, w którym w trzech krokach zaimportujemy nasze dane. Przede wszystkim musimy zdecydować czy nasz test będziemy rozdzielać czy ma mieć stałą szerokość (punkt 1 na rysunku nr 6). Wybieramy tekst rozdzielany, bo mamy rozdzielenie znakiem tabulacji. Następnie musimy się upewnić czy jest zaznaczone prawidłowe pochodzenie pliku – środkowoeuropejski (punkt 2). Nasze dane nie mają nagłówków, więc checbox oznaczony na rysunku punktem 3 ma pozostać niezaznaczony. Następnie klikamy przycisk Dalej.

rys. nr 6 — Kreator importu tekstu — krok 1

W drugim kroku musimy zaznaczyć checbox przy odpowiednim ograniczniku – w naszym zadaniu jest to Tabulator, dzięki temu nasze dane zostaną rozdzielone na dwie kolumny. Następnie klikamy przycisk Dalej (rys. nr 7).


rys. nr 7 — Kreator importu tekstu — krok 2

W trzecim kroku wybieramy format danych w kolumnach, w pierwszej kolumnie Data (RMD) pokazane na rysunku nr 8, w drugiej kolumnie wybieramy formatowanie ogólne bo są to liczby całkowite. Na koniec zatwierdzamy przyciskiem Zakończ.


rys. nr 8 — Kreator importu tekstu — krok 3

Pojawi nam się okienko Importowanie danych, gdzie musimy zdecydować gdzie nasze dane mają zostać wstawione – wybieramy komórkę A1, następnie zatwierdzamy klikając przycisk OK (rys. nr 9).

rys. nr 9 — Okno importowanie danych

Nasze dane zostały pobrane z pliku tekstowego do Excela co widać na rysunku nr 10.


rys. nr 10 — Pobrane dane

Wstawione dane są połączone z plikiem z którego zostały zaimportowane. Możemy to sprawdzić za pomocą polecenie Zapytania i połączenia w karcie Dane (punkt nr 2 na rysunku nr 11). Otworzy nam się okienko z zapytaniami i połączeniami.

rys. nr 11 — Zapytania i połączenia

Naszym danym brakuje nagłówków, możemy je dodać za pomocą skrótów klawiszowych: pierwszy Shift+Space w celu zaznaczenia wiersza z aktywną komórką, a następnie Ctrl+Shift+Plus w celu dodania wiersza powyżej. Wpisujemy nagłówki w wolne komórki.

Wygodniej jest zamienić nasze dane na tabelę w rozumieniu Excela. Za pomocą skrótu klawiszowego Ctrl+T otwieramy okno Tworzenie tabeli, gdzie Excel domyślnie zaznacza dane, ale bez nagłówków. Dzieje się tak dlatego że Excel zaznacza dokładnie te dane, które zostały zaimportowane. My chcemy aby nagłówki zostały uwzględnione więc ręcznie zamieniamy w zakresie danych numer komórki z $A$2 na $A$1 (rys. nr 12).

rys. nr 12 — Tworzenie tabeli

Po zatwierdzeniu przyciskiem OK wyświetli nam się komunikat, że jeśli zamienimy te dane na tabelę to stracą one połączenie z danymi zewnętrznymi (z plikiem, z którego je zaimportowaliśmy). Potrzebowaliśmy te dane pobrać raz, więc możemy zatwierdzić ten komunikat (rys. nr 13).

rys. nr 13 — Komunikat Excela

Innym sposobem na zaimportowanie danych jest po prostu skopiowanie ich, czyli zaznaczamy wszystkie dane w pliku tekstowym za pomocą skrótu klawiszowego Ctrl+A, a następnie kopiujemy za pomocą Ctrl+C. Następnie wklejamy w Excelu Ctrl+V i te dane wkleją nam się w osobne komórki. Dane te nie są połączone z plikiem wyjściowym, inaczej niż w poprzednim przypadku.

Najlepiej importować dane za pomocą Power Query. Microsoft się z tym zgadza, dlatego właśnie ukrył te starsze polecenia importu zewnętrznych danych. Kiedy mamy już zaimportowane dane, możemy sprawdzić, w którym roku do zbiornika retencyjnego wpłynęło najwięcej wody. Zrobimy to za pomocą tabel przestawnych. Wybieramy dowolną komórkę w naszej tabeli, a następnie wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 14).

rys. nr 14 — Tabela przestawna

Otworzy nam się okno Tworzenie tabeli przestawnej. Zakres domyślnie zawiera całą tabelę, wybieramy wstawienie w istniejącym arkuszu (komórka D1) i zatwierdzamy przyciskiem OK. W oknie tabeli przestawnej przeciągamy Datę do obszaru etykiet wierszy, a następnie klikamy na dowolną datę prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Grupuj. W oknie Grupowanie zaznaczamy grupowanie po latach, bo Excel sam domyślnie nie zgrupuje danych. Zatwierdzamy przyciskiem OK (rys. nr 15).

rys. nr 15 — Grupowanie wg lat

Otrzymamy daty zgrupowane latami (rys. nr 16)

rys. nr 16 — Pogrupowane dane

Kiedy mamy tak pogrupowane dane, wystarczy przeciągnąć pole Woda do obszaru sumy wartości i ona automatycznie nam się zsumuje dla każdego roku (rys. nr 17).

rys. nr 17 — Pola tabeli przestawnej

Możemy kliknąć prawym przyciskiem myszy na któryś z tych podsumowań, aby zmienić Format liczby (rys. nr 18).

rys. nr 18 — Format liczby

W oknie Formatowanie komórek zmieniamy formatowanie na liczbowe, zero miejsc po przecinku i zaznaczamy checkbox Użyj separatora, żeby liczby były bardziej czytelne (rys. nr 19).

rys. nr 19 — Formatowanie liczbowe

Aby znaleźć największą wartość możemy posortować nasze dane od Z do A za pomocą polecenia Sortuj na karcie Dane. Wtedy największy wynik będzie na początku tabeli (rys. nr 20).

rys. nr 20 — Posortowane dane

Innym sposobem jest założenie filtra. Rozwijamy sobie filtr dla kolumny Data, wybieramy Filtry wartości, a następnie Pierwsze 10 – punkt 3 na rysunku nr 21.

rys. nr 21 — Filtry wartości

Otworzy nam się okno Filtr 10 pierwszych wartości, gdzie wpisujemy w miejscu oznaczonym zieloną strzałką, że interesuje nas tylko jedna wartość i zatwierdzamy OK (rys. nr 22).

rys. nr 22 — Okno Filtra 10 pierwszych wartości

Otrzymujemy jeden interesujący nas wynik (rys. nr 23).

rys. nr 23 — Największy wynik

Nasza tabela przestawna zawiera sumę końcową, która nie jest nam tu potrzebna, więc możemy ją wyłączyć za pomocą polecenia Wyłącz dla wierszy i kolumn (punkt nr 3 na rysunku nr 24) z Sum końcowych na karcie Projektowanie.

rys. nr 24 — Sumy końcowe

Ostatecznie otrzymujemy jedną datę (rok) i sumę m3 wody, jakie wpłynęły w danym roku do zbiornika retencyjnego (rys. nr 25).

rys. nr 25 — Odpowiedź na zadanie z matury

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 — Formatowanie warunkowe wykresów — postęp zadania — porada #363

W dzisiejszym poście nauczymy się jak formatować warunkowo wykresy na przykładzie postępu zadania lub ile procentowo upłynęło czasu od rozpoczęcia zadania do planowej daty jego zakończenia. Temat ten omówimy na przykładowych danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

W celu wyjaśnienia, strefa zielona obowiązuje do 60% czasu przeznaczonego na wykonanie zadania, strefa pomarańczowa do 80% i analogicznie powyżej 80% strefa czerwona.

W tym poście nauczymy się jak stworzyć wykres, w którym będą się zmieniać kolory w zależności od wartości jaką pobieramy z danych. Sztuczka polega na tym, że na wykresie mamy uwzględnione trzy serie a nie jedną jak to bywa standardowo. Każda z tych serii jest odpowiedzialna za inną wartość czasu, który upłynął od rozpoczęcia zadania i jest oznaczona innym kolorem. Aby wyznaczyć poszczególne kolory serii użyjemy funkcji JEŻELI.

Dla serii oznaczonej kolorem zielonym zapis funkcji będzie wyglądał następująco:

=JEŻELI(D2<60%;D2;"")

Wyjaśnimy sobie teraz zapis formuły, chcemy sprawdzić czy wartość podana w komórce D2 (postęp zadania) jest mniejszy od 60%. Jeżeli test logiczny zwraca wartość logiczną PRAWDA, czyli wartość jest mniejsza od 60%, to chcemy aby Excel zwrócił nam tą wartość, jeżeli FAŁSZ, czyli wartość okaże się większa od 60%, to chcemy by Excel zwrócił nam nic ("" – oznaczenie pustego ciągu znaków). Zatwierdzamy formułę a następnie przeciągamy na wiersze poniżej. Otrzymamy wartości przedstawione na rysunku nr 2.

rys. nr 2 — Wynik funkcji JEŻELI dla wartości mniejszej od 60%

Dla serii pomarańczowej sytuacja jest analogiczna z tą różnicą że musimy połączyć dwa warunki za pomocą funkcji ORAZ. Zapis funkcji będzie wyglądał następująco:

=JEŻELI(ORAZ(D2>=60%;D2<80%);D2;"")

Nasza sprawdzana wartość musi być większa lub równa 60% i jednocześnie mniejsza od 80%. Jeżeli jest to prawda to Excel ma zwrócić nam tą sprawdzaną wartość, a jeżeli jest to fałsz otrzymamy nic. Zatwierdzamy formułę i przeciągamy na wiersze poniżej (rys. nr 3).

rys. nr 3 — Wynik funkcji JEŻELI dla wartości większej bądź równej 60% i mniejszej od 80%

Analogicznie dla serii czerwonej, Czyli użyjemy funkcji JEŻELI, zapis jej będzie wyglądał następująco:

=JEŻELI(D2>=80%;D2;"")

Sprawdzamy czy nasza wartość jest większa bądź równa 80%, jeżeli prawda to otrzymamy tą wartość, jeżeli fałsz otrzymamy nic. Zatwierdzamy formułę i przeciągamy na wiersze poniżej (rys. nr 4).


rys. nr 4 — Wynik funkcji JEŻELI dla wartości większej bądź równej 80%

Musimy jeszcze sobie jeszcze wyjaśnić jak obliczyć procentowy czas jaki upłynął od rozpoczęcia zadania. Ustalamy sobie datę zakończenia zadań, w naszym przypadku data 2019-01-20. Będziemy odejmować od siebie wartości (daty) i dzielić przez różnicę czasu, czyli od daty rozpoczęcia zadania odejmiemy datę jego zakończenia.

Zapis funkcji będzie wyglądał następująco:

=(MIN($C11;DZIŚ())-$B11)/($C11-$B11)

Musimy wyznaczyć wartość minimalną z dnia końca zadania i dnia dzisiejszego, dzięki temu nigdy nie przekroczymy 100%.

Mamy obliczone nasze kolorowe serie pomocnicze przedstawione na rysunku powyżej (rys nr 4) i na ich podstawie możemy stworzyć wykres. Aby Excel się nam nie pogubił w obliczeniach, na początek zaznaczymy tylko 2 kolumny – Zadanie i Zielone. Z karty Wstawianie wybieramy polecenie Wykres słupkowy skumulowany (rys. nr 5).

rys. nr 5 — Wstawianie wykresu

Otrzymamy wykres dla serii zielonej. Używając skrótu klawiszowego Ctrl+1 otworzy nam się panel boczny Formatowanie osi. Zaznaczamy w Położeniu osi polecenie Kategorie w kolejności odwrotnej i w Przecięciu z osią poziomą polecenie Przy kategorii maksymalnej (rys. nr 6).

rys. nr 6 — Opcje osi

Zmieniamy sobie tytuł wykresu na Upłynęło zadań. Następnie musimy dodać kolejne serie do wykresu, czyli z karty Projektowanie wybieramy polecenie Zaznacz dane (rys. nr 7).

rys. nr 7 — Polecenie Zaznacz dane

Otworzy nam się okno Wybieranie źródła danych, gdzie wciskamy polecenie Dodaj (rys. nr 8).

rys. nr 8 — Dodawanie danych

W oknie Edytowanie serii zaznaczamy Nazwę serii (tytuł) i Wartości serii. Zatwierdzamy klikając OK (rys. nr 9).

rys. nr 9 — Edytowanie serii

Analogicznie dodajemy serię czerwoną. Mamy teraz trzy serie o różnych kolorach (rys. nr 10).

rys. nr 10 — Wybieranie źródła danych

Zatwierdzamy przyciskiem OK. Otrzymaliśmy wykres ale w innych kolorach niż założyliśmy. Zmieniamy kolory poszczególnych serii zmieniając kolor wypełnienia w karcie Narzędzia główne (rys. nr 11).

Rys. nr 11 — Wykres po zmianie kolorów wypełnienia

W czasie różnych zmian zepsuło nam się procentowe formatowanie osi. Naciskamy skrót klawiszowy Ctrl+1, otworzy nam się panel boczny Formatowanie osi. Musimy przejść do zakładki Liczby i zmienić kod formatu na 0% (rys. nr 12).

rys. nr 12 — Formatowanie osi — Liczby

Następnie w zakładce Opcje osi ustawić maksimum na 100%, czyli wpisać wartość 1 (rys. nr 13).

rys. nr 13 — Formatowanie osi — Maksimum

Następnie w opcjach serii ustawić szerokość odstępów między seriami, chcemy zmniejszyć tą odległość, czyli żeby nasze słupki były szersze (rys. nr 14).

rys. nr 14 — Odległość między seriami

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 — Filtrowanie tabeli przestawnej po podsumowanych wartościach — porada #362

W dzisiejszym poście omówimy filtrowanie tabeli przestawnej po podsumowanych wartościach. Zagadnienie to omówimy na podstawie przykładowych danych sprzedażowych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Dla podanych danych stworzyliśmy tabelę przestawną (rys. nr 2). W naszym przykładzie chodziło o to by stworzyć tabelę przestawną tylko  z tymi miastami, których podsumowanie po polu (kolumnie) Zysk/strata daje wartość ujemną.

rys. nr 2 — Pola tabeli przestawnej

Rozpatrzyliśmy konkretny przykład, ale możemy to rozwiązanie przenosić na wiele innych filtrów. Aby stworzyć tabelę przestawną, zaznaczamy pojedyncza komórkę w danych, następnie wybieramy polecenie Tabela przestawna z karty Wstawianie (rys. nr 3).

rys. nr 3 — Wstawianie tabeli przestawnej

Otworzy nam się okno Tworzenie tabeli przestawnej. Zaznaczamy tabelę – zakres (punkt nr 1 na rysunku nr 4) i wybieramy gdzie chcemy stworzyć tabelę przestawną – w naszym przykładzie w nowym arkuszu (punkt 2 na rysunku poniżej). Zatwierdzamy klikając OK.

rys. nr 4 — Tworzenie tabeli przestawnej

Przeciągamy Miasto do obszaru etykiet wierszy, natomiast Zysk/ Strata do obszaru podsumowań wartości (rys. nr 5).

rys. nr 5 — Obszary etykiet

Następnie klikamy prawym przyciskiem myszy na pierwszą wartość i z podręcznego menu wybieramy polecenie Format liczby (rys. nr 6).

rys. nr 6 — Format liczby

Otworzy nam się okno Formatowania. Wybieramy Kategorię formatowania Walutowe (punkt nr 1 na rysunku poniżej) oraz typ z wartościami ujemnymi (punkt 2) i ustawiamy brak miejsc po przecinku – patrz punkt 3 na rysunku nr 7. Zatwierdzamy klikając przycisk OK.

rys. nr 7 — Okno formatowania

W wyniku naszych działań mamy wartości ujemne i dodatnie w zależności od miast, czyli widzimy w których miastach mieliśmy zysk, a w których straty (rys. nr 8).

rys. nr 8 — Tabela przestawna

Nas interesują tylko te miasta, które przyniosły straty, czyli te z wartością ujemną po podsumowaniu w kolumnie Zysk/Strata. Tabela przestawna nie daje standardowo takich możliwości filtrowania, abyśmy uzyskali tylko miasta z wartościami ujemnymi, ale możemy przefiltrować tabelę po miastach. Jeżeli nie chcemy aby w naszej tabeli pojawiły się przykładowo miasta takie jak Białystok, Bielsko Biała i Częstochowa to wystarczy, że odznaczymy checboxy obok ich nazw i zatwierdzimy klikając OK (rys. nr 9).

rys. nr 9 — Filtrowanie po miastach

Te miasta nie będą już wyświetlane w tabeli przestawnej, zostały przefiltrowane na jej poziomie. Nam chodzi o filtr, który zwróci nam miasta z ujemną wartością. Musimy wyczyścić poprzedni filtr za pomocą polecenie Wyczyść filtr z „Miasto” (rys. nr 10).

rys. nr 10 — Wyczyść filtr

Sposobu na filtrowanie po wartościach w kolumnie Suma z Zysk/Strata nauczyłem się od Billa Jelena. Zaznaczamy komórkę obok nagłówka kolumny Suma z Zysk/Strata, następnie wciskamy skrót klawiszowy Ctrl+Shift+L, czyli nakładamy filtr z karty Dane (rys. nr 11).

rys. nr 11 — Polecenie Filtruj

Teraz możemy filtrować po kolumnie Suma z Zysk/Strata. Wybieramy Filtry liczb a następnie Mniejsze lub równe (rys. nr 12).

rys. nr 12 — Filtry liczb

Otworzy nam się okno Autofiltr niestandardowy. Wpisujemy obok pola jest mniejsze niż lub równe wartość 0, zatwierdzamy klikając przycisk OK (rys. nr 13).

rys. nr 13 — Autofiltr niestandardowy

Otrzymamy przefiltrowane dane przedstawione na rysunku nr 14. Co warto zauważyć to nasz filtr nałożyliśmy na cały arkusz a nie na samą tabelę przestawną, a możemy to rozpoznać przez to, że pokazują nam się kolorowe numery wierszy, a niektórych wierszy w ogóle brakuje.

rys. nr 14 — Filtr nałożony na arkusz

Możemy sobie nałożyć dodatkowe dane, tzn. do obszaru etykiet kolumn przeciągnąć Datę. Domyślnie Excel automatycznie nie grupuje po datach, więc klikamy prawym przyciskiem myszki na jakąś datę, a następnie z podręcznego menu wybieramy polecenie Grupuj. W oknie Grupowanie, zaznaczamy grupowanie po miesiącach, zatwierdzamy klikając OK (rys. nr 15).

rys. nr 15 — Grupowanie

Otrzymamy tabelę przestawną przedstawioną na rysunku nr 16.

rys. nr 16 — Tabela przestawna pogrupowana po miesiącach

Musimy wyłączyć poprzedni filtr za pomocą skrótu klawiszowego Ctrl+Shift+L, ponieważ wychodzi on w środku nowej tabeli. Ponownie ustawiamy aktywną komórkę obok nagłówka miesiąca tabeli przestawnej i włączamy filtr po całości za pomocą skrótu klawiszowego Ctrl+Shift+L. Teraz możemy filtrować dane w tabeli przestawnej po dowolnej kolumnie. Rozpatrzymy filtrowanie dla Stycznia. Klikamy trójkącik przy styczniu (na rysunku oznaczony zieloną strzałką), a następnie wybieramy polecenie Filtry liczb i Mniejsze lub równe (rys. nr 17).

rys. nr 17 — Filtry liczb

Dalej analogicznie jak dla poprzedniego przypadku (patrz rys. 13), w oknie Autofiltr niestandardowy wpisujemy Mniejsze lub równe 0. Zatwierdzamy OK. Otrzymamy przefiltrowane dane ale tylko w miesiącu styczeń (rys. 18).

rys. nr 18 — Tabela przestawna z filtrem

Podsumowując nakładanie takich filtrów w tabeli przestawnej nie jest możliwe. My nakładamy filtr na cały arkusz, dzięki temu możemy nakładać ten filtr arkusza na tabelę przestawną. Na tabelę przestawną możemy tylko nałożyć filtry po miastach lub po datach, czyli te filtry działają zanim te dane zostaną wyświetlone w arkuszu. Możemy rozpoznać, że filtry zostały nałożone na arkusz a nie bezpośrednio na tabelę przestawną po tym, że Excel zaczyna kolorować numery wierszy oraz części z nich brakuje.


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