Выделение пустых ячеек в Excel

Обновлено: 05.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 должен форматировать пустые ячейки в таблице. Нажмите на кнопку «Формат».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оглавление

Разница между пустыми ячейками и ячейками, которые кажутся пустыми

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

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

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

Работает для ячеек, которые не являются пустыми, но кажутся пустыми? Да.

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

Выделите диапазон, содержащий пустые ячейки.
На вкладке Главная нажмите кнопку Условное форматирование в разделе Стили. В меню выберите наведите указатель мыши на Правила выделения ячеек, чтобы развернуть меню, и выберите Дополнительные правила…

Откроется диалоговое окно Новое правило форматирования.
Нажмите стрелку раскрывающегося списка рядом с полем Значение ячейки и выберите в меню Пробелы.

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

Откроется отдельное окно для выбора формата. В новом окне Формат ячеек перейдите на вкладку Заливка и выберите цвет для выделения пустых ячеек.
По завершении нажмите ОК.

В предварительном просмотре отображается выбранная цветовая заливка.
Нажмите кнопку ОК в разделе Новое правило форматирования

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

Плюсы. Будучи динамической функцией, Условное форматирование будет автоматически подстраиваться под изменения данных. Например. если вы добавите значение в пустую ячейку, оно не останется выделенным. Аналогичным образом, если вы удалите значение, ячейка станет пустой и выделенной.

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

Фильтрация и выделение пустых ячеек

Работает для ячеек, которые не являются пустыми, но кажутся пустыми? Да.

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

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

Выберите любую ячейку в наборе данных, а затем перейдите на вкладку Главная >Редактирование кнопка >Сортировка и фильтр >Фильтр или нажмите клавиши Ctrl + Shift + L, чтобы добавить столбцовые фильтры в набор данных.

Здесь фильтры добавлены в набор данных, как показано маленькими стрелками в заголовках:

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

Нажмите ОК. Все пустые ячейки в столбце будут сгруппированы следующим образом:

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

Снова нажмите клавиши Ctrl + Shift + L, чтобы очистить фильтры. Все данные вернутся на место, а пустые ячейки останутся выделенными:

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

Работает для ячеек, которые не являются пустыми, но кажутся пустыми? №

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

Нажмите клавиши Ctrl + G или клавишу F5, чтобы открыть диалоговое окно Перейти к.

Нажмите кнопку Специальный в диалоговом окне, чтобы открыть диалоговое окно Перейти к специальному. (Вы также можете найти параметр Перейти к специальному на вкладке Главная в группе Редактирование меню >Найти и выбрать .)

Выберите переключатель Пробелы, а затем кнопку ОК

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

Используйте кнопку Цвет заливки на вкладке Главная в группе Шрифт, чтобы выделить выделенные пустые ячейки.

Будьте осторожны, чтобы не щелкнуть где-либо еще на активном листе; это изменит выделение ячеек.

Пустые ячейки будут выделены/залиты выбранным цветом:

Выделение пустых ячеек с помощью VBA

Работает для ячеек, которые не являются пустыми, но кажутся пустыми? №

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

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

Нажмите кнопку Visual Basic в разделе Код на вкладке Разработчик, если эта вкладка включена, или вы можете нажать Alt + F11.

Откроется редактор Visual Basic (VB). Вот что вы увидите, открыв редактор VB:

Перейдите на вкладку Вставка и выберите Модуль из списка, чтобы запустить Модуль
.

Скопируйте и вставьте следующий код в модуль

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

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

Чтобы запустить код позже, закройте редактор VB сейчас. Перед запуском кода убедитесь, что вы выбрали ячейки в наборе данных. Чтобы запустить код, вам нужно открыть Макрос. Нажмите кнопку Макросы на вкладке Просмотр в разделе Макрос. или нажмите клавиши Alt + F8.

Выберите соответствующий макрос и нажмите кнопку команды Выполнить.

Кнопка Выполнить запустит код (никаких сюрпризов). Пустые ячейки из выбранных ячеек будут выделены заданным цветом:

Не то чтобы мы не знали, что делать дальше, но это конец нашего руководства. Мы быстро описали и подробно описали простые методы выделения пустых ячеек, к которым вы можете вернуться и посмотреть, когда у вас ничего не получится, как выделить так много из них. Мы будем больше обучать вас, и у вас есть шанс изучить все, что касается Excel, в упор, чтобы вы знали, что не заполняете все пробелы самостоятельно. Вернитесь назад, когда вы рисуете пробел в затруднительном положении Excel! Конец (учебника и насыщения пустыми каламбурами).

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

Мы можем использовать подход условного форматирования для «ячеек, не содержащих никаких значений, или для пустых ячеек». Это пошаговое руководство поможет пользователям 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 в программировании? С другой стороны, есть ли способ заморозить число в ячейке, в которой уже есть формула, если другая ячейка находится над определенным числом?

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


Как сделать демотиватор в фотошопе

Программа Python для школьников

Как анимировать текст в After Effects

Как включить проверку орфографии в wordpad

Как изменить номер сноски в Word