Як зробити розрахунок (наприклад, VLOOKUP) для кожного елемента, який був Alt + Введений у клітинку.
Переглянути відео
- Переглядач завантажує дані із системи, де кожен елемент розділений Alt + Enter
- Білл: Чому ти це робиш? Переглядач: Це те, як я успадковую дані. Я хочу зберегти так.
- Білл: Що ви хочете зробити із 40% значень, яких немає в таблиці? Глядач: відповіді немає
- Білл: Існує складний спосіб вирішити цю проблему, якщо у вас найновіші інструменти Power Query.
- Натомість макрос VBA для його вирішення - макрос повинен працювати аж до Excel 2007
- Замість того, щоб робити VLOOKUP, зробіть серію Знайти та замінити VBA
Стенограма відео
Дізнайтеся Excel From, Підкаст Епізод 2150: ПЕРЕГЛЯНУТИ кожен Alt + введене значення в кожній комірці.
Привіт Ласкаво просимо до мережі. Я Білл Джелен. Сьогодні одне з найбільш химерних питань. Хтось сказав, ей, я хочу зробити ПЕРЕГЛЯД для кожного значення в комірці, і коли я відкрив файл Excel, дані були ALT + Введені. Отже, у цьому порядку є 4 елементи, і всі вони розділені ALT + ENTER, а потім лише 2 тут та 6 тут тощо.
Я повернувся до людини, яка надіслала це повідомлення. Мені здалося, ну, це дійсно поганий спосіб зберігати ці дані. Чому ви це робите? І він був таким, наче я цього не роблю. Це спосіб завантаження даних. Я сказав, чи добре, якщо я розділю його на окремі рядки? Ні, потрібно так тримати.
Гаразд Отже, немає хорошого способу зробити ПЕРЕГЛЯД для кожного окремого предмета, і завтра, у завтрашньому епізоді, 2151, я покажу вам, як ми можемо використовувати для цього нову функцію в Power Query, але у вас буде мати Office 365, щоб це мати.
Отже, сьогодні я хочу скористатися методом, який повернеться до повного шляху, і те, що я тут зробив, це те, що я створив новий аркуш із LOOKUPTABLE, отже, ці елементи. Я також помітив, що тут є ціла купа речей, приблизно 40% речей тут, не є в ПЕРЕГЛЯДІ. Я сказав, що ти хочеш там робити, і відповіді на це запитання немає, тому я просто залишу їх такими, як вони є, якщо я не знайду збігу.
Гаразд, отже, у мене тут є аркуш під назвою LOOKUPTABLE, і ви побачите, що мій файл зараз зберігається як xlsx, і я буду використовувати макрос VBA. Для того, щоб використовувати макрос VBA, ви не можете мати його як xlsx. Це суперечить правилам. Отже, ви повинні ЗБЕРЕГТИ ЯК і зберегти це xlsm. ФАЙЛУЙТЕ, ЗБЕРІГАЙТЕ ЯК і змініть його з РОБОЧОЇ КНИГИ на МАКРОАКТИВОВАНУ РОБОЧУ КНИГУ XLSM або ДВОЙНУ РОБОЧУ КНИГУ - будь-яка з них буде працювати - гаразд, і натисніть ЗБЕРЕГТИ.
Добре, отже, тепер нам дозволено запускати макроси. ALT + F11, щоб дістатися до макрореєстратора. Ви починаєте з цього великого сірого екрану. INSERT, MODULE, і є наш модуль, і ось код. Отже, я назвав це ReplaceInPlace і визначаю один робочий аркуш. Це LookupTable. Ви помістили б там свою справжню назву робочого аркуша таблиці пошуку, і тоді моя таблиця пошуку починалася б у стовпці A, тобто стовпці 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, щоб отримати список усіх макросів. Там наш ЗАМІНИТЕ ЗАМІСТИТИ. Я натисну RUN, і скрізь, коли він знайшов елемент у LOOKUPTABLE, він замінив цей номер елемента на елемент, мабуть, робить VLOOKUP, хоча ми взагалі не вирішуємо це з VLOOKUP.
Гаразд Отже, привіт, вийшла абсолютно нова книга - Power Excel With, видання 2017 року, 617 розгаданих таємниць Excel - всілякі чудові нові поради.
Підсумок сьогоднішнього дня: переглядач завантажує дані із системи, де кожен елемент відокремлений ALT + ENTER, а потім потрібно зробити ПЕРЕГЛЯД кожного елемента, і, знаєте, навіщо я це роблю; отже, людина сказала, що я не роблю цього, але мені потрібно так тримати; і тоді 40% значень відсутні в таблиці, ну, відповіді немає; тож я думаю, вони збираються додати ці предмети до таблиці; тепер, завтра, ми поговоримо про те, як це вирішити за допомогою Power Query, але сьогодні цей макрос працюватиме до кінця у всіх версіях Excel для Windows, повертаючись принаймні до Excel 2007; отже, замість VLOOKUP, просто серія пошуку та заміни на VBA.
Ну, привіт. Я хочу подякувати вам, що завітали. Ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2150.xlsm
