Blog

POWER PIVOT [2] – RELACJE
POWER PIVOT – poznaj i wykorzystaj [cz. 2 RELACJE]

W poprzedniej części dowiedziałeś się jak zainstalować, do czego służy i jak możesz wykorzystać Power Pivot. Mogłeś także ściągnąć plik z danymi, które wykorzystamy w kolejnych częściach serii.

W tym artykule dowiesz się:
- jak przygotować dane pod relacje,
- czym są relacje i jak ich używać.
Jak przygotować dane pod relacje?

Model danych w Power Pivot pozwala na zebranie danych z różnych źródeł oraz połączenie ich relacjami. To da Ci możliwość wykorzystywania tych danych w ujednoliconej i usystematyzowanej formie.

Jeżeli musisz połączyć w modelu dane z różnych źródeł – np. plików tekstowych, plików Excela, baz danych Access, czy programu SQL Serwer – wykonaj poniższe kroki:
- Na wstążce POWER PIVOT, kliknij ikonę ZARZĄDZAJ w sekcji MODEL DANYCH.
- Uruchomisz dodatek Power Pivot. Na wstążce NARZĘDZIA GŁÓWNE, w sekcji POBIERANIE DANYCH ZEWNĘTRZNYCH znajdziesz różne opcje na pobranie danych z różnych typów plików czy programów.
Nie będę opisywał tu szczegółowo każdej z opcji, z dwóch powodów:

Po pierwsze chciałbym żebyśmy przećwiczyli opcję z danymi w excelu umieszczonymi w różnych tabelach (plik ćwiczeniowy)
Po drugie, więcej możliwości z pobieraniem danych z zewnętrznych źródeł daje inne narzędzie – POWER QUERY. Ale to temat na inny cykl 😉

Wracając do naszego pliku, danych i przykładu…

Najprostszy sposób, aby dodać wszystkie dane do modelu to kolejne kroki:
- Ustaw się w dowolnej komórce zakresu danych.
- Na wstążce POWER PIVOT, w sekcji TABELE, kliknij ikonę DODAJ DO MODELU DANYCH.
- Pojawi się okno dialogowe TWORZENIE TABELI, z domyślnie zaznaczonym zakresem danych.
- Zaznacz opcję MOJA TABELA ZAWIERA NAGŁÓWKI.
Jednak, jeśli chcesz sobie ułatwić życie później – na etapie tworzenia relacji – najpierw utwórz TABELE, a później je nazwij. Jednoznaczna nazwa tabeli ułatwi Ci za chwilę tworzenie relacji.

Relacje, czyli tak naprawdę co?

Relacja to sposób na zorganizowane i logiczne połączenie danych z różnych tabel (źródeł danych) Powiązanie to powstaje na podstawie tzw. klucza, czyli porządkowej danej, która pojawia się w minimum dwóch tabelach. W jednej najczęściej będzie to tzw. klucz podstawowy, a w drugiej obcy.

Na przykład, w tabeli DZIAŁY, będziemy mieli klucz podstawowy – ID DZIAŁ. Ta sama informacja (ID DZIAŁ) znajduje się w tabeli MPKI (klucz obcy)

W relacyjnych bazach danych wyróżnia się trzy typy powiązań:
1. relacja 1 do 1 – wtedy, gdy jednemu wierszowi danych z tabeli A, odpowiada 1 wiersz tabeli B. Taka relacja jest raczej rzadka, bo zazwyczaj takie dane umieszczasz po prostu w jednej tabeli.
2. relacja 1 do wielu – występuje, gdy 1 wierszowi z tabeli A, odpowiada wiele wierszy z tabeli B. Sytuacja najczęstsza. I w przykładzie jaki robimy, właśnie takie relacje będą występować.
3. relacja wiele do wielu – to sytuacja, gdy wiersz w tabeli A może mieć wiele pasujących wierszy w tabeli B i jednocześnie, wierszowi w tabeli B odpowiada wiele powiązań z tabeli A. Standardowo, w Power Pivot nie jest możliwe stworzenie takiej relacji i trzeba to „obchodzić” poprzez wstawianie dodatkowej tabeli, która niejako łączy dane z tabel A i B.
Relacje – jak ich używać?

Pewnie się niecierpliwisz? 😉 Wybacz te teoretyczne wtręty ale to minimum podstaw teoretycznych pozwoli lepiej zrozumieć co właściwie tworzysz i pozwoli uniknąć frustrujących błędów (przerabiałem to)

Relację możesz utworzyć na więcej niż jeden sposób, ale ja pokaże Ci taki, który wizualnie odzwierciedla zależności pomiędzy tabelami.
- Dodaj wszystkie dane (z każdego arkusza pliku z przykładem) do modelu danych: wstążka POWER PIVOT, polecenie DODAJ DO MODELU DANYCH.
- Po wybraniu okna programu POWER PIVOT, powinieneś zobaczyć tabele przypominające arkusz Excela, wraz z zakładkami, które będą nazwane tak jak utworzone przed chwilą tabele (m.in. dlatego opłaca się te tabele wcześniej nazwać)
- Przejdź do sekcji WIDOK, na wstążce NARZĘDZIA GŁÓWNE i wybierz WIDOK DIAGRAMU.
- Zobaczysz okno, w którym graficznie przedstawione będą wszystkie tabele dodane do modelu danych.
- Następnie utworzymy pierwszą relację. Kliknij pole ID DZIAŁ, w tabeli TBL_DZIALY i trzymając lewym przyciskiem myszy przeciągnij w na pole ID DZIAŁ w tabeli TBL_MPK.
Teraz tworzymy kolejne relacje. Skąd wiesz jakie? To wynika z dwóch czynników. Po pierwsze: musisz rozumieć dane na jakich działasz. Jakie są pomiędzy nimi zależności i w jaki sposób są powiązane. Po drugie: musisz oczywiście mieć możliwość połączenia tabel. A będziesz mógł to zrobić, tylko jeżeli w łączonych tabelach występuje klucz, który te dane łączy.

W powyższym przykładzie tym kluczem jest ID DZIAŁ. A utworzona relacja jest relacją 1 do wielu, bo jednemu działowi firmy może odpowiadać wiele MPKów (ID DZIAŁU z tabeli TBL_DZIAL jest unikalne, tzn. nie dubluje się)

Jeżeli rozumiesz powyższe zasady, to połączenie reszty danych w relacje nie powinno Ci już sprawić problemów. Efekt końcowy powinien wyglądać tak:

Diagram pokazuje następujące relacje:
- ID OKRESU (TBL_OKRESY) – OKRES (TBL_DANE)
- MPK (TBL_DANE) – MPK (TBL_MPK)
- ID DZIAŁ (TBL_MPK) – ID DZIAŁ (TBL_DZIALY)
- PODGRUPA KOSZTÓW (TBL_DANE) – PODGRUPA KOSZTÓW (TBL_PODGRUPA_KOSZTÓW)
- ID GRUPA KOSZTÓW (TBL_PODGRUPA_KOSZTÓW ) – ID GRUPA KOSZTÓW (TBL_GRUPA_KOSZTÓW)
Kompletne rozwiązanie znajdziesz także w tym pliku.
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 drugiej części cyklu o Power Pivot dowiedziałeś się:
- jak przygotować dane,
- czym są relacje,
- jak używać relacji.
Zapraszam Cię do kolejnej części w której dowiesz się w jaki sposób wykorzystać utworzone relacje do stworzenia ciekawych raportów. Dzięki nim, odpowiemy także na pytania jakie postawiłem w pierwszej części cyklu.
12 marca 2019
Lista rozwijana
Lista rozwijana – 3 proste sposoby

Listę rozwijaną w Excelu możesz wykorzystać na wiele sposobów. Od stworzenia formularza, po interaktywne wykresy. W tym artykule poznasz 3 proste sposoby na stworzenie listy. Dowiesz się jakie są różnice i zalety każdej z nich. Na końcu artykuły znajdziesz plik z opisywanymi tu przykładami.

Lista rozwijana w komórce

Najprostszym i często wykorzystywanym sposobem na listę rozwijaną, jest wykorzystanie funkcjonalności – poprawność danych. Załóżmy, że chcesz stworzyć listę rozwijaną, z której będzie możliwe wybranie jakiegoś miasta. Przygotuj listę miast w dowolnym zakresie w arkuszu. Kolejne kroki jakie musisz zrobić:
- Zaznacz komórkę, w której ma powstać lista rozwijana.
- Przejdź na wstążkę DANE i wybierz POPRAWNOŚĆ DANYCH.
- W zakładce USTAWIENIA, wybierz z listy rozwijanej LISTĘ.
- W polu ŹRÓDŁO, kliknij w ikonę z komórkami. To pozwoli Ci zaznaczyć zakres z miastami, które wcześniej wpisałeś. Jeżeli wiesz jak nadawać NAZWY – w tym miejscu zamiast wskazywać zakres – możesz podać wcześniej zdefiniowaną nazwę.
- Po zaznaczeniu zakresu, ponownie kliknij w ikonę z komórkami. To pozwoli Ci powrócić do menu USTAWIENIA, w oknie dialogowym SPRAWDZANIE POPRAWNOŚCI DANYCH. Teraz wystarczy, że klikniesz w przycisk OK.
- Lista rozwiana jest gotowa. Klikając w komórkę z listą, będziesz mógł zobaczyć wszystkie wcześnie wpisane miasta.
Lista rozwijana jako kontrolka formularza

Drugim sposobem na listę jest wykorzystanie kontrolki formularza. Na karcie DEWELOPER (z tego artykułu dowiesz się jak ją włączyć) kliknij polecenie WSTAW.

Pojawi się menu z dwoma grupami: kontrolki formularza i formanty ActiveX (o tych drugich później) W ramach kontrolek, możesz używać takich jak: przycisk, pasek przewijania, pole wyboru (tzw. checkbox) i właśnie lista, która tu nazywa się pole kombi.

Kliknij w nią, a następnie trzymając lewy przycisk myszy „narysuj” jej kształt w wybranym przez Ciebie miejscu arkusza.

Jak wypełnić formant, miastami? Wykonaj następujące kroki:
- Kliknij prawym przyciskiem myszy, w listę.
- Wybierz FORMATUJ FORMANT z menu kontekstowego.
- Pojawi się okno dialogowe FORMATOWANIE FORMANTU.
- Na karcie FORMANT, kliknij ikonę dla pola ZAKRES WEJŚCIOWY.
- Podobnie jak wcześniej, możesz teraz zaznaczyć zakres komórek, w którym wpisałeś nazwy miast. Wracasz do menu, ponownie klikając w ikonę.
- Możesz jeszcze wybrać komórkę, która będzie pojawiała się liczba, reprezentująca pozycję miasta na liście (po jego wybraniu). W tym celu wybierz ikonę przy polu ŁĄCZE KOMÓRKI. Jest to opcja, którą możesz wykorzystać później do różnych obliczeń czy interakcji z użytkownikiem. Na przykład przy wykorzystaniu takich formuł jak: INDEKS i PODAJ.POZYCJĘ.
Lista rozwijana jako formant ActiveX

Ostatni sposób na listę z opcjami do wyboru to tzw. pole kombi (formant ActiveX) Do pewnego momentu postępujesz analogicznie, jak przy kontrolce formularza: klikasz kartę DEWELOPER, wybierasz WSTAW i z sekcji FORMANTY ACTIVEX wybierasz POLE KOMBI.

Podobnie jak wcześniej rysujesz oczekiwany kształt listy w wybranym przez Ciebie miejscu arkusza. W tym miejscu zaczynają się różnice.

Po pierwsze, po narysowaniu formantu zauważysz z pewnością dwie rzeczy:
1. Klikając w formant, nie możesz jeszcze używać listy. Jesteś w tzw. trybie projektowania i aby to zmienić, musisz odkliknąć ikonę TRYB PROJEKTOWANIA na wstążce DEWELOPER.
2. W pasku formuły pojawił się dziwny zapis:
  =OSADŹ(„Forms.ComboBox.1″;””)
  
  Zapis Forms.ComboBox.1 sugeruje, że masz do czynienia z kodem VBA. Ale nie musisz znać VBA, aby wykorzystać formant.
Co musisz zrobić?
- W TRYBIE PROJEKTOWANIA, wybierz formant prawym przyciskiem myszy.
- Z menu kontekstowego wybierz WŁAŚCIWOŚCI.
- Pojawi się dość rozbudowane menu w języku angielskim. Interesują Cię dwie pozycje: ListFillRange i LinkedCell. Pełnią one dokładnie te same funkcje jak w przypadku kontrolki formularza. W pierwszym przypadku wypełniasz listę danymi, a w drugim wskazujesz komórkę, która będzie połączona z wyborem konkretnej wartości z listy.
Niestety nie możesz wybrać zakresów za pomocą myszki. Musisz wpisać odpowiedni adres zakresu. W przypadku kontrolki ActiveX, po wybraniu jakieś pozycji z listy, pojawi się ona bezpośrednio w komórce połączonej (LinkedCell), a nie jako wartość reprezentująca jej pozycję na liście (jak w przypadku kontrolki formularza)

Chcesz lepiej poznać Excela? Zapisz się na Excelness News
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

Poznałeś trzy sposoby na stworzenie listy rozwijanej. Wszystkie przykłady znajdziesz w tym pliku. Czy któryś z nich jest lepszy od innego? Najkrócej mówiąc nie. Wszystko zależy od tego czego konkretnie potrzebujesz.

Najczęściej będziesz wykorzystywał opcję ze sprawdzaniem poprawności. Dzięki tej funkcjonalności stworzysz listę zarówno do formularzy, jak i opcję do interakcji z użytkownikiem w przypadku bardziej skomplikowanych raportów. Wybrana wartość, zawsze pojawia się w komórce, w której stworzysz daną listę.

Kontrolka formularza daje Ci co najmniej dwie nowe możliwości. Po pierwsze do każdej kontrolki możesz przypisać makro. Po drugie po wybraniu wartości z listy, w połączonej komórce nie pojawia się ta wartość tylko liczba reprezentująca pozycję tej wartości na liście. Wbrew pozorom,
w niektórych przypadkach to może być kluczowa funkcja .

Najbardziej złożona opcja to ta z kontrolką ActiveX. Pozwala na oprogramowanie jej przez kod VBA. To daje Ci najwięcej możliwości i zapewnia największą elastyczność. Przykładowo, możesz wypełniać listę dynamicznie w kodzie VBA, nie odwołując się do jakiegokolwiek zakresu komórek.

Wierzę, że teraz tworzenie formularzy i raportów z listami rozwianymi będzie dla Ciebie codziennością 😉 Pochwal się w komentarzu, którą z opcji zacząłeś wykorzystywać.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
5 marca 2019
Slang Excel
Slang Excel freaków

Wtorek. Godzina siódma rano. Włączasz komputer i idziesz zrobić sobie kawę. Czujesz, że Twój mózg jeszcze śpi. Wchodzisz do firmowej kuchni, a oni już tam są. Dział Analiz. Już wiesz, że mimo wczesnej godziny Twój mózg zaraz przeżyje szok…

Stoją w kilka osób nad ekspresem, ale nie robią sobie kawy, więc niewinnie pytasz:

„Ekspres nie działa?”.

W odpowiedzi słyszysz:

„Nie, czekamy bo ekspres wywalił endek na ziarenkach”. Brzmi jak jakiś szyfr, więc myślisz, że to coś poważnego i wracasz do komputera.

Włączasz raport w Excelu dla szefa, ale zamiast raportu wyskakuje Ci jakiś dziwny błąd. Łapiesz za telefon i dzwonisz do Analiz, może już wrócili z tej kawy, skoro ekspres nie działa. W sumie to od razu idziesz, niech sami poklikają, bo myślisz, że i tak nie zrozumiesz tego co Ci powiedzą przez telefon. Tracisz z życia pół godziny, zamiast 5 minut. A gdyby tak dało się ich zrozumieć?

W dzisiejszym artykule chciałabym przybliżyć Wam różne zwroty, które stosują specjaliści od Excela, a które mogą być niezrozumiałe dla osób, nie związanych szczególną więzią z tym programem.

Sama niejednokrotnie pomagając znajomym słyszałam:

„Aga, weź mów po polsku, bo nie rozumiem”. I nie chodziło im o moją uroczą wadę wymowy, tylko o słowa, których używam.

Błąd #N/D!

Zaczynając od wspomnianego wcześniej Endeka. To błąd
#N/D! , czyli błąd wartości niedostępnej. Oznacza, że np. wyszukując pionowo Excel nie znalazł dopasowania dla danego ID.

Idąc dalej, ID to identyfikator – najlepiej unikatowy (ale niekoniecznie) – po którym Excel „dopasuje” do siebie dane. A jak ktoś do Was powie:

„Zaciągnij po miesiącu do swojego arkusza, to samo się zrobi”,

to ma na myśli WYSZUKAJ.PIONOWO lub POZIOMO, gdzie jako ID zastosujesz miesiąc.

„Usztywnianie” adresów

A o co chodzi z tym usztywnianiem? Pewnie czasem słyszeliście tekst o „dodaniu dolarów” albo „usztywnieniu zakresu”. Nie, nie chodzi o wydanie miliona monet w markecie budowlanym na drewniane listewki. Chodzi o dodanie symbolu dolara we wpisany w formule zakres komórek.

Przykładowo, mając formułę:

=SUMA(A1:C1)

i chcąc ją dalej skopiować do następnych komórek, możemy wpisać:

=SUMA($A1:$C1)

Wtedy, po skopiowaniu do komórki niżej, pojawi się:

=SUMA($A2:$C2)

a po skopiowaniu do komórki obok:

=SUMA($A1:$C1)

To znaczy, że przy kopiowaniu komórki – „$” – usztywnia zakres, czyli blokuje zmianę w formule litery określającej kolumnę lub numeru wiersza.

Tabela, tabeli nierówna

Czasami źródłem nieporozumienia bywa słowo TABELA. Jak „człowiek od Excela” mówi „zrób z tego tabelę”, to nie chodzi o dodanie obramowania komórek i pokolorowanie nagłówków, tylko o funkcjonalność WSTAW TABELĘ.

Wystarczy zaznaczyć zakres danych, potem w zakładce WSTAWIANIE kliknąć na napis TABELA (albo skorzystać ze skrótu klawiszowego CTRL+T).

A jeśli usłyszycie w słuchawce telefonu „zrób z tego tabelę krzyżową / przestawną / pivota, będzie Ci łatwiej” to chodzi o wstawienie TABELI PRZESTAWNEJ (też zakładka WSTAWIANIE i opcja TABELA PRZESTAWNA).

Obliczanie automatyczne i ręczne

Kolejnym stwierdzeniem, które może spowodować, że zwątpicie w swoją znajomość języka polskiego, jest:

„Sprawdź czy się nie oblicza automatycznie, bo może lepiej będzie przełączyć się na obliczanie ręczne”.

I wtedy myślisz:

„Jak ma się obliczać? Przecież po to używam arkusza kalkulacyjnego, żeby było automatycznie, a nie żeby liczyć na kalkulatorze.”

Pora na trzy głębokie oddechy, zajrzenie we wstążkę FORMUŁY, w część dotyczącą OPCJI OBLICZANIA. W tym miejscu ustawiacie: czy Excel ma obliczać na bieżąco wszystkie formuły, czy tylko po kliknięciu w przycisk OBLICZ TERAZ albo OBLICZ ARKUSZ. Jest to bardzo przydatne, jeśli arkusz jest napakowany formułami i zwykłe filtrowanie danych trwa 15 minut.

Filtrowanie

A właśnie! Filtrowanie! Stwierdzenie „załóż filtr” nie oznacza nakładania jakiś kolorków czy usuwania wierszy, a funkcjonalność znajdująca się na wstążce DANE.

Zaznaczacie zakres danych (ważne by miał nagłówki kolumn), a następnie klikacie w FILTRUJ. Przy nagłówkach pojawią się małe strzałeczki pozwalające „wyfiltrować” dane z tabeli po określonej wartości z wybranej kolumny. Mając tabelę z imionami i nazwiskami osób możecie wyfiltrować dane tylko dla samych „Agnieszek”.

Zablokuj okienka

Innym stwierdzeniem, które może wywołać paraliż podczas prezentacji danych, jest:

„Czy możesz zablokować okienka, bo nie widzę danych z pierwszej kolumny.”

Zablokowanie okienek znajduje się we wstążce WIDOK . Domyślnie mamy do wyboru zablokowanie górnego wiersza albo pierwszej kolumny. Po wybraniu tej opcji, przy przewijaniu widoku w arkuszu, pierwsza kolumna/wiersz zostanie nam w widoku, a pozostałe kolumny będą się standardowo przewijać.

Mówiąc inaczej: możemy na jednym ekranie, bez ukrywania/grupowania kolumn, zobaczyć jaka wartość znajduję się w kolumnie „Z” widząc ciągle kolumnę „A”.

XLS vs. XLSM

Dochodzimy do końca, czyli zapisania arkusza. Jeśli w arkuszu znajduje się jakiś magiczny przycisk, po kliknięciu którego coś „samo się liczy”, to najprawdopodobniej w tle zaszyty jest jakiś kod VBA, czyli MAKRO.

Makro to taki skrypt, który sam wywołuje wykonanie poszczególnych czynność przez program, nie wymagając od użytkownika klikania wszystkiego „po kolei”.

Jeśli usłyszycie:

„Nie działa, bo masz to zapisane jako zwykły Excel. Zapisz z makrami.”

to musicie:
1. wejść w PLIK
2. wybrać ZAPISZ JAKO
3. wybrać lokalizację, w której chcecie zapisać plik
4. wpisać nazwę pliku
5. w okienku ZAPISZ JAKO TYP wybrać SKOROSZYT PROGRAMU EXCEL Z OBSŁUGĄ MAKR.
To w jakim formacie jest zapisany plik można sprawdzić w prostu sposób, patrząc na nazwę pliku – jeśli Wasz plik ma rozszerzenie XLS, to jest to tzw. „zwykły” Excel. Jeśli rozszerzenie pliku to XLSM to jest to „niezwykły Excel” z obsługą makr.
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

Pamiętajcie: specjalista od Excela to też człowiek, więc jak mówi do Was szyfrem, zawsze można poprosić o wyjaśnienie co miał na myśli. Większość „dziwnych” stwierdzeń da się przetłumaczyć na polski albo słowami, albo pokazując co trzeba kliknąć.

A teraz możecie już spokojnie iść po kawę, bo pewnie już dotarły nowe ziarenka do ekspresu.

Agnieszka Kmieć
26 lutego 2019
Funkcja FRAGMENT TEKSTU
Funkcja FRAGMENT TEKSTU

Znajomość funkcji tekstowych nieraz zaoszczędzi Ci masę czasu i manualnej pracy. Na szkoleniach mam takie ćwiczenie, w którym najpierw proszę uczestników o wyciągnięcie kodu pocztowego z adresu. Później, następuje trudniejsza część – trzeba wyciągnąć cały adres ale bez kodu pocztowego. Funkcja FRAGMENT TEKSTU [Ang. MID] pozwala rozwiązywać takie zadania z łatwością 😉

Dzięki niej możesz wyciągnąć określony fragment zarówno: z początku, z końca, jak i ze środka tekstu.

Jak działa?

Funkcja „wydobywa” z jakiegoś tekstu tyle znaków ile potrzebujemy, od miejsca w tekście, które wskażemy.

Składnia i argumenty
```
=FRAGMENT.TEKSTU(tekst;liczba początkowa;liczba znaków)
```
```
=FRAGMENT.TEKSTU(komórka z tekstem;od którego znaku "wydobywamy" tekst; ile znaków "wydobywamy")
```
Przykład użycia

Masz bazę adresów, które wyglądają tak:
00-690 Warszawa, ul. Woronicza 17
Chcesz wydobyć do osobnych komórek tylko fragment dotyczący ulicy (bez kodu pocztowego)

Użyj =FRAGMENT.TEKSTU(komórka z adresem;8;1000)
Dlaczego 8 – bo ulica zaczyna się od 8 znaku
Dlaczego 1000 – bo adres raczej nie będzie dłuższy niż 1000 znaków (puste znaki na końcu zostaną automatycznie pominięte)

Inne przykłady z parametrami:

Pobierz plik z przykładami.

Co jeszcze powinieneś wiedzieć?
1. Jeżeli wartość liczby początkowej jest większa niż cały tekst, z którego chcemy wydobyć jakiś tekst – funkcja zwróci pusty znak „”. Na przykład: =FRAGMENT.TEKSTU(„Excelness”;10;10) Słowo Excelness ma 9 znaków, a liczba początkowa użyta jako argument to 10.
2. Jeżeli jako argumentu liczba znaków, użyjesz liczby większej niż całkowita długość tekstu – funkcja zwróci tylko znaki do końca tekstu.
3. Wprowadzając wartość mniejszą niż 1, jako argument liczby początkowej – funkcja zwróci błąd #ARG!.
4. W zaawansowanym użyciu funkcji, argument liczba początkowa może być zwracany przez takie funkcje jak: ZNAJDŹ czy SZUKAJ.TEKST.
Podsumowanie
1. Użyj funkcji FRAGMENT TEKSTU, aby wyciągnąć określoną ilość znaków ze środka danego tekstu (lub z początku)
2. Argumenty: liczba początkowa i liczba znaków mogą być: liczbą, komórką (z wartością liczbową) albo funkcją (np. ZNAJDŹ)
3. Liczba początkowa nie powinna przekraczać długości całego tekstu, z którego chcesz wyciągnąć jakiś fragment (w przeciwnym przypadku otrzymasz pusty znak)
4. Liczba znaków jaką podasz jako argument może być większa niż długość całego tekstu.
5. Przy liczbie początkowej mniejszej niż 1 – otrzymasz błąd.
To jedna z moich ulubionych i często używanych funkcji tekstowych. A jakich Ty najczęściej używasz?

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
19 lutego 2019
POWER PIVOT [1]
POWER PIVOT – poznaj i wykorzystaj [1]

Jeżeli chcesz przenieść swoją znajomość Excela na zupełnie nowy poziom – naucz się jak używać Power Pivot (PP). To jedna z tych technologii z „Power” w nazwie, która definitywnie zmieniła sposób zarządzania danymi. Wszystko stało się prostsze, szybsze – po prostu bardziej efektywne.

Rozpoczynam mini cykl artykułów, w których opiszę podstawy tego narzędzia. Po ich lekturze będziesz wiedział, m.in.:
- jak zainstalować / uruchomić,
- do czego służy,
- i jak możesz wykorzystać Power Pivot?
Gdzie ja to mam?

Jeżeli jesteś użytkownikiem Excela w wersji 2013 / 2016 (Professional Plus) lub 2019 (Home & Business), to PP masz w standardzie. To samo dotyczy wersji subskrybowanej Office’a 365. Jeżeli chodzi o wersję 2010, to możesz pobrać go jako dodatek. We wcześniejszych wersjach Office’a, jak i nowszych, w wariantach dla: użytkowników domowych, uczniów i małych firm – niestety nie można z niego korzystać.

Do czego służy?

Najkrócej mówiąc – do modelowania danych. Ale co to właściwie znaczy? Wyobraź sobie dużo różnych excelowych tabel, każda licząca tysiące wierszy. Dane dotyczą tej samej kwestii, np. sprzedaży, produkcji czy ludzi. Ale są rozdzielone na wiele różnych fragmentów. Np. w przypadku pracowników, możesz mieć informacje o:
- wynagrodzeniach,
- przypisaniu organizacyjnym,
- historii ocen.
Teraz, aby połączyć te dane ze sobą na pewno używasz WYSZUKAJ.PIONOWO albo INDEKS i PODAJ.POZYCJĘ.

W przypadku PP, pomiędzy tabelami budujesz relacje. Następnie, możesz dokonać mniej lub bardziej skomplikowanych obliczeń, a potem to wszystko ukształtować w odpowiedni model i pokazać całość np. w formie tabeli przestawnej.

Podstawy

Po zainstalowaniu dodatku (jeżeli musiałeś to zrobić) na wstążce zobaczysz kartę Power Pivot. Klikając w ikonę Zarządzaj, przejdziesz do nowego okna PowerPivot for Excel.

Ponieważ to podstawy, to skupimy się na konkretach, a nie opisywaniu wszystkich opcji. Na screenie powyżej zaznaczyłem dostępne wstążki oraz obszar Widok na wstążce Narzędzia główne. To miejsce „już za chwilę” będzie dla nas szczególnie ważne, bo klikając w Widok diagramu, będziemy tworzyć wspomniane wyżej relacje.

Dla porządku – to nie jedyny sposób, aby relacje utworzyć ale wykorzystam właśnie ten, bo w fajny, wizualny sposób pokaże Ci na czym to wszystko polega.

Zaczynamy

Ponieważ to jest przewodnik praktyczny, ściągnij ten plik. Znajdziesz tam dane, które będą służyły nam do odkrywania kolejnych tajników PP. Jednak zanim zaczniemy, musimy znać cel naszych działań.

Powiedzmy, że jesteś pracownikiem działu HR i otrzymałeś dane o kosztach wynagrodzeń za 2018 rok. Danych jest sporo, bo ponad 60 000 wierszy. Twoim zadaniem jest przygotować raport, który pokaże:
- jak wyglądały koszty w podziale na grupy i podgrupy,
- które piony i działy kosztowały najwięcej,
- jaki jest rozkład kosztów pomiędzy miastami,
- czy premie były wypłacane regularnie czy podlegały jakimś wahaniom.
Gdy przyjrzysz się zawartości poszczególnych arkuszy:

szybko zorientujesz się, że większość informacji znajduje się w arkuszu DANE, ale nie są to informacje wystarczające do zrealizowania raportu. Jak bowiem, pokazać rozkład kosztów pomiędzy miastami, skoro w tym arkuszu w ogóle nie ma miast?

Jeżeli dobrze znasz i używasz funkcji WYSZUKAJ.PIONOWO, z pewnością teraz krzykniesz: „No jak to jak?! Przecież dane o mieście są w arkuszu MPKI. Mogę dołączyć informację o mieście do arkusza DANE, wykorzystując WYSZUKAJ.PIONOWO.”

Masz rację. Ale jeśli przeanalizujesz dane, okaże się, że w podobny sposób musisz postąpić z: grupą kosztów, działami / pionami czy okresami. Za każdym razem do arkusza DANE dodajesz kolejne kolumny i formuły, co powoduje że:
- niepotrzebnie duplikujesz dane,
- spowalniasz i powiększasz plik poprzez wielokrotne użycie formuł.
Czy można inaczej? Lepiej? Oczywiście 🙂 Dzięki wykorzystaniu Power Pivot. Ale o tym jak to zrobić dowiesz się z kolejnego odcinka.

Podsumowanie

W pierwszej części cyklu o Power Pivot dowiedziałeś się:
- czym jest i do czego służy to narzędzie,
- poznałeś podstawowe terminy z nim związane, takie jak: relacja czy model danych,
- dowiedziałeś się jak zainstalować dodatek, jeżeli nie posiadasz PP w swojej wersji Excela,
- zapoznałeś się z przykładowymi danymi i zadaniem, dzięki któremu praktycznie poznasz narzędzie.
Zapraszam Cię do kolejnej części, a tymczasem napisz proszę w komentarzu czy miałeś już styczność z tą funkcjonalnością Excela, a jeżeli nie to czy planujesz ją wykorzystać w swojej pracy?

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
12 lutego 2019
Skróty klawiszowe Excel
20 skrótów klawiszowych, które musisz znać

Skróty klawiszowe Excel, to temat tyleż wdzięczny co oklepany 😉 Dlatego wybrałem dla Ciebie 20 najważniejszych. To takie skróty, które wg mnie musisz znać, bo oszczędzają czas i umożliwiają efektywniejszą pracę.

Ponadto przygotowałem narzędzie i prostą metodę, abyś je wszystkie zapamiętał i używał. To wszystko na końcu artykułu. Ale zanim zaczniesz ćwiczyć, przekonaj się że za niektórymi z nich stoi trochę więcej niż proste polecenie Excel.

#01 Poruszanie się po arkuszach

Szybkie przechodzenie pomiędzy arkuszami, bez odrywania rąk od klawiatury, to umiejętność, która przydaje się nader często.

CTRL + PgDown pozwala na przejście do kolejnego arkusza po prawej stronie (następny arkusz), natomiast CTRL + PgUp – po lewej stronie (poprzedni arkusz).

#02 Wklejanie specjalne

Kopiuj-wklej zna chyba każdy. Ale z dobrodziejstw opcji wklejania specjalnego nie wszyscy zdają sobie sprawę. Być może dlatego, że wywołanie tych opcji z menu kontekstowego (prawym przyciskiem myszy) zabiera za dużo czasu.

Skrót CTRL + ALT + V wywoła okno dialogowe, w którym znajdziesz wszystkie bardziej zaawansowane opcje wklejania.

#03 Włączanie i wyłączanie autofiltra

Filtrowanie danych to jedna z podstawowych czynności jakie zapewne robisz w Excelu. Skrót CTRL + SHIFT + L pozwala na szybkie zakładanie i zdejmowanie filtra na zakresie danych.

Nie każdy wie, że ta opcja pozwala także szybko pozbyć się wszelkich filtrów, jakie założyłeś na różnych kolumnach. Zamiast czyścić filtr z poszczególnych kolumn – po prostu użyj skrótu.

#04 Poruszanie się po zakresie

O tych skrótach zawsze mówię na szkoleniach. CTRL + strzałki kierunkowa: góra, prawo, dół, lewo – pozwolą szybko przemieścić się do skrajnych komórek w kolumnach i wierszach zakresu danych. Opcja niezwykle przydatna, gdy masz do czynienia z tabelą, która np. liczy setki tysięcy wierszy.

#05 Zaznaczanie danych do skrajów zakresu

Zamiast przewijać ekran, męcząc palce i kółko w myszce – używaj skrótów, które pozwalają zaznaczyć cały zakres danych od – do.

CTRL + SHIT + strzałki umożliwiają przyspieszenie operacji szczególnie, gdy używasz takich funkcji jak: SUMA.WARUNKÓW, LICZ.WARUNKI czy WYSZUKAJ.PIONOWO (zaznaczanie tabeli, która jest źródłem danych)

#06 Znajdź pasujące

Prosty ale potrzebny skrót CTRL + F, który umożliwia wyszukiwanie wartości czy tekstów. Wywołując go, pojawi się okno dialogowe: ZNAJDOWANIE I ZAMIENIANIE. I to właśnie kolejny skrót daje nawet więcej możliwości.

#07 Zamień

Kombinacja klawiszy CTRL + H, pozwala zamieniać określone ciągi znaków, czy liczb na inne, wybrane przez Ciebie. Wydawałoby się proste i w sumie mało przydatne. Ale ta funkcjonalność jest podstawą do wielu fajnych i skutecznych sztuczek, które znacząco ułatwiają pracę.

Na przykład wyobraź sobie, że masz formułę w setkach komórek, która odwołuje się do kolumny A i kolejnych wierszy. Z jakieś przyczyny wszystkie te formuły powinny odwoływać się teraz do kolumny B i to jeszcze z adresowaniem mieszanym (znak dolara przed B)

Co zrobić? Użyj CTRL + H i zamień A na $B w zaznaczonym uprzednio zakresie. Voila! 😉

#08 Zacznij nową linię tekstu w komórce

Kilka wierszy w jednej komórce? Żaden problem. Podczas edycji komórki, użyj ALT + ENTER. Rozpoczniesz nową linię tekstu.

Oczywiście, to jak ten tekst będzie w komórce wyglądał, zależy od kilku czynników, takich jak: szerokość kolumny, wysokość wiersza, wielkość czcionki czy tzw. zawijanie tekstu w komórce.

#09/10 Wypełnij w dół lub w prawo

Bardzo pomocne skróty, które pozwalają skopiować zawartość komórki powyżej aktualnie zaznaczonej – CTRL + D, lub zawartość komórki położonej po lewej stronie od obecnie zaznaczonej – CTRL + R.

Co ciekawe, możesz wykorzystać skróty w taki sposób aby skopiować zawartość danej komórki do więcej niż jednej komórki jednocześnie. W taki przypadku, zaznaczasz komórkę źródłową i cały zakres docelowy.

Na przykład, jeśli chcesz skopiować zawartość komórki A1, do 5 komórek poniżej – zaznacz komórki A1:A6 i wciśnij kombinację CTRL + D.

#11 Formatuj co chcesz

Na wstążce masz dostęp do najczęściej używanych opcji formatowania. Czasami jednak, łatwiej będzie wywołać okienko formatowania, niż szukać odpowiedniego polecenia na wstążce. Jeśli skorzystasz ze skrótu CTRL + 1 możesz dostać się do opcji formatowania niemal wszystkiego: od komórki po elementy wykresu.

#12 Edytuj formułę

Formułę w komórce możesz edytować na dwa sposoby: albo dwukrotnie szybko klikasz na komórkę z formułą, albo używasz skrótu F2.

#13 Zmień adresowanie na względne / bezwzględne

Adresowanie względne / bezwzględne zazwyczaj sprawia sporo problemów początkującym adeptom Excela. I nawet gdy już rozumieją zasady i reguły używania tych form adresowania, często marnują niepotrzebnie czas na manualne zmiany adresów.

Klawisz F4, w przypadku gdy edytujesz formułę pozwala Ci bardzo łatwo przełączać się między adresowaniem: względnym, bezwzględnym i mieszanym.

Spróbuj. Wpisz w dowolnej komórce =A2, a następnie naciśnij F4. I jeszcze raz. I jeszcze raz, i kolejny… 😉 Otrzymasz kolejno: $A$2, A$2, $A2, A2.

#14/15 Wstaw / usuń wiersze lub kolumny

Zaznacz jeden lub wiele wierszy / kolumn (tyle ile chcesz wstawić) i użyj skrótu CTRL + SHIFT + +. Pamiętaj, że aby zaznaczyć cały wiersz lub kolumnę – klikasz w numer wiersza lub literę kolumny.

Analogicznie robisz, jeśli chcesz usunąć wiersze / kolumny. Zaznaczasz co chcesz, a następnie klikasz w klawisze CTRL + – (minus)

#16/17 Ukryj / odkryj wiersze lub kolumny

Ukrywanie i odkrywanie kolumn i wierszy jest jeszcze prostsze. Nie musisz bowiem, zaznaczać całego wiersza czy kolumny. Stojąc w dowolnej komórce, bądź mając zaznaczony zakres komórek – użyjesz:
- żeby ukryć kolumny – CTRL + 0,
- aby je odkryć – CTRL + SHIFT + 0,
- żeby ukryć wiersze – CTRL + 9,
- aby wiersze ukryć – CTRL + SHIFT + 9.
#18 Wstaw wykres

Zaznacz zakres danych z których chcesz stworzyć wykres i użyj klawiszy ALT + F1. Wstawisz tym samym wykres osadzony w arkuszu, ze wszystkimi typowymi elementami, jak: tytuł, legenda i osie.

#19 Wstaw autosumowanie

Opcja autosumowania jest bardzo przydatna i nie każdy wie, że można ją wykorzystać na wiele różnych sposobów.

W najprostszej wersji ustawiasz się w komórce poniżej, lub obok danych liczbowych i klikasz ALT + =. Excel domyśla się co chcesz podsumować i automatycznie proponuje zakres. Czasami domyśla się źle 😉

Na przykład, gdy w nagłówku tabeli masz miesiąc oznaczony jako kolejna liczba: 1…12, a poniżej dane, które chcesz podsumować – Excel potraktuję liczbę oznaczającą miesiąc jako daną, którą należy podsumować. W takim przypadku, musisz oczywiście skorygować program.

W wersji bardziej złożonej możesz podsumować jednocześnie kilka, bądź więcej kolumn czy wierszy. Zaznaczasz cały zakres z dodatkowym pustym wierszem (bądź kolumną – jeżeli sumujesz wiele wierszy) na wstawienie sumy i używasz skrótu.

Co więcej, możesz zaznaczyć całą tabelkę z danymi wraz z dodatkowymi pustym wierszem i kolumną. Używając ALT + =, wstawisz jednocześnie sumę dla całej tabeli (każdego wiersza i kolumny)

#20 Wypełnij komórki tą samą wartością

CTRL + ENTER to prosty skrót, który zrobi wrażenie na nieznającym tematu obserwatorze 🙂 Dzięki niemu wypełnisz dowolny zakres komórek tym czym chcesz. Jak? Kolejne kroki:
1. Zaznacz interesujący Cię zakres komórek.
2. Wpisz co chcesz w nich umieścić.
3. Kliknij CTRL + ENTER aby zatwierdzić całość.
Podsumowanie

A teraz obiecana metoda na zapamiętanie tego wszystkiego – PDF ze wszystkimi opisanymi tu skrótami w formie gotowej do wydrukowania i wycięcia,

Rzucam Ci wyzwanie 😉 Weź jeden skrót na tydzień. Wytnij. Przyklej do monitora. Za każdym razem, gdy będziesz pracować w Excelu – postaraj się go zastosować – gdy tylko będzie to możliwe. Jest duża szansa, że po tygodniu utkwi Ci w pamięci na tyle, że zaczniesz używać regularnie 😉

Być może jakiegoś skrótu zabrakło Ci w tym zestawieniu? Daj proszę znać w komentarzu. Może pdf zostanie w przyszłości poszerzony.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
5 lutego 2019
Funkcja LICZ JEŻELI
Funkcja LICZ JEŻELI

Funkcja LICZ JEŻELI [Ang: COUNTIF] zlicza ile razy jakaś wartość wystąpiła w podanych przez Ciebie komórkach. Dzięki niej możesz zliczać daty, liczby i tekst, które spełniają określone przez Ciebie kryteria. Właściwie to jedno kryterium.

Funkcja wspiera operatory logiczne: >,<,=,>=,<=,<> oraz operatory: * i ? (gwiazdkę i znak zapytania)

Jak działa?

Funkcja policzy ile razy jakaś wartość wystąpiła w danym zakresie komórek.

Składnia i argumenty
```
=LICZ.JEŻELI(zakres;kryteria)
```
```
=LICZ.JEŻELI(podajesz gdzie szukasz;podajesz co chcesz znaleźć)
```
Przykład użycia

Masz listę transakcji z klientami. Chcesz wiedzieć ile razy dany klient dokonał zakupu. Powiedzmy, że nazwy klientów znajdują się w zakresie B2:B10000, a szukanym klientem jest firma ABC.

Użyj:
```
=LICZ.JEŻELI(B2:B10000;"ABC")
```
i wyniku otrzymasz ilość wystąpień tego klienta w podanym zakresie.

Inne przykłady z parametrami:

Pobierz plik z przykładami.

Co jeszcze powinieneś wiedzieć?
1. Nienumeryczne kryteria (np. tekst) umieszczasz w pomiędzy znakami cudzysłowu.
2. Wielkość liter nie ma znaczenia.
3. Możesz stosować operatory typu: >,<.<>,>=,<= – np. ile razy sprzedano coś za więcej niż 1000 zł (>1000)
4. W kryteriach możesz używać operatorów ? i * (znak zapytania i gwiazdka) Znak zapytania zastępuje jeden pojedynczy znak, a gwiazdka ciąg znaków. Przykładowo, jeżeli wpiszesz w kryterium wyrażenie „pol*”, to funkcja LICZ.JEŻELI policzy np. komórki zawierające słowa: polska, polonia, polityka itd.
5. Jeżeli chcesz faktycznie znaleźć znak ? lub * – wpisując je w kryteria musisz dodać przed nimi znak tyldy „~”.
6. Uważaj na przypadkowe spacje lub inne znaki niedrukowalne, w zawartości komórek. W takich przypadkach funkcja LICZ.JEŻELI może dać błędne wyniki. Możesz wykorzystać funkcje: USUŃ.ZBĘDNE.ODSTĘPY i OCZYŚĆ, aby wyeliminować problem.
Podsumowanie
1. Użyj funkcji LICZ JEŻELI, aby policzyć ilość komórek spełniających jedno kryterium.
2. Kryterium może być: liczba, data, ciąg tekstowy.
3. Wielkość liter w kryterium nie ma znaczenia.
4. Możesz używać operatorów matematycznych oraz znaków ? i *.
5. Uważaj na niedrukowalne znaki i spacje w zakresie, który podlega przeliczeniu.
Stare powiedzenie mówi: „Umiesz liczyć? Licz na siebie!” 😉 Jeżeli artykuł Ci pomógł zostaw proszę komentarz i podziel się ze znajomymi.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
29 stycznia 2019
Nie kombinuj – wklej specjalnie!
Nie kombinuj – wklej specjalnie!

Na temat kopiowania i wklejania danych w Excelu można by napisać książkę. Albo dwie – jedną o zwykłym, drugą o specjalnym. Kopiowanie w Excelu to nie sztuka – wystarczy zaznaczyć to co chcemy skopiować i wcisnąć na klawiaturze CTRL + C.

Sztuką jest wkleić te dane. Wkleić tak, żeby ich później nie modyfikować przez godzinę. Dajmy więc wykazać się Excelowi i pozwólmy mu zrobić tę pracę za nas.

Zacznijmy od podstawowej różnicy: mamy dwa skróty klawiszowe do wklejania danych. Pierwszy, czyli CTRL + V – pozwoli nam po prostu wkleić wcześniej skopiowane dane. Drugi, czyli CTRL + ALT + V – wywoła okno wklejania specjalnego, które daje możliwość zmodyfikowania wklejanych danych.

Najczęściej korzystamy z dwóch opcji: wklejamy wartości albo formuły. Można i tak. Ale po co potem dodatkowo tracić czas na ustawianie np. formatowania komórek i szerokości kolumn?

Okno wklejania specjalnego wygląda następująco:

Wklejanie specjalnie – podstawy

Zaznaczamy interesujące nas opcje i klikamy przycisk OK. Jak widać mamy cztery sekcje:
- wklej,
- operacja,
- pomijaj puste,
- transpozycja,
- i wklej łącze.
Najlepsza zabawa jest wtedy, gdy połączymy po jednej opcji z każdej sekcji ze sobą np. wklej wartości, wykonując na nich operacje dodawania i transpozycji wklejonych danych.

Trzy opcje z pierwszej sekcji to dla mnie „ułatwiacze” życia – zaznaczając jedną z nich nie musimy męczyć się na nowo z ustawieniem formatowania we wklejonych komórkach:
- wszystko poza obramowaniem – wkleja dane jak przy zwykłym kopiuj – wklej, pomijając ustawienia obramowania komórek,
- szerokość kolumny – wkleja zachowując szerokość kolumn – przydaję się, gdy kopiujemy całą kolumnę, ale chcemy zachować jej pierwotną szerokość,
- formaty – nie kopiuje wartości z komórek, a formatowanie na nich ustawione, czyli np. kolor czcionki, format liczb, obramowanie itp.,
- formuły / wartości i formaty liczb – wraz z zawartością skopiowane zostaną ustawienia dotyczące formatowania liczb, np. liczbowe, data, księgowe.
Wklej specjalnie i operacje matematyczne

Bardzo ciekawą i użyteczną opcją są operacje. Po skopiowaniu i wklejeniu danych w nowy zakres (w którym znajdują się już dane), Excel na danych ze starego zakresu wykonana działanie matematyczne: dodając, odejmując, mnożąc, albo dzieląc wartości z nowego zakresu.

Na przykład – mając ciąg liczb od 1 do 5, i chcąc je wszystkie przemnożyć przez 1,2, możemy skopiować samą wartość 1,2, zaznaczyć interesujący nas zakres i wkleić specjalnie wartość 1,2 zaznaczając operację mnożenia:

Ja dodatkowo wklejając specjalnie zaznaczyłam w pierwszej sekcji „wartości”, aby nie zmieniło się formatowanie danych na pogrubioną czcionkę.

Trudne słowo – „transpozycja”

Równie ciekawe jest wklejanie specjalnie ze „sprawdzaniem poprawności”, czyli np. listy rozwijanej. Najbardziej przydaje się, gdy mamy już arkusz z taką listą, w którym część danych jest uzupełniona, ale w części komórek brakuje nam naszej listy. Po wklejeniu specjalnym ze sprawdzaniem poprawności nie utracimy wcześniej wpisanych danych, a dodamy brakującą listę rozwijaną.

Kolejna opcja to transpozycja, czyli zmiana pozycji np. z wierszy na kolumny. Po wklejeniu danych z zaznaczoną opcją transponuj, zostaną one obrócone o 90 stopni. Mając listę danych ułożonych w kolejnych wierszach, możemy je skopiować i wkleić np. jako nagłówki kolumn:

Opcja „pomijaj puste” jest przydatna, jeśli wklejamy dane w zakres, w którym już jakieś dane się znajdują. Jeśli mamy dane od dwóch osób, które wypełniały tę samą kolumnę, ale w innych wierszach / kolumnach (pozostawiając resztę tabeli „pustą”), po skopiowaniu i wklejeniu danych z zaznaczeniem opcji „pomijaj puste”, Excel nałoży dane z jednej tabeli na drugą:

Aparat fotograficzny w Excelu?

A czy wiecie, że w Excelu można robić zdjęcia? Jest taka funkcjonalność, która nazywa się aparat fotograficzny. Bardzo ułatwia pisanie maili, czy robienie prezentacji 😉

Po zaznaczeniu zakresu danych klikamy na ikonę aparatu, a następnie po kliknięciu w dowolnym miejscu wkleja nam się obraz z danymi. Najlepsze jest to, że ten obraz będzie ciągle połączony z zakresem, który zaznaczyliśmy, czyli jeśli w nim coś zmodyfikujemy, to zmieni się również we wklejonym przez nas zdjęciu.

Ta funkcja jest domyślnie ukryta. Trzeba dostosować wstążkę, aby ją włączyć: OPCJE PROGRAMU EXCEL > DOSTOSOWYWANIE WSTĄŻKI > POLECENIA, KTÓRYCH NIE MA NA WSTĄŻCE.

Podsumowanie

Gorąco Was zachęcam do wklejania danych za pomocą opcji „wklej specjalne” – to naprawdę zaoszczędzi Wam sporo dodatkowej pracy po wklejeniu danych.

Agnieszka Kmieć

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
22 stycznia 2019
Tabela przestawna – 5 sztuczek, które musisz znać
Tabela przestawna – 5 sztuczek, które musisz znać

Tabela przestawna daje Ci elastyczność, oszczędność czasu i ogromne możliwości. Elastyczność, bo układasz dane jak chcesz. Oszczędność czasu, bo zamiast tworzyć skomplikowane formuły – wszystko dzieje się online. Możliwości, bo dostajesz narzędzie dzięki któremu zrobisz rzeczy, których nie zrobisz (łatwo) w żaden inny sposób.

Dzięki lekturze artykułu dowiesz się jak:
1. grupować czas i daty,
2. pokazać udział procentowy,
3. szybko podzielić dane,
4. pokazać top 5 największych lub najmniejszych wartości,
5. jeszcze szybciej analizować dane.
Pogrupuj wg czasu i dat

Jeżeli masz dane, w których znajduje się kolumna z datą lub czasem, na pewno przyda Ci się umiejętność szybkiego przechodzenia pomiędzy różnymi interwałami czasu. W tabeli przestawnej mam opcję grupowania, która pozwala Ci szybko przejść z dni, na: miesiące, kwartały czy lata. Podobnie jeżeli masz czas – możesz grupować wg sekund, minut czy godzin. Zobacz jak.

Pokaż udział procentowy

Tabela przestawna umożliwia Ci błyskawiczne podsumowanie dużej ilości danych. Albo wyciągnięcie średniej. A co jeżeli zamiast, albo obok sumy wielkości sprzedaży dla danego regionu chciałbyś zobaczyć jaki udział stanowiła ta sprzedaż w całości? Jest na to sposób!

Podziel dane na arkusze z tabelami przestawnymi

Zdarza Ci się wysyłać te same dane ale podzielone na wiele różnych osób? Przykładowo, masz tabelę przestawną w kosztami wynagrodzeń ale każdy menedżer może widzieć tylko swój zakres. Zobacz jak szybko możesz podzielić takie dane.

Pokaż tylko najlepszych / najgorszych

W gąszczu danych czasami trzeba się skupić na najważniejszych informacjach. Dobrym sposobem może być pokazywanie tylko najlepszych lub najgorszych w danej kategorii. W tabeli przestanej robisz to tak.

Analizuj dane szybciej

Czasami filtrowanie danych w tabeli może być nieco uciążliwe. Musisz zaznaczyć coś w polu filtru raportu, odznaczyć w polu wierszy itd. Odkąd pojawiły się fragmentatory życie stało się prostsze 🙂

Podsumowanie

W artykule opisałem tylko część z ogromu możliwości jakie daje tabela przestawna. Ale opisane sztuczki pozwolą Ci:
- zaoszczędzić sporo czasu,
- pozwolą na realizację zadań które wcześniej były trudne lub niemożliwe,
- rozbudują Twój arsenał możliwości analizy danych.
Daj mi znać w komentarzu co udało Ci się wykorzystać. A może chcesz się z nami podzielić swoim ulubionym trikiem? 😉

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
15 stycznia 2019
Jak drukować nagłówek i stopkę w Excelu?
Jak drukować nagłówek i stopkę w Excelu?

Drukowanie arkuszy to jedna z podstawowych czynności jakie robimy w Excelu. Na szkoleniach często powtarza się pytanie – jak drukować nagłówek i stopkę w Excelu? Dowiesz się tego, czytając ten artykuł.

Jak wstawić nagłówek i stopkę?

Masz kilka sposobów, aby wstawić nagłówek i stopkę do arkusza.

Sposób pierwszy
- Przejdź na kartę UKŁAD STRONY.
- W OBSZARZE WYDRUKU, kliknij w małą ikonkę w prawym dolnym rogu.
- Następnie w okienku USTAWIENIA STRONY, kliknij w kartę NAGŁÓWEK / STOPKA.
- Kliknij w przyciski NAGŁÓWEK NIESTANDARDOWY / STOPKA NIESTANDARDOWA.
- Teraz możesz edytować nagłówek / stopkę, w jednej z trzech sekcji strony: lewa, centralna i prawa.
- Użyj ikon, aby np.: sformatować tekst nagłówka / stopki, wstawić numer strony albo obrazek.
Sposób drugi
- Przejdź na kartę WIDOK.
- W obszarze WIDOKI SKOROSZYTU, wybierz UKŁAD STRONY.
- Kliknij w jeden z obszarów nagłówka / stopki, w wybranej przez Ciebie sekcji.
- Pojawi się dodatkowa karta PROJEKTOWANIE, gdzie możesz wybierać spośród możliwych opcji edycji.
Jak edytować nagłówek i stopkę?

Gdy już zdecydujesz się na jeden ze sposobów edycji nagłówka lub stopki – sposób drugi daje większą wygodę i elastyczność – możesz wykorzystać kilka różnych opcji.

Po pierwsze w sekcji NAGŁÓWEK i STOPKA karty PROJEKTOWANIE użyj gotowych szablonów formatowania, np. możesz wstawić nazwę arkusza i numer strony.

Po drugie, masz możliwość wstawienie jednego lub wielu z dostępnych elementów nagłówka / stopki. Na przykład obrazu czy pełnej ścieżki pliku.

Po trzecie w sekcji nawigacja – przełączysz się szybko pomiędzy edycją nagłówka, a stopki.

I w końcu, w sekcji opcje możesz zdecydować, że:
- inny nagłówek / stopka pojawi się na pierwszej stronie, a inny na pozostałych,
- inny nagłówek / stopka pojawi się na stronach parzystych, a inny na nieparzystych.
Te opcje mogą być szczególnie przydatne przy drukowaniu arkuszy, składających się z wielu stron.

Jak drukować nagłówek i stopkę?

Jeżeli wstawiłeś już wszystkie potrzebne elementy w pola nagłówka i stopki, to czas na drukowanie. Aby przygotować cały arkusz, czy arkusze do druku możesz skorzystać z karty UKŁAD STRONY. W poniższych sekcjach uzyskujesz pełną kontrolę nad wydrukiem.

Ale my skupimy się na innym sposobie:
- Przejdź na kartę PLIK i wybierz opcję DRUKUJ.
- Kliknij w link do USTAWIEŃ STRONY. To te same opcje, z których możesz skorzystać wstawiając i modyfikując nagłówek / stopkę.
- Teraz klikając kartę NAGŁÓWEK / STOPKA widzisz i nadal możesz edytować te elementy, które wcześniej wstawiłeś. U mnie jest to napis „staging.excelness.com” i logo w nagłówku oraz data i numer strony w stopce.
Podsumowanie

Jak widzisz wstawianie, edycja i późniejsze drukowanie nagłówków i stopek w arkuszach nie jest skomplikowane. Jeżeli zależy Ci na najszybszej drodze – to pewnie będzie to ścieżka PLIK > DRUKUJ > USTAWIENIA STRONY > NAGŁÓWEK / STOPKA.

Z kolei pełną kontrolę i możliwość edycji nagłówka i stopki uzyskujesz przechodząc do: WIDOK > UKŁAD STRONY > PROJEKTOWANIE. To w tym miejscu zmodyfikujesz rozmiar logo, jakie wstawiłeś w nagłówku czy wstawisz taką samą stopkę jednocześnie na wielu arkuszach. Jesteś ciekawy jak to się robi?

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
8 stycznia 2019