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

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

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

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

Выберите и выделите пустые ячейки с помощью Go To Special

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

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

  1. Выберите диапазон, в котором вы хотите выделить пустым. Чтобы выбрать все ячейки с данными, щелкните верхнюю левую ячейку и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней использованной ячейки.
  2. На Дом вкладка, в Редактирование группа, нажмите Найти и выбрать > Перейти к Спец. Или нажмите F5 и нажмите Специальный… .
    Перейти к специальному в Excel
  3. в Перейти к специальному диалоговом окне выберите «Пробелы» и щелкните ХОРОШО. Это выберет все пустые ячейки в диапазоне.
    Выделение пустых ячеек в Excel
  4. Выделив пустые ячейки, щелкните значок «Цвет заливки» в Дом вкладка, в Шрифт группу и выберите нужный цвет. Сделанный!
    Заполните выделенные пробелы нужным цветом.

Советы и примечания:

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

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

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

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

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

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

Заметки:

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

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

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

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

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

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

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

    =ПУСТО(A2)

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

    =ДЛСТР(A2)=0

    или же

    =А2=»»

  4. Нажмите на Формат кнопку, переключитесь на Наполнять выберите нужный цвет фона и нажмите OK.
  5. Нажмите ХОРОШО чтобы сохранить правило и закрыть главное диалоговое окно.
    Правило условного форматирования для выделения пустых ячеек в Excel.

Подробные инструкции см. в статье Создание правила условного форматирования на основе формул в Excel.

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

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

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

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

=ЕПУСТО($B2)

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

=ДЛСТР($B2)=0

или же

=$B2=»»

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

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

Выделите, если пусто с VBA

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

Макрос 1: Цвет пустых ячеек

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

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

Sub Highlight_Blank_Cells() Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106) End Sub

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

Sub Highlight_Blank_Cells() Dim rng As Range Set rng = Sheet1.Range(«A2:E6») rng.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106) End Sub

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

Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = vbBlue

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

Selection.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6

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

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

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

Sub Highlight_Blanks_Empty_Strings() Dim rng As Range Set rng = Выбор для каждой ячейки в rng If cell.Text = «» Then cell.Interior.Color = RGB(255, 181, 106) Else cell.Interior.ColorIndex = xlNone End If Next Конец сабвуфера

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

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

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

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

  1. Выберите диапазон на листе.
  2. Нажмите Alt + F8, чтобы открыть макрос диалог.
  3. Выберите макрос и нажмите Бежать.
    Запуск макроса для выделения пустых ячеек в Excel

Подробные пошаговые инструкции см.

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

Доступные загрузки

Выделение пробелов с помощью условного форматирования (файл .xlsx)
Макросы VBA для окрашивания пустых ячеек (файл .xlsm)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *