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

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

  • автор:

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

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

Описание работы

Чтобы заполнить пустые ячейки необходимо:

  1. Выделить диапазон данных, в котором необходимо заполнить пустые ячейки
  2. Перейти на вкладку VBA-Excel. Она будет доступна после установки программы.
  3. В меню Вставить выберите пункт Вставить значения в пустые ячейки.
    Выбор пункта меню Вставить значения в пустые ячейки
  4. В диалоговом окне Заполнение пустых ячеек будет уже вставлен выбранный ранее Диапазон ячеек. Его можно при необходимости изменить.
    Заполнение пустых ячеек
  5. Вы можете заполнить пустые ячейки из верхней непустой ячейки или заполнить из левой непустой ячейки. Наглядно это продемонстрировано на рисунках ниже.

Надстройка
VBA-Excel

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

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

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

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

Чтобы воспользоваться данной командой выделите список с пустыми ячейками, перейдите во вкладку «ЁXCEL» Главного меню, нажмите кнопку «Ячейки» и выберите команду «Заполнить пустые ячейки верхними значениями»:

zapolnit-pustye-yachejki-verkhnimi-znacheniyami

Пустые ячейки списка будут заполнены.

Для отмены операции нажмите кнопку отмены:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек — по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками — например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.

из fill-blanks1.png сделать fill-blanks2.png

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

Способ 1. Без макросов

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12). Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) : fill-blanks3.pngНе снимая выделения, вводим в первую ячейку знак «равно» и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами): fill-blanks4.pngИ, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво. В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.

Способ 2. Заполнение пустых ячеек макросом

Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert — Module и копируем или вводим туда вот такой короткий код:

Sub Fill_Blanks() For Each cell In Selection If IsEmpty(cell) Then cell.Value = cell.Offset(-1, 0).Value Next cell End Sub

Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки. Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.

Способ 3. Power Query

  • Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
  • При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов — всё делать заново.

Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы — Диспетчер имен), либо превратить в «умную» таблицу командой Главная — Форматировать как таблицу (Home — Format as Table ) или сочетанием клавиш Ctrl + T :

Превращаем таблицу в умную

После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.

В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить — Заполнить вниз (Transform — Fill — Fill Down) :

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

Вот и всё 🙂 Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close&Load — Close&Load to. )

В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные — Обновить всё (Data — Refresh All) .

Ссылки по теме

  • Объединение двух столбцов с данными
  • Быстрое выделение всех ячеек с формулами или константами
  • Быстрое заполнение пустых ячеек макросом из надстройки PLEX

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

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