Ako vykonať výpočet (napríklad VLOOKUP) pre každú položku, ktorá bola v bunke Alt + zadaná.
Pozeraj video
- Prehliadač sťahuje údaje zo systému, v ktorom sú jednotlivé položky oddelené klávesovou skratkou Alt + Enter
- Bill: Prečo to robíš? Prehliadač: Takto dedím údaje. Chcem to tak udržať.
- Bill: Čo chceš urobiť so 40% hodnôt, ktoré nie sú v tabuľke? Prehliadač: Žiadna odpoveď
- Bill: Existuje zložitý spôsob, ako to vyriešiť, ak máte najnovšie nástroje Power Query.
- Namiesto toho to treba vyriešiť makrom VBA - makro by malo fungovať až do programu Excel 2007
- Namiesto toho, aby ste robili VLOOKUP, urobte sériu Find & Replace with VBA
Prepis videa
Naučte sa Excel od, Podcast Episode 2150: VLOOKUP Každý Alt + zadaná hodnota v každej bunke.
Ahoj. Vitajte späť na netcaste. Som Bill Jelen. Dnes jedna z najbizarnejších otázok. Niekto povedal, že hej, chcem urobiť VLOOKUP pre každú hodnotu v bunke, a keď som otvoril súbor programu Excel, údaje boli ALT + zadané. Takže v tomto poradí sú 4 položky a všetky sú oddelené klávesami ALT + ENTER a potom iba 2 tu a 6 tu a tak ďalej.
Vrátil som sa k osobe, ktorá to poslala. Bol som rád, no, toto je naozaj zlý spôsob ukladania týchto údajov. Prečo to robíš? A on bol rád, som akoby som to nerobil. Týmto spôsobom sa sťahujú údaje. Povedal som, je to v poriadku, ak to rozdelím do samostatných riadkov? Nie, musíte to tak držať.
Dobre. Neexistuje teda dobrý spôsob, ako urobiť VLOOKUP pre každú jednotlivú položku, a zajtra, v zajtrajšej epizóde, 2151, vám ukážem, ako na to môžeme použiť úplne novú funkciu v Power Query, ale mali by ste mať Office 365.
Takže dnes chcem použiť metódu, ktorá sa vráti úplne späť, a to, čo som tu urobil, je, že som vytvoril nový pracovný hárok s výrazom LOOKUPTABLE, takže toto sú položky. Tiež som si všimol, že je tu kopa vecí, asi 40% vecí tu nie je v VYHĽADÁVANÍ. Povedal som, čo tam chcete robiť, a na túto otázku nereagujem, takže ich akurát nenájdem, nechám ich tak, ako sú.
Dobre, takže to, čo tu mám, je, že mám list s názvom LOOKUPTABLE a uvidíte, že môj súbor je teraz uložený ako xlsx a použijem makro VBA. Ak chcete použiť makro VBA, nemôžete ho mať ako xlsx. Je to v rozpore s pravidlami. Takže musíte ULOŽIŤ AKO a uložiť toto je xlsm. SÚBOR, ULOŽIŤ AKO, a z WORKBOOKU ho zmeňte na WORKBOOK XLSM ZAPNUTÝ MAKRO, alebo BINÁRNY WORKBOOK - buď bude jeden z nich fungovať - v poriadku, a kliknite na ULOŽIŤ.
Dobre, takže teraz môžeme spúšťať makra. ALT + F11 sa dostanete do záznamníka makier. Začínate s touto veľkou sivou obrazovkou. INSERT, MODULE a tu je náš modul a tu je kód. Takže som to nazval ReplaceInPlace a definujem jeden pracovný list. To je LookupTable. Dali by ste tam svoj skutočný názov hárka vyhľadávacej tabuľky a potom sa moja vyhľadávacia tabuľka začne v stĺpci A, čo je stĺpec 1. Prejdem teda na úplne posledný riadok v stĺpci 1, stlačte kláves END a šípku HORE alebo , samozrejme, šípka CONTROL + HORE by urobila to isté, zistila by, o aký riadok ide, a potom pôjdeme z každého radu od 2 po FinalRow. Prečo 2? Pretože sú nadpisy v riadku 1. Takže chcem nahradiť, počnúc riadkom 2 až do posledného riadku, a tak pre každý riadok od 2 do FinalRow je hodnota FromValue 's v stĺpci A a ToValue je to, čo je v stĺpci B.
Teraz, ak by z nejakého dôvodu boli vaše údaje v J a K, potom by toto J bolo 10. stĺpcom, takže by ste tam vložili 10, a K by bolo 11. stĺpcom, a potom vo výbere nahradíme FromValue do ToValue. Toto je tu skutočne dôležité. xlPart, xlPart - a to je L, nie číslo 1 - xlPart, ktorý hovorí, že nám umožní nahradiť časť bunky, pretože tieto čísla častí sú oddelené znakom riadku. Aj keď to nevidíte, je to tam. To by nám malo umožniť, aby sme náhodou neaktualizovali nesprávnu vec a potom xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Dobre. Toto je teda naše malé makro. Poďme to skúsiť. Vezmeme tieto údaje a ja nechcem nič zničiť, tak len vezmem pôvodné údaje a skopírujem ich doprava. Dobre. Takže tu máme náš výber. Vlastne začnem od tohto bodu. CONTROL + BACKSPACE a potom ALT + F8 získate zoznam všetkých makier. Je tu naše REPLACEINPLACE. Kliknem na RUN a všade, kde našla položku v LOOKUPTABLE, nahradilo to číslo položky položkou, zdanlivo urobilo VLOOKUP, hoci to vôbec neriešime pomocou VLOOKUP.
Dobre. Takže, hej, úplne nová kniha, ktorá vyšla - Power Excel With, vydanie 2017, 617 Excel Mysteries Vyriešené - všetky druhy skvelých nových tipov.
Dnešné zhrnutie: prehliadač sťahuje údaje zo systému, kde je každá položka oddelená klávesovou skratkou ALT + ENTER, a potom pri každej položke musí urobiť VLOOKUP a viete, prečo to robím; takže ten človek povedal, že to nerobím, ale musím to takto udržiavať; a potom 40% hodnôt nie je v tabuľke, teda žiadna odpoveď; takže myslím, že pridajú tieto položky do tabuľky; teraz, zajtra, si povieme, ako to vyriešiť pomocou Power Query, ale dnes bude toto makro fungovať úplne späť vo všetkých verziách Excelu pre Windows, prinajmenšom späť do Excelu 2007; takže namiesto VLOOKUPU stačí séria vyhľadania a nahradenia pomocou VBA.
No hej. Chcem sa vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2150.xlsm
