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

Обновлено: 03.07.2024

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

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

Это руководство научит вас 4 быстрым и простым способам выделения пустых ячеек в Excel, чтобы вы могли их визуально идентифицировать. Какой метод лучше? Ну, это зависит от структуры данных, ваших целей и вашего определения "пустых мест".

Выберите и выделите пустые ячейки с помощью «Перейти к специальному»

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

Чтобы выбрать пустые ячейки в Excel, вам нужно сделать следующее:

Функция Перейти к специальному выбирает только действительно пустые ячейки, т. е. ячейки, которые абсолютно ничего не содержат. Ячейки, содержащие пустую строку, пробелы, символы возврата каретки, непечатаемые символы и т. д., не считаются пустыми и не выделяются. Чтобы выделить ячейки с формулами, возвращающими в результате пустую строку (""), используйте либо условное форматирование, либо макрос VBA.
Этот метод является статическим, и его лучше всего использовать в качестве одноразового решения. Изменения, которые вы сделаете позже, не будут отражены автоматически: новые пробелы не будут выделены, а прежние пробелы, заполненные значениями, останутся окрашенными. Если вы ищете динамическое решение, вам лучше использовать подход условного форматирования.

Фильтрация и выделение пробелов в определенном столбце

Если вам не нужны пустые ячейки в таблице, а нужно найти и выделить ячейки или целые строки, содержащие пробелы в определенном столбце, Excel Filter может быть правильным решением.

Чтобы это сделать, выполните следующие действия:

Выберите любую ячейку в наборе данных и нажмите Сортировка и фильтрация > Фильтр на вкладке Главная. Или нажмите сочетание клавиш CTRL + Shift + L, чтобы включить автофильтры.
Нажмите стрелку раскрывающегося списка для целевого столбца и отфильтруйте пустые значения. Для этого снимите флажок Выбрать все и выберите (Пробелы).
Выберите отфильтрованные ячейки в ключевом столбце или целых строках и выберите цвет Fill, который вы хотите применить.

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

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

Как выделить пустые ячейки в Excel с помощью условного форматирования

Оба рассмотренных ранее метода просты и лаконичны, но у них есть существенный недостаток: ни один из них не реагирует на изменения, внесенные в набор данных. В отличие от них, условное форматирование — это динамическое решение, то есть вам нужно настроить правило только один раз. Как только пустая ячейка будет заполнена каким-либо значением, цвет тут же исчезнет. И наоборот, как только появится новое пустое место, оно будет выделено автоматически.

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

Чтобы выделить все пустые ячейки в заданном диапазоне, настройте правило условного форматирования Excel следующим образом:

Выберите диапазон, в котором вы хотите выделить пустые ячейки (в нашем случае A2:E6).
На вкладке Главная в группе Стили нажмите Новое правило >Используйте формулу, чтобы определить, какие ячейки следует форматировать .
В поле Формат значений, где эта формула верна, введите одну из следующих формул, где A2 – это верхняя левая ячейка выбранного диапазона:

Чтобы выделить абсолютно пустые ячейки, которые ничего не содержат:

Чтобы также выделить кажущиеся пустыми ячейки, содержащие строки нулевой длины (""), возвращаемые вашими формулами:

Пример 2. Выделение строк с пробелами в определенном столбце

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

Например, чтобы выделить строки с пробелами в столбце B, выберите всю таблицу без заголовков столбцов (в данном примере A2:E6) и создайте правило с одной из следующих формул:

Чтобы выделить абсолютно пустые ячейки:

Чтобы выделить пробелы и ячейки, содержащие пустые строки:

В результате выделяются только те строки, в которых ячейка SKU пуста:

Выделите, если пусто, с помощью VBA

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

Макрос 1: закрашивание пустых ячеек

Этот макрос может помочь вам выделить действительно пустые ячейки, которые абсолютно ничего не содержат.

Чтобы закрасить все пустые ячейки в выбранном диапазоне, вам понадобится всего одна строка кода:

Чтобы выделить пробелы на предопределенном рабочем листе и в диапазоне (диапазон A2:E6 на листе 1 в приведенном ниже примере), используйте следующий код:

Вместо цвета RGB можно применить один из 8 основных базовых цветов, введя "vb" перед названием цвета, например:

Или вы можете указать индекс цвета, например:

Макрос 2: Цветные пробелы и пустые строки

Чтобы распознать визуально пустые ячейки, содержащие формулы, которые возвращают пустые строки как пробелы, проверьте, установлено ли для свойства Текст каждой ячейки в выбранном диапазоне значение "", и если оно TRUE, то примените цвет.

Вот код для выделения всех пробелов и пустых строк в выбранном диапазоне:

Как вставить и запустить макрос

Чтобы добавить макрос в книгу, выполните следующие действия:

Нажмите клавиши ALT + F11, чтобы открыть редактор Visual Basic.
В обозревателе проектов слева щелкните правой кнопкой мыши целевую книгу и выберите Вставить >Модуль.
В окне кода справа вставьте код VBA.

Чтобы запустить макрос, вам нужно сделать следующее:

Для получения подробных пошаговых инструкций см.:

Вот как найти, выбрать и выделить пустые ячейки в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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

Одной из таких проверок является поиск и выделение пустых ячеек в Excel.

Существует множество причин, по которым ячейки в наборе данных могут оставаться пустыми:

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

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

В этом руководстве я покажу вам различные способы поиска и выделения пустых ячеек в Excel.

Это руководство охватывает:

Выделение пустых ячеек с помощью условного форматирования

Условное форматирование — отличный способ выделить ячейки на основе их значения при выполнении заданного условия.

Я собираюсь продемонстрировать эти примеры с небольшим набором данных. Однако эти же методы можно использовать и с большими наборами данных.

Предположим, у вас есть набор данных, как показано ниже:

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

Вот шаги, чтобы выделить пустые ячейки в Excel (с использованием условного форматирования):

Это выделит все пустые ячейки в наборе данных.

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

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

Выберите и выделите пустые ячейки в Excel

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

Вот шаги, чтобы выбрать и выделить пустые ячейки в Excel:

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

Обратите внимание, что в отличие от условного форматирования этот метод не является динамическим. Если вы сделаете это один раз, а затем по ошибке удалите точку данных, она не будет выделена.

Использование VBA для выделения пустых ячеек в Excel

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

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

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

Вот шаги, чтобы поместить этот код VBA в серверную часть, а затем использовать его для выделения пустых ячеек в Excel:

Как запустить код VBA (макрос)?

После того как вы скопировали этот макрос, вы можете использовать его несколькими способами.

Использование диалогового окна макроса

Вот шаги для запуска этого макроса с помощью диалогового окна Macro:

Использование редактора VB

Вот шаги для запуска этого макроса с помощью редактора VB:

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

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

См. видеоруководство и расшифровку ниже:

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

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

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

Первое, что нужно сделать, это отметить область, в которой мы хотим, чтобы были обнаружены и выделены пустые ячейки. Если вы работаете с таблицей, содержащей много информации, просто выберите целые столбцы. Так.

Перейдите в группу «Стили», нажмите «Условное форматирование» и выберите «Новое правило».

Во всплывающем окне выберите параметр «Форматировать только те ячейки, которые содержат».

Укажите, как Excel должен форматировать пустые ячейки

Теперь мы настроим правило.

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

Следующий шаг — указать, как Excel должен форматировать пустые ячейки в таблице. Нажмите на кнопку «Формат».

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

Итак, нажмите на выбранный цвет и подтвердите, нажав OK.

Пустые ячейки в таблице выделены красным, как мы и хотели. И что еще лучше, как вы уже знаете, подсветка будет динамической.

Если мы введем некоторые данные в ячейку C5, красная подсветка исчезнет.

Кроме того, если мы удалим значение, например, в ячейке B4, Excel автоматически выделит ячейку выбранным нами цветом.

Как отключить функцию

А если вы хотите отключить эту функцию, просто нажмите «Условное форматирование», а затем «Очистить правила». Это может относиться к выбранной области или ко всей электронной таблице. Выберите «Удалить правила со всего листа», и Боб станет вашим дядей — все выделение исчезнет!

Если вам интересно узнать больше о простом способе условного форматирования, посмотрите наши предстоящие видеоролики от команды EasyClick Academy.

Если вы нашли это руководство полезным, поставьте нам лайк и посмотрите другие видеоуроки от EasyClick Academy. Узнайте, как использовать Excel быстро и легко!

Вы впервые пользуетесь EasyClick? Мы будем более чем рады приветствовать вас в нашем онлайн-сообществе. Нажмите кнопку «Подписаться» и присоединяйтесь к EasyClickers!

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

Применить условное форматирование к пустым ячейкам

Целью этого упражнения является применение форматирования к пустым ячейкам Excel. Например, на рис. 1 в конечном результате все пустые ячейки будут выделены желтым цветом. Вот как это можно сделать:

Рисунок 1. Окончательный результат

Настройка данных

Сначала мы настроим наши данные, введя значения элементов в выбранные ячейки.
Наши данные показаны ниже.

Рисунок 2. Настройка данных

Как применить условное форматирование только к пустым ячейкам

Из наших данных на рисунке 1 видно, что следующие ячейки пусты:

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

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

Рисунок 3. Как применить условное форматирование только к пустым ячейкам

После того, как мы нажмем стрелку раскрывающегося списка, мы увидим новое правило. Нажмите «Новое правило», и появится диалоговое окно «Новое правило форматирования», как показано на рис. 3.

Рисунок 4. Как применить условное форматирование только к пустым ячейкам

Там, где вы видите «Выберите тип правила», как показано на рис. 3, нажмите «Форматировать только те ячейки, которые содержат».
Нам также нужно изменить описание правила, чтобы форматировать только ячейки с пробелами, щелкнув стрелку раскрывающегося списка.
Мы можем использовать ряд параметров условного форматирования для пустых ячеек, щелкнув «Формат» рядом с «Без набора форматов». В этом примере давайте воспользуемся желтым цветом, чтобы заполнить пустые ячейки. Мы нажмем OK после выбора нашего варианта.

Рисунок 5. Как применить условное форматирование только к пустым ячейкам

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

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

Как применить условное форматирование с помощью пользовательской формулы

=ЕПУСТО(ЗНАЧЕНИЕ)

Объяснение формул

Пустая ячейка может содержать символ, например пробел (так же, как пробелы, которые мы даем при наборе текста). Мы введем пробел в одну из пустых ячеек (ячейка A9).

Функция ЕПУСТО возвращает значение ИСТИНА, если ячейка не содержит ничего, и значение ЛОЖЬ, если ячейка содержит хотя бы один символ.

Функция ДЛСТР возвращает значение ИСТИНА для пустых ячеек без каких-либо символов и ЛОЖЬ для ячеек с символом.

При использовании пользовательской формулы для условного форматирования важно, чтобы мы вводили формулу относительно активной ячейки в выделении. В данном случае это Cell A4. Это автоматически обновит диапазон, в котором предполагается применить условное форматирование.

По-прежнему применяя шаги, описанные выше, на рисунке 3 мы нажмем Использовать формулу, чтобы определить, какие ячейки нужно отформатировать.

Рисунок 7. Как использовать пользовательскую формулу для условного форматирования

После этого мы нажмем OK, и результат будет показан на рисунке 7. Помните, что ячейка A9 УЖЕ НЕ ПУСТА, НО СОДЕРЖИТ ПРОБЕЛ.

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

Используя тот же подход, мы можем ввести эту строку =LEN(A4)=0 в описание правила на рисунке 6, и мы получим тот же результат, что и на рисунке 7.

Как условно отформатировать столбец на основе результата из другого столбца

Мы можем условно отформатировать столбец с номером автомобиля на основе наличия пустой или визуально пустой ячейки (ячейки, содержащей хотя бы один символ). Ячейка A9 визуально пуста, потому что в ней есть пробел. Для этого мы можем использовать следующую строку:

=$B4=""

Мы выберем диапазон столбца с номером автомобиля (от ячейки A4 до ячейки A12), следуем той же процедуре и, как показано на рисунке 6, введем строку.

=$B4=""

Результат на рис. 8 показывает, что ячейки в столбце A были отформатированы условно, если соответствующие ячейки в столбце B (дата прибытия) пусты.

Если у вас возникли трудности с его применением, у нас есть специалисты, готовые помочь.

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

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

Справка по формуле: =СЧЕТПУСТЫ(смещение(Инглвуд!C$2,,MATCH(D3,Инглвуд!$C$1:$N$1,0)-1,counta(Инглвуд!$A:$A)-1)) . Это помогает мне иметь условное форматирование на основе пустых ячеек с другой вкладки. Я хочу изменить диапазон отображаемых данных. (т.е. вместо C2:C47 я хочу, чтобы это было C11:C47)

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

Есть ли формула для изменения текста (жирного/цветного) ячейки, если значение в другой ячейке превышает определенное значение? Что-то вроде оператора if-then в программировании? С другой стороны, есть ли способ заморозить число в ячейке, в которой уже есть формула, если другая ячейка находится над определенным числом?

Читайте также: