Nie masz dodatku Power Pivot? Nieważne. Większość dodatku Power Pivot jest wbudowana w program Excel 2013, a jeszcze więcej w programie Excel 2016. Dziś nasza wskazówka od Asha dotyczy łączenia tabel w tabeli przestawnej.
W każdą środę przez siedem tygodni przedstawiam jedną z ulubionych wskazówek Ash Sharma. Ash jest menedżerem produktu w zespole Excela. Jego zespół oferuje tabele przestawne i wiele innych dobrych rzeczy. Obecnie ulubioną funkcją Asha jest łączenie wielu zestawów danych za pomocą relacji i modelu danych.
Załóżmy, że Twój dział IT dostarcza zestaw danych pokazany w kolumnach A: D. Istnieją pola dla klientów i rynków. Musisz połączyć niektóre rynki w regiony. Każdy klient należy do sektora. Region i sektor nie znajdują się w oryginalnych danych, ale masz tabele odnośników, które zawierają te informacje.
Zwykle spłaszczyłbyś dane za pomocą funkcji WYSZUKAJ.PIONOWO, aby pobrać dane z pomarańczowych i żółtych tabel do niebieskiej tabeli. Ale ponieważ pole klucza nie znajduje się po lewej stronie każdej tabeli, będziesz musiał albo przełączyć się na INDEX i MATCH, albo ponownie uporządkować tabele przeglądowe.
Począwszy od programu Excel 2013, możesz pozostawić tabele odnośników tam, gdzie się znajdują i połączyć je w raporcie tabeli przestawnej.
Aby ta technika działała, wszystkie trzy tabele muszą być sformatowane jako tabela. Wybierz jedną komórkę w każdym zestawie danych i wybierz opcję Strona główna, Formatuj jako tabelę lub naciśnij Ctrl + T. Początkowo trzy tabele będą nazywane Tabela1, Tabela2 i Tabela3. Używam karty Projektowanie narzędzi tabel na Wstążce i zmieniam nazwę każdej tabeli. Zmieniam też kolor każdego stołu. W tym przykładzie niebieska tabela nosi nazwę Dane. Pomarańczowa tabela to RegionTable. Żółta tabela to SectorTable.
Uwaga
Niektórzy powiedzą ci, że powinieneś używać geekowskich nazw, takich jak Fact, TblSector i TblRegion. Jeśli ktoś cię tak męczy, po prostu ukradnij jego ochraniacz kieszeni i daj mu znać, że wolisz nazwy brzmiące po angielsku.
Aby zmienić nazwę tabeli, wpisz nową nazwę w polu po lewej stronie karty Projektowanie narzędzi tabel. Nazwy tabel nie powinny zawierać spacji.
Po zdefiniowaniu trzech tabel przejdź do zakładki Dane i kliknij Relacje.
W oknie dialogowym Zarządzaj relacjami kliknij opcję Nowa. W oknie dialogowym Tworzenie relacji określ, że pole Customer tabeli danych jest powiązane z polem Customer tabeli SectorTable. Kliknij OK.
Zdefiniuj inną nową relację między polem Rynek w polach Dane i Tabela regionów. Po zdefiniowaniu obu relacji zobaczysz je w oknie dialogowym Zarządzaj relacjami.
Gratulacje: właśnie utworzyłeś model danych w swoim skoroszycie. Czas zbudować tabelę przestawną.
Wybierz pustą komórkę, w której ma się pojawić tabela przestawna. Domyślnie w oknie dialogowym Tworzenie tabeli przestawnej zostanie wybrana opcja Użyj modelu danych tego skoroszytu. Domyślną lokalizacją tabeli przestawnej będzie wybrana komórka. Kliknij OK.
Lista Pola tabeli przestawnej zawiera listę wszystkich trzech tabel. Użyj trójkąta po lewej stronie tabeli, aby rozwinąć nazwę tabeli, aby wyświetlić pola.
Rozwiń tabelę danych. Wybierz pole Przychód. Automatycznie przejdzie do obszaru Wartości. Rozwiń SectorTable. Wybierz pole Sektor. Zostanie przeniesiony do obszaru Wiersze. Rozwiń RegionTable. Przeciągnij pole Region do obszaru Kolumny. Będziesz mieć teraz tabelę przestawną podsumowującą dane z trzech tabel.
Uwaga
W każdej książce, którą napisałem dzisiaj, używam innej techniki do tworzenia tego raportu. Po zdefiniowaniu trzech tabel wybieram komórkę A1 i Wstaw, tabelę przestawną. Zaznaczam pole Dodaj te dane do modelu danych. Na liście Pola tabeli przestawnej wybierz pozycję Wszystko u góry listy. Wybierz pola raportu, a następnie zdefiniuj relacje po fakcie. Technika opisana powyżej wydaje się płynniejsza i w rzeczywistości wymaga odrobiny planowania z wyprzedzeniem. Osobom, które używają Option Explicit w swoim kodzie VBA, z pewnością spodoba się ta metoda.
Relacje w modelu danych sprawiają, że program Excel przypomina bardziej Access lub SQL Server, ale z wszystkimi zaletami programu Excel.
Uwielbiam prosić zespół programu Excel o ich ulubione funkcje. W każdą środę podzielę się jedną z ich odpowiedzi. Podziękowania dla Ash Sharma za dostarczenie tego pomysłu.
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Nie szukaj, jeśli jesteś w związku”
John Michaloudis
