Мгновенное заполнение в Excel

Обновлено: 21.11.2024

Вывод. В этом посте мы рассмотрим 3 способа копирования значений в пустые ячейки столбца. Эти методы включают использование формулы, Power Query и макроса VBA.

Уровень квалификации: средний

Посмотреть обучающее видео

Загрузить файлы Excel

Вы можете скачать файлы BEGIN и FINAL, если хотите попрактиковаться.

Методы заполнения BEGIN.xlsx (323,3 КБ)

Методы заполнения FINAL.xlsm (465,5 КБ)

Заполнение пустых ячеек

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

Но есть ли простой способ воспроизвести этот процесс сотни раз для отчетов с большими объемами данных?

В этом посте мы рассмотрим три способа автоматизации этого процесса с помощью простой формулы, Power Query и макроса VBA.

1. Заполнение с помощью формулы

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

Шаг 1. Выберите пустые ячейки

Чтобы выбрать пустые ячейки в столбце и заполнить их формулой, мы начинаем с выбора всех ячеек (включая заполненные ячейки). Это можно сделать разными способами, в том числе удерживать нажатой клавишу Shift при переходе к нижней части столбца или, если данные находятся в таблице Excel, с помощью сочетания клавиш Ctrl + Пробел .

Выделив все эти ячейки, мы можем сократить выделение, включив в него только пустые ячейки. Это делается с помощью окна «Перейти к специальному» в меню «Найти и выбрать» на вкладке «Главная» ленты.

Нажмите, чтобы увеличить

В качестве альтернативы можно открыть окно «Перейти» с помощью F5 или Ctrl + G . Затем нажмите специальную кнопку, чтобы открыть окно «Перейти к специальному».

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

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

Шаг 2. Напишите формулу

Здесь вы можете начать вводить формулу, что очень просто. Введите знак равенства ( = ), а затем укажите ссылку на ячейку выше (в нашем примере это B2). Это все, что вам нужно для формулы!

Шаг 3. Ctrl+Введите формулу

После написания формулы не нажимайте Enter. Вместо этого используйте Ctrl + Enter, чтобы заполнить все выделенные ячейки одной и той же формулой. Удерживая нажатой клавишу Ctrl, нажмите Enter.

Поскольку ссылка на формулу является относительной (B2), а не абсолютной ($B$2), каждая ячейка будет просто копировать значение ячейки непосредственно над ней.

Шаг 4. Скопируйте и вставьте значения

Сейчас рекомендуется скопировать весь столбец данных, а затем вставить его с помощью параметра «Вставить значения», чтобы данные были жестко запрограммированы. Тогда, если вы сделаете какую-либо сортировку, данные не изменятся. Вы можете найти параметр «Вставить значения» на вкладке «Главная» в раскрывающемся меню «Вставить»:

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

2. Заполнение с помощью Power Query

Для этого варианта ваши данные должны быть в формате таблицы Excel. В любом месте таблицы вы можете выбрать вкладку Данные или Power Query, а затем выбрать Из таблицы/диапазона. Вы также можете создать запрос, который подключается к другому источнику данных, например к базе данных или Интернету.

Откроется редактор Power Query. В Power Query пустые ячейки помечаются как нулевые в каждой ячейке.

Чтобы заполнить, просто щелкните правой кнопкой мыши заголовок столбца и выберите "Заполнить", а затем "Вниз".

Power Query заполнит каждый раздел пустых ячеек в столбце значением из ячейки над ним.

При нажатии кнопки "Закрыть и загрузить" в книгу будет добавлен новый лист с этими изменениями.

Этот процесс заполнения можно выполнить для нескольких столбцов одновременно, сначала выбрав несколько столбцов с помощью Ctrl или Shift, а затем выполнив операцию «Заполнить > Вниз».

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

Бесплатный веб-семинар по Power Query, VBA и многому другому

Прямо сейчас у меня проходит бесплатный веб-семинар, посвященный введению в Power Query и другим инструментам Excel, таким как Power Pivot, Power BI, макросы и VBA, сводные таблицы и т. д.

3. Заполнение с помощью макроса

Чтобы заполнить с помощью макроса, начните с открытия редактора VB. Вы можете сделать это, перейдя на вкладку «Разработчик» и нажав кнопку Visual Basic или используя сочетание клавиш Alt + F11. Вставьте новый модуль кода, а затем напишите свой макрос.

Этот макрос перебирает все ячейки в выбранном диапазоне и выполняет метод FillDown, если ячейка пуста. Метод FillDown копирует значение из ячейки выше.

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

Чтобы узнать, как создать личную книгу макросов и настраиваемые кнопки ленты, ознакомьтесь с этим руководством: Как создать личную книгу макросов (серия видео).

За и против

Итак, какой из этих трех методов вам подходит? Подумайте об этом:

Метод формулы прост и удобен в применении. Это можно сделать без знания Power Query или VBA. Однако это не так быстро, как решение с одним нажатием кнопки.
Power Query требует исходных данных, которые выводятся на отдельный лист, поэтому это больше процесс, чем просто заполнение пустых ячеек. Если вы уже регулярно очищаете данные для других целей, добавление этого в существующий процесс может иметь больше смысла.
После того как первоначальный макрос настроен, VBA становится быстрым и простым в использовании решением — просто нажмите кнопку. Однако у этого метода есть большой недостаток. Вы не сможете отменить свое действие после нажатия этой кнопки, поэтому вам необходимо заранее сохранить резервную копию, если вас это беспокоит.

Заключение

Существует три разных метода для очень распространенной задачи Excel. Я что-то пропустил? Пожалуйста, оставьте комментарий ниже, если у вас есть другой способ сделать это или у вас есть какие-либо вопросы. Спасибо! 🙂

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

Примечание. Быстрое заполнение доступно только в Excel 2013 и более поздних версиях.

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

Введите полное имя в ячейку C2 и нажмите клавишу ВВОД.

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

Чтобы принять предварительный просмотр, нажмите ENTER.

Если Flash Fill не создает предварительный просмотр, возможно, он не включен.Вы можете перейти в «Данные» > «Быстрая заливка», чтобы запустить его вручную, или нажать Ctrl+E. Чтобы включить мгновенное заполнение, выберите Инструменты > Параметры > Дополнительно > Параметры редактирования > установите флажок Автоматически мгновенное заполнение.

Введите полное имя в ячейку C2 и нажмите клавишу ВВОД.

Выберите Данные > Быстрое заполнение или нажмите Ctrl+E.

Excel обнаружит шаблон, указанный вами в C2, и заполнит ячейки ниже.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

В этом руководстве приведены краткие инструкции по автозаполнению практически любых данных в Excel: одинаковые значения, даты, линейные ряды и ряды роста, дни, месяцы и т. д.

В каждой теме также есть ссылка на полное руководство с более подробным описанием. Меню, поясняющее наши отдельные руководства по автозаполнению, см. в Информационном центре автозаполнения Excel.

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

Как использовать дескриптор заполнения для автозаполнения

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

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

Автозаполнение ячеек одинаковым значением

Для соседних ячеек введите начальное значение в первой ячейке, выберите эти ячейки и ячейки для автозаполнения и нажмите Ctrl + D.

Еще один способ — после ввода первой записи. наведите указатель мыши на эту ячейку, пока не отобразится маркер заполнения, и перетащите маркер заполнения, чтобы выбрать и заполнить другие ячейки.

Автозаполнение несмежных ячеек или диапазонов ячеек? Сначала выделите ячейки, удерживая нажатой клавишу Ctrl. Введите значение для репликации и нажмите Ctrl + Enter. Дополнительные сведения см. в разделе Как автоматически заполнять ячейки одинаковыми значениями.

Даты автозаполнения в Excel

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

Самый простой способ автозаполнения непоследовательных дат — ввести первые две даты, выбрать обе ячейки и перетащить маркер заполнения для автозаполнения.

В качестве альтернативы введите первую дату, нажмите и удерживайте правую кнопку мыши и перетащите маркер заполнения, чтобы выбрать ячейки для заполнения. Затем нажмите «Серии» в появившемся меню, введите нужное значение шага и нажмите «ОК».

Мы объясняем этот и другие методы в нашем подробном руководстве Как автоматически заполнять даты в Excel.

Автозаполнение линейного ряда

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

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

Автозаполнение серии роста

В ряду роста следующее число всегда находится путем умножения на константу. Чтобы автоматически заполнить ряд роста, введите первые два числа, выберите эти ячейки и перетащите маркер заполнения, удерживая нажатой правую кнопку мыши, а затем нажмите «Тенденция роста» в появившемся меню.

Если вам не нравится использовать дескриптор заполнения, введите первое число, выберите его и ячейки для автозаполнения, откройте диалоговое окно «Серия» («Заливка») в разделе «Редактирование» на ленте, нажмите «Рост» и введите ваша «Стоимость шага». Примеры см. в разделе Автозаполнение ряда роста в Excel.

Автозаполнение дней и рабочих дней

Excel может автоматически заполнять дни недели! Для последовательных дней введите первый день и перетащите маркер заполнения, чтобы выбрать ячейки для автозаполнения. Для непоследовательных дней введите первые два дня и перетащите с помощью маркера заполнения.

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

Автозаполнение месяцев и лет

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

Для непоследовательных месяцев или лет введите первые два значения, выберите обе ячейки и перетащите с помощью маркера заполнения. Дополнительные примеры см. в разделе Как автоматически заполнять месяцы и годы в Excel

Время автозаполнения

Чтобы автоматически заполнить значения с разницей в один час, что является значением по умолчанию, введите первое время (или дату/время) и перетащите маркер заполнения, чтобы выбрать и автоматически заполнить дополнительные ячейки.

Чтобы автоматически заполнить время или даты и время, которые не являются последовательными, введите первые два значения, выберите обе ячейки, а затем перетащите маркер заполнения. Примеры см. в разделе Автозаполнение времени в Excel.

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

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

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

1. Автозаполнение каждой второй ячейки в Excel

Любой, кто когда-либо использовал Excel, знает, как использовать функцию автозаполнения для автозаполнения ячейки Excel на основе другой.

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

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

Но что, если вы не хотите применять формулу автозаполнения Excel к каждой отдельной ячейке под ней? Например, что, если вы хотите, чтобы каждая вторая ячейка заполнялась автоматически и объединяла имя и фамилию, но вы хотите оставить строки адреса нетронутыми?

Как автоматически заполнить каждую вторую ячейку в Excel

Этого можно добиться, немного изменив процедуру автозаполнения. Вместо того, чтобы щелкнуть первую ячейку, а затем перетащить ее вниз из правого нижнего угла, вместо этого вы выделите первые две ячейки. Затем поместите мышь в правый нижний угол двух ячеек, пока курсор не примет форму «+».

Теперь удерживайте и перетащите его вниз, как обычно.

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

Как обрабатываются другие ячейки

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

Например, если во второй ячейке есть "1", Excel автоматически заполнит каждую вторую ячейку, увеличив ее на 1.

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

2. Автозаполнение до конца данных в Microsoft Excel

При работе с таблицами Excel в корпоративной среде люди часто сталкиваются с большими таблицами.

Достаточно просто перетащить курсор мыши сверху вниз в наборе из 100–200 строк, чтобы автоматически заполнить этот столбец. Но что, если в электронной таблице на самом деле 10 000 или 20 000 строк? Перетаскивание курсора мыши вниз по 20 000 строк заняло бы много времени.

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

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

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

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

3. Заполните пробелы

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

Вы не видите какой-либо предсказуемой закономерности, поэтому вы не можете использовать описанный выше прием автозаполнения "каждый второй x". Кроме того, такой подход сотрет все существующие данные в столбце. Что вы можете сделать?

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

На приведенном выше листе ваш начальник хочет, чтобы вы заполнили любую пустую ячейку строкой "Н/Д". На листе всего с несколькими строками это будет простой ручной процесс. Но в таблице с тысячами строк это заняло бы у вас целый день.

Поэтому не делайте этого вручную. Просто выберите все данные в столбце. Затем перейдите в главное меню, выберите значок «Найти и выбрать», выберите «Перейти к специальному».

В следующем окне выберите Пробелы.

В следующем окне вы можете ввести формулу в первую пустую ячейку. В этом случае вы просто наберете N/A и затем нажмете Ctrl + Enter, чтобы то же самое применялось к каждой найденной пустой ячейке.

При желании вместо "Н/Д" вы можете ввести формулу в первую пустую ячейку (или щелкнуть предыдущее значение, чтобы использовать формулу из ячейки чуть выше пустой).

Когда вы нажмете Ctrl + Enter, эта же формула будет применена ко всем остальным пустым ячейкам. С помощью этой функции можно легко и быстро навести порядок в электронной таблице.

4. Заполнить макросом предыдущего значения

Этот последний трюк на самом деле требует нескольких шагов. Вам нужно щелкнуть кучу пунктов меню, и сокращение кликов — это то, что нужно для того, чтобы стать более эффективным, верно?

Итак, давайте продвинемся в этом последнем трюке еще на один шаг. Давайте автоматизируем это с помощью макроса. Следующий макрос в основном выполняет поиск по столбцу и проверяет наличие пустой ячейки. Если пусто, будет скопировано значение или формула из ячейки над ним.

Чтобы создать макрос, щелкните элемент меню "Разработчик" и щелкните значок "Макросы".

Назовите макрос и нажмите кнопку "Создать макрос". Это откроет окно редактора кода. Вставьте следующий код в новую функцию.

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

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

После того, как вы ответите на запросы о начальном столбце и строке, он заполнит все пробелы в этом столбце, не затрагивая существующие данные.

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

5. Макрос итерационных вычислений

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

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

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

В этом скрипте пользователь указывает как первый, так и последний номер строки. Поскольку в остальной части столбца нет данных, скрипт понятия не имеет, с чего начать. Как только скрипту будет предоставлен диапазон, он выполнит интерактивный расчет, используя предыдущее значение, и заполнит весь столбец новыми данными.

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

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

Автозаполнение столбцов 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

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

Как использовать мгновенное заполнение в Excel

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

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

Если вас не устраивают результаты быстрой заливки, вы можете отменить их, нажав Ctrl + Z или через меню параметров быстрой заливки.
Если Flash Fill не запускается автоматически, попробуйте эти простые методы устранения неполадок.

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

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

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

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

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

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

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

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

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

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

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

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

- формулы для извлечения текста определенной длины или получения подстроки до или после заданного символа. - формулы для извлечения чисел из буквенно-цифровых строк. - формулы для извлечения имени, фамилии и отчества.

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

- 25 инструментов для выполнения различных манипуляций с текстом, включая разделение ячейки любым символом, таким как запятая, пробел, разрыв строки; извлечение текста и чисел. - быстрый и простой способ разделения имен в Excel.

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

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

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

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

- формулы для объединения текстовых строк, ячеек и столбцов.

- быстрый способ объединить две таблицы по общим столбцам. - Объединение похожих строк в одну по ключевым столбцам.

Очистить данные

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

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

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

- обрезать все начальные, конечные и промежуточные пробелы, но один пробел между словами.

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

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

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

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

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

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

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

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

Для этого либо используйте функцию ЗАМЕНА, либо введите нужное значение в первую ячейку и дайте Flash Fill автоматически заполнить оставшиеся ячейки:

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

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

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

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

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

Ограничения и предупреждения Excel Flash Fill

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

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

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

2. Может не определить шаблон

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

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

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

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

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

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

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

Как включить и отключить быструю заливку

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вас также может заинтересовать

11 комментариев к статье «Как использовать мгновенное заполнение в Excel 365 — 2013»

Я отключил флэш-заполнение, но оно по-прежнему пытается выполнить флэш-заполнение. У меня никогда не было работы с flashfill, как описано, и она дает очень странные заливки для меня (все виды слов с ошибками, которые не имеют смысла). Как навсегда убить эту функцию. Как я уже сказал, он отключен. Я не могу остановить его, и я даже не могу использовать свою электронную таблицу, так как каждый раз, когда я пытаюсь что-то напечатать, она заполняет мусор, а когда я отменяю и пытаюсь снова, она просто заполняет снова.

Привет!
Отключите Flash Fill, как описано в статье выше. Затем перезапустите Excel.

У меня нет параметра мгновенной заливки в меню редактирования расширенных параметров,

Добрый день. Спасибо за урок. У меня есть одна проблема. Я хочу извлечь год из заданной даты, скажем, я хочу извлечь 2020 год из даты 02.03.2020. Как мне это сделать? Я продолжаю получать неправильные ответы.

Привет!
Я рекомендую прочитать это руководство: Функция Excel YEAR - преобразование даты в год.
Это должно решить вашу задачу.

Как отключить флэш-заполнение и копирование и вставку в книге?

Я просто хочу узнать о формуле заполнения Flash. Мои требования к листу Excel указаны ниже;

пусть и пример;
Ali-050512-25-07-20220 я хочу извлечь только номер, а не имя и дату, и указанный номер имеет другую длину.

пожалуйста, предоставьте мне решение

Здравствуйте!
Не видя ваших данных, трудно дать вам совет.
Флэш-заполнение ищет общий шаблон в ваших данных. Для этого он использует несколько ячеек, расположенных последовательно в столбик. Из вашего примера невозможно понять, где дата, где число. Но если число может иметь разное количество цифр, то закономерности нет. Приведите примеры исходных данных и ожидаемого результата.
Это поможет мне лучше понять проблему и найти решение для вас.

Привет
Вы можете использовать встроенные инструменты Excel
Комбинацию этих инструментов
Текст в столбцы
И функцию
Если
Длина

Хотя в некоторых случаях я вижу преимущества мгновенного заполнения; ЭТО НИКОГДА НЕ ДОЛЖНО БЫТЬ АВТОМАТИЧЕСКИ ВКЛЮЧЕНА ДЛЯ АВТОМАТИЧЕСКОГО ЗАПОЛНЕНИЯ FLASH! У меня есть документ, поддерживаемый мной и тремя другими людьми, и эта дурацкая новая функция с автоматическим включением начала автоматически заполнять инициалы 1 человека над всем! Если у вас есть новая функция, просто поместите ее в однократное уведомление и не думайте, что все делают глупые и/или непродуктивные и/или рискованные действия, которые могут привести к тому, что кто-то введет дезинформацию, не обращая внимания на скрытый факт. что вы добавили функцию, но НИКОМУ не сказали!

Это перестало работать:
2 9 /5day
39 116 /3day
18 49 /3day
8 24
7 22
9 28
63 189
Мне нужно, чтобы во втором столбце было сообщение "N/3day".

Читайте также: