Tag: Podstawy

  • Ochrona arkusza i skoroszytu. Zabezpiecz swoją pracę.

    Ochrona arkusza i skoroszytu. Zabezpiecz swoją pracę.

    Ochrona arkusza i skoroszytu. Zabezpiecz swoją pracę.

    Masz to! Po wielu godzinach pracy, przygotowałeś świetny szablon budżetowy. Za chwilę roześlesz go do kolegów, aby wprowadzili do niego dane. Gdy plik wróci do Ciebie, będziesz mógł zająć się dalszymi analizami. O ile będziesz miał szczęście…

    Koledzy mogą mieć swoje pomysły na wypełnienie arkusza. Mogą wprowadzić dane w miejscach i formacie, który kompletnie nie będzie Ci odpowiadał. A jak wiadomo, Polacy to bardzo kreatywny naród 😉

    Jednak jest na to rada! Musisz się zabezpieczyć…a raczej zabezpieczyć efekty swojej pracy. Na przykład używając takich narzędzi jak ochrona arkusza czy ochrona skoroszytu.

    Ochrona arkusza. Uzbrojeni ochroniarze

    Ochrona arkusza: przygotowania

    Aby zrozumieć jak działa ochrona arkusza, kluczowym jest, abyś zrozumiał jak działa właściwość ZABLOKUJ na karcie OCHRONA, w menu FORMATOWANIE KOMÓREK. Każda z komórek może mieć jedną z dwóch właściwości: może być zablokowana lub niezablokowana.

    Ochrona arkusza. Blokowanie komórek na karcie Ochrona.

    Jeżeli zaznaczysz „ptaszek” – czyli zabezpieczysz komórkę – to spowodujesz, że ta komórka będzie „reagowała” na ustawienia ochrony arkusza jakie wprowadzisz.

    W przeciwnym przypadku – braku zaznaczonego „ptaszka” – komórka nie będzie zablokowana, co oznacza że żadne ustawienia ochrony arkusza nie będą na nią wpływały. Innymi słowy – każdy będzie mógł w dowolny sposób modyfikować tę komórkę – nawet jeżeli wprowadzisz ustawienia ochrony arkusza.

    Standardowe ustawienie każdej komórki w nowo tworzonym arkuszu, wygląda jak na powyższym obrazku. Komórka jest zablokowana, ale nie ukryta.

    No właśnie. Opcja UKRYJ pozwoli Ci na ukrycie formuły w danej komórce. Jednak podobnie jak w przypadku ZABLOKUJ, opcja będzie skuteczna jeżeli zastosujesz ochronę arkusza. Wartość komórki (wynik formuły) pozostaje oczywiście widoczna.

    Przygotowanie Twojego arkusza będzie zatem polegało na:

    • pozostawieniu opcji ZABLOKUJ dla wszystkich komórek, które mają być chronione przed modyfikacją,
    • odznaczeniu (zdjęciu ochrony) opcji ZABLOKUJ dla wszystkich komórek, które mogą być modyfikowane, pomimo wprowadzenia ochrony arkusza.

    Tak przygotowany arkusz działa jak prosty formularz: można wpisywać dane tylko w odblokowane komórki.

    Ochrona arkusza: opcje

    Jeżeli odpowiednio zmodyfikowałeś właściwości komórek, możesz wprowadzić właściwą ochronę arkusza. Odpowiednią opcję znajdziesz na wstążce RECENZJA w sekcji ZMIANY.

    Opcja chroń arkusz na wstążce Recenzja.

    Po kliknięciu w CHROŃ ARKUSZ, zobaczysz nowe okno dialogowe, gdzie możesz zdecydować o:

    • haśle, które będzie chroniło przed zmianami w ustawieniach ochrony (pozostawienie pustego pola spowoduje, że arkusz będzie chroniony ale będzie można wyłączyć ochronę bez podawania hasła),
    • sposobie w jaki arkusz ma być chroniony, czyli jakie modyfikacje arkusza (zablokowanych komórek) będą możliwe, a jakie nie.
    Okno dialogowe "Chronienie arkusza"

    Poniżej znajdziesz wszystkie opcje z których możesz skorzystać, z krótkim wyjaśnieniem jak działają. Pamiętaj, że zaznaczając „ptaszkiem” przy danej opcji pozwalasz użytkownikowi na daną czynność. Dalszy opis będzie dotyczył sytuacji, kiedy każda z opcji jest odznaczona.

    1. Zaznaczanie zablokowanych komórek – użytkownik nie będzie mógł zaznaczać / aktywować komórek ze statusem zablokowana.
    2. Zaznaczanie odblokowanych komórek – użytkownik nie będzie mógł zaznaczać / aktywować także komórek odblokowanych.
    3. Formatowanie komórek – nie będzie możliwe jakiekolwiek formatowanie komórek, w tym formatowanie warunkowe.
    4. Formatowanie kolumn i wierszy – nie będzie formatowanie kolumn i wierszy, np. zmiana szerokości, wysokości czy ukrywanie.
    5. Wstawianie kolumn i wierszy – nie będzie możliwe.
    6. Wstawianie hiperłączy – nie będzie można wstawić hiperłącza, nawet do niezablokowanych komórek.
    7. Usuwanie kolumn i wierszy – nie będzie możliwe.
    8. Sortowanie – nie będzie możliwe, także w przypadku sortowania zakresów z zablokowanymi komórkami – nawet jeżeli opcja będzie zaznaczona.
    9. Używanie Autofiltru – zmiana filtru nie będzie możliwa oraz nie można zastosować opcji Autofiltru, nawet jeżeli opcja będzie zaznaczona.
    10. Używanie raportów w formie tabeli przestawnej – nie będzie możliwa zmiana i odświeżanie istniejących tabel przestawnych, ani tworzenie nowych.
    11. Edytowanie obiektów – nie będzie możliwa zmiana takich elementów jak: mapy, wykresy, kształty, kontrolki. Nie będzie można dodać lub edytować komentarzy.
    12. Edytowanie scenariuszy – nie będzie możliwa edycja i usuwanie scenariuszy.

    Ochrona skoroszytu

    Kolejną ważną opcją ochrony jest ochrona skoroszytu. Przy czym, jest to opcja mniej skomplikowana. Odpowiednie polecenie znajduje się również na karcie RECENZJA, w sekcji ZMIANY.

    Opcja chroń skoroszyt.

    Ochrona skoroszytu pozwoli Ci zabezpieczyć plik w taki sposób, że na arkuszu nie będą możliwe takie operacje jak: dodawanie nowych arkuszy, usuwanie istniejących, zmiana ich nazwy czy też wyświetlanie ukrytych arkuszy.

    Jak zdjąć ochronę arkusza z hasłem

    No cóż…najlepiej znać hasło 🙂 W takim przypadku, po prostu wybierasz opcję NIE CHROŃ ARKUSZA na karcie RECENZJA, wpisujesz hasło i voilà!

    Co jednak, gdy zapomniałeś hasła? Możesz skorzystać z metody opisanej poniżej. Musisz wykorzystać do tego zewnętrzne narzędzie, która pozwala na otwieranie plików zip. Przykładowo, może to być darmowy program 7-Zip

    Metoda pozwala dostać się do struktury plików Excela i usunąć fragment dotyczący ochrony arkusza. Do tej operacji wystarczy Ci zwykły windowsowy Notatnik. Musisz poszukiwać fragmentu, który zaczyna się tak: ” <sheetProtection algorithmName= ….>”

    .

    Podsumowanie

    Mam nadzieję, że teraz Twoje narzędzia będą lepiej chronione przed przypadkowymi zmianami i kreatywnym podejściem kolegów 😉 Warto wykorzystywać opisane funkcjonalności, ponieważ przy dużej ilości danych i plików łatwo przeoczyć przypadkowy błąd, który przecież może mieć fatalne skutki dla Ciebie i firmy.

    Z drugiej strony, w sytuacji kiedy zapomnisz hasła – nie pozostajesz bezradny. Metoda opisana wyżej powinna pomóc. Nie jest najprostsza, więc jeżeli będziesz miał kłopoty – napisz w komentarzu.

  • Podstawy VBA. Od czego zacząć?

    Podstawy VBA. Od czego zacząć?

    Podstawy VBA

    Czy VBA jestem świętym Graalem, rozwiązującym wszystkie problemy jakie masz w Excelu? Najprostsza odpowiedź brzmi NIE. Ale jednocześnie, dzięki znajomości VBA, z pewnością możesz:

    • robić rzeczy jakich nie zrobiłbyś bez tej wiedzy – zyskujesz nowe możliwości,
    • automatyzować pracę swoją i innych – zyskujesz czas.
    Podstawy VBA. Kobieta pisząca kod na laptopie.

    Tym artykułem rozpoczynam cykl, którym zamierzam w uporządkowany sposób przekazać Ci podstawy VBA. Będę w 100% szczery. Nie jestem mistrzem VBA. Ale pomyślałem, że to może być zaleta. Nie będę mówił do Ciebie jak programista i być może dzięki temu ta wiedza będzie bardziej zjadliwa 😉

    Zanim przejdziesz dalej, przeczytaj mój artykuł o rejestrowaniu makr. Dzięki temu zyskasz wiedzę jak:

    • dodać na wstążce kartę DEVELOPER, która zbiera polecenia związane z makrami i VBA,
    • nagrać makro,
    • uruchomić edytor VBA i modyfikować zapisany przez rejestrator kod.

    To będzie dobry wstęp, aby ruszyć z dalszą nauką. Takie podstawy podstaw VBA 😉

    VBA, czyli manipulowanie obiektami

    Visual Basic for Applications (VBA) to język programowania, który pozwala na automatyzację pracy z dokumentami w programach Microsoft Office. Kod VBA będzie zapisany w pliku dokumentu MS Office, np. w arkuszu Excela. To oznacza, że aby wywołać działanie takiego programu potrzebujesz najpierw uruchomić dany program. W naszym przypadku – Excel.

    VBA wykorzystuje tzw. model obiektowy. Poprzez pisanie kodu VBA zazwyczaj rozumiemy manipulowanie obiektami. Obiekty mają właściwości i metody, za pomocą których wykonujesz działania na tychże obiektach. I jest to w zasadzie klucz do zrozumienia całego języka. No dobra, ale co to właściwie oznacza?

    Posłużę się analogią. Weźmy samochód. Samochód, ma: kolor, wielkość, określony silnik, pojemność i wiek. To właściwości samochodu. Samochód możesz także: uruchomić, ruszyć do przodu, zahamować…etc. – to metody jakich możesz użyć.

    Przełóżmy to na język Excela. Jeżeli obiektem będzie pojedyncza komórka (samochód) to jej właściwością może być np. adres (A1), a metodą – wyczyszczenie zawartości (Clear)

    Obiekty mogą występować w relacjach, tworząc hierarchię. I znowu posłużmy się analogią. Weźmy dom (obiekt podstawowy). Pojedynczy dom jest częścią osiedla (kolekcja) Natomiast osiedla są częścią dzielnicy, a dzielnice – miasta. Mamy więc kolekcję domów, osiedli i w końcu dzielnic.

    W Excel VBA mamy na przykład zakres (może być pojedyncza komórka), która jest obiektem w skoroszycie (skoroszytów może być więcej i tworzą kolekcję), a ten z kolei jest częścią zeszytu (pliku Excela) I tak, przykładowo w VBA aby odwołać się do zakresu / komórki A1 możesz użyć kodu:

    Workbooks("Zeszyt1").Worksheets("Arkusz1").Range("A1")

    Jak pewnie zauważyłeś odwołanie do kolejnych obiektów następuje z wykorzystaniem „.” (kropki)

    Właściwości vs. metody

    Jeżeli do tej pory wszystko jest dla Ciebie jasne to jesteś na dobrej drodze, aby opanować podstawy VBA 🙂 Sprawę komplikuje trochę fakt, że zarówno właściwości jak i metod używasz tak samo. Co mam na myśli? Spójrz na przykład:

    Range("A1").Address
Range("A1").Select

    Jak widzisz, mamy ten sam obiekt – Range(„A1”) ale raz mówimy o właściwości (Address), a raz o metodzie (Select) Skąd właściwie masz wiedzieć co jest czym? O tym nieco później.

    Podstawy VBA: Właściwości

    Właściwości pojawiają się na końcu hierarchii obiektów. Przykładowo:

     Workbooks("Zeszyt1").Worksheets("Arkusz1").Range("A1").Value

    To pewnie zamiesza Ci trochę w głowie ale właściwości mogą także zwracać…obiekty.

    Część właściwości możesz tylko odczytać, a część zarówno odczytać jak i zapisać. Na przykład Address to właściwość, którą możesz tylko odczytać. To dość logiczne, bo przecież nie możesz zmienić adresu komórki A1.

    Z kolei Value, to przykład właściwości którą możesz zarówno odczytać, jak i zapisać. I to również jest logiczne, bo przecież możesz zarówno odczytać zawartość jakieś komórki, jak i zmienić jej wartość.

    Podstawy VBA: metody

    Co musisz wiedzieć o metodach? Na początku dwie rzeczy. Po pierwsze metoda może zmienić właściwość. Po drugie część metod, zawiera dodatkowe informacje, które precyzują jej działanie. To argumenty metody.

    Przykładowo metoda Clear, może zmienić wartość (Value) komórki, czyli jej właściwość.

    Range("A1").Clear

    Z kolei metoda PasteSpecial (wklej specjalnie) ma 4 dodatkowe argumenty / parametry: Paste, Operation, SkipBlanks, Transponse. Przykładowo możesz zapisać te dodatkowe parametry w metodzie tak:

    Range("B1").PasteSpecial xlPasteValues

    ale również tak:

    Range("B1").PasteSpecial Paste:=xlPasteValues

    Jak widzisz w pierwszym przypadku, od razu podajesz wartość parametru (xlPasteValues, oznacza tu wklejenie wartości), a w drugim następuje przypisanie do danego argumentu za pomocą znaków „:=”.

    Podstawy VBA: procedura SUB

    W zasadzie jesteś gotowy aby napisać swój pierwszy kod. I zanim zabierzemy się do rzeczy, dwa słowa wprowadzenia.

    Zazwyczaj (nie zawsze) kod wpisujesz w procedurze SUB. Wygląda to tak:

    Sub JakasNazwa()
...
 Twoj kod
...
End Sub

    Procedura Sub to po prostu logiczny zestaw poleceń, który wykonuje jakieś działanie lub zestaw działań. Procedury umieszczasz w modułach (Module) ale to od Ciebie zależy, czy umieścisz wszystkie procedury w jednym module czy rozdzielisz na kilka. Na razie nie musisz się tym przejmować.

    Nie będziemy robić nic skomplikowanego i wystarczy nam jedna procedura i jeden moduł. Nasza procedura ma za zadanie skopiować zawartość komórki A1 i wkleić ją jako wartość (bez formuły) do komórki B1.

    Co musisz zrobić, aby to osiągnąć?

    • utwórz nowy plik, a następnie w komórce wpisz dowolną formułę. Może być =2+2 😉
    • uruchom edytor VBA (skrót ALT + F11),
    • po lewej stronie, w oknie Project – VBA Project odnajdź nazwę swojego pliku (zapewne Zeszyt1),
    • kliknij prawym przyciskiem myszy na tę nazwę (VBA Project – Zeszyt1) i wybierz INSERT > MODULE,
    • w środkowej części ekranu pojawi się puste okno, gdzie możesz zacząć pisać kod.
    Podstawy VBA. Wstawianie modułu.

    Podstawy VBA: procedura SUB (przykład)

    Sub KopiujWartosci()
 Range("A1").Copy
 Range("B1").PasteSpecial Paste:=xlPasteValues
End Sub

    Po wpisaniu kodu, uruchamiasz go skrótem F5. Odpowiednią opcję znajdziesz również na pasku narzędzi.

    Podstawy VBA. Pasek narzędzi z poleceniem Uruchom.

    Jeżeli będziesz chciał zapisać plik z napisanym właśnie kodem, musisz to zrobić wybierając odpowiedni format pliku – skoroszyt programu Excel z obsługą makr (rozszerzenie pliku .xlsm)

    Prześledźmy wspólnie kod linijka po linijce. 

     Sub KopiujWartosci()

    Zaczynamy od zdefiniowania procedury – słowo kluczowe Sub oraz nazwy KopiujWartosci. Całość kończy się nawiasami. Na marginesie, pomiędzy nawiasami będziesz mógł umieszczać argumenty i przekazywać wyniki z / do innych procedur. Ale póki co – po prostu zostawiamy otwarty i zamknięty nawias. To konieczne dla poprawności składni.

     Range("A1").Copy

    Dalej mamy zdefiniowany obiekt Range(„A1”), czyli po prostu komórka A1. Skąd wiadomo z którego arkusza / pliku to będzie komórka? Ponieważ nie definiujemy tego wcześniej (przed Range(„A1”)) będzie to komórka A1 aktywnego arkusza – w momencie uruchomienia kodu.

    Następnie, po kropce definiujemy metodę – Copy, czyli po prostu skopiowanie zawartości komórki.

      Range("B1").PasteSpecial Paste:=xlPasteValues

    Kolejna linijka, to znowu obiekt – Range(„B1”) oraz metoda PasteSpecial (wklej specjalnie) Dalej korzystamy z parametru xlPasteValues, aby wkleić tylko wartości.

    End Sub

    Całą procedurę musimy zakończyć słowami End Sub, co jest wymaganą składnią dla poprawności działania całej procedury.

    .

    Podsumowanie

    Zaryzykuję stwierdzenie, że VBA sprowadza się do manipulowania obiektami: komórkami, zakresami, wykresami, tabelami etc. Przy czym możesz wykorzystywać przy tym elementy charakterystyczne dla „klasycznych” języków programowania takich, jak: pętle, instrukcje warunkowe czy zmienne.

    Jeżeli zrozumiesz na czym polega hierarchiczność obiektów (np. komórka w arkuszu, arkusz w skoroszycie) oraz zapamiętasz najważniejsze pojęcia: obiekt, właściwość, metoda to będzie dobry początek aby szybko przyswajać pozostałe elementy VBA.

    Mam nadzieję, że wspólnie ze mną 😉 Daj znać w komentarzu, jeżeli masz jakieś pytania albo chciałbyś się podzielić swoimi doświadczeniami w nauce VBA.

  • POWER QUERY, czyli jak zyskać masę czasu

    POWER QUERY, czyli jak zyskać masę czasu

    POWER QUERY, czyli jak zyskać masę czasu

    POWER QUERY (PQ), wraz z POWER PIVOT i POWER VIEW to prawdopodobnie najbardziej rewolucyjne narzędzie od czasu tabel przestawnych. Dlaczego? Ponieważ daje Ci, prosty sposób na: pobranie danych z wielu różnych źródeł, przekształcenie ich w pożądany sposób i wysłanie do arkusza, gdzie możesz je dalej analizować.

    To co czyni to narzędzie tak potężnym jest fakt, że dzięki niemu możesz robić rzeczy, które wcześniej często były zarezerwowane dla zaawansowanych użytkowników VBA. Co więcej jest to bardzo proste. Podstawową obsługę POWER QUERY „ogarniesz” w kilka godzin.

    Power Query - jak zyskać masę czasu. Mężczyzna trzymający zegar.

    Co to jest POWER QUERY?

    Fachowa definicja powie, że POWER QUERY to tzw. narzędzie ETL (z ang. Extract, Transform, Load – czyli wyciągnij, przekształć i załaduj)

    Jeżeli mam to przełożyć na bardziej praktyczny i prosty język…posłużę się przykładem. Wyobraź sobie, że musisz zrobić analizę danych sprzedażowych. Dane źródłowe o dziennych wartościach sprzedaży są dostępne na stronie www, informacje o klientach masz w bazie Access, a szczegółowy opis produktów w dziesiątkach plików Excel.

    Żeby było ciekawiej wszystkie te dane są w różnym układzie, często zawierają nadmiarowe spacje i wiersze. Słowem jeden wielki chaos. Znasz to?

    PQ, pozwoli Ci pobrać dane z tych źródeł (Extract) Następnie oczyścisz je z nadmiarowych informacji i uporządkujesz w pożądaną strukturę (Transform) To, z kolei pozwoli Ci wysłać jedną lub wiele tabel do dalszej analizy, np. za pomocą POWER PIVOT (Load)

    Co więcej raz wykonana praca, nie będzie musiała być powtarzana. Gdy kolega zapisze kolejny plik z opisem kolejnego produktu – PQ będzie wiedział, co ma z nim zrobić i niejako automatycznie dołączy do pierwotnego zestawu danych. Prawda, że piękne? 😉

    Gdzie ja mam POWER QUERY?

    Myślę, że zachęciłem Cię do dalszego zgłębiania tajników tego dodatku. No właśnie – dodatku, bo dla Excela 2010 i 2013 POWER QUERY jest dostępny w formie dodatku, który instalujesz jak każdy program. W wersji 2016 PQ jest już „wbudowany” w podstawową wersję.

    Po zainstalowaniu, w wersji 2010 / 2013, na wstążce zobaczysz nową kartę – POWER QUERY. Ale nie w wersji 2016. Ta funkcjonalność została włączona do karty DANE jako POBIERANIE I PRZEKSZTAŁCANIE DANYCH. Dalszy opis dotyczy dodatku w wersji 2010 / 2013 ale nie powinieneś mieć kłopotu z odczytaniem o czym piszę, jeżeli dysponujesz nowszą wersją.

    Automatyczna aktualizacja danych

    Na początku wspomniałem, że za pomocą PQ możesz pobrać dane z wielu źródeł. Możliwości jest naprawdę mnóstwo. Zobacz sam:

    Power Query - pobieranie danych z wielu różnych źródeł.

    Zakładam, że to Twoja pierwsza styczność z PQ dlatego na potrzeby artykułu przejdziemy przez dość prosty przykład. Powiedzmy, że każdego dnia koledzy z trzech różnych regionów przesyłają Ci dzienne zestawienia sprzedaży. Do tej pory ręcznie kopiowałeś dane z tych plików do swojej zbiorczej bazy, gdzie wykonywałeś dalsze analizy.

    Dzięki PQ to samo osiągniesz automatycznie. Oznacza to, że każdego dnia, po otrzymaniu danych będziesz musiał zrobić tylko dwie operacje:

    • zapisać je w odpowiednim miejscu (folderze)
    • kliknąć przycisk odśwież 😉

    Pobierz dane

    Przećwiczmy to razem. Pobierz te dane i zapisz je w dowolnym folderze na dysku. Następnie utwórz nowy zeszyt (plik Excela) i przejdź na kartę POWER QUERY (wersja 2010/2013) lub DANE (w późniejszych wersjach). Tam odnajdujemy polecenie, które znajduje się w sekcji POBIERZ DANE ZEWNĘTRZNE > Z PLIKU > Z FOLDERU.

    W pierwszym kroku, będziesz musiał wskazać ścieżkę do folderu, w którym zapisałeś dane pochodzące z regionów. Zobaczysz okienko, które będzie przedstawiało zawartość folderu z kilkoma dodatkowymi informacjami. Następnie kliknij w przycisk EDYTUJ. Tutaj mała ale ważna dygresja.

    Na tym etapie pojawił się edytor zapytań. Edytor rejestruje wszystkie Twoje kroki i pozwala na je późniejsze odtworzenie, usunięcie jak i edycję. Możesz też samodzielnie konstruować zapytania lub edytować istniejące za pomocą języka M. Oczywiście to trudniejsze ale daje pełną kontrolę i elastyczność. Jeżeli tworzyłeś kiedyś makra, to trochę przypomina to właśnie rejestrację makr vs. pisanie kodu VBA.

    Interesujące nas dane znajdują się w pierwszej kolumnie CONTENT. Z pewnością zauważyłeś ikonę z dwoma małymi strzałkami, które wskazują na możliwość rozwinięcia zawartości. Zanim to zrobimy, pozbędziemy się reszty kolumn. Nie będą nam dalej potrzebne.

    Wybierz kolumnę CONTENT, a następnie kliknij prawym przyciskiem myszy i wybierz polecenie USUŃ POZOSTAŁE KOLUMNY. Teraz możesz już kliknąć w ikonę ze strzałkami. Pojawi się kolejne okno dialogowe, gdzie będzie odzwierciedlona struktura przykładowego pliku z danymi. Ponieważ każdy plik z danymi z regionów, zawierał tabelę (TABELA 1) – wybierzemy właśnie ją jako źródło danych.

    Po chwili zobaczysz wszystkie dane (z 3 plików) połączone w oknie edytora. Możesz się o tym przekonać klikając w filtr na kolumnie region i wybierając ZAŁADUJ WIĘCEJ.

    Na pewno zauważyłeś, że wszystkie dotychczasowe kroki były rejestrowane w kolumnie po prawej stronie ekranu. To jedna z wielu korzyści jakie niesie ze sobą PQ. Możesz cofać się do dowolnego kroku, modyfikować go czy usuwać. Ale przede wszystkim PQ zapamiętuje wszystkie operacje i jest w stanie je odtworzyć, jeżeli do folderu dodasz kolejne pliki. Ale o tym na końcu.

    Oczyść i przekształć dane

    Załóżmy, że aby nasze połączone dane były jeszcze bardziej przydatne do analizy potrzebujemy zrobić jeszcze trzy rzeczy:

    1. chcemy aby nazwa klienta była pisana wielkimi literami,
    2. nie potrzebujemy kolumn „Przedstawiciel Id” i „Klient Id”,
    3. chcielibyśmy mieć dodatkową kolumnę, która pokażą nam tylko miesiąc sprzedaży.

    Przekształć na wielkie litery

    Kliknij w kolumnę „Klient”, a następnie z karty PRZEKSZTAŁĆ wybierz polecenie FORMAT > WIELKIE LITERY.

    Usuwanie niepotrzebnych kolumn

    Zaznacz kolumny „Przedstawiciel Id” i „Klient Id”, a następnie kliknij prawym przyciskiem myszy i wybierz polecenie USUŃ KOLUMNY.

    Dodaj nową kolumnę

    Chcemy mieć miesiąc sprzedaży w osobnej kolumnie i uzyskamy go z kolumny „Data”. Pokażę Ci sposób, który umożliwia później dowolną zmianę nazwy nagłówka tej kolumny. Kliknij na kolumnę „Data”, a później wybierz polecenie DUPLIKUJ KOLUMNĘ z karty DODAJ KOLUMNĘ (to samo polecenie znajdziesz w menu kontekstowym)

    Jak sama nazwa polecenia wskazywała utworzyłeś duplikat kolumny „Data”. Teraz na karcie PRZEKSZTAŁĆ wybierz polecenie DATA > MIESIĄC > MIESIĄC. Dodatkowo możesz zmienić nazwę tej kolumny klikając dwukrotnie na nazwę nagłówka i wpisując np. „Miesiąc sprzedaży”.

    Załaduj do Excela

    Ostatnia faza działań to załadowanie pobranych i oczyszczonych danych z powrotem do arkusza. Możesz to zrobić przesyłając dane bezpośrednio do tabeli danych lub wybierając polecenie UTWÓRZ TYLKO POŁĄCZENIE.

    To spowoduje, że dane nie będą załadowane bezpośrednio do arkusza. Powstanie natomiast połączenie, które będziesz mógł później wykorzystać np. podając je w źródle do tabeli przestawnej.

    Z takiego połączenia zbudujesz na przykład tabelę przestawną na danych ze wszystkich plików naraz.

    Jeżeli powyższe ćwiczenie nie przekonało Cię do Power Query, to spróbuj zrobić jeszcze jedną rzecz. Zapisz i zamknij plik na którym pracowałeś. Następnie przejdź do dowolnego pliku z danymi źródłowymi i dodaj kolejne rekordy albo zmodyfikuj istniejące. Zamknij i zapisz, a następnie ponownie otwórz plik, w którym przygotowywałeś zapytania PQ.

    Wybierz polecenie ODŚWIEŻ, z karty DANE. Jak widzisz wszystko zostało odświeżone! Co więcej, gdy dojdzie kolejny region i umieścisz w folderze źródłowym plik w takim samym formacie jak poprzednie – zadzieje się dokładnie to samo. Dane z nowego regionu zostaną automatycznie zaczytane! Powiedz sam, czy to nie magia? 😉

    .

    Podsumowanie

    Ten artykuł to tylko namiastka tego co potrafi Power Query. Mam jednak nadzieję, że zachęciłem Cię na tyle iż podobnie jak ja staniesz się wielkim entuzjastą Power Query. W połączeniu z Power Pivot, dostajesz absolutnie potężne narzędzia do pracy i analizy na dużej ilości, niekoniecznie jednorodnych danych. Daj proszę znać w komentarzu, czy używałeś już PQ i jakie są Twoje doświadczenia.

  • Rozmowa kwalifikacyjna – test z Excela

    Rozmowa kwalifikacyjna – test z Excela

    Co mogą (ę) sprawdzać na rozmowie kwalifikacyjnej, czyli test z Excela.

    W sieci znajdziesz niejeden artykuł i poradę jak przygotować się do rozmowy kwalifikacyjnej. W tym także test z Excela. Mój poradnik będzie jednak wyjątkowy…. bo opiszę Ci co ja bym sprawdzał, gdybym dzisiaj rekrutował do swojego zespołu.

    To co musisz wiedzieć od samego początku to to, że nie będzie to teoria ale sama praktyka. Jestem menedżerem i część mojego zespołu zajmuje się przygotowaniem i analizą danych w Excelu. Otrzymasz więc bardzo praktyczną wiedzę na temat tego jak przygotować się do rozmowy, do działu który zajmuje się „takimi rzeczami” 😉 Co więcej, udostępnię Ci swój test z Excela.

    Rozmowa kwalifikacyjna - test z Excela. Kobiety w trakcie rozmowy.

    Skala umiejętności

    Testując Twoje excelowe umiejętności miałbym w głowie pewną skalę oczekiwań:

    • Umiejętności podstawowe – coś co musisz wiedzieć na pewno, takie must have jak mawiają Słowianie 😉
    • Umiejętności „mile widziane” – jak to umiesz to zyskujesz ekstra punkty, które pomogą Ci pozytywnie zakończyć rozmowę, nawet jeżeli gdzie indziej coś nie poszło.
    • „Wow, tego się nie spodziewałem” – jesteś prawie przyjęty / -a do pracy o ile po ludzku się dogadamy 😉

    W kolejnych akapitach napiszę co konkretnie wchodzi w każdy z poziomów.

    Umiejętności podstawowe

    Moim zdaniem są cztery podstawowe umiejętności jakie po prostu musisz mieć startując do dowolnego działu, który ma cokolwiek wspólnego z analityką:

    Większość testów jakie robiłem, albo widziałem zawsze wykorzystywała te umiejętności.

    Dwie kobiety w trakcie rozmowy kwalifikacyjnej. . Test z Excela.

    Wiedza o tym jak adresować jest kluczowa dla każdej pozostałej umiejętności. Spróbuj nie popełnić błędu w WYSZUKAJ.PIONOWO adresując źródło danych jako zakres względny. Podobnie, z tworzeniem formuł, które potem kopiujesz, wklejasz i działają dla wielu różnych zakresów. To też nie będzie możliwe bez wiedzy o tym jak wykorzystać adres bezwzględny czy mieszany.

    Funkcja JEŻELI to podstawa do budowania dowolnych warunków i sterowania działaniem Twoich arkuszy. Obojętnie czy będzie chodziło o sprawdzenie czy budżet został zrealizowany i należy się premia, czy dowolny inny warunek typu: „JEŻELI spełniony jest warunek” – daj WYNIK 1, a w przeciwnym razie WYNIK 2.

    WYSZUKAJ PIONOWO, niekwestionowana królowa wszystkich funkcji… no przynajmniej do niedawna. Weź dowolną firmę. Zbierz wszystkie pliki Excela, jakich kiedykolwiek użyto i policz która funkcja wystąpiła najczęściej. Daje…sobie paznokcie uciąć ;-), że WYSZUKAJ PIONOWO pozostawi w tyle wszystkie inne kandydatki.

    I w końcu tabele przestawne. To one są bardzo elastycznym i szybkim narzędziem do kalkulacji danych z wieloma warunkami w tle. Szef pyta o wartość sprzedaży w regionie centralnym, dla przedstawiciela Nowaka w okresie od stycznia do czerwca? Trzy ruchy i kliknięcia myszką i masz gotową odpowiedź. A szybkość w firmie ma znaczenie…!

    Kobieta skacząca na tel zachodzącego słońca. Rozmowa kwalifikacyjna do nowej pracy. Test z Excela.

    Umiejętności mile widziane

    No dobrze, załóżmy że powyższe funkcjonalności i narzędzia doskonale znasz. Co możesz zrobić, aby wywrzeć wrażenie i zwiększyć swoje szanse na zatrudnienie?

    Odpowiedzią jest znajomość dodatków POWER, w szczególności POWER QUERY i POWER PIVOT. Dlaczego warto je znać?

    Dzięki znajomości POWER QUERY, będziesz w stanie:

    • szybko wyciągnąć dane z różnych źródeł, np. plików Excel, plików tekstowych czy baz danych,
    • oczyścić te dane i odpowiednio przekształcić,
    • zasilić nimi na przykład docelowy raport czy zestawienie.

    Znając POWER PIVOT, będziesz gotowy do budowania złożonych raportów, opartych na tzw. modelu danych i relacjach. Taka umiejętność w połączeniu przykładowo z fragmentatorami, wskaźnikami KPI da Ci potężny arsenał do robienia nieustającego wrażenia na przyszłym szefie 😉

    W wielu miejscach menedżerowie nie mają komfortu używania interaktywnych i przekrojowych raportów. Muszą opierać się na prostych zestawieniach i tabelkach. To Twoja szansa!

    Wow, tego się nie spodziewałem

    VBA, wizualizacja danych, statystyka. Te cztery słowa oddają istotę rzeczy 🙂

    Jeżeli znasz VBA, to znaczy że potrafisz zautomatyzować pracę swoją i kolegów. Co oznacza, że uwalniasz swoje zasoby, czas i mózg na „poważniejsze” rzeczy. Na przykład analizę danych, wyciąganie wniosków i dostarczanie rekomendacji.

    Mężczyzna z kciukiem w górze.

    W szumie informacyjnym trzeba umieć przykuć uwagę. Z potoku liczb wyłowić to co najważniejsze. I przestawić tak, aby odbiorca to natychmiast zobaczył i zrozumiał. Uważam, że to jedna z najważniejszych umiejętności XXI w. Nikt nie ma czasu. Każdy jest przytłoczony nadmiarem danych. Naucz się wyciągać esencję i pokazywać ją w atrakcyjny sposób. Będziesz niezastąpiony!

    Znasz to powiedzenie, że statystycznie ja i mój pies mamy 3 nogi? A jednak statystyka to cenna rzecz jeżeli zajmujesz się liczbami i danymi. Czasami wystarczy dość podstawowa wiedza. Ale liczy się umiejętność wykorzystania tej teoretycznej wiedzy w praktyce.

    Od średniej, mediany, dominanty, odchylenia standardowego przez rachunek prawdopodobieństwa aż do metody najmniejszych kwadratów. Nie ważne, że znasz definicje i wzory. Ważne jak zastosujesz tę wiedzę np. do estymacji jakiegoś zjawiska (sprzedaż, odejścia pracowników etc.)

    Podsumowanie

    Znajomość Excela to dziś tak samo podstawowa umiejętność jak kiedyś obsługa ksero i faxu. Jeżeli nie startujesz do działu analitycznego, pewnie wystarczą umiejętności na poziomie podstawowym lub średnio zaawansowanym.

    W dziale związanym z analizą danych, poziom średni może być poziomem minimum. Ale tak naprawdę liczy się to jak szybko się uczysz i czy masz motywację aby stać się dobrym.

    Nagrodą może okazać się świetlana przyszłość. Wszyscy specjaliści przewidują, że najbliższe lata będą należały do ludzi, którzy potrafią sobie radzić z liczbami.

  • Grupowanie i ukrywanie wierszy i kolumn

    Grupowanie i ukrywanie wierszy i kolumn

    To co niewidzialne dla oczu, czyli grupowanie i ukrywanie wierszy i kolumn

    Chłopiec zakrywający oczy dłońmi

    Zacznę od kwestii oczywistej. Każdy arkusz składa się z wierszy i kolumn. Czasem jest ich całe mnóstwo. Wypełnionych cyferkami tak, że od samego patrzenia może rozboleć głowa. Trudno wtedy zrozumieć co autor chciał nam przekazać. Dojście do jednej kluczowej kolumny wymaga wykonania szeregu obliczeń, które odbiorcę nie interesują – liczy się wynik. Receptą na informacyjny atak migreny jest grupowanie i ukrywanie części wierszy i kolumn.

    Grupowanie i ukrywanie – podstawowe różnice

    Na zasadniczą różnicę pomiędzy tymi dwoma funkcjonalnościami wskazuje ich nazwa. Ukryć, czyli coś schować. Ukrywanie jest przydatne, kiedy nie będziemy często zaglądać do ukrytych danych. Czasem przydaje się także, gdy tylko na chwilę chcemy oczyścić sobie widok z nadmiaru danych.

    Grupowanie to zebranie wierszy i kolumn w logicznie połączone zbiory. Przydaje się w sytuacji, gdy w pierwszej kolumnie znajdują się kategorie, które w następnych kolumnach posiadają swoje przypisania np. w czasie. Nie musimy wtedy przewijać paska widoku, aby zobaczyć co znajduje się w ostatniej kolumnie. Jest też dużo przyjemniejsze w obsłudze niż przykładanie tradycyjnej linijki do ekranu komputera : )

    Ukrywanie wierszy i kolumn

    Jak zastosować każdą z tych funkcjonalności opowiem Wam na przykładzie. Wyobraźmy sobie trzy studentki, które wynajmują wspólnie mieszkanie. W wyniku wielu dyskusji na temat wykonywania obowiązków domowych, wspólnie postanowiły stworzyć w Excelu tabelę podziału obowiązków na każdy miesiąc roku akademickiego. Wstępnie tabela wyglądała tak:

    Mamy dwa rodzaje obowiązków: tygodniowe i miesięczne. Dla każdego z nich istnieje komórka pozwalająca wpisać osobę, która jest odpowiedzialna za wykonanie jednej z czynności (zgodnie z legendą). Po wypełnieniu w ten sposób pierwszych dwóch miesięcy tabela wygląda następująco:

    Gdy widzimy tylko pierwsze trzy miesiące tabela staje się dużo bardziej czytelna. Nasze bohaterki korzystając z harmonogramu czasami będą chciały spojrzeć na obowiązki miesięczne, a czasami zobaczyć rozkład tygodniowy w danym miesiącu. Innym razem mogą spojrzeć na zadania przypisane w danej kategorii.

    Stworzenie takiego układu danych umożliwi nam grupowanie. Ukrywanie zastosujemy w przypadku legendy. Przy trzech osobach korzystających z pliku legenda nie musi być cały czas widoczna. Może się jednak okazać przydatna w przyszłości, gdy zmieni się skład osób wynajmujących mieszkanie.

    Najpierw zajmiemy się ukryciem legendy. Ze względu na to, że te dane są raczej rzadko wyświetlane, ukryjemy pierwsze cztery wiersze arkusza. Jak to zrobić? Musimy zaznaczyć całe wiersze klikając lewym przyciskiem myszy. Po zaznaczeniu klikamy prawym przyciskiem myszy i wybieramy opcję UKRYJ.

    Pierwsze cztery wiersze są ukryte (na pasku z brzegu arkusza, na którym znajdują się numery wierszy, nie mamy już cyfry „1”, a pojawiła się cyfra „5”). Spojrzenie na oznaczenie numeracji wiersza lub kolumny to jeden ze sposób, aby na szybko sprawdzić czy ktoś czegoś przed nami nie ukrył : ) Drugim sposobem jest odkrycie wszystkich ukrytych kolumn i wierszy. Aby to zrobić, zaznaczamy wszystko, a następnie klikamy prawym przyciskiem myszy i wybieramy opcje ODKRYJ.

    W podobny sposób moglibyśmy ukryć poszczególne obowiązki tygodniowe dla danych miesięcy. Jest tylko jedno „ale” – za każdym razem, gdy ktoś będzie chciał spojrzeć na obowiązki tygodniowe, będzie musiał zaznaczyć kolumny z wybranych dwóch miesięcy i kliknąć ODKRYJ.

    Takie rozwiązanie jest ok, ale (zawsze jest jakieś „ale”) nie jest najlepsze, jeśli dziewczyny będą chciały często ukrywać i odkrywać tygodniowe przypisania. Tutaj dużo bardziej sprawdzi się grupowanie. Jak to zrobić?

    Grupowanie wierszy i kolumn

    Zaznaczymy wiersze / kolumny, które chcemy zgrupować, a następnie klikamy GRUPUJ na wstążce DANE w sekcji KONSPEKT. Nad zgrupowanymi danymi pojawi się linia zakończona przyciskiem ze znakiem minus.

    Niestety nie ma możliwości zgrupowania kilku zakresów jednocześnie, więc musimy robić to osobno dla każdego miesiąca. To samo zrobimy z kategoriami sprzątania.

    Następnie zaznaczamy cały arkusz (symbol w prawym górnym roku, zaznaczony na obrazku poniżej czerwoną ramką) albo jeden z zakresów i wybieramy opcję UKRYJ SZCZEGÓŁY.

    Efekt jest następujący.

    Klikając w odpowiedni „plus” możemy zobaczyć dane dla tygodni w pojedynczym miesiącu albo obowiązki znajdujące się w danej kategorii. Musimy jednak pamiętać, że „plusiki” są przesunięte o jeden miesiąc. Spokojnie, nie musi tak zostać : ) Jeśli wydarzy nam się taka wpadka najprościej będzie zmienić etykiety miesięcy (w tym przypadku nie jest to tak duży problem, wystarczy zrobić proste Kopiuj – Wklej i zmienić zakresy w formułach sprawdzających). Warto pamiętać na przyszłość – „plus” pojawi się zawsze o jedną kolumnę / wiersz dalej niż zgrupowany obszar. Jeśli będziemy o tym pamiętać przygotowując tabelę z danymi to zaoszczędzi nam dodatkowej pracy.

    Po rozwinięciu kategorii oraz danych tygodniowych dla dwóch miesięcy będziemy mieć taki widok.

    .

    Podsumowanie

    W ramach podsumowania proponuje abyście cofnęli się do obrazka z początku artykułu i zobaczyli co jest bardziej czytelne oraz funkcjonalne : ) Moim zdaniem, ukrycie części informacji i pogrupowanie danych (jeśli ma to logiczny sens), w dużym stopniu poprawia czytelność arkusza kalkulacyjnego.

    Agnieszka Kmieć

  • Porządkowanie danych – czyli niech zapanuje ład!

    Porządkowanie danych – czyli niech zapanuje ład!

    Porządkowanie (i nie tylko danych) to często żmudny i niezbyt atrakcyjny temat. W dzisiejszym tekście pokażę Wam jakie triki zastosować, aby porządkowanie danych przebiegło szybko i sprawnie.

    Gdzieś na świecie są tacy ludzie, którzy mają porządek. Ich szafy zawsze wyglądają jak z reklamy sklepów meblowych. Bluzki poukładane rodzajami. Wyprasowane koszule poukładane pod sznurek. Do tego wszystko przypomina przepiękną tęcze, w której płynnie jeden kolor przechodzi w drugi. I nie ma tam żadnej bluzki kupionej dziesięć lat temu, której nigdy nie założą.

    Kobieta z mopem

    Ubieranie się przy takiej szafie to pewnie pestka. Teraz wyobraźcie sobie, że arkusz kalkulacyjny to taka szafa, z której chcecie wyciągać nie ciuchy, a dane. Na pewno jest to dużo łatwiejsze, jeśli dane są poukładane i nie ma żadnych zbędnych informacji.

    Na szczęście w Excelu można zastosować kilka trików, które zrobią to automatycznie, więc nie będzie to tak trudne jak przy przypadku szafy z ubraniami. Powiedzmy, że przed porządkami nasza szafa wygląda tak:

    Porządkowanie danych. Tabela z danymi nieuporządkowanymi.

    Sformatuj jako tabelę

    Mając dane wpisane w arkuszu dobrze zacząć od wstawienia tabeli z nagłówkami. Nazwane kolumny z możliwością filtrowania ułatwiają korzystanie z danych. Dodatkowo tabela wymusza od użytkownika zastosowanie stylu, czyli spójnego formatowania danych. Jednym zdaniem: klikamy na klawiaturze CTRL+T i już jest porządniej.

    Porządkowanie danych. Tabela z danymi.

    Porządkowanie pustych komórek

    W następnym kroku pozbędziemy się zbędnych, pustych komórek. Zaczniemy od zaznaczenia wszystkich pustych komórek w naszym zakresie danych: zaznaczamy cały zakres, następnie wstążka NARZĘDZIA GŁÓWNE > SEKCJA EDYTOWANIE > ZNAJDŹ I ZAZNACZ > PRZEJDŹ DO – SPECJALNE. W wyświetlonym oknie zaznaczamy opcję PUSTE. Program zaznaczy nam wszystkie puste komórki. Zostaje już tylko usunięcie wszystkich zaznaczonych komórek klikając CTRL + znak minus na klawiaturze.

    Jeśli nie chcemy usuwać pustych komórek, ale wpisać w nie np. wartość „brak danych” to przydatne będzie funkcja ZNAJDŹ I ZAMIEŃ. Znajduję się ona na wstążce NARZĘDZIA GŁÓWNE w sekcji EDYTOWANIE . Najpierw wyszukujemy wszystkie puste komórki (bądź zawierające inną wartość), a następnie wpisujemy na co ma zostać zamieniona. W naszym przykładzie we wszystkie puste komórki wpisałam słowo „wszystkie”:

    Porządkowanie danych. Znajdź i zamień.

    Posortuj dane

    Teraz posortujemy dane, czyli poukładamy nasze ciuchy ; ) Zaczynamy od zaznaczenia zakresu danych, które mają być posortowane (wyrzucamy wszystko z szafy na podłogę). Potem przejdziemy na wstążkę DANE i klikniemy w przycisk SORTUJ. W wyświetlonym oknie ustawimy porządek jaki chcemy mieć w ciuchach określając kolejne poziomy sortowania.

    Porządkowanie danych. Sortowanie.

    Ponieważ moje dane to dane tekstowe, kolejność ich ułożenia w tabeli ma być zawsze alfabetyczna. Gdyby były to dane liczbowe mogłabym je posortować w kolejności od najmniejszych do największych albo odwrotnie.

    Ustalając poziomy sortowania warto wcześniej dobrze przemyśleć w jakiej kolejności mają być poukładane. W przypadku ciuchów najpierw interesuje mnie na jaką porę roku są przeznaczone, osobno chce mieć ciuchy domowe i wyjściowe, a dopiero na samym końcu poukładane kolorami. Excel zawsze będzie sortować najpierw dane według najwyższego poziomu sortowania, a dopiero później w ramach następnej kategorii, potem w ramach następnej kategorii i tak dalej.

    Porządkowanie danych. Sortowanie wg kategorii.

    Jeśli będę chciała zobaczyć ciuchy w innym ułożeniu to po prostu zmienię kolejność poziomów sortowania. Powiedzmy, że chce zobaczyć wszystkie ciuchy, ale najważniejsze będą dla mnie grupa i kolor. Za pomocą strzałek w oknie sortowania zmienię więc kolejność:

    Sortowanie po poziomach.

    I uzyskam taki efekt:

    Dane posortowane.

    Przemyślane sortowanie bardzo ułatwia czytanie danych. Do pełnego porządku, w przypadku mojej tabeli, pozostało mi ukrycie pierwszej kolumny. W końcu liczba ciuchów nie ma znaczenia ; ) A poważnie – nie wnosi dodatkowej wartości dla osoby, która będzie korzystać z tabeli. Tego typu dane warto poukrywać. Można je też pogrupować i zwinąć zgrupowany zakres za pomocą plusika, który pojawi po zgrupowaniu danych.

    Chcesz darmowy szablon Excel do prowadzenia projektów i śledzenia zadań?

    .

    Usuń zbędne spacje

    Przy robieniu porządków warto się też pozbyć niepotrzebnych spacji w tekście, które mogą się pojawić np. w wyniku błędu przy wpisywaniu danych. Zbędne spacje są częstym problemem, gdy importujemy dane z innych programów do Excela. Można to szybko zrobić za pomocą ZNAJDŹ I ZAMIEŃ. Zaznaczamy zakres komórek i klikamy ZAMIEŃ.

    Porządkowanie danych. Usuwanie zbędnych spacji.

    W polu ZNAJDŹ wystarczy wpisać spacje, a pole ZAMIEŃ NA zostawić puste. Uważaj jednak na przypadki, gdy masz dwa lub więcej wyrazów, ponieważ powyższe działanie usunie również spację pomiędzy wyrazami (np. „krótki rękaw” zamieni się w „krótkirękaw”)

    Ostatnią rzeczą, którą zrobię, to ustawienie szerokość kolumn i wierszy, tak aby dane były bardziej przejrzyste w odbiorze. Zaznaczam interesujący mnie zakres danych, wchodzę na wstążkę NARZĘDZIA GŁÓWNE > SEKCJA KOMÓRKI > FORMATUJ > SZEROKOŚĆ KOMÓRKI / WYSOKOŚĆ WIERSZA.

    Porządkowanie danych. Uporządkowane dane.

    W moim odczuciu te dane czyta się dużo lepiej niż w ich pierwotnej wersji. Szkoda tylko, że w mojej realnej szafie dalej panuje chaos : )

    Agnieszka Kmieć

  • Filtrowanie danych bez tajemnic

    Filtrowanie danych bez tajemnic

    Filtrowanie danych bez tajemnic

    Filtrowanie umożliwia wyświetlenie tylko części danych z tabeli. Robisz to na podstawie określonych za pomocą filtru kryteriów. Kiedy może Ci to pomóc? Przykładów jest wiele:

    • kiedy potrzebujesz podsumować, zliczyć czy wyciągnąć średnią z wybranego zestawu informacji, np. produkty producenta X,
    • gdy potrzebujesz podzielić dane wg określonego typu – np. regionu,
    • wtedy, gdy z szerokiego zestawu danych do dalszych prac chcesz pozostawić wycinek, np. tylko określone stanowiska.

    To tylko część przykładów, kiedy filtrowanie danych może okazać się przydatne.

    Filtrowanie światła przez soczewkę

    Filtrowanie – gdzie ja to mam?

    Narzędzia filtrowania dostępne są na wstążce DANE, w sekcji SORTOWANIE I FILTROWANIE.

    Sekcja Sortowanie i filtrowanie

    Aby filtrować, dane muszą posiadać określony układ. Oznacza to, że muszą być ułożone w kolumnach posiadających nagłówki w pierwszym wierszu tabeli. W celu rozpoczęcia filtrowania zaznacz dowolną komórkę znajdującą się w tabeli danych, a następnie skorzystać z jednej ze ścieżek:

    • Kliknij na przycisk FILTRUJ na wstążce DANE:
    Filtrowanie - filtr prosty

    • Skorzystać ze skrótu klawiaturowego: CTRL + SHIFT + L

    Po włączeniu filtrowania przy każdym nagłówku kolumn pojawi się ikona ze strzałką, za pomocą której można filtrować dane:

    Ikony filtrowania

    Zwróć uwagę na następującą sprawę: jeśli w filtrowanej tabeli będzie występował cały pusty wiersz lub cała pusta kolumna, to filtrowanie obejmie tylko część danych (do pustego wiersza / kolumny). Dzieję się tak dlatego, że Excel domyślnie zaznacza zakres danych „przylegających do siebie”. Po prostu traktuje ten obszar jako jednolity obszar do filtrowania. W takiej sytuacji możesz usunąć pusty wiersz / kolumnę z tabeli, a następnie ponownie zastosować filtrowanie.

    Jak filtrować?

    Po kliknięciu lewym przyciskiem myszy na ikonę filtrowania zobaczysz menu filtrowania:

    Menu filtrowania

    W tym menu istnieje kilka możliwości filtrowania.

    • Zaznacz symbolem „ptaszka” poszczególne wartości znajdujących się w danej kolumnie, np. wybierz tylko „Buraki”:
    Ręczny wybór z menu filtrowania

    Tak przefiltrowana tabela będzie wyglądała następująco:

    Przefiltrowana tabela danych

    • Zaznacz lub odznacz przy opcji ZAZNACZ WSZYSTKO – co spowoduje albo wybór wszystkich wartości, albo odznaczenie wszystkich wartości.
    • Wybierz opcję WYCZYŚĆ FILTR Z…, co spowoduje zdjęcie filtrowania z danej kolumny (tak jak ZAZNACZ WSZYSTKO)
    • FILRUJ WG KOLORU umożliwi Ci wyfiltrowanie tylko wierszy, dla których komórki w danej kolumnie posiadają tło w określonym kolorze. Na przykład gdyby jagody, w poniższym przykładzie posiadały jasnoczerwone tło, to w menu filtrowania, przy tej opcji, pojawi się możliwość filtrowania wierszy, dla których komórki w kategorii Produkty mają jasnoczerwone tło:
    Filtrowanie wg koloru

    Filtrowanie wg koloru komórek

    • Opcja FILTR TEKSTU umożliwi filtrowanie komórek, w których występuje określony tekst.
    Filtrowanie tekstu

    Jeśli filtrowana kolumna zawiera dane liczbowe, to w tym miejscu pojawi się opcja FILTRY LICZBY. Ta opcja umożliwia filtrowanie danych według określonych wartości (np. cena większa od 2 zł):

    Filtrowanie liczb

    • Okno WYSZUKAJ pozwoli Ci wyszukiwać wartości, które mają być wyfiltrowane. Na przykład wpisując tekst „ja” (bez cudzysłowów) podpowiedzą się do wyboru tylko „jabłka” i „jagody”, które posiadają te frazę.
    Wyszukiwanie w filtrowaniu

    W tym przypadku, pojawi się dodatkowa opcja – DODAJ BIEŻĄCE ZAZNACZENIE DO FILTRU. Jeśli ją zaznaczysz to wybrane wartości zostaną dodane do wcześniej zastosowanego filtru w danej kolumnie. Jest to szczególnie przydatne, gdy nie masz jednego wzorca filtrowania. Zamiast tego potrzebujesz wybrać z listy tylko kilka konkretnych przykładów – na przykład konkretnych pracowników.

    Filtrowanie danych może być oczywiście zastosowane dla wielu kolumn. Oznacza to, że po nałożeniu filtru na jedną z kolumn w tabeli, możesz następnie filtrować wartości w kolejnych kolumnach. W ten sposób możesz coraz bardziej zawężać zakres danych.

    Przykładowo, jeśli w kolumnie Kategoria wybierzesz tylko „Warzywa”, to w kolumnie Produkty do filtrowania będą dostępne tylko produkty znajdujące się w kategorii Warzywa:

    Filtr na wielu kolumnach

    Filtrowanie wielu kolumn

    Jeżeli chcesz szybko pozbyć się całego filtrowania z tabeli, na wstążce DANE w sekcji SORTOWANIE I FILTROWANIE wybierz opcję WYCZYŚĆ.

    Czyszczenie filtru

    W celu wyłączenia filtrowania w tabeli, zaznacz jedną z komórek znajdującą się w tabeli danych, a następnie kliknij na ikonę FILTRUJ.

    Podsumowanie

    Poznałeś większość opcji przy tzw. filtrowaniu prostym. Istnieje jeszcze coś takiego jak filtr zaawansowany. Przykładowo, pozwala on na jednoczesne filtrowanie danych z przeniesieniem przefiltrowanego zakresu w inne miejsce arkusza. O tym jednak innym razem.

    Filtrowanie danych w Excelu to prosta ale wyjątkowo przydatna umiejętność. Być może uważasz, że to zbyt prosta sprawa by poświęcać jej czas. Ale może przekona Cię fakt, że filtrowania często używa się także w kodzie VBA, by na przykład szybciej rozdzielić bazę z danymi na wiele różnych plików.

  • Funkcja LUB

    Funkcja LUB

    Funkcja LUB

    Funkcja LUB [Ang: OR] sprawdza podane przez Ciebie warunki i w rezultacie daje wartość PRAWDA albo FAŁSZ.

    Rozdroże. Wybór dróg.

    Jak działa?

    Każdy ze sprawdzanych warunków także daje wartość PRAWDA albo FAŁSZ. Powiedzmy, że warunek – sprawdzamy płeć osoby – wygląda tak:

    =A2="K"

    czyli, sprawdzamy czy w komórce A2, znajduje się litera K, która to reprezentuje kobietę. I tak:

    • Jeżeli w komórce A2 będzie litera K, w wyniku otrzymasz wartość PRAWDA.
    • Jeżeli będzie wartość inna niż K, w wyniku otrzymasz FAŁSZ.

    Z kolei w kolejnej komórce sprawdzamy wiek:

    =B2>30

    co oznacza, sprawdzenie czy dana osoba ma więcej niż 30 lat.

    I podobnie:

    • Jeżeli w komórce B2 będzie wartość większa niż 30, w wyniku otrzymasz wartość PRAWDA.
    • Jeżeli będzie wartość mniejsza lub równa, w wyniku otrzymasz FAŁSZ.

    Funkcję LUB wykorzystujesz właśnie w sytuacji, w której masz do sprawdzenia wiele warunków. Powiedzmy, że chcesz sprawdzić (dość abstrakcyjny) zestaw warunków:

    Szukasz przypadków, kiedy dana osoba jest kobietą lub ma więcej niż 30 lat. Zapiszesz to tak:

    =LUB(A2="K";B2>30)

    Funkcja LUB działa w ten sposób, że jeżeli przynajmniej jeden z warunków jest prawdziwy, to całe wyrażenie jest prawdziwe. A zatem, aby funkcja LUB dała w wyniku FAŁSZ – wszystkie warunki muszą być fałszywe.

    Jak działa funkcja LUB

    Składnia i argumenty

    Składnia jest bardzo prosta. To nazwa funkcji i kolejne warunki. Warunków może być maksymalnie…255.

    =LUB(logiczna1;logiczna2;...;logicznaN)
    =LUB(pierwsza wartość logiczna, którą 
sprawdzasz; druga wartość logiczna;...;
i kolejne)

    Przykład użycia z użyciem funkcji JEŻELI

    Funkcje logiczne, takie jak funkcja LUB bardzo często występują w połączeniu z funkcją JEŻELI. Dlatego, że po sprawdzeniu określonych warunków najczęściej chciałbyś, aby w wyniku tej kontroli coś się wydarzyło – czytaj pojawiło się jakieś inne działanie albo wynik.

    Powiedzmy, że liczysz premie dla handlowców. Warunki premiowe są następujące: każdy dostaje 500 zł premii, jeżeli: sprzedaż (wartość sprzedaży jest w komórce A1) przekroczy 10 000 zł, lub handlowiec pozyska 10 nowych klientów (ilość nowych klientów w komórce A2).

    Wiesz już, jak sprawdzić warunki dzięki funkcji LUB:

    =LUB(A1>10000;A2>10)

    Jednak, aby „wypłacić premię” po spełnieniu minimum jednego z warunków, musisz dodać funkcję JEŻELI i całość będzie wyglądała tak:

    =JEŻELI(LUB(A1>10000;A2>10);500;0)

    Powyższa formuła sprawdza warunek (sprzedaż lub liczba nowych klientów) i jeżeli jest spełniony – daje w wyniku 500, a jeżeli nie to oczywiście 0.

    .

    Podsumowanie

    Funkcje logiczne, takie jak LUB czy ORAZ to „must have” w arsenale każdego adepta Excela. W połączeniu z funkcją JEŻELI pozwalają na „sterowanie” działaniami w zależności od spełnienia określonych warunków.

  • Podstawowe funkcje matematyczne w Excelu

    Podstawowe funkcje matematyczne w Excelu

    Działania matematyczne w Excelu, czyli 8 podstawowych funkcji, które musisz znać.

    Podstawowe funkcje matematyczne….w zasadzie nie ma innej możliwości – korzystając z Excela musisz ich używać. Skoro bowiem, matematyka jest królową nauk – to musisz wiedzieć jak obliczyć iloczyn, procent, średnią czy pierwiastek w Excelu. O tym jak to robić, dowiesz się z tego artykułu.

    Podstawowe funkcje matematyczne w Excelu. Ręka pisząca na tablicy.

    Do dziś z uśmiechem wspominam pewną panią, która na moim szkoleniu z podstaw Excela przyznała:

    „Czasami sprawdzam na kalkulatorze wynik, jaki wychodzi mi w Excelu.”

    Gwarantuję… nie musisz tego robić 😉 Excel to taki większy i lepszy kalkulator. Musisz tylko wiedzieć co robisz.

    Podstawowe funkcje matematyczne mają swoje wymagania.

    Podstawowe funkcje matematyczne w Excelu są w większości bardzo proste. Na pewno jednak musisz znać pewne podstawy. Do nich zaliczam umiejętność adresowania komórek czy zakresów. Musisz także wiedzieć, że w Excelu do obliczeń służą funkcje. Większość funkcji ma argumenty. A każda funkcja zaczyna się od znaku równości =.

    Pewnym wyjątkiem są zupełnie podstawowe działania matematyczne, takie jak:

    • dodawanie +
    • odejmowanie –
    • mnożenie *
    • dzielenie /
    • potęgowanie ^

    W tych przypadkach, nie musisz używać nazwy funkcji (np. SUMA) Możesz po prostu użyć znaków: dodawania (+), odejmowania (-), mnożenia (*), dzielenia (/) i potęgowania (^).

    Zacznijmy właśnie od takich podstaw.

    Dodawanie w Excelu – suma

    Aby dodać do siebie dwie lub więcej wartości (składników), w dowolną komórkę wpisz:

    =10+10

    Zatwierdź wciskając ENTER na klawiaturze.

    Excel tym się różni od kalkulatora (nie sądziłem, że kiedyś napiszę takie zdanie ;-)), że nie musisz używać „sztywnych” wartości. Możesz odwołać się do komórek arkusza. Dzięki temu zmieniając liczby w tych komórkach, będziesz na bieżąco otrzymywał wyniki sumowania (czy innych działań lub funkcji)

    Na przykład możesz chcieć zsumować takie 4 komórki:

    =A1+A2+B1+B2

    Przy większym zakresie danych, z pewnością będziesz chciał użyć funkcji SUMA. Dzięki temu, nie musisz wpisywać adresu każdej pojedynczej komórki.

    =SUMA(A1:E10)
    Podstawowe funkcje matematyczne - sumowanie zakresu

    Odejmowanie w Excelu – różnica

    Odejmowanie działa bardzo podobnie do sumowania. Oczywiście użyjesz innego znaku. Na przykład:

    =A1-A2

    A co jeśli będziesz chciał odjąć od siebie liczby z większego zakresu? Możesz użyć funkcji SUMA. Tak, nie pomyliłem się. Powiedzmy, że chcesz odjąć wartości z zakresu B1:B10 od tych z A1:A10. Zrobisz tak:

    =SUMA(A1:A10)-SUMA(B1:B10)
    Podstawowe funkcje matematyczne - odejmowanie.

    Mnożenie w Excelu – iloczyn

    Analogicznie do poprzednich działań, w przypadku mnożenia możesz użyć znaku „*” (gwiazdka):

    =A1*A2*A3*A4

    W powyższym przykładzie przemnożysz liczby, które znajdują się w komórkach A1, A2, A3, A4. Zamiast za każdym razem używać „*”, możesz przy większej ilości liczb / komórek do przemnożenia użyć funkcji ILOCZYN. W argumentach tej funkcji podajesz zakres liczb jakie mają ulec przemnożeniu:

    =ILOCZYN(A1:A4)
    Działania matematyczne - iloczyn liczb

    Dzielenie w Excelu – iloraz

    „Pamiętaj cholero, nie dziel przez zero” – mawiała moja nauczycielka od matematyki w podstawówce. W odróżnieniu do mnożenia, dzielenie w Excelu nie ma swojego odpowiednika w funkcji. Aby podzielić dwie liczby, użyjesz znaku „/”, np. tak:

    =100/50

    Tak jak poprzednio, możesz używać odwołań do komórek zamiast konkretnych liczb. 

    =A1/A2

    Potęgowanie w Excelu

    Podnieść do potęgi, możesz korzystając z funkcji POTĘGA. Funkcja ma dwa argumenty: liczba, która ma być potęgowana oraz wartość potęgi.

    =POTĘGA(10;2)

    Taki sam efekt osiągniesz, korzystając ze znaku „^”, który reprezentuje to działanie:

    =10^2

    Oczywiście i tutaj możesz użyć odwołania do komórek.

    Działania matematyczne - potęgowanie

    Pierwiastek w Excelu

    Właściwie to – pierwiastek kwadratowy. Aby wyciągnąć pierwiastek użyj funkcji…o zaskoczenie…PIERWIASTEK 🙂

    =PIERWIASTEK(100)

    Jeżeli argumentem będzie liczba ujemna, w wyniku otrzymasz błąd #LICZBA!.

    Reszta z dzielenia

    Możesz się zastanawiać kiedy będziesz miał potrzebę, aby w Excelu liczyć resztę z dzielenia. Spieszę odpowiedzieć, że nie tak często jak liczyć sumę, nie mniej jednak i ta funkcja ma swoje zastosowanie.

    Powiedzmy, że chcesz sprawdzić na dużym zakresie danych, które liczby są parzyste. Jak to zrobić? No właśnie sprawdzając resztę z dzielenia, np. przez dwa 😉

    Funkcją, która liczy resztę z dzielenia jest MOD. Ma dwa argumenty. Pierwszy z nich to liczba, którą dzielisz (w matematyce – to dzielna). Drugi to liczba, przez którą dzielisz (dzielnik)

    =MOD(100;3)

    Z przykładu powyżej otrzymamy wynik równy 1. Dlaczego? Ponieważ 3 razy 33 daje 99… czyli brakuje 1 do 100. Albo inaczej 100 / 3 = 33 ale zostaje reszta…1 😉

    Podstawowe funkcje matematyczne - reszta z dzielenia

    Procenty w Excelu

    Ach…obliczanie procentów. Zaskakująco wiele razy przekonałem się, że wielu z Was ma problemy z procentami. Ups. To źle zabrzmiało 🙂 Ale zupełnie serio, napiszę kiedyś osobny artykuł na ten temat, bo jest tam kilka kwestii, które warto wyjaśnić. Teraz tylko podstawy oraz zagadka. Daj znać w komentarzu jaka jest Twoja odpowiedź.

    Jeżeli cena mieszkania najpierw wzrosła o 10%, a następnie spadła o 10% to aktualna cena mieszkania jest: taka sama, większa czy mniejsza niż przed zmianami?

    Wracając do procentowania, to w Excelu po prostu mnożysz jakąś wartość przez procent, np:

    =A1*10%

    Czyli „wyciągam” 10% z liczby w komórce A1.

    Alternatywnie, jeżeli chcesz sprawdzić jaki procent jednej liczby stanowi inna liczba – wykonujesz dzielenie:

    =10/100

    a następnie możesz zmienić formatowanie komórki z wynikiem na procentowe. „Fabrycznie” bowiem, wynik dostaniesz w liczbach dziesiętnych.

    Podstawowe funkcje matematyczne - procent z liczby
    .

    Podsumowanie

    Podstawowe funkcje, czy też działania matematyczne mają swoje odpowiedniki w Excelu. Jak widzisz nie ma w ich przypadku, niczego bardziej skomplikowanego, co nie wynikałoby z natury samej…matematyki 🙂

    Na pewno musisz pamiętać o kilku podstawach, takich jak: używanie znaku równości przed nazwą funkcji, czy działaniami; rozdzielaniu argumentów funkcji średnikami (w polskiej wersji); umiejętność poprawnego adresowania.

    Jeżeli masz problem z inną funkcją – daj mi znać w komentarzu. Spróbuję pomóc.

  • Jak porównać dane – wykorzystaj znak „=”

    Jak porównać dane – wykorzystaj znak „=”

    Znajdź 3 różnice, czyli jak porównać dane

    Pamiętacie z dzieciństwa zabawę w znajdź trzy różnice? Polegała ona na znalezieniu różnic pomiędzy pozornie takimi samymi obrazkami. Na przykład: brakującym guziku w płaszczu czy różnym kolorze czapki.

    Gdy na samym końcu nie było klucza odpowiedzi to zostawała niepewność czy znaleźliśmy wszystkie różne elementy, które autor zagadki miał na myśli. Czasem można było też znaleźć ich więcej od liczby zadanej w zagadce.

    Dziecko i starsza kobieta.


    Jak porównać dane w arkuszach, kolumnach i komórkach

    Podobnym wyzwaniem jest porównanie ze sobą dwóch pozornie takich samych arkuszy w Excelu. Innym przykładem mogą być dwie kolumny, które powinny zwrócić ten sam wynik, a po zsumowaniu każdej z nich widać, że matma się nie zgadza.

    Znam bardzo prosty sposób na znalezienie winnej komórki w tej sytuacji. I wcale nie wymaga to korzystania ze skomplikowanych funkcji typu INDEX i PODAJ.POZYCJĘ (ufff!).

    Jak to zrobić? Wpisując pomiędzy adres dwóch komórek znak „=”. W odpowiedzi otrzymamy wartość logiczną „PRAWDA” albo „FAŁSZ”. W przypadku drugiej odpowiedzi – znaleźliśmy winnego!

    Na przykład, wyobraźmy sobie dwie osoby prowadzące zapiski z paragonów z księgarni wykorzystujące w tym celu arkusz kalkulacyjny. Oprócz kolumny z kwotą, z paragonu każda z nich ma swoją kolumnę, w której „przyznaje się” do swoich wydatków. W wyniku tego mamy tabelkę:

    Tabela z wydatkami


    Już na pierwszy rzut oka widzimy, że ktoś nie przyznał się do kosztów zwiększenia wartości swojej domowej biblioteki. Możemy analizować miesiąc po miesiącu i sprawdzać kto co ma nowego na półce z książkami. Jednak łatwiej będzie dodać kolumnę, którą nazwiemy „CHECK”. Będzie ona porównywać wynik dodawania wydatków dwóch osób z wartością z kolumny „kwota z paragonu”.

    Tabela z wydatkami - jak porównać dane


    Bingo! Nie zgadza nam się styczeń i kwiecień. Co więcej, w przypadku gdybyśmy mieli bardziej rozbudowaną tabelę np. z 10 000 wierszy, wystarczy skorzystać z filtrowania danych, aby wyłapać same fałsze.

    Filtrowanie danych, które się nie zgadzają


    O czym pamiętać przy porównywaniu danych?

    W ten sam sposób można porównywać dane pomiędzy dwoma dowolnymi komórkami, które nie muszą znajdować się w tych samych Zeszytach czy Arkuszach. Dotyczy to zarówno tekstu, wartości czy wyniku funkcji. Korzystając z tego sposobu warto zwrócić uwagę na następujące przypadki:

    • jeśli porównujemy tekst to wielkość liter nie ma znaczenia – ale literówka w tekście już tak 🙂
    Jak porównać dane - literówki w tekście


    • diabeł tkwi w szczegółach – dwie liczby, nawet jeśli na pierwszy rzut oka wydają się identyczne, mogą zwrócić fałsz – „winny” może kryć się w sposobie wyświetlania danej wartości w komórkach:
    Jak porównać dane - sposób wyświetlania wartości


    żeby nie było za łatwo – jeśli zaokrąglenie wynika z użytej funkcji (np. ZAOKR.DO.CAŁK) to wynikiem będzie PRAWDA:

    Jak porównać dane - zaokrąglanie


    …oczywiście tylko w przypadku, jeśli użyto tej samej funkcji do zaokrąglenia.

    • formatowanie liczby (np. księgowe, procentowe, tekstowe) nie ma w tym przypadku znaczenia:
    Jak porównać dane - formatowanie liczb


    Jeśli „zamienimy” 5,99 na „procentowe” to otrzymamy wartość 599%, ponieważ 5,99 = 599% = PRAWDA. Natomiast gdybyśmy porównali 5,99 z 5,99% to otrzymamy wartość FAŁSZ, ponieważ 5,99% = 0,0599.

    Podsumowanie

    Warto sprawdzać swoje obliczenia. W szczególności dane wpisane ręcznie. Aby nie trwało to zbyt długo i nie było opłacone bólem oczu i karku można skorzystać ze zwykłego znaku „=”. Excel albo nam przyzna rację (zwracając wartość PRAWDA) albo wychwyci błąd (zwracając wartość FAŁSZ).

    Skoro już wiesz jak porównać dane, to od dziś – zamiast wytężać wzrok analizując poszczególne komórki i „skakać” pomiędzy skoroszytami, wyładowując frustracje na biednych klawiszach ALT + TAB – skorzystaj z logiki zaszytej w Excelu.

    Agnieszka Kmieć