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

Как заполнить все пустые ячейки в excel одним значением

  • автор:

Как автоматически заполнить ячейки в MS Excel с большими таблицами

В программе Excel существует много приемов для быстрого и эффективного заполнения ячеек данными. Всем известно, что лень – это двигатель прогресса. Знают об этом и разработчики.

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

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

Как в Excel заполнить ячейки одинаковыми значениями?

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

Таблица для заполнения.

Это небольшая табличка только на примере и ее можно было заполнить вручную. Но в практике иногда приходится заполнять по 30 тысяч строк. Чтобы не заполнять эту исходную таблицу вручную следует создать формулу для заполнения в Excel данными – автоматически. Для этого следует выполнить ряд последовательных действий:

  1. Перейдите на любую пустую ячейку исходной таблицы.
  2. Выберите инструмент: «Главная»-«Найти и выделить»-«Перейти» (или нажмите горячие клавиши CTRL+G).
  3. В появившемся окне щелкните на кнопку «Выделить».
  4. В появившемся окне выберите опцию «пустые ячейки» и нажмите ОК. Все незаполненные ячейки выделены.
  5. Теперь введите формулу «=A1» и нажмите комбинацию клавиш CTRL+Enter. Так выполняется заполнение пустых ячеек в Excel предыдущим значением – автоматически. Заполнение пустых ячеек.
  6. Выделите колонки A:B и скопируйте их содержимое.
  7. Выберите инструмент: «Главная»-«Вставить»-«Специальная вставка» (или нажмите CTRL+ALT+V).
  8. В появившемся окне выберите опцию «значения» и нажмите Ок. Теперь исходная таблица заполнена не просто формулами, а естественными значениями ячеек.

Результат авто-заполнения.

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

Внимание! В 5-том пункте таблица красиво заполнилась без ошибок, так как наша активная ячейка была по адресу A2, после выполнения 4-го пункта. При использовании данного метода будьте внимательны и следите за тем где находится активная ячейка после выделения. Важно откуда она будет брать свои значения.

Полуавтоматическое заполнение ячеек в Excel из выпадающего списка

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

В новой исходной таблице автоматически заполните колонки C и D соответствующие им данными.

Полуавтоматическое заполнение ячеек.

  1. Заполните заголовки колонок C1 – «Дата» и D1 – «Тип платежа».
  2. В ячейку C2 введите дату 18.07.2015
  3. В ячейках С2:С4 даты повторяются. Поэтому выделяем диапазон С2:С4 и нажимаем комбинацию клавиш CTRL+D, чтобы автоматически заполнить ячейки предыдущими значениями.
  4. Введите текущею дату в ячейку C5. Для этого нажмите комбинацию клавиш CTRL+; (точка с запятой на английской раскладке клавиатуры). Заполните текущими датами колонку C до конца таблицы.
  5. Диапазон ячеек D2:D4 заполните так как показано ниже на рисунке.
  6. В ячейке D5 введите первую буку «п», а дальше слово заполнят не надо. Достаточно нажать клавишу Enter.
  7. В ячейке D6 после ввода первой буквы «н» не отображается часть слова для авто-заполнения. Поэтому нажмите комбинацию ALT+(стрела вниз), чтобы появился выпадающий список. Выберите стрелками клавиатуры или указателем мышки значение «наличными в кассе» и нажмите Enter.

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

Внимание! Если значение состоит из нескольких строк, то при нажатии на комбинацию ALT+(стрела вниз) оно не будет отображаться в выпадающем списке значений.

Разбить значение на строки можно с помощью комбинации клавиш ALT+Enter. Таким образом, текст делится на строки в рамках одной ячейки.

Разбиение ячейки на 2 строки.

Примечание. Обратите внимание, как мы вводили текущую дату в пункте 4 с помощью горячих клавиш (CTRL+;). Это очень удобно! А при нажатии CTRL+SHIFT+; мы получаем текущее время.

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

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

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

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

Другие видеоуроки от Майка Гирвина в YouTube на канале excelisfun

  1. Выделите одну или несколько ячеек, которые необходимо использовать в качестве основы для заполнения других ячеек. Например, если требуется задать последовательность 1, 2, 3, 4, 5. введите в первые две ячейки значения 1 и 2. Если необходима последовательность 2, 4, 6, 8. введите 2 и 4. Если необходима последовательность 2, 2, 2, 2. введите значение 2 только в первую ячейку.
  2. Перетащите маркер заполнения

Дополнительные сведения

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

Использование функции мгновенного заполнения в Excel

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

Примечание: Функция мгновенного заполнения доступна только в Excel 2013 и более поздних версий.

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

Объединение данных с помощью функции мгновенного заполнения

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

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

  1. Введите полное имя в ячейке C2 и нажмите клавишу ВВОД.
  2. Выберите Данные >Мгновенное заполнение или нажмите клавиши CTRL+E.
  3. Excel определит закономерность в ячейке C2 и заполнит ячейки ниже.

Дополнительные сведения

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

Мгновенно заполняйте пустые ячейки ближайшими значениями

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

Инструмент «Заполнить пустые ячейки» позволяет мгновенно заполнить пропуски данных в таблице:

Заполнить пустые ячейки в столбце, строке, диапазоне или всей таблице
Заполнить пустые ячейки ближайшими значениями сверху, снизу, слева или справа
При необходимости автоматически разделить и заполнить объединённые ячейки

Перед началом работы добавьте «Заполнить пустые ячейки» в Excel

«Заполнить пустые ячейки» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Начните работу с инструментами XLTools

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.

Как заполнить пустые ячейки сверху вниз

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

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

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

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

Как заполнить пустые ячейки снизу вверх

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

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

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

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

Как заполнить пустые ячейки слева направо

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

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

Эта операция заполнит пустые ячейки слева направо ближайшими значениями слева. Другими словами, скопирует значения ячеек слева в пустые ячейки справа.

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

Как заполнить пустые ячейки справа налево

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

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

Эта операция заполнит пустые ячейки справа налево ближайшими значениями справа. Другими словами, скопирует значения ячеек справа в пустые ячейки слева.

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

Задайте параметры заполнения пустых ячеек

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

Нажмите кнопку Заполнить пустые на панели XLTools Вы выпадающем списке выберите Задать параметры. Откроется диалоговое окно.

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

Отметьте флажком Отменить объединение ячеек . Если эта опция выбрана, все объединённые ячейки в указанном диапазоне будут автоматически разделены и заполнены соответствующими значениями.

Как задать параметры заполнения пустых ячеек в таблице

Нажмите ОК Готово, все пропуски в таблице заполнены значениями.

Как заполнить пустые ячейки и отменить объединение ячеек в таблице

Если остаются пустые ячейки, и что такое по-настоящему пустая ячейка

Инструмент «Заполнить пустые ячейки» учитывает только по-настоящему пустые ячейки. Если после выполнения операции пустоты все же остались, скорее всего, пустые ячейки не были действительно пустыми.

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

Ячейка содержит пробел, перенос строки, апостроф, непечатаемый символ, или что-то ещё невидимое для глаза

Проверка пустых ячеек с помощью функции ЕПУСТО

Как инструмент «Заполнить пустые ячейки» обрабатывает данные

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

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

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

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

Обратите внимание, этот инструмент нельзя применить непосредственно к сводной таблице (PivotTable). Вы можете заполнить пустоты в исходных данных или скопировать и вставить сводную таблицу — и только после этого использовать «Заполнить пустые ячейки».

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *