Суперсила Мгновенного заполнения (Flash Fill)
Эксперименты делают из нас героев.
(Флэш)
Хотя инструмент Мгновенное заполнение (Flash Fill) появился в Excel ещё с 2013-й версии, но почему-то для многих пользователей этот факт остался незамеченным. И совершенно напрасно. Во многих случаях он оказывается проще, легче и быстрее, чем аналогичные решения на формулах или макросах. По моему опыту, на тренингах эта тема вызывает постоянное "вау!" аудитории - независимо от продвинутости и/или усталости слушателей.
Механизм работы этого инструмента прост: если у вас есть один или несколько столбцов с исходными данными и вы начинаете набирать рядом в соседнем столбце их же, но в каком-либо нужном вам измененном виде, то Excel рано или поздно намекнёт, что готов продолжить дальше за вас:
Чтобы выявить логику (шаблон, pattern) преоборазования и запустить эту функцию Excel обычно хватает ввода 1-3 первых результирующих значений вручную. Если предложенный вариант вам подходит, то достаточно нажать Enter - и остаток списка будет доделан моментально.
Если вы уже ввели 2-3 первых значения, а продолжение всё не появляется, то можно форсировать процесс сочетанием клавиш Ctrl+E или использовать кнопку Мгновенное заполнение (Flash Fill) на вкладке Данные (Data):
Давайте рассмотрим несколько примеров использования этого инструмента на практике, чтобы понять его возможности.
Извлечение слов из текста и перестановки
Написать формулу, которая извлекает, например, третье слово из текста в ячейке - маленький подвиг. Разобрать фразу по пробелу в разные колонки с помощью Данные - Текст по столбцам (Data - Text to Columns) тоже дело не быстрое. С помощью мгновенного заполнения это делается легко и красиво. Причем, можно попутно менять извлекаемые слова местами, комбинируя их в любом порядке:
Деление текста по регистру
Для выделения слов мгновенному заполнению совершенно не обязательно наличие именно пробела. Отлично сгодится и любой другой разделитель, например, запятая или точка-с-запятой после импорта CSV-файла. Но что совсем круто, так это то, что разделителя может и не быть совсем - достаточно только заглавных букв:
Реализовать подобное формулами очень тяжко. Если без мгновенного заполнения, то поможет только макрос.
Склейка текста
Если можно делить, то можно и клеить! Мгновенное заполнение легко соберёт для вас длинную фразу из нескольких фрагментов, перемежая их нужными пробелами, запятыми, союзами или словами:
Извлечение отдельных символов
Обычно для вытаскивания отдельных символов и подстрок в Excel используются функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID) и им подобные, но мгновенное заполнение с легкостью решает и эту задачу. Классический пример - формирование ФИО:
Извлечение только чисел, текста или дат
Если вы когда-нибудь пытались вытащить только нужный тип данных из буквенно-цифровой каши, то должны понимать всю сложность этой простой, на первый взгляд, задачи. Мгновенное заполнение и тут справляется "на ура", но нужен лёгкий пендель в виде Ctrl+E:
С извлечением текста тоже самое:
Даты - тоже не проблема (даже если они написаны в разных форматах):
Преобразование форматов чисел или дат
Мгновенное заполнение поможет изменить внешний вид имеющихся данных или привести их к "одному знаменателю". Например, обычную дату преобразовать "шиворот-навыворот" в Unix-формат:
Здесь нюанс в том, что перед вводом нужно заранее поменять формат итоговых ячеек на текстовый, чтобы Excel не пытался распознать введенные вручную в качестве образца "неправильные" даты.
Аналогичным образом можно также правильно представить телефонные номера, добавив код страны и трехзначный префикс оператора (города) в скобках:
Не забудьте сначала поменять формат ячеек в столбце В на текстовый - иначе Excel будет воспринимать значения начинающиеся со знака "+" как формулы.
Преобразование текста (чисел) в дату
При выгрузках из различных ERP и CRM-систем часто дата представляется 8-значным числом в формате ГГГГММДД. Конвертировать её в нормальный вид можно либо функцией ДАТАЗНАЧ (DATEVALUE), либо гораздо проще - мгновенным заполнением:
Изменение регистра
Если вам достался текст с нЕпрАвИльНЫм рЕгисТроМ, то можно просто намекнуть в соседнем столбце к какому виду вы хотите его преобразовать - и мгновенное заполнение сделает за вас всю работу:
Чуть сложнее будет, если нужно изменить регистр по разному у разных частей текста. Например, сделать заглавными только второе слово, оставив первое в нормальном виде. Здесь двух введённых в качестве образца значений окажется недостаточно и придется внести правки, которые мгновенное заполнение тут же учтёт в результатах:
Ограничения и нюансы
При использовании в работе мгновенного заполнения следует помнить несколько моментов:
- Оно срабатывает только, если вводить образцы строго рядом - в предыдущем или следующем столбце справа от данных. Если отступить одну пустую колонку от исходного текста, то ничего не получится.
- При выявлении шаблона учитываются все значения в строке - слева и справа от вводимого столбца. Мораль: лишние колонки, способные запутать алгоритм или внести помехи, лучше заранее отделить от рабочих данных пустыми столбцами или удалить.
- Мгновенное заполнение отлично работает в умных таблицах.
- Малейшая ошибка или опечатка при наборе ячеек-образцов может привести к тому, что мгновенное заполнение не сможет выявить паттерн и не сработает. Будьте внимательны.
- Есть ситуации, когда шаблон определяется некорректно, поэтому всегда нужно проверять результаты, которые вы получили (хотя бы выборочно).
Ссылки по теме
- Как извлечь последнее слово из текста в ячейке
- Нечёткий текстовый поиск (Пушкин = Пушкен) с помощью Fuzzy Lookup в Excel
- Три способа склеить текст в Excel
В данном примере возник вопрос по закладке с телефонами, а именно при мгновенном заполнении телефонов , например в формате :+7(903)545-29-85 -ячейки заполняются неверно, а именно последние цифры телефона. Делал в Вашем примере. С чем может быть это связано.
Кстати и у Вас в видео -когда Вы это сделали -произошло тоже самое, просто не успели это увидеть, тк пошел следующий слайд. Спасибо за ответ.
Так как пользовательские числовые форматы предназначены для работы в основном с номерами, невозможно создать пользовательский числовой формат, который содержит более 15 цифр. Например нельзя использовать следующий формат для хранения идентификатора 16 символов кредитной карты как число:
####-####-####-####
Если в ячейке в формате ### ###-### ### введите номер 1111222233334444 , Excel отображает 1111 2222-3333 4440 в ячейке. Фактический номер, который вы пытаетесь сохранить, составляет 1 111 222 233 334 444, что более чем один квадрильон. Но поскольку это число велико, Excel удаляет последнюю цифру (младший) и помещает нуль в его место.
При вводе числа в ячейки, отформатированные как текст, все символы остаются, как вы их вводите, поскольку Excel не пытается сохранить код кредитной карты как число, но сохраняет его как текст.
Как сделать автозаполнение из моей таблицы при вводе в ячейку А2 числа,чтоб автоматически заполнялась ячейка I2, заранее благодарен за подсказку.