Tag: Zaawansowany

Dashboard: interaktywny raport z efektem WOW [1/2]
Dashboard, czyli interaktywny raport z efektem WOW [1/2]

Robisz raporty w pracy? Jeżeli chcesz zrobić wrażenie na szefie, koniecznie przeczytaj ten artykuł. Mogę Cię zapewnić, że interaktywny dashboard, zamiast tradycyjnej tabelki to najszybsza droga do „efektu wow”. Wiem to ze swojego własnego doświadczenia 😉

Czym jest dashboard?

Dashboard to rodzaj raportu z kluczowymi wskaźnikami (KPI), najczęściej w postaci wykresów, tabel czy widoków ułatwiający menedżerom podejmowanie decyzji. Często wykorzystuje formatowanie warunkowe czy niestandardowe formatowanie komórek.

Najczęściej dashboard mieści się na jednej stronie (ekranie komputera) i często jest interaktywny, tzn. użytkownik może np. zmienić okres czy inne parametry aby zobaczyć wybrane metryki w innym ujęciu. Wyborów dokonuje się za pomocą fragmentatorów, list rozwijanych, przycisków czy przełączników.

Dashboard w 6 krokach

W tym i kolejnym artykule nie będziemy się jednak zajmować teorią. Skupimy się na konkretnym rozwiązaniu i w kolejnych krokach przygotujemy gotowe rozwiązanie.

Przygotujemy dashboard prezentujący dane sprzedażowe. Wykorzystamy przy tym tylko jedną technikę, używając: tabel, wykresów przestawnych oraz fragmentatorów. Zacznij od ściągnięcia pliku z danymi, aby krok po kroku zrobić ze mną ten raport.

Krok 1: Zaprojektuj dashboard

Ale zanim w ogóle otworzysz Excela, warto wziąć ołówek i kartkę papieru… Nie żartuję! Ważne, abyś się na początku zastanowił się i zaprojektował na kartce papieru:
- jakimi danymi dysponujesz,
- co jest ważne,
- co chcesz pokazać,
- gdzie chcesz to pokazać,
- jak chcesz to pokazać.
To zdecydowanie ułatwi późniejszą pracę.

W naszym przykładzie dysponujemy danymi o sprzedaży w regionach, per przedstawiciel handlowy. Początkowy plik z danymi znajdziesz tu (takimi danymi dysponujesz)

Powiedzmy, że nasz wymyślony szef chciałby widzieć (to jest ważne i to musisz pokazać) :
- dane o sprzedaży per region,
- najlepszych 5 przedstawicieli pod kątem wielkości sprzedaży,
- jak wyglądał zysk per miesiąc.
Te trzy wymiary pokażemy na wykresach słupkowych i kolumnowych, w takim układzie (gdzie i jak):

Ponadto wprowadzimy elementy sterujące raportem, tak aby raport był interaktywny.

Krok 2: Przygotuj dane

Teraz już możemy otworzyć Excela 😉 Zrobisz to ze mną? Analizując dostępne dane widzisz, że nie masz w bazie dwóch istotnych informacji: regionu i zysku.

Po pierwsze zamieńmy dane w arkuszu BAZA na postać tabeli. Wybierz FORMATUJ JAKO TABELĘ na wstążce NARZĘDZIA GŁÓWNE. Ułatwi to nam późniejsze działania (np. dodawanie formuł)

Po drugie, brakującą informację o regionie możemy dodać za pomocą WYSZUKAJ PIONOWO. W arkuszu PH mamy zarówno identyfikator przedstawiciela handlowego jak i region.

Po trzecie – zysk. Dla uproszczenia przyjmijmy, że to prosta różnica między przychodami i kosztami. Wprowadź formułę do tabeli w arkuszu BAZA.

W rezultacie tych działań mamy wstępnie przygotowane dane, które są podstawą do dalszych działań.

Krok 3: Stwórz kalkulacje do dashboardu

Nasz projekt zakłada wyświetlanie danych na 3 wykresach. Wykorzystamy do tego wykresy przestawne. Ale po kolei:
- Tworzymy 3 nowe arkusze, które nazwiemy T1, T2, T3 (T od tabeli)
- Klikając na dowolnej komórce z danymi w arkuszu BAZA tworzymy pierwszą tabelę przestawną arkuszu T1: WSTAWIANIE > TABELA PRZESTAWNA.
- Na pierwszym wykresie mamy pokazać sprzedaż per region. Zatem nasza tabela będzie miała taką postać.
- Powtarzamy drugi krok 2 dwa razy, wstawiając tabele 2 i 3 w arkusze T2 i T3.
- Tabela 2 będzie prezentowała najlepszych 5 przedstawicieli. Natomiast Tabela 3 to zysk w czasie.
- W kolejnym kroku do każdej tabeli przestawnej tworzymy wykres przestawny i odpowiednio go formatujemy. To jak to zrobisz, oczywiście zależy całkowicie od Ciebie. Przykładowe wykresy zobaczysz na screenie powyżej lub w końcowej wersji pliku.
- Tworzymy kolejny arkusz, na którym umieścimy nasze wykresy – DASHBOARD.
- Wycinamy kolejne wykresy CTRL+X i wklejamy w arkusz DASHBOARD, dopasowując ich położenie do naszego projektu.
- Dodajemy inne elementy, takie jak chociażby tytuł czy opisy.
Mając przygotowane dane i odpowiednią strukturę pliku, możesz przystąpić do wprowadzenia interaktywności do Twojego dashboardu. O tym jak to zrobić przeczytasz w kolejnej części artykułu.
11 czerwca 2019
Analiza danych w POWER PIVOT
POWER PIVOT [3]: Analiza danych

Analiza danych oraz ich prezentacja dzięki Power Pivot stają się prostsze. Zyskujesz więcej możliwości, bo możesz korzystać z szerszego zakresu danych (powiązanych w relacje) Ponadto możesz wykorzystywać dodatkowe miary, aby lepiej zobrazować i zrozumieć dane. O tym przeczytasz w trzeciej części cyklu o Power Pivot.

W pierwszej części serii o dowiedziałeś się do czego służy
Power Pivot oraz poznałeś podstawy. Część druga nauczyła Cię jak budować relację pomiędzy tabelami z danymi. Skończyliśmy tworząc plik, który będzie podstawą do dalszych ćwiczeń.

Analiza danych w Power Pivot – jak to zrobić?

Na początku serii postawiliśmy przed sobą pewne zadanie, a mianowicie – na podstawie zestawu względnie niepowiązanych danych mamy odpowiedzieć na następujące pytania:
- 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?
Zanim odpowiesz na te pytania, musisz wiedzieć w jaki sposób możesz stworzyć raport na podstawie danych powiązanych relacjami w modelu danych.
- Na wstążce POWER PIVOT, kliknij ikonę ZARZĄDZAJ w sekcji MODEL DANYCH.
- Będąc w POWER PIVOT, na wstążce NARZĘDZIA GŁÓWNE kliknij w dolną część przycisku TABELA PRZESTAWNA.
Zobaczysz kilka opcji, z których część na pewno nie będzie dla Ciebie zaskoczeniem. Na przykład tabela przestawna czy wykres przestawny.

Co reprezentują pozostałe opcje?
- Wykres i tabela (w poziomie) – utworzysz jednocześnie wykres i tabelę przestawną, które będą domyślnie położone obok siebie, w poziomie.
- Wykres i tabela (w pionie) – utworzysz jednocześnie wykres i tabelę przestawną, które będą domyślnie położone obok siebie, w pionie.
- Dwa wykresy (w poziomie)- utworzysz jednocześnie dwa wykres przestawne, które będą domyślnie położone obok siebie, w poziomie.
- Dwa wykresy (w pionie) – utworzysz jednocześnie dwa wykres przestawne, które będą domyślnie położone obok siebie, w pionie.
- Cztery wykresy – utworzysz jednocześnie cztery wykres przestawne, które będą domyślnie ułożone w formie szachownicy.
- Spłaszczona tabela przestawna – utworzysz tabelę przestawną w układzie tabelarycznym (kolejne kategorie znajdą się w osobnych kolumnach)
To, że analizy danych możesz dokonać w tabeli przestawnej jest dość naturalne. W końcu to podstawowe narzędzie Excela do tego typu zastosowań. To co odróżnia wykorzystanie tabeli przestawnej w przypadku Power Pivot, to możliwość korzystania z danych z wielu różnych tabel jednocześnie (o ile wcześniej zostały powiązane relacjami)

Analiza danych: KPI i DAX

Ponadto, na wstążce Power Pivot mamy kilka opcji powiązanych z analizą danych.

Krótko mówiąc, pola obliczeniowe to dodatkowe miary, które pozwalają na obliczenia, których nie jesteś w stanie przeprowadzić za pomocą samej tabeli przestawnej. Do ich tworzenia wykorzystujesz specjalny język tworzenia formuł DAX. O podstawach DAX i jego możliwościach przeczytasz w kolejnej części serii.

Gdy stworzysz miarę, możesz ją wykorzystać do stworzenia KPI (ang. Key Performance Indicator) czyli kluczowego wskaźnika, który pozwala zobrazować jak wartości z tabeli przestawnej mają się do innych wartości (cel i realizacja) Przykład takiego rozwiązania również pokażę Ci przy okazji kolejnej części serii.

Analiza danych, czyli odpowiedzi na pytania

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

Z poprzednich części wiemy, że:
- dane do analizy znajdują się w poniższych arkuszach
- aby móc używać tych danych jako logicznej całości konieczne było połączenie ich w relacje
Do odpowiedzi na pytania z początku artykułu – wykorzystamy najprostsze narzędzie, czyli spłaszczoną tabelę przestawną (układ tabelaryczny). Odpowiednią opcję znajdziesz w POWER PIVOT, na wstążce NARZĘDZIA GŁÓWNE pod przyciskiem TABELA PRZESTAWNA. Do dzieła!

Analiza danych: jak wyglądały koszty w podziale na grupy i podgrupy?

Ponieważ dane z tabel masz powiązane relacjami, w polach tabeli przestawnej zobaczysz taki widok.

Jak widać masz dostęp do wszystkich powiązanych tabel i pól. To jedna z największych zalet Power Pivot! Możesz analizować dane z różnych źródeł, łącząc je w logiczną całość.

W tym zadaniu potrzebujemy: wartości kosztów, grup i podgrup kosztowych. Przede wszystkim dodajmy grupę i podgrupę kosztów w tabeli przestawnej.

Jeżeli zapoznałeś się wcześniej z danymi, na pewno zobaczysz tu pewną nielogiczność. Dla grupy kosztowej Wynagrodzenia, mamy wszystkie podgrupy kosztowe (np. ZUS). Dzieje się tak dlatego ponieważ, nie dodałeś jeszcze żadnej wartości do pola obliczeniowego tabeli przestawnej.

Dodajmy Wartość z tabeli tbl_dane.

Jak widzisz, w tabeli przestawnej zostają tylko te grupy i podgrupy kosztów, dla których rzeczywiście występowała jakaś wartość.

Po uporządkowaniu tabeli, z łatwością odpowiemy na pierwsze pytanie w tym zadaniu.

Analiza danych: które piony i działy kosztowały najwięcej?

W kolejnych krokach sposób postępowania będzie bardzo podobny do opisanego powyżej. To co musisz robić, to tak „układać” tabelę przestawną, aby odpowiadać na postawione pytania. Jedyne co Cię ogranicza to poziom znajomości standardowej tabeli przestawnej.

Poniżej przykład, który pokazuje 3 najdroższe działy w każdym pionie.

Analiza danych: jaki jest rozkład kosztów pomiędzy miastami?

W tym przypadku zadanie jest wyjątkowo proste. Wystarczy Miasto z tabeli tbl_MPK oraz Wartość z tabeli tbl_dane.

Analiza danych: czy premie były wypłacane regularnie czy podlegały jakimś wahaniom?

Dodając czas, wartość oraz ograniczając podgrupę kosztów tylko do premii, widzimy że wypłacane premie podlegały dość dużym wahaniom.

Kompletne rozwiązanie znajdziesz także w tym pliku.

Podsumowanie

W trzeciej części serii o Power Pivot dowiedziałeś się jak przeprowadzić analizę danych powiązanych relacjami. Wykorzystujesz do tego tabelę lub wykres przestawny, co czyni obsługę całego narzędzia dość intuicyjnym, przynajmniej w końcowej fazie.

Oczywiście im lepiej znasz możliwości tabel przestawnych, tym bardziej wykorzystasz to narzędzie. Ewentualne braki zawsze można nadrobić na szkoleniu 😉

Wróć do poprzednich części:
1. POWER PIVOT cz.1 – instalacja i podstawy obsługi
2. POWER PIVOT cz.2 – przygotowywanie danych i łączenie ich w relacje
4 czerwca 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.
16 kwietnia 2019
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
Funkcja INDEKS i PODAJ.POZYCJĘ – udane połączenie
Funkcja INDEKS i PODAJ.POZYCJĘ – udane połączenie

Jako użytkownik Excela na pewno przynajmniej słyszałeś o WYSZUKAJ.PIONOWO. Ale czy znasz funkcje: INDEKS i PODAJ.POZYCJĘ? Jeśli nie – to złap za kubek z kawą, rozsiądź się wygodnie i poczytaj.

Każda z tych funkcji spełnia oczywiście niezależnie jakąś rolę. Ale żeby w pełni skorzystać z ich dobrodziejstw – najlepiej połączyć działanie obu.

Funkcja PODAJ.POZYCJĘ

Funkcja PODAJ.POZYCJĘ ma podawać względną pozycję szukanej wartości na liście. Co to znaczy?

Masz listę: Audi, BMW, Mercedes. Jeżeli szukasz BMW, to funkcja PODAJ.POZYCJĘ zwróci wartość 2 (bo BMW jest drugie na liście) Jasne, prawda?

Składnia funkcji wygląda tak:
```
=PODAJ.POZYCJĘ(szukana_wartość;przeszukiwana_tab;[typ_porównania])
```
czyli:
```
=PODAJ.POZYCJĘ(tu podajesz czego szukasz; tu podajesz gdzie szukasz; tu podajemy parametr: -1,0 albo 1, które mówi o sposobie porównania szukanej wartości z wartościami na liście, gdzie szukasz)
```
Po wyjaśnienie jak działają parametry w typie porównania odsyłam Cię do systemu pomocy Excela. W tym artykule skupimy się na użyciu parametru 0, czyli tzw. dokładnego dopasowania (szukana wartość ma być identyczna jak ta występująca na przeszukiwanej liście)

Funkcja INDEKS

Funkcja INDEKS zwraca wartość, która znajduje się w jakimś zakresie na przecięciu określonego wiersza i kolumny. Może mieć dwie postacie:
- Możesz szukać wartości w jednym, konkretnym zakresie (forma tablicowa)
- Możesz szukać wartości w jednym z wielu zakresów (forma odwołaniowa)
Wygląda to tak:
```
=INDEKS(tablica; nr_wiersza; nr_kolumny)
```
gdzie:
```
=INDEKS(tu zakres / lista, która będzie przeszukiwana; tu musi znaleźć się numer wiersza; w którym występuje szukana przez nas wartość; tu natomiast numer kolumny)
```
W przypadku, formy odwołaniowej:
```
=INDEKS(odwołanie; nr_wiersza; nr_kolumny; numer obszaru)
```
czyli
```
=INDEKS(tu podaj zakresy / listy, które będą przeszukiwane; tu numer wiersza, w którym występuje szukana przez nas wartość; wpisz numer kolumny; a tu numer obszaru, który przeszukamy)
```
Wszystko stanie się jaśniejsze, jeżeli spojrzysz na poniższy przykład. Stąd możesz pobrać plik początkowy.

Chciałbym wyszukać jaka była wartość sprzedaży w drugim kwartale, dla regionu wschód. Oczywiście w tej prostej tabeli widzę to bez żadnej dodatkowej funkcji. Chodzi jednak o przykład.
- W pierwszym argumencie zaznaczyłem cały zakres tabeli z danymi.
- Argument drugi to numer wiersza – stąd liczba 3, bo drugi kwartał (K2) znajduje się w 3 wierszu zaznaczonej tabeli
- Argument trzeci to numer kolumny – stąd liczba 4, bo region wschód znajduje się w 4 kolumnie zaznaczonej tabeli
Pewnie sobie teraz myślisz: „Faktycznie atrakcja…. Żeby znaleźć wartość, której szukam muszę sam najpierw zobaczyć w którym wierszu i kolumnie się znajduje…” No właśnie 😉 Dlatego teraz połączymy działanie obu funkcji.

Dobrana para

Zawsze powtarzam, że Excela uczą się lenie 😉 Bo w gruncie rzeczy chodzi o to, żeby automatyzować co się da i unikać ręcznej, mozolnej pracy. Dlatego funkcji INDEKS, przydałaby się towarzyszka, która wyeliminuje potrzebę ręcznego wpisywania numeru wiersza i kolumny.

Funkcja PODAJ.POZYCJĘ doskonale zrealizuje to zadanie. Aby znaleźć wiersz, w którym mamy szukany przez nas kwartał (K2) wpiszemy:
- Pierwszy argument to szukany przez nas kwartał K2 – wpisany w cudzysłowie, ponieważ to tekst (oczywiście może to być także, odwołanie do jakieś komórki, o czym dalej)
- W argumencie drugim zaznaczyłem kolumnę, w której mam szansę znaleźć szukaną wartość
- Trzeci argument wskazuje, że zależy mi na dokładnym dopasowaniu (szukam dokładnie wartości K2)
A teraz numer kolumny.
- Pierwszy argument to szukany przez nas region wschodni – ponownie wpisany w cudzysłowie, ponieważ to tekst
- W argumencie drugim zaznaczyłem wiersz, w której mamy regiony
- Trzeci argument to dokładne dopasowanie
Jak widzisz, funkcja PODAJ.POZYCJĘ skutecznie rozwiążę problem wyszukiwania odpowiedniego wiersza i kolumny. W rezultacie, wszystko możemy zrobić w ramach jednej, nieco rozbudowanej formuły.

Funkcja INDEKS w formie odwołaniowej

No dobrze, ale co jeżeli zechcę wyszukać wartość dla innego kwartału czy regionu? Nie wiem jak Ty, ale ja nie chcę za każdym razem „grzebać” w formule i wpisywać kolejne wartości. Zautomatyzujmy działanie, przez odwołanie do komórek, w których będziemy mogli zmieniać: kwartały i regiony. Stwórzmy listy rozwijane:

I zmodyfikujmy naszą formułę, tak aby zamiast ręcznie wpisywanych wartości odwoływała się do naszych komórek z listami. Dzięki temu zmieniając region czy kwartał, od razu otrzymam odpowiedni wynik.

Super, prawda? 😉

Jak już mówiłem, funkcja INDEKS występuje w dwóch formach. Do tej pory odwoływaliśmy się do jednego zakresu. A co jeżeli mamy dane z 3 lat i chcielibyśmy mieć podobny mechanizm wyszukiwania, w zależności od wybranego roku?

Rozbijmy naszą formułę:
```
=INDEKS((B10:F14;B18:F22;B26:F30);PODAJ.POZYCJĘ(C3;B10:B14;0);PODAJ.POZYCJĘ(C4;B10:F10;0);PODAJ.POZYCJĘ(C2;{2016;2017;2018};0))
```
na części pierwsze:
- Pierwszy argument – (B10:F14;B18:F22;B26:F30) – to adresy zakresów dla poszczególnych lat; umieszczamy je w nawiasie
- W drugim i trzecim argumencie – PODAJ.POZYCJĘ(C3;B10:B14;0);PODAJ.POZYCJĘ(C4;B10:F10;0) – mamy PODAJ.POZYCJĘ, która szuka odpowiedniego kwartału i regionu. Ponieważ dla każdej tabeli mamy taki sam układ – możemy się odwołać tylko do jednej z nich.
- Czwarty argument – PODAJ.POZYCJĘ(C2;{2016;2017;2018};0)) – to numer obszaru, który ma być przeszukiwany. Ponieważ nie chcemy wpisywać za każdym razem 1 dla roku 2016, 2 dla 2017 czy 3 dla 2018 – wprowadzamy kolejną PODAJ.POZYCJĘ, która podaje nam pozycję szukanej wartości na liście {2016;2017;2018} Nawiasy klamrowe są konieczne, aby wpisane wartości były traktowane jako jedna przeszukiwana tablica.
Finalny plik ściągniesz stąd.

Zalety używania funkcji INDEKS i PODAJ.POZYCJĘ

Podsumowując, na co dzień WYSZUKAJ PIONOWO użytkujemy dużo częściej. Im bardziej zaawansowany jednak jesteś w Excelu, tym większa pokusa żeby poznać funkcję INDEKS i PODAJ POZYCJĘ. Dwie największe przewagi to:
- Większa elastyczność niż w przypadku WYSZUKAJ PIONOWO
- Szybsze działanie, w szczególności gdzie mamy dużo formuł wyszukiwania
Natomiast podstawowa wada – ciężej nauczyć się wykorzystywać INDEKS i PODAJ POZYCJĘ. Mam nadzieję, że po tym artykule uczynisz duży krok w tym kierunku. Jeśli potrzebujesz dodatkowych wyjaśnień napisz w komentarzu. A może znasz inne, ciekawe zastosowania tych funkcji?
6 listopada 2018