Примеры функции ЕПУСТО для проверки пустых ячеек в Excel

Функция ЕПУСТО в Excel употребляется для наличия текстовых, числовых, логических и иных типов данных в обозначенной ячейке и возвращает логическое значение ИСТИНА, если ячейка является пустой. Если в обозначенной ячейке содержатся какие-либо данные, результатом выполнения функции ЕПУСТО будет логическое значение ЛОЖЬ.

Примеры использования функции ЕПУСТО в Excel

Пример 1. В таблице Excel находятся результаты (баллы) по экзамену, который проводился в учебном заведении. В данной электрической ведомости напротив неких студентов оценки не указаны, так как их выслали на пересдачу. В столбце рядом вывести текстовую строчку «Сдал» напротив тех, кому выставили оценки, и «На пересдачу» — напротив не сдавших с первого раза.

Выделим ячейки C3:C18 и запишем последующую формулу:

Формула ЕСЛИ делает проверку возвращаемого результата функции ЕПУСТО для спектра ячеек B3:B18 и возвращает один из вариантов («На пересдачу» либо «Сдал»). Итог выполнения функции:

Сейчас часть данной формулы можно употреблять для условного форматирования:

Выделите спектр ячеек C3:C18 и изберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Сделать правило».
В показавшемся окне «Создание правила форматирования» изберите опцию: «Применять формулы для определения форматируемых ячеек» и введите последующую формулу:
Нажмите на клавишу «Формат» (как на эталону), позже укажите в окне «Формат ячеек» красноватый цвет заливки и нажмите ОК на всех открытых окнах:

На против незаполненных (пустых) ячеек либо двоек мы получаем соответствующее сообщение «На пересдачу» и красноватую заливку.

Почему необходимо употреблять функцию ЕПУСТО при проверке пустых ячеек

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

=ЕСЛИ(ИЛИ( B3=»» ;B3=2);»На пересдачу»;»Сдал»)

Но не постоянно! Все зависит от значений, которые могут содержать ячейки. Направьте внимание на то как по-разному себя ведут двойные кавычки, и функция ЕПУСТО если у нас в ячейках находятся одни и те же специальные значения:

Как видно на рисунке в ячейке находится знак одинарной кавычки. 1-ая формула (с двойными кавычками заместо функции) ее не лицезреет. Наиболее того в самой ячейке A1 одинарная кавычке не отображается потому что данный спецсимвол в Excel созданный для отображения значений в текстовом формате. Это комфортно, к примеру, когда нам необходимо показать саму формулу, а не итог ее вычисления как изготовлено в ячейках D1 и D2. Довольно только перед формулой ввести одинарную кавычку и сейчас отображается сама формула, а не возвращаемый ее итог. Но функция ЕПУСТО лицезреет, что в реальности ячейка А1 не является пустой!

Проверка на пустую ячейку в таблице Excel

Пример 2. В таблице Excel записаны некие данные. Найти, все ли поля заполнены, либо есть как минимум одно поле, которое является пустым.

Начальная таблица данных:

Чтоб найти наличие пустых ячеек используем последующую формулу массива (CTRL+SHIFT+Enter):

Функция СУММ употребляется для определения суммы величин, возвращаемых функцией —ЕПУСТО для каждой ячейки из спектра B3:B17 (числовых значений, так как употребляется двойное отрицание). Если запись СУММ(—ЕПУСТО(B3:B17) возвращает хоть какое значение >0, функция ЕСЛИ возвратит значение ИСТИНА.

Другими словами, в спектре B3:B17 есть одна либо несколько пустых ячеек.

Примечание: в обозначенной выше формуле были применены знаки «—». Данный вид записи именуется двойным отрицанием. В данном случае двойное отрицание нужно для очевидного преобразования данных логического типа к числовому. Некие функции Excel не делают автоматического преобразования данных, потому механизм преобразования типов приходится запускать вручную. Более всераспространенными вариациями преобразования текстовых либо логических значений к числовому типу является умножение на 1 либо добавление 0 (к примеру, =ИСТИНА+0 возвратит число 1, либо =«23»*1 возвратит число 23. Но внедрение записи типа =—ИСТИНА ускоряет работу функций (по неким оценкам прирост производительности составляет до 15%, что имеет значение при обработке огромных размеров данных).

Интересно почитать: Excel сумма отфильтрованных ячеек

Как посчитать количество пустых ячеек в Excel

Пример 3. Высчитать средний возраст работников кабинета. Если в таблице заполнены не все поля, вывести соответственное сообщение и не делать расчет.

Формула для расчета (формула массива):

Функция ЕСЛИ делает проверку спектра на наличие пустых ячеек (выражение СУММ(—ЕПУСТО(B3:B12))). Если СУММ возвратила значение >0, будет выведено сообщение, содержащее количество незаполненных данными ячеек (СЧИТАТЬПУСТОТЫ) и строчку «поля не заполнены», которые склеены знаком «&» (операция конкатенации).

Индивидуальности использования функции ЕПУСТО в Excel

Функция ЕПУСТО в Excel относится к числу логических функций (выполняющих проверку какого-нибудь условия, к примеру, ЕСЛИ, ЕССЫЛКА, ЕЧИСЛО и др., и возвращающих результаты в виде данных логического типа: ИСТИНА, ЛОЖЬ). Синтаксическая запись функции:

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

Заполнить пустые ячейки нулями либо значениями прошлых

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

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

Заполнить пустые ячейки предшествующим значением при помощи формул в Excel

Как заполнить пустые ячейки в Excel значениями непустых ячеек, стоящих сверху над ними?

Обычными средствами Excel это делается в пару шажков:

Создается фильтр по столбцу
В первой пустой ячейке прописывается формула, ссылающаяся на предшествующую ячейку, к примеру, в ячейке A3 будет формула «=A2»
Копируем ячейку с формулой
Отфильтровываем лишь пустые ячейки столбца
Выделяем их и вставляем в их формулу сочетанием Ctrl+V
Снимаем фильтр
Выделяем весь столбец и удаляем формулы, оставляя значения

У данного решения есть тривиальные минусы:

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

Заполнить пустые ячейки в 2 клика

Настройка !SEMTools дозволяет решить задачку в пару мгновений, не меняя форматирование пустых ячеек. Процедура находится в меню «Поменять ячейки» в группе «ИЗМЕНИТЬ«.

Заполняем пустые ячейки в Excel верхними значениями при помощи !SEMTools

Заполнить пустые ячейки значением ниже

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

Заполняем пустые ячейки в Excel значениями нижестоящих ячеек при помощи !SEMTools.

Заполнить пустые ячейки нулями

Наполнение пустых ячеек нулями — легкая операция. Можно произвести ее при помощи поиска и подмены, выделив спектр и вызвав диалог сочетанием кнопок Ctrl+H. Конфигурация подмены обязана быть таковой, как на картинке — отыскиваем ячейки полностью, в каких ничего нет, и заменяем пустоты на нули.

Наполнение пустых ячеек нулями при помощи подмены

При помощи надстройки !SEMTools, тем не наименее, это можно создать еще резвее:

excel если пусто то иная ячейка

Задачка функции ЕПУСТО() , британский вариант ISBLANK(), — инспектировать есть ли в ячейке число, текстовое значение, формула либо нет. Если в ячейке А1 имеется значение 555, то формула = ЕПУСТО(А1) возвратит ЛОЖЬ, а если ячейка А1 пуста, то ИСТИНА.

Синтаксис функции ЕПУСТО()

Интересно почитать: Excel содержит ли ячейка часть текста

ЕПУСТО(значение)

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

Внедрение функции

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

1. Если в проверяемой ячейке содержится число, текстовое значение, формула, то функция возвратит логическое значение ИСТИНА.

2. Если проверяемая ячейка пуста, то функция также возвратит логическое значение ЛОЖЬ.

Проверка спектра ячеек

Функция ЕПУСТО() инспектирует содержимое лишь одной ячейки. Чтоб подсчитать количество пустых ячеек в спектре, то используйте функцию СЧИТАТЬПУСТОТЫ() , но если ячейки содержат значение Пустой текст («»), то функция СЧИТАТЬПУСТОТЫ() будет подсчитывать также и эти ячейки вместе с вправду пустыми. О этом читайте в статье Подсчет пустых ячеек.

Чтоб ответить на вопросец «Есть ли хотя бы 1 пустая ячейка в B6:B11?» используйте формулу массива

Чтоб ответить на вопросец «Есть ли хотя бы 1 заполненная ячейка в B6:B11?» используйте формулу массива

Примечание: Мы стараемся как можно оперативнее обеспечивать вас животрепещущими справочными материалами на вашем языке. Эта страничка переведена автоматом, потому ее текст может содержать некорректности и грамматические ошибки. Для нас принципиально, чтоб эта статья была для вас полезна. Просим вас уделить пару секунд и сказать, посодействовала ли она для вас, при помощи клавиш понизу странички. Для удобства также приводим ссылку на оригинал (на британском языке).

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

В данном случае мы используем ЕСЛИ вкупе с функцией ЕПУСТО:

Эта формула значит: ЕСЛИ(ячейка D2 пуста, возвратить текст «Пустая», в неприятном случае возвратить текст «Не пустая»). Вы также сможете просто употреблять свою формулу для состояния «Не пустая». В последующем примере заместо функции ЕПУСТО употребляются знаки «». «» — практически значит «ничего».

Эта формула значит: ЕСЛИ(в ячейке D3 ничего нет, возвратить текст «Пустая», в неприятном случае возвратить текст «Не пустая»). Вот вам наглядный пример всераспространенного метода использования символов «», при котором формула не рассчитывается, если зависимая ячейка пуста:

Если (D3 — Nothing, ворачивается Nothing, в неприятном случае рассчитывается формула).

Чтоб выполнить действие лишь тогда, когда ячейка не пуста (содержит какие-то значения), вы сможете употреблять формулу, основанную на функции ЕСЛИ.

В примере ниже столбец F содержит даты окончания закупок шоколада.

Так как даты для Excel — это числа, то наша задачка заключается в том, чтоб проверить в ячейке наличие числа.

Формула в ячейке F3:

Как работает эта формула?

Функция СЧЕТЗ (британский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в спектре ячеек Excel. Если мы знаем количество значений в спектре, то просто можно составить условие. Если число значений равно числу ячеек, означает, пустых ячеек нет и можно создавать вычисление. Если равенства нет, означает есть хотя бы одна пустая ячейка, и вычислять недозволено.

Согласитесь, что недозволено именовать этот метод определения наличия пустых ячеек комфортным. Ведь число строк в таблице может поменяться, и необходимо будет поменять формулу: заместо числа 7 ставить другое число.

Давайте разглядим и остальные варианты. В ячейке F6 записана большая формула —

Функция ЕПУСТО (британский вариант — ISBLANK) инспектирует, не ссылается ли она на пустую ячейку. Если это так, то возвращает ИСТИНА.

Интересно почитать: Excel закрасить ячейку по условию

Функция ИЛИ (британский вариант — OR) дозволяет соединить условия и указать, что нам довольно того, чтоб хотя бы одна функция ЕПУСТО нашла пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строчку. В неприятном случае — производим вычисления.

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

Разглядим сейчас наиболее всепригодные решения.

В качестве условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (британский вариант — COUNTBLANK). Она возвращает количество пустых ячеек, но хоть какое число больше 0 Excel интерпретирует как ИСТИНА.

И, в конце концов, еще одна формула Excel, которая дозволит создавать расчет лишь при наличии непустых ячеек.

Функция ЕЧИСЛО ( либо ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку вернет ЛОЖЬ.

А сейчас поглядим, как это работает. Заполним таблицу недостающим значением.

Видите ли, все наши формулы рассчитаны и вернули однообразные значения.

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

Итак, перед нами уже знакомая формула

Для функции СЧЕТЗ не имеет значения, число либо текст употребляются в ячейке Excel.

То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.

А вот 3-ий вариант — к проверке условия с помощью функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в британском варианте). Объединяем их функцией ИЛИ.

А сейчас вставляем в ячейку D5 недостающее значение и проверяем, все ли работает.

Возлагаем надежды, этот материал был полезен. А вот еще несколько примеров работы с критериями и функцией ЕСЛИ в Excel.

Функция ЕСЛИ: примеры с несколькими критериями

Для того, чтоб обрисовать условие в функции ЕСЛИ, Excel дозволяет употреблять наиболее сложные конструкции. В том числе можно употреблять и несколько критерий. Разглядим на примере. Для объединения нескольких критерий в […]

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

Чтоб выполнить действие лишь тогда, когда ячейка не пуста (содержит какие-то значения), вы сможете употреблять формулу, основанную на функции ЕСЛИ. В примере ниже столбец F содержит даты окончания закупок шоколада. […]

Проверка ввода данных в Excel

Подтверждаем корректность ввода галочкой. Задачка: При ручном вводе данных в ячейки таблицы инспектировать корректность ввода в согласовании с имеющимся перечнем допустимых значений. В случае правильного ввода в отдельном столбце ставить […]

Функция ЕСЛИ: проверяем условия с текстом

Разглядим внедрение функции ЕСЛИ в Excel в этом случае, если в ячейке находится текст. Будьте особо внимательны в этом случае, если вам важен регистр, в каком записаны ваши текстовые […]

Визуализация данных с помощью функции ЕСЛИ

Функцию ЕСЛИ можно употреблять для вставки в таблицу знаков, которые наглядно демонстрируют происходящие с данными конфигурации. Например, мы желаем показать, происходит рост либо понижение продаж. В столбце N поставим […]

Как функция ЕСЛИ работает с датами?

На 1-ый взор может показаться, что функцию ЕСЛИ для работы с датами можно употреблять так же, как для числовых и текстовых значений, которые мы лишь что обсудили. К огорчению, это […]