Blog

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żą.

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:

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 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”:

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.

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.

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ść:

I uzyskam taki efekt:

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ń?
Chcę otrzymywać newsletter z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach związane z blogiem Excelness.com. Rozumiem i akceptuję regulamin zapisu do newslettera.

Twoje dane osobowe będą przetwarzane w celu obsługi newslettera przez Łukasz Pietrzaka na zasadach opisanych w polityce prywatności. W każdej chwili możesz zrezygnować.

Dziękuję!

Proszę, sprawdź swoją skrzynkę pocztową.

Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
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Ń.

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.

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ć

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
28 maja 2019
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 – gdzie ja to mam?

Narzędzia filtrowania dostępne są na wstążce DANE, w sekcji 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:
- 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:

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:

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”:
Tak przefiltrowana tabela będzie wyglądała następująco:
- 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:
- Opcja FILTR TEKSTU umożliwi filtrowanie komórek, w których występuje określony tekst.
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ł):
- 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ę.
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:

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

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.

POBIERZ PDF Z TOP 20 SKRÓTAMI EXCEL

Dodatkowo zyskaj dwa kupony rabatowe!

Sukces!

Dziękuję. Wkrótce otrzymasz ode mnie maila i obiecane materiały.

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.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
21 maja 2019
Funkcja LUB
Funkcja LUB

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

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.

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.

Czy chcesz bezpłatny test z Excela?

Akceptuję regulamin zapisu do newslettera.

Pobierając za darmo „Test z Excela”, zapisujesz się na nieodpłatny newsletter, zawierający informacje: z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach. Z newslettera możesz zrezygnować w dowolnym momencie.

Jeżeli nie chcesz zapisywać się do newslettera, możesz kupić to narzędzie za 29 zł brutto, pisząc do mnie na adres [email protected]. Szczegóły znajdziesz w regulaminie, który akceptujesz zaznaczając okienko wyboru powyżej.

Dziękuję!

Odbierz e-maila z materiałami. Koniecznie sprawdź zakładki spam, społeczności i oferty, bo moje maile lubią się tam ukrywać. Aby nie przeoczyć niczego w przyszłości, najlepiej jak dodasz mój adres – [email protected]om – do swojej książki adresowej.

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.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
14 maja 2019
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.

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

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 😉

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.

Czy chcesz bezpłatny test z Excela?

Akceptuję regulamin zapisu do newslettera.

Pobierając za darmo „Test z Excela”, zapisujesz się na nieodpłatny newsletter, zawierający informacje: z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach. Z newslettera możesz zrezygnować w dowolnym momencie.

Jeżeli nie chcesz zapisywać się do newslettera, możesz kupić to narzędzie za 29 zł brutto, pisząc do mnie na adres [email protected]. Szczegóły znajdziesz w regulaminie, który akceptujesz zaznaczając okienko wyboru powyżej.

Dziękuję!

Odbierz e-maila z materiałami. Koniecznie sprawdź zakładki spam, społeczności i oferty, bo moje maile lubią się tam ukrywać. Aby nie przeoczyć niczego w przyszłości, najlepiej jak dodasz mój adres – [email protected]om – do swojej książki adresowej.

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.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
7 maja 2019
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.

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ę:

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

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.

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 🙂
- 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:
ż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:

…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:
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ć
23 kwietnia 2019
Wykres interaktywny
Jak zrobić wykres interaktywny – przykład z włączaniem serii danych

Wszyscy wiemy, że mniej znaczy więcej. Ta prawda w szczególny sposób sprawdza się w przypadku wizualizacji danych. Pozostawienie na wykresie tylko najważniejszych danych jest kluczowe dla dobrego odbioru informacji jakie chcemy przekazać.

Często jest jednak tak, że mamy mało miejsca albo z innych powodów na jednym wykresie musimy pokazać dużą ilość informacji. W takich przypadkach można wykorzystać jedną z możliwości prezentowania danych – wykres interaktywny.

W tym artykule pokażę Ci prosty sposób, dzięki któremu umożliwisz końcowemu odbiorcy włączanie, bądź wyłączanie wybranych informacji na wykresie.

Wykres interaktywny: od czego zacząć

Zacznijmy od tego, że na jednym wykresie chcemy prezentować sprzedaż, zysk i liczbę klientów na przestrzeni lat. Dane wyglądają tak:

Dane o sprzedaży i zysku pokażemy jako wykres kolumnowy, a liczbę klientów w postaci wykresu liniowego. Jednocześnie chcemy umożliwić końcowemu odbiorcy możliwość zobaczenia: jednej, dwóch lub trzech informacji na raz. To znaczy, że musimy przygotować mechanizm, który pozwoli wyłączać i włączać wybrane serie danych.

Dlatego też, musimy przygotować dwie rzeczy:
1. Mechanizm wyboru – tu w postaci listy rozwijanej.
2. Odrębną tabelę, która pozwoli na pokazywanie, bądź wyłączanie danych na wykresie.
Wykres interaktywny: przygotuj dane

Możesz wspólnie ze mną zrobić cały wykres. W tym celu ściągnij plik początkowy.

Na początku tworzymy listę rozwijaną dla każdej z kategorii, tak aby można było za ich pomocą włączyć i wyłączyć daną informację na wykresie. Ja zrobiłem to w zakresie komórek C17:E17.

Teraz musisz utworzyć lustrzaną kopię tabeli z danymi, która będzie podstawą do utworzenia wykresu. W tej tabeli dodamy formuły, które pozwolą na pokazywanie, bądź ukrywanie poszczególnych danych na wykresie. Tabelę tworzysz w dowolnym miejscu ale najlepiej gdyby nie była bezpośrednio widoczna dla końcowego użytkownika. W końcu jest to tylko pomocniczy zakres danych. Ja utworzyłem ją w zakresie G9:J15.

Kolejne kroki wyglądają tak:
- Pozostaw nazwy nagłówków i lata jako wartości – nic nie zmieniamy.
- W pierwszej komórce, gdzie mają się znaleźć dane o sprzedaży (komórka H10) wstawiamy formułę:
```
=JEŻELI($C$17="Tak";C10;BRAK())
```
- Formuła odczytuje zawartość z listy rozwijanej i jeżeli jest tam wartość „Tak” – pokazuj wartości na wykresie – to w tabeli pomocniczej pokaże się odpowiednia wartość sprzedaży z podstawowej tabeli z danymi. Jeżeli natomiast, z listy wybrano wartość „Nie” – w tabeli pomocniczej pokaże się błąd #N/D!. Dzięki temu, osiągniemy pewien efekt na wykresie. Ale o tym później.
- W kolejnym kroku robimy podobną operację dla kolumn z zyskiem i liczbą klientów. Formuły w komórkach I10 i J10 będą wyglądały następująco:
```
=JEŻELI($D$17="Tak";D10;BRAK())
=JEŻELI($E$17="Tak";E10;BRAK())
```
- Dalej pozostaje nam skopiować wszystkie stworzone formuły, w dół do zakresu komórek H15:J15.
- W wyniku powyższych działań otrzymamy tabelę:
Utwórz wykres interaktywny

Dane przygotowane, a więc czas na utworzenie wykresu. Postępuj wg następującego schematu:
- Zaznacz dane w komórkach H9:J15, a następnie na wstążce WSTAWIANIE, wybierz polecenie WSTAW WYKRES KOLUMNOWY.
- Na wykresie zauważysz, że brakuje etykiet osi poziomej, czyli lat. Zaznacz wykres i klikając prawym przyciskiem myszy wybierz ZAZNACZ DANE, a następnie EDYTUJ etykiety osi poziomej wybierając zakres danych w którym znajdują się lata (komórki G9:J15)
- Zamieńmy kategorię z liczbą klientów na wykres liniowy. W związku z tym, zaznacz słupki z tą informacją na wykresie i wybierz polecenie ZMIEŃ TYP WYKRESU SERYJNEGO. Dodatkowo zaznacz znacznik w kolumnie OŚ POMOCNICZA, szczególnie jeżeli dane dla tej kategorii znacznie odbiegają (ilościowo czy merytorycznie) od pozostałych danych (liczba klientów vs. sprzedaż / zysk)
- Dostosowujemy wykres wg upodobań. Ja nałożyłem na siebie słupki kategorii sprzedaży i zysku oraz zmieniłem formatowania. Końcowy efekt wygląda tak:
Efekt końcowy

Powyższe zmiany pozwalają nam osiągnąć efekt interaktywności. To znaczy, że możemy za pomocą list rozwijanych włączać i wyłączać poszczególne dane: sprzedaż, zysk i liczbę klientów.

W tabeli pomocniczej zastosowałem funkcję BRAK(), która daje błąd #N/D!. Dzięki temu na wykresie nie są widoczne serie danych z wartościami zerowymi. W konsekwencji taka seria danych jest niewidoczna.

Końcowy plik możesz ściągnąć stąd.
Chcę otrzymywać newsletter z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach związane z blogiem Excelness.com. Rozumiem i akceptuję regulamin zapisu do newslettera.

Twoje dane osobowe będą przetwarzane w celu obsługi newslettera przez Łukasz Pietrzaka na zasadach opisanych w polityce prywatności. W każdej chwili możesz zrezygnować.

Dziękuję!

Proszę, sprawdź swoją skrzynkę pocztową.

Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
Podsumowanie

W artykule opisałem przykład wykresu interaktywnego, który pozwala na włączanie i wyłączanie danych na wykresie. Wykorzystałem w nim bardzo proste mechanizmy: listy rozwianej, funkcji: JEŻELI i BRAK().

Oczywiście to nie jedyny sposób na interaktywność z użytkownikiem. Można stosować także inne elementy jak chociażby checkboxy i wykorzystywać bardziej zaawansowane techniki z wykorzystaniem funkcji PRZESUNIĘCIE. Niezwykle proste, a zarazem efektowne jest użycie fragmentatorów w tabelach przestawnych.

Zachęcam Cię do dalszych ćwiczeń. Wykres interaktywny jest bowiem nie tylko efektowny ale pozwala na efektywne wykorzystanie dostępnej przestrzeni do prezentacji danych.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
16 kwietnia 2019
Sumy częściowe
Sumy częściowe – jak zaoszczędzić czas

Sumy częściowe występują w dwóch postaciach. Po pierwsze jako funkcja SUMY.CZĘŚCIOWE. Po drugie jako automatyczne narzędzie, które z tej funkcji korzysta. Wstawiamy je na listach danych, aby na przykład szybko podsumować dane wg określonej kategorii.

Narzędzie SUMA CZĘŚCIOWA dostępne jest na wstążce DANE, w sekcji KONSPEKT.

Jak przygotować dane?

Jeżeli chcesz korzystać z tego narzędzia w prawidłowy sposób, powinieneś najpierw posortować dane. Jest ku temu bardzo ważny powód. Narzędzie ma za zadanie podsumować, wyciągnąć średnią lub dokonać jednego z wielu innych obliczeń, wg wybranej przez Ciebie kategorii.

Na przykład, jeżeli chciałbyś podsumować sprzedaż owoców i warzyw, to oczywiste jest, że jabłka powinny sumować się do jabłek, a gruszki do gruszek… Właśnie dlatego sortowanie danych wg określonej kategorii jest pierwszą czynnością jaką powinieneś zrobić.

Jakie możliwości dają SUMY CZĘŚCIOWE?

Narzędzie pozwala na obliczenie:
- Sumy – oblicza sumę komórek
- Średniej – oblicza średnią wartość z komórek
- Liczba – zlicza ilość poszczególnych liczb / kategorii
- Maksimum – jako wynik zwraca maksymalną wartość z komórek
- Minimum – jako wynik zwraca minimalną wartość z komórek
- Iloczyn – jako wynik zwraca wynik mnożenia liczb
- Zliczanie – zliczanie niepustych komórek
- Odchylenie standardowe – oblicza odchylenie standardowe
- Odchstdc – oblicza odchylenie standardowe populacji
- Wariancja – oblicza wariancje
- Wariancja populacji – oblicza wariancje populacji
Aby użyć narzędzia suma częściowa należy:
1. Posortować dane.
2. Zaznaczyć jedną z komórkę na liście danych.
3. Na wstążce DANE, w sekcji KONSPEKT, kliknąć na przycisk SUMA CZĘŚCIOWE.
Zostanie wyświetlone okno sumy częściowe.

W poszczególnych częściach należy ustawić:
1. DLA KAŻDEJ ZMIANY W – według jakiej kategorii dane będą sumowane, czyli np. dla każdej zmiany w kolumnie Kategoria ma zostać obliczona suma częściowa
2. UŻYJ FUNKCJI – jaki rodzaj obliczeń ma zostać wykonany, np. suma, średnia itp.
3. DODAJ SUMĘ CZĘŚCIOWĄ DO – które wartości mają podlegać obliczeniom.
Dla przykładu z obrazka powyżej zostanie obliczona suma sprzedaży dla każdej kategorii. Wynik jest następujący.

Po zastosowaniu sum częściowych na liście danych, automatycznie zostanie dodane grupowanie wierszy według sumowanej kategorii. Przy czym, jeżeli używam sformułowania „sumowanej” to tak naprawdę mam na myśli dowolną z wymienionych wyżej funkcji. Zwijając poziomy grupowania można otrzymać następując widok podsumowujący sprzedaż.

Rzecz jasna tak samo będzie wyglądał efekt obliczania innych funkcji.

Czy chcesz bezpłatny test z Excela?

Modyfikacja istniejących wyników

Aby zmienić rodzaj zastosowanej sumy częściowej należy wywołać okno dialogowe SUMA CZĘŚCIOWA, a następnie wybrać inny sposób obliczania oraz zaznaczyć checkbox ZAMIEŃ BIEŻĄCE SUMY CZĘŚCIOWE.

W efekcie program Excel zamieni wcześniej zastosowane sumy na średnie.

W przypadku, gdy checkbox ZAMIEŃ BIEŻĄCE SUMY CZĘŚCIOWE pozostałby pusty, do danych zostanie dodana zarówno suma, jak i średnia.

Aby suma częściowa pojawiała się nie poniżej, a powyżej kategorii – pozostaw pusty checkbox PODSUMOWANIE PONIŻEJ DANYCH.

W tym przypadku wynik obliczania sumy częściowej pojawi się powyżej kategorii produktów.

Ja jednak zalecam pozostawić podsumowanie poniżej danych. Ułatwi to odbiorcy poprawną interpretację zawartych tam informacji.

Podsumowanie

Szczerze mówiąc nie jestem fanem tej funkcjonalności. Głównie dlatego, że jest mało elastyczna. Te same i lepsze efekty da Ci użycie tabel przestawnych.

Jednakże, jeżeli miałbyś dla przykładowej listy 10 000 wierszy ręcznie wstawiać podsumowania dla każdej zmiany w kolumnie klient – to już lepiej użyj tego narzędzia. Być może jak jedna z uczestniczek moich szkoleń – zaoszczędzisz dwóch dni pracy studentowi na zleceniu 😉

Daj mi znać, czy używasz sum częściowych. A jeżeli uważasz, że ta wiedza może się komuś jeszcze przydać – udostępnij artykuł. Będzie mi bardzo miło, jeżeli pomogę także Twoim znajomym.
9 kwietnia 2019
Usuń duplikaty w Excelu
Wszystko się powtarza – sposoby na dublujące się dane.

Usuwanie duplikatów w Excelu to koszmar większości stażystów w działach analitycznych. Siedzisz, wpatrujesz się jak sroka w gnat w ekran komputera, wypatrując powtarzających się wartości. Po pewnym czasie wszystko zlewa się przez oczami w jeden wielki, czarny placek podzielony na komórki.

Wyobraźcie sobie, że słyszałam o jednej osobie, która usuwa ręcznie duble przez cały miesiąc pracy. Całe szczęście Excel daje nam kilka możliwości usprawnienia takiej pracy, a w pewnym okolicznościach może wykonać ją za nas.

Jeśli nie spotkaliście się z tzw. duplikującymi się wartościami w Excelu – to są to identyczne wartości, które się powtarzają w wierszach bazy danych. Najczęściej występują, gdy eksportujemy dane z innego programu i musimy je odpowiednio opracować, aby dostosować do swoich potrzeb.

Inna sytuacja to błąd ludzki przy wprowadzaniu danych do bazy. Wyobraźmy sobie, że mamy tabelę ze zgłoszeniami incydentów od klientów, w której osoba obsługująca wpisuje:
- nazwę klienta,
- numer klienta,
- numer zgłoszenia,
- datę zgłoszenia,
- datę rozwiązania problemu.
Na powyższym przykładzie, wytłumaczę Wam, jak można wykryć lub pozbyć się dubli na pięć sposobów:
1. Wykorzystując funkcję LICZ.JEŻELI, bez i z wykorzystaniem stworzonego ID,
2. Za pomocą formatowania warunkowego,
3. Używając funkcji „usuń duplikaty”,
4. Tworząc tabelę przestawną,
5. Pisząc krótkie makro, które zrobi to za nas 🙂
Wykorzystaj funkcję LICZ.JEŻELI

Funkcja LICZ.JEŻELI nie usuwa duplikatów, ale ułatwia wyłapanie dublujących się wartości. Jest bardziej narzędziem sprawdzającym, czy musimy się martwić dublami. Do naszej tabeli dodamy kolumnę „Duble licz jeżeli”, w której sprawdzimy duble po numerze zgłoszenia:

W kolumnie „Duble Licz jeżeli” widoczny jest wynik funkcji, a w kolumnie obok sama funkcja. Funkcja przeszukuje – wskazany jako pierwszy argument funkcji – zakres, w celu zliczenia liczby wystąpień wartości podanej jako drugi argument funkcji. Należy pamiętać, aby zakres był zaadresowany bezwzględnie (dodany symbol „$”), aby przy kopiowaniu formuły zakres pozostawał ten sam, a zmieniała się komórka z wartością, której występowanie jest zliczane przez funkcję.

Jeśli wynikiem funkcji jest cyfra 1 oznacza to, że dana wartość występuję w zakresie tylko jeden raz, a więc nie mamy do czynienia z dublami. Każdy wynik powyżej 1 oznacza liczbę wierszy, w której dana wartość się powtarza.

Najszybszy sposób na sprawdzenie, czy mamy duplikaty to rozwinięcie filtra w kolumnie, w której sprawdzaliśmy duble. Na filtrze powinny być widoczne wartości 1,2,3…, które są wynikiem funkcji LICZ.JEŻELI. Pamiętajcie, że jeśli wartości nie będą identyczne (np. wystąpi literówka w nazwie, dodatkowa spacja) to Excel będzie traktował je jak inne wartości.

Chcąc sprawdzić duplikaty, na podstawie więcej niż jednej kolumny, możemy stworzyć ID (identyfikator). Aby sprawdzić, czy dubluje nam się numer zgłoszenia, dotyczący tego samego klienta, które posiada zarówno taką samą datę zgłoszenia i zamknięcia, możemy stworzyć ID łączące wartości z tych kolumn w jednej kolumnie, a następnie sprawdzić duplikaty w tej kolumnie.

Użyj formatowania warunkowego

Drugim szybkim sposobem na sprawdzenie, czy w naszych danych występują duplikujące się wartości, jest skorzystanie z opcji formatowania warunkowego. Zaznaczamy

zakres, który chcemy sprawdzić, a następnie korzystamy ze ścieżki: NARZĘDZIA GŁÓWNE > FORMATOWANIE WARUNKOWE > REGUŁY WYRÓŻNIANIA KOMÓREK > DUPLIKUJĄCE SIĘ WARTOŚCI.

Jeśli jakaś wartość występuje więcej niż jeden raz w zaznaczonym zakresie to Excel wyróżni ją we wskazany przez nas sposób:

Narzędzie usuń duplikaty

Ok, czyli teraz już wiemy, że mamy duble, które są wynikiem błędu i chcemy się ich pozbyć. Po co? Przy dużej bazie danych duble mogą powodować wolniejsze działanie pliku, błędy przy sumowaniu wartości, np. jeśli będziemy zliczać liczbę wszystkich zleceń dla danego klienta to, bez usunięcia dubli, pojawi nam się suma większa niż była w rzeczywistości.

Najłatwiej zrobić to przez narzędzie USUŃ DUPLIKATY znajdujące się na wstążce DANE. Zaznaczamy dane, a następnie korzystamy ze ścieżki: DANE > USUŃ DUPLIKATY.

Wyświetli się okno, w którym zaznaczamy kolumny, które mają zostać sprawdzone pod kątem występowania duplikatów. Te wiersze, które będą się duplikować zostaną usunięte, tzn. zostanie tylko wiersz, w którym duplikująca się wartość występuje po raz pierwszy.

Jeśli w naszym przykładzie zaznaczymy wszystkie kolumny to Excel nie usunie żadnego wiersza, ponieważ w żadnym z wierszy nie występują we wszystkich kolumnach identyczne wartości (czyli w każdym z wierszy dane różnią się przynajmniej w jednej z kolumn).

Jeśli w oknie USUWANIA DUPLIAKTÓW zaznaczymy tylko kolumnę „Numer klienta” to zostaną usunięte wszystkie wiersze, w których powtarza się wcześniej występujący numer klienta. Istnieje również możliwość zaznaczenia kilku kolumn, co zadziała tak, jakbyśmy mieli stworzone ID, łączące wartości z tych kolumn – zostaną usunięte wiersze, dla których powtarzają się wartości we wszystkich zaznaczonych przez nas kolumnach. Na przykład, jeśli zaznaczymy numer klienta, datę zgłoszenia i datę rozwiązania zostanie usunięty tylko jeden wiersz:

Po każdym zastosowaniu tej funkcjonalności otrzymamy informacje, ile powtarzających się wartości zostało usuniętych:

Przed skorzystaniem z narzędzia usuwania duplikatów warto stworzyć sobie kopię zapasową danych, z których będziemy usuwać powtarzające się wartości. Excel usunie dane z zaznaczonego zakresu, a więc dane zostaną usunięte z naszej tabeli danych. Sposobem na odzyskanie tych danych jest cofnięcie wykonanej czynności poprzez przycisk COFNIJ.
Chcę otrzymywać newsletter z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach związane z blogiem Excelness.com. Rozumiem i akceptuję regulamin zapisu do newslettera.

Twoje dane osobowe będą przetwarzane w celu obsługi newslettera przez Łukasz Pietrzaka na zasadach opisanych w polityce prywatności. W każdej chwili możesz zrezygnować.

Dziękuję!

Proszę, sprawdź swoją skrzynkę pocztową.

Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉
Usuń duplikaty, a tabela przestawna.

A co, jeśli nie chcemy usuwać duplikujących się wartości, ponieważ informacje znajdujące się w kolejnych kolumnach są różne i w takim układzie też są nam potrzebne?

Spójrzmy na nasz przykład. Chcemy zobaczyć tylko listę wszystkich klientów (bez duplikatów) ale nie chcielibyśmy tracić pozostałych informacji. Wystarczy, że z naszej tabeli stworzymy TABELĘ PRZESTAWNĄ, a następnie do sekcji WIERSZE przeciągniemy interesującą nas kolumnę (klikamy na nazwę tej kolumny w sekcji pól, i trzymając cały czas wciśnięty prawy przycisk myszy „przeciągamy” i „upuszczamy” w sekcji wiersze).

W ten sposób otrzymamy listę wszystkich klientów, bez duplikujących się wartości:

Wykorzystanie w ten sposób tabeli przestawnej przydaje się, jeśli chcemy coś sprawdzić „na szybko”, np. ile klientów obsługujemy, bez ingerencji w bazę danych. Należy jednak pamiętać, że jeśli będziemy chcieli za pomocą takiej tabeli przestawnej sumować wartości z bazy danych, to zostaną one zsumowane ze wszystkich wierszy, co może doprowadzić do błędnych wyników.

Zautomatyzuj. Użyj makra.

A teraz pora na jazdę bez trzymanki, czyli MAKRO. Kiedy skorzystać z takiego rozwiązania? Wtedy, gdy co miesiąc usuwacie duble ze swojej bazy i już macie serdecznie dość „klikania” co miesiąc tego samego : )

W tym artykule zamieszczę Wam krótki skrypt działający jak narzędzie „usuń duplikaty”.

Sub Usuń_duble()
Range(„A1:E12”).RemoveDuplicates Columns:=Array(2, 4, 5), Header:=xlYes
End Sub

Jak dostosować to proste makro do Waszych potrzeb? Zmieniacie tylko następujące dane:
- „A1:E12” – tutaj wpiszcie zakres, w którym mają być sprawdzane i usuwane duplikujące się wiersze (tak jak zaznaczaliśmy tabelę korzystając z funkcji „Usuń duplikaty”)
- 2,4,5 – to są numery kolumn w naszym zakresie, które mają być sprawdzane pod kątem występowania duplikujących się wartości (tak jak zaznaczaliśmy „ptaszkami” nazwy kolumn korzystając z funkcjonalności „usuń duplikaty”), tutaj są to kolumny druga, czwarta i piąta (numer klienta, data zgłoszenia i data rozwiązania),
- xlYes – to jest ten „ptaszek” przy napisie „moje dane mają nagłówki”.
Jak już dostosujecie to makro do swoich potrzeb, to następnym razem tylko odpalacie plik, uruchamiacie makro i idziecie na obiad. Smacznego! : )

Agnieszka Kmieć
2 kwietnia 2019
Wykres Gantta
Jak zrobić wykres Gantta?

Wykres Gantta to rodzaj grafu, na którym możesz pokazać zadania i czas. Dzięki temu jest szczególnie często wykorzystywany w projektach. Na marginesie oryginalnym twórcą tego narzędzia był Karol Adamiecki, który jednak nie opublikował swojej pracy w odpowiednim czasie.

W jednym z poprzednich artykułów, pokazywałem jak zrobić harmonogram w Excelu z wykorzystaniem Gantta, ale z pominięciem wykresów. Całość wykorzystywała formatowanie warunkowe i m.in. funkcję JEŻELI.

W tym artykule pokaże Ci jak stworzyć Gantta z wykorzystaniem wykresu w Excelu. Całe zadanie jest całkiem proste. Zatem, do dzieła!

Przygotowujemy dane

Wykres Gantta przygotujemy bazując na danych związanych z datami. Jednak nic nie stoi na przeszkodzie, abyś wykorzystywał ten typ wykresu także do przedstawiania innego rodzaju informacji, np. jako lejek rekrutacyjny, sprzedażowy czy …do narysowania choinki 😉

Wracając do naszego zadania, mamy następujące informacje:
- zadanie,
- datę rozpoczęcia,
- datę zakończenia,
- czas trwania (liczony, jako różnica pomiędzy tymi datami)
Stworzymy wykres, który będzie graficznie prezentował zakres czasu potrzebny do realizacji każdego zadania.

Tworzymy wykres Gantta

Przechodząc do rzeczy. Kolejne kroki, będą wyglądały następująco:
- Utwórz SKUMULOWANY WYKRES SŁUPKOWY. Na wstążce WSTAWIANIE, w sekcji WYKRESY, wybierz odpowiednie polecenie. Nie zaznaczaj wcześniej danych. Dodamy je do wykresu w kolejnych krokach.
- Kliknij prawym przyciskiem myszy na wykresie i wybierz polecenie ZAZNACZ DANE.
- Dodaj pierwszą SERIĘ DANYCH, zaznaczając zakres z datami rozpoczęcia zadania jak na poniższym screenie. Możesz dodać etykietę – wpisując ręcznie, bądź zaznaczając nagłówek z odpowiednią nazwą.
- Jako etykiety osi (pionowej) wybierz zakres, w którym są wpisane zadania.
- Dodaj kolejną SERIĘ DANYCH, tym razem wybierając czas trwania.
- W tej chwili Twój wykres powinien wyglądać mniej więcej tak:
- Z pewnością zauważyłeś dwa problemy. Po pierwsze na osi pionowej, zadania są ułożone w odwrotnej kolejności. Po drugie niebieski słupek, który reprezentuję datę rozpoczęcia – nie jest nam potrzebny.
- Jeżeli chodzi o kategorię na osi – kliknij na nią prawym przyciskiem myszy i wybierz polecenie FORMATUJ OŚ. Następnie zaznacz opcję KATEGORIE W KOLEJNOŚCI ODWROTNEJ.
- Niebieskiej serii danych nie będziemy się pozbywać. Trzeba ją odpowiednio…sformatować. Kliknij w słupek serii prawym przyciskiem myszy i ustaw WYPEŁNIENIE, jako BRAK WYPEŁNIENIA oraz KONTUR, jako BRAK KONTURU.
- Opcjonalnie możesz sformatować oś poziomą, aby prezentowane daty były bardziej czytelne. Na przykład zastosuj format MMM RR. Zmiany formatowania dokonujesz klikając prawym przyciskiem myszy na oś i wybierając polecenie FORMATUJ OŚ. Następnie wybierasz odpowiednią opcję w sekcji LICZBY.
- W rezultacie otrzymasz wykres Gantta, podobny do poniższego. Gotowy plik z omawianym wykresem oraz wspomnianym wcześniej „lejkiem” ściągniesz stąd. Ale można jeszcze lepiej…
Wykres Gantta w zaawansowanym harmonogramie projektu

Z pewnością korzystając z powyższej wiedzy, jesteś w stanie stworzyć swój własny harmonogram zadań czy projektu. Na wypadek, gdybyś jednak potrzebował bardziej zaawansowanego narzędzia – przygotowałem dla Ciebie zaawansowany harmonogram, wykorzystujący wykres Gantta.

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

Akceptuję regulamin zapisu do newslettera.

Pobierając za darmo „Harmonogram Gantta”, zapisujesz się na nieodpłatny newsletter, zawierający informacje: z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach. Z newslettera możesz zrezygnować w dowolnym momencie.

Jeżeli nie chcesz zapisywać się do newslettera, możesz kupić to narzędzie za 49 zł brutto, pisząc do mnie na adres [email protected]. Szczegóły znajdziesz w regulaminie, który akceptujesz zaznaczając okienko wyboru powyżej.

Sukces!

Plik już do Ciebie leci…Sprawdź swoją skrzynkę pocztową.

Harmonogram ma kilka dodatkowych funkcji. Po pierwsze pozwala na określanie terminów zadań wg planu (przerywane linie na wykresie) i wg realizacji (szare słupki) Po drugie możesz odnotowywać procent realizacji, danego zadania. W takim przypadku, szary słupek zamienia się w zielony – zgodnie z procentem realizacji.

Wprowadzane daty startu i czasu trwania w tygodniach, pozwalają śledzić postęp prac w tygodniowych interwałach. Możesz wprowadzić 25 zadań, ale w ramach ćwiczeń możesz spróbować samodzielnie zmodyfikować plik, aby można było dodawać ich więcej 😉

Podsumowanie

Wykres Gantta możesz wykorzystać w wielu sytuacjach, a przy tym jest stosunkowo prosty do zrobienia. W artykule wykorzystałem skumulowany wykres słupkowy i proste techniki formatowania wykresów.

Jeżeli potrzebujesz bardziej zaawansowanej formy do śledzenia postępów swoich projektów – wykorzystaj mój harmonogram, który pozwala m.in. na śledzenia stopnia realizacji zadań.
26 marca 2019
Nazwy w Excelu
Po co nam nazwy w Excelu? Dowiedz się i ułatw sobie życie.

Excel ma nam ułatwiać życie. Z kolei, używanie nazwy w Excelu ma nam ułatwić zadania jakie w nim wykonujemy. Dlatego najważniejsze powody, dla których stosujemy nazwy to:
1. Zwiększenie przejrzystości i czytelności formuł, dzięki czemu stają się łatwiejsze w obsłudze.
2. Automatyzacja zmian w arkuszach, w sytuacji gdy musimy zaktualizować jakieś dane.
W tym artykule dowiesz się jak:
1. Definiować nazwy i jakie obowiązują przy tym zasady.
2. W jakich sytuacjach „opłaca się” użycie nazw.
3. Jak zarządzać nazwami, czyli: modyfikować, usuwać czy zmieniać ich parametry.
Jak tworzymy nazwy w Excelu

Nazwy w programie Excel odnoszą się do pojedynczych komórek, zakresów komórek, stałych wartości, tabeli i funkcji. Są 4 zasady dotyczące tworzenia nazw:
1. Nazwa nie może być dłuższa niż 255 znaków (nie polecam tworzenia takich długich)
2. Nie zaczynaj nazwy inaczej jak od litery lub znaku podkreślenia.
3. W nazwie nie można używać spacji i znaków specjalnych, typu: @,!,#,$.
4. Nazwy nie mogą się powtarzać (są unikalne w obrębie pliku)
Istnieją dwa sposoby na utworzenie nazwy:
1. Poprzez pole nazwy komórki.
2. Z wykorzystaniem menedżera nazw.
Pole nazwy komórki

W celu nadania nazwy pojedynczej komórce lub zakresowi komórek możesz skorzystać z pola nazwy komórki.

Aby utworzyć nazwę dla komórki lub zakresu komórek, możesz skorzystać z jednej z dwóch ścieżek.

Sposób 1. Zaznacz komórkę lub zakres komórek, a następnie kliknij lewym przyciskiem myszy w pole nazwy komórki i wpisz nazwę. Koniecznie zatwierdź ją ENTEREM na klawiaturze.

Sposób 2. Zaznacz komórkę wraz z jej etykietą i skorzystaj z przycisku UTWÓRZ Z ZAZNACZENIA. Zostanie wyświetlone okno, w którym wskazujesz, gdzie znajduje się etykieta nazwy w zaznaczonym przez Ciebie zakresie. Następnie kliknij na przycisk OK, a nazwa zostanie utworzona automatycznie.

Menedżer nazw

Okno Menadżera nazw możesz wywołać przez skrót CTRL + F3. Odpowiednie polecenie znajdziesz także na wstążce FORMUŁY. Aby, utworzyć nazwę w ten sposób:
- zaznacz komórkę lub zakres komórek.
- na wstążce FORMUŁY, w sekcji NAZWY ZDEFINIOWANE, kliknij polecenie MENEDŻER NAZW.
Zostanie wyświetlone okno tworzenia nowej nazwy. Teraz uzupełnij kolejne pola:
- Nazwa – to nazwa zaznaczonej komórki / zakresu.
- Zakres – można zostawić wartość domyślną – określasz do jakiej części skoroszytu będzie przypisana dana nazwa, czyli gdzie będzie jej można użyć w formułach np. tylko jeden z arkuszy w skoroszycie.
- Komentarz – możesz zostawić puste – to opis nazwy, który wyświetli się pod daną nazwą podczas wpisywania formuły.
- Odwołuje się do – jeśli przed kliknięciem NOWY została zaznaczona komórka lub zakres, to pole będzie uzupełnione adresem tej komórki lub zakresu.
Nazwy w Excelu mogą się także odwoływać do wartości stałej, czyli takiej która nie będzie znajdować się w żadnej komórce arkusza. Aby utworzyć taką nazwę także korzystasz z MENEDŻERA NAZW. W sekcji ODWOŁUJE SIĘ DO skasuj podpowiedziany adres i wpisz wartość stałą po znaku =.

Nazwy w Excelu – kiedy to się opłaca?

Porównaj ze sobą takie dwa zapisy:

=([Zeszyt1]Arkusz1!$A$1+[Zeszyt1]Arkusz1!$B$1)*[Zeszyt1]Arkusz1!$C$1

=(podstawa+premia)*składka_ZUS

Który jest bardziej czytelny? 😉

Nazwy ułatwiają zrozumienie formuł

To pierwszy powód, dla którego warto używać nazw. W sytuacji gdy tworzysz wiele formuł, a poziom ich skomplikowania rośnie – zdecydowanie dobrym pomysłem jest upraszczać ich zapis.

Czasami napracujemy się solidnie, tworząc jakieś narzędzie w Excelu. Tworzymy wiele skomplikowanych zależności i formuł. W momencie, gdy tworzymy – wszystko jest dla nas jasne. Ale spróbuj wrócić do swojej pracy po tygodniu, czy miesiącu i zrozumieć co miałeś na myśli, a zrozumiesz o co mi chodzi.

Nazwy pozwalają na automatyzację

Ale w używaniu nazw nie chodzi tylko o upraszczanie zapisów formuł. Z utworzonych nazw możesz oczywiście korzystać w formułach. Aby to zrobić, musisz wpisać zdefiniowaną nazwę w formułę (po wpisaniu pierwszych liter, nazwy będą się podpowiadać).

Możesz też skorzystać z przycisku UŻYJ W FORMULE, ze wstążki FORMUŁY w sekcji NAZWY ZDEFINIOWANE. Po kliknięciu przycisku zostanie rozwinięta lista wszystkich nazw zdefiniowanych w skoroszycie.

Wyobraź sobie teraz, że dostałeś zadanie zbudowania modelu budżetowego. W modelu będzie bardzo wiele formuł, w których liczysz m.in. podatek VAT, wg różnych stawek. Używasz formuł w postaci:

=SUMA(A1:A10)*23%

=SUMA(B10:B20)*8%

W efekcie, po wielu godzinach czy dniach pracy, jesteś zadowolony. Powstało narzędzie, które automatyzuje proces budżetowania w Twojej firmie. Wtedy przychodzi TA chwila…, czyli zmiana przepisów. Rząd wprowadza podwyżkę (wszakże, jakżeby inaczej) stawek podatkowych. Co to oznacza dla Twojego modelu? Oczywiście, musisz znaleźć wszystkie komórki, w których używałeś do tej pory stawek 8% czy 23%, i zmienić je na nowe…

Ilość pracy, jaką będziesz musiał wykonać będzie wprost proporcjonalna do tego, jak często odwołanie do tych stawek występuje w modelu. Gdyby jednak, było można przewidzieć zmianę przepisów…?

Właściwie nie musisz ich przewidywać. Przede wszystkim powinieneś tworzyć elastyczne narzędzia, czyli Twoje pierwotne formuły, mogłyby wyglądać tak:

=SUMA(A1:A10)*vat_23

=SUMA(B10:B20)*vat_8

Wykorzystując nazwę (vat_8, vat_23) – po aktualizacji przepisów – Twoje zadanie ograniczy się tylko do modyfikacji wartości nazw w MENEDŻERZE NAZW. Wszystkie formuły, które wykorzystują te nazwy – zostaną automatycznie zaktualizowane. Podobnie proste będzie wykonywanie symulacji różnych stawek.

Modyfikowanie nazw

Jeżeli chcesz zmienić wcześniej utworzoną nazwę, skorzystaj z omawianej wcześniej ścieżki:
- na wstążce FORMUŁY, w sekcji NAZWY ZDEFINIOWANE, kliknij polecenie MENEDŻER NAZW.
- Zaznacz wybraną nazwę i kliknij EDYTUJ.
- Zostanie wyświetlone okno edycji nazwy, w którym możesz dokonać edycji poszczególnych pól.
Utworzoną nazwę możesz również usunąć, wykonując kolejne kroki:
- na wstążce FORMUŁY, w sekcji NAZWY ZDEFINIOWANE, kliknij polecenie MENEDŻER NAZW.
- Zaznacz wybraną nazwę i kliknij USUŃ.
Pamiętaj jednak, że jeśli dana nazwa była wykorzystana już w formułach, to po jej usunięciu formuły, które ją wykorzystywały zwrócą błąd #NAZWA?

Czy chcesz bezpłatny test z Excela?

Podsumowanie

Nazw w Excelu używasz z dwóch powodów. Po pierwsze upraszczasz zapisy formuł, a po drugie automatyzujesz pracę, w sytuacji zmian i konieczności aktualizacji parametrów, które się powtarzają.

Możesz je tworzyć za pomocą pola nazwy komórki i menedżera nazw. Pamiętaj o 4 regułach przy ich wprowadzaniu.

Dobrą praktyką w korzystaniu z nazw jest zaczynanie ich tym samym skrótem – na obrazkach powyżej mogłeś zauważyć, że każda nazwa poprzedzona jest skrótem „ex_”. Taki zabieg ułatwia późniejsze wpisywania nazw w formułach.

Jeżeli uważasz, że wiedza z tego artykułu może komuś jeszcze pomóc, udostępnij go proszę. A jeżeli masz jakieś dodatkowe pytania – zadaj je w komentarzu pod artykułem. Na pewno odpowiem 😉
19 marca 2019