Как да направите изчисление (като VLOOKUP) за всеки елемент, който е бил Alt + Въведен в клетка.
Гледам видео
- Зрителят изтегля данни от система, където всеки елемент е разделен с Alt + Enter
- Бил: Защо правиш това? Преглед: Това е начинът, по който наследявам данните. Искам да го запазя така.
- Бил: Какво искате да направите с 40% от стойностите, които не са в таблицата? Зрител: Няма отговор
- Бил: Има сложен начин да се реши това, ако имате най-новите инструменти за Power Query.
- Вместо това, VBA макрос за решаването му - макросът трябва да работи чак до Excel 2007
- Вместо да правите VLOOKUP, направете серия от Find & Replace с VBA
Видео транскрипция
Научете Excel от, Подкаст Епизод 2150: ПРЕГЛЕД НА всеки Alt + въведена стойност във всяка клетка.
Хей. Добре дошли отново в netcast. Аз съм Бил Джелен. Днес един от най-странните въпроси. Някой каза, хей, искам да направя VLOOKUP за всяка стойност в клетката и когато отворих файла на Excel, данните са ALT + Въведени. И така, има 4 елемента в този ред и всички те са разделени от ALT + ENTER, а след това само 2 тук и 6 тук и така нататък.
Върнах се при човека, който изпрати това. Бях като, е, това е наистина лош начин за съхраняване на тези данни. Защо правиш това? И той беше като, аз като че ли не го правя. Това е начинът, по който се изтеглят данните. Казах, добре ли е, ако го разделя на отделни редове? Не, трябва да го запазите по този начин.
Добре. Така че, няма добър начин да направите VLOOKUP за всеки отделен елемент, а утре, в утрешния епизод, 2151, ще ви покажа как можем да използваме чисто нова функция в Power Query, за да направим това, но вие ще имате да има Office 365, за да има това.
И така, днес искам да използвам метод, който ще се върне по целия път назад и това, което направих тук е, че създадох нов работен лист с LOOKUPTABLE, така че това са елементите. Също така забелязах, че има цял куп неща, около 40% от нещата тук, не са в LOOKUPTABLE. Казах, какво искате да правите там и няма отговор на този въпрос, така че просто ще ги оставя такива, каквито са, ако не намеря съвпадение.
Добре, така че това, което имам тук, е, че имам лист, наречен LOOKUPTABLE и ще видите, че файлът ми в момента се съхранява като xlsx и ще използвам макрос VBA. За да използвате VBA макрос, не можете да го имате като xlsx. Това е против правилата. И така, трябва да СПАСИТЕ КАТО и да запазите това е xlsm. ФАЙЛИРАЙТЕ, ЗАПАЗЕТЕ КАТО и го променете от РАБОТНИК на МАКРО АКТИВИРАНА РАБОТНИК XLSM или ДВОЙНА РАБОТНИЧНА КНИГА - или една от тях ще работи - добре, и щракнете ЗАПАЗИ.
Добре, така че сега ни е разрешено да изпълняваме макроси. ALT + F11, за да стигнете до макрорекордера. Започвате с този голям сив екран. INSERT, MODULE, и там е нашият модул, и ето кода. И така, нарекох го ReplaceInPlace и определям един работен лист. Това е LookupTable. Бихте поставили истинското име на работния лист за справочна таблица там и тогава моята справочна таблица започва в колона А, която е колона 1. И така, отивам до последния ред в колона 1, натискам бутона END и стрелката НАГОРЕ , разбира се, стрелката CONTROL + UP ще направи същото, ще разбере кой ред е и след това ще преминем от всеки ред от 2 към FinalRow. Защо 2? Е, тъй като заглавията са в ред 1. Така че искам да заменя, започвайки от ред 2 чак до последния ред, и така, за всеки ред от 2 до FinalRow, FromValue е какво 's в колона A и ToValue е това, което е в колона B.
Сега, ако по някаква причина вашите данни са в J и K, тогава този J ще бъде 10-та колона, така че вие поставяте 10 там, а K ще бъде 11-та колона, а след това, в селекцията, ще заменим FromValue към ToValue. Тук това е наистина важно. xlPart, xlPart - и това е L, а не число 1 - xlPart, което казва, че ще ни позволи да заменим част от клетката, тъй като всички тези номера на части са разделени от символа за линейно подаване. Въпреки че не можете да го видите, той е там. И така, това трябва да ни позволи да не актуализираме случайно грешното нещо и след това xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Добре. И така, това е нашият малък макрос тук. Да пробваме. Ще вземем тези данни и не искам да унищожавам нищо, така че просто ще взема оригиналните данни и ще ги копирам вдясно. Добре. И така, имаме своя избор там. Всъщност ще започна от този момент. CONTROL + BACKSPACE и след това ALT + F8, за да получите списък с всички макроси. Има нашето REPLACEINPLACE. Ще щракна RUN и навсякъде, където е намерил елемент в LOOKUPTABLE, той е заменил този номер на елемент с елемента, като изглежда прави VLOOKUP, въпреки че изобщо не го решаваме с VLOOKUP.
Добре. И така, хей, чисто новата книга, която излезе - Power Excel With, изданието 2017, 617 Excel Mysteries Solved - всякакви страхотни нови съвети там.
Днес обобщение: зрителят изтегля данни от система, при която всеки елемент е разделен с ALT + ENTER и след това трябва да направи VLOOKUP за всеки елемент и, знаете ли, защо правя това; така че, каза човекът, аз не го правя, но трябва да го запазя по този начин; и тогава 40% от стойностите не са в таблицата, добре, няма отговор; така че предполагам, че ще добавят тези елементи в таблицата; сега, утре, ще говорим за това как да решим това с Power Query, но днес този макрос ще работи докрай във всички версии на Excel на Windows, връщайки се най-малко до Excel 2007; така че, вместо VLOOKUP, просто поредица за намиране и замяна с VBA.
Е, хей. Искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast2150.xlsm
