GWARANTOWANE TERMINY
Jesteś tutaj: strona głównastrefa wiedzyPowerpivot
MENU

Powerpivot


Kategoria: Kurs Excel, Samouczek Excel , Analiza danych
30Dec

Dodatek Powerpivot umożliwia rozszerzenie funkcjonalności programu Excel. Dzięki dodatkowi można analizować tabele, które mają więcej niż 1048576 wierszy, przy czym należy pamiętać o tym, że wstawienie tabeli przestawnej do Excela z dodatku Powerpivot, które przekroczy liczbę wierszy w tym programie spowoduje wyświetlenie odpowiedniego komunikatu. W poniższym artykule przedstawiono sposób tworzenia relacji pomiędzy tabelami, importowanie danych z innych źródeł a także dodawanie nowych pól w tabelach. Zachęcamy do ściągnięcia plików ćwiczeniowych, które zostały wykorzystane w artykule.

 

Włączanie dodatku Powerpivot

Kroki, które należy wykonać aby włączyć dodatek Powerpivot są następujące: z menu Plik proszę wybrać Opcje, następnie z okna Opcje programu Excel należy kliknąć na Dodatki. Z listy proszę wybrać Dodatki COM i wcisnąć przycisk Przejdź. Kolejnym krokiem jest zaznaczenie pola wyboru Microsoft Office Powerpivot For Excel 2013 (rys. 1) i zaakceptować wprowadzone ustawienia przyciskiem OK.

rys 1

Rys. 1. Okno dodatków COM

 

Dodawanie tabeli w aplikacji Excel

Proszę otworzyć plik faktury.xlsx. Proszę kliknąć na komórkę w obrębie zakresu danych np. na komórkę A1. Aby dodać tabelę należy z karty WSTAWIANIE z grupy Tabele wybrać opcję Tabela. Zostanie wyświetlone okno Tworzenie tabel, w którym należy sprawdzić, czy zaznaczenie które proponuje Excel jest prawidłowe oraz czy tabela ma nagłówki (rys. 2) a następnie proszę kliknąć przycisk OK.

rys 2

Rys. 2. Okno tworzenie tabeli

 

Konsekwencją powyższych kroków jest utworzenie tabeli co jest sygnalizowane dodatkową kartą PROJEKTOWANIE dla narzędzi tabel. Proszę zmienić nazwę tabeli na faktury. W tym celu z karty PROJEKTOWANIE proszę w polu tekstowym (znajdującym się w grupie Właściwości) zmienić nazwę (rys. 3) i zaakceptować przyciskiem Enter. Warto pamiętać, że narzędzie tabel jest aktywne dopiero wtedy, gdy aktywna jest tabela.

rys 3

Rys. 3. Zmiana nazwy tabeli

 

Import danych z pliku tekstowego do Excela

Do określenia imion oraz nazwisk poszczególnych klientów potrzebny jest plik o nazwie klienci.txt. Aby zaimportować dane z tego pliku należy dodać nowy arkusz (Shift + F11). Proszę przejść do nowego arkusza i z karty DANE z grupy Pobieranie danych zewnętrznych wybrać opcję Z tekstu. Powinno pojawić się okno dialogowe, z którego proszę wskazać lokalizację tego pliku tekstowego (rys. 4) a następnie proszę kliknąć na przycisk Otwórz.

rys 4

Rys. 4. Wybranie lokalizacji pliku tekstowego

 

Wykonanie powyższych kroków spowoduje wyświetlenie okna kreatora. W pierwszym kroku należy upewnić się, że polskie znaki diakrytyczne są poprawnie wyświetlanie. Jeśli nie, to należy z listy o nazwie Pochodzenie pliku wybrać odpowiednią wartość (rys. 5).

rys 5

Rys. 5. Kreator importu tekstu - krok 1

 

W drugim kroku kreatora należy wskazać tabulator jako separator tekstu (rys. 6).

rys 6

Rys. 6. Kreator importu tekstu - krok 2

 

W trzecim kroku kreatora można określić typ danych, jednak w tym przypadku proszę zastosować domyślne ustawienia klikając na przycisk Zakończ (rys. 7).

rys 7

Rys. 7. Kreator importu tekstu - krok 3

 

W ostatnim oknie dialogowym proszę zaznaczyć opcję Dodaj te dane do modelu danych i zaakceptować przyciskiem OK (rys. 8).

rys 8

Rys. 8. Ostatni krok importu danych

 

Dzięki ostatniej operacji Excel utworzy nam obiekt tabeli o nazwie Tabela_klienci oraz umieści ją w dodatku Powerpivot.

 

Import danych z Accessa do Excela

Proszę utworzyć nowy arkusz (Shift + F11). Z karty DANE z grupy Pobieranie danych zewnętrznych proszę wybrać z programu Access. Zostanie wyświetlone okno dialogowe Wybieranie źródła danych w którym należy wskazać bazę danych (rys. 9).

rys 9

Rys. 9. Wskazanie bazy danych

 

Kolejny krok to wskazanie danych gdzie mają być umieszczone. W oknie Importowanie danych proszę zaznaczyć Dodaj te dane do modelu danych i zaakceptować przyciskiem OK (rys. 10).

rys 10

Rys. 10. Wskazanie miejsca importowanych danych

 

Podobnie jak w przypadku pliku tekstowego dane z bazy danych zostały umieszczone w dodatku Powerpivot.

 

Dodanie tabeli do modelu danych

Na przeszkodzie w utworzeniu tabeli przestawnej z powyższych źródeł pozostaje tabela o nazwie faktury, którą należy dodać do modelu danych. Aby to zrobić należy przejść do arkusza faktury i kliknąć na tabelę faktury, następnie z karty POWERPIVOT proszę wybrać opcję Dodaj do modelu danych (rys. 11).

rys 11

Rys. 11. Opcja Dodaj do modelu danych

 

Po wykonaniu powyższych operacji dodatek Powerpivot powinien zostać otworzony wraz z tabelami, które zostały dodane do modelu danych (rys. 12).

rys 12

Rys. 12. Wygląd dodatku Powerpivot

 

Tworzenie relacji pomiędzy tabelami

Tabela faktury powinna być połączona z tabelą klienci za pomocą pola Nr klienta. Powiązanie tych tabel umożliwi zidentyfikowanie klientów po imieniu i nazwisku. Aby powiązać te tabele relacjami, należy z dodatku Powerpivot wybrać opcję Widok diagramu (rys. 13).

rys 13

Rys. 13. Lokalizacja widoku diagramu w dodatku Powerpivot

 

Kolejnym krokiem jest wciśnięcie LPM na polu Nr klienta w tabeli faktury a następnie jego przeciągnięcie na pole Nr klienta w tabeli klienci i puszczenie LPM. Dzięki tej operacji tabele będą ze sobą powiązane (rys. 14).

rys 14

Rys. 14. Powiązane tabele faktury oraz klienci

 

Podobnie postępujemy w przypadku tabeli faktury oraz szkolenia z tą różnicą, że te tabele będą ze sobą połączone przy pomocy pola Nr szkolenia (rys. 15).

rys 15

Rys. 15. Relacje pomiędzy tabelami

 

Połączenie tabeli faktury oraz szkolenia umożliwi pobranie ceny za szkolenie, dzięki czemu można otrzymać informacje jakie kwoty zostały wydane przez poszczególnych klientów w firmie.

 

Dodawanie pola obliczeniowego

Dodanie nazwy szkolenia oraz ceny do tabeli faktury

Do tabeli faktury zostanie dodane pole dotyczące nazwy oraz ceny szkolenia. W tym celu proszę  przywrócić widok na Widok danych (rys. 16).

rys 16

Rys. 16. Lokalizacja widoku danych w dodatku Powerpivot

 

Jeśli tabela faktury nie jest aktywowana, to proszę na nią kliknąć a następnie proszę dwukrotnie kliknąć na pole Dodaj kolumnę (rys. 17).

rys 17

Rys. 17. Edycja nazwy kolumny

 

Proszę zmienić nazwę kolumny na Nazwa szkolenia oraz użyć klawisza Enter w celu akceptacji nazwy (rys. 18).

rys 18

Rys. 18. Nazwa szkolenia

 

Kolejnym etapem jest wprowadzenie formuły. W tym celu proszę kliknąć na pierwszą komórkę a następnie w pasku formuły wpisać =RELATED(szkolenia[Nazwa szkolenia]) (rys. 19) i zaakceptować przyciskiem Enter.

rys 19

Rys. 19. Formuła wstawiająca nazwę szkolenia

 

W przypadku, gdy formuła jest prawidłowo wpisana, to powinny pojawić się nazwy szkoleń (rys. 20).

rys 20

Rys. 20. Nazwy szkoleń w dodatku Powerpivot

 

Kolejnym etapem jest dodanie wartości brutto do tabeli faktury w dodatku Powerpivot. W tym celu proszę dodać kolumnę o nazwie Wartość brutto. Następnym krokiem jest wprowadzenie formuły obliczającej żądaną wartość. W pasku formuły należy wprowadzić następujący ciąg znaków: (rys. 21)

=RELATED(szkolenia[Cena])*[Ilość]*([VAT]+1)

rys 21

Rys. 21. Pole wartość brutto

 

Wykorzystanie funkcji tekstowej przy tworzeniu pola obliczeniowego

Ostatnim etapem związanym z dodaniem nowego pola jest wykorzystanie tabeli klienci do utworzenia w tabeli faktury pola o nazwie Imię i nazwisko. Pole będzie składać się z dwóch kolumn tabeli klienci: pole Imie oraz pole Nazwisko. Do połączenia tych dwóch pól zostanie wykorzystana funkcja CONCATENATE.

Proszę utworzyć pole w tabeli faktury o nazwie Imię i nazwisko a następnie (w pasku formuły) wprowadzić następującą formułę (rys. 22):

=CONCATENATE(RELATED(klienci[Imie]);" "&RELATED(klienci[Nazwisko]))

rys 22

Rys. 22. Pole Imie i nazwisko w tabeli faktury

 

Utworzenie tabeli przestawnej

Powerpivot pozwala na umieszczenie danych w tabeli przestawnej. W tym przypadku celem będzie utworzenie tabeli przestawnej w taki sposób, aby dowiedzieć się jakie kwoty zostały wydane na poszczególne szkolenia przez klientów. W tym celu proszę wybrać z dodatku Powerpivot, z karty Narzędzia główne opcję Tabela przestawna. Wykonanie powyższych kroków spowoduje wyświetlenie okna dialogowego, w którym należy wskazać lokalizację tabeli przestawnej. W tym przypadku proszę wybrać Nowy arkusz i zaakceptować wybór przyciskiem OK.

Po wstawieniu tabeli przestawnej proszę umieścić pola Imie i nazwisko oraz Nazwa szkolenia (z tabeli faktury) do obszaru wierszy, natomiast do obszaru wartości wstawić pole Wartość brutto (również z tabeli faktury). Wygląd poszczególnych obszarów zaprezentowano na rys. 23.

rys 23

Rys. 23. Obszary tabeli przestawnej

 

Część tabeli przestawnej pokazano na rys. 24.

rys 24

Rys. 24. Końcowy efekt wykonania ćwiczeń

 

Autorem tekstu i obrazków jest trener Cognity - Grzegorz Plak.


Licencja Creative Commons
Powerpivot by Cognity is licensed under a Creative Commons Uznanie autorstwa-Użycie niekomercyjne 4.0 Międzynarodowe License.

Wybrani Klienci:

Ten serwis używa plików cookies. Brak zmiany ustawień przeglądarki oznacza zgodę na ich używanie.
Więcej informacji o plikach cookies znajdziesz tutaj
Kliknij ZGADZAM SIĘ, aby ta informacja nie wyświetlała się ponownie
Cognity.pl