Powerpivot

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. 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. 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. 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. 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. Kreator importu tekstu - krok 1

W drugim kroku kreatora należy wskazać tabulator jako separator tekstu (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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. Edycja nazwy kolumny

Proszę zmienić nazwę kolumny na Nazwa szkolenia oraz użyć klawisza Enter w celu akceptacji nazwy (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. 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. 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. 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. 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. Obszary tabeli przestawnej

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

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

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

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