Мгновенное заполнение в Excel. Плюсы и минусы

Начиная с версии Excel 2013 на вкладке "Данные" в группе "Работа с данными" появилась новая функция - "Мгновенное заполнение" (Flash Fill).

Это команда для быстрого ввода значений. Да не простого ввода, а интеллектуального, основанного на обработке определенного объема информации. Функция умеет заполнять столбец данными, полученными на основе анализа соседних столбцов и примеров заполнения, указанных пользователем. Общая схема следующая:

Примеры данных нужно вводить для того, чтобы Excel смог определить шаблон, по которому вы хотите заполнить столбец. Чем больше примеров и чем строже шаблон - тем больше вероятность того, что "Мгновенное заполнение" сработает корректно. Рассмотрим несколько типичных кейсов, в которых данная команда может оказаться отличным решением.

Работа со списками Ф.И.О.

Когда Ф.И.О. записаны целиком в одной ячейке, бывает необходимо вытащить их оттуда и поместить в отдельные столбцы. Или наоборот, собрать в одну колонку из нескольких исходных. Или же собрать, но оставить фамилии целиком, а для имен и отчеств - лишь первую букву. Конечно, подобные задачи решаются и без мгновенного заполнения (иначе, как бы мы жили до Excel 2013?). Примеры решений можно увидеть здесь, здесь и здесь. Однако, мгновенное заполнение позволяет выполнить задачу еще более изящно. В данном случае команда реагирует на шаблоны, которые задаются в примерах (например, "извлечь слово слева от первого пробела").

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

К сожалению, в отличие от Word в Excel нет кнопки, которая позволила бы изменить регистр выделенного текста в один клик. Если вам понадобилось изменить регистр в целом столбце, то новая функция вполне может помочь. Делаем все как и в предыдущем кейсе - записываем пример текста в первую строку, а затем применяем команду. В результате получаем столбец с текстом в нужном регистре. Шаблон, который распознала программа - "регистр всех символов - верхний".

Составление простых шаблонных фраз

Если нужно составить на основе числовых данных множество шаблонных фраз (например, "... числа продали товара на сумму ... рублей", где вместо отточий нужно подставлять различные даты и суммы), то в первую очередь смотрят в сторону конкатенации (или попросту "сцепки"). А можно воспользоваться мгновенным заполнением. Быстрее и эффектнее.

Извлечение названий файлов из списка или URL

Если к Вам попала база, содержащая полные адреса файлов в интернете, на сервере или на локальном диске, то возможно потребуется извлечь их имена, отбросив все лишнее. Если адреса внесены без ошибок и однотипны, то новая функция вполне справится с этой задачей. В данном случае Excel определил алгоритм заполнения как "скопировать все, что следует за последним слэшем (/), кроме расширения, отделенного точкой".

Ключевые недостатки "Мгновенного заполнения"

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

1. После ее применения данные вставляются как значения. Это влечет за собой сразу две проблемы. Во-первых, данные будут необновляемыми. То есть при внесении изменений в исходные столбцы придется повторить всю операцию с самого начала. Во-вторых, отсутствие формул не позволит найти причину ошибки, если таковая обнаружится в полученных данных (нельзя будет поэтапно вычислить формулу, разобрать ее на кусочки и найти место, где возникла проблема).
2. Вероятность некорректного заполнения данных после применения функции достаточна высока. Она прямо пропорциональна сложности "шаблона", который пытается сформировать Excel на основе введенных в первые ячейки примеров. Чем сложнее шаблон (а может быть, его и вовсе нет), тем больше вероятность ошибки. Причем программа никак не оповещает нас о том, насколько она уверена в своей правоте и смогла ли определить алгоритм. Ошибку в данных Excel допустит, не ставя нас в известность. Поэтому старайтесь задавать максимально понятные шаблоны и максимально четко структурировать исходную информацию. Например, извлекая имена, Excel может ошибиться, если в списке в каком то Ф.И.О. не окажется отчества.

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

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot