Как выделить и выбрать пустые ячейки в Excel
В статье показано, как найти и выделить пробелы в Excel с помощью условного форматирования и VBA. В зависимости от ваших потребностей вы можете раскрашивать только действительно пустые ячейки или те, которые также содержат строки нулевой длины.
Когда вы получаете файл Excel от кого-то или импортируете его из внешней базы данных, всегда полезно проверить данные, чтобы убедиться, что в них нет пропусков или отсутствующих точек данных. В небольшом наборе данных вы легко найдете все пробелы своими глазами. Но если у вас есть огромный файл, содержащий сотни или даже тысячи строк, определить пустые ячейки вручную практически невозможно.
Этот учебник научит вас 4 быстрым и простым способам выделения пустых ячеек в Excel, чтобы вы могли их визуально идентифицировать. Какой метод лучше? Ну, это зависит от структуры данных, ваших целей и вашего определения «пустых».
Выберите и выделите пустые ячейки с помощью Go To Special
Этот простой метод выбирает все пустые ячейки в заданном диапазоне, которые затем можно заполнить любым цветом по вашему выбору.
Чтобы выбрать пустые ячейки в Excel, вам нужно сделать следующее:
- Выберите диапазон, в котором вы хотите выделить пустым. Чтобы выбрать все ячейки с данными, щелкните верхнюю левую ячейку и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней использованной ячейки.
- На Дом вкладка, в Редактирование группа, нажмите Найти и выбрать > Перейти к Спец. Или нажмите F5 и нажмите Специальный… .
- в Перейти к специальному диалоговом окне выберите «Пробелы» и щелкните ХОРОШО. Это выберет все пустые ячейки в диапазоне.
- Выделив пустые ячейки, щелкните значок «Цвет заливки» в Дом вкладка, в Шрифт группу и выберите нужный цвет. Сделанный!
Советы и примечания:
- Перейти к специальному Функция выбирает только действительно пустые ячейки, т.е. ячейки, которые абсолютно ничего не содержат. Ячейки, содержащие пустую строку, пробелы, символы возврата каретки, непечатаемые символы и т. д., не считаются пустыми и не выделяются. Чтобы выделить ячейки с формулами, возвращающими в результате пустую строку (“”), используйте либо Условное форматирование или же Макрос VBA.
- Этот метод является статическим и лучше всего использовать его как одноразовое решение. Изменения, которые вы сделаете позже, не будут отражены автоматически: новые пробелы не будут выделены, а прежние пробелы, заполненные значениями, останутся окрашенными. Если вы ищете динамическое решение, вам лучше использовать Подход условного форматирования.
Фильтровать и выделять пробелы в определенном столбце
Если вам не нужны пустые ячейки в таблице, а нужно найти и выделить ячейки или целые строки, содержащие пробелы в определенном столбце, Excel Filter может быть правильным решением.
Чтобы это сделать, выполните следующие действия:
- Выберите любую ячейку в вашем наборе данных и нажмите Сортировать и фильтровать > Фильтровать по Дом вкладка Или нажмите сочетание клавиш CTRL + Shift + L, чтобы включить автофильтры.
- Щелкните стрелку раскрывающегося списка для целевого столбца и отфильтруйте пустые значения. Для этого очистите Выбрать все поле, а затем выберите (Пробелы).
- Выберите отфильтрованные ячейки в ключевом столбце или целых строках и выберите Наполнять цвет, который вы хотите применить.
В нашем образце таблицы мы можем отфильтровать, а затем выделить строки, в которых ячейки SKU пусты:
Заметки:
- В отличие от предыдущего метода, этот подход рассматривает формулы, возвращающие пустые строки (“”), как пустые ячейки.
- Это решение не подходит для часто меняющихся данных, потому что вам придется очищать и снова выделять их при каждом изменении.
Как выделить пустые ячейки в Excel с условным форматированием
Оба рассмотренных ранее метода просты и лаконичны, но у них есть существенный недостаток — ни один из них не реагирует на изменения, внесенные в набор данных. В отличие от них, условное форматирование — это динамическое решение, то есть вам нужно настроить правило только один раз. Как только пустая ячейка будет заполнена каким-либо значением, цвет тут же исчезнет. И наоборот, как только появится новое пустое место, оно будет выделено автоматически.
Пример 1. Выделите все пустые ячейки в диапазоне
Чтобы выделить все пустые ячейки в заданном диапазоне, настройте правило условного форматирования Excel следующим образом:
- Выберите диапазон, в котором вы хотите выделить пустые ячейки (в нашем случае A2: E6).
- На Дом вкладка, в Стили группа, нажмите Новое правило > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать.
- в Форматировать значения, где эта формула верна введите одну из следующих формул, где A2 — это верхняя левая ячейка выбранного диапазона:
Чтобы выделить абсолютно пустые ячейки, которые ничего не содержат:
=ПУСТО(A2)
Чтобы также выделить кажущиеся пустыми ячейки, содержащие строки нулевой длины (“”), возвращаемые вашими формулами:
=ДЛСТР(A2)=0
или же
=А2=””
- Нажмите на Формат кнопку, переключитесь на Наполнять выберите нужный цвет фона и нажмите OK.
- Нажмите ХОРОШО чтобы сохранить правило и закрыть главное диалоговое окно.
Подробные инструкции см. в статье Создание правила условного форматирования на основе формул в 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 Конец сабвуфера
Как вставить и запустить макрос
Чтобы добавить макрос в книгу, выполните следующие действия:
- Нажмите Alt + F11, чтобы открыть редактор Visual Basic.
- В обозревателе проектов слева щелкните правой кнопкой мыши целевую книгу и выберите Вставлять > Модуль.
- В окне кода справа вставьте код VBA.
Чтобы запустить макрос, вам нужно сделать следующее:
- Выберите диапазон на листе.
- Нажмите Alt + F8, чтобы открыть макрос диалог.
- Выберите макрос и нажмите Бежать.
Подробные пошаговые инструкции см.
Вот как находить, выбирать и выделять пустые ячейки в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Доступные загрузки
Выделение пробелов с помощью условного форматирования (файл .xlsx)
Макросы VBA для окрашивания пустых ячеек (файл .xlsm)