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