Kurs Excel: Zadanie 6
od Podstaw
6 godz. 10 min · Excel · Biznes i Automatyzacje
Michał KowalczykTrener i założyciel w Excellent Work - Skuteczna Nauka ExcelaCzego się nauczysz
- Zyska lepsze zrozumienie działania programu Excel.
- Zyska umiejętność pracy na dużych zestawach danych (filtrowanie, przekształcanie, raportowanie).
- Dowie się jak doszukiwać i łączyć potrzebne dane z innych plików i arkuszy.
- Pozna sposoby wyciągania z komórki istotnych fragmentów danych.
- Pozna podstawy analizy dużych zestawów danych z użyciem Tabel Przestawnych.
- Będzie umiał podsumować dane według kryteriów (np. sprzedaż w rozbiciu na kategorie).
- Będzie umiał zliczyć wystąpienia według kryteriów (np. liczba transakcji nieopłaconych).
- Nauczy się tworzyć proste algorytmy przypisujące wartości na podstawie spełnionych kryteriów.
Formatowanie i zarządzanie danymi
Efektywna praca w Excelu zaczyna się od płynnego poruszania się w arkuszach i tabelach. W tej części kursu skupimy się na wprowadzaniu danych, ich edycji i tworzeniu układów, które znacznie poprawią czytelność raportów. Nauczysz się też, jak sprawnie sortować i filtrować duże zestawy informacji, by w kilka chwil wyciągać z nich najważniejsze wnioski. Pokażę Ci również sztuczki przydatne na co dzień, dzięki którym oszczędzisz mnóstwo czasu.
Formuły i kalkulacje
Excel oferuje imponującą liczbę funkcji, które pozwalają na głęboką analizę dowolnych danych – od formuł tekstowych, przez przetwarzanie dat, po budowanie złożonych wyrażeń logicznych. W tej części kursu zobaczysz, jak łączyć różne funkcje w jedno, aby poszerzyć ich możliwości. Dowiesz się, jak tworzyć warunkowe formuły, które będą automatycznie reagować na zmiany w arkuszu. Dzięki temu aktualizacje danych w tabeli wyliczą się same, bez dodatkowej pracy z Twojej strony.
Praca z tabelami przestawnymi i wykresami
Nawet najlepsze dane nie mają większej wartości, jeśli nie umiesz ich właściwie zaprezentować. Dlatego w kolejnej sekcji nauczysz się tworzyć klarowne tabele przestawne i atrakcyjne wizualizacje. Wykorzystasz listy wyboru, by ograniczyć liczbę błędów przy wprowadzaniu danych, a następnie za pomocą wykresów pokażesz wyniki w jasny i przystępny sposób. Zyskasz praktyczne umiejętności tworzenia zestawień w oparciu o Tabele Przestawne.
Podsumowanie i praktyczne wskazówki
Na koniec kursu zebrałem najczęstsze błędy, na jakie trafiają nawet zaawansowani użytkownicy, i pokazałem, jak skutecznie ich unikać. Dostaniesz pakiet najlepszych praktyk, przydatnych skrótów klawiszowych oraz zestaw praktycznych rad, które pomogą Ci rozwiązać typowe problemy w Excelu. Do zobaczenia w kursie!
Dla kogo jest ten kurs?
Ten kurs powstał z myślą o osobach, które pracują już z Excelem oraz tych, które dopiero rozpoczynają przygodę z tym programem. Z kursu najwięcej wyniosą osoby, które:
Ok.
Witam Cię w bloku zadań i tradycyjnie zapraszam Cię do zatrzymania wideo
przejścia sobie przez te zadania na spokojnie, we własnym zakresie, a potem do
powrotu do wideo i zweryfikowania swoich odpowiedzi.
Sposobem, z którego ja korzystam.
Ok, mamy zadanie pierwsze wyciągnij imię i nazwisko z adresu email.
I tutaj chciałbym pokazać Ci pewien trik.
Jeżeli wpisałbym sobie tutaj Andrzej ustawiłbym się w komórce poniżej i wcisnął
Ctrl+E, to zwróć uwagę co się stało.
Excel automatycznie wyciągnął mi imiona na podstawie tego mojego szablonu.
Co tutaj się w ogóle wydarzyło? Jakie Ctrl+F?
Jeżeli wybierzemy sobie te dane i przejdziemy sobie tutaj po prawej, to jest
taka opcja jak wypełnienie błyskawiczne Flashfield.
Ona nie zawsze działa i ma pewien minus, o którym za moment.
Natomiast pozwala w takich podręcznych, jednorazowych sytuacjach
zaoszczędzić całkiem sporo czasu.
No i gdybyśmy mieli tylko jednorazowo taką listę sobie robić, ten skrót
Ctrl będzie dobrze działał.
Czasami trzeba podać więcej przykładów, a więc podamy sobie tutaj
na przykład widelec.
Wpiszemy sobie jeszcze Kowalska.
To tak dla pewności, że zadziała i wciskamy znowu Ctrl,
będąc w pustej komórce.
Mamy Rumun, mamy Nowak, mamy Sova.
Pięknie nam to zadziałało.
Ok, co w sytuacji, gdy nie zadziała, gdy na przykład nie będzie odpowiednio
ta funkcja zwracała wyników?
Wciskamy wtedy Ctrl+F i podajemy na przykład jeszcze dwa, trzy przykłady.
I wtedy możemy wcisnąć kontrolę i zobaczyć, czy ten rezultat
będzie taki, jaki oczekujemy.
Minus takiego rozwiązania jest taki, że gdy tworzymy jakieś formularze, które
będziemy sobie odświeżali, no to niestety wklejenie tutaj nowej listy maili
spowoduje, że po raz kolejny trzeba będzie to sobie uzupełniać
przy pomocy tej funkcji.
Ctrl+F, czyli Ctrl+E.
Tak zwane wypełnienie błyskawiczne świetnie sprawdza się ad hoc,
czyli tak właśnie jednorazowo.
Natomiast gdy tworzymy formularze, a bardzo często w organizacjach do tego się
to sprowadza, że tworzymy sobie jakieś pewnego rodzaju obliczenia, takie raporty,
gdzie wklejamy sobie tylko nowe źródło, które na przykład schodzi z jakiegoś
systemu albo klient nam je podsyła i wszystko dzieje się samo, wszystko liczy
się samo, żebyśmy nie musieli tego robić po raz kolejny.
W takich sytuacjach sprawdzają się formuły i tutaj możemy wyciągnąć imię,
nazwisko i imię oraz nazwisko.
Jak możemy to zrobić?
Pamiętamy z ćwiczeń związanych z funkcjami tekstowymi, że jesteśmy w stanie
skorzystać z funkcji znajdź do znalezienia jakiegoś tutaj interesującego
nas miejsca w naszym przypadku.
Interesuje nas znalezienie kropki, czyli znajdźmy sobie cudzysłów, kropka,
cudzysłów w jakimś konkretnym tekście.
W tekście, który znajduje się tutaj.
Liczby początkowej nie potrzebujemy podawać.
Zamykamy nawias, mamy pozycję kropki złapiemy sobie, przeciągniemy w dół.
Mamy tutaj pozycję pozycja kropki.
Zapiszemy sobie nagłówek, aby było nam łatwiej.
Świetnie!
Mając pozycję kropki jesteśmy w stanie skorzystać z funkcji lewy, aby
wyciągnąć nasze imię do kropki.
Czyli mamy konkretny tekst.
Liczba znaków.
To co tutaj jest?
Odejmujemy jeden znak.
Pamiętamy, że jeżeli tego nie zrobimy, to wyciągnęlibyśmy imię razem z kropką.
Zamykamy nawias.
Mamy Andrzeja.
Tutaj na samym początku możemy dodać jeszcze funkcję, która spowoduje, że ten
Andrzej będzie zapisany z wielkiej litery.
Jest taka funkcja z wielkiej, z wielkiej litery i zamykamy nawias od tej funkcji.
To jest też coś, co robiliśmy już wcześniej.
Wkładaliśmy jedną funkcję w drugą, czyli innymi słowy nasza funkcja z wielkiej
litery zapisuje z wielkiej litery to, co zwraca funkcja lewy, a funkcja lewy zwraca
Andrzej, co powoduje, że mamy imię już odpowiednio sformatowane.
Teraz pozostaje pytanie, w jaki sposób jesteśmy w stanie sobie
wyciągnąć nazwisko?
Nazwisko to jest coś, co znajduje się pomiędzy kropką a małpą.
No to teraz przydałaby nam się pozycja małpy.
Pozycja małpy.
Ok, no to szukamy małpy.
Również skorzystamy z funkcji znajdź.
Naszym szukanym tekstem jest małpa, czyli cudzysłów, małpa, cudzysłów, średnik.
Gdzie szukamy? Szukamy.
Tutaj zamykamy nawias i zatwierdzamy enter.
Mamy pozycję naszej małpy, czyli teraz nazwisko, a w zasadzie
liczba liter w nazwisku.
To będzie Różnica pomiędzy pozycją małpy a pozycją kropki.
Wpiszmy sobie tutaj Liczba liter nazwiska. Ok.
I teraz tak. Pozycja małpy minus pozycja kropki.
Mamy 8 liter.
To prawie by działało dlatego, że mamy pozycję, kropki i pozycję małpy.
A w zasadzie trzeba tutaj będzie jeszcze odjąć jedynkę.
Dlatego, że pozycja małpy jest o jeden znak za nazwiskiem, a więc
wypadałoby oczywiście to uwzględnić.
Czyli widelec ma 7 znaków.
Kowalska ma znaków 8.
Przeciągnijmy w dół i zobaczmy, czy to ma sens.
Wygląda na to, że jak najbardziej. Świetnie.
Czyli jeżeli mamy już liczbę liter i mamy też pozycję startową, czyli kropkę
będziemy wyciągali o jeden znak od kropki, to możemy też wyciągnąć nazwisko
korzystając z funkcji Fragment tekstu, która pozwala wyciągnąć tekst ze środka.
Odwołujemy się do naszego konkretnego tekstu.
Wciskamy średnik.
Jaka jest liczba początkowa?
Liczba początkowa to jest nasza kropka.
+1 jeden znak za kropką. Świetnie.
Ile znaków nas interesuje?
No tutaj już nie możemy wpisywać 100, tak jak robiliśmy to w ćwiczeniach.
Potrzebujemy konkretnej liczby znaków, dlatego, że no za chwilę
jest małpa i exwork.
No więc to jest też coś, co pojawiłoby się wtedy w tym naszym nazwisku.
Możemy sobie zamknąć nawias i zatwierdzić enterem.
Mamy nasze nazwisko, nazwisko, widelec.
Tutaj również możemy pokusić się o ten trik, co wcześniej.
Ustawiam się pomiędzy równa się a fragment tekstu i wpisuję z wielkiej.
Zaczynam wpisywać.
Pojawiła się już funkcja wciskam tab, żeby ją sobie zaciągnąć.
Dojeżdżam do samego końca i zamykam nawias.
I tutaj kolejny bardzo fajny trik, który Ci się przyda niejednokrotnie w funkcjach
zwróć uwagę ten nawias tutaj jest czerwony.
Ostatni nawias od funkcji jest zawsze czarny, czyli jeżeli tych funkcji tutaj
będzie więcej, zdobędziesz poziom średni średniozaawansowany, być może
zaawansowane, to te funkcje będzie zagnieżdżało Ci się coraz chętniej.
Będzie tych funkcji się robiło więcej w komórce.
Dlatego pamiętaj jeżeli chcesz zamknąć wszystkie nawiasy do końca, to ten ostatni
będzie czarny, co zresztą tutaj u nas widać.
Ten wcześniejszy jest czerwony.
Zatwierdzimy sobie enterem, mamy nazwisko, przeciągamy sobie w dół.
No i na samym końcu pozostaje tylko połączenie imienia z
nazwiskiem, czyli Andrzej.
Łączymy go z cudzysłów spacja cudzysłów i łączymy go z nazwiskiem.
Przeciągamy sobie w dół.
Dynamizm tych funkcji polega na tym, że gdyby na przykład tutaj pojawiły się
jakieś nowe dane i zamiast Andrzeja pojawiłoby się imię Anna, to zwróć uwagę
ta funkcja by nam tutaj fajnie wszystko przeliczyła.
No i faktycznie nie musielibyśmy tego przeliczenia robić.
Po raz kolejny wciśniemy Ctrl+F, aby edytować zadanie pierwsze za nami.
Zadanie drugie.
W zadaniu drugim musimy korzystając ze zdobytej wiedzy w kolumnie i
utworzyć formułami zdanie Imię.
Rok urodzenia i tutaj podać rok urodzenia zarabiaok i tutaj zaokrąglona pensja.
Możemy skorzystać z przygotowanych formuł pomocniczych.
I my właśnie to zrobimy.
A z racji tego, że już trochę wyciągaliśmy razy to imię, to może nie będziemy
korzystali z funkcji znajdź spację, tylko zrobimy wszystko już
bezpośrednio w jednej komórce.
Skorzystamy z funkcji lewy, no bo wiemy, że ta funkcja nam pomaga
wyciągnąć wartości z lewej strony. Liczba znaków.
No właśnie.
W tej liczbie znaków pomagała nam funkcja znajdź, gdzie szukaliśmy spacji,
którą zawsze wpisujemy w cudzysłowie. W jakim tekście?
W tym tekście, który znajduje się tu po lewej?
Świetnie. I co teraz?
Liczba początkowa to jest coś, co nas nie interesuje, a wyciągamy od lewej do znaku,
który jest o 1 przed spacją, czyli minus 1.
Zamykamy. Zatwierdzamy Enter.
Mamy imię Robert. Świetnie.
Rok urodzenia.
Rok urodzenia możemy wyciągnąć przy pomocy funkcji rok.
I odwołujemy się tutaj bezpośrednio do daty urodzenia.
Mamy wyciągnięty rok.
Następnie trzeba zaokrąglić pensję do wielokrotności 500 zł, a więc użyjemy
funkcji zaokrąg wielokrotności.
Szukamy tutaj takiej funkcji. Jest.
Odwołujemy się do konkretnej liczby, czyli do pensji netto i zaokrąglamy.
Do jakiej wielokrotności?
Do wielokrotności 500, bo tak mamy w nagłówku.
Zamykamy. Zatwierdzamy.
Ok.
No i teraz możemy spróbować stworzyć sobie gotowe zdanie, a na końcu przeciągniemy
sobie funkcję, czyli piszemy gotowe zdanie.
Zaczynajmy. Imię.
Czyli zaczynamy od imienia.
Dołączamy w cudzysłowie przecinek.
Rok urodzenia.
Dwukropek i spacja. Zamykamy.
I teraz dołączamy właśnie ten rok urodzenia, który sobie tutaj
wcześniej zbudowaliśmy.
Następnie dołączamy przecinek.
Zarabiaok.
Spacja.
Zamykamy cudzysłów i dołączamy wartość, którą policzyliśmy.
No i tutaj jest jeszcze prośba o kropkę na końcu.
A więc dołączamy sobie jeszcze w cudzysłowie kropkę.
Zatwierdzamy Enterem.
No i mamy gotowe zdanie.
Tutaj gdzieś pojawiła się kropka.
Być może tak pojawiła się kropka.
Zamiast przecinka wpisałem kropkę, więc możemy sobie tę funkcję edytować
i zrobić korektę z mojej strony. Robert.
Rok urodzenia zarabia ok. Świetnie.
Możemy sobie złapać teraz te nasze funkcje, które wcześniej zbudowaliśmy,
przeciągnąć w dół, skorzystać z auto dopasowania szerokości.
No i zobaczmy, czy to ma sens.
Mamy Jadwigę 1994.
Wszystko wygląda na to, że działa w sposób prawidłowy.
Formuła również zaokrągla tak, jak byśmy chcieli.
Ok. No to zadanie numer 3.
Policz dni robocze pomiędzy datami od poniedziałku do piątku.
To są dni robocze.
Załóż, że w każdym dniu roboczym osoba przepracowała 8 godzin.
Policz kwotę do wypłaty.
Nie ma tutaj słowa o świętach, więc zakładamy, że święta są pominięte.
Liczymy dni robocze.
I teraz chcemy policzyć dni robocze pomiędzy dwoma datami, a więc
korzystamy z funkcji dni robocze.
Jeżeli najedziemy sobie na funkcję dni robocze, mamy tutaj informację po prawej.
Zwraca liczbę dni roboczych pomiędzy dwiema datami.
Gdyby czasami pojawiło się u Ciebie wahanie, czy to są dni robocze, czy dzień
roboczy, jeżeli wpiszemy sobie dzień roboczy i zjedziemy w dół, zwraca wartość
liczby seryjnej daty przed lub po podanej liczbie dni roboczych.
Czyli to nie jest funkcja, na której nam zależy.
Nam zależy na funkcji dni robocze. Zjeżdżamy w dół.
Zwraca liczbę dni roboczych. Świetnie!
To jest właśnie ta funkcja.
Data początkowa.
data końcowa, czyli ja tutaj znowu operowałem strzałkami, zaznaczyłem sobie
strzałką w lewo datę startu, wcisnąłem średnik, strzałka w lewo, data końca
święta pomijam, więc zamykam nawias, zatwierdzam enterem.
No i mam tutaj dni robocze.
Znowu pojawia się jakiś problem natury formatu, ale my już jesteśmy po lekcjach
związanych z formatowaniem, więc wiemy, że możemy sobie dać format ogólny.
Już mamy tutaj liczbę dni, mamy stawkę godzinową i mamy policzyć wartość
do wypłaty w każdym dniu roboczym.
Osoba pracowała 8 godzin, a więc mamy nasze dni robocze.
Następnie mamy naszą stawkę godzinową i mnożymy to jeszcze razy 8.
Dlatego, że każdy dzień jest traktowany jako 8 godzin, przeciągamy sobie w dół.
Zwróć uwagę znowu tutaj jakieś formaty.
Pułapka na pułapce.
Wybieramy sobie formatowanie ogólne i to są kwoty do wypłaty tym konkretnym osobom.
To tyle jeżeli chodzi o zadania do tego bloku.
Zapraszam Cię do kolejnego modułu, w którym zajmiemy się funkcjami
warunkowego zliczania.
Bardzo, bardzo ciekawy temat. Do zobaczenia.