Содержание:
Эксперименты делают нас героями.
(Вспышка)
Хотя инструмент Мгновенное заполнение (Мгновенное заполнение) появился в Excel начиная с версии 2013 года, но этот факт почему-то остался незамеченным для многих пользователей. И совершенно напрасно. Во многих случаях это оказывается проще, легче и быстрее, чем аналогичные решения на основе формул или макросов. По моему опыту, на тренингах эта тема вызывает постоянное «вау!» аудитории – независимо от продвинутости и/или усталости слушателей.
Механизм работы этого инструмента прост: если у вас есть один или несколько столбцов с исходными данными и вы начинаете вводить их рядом друг с другом в следующем столбце, но в каком-то измененном нужном вам виде, то рано или поздно Excel намекнет, что он готов продолжить дальше вас:
Чтобы выявить логику (закономерность, закономерность) преобразования и запустить эту функцию Excel, обычно достаточно ввести первые 1-3 полученных значения вручную. Если предложенный вариант вас устраивает, то просто нажмите Enter – и остальная часть списка будет заполнена мгновенно.
Если вы уже ввели первые 2-3 значения, а продолжение все равно не появляется, то можно принудительно выполнить процесс с помощью сочетания клавиш Ctrl+E или используйте кнопку Мгновенное заполнение (Мгновенное заполнение) таб Данные (Дата):
Давайте рассмотрим несколько примеров того, как этот инструмент можно использовать на практике, чтобы понять его возможности.
Извлечение слов из текста и перестановки
Написать формулу, которая извлекает, например, третье слово из текста в ячейке, — непростая задача. Разберите фразу по пробелам в разные столбцы, используя Данные – текст по столбцам (Данные — текст в столбцы) Это тоже не быстро. При мгновенной заливке это делается легко и красиво. Более того, вы можете одновременно менять местами извлеченные слова, объединяя их в любом порядке:
Разделение текста по регистрам
Для выделения слов для мгновенного заполнения совершенно не обязательно иметь пробел. Любой другой разделитель, например запятая или точка с запятой, будет работать нормально после импорта файла CSV. Но что действительно круто, так это то, что разделителя может вообще не быть — достаточно только заглавных букв:
Реализовать такие формулы очень сложно. Если без мгновенного заполнения, то поможет только макрос.
Наклеивание текста
Если умеешь делить, то можно и склеить! Instant Fill легко соберет для вас длинную фразу из нескольких фрагментов, перемежая их необходимыми пробелами, запятыми, союзами или словами:
Извлечение отдельных символов
Обычно для извлечения отдельных символов и подстрок в Excel используются функции ЛЕВСИМВ (ОСТАВИЛ), ПРАВО (ВЕРНО), ПСТР (середина) и тому подобное, но мгновенное заполнение с легкостью решает эту проблему. Классический пример – образование полного имени:
Извлекать только числа, текст или даты
Если вы когда-нибудь пытались вытащить из буквенно-цифровой каши только нужный тип данных, то вы должны понимать всю сложность этой, казалось бы, простой задачи. Мгновенное заполнение и здесь справляется на ура, но нужен легкий пендель в форме Ctrl+E:
То же самое касается извлечения текста.
Даты тоже не проблема (даже если они записаны в разных форматах):
Преобразование форматов чисел или дат
Flash Fill может помочь изменить внешний вид существующих данных или привести их к одному знаменателю. Например, чтобы преобразовать обычную дату «шиворот-навыворот» в формат Unix:
Здесь нюанс в том, что перед вводом нужно заранее изменить формат полученных ячеек на текстовый, чтобы Excel не пытался распознать «неправильные» даты, введенные вручную в качестве образца.
Точно так же вы также можете правильно представить номера телефонов, добавив в скобках код страны и трехзначный префикс оператора (города):
Не забудьте предварительно изменить формат ячеек в столбце B на текстовый — иначе Excel будет трактовать значения uXNUMXbuXNUMXb, начинающиеся со знака «+», как формулы.
Преобразование текста (цифр) в дату
При загрузке из различных ERP и CRM систем дата часто представляется в виде 8-значного числа в формате ГГГГММДД. Преобразовать его в нормальную форму можно либо с помощью функции ИДЕНТИФИКАТОР ДАННЫХ (ДАТАЗНАЧЕНИЕ), или гораздо проще – мгновенное заполнение:
Изменить регистр
Если вам попался текст с неправильным регистром, то вы можете просто намекнуть в соседнем столбце, в какой вид вы хотите его преобразовать — и мгновенное заполнение сделает всю работу за вас:
Немного сложнее будет, если вам нужно будет по-разному менять регистр для разных частей текста. Например, напишите с заглавной буквы только второе слово, оставив первое в его обычной форме. Здесь двух значений, введенных как образец, будет недостаточно и придется внести изменения, которые мгновенное заполнение сразу учтет в результатах:
Ограничения и нюансы
При использовании Flash Fill в работе следует учитывать несколько вещей:
- Это работает, только если вводите образцы строго рядом – в предыдущем или следующем столбце справа от данных. Если отступить от исходного текста одну пустую колонку, то ничего не получится.
- Когда закономерность найдена учитываются все значения подряд — слева и справа от столбца ввода. Мораль: лишние столбцы, которые могут запутать алгоритм или внести шум, следует заранее отделить от рабочих данных пустыми столбцами или удалить.
- Мгновенное заполнение отлично работает в умных таблицах.
- малейшее ошибка или опечатка при вводе ячеек образца может привести к тому, что флэш-заполнение не сможет выявить шаблон и не будет работать. Будь осторожен.
- Бывают ситуации, когда шаблон определен неправильно, поэтому всегда нужно проверять результатычто вы получили (по крайней мере выборочно).
- Как извлечь последнее слово из текста в ячейке
- Нечеткий текстовый поиск (Пушкин = Пушкин) с нечетким поиском в Excel
- Три способа вставки текста в Excel