Jak provést výpočet (například VLOOKUP) pro každou položku, která byla Alt + zadána v buňce.
Sledovat video
- Prohlížeč stáhne data ze systému, kde je každá položka oddělena klávesami Alt + Enter
- Bill: Proč to děláš? Prohlížeč: Tímto způsobem zdědím data. Chci to tak udržet.
- Bill: Co chcete dělat se 40% hodnot, které nejsou v tabulce? Prohlížeč: Žádná odpověď
- Bill: Existuje složitý způsob, jak to vyřešit, pokud máte nejnovější nástroje Power Query.
- Místo toho makro VBA k jeho vyřešení - makro by mělo fungovat až do aplikace Excel 2007
- Místo VLOOKUPU proveďte řadu Najít a nahradit VBA
Přepis videa
Learn Excel From, Podcast Episode 2150: VLOOKUP Every Alt + Zadaná hodnota v každé buňce.
Ahoj. Vítejte zpět na netcastu. Jsem Bill Jelen. Dnes jedna z bizarnějších otázek. Někdo řekl, hej, chci udělat VLOOKUP pro každou hodnotu v buňce, a když jsem otevřel soubor Excel, data byla ALT + Entered. Takže v tomto pořadí jsou 4 položky a všechny jsou odděleny klávesami ALT + ENTER a pak jen 2 zde a 6 zde a tak dále.
Vrátil jsem se k osobě, která to zaslala. Byl jsem rád, no, tohle je opravdu špatný způsob, jak ukládat tato data. Proč to děláš? A on byl jako, jsem jako bych to nedělal. Tímto způsobem se stahují data. Řekl jsem, je to v pořádku, když to rozdělím do samostatných řádků? Ne, musíte to takto udržet.
V pořádku. Neexistuje tedy žádný dobrý způsob, jak provést VLOOKUP pro každou jednotlivou položku, a zítra, v zítřejší epizodě, 2151, vám ukážu, jak k tomu můžeme použít zcela novou funkci v Power Query, ale měli byste mít Office 365.
Takže dnes chci použít metodu, která se vrátí úplně zpět, a to, co jsem zde udělal, je, že jsem vytvořil nový list s LOOKUPTABLE, takže to jsou položky. Také jsem si všiml, že je tu spousta věcí, asi 40% věcí zde není v LOOKUPTABLE. Řekl jsem, co tam chcete dělat, a žádná odpověď na tuto otázku, takže je prostě nechám tak, jak jsou, pokud nenajdu shodu.
Dobře, takže to, co zde mám, je, že mám list s názvem LOOKUPTABLE a uvidíte, že můj soubor je teď uložen jako xlsx a já použiji makro VBA. Chcete-li použít makro VBA, nemůžete jej mít jako xlsx. Je to v rozporu s pravidly. Musíte tedy ULOŽIT JAKO a uložit toto je xlsm. SOUBOR, ULOŽIT JAKO, a změňte jej z WORKBOOKU na WORKBOOK XLSM S MOŽNOSTÍ MAKRO, nebo BINARY WORKBOOK - buď bude fungovat - v pořádku, a klikněte na ULOŽIT.
Dobře, takže nyní můžeme spouštět makra. ALT + F11 se dostanete do záznamníku maker. Začnete s touto velkou šedou obrazovkou. VLOŽTE, MODUL, a je tu náš modul, a tady je kód. Nazval jsem to ReplaceInPlace a definuji jeden list. To je LookupTable. Vložili byste tam svůj skutečný název listu vyhledávací tabulky a pak moje vyhledávací tabulka začíná ve sloupci A, což je sloupec 1. Přejdu tedy na úplně poslední řádek ve sloupci 1, stiskněte klávesu END a šipku NAHORU, nebo samozřejmě šipka CTRL + ŠIPKA NAHORU udělá totéž, přijde na to, o jaký řádek jde, a potom přejdeme z každé řady od 2 do FinalRow. Proč 2? Protože nadpisy jsou v řádku 1. Takže chci nahradit, počínaje řádkem 2 až do posledního řádku, a tak pro každý řádek od 2 do FinalRow je hodnota FromValue to 's ve sloupci A a ToValue je to, co je ve sloupci B.
Nyní, pokud by z nějakého důvodu byla vaše data v J a K, pak by toto J bylo 10. sloupec, takže byste tam vložili 10, a K by byl 11. sloupec, a pak ve výběru nahradíme FromValue do ToValue. To je zde opravdu důležité. xlPart, xlPart - a to je L, ne číslo 1 - xlPart, který říká, že nám umožní nahradit část buňky, protože tato čísla částí jsou oddělena znakem řádkování. I když to nevidíte, je tam. To by nám mělo umožnit, abychom omylem neaktualizovali špatnou věc, a pak xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
V pořádku. Toto je naše malé makro. Pojďme to zkusit. Vezmeme tato data a já nechci nic zničit, takže vezmu jen původní data a zkopíruji je doprava. V pořádku. Takže tam máme náš výběr. Ve skutečnosti začnu od tohoto bodu. CONTROL + BACKSPACE a poté ALT + F8 získáte seznam všech maker. Je tu naše REPLACEINPLACE. Kliknu na RUN a všude, kde našla položku v LOOKUPTABLE, nahradila toto číslo položky položkou, zdánlivě provedla VLOOKUP, ačkoli to vůbec nevyřešíme pomocí VLOOKUP.
V pořádku. Takže, hej, zbrusu nová kniha, která vyšla - Power Excel With, vydání 2017, 617 Excel Mysteries Vyřešeno - všechny druhy skvělých nových tipů.
Dnešní shrnutí: prohlížeč stáhne data ze systému, kde je každá položka oddělena klávesou ALT + ENTER, a poté musí u každé položky provést VLOOKUP a víte, proč to dělám; osoba tedy řekla, že to nedělám, ale musím to tak udržet; a pak 40% hodnot není v tabulce, no, žádná odpověď; takže myslím, že přidají tyto položky do tabulky; nyní, zítra, budeme hovořit o tom, jak to vyřešit pomocí Power Query, ale dnes bude toto makro fungovat úplně zpět ve všech verzích aplikace Excel pro Windows, minimálně zpět do aplikace Excel 2007; takže namísto VLOOKUPu stačí najít a nahradit soubor VBA.
No, hej. Chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2150.xlsm
