Продолжаем изучать возможности функции ЕСЛИ. Если вы пропустили предыдущие части разбора, то вот и вот. Сегодня мы узнаем, как с помощью неё можно проверить значения даты, а также пустоту или незаполненность ячеек. Поехали!
Пример 5: Проверка значений даты
Рассмотрим пример использование функции ЕСЛИ для проверки значений даты. Вернёмся к нашим клиентам из прошлых примеров. Вы также можете скачать готовый файл с примерами по ссылке.
Предположим, что у нас есть даты, когда от них должен поступить платеж. Мы должны проверить наличие просрочек по платежам, а если дата платежа ещё не наступила, то посчитать сколько дней до неё осталось.
Введём в ячейку C2 и протянем вниз формулу:
=ЕСЛИ(B2<СЕГОДНЯ();"Просрочено";ДНИ(B2;СЕГОДНЯ()))
Как вы можете видеть, мы в нашей формуле использовали кроме функции ЕСЛИ также ещё две функции: СЕГОДНЯ и ДНИ.
Функцию СЕГОДНЯ мы уе рассматривали в самом первом уроке. Она возвращает текущую дату и не требует никаких аргументов. Однако, не забывайте, что после её имени всё равно обязательно печатать скобки СЕГОДНЯ().
Функция ДНИ используется для подсчёта разницы между двумя датами. Она требует два аргумента: первый - конечная дата, и второй - начальная дата.
Теперь расшифруем нашу формулу. Если дата в ячейке B2 меньше сегодняшней даты (на момент написания этой статьи 05.02.2023 г.), то пишем "Просрочено", иначе считаем разницу в количестве дней между датой в ячейке B2 и сегодняшней даты.
Вот что у нас получилось:
Надеюсь, тут всё понятно. Если нет, то задавайте ваши вопросы в комментариях. А мы перейдём к следующему примеру.
Пример 6: Проверка пустой или незаполненной ячейки
Существует два метода проверки того, является ли ячейка пустой или незаполненной. Вы можете удивиться, зачем я повторяюсь? Разве это не одно и то же? Нет, между этими двумя словами есть различие, потому что иногда ячейка может выглядеть пустой, однако, на самом деле она что-то содержит.
Посмотрим на наш предыдущий пример с другой стороны. У нас есть даты совершенных платежей. Если платежа ещё не было, то дата отсутствует и ячейка пустая. Нам нужно посчитать сколько дней прошло со дня платежа тех людей, у которых он был совершён.
Перед тем как читать дальше, используя предыдущие знания, попробуйте придумать формулу, которая считает количество прошедших дней до сегодняшнего числа.
Самый простой способ это сделать с помощью следующей формулы:
=СЕГОДНЯ()-B2
От сегодняшней даты отнимаем дату в ячейке B2.
Посмотрите на результат. Напоминаю, что сегодня, когда я пишу эту статью 5 февраля 2023 года. С платежа Гришина Вячеслава 03.02.2023 прошло 2 дня. Отлично. Поляков Тихон заплатил 04.02.2023 - прошел 1 день. До сих по всё корректно. Но Зорина Варвара ещё не платила, поэтому ячейка B4 пустая. А вот количество дней с платежа у нас получилось 44962. Это явно не тот результат, который мы ожидаем увидеть.
Давайте сперва разберёмся почему так произошло, а потом как сделать так, чтобы ячейки напротив отсутствующих дат платежей также были пустыми.
Откуда взялось такое непонятное число 44962? Дело в том, что Excel может представлять даты как в привычном нам формате дат, так и в числовом виде. В колонке B данные у нас отформатированы в формате дат.
А колонка C отформатирована в формате Общий, который по умолчанию представляет данные в числовом формате. Это то что нам и нужно, потому что результат в количестве дней нам нужен в виде числа.
А каким образом Excel может показывать даты в числовом формате? Дело в том, что в Excel каждая дата последовательно нумеруется. Но есть одно ограничение: даты в Excel начинаются с 01.01.1900 г.
Таким образом, в Excel 1 января 1900 года - это 1, 2 января 1900 года - это 2 и так далее, вплоть до сегодняшнего дня и в будущее. Сегодня, например, 5 февраля 2023 года - 44962 день. Знакомое число, не правда ли?
Это то число, которое у нас вернула формула напротив пустой ячейки. По сути, пустая ячейка - это ноль. А наша формула отняла от сегодняшней даты ноль и получилась сегодняшняя дата. А так как колонка отформатирована в Общем формате, то дата была представлена в виде числа 44962.
Надеюсь, вы разобрались, что произошло. Теперь давайте разберёмся как это исправить.
Для того, чтобы напротив пустой ячейки наша формула ничего не выводила внесём в неё следующие изменения:
=ЕСЛИ(ЕПУСТО(B2);"";СЕГОДНЯ()-B2)
Познакомимся с новой логической функцией ЕПУСТО. Она была использована для того, чтобы проверить, является ли ячейка пустой и если это так, то возвращает ИСТИНА, в противном случае ЛОЖЬ.
Таким образом объяснение нашей формулы следующее: если ячейка B2 пустая, то возвращаем пустую строку (обратите внимание на две двойные кавычки, которые мы использовали для этого ""), иначе от сегодняшней даты отнимаем дату в ячейке B2.
Теперь мы получили тот результат, который и требовался.
Кстати, мы могли бы достичь такого же результата и с помощью формулы:
=ЕСЛИ(B2="";"";СЕГОДНЯ()-B2)
Если в ячейке B2 пустая строка, то возвращаем пустую строку, иначе от сегодняшней даты отнимаем дату в ячейке B2.
Результат в нашем примере при использовании обеих формул идентичный. Но в чём тогда разница? Зачем использовать функцию ЕПУСТО, если можно обойтись кавычками ""?
Дело в том, что чтобы функция ЕПУСТО сработала, ячейка должна быть полностью пустой. А как она может быть не полностью пустой, спросите вы? Ну, например, в нашей таблице есть такие не полностью пустые ячейки и мы их сделали сами. Там, где не было платежей и наша формула возвращала пустую строку "" визуально ячейки выглядят пустыми, но на самом деле они содержат в себе строку, хотя и пустую. Если мы выделим такую ячейку, то увидим что в ней находится формула:
Таким образом, нашей второй формуле достаточно, чтобы проверяемая ячейка была только визуально пустой. А вот первая формула вернёт ошибку, если проверяемая ячейка визуально будут пустой, но на самом деле незаполненной.
Давайте для примера введём в ячейку B4 пустую строку:
=""
В результате получаем ошибку #ЗНАЧ! (обо всех ошибках вы можете узнать здесь).
Вот в чём разница между пустыми и незаполненными ячейками.
Если бы вам нужно было проверить, не содержит ли ячейка что-либо, визуально невидимое, то есть не незаполненная ли она, то вы могли бы использовать следующую формулу, используя оператор <>:
=ЕСЛИ(B2<>"";СЕГОДНЯ()-B2;"")
Если содержимое ячейки B2 не равно (<>, со всеми операторами, используемыми в формулах можете познакомиться здесь) пустой строке, то от сегодняшней даты отнимаем дату в ячейке B2, иначе вернуть пустую строку.
Мы могли бы сделать такую проверку и с функцией ЕПУСТО добавив функцию НЕ:
=ЕСЛИ(НЕ(ЕПУСТО(B2));СЕГОДНЯ()-B2;"")
Если ячейка B2 не пустая, то от то от сегодняшней даты отнимаем дату в ячейке B2, иначе вернуть пустую строку.
Функция НЕ была использована для получения обратного результата функции ЕПУСТО. В ближайшее время мы рассмотрим функцию НЕ подробнее, но на сегодня, думаю, достаточно.
Чувствуете, как с каждым разом становится всё труднее? Но это замечательно! Ведь только преодолевая трудности мы растём над собой.
Сегодня мы с вами в рамках изучения функции ЕСЛИ познакомились с новыми функциями: ДНИ, ЕПУСТО, вскользь НЕ. Узнали как Excel считает даты и в чём разница между пустыми и незаполненными ячейками. В общем, поработали плодотворно.
В следующей статье немного отдохнём и повеселимся, научившись использовать в работе с Excel смайлики😉 Ну и сделаем это в рамках, конечно же, продолжения изучения функции ЕСЛИ.
Не переключайтесь;)