Dzisiejsze pytanie od Quentina, który był na moim seminarium Power Excel w Atlancie. Quentin musi wygenerować te same 7 pytań ankietowych dla każdego z ponad 1000 klientów w programie Excel.
Jak widać na tym rysunku, klienci są w A. Pytania do powtórzenia znajdują się w kolumnie D.
Możesz rozwiązać ten problem za pomocą VBA lub formuł, ale jest to tydzień Power Query o godzinie, więc zamierzam użyć fajnej sztuczki w dodatku Power Query.
Jeśli chcesz, aby między każdą ankietą był pusty wiersz, dodaj numer kolejny i numer 7 po ostatnim pytaniu.
Naciśnij Ctrl + T z obu zestawów danych. Nazwij drugi zestaw danych nazwą, którą możesz zapamiętać, na przykład Pytania lub Ankieta.
Z drugiego zestawu danych użyj opcji Dane, z tabeli.
Otworzy się edytor Power Query. Na karcie Strona główna wybierz listę rozwijaną Zamknij i załaduj i wybierz opcję Zamknij i wczytaj do…. W następnym oknie dialogowym wybierz opcję Tylko utwórz połączenie.
Jesteś z powrotem w programie Excel. Wybierz dowolną komórkę w tabeli klientów w kolumnie A. Dane z tabeli. Po otwarciu edytora zapytań kliknij kartę Dodaj kolumnę na Wstążce, a następnie wybierz opcję Kolumna niestandardowa. Formuła to =#"Questions"(w tym # i cudzysłowy).
W edytorze pojawi się nowa kolumna z tabelą wartości powtórzoną w każdym wierszu. Kliknij ikonę Rozwiń w nagłówku kolumny.
Wybierz oba pola w tabeli. Z zakładki Home wybierz Close & Load.
Pojawi się nowy arkusz z 7 pytaniami powtórzonymi dla każdego z ponad 1000 klientów.
Obejrzyj wideo
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2205: Eksplozja ankiet kredytowych.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Otóż, zaledwie wczoraj w odcinku 2204, to Kaylee z Nashville musiała wykonać eksplozję WYSZUKAJ.PIONOWO - dla każdego elementu w kolumnie D mieliśmy pasującą grupę elementów w kolumnie G i musieliśmy je wysadzić. Tak więc, gdyby Palace C miał 8 elementów, otrzymalibyśmy 8 rzędów.
Teraz mamy Quentina. Quentin był na moim seminarium w Atlancie, ale tak naprawdę pochodzi z Florydy, a Quentin ma tutaj prawie 1000 klientów - cóż, ponad 1000 klientów - w kolumnie A i dla każdego klienta musi utworzyć tę ankietę - - ankieta zawierająca 1, 2, 3, 4, 5, 6 pytań. I to, co tutaj zrobię, to dodam numer sekwencyjny tylko z liczbami od 1 do 7, więc w ten sposób mogę utworzyć ładny pusty wiersz między nimi. Zamierzam uczynić oba te zestawy danych w tabeli; więc staramy się, aby te 7 wierszy eksplodowało dla każdego z tych 1000 klientów. To jest cel.
Teraz mogę to zrobić z VPA; Mogę to zrobić za pomocą formuł; ale mamy tu do czynienia z czymś w rodzaju „Tygodnia Power Query”. To już trzeci przykład dodatku Power Query z rzędu, więc zamierzam użyć dodatku Power Query. Zamierzam zamienić ten lewy w stół. Będę bardzo ostrożny, aby nazwać to nie Tabela 1. Nadam jej nazwę. Będziemy musieli później użyć tej nazwy ponownie, więc nadam jej nazwę Pytania - w ten sposób. A potem będzie Tabela 2, ale zamierzam zmienić jej nazwę na Klienci - nie tak ważne, żebym zmienił nazwę tej, ponieważ jest to druga, która musi mieć tę nazwę. Więc wybierzemy to; Dane; i powiemy From Table / Range. Pobieranie i przekształcanie danych - jest to znane jako dodatek Power Query. Jest wbudowany w program Excel 2016. Jeśli masz 2010 lub 2013 w systemie Windows,nie komputer Mac, nie iOS, nie Android, możesz pobrać dodatek Power Query bezpłatnie od firmy Microsoft.
Więc będziemy pobierać dane z tabeli / zakresu; oto nasz stół - nic z tym nie zrobimy, wystarczy zamknąć i załadować; Zamknij i załaduj do; tylko Utwórz połączenie; w porządku, a nazwa tego zapytania to pytania. Używa tej samej nazwy, co tutaj. A potem wracamy do tego, a Data; Z tabeli / zakresu; więc istnieje lista naszych 1000 lub więcej klientów.
Hej, oto krzyk do Miguela Escobara, mojego przyjaciela, który jest współautorem M Is For (DATA) MONKEY). Umieszczę link do tego w filmie - świetna książka o dodatku Power Query - pomogła mi w tym. Zamierzamy wstawić zupełnie nową kolumnę niestandardową, a formuła kolumny niestandardowej jest taka: = # "nazwa zapytania". Nigdy bym tego nie zrozumiał bez Miguela, więc dzięki Miguelowi za to.
A kiedy klikam OK, tak, nie wygląda na to, że zadziałało - po prostu dostajemy stół, stół, stół, ale to jest dokładnie to, co mieliśmy wczoraj z Kaylee i biletami. I wszystko, co muszę zrobić, to rozwinąć to i właściwie zamierzam powiedzieć, że prawdopodobnie nie potrzebuję Sekwencji… cóż, włóżmy to na wszelki wypadek. Możemy to wyjąć, gdy to zobaczymy. W tej chwili mamy 1000 rzędów, a teraz mamy 7000 - pięknie. Teraz widzę, że pojawia się w Sekwencji, więc nie potrzebuję tego. Kliknę prawym przyciskiem myszy i usunę tylko tę jedną kolumnę. A potem mogę wrócić do domu; Zamknij & wczytaj; i BAM! - powinniśmy teraz mieć ponad 7000 wierszy z 6 pytaniami i pustym miejscem dla każdego klienta. Quentin był tym zachwycony na seminarium. Fajna, fajna sztuczka - unika VBA, unika całej masy formuł za pomocą indeksu,i tym podobne - świetna droga.
Ale, hej, dziś pozwól, że wyślę cię z M Is For (DATA) MONKEY. Ken Puls i Miguel Escobar napisali najlepszą książkę o Power Query. Uwielbiam tę książkę; w 2 godziny staniesz się profesjonalistą dzięki tej książce.
W porządku, podsumujmy dzisiaj - Quentin musi wygenerować identyczną ankietę dla 1000 różnych klientów. Dla każdego klienta jest 6 lub 7 lub 8 pytań. Moglibyśmy to zrobić za pomocą języka VBA lub makra, ale ponieważ korzystamy tutaj z dodatku Power Query, zróbmy dodatek Power Query. Dodałem dodatkowe puste pytanie do pytań; Dodałem numer sekwencyjny, aby upewnić się, że puste miejsce pozostanie tam; uczynić klientów stolikiem; ułóż pytania w tabeli; naprawdę ważne jest, aby nazwać Pytania czymś, co zapamiętasz - nazwałem moje „Pytania”. Dodaj pytania do dodatku Power Query, tylko jako połączenie; a następnie podczas dodawania klientów do dodatku Power Query utwórz nową kolumnę niestandardową, w której formuła ma postać: # „nazwa pierwszego zapytania”, a następnie rozwiń tę kolumnę w edytorze dodatku Power Query; Blisko &Wczytaj z powrotem do arkusza kalkulacyjnego i gotowe. Niesamowita sztuczka - uwielbiam Power Query - najwspanialsza rzecz, jaka spotkała Excela od 20 lat.
Chcę podziękować Quentinowi za udział w moim seminarium. Był wcześniej na moim seminarium kilka razy - świetny facet. Chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik programu Excel: pożyczka-ankieta-eksplozja.xlsx
Power Query nadal mnie zadziwia. Zapoznaj się z książką M jak Data Monkey, aby dowiedzieć się więcej o dodatku Power Query.
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Możesz zrobić wszystko z AGREGATEM oprócz zrozumienia tego”.
Liam Bastick
