Если пользователь использует электронную таблицу Excel с небольшим объемом данных, нет проблем с поиском пустых ячеек. Но когда информации много, хорошо бы этот процесс автоматизировать. В конце концов, просмотр тысяч ячеек вручную занимает много времени, которое можно использовать для другой работы или для отдыха. Это особенно важно при работе с документами, где большая часть данных является числовой. Однако важно не запутаться.
Также автоматизация поиска пустых ячеек важна в ситуациях, когда на основе полученных данных другая формула выполняет определенные вычисления. Таким образом, вы экономите время на нескольких этапах одновременно.
Встроенные инструменты Excel упрощают эту задачу. В этом случае есть до трех способов проверить, содержит ли ячейка информацию, каждый из которых следует использовать в зависимости от ситуации.
Как определить, пустая ячейка или нет (способы)
Вы можете сделать это с помощью функции ISBLANK, которую можно использовать с широким спектром типов данных, включая не только числа и текст, но и логические функции. Давайте подробнее рассмотрим особенности работы с этой формулой.
Эта функция очень проста в использовании. Если в определенной ячейке нет информации, возвращается ИСТИНА. Если он содержит определенную информацию, то, следовательно, значение ЛОЖЬ.
Есть и другие способы определить, находится ли информация во всех ячейках определенного диапазона или в определенной ячейке. Также в некоторых ситуациях имеет смысл использовать обычную функцию ЕСЛИ, знакомую практически любому активному пользователю. Давайте также ознакомимся с правилами их использования.
Процедуры VBA
VBA — это язык программирования, используемый для написания макросов. Это небольшие исполняемые модули, которые выполняют определенную последовательность действий, включая проверку наличия информации в ячейке.
Код для конкретной ячейки
Если перед нами стоит задача указать конкретную ячейку для проверки некоторой информации, то нам необходимо написать такой код.
Подпример()
Если он пуст (range («A1»)) = true, то
MsgBox «Ячейка пуста»
Другой
MsgBox «Ячейка не пуста»
Конец, если
Конец подзаголовка
Этот фрагмент кода проверяет ячейку A1, чтобы увидеть, содержит ли она какие-либо данные. И результат отображается в соответствующем окне.
Код для активной ячейки
Если перед нами стоит задача написать макрос, который анализирует выбранную ячейку на предмет содержания некоторой информации, содержащейся в ней, то мы должны использовать такой кусок кода.
Подпример()
Если IsEmpty (ActiveCell) = True, то
MsgBox «Ячейка пуста»
Другой
MsgBox «Ячейка не пуста»
Конец, если
Конец подзаголовка
Функция ЕПУСТО для проверки пустых ячеек
Как мы уже поняли, функция ISBLANK позволяет определить, пуста ли конкретная ячейка. Давайте посмотрим на некоторые практические аспекты использования этой функции.
Примеры использования функции ЕПУСТО
Мы описываем некоторые способы использования функции ISBLANK на практике. Допустим, вы учитель и у вас есть открытая таблица с оценками учащихся за последний тест. При этом некоторые баллы не засчитывались, так как ряд студентов плохо сдали их и пошли забирать. И допустим, что перед нами стоит задача написать в третьем столбце список сдавших экзамен с первой попытки.
Для выполнения поставленных задач необходимо после выделения диапазона C3 ввести следующую формулу: C18.
= SE (OR (ISBLANK (B3); B3 = 2); «Повторить»; «Пройдено»)
Если пользователь хоть немного разбирается в том, как организована работа с формулами, то, исходя из синтаксиса приведенной выше строки, он поймет, что если в ячейке стоит оценка, то в ячейке будет написано «Пройдено» . Если голосов нет, появится результат «Повторить».
После ввода этой формулы результат будет следующим:
Итак, попробуем сделать специальное форматирование в зависимости от того, есть у человека голос или нет. Для этого вам нужно найти опцию «Стили» на вкладке «Главная». Есть элемент «Условное форматирование». После его выбора нам нужно нажать на кнопку «Создать правило».
Появится окно, в котором мы должны выбрать последний вариант, связанный с использованием формулы для определения параметров форматирования. В строке ниже вам нужно написать следующую формулу, которая показана на скриншоте.
После этого нам нужно нажать на кнопку «Форматировать» и выбрать красный цвет фона. Действия подтверждаются нажатием кнопки «ОК». Теперь все ячейки с пометкой «Повторить» выделены красным.
Почему следует использовать функцию ЕПУСТО при проверке пустых ячеек?
Любой, кто уже немного знаком с Excel, может задать вопрос: почему вы не можете использовать для этой цели обычную функцию ЕСЛИ? Да, ты можешь. Просто напишите эту формулу:
= SE (OR (B3 = «»; B3 = 2); «Повторить»; «Пройден»)
Мы видим, что здесь мы использовали кавычки для определения ячеек без значения. И в случае, описанном выше, эта формула тоже сработает. Но это возможно не во всех случаях. Если определенные типы значений содержатся, этот трюк не сработает. Давайте посмотрим на этот снимок экрана.
Мы видим, что в первой ячейке стоит знак ‘. Этот символ не отображается на листе и также не может быть обнаружен функцией ЕСЛИ. Это связано с тем, что это специальный символ Excel, необходимый для отображения текстовой информации, например, если ячейка имеет числовой формат. Это часто используется, когда вам нужно ввести плюс в ячейку. Если вы не используете одинарные кавычки, программа подумает, что мы собираемся ввести формулу. В этом главное отличие.
Если вы используете для этой цели функцию ISBLANK, у пользователя есть возможность узнать реальное изображение, какие ячейки действительно пусты, а не просто отображаются как пустые.
Пример проверки на пустую ячейку
У нас есть таблица, в которой содержится некоторая информация. Наша задача — проверить комплектность полей. Наша исходная таблица выглядит так.
Чтобы понять, есть ли пустые ячейки, нужно воспользоваться формулой массива. Его главное отличие в том, что он работает одновременно с большим объемом данных. Для создания формулы массива необходимо нажать комбинацию Ctrl + Shift + Enter после ввода формулы в соответствующей строке.
= ЕСЛИ (СУММ (- БЕЛЫЙ (B3: B17)); ИСТИНА; ЛОЖЬ)
В примере, который мы сейчас описываем, функция СУММ используется для количественной оценки всех значений, возвращаемых функцией ISBLANK. Если после расчета по этой формуле окажется значение больше нуля, будет возвращено значение «Истина».
В результате получаем следующую таблицу.
Мы видим, что в рассматриваемом примере есть те ячейки, которые не содержат никаких значений.
Внимание. В приведенной выше формуле использовались символы «––». На это следует обратить особое внимание. Эти символы представляют собой двойное отрицание. В нашем примере нам нужно использовать его для преобразования логического типа данных в числовой.
Некоторые функции Excel не поддерживают автоматическое преобразование типов данных, поэтому вам нужно сделать это самостоятельно. Чаще всего для этого используют умножение на единицу или прибавление к полученному значению нуля. Но вы также можете использовать любую другую арифметическую операцию, которая возвращает то же значение.
Но практика показывает, что двойной минус удобнее использовать. Это не только удобнее на этапе набора текста, но и ускоряет работу стола. К тому же прирост производительности может достигать 15 процентов, что очень много при большом количестве функций.
Как подсчитать количество пустых ячеек в Эксель
Теперь давайте посмотрим на другой пример, который лучше всего подходит для нашей ситуации. Предположим, у нас есть список сотрудников, каждый из которых идентифицируется не только по возрасту, но и по определенному коду. Однако еще не все поля перечислены в таблице. Наша задача — проверить, все ли поля заполнены, и вывести сообщение с количеством незаполненных полей.
Сама таблица выглядит так.
Для выполнения поставленной задачи нам нужна такая формула.
= ЕСЛИ (СУММ (-ПРОСТОЙ (B3: B12)); СЧИТАТЬ ПУСТОЙ (B3: B12) & «поля пусты»; СРЕДНИЙ (B3: B12))
Функция ЕСЛИ позволяет вам проверить набор ячеек, чтобы увидеть, есть ли между ними какие-либо пустые значения (для этого используйте функцию —BLAND с соответствующими аргументами). Если в результате суммирования было возвращено положительное значение, будет показан текст, в котором написано, сколько ячеек бесполезны. Для этого используйте функцию СЧИТАТЬПУСТОТЫ. А со знаком & мы объединяем несколько значений.
Получается такой документ.
При этом необходимо помнить обо всех особенностях и ограничениях использования функции SE, описанных выше.
Особенности применения функции ЕПУСТО в Excel
Существуют разные типы функций, и ISBLANK относится к логической категории. Проще говоря, проверьте определенные выражения по определенному критерию. Таких функций до сих пор огромное количество, и это не только вышеупомянутый IF. Также к этим функциям относятся такие операторы, как ISLINK, ISNUMBER и т.д. Все возвращают одно из двух возможных значений: истина или ложь.
Если вы пишете функцию, ее синтаксис следующий.
= БАЛАНС (значение)
У этой функции есть только один обязательный аргумент. В него можно вводить много типов данных. Это могут быть числа, текст и любое другое значение. В этом случае пользователь может указать как одну ячейку, так и весь диапазон. Если вы используете диапазон ячеек, вам нужно использовать формулу массива вместо обычной.
При работе с этой функцией необходимо учитывать ряд особенностей:
- Невозможно указать конкретное значение в качестве параметра, так как в этом случае автоматически будет возвращено «FALSE». Дело в том, что такое априорное значение не может содержать никакой ценности.
- Если перед нами стоит задача убедиться, что возвращается «ИСТИНА», если ячейка заполнена информацией, то мы должны использовать одновременно две функции: НЕ и ISBLANK. Таким образом, вы должны использовать сначала первое, а затем второе в качестве аргумента.
- Использование функции ADDRESS в качестве параметра функции ISBLANK бесполезно. Тогда он всегда будет возвращать результат, что значение не соответствует критерию. Это связано с тем, что функция АДРЕС возвращает ссылку, которая сама по себе уже является некоторой информацией, даже если в ячейке, на которую она ссылается, нет данных.
- Значение «ЛОЖЬ» будет итоговым даже в случае возникновения ошибки. Это потому, что он все еще содержит некоторую информацию, даже если она неверна. И если мы говорим об информации об ошибке, это тоже полезная информация. Ссылку уже можно считать полноценным типом данных.
- Очень часто пользователи забывают формулу массива, чтобы одновременно проверять большое количество ячеек. В противном случае значение, возвращаемое функцией, будет неверным.
Выводы
Проще говоря, найти пустые ячейки в конкретном документе или листе не так уж и сложно. Это делается по-разному, каждый со своими особенностями. Поэтому необходимо использовать те, которые лучше всего подходят для конкретной ситуации.
В дополнение к двум функциям вы также можете использовать макросы, чтобы понять, есть ли информация в определенной ячейке или нет. Выше были примеры кода, которые позволили вам написать такую подпрограмму самостоятельно. По правде говоря, вы должны адаптировать его к себе, заменив соответствующие ячейки своими собственными. Эту проблему можно решить, используя макрос в активной ячейке. В этом случае вам просто нужно нажать на ячейку, которую вы хотите проверить, после чего программа все сделает за вас.
