Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов
Шрифт:
Интервал:
Закладка:
Итак, если все собрать, то функция для расчета рабочих дней в текущем году будет выглядеть так:
=ЧИСТРАБДНИ(ДАТА(ГОД(СЕГОДНЯ());1;1); СЕГОДНЯ())
У обеих функций есть «международная» версия (.МЕЖД или. INTL на конце), в которой задается специальный тип рабочей недели. Вы можете выбрать один из вариантов в списке или ввести вручную соответствующее ему число. Например, 7 — выходные в пятницу и субботу.
Или задать ваш собственный (в том же аргументе) в формате 0011001, где 1 = выходной, а 0 = рабочий день.
Логические выражения и функция ЕСЛИ / IF
Файл с примерами: Логические выражения.xlsxФункция ЕСЛИ / 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, если они есть в вашей версии. О них мы поговорим прямо сейчас.
Либо можно воспользоваться функцией ВПР /