Заполнить пустые ячейки в excel предыдущим значением
Хотелось бы поделится, часто используемым приемом при работе с таблицами, в которых к одному и тому же объекту принадлежит несколько записей (строк таблицы), при этом таблица первоначально сформирована по принадлежности к этому объекту, но его значение указано только в первой строке конкретной группы.
Разберем ситуацию на рабочем примере:
Имеется выгрузка о штатной расстановке сотрудников, в столбце «A» указан номер ГОСБ не для всех сотрудников, но структура таблицы подразумевает, что пустые строки от одного заполненного значения до другого, равны начальному значению каждой группы:
Чтобы заполнить каждую строку соответствующим значением необходимо:
- Выделить столбец «A» нажав на его название, и вызвать окно «перехода» нажав клавишу «F5»:
2. В левом нижнем углу нажимаем кнопку «Выделить» и в открывшемся окне выбираем пункт «Пустые ячейки», подтверждаем выбор действия кнопкой «ОК»:
3. В самой верхней незаполненной ячейке выделенного диапазона теперь необходимо ввести нужную формулу, указывающую на значение вышестоящей заполненной ячейки, в нашем случае в ячейку «А3» вводим простую формулу равенства ‘=А2′:
4. На финальном этапе, для заполнения всех выделенных пустых ячеек необходимо зажать клавишу «Ctrl» и нажать клавишу «Enter». При необходимости убираем формулу из заполненных ячеек:
В ИТ из образования, строительства и банкинга: выпускники ИТ-курсов о смене профессии Университет Иннополис проводит курсы ускоренной подготовки кадров. С 2016 года обучение по различным программам прошли 2 500 специалистов. Выпускники курса по тестированию рассказывают истории своих карьерных изменений. Коротко: цифровой сервис для планирования путешествий по РоссииВыбор туров, маршрутов и экскурсий в одном месте, покупка авиа- и ж/д-билетов и бронирование столиков.
Поездка началась: как сервис по заказу такси DiDi вышел на рынки Мексики, Чили и Новой ЗеландииИ почему этот опыт помог ему закрепиться в России.
Как вывести бизнес по перепродаже вещей на оборот в 2 млн рублей в месяцРассказывает руководитель проекта Svalka.
Как мы улучшили общение с пользователями и оптимизировали внутренние процессы работы службы поддержки
Ноябрь - самое время платить налоги. А если вы клиент Альфа-банка, то и налоги на воздух. Торговые островки на максималках: сколько можно заработать на новогодних ярмарках и как это сделатьСпойлер: с цифрами - как вы любите. Ценный опыт организатора запуска более 500 торговых точек и 6 лет работы с островками.
Современная жизнь человека - это лаборатория по проверке гипотез Монолог обоснования моего желания сменить профессию в 50 лет. Управляющая компания ГК INGRAD передала мои персональные данные сотрудникам Beeline, которые начали спам-атакуСеть быстрого питания на протяжении 50 лет развивала франшизу — один из основателей был одержим открытием заведений, иногда в ущерб компании. В последние несколько лет она столкнулась с медийными проблемами и падением продаж, но наняла нового руководителя и надеется выйти из кризиса.
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.
| из | сделать |
В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.
Способ 1. Без макросов
Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).
Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :
Не снимая выделения, вводим в первую ячейку знак "равно" и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):
И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.
В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.
Способ 2. Заполнение пустых ячеек макросом
Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert - Module и копируем или вводим туда вот такой короткий код:
Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки.
Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (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) .
Для того, чтобы использовать возможности Excel на всю мощь, нужно правильно организовывать данные. Если таблица содержит пустые ячейки, то могут некорректно работать фильтры, сортировка, формулы и сводные таблицы. "Красивое" оформление важно, но его нужно добиваться без потери функциональности.
Очень часто можно встретить такой пример неправильной организации данных.
Таблица слева не позволит нормально фильтровать и сортировать месяцы и годы, а также не даст создать полнофункциональную сводную таблицу (поля "Год" и "Месяц" в ней будут, по сути, бесполезны). Многие пользователи готовы пойти на такие неудобства из-за того, что не хотят тратить время на исправление таблицы. Но они просто еще не знают, что заполнить все пустые ячейки в такой таблице можно за несколько секунд.
Заполнение пустых ячеек таблицы значениями "Сверху"
1) Выделите проблемные столбцы, начиная первой заполненной строкой и заканчивая последней, которую нужно заполнить;
2) Вызовите окно "Выделить группу ячеек". Это можно сделать двумя способами:
- " Главная " - " Найти и выделить " - " Выделить группу ячеек ";
3) В открывшемся окне "Выделить группу ячеек" выберите " Пустые ячейки ";
4) Не снимая выделение, введите в активную ячейку ссылку на предыдущую верхнюю ячейку (эта ячейка должна быть заполнена);
5) Чтобы ввести формулу во все пустые ячейки, завершите ввод нажатием Ctrl+Enter;
6) Выделите заполненный диапазон, скопируйте и замените на значения (чтобы удалить ненужные больше формулы).
Как видите, привести таблицу в порядок - дело пары минут. Их стоит потратить на такую простую операцию, чтобы в дальнейшем не было проблем с обработкой таблицы.
Видеоверсию данной статьи смотрите на нашем канале на YouTube
Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel - подписывайтесь на наш канал в Telegram Excel Everyday
Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point и т.д.) - на нашем канале в Telegram Office Killer
Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot
Вопросы по другому ПО (кроме Excel) задавайте второму боту - @KillOfBot
Наличие в таблицах ячеек с отсутствующими значения приводит к некорректной сортировке и фильтрации, а также к проблемам при создании сводных таблиц. Для устранения таких проблем необходимо заполнить пустые ячейки значениями, создав непрерывный список. Чаще всего требуется заполнение ячеек, в которых отсутствуют значения, нулями или значениями верхних, нижних, левых или правых ближайших заполненных ячеек.
Заполнение ячеек с отсутствующими значениями стандартными средствами Excel
Заполнение пустых ячеек нулями (Поиск и замена)
1. Выделение пустых ячеек в нужном диапазоне: Вкладка «Главная», группа кнопок «Редактирование», меню кнопки «Найти и выделить», пункт меню «Выделить группу ячеек». В диалоговом окне «Выделить группу ячеек» выбирается опция «Пустые ячейки»;
2. Когда пустые ячейки выделены, вызывается диалоговое окно «Найти и заменить» либо через меню этой же кнопки «Найти и выделить», либо сочетанием горячих клавиш Ctrl+F и перейти на вкладку «Заменить».
3. Поле «Найти» остается пустым, в поле «Заменить на» вписывается нуль, либо другое необходимое значение. После этого нажимается кнопка «Заменить все».
Заполнение пустых ячеек нулями (Ctrl+Enter)
1. По аналогии с предыдущим пунктом предварительно выделяются пустые ячейки в нужном диапазоне;
2. Курсор помещается в строку формул и вписывается необходимое значение, например, нуль;
3. Зажимается клавиша Ctrl на клавиатуре, после чего нажимается клавиша Enter.
В результате все выделенные пустые ячейки заполняются значением, введенным в строку формул.
Заполнение пустых ячеек верхними значениями (формула)
1. Пустые ячейки можно заполнить значениями предыдущих ячеек.
2. Выделяются пустые ячейки (также как это описано выше);
3. Курсор помещается в строку формул и ставится знак «равно», после этого указывается адрес вышестоящей ячейки (можно просто кликнуть по нужной ячейке левой кнопкой мыши);
4. Удерживая клавишу Ctrl на клавиатуре нажимается клавиша Enter.
В этом случае пустые ячейки заполняются не значениями, а формулами. Если применить к таким значениям сортировку, добавить новые строки, либо удалить существующие, формулы могут сбиться, поэтому для корректной дальнейшей работы желательно заменить формулы результатами их вычислений. Для этого можно скопировать диапазон заполненных ячеек и вставить только значения.
Быстрое заполнение пустых ячеек верхними значениями при помощи макроса
Надстройка для быстрого заполнения пустых ячеек верхними, нижними, левыми или правыми значениями
Максимально быстро, не делая лишних манипуляций, буквально за пару кликов можно осуществить заполнение пустых ячеек значениями соседних ячеек при помощи надстройки для Excel. Достаточно только вызвать диалоговое окно надстройки, указать диапазон ячеек и выбрать одну из доступных опций: заполнить верхними значениями, заполнить нижними значениями, заполнить левыми значениями, заполнить правыми значениями после чего нажать кнопку «Пуск».
Надстройка позволяет:
1. Выделять необходимый диапазон для заполнения пустых ячеек значениями соседних ячеек;
Читайте также: