Excel заполнить ячейки слева

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

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

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

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

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).

Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks):

Не снимая выделения, вводим в первую ячейку знак «равно» и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):

И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем 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 — это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно — в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:

• Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. 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):

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

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

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

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

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

Использование формулы для заполнения пустых ячеек значением выше

С помощью этого метода выполните следующие действия:

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

2. Нажмите Главная > Найти и выбрать > Перейти к специальному…И Перейти к специальному появится диалоговое окно, затем отметьте Пробелы вариант. Смотрите скриншот:

3. Нажмите OK, и все пустые ячейки выбраны. Затем введите формулу «= A2 »В активную ячейку A3, не меняя выделения. Эту ссылку на ячейку можно изменить по мере необходимости. Смотрите скриншот:

4, нажмите Ctrl + Enter, Excel скопирует соответствующую формулу во все пустые ячейки. Смотрите скриншот:

5. На этом этапе заполненное содержимое представляет собой формулы, и нам нужно преобразовать формальные формы в значения. Затем выберите весь диапазон, скопируйте его и нажмите Ctrl + Alt + V для активного Специальная вставка… диалоговое окно. Затем выберите Ценности вариант из ВставитьИ выберите Ничто вариант из Эксплуатация. Смотрите скриншот:

6. Затем нажмите OK. И все формулы преобразованы в значения.

Заполните пустые ячейки значением выше с помощью Kutools for Excel

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

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите диапазон, которым вы хотите заполнить пустые ячейки. И нажмите Кутулс > Вставить > Заполнить пустые ячейки. Смотрите скриншот:

2. В Заполнить пустые ячейки диалоговое окно, щелкните На основе ценностей от Заполнить, и проверьте вниз от Опции. Смотрите скриншот:

3. Затем нажмите OK or Применить. И все пустые ячейки заполнены указанным выше значением. Смотрите скриншоты:

Бесплатная загрузка Kutools for Excel от Yhao сейчас!

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

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

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

2. Примените эту функцию, нажав Кутулс > Вставить > Заполнить пустые ячейки, В Заполнить пустые ячейки диалоговое окно, выберите Фиксированная стоимость под Заполнить раздел, а затем введите значение, которым вы хотите заполнить пробелы в Заполненное значение текстовое поле, см. снимок экрана:

3, Затем нажмите Ok or Применить Кнопка, конкретное значение будет сразу заполнено в пустые ячейки. Смотрите скриншоты:

Внимание: Эта утилита также может заполнять пустые ячейки на основе линейных значений сверху вниз или слева направо.

Бесплатная загрузка Kutools for Excel от Yhao сейчас!

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

Статьи по теме:

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

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

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

При заполнении таблиц часто приходится вводить повторяющиеся или похожие значения.

Предположим, что пользователь заполняет таблицу доходов семьи.

Пусть ячейка

В5

содержит значение

«

прибавка за выходные

»

. Предположим, что в ячейку

В6

также необходимо ввести значение «

прибавка за выходные»

. Можно, конечно, использовать мышь и

Маркер заполнения

, и с его помощью скопировать значение из ячейки

B5

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

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

B

6

начать вводить текст

«зарплата»

, то после ввода первой буквы

«з»

EXCEL автоматически предложит заполнить ячейку подходящим текстом из ячеек выше (находящихся в той же

Текущей области

), и нам останется только нажать

ENTER

. Если использовать автозавершение записей не требуется, эту функцию можно отключить (Кнопка

, затем щелкните вкладку

Подробнее

, и затем в меню

Параметры правки

снимите флажок

Автозавершение значений ячеек

).

Но, в нашем случае (мы вводим значение

«

прибавка за выходные

»

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

B

6

не будет введено хотя бы

«

прибавка за в

»

, так как в этом столбце (в ячейке

B

4

) существует запись похожего содержания

«прибавка за доп.работу»,

и EXCEL, естественно, не знает какое из двух значений нужно предложить пользователю.

Решение:

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

CTRL

+

D

(или через меню

). Запомнить несложно –

D

означает первую букву английского слова

Down

(вниз).

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

CTRL

+

D

,

получим копию строки сверху.

Аналогично можно заполнить ячейку значением соседней ячейки слева. Для этого используйте комбинацию клавиш

CTRL

+

R

.

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

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

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

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

1. Выделение пустых ячеек в нужном диапазоне: Вкладка «Главная», группа кнопок «Редактирование», меню кнопки «Найти и выделить», пункт меню «Выделить группу ячеек». В диалоговом окне «Выделить группу ячеек» выбирается опция «Пустые ячейки»;

2. Когда пустые ячейки выделены, вызывается диалоговое окно «Найти и заменить» либо через меню этой же кнопки «Найти и выделить», либо сочетанием горячих клавиш Ctrl+F и перейти на вкладку «Заменить».

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

Заполнение пустых ячеек нулями (Ctrl+Enter)

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

2. Курсор помещается в строку формул и вписывается необходимое значение, например, нуль;

3. Зажимается клавиша Ctrl на клавиатуре, после чего нажимается клавиша Enter.

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

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

1. Пустые ячейки можно заполнить значениями предыдущих ячеек.

2. Выделяются пустые ячейки (также как это описано выше);

3. Курсор помещается в строку формул и ставится знак «равно», после этого указывается адрес вышестоящей ячейки (можно просто кликнуть по нужной ячейке левой кнопкой мыши);

4. Удерживая клавишу Ctrl на клавиатуре нажимается клавиша Enter.

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

Быстрое заполнение пустых ячеек верхними значениями при помощи макроса

Во всех описанных выше способах пустые ячейки, подлежащие заполнению должны быть предварительно выделены, но этого можно и не делать, если использовать процедуру, написанную на VBA. Пример такой процедуры, представлен на странице https://macros-vba.ru/makrosy/excel/kak-zapolnit-pustye-yachejki. Макрос проверяет есть ли значения в ячейках выделенного диапазона и заполняет пустые ячейки значениями верхних ячеек в заданном столбце. При необходимости в процедуру можно добавить цикл по столбцам, тогда заполняться будут пустые ячейки выделенной области в нескольких столбцах.

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

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

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

Надстройка позволяет:

1. Выделять необходимый диапазон для заполнения пустых ячеек значениями соседних ячеек;

2. заполнять пустые ячейки значениями ячеек, находящихся:

а) сверху;

б) снизу;

в) слева;

г) справа.

Видео по работе с надстройкой: