Использование функции "ЕСЛИ" для проверки ячейки на наличие символов
Иногда вы хотите проверить, пуста ли ячейка. Обычно это делается для того, чтобы формула не возвращала результат при отсутствии входного значения.
В этом случае мы используем ЕСЛИ вместе с функцией ИМПЛАН:
Эта формула означает: ЕСЛИ (ячейка D2 пуста, вернуть текст «Пусто», иначе вернуть текст «Не пусто»). Вы также можете легко использовать собственную формулу для состояния Not Empty. В следующем примере символы "" используются вместо функции IПУСТО. "" на самом деле означает "ничего».
Эта формула означает: ЕСЛИ (в ячейке D3 ничего нет, вернуть текст «Пусто», иначе вернуть текст «Не пусто»). Вот пример распространенного способа использования символов "", когда формула не оценивается, если зависимая ячейка пуста:
Если (D3 - Ничего, Ничего не возвращается, иначе вычисляется формула).
Функция ЕПУСТО() в MS EXCEL
Задача функции ISBLANK(), английской версии ISBLANK(), состоит в том, чтобы проверить, содержит ли ячейка число, текстовое значение, формулу или нет. Если ячейка A1 содержит значение 555, формула =EMPLAND(A1) вернет ЛОЖЬ, а если ячейка A1 пуста, то ИСТИНА.
Синтаксис функции IBUNDLY
НУЛЬ(значение)
Значение - значением может быть что угодно: текст, число, ссылка, имя, пустая ячейка, значение ошибки, логическое выражение.
Использование функции
Файл примера содержит несколько вариантов элементов управления:
1. Если выделенная ячейка содержит число, текстовое значение, формулу, функция вернет логическое значение ИСТИНА.
2. Если выбранная ячейка пуста, функция также вернет логическое значение FALSE.
Проверка диапазона ячеек
Функция IBПУСТО() проверяет содержимое только одной ячейки. Чтобы подсчитать количество пустых ячеек в диапазоне, используйте функцию COUNTNULL(), но если ячейки содержат значение Empty text (""), функция COUNTNULL() также подсчитает эти ячейки вместе с действительно пустыми. Об этом читайте в статье Подсчет пустых ячеек.
Чтобы ответить на вопрос "Есть ли хотя бы 1 пустая ячейка в B6:B11?" использовать формулу массива
Чтобы ответить на вопрос "Есть ли хотя бы 1 заполненная ячейка в B6:B11?" использовать формулу массива
Примеры функции ЕПУСТО для проверки пустых ячеек в Excel
Функция IMPLAND в Excel используется для хранения текста, чисел, логических значений и других типов данных в указанной ячейке и возвращает логическое значение TRUE, если ячейка пуста. Если указанная ячейка содержит данные, результатом выполнения функции IMPLAND будет логическое значение FALSE.
Примеры использования функции ЕПУСТО в Excel
Пример 1. Таблица Excel содержит результаты (баллы) экзамена, проводимого в образовательном учреждении. В данном электронном задании оценки по отдельным ученикам не указаны, т.к они были отправлены на повторную проверку. В следующей колонке вы выводите текстовую строку «Сдано» напротив тех, кто прошел оценку, а «Пересдать» — напротив тех, кто не сдал с первого раза.
Выберите ячейки C3:C18 и напишите следующую формулу:
Формула ЕСЛИ проверяет возвращаемый результат функции ЕПУСТО для диапазона ячеек B3:B18 и возвращает один из вариантов ("Пересдать" или "Пройдено"). Результат выполнения функции:
Теперь часть этой формулы можно использовать для условного форматирования:
- Выделите диапазон ячеек C3:C18 и выберите инструмент: «ГЛАВНАЯ» — «Стили» — «Условное форматирование» — «Создать правило».
- В появившемся окне «Создать правило форматирования» выберите вариант: «Использовать формулы для определения форматируемых ячеек» и введите следующую формулу:
- Нажмите кнопку «Формат» (как в примере), затем укажите красный цвет заливки в окне «Формат ячеек» и нажмите ОК во всех открытых окнах:
Подробнее: Уменьшить разрядность в Excel
На незаполненных (пустых) ячейках или двойках получаем соответствующее сообщение «На пересдачу» и красную заливку.
Почему нужно использовать функцию ЕПУСТО при проверке пустых ячеек:
В приведенном выше примере вы можете изменить формулу, чтобы использовать двойные кавычки ("") вместо функции проверки ячеек на наличие пустых, и это тоже будет работать:
=ЕСЛИ(ИЛИ(B3="" ;B3=2);"Повторить";"Выполнено»)
Но не всегда! Все зависит от значений, которые могут содержать ячейки. Обратите внимание, как по-разному ведут себя двойные кавычки и функция IMPLAND, если у нас есть одинаковые конкретные значения в ячейках:
Как видно на рисунке, ячейка содержит одинарную кавычку. Первая формула (с двойными кавычками вместо функции) ее не видит. Также в самой ячейке А1 не отображается одинарная кавычка, так как этот специальный символ в Excel предназначен для отображения значений в текстовом формате. Это практично, например, когда нам нужно показать саму формулу, а не результат вычисления, как это делается в ячейках D1 и D2. Достаточно поставить одинарную кавычку перед формулой, и теперь отображается сама формула, а не возвращаемый результат. Но функция IBПУСТО видит, что ячейка A1 на самом деле не пуста!
Проверка на пустую ячейку в таблице Excel
Пример 2. Некоторые данные записываются в таблицу Excel. Определите, все ли поля заполнены или хотя бы одно поле пусто.
Таблица исходных данных:
Чтобы определить наличие пустых ячеек, используйте следующую формулу массива (CTRL+SHIFT+Enter):
Функция SUM используется для определения суммы значений, возвращаемых функцией -ISNULL для каждой ячейки в диапазоне B3:B17 (числовые значения, поскольку используется двойное отрицание). Если СУММ(—ISNULL(B3:B17) возвращает любое значение >0, функция ЕСЛИ вернет значение ИСТИНА.
То есть в диапазоне B3:B17 есть одна или несколько пустых ячеек.
Примечание: в приведенной выше формуле использованы символы «-». Этот тип записи называется двойным отрицанием. В этом случае двойное отрицание необходимо для явного преобразования логических значений в числовые. Некоторые функции Excel не выполняют автоматическое преобразование данных, поэтому механизм преобразования типов необходимо запускать вручную. Наиболее распространенными вариантами преобразования текстовых или логических значений в числовой тип являются умножение на 1 или добавление 0 (например, =TRUE+0 вернет число 1, или ="23"*1 вернет число 23. Но использование нотации =—TRUE обеспечивает более быстрое выполнение функций (по некоторым оценкам, прирост производительности составляет до 15%, что немаловажно при обработке больших объемов данных).
Как посчитать количество пустых ячеек в Excel
Пример 3. Рассчитайте средний возраст офисных работников. Если в таблице заполнены не все поля, выведите соответствующее сообщение и не выполняйте расчет.
Формула для расчета (матричная формула):
Функция ЕСЛИ проверяет диапазон на наличие пустых ячеек (выражение СУММ(—ISNULL(B3:B12))). Если СУММА вернула значение >0, будет отображено сообщение, содержащее количество ячеек, не заполненных данными (COUNTNULL), и строку «поля не заполнены», которая вставляется вместе со знаком «&» (операция конкатенации).
Особенности использования функции ЕПУСТО в Excel
Функция IMPLAND в Excel является одной из логических функций (выполняет проверку условия, такого как IF, IFREF, INUMER и т д., и возвращает результаты в виде данных логического типа: TRUE, FALSE). Синтаксис функции:
Единственный аргумент является обязательным и может принимать ссылку на ячейку или диапазон ячеек для определения наличия данных. Если функция принимает диапазон ячеек, функция должна использоваться как формула массива.
- Если значение было явно передано функции в качестве аргумента (например, =NULL(TRUE), =NULL("текст"), =NULL(12)), результатом ее выполнения будет FALSE.
- Если вы хотите, чтобы функция возвращала ИСТИНА, если ячейка не пуста, вы можете использовать ее с функцией НЕ. Например, =NOT(ISNULL(A1)) вернет TRUE, если A1 не пуст.
- Такая запись, как =ISNULL(АДРЕС(x;y)) всегда будет возвращать ложное значение, так как функция ADDRESS(x;y) возвращает ссылку на ячейку, то есть непустое значение.
- Функция возвращает ЛОЖЬ, даже если ячейка, переданная в качестве аргумента, содержит ошибку или ссылку на ячейку. Это суждение справедливо и для случаев, когда в ячейке отображалась пустая строка в результате выполнения функции. Например, в ячейке A1 была введена формула =ЕСЛИ(2>1;"";ЛОЖЬ), которая вернет пустую строку "". В этом случае функция =ISNULL(A1) вернет FALSE.
- Если вы хотите проверить сразу несколько ячеек, вы можете использовать функцию как формулу массива (выберите необходимое количество пустых ячеек, введите формулу "=ПУСТОЙ(" и передайте в качестве аргумента диапазон проверяемых ячеек, используйте клавишу комбинацию Ctrl+Shift+Enter для выполнения)
