Суперсила Мгновенного заполнения (Flash Fill)

Эксперименты делают из нас героев.
(Флэш)

Хотя инструмент Мгновенное заполнение (Flash Fill) появился в Excel ещё с 2013-й версии, но почему-то для многих пользователей этот факт остался незамеченным. И совершенно напрасно. Во многих случаях он оказывается проще, легче и быстрее, чем аналогичные решения на формулах или макросах. По моему опыту, на тренингах эта тема вызывает постоянное "вау!" аудитории - независимо от продвинутости и/или усталости слушателей.

Механизм работы этого инструмента прост: если у вас есть один или несколько столбцов с исходными данными и вы начинаете набирать рядом в соседнем столбце их же, но в каком-либо нужном вам измененном виде, то Excel рано или поздно намекнёт, что готов продолжить дальше за вас:

Пример работы Мгновенного заполнения Flash Fill

Чтобы выявить логику (шаблон, pattern) преоборазования и запустить эту функцию Excel обычно хватает ввода 1-3 первых результирующих значений вручную. Если предложенный вариант вам подходит, то достаточно нажать Enter - и остаток списка будет доделан моментально.

Если вы уже ввели 2-3 первых значения, а продолжение всё не появляется, то можно форсировать процесс сочетанием клавиш Ctrl+E или использовать кнопку Мгновенное заполнение (Flash Fill) на вкладке Данные (Data):

Кнопка Мгновенного заполнения

Давайте рассмотрим несколько примеров использования этого инструмента на практике, чтобы понять его возможности.

Извлечение слов из текста и перестановки

Написать формулу, которая извлекает, например, третье слово из текста в ячейке - маленький подвиг. Разобрать фразу по пробелу в разные колонки с помощью Данные - Текст по столбцам (Data - Text to Columns) тоже дело не быстрое. С помощью мгновенного заполнения это делается легко и красиво. Причем, можно попутно менять извлекаемые слова местами, комбинируя их в любом порядке:

Извлечение слов из текста

Деление текста по регистру

Для выделения слов мгновенному заполнению совершенно не обязательно наличие именно пробела. Отлично сгодится и любой другой разделитель, например, запятая или точка-с-запятой после импорта CSV-файла. Но что совсем круто, так это то, что разделителя может и не быть совсем - достаточно только заглавных букв:

Деление текста по регистру

Реализовать подобное формулами очень тяжко. Если без мгновенного заполнения, то поможет только макрос.

Склейка текста

Если можно делить, то можно и клеить! Мгновенное заполнение легко соберёт для вас длинную фразу из нескольких фрагментов, перемежая их нужными пробелами, запятыми, союзами или словами:

Склейка текста

Извлечение отдельных символов

Обычно для вытаскивания отдельных символов и подстрок в Excel используются функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID) и им подобные, но мгновенное заполнение с легкостью решает и эту задачу. Классический пример - формирование ФИО:

Пример работы Мгновенного заполнения Flash Fill

Извлечение только чисел, текста или дат

Если вы когда-нибудь пытались вытащить только нужный тип данных из буквенно-цифровой каши, то должны понимать всю сложность этой простой, на первый взгляд, задачи. Мгновенное заполнение и тут справляется "на ура", но нужен лёгкий пендель в виде Ctrl+E:

Извлечение чисел

С извлечением текста тоже самое:

Извлечение текста

Даты - тоже не проблема (даже если они написаны в разных форматах):

Извлечение дат

Преобразование форматов чисел или дат

Мгновенное заполнение поможет изменить внешний вид имеющихся данных или привести их к "одному знаменателю". Например, обычную дату преобразовать "шиворот-навыворот" в Unix-формат:

Изменение формата даты

Здесь нюанс в том, что перед вводом нужно заранее поменять формат итоговых ячеек на текстовый, чтобы Excel не пытался распознать введенные вручную в качестве образца "неправильные" даты.

Аналогичным образом можно также правильно представить телефонные номера, добавив код страны и трехзначный префикс оператора (города) в скобках:

Преобразование формата телефонных номеров

Не забудьте сначала поменять формат ячеек в столбце В на текстовый - иначе Excel будет воспринимать значения начинающиеся со знака "+" как формулы.

Преобразование текста (чисел) в дату

При выгрузках из различных ERP и CRM-систем часто дата представляется 8-значным числом в формате ГГГГММДД. Конвертировать её в нормальный вид можно либо функцией ДАТАЗНАЧ (DATEVALUE), либо гораздо проще - мгновенным заполнением:

Преобразование чисел в дату

Изменение регистра

Если вам достался текст с нЕпрАвИльНЫм рЕгисТроМ, то можно просто намекнуть в соседнем столбце к какому виду вы хотите его преобразовать - и мгновенное заполнение сделает за вас всю работу:

Исправление регистра

Чуть сложнее будет, если нужно изменить регистр по разному у разных частей текста. Например, сделать заглавными только второе слово, оставив первое в нормальном виде. Здесь двух введённых в качестве образца значений окажется недостаточно и придется внести правки, которые мгновенное заполнение тут же учтёт в результатах:

Второе слово заглавными

Ограничения и нюансы

При использовании в работе мгновенного заполнения следует помнить несколько моментов:

  • Оно срабатывает только, если вводить образцы строго рядом - в предыдущем или следующем столбце справа от данных. Если отступить одну пустую колонку от исходного текста, то ничего не получится.
  • При выявлении шаблона учитываются все значения в строке - слева и справа от вводимого столбца. Мораль: лишние колонки, способные запутать алгоритм или внести помехи, лучше заранее отделить от рабочих данных пустыми столбцами или удалить.
  • Мгновенное заполнение отлично работает в умных таблицах.
  • Малейшая ошибка или опечатка при наборе ячеек-образцов может привести к тому, что мгновенное заполнение не сможет выявить паттерн и не сработает. Будьте внимательны.
  • Есть ситуации, когда шаблон определяется некорректно, поэтому всегда нужно проверять результаты, которые вы получили (хотя бы выборочно).

Ссылки по теме




20.05.2019 12:07:41
Доброго дня !!! А в Офисе 2010 это работает?
22.05.2019 10:14:58
Нет, к сожалению. Эта функция появилась в Excel начиная с 2013-й версии.
24.05.2019 21:19:46
Доброго времени суток. Николай, безмерно Вам признателен за Ваши приемы в экселе и за очередной, в частности. Вы сильно упрощаете и экономите время тем людям, которые работают с экселем. Спасибо Вам огромное!
В данном примере возник вопрос по закладке с телефонами, а именно при мгновенном заполнении телефонов , например в формате :+7(903)545-29-85 -ячейки заполняются неверно, а именно последние цифры телефона. Делал в Вашем примере. С чем может быть это связано.
Кстати и у Вас в видео -когда Вы это сделали -произошло тоже самое, просто не успели это увидеть, тк пошел следующий слайд. Спасибо за ответ.
03.06.2019 22:59:42
Надо было еще один телефон ввести в качестве образца - и все было бы ОК. Поспешил, моя вина :)
26.05.2019 11:40:23
Помимо формата 7(903)545-29-85 есть некоторые другие примеры где заполняется неверно. Наверняка есть какие-то правила исключения, где можно почитать про них?
26.05.2019 15:04:36
Это происходит только с числами, предназначенными для расчета, т.е., в ячейках, отформатированных как числа. В ячейки, отформатированные как текст можно ввести до 32 767 символов, до 1024 из которых Excel отображает на листе.

Так как пользовательские числовые форматы предназначены для работы в основном с номерами, невозможно создать пользовательский числовой формат, который содержит более 15 цифр. Например нельзя использовать следующий формат для хранения идентификатора 16 символов кредитной карты как число:
####-####-####-####

Если в ячейке в формате ### ###-### ### введите номер 1111222233334444 , Excel отображает 1111 2222-3333 4440 в ячейке. Фактический номер, который вы пытаетесь сохранить, составляет 1 111 222 233 334 444, что более чем один квадрильон. Но поскольку это число велико, Excel удаляет последнюю цифру (младший) и помещает нуль в его место.

При вводе числа в ячейки, отформатированные как текст, все символы остаются, как вы их вводите, поскольку Excel не пытается сохранить код кредитной карты как число, но сохраняет его как текст.
05.06.2019 08:54:57
и еслив примере с телефонами последние 4 цифры не делить дефисом то вроде все ок)) А в каких примерах еще???
Наверх