Niedawno mój znajomy zastanawiał się nad przyciskami Drąż w dół i Rozwiń na karcie Narzędzia tabeli przestawnej na Wstążce. Dlaczego są one wiecznie wyszarzone? Zajmują dużo miejsca na Wstążce. Jak ktoś ma ich używać?
Po przeprowadzeniu pewnych badań jest sposób, aby z nich skorzystać, ale musisz użyć modelu danych i widoku diagramu dodatku Power Pivot, aby utworzyć hierarchię. Jeśli nie masz karty Power Pivot na Wstążce, będziesz musiał znaleźć współpracownika, który ma przycisk, aby utworzyć hierarchię. (Lub, jeśli chcesz po prostu wypróbować tę funkcję, pobierz utworzony przeze mnie plik Excel: Hierarchy.xlsx)
Pierwszy krok - przekonwertuj zestaw danych ze źródła przestawnego na tabelę, używając opcji Strona główna - Formatuj jako tabelę lub Ctrl + T. Upewnij się, że opcja Moja tabela ma nagłówki jest zaznaczona.
Użyj Wstaw - Tabela przestawna. W oknie dialogowym Tworzenie tabeli przestawnej wybierz pole Dodaj te dane do modelu danych.
Oto pola tabeli przestawnej przed utworzeniem hierarchii.
Kliknij ikonę Zarządzaj na karcie Power Pivot na Wstążce. (Wiele wystąpień programów Excel 2013 i 2016 nie ma tej karty. Nie pojawia się ona na komputerach Mac).
W oknie dodatku Power Pivot dla programu Excel kliknij ikonę Widok diagramu. Znajduje się w pobliżu prawej strony karty Strona główna.
Użyj uchwytu zmiany rozmiaru w prawym dolnym rogu Tabeli1, aby powiększyć Tabelę1 i zobaczyć wszystkie pola. Kliknij pierwszą pozycję w swojej hierarchii (w moim przykładzie kontynent). Kliknij z wciśniętym klawiszem Shift ostatni element w hierarchii (w moim przykładzie miasto). Możesz także kliknąć jeden element i kliknąć z wciśniętym klawiszem Ctrl na inne, jeśli pola hierarchii nie sąsiadują ze sobą. Po wybraniu pól kliknij prawym przyciskiem myszy dowolne z pól i wybierz opcję Utwórz hierarchię.
Hierarchia1 została utworzona i oczekuje na wpisanie nowej nazwy. Nazwę moją hierarchię Geografia. Jeśli klikniesz poza Power Pivot, Hierarchia1 nie będzie już w trybie zmiany nazwy. Kliknij prawym przyciskiem myszy Hierachy1 i wybierz Zmień nazwę.
Zamknij dodatek Power Pivot i wróć do programu Excel. W polach tabeli przestawnej jest teraz wyświetlana hierarchia geograficzna i więcej pól. Twoje pole Sprzedaż jest ukryte pod Więcej pól. W pewnym sensie rozumiem, dlaczego ukrywają kontynent, kraj, region, terytorium i miasto pod większą liczbą pól. Ale nie rozumiem, dlaczego ukrywają sprzedaż w sekcji Więcej pól.
Aby utworzyć tabelę przestawną, zaznacz pole wyboru obok hierarchii geograficznej. Otwórz więcej pól, klikając trójkąt obok niego. Wybierz sprzedaż.
Na powyższym obrazku jest wiele do zauważenia. Na początku tworzenia tabeli przestawnej aktywna komórka znajduje się na A3, a ikona Drąż w dół jest wyszarzona. Jeśli jednak przesuniesz wskaźnik komórki do Ameryki Północnej w formacie A4, zobaczysz, że funkcja Drąż w dół jest włączona.
Po ustawieniu wskaźnika komórki na Amerykę Północną kliknij Przejdź do szczegółów, a Kontynent zostanie zastąpiony przez Kraj.
Po umieszczeniu wskaźnika komórki na Kanadzie kliknij opcję Drąż w dół, a zobaczysz Kanadę wschodnią i Kanadę zachodnią. Zauważ, że w tym momencie oba przyciski Drąż w dół i Uogólnij są aktywne.
Kliknąłem Drill Up, aby powrócić do Country. Wybierz Stany Zjednoczone. Drill Down trzy razy i trafiam do miast w regionie Karoliny. W tym momencie przycisk Drąż w dół jest wyszarzony.
Zwróć uwagę, że z poziomu kontynentu możesz kliknąć opcję Rozwiń pole, aby wyświetlić kontynenty i kraje. Następnie z pierwszego kraju wybierz opcję Rozwiń pole, aby wyświetlić regiony. Z pierwszego regionu użyj opcji Rozwiń pole, aby wyświetlić terytoria. Na pierwszym terytorium kliknij opcję Rozwiń pole, aby wyświetlić miasto.
Wszystkie powyższe zrzuty ekranu pokazują tabelę przestawną w moim domyślnym widoku Pokaż w formie tabelarycznej. Jeśli tabele przestawne zostały utworzone w formacie kompaktowym, zobaczysz poniższy widok. (Aby dowiedzieć się, jak wszystkie przyszłe tabele przestawne mają zaczynać się w formie tabelarycznej, obejrzyj ten film).
Jaka jest zaleta Hierarchii? Próbowałem utworzyć zwykłą tabelę przestawną bez hierarchii. Nadal mam możliwość rozwijania i zwijania pól. Ale jeśli chcę wyświetlić tylko regiony w Kanadzie, musiałbym dodać fragmentator lub filtr raportu.
Obejrzyj wideo
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2196: Drill Up and Drill Down in Pivot Tables.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Tabele przestawne są tajemnicą. Jeśli wstawię tutaj tabelę przestawną, zobaczysz, że mamy pola Drill Up i Drill Down, ale one nigdy się nie świecą. O co w tym chodzi? Dlaczego je mamy? Jak sprawiamy, że działają? W porządku, to świetne, świetne pytanie i niestety czuję się z tym źle. Próbuję całe życie spędzać w programie Excel, nigdy nie korzystając z karty Power Pivot. Nie chcę, abyś musiał płacić dodatkowe 2 dolary miesięcznie za wersję Pro Plus Office 365, ale to jest jedno - to jest jedno - na które musimy wydawać dodatkowe 2 dolary miesięcznie lub znaleźć kogoś, kto ma dodatkowe 2 dolary miesięcznie, aby to skonfigurować.
Przyjmę ten format danych jako tabelę. Nie ma znaczenia, jaki format wybiorę, format nie jest ważny; tylko zdobycie dla nas stołu jest ważną częścią. Power Pivot, zamierzamy dodać tę tabelę do naszego modelu danych, a następnie kliknąć opcję Zarządzaj. W porządku, oto nasza tabela w modelu danych. Musimy przejść do widoku diagramu, teraz poszerzymy go trochę, abyśmy mogli zobaczyć wszystkie pola. Mam zamiar wybrać Kontynent; Idę do Shift + kliknięcie na Miasto. Teraz to składa się na moją hierarchię Drill Down, Drill Up. A potem klikniemy prawym przyciskiem myszy i powiemy Create Heirarchy. I podają nam nazwę - mam zamiar wpisać „Geografia” dla mojej Hierarchii, w ten sposób. Świetnie, teraz, z tą jedną zmianą, wstawiamy tabelę przestawną - a będzie to tabela przestawna modelu danych - i widzisz, że możemy dodać Geography jako własną hierarchię.
Teraz jedyną rzeczą, której nie lubię w tym, jest to, że wszystko inne przenosi się do More Fields. W porządku? Wybieramy więc Geografię i leci po lewej stronie. I chociaż to świetnie, muszę również wybrać Revenue, a oni wzięli pola, które nie były częścią Hierarchii i przenieśli je do More Fields. Więc to tak, rozumiem, próbują ukryć pola, których nie powinienem wybierać, ale w trakcie tego ukrywali również więcej pól - przychody lub sprzedaż tutaj. W porządku. Tak więc, trochę frustrujące, musimy udać się do większej liczby pól, aby zdobyć pola, które nie są częścią geografii, ale tak to już jest.
W porządku. Więc teraz, skoro już to mamy, przyjrzyjmy się, co tutaj działa. Siedzę na kontynencie, przechodzę do zakładki Analiza i nic się nie świeci, nie działa. Strzelać! Nie, to zadziałało, wystarczy przyjechać do Ameryki Północnej, a wtedy mogę Drill Down i zastąpi Kontynent Krajem. A potem z Kanady mogę Drill Down i dostać się do wschodniej i zachodniej Kanady. Ze wschodniej Kanady docieram do Ontario i Quebecu. Ontario, mam te miasta, mogę drążyć drążenie, drążenie i wybieranie Stanów Zjednoczonych; Drąż w dół, Drąż w dół, Drąż w dół. W porządku, więc tak to działa.
Spróbuj, musisz mieć kartę Power Pivot lub znaleźć kogoś z kartą Power Pivot. Jeśli chcesz to wypróbować, spójrz w opis YouTube, tam będzie link do strony internetowej i jest tam miejsce na stronie internetowej, gdzie możesz pobrać ten plik i powinieneś być w stanie użyć Hierarchii, nawet jeśli nie masz karty Power Pivot. Jeśli korzystasz z programu Excel 2016 lub Office 365, powinno działać.
Teraz, wiesz, widzisz, myślę, że rzecz, której nie jestem pewien, czy jestem fanem, to fakt, że pozbywają się innych informacji, w przeciwieństwie do używania ikony Rozwiń, która następnie rozwijałaby się do następna grupa, następna grupa i następna grupa. Zawsze mieliśmy ikonę Rozwiń, ale nawet wtedy działa trochę inaczej. Tutaj, gdybym chciał, mógłbym siedzieć w Ameryce Północnej i rozszerzać o jeden poziom na raz, bez konieczności wybierania każdego dodatkowego z modelu danych. Wygląda na to, że musimy przesuwać wskaźnik komórki po jednym kawałku.
W porządku, teraz ta wskazówka została naprawdę, w pewnym sensie, odkryta. MVP programu Excel przeprowadzili rozmowę z zespołem programu Excel na temat tych przycisków, więc nie zostały one omówione w tej książce. Ale wiele innych świetnych wskazówek omówionych w LIVe, 54 najlepszych wskazówkach wszechczasów.
Podsumowanie na dziś: dlaczego Drill Up i Drill Down są stale wyszarzone? Cóż, musisz stworzyć hierarchię. Aby utworzyć hierarchię, musisz przejść do dodatku Power Pivot; do widoku diagramu; wybierz pola dla hierarchii; a następnie kliknij prawym przyciskiem myszy; i Utwórz hierarchię.
Chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem od.
