Kaley z Nashville pracuje nad arkuszem biletów. Dla każdego wydarzenia wybiera plan biletowy. Ten plan biletowy może wskazywać od 4 do 16 rodzajów biletów na wydarzenie. Kaley potrzebuje formuły, która przejdzie do tabeli przeglądowej i zwróci * wszystkie * dopasowania, wstawiając odpowiednio nowe wiersze.
Chociaż nie mam funkcji WYSZUKAJ.PIONOWO, która może rozwiązać ten problem, nowe narzędzia Power Query wbudowane w program Excel 2016 mogą to rozwiązać.
Uwaga
Jeśli masz wersję programu Excel 2010 lub Excel 2013 dla systemu Windows, możesz bezpłatnie pobrać dodatek Power Query od firmy Microsoft. Niestety dodatek Power Query nie jest jeszcze dostępny dla programów Excel dla systemu Android, Excel dla iOS ani Excel dla komputerów Mac.
Aby zilustrować cel: Mike McCann and the Mechanics pojawia się w Allen Theatre z planem biletów C. Ponieważ są cztery pasujące wiersze w tabeli przeglądowej, Kaley chce czterech wierszy, które mówią Mike McCann i Mechanics, każdy z innym dopasowaniem od tabela przeglądowa.
Wybierz komórkę w oryginalnej tabeli. Naciśnij Ctrl + T, aby oznaczyć te dane jako tabelę. Na karcie Narzędzia tabel zmień nazwę tabeli z Tabela1 na Pokazy. Powtórz to dla tabeli przeglądowej, nazywając ją Ticketami.
Wybierz komórkę w tabeli Programy. Na karcie Dane wybierz opcję Z tabeli / zakresu.
Po otwarciu edytora Power Query otwórz listę rozwijaną Zamknij i wczytaj i wybierz opcję Zamknij i wczytaj do….
W oknie dialogowym Importuj dane wybierz opcję Utwórz tylko połączenie.
Przejdź do tabeli biletów. Powtórz kroki, aby utworzyć tylko połączenie z biletami. Powinieneś zobaczyć oba połączenia w okienku Zapytania:
Wybierz dowolną pustą komórkę. Wybierz dane, pobierz dane, połącz zapytania, scal.
W oknie dialogowym Scal znajduje się sześć kroków. Trzecie i czwarte nie wydają mi się intuicyjne.
- Wybierz Programy z górnego menu rozwijanego
- Wybierz Bilety z drugiego menu rozwijanego.
- Kliknij nagłówek planu biletów u góry, aby wybrać tę kolumnę jako klucz obcy w tabeli Przedstawienia.
- Kliknij nagłówek Ticket Plan u dołu, aby wybrać tę kolumnę jako pole kluczowe w tabeli przeglądowej.
- Otwórz typ łączenia i wybierz opcję Wewnętrzne (tylko pasujące wiersze).
- Kliknij OK
Wyniki są początkowo rozczarowujące. Zobaczysz wszystkie pola z tabeli 1 i kolumnę z napisami Tabela, Tabela, Tabela.
Kliknij ikonę Rozwiń u góry kolumny Bilety.
Odznacz Plan biletowy, ponieważ masz już to pole. Pozostałe pole będzie nazwane Bilety.Typ biletu, chyba że odznaczysz Użyj oryginalnej nazwy jako prefiksu.
Sukces! Każdy wiersz w każdym przedstawieniu rozbija się na wiele wierszy.
Nie jestem szczególnie zadowolony z sortowania danych. Sortowanie według daty powoduje, że typy biletów są sortowane w dziwny sposób.
Obejrzyj wideo
W dzisiejszym przypadku wideo zostało nakręcone po napisaniu artykułu. Sugeruję dodanie kolumny sekwencji do Typów zgłoszeń, aby kontrolować porządek sortowania.
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2204: Zwróć wszystkie WYSZUKAJ.PIONOWO.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie od Nashville Music City. Byłem tam w Nashville, ktoś jest odpowiedzialny za planowanie załadunku biletów do systemu biletowego, więc oto, co mamy: Mamy listę wydarzeń - nadchodzące wydarzenia - mamy datę, miejsce i plan biletów. Więc, chociaż coś odbywa się w Pałacu, mogą być różne plany biletów - na przykład, może piętro jest skonfigurowane, wiesz, z siedzeniami, a może to tylko miejsce stojące, prawda?
Tak więc, w zależności od rodzaju planu biletów, musisz podejść tutaj do tabeli wyszukiwania i znaleźć wszystkie pasujące zdarzenia, a zasadniczo zrobimy to, co nazywam eksplozją VLOOKUP. Więc jeśli coś jest w Hannah C, pójdą do Hannah C, a jeśli są - 1, 2, 3, 4, 5, 6 - 7 pozycji w Hannah C, będziemy mieć aby zwrócić siedem wierszy - co oznacza, że będziesz musiał wstawić sześć kolejnych wierszy i skopiować te dane. W porządku.
Teraz w ogóle nie będziemy tego robić z WYSZUKAJ.PIONOWO, ale rozumiesz koncepcję - robimy WYSZUKAJ.PIONOWO i zwracamy wszystkie odpowiedzi jako nowe wiersze. W porządku, więc zamierzam wziąć obie te tabele i przekształcić je w prawdziwy stół za pomocą Ctrl + T. Pierwsze nazywały się Tabela 1 - okropna nazwa, nazwijmy to Zdarzeniami lub Pokazami, nazwijmy to Pokazami, w ten sposób - a drugie, hej, oto czego się nauczyłem, ponieważ to ćwiczyłem - musimy mieć pole sekwencji tutaj. Więc = WIERSZ (A1), kliknij dwukrotnie i skopiuj to w dół, a następnie skopiuj i wklej wartości specjalne. W porządku. Teraz zrobimy to w tabeli - Ctrl + T i nazwiemy to Ticketami.
W porządku. Więc mamy koncerty, mamy bilety. Mam zamiar przejść do karty Dane i jestem tutaj w kwestii pokazów, chcę powiedzieć, że chcę uzyskać dane z tabeli lub zakresu - tak przy okazji, to jest Power Query. Jeśli wróciłeś do programu Excel 2010 lub 2013, możesz pobrać to bezpłatnie z witryny firmy Microsoft, pobierz narzędzie Power Query. Jeśli używasz komputera Mac, iOS lub Android, przepraszam, nie ma dla Ciebie dodatku Power Query. W porządku, więc z tabeli lub zakresu… znajdź kogoś, kto ma-- znajdź znajomego, który ma-- komputer z systemem Windows i niech to skonfiguruje. W porządku. Oto tabela, nie zamierzamy nic z tym robić, wystarczy zamknąć i załadować, zamknąć i załadować do, a następnie powiedzieć „Tylko utwórz połączenie”, doskonale. Przejdziemy tutaj do naszej drugiej tabeli: Pobierz dane, z tabeli lub zakresu, nic nie robimy z tym, Zamknij i załaduj,Zamknij i załaduj do, „Tylko utwórz połączenie”, OK. Mamy więc teraz połączenie z pierwszą tabelą i połączeniem z drugą tabelą. Nie zamierzamy łączyć tych dwóch, co zasadniczo jest jak robienie WYSZUKAJ.PIONOWO lub połączenie bazy danych, jak sądzę, jest naprawdę takie, jakie jest. Połącz zapytania, zamierzamy scalić. W porządku.
Teraz siedem rzeczy, które musisz zrobić w tym oknie dialogowym - i jest to trochę zagmatwane - wybierzemy Pokazy jako pierwszą tabelę; wybierz Bilety jako drugi stół; wybierz, które pola mają wspólne, może to być wiele pól - możesz nacisnąć klawisz Ctrl i kliknąć - ale w tym przypadku jest tylko jeden plan biletów; a następnie Plan biletowy; a następnie zmienimy typ sprzężenia na sprzężenie wewnętrzne z „tylko pasującymi wierszami”. W porządku. Teraz klikasz OK i myślisz, że cały twój problem zostanie rozwiązany, ale jesteś po prostu zmiażdżony, ponieważ tutaj są wszystkie dane z A - w ogóle nie wstawili żadnych nowych wierszy - a tutaj, po prostu nudne, głupie pole o nazwie Bilety, które ma tylko stół, stół, stół, hah.
Ale na szczęście na górze znajduje się ikona Rozwiń i zamierzamy ją rozszerzyć - nie muszę brać planu, już to mam - Typ i kolejność biletów. Nie chcę, aby nazywało się to Tickets.TicketType, co chce zrobić dodatek Power Query - więc odznaczam to pole. W porządku. W tej chwili mamy 17 wierszy danych; kiedy kliknę OK, BAM! Jest eksplozja. Tak więc Michael Seeley i Starlighter pojawiają się z różnymi typami biletów, takimi jak ten. W porządku i zobacz, jak te typy biletów pojawiają się po kolei, to świetnie. Ale Michael Seeley nie jest kolejnym koncertem, następny jest 5 czerwca. Więc kiedy próbuję posortować to według daty - to doprowadza mnie do szaleństwa, nie mogę tego wyjaśnić. Sortuj według daty, a Mike Man and the Mechanics dochodzi do 65, ale potem wszystkie bilety są spieprzone. One'są na złej kolejności i dlatego musiałem zrobić tę sekwencję - czuję w ten sposób. Mogę sortować według kolejności. Więc teraz, 6, 5, pięknie, a potem bilety są poprawne. Właściwie w tym momencie nie potrzebujemy już tej kolumny. Mogę więc kliknąć prawym przyciskiem myszy i usunąć, a następnie Close & Load - tym razem zamknę i załaduję, a nie Close & Load to - i mamy nasz wynik. W porządku.
Więc przeszliśmy z listy wydarzeń do tej całej dużej listy, ale oto niesamowita część: schrzaniłem to, Mike Man and Mechanics to nie Palace B, to jego Palace C.Więc wracam do oryginału w prawym górnym rogu -kącik dłoni, aby uzyskać więcej informacji o książce.
W porządku. Tematy w tym odcinku: Kaley w Nashville musi wykonać WYSZUKAJ.PIONOWO, aby zwrócić wszystkie dopasowania, zwykle wstawiając nowe wiersze. I to jest baza danych biletów, dobrze? Nazywam to więc eksplozją WYSZUKAJ.PIONOWO, ponieważ każdy program eksploduje do 16 rzędów. Zamierzamy rozwiązać ten problem za pomocą dodatku Power Query i dowiedziałem się, że data będzie wyświetlana w niewłaściwej kolejności, chyba że dodamy pole Sekwencja do typu biletu. Połącz oba zestawy w tabelę za pomocą Ctrl + T; nazwij je jako Przedstawienia i Bilety; a następnie z każdej tabeli Get Data, From Table, Close & Load, aby tylko utworzyć połączenie; powtórz dla drugiej tabeli; następnie Dane, Pobierz dane, Połącz zapytania, Połącz; a potem to okno dialogowe, jest dla mnie dość mylące - wybierz Wydarzenia, wybierz Bilety, kliknij Typ biletu w obu, zmień połączenie na połączenie wewnętrzne,kliknij OK, a otrzymasz okropnie rozczarowujący wynik, w którym jest to tylko kolumna z napisami Tabela, Tabela, Tabela, Tabela; kliknij ikonę Rozwiń u góry tego; wybierz pole Sekwencja biletów; nie poprzedzaj nazwą tabeli; i możesz sortować według daty, sortować według kolejności; Zamknij i załaduj do arkusza kalkulacyjnego. Piękną rzeczą jest to, że jeśli zmieniają się podstawowe dane - po prostu odśwież i masz swoje wyniki.
Teraz, hej, aby pobrać skoroszyt użyty z dzisiejszego filmu, odwiedź adres URL w opisie YouTube. Jest tam również lista nadchodzących seminariów - bardzo chciałbym zobaczyć Cię na jednym z moich seminariów Power Excel na żywo.
Chcę podziękować Kaley za pojawienie się w Nashville i zadanie mi tego wspaniałego pytania. Chcę, żebyś się zatrzymał. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik Excela: return-all-vlookups.xlsx
Power Query nadal mnie zadziwia. To jest drugi z trzydniowej serii, w której odpowiedzią jest dodatek Power Query:
- Wtorek: przekonwertuj kolumnę daty / godziny na samą datę
- Dzisiaj: zwróć wszystkie VLOOKUP
- Czwartek: Utwórz ankietę dla każdego z 1100 elementów
Mam całą playlistę YouTube zawierającą rzeczy, które ostatecznie rozwiązałem za pomocą 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:
„W razie wątpliwości użyj funkcji ROUND!”
Mike Girvin
