Jak wykonać obliczenia (takie jak WYSZUKAJ.PIONOWO) dla każdego elementu, który został wpisany Alt + w komórce.
Obejrzyj wideo
- Przeglądarka pobiera dane z systemu, w którym każdy element jest oddzielony klawiszami Alt + Enter
- Bill: Dlaczego to robisz? Przeglądający: w ten sposób dziedziczę dane. Chcę, żeby tak zostało.
- Bill: Co chcesz zrobić z 40% wartości, których nie ma w tabeli? Przeglądający: Brak odpowiedzi
- Bill: Jeśli masz najnowsze narzędzia Power Query, można to rozwiązać w skomplikowany sposób.
- Zamiast tego do rozwiązania tego problemu służy makro VBA - makro powinno działać aż do programu Excel 2007
- Zamiast wykonywać WYSZUKAJ.PIONOWO, wykonaj serię funkcji Znajdź i zamień za pomocą VBA
Transkrypcja wideo
Learn Excel From, Podcast Episode 2150: VLOOKUP Każdy Alt + wprowadzona wartość w każdej komórce.
Hej. Witamy ponownie w netcast. Jestem Bill Jelen. Dzisiaj jedno z bardziej dziwacznych pytań. Ktoś powiedział, hej, chcę zrobić WYSZUKAJ.PIONOWO dla każdej wartości w komórce, a kiedy otworzyłem plik Excela, dane zostały wprowadzone ALT +. Tak więc są 4 elementy w tej kolejności i wszystkie są oddzielone przez ALT + ENTER, a następnie tylko 2 tutaj i 6 tutaj i tak dalej.
Wróciłem do osoby, która to przesłała. Pomyślałem, że to naprawdę zły sposób przechowywania tych danych. Dlaczego to robisz? A on na to, że ja tego nie robię. W ten sposób dane są pobierane. Powiedziałem, czy w porządku, jeśli podzielę to na oddzielne rzędy? Nie, musisz to tak trzymać.
W porządku. Nie ma więc dobrego sposobu na wykonanie WYSZUKAJ.PIONOWO dla każdego pojedynczego elementu, a jutro, w jutrzejszym odcinku 2151, pokażę, jak możemy użyć do tego zupełnie nowej funkcji dodatku Power Query, ale mieć Office 365, aby to mieć.
Tak więc dzisiaj chcę użyć metody, która ma sięgać wstecz, a to, co tutaj zrobiłem, to utworzenie nowego arkusza roboczego z LOOKUPTABLE, więc to są elementy. Zauważyłem również, że jest cała masa rzeczy, około 40% rzeczy tutaj, nie znajduje się w WYSZUKIWARCE. Powiedziałem, co chcesz tam robić i nie mam odpowiedzi na to pytanie, więc zostawię je tak, jak są, jeśli nie znajdę dopasowania.
W porządku, mam tutaj arkusz o nazwie LOOKUPTABLE, a zobaczysz, że mój plik jest teraz przechowywany jako xlsx i zamierzam użyć makra VBA. Aby użyć makra VBA, nie możesz mieć go jako xlsx. To niezgodne z zasadami. Więc musisz ZAPISZ JAKO i zapisać to jako xlsm. ZAREJESTRUJ SIĘ, ZAPISZ JAKO i zmień go z PODRĘCZNIKA ROBOCZEGO na PODRĘCZNIK ROBOCZY XLSM Z OBSŁUGĄ MAKRO lub PODRĘCZNIK BINARNY - którykolwiek z nich zadziała - w porządku i kliknij ZAPISZ.
W porządku, więc teraz możemy uruchamiać makra. ALT + F11, aby dostać się do rejestratora makr. Zaczynasz od tego dużego szarego ekranu. INSERT, MODULE, a oto nasz moduł, a oto kod. Tak więc nazwałem to ReplaceInPlace i definiuję jeden arkusz. To jest LookupTable. Umieściłbyś tam swoją prawdziwą nazwę arkusza tabeli przeglądowej, a moja tabela przeglądowa zaczyna się w kolumnie A, czyli kolumnie 1. Więc przechodzę do ostatniego wiersza w kolumnie 1, naciskam klawisz END i strzałkę W GÓRĘ lub oczywiście, CONTROL + strzałka W GÓRĘ zrobiłby to samo, zorientowałoby się, który to wiersz, a następnie przejdziemy z każdego wiersza od 2 do ostatniego wiersza. Dlaczego 2? Cóż, ponieważ nagłówki znajdują się w wierszu 1. Więc chcę zamienić, zaczynając od wiersza 2, aż do ostatniego wiersza, więc dla każdego wiersza od 2 do ostatniego wiersza wartość FromValue jest tym, co 'sw kolumnie A, a ToValue jest tym, co jest w kolumnie B.
Teraz, jeśli z jakiegoś powodu twoje dane byłyby w J i K, to J byłby dziesiątą kolumną, więc wstawiasz tam 10, a K byłoby 11 kolumną, a następnie, w Zaznaczeniu, zastąpimy FromValue do ToValue. To jest naprawdę ważne. xlPart, xlPart - i to jest L, a nie liczba 1 - xlPart, która mówi, że pozwoli nam zastąpić część komórki, ponieważ wszystkie te numery części są oddzielone znakiem wysuwu wiersza. Chociaż tego nie widać, jest tam. To powinno pozwolić nam nie przypadkowo zaktualizować niewłaściwą rzecz, a następnie xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
W porządku. A więc to jest nasze małe makro. Spróbujmy. Weźmiemy te dane, a ja nie chcę niczego niszczyć, więc po prostu wezmę oryginalne dane i skopiuję je w prawo. W porządku. Mamy tam więc nasz wybór. Właściwie zacznę od tego punktu. CONTROL + BACKSPACE, a następnie ALT + F8, aby uzyskać listę wszystkich makr. Jest nasz REPLACEINPLACE. Kliknę URUCHOM i wszędzie tam, gdzie znalazł element w TABELI WYSZUKIWANIA, zamienił ten numer na ten element, pozornie wykonując WYSZUKAJ.PIONOWO, chociaż w ogóle nie rozwiązujemy tego za pomocą WYSZUKAJ.
W porządku. Więc, hej, zupełnie nowa książka, która wyszła - Power Excel With, wydanie 2017, 617 Excel Mysteries Solved - zawiera wszelkiego rodzaju świetne nowe wskazówki.
Dzisiejsze podsumowanie: przeglądarka pobiera dane z systemu, w którym każdy element jest oddzielony klawiszami ALT + ENTER, a następnie musi wykonać WYSZUKAJ.PIONOWO przy każdym elemencie i, wiesz, dlaczego to robię; więc, osoba powiedziała, nie robię tego, ale muszę tak trzymać; a potem 40% wartości nie ma w tabeli, no cóż, brak odpowiedzi; więc myślę, że zamierzają dodać te pozycje do tabeli; teraz, jutro, porozmawiamy o tym, jak rozwiązać ten problem za pomocą dodatku Power Query, ale dziś to makro będzie działać we wszystkich wersjach programu Excel dla systemu Windows, wracając przynajmniej do programu Excel 2007; więc zamiast WYSZUKAJ.PIONOWO wystarczy seria funkcji znajdź i zamień na VBA.
Więc hej. Chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2150.xlsm
