Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

Итак, если все собрать, то функция для расчета рабочих дней в текущем году будет выглядеть так:

=ЧИСТРАБДНИ(ДАТА(ГОД(СЕГОДНЯ());1;1); СЕГОДНЯ())

У обеих функций есть «международная» версия (.МЕЖД или. INTL на конце), в которой задается специальный тип рабочей недели. Вы можете выбрать один из вариантов в списке или ввести вручную соответствующее ему число. Например, 7 — выходные в пятницу и субботу.

Или задать ваш собственный (в том же аргументе) в формате 0011001, где 1 = выходной, а 0 = рабочий день.

Логические выражения и функция ЕСЛИ / IF

Функция ЕСЛИ / IF делает следующее: проверяет логическое выражение (это ее первый аргумент) и возвращает в зависимости от его значения (а логическое выражение может принимать только два значения — ИСТИНА / TRUE или ЛОЖЬ / FALSE) то или иное значение (второй и третий аргумент).

Проще говоря, ЕСЛИ позволяет проверить условие и в зависимости от его выполнения выдать тот или иной результат.

В логических выражениях используются знаки сравнения: «равно» (=), «не равно» (<>), «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=).

Например:

=A1="Возврат"

Такое выражение будет возвращать ИСТИНА, если в ячейке A1 находится текст «Возврат» (в любом регистре!).

Обратите внимание: первый знак «равно» всегда означает начало формулы, а второй — это знак сравнения.

Если нужно сравнивать с учетом регистра, используйте функцию EXACT / СОВПАД — она возвращает ИСТИНА только тогда, когда два ее аргумента равны друг другу, включая регистр символов.

=B1>=A1

Такое выражение будет возвращать ИСТИНА / TRUE, если число в ячейке B1 больше (либо равно) числа в ячейке A1.

=A2>10000

Это выражение будет истинным, если число в A2 строго больше десяти тысяч.

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

Такие выражения и функции можно использовать в первом аргументе функции ЕСЛИ / IF — функции, которая позволяет проверять условия и возвращать одно или другое значение. Они проверяются, и в зависимости от выполнения условия ЕСЛИ возвращает второй или третий аргумент:

=ЕСЛИ(условие; значение при выполнении условия; значение при невыполнении условия)

Например, мы хотим автоматически (формулой) формировать скидку в определенных случаях: если стоимость позиции выше какой-то планки, допустим 300 000. Тогда в общем виде функция ЕСЛИ будет выглядеть так:

=ЕСЛИ(Ячейка со стоимостью > 300000; Стоимость * % скидки; 0)

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

«Пограничную» стоимость для получения скидки и процент скидки можно указывать в отдельных ячейках — тогда их можно будет легко поменять и получить в таблице обновленный результат, не трогая формулы. Главное в таком случае — не забыть закрепить их, сделав ссылки абсолютными.

НЕСКОЛЬКО УСЛОВИЙ: ФУНКЦИИ И / AND, ИЛИ / OR

Если нужно проверить выполнение сразу нескольких условий, используйте функцию И / AND. Она возвращает ИСТИНА только в том случае, если сразу все ее аргументы (два или более логических выражений) истинные.

Допустим, мы даем скидку только тем, кто покупал у нас электронную библиотеку, и только если клиент обратился к нам на выставке. Оба условия в разных столбцах должны выполняться одновременно, поэтому мы отправляем их внутрь функции И:

И([@Продукт]="Электронная библиотека";[@Канал]="Выставка")

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

Эта функция нужна нам не сама по себе — она будет внутри ЕСЛИ в качестве логического выражения. В общем виде:

=ЕСЛИ(И(…); значение, если все условия в И выполняются; значение, если хотя бы одно не выполняется)

И в нашем примере:

=ЕСЛИ(И([@Продукт]="Электронная библиотека";[@Канал]="Выставка"); % Скидки * Стоимость; 0)

А если требуется выполнение хотя бы одного из условий, подойдет функция ИЛИ / OR. Она возвращает ИСТИНА / TRUE, если хотя бы один из ее аргументов истинный.

Если мы хотим давать одинаковую скидку за разные продукты, то ИЛИ будет лучшим решением:

ИЛИ([@Продукт]="Курс";[@Продукт]="Консультация")

Она будет возвращать ИСТИНА и в тех случаях, когда продукт — курс, и когда продукт — консультация.

По аналогии с И подставим ее в качестве первого аргумента ЕСЛИ:

=ЕСЛИ(ИЛИ(…); значение, если хотя бы одно условие в ИЛИ выполняется; значение, если ни одно не выполняется)

Очевидно, что одновременно оба условия в данном примере выполняться не могут — в ячейке может быть или что-то одно, или другое. Но если бы мы, например, давали скидку во всех случаях, когда покупают курс (столбец «Продукт») ИЛИ когда канал продаж — «Рассылка», мы тоже могли бы воспользоваться функцией ИЛИ:

ИЛИ([@Продукт]="Курс";[@Продукт]="Консультация")

НЕСКОЛЬКО УСЛОВИЙ: ВЛОЖЕННЫЕ ЕСЛИ

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

=ЕСЛИ(первое условие; значение, если выполняется первое условие; ЕСЛИ(второе условие; значение, если оно выполняется; третье условие))

То есть вторая функция ЕСЛИ выступает третьим аргументом первой — она вычисляется только тогда, когда первое условие не выполняется (возвращает ЛОЖЬ), и проверяет свое, второе условие.

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

Например, мы хотим выдавать скидку 10% при продаже курса, но 12% — при продаже консультации.

Тогда формула будет выглядеть так:

=ЕСЛИ([@Продукт]="курс";10%*[@Сумма];ЕСЛИ([@Продукт]="консультация";12%*[@Сумма];0))

Если условий больше, то, соответственно, придется добавить еще функции ЕСЛИ.

В Excel 2003 допускалось максимум 7 уровней с вложенными функциями ЕСЛИ, а начиная с 2007 — до 64 уровней! Но лучше избегать таких массивных конструкций и при таком разветвлении рассмотреть альтернативы.

Это могут быть функции ЕСЛИМН / IFS или ПЕРЕКЛЮЧ / SWITCH, если они есть в вашей версии. О них мы поговорим прямо сейчас.

Либо можно воспользоваться функцией ВПР /