Электронные таблицы для финансовых и офисных задач

2.4. Бизнес-логика: ЕСЛИ, ЕСЛИМН, ЕСЛИОШИБКА

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

Например:

— платеж просрочен или нет;

— план продаж выполнен или не выполнен;

— клиент получает скидку или платит полную стоимость;

— сотруднику начисляется премия или нет;

— ошибка в формуле должна отображаться на экране или заменяться понятным текстом.

Для таких задач используются логические функции. Они позволяют Excel действовать по принципу:

если условие выполняется — сделать одно, если не выполняется — сделать другое.

В этом разделе рассмотрим три функции:

ЕСЛИ — проверяет одно условие;

ЕСЛИМН — проверяет несколько условий по порядку;

ЕСЛИОШИБКА — заменяет ошибку в формуле на понятный результат.

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

Функция ЕСЛИ

Функция ЕСЛИ проверяет условие и возвращает один результат, если условие истинно, и другой результат, если условие ложно.

Синтаксис:

=ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь)

Разберем эту запись по частям.

Логическое_выражение — это условие, которое Excel должен проверить.

Значение_если_истина — что показать, если условие выполняется.

Значение_если_ложь — что показать, если условие не выполняется.

Простой пример:

=ЕСЛИ (B2> =100000; «План выполнен»; «План не выполнен»)

Эта формула проверяет значение в ячейке B2. Если оно больше или равно 100 000, Excel покажет текст «План выполнен». Если меньше — «План не выполнен».

Пример: проверка выполнения плана

Представим таблицу продаж менеджеров:

В столбце «Статус» нужно показать, выполнил ли менеджер план.

В ячейке D2 можно написать:

=ЕСЛИ (C2> =B2; «План выполнен»; «План не выполнен»)

Excel сравнит факт продаж с планом.

Если факт больше или равен плану, появится «План выполнен».

Если факт меньше плана, появится «План не выполнен».

После этого формулу можно протянуть вниз на остальные строки.

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

Условия сравнения

В функции ЕСЛИ часто используются знаки сравнения:

> — больше;

< — меньше;

> = — больше или равно;

<= — меньше или равно;

= — равно;

<> — не равно.

Примеры:

=ЕСЛИ (C2> 0; «Есть продажи»; «Нет продаж»)

=ЕСЛИ (D2=«Оплачено»; «Закрыто»; «Проверить»)

=ЕСЛИ (B2 <> «»; «Заполнено»; «Пусто»)

Последний пример проверяет, заполнена ли ячейка B2. Запись «» означает пустую строку. Если B2 не равно пустоте, Excel покажет «Заполнено», иначе — «Пусто».

Пример: расчет премии

Функция ЕСЛИ может возвращать не только текст, но и числа или результаты расчетов.

Представим, что сотрудник получает премию 5% от продаж, если выполнил план:

В ячейке D2 можно написать:

=ЕСЛИ (C2> =B2; C2*5%; 0)

Если менеджер выполнил план, Excel умножит фактические продажи на 5%.

Если не выполнил — поставит 0.

Для Иванова премия будет рассчитана, потому что 1 150 000 больше плана.

Для Садыковой результат будет 0, потому что план не выполнен.

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

Вложенные функции ЕСЛИ

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

— 100% и выше — «Отлично»;

— от 80% до 99% — «Нормально»;

— ниже 80% — «Плохо».

Такую задачу можно решить вложенными функциями ЕСЛИ:

=ЕСЛИ (C2/B2> =1; «Отлично»; ЕСЛИ (C2/B2> =0,8; «Нормально»; «Плохо»))

Excel сначала проверяет первое условие: факт делится на план. Если результат больше или равен 1, выводится «Отлично».

Если первое условие не выполнено, Excel переходит ко второй проверке. Если выполнение плана больше или равно 80%, выводится «Нормально». Если и это условие не выполнено, выводится «Плохо».

Такая формула работает, но читать её сложнее. Чем больше условий, тем длиннее становится запись. Поэтому для нескольких вариантов удобнее использовать функцию ЕСЛИМН.

Функция ЕСЛИМН

Функция ЕСЛИМН проверяет несколько условий по порядку и возвращает результат для первого выполненного условия.

Синтаксис:

=ЕСЛИМН (условие1; значение1; условие2; значение2; …)

Формула читается так:

если условие1 выполняется — вернуть значение1;

если условие2 выполняется — вернуть значение2;

и так далее.

Перепишем пример с рейтингом через ЕСЛИМН:

=ЕСЛИМН (C2/B2> =1; «Отлично»; C2/B2> =0,8; «Нормально»; C2/B2 <0,8; «Плохо»)

Такая запись обычно понятнее, чем вложенные функции ЕСЛИ:

Excel проверяет условия слева направо. Как только он находит первое истинное условие, он возвращает соответствующий результат и дальше не проверяет.

Поэтому порядок условий очень важен.

Важность порядка условий

Представим, что мы хотим присвоить уровень скидки по сумме заказа:

— от 500 000 и выше — скидка 10%;

— от 300 000 и выше — скидка 7%;

— от 100 000 и выше — скидка 3%;

— меньше 100 000 — скидка 0%.

Правильная формула:

=ЕСЛИМН (B2> =500000; 10%; B2> =300000; 7%; B2> =100000; 3%; B2 <100000; 0%)

Здесь условия идут от большего к меньшему. Если заказ равен 600 000, Excel сразу назначит скидку 10%.

А теперь неправильный порядок:

=ЕСЛИМН (B2> =100000; 3%; B2> =300000; 7%; B2> =500000; 10%; B2 <100000; 0%)

Если сумма заказа равна 600 000, первое условие B2> =100000 уже выполняется. Excel вернет 3% и до условий 7% и 10% уже не дойдет.

Поэтому в ЕСЛИМН условия нужно располагать так, чтобы более строгие условия проверялись раньше.

Пример: классификация задолженности

Функция ЕСЛИМН удобна для финансового контроля. Например, нужно разделить клиентов по сроку просрочки:

— 0 дней — «Нет просрочки»;

— от 1 до 10 дней — «Небольшая просрочка»;

— от 11 до 30 дней — «Средний риск»;

— более 30 дней — «Высокий риск».

В ячейке C2 можно написать:

=ЕСЛИМН (B2=0; «Нет просрочки»; B2 <=10; «Небольшая просрочка»; B2 <=30; «Средний риск»; B2> 30; «Высокий риск»)

Здесь условия идут от меньшего срока к большему. Если просрочка 5 дней, Excel вернет «Небольшая просрочка». Если 18 дней — «Средний риск». Если 45 дней — «Высокий риск».

Такая классификация помогает быстро расставить приоритеты: кого нужно просто напомнить об оплате, а с кем уже стоит работать внимательнее.

Функция ЕСЛИОШИБКА

В реальных таблицах формулы не всегда дают красивый результат. Иногда Excel показывает ошибки:

#ДЕЛ/0! — деление на ноль;

#Н/Д — значение не найдено;

#ЗНАЧ! — неправильный тип данных;

#ССЫЛКА! — ссылка повреждена;

#ИМЯ? — Excel не распознал имя функции или диапазона.

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

Функция ЕСЛИОШИБКА позволяет заменить ошибку на понятный текст, ноль или пустую ячейку.

Синтаксис:

=ЕСЛИОШИБКА (значение; значение_если_ошибка)

Первый аргумент — формула, которую нужно проверить.

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

Пример: деление на ноль

Представим таблицу расчета средней цены:

Обычная формула средней цены:

=B2/C2

Для первой строки всё работает: 700 000 делится на 2.

Но во второй строке количество равно 0. Делить на ноль нельзя, поэтому Excel покажет ошибку #ДЕЛ/0!.

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

=ЕСЛИОШИБКА (B2/C2; 0)

Теперь, если расчет невозможен, Excel покажет 0.

Иногда вместо нуля лучше показать текст:

=ЕСЛИОШИБКА (B2/C2; «Нет данных»)

А иногда лучше оставить ячейку визуально пустой:

=ЕСЛИОШИБКА (B2/C2; «»)

Запись «» означает пустой текст.

Важно понимать: ЕСЛИОШИБКА делает отчет аккуратнее, но не исправляет исходные данные.

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

2.5. Хронологический учет: функции даты и времени

В финансовых таблицах время играет такую же важную роль, как сумма или процент. Платеж может быть внесен вовремя или с задержкой. Счет может быть выставлен сегодня, а оплачен через 10 рабочих дней. Кредитный платеж может повторяться каждый месяц. Отчет может строиться на основе текущей даты.

Поэтому в Excel важно уметь работать не только с числами, но и с датами.

На первый взгляд дата выглядит как обычный текст:

15.01.2026

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

В данном подразделе рассмотрим базовые функции даты и времени:

СЕГОДНЯ — показывает текущую дату;

ДАТА — собирает дату из года, месяца и дня;

ДЕНЬ, МЕСЯЦ, ГОД — извлекают части даты;

ДАТАМЕС — прибавляет или вычитает месяцы;

РАБДЕНЬ — рассчитывает дату через заданное количество рабочих дней.

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

Как Excel понимает дату

Дата в Excel — это не просто надпись. Программа воспринимает её как порядковый номер дня.

Например, если в ячейку ввести дату и изменить формат ячейки на числовой, Excel покажет не привычную дату, а число. Это число используется для расчетов.

Именно поэтому можно написать:

=B2-A2

Если в A2 указана дата выставления счета, а в B2 — дата оплаты, Excel посчитает количество дней между ними.

Пример:

В ячейке D2 можно написать:

=C2-B2

Excel покажет, сколько дней прошло между выставлением счета и оплатой.

Если счет выставлен 05.01.2026, а оплачен 12.01.2026, результат будет 7 дней.

Такой расчет часто используется для анализа платежной дисциплины клиентов.

Функция СЕГОДНЯ

Функция СЕГОДНЯ возвращает текущую дату.

Синтаксис:

=СЕГОДНЯ ()

У этой функции нет аргументов, поэтому скобки остаются пустыми.

Если сегодня 29.06.2026, формула покажет:

29.06.2026

Главная особенность функции СЕГОДНЯ в том, что она обновляется автоматически. Если открыть файл завтра, дата изменится на завтрашнюю.

Эта функция полезна, когда нужно сравнить дату в таблице с текущей датой.

Пример: контроль просроченной оплаты

Представим таблицу счетов:

Нужно определить, какие счета уже просрочены.

В ячейке C2 можно написать:

=ЕСЛИ (B2 <СЕГОДНЯ (); «Просрочено»; «В срок»)

Формула сравнивает срок оплаты с текущей датой.

Если срок оплаты меньше сегодняшней даты, значит платеж просрочен.

Если срок оплаты равен сегодняшней дате или позже, Excel покажет «В срок».

Такую формулу удобно использовать в реестре дебиторской задолженности. При каждом открытии файла статусы будут обновляться автоматически.

Функция ДАТА

Функция ДАТА создает дату из трех частей: года, месяца и дня.

Синтаксис:

=ДАТА (год; месяц; день)

Пример:

=ДАТА (2026; 1; 15)

Результат:

15.01.2026

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

Например:

В ячейке D2 можно написать:

=ДАТА (A2; B2; C2)

Excel соберет корректную дату из отдельных частей.

Это удобно при формировании графиков платежей, отчетных периодов и календарных планов.

Функции ДЕНЬ, МЕСЯЦ и ГОД

Иногда нужно выполнить обратное действие: не собрать дату, а извлечь из неё отдельную часть.

Для этого используются функции:

=ДЕНЬ (дата)

=МЕСЯЦ (дата)

=ГОД (дата)

Пример:

Если дата находится в ячейке A2, можно использовать формулы:

=ДЕНЬ (A2)

=МЕСЯЦ (A2)

=ГОД (A2)

Для даты 15.01.2026 Excel вернет:

день — 15;

месяц — 1;

год — 2026.

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

Например, если в реестре платежей есть столбец «Дата оплаты», можно добавить отдельный столбец «Месяц» и использовать:

=МЕСЯЦ (A2)

После этого таблицу будет проще фильтровать и анализировать по месяцам.

Конец ознакомительного фрагмента.

Текст предоставлен ООО «Литрес».

Прочитайте эту книгу целиком, купив полную легальную версию на Литрес.

Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.