Заполнение пустых ячеек значениями из соседних ячеек
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в 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
у меня список из 60 населенных пунктов в каждом по 50-100 значений...
долго гемороился.... спасибо!.
Сегодня мне нужно было сделать то же самое, но заполненные строки стояли ниже пустых, а не выше как в примере. В этом случае прием тот же, но после выделения пустых ячеек нужно выбрать "= ссылка на последующую ячейку" => Ctrl + Enter. И так тоже работает..
К сожалению мне данный способ не подошел
после пункта ".. выбираем Выделить пустые ячейки (Blanks):"
выдается ошибка "Не найдено ни одной ячейки, ...."
ячейки визуально пустые, но функция "ЕПУСТО" выдает значение Ложь
при этом фильтр по столбцу спокойно фильтрует пустые ячейки.
Удалить значения из всех отобранных фильтром "пустых" ячеек не получается. Выдается сообщение об ошибке "Невозможно создать или использовать ссылку на диапазон данных, так как она слишком сложна. ..."
Думаю это из-за того, что обрабатываемый файл (XML) и в нем около 200 000 строк.
Подскажите как можно решить проблему в моем случае ? Можно ли решить штатными способами или придется писать макрос.
В любом свободном столбце, в нашем примере столбец C, строка 2 написать формулу =Если(A2="";C1;A2), и протянуть на все строчки, нужно учесть, что в первой строчке должна быть шапка таблицы
Благодаря Вам изучение excel 'я и работа в нем становится намного проще!!!
Спасибо.
Реально экономит время!!!
P.S.: Найдя решение этой проблемы сначала в сторонних НАДСТРОЙКАХ, а потом здесь (гениально и просто), лишний раз убедился в уникальности и универсальности Вашей надстройки PLEX - ничего лишнего, что можно быстрее сделать без макросов, удобная навигация, справка по каждому инструменту и возможность отмены большинства действий (это очень нечасто в надстройках) и, конечно, место №1 - работа с текстом)))))
Уважаемые ученые Экселя прошу помочь с решением проблемы:
по факту в каждой строке нужно найти значения первого и последнего числами и если между ними есть пустые ячейки, то заменить на ноль.
Обыскал все поисковики, но не смог найти даже подобного. Прошу помочь. Строк очень много и вручную (через замену) буду делать неделю(
Пример ниже: где стоят красные нули, их по факту нет, но должны появится (цвет не важен)
Конечно же спасибо за Ваш труд. Выручает только так.
А есть ли вариант этого же "фокуса", но с цветами ячеек?
Спасибо
А, не подскажите ли, как затем обратно с пустыми ячейками список сдалать ?
И вот, когда выбираете Наименование из выпадающего списка, все остальные из этой строки должны автозаполнятся.
Просвятите пожалуйста.
Спасибо
подскажите пожалуйста а как сделать такой же трюк в DAX через формулу