В excel выделить пустые ячейки в

Создание нового правила заливки
После нажать «ОК»

Результат заливки пустых ячеек

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

В excel выделить пустые ячейки в

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

Начальные данные: Таблица, содержащая N-ое количество пустых ячеек (Рис.1).

Задача: найти ячейки и удалить

Рисунок 1. Таблица Excel с пустыми ячейками

Решение:

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

Рисунок 2. Выделение таблицы

2. В меню «Главная» нажмите на стрелку около значка «Найти и выделить» (в правой части панели) и выберите «Выделить группу ячеек».

Рисунок 3. Выделение пустых ячеек

3. В открывшемся окне «Выделить группу ячеек» отметьте «пустые ячейки» (Рис.4)

Рисунок 4. Выбор пустых ячеек

4. В результате будут выделены все пустые ячейки диапазона (Рис.5). Теперь их можно удалить.

Рисунок 5. Выделенные пустые ячейки

5. Нажатием кнопки «Удалить» (Рис.6) выделенные ячейки будут удалены. Нажатием стрелки вниз под кнопкой «Удалить» можно выбрать удаление строк или столбцов.

Выделение в MS EXCEL незаполненных ячеек

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

Для работы практически любой формулы требуются исходные данные. Эти исходные данные пользователь должен вводить в определенные ячейки. Например, для определения суммы месячного платежа с помощью функции ПЛТ() от пользователя требуется заполнить 3 ячейки, в которые нужно ввести следующие значения: Годовую процентную ставку, Количество месяцев платежей и Сумму кредита.

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

Для этого, для ячеек, содержащих исходные данные, настроим правило Условного форматирования (см. Файл примера ):

• выделите ячейки, в которые пользователь будет вводить исходные данные (пусть это диапазон А2:А5);
• вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило );
• выберите Использовать формулу для определения форматируемых ячеек;
• в поле «Форматировать значения, для которых следующая формула является истинной» введите =ЕПУСТО($А2) ;
• выберите требуемый формат, например, серый цвет фона;

Незаполненные ячейки (А3) будут выделены серым цветом, а заполненные ячейки будут без выделения.

Вместо функции ЕПУСТО() можно использовать другое правило Условного форматирования: Главная/ Стили/ Условное форматирование/ Создать правило / Форматировать только ячейки, которые содержат. В разделе Форматировать только ячейки, для которых выполняется следующее условие выбрать в выпадающем списке значение Пустые и затем выбрать требуемый формат, например, серый цвет фона.

Если ячейка содержит значение Пустой текст («»), то Условное форматирование на основе функции ЕПУСТО() и на основе правила Главная/ Стили/ Условное форматирование/ Создать правило / Форматировать только ячейки, которые содержат . Пустые дадут разные результаты. Об этом читайте в статье Подсчет пустых ячеек.

СОВЕТ :
Чтобы найти все ячейки на листе, к которым применены правила Условного форматирования необходимо:

• на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить;
• выберите в списке пункт Условное форматирование;
• будут выделены все ячейки, которым применены правила Условного форматирования.

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

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

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

Заполнять или не заполнять? – этот вопрос часто возникает в отношении пустых ячеек в таблицах Excel. С одной стороны, таблица выглядит аккуратнее и более читабельной, когда Вы не загромождаете её повторяющимися значениями. С другой стороны, пустые ячейки в Excel могут привести к проблемам во время сортировки, фильтрации данных или при создании сводной таблицы. В таком случае Вам придётся заполнить все пустые ячейки. Существуют разные способы для решения этой проблемы. Я покажу Вам несколько быстрых способов заполнить пустые ячейки различными значениями в Excel 2010 и 2013.

Итак, моим ответом будет – заполнять! Давайте посмотрим, как мы сможем это сделать.

Как выделить пустые ячейки на листе Excel

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

1. Выделите столбцы или строки, в которых требуется заполнить пустоты.
2. Нажмите Ctrl+G или F5, чтобы отобразить диалоговое окно Go To (Переход).
3. Нажмите кнопку Special (Выделить).

Замечание: Если Вы вдруг забыли сочетание клавиш, откройте вкладку Home (Главная) и в разделе Editing (Редактирование) из выпадающего меню Find & Select (Найти и выделить) выберите команду Go To Special (Выделить группу ячеек). На экране появится то же диалоговое окно.

1. Команда Go To Special (Выделить группу ячеек) позволяет выбрать ячейки определённого типа, например, ячейки, содержащие формулы, примечания, константы, пустые ячейки и так далее.
2. Выберите параметр Blanks (Пустые ячейки) и нажмите ОК.Теперь в выбранном диапазоне выделены только пустые ячейки и всё готово к следующему шагу.

Формула для заполнения пустых ячеек значениями из ячеек выше (ниже)

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

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

1. Выделите все пустые ячейки.
2. Нажмите F2 или просто поместите курсор в строку формул, чтобы приступить к вводу формулы в активную ячейку. Как видно на снимке экрана выше, активна ячейка C4.
3. Введите знак равенства (=).
4. Укажите ячейку, находящуюся выше или ниже, нажав стрелку вверх или вниз, или просто кликнув по ней.Формула (=C3) показывает, что в ячейке C4 появится значение из ячейки C3.
5. Нажмите Ctrl+Enter, чтобы скопировать формулу во все выделенные ячейки.

Отлично! Теперь в каждой выделенной ячейке содержится ссылка на ячейку, расположенную над ней.

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

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

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

1. Выделите все пустые ячейки
2. Нажмите F2, чтобы ввести значение в активную ячейку.
3. Введите нужное число или текст.
4. Нажмите Ctrl+Enter.

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

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

Какой бы способ Вы ни выбрали, обработка всей таблицы Excel займёт не больше минуты!

Теперь Вы знаете приёмы, как заполнить пустые ячейки различными значениями в Excel 2013. Уверен, для Вас не составит труда сделать это как при помощи простой формулы, так и при помощи инструмента Find & Replace (Найти и заменить).