Unikaj funkcji WYSZUKAJ.PIONOWO przy użyciu modelu danych. Tak więc masz dwie tabele, które muszą zostać połączone za pomocą funkcji WYSZUKAJ.PIONOWO, zanim będzie można utworzyć tabelę przestawną. Jeśli masz program Excel 2013 lub nowszy na komputerze z systemem Windows, możesz to teraz zrobić prosto i łatwo.
Załóżmy, że masz zestaw danych zawierający informacje o produkcie, klientach i sprzedaży.
Dział IT zapomniał umieścić tam sektor. Oto tabela przeglądowa, która mapuje klientów do sektora. Czas na VLOOKUP, prawda?
Nie ma potrzeby wykonywania funkcji WYSZUKAJ.PIONOWO, aby dołączyć do tych zestawów danych, jeśli masz program Excel 2013 lub Excel 2016. Obie te wersje programu Excel mają wbudowany silnik Power Pivot w rdzeń programu Excel. (Możesz to również zrobić za pomocą dodatku Power Pivot dla programu Excel 2010, ale jest kilka dodatkowych kroków).
Zarówno w oryginalnym zestawie danych, jak iw tabeli przeglądowej użyj opcji Strona główna, Formatuj jako tabelę. Na karcie Narzędzia tabel zmień nazwę tabeli z Tabela1 na istotną. Użyłem danych i sektorów.
Wybierz jedną komórkę w tabeli danych. Wybierz Wstaw, Tabela przestawna. Począwszy od programu Excel 2013, istnieje dodatkowe pole Dodaj te dane do modelu danych, które należy wybrać przed kliknięciem przycisku OK.
Pojawi się lista Pola tabeli przestawnej z polami z tabeli danych. Wybierz przychód. Ponieważ używasz modelu danych, na górze listy pojawia się nowa linia, oferująca Aktywne lub Wszystkie. Kliknij All.
Co zaskakujące, lista pól tabeli przestawnej zawiera wszystkie inne tabele w skoroszycie. To jest przełomowe. Nie wykonałeś jeszcze VLOOKUP. Rozwiń tabelę Sektory i wybierz Sektor. Dwie rzeczy ostrzegają cię, że jest problem.
Po pierwsze, tabela przestawna pojawia się z tym samym numerem we wszystkich komórkach.
Być może subtelniejszym ostrzeżeniem jest żółte pole u góry listy pól tabeli przestawnej, wskazujące, że należy utworzyć relację. Wybierz opcję Utwórz. (Jeśli korzystasz z programu Excel 2010 lub 2016, skorzystaj z funkcji automatycznego wykrywania).
W oknie dialogowym Utwórz relację dostępne są cztery menu rozwijane. Wybierz Dane w tabeli, Klient w kolumnie (obce) i Sektory w powiązanej tabeli. Dodatek Power Pivot automatycznie wypełni pasującą kolumnę w kolumnie Powiązana (podstawowa). Kliknij OK.
Wynikowa tabela przestawna jest połączeniem oryginalnych danych i tabeli przeglądowej. Nie jest wymagane wyszukiwanie VLOOKUP.
Obejrzyj wideo
- Począwszy od programu Excel 2013, okno dialogowe Tabela przestawna oferuje model danych
- To jest słowo kodowe dla silnika Power Pivot
- Aby użyć modelu danych, utwórz tabelę Ctrl + T z każdej tabeli w skoroszycie
- Utwórz tabelę przestawną z pierwszej tabeli
- Na liście pól tabeli przestawnej zmień z Aktywne na Wszystkie
- Wybierz pole z tabeli przeglądowej
- Utwórz relację lub Wykryj automatycznie
- Funkcja automatycznego wykrywania nie istniała w 2013 roku
- Podziękowania dla Colina Michaela i Alejandro Quiceno za ogólną sugestię dotyczącą Power Pivot.
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2014 - Wyeliminuj WYSZUKAJ.PIONOWO!
Podcasting całej tej książki, kliknij „i” w prawym górnym rogu, aby wyświetlić listę odtwarzania!
Hej, witaj ponownie w netcastie, jestem Bill Jelen, to się właściwie nazywa Eliminacja WYSZUKAJ.PIONOWO za pomocą modelu danych! Teraz przepraszam, to jest Excel 2013 i nowsze, jeśli wróciłeś do Excela 2010, musisz pobrać dodatek Power Pivot, który oczywiście jest darmowy w 2010 roku. Mamy więc tutaj nasze główny zestaw danych, jest tutaj pole Klient, a następnie mam małą tabelkę, która mapuje klientów w sektorach, muszę stworzyć całkowity przychód według sektora, prawda? To jest WYSZUKAJ.PIONOWO, po prostu wykonaj WYSZUKAJ.PIONOWO, ale hej, dzięki Excel 2013 nie musimy wykonywać WYSZUKAJ.PIONOWO! Zrobiłem oba z nich w tabeli, a w Narzędziach tabel, Projekt, zmieniam nazwy tabel, nazywam ten Sektory, a nazywam ten Dane, aby przekształcić go w tabelę, po prostu wybierz jedną komórkę, naciśnij Ctrl + T. Jeśli więc mamy jakieś nagłówki i jakieś liczby, po naciśnięciu Ctrl + T,pytają „Gdzie są dane twojej tabeli?”, Moja tabela ma nagłówki, a potem nazywają ją Tabela3, ty nazywasz to inaczej. W porządku, tak utworzyłem te dwie tabele, pozbędę się tego stołu, w porządku.
Aby ta sztuczka zadziałała, wszystkie dane muszą znajdować się w tabelach. Przechodzimy do zakładki Wstawianie, wybieramy tabelę przestawną i właśnie tutaj, na dole, Dodaj te dane do modelu danych. Brzmi to bardzo niewinnie, prawda? Nie ma to jak migający punkt, który mówi: „Hej, to pozwoli Ci robić niesamowite rzeczy!” A to, o czym tutaj mówią, to czego starają się nie powiedzieć, to to, że… A tak przy okazji, każda kopia programu Excel 2013 ma za sobą silnik Power Pivot. Wiesz, jeśli korzystasz z Office 365, płacisz 10 USD miesięcznie, a oni chcą, abyś płacił 12 lub 15 USD miesięcznie, aby uzyskać Power Pivot, czyli dodatkowe dwa lub pięć dolarów. Cóż, hej, ciii, nie mów, tak naprawdę większość dodatku Power Pivot masz już w programie Excel 2013. W porządku, więc klikam OK, ładowanie modelu danych zajmuje trochę więcej czasu, w porządku, ale wszystko w porządku i zaraz po tym tutaj,w polach tabeli przestawnej mam listę wszystkich pól. Z pewnością chcę pokazać przychody, ale to, co jest innego, dotyczy Aktywnych i Wszystkich. Po wybraniu opcji Wszystkie otrzymuję wszystkie tabele ze skoroszytu. W porządku, więc przechodzę do Sektorów i powiedziałem, że chcę umieścić sektor w obszarze Wiersze. Teraz, początkowo raport będzie błędny, zobacz 6,7 miliona aż do końca, a to żółte ostrzeżenie tutaj powie, że musisz stworzyć związek.a to żółte ostrzeżenie powie, że musisz stworzyć związek.a to żółte ostrzeżenie powie, że musisz stworzyć związek.
W porządku, teraz, w 2010 z Power Pivot, po prostu oferowałby AutoDetect, w 2013 wycofali AutoDetect, aw 2016 przywrócili AutoDetect, dobrze? Powinienem pokazać Ci, jak wygląda UTWÓRZ, ale kiedy kliknę ten przycisk UTWÓRZ, o tak, to wszystko, w porządku, dobrze. Więc z naszej pierwszej tabeli Dane mam pole o nazwie Klient, z powiązanej tabeli Sektory, mam pole o nazwie Klient, a następnie klikasz OK, w porządku. Ale pozwólcie, że pokażę wam, jak fajna jest funkcja AutoDetect, jeśli zdarzyło ci się być w 2016 roku, to zorientowali się, jakie to niesamowite, prawda? Nie musisz się martwić o WYSZUKAJ.PIONOWO, a przecinek spada na końcu, jeśli WYSZUKAJ.PIONOWO boli cię w głowę, pokochasz Model danych. Wziąłem te dwie tabele, połączyłem je ze sobą, wiesz, tak jak zrobiłby to Access, jak sądzę, i stworzył tabelę przestawną, absolutnie niesamowity.Więc sprawdź model danych następnym razem, gdy będziesz musiał wykonać WYSZUKAJ.PIONOWO między dwiema tabelami. Cóż, ta i wszystkie pozostałe 40 wskazówek znajduje się w książce. Kliknij „i” w prawym górnym rogu. Możesz kupić książkę, mieć pełne odniesienie do całej serii filmów, przez cały sierpień, przez cały wrzesień, do cholery, możemy nawet przenieść na październik, aby wszystko załatwić.
W porządku, podsumuj dzisiaj: począwszy od programu Excel 2013, okno dialogowe tabeli przestawnej oferuje coś, co nazywa się modelem danych, jest to słowo kodowe dla silnika Power Pivot. Zanim utworzysz tabele przestawne, wykonaj Ctrl + T, aby utworzyć tabelę z każdego skoroszytu, poświęciłem więcej czasu na nazwanie każdej z nich. Zbuduj tabelę przestawną z pierwszej tabeli, a następnie na liście pól przejdź na górę i zmień z Aktywnej na Wszystkie. Wybierz pole z tabeli odnośników, a następnie ostrzeże Cię, że musisz utworzyć relację lub automatycznie wykryć, w 2013 musisz kliknąć UTWÓRZ. Ale to co, 4 kliknięcia, aby go utworzyć, 5 jeśli policzysz przycisk OK, więc naprawdę, bardzo łatwe do zrobienia.
W porządku, Colin, Michael i Alejandro Quiceno zasugerowali ogólnie Power Pivot do książek, dzięki, dzięki, że wpadliście, do zobaczenia następnym razem z kolejnym netcastem od!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2014.xlsx
