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

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

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

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

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

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

 



Екатерина
30.10.2012 16:32:12
Отличная подсказка. Теперь то, что я делала часами (более 2000 строк) - делаю за 2 минуты. Спасибо большое!!!!!!!!!
Катя
30.10.2012 16:34:02
низкий поклон до земли!!!! страшно вспомнить сколько времени было потрачено на рабский механический труд!!! спасибо!!!
Samir
30.10.2012 16:34:28
Николай вы настоящая находка! Спасибо вам и вашим родителям за вас!
Fellini
30.10.2012 16:35:07
потрясающе!!!
у меня список из 60 населенных пунктов в каждом по 50-100 значений...
долго гемороился.... спасибо!.
Максим
30.10.2012 16:36:54
Спасибо автору!
Сегодня мне нужно было сделать то же самое, но заполненные строки стояли ниже пустых, а не выше как в примере. В этом случае прием тот же, но после выделения пустых ячеек нужно выбрать "= ссылка на последующую ячейку" => Ctrl + Enter. И так тоже работает..
Максим
30.10.2012 16:37:45
Вот уж порадовали:) Супер....спасли мое драгоценное время:) руками на это я тратила каждый день по 4-5 часов...Спасибо человеческое. В 2007 у меня работает Ctrl+G....Ура...можно теперь ехать спокойно в отпуск :).
val
30.10.2012 16:39:41
Sub Macro1()
 Selection.SpecialCells(xlCellTypeBlanks).Select
 Selection.FormulaR1C1 = "=R[-1]C"
End Sub
15.02.2013 03:18:30
Спасибо Вам за труды.
К сожалению мне данный способ не подошел :(
после пункта  ".. выбираем Выделить пустые ячейки (Blanks):"
выдается ошибка  "Не найдено ни одной ячейки, ...."
ячейки визуально пустые, но функция "ЕПУСТО" выдает значение Ложь
при этом фильтр по столбцу спокойно фильтрует пустые ячейки.

Удалить значения из всех отобранных фильтром "пустых" ячеек не получается. Выдается сообщение об ошибке "Невозможно создать или использовать ссылку на диапазон данных, так как она слишком сложна. ..."
Думаю это из-за того, что обрабатываемый файл (XML)  и в нем около 200 000 строк.

Подскажите как можно решить проблему в моем случае ? Можно ли решить штатными способами или придется писать макрос.
17.02.2013 09:26:11
Значит ячейки в вашем случае не пустые, а чем-то заполнены - пробелами, непечатаемыми знаками и т.п. Попробуйте выделить мышью этот невидимый символ, скопировать его (Ctrl+C), затем использовать замену (Ctrl+H), чтобы найти эти символы и удалить их (заменить на пустоту).
11.03.2013 23:43:38
Уважаемые всё намного проще!
В любом свободном столбце, в нашем примере столбец C, строка 2 написать формулу =Если(A2="";C1;A2), и протянуть на все строчки, нужно учесть, что в первой строчке должна быть шапка таблицы
14.03.2013 22:14:23
Я бы не сказал, что это сильно проще (да еще и отдельный столбец нужен), но - тоже вариант.
05.10.2016 13:48:55
<удалено>
15.04.2013 09:57:53
Спасибо Вам огромное!
Благодаря Вам изучение excel 'я и работа в нем становится намного проще!!! :D
12.05.2013 18:31:37
Огромное спасибо, эта подсказка "спасла" кучу времени
23.10.2013 12:01:39
Добрый день Николай. Скажите можно сделать все тоже самое только не заполняя последнюю ячейку (ваш пример: москва заполняется до 3 вкл ячейки, 4-ая пустая; питер заполняется до 6 вкл, 7-ая пустая и т.д.)
Спасибо.
02.07.2014 16:41:20
Супер! Так просто!
07.07.2014 06:24:48
Ага :) Сам радуюсь, каждый раз когда использую или показываю эту фишку на тренингах.
19.11.2014 13:31:26
Ух, спасибо а то я уже в макросы полез , здоровская штука8)
09.03.2015 22:32:08
Спасибо за прекрасный совет!
Реально экономит время!!!
01.04.2015 10:55:32
Спасибо! Вот честно, просто огромное человеческое Спасибо!!!!!
21.05.2015 10:10:16
Просто и прекрасно))) огромное спасибо, Николай Владимирович!!! И ненужно ничего придумывать, записывать и попросту тратить драгоценное время.... Потом только выделить диапазон и преобразовать в значения на всякий ;)
P.S.: Найдя решение этой проблемы сначала в сторонних НАДСТРОЙКАХ, а потом здесь (гениально и просто), лишний раз убедился в уникальности и универсальности Вашей надстройки PLEX - ничего лишнего, что можно быстрее сделать без макросов, удобная навигация, справка по каждому инструменту и возможность отмены большинства действий (это очень нечасто в надстройках) и, конечно, место №1 - работа с текстом)))))
01.09.2015 09:02:35
Объединил макрос заполнения от VAL и преобразование формул в значения от Николая Владимировича. Теперь достаточно выделить диапазон столбцов (или 1 столбец) и нажать кнопку. Вставленные ссылки на верхние непустые ячейки сразу преобразовываются в значения.

Sub FillEmpty()
Dim smallrng As Range
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
For Each smallrng In Selection.Areas
        smallrng.Value = smallrng.Value
    Next smallrng
End Sub
 
16.06.2016 10:28:29
Это гениально, то что искала! СПАСИБО!!!
Здравствуйте.
Уважаемые ученые Экселя прошу помочь с решением проблемы:
по факту в каждой строке нужно найти значения первого и последнего числами и если между ними есть пустые ячейки, то заменить на ноль.
Обыскал все поисковики, но не смог найти даже подобного. Прошу помочь. Строк очень много и вручную (через замену)  буду делать неделю(
Пример ниже: где стоят красные нули, их по факту нет, но должны появится (цвет не важен)
Т01.1502.1503.1504.1505.1506.1507.1508.1509.15
111206809
210008
33322011
46
  1. Выделите диапазон, содержащий пустые ячейки.

  2. Нажмите Ctrl+H, чтобы появилось диалоговое окно Find & Replace (Найти и заменить).
  3. Перейдите на вкладку Replace (Заменить).
  4. Оставьте поле Find what (Найти) пустым и введите нужное значение в поле Replace with (Заменить на).

  5. Нажмите кнопку Replace All (Заменить все).
25.04.2017 15:55:04
Добрый день !!!
Конечно же спасибо за Ваш труд. Выручает только так.
А есть ли вариант этого же "фокуса", но с цветами ячеек?
Спасибо
16.12.2018 13:35:37
Эльдар, можно во втором способе заменить в коде Value на Interior.Color - и получится то, что вы хотите.
11.01.2019 22:21:08
Циклом , если ячеек больше 100 - анрил , Vba очень долго все заполняет ...
31.01.2019 08:22:05
Попробуйте заменить
If IsEmpty(cell) Then cell.Value = cell.Offset(-1, 0).Value
на
    If IsEmpty(cell) Then
        With cell
            .Value = .Offset(-1, 0).Value
        End With
    End If
04.02.2019 21:03:58
Вариант, которым постоянно пользуюсь. Для столбца.
Public Sub Заполнение_столбец()
'заполнить столбец пропущенными данными
Application.ScreenUpdating = False
Dim x As Long
x = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 'определяем количество строк
r = ActiveCell.Row 'MsgBox (r)
c = ActiveCell.Column 'MsgBox (c)
For i = r To x
    If Cells(i, c) = "" Then
        Cells(i, c) = Cells(i - 1, c)
    End If
Next i
End Sub
14.02.2019 13:33:01
Николай, Вас надо к лику святых причислить :)
18.02.2019 20:57:48
Упаси боже :)
Наверх