В 2013-й версии Excel появилась новая функциональная возможность – «Мгновенное заполнение» (Flash Fill в англоязычной версии Excel). С помощью данного механизма можно быстро извлекать и соединять данные на страницах Excel без применения формул.
Смотрите также видеоверсию статьи «Мгновенное заполнение в Excel (Flash Fill)».
Мгновенное заполнение использует распознавание шаблонов для извлечения данных из смежных столбцов. Принцип работы Мгновенного заполнения очень схож с таковым в Автозаполнении, т.е. пользователь указывает несколько примеров преобразования данных и Excel, распознав алгоритм преобразования, сможет автоматизировать работу пользователя.
Для работы Мгновенного заполнения необходимо ввести несколько, а иногда просто даже одно, значений, прилегающие к столбцу с данными и выполнить команду: «Вкладка Данные/Работа с данными/Мгновенное заполнение» (или горячее сочетание Ctrl+E). Если заполнение не сработает, можно ввести еще несколько примеров и попробовать выполнить команду еще раз.
Мгновенное заполнение для извлечения данных
Разбиение строки на подстроки
Мы уже рассматривали способы того, как разбить ячейки в Excel, использование мгновенного заполнения можно считать небольшим дополнением к двум рассмотренным ранее способам.
Как и ранее, в качестве примера извлекаемых данных, будем рассматривать ФИО людей.
Можно извлечь отдельно фамилию или имя, а можно полностью разбить данные в ячейке на несколько, достаточно просто ввести рядом с полным ФИО пользователя ту часть данных, которую необходимо извлечь, а потом выполнить команду мгновенного заполнения (Ctrl+E).
Изменение регистра текста
Мгновенное заполнение отлично подходит для однообразного изменение написания текста, например, преобразовать первые строчные буквы в прописные, все строчные в прописные, прописные в строчные и т.д.
Извлечение информации из URL
Для извлечения данных текстовая информация в ячейке не обязательно должна быть разделена пробелами, мгновенное заполнение отлично справиться с «вытягиванием» информации из URL. В следующем примере приведены адреса ссылок на ролики в YouTube, не секрет, что идентификатором конкретного ролика являются символы после знака «=». Достать данную информацию из ячейки проще простого.
Возможные проблемы при работе с мгновенным заполнением, а также недостатки.
Несмотря на то, что мгновенное заполнение просто отличная функция, она не обладает каким-либо обучаемым механизмом распознавания, поэтому данные в ячейках должны быть однообразными, например, если в пример с фамилиями и именами добавить пользователя с отчеством, то мгновенное заполнение извлечет последнее слово из ячейки. То же произойдет, если фамилия человека будет представлена двумя словами.
На рисунке выше цветом выделены ячейки, где алгоритм мгновенного заполнения сработал некорректно. Однако, поспешно расстраиваться не стоит, можно провести дополнительное «обучение» мгновенного заполнения, введя не по одному значению, а, скажем, первые пять, предусмотрев данные с отчеством, а также фамилии с тире, тогда Excel, заполнит данные правильно. На рисунке ниже зеленым выделены ячейки, которые использовались для «обучения».
В любом случае, при работе с большими объемами данных не следует терять бдительность и предусмотреть такие варианты.
Следующей потенциальной неприятностью, с которой может столкнуться пользователь, является то, что иногда мгновенное заполнение не сможет распознать алгоритм извлечения данных и дополнительное введение данных в ячейки не поможет. Здесь придется использовать другие инструменты Excel.
Нужно понимать, что мгновенное заполнение не будет изменять информацию, при ее изменении в первоначальных ячейках, если нужны динамические связи, придется воспользоваться функциями Excel.
Последний недостаток, который придется принять, при работе с мгновенным заполнением – это отсутствие возможности проведения аудита, т.е. вы просто не сможет отследить формулу, по которой было произведено извлечение данных.
Мгновенное заполнение для объединения данных
Мгновенное заполнение отлично подходит не только для извлечения данных из ячеек, но и для объединения и других операций.
Например, можно очень быстро создать единообразные адреса электронной почты из фамилии и имени сотрудника, записанных латиницей, для обучения алгоритма понадобилось заполнить всего одну ячейку.
Кстати, работу алгоритма мгновенного заполнения можно записать формулой с использованием функций: СТРОЧН, ЛЕВСИМВ.
=СТРОЧН(ЛЕВСИМВ(A1;1)&"."&B1&"@msoffice-prowork.com")
В следующем примере, данные в ячейках были сцеплены в одну, но число из столбца «День» преобразовано в дату месяца.
В этом примере для обучения алгоритма пришлось заполнить две строки.
В итоге, можно отметить, что мгновенное заполнение отличный инструмент и каждый сможет найти для себя пути его применения.
