Как использовать Flash Fill в Excel с примерами

В учебном пособии объясняются основы функции мгновенного заполнения и приводятся примеры использования мгновенного заполнения в Excel.

Мгновенное заполнение — одна из самых удивительных функций Excel. Он берет утомительную задачу, на выполнение которой вручную уходят часы, и мгновенно выполняет ее автоматически (отсюда и название). И это происходит быстро и просто, вам не нужно ничего делать, а только показать пример того, что вы хотите.

Что такое мгновенное заполнение в Excel?

Excel Flash Fill — это специальный инструмент, который анализирует вводимую вами информацию и автоматически заполняет данные, когда идентифицирует шаблон.

Функция мгновенного заполнения была представлена в Excel 2013 и доступна во всех более поздних версиях Excel 2016, Excel 2019, Excel 2021 и Excel для Microsoft 365.

Начато в декабре 2009 г. как попытка Сумита Гулвани, старшего научного сотрудника Microsoft, помочь деловой женщине, которую он случайно встретил в аэропорту, с ее задачей слияния, несколько лет спустя она превратилась в мощную способность автоматизировать многие рутинные операции Excel.

Flash Fill легко справляется с десятками различных задач, которые в противном случае потребовали бы сложных формул или даже кода VBA, таких как разделение и объединение текстовых строк, очистка данных и исправление несоответствий, форматирование текста и чисел, преобразование дат в нужный формат и многое другое.

Каждый раз Flash Fill объединяет миллионы небольших программ, которые могут выполнить задачу, затем сортирует эти фрагменты кода с помощью методов машинного обучения и находит тот, который лучше всего подходит для этой работы. Все это делается за миллисекунды в фоновом режиме, а результат пользователь видит практически сразу!

Где Flash Fill в Excel?

В Excel 2013 и более поздних версиях инструмент «Быстрая заливка» находится на Вкладка «Данные»в Инструменты данных группа:

Ярлык быстрого заполнения Excel

Те из вас, кто предпочитает большую часть времени работать с клавиатуры, могут запустить Flash Fill с помощью этой комбинации клавиш: Ctrl + E.

Как использовать Flash Fill в Excel

Обычно Flash Fill запускается автоматически, и вам нужно только указать шаблон. Вот как:

Вставьте новый столбец рядом с столбцом с вашими исходными данными.
В первой ячейке вновь добавленного столбца введите нужное значение.
Начните вводить в следующей ячейке, и если Excel обнаружит шаблон, он покажет предварительный просмотр данных, которые будут автоматически заполнены в следующих ячейках.
Нажмите клавишу Enter, чтобы принять предварительный просмотр. Сделанный!

Как быстро заполнить Excel одним нажатием кнопки или ярлыком

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

Заполните первую ячейку и нажмите Enter.
Нажмите кнопку «Быстрая заливка» на Данные вкладку или нажмите сочетание клавиш Ctrl + E.

Параметры мгновенного заполнения Excel

При использовании мгновенного заполнения в Excel для автоматического ввода данных рядом с автоматически заполняемыми ячейками появляется кнопка «Параметры мгновенного заполнения». При нажатии на эту кнопку открывается меню, в котором можно сделать следующее:

Отмените результаты быстрой заливки.
Выберите пустые ячейки, которые Excel не смог заполнить.
Выберите измененные ячейки, например, чтобы отформатировать их все сразу.

Примеры мгновенного заполнения Excel

Как уже упоминалось, Flash Fill — очень универсальный инструмент. Приведенные ниже примеры демонстрируют некоторые из его возможностей, но это еще не все!

Извлечь текст из ячейки (разделить столбцы)

До появления Flash Fill для разделения содержимого одной ячейки на несколько ячеек требовалось использование функции «Текст в столбцы» или текстовых функций Excel. С помощью Flash Fill вы можете мгновенно получить результаты без сложных текстовых манипуляций.

Предположим, у вас есть столбец адресов, и вы хотите извлечь почтовые индексы в отдельный столбец. Укажите свою цель, введя почтовый индекс в первой ячейке. Как только Excel понимает, что вы пытаетесь сделать, он заполняет все строки ниже примера извлеченными почтовыми индексами. Вам нужно только нажать Enter, чтобы принять их все.

Формулы для разделения ячеек и извлечения текста:

Инструменты извлечения и разделения:

Text Toolkit for Excel — 25 инструментов для выполнения различных манипуляций с текстом, включая разделение ячейки любым символом, таким как запятая, пробел, разрыв строки; извлечение текста и чисел.
Инструмент «Разделить имена» — быстрый и простой способ разделить имена в Excel.

Объединить данные из нескольких ячеек (объединить столбцы)

Если у вас есть противоположная задача, не проблема, Flash Fill также может объединять ячейки. Более того, он может разделять объединенные значения пробелом, запятой, точкой с запятой или любым другим символом — вам просто нужно указать Excel нужный знак препинания в первой ячейке:

Этот метод особенно удобен для объединения различных частей имени в одну ячейку, как показано в разделе «Как объединить имя и фамилию с помощью мгновенного заполнения».

Формулы для соединения значений ячеек:

Инструменты слияния:

Чистые данные

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

Формулы для очистки данных:

Инструменты очистки данных:

Text Toolkit for Excel — обрезайте все начальные, конечные и промежуточные пробелы, но один пробел между словами.

Форматирование текста, чисел и дат

Довольно часто данные в ваших электронных таблицах форматируются одним способом, а вы хотите — другим. Просто начните вводить значения именно так, как вы хотите, а Flash Fill сделает все остальное.

Возможно, у вас есть столбец с именами и фамилиями в нижнем регистре. Вы хотите, чтобы фамилия и имя были в правильном регистре, разделенные запятой. Кусочек пирога для Flash Fill 🙂

Возможно, вы работаете со столбцом чисел, которые необходимо отформатировать как номера телефонов. Эту задачу можно выполнить, используя предопределенный специальный формат или создав собственный числовой формат. Или вы можете сделать это простым способом с Flash Fill:

Чтобы переформатировать даты по своему вкусу, вы можете применить соответствующий формат даты или ввести правильно отформатированную дату в первую ячейку. К сожалению, никаких предложений не появилось… Что, если мы нажмем ярлык Flash Fill (Ctrl + E) или щелкнем его кнопку на ленте? Ага, красиво работает!

Заменить часть содержимого ячейки

Замена части строки другим текстом — очень распространенная операция в Excel, которую также можно автоматизировать с помощью Flash Fill.

Допустим, у вас есть столбец с номерами социального страхования, и вы хотите подвергнуть цензуре эту конфиденциальную информацию, заменив последние 4 цифры на XXXX.

Чтобы это сделать, либо используйте функцию REPLACE, либо введите нужное значение в первую ячейку и позвольте Flash Fill автоматически заполнить оставшиеся ячейки:

Расширенные комбинации

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

В качестве примера давайте объединим разные фрагменты информации из 3 столбцов и добавим к результату несколько пользовательских символов.

Предположим, у вас есть имена в столбце A, фамилии в столбце B и доменные имена в столбце C. На основе этой информации вы хотите сгенерировать адреса электронной почты в следующем формате: initial.surname@domain.com.

Для опытных пользователей Excel нет проблем с извлечением инициала с помощью функции LEFT, преобразованием всех символов в нижний регистр с помощью функции LOWER и конкатенацией всех частей с помощью оператора конкатенации:

=НИЖНИЙ(ЛЕВЫЙ(B2,1))&”.”&НИЖНИЙ(A2)&”@”&НИЖНИЙ(C2)&”.com”

Но может ли Excel Flash Fill автоматически создавать для нас эти адреса электронной почты? Конечно!

Ограничения и предостережения Excel Flash Fill

Flash Fill — отличный инструмент, но у него есть несколько ограничений, о которых вам следует знать, прежде чем вы начнете использовать эту функцию в своих реальных наборах данных.

1. Результаты Flash Fill не обновляются автоматически.

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

2. Может не определить закономерность

В некоторых ситуациях, особенно если ваши исходные данные организованы или отформатированы по-другому, Flash Fill может спотыкаться и давать неверные результаты.

Например, если вы используете мгновенное заполнение для извлечения отчества из списка, где некоторые записи содержат только имя и фамилию, результаты для этих ячеек будут неверными. Таким образом, разумно всегда проверять выходные данные мгновенного заполнения.

3. Игнорирует ячейки с непечатаемыми символами

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

Итак, если какая-либо из результирующих ячеек пуста, очистите эти ячейки (Дом вкладка >Форматы группа > чистый > Очистить все) и снова запустите Flash Fill.

4. Может преобразовывать числа в строки

При использовании Flash Fill для переформатирования чисел имейте в виду, что оно может преобразовывать ваши числа в буквенно-цифровые строки. Если вы предпочитаете сохранять числа, используйте возможности формата Excel, который изменяет только визуальное представление, но не базовые значения.

Как включить и выключить Flash Fill

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

В вашем Excel перейдите к Файл> Опции.
На левой панели нажмите Передовой.
Под Параметры редактированияснимите флажок «Автоматическое мгновенное заполнение».
Нажмите OK, чтобы сохранить изменения.

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

Excel Flash Fill не работает

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

1. Приведите больше примеров

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

2. Запустите его

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

3. Убедитесь, что функция Flash Fill включена.

Если он не запускается ни автоматически, ни вручную, проверьте, включена ли функция мгновенного заполнения. включенный в вашем Экселе.

4. Ошибка мгновенного заполнения сохраняется

Если ни одно из приведенных выше предложений не сработало, а Excel Flash Fill по-прежнему выдает ошибку, вы ничего не можете сделать, кроме как ввести данные вручную или с помощью формул.

Вот как вы используете Flash Fill в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!