Poznaj zaawansowane możliwości Excela!

• poznasz zastosowanie fragmentatorów
• przeprowadzisz zaawansowane analizy ogromnych zbiorów danych
• przeanalizujesz wyniki testów A/B
• poznasz możliwości narzędzi Power Query, Power View oraz Power Map
• zaprzęgniesz Excel do Business Intelligence

Rozdziały:

Podziękowania (13)

Przedmowa (15)

Wprowadzenie (17)

• Niedoszacowanie - główny problem z każdą książką (17)
• Głupie zasady są po to, aby je łamać! (18)
  • O tym, jak Bill zmylił mnie po raz drugi (18)

Wprowadzenie II (21)

Rozdział 1. Sztuczki z raportami i techniki wizualizacji danych (25)

• Umieszczenie informacji "Data ostatniej aktualizacji" (25)
  • Krok 1. Umieszczenie pola obliczeniowego OstatnieOdświeżenie (26)
  • Krok 2. Użycie pola obliczeniowego w funkcji modułowej (27)
• Normalizowanie na wykresach pól obliczeniowych według pierwszej/średniej/maksymalnej wartości (29)
  • Słowo z loży szyderców na temat wykresów (30)
  • Liczby to liczby, a obrazy to obrazy - każdy lubi po swojemu (30)
  • Formuły (31)
  • Alternatywne formuły wykorzystujące średnią (32)
  • Tworzenie wykresów dynamicznie normalizowanych według pierwszej wartości (33)
• Wydobywanie wyjątków w "problemie Sarah" (35)
  • Zacznijmy od pola "problem Sarah" (35)
  • Wracamy do formuł (36)
• Wydobywanie rankingów i wyjątków do poziomu sum częściowych (38)
  • W rzeczywistości nie jest to prawdziwe "wydobywanie" danych, ale praca w nadgodzinach (40)
• Własne etykietki ekranowe w raportach (40)
  • Sztuczka: hiperłącza prowadzące donikąd (41)
  • Lepszy sposób tworzenia etykietek (42)
  • Pytania do tej sztuczki (44)
• Nazwane zestawy danych i "asymetryczne" tabele przestawne: różne pola obliczeniowe dla różnych lat (44)
  • Powtarzalne! Przenośne! (47)
  • Nazwane zestawy nie działają w "tradycyjnych" tabelach przestawnych (48)

Rozdział 2. Fragmentatory: przepustka do interaktywności (49)

• "Inicjalizacja" fragmentatorów zawierających zbyt wiele pozycji do przewijania (53)
• Wingdings i inne czcionki symboliczne we fragmentatorach (55)
  • Czcionki symboliczne dostępne we fragmentatorach (55)
  • Rozszerzone znaki (57)
  • Jak umieścić symbole we fragmentatorze (59)
  • Utworzenie fragmentatorów (60)
  • Zmiana czcionki fragmentatora (60)
  • Osobne style dla różnych czcionek (63)
  • Ukrywanie nagłówka (63)
• Zastosowanie makr do zmiany czcionek fragmentatorów (64)
  • Obiekt ActiveSlicer (64)
  • A teraz... makro (64)
  • Jeżeli chcesz zmienić również czcionkę nagłówka... (65)
• Prosta sztuczka na "przestarzałe" fragmentatory (65)
  • Utworzenie "zastępczej" kolumny z etykietami fragmentatora (67)
  • A jeżeli kalendarz nie jest "przycięty"? (69)
• Przyjazne sortowanie w raportach z fragmentatorami (70)
  • Utworzenie sztucznych tabel dla fragmentatorów (71)
  • Utworzenie pól obliczeniowych (72)
  • Umieszczenie pola UkrytaKolejnośćSortowania w tabeli i sortowanie danych (73)
  • Ukrycie kolumny UkrytaKolejnośćSortowania w arkuszu (73)
• Sztuczka z sortowaniem według województw za pomocą fragmentatora (74)
  • Sposób 1.: tekstowe pola obliczeniowe (74)
  • Sposób 2.: dodanie nowej kolumny do tabeli Województwa (75)
• Dynamiczne raporty "Pierwszych N wartości" w Power Pivot (77)
  • Utworzenie dwóch odłączonych fragmentatorów (79)
  • Zakulisowe pola obliczeniowe: odczytywanie wyboru z fragmentatorów (79)
  • Użycie funkcji RANKX() (80)
  • Pole obliczeniowe UwzględnićKlienta (82)
  • Zagwarantowanie unikatowych nazw klientów (83)
  • Napisy w raporcie (83)
• Formatowanie warunkowe sterowane za pomocą fragmentatorów (85)
  • Inna technika z użyciem odłączonych tabel (86)
  • Użycie nowego fragmentatora i pola obliczeniowego do sterowania formatowaniem warunkowym (87)
  • Utworzenie nowej reguły ze skalą kolorów (88)
  • A teraz sztuczka (89)
  • Korekta błędu (91)
  • Określenie progu zielonego (92)
  • Określenie progów żółtego i czerwonego (93)
• Użycie w formule wybranej opcji fragmentatora (94)
  • Użycie funkcji ZESTAW.MODUŁÓW() (94)
  • Użycie funkcji USZEREGOWANY.ELEMENT.MODUŁU() (95)
  • Zastosowanie dodatkowych reguł formatowania (96)
  • Obsługa wyczyszczonego wyboru (96)

Rozdział 3. Najczęstsze wyzwania związane z kolumnami obliczeniowymi (99)

• Podręcznik do kolumn obliczeniowych dla specjalisty od Excela (100)
  • Osobliwości funkcji TEXT() i FORMAT() (100)
  • Gdzie jest funkcja WYSZUKAJ.PIONOWO()? (100)
  • Nie można wskazać kolumny za pomocą klawiszy strzałek (100)
• Sumy pośrednie w kolumnach obliczeniowych i funkcja EARLIER() (101)
• Odwołania do poprzedniego wiersza i temu podobne obliczenia (104)
• Odwołania do wierszy "z zakresu" bieżącego wiersza (105)
• Krzyżowe przeszukiwanie tabel: funkcja LOOKUPVALUE() i inne techniki (107)
  • Co się stanie, jeżeli funkcja LOOKUPVALUE() znajdzie więcej niż jedną wartość? (109)
  • A jeżeli funkcja LOOKUPVALUE() nie spełni Twoich oczekiwań? (110)
  • Przeszukiwanie danych na podstawie daty początkowej i końcowej (111)
  • Przeszukiwanie tylko na podstawie daty początkowej (112)
• Sumowanie tabel danych w tabelach przeszukiwanych (114)
• Funkcja CONTAINSX: wyszukiwanie wspólnych wartości w dwóch tabelach (116)
  • Tworzenie własnych funkcji X (117)
  • Formuła kojarząca wartości z dwóch tabel (117)
  • Relacje nie są potrzebne (118)
  • Miliony odmian (118)
• Ponowne spojrzenie na funkcję CONTAINSX: jaka jest wspólna wartość? (119)
  • Gdzie można użyć tej sztuczki? (121)
• Najczęstsze wyzwania związane z kalendarzem (121)
  • Kolumna DataPrzyszła (122)
  • Kolumna DzieńTygodnia (123)
  • Kolumna RokMiesiąc (123)
  • Kolumna Dzień roboczy/weekend (123)
  • Kolumna RokKwartałSortowanie (124)
• Wyzwania związane z niestandardowym kalendarzem (124)
  • Określenie numeru dnia w danym okresie (125)
  • Określenie numeru tygodnia w przedziale czasu (126)
  • Stale rosnący i "gładki" numer tygodnia (126)

Rozdział 4. Modelowanie danych i formuły przenośne (127)

• • Nowa, lepsza rzeczywistość (128)
  • Do czego służą "formuły przenośne"? (130)
• Prosty przykład przenośności (130)
• Integracja danych o różnej "granulacji" (133)
  • Czy nie można było dodać do tabeli Sprzedaż kolumny OkresID i na tym poprzestać? (136)
  • Rozszerzenie reguły na inne pola niż OkresID (138)
  • Upierz, wypłucz, powtórz (139)
  • Nadrzędne tabele filtrujące w przypadku pojedynczej tabeli (140)
• Zadziwiające i rewelacyjne rozwiązanie problemu relacji wiele-do-wielu (140)
  • Na czym polega straszny problem z relacjami wiele-do-wielu? (141)
  • Pozdrawiam Cię, Gerhardzie Bruecklu! (141)
  • Rozwiązanie Gerharda problemu relacji wiele-do-wielu (142)
• Obliczanie w Power Pivot sprzedaży w tym samym sklepie (143)
  • Utworzenie tabeli Sklepy (144)
  • "Surowe" formuły (144)
  • Obliczenie liczby transakcji (144)
  • Filtrowanie danych sprzedaży (145)
  • Implementacja ostatniego pola obliczeniowego (145)
• Ciąg dalszy tematu sprzedaży w tych samych sklepach: zastosowanie dat otwarcia i zamknięcia sklepów (146)
  • Utworzenie pól obliczeniowych (147)
  • Obliczenie daty zamknięcia sklepu (149)
  • Test całości (149)
  • Ale to pole ma długą nazwę! (150)
  • Jedna zaleta sposobu "czy była wtedy sprzedaż?" (151)
• Analiza kampanii reklamowych: okresowo dopasowywane pola obliczeniowe (151)
  • Tworzenie tabeli przestawnej z profilem sprzedaży (152)
  • Obliczenie tygodniowego współczynnika korygującego (152)
  • Import współczynników korygujących do modelu danych Power Pivot (153)
  • Utworzenie relacji z pozostałymi tabelami w modelu danych (154)
  • Tworzenie pola obliczeniowego z okresowym współczynnikiem korygującym (155)
  • Pola obliczeniowe uwzględniające okresowość sprzedaży (157)
  • Inne zastosowania pól obliczeniowych uwzględniających okresowość sprzedaży (158)
• Analiza kampanii A/B z użyciem fragmentatorów z datami początkową i końcową (159)
  • Utworzenie fragmentatorów i pól obliczeniowych z datami (160)
  • Utworzenie pola obliczeniowego z danymi sprzedaży przefiltrowanymi według pól z datami (162)
  • Utworzenie pól z dzienną sprzedażą (163)
  • Inny sposób: użycie tabeli Kampanie (164)
  • Inne ciekawe tematy (166)
• Określenie odsetka powracających klientów i gości strony WWW (166)
  • Dodanie do tabeli Klienci kolumny "Rok pierwszego zakupu" (167)
  • Utworzenie pola obliczeniowego "Liczba klientów" (168)
  • Utworzenie pola obliczeniowego "Aktywni klienci" (168)
  • Utworzenie pola obliczeniowego z odsetkiem klientów (169)
  • Uwaga: unikaj "nakładania się" lat (169)
  • Zliczanie nowych klientów (170)
  • Pole obliczeniowe oparte na polach pośrednich (171)
• Wyliczenie średniego "wieku" klienta (171)
• Wyszukiwanie "najlepszych rzeczy" (172)
• Średnia ważona: inne zastosowanie funkcji SUMX() (176)
  • Nadanie wag odpowiednich do populacji (177)
• Ruchoma średnia, suma itp. (178)
  • Suma ruchoma (180)
  • Średnia ruchoma: pierwsze podejście (180)
  • Średnia ruchoma: korekta (181)
  • Inne odmiany metody (181)
• Średnia ruchoma sterowana za pomocą fragmentatora (181)
  • Utworzenie selektywnego pola obliczeniowego (185)
  • Korekta błędu (186)
  • Tytuł wykresu (186)
• Zliczanie rzeczy, które nie miały miejsca (187)
  • Kiedy ten sposób nie działa? (188)
  • Uwaga 1.: uważaj na etykiety i wiersze! (189)
  • Uwaga 2.: ten przykład dotyczy tylko produktów (190)

Rozdział 5. Power Query (191)

• Łączenie kilku arkuszy lub skoroszytów w jedną tabelę Power Pivot (191)
  • Usunięcie wiersza nagłówka (192)
  • Zapisanie każdego arkusza w pliku CSV (193)
  • Otwarcie okna wiersza poleceń (193)
  • Przejście do folderu zawierającego pliki CSV (194)
  • Połączenie wszystkich plików w jeden plik (194)
  • Import złączonego pliku CSV do Power Pivot (195)
  • Bonus: makro do zapisywania wszystkich skoroszytów w folderze do pliku CSV (196)
• Złączenie plików CSV w Power Query (196)
  • Odczytanie pierwszego pliku CSV (197)
  • Dodanie niestandardowej kolumny z "oznaczeniem" pliku (197)
  • Odczytanie drugiego pliku CSV (201)
  • Odczytanie trzeciego pliku CSV (201)
  • Czas połączyć dane! (202)
  • Zachowanie tylko zapytania łączącego tabele (204)
  • Testowanie odświeżania danych (206)
  • Dlaczego jest to takie wyjątkowe? (206)
• Zastosowanie Power Query do anulowania przestawienia tabeli (207)
  • Wykorzystanie wiersza nagłówka (208)
  • Anulowanie przestawienia kolumn (209)
  • Dlaczego jest to takie wyjątkowe (211)
• Tworzenie tabeli przeszukiwanej z tabeli danych za pomocą Power Query (212)
  • Metody tworzenia tabel przeszukiwanych (212)
  • Wyniki operacji (214)
• Zaawansowane zastosowanie Power Query: tworzenie tabeli kalendarza (216)
  • Zaraz, nie widzę przycisku "Utwórz kalendarz"! (216)
  • W skrócie (217)
  • Jak wpisywać formuły (219)
  • Wyniki (219)
  • Usuwanie duplikatów (221)

Rozdział 6. Power View (223)

• Jak korzystać z Power View (223)
  • Wstawienie raportu Power View (224)
  • Domyślna pierwsza tabela Power View (224)
  • Tworzenie nowego elementu panelu (226)
  • Ukryte kontrolki elementu (226)
  • Tworzenie nowego elementu przez przeciągnięcie pola (227)
  • Lista Pola programu Power View (227)
  • Zmiana tabeli w wykres (227)
  • Opcje formatowania (228)
  • Hierarchia danych (229)
  • Nowe fragmentatory jawne i niejawne (230)
  • Dwa rodzaje filtrów (234)
  • Kafelki (236)
  • Zastosowanie zwielokrotnienia pionowego do tworzenia wielu wykresów (239)
  • Tworzenie map (240)
  • Tworzenie animowanych wykresów XY (242)

Rozdział 7. Power Map (243)

• • Kodowanie geograficzne (243)
  • Importowanie danych zakodowanych geograficznie (244)
  • Power Map i precyzyjne dane lokalne (246)
  • Poruszanie się po mapach Power Map (246)
  • Wyświetlanie mapy terenu i zdjęć satelitarnych (247)
  • Zmiana etykiety czasu na mapie (247)
  • Trzy metody wyświetlania czasu (248)
  • Rysowanie linii pomiędzy dwoma punktami (248)
  • Wskazówki dotyczące tworzenia przewodnika (250)
  • Porada dotycząca powiększania widoku (252)
  • Porada dotycząca pola tekstowego (252)
  • Przejścia i efekty (252)

Skorowidz (255)